Check-moscow.ru

Финансы и учет
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как выполнить vlookup и вернуть несколько значений из раскрывающегося списка?

Расширенные формулы в Excel

Как только вы достигнете промежуточного уровня в Excel, вам придется приложить немало усилий, чтобы перейти на продвинутый уровень. Чтобы перейти на продвинутый уровень, необходимо знать некоторые часто используемые продвинутые формулы. В этой статье я расскажу о 10 самых важных формулах, с которыми должны быть знакомы студенты. Следуйте этой статье, чтобы учиться и изучать.

Примеры расширенной формулы в Excel

Рассмотрим, как использовать расширенные формулы в Excel на некоторых примерах.

Вы можете скачать этот Шаблон расширенных формул Excel здесь — Шаблон расширенных формул Excel

Пример # 1 — Частичная функция VLOOKUP

Вы наверняка сталкивались с ситуацией, когда VLOOKUP выдает ошибку даже при небольшом несоответствии в искомом значении. Если вы ищете зарплату Абхишека Синхи, а у вас есть только Абхишек, VLOOKUP не сможет получить данные.

Однако с помощью звездочки в конце значения поиска можно извлечь данные для частичного поиска.

В таблице 1 у меня есть полное имя и зарплата, но в таблице 2 есть только частичные имена, и мне нужно найти зарплату каждого сотрудника.

Шаг 1: Откройте формулу VLOOKUP в ячейке E3. Поставьте звездочку (*) с каждой стороны искомого значения, прежде чем выбрать его.

Шаг 2: Как обычно, теперь можно заполнить формулу VLOOKUP, и мы получим результат.

После использования вышеприведенной формулы результат показан ниже.

Та же формула используется в других ячейках.

N OTE. Недостатком здесь является то, что Partial VLOOKUP вернет одно и то же значение, если есть Abhishek Sinha, а также Abhishek Naidu. Потому что общее частичное значение здесь — Abhishek.

Пример № 2 — COUNTIFS с символами оператора

Вы должны были использовать функцию COUNTIF & IFS для подсчета элементов в списке. Мы также можем вычислять на основе символов операторов, таких как больше (>), меньше (<) и равно (=).

Теперь, например, посмотрите на приведенные ниже данные. Если вы хотите подсчитать общее количество счетов-фактур для Юго-Восточного региона после даты 10 января 2018 года, как вы его рассчитаете?

Читайте так же:
Как добавить текстовое поле в диаграмму в Excel?

Пример № 3 — ЕСЛИ условие с условиями И & ИЛИ

Логические функции являются частью нашей повседневной деятельности. Чтобы перейти на следующий уровень, необходимо овладеть ими. Если вы рассчитываете премию на основе нескольких условий, вам нужно вставить AND или OR в оператор IF, чтобы выполнить работу.

Предположим, вам нужно рассчитать размер премии за каждую услугу, исходя из стажа и выслуги лет, необходимых для этих функций. На основе приведенных ниже критериев нам необходимо рассчитать бонус.

Если стаж работы более 4 лет и отдел продаж или поддержки, то премия составляет 50 000 или 25 000.

Примените приведенную ниже формулу, чтобы получить сумму страхового взноса.

После использования приведенной выше формулы вывод приведен ниже.

Такая же формула используется в ячейках E3 — E9.

Пример # 4 — функция TEXT, чтобы сделать вас динамичным

Допустим, у вас есть ежедневная таблица продаж, которую нужно обновлять каждый день. В начале таблицы у вас есть один заголовок, который гласит «Сводные данные о продажах с ДД-ММ-ГГГГ по ДД-ММ-ГГГГ». Когда таблица обновляется, вы должны обновить дату заголовка. Разве это не утомительная задача — делать одно и то же снова и снова? Мы можем сделать этот заголовок динамическим, используя функции TETX, MIN и Max вместе с символом оператора сцепления амперсанд (&).

Пример № 5 — INDEX + MATCH + MAX, чтобы найти самого высокого продавца

У вас есть список менеджеров по продажам и продаж, которые они совершили, с их именами. Как узнать, кто является лучшим или величайшим продавцом в списке? Конечно, у нас есть и другие методы определения результата, но эта функция может вернуть лучшего продавца из всех.

Используйте функцию ниже, чтобы получить имя главного провайдера.

После использования приведенной выше формулы получается следующий результат.

Читайте так же:
Как добавить верхний или нижний колонтитул на все листы / страницы в Excel?

