Все секреты excel-функции впр (vlookup) для поиска данных в таблице и извлечения их в другую

Как использовать функцию «ВПР» для сравнения данных

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

1. Добавим второй лист с точно такой же таблицей (копировали при помощи горячих клавиш Ctrl+C и Ctrl+V).
2. Повысим стажеров до «Младший сотрудник». Эта информация будет отправной точкой для сравнения.

1. Добавим ещё один столбец в нашу старую таблицу.

1. Переходим в первую клетку нового столбца и вводим там следующую формулу.

=ВПР($B$3:$B$11;Лист2!$B$3:$E$11;4;ЛОЖЬ)

Она означает:

• $B$3:$B$11 – для поиска используются все значения первой колонки (применяются абсолютные ссылки);
• Лист2! – эти значения нужно искать на листе с указанным названием;
• $B$3:$E$11 – таблица, в которой нужно искать (диапазон ячеек);
• 4 – номер столбца в указанной области данных;
• ЛОЖЬ – искать точные совпадения.

1. Новая информация выведется в том месте, где мы указали формулу.
2. Результат будет следующим.

1. Теперь продублируйте эту формулу в остальные ячейки. Для этого нужно потянуть мышкой за правый нижний угол исходной клетки.

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

Теперь мы можем без труда определить, в записях какого сотрудника произошли изменения.

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

Вставлен столбец

Аргумент col_index_num (номер_столбца) используется функцией ВПР, чтобы указать, какую информацию необходимо извлечь из записи.

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

Столбец Quantity (Количество) был 3-м по счету, но после добавления нового столбца он стал 4-м. Однако функция ВПР автоматически не обновилась.

Решение 1

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

Решение 2

Другой вариант – вставить функцию MATCH (ПОИСКПОЗ) в аргумент col_index_num (номер_столбца) функции ВПР.

Функция ПОИСКПОЗ может быть использована для того, чтобы найти и возвратить номер требуемого столбца. Это сделает аргумент col_index_num (номер_столбца) динамичным, т.е. можно будет вставлять новые столбцы в таблицу, не влияя на работу функции ВПР.

Формула, показанная ниже, может быть использована в этом примере, чтобы решить проблему, описанную выше.

Функция ВПР в Экселе: пошаговая инструкция

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

Во второй – цены:

Если перечень товаров в обеих таблицах совпадает, то, зная магическое сочетание Ctrl+C и Ctrl+V, данные о ценах можно легко подставить к данным о количестве. Однако очередность позиций в обеих таблицах не совпадает. Тупо скопировать цены и подставить к количеству не получится.

Поэтому мы не можем прописать формулу умножения и «протянуть» вниз на все позиции.

Что делать? Надо как-то цены из второй таблицы подставить к соответствующему количеству в первой, т.е. цену товара А к количеству товара А, цену Б к количеству Б и т.д.

Вот так.

Функция ВПР в Эксель легко справится с задачей.

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

Для вызова функции с помощью Мастера нужно активировать ячейку, где будет прописана формула и нажать кнопку f(x) в самом начале строки формул. Появится диалоговое окно Мастера, где из списка всех функций нужно выбрать ВПР.

Кликаем по надписи «ВПР». Открывается следующее диалоговое окно.

Теперь нужно заполнить предлагаемые поля. В первом окошке «Искомое_значение» нужно указать критерий для ячейки, в которую мы вписываем формулу. В нашем случае это ячейка с наименованием товара «А».

Следующее поле «Таблица». В нем нужно указать диапазон данных, где будет осуществляться поиск нужных значений. В нашем случае это вторая таблица с ценой. При этом крайний левый столбец выделяемого диапазона должен содержать те самые критерии, по которым осуществляется поиск (столбец с наименованиями товаров). Затем таблица выделяется вправо минимум до того столбца, где находятся искомые значения (цены). Можно и дальше вправо выделить, но это уже ни на что не влияет. Главное, чтобы выделенная таблица начиналась со столбца с критериями и захватывала нужный столбец с данными

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

