Загрузка...Функция, формула ВПР (VLOOKUP)
Функция, формула ВПР (VLOOKUP)
Предположим мы имеем две таблици с данными — таблицу кредитов и таблицу обеспечения:
Нам нужно автоматически подставить обеспечения в таблицу кредитов, ориентируясь по номеру соглашения для дальнейшего формирования отчетности.
Решение.
В Excel в стандартоному наборе функций в категории Lookup and reference есть функция VLOOKUP . Эта функия ищет указанное значение (в нашем примере номер соглашения) в крайней левой колонке указанной таблицы (таблицы обеспечения) двигаясь сверху вниз и, находя их, отражает значение соседней ячейки (тип обеспечения по кредиту). Схематично работа функции выглядит так:
(Insert — Name — Define) та натисніть CTRL+F3 та введіть будь-яке ім’я (без пробілів), для прикладу, Прайс . Надалі ви зможете використовувати цю назву для прайс-листа.
Поэтому, используем функцию VLOOKUP . Выберите ячейку где она будет введена (E2) и откройте мастера формул (меню Insert — Function ). В категории (Lookup and Reference) найдите функцию VLOOKUP и нажмите ОК . Появится окно для ввода аргументов функции:
Заполните их один за другим:
- Lookup Value — номер соглашения, которое функция будет искать в крайней левой колонке таблицы обеспечения. В нашем случае — номер соглашения «KF568» с ячейки С2 .
- Table Array — таблица, из которой мы будем получать искомые значения, это наша таблица обеспечения. Для ссылки мы используем диапазон $G$2:$H$11 (диапазон фиксируем знаком доллара ($), для того, чтобы он нам не смещался вниз при копировании формулы).
- Column index number — порядковый номер (не буква!) Колонки в таблице обеспечения, с которой мы будем брать название обеспечения. Первая колонка таблицы обеспечения имеет порядковый номер 1, следовательно, нам нужно взять тип обеспечения со второй колонки (номер 2).
- Range Lookup — в это поле мы можем ввести только два значения: TRUE або FALSE:
- Если мы введем значение 0 или FALSE , то это в действительности означает, что разрешен поиск только с точным совпадением , то есть, если функция не найдет в таблице обеспечение номер соглашения (если он отсутствует), тогда появится ошибка #N/A (нет данных).
- Если мы введем значение 1 или TRUE , это означает, что разрешено поиск не только точного, но и приблизительного совпадения . В большинстве случаев, такая приблизительная подстановка может привести ошибочный результат, когда VLOOKUP подставлять значения других соглашений. Поэтому, приблизительный поиск вам следует использовать только для цифровых значений (предварительно отсортированных по возрастанию).
- Разрешено точный поиск (аргумент Range Lookup = 0 ) и искомого значения нет в таблице (Table ) .
- Разрешен приблизительный поиск ( Range Lookup = 1 ), но в таблице (T able) , в которой мы ищем значение, названия не посортированы по возрастанию .
- Формат ячейки, с которой мы берем номер сделки (например, С2 в нашем случае) и формат ячейки первой колонки (G2:G11) таблицы различны (например, цифровой и текстовый). Эта ситуация особенно типична, когда используются цифровые коды вместо текстовых названий (номера счетов, идентификационные коды, даты и т.п.). В этом случае вы можете использовать функции VALUE и TEXT для преобразования форматов данных. Это выглядит так:
= VLOOKUP (TEXT (C2)$G$2:$H$11;0). - Функция не находит значение тому, что в коде есть пробелы и непечатаемые символы (переносы строки и т.п.). В этом случае вы можете использовать текстовую функцию (TRIM) и (CLEAN) для их удаления:
=VLOOKUP(TRIM(CLEAN(C2));$G$2:$H$11;0). - Искомое значение, в рассматриваемом примере, это табельный номер, ячейка с табельным номером выделена рамкой синего цвета;
- Таблица, в приведенном примере это таблица с табельными номерами и именами, выделена рамкой зеленого цвета;
- Номер столбца, в используемой для примера таблице, столбец с именами имеет порядковый номер два;
- Интервальный просмотр. Это необязательный аргумент, о нем чуть позже.
- Номер столбца с искомыми значениями;
- Таблица;
- Номер столбца в таблице;
- Условие (тип) поиска;
- Номер столбца для вставки результатов.
- Искомое значение — то, что должна найти функция, и вариантами которого являются значения ячейки, ее адрес, имя, заданное ей оператором. В нашем случае — это фамилия и имя менеджера.
- Таблица — диапазон строк и столбцов, в котором ищется критерий.
- Номер столбца — его порядковое число, в котором располагается сумма продаж, то есть результат работы формулы.
- Интервальный просмотр. Он вмещает значение либо ЛОЖЬ, либо ИСТИНА. Причем ЛОЖЬ возвращает только точное совпадение, ИСТИНА — разрешает поиск приблизительного значения.
- Формула введена, а столбец искомых критериев не заполнен (в данном случае колонка С).
- В столбец С внесено значение, которое отсутствует в колонке А (в диапазоне поиска данных). Для проверки наличия искомого значения следует выделить столбец критериев и во вкладке меню «Правка» — «Найти» вставить данную запись, запустить поиск. Если программа не находит его, значит оно отсутствует.
- Форматы ячеек колонок А и С (искомых критериев) различны, например, у одной — текстовый, а у другой — числовой. Изменить формат ячейки можно, если перейти в редактирование ячейки (F2). Такие проблемы обычно возникают при импортировании данных с других прикладных программ. Для избежания подобного рода ошибок в формулу ВПР есть возможность встраивать следующие функции: ЗНАЧЕН или ТЕКСТ. Выполнение данных алгоритмов автоматически преобразует формат ячеек.
- В коде функции присутствуют непечатные знаки или пробелы. Тогда следует внимательно проверить формулу на наличие ошибок ввода.
- Задан приблизительный поиск, то есть четвертый аргумент функции ВПР имеет значение 1 или ИСТИНА, а таблица не отсортирована по восходящему значению. В этом случае столбец искомых критериев требуется отсортировать по возрастанию.
- Использование дополнительной колонки
- Использование функции ВЫБОР для создания новой таблицы просмотра
- Использование функций ИНДЕКС и ПОИСКПОЗ
- Использование функции СУММПРОИЗВ
- Объединяет значения столбцов Месяц и Город в один массив: ЯнвМосква, ФевМосква …
- Объединяет два массива в таблицу, состоящую из двух столбцов.
Вот и все! Осталось нажать ОК и скопировать введенную функцию по всей колонке.
Функция VLOOKUP возвращает ошибку (#N/A) если:
Для подавления сообщения об ошибке (#N/A) , когда функция не может найти точного совпадения, вы можете использовать функцию IFERROR .
Функция IFERROR проверяет, является ли ошибка (#N/A) результатом работы VLOOKUP, и если да, то выводит пустую строку ( «» ) или ноль, если нет — результат работы VLOOKUP.
Функция ВПР в Excel
Функция ВПР в Excel — одна из самых интересных и полезных функций для нахождения определенных значений в одной таблице и подстановки этих значений в другую таблицу. Однако, функция имеет некоторые ограничения, а также, при неправильном использовании, может выдавать некорректные результаты или приводить к ошибкам.
Формула ВПР
Функция ВПР предназначена для поиска и подстановки значений из одной таблицы в другую на основании какого-либо признака, объединяющего обе эти таблицы. Находится функция в категории «Ссылки и массивы».
Свое название ВПР функция получила от первых букв в словах Вертикальное Первое Равенство. Смысл этой функции заключается в поиске значения в крайнем левом столбце таблицы и определении значения ячейки, находящейся в заданном столбце той же строки.
Допустим, существует две таблицы. В одной таблице содержатся табельные номера работников и их имена, другая таблица состоит из табельных номеров и фамилий. Задача заключается в том, чтобы объединить эти таблицы, или другими словами, подставить в одну из этих таблиц данные из другой таблицы.
Функция ВПР имеет четыре аргумента:
Результат вычисления функции виден на изображении ниже.
В примере два работника с именами Иван и Сидор умышленно имеют одинаковый табельный номер. Если их поменять местами, то и результат вычисления функции ВПР будет другим, что может привести к неожиданным результатам. Функция определяет ПЕРВОЕ равенство. При использовании формул с функцией ВПР рекомендуется сделать сортировку таблицы по искомому значению, то есть, по табельному номеру в рассматриваемом примере.
Ошибки #Н/Д, #ССЫЛКА! и #ЗНАЧ!
Достаточно часто функция ВПР вместо ожидаемого результата выдает ошибки вида #Н/Д (значение недоступно формуле или функции). Появление таких ошибок, как правило, связано с отсутствием искомого значения в таблице, либо с неправильным вводом формулы и незнанием некоторых особенностей функции ВПР.
Неправильный ввод формулы
При распространении формулы на другие ячейки, выделенные диапазоны могут быть смещены таким образом, что искомое значение оказывается за пределами таблицы.
Результатом вычисления функции будет ошибка вида #Н/Д.
В нашем простом примере таблица состоит всего из двух столбцов, на практике же столбцов может быть больше. Важно, чтобы, во-первых, столбец, в котором будет производиться поиск искомых значений был крайним левым, а во-вторых правильно определить номер столбца. Если в таблице два столбца, а при вводе формулы в третьем аргументе указано число, которое больше двух, то результатом вычисления функции будет ошибка типа #ССЫЛКА!, а если меньше единицы, то #ЗНАЧ!
Число отформатировано как текст
В первой таблице, в ячейке с адресом «A2» число 2551 отформатировано как текст, а во второй таблице, в ячейке «D3» записано числовое значение 2551. Поскольку число 2551 не равно тексту 2551, функция выдает ошибку #Н/Д.
При поиске числовых значений, необходимо позаботиться о том, чтобы и в таблице эти значения были также числовыми.
Различие в написании текстовых значений
В примере, который приведен выше, поиск в таблице осуществляется по табельному номеру, то есть по числовому значению. На практике же, вместо числовых значений могут использоваться значения текстовые. При работе с текстом нужно учитывать тот факт, что ВПР различает буквы кириллицы и латиницы, а регистр не имеет значения.
В ячейке с адресом «A2» написана буква кириллицы, а в «D3» — буква латиницы, в результате ошибка.
Если написание искомых значений различается в разных таблицах, то для корректной работы функции необходимо привести их к одинаковому виду. Лишние пробелы и другие непечатаемые символы также могут привести к неверному либо непредсказуемому результату.
ЛОЖЬ и ИСТИНА
Аргумент «Интервальный_просмотр» не является обязательным для функции ВПР. Этот аргумент может принимать два значения ЛОЖЬ (если необходимо найти точное совпадение) и ИСТИНА (если необходимо найти приблизительное совпадение).
Этот момент не всегда понятен пользователям, поэтому требует уточнения, которое проще всего показать на примере.
В последнем аргументе формулы установлено значение «ИСТИНА», что соответствует приблизительному поиску. Искомым значением является число 2552, но в таблице (той, которая расположена слева) искомое значение отсутствует, точного совпадения нет, поэтому функция ищет ближайшее меньшее число, то есть 2551 и возвращает значение «Иван».
Если искомое значение – это текст, то при интервальном просмотре, соответствующем значению «ИСТИНА» и при отсутствии точного совпадения, функция будет искать ближайшее меньшее значение по алфавиту.
Искомое значение – Петр, но в таблице нет значения Петр, поэтому функция ищет ближайшее меньшее значение по алфавиту. Буква «Ф» в алфавите находится после буквы «П», а вот «И» — до буквы «П», поэтому она и является меньшим значением. Соответственно результатом функции будет значение «Иванов».
В случае, когда необходимо найти неточное совпадение с текстовым значением, необходимо использовать интервальный просмотр «ЛОЖЬ», а к искомому значению подставлять специальные символы совпадения (?-одиночный символ и *-произвольная последовательность символов). Тильда (
) ставится в том случае, если необходимо найти сами знаки (?) и (*).
Несколько условий в ВПР
Встречаются случаи, когда поиск необходимо осуществить по нескольким значениям. Для этого необходимо в обоих таблицах произвести сцепку нужных значений при помощи текстовой функции «Сцепить» и уже сцепленное значение указывать в качестве искомого значения.
Ограничения в функции ВПР
Функция «ВПР» позволяет решать широкий круг задач, однако имеет ряд ограничений. В случаях, когда в таблице несколько совпадений с искомым значением функция позволяет определить только первое (либо последнее, в зависимости от того как отсортирована таблица) совпадение.
VBA-аналог функции ВПР
Когда использование функции ВПР невозможно по каким-то причинам, либо функция выдает не тот результат, который нужен, можно воспользоваться надстройкой для Excel, которая по аналогии с формулой ВПР позволяет производить поиск и подстановку значений из одной таблицы в другую.
Аналогично функции ВПР, в диалоговом окне надстройки необходимо задать несколько параметров:
При выборе опции «Точное совпадение», программа находит в таблице точное равенство, при выборе опции «Неточное совпадение», программа приводит сравниваемые значения к нижнему регистру, удаляет лишние пробелы и определяет не равенство, а содержание искомого значения в значениях таблицы. При необходимости можно определять не только первое, а любое заданное равенство, если таковых несколько, либо вообще все совпадения через запятую.
Для удобства определения номеров столбцов в диалоговом окне предусмотрена возможность переключения с текстовых названий столбцов на числовые и обратно. Кроме того, реализована возможность сохранения настроек диалогового окна для следующих сеансов работы.
Лабораторная работа 4 Excel
Цель работы: Научить студентов использовать готовые функции при решении задач экономического профиля с помощью табличного процессора на персональном компьютере.
Краткий комментарий
Функция ВПР – это функция работы со справочниками из категории «Ссылки и массивы». Она ищет значение в первом столбце массива таблицы и возвращает значение в той же строке из другого столбца массива таблицы. Синтаксис написания функции ВПР:
ВПР(искомое_значение;таблица;номер_столбца),
где — искомое_значение — значение, которое должно быть найдено в первом столбце табличного массива;
таблица — два или более столбцов данных. Можно использовать ссылку на диапазон или имя диапазона. Значения в первом столбце аргумента «таблица» — это значения, в которых выполняется поиск аргумента «искомое_значение». Эти значения могут быть текстовыми, числовыми или логическими. Текстовые значения в нижнем и верхнем регистре считаются эквивалентными.
номер_столбца — номер столбца в аргументе «таблица», из которого возвращается соответствующее значение. Если номер_столбца = 2, то возвращается значение из второго столбца таблицы; если номер_столбца = 3 — значение из третьего столбца таблицы и т.д.
Практическое задание
Загрузить MS Excel 2007.
На Листе 1 рабочей книги на основании данных, представленных на рис. 4.1, создать таблицу. Установить при этом достаточную ширину столбцов и высоту строк, в «шапке» таблицы выполнить центрирование заголовков. Оформить таблицу линиями.
Рис. 4.1 Учет надоя молока за три дня
На Листе 2 Рабочей книги создать таблицу по образцу (Рис. 4.2).
Рис. 4.2 Справочник доярок
Используя функцию ВПР (из категории «Ссылки и массивы»), каждому табельному номеру (Рис. 4.1) найти соответствующее ФИО из таблицы (Рис. 4.2) и поместить в соответствующую графу, а также каждому табельному номеру – соответствующий процент доплаты и поместить в графу «Доплата за мастерство, %» (Рис. 4.1).
Для этого необходимо вызвать Мастер функций, где на первом шаге отыскать функцию ВПР в категории Ссылки и массивы (Рис. 4.3).
Рис. 4.3 Мастер функций 1 шаг
А затем, перейдя на второй шаг Мастера функций ввести аргумент данной функции (Рис. 4.4)
Рис. 4.4 Мастер функций 2 шаг
Протащить формулу по столбцу.
Выполнить расчет: «Сумма оплаты» = «Количество» * «Расценку» (взять для расчета 110 руб.); «Сумма доплаты» = «Сумма оплаты» * «Процент доплаты за мастерство» / 100; «Сумма всего» = «Сумма оплаты» + «Сумма доплаты» (для отображения результата использовать числовой формат, ноль десятичных знаков и с разделителем разрядов).
Сохранить документ в фамильной папке под именем лр4зад1.
На Листе 3 рабочей книги на основании данных, представленных на рис. 4.5, создать таблицу по образцу:
Рис. 4.5 Расчет сравнительной эффективности суточных рационов по содержанию переваримого протеина
На листе 4 Рабочей книги создать таблицу по образцу рис. 4.6.
Рис. 4.6 Справочник кормов
Используя функцию ВПР (из категории «Ссылки и массивы»), каждому коду корма (Рис. 4.5) найти соответствующее наименование вида корма из таблицы (Рис. 4.6) и поместить в графу «Вид корма».
Аналогично заполнить столбец «Содержание в одном грамме корма переваримого протеина, г» по первому и второму вариантам рационов.
Посчитать столбец «Количество переваримого протеина» по формуле: «Количество переваримого протеина, г» = «Содержание в одном грамме корма переваримого протеина, г» * «Количество корма, кг» * 1000 по обоим вариантам рационов кормления.
Посчитать итоги в конце таблицы по столбцам.
Сохранить документ в фамильной папке под именем лр4зад2.
Вопросы для самоконтроля
Что позволяет выполнить функция ВПР?
Синтаксис написания функции ВПР?
Аргумент функции ВПР?
Как работает Мастер функций с функцией ВПР?
Тут вы можете оставить комментарий к выбранному абзацу или сообщить об ошибке.
Функция ВПР. Использование функции ВПР. Excel — ВПР
Прикладная программа Excel популярна благодаря своей доступности и простоте, так как не требует особых знаний и навыков. Табличный вид предоставления информации понятен любому пользователю, а широкий набор инструментов, включающих «Мастер функции», позволяет проводить любые манипуляции и расчеты с предоставленными данными.
Одной из широко известных формул Excel является вертикальный просмотр. Использование функции ВПР на первый взгляд кажется довольно сложным, но это только поначалу.
Как работает ВПР Excel
При работе с формулой ВПР следует учитывать, что она производит поиск искомого значения исключительно по столбцам, а не по строкам. Для применения функции требуется минимальное количество столбцов — два, максимальное отсутствует.
Функция ВПР производит поиск заданного критерия, который может иметь любой формат (текстовый, числовой, денежный, по дате и времени и т. д.) в таблице. В случае нахождения записи она выдает (подставляет) значение, занесенное в той же строке, но с искомого столбца таблицы, то есть соответствующее заданному критерию. Если искомое значение не находится, то выдается ошибка #Н/Д (в англоязычном варианте #N/А).
Необходимость использования
Функция ВПР приходит на помощь оператору, когда требуется быстро найти и применить в дальнейших расчетах, анализе или прогнозе определенное значение из таблицы больших размеров. Главное при использовании данной формулы — следить, чтобы заданная область поиска была правильно выбрана. Она должна включать все записи, то есть начиная с первой по последнюю.
Самый частый случай применения ВПР (функция Excel) — это сравнение или добавление данных, находящихся в двух таблицах, при использовании определенного критерия. Причем диапазоны поиска могут быть большими и вмещать тысячи полей, размещаться на разных листах или книгах.
Показана функция ВПР, как пользоваться ею, как проводить расчеты, в качестве примера на рисунке выше. Здесь рассматривается таблица размеров розничных продаж в зависимости от региона и менеджера. Критерием поиска служит конкретный менеджер (его имя и фамилия), а искомым значением является сумма его продаж.
В результате работы функции ВПР (VLOOKUP) формируется новая таблица, в которой конкретному искомому менеджеру быстро сопоставляется его сумма продаж.
Алгоритм заполнения формулы
Расположена формула ВПР во вкладке «Мастер функций» и разделе «Ссылки и массивы». Диалоговое окно функции имеет следующий вид:
Аргументы в формулу вносятся в порядке очереди:
Пример использования функции
Функция ВПР пример использования может иметь следующий: при ведении дел торгового предприятия в таблицах Excel в столбце А записано наименование продукции, а в колонке В — соответствующая цена. Для составления предложения в столбце С нужно отыскать стоимость на определенный продукт, которую требуется вывести в колонке Д.
Наглядный пример организации таблицы
А В С Д продукт 1 90 продукт 3 60 продукт 2 120 продукт 1 90 продукт 3 60 продукт 4 100 продукт 4 100 продукт 2 120 Формула, записанная в Д, будет выглядеть так: =ВПР (С1; А1:В5; 2; 0), то есть =ВПР (искомое значение; диапазон данных таблицы; порядковый номер столбца; 0). В качестве четвертого аргумента вместо 0 можно использовать ЛОЖЬ.
Для заполнения таблицы предложения полученную формулу необходимо скопировать на весь столбец Д.
Закрепить область рабочего диапазона данных можно при помощи абсолютных ссылок. Для этого вручную проставляются знаки $ перед буквенными и численными значениями адресов крайних левых и правых ячеек таблицы. В нашем случае формула принимает вид: =ВПР (С1; $А$1:$В$5; 2; 0).
Ошибки при использовании
Функция ВПР не работает, и тогда появляется сообщение в столбце вывода результата об ошибке (#N/A или #Н/Д). Это происходит в таких случаях:
Причем при организации новой сводной таблицы заданные искомые критерии могут находиться в любом порядке и последовательности и не обязательно вмещаться полным списком (частичная выборка).
Особенности использования в качестве интервального просмотра 1 или ИСТИНЫ
Ошибка под №5 является довольно распространенной и наглядно изображена на рисунке ниже.
В данном примере список имен согласно нумерации отсортирован не по возрастанию, а по ниспадающему значению. Причем в качестве интервального просмотра использован критерий ИСТИНА (1), который сразу прерывает поиск при обнаружении значения большего, чем искомое, поэтому выдается ошибка.
При применении 1 или ИСТИНЫ в четвертом аргументе нужно следить, чтобы столбец с искомыми критериями был отсортирован по возрастанию. При использовании 0 или ЛЖИ данная необходимость отпадает, но также отсутствует тогда возможность интервального просмотра.
Просто следует учитывать, что особенно важно сортировать интервальные таблицы. Иначе функция ВПР будет выводить в ячейки неправильные данные.
Другие нюансы при работе с функцией ВПР
Для удобства работы с такой формулой можно озаглавить диапазон таблицы, в которой проводится поиск (второй аргумент), как это показано на рисунке.
В данном случае область таблицы продаж озаглавлена. Для этого выделяется таблица, за исключением заголовков столбцов, и в поле имени (слева под панелью вкладок) присваивается ей название.
Другой вариант — озаглавить — подразумевает выделение диапазона данных, потом переход в меню «Вставка»- «Имя»- «Присвоить».
Для того чтобы использовать данные, размещенные на другом листе рабочей книги, при помощи функции ВПР, необходимо во втором аргументе формулы прописать расположение диапазона данных. Например, =ВПР (А1; Лист2!$А$1:$В$5; 2; 0), где Лист2! — является ссылкой на требуемый лист книги, а $А$1:$В$5 — адрес диапазона поиска данных.
Пример организации учебного процесса с ВПР
Довольно удобно в Excel ВПР-функцию применять не только фирмам, занимающимся торговлей, но и учебным учреждениям для оптимизации процесса сопоставления учеников (студентов) с их оценками. Примеры данных задач показаны на рисунках ниже.
Существуют две таблицы со списками студентов. Одна с их оценками, вторая указывает возраст. Необходимо сопоставить обе таблицы так, чтобы наравне с возрастом учащихся выводились и их оценки, то есть ввести дополнительный столбец во втором списке.
Функция ВПР отлично справляется с решением данной задачи. В столбце G под заголовком «Оценки» записывается соответствующая формула: =ВПР (Е4, В3:С13, 2, 0). Ее нужно скопировать на всю колонку таблицы.
В результате выполнения функция ВПР выдаст оценки, полученные определенными студентами.
Пример организации поисковой системы с ВПР
Еще один пример применения функции ВПР — это организация поисковой системы, когда в базе данных согласно заданному критерию следует найти соответствующее ему значение. Так, на рисунке показан список с кличками животных и их принадлежность к определенному виду.
При помощи ВПР создается новая таблица, в которой легко найти по кличке животного его вид. Актуальны подобные поисковые системы при работе с большими списками. Для того чтобы вручную не пересматривать все записи, можно быстро воспользоваться поиском и получить требуемый результат.
Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Четыре способа использования ВПР с несколькими условиями
Все мы знаем насколько полезной является функция ВПР. Наверное, половина всех действий в Excel выполняется с помощью нее. Однако данная функция обладает рядом ограничений. Например, ВПР просматривает только крайний левый столбец в таблице или ищет только одно условие. Но что, если нам необходимо вернуть значение, соответствующее двум условиям. В таком случае нам придется прибегнуть к некоторым хитростям. Об этих хитростях мы и поговорим в сегодняшней статье.
Итак, мы с вами рассмотрим четыре варианта создания подстановочной функции с двумя условиями:
Ну а начнем мы с вами с самого простого.
Использование дополнительной колонки
В большинстве случаев сложные проблемы становятся проще и более управляемыми, если их разбить на маленькие кусочки. Тоже самое касается при построении формул в Excel.
Рассмотрим классический пример. У нас имеется таблица с продажами по месяцам и городам. И нам необходимо определить значение продаж, соответствующее двум условиям: месяц – Февраль и город – Самара.
Использование функции ВПР в классическом виде нам не поможет, так как она сможет вернуть значение, соответствующее только одному условию. Из положения нам поможет выйти дополнительный столбец, в котором мы объединим значения столбцов Месяц и Город. Для этого в ячейке А2 прописываем формулу =B2&C2 и протягиваем данную формулу до ячейки А13. Теперь мы сможем использовать значения столбца А, чтобы вернуть необходимое значение. Прописываем в ячейке G3 формулу:
Данная формула объединяет два условия ячеек G1 и G2 в одну строку и просматривает его в столбце А. После того, как нужное условие было обнаружено, формула возвращает значение с четвертого столбца таблицы A1:D13, т.е. столбца Продажи.
Использование функции ВЫБОР для создания новой таблицы просмотра
В случае если по каким-либо причинам использование дополнительного столбца для нас является не вариантом, мы можем использовать формулу массива.
Использование функции ВЫБОР подразумевает создание новой таблицы для просмотра, в котором значения столбцов Месяц и Город уже объединены. Наша формула будет выглядеть следующим образом:
Основной момент данной формулы заключается в части ВЫБОР(<1;2>;B2:B13&C2:C13;D2:D13), который делает две вещи:
Результатом работы данной функции будет таблица, которая выглядит следующим образом:
Теперь формула стала более понятной.
ВАЖНО: Так как мы использовали формулу массива, по окончании ввода формулы нажмите Ctrl+Shift+Enter, чтобы дать знать программе о наших намерениях. После нажатия данной комбинации клавиш, программа автоматически установит фигурные скобки в начале и в конце формулы.
Использование функций ИНДЕКС и ПОИСКПОЗ
Третий способ, который мы с вами рассмотрим, также предполагает использование формулы массива и задействует функции ИНДЕКС и ПОИСКПОЗ.
Формула будет выглядеть следующим образом.
Давайте разберем, что делает каждая часть данной формулы.
Сначала рассмотрим функцию ПОИСКПОЗ(1;(B2:B13=G1)*(C2:C13=G2);0). В данном случае последовательно сравнивается значение ячейки G1 с каждым значением ячеек диапазона B2:B13 и возвращается ИСТИНА, если значения совпадают и ЛОЖЬ, если нет. Такое же сравнение производится со значением ячейки G2 и диапазоном C2:C13. Далее мы сравниваем оба эти массива, состоящих из ИСТИНА и ЛОЖЬ. Комбинация ИСТИНА * ИСТИНА дает нам результат 1 (ИСТИНА). Давайте посмотрим на картинку ниже, которая поможет объяснить принцип работы более наглядно.
Теперь мы можем сказать, где находится строка, удовлетворяющая обоим условиям. Функция ПОИСКПОЗ отыскивает положение 1 в результирующем массиве и возвращает 6, так как единица встречается в шестой строке. Далее функция ИНДЕКС возвращает значение шестой строки диапазона D2:D13.
Использование СУММПРОИЗВ
СУММПРОИЗВ одна из самых мощных формул Excel. У меня даже есть отдельная статья, посвященная данной формуле. Наш четвертый способ использовании нескольких условий заключается в написании формулы с функцией СУММПРОИЗВ. И выглядеть она будет следующим образом:
Принцип работы данной формулы схож с принципом работы предыдущего подхода. Создается виртуальная таблица, в которой сравниваются значения ячеек G1 и G2 с диапазонами B2:B13 и С2:С13 соответственно. Далее оба этих массива сопоставляются и получается массив из единиц и нулей, где единица присваивается той строке, в которой оба условия совпали. Далее данный виртуальный массив перемножается на диапазон D2:D13. Так как в нашем виртуальном массиве будет только одна единица в шестой строке, формула вернёт результат 189.
Данная функция не будет работать, если в диапазоне D2:D13 имеются текстовые значения.
Чтобы понять, как работает данная формула, рекомендую прочитать статью о функции СУММПРОИЗ.
Итак, какой же способ использовать? Хотя все они работают стабильно, я предпочитаю первый способ. В своей ежедневной работе, я предпочитаю работать с файлами, которые просты для понимания и поддаются изменениям. Оба эти требования отвечают условиям первого подхода.
Для более лучшего понимания формул, вы можете скачать рабочую книгу с примерами, разобранными в сегодняшней статье.
Вам также могут быть интересны следующие статьи
11 комментариев
А я пользовался только одной — самой простой.
Автору спасибо за обзор! Очень часто приходится пользоваться в работе этой функцией.
