Функция впр в эксель

Объединить две таблицы — альтернатива ВПР в Excel без формул.

Если ваши файлы Excel чрезвычайно велики и сложны, а выполнить расчеты нужно быстро, и вы ищете кого-то, кто может протянуть вам руку помощи, то попробуйте Мастер объединения таблиц .

Этот инструмент является простой и наглядной альтернативой функции ВПР в Excel. Работает он следующим образом:

1. Выберите свою основную таблицу. Предположим, это таблица с данными о продажах.

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

1. Выберите один или несколько общих столбцов в качестве уникальных идентификаторов. В нашем случае это будет наименование заказчика.

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

При этом совершенно не важно, где находятся эти столбцы для добавления — слева или справа от столбца поиска.

1. На следующем шаге вы можете указать дополнительные опции объединения – выделение цветом добавленного, добавление несовпадающих значений в конец основной таблицы, вставка столбца статуса и др. Но в нашем случае в этом нет необходимости. Ведь мы просто хотим найти и добавить в основную таблицу фамилии менеджеров. Поэтому просто нажимаем Finish. Теперь дайте Мастеру объединения таблиц несколько секунд для обработки… и наслаждайтесь результатами 🙂

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

Более подробную информацию об инструменте Merge Tables вы можете посмотреть здесь.

Дополнительные материалы о функции ВПР:

Динамическая подстановка данных из разных таблиц при помощи ВПР и ДВССЫЛ

В начале разъясним, что мы подразумеваем под выражением “Динамическая подстановка данных из разных таблиц”, чтобы убедиться правильно ли мы понимает друг друга.

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

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

Если у Вас всего два таких отчета, то можно использовать до безобразия простую формулу с функциями ВПР и ЕСЛИ (IF), чтобы выбрать нужный отчет для поиска:

Где:

$D$2 – это ячейка, содержащая название товара

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

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

FL_Sales и CA_Sales – названия таблиц (или именованных диапазонов), в которых содержаться соответствующие отчеты о продажах

Вы, конечно же, можете использовать обычные названия листов и ссылки на диапазоны ячеек, например ‘FL Sheet’!$A$3:$B$10, но именованные диапазоны гораздо удобнее.

Однако, когда таких таблиц много, функция ЕСЛИ – это не лучшее решение. Вместо нее можно использовать функцию ДВССЫЛ (INDIRECT), чтобы возвратить нужный диапазон поиска.

Как Вы, вероятно, знаете, функция ДВССЫЛ используется для того, чтобы вернуть ссылку, заданную текстовой строкой, а это как раз то, что нам сейчас нужно. Итак, смело заменяем в представленной выше формуле выражение с функцией ЕСЛИ на ссылку с функцией ДВССЫЛ. Вот такая комбинация ВПР и ДВССЫЛ отлично работает в паре:

Где:

• $D$2 – это ячейка с названием товара, она неизменна благодаря абсолютной ссылке.
• $D3 – это ячейка, содержащая первую часть названия региона. В нашем примере это FL.
• _Sales – общая часть названия всех именованных диапазонов или таблиц. Соединенная со значением в ячейке D3, она образует полное имя требуемого диапазона. Ниже приведены некоторые подробности для тех, кто не имеет опыта работы с функцией ДВССЫЛ.

Как работают ДВССЫЛ и ВПР

Во-первых, позвольте напомнить синтаксис функции ДВССЫЛ (INDIRECT):

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

• A1, если аргумент равен TRUE (ИСТИНА) или не указан;
• R1C1, если FALSE (ЛОЖЬ).

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

Итак, давайте вернемся к нашим отчетам по продажам. Если Вы помните, то каждый отчёт – это отдельная таблица, расположенная на отдельном листе. Чтобы формула работала верно, Вы должны дать названия своим таблицам (или диапазонам), причем все названия должны иметь общую часть. Например, так: CA_Sales, FL_Sales, TX_Sales и так далее. Как видите, во всех именах присутствует “_Sales”.

Функция ДВССЫЛ соединяет значение в столбце D и текстовую строку “_Sales”, тем самым сообщая ВПР в какой таблице искать. Если в ячейке D3 находится значение “FL”, формула выполнит поиск в таблице FL_Sales, если “CA” – в таблице CA_Sales и так далее.