Пример # 6 — Получить количество уникальных значений из списка

Если у вас много дублирующихся значений и вам нужно определить, сколько уникальных значений содержится в этих значениях, как это сделать? Вы можете определить это, удалив дублирующее значение, но это не динамический способ подсчета уникального значения.

Используя эту функцию массива, мы можем найти из множества уникальные значения.

Для получения уникальных значений можно использовать формулу, приведенную ниже.

Примечание: Это формула массива, вы должны закрыть формулу, удерживая Shift + Ctrl, и нажать Enter.

Пример №7. Использование именованного диапазона для динамического раскрывающегося списка

При удалении или добавлении значений в выпадающий список необходимо вернуться к исходному диапазону списка. После этого необходимо вернуться к ячейкам выпадающего списка и снова обновить диапазон выпадающего списка.

Однако если вы сможете создать именованный диапазон для вашего выпадающего списка, вы сможете сделать его динамичным и актуальным.

Создайте строку имени, как показано на следующем рисунке.

Затем вы можете открыть выпадающее диалоговое окно, выбрав выпадающую ячейку.

На источнике нажмите F3, отобразятся все определенные имена, выберите имя из его выпадающего списка.

ОК, выпадающий список готов и автоматически обновляет значения при изменении области выпадающего списка.

Пример № 8 — избавление от значений ошибок с использованием функции IFERROR

Я уверен, что вы сталкивались с ошибками при работе с VLOOKUP, раздел Расчеты. Обработка этих значений ошибок является сложной задачей. Однако мы можем избавиться от этих ошибочных значений, используя в формуле функцию IFERROR.

После применения вышеприведенной формулы мы получаем результат, приведенный ниже.

Та же формула, что и для остальных ячеек.

Пример № 9 — Использование функции PMT для создания собственной диаграммы EMI

Сегодня вариант EMI не является редкостью для всех нас. В Excel мы можем оценить собственный график EMI с помощью функции PMT. Выполните следующие действия, чтобы создать свой собственный график.

Примените приведенную ниже формулу в ячейке В4, чтобы получить значение EMI.

Читайте так же:
Как выделить повторяющиеся значения разными цветами в Excel?

Пример # 10 — INDEX + MATCH как альтернатива функции VLOOKUP

Надеюсь, вы знаете об ограничении функции VLOOKUP. Одно из основных ограничений заключается в том, что VLOOKUP может получать данные слева направо, а не справа налево. Не все временные данные хорошо организованы и готовы к использованию. Часто столбец значений, которые мы ищем, находится слева от искомого значения, поэтому VLOOKUP в таких случаях бесполезен.

Комбинация INDEX + MATCH — это альтернатива функции VLOOKUP.

Применив приведенную выше формулу, мы получим результат, представленный ниже.

Такую же формулу можно найти и в других ячейках.

На основе идентификатора продукта нам нужно извлечь значения продаж. В основной таблице идентификатор продукта находится справа от столбца продаж. Поэтому VLOOKUP не может получить данные. Используйте следующую формулу для извлечения данных.

Рекомендуемые статьи

Это руководство по расширенным формулам в Excel. Здесь мы обсудим, как использовать расширенные формулы в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете посмотреть другие предложенные нами статьи -.

Формула с несколькими функциями ЕСЛИ в Excel

На следующей иллюстрации показан лист с двумя выпадающими списками, в которых пользователь может указать тип автомобиля и его характеристики.

тип автомобиля и его характеристики.

Ячейки A2 и C2 содержат раскрывающиеся списки. Свойства автомобиля хранятся в двух диапазонах ячеек под этими таблицами D2:D3 и D6:D7. Создайте combobox, выбрав «DATA» — «Data Handling» — «Data Validation», и в окне «Value Validation» укажите «Data Type»: List». Заполните параметры, как показано на рисунке ниже:

выпадающие списки Свойства.

В данном примере используется формула в ячейке C7, которая, в зависимости от типа и характеристик автомобиля, возвращает тип его кузова: седан, купе, пикап или SUV:

Формула несколько функций ЕСЛИ.

Вычисление первого логического выражения в соответствии с первым условием влияет на результат, возвращаемый вторым логическим выражением. Рисунок:

ЕСЛИ несколько условий.