Следующее поле «Номер_столбца» — это число, на которое столбец с искомыми данными (ценами) отстоит от столбца с критерием (наименованием товара) включительно. То есть отсчет идет, начиная с самого столбца с критерием. Если у нас во второй таблице оба столбца находятся рядом, то нужно указать число 2 (первый – критерий, второй — цены). Часто бывает, что данные отстоят от критерия на 10 или 20 столбцов

Это не важно, Excel все сосчитает

Последнее поле «Интервальный_просмотр», где указывается тип поиска: точное (0) или приблизительное (1) совпадение критерия. Пока ставим 0 (или ЛОЖЬ). Второй вариант рассмотрен ниже.

Нажимаем ОК. Если все правильно и значение критерия есть в обеих таблицах, то на месте только что введенной формулы появится некоторое значение. Остается только протянуть (или просто скопировать) формулу вниз до последней строки таблицы.

Теперь легко рассчитать стоимость простым умножением количества на цену.

Формулу ВПР можно прописать вручную, набирая аргументы по порядку, и разделяя точкой с запятой (см. видеоурок ниже). 

Что такое функция ВПР в Эксель – область применения

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

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

В случаях, когда работников предприятия всего два-три, или товаров – до десятка, можно сделать все вручную. При должной внимательности работать человек будет без ошибок. Но если значений для обработки, например, тысяча, требуется автоматизация работы. Для этого в Excel существует ВПР (анг. VLOOKUP).

Примеры для наглядности: в таблицах 1,2 – исходные данные, таблице 3 – что должно получиться.

Исходные данные таблица 1

Ф. И. О.	Штраф
Петров	12 000,00 ₽
Сидоров	200,00 ₽
Иванов	38 000,00 ₽

Исходные данные таблица 2

Ф. И. О.	З.П.
Иванов	20 000 ₽
Петров	19 000 ₽
Сидоров	21 000 ₽

Объединенные данные таблица 3

Ф. И. О.	З.П.	Штраф
Иванов	20 000 ₽	38 000 ₽
Петров	19 000 ₽	12 000 ₽
Сидоров	21 000 ₽	200 ₽

Если вдруг Вы еще не знали, то советуем прочитать о том, как объединить ячейки в Exсel.

Как пользоваться функцией ВПР в Excel: примеры

Для учебных целей возьмем таблицу с данными:

Формула	Описание	Результат
	Функция ищет значение ячейки F5 в диапазоне А2:С10 и возвращает значение ячейки F5, найденное в 3 столбце, точное совпадение.
	Нам нужно найти, продавались ли 04.08.15 бананы. Если продавались, в соответствующей ячейке появится слово «Найдено». Нет – «Не найдено».
	Если «бананы» сменить на «груши», результат будет «Найдено»
	Когда функция ВПР не может найти значение, она выдает сообщение об ошибке #Н/Д. Чтобы этого избежать, используем функцию ЕСЛИОШИБКА.	Мы узнаем, были ли продажи 05.08.15
	Если необходимо осуществить поиск значения в другой книге Excel, то при заполнении аргумента «таблица» переходим в другую книгу и выделяем нужный диапазон с данными.	Мы захотели узнать, кто работал 8.06.15.
	Поиск приблизительного значения.

Это важно:

1. Функция ВПР всегда ищет данные в крайнем левом столбце таблицы со значениями.
2. Регистр не учитывается: маленькие и большие буквы для Excel одинаковы.
3. Если искомое меньше, чем минимальное значение в массиве, программа выдаст ошибку #Н/Д.
4. Если задать номер столбца 0, функция покажет #ЗНАЧ. Если третий аргумент больше числа столбцов в таблице – #ССЫЛКА.
5. Чтобы при копировании сохранялся правильный массив, применяем абсолютные ссылки (клавиша F4).

ВПР и выпадающий список

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

Сперва нужно создать сам раскрывающийся список:

1. Кликнуть левой кнопкой мыши по ячейке E2, тем самым выделив ее. 

2. Переместиться на вкладку «Данные», и там найти пункт «Проверка данных».

3. Указать источник информации. В качестве него у нас используются названия товаров. Сам тип данных выставляем – список.

После того, как мы нажмем кнопку «ОК», появится выпадающий список.

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

1. Нажимаем на кнопку fx, что позволит открыть мастер функций. В этом диалоговом окне выбирается функция ВПР.
2. В качестве первого аргумента указываем ту ячейку, в которой содержится выпадающий список. В качестве второго – диапазон с наименованиями продукции и ее стоимостью. Столбец второй. Функция, в результате, обретает такой вид. =ВПР(E8;A2:B16;2;ЛОЖЬ)

3. После нажатия клавиши «ВВОД» получаем необходимый результат.

После каждой коррекции продукции изменяется и цена.

18

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

Функция ВПР с несколькими условиями

Рассмотрим пример функции ВПР с несколькими условиями. У нас есть следующие исходные данные:

Функция ВПР в Excel – Таблица исходных данных

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

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

Итак на листе «Цены» вставляем столбец и в ячейке А2 вводим следующую формулу:

=B2&C2

При помощи этой формулы мы сцепляем значение столбца «Продукт» и «Тип». Заполняем все ячейки.

Теперь таблица для поиска выглядит следующим образом:

Функция ВПР в Excel – Добавление вспомогательного столбца

1. Теперь в ячейке С2 на листе «Продажи» напишем следующую формулу ВПР:

=ВПР(A2&B2;Цены!$A$1:$D$8;4;ЛОЖЬ)

Заполняем для остальных ячеек и в результате получаем цены для каждого продукта в соответствии с типом:

Функция ВПР в Excel – Пример ВПР с несколькими условиями

Теперь разберем ошибки функции ВПР.

Как расшифровываются параметры ВПР?

Чтобы лучше понять, что такое ВПР в Excel, давайте для начала просто попробуем создать ее на нашем рабочем листе.

Устанавливаем курсор в нужную ячейку, в которую нужно вставить цену из прайс-листа по определенному товару. Нажимаем на значок возле строки формул и в категории «Ссылки и массивы» находим ВПР.

Либо просто ставим «=» и начинаем писать название. И  тут же появляется выпадающий список подходящих функций. Выбираем нужную.

Затем в открывшемся окне заполняем все параметры. Их вы видите на рисунке выше.

• Искомое — то, что ищем.
• Таблица — диапазон ячеек, в котором ищем и из которого затем извлекаем данные.
• Номер_столбца — номер столбца диапазона, из которого возвращаются данные, если значение в первом столбце данной строки совпадает с аргументом искомое_ значение.
• Интервальный_просмотр — необязательный, но очень важный логический аргумент. О нем мы поговорим ниже.

Функция ВПР в Excel – как пользоваться

Для того чтобы таблица 1 пришла к конечному виду, в ней вписываем заголовок столбца, например «Штраф». На самом деле, это необязательно, можно написать любой текст, или оставить его незаполненным. Работать функция будет также по клику мыши в поле, где должно появиться найденное в другой таблице значение.

Теперь нужно вызвать функцию. Это можно сделать разными способами:

1. Прописать функцию вручную.
2. Нажать «Вставить функцию», выбрать «Ссылки и массивы», выбрать ВПР.
3. Открыть вкладку «Формулы», выбрать «Ссылки и массивы», выбрать ВПР.

Необходимо заполнить значения для функции ВПР

1. Искомое_значение – это то, что Эксель будет искать в другой таблице. В нашем случае, Иванов, Петров, Сидоров. Кликаем по первой фамилии в списке это ячейка А2. Удерживаем на клавиатуре клавишу Shift, после чего кликаем по последней фамилии в столбце, ячейка А4. В итоге получаем выделенный список фамилий с которым ВПР будет работать.
2. Таблица – место где будет осуществлен поиск. В нашем случае вторая таблица лежит в Лист 2. Переходим на него. Здесь необходимо выделить весь интервал поиска. Кликаем по левой верхней ячейке интервала. Удерживаем на клавиатуре Shift и нажимаем левой кнопкой мыши правую нижнюю ячейку. Нажимаем F4. Интервал поиска задан.
3. Номер_столбца – номер столбца из таблицы, в которой ищутся данные. В нашем случае – 2.
4. Интервальный_просмотр – это логическое значение. В нашем случае необходимо написать «ЛОЖЬ».
5. Нажать «ОК»
6. Теперь необходимо скопировать функцию в каждую пустую ячейку. Можно, например, сделать с помощью копировать — вставить, а можно нажать на ячейку с результатом работы функции, и в правом нижнем углу кнопкой мыши слева нажать на жирный квадратик, не отпуская левую кнопку потянуть вниз.

Результат налицо – в таблице 3 (смотреть выше).

Завершаем создание шаблона

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

Важно отметить, что ВПР может использоваться и в других ситуациях, помимо работы с базами данных. Это бывает редко, и может быть рассмотрено подробнее в будущих статьях

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

Удалим значение кода товара из ячейки A11 и значение 2 из ячейки D11. В результате созданные нами формулы сообщат об ошибке.Мы можем исправить это, разумно применив функции IF (ЕСЛИ) и ISBLANK (ЕПУСТО). Изменим нашу формулу с такого вида:

на такой вид:

Нам нужно скопировать формулы из ячеек B11, E11 и F11 на оставшиеся строки нашего шаблона

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

Другой способ, более продвинутый, это создать именованный диапазон для всех ячеек, вмещающих нашу базу данных (назовём его Products) и использовать имя диапазона вместо ссылок на ячейки. Формула превратится из такой:

… в такую:

…теперь можно смело копировать формулы в ячейки остальных строк нашего шаблона.

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

Если подойти к работе с максимальной ответственностью, то можно создать базу данных всех наших клиентов еще на одном листе документа. А затем вводить идентификатор клиента в ячейку F5, чтобы автоматически заполнять ячейки B6, B7 и B8 данными о клиенте.

Что такое ВПР и как ею пользоваться?

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

Для того чтобы функция ВПР работала корректно, обратите внимание на наличие в заголовках вашей таблицы объединённых ячеек. Если таковые имеются, вам необходимо будет их разбить

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

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

Теперь активируйте первую ячейку в блоке «Цена» и вызовите «Мастер функций». Сделать это можно, нажав на кнопку «fx», расположенную перед строкой формул, или зажав комбинацию клавиш «Shift+F3». В открывшемся диалоговом окне отыщите категорию «Ссылки и массивы». Здесь нас не интересует ничего кроме функции ВПР. Выберите её и нажмите «ОК». Кстати, следует сказать, что функция VLOOKUP может быть вызвана через вкладку «Формулы», в выпадающем списке которой также находится категория «Ссылки и массивы».

После активации ВПР перед вами откроется окно с перечнем аргументов выбранной вами функции. В поле «Искомое значение» вам потребуется внести диапазон данных, содержащийся в первом столбце таблицы с перечнем поступивших товаров и их количеством. То есть вам нужно сказать Excel, что именно ему следует найти во второй таблице и перенести в первую.

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

Укажите Excel, какие именно значения необходимо сопоставить функции VLOOKUP.
Для того чтобы Excel не путался и ссылался на нужные вам данные, важно зафиксировать заданную ему ссылку. Чтобы сделать это, выделите в поле «Таблица» требуемые значения и нажмите клавишу F4

Если всё выполнено верно, на экране должен появиться знак $.

Теперь мы переходим к полю аргумента «Номер страницы» и задаём ему значения «2». В этом блоке находятся все данные, которые требуется отправить в нашу рабочую таблицу, а потому важно присвоить «Интервальному просмотру» ложное значение (устанавливаем позицию «ЛОЖЬ»). Это необходимо для того, чтобы функция ВПР работала только с точными значениями и не округляла их.

Теперь, когда все необходимые действия выполнены, нам остаётся лишь подтвердить их нажатием кнопки «ОК». Как только в первой ячейке изменятся данные, нам нужно будет применить функцию ВПР ко всему Excel документу. Для этого достаточно размножить VLOOKUP по всему столбцу «Цена». Сделать это можно при помощи перетягивания правого нижнего уголка ячейки с изменённым значением до самого низа столбца. Если все получилось, и данные изменились так, как нам было необходимо, мы можем приступить к расчёту общей стоимости наших товаров. Для выполнения этого действия нам необходимо найти произведение двух столбцов — «Количества» и «Цены». Поскольку в Excel заложены все математические формулы, расчёт можно предоставить «Строке формул», воспользовавшись уже знакомым нам значком «fx».

Синтаксис и особенности применения функции ВПР

Предположим, вы заведующий складом, в котором хранятся разные материалы, такие как упаковки и контейнеры для хранения разных предметов. И вам завезли продукцию в конкретном количестве, и оно задается в Excel.

1

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

2

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

Если говорить более точно, последовательность действий следующая:

1. Приводим внешний вид таблицы в нужный нам вид путем вставки двух колонок, которые называются «Цена» и «Стоимость/Сумма». При этом нужно применить к ячейкам денежный формат. 

2. Нажимаем на ячейку, являющуюся первой в нашей колонке «Цена». В случае с нами она имеет адрес D2. С использованием мастера функций пользователь всегда может найти ВПР в категории «Ссылки и массивы», независимо от версии Excel. А для вызова мастера функций есть два метода. Первый – это нажать на кнопку fx рядом со строкой ввода формулы. Второй же – комбинация клавиш SHIFT + F3. После того, как нужная нам функция будет выбрана, надо нажать на клавишу ОК, чтобы подтвердить свои действия. Есть еще один способ вызова этой функции. Нужно перейти на вкладку «Формулы» и там найти тот же пункт «Ссылки и массивы».

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

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

   • Таблица. Это набор ячеек, в которых будет осуществляться поиск. В данном примере это вторая таблица с прайс-листом. Осуществляем переход на нее и выбираем необходимые значения. 

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

   • Номер столбца. В этом аргументе мы пишем цифру два.

   • Интервальный просмотр. Этот параметр нужен, если ищутся только приблизительные данные. Этот аргумент может принимать два значения «Истина» и «Ложь». Мы запишем второй вариант, поскольку нам требуется точная информация.

4. После этого нажимаем кнопку «ОК».
5. Далее функция размножается на всю колонку, воспользовавшись маркером автозаполнения, потянув за правый нижний угол ячейки по направлению вниз. 

Как следствие, у нас получится такая симпатичная табличка. Далее схема очень простая. Для того, чтобы обнаружить стоимость полученной продукции на складе, необходимо умножить количество товара на его цену. 

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

Как это сделать? Можно применить «Специальную вставку». Последовательность действий следующая:

1. Выделяем нужную колонку и делаем правый клик мыши.
2. Копируем колонку.
3. Оставляем выделение, опять делаем правый клик мыши и нажимаем «Специальная вставка», после чего появится меню, в котором нужно установить радиокнопку возле пункта «Значения».

В самом конце нужно подтвердить свои действия с помощью кнопки «ОК».

7

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

Аргументы функции

• lookup_value (искомое_значение) – это искомое число, которое вы собираетесь искать в первой строке таблицы;
• table_array (таблица) – это диапазон таблицы, в которой вы будете искать данные. Аргументом может быть как ссылка на диапазон так и именной диапазон;
• row_index (номер_строки) – это номер строки из которой вы хотите найти и сопоставить данные;Если аргумент row_index (номер_строки) равен “1”, это означает что функция выдаст результат из первой строки диапазона таблицы (из строки поиска).Если row_index (номер строки) равен “2”, то функция выдаст результат из строки, следующей за первой строкой диапазона поиска.

()– не обязательный аргумент. В нем вы указываете, нужно ли вам точное совпадение данных или приблизительное соответствие. “1” – приблизительное соответствие, “0” – точное совпадение.

Именованные диапазоны облегчают понимание расчетов и еще больше упрощают работу.

Абсолютные ссылки выглядят довольно некрасиво и не очень понятно и наглядно. Поэтому можно сделать ваши расчёты намного чище и проще для понимания, заменив абсолютные ссылки именованными диапазонами. И никакие возможные изменения на вашем листе Excel не смогут их «испортить».

Копировать и переносить их также можно без проблем.

В приведенном выше примере с данными о сотрудниках вы можете назвать входную ячейку B2 «фамилия», а затем выделить все ячейки с информацией и назвать диапазон B5:F100 как «ДанныеСлужащего». Затем перепишите свою формулу в C2 следующим образом:

Сравните сами — насколько понятнее стал расчет из совета №12 по сравнению с №11.

Виртуальная перестановка данных.

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

Для этого нам понадобится функция ВЫБОР (CHOOSE в английском варианте). Она позволяет выбрать данные из какого-то массива по их индексу. Проще говоря, по порядковому номеру колонки.

Для поиска напитка используем формулу:

Вся хитрость состоит в указании массива данных для поиска. Мы выбираем два диапазона: С10:С25 – с кодами (первый), и В10:В25 – с напитками (второй). И из них при помощи ВЫБОР создаем новую виртуальную таблицу, с которой и будем работать.

Конструкция в виде массива {1;2} показывает, что мы берем сначала первый указанный диапазон C10:C25, потом – второй B10:B25, и формируем из них виртуальную таблицу с двумя колонками. В ней мы и производим поиск по первой и извлекаем значения – из второй

И при этом не важно, где они реально расположены на листе Excel. ВПР работает с виртуальным массивом, который мы сами создали

ВПР имеет два режима поиска.

Четвертый параметр «интервальный просмотр» позволяет задать режим сравнения, в котором нужно работать – точный или приблизительный. Это логический параметр, то есть для него возможно 2 варианта:

• или ЛОЖЬ – интервальный просмотр выключен (ищем точное соответствие)
• 1 или ИСТИНА – интервальный просмотр включен (достаточно приблизительного)

Если этот параметр — ЛОЖЬ, то ищется точное равенство в первом столбце с аргументом «искомое». Если же такого нет, воз­вращается ошибка #Н/Д. Точный режим нужен, когда вы хотите найти информацию, основанную на каком-то уникальном ключе. Скажем, информацию о товаре — по коду товара, или данные фильма по его названию, человека — по фамилии.

Если он равен ИСТИНА или вовсе пропущен, то допускается неточное совпадение в данных отсортированной таблицы с аргументом «искомое». То есть нам нужно «лучшее соответствие», «как можно самое близкое».  Возможно, вы просматриваете почтовые тарифы по весу посылки, ищете налоговую ставку на основе дохода, или определяете скидку на основе ежемесячного объема продаж. В этих случаях вы, скорее всего, не найдете именно ваш вес либо точную цифру вашего дохода.  А если точного нет, то принимается ближайшее подходящее значение.

Обратите внимание, что при этом предполагается, что наш перечень отсортирован по столбцу поиска

Функция ВПР чувствительная к регистру

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

Предположим, в столбце B находятся идентификаторы товаров (Item), и Вы хотите извлечь цену товара и соответствующий комментарий из столбцов C и D. Проблема в том, что идентификаторы содержат символы как нижнего, так и верхнего регистров. Например, значения ячеек B4 (001Tvci3u) и B5 (001Tvci3U) отличаются только регистром последнего символа, u и U соответственно.

Как Вы сами догадываетесь, обычная формула поиска

возвратит $90, поскольку значение 001Tvci3u стоит в диапазоне поиска раньше, чем 001Tvci3U. Но это не то, что нам нужно, не так ли?

Чтобы выполнить поиск функцией ВПР в Excel с учётом регистра, Вам придётся добавить вспомогательный столбец и заполнить его ячейки следующей формулой (где B это столбец поиска):

Эта формула разбивает искомое значение на отдельные символы, заменяет каждый символ его кодом (например, вместо A код 65, вместо a код 97), а затем соединяет эти коды в уникальную строку цифр.

После этого используем простую функцию ВПР для поиска с учётом регистра:

Правильная работа функции ВПР с учётом регистра зависит от двух факторов:

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