Результат работы функций ВПР и ДВССЫЛ будет следующий:

Если данные расположены в разных книгах Excel, то необходимо добавить имя книги перед именованным диапазоном, например:

Если функция ДВССЫЛ ссылается на другую книгу, то эта книга должна быть открытой. Если же она закрыта, функция сообщит об ошибке #REF! (#ССЫЛ!).

Как сравнить две таблицы: пошаговая инструкция для «чайников»

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

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

Делается это в несколько шагов:

1. Открыть первую со старой информацией.
2. Добавить дополнительный столбик для новых данных «Новая стоимость».
3. Выделить первое пустое поле в созданном столбце (С2).
4. Выбрать раздел «ВПР Формулы» и «Вставить функцию».

5. Найти категорию Excel «Ссылки и массивы».

6. Выбрать ВПР.
7. Задать «Аргументы».

– то, что важно будет найти во второй таблице. Чтобы значение появилось в строке, нужно выделить первый столбик с наименованиями товаров (А2 – А5)

– с чем программа будет сравнивать. Для заполнения нужно перейти на вторую страницу и отметить два наименования – предметы и цена (А2 – В5). И зафиксировать результат кнопкой F4.

Номер столбца – второй, так как именно стоимость переносится в новую.

Интервальный просмотр – ЛОЖЬ.

Заполненное окно выглядит так:

После нажатия кнопки «ОК» новые значения появятся в таблице. Чтобы ценовая информация появилась у всех предметов нужно растянуть ячейку.

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

Производительность работы функции ВПР

Вы добрались до кульминационного места статьи. Казалось бы, ну какая разница, укажу ли я в качестве последнего параметра ноль или единицу? В основном все указывают, конечно же, ноль, так как это довольно практично: не надо заботиться о сортировке первого столбца массива, сразу видно, найдено значение или нет. Но если у вас на листе несколько тысяч формул ВПР (VLOOKUP), то вы заметите, что ВПР вида II работает медленно. При этом обычно все начинают думать:

• мне нужен более мощный компьютер;
• мне нужна более быстрая формула, например, многие знают про ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), которая якобы быстрее на жалкие 5–10%.

И мало кто думает, что стоит только начать использовать ВПР вида I и обеспечить любыми способами сортировку первого столбца, как скорость работы ВПР возрастёт в 57 раз. Пишу прописью — В ПЯТЬДЕСЯТ СЕМЬ РАЗ! Не на 57%, а на 5 700%. Данный факт я проверил вполне надёжно.

Секрет такой быстрой работы кроется в том, что на отсортированном массиве можно применять чрезвычайно эффективный алгоритм поиска, который носит название бинарного поиска (метод деления пополам, метод дихотомии). Так вот ВПР вида I его применяет, а ВПР вида II ищет без какой-либо оптимизации вообще. То же самое относится и к функции ПОИСКПОЗ (MATCH), которая включает в себя аналогичный параметр, а также и к функции ПРОСМОТР (LOOKUP), которая работает только на отсортированных массивах и включена в Excel ради совместимости с Lotus 1-2-3.

Структура формулы ВПР в Excel

Первая строка – значение, которое вы хотите найти в базе данных Excel. В примере необходимая информация находится в ячейке B3. Если вы введете ее адрес в первое поле, при помощи выражения можно найти нужные сведения в списке со сведениями о персонале.

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

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

Существует два варианта значений для параметра «Порядок сортировки». Введете 0 – тогда результатом вставки будет ошибка #Н/Д, если не сможет найти в базе Экселя результаты по запросу.

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

Введете 1 – и формула тоже отобразит результат, если найдет полное совпадение между Lookup_value и содержимым какой-либо строки в первом столбце матрицы (Table_array).

Но если нет соответствия, то будет выбрано значение, приближенное к этому числу. Другими словами, если вы в примере вместо B3 напишете 12, функция вернет ответ «Gwendy», потому что 10 является числом, максимально приближенным к 12. Первая колонка в таблице заранее должна быть отсортирована по возрастанию, если вы вставляете 1 в Range_lookup.

Точное или приближенное совпадение в функции ВПР