В этом примере, если проверяемая ячейка имеет значение «Автомобиль», второе условие проверяет, имеет ли другая ячейка значение 2-дверный или 4-дверный. И если проверяемая ячейка имеет значение «Truck» в первом условии, то проверяется второе условие. Теперь проверяется, содержит ли вторая ячейка значение «с задним сиденьем» или «без заднего сиденья».

Читайте так же:
Как заменить n-е / все вхождения символа в строке в Excel?

Основным инструментом для выполнения условного анализа данных в Excel является функция ЕСЛИ. Эту функцию можно зациклить, объединив с ее аргументами другую функцию ЕСЛИ, если вы хотите проанализировать данные по нескольким условиям. В приведенном выше примере первая функция ЕСЛИ проверяет содержимое ячейки A2. Вместо того чтобы вернуть результат от второго аргумента, функция вызывает вторую функцию ЕСЛИ, которая уже проверяет значение ячейки C2 по второму условию. Как и во второй функции, третьим аргументом является не обычное значение, а третья функция ЕСЛИ, которая также проверяет значение ячейки C2, но уже по другому условию.

На изображении примера мы видим, что пользователь выбрал из выпадающего списка тип транспортного средства «Грузовик». Логическое выражение первой функции ЕСЛИ вернуло FALSE, потому что содержимое ячейки A2 не равно значению «Truck». Поэтому возвращается второй аргумент первой функции. И в этом аргументе формула проверяет, содержит ли ячейка C2 значение «Заднее сиденье», и возвращает значение первого аргумента «SUV». Если бы пользователь указал во втором выпадающем списке опцию «без заднего сиденья», то было бы возвращено содержимое третьего аргумента третьей функции IF, «Pickup».

Как выполнить vlookup и вернуть несколько значений из раскрывающегося списка?

Обычно вы можете использовать функцию vlookup для получения первого совпадающего значения, но иногда вы хотите получить все совпадающие записи на основе определенного критерия. В этой статье я расскажу, как использовать vlookup и возвращать все совпадающие значения по вертикали, горизонтали или в одной ячейке.

Vlookup и вернуть все соответствующие значения по вертикали

Чтобы извлечь все значения, соответствующие определенному критерию по горизонтали, используйте следующий алгоритм:

1. Введите или скопируйте эту формулу в пустую ячейку, где вы хотите отобразить результат:

Читайте так же:
Как заменить последнюю запятую на и в ячейках в Excel?

Например, в приведенной выше формуле B2:B15 указывает, содержит ли столбец запись, которую вы хотите вернуть; A2:A15 указывает критерий; и D2 указывает конкретный критерий, по которому вы хотите вернуть значения. Вы можете изменить их по своему усмотрению.

2, Затем нажмите Shift + Ctrl + Enter вместе, чтобы получить первое значение, затем перетащите описание заливки вниз, чтобы получить все соответствующие записи, если необходимо, см. скриншот:

Vlookup и вернуть все соответствующие значения по горизонтали

Если вы хотите, чтобы соответствующие значения отображались в горизонтальном порядке, вам поможет приведенная ниже матричная формула.

Введите или скопируйте эту формулу в пустую ячейку, где должен появиться результат:

Примечание: В приведенной выше формуле B2:B15 — столбец, содержащий соответствующую запись, которую мы хотим вернуть; A2:A15 — столбец, содержащий критерий; и D2 — конкретный критерий, на основе которого мы хотим вернуть значения. Они могут быть изменены по мере необходимости.

2, Затем нажмите Shift + Ctrl + Enter, чтобы получить первое значение, затем перетащите маркер заполнения вправо, чтобы получить все необходимые записи, см. скриншот :

Vlookup и вернуть все соответствующие значения в одну ячейку

Чтобы выполнить vlookup и вернуть все соответствующие значения в одной ячейке, необходимо применить следующую формулу массива

Введите или скопируйте в пустую ячейку следующую формулу:

Примечание: В приведенной выше формуле B2:B15 — столбец, содержащий соответствующую запись, которую мы хотим вернуть; A2:A15 — столбец, содержащий критерий; и D2 — конкретный критерий, по которому мы хотим вернуть значения. Не стесняйтесь изменять их по своему усмотрению.

2, Затем нажмите Shift + Ctrl + Enter вместе, чтобы внести все совпадающие значения в одну ячейку, смотрите скриншот:

Совет: Эта формула успешно применяется только в Excel 2016 и более поздних версиях. Если у вас нет Excel 2016, перейдите по ссылке, чтобы загрузить его.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector