Совет: Попробуйте использовать новую функцию кслукуп — улучшенную версию функции ВПР, которая работает в любом направлении и возвращает точные совпадения по умолчанию, упрощая и удобную в использовании, чем его предшественник.
ФУНКЦИЯ ВПР используется, если нужно найти элементы в таблице или диапазоне по строкам. Например, можно найти цену на автомобильную часть по номеру детали или получить имя сотрудника на основе его кода сотрудника.
Совет: Чтобы получить дополнительную справку о функции ВПР, просмотрите эти видео с YouTube от экспертов сообщества Excel!
Самая простая функция ВПР означает следующее:
= ВПР (необходимые условия для поиска, номер столбца в диапазоне, содержащий возвращаемое значение, возвращаемое приближенное или точное соответствие — обозначено как 1/истина или 0/ложь).
Совет: Секрет функции ВПР состоит в организации данных таким образом, чтобы искомое значение (Фрукт) отображалось слева от возвращаемого значения, которое нужно найти (Количество).
Используйте функцию ВПР для поиска значения в таблице.
Значение для поиска. Значение, которое вы хотите найти, должно находиться в первом столбце диапазона ячеек, указанного в аргументе table_array .
Например, если Таблица-массив охватывает ячейки B2: D7, то lookup_value должен находиться в столбце B.
Искомое_значение может являться значением или ссылкой на ячейку.
Диапазон ячеек, в котором будет выполнен поиск искомого_значения и возвращаемого значения с помощью функции ВПР. Вы можете использовать именованный диапазон или таблицу, а вместо ссылок на ячейки можно использовать имена в аргументе.
Первый столбец в диапазоне ячеек должен содержать lookup_value. Диапазон ячеек также должен содержать возвращаемое значение, которое нужно найти.
Номер столбца (начиная с 1 для самого левого столбца table_array), содержащего возвращаемое значение.
Логическое значение, определяющее, какое совпадение должна найти функция ВПР, — приблизительное или точное.
Приближенное соответствие: 1/истина предполагает, что первый столбец в таблице отсортирован либо в числовом формате, либо в алфавитном порядке, а затем будет искать ближайшее значение. Это способ по умолчанию, если не указан другой. Например, = ВПР (90; a1: B100; 2; истина).
Точное совпадение: 0/ложь осуществляет поиск точного значения в первом столбце. Например, = ВПР («Смит»; a1: B100; 2; ложь).
Начало работы
Для построения синтаксиса функции ВПР вам потребуется следующая информация:
Значение, которое вам нужно найти, то есть искомое значение.
Диапазон, в котором находится искомое значение. Помните, что для правильной работы функции ВПР искомое значение всегда должно находиться в первом столбце диапазона. Например, если искомое значение находится в ячейке C2, диапазон должен начинаться с C.
Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона задано значение B2: D11, число B должно быть первым столбцом, а в качестве второго — «C» и т. д.
При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.
Теперь объедините все перечисленное выше аргументы следующим образом:
= ВПР (искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением, приближенное соответствие (истина) или точное совпадение (ложь)).
Примеры
Вот несколько примеров использования функции ВПР.
Пример 1
Пример 2
Пример 3
Пример 4
Пример 5
Вы можете использовать функцию ВПР для объединения нескольких таблиц в одну, если одна из них имеет общие поля. Это может быть особенно удобно, если вам нужно предоставить доступ к книге пользователям более ранних версий Excel, которые не поддерживают функции работы с данными с несколькими таблицами в качестве источников данных — путем объединения источников в одну таблицу и изменения источника данных компонента данных на новый. Таблица, функция данных может использоваться в более ранних версиях Excel (при условии, что сама функция данных поддерживается в более ранней версии).
Здесь в столбцах A-F и H есть значения или формулы, которые используют только значения на листе, а остальные столбцы используют функцию ВПР и значения столбца A (клиентский код) и столбец B (юрист) для получения данных из других таблиц.
Скопируйте таблицу с общими полями на новый лист и присвойте ей имя.
Чтобы открыть диалоговое окно Управление связями, нажмите кнопку данные > работа с данными > связей .
Для каждой из перечисленных связей обратите внимание на следующее:
Поле, связывающее таблицы (в диалоговом окне отображаются в круглых скобках). Это lookup_value формулы функции ВПР.
Имя связанной таблицы подстановки. Это table_array формулы функции ВПР.
Поле (столбец) в связанной таблице подстановки с данными, которые вы хотите добавить в новый столбец. Эти сведения не отображаются в диалоговом окне Управление связями. чтобы узнать, какое поле вы хотите извлечь, вам потребуется ознакомиться со связанной таблицей подстановки. Вы хотите отметить номер столбца (A = 1) – это col_index_num в формуле.
Чтобы добавить поле в новую таблицу, введите формулу ВПР в первом пустом столбце с помощью сведений, собранных в действии 3.
В нашем примере столбец G использует юрист ( lookup_value) для получения данных о тарифных курсах из четвертого столбца ( col_index_num = 4) из таблицы судебные тблатторнэйс ( table_array) с формулой = ВПР ([@Attorney], tbl_Attorneys, 4, ложь).
В формуле также могут использоваться ссылки на ячейки и ссылки на диапазоны. В нашем примере это было бы = ВПР (a2; «Юристы»! А:Д, 4, ложь).
Продолжайте добавлять поля, пока не будут созданы все нужные поля. Если вы пытаетесь подготовить книгу с функциями данных, которые используют несколько таблиц, измените источник данных функции данных в новой таблице.
Функция ВПР в Excel
Данная статья посвящена функции ВПР. В ней будет рассмотрена пошаговая инструкция функции ВПР, под названием «Функция ВПР в Excel для чайников». В данной статье мы подробно рассмотрим описание, синтаксис и примеры функции ВПР в Excel. А также рассмотрим, как использовать функцию ВПР с несколькими условиями и разберем основные ошибки, почему не работает функция ВПР.
Синтаксис и описание функции ВПР в Excel
Итак, так как второе название этой статьи «Функция ВПР в Excel для чайников», начнем с того что узнаем, что же такое функция ВПР и что она делает? Функция ВПР на английском VLOOKUP, ищет указанное значение и возвращает соответствующее значение из другого столбца.
Как работает функция ВПР? Функция ВПР в Excel выполняет поиск по вашим спискам данных на основе уникального идентификатора и предоставляет вам часть информации, связанную с этим уникальным идентификатором.
Буква «В» в ВПР означает «вертикальный». Она используется для дифференциации функции ВПР и ГПР, которая ищет значение в верхней строке массива («Г» обозначает «горизонтальный»).
Функция ВПР доступна во всех версиях Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003.
Синтаксис функции ВПР выглядит следующим образом:
Как видите, функция ВПР имеет 4 параметра или аргумента. Первые три параметра обязательные, последний — необязательный.
искомое_значение – это значение для поиска.
Это может быть либо значение (число, дата или текст), либо ссылка на ячейку (ссылка на ячейку, содержащую значение поиска), или значение, возвращаемое некоторой другой функцией Excel. Например:
Поиск числа : =ВПР(40; A2:B15; 2) — формула будет искать число 40.
Поиск текста : =ВПР(«яблоки»; A2:B15; 2) — формула будет искать текст «яблоки». Обратите внимание, что вы всегда включаете текстовые значения в «двойные кавычки».
Поиск значения из другой ячейки : =ВПР(C2; A2:B15; 2) — формула будет искать значение в ячейке C2.
таблица — это два или более столбца данных.
Помните, что функция ВПР всегда ищет искомое значение в первом столбце таблицы. Ваш таблица может содержать различные значения, такие как текст, дата, числа или логические значения. Значения нечувствительны к регистру , что означает, что прописные и строчные буквы считаются идентичными.
Итак, наша формула =ВПР(40; A2:B15; 2) будет искать «40» в ячейках от A2 до A15, потому что A — это первый столбец таблицы A2: B15.
номер_столбца — номер столбца в таблице, из которой должно быть возвращено значение в соответствующей строке.
Самый левый столбец в указанной таблице равен 1, второй столбец — 2, третий — 3 и т. д.
Итак, теперь вы можете прочитать всю формулу =ВПР(40; A2:B15; 2). Формула ищет «40» в ячейках от A2 до A15 и возвращает соответствующее значение из столбца B (потому что B является вторым столбцом в указанной таблице A2:B15).
4. интервальный_просмотр определяет, ищете ли вы точное соответствие (ЛОЖЬ) или приблизительное соответствие (ИСТИНА или опущено). Этот последний параметр является необязательным, но очень важным.
Функция ВПР в Excel примеры
Теперь давайте рассмотрим несколько примеров использования функции ВПР для реальных данных.
Функция ВПР на разных листах
На практике формулы ВПР редко используются для поиска данных на одном листе. Чаще всего вам придется искать и вытаскивать соответствующие данные с другого листа.
Чтобы использовать функцию ВПР с другого листа Excel, вы должны ввести имя рабочего листа и восклицательный знак в аргументе таблица перед диапазоном ячеек, например, =ВПР(40;Лист2!A2:B15;2). Формула указывает, что диапазон поиска A2:B15 находится в Лист2.
Конечно, вам не нужно вводить имя листа вручную. Просто начните вводить формулу, и когда дело дойдет до аргумента таблица, переключитесь на рабочий лист поиска и выберите диапазон с помощью мыши.
Формула, которую вы видите на изображении ниже, ищет текст в ячейке А2 (« Продукт 3 ») в столбце A (1-й столбец диапазона поиска A2:B9) на листе « Цены »:
Функция ВПР в Excel – Функция ВПР на разных листах
Обратите внимание, что рекомендуется использовать абсолютные ссылки на ячейки (со знаком $) в аргументе таблица. Это предотвратит изменение диапазона поиска при копировании формулы ВПР в другие ячейки.
Как использовать именованный диапазон или таблицу в формулах ВПР
Если вы собираетесь использовать один и тот же диапазон поиска в нескольких формулах ВПР, вы можете создать именованный диапазон для него и ввести имя непосредственно в аргументе таблица вашей формулы ВПР.
Чтобы создать именованный диапазон, просто выберите ячейки и введите любое имя в поле « Имя », слева от панели « Формула ».
Функция ВПР в Excel – Присвоение имени диапазону
Теперь вы можете написать следующую формулу ВПР, чтобы получить цену Продукта 1:
Функция ВПР в Excel – Пример функции ВПР с именем диапазона
Большинство имен диапазонов в Excel применяются ко всей книге, поэтому вам не нужно указывать имя рабочего листа, даже если ваш диапазон поиска находится на другом листе. Такие формулы гораздо более понятны. Кроме того, использование именованных диапазонов может быть хорошей альтернативой абсолютным ссылкам на ячейки. Поскольку именованный диапазон не изменяется, когда формула копируется в другие ячейки, и вы можете быть уверены, что ваш диапазон поиска всегда останется верным.
Если вы преобразовали диапазон ячеек в полнофункциональную таблицу Excel (вкладка «Вставка» —> «Таблица»), вы можете выбрать диапазон поиска с помощью мыши, а Microsoft Excel автоматически добавит имена колонок или имя таблицы в формулу:
Функция ВПР в Excel – Пример функции ВПР с именем таблицы
Полная формула может выглядеть примерно так:
или даже =ВПР(«Продукт 1»;Таблица6;2).
Как и именованные диапазоны, имена столбцов являются постоянными, а ссылки на ячейки не изменятся независимо от того, где копируется формула ВПР.
Функция ВПР с несколькими условиями
Рассмотрим пример функции ВПР с несколькими условиями. У нас есть следующие исходные данные:
Функция ВПР в Excel – Таблица исходных данных
Пусть нам необходимо использовать функцию ВПР с несколькими условиями. Например, для поиска цены товара по двумя критериями: названию продукта и его типу.
Для того чтобы использовать функцию ВПР с несколькими условиями необходимо вставить в начало дополнительный столбец, который будет хранить информацию с названием и типом товара.
Итак на листе « Цены » вставляем столбец и в ячейке А2 вводим следующую формулу:
При помощи этой формулы мы сцепляем значение столбца « Продукт » и « Тип ». Заполняем все ячейки.
Теперь таблица для поиска выглядит следующим образом:
Функция ВПР в Excel – Добавление вспомогательного столбца
Теперь в ячейке С2 на листе « Продажи » напишем следующую формулу ВПР:
Заполняем для остальных ячеек и в результате получаем цены для каждого продукта в соответствии с типом:
Функция ВПР в Excel – Пример ВПР с несколькими условиями
Теперь разберем ошибки функции ВПР.
Почему не работает функция ВПР
В этой части статьи мы рассмотрим почему не работает функция ВПР и возможные ошибки функции ВПР.
Неверное расположение столбца, по которому происходит поиск
Столбец таблицы, по которому происходит поиск ОБЯЗАТЕЛЬНО должен быть крайним левым.
Перенесите столбец, по которому происходит поиск в крайнее левое положение таблицы.
Или создайте вспомогательный дублирующий столбец, слева в таблице.
Не закреплен диапазон таблицы
Если первое значение было выведено правильно, а после протягивания формулы ВПР в некоторых ячейках встречается ошибка #Н/Д, то диапазон таблицы не закреплен.
Используйте абсолютные ссылки ($) для закрепления диапазона таблицы, чтобы при заполнении формула использовала один и тот же диапазон.
Или используйте именованный диапазон
Не удалось найти точное совпадение (если в интервальном просмотре выбран поиск точного значения (0)
В интервальном просмотре выполняется поиск ближайшего значения (1), а таблица , по которой происходит поиск не отсортирована.
Отсортируйте первый столбец таблицы по возрастанию наименований.
Данные содержат лишние пробелы, недопустимые кавычки или непечатаемые символы.
Используйте функции ПЕЧСИМВ или СЖПРОБЕЛЫ.
Значение номер столбца превышает число столбцов в таблице
Проверьте номер столбца, содержащий возвращаемое значение.
В формуле пропущены кавычки
Если вы используете в качестве искомого значения не ссылку на ячейку, а текст, то его необходимо заключить в кавычки.
=ВПР(«Продукт 1»; Цены!$A$2:$B$8;2;0)
Надеюсь, что теперь даже для чайников функция ВПР в Excel будет понятна.
ВПР по двум и более критериям
Наверняка все, кто знаком с функцией ВПР знают, что она осуществляет поиск заданных значений исключительно в левом столбце указанной таблицы(подробнее про ВПР можно прочитать в статье: Как найти значение в другой таблице или сила ВПР). Так же многие знают, что ВПР ищет только на основании одного значения.
Для большего понимания смысла статьи рекомендую сначала скачать файл с примером данных и формул: Скачать пример:
Например, имеется файл с таблицей такого вида: И необходимо получить сумму выручки не только на основании салона, но и на основании бренда. При этом делать это автоматически, например для получения данных в такую табличку: Т.е. в ячейке B2 из выпадающего списка выбирается Салон, а из B3 — Бренд(подробнее про выпадающие списки можно прочесть в статье Выпадающие списки). И в зависимости от выбора должна рассчитаться сумма Выручки и должен проставиться комментарий. В приложенном к статье файле исходная таблица находится на листе » Отчет сентябрь «, а вторая(с выбором) на листе » Выбор «. На самом деле это не сильно принципиально, просто будет более понятна логика формул, приведенных ниже. Сумму по двум критериям можно найти при помощи той же СУММЕСЛИМН (SUMIFS) : =СУММЕСЛИМН(‘Отчет сентябрь’!$C$2:$C$67;’Отчет сентябрь’!$A$2:$A$67; B2 ;’Отчет сентябрь’!$B$2:$B$67; B3 ) =SUMIFS(‘Отчет сентябрь’!$C$2:$C$67,’Отчет сентябрь’!$A$2:$A$67,Выбор!B2,’Отчет сентябрь’!$B$2:$B$67,Выбор!B3) Подробнее про поиск сумм по двум и более критериям можно прочитать в статье Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям. А вот с комментарием сложнее — там содержится текст и СУММЕСЛИМН ничего не найдет. Да и в случае, если в исходных данных есть задвоения и суммировать по определению ничего не надо СУММЕСЛИМН будет не лучшим вариантом — ведь она вернет сумму всех ячеек, удовлетворяющих условиям. А это, повторюсь — не всегда нужно. Здесь на помощь придет родственная с ВПР функция — ПОИСКПОЗ (MATCH) : =ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ( B2 & B3 ;’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67;0)) =INDEX(‘Отчет сентябрь’!$D$2:$D$67,MATCH(B2&B3,’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67,0)) Эта функция является формулой массива. Это означает, что вводить её в ячейки надо нажатием не просто Enter, а сочетанием трех клавиш — Ctrl+Shift+Enter. Теперь рассмотрим подробнее принцип работы этой формулы, чтобы можно было применить на любых данных. На самом деле принцип не так уж сложен. Основной упор сделан на эти две «связки»:
B2 & B3 — здесь объединяем значение выбранного Салона(Санкт-Петербург) и Бренда(Ниссан) в одну строку, чтобы получилось «Санкт-ПетербургНиссан». За объединение двух значений отвечает амперсанд(&).
‘Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67 — а здесь мы последовательно объединяем в одну строку значения двух столбцов исходных данных — Салон и Бренд. Т.е. в результате мы получим массив объединенных значений: МоскваBMW, ВоронежBMW, СамараЛексус и т.д. (именно это объединение требует, чтобы формула вводилась как формула массива) . И уже среди этих значений ищем «Санкт-ПетербургНиссан». При нахождении совпадения ПОИСКПОЗ вернет позицию строки, в которой нашел. И передаст её в ИНДЕКС. А т.к. ИНДЕКС в нашем случае возвращает значение из заданной строки указанного массива, то мы получаем требуемое.
По шагам это будет выглядеть так:
сначала искомые значения будут объединены в один =ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ( B2 & B3 ;’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67;0)) => =ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ( Санкт-Петербург & Ниссан ;’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67;0)) => =ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ( Санкт-Петербург Ниссан ;’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67;0)) => далее так же объединяем построчно значения всех столбцов для поиска значений =ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ( Санкт-Петербург Ниссан ;<"МоскваBMW":"ВоронежBMW":"СамараЛексус". >;0)) => =ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;4) => только допы
Здесь важно следить за тем, чтобы значения в исходном массиве для поиска объединялись в том же порядке, что и искомые значения. На приложенном примере, если первым аргументом(искомое_значение) в ПОИСКПОЗ мы указали сначала Салон( B2 ) , а потом Бренд( B3 ) , то и вторым аргументом(просматриваемый_массив) мы должны указать два столбца в том же порядке — сначала Салон( ‘Отчет сентябрь’!$A$2:$A$67 ), а уже потом Бренд( ‘Отчет сентябрь’!$B$2:$B$67 ). Если их перепутать ошибки как таковой это не вызовет, но и значения найдены не будут. Так же стоит учитывать, что такая формула будет рассчитываться дольше обычной, поэтому не стоит указывать столбцы полностью( ‘Отчет сентябрь’!$A:$A ), т.к. это может повлечь за собой расчет всего одной формулы непозволительно долгое время. При этом очевидно, что просматривать значения можно не только из смежных столбцов, а из любых и уж точно они могут быть и первыми, и последними. И значения возвращаемые могут так же располагаться в любом столбце таблицы. И также хочу заметить, что это в примере используется только два критерия — реально их можно сделать и три, и пять, и десять. Объединяем сколько надо, указываем нужное кол-во столбцов так же с объединением и все. Но тут надо понимать, что в некоторых случаях будет оптимальнее добавить в исходные данные(те, где ищем) еще один столбец, в котором записать все данные, объединив их. Т.е. опять же на примере файла из статьи можно на листе «Отчет сентябрь» добавить формулу в столбец E: = A2 & B2 и затем искать можно будет обычной формулой: =ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ( B2 & B3 ;’Отчет сентябрь’!$E$2:$E$67;0)) =INDEX(‘Отчет сентябрь’!$D$2:$D$67,MATCH(B2&B3,’Отчет сентябрь’!$E$2:$E$67,0)) Больше нюансов, о которых я бы хотел рассказать, нет. Все стандартно, как для обычной связки ИНДЕКС(ПОИСКПОЗ .
В приложенном к статье примере приведен пример и с функцией СУММЕСЛИМН и с функцией ПОИСКПОЗ. Скачать пример:
P.S. Если кого-то пугает тот факт, что формулу надо вводить как формулу массива(Ctrl+Shift+Enter), то можно модифицировать формулу так: =ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;СУММПРОИЗВ(МАКС(( B2 =’Отчет сентябрь’!$A$2:$A$67)*( B3 =’Отчет сентябрь’!$B$2:$B$67)*(СТРОКА( $B$2:$B$67 )-1)))) =INDEX(‘Отчет сентябрь’!$D$2:$D$67,SUMPRODUCT(MAX((B2=’Отчет сентябрь’!$A$2:$A$67)*(B3=’Отчет сентябрь’!$B$2:$B$67)*(ROW($B$2:$B$67)-1)))) Здесь вместо ПОИСКПОЗ роль поиска номера строки в массиве ‘Отчет сентябрь’!$D$2:$D$67 играет СУММПРОИЗВ. Сам принцип работы СУММПРОИЗВ я описывал в этой статье — Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям. К этому могу добавить лишь некоторые разъяснения(хотя если хотите понять сам принцип — лучше потратить пару минут на статью). ( B2 =’Отчет сентябрь’!$A$2:$A$67) — здесь значение выбранного салона сверяется со списком салонов на листе Отчет сентябрь . Где значения равны получаем значение ИСТИНА(TRUE), где отличаются — ЛОЖЬ(FALSE). ( B3 =’Отчет сентябрь’!$B$2:$B$67) — здесь значение выбранного бренда сверяется со списком брендов на листе Отчет сентябрь . Где значения равны получаем значение ИСТИНА(TRUE), где отличаются — ЛОЖЬ(FALSE). В результате перемножения этих двух выражений мы получим массив 1 и 0(1 будет там, где бренд и салон совпали, 0 — где различаются), т.к. ИСТИНА для Excel по сути =1, а ЛОЖЬ =0. Далее полученный массив единиц и нулей перемножаем на выражение: СТРОКА( $B$2:$B$67 ) . Это выражение дает нам массив номеров строк т.к. в выражении берется номер строки на листе, а нам нужен номер в диапазоне ‘Отчет сентябрь’!$D$2:$D$67 , то мы еще вычитаем 1(т.к. диапазон у нас начинается со второй строки). С таким же успехом можно было не вычитать, а либо все диапазоны взять с первой строки, либо указать так: СТРОКА( $B$1:$B$66 ) Полученный массив строк перемножаются на массив единиц и нулей салона и бренда. В результате получим номер строки, умноженный на 1 там, где салон и бренд равны искомым, и на ноль там, где отличаются. И в итоге массив номеров строки и нулей. И из этого отбирается максимальный номер строки. По сути, это уже решает проблему поиска, но если значений, подходящих под условия больше одного, то именно такой результат может быть(даже скорее всего будет) неверным. Чтобы этого избежать мы применяем функцию МАКС (MAX) , чтобы в результате из всех строк была отобрана только максимальная. Т.е. в результате мы получим не первое совпадение из всех возможных, а последнее. Так же рекомендую ознакомиться со статьей Как просмотреть этапы вычисления формул, чтобы можно было самостоятельно поразбираться в принципах работы формул.
В сети можно найти так же решения при помощи макросов(VBA), но далеко не всегда есть возможность использовать макросы в файлах.
Статья помогла? Поделись ссылкой с друзьями!
Как пользоваться функцией ВПР в Excel: пример с двумя таблицами
ВПР в Excel очень удобный и часто используемый инструмент для работы с таблицами как с базой данных и не только. Данная функция проста в освоении и очень функциональна при выполнении.
Благодаря гармоничному сочетанию простоты и функциональности ВПР пользователи активно ее используют в процессе работы с электронными таблицами. Но стоит отметить, что у данной функции достаточно много недостатков, которые ограничивают возможности. Поэтому ее иногда нужно использовать с другими функциями или вообще заменять более сложными. Для начала на готовом примере применения функции рассмотрим ее преимущества, а потом определим недостатки.
Как работает функция ВПР в Excel: пример
Функция ВПР предназначена для выборки данных из таблицы Excel по определенным критериям поиска. Например, если таблица состоит из двух колонок: «Наименование товара» и «Цена». Рядом находится другая таблица, которая будет искать в первой таблице по наименованию товара и получать значение соответствующей цены.
Переходим в ячейку второй таблицы под названием столбца «Цена».
Выберите «Формулы»-«Ссылки и массивы»-«ВПР». Ввести функцию ВПР можно и с помощью «мастера функций». Для этого нажмите на кнопку «fx», которая находиться в начале строки формул. Или нажмите комбинацию горячих клавиш SHIFT+F3. В появившимся диалоговом окне на поле категория, выберите из выпадающего списка: «Ссылки и массивы», а потом ниже укажите на функцию.
Заполняем аргументы функции.
В поле «Исходное значение» вводим ссылку на ячейку под наименованием товара второй таблицы D3. В поле «Таблица» вводим диапазон всех значений первой таблицы A2:B7. В поле «Номер столбца» вводим значение 2, так как во втором столбце у нас находиться цена, которую мы хотим получить при поиске товара. И нажимаем ОК.
Теперь под заголовком столбца второй таблицы «Товар» введите наименования того товара по котором нам нужно узнать его цену. И нажмите Enter.
Функция позволяет нам быстро находить данные и получать по ним все необходимые значения из больших таблиц. Это похоже на работу с базами данных. Когда к базе создается запрос, а в ответ выводятся результаты, которые являются ответом на критерии запроса.
Функция ВПР в Excel и две таблицы
Немного усложним задание, изменив структуру и увеличив объем данных в таблице. Расширьте объем данных первой таблицы, добавив столбцы: «январь», «февраль», «март». Там запишем суммы продаж в первом квартале как показано на рисунке:
Как видите вторую таблицу так же нужно немного изменить, чтобы не потерять суть задачи.
Теперь нам нужно сделать выборку данных с помощью функции ВПР отдельно по товару и просуммировать продажи за первый квартал. Для этого переходим в ячейку H3 и после вызова функции заполняем ее аргументы следующим образом:
Исходное значение: G3.
Таблица: A2:E7. Диапазон нашей таблицы расширен.
Номер столбца: <3;4;5>. Нам нужно с помощью функции обращаться одновременно к нескольким столбцам, поэтому значение данного аргумента будет взято в массив фигурными скобками. А номера столбцов следует перечислять через точку с запятой.
Интервальный просмотр: ЛОЖЬ.
Чтобы значения в выбранных столбцах суммировались, тогда всю функцию нужно поместить внутрь функции СУММ(). Вся формула в целом выглядит следующим образом: =СУММ(ВПР(G3;A3:E6;<3;4;5>;ЛОЖЬ)).
После ввода данной формулы следует нажать комбинацию клавиш: CTRL+SHIFT+ENTER. Внимание! Если не нажать комбинацию этих клавиш формула будет работать ошибочно . В Excel иногда приходиться выполнять функции в массиве для этого нужно обязательно использовать клавиши: CTRL+SHIFT+ENTER при вводе функций. Тогда в строке формул все содержимое будет взято в фигурные скобки «<>», что свидетельствует о выполнении формулы в массиве.
Теперь вводите в ячейку G3 наименование товара, в ячейке H3 получаем сумму продаж в первом квартале по данному товару.
Происходит сравнение двух таблиц в Excel функцией ВПР и как только определяется совпадение запрашиваемых данных, сразу подставляется их значения для суммирования функцией СУММ. Весь процесс выполняется циклически благодаря массиву функций о чем свидетельствуют фигурные скобки в строке формул.
Примечание. Если ввести вручную крайние фигурные скобки в строку формул то это не приведет ни ка какому результату. Выполнить функцию циклическим массивом можно только через комбинацию горячих клавиш: CTRL+SHIFT+ENTER.
Стоит отметить, что главным недостатком функции ВПР является отсутствие возможности выбрать несколько одинаковых исходных значений в запросе.
Другими словами если в нашей таблице повторяются значения «груши», «яблока» мы не сможем просуммировать всех груш и яблок. Для этого нужно использовать функцию ПРОСМОТР(). Она очень похожа на ВПР но умеет хорошо работать с массивами в исходных значениях.
В появившимся диалоговом окне на поле категория, выберите из выпадающего списка: «Ссылки и массивы», а потом ниже укажите на функцию.