И, наконец, давайте рассмотрим поподробнее последний аргумент, который указывается для функции ВПР – range_lookup (интервальный_просмотр). Как уже упоминалось в начале урока, этот аргумент очень важен. Вы можете получить абсолютно разные результаты в одной и той же формуле при его значении TRUE (ПРАВДА) или FALSE (ЛОЖЬ).

Для начала давайте выясним, что в Microsoft Excel понимается под точным и приближенным совпадением.

• Если аргумент range_lookup (интервальный_просмотр) равен FALSE (ЛОЖЬ), формула ищет точное совпадение, т.е. точно такое же значение, что задано в аргументе lookup_value (искомое_значение). Если в первом столбце диапазона table_array (таблица) встречается два или более значений, совпадающих с аргументом lookup_value (искомое_значение), то выбрано будет первое из них. Если совпадения не найдены, функция сообщит об ошибке #N/A (#Н/Д).Например, следующая формула сообщит об ошибке #N/A (#Н/Д), если в диапазоне A2:A15 нет значения 4:

• Если аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА), формула ищет приблизительное совпадение. Точнее, сначала функция ВПР ищет точное совпадение, а если такое не найдено, выбирает приблизительное. Приблизительное совпадение – это наибольшее значение, не превышающее заданного в аргументе lookup_value (искомое_значение).

Если аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или не указан, то значения в первом столбце диапазона должны быть отсортированы по возрастанию, то есть от меньшего к большему. Иначе функция ВПР может вернуть ошибочный результат.

Чтобы лучше понять важность выбора TRUE (ИСТИНА) или FALSE (ЛОЖЬ), давайте разберём ещё несколько формул с функцией ВПР и посмотрим на результаты

Пример 1: Поиск точного совпадения при помощи ВПР

Как Вы помните, для поиска точного совпадения, четвёртый аргумент функции ВПР должен иметь значение FALSE (ЛОЖЬ).

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

Обратите внимание, что наш диапазон поиска (столбец A) содержит два значения 50 – в ячейках A5 и A6. Формула возвращает значение из ячейки B5

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

Пример 2: Используем ВПР для поиска приблизительного совпадения

Когда Вы используете функцию ВПР для поиска приблизительного совпадения, т.е. когда аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или пропущен, первое, что Вы должны сделать, – выполнить сортировку диапазона по первому столбцу в порядке возрастания.

Это очень важно, поскольку функция ВПР возвращает следующее наибольшее значение после заданного, а затем поиск останавливается. Если Вы пренебрежете правильной сортировкой, дело закончится тем, что Вы получите очень странные результаты или сообщение об ошибке #N/A (#Н/Д)

Вот теперь можно использовать одну из следующих формул:

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

Как видите, формула возвратила результат Антилопа (Antelope), скорость которой 61 миля в час, хотя в списке есть также Гепард (Cheetah), который бежит со скоростью 70 миль в час, а 70 ближе к 69, чем 61, не так ли? Почему так происходит? Потому что функция ВПР при поиске приблизительного совпадения возвращает наибольшее значение, не превышающее искомое.

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

Функция ВПР и выпадающий список

Допустим, какие-то данные у нас сделаны в виде раскрывающегося списка. В нашем примере – «Материалы». Необходимо настроить функцию так, чтобы при выборе наименования появлялась цена.

Сначала сделаем раскрывающийся список:

1. Ставим курсор в ячейку Е8, где и будет этот список.
2. Заходим на вкладку «Данные». Меню «Проверка данных».
3. Выбираем тип данных – «Список». Источник – диапазон с наименованиями материалов.
4. Когда нажмем ОК – сформируется выпадающий список.

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

1. Открываем «Мастер функций» и выбираем ВПР.
2. Первый аргумент – «Искомое значение» — ячейка с выпадающим списком. Таблица – диапазон с названиями материалов и ценами. Столбец, соответственно, 2. Функция приобрела следующий вид: .
3. Нажимаем ВВОД и наслаждаемся результатом.

Изменяем материал – меняется цена:

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

Почему функция не работает

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

Нужно точное совпадение

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

Поэтому если требуется уникальное значение, то нужно обязательно указывать последний аргумент со значением ЛОЖЬ.

Необходима фиксация ссылок на таблицу

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

Если ВПР будет копироваться в несколько ячеек, то важно сделать часть ссылок абсолютными. . Очень хорошо это видно на примере ниже