Как правильно пользоваться функцией КОДСИМВ

Формула, вставленная в ячейки вспомогательного столбца, предполагает, что все Ваши искомые значения имеют одинаковое количество символов. Если нет, то нужно знать наименьшее и наибольшее количества и добавить столько функций ЕСЛИОШИБКА (IFERROR), сколько символов составляет разница между самым коротким и самым длинным искомым значением.

Например, если самое коротко искомое значение состоит из 3 символов, а самое длинное – из 5 символов, используйте такую формулу:

Для функции ПСТР (MID) Вы задаёте следующие аргументы:

• 1-й аргумент – text (текст) – это текст или ссылка на ячейку, содержащую символы, которые нужно извлечь (в нашем случае это B2)
• 2-й аргумент – start_num (начальная_позиция) – позиция первого из тех символов, которые нужно извлечь. Вы вводите 1 в первой функции ПСТР, 2 – во второй функции ПСТР и т. д.
• 3-й аргумент – num_chars (количество_знаков) – определяет количество знаков, которые нужно извлечь из текста. Так как нам всё время нужен только 1 символ, то во всех функциях пишем 1.

ОГРАНИЧЕНИЯ: Функция ВПР – это не лучшее решение для поиска в Excel с учётом регистра. Во-первых, требуется добавление вспомогательного столбца. Во-вторых, формула неплохо справляется, только если данные однородны, или известно точное количество символов в искомых значениях. Если это не Ваш случай, лучше используйте одно из решений, которые мы покажем далее.

Особенности использования функции ГПР в Excel

Функция имеет следующую синтаксическую запись:

=ГПР(искомое_значение;таблица;номер_строки;)

Описание аргументов:

• искомое_значение – обязательный аргумент, принимающий данные любого типа, характеризующие элемент первой строки константы массива или ячейку, расположенную в первой строке диапазона ячеек. При работе с именованными областями данных (списки, таблицы) в качестве данного аргумента указывают название поля (столбца), в котором содержится возвращаемое значение.
• таблица – обязательный аргумент, принимающий константу массива или ссылку на диапазон ячеек, в котором находится список или таблица. Если первой строкой диапазона является шапка таблицы с текстовыми названиями полей, при поиске требуемого названия поля не учитывается регистр символов. Для корректной работы функции ГПР необходимо, чтобы содержащиеся в первой строке значения были отсортированы слева направо.
• номер_строки – обязательный аргумент, характеризующийся числовым значением из диапазона целых положительных чисел, которое соответствует номеру строки относительно первой строки массива или диапазона ячеек, переданных в качестве аргумента таблица. Функция ГПР возвращает результат, взятый из строки таблицы с номером, указанным данным аргументом. Ошибки могут возникать в следующих случаях:

1. В качестве аргумента номер_строки передано число <1. В этом случае будет возвращен код ошибки #ЗНАЧ!.
2. Передано число, превышающее значение номера последней записи (строки) в таблице или массиве. Возвращаемое значение – код ошибки #ССЫЛ!.

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

1. ИСТИНА – поиск ближайшего меньшего значения в случае, если точное совпадение с искомым значением отсутствует. Используется по умолчанию, если аргумент явно не указан.
2. ЛОЖЬ – поиск точного совпадения с искомым значением. Возврат кода ошибки #Н/Д, если точного соответствия нет.

Примечания:

1. Если аргумент явно не указан или имеет значение ИСТИНА, для получения корректных результатов работы функции ГПР первая строка таблицы или массива, переданных в качестве аргумента таблица, должна быть отсортирована по возрастанию значений слева направо. Для поиска точного совпадения (значение аргумента – ЛОЖЬ) сортировку можно не выполнять.
2. Если все данные, хранящееся в первой строке массива или таблицы, превышают по значению данные, переданные в качестве аргумента искомое_значение, функция ГПР вернет код ошибки #Н/Д.
3. При поиске точного соответствия (аргумент = ЛОЖЬ) в таблицах с именованными столбцами (полями) можно использовать подстановочные знаки: «?» – замена одного символа, «*» – замена нескольких символов.