Здесь были введены неверные диапазоны, и из-за этого функция не хочет работать

Очень хорошо это видно на примере ниже. Здесь были введены неверные диапазоны, и из-за этого функция не хочет работать.

19

Чтобы решить эту проблему, достаточно просто нажать на клавишу F4, чтобы зафиксировать адрес ссылки.

Простыми словами, формула должна обрести следующий вид.

=ВПР(($H$3;$B$3:$F$11;4;ЛОЖЬ)

Вставлена колонка

Для чего нужен аргумент «номер столбца»? Для того, чтобы задать функции, какие именно данные должны быть извлечены. 

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

20

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

Но так бывает не всегда. Тогда на помощь придет второе решение. Мы знаем, что в качестве аргумента функции может использоваться другая функция. Вот это и решение. Нужно просто использовать функцию ПОИСКПОЗ, которая возвращает правильный номер столбца. 

Увеличение размеров таблицы

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

21

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

Функция не умеет анализировать данные слева

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

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

Дублирование данных

Еще один недостаток функции в том, что она умеет извлекать лишь первое найденное значение, нужно удалить дубликаты с помощью кнопки «Удалить дубликаты», располагаемой на вкладке «Данные».

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

Функции ИНДЕКС и ПОИСКПОЗ в Excel на простых примерах

Совместное использование функций ИНДЕКС и ПОИСКПОЗ в Excel – хорошая альтернатива ВПР, ГПР и ПРОСМОТР. Эта связка универсальна и обладает всеми возможностями этих функций. А в некоторых случаях, например, при двумерном поиске данных на листе, окажется просто незаменимой. В данном уроке мы последовательно разберем функции ПОИСКПОЗ и ИНДЕКС, а затем рассмотрим пример их совместного использования в Excel.

Более подробно о функциях ВПР и ПРОСМОТР.

Функция ПОИСКПОЗ в Excel

Функция ПОИСКПОЗ возвращает относительное расположение ячейки в заданном диапазоне Excel, содержимое которой соответствует искомому значению. Т.е. данная функция возвращает не само содержимое, а его местоположение в массиве данных.

Например, на рисунке ниже формула вернет число 5, поскольку имя “Дарья” находится в пятой строке диапазона A1:A9.

В следующем примере формула вернет 3, поскольку число 300 находится в третьем столбце диапазона B1:I1.

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

• – функция ПОИСКПОЗ ищет первое значение в точности равное заданному. Сортировка не требуется.
• 1 или вовсе опущено – функция ПОИСКПОЗ ищет самое большое значение, которое меньше или равно заданному. Требуется сортировка в порядке возрастания.
• -1 – функция ПОИСКПОЗ ищет самое маленькое значение, которое больше или равно заданному. Требуется сортировка в порядке убывания.

В одиночку функция ПОИСКПОЗ, как правило, не представляет особой ценности, поэтому в Excel ее очень часто используют вместе с функцией ИНДЕКС.

Функция ИНДЕКС в Excel

Функция ИНДЕКС возвращает содержимое ячейки, которая находится на пересечении заданных строки и столбца. Например, на рисунке ниже формула возвращает значение из диапазона A1:C4, которое находится на пересечении 3 строки и 2 столбца.

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

Если массив содержит только одну строку или один столбец, т.е. является вектором, то второй аргумент функции ИНДЕКС указывает номер значения в этом векторе. При этом третий аргумент указывать необязательно.

Например, следующая формула возвращает пятое значение из диапазона A1:A12 (вертикальный вектор):

Данная формула возвращает третье значение из диапазона A1:L1(горизонтальный вектор):

Совместное использование ПОИСКПОЗ и ИНДЕКС в Excel

Если Вы уже работали с функциями ВПР, ГПР и ПРОСМОТР в Excel, то должны знать, что они осуществляют поиск только в одномерном массиве. Но иногда приходится сталкиваться с двумерным поиском, когда соответствия требуется искать сразу по двум параметрам. Именно в таких случаях связка ПОИСКПОЗ и ИНДЕКС в Excel оказывается просто незаменимой.

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

Пускай ячейка C15 содержит указанный нами месяц, например, Май. А ячейка C16 – тип товара, например, Овощи. Введем в ячейку C17 следующую формулу и нажмем Enter:

=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))

Как видите, мы получили верный результат. Если поменять месяц и тип товара, формула снова вернет правильный результат:

В данной формуле функция ИНДЕКС принимает все 3 аргумента:

1. Первый аргумент – это диапазон B2:E13, в котором мы осуществляем поиск.
2. Вторым аргументом функции ИНДЕКС является номер строки. Номер мы получаем с помощью функции ПОИСКПОЗ(C15;A2:A13;0). Для наглядности вычислим, что же возвращает нам данная формула:
3. Третьим аргументом функции ИНДЕКС является номер столбца. Этот номер мы получаем с помощью функции ПОИСКПОЗ(C16;B1:E1;0). Для наглядности вычислим и это значение:

Если подставить в исходную громоздкую формулу вместо функций ПОИСКПОЗ уже вычисленные данные из ячеек D15 и D16, то формула преобразится в более компактный и понятный вид:

=ИНДЕКС(B2:E13;D15;D16)

Как видите, все достаточно просто!

На этой прекрасной ноте мы закончим. В этом уроке Вы познакомились еще с двумя полезными функциями Microsoft Excel – ПОИСКПОЗ и ИНДЕКС, разобрали возможности на простых примерах, а также посмотрели их совместное использование. Надеюсь, что данный урок Вам пригодился. Оставайтесь с нами и успехов в изучении Excel.

Функция ВПР в Excel – общее описание и синтаксис

Итак, что же такое ВПР? Ну, во-первых, это функция Excel. Что она делает? Она ищет заданное Вами значение и возвращает соответствующее значение из другого столбца. Говоря техническим языком, ВПР ищет значение в первом столбце заданного диапазона и возвращает результат из другого столбца в той же строке.

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

Первая буква в названии функции ВПР (VLOOKUP) означает Вертикальный (Vertical). По ней Вы можете отличить ВПР от ГПР (HLOOKUP), которая осуществляет поиск значения в верхней строке диапазона – Горизонтальный (Horizontal).

Функция ВПР доступна в версиях Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP и Excel 2000.

Синтаксис функции ВПР

Функция ВПР (VLOOKUP) имеет вот такой синтаксис:

Как видите, функция ВПР в Microsoft Excel имеет 4 параметра (или аргумента). Первые три – обязательные, последний – по необходимости.

lookup_value (искомое_значение) – значение, которое нужно искать.Это может быть значение (число, дата, текст) или ссылка на ячейку (содержащую искомое значение), или значение, возвращаемое какой-либо другой функцией Excel. Например, вот такая формула будет искать значение 40:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)

Если искомое значение будет меньше, чем наименьшее значение в первом столбце просматриваемого диапазона, функция ВПР сообщит об ошибке #N/A (#Н/Д).

table_array (таблица) – два или более столбца с данными.Запомните, функция ВПР всегда ищет значение в первом столбце диапазона, заданного в аргументе table_array (таблица). В просматриваемом диапазоне могут быть различные данные, например, текст, даты, числа, логические значения. Регистр символов не учитывается функцией, то есть символы верхнего и нижнего регистра считаются одинаковыми.Итак, наша формула будет искать значение 40 в ячейках от A2 до A15, потому что A – это первый столбец диапазона A2:B15, заданного в аргументе table_array (таблица):
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)

col_index_num (номер_столбца) – номер столбца в заданном диапазоне, из которого будет возвращено значение, находящееся в найденной строке.Крайний левый столбец в заданном диапазоне – это 1, второй столбец – это 2, третий столбец – это 3 и так далее. Теперь Вы можете прочитать всю формулу:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)

Формула ищет значение 40 в диапазоне A2:A15 и возвращает соответствующее значение из столбца B (поскольку B – это второй столбец в диапазоне A2:B15).

Если значение аргумента col_index_num (номер_столбца) меньше 1, то ВПР сообщит об ошибке #VALUE! (#ЗНАЧ!). А если оно больше количества столбцов в диапазоне table_array (таблица), функция вернет ошибку #REF! (#ССЫЛКА!).

• range_lookup (интервальный_просмотр) – определяет, что нужно искать:
  • точное совпадение, аргумент должен быть равен FALSE (ЛОЖЬ);
  • приблизительное совпадение, аргумент равен TRUE (ИСТИНА) или вовсе не указан.

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