Впр истина или ложь

Содержание
  1. Начало работы
  2. Примеры
  3. Пример 1
  4. Пример 2
  5. Пример 3
  6. Пример 4
  7. Пример 5
  8. Дополнительные сведения
  9. См. также
  10. Синтаксис функции
  11. Задача1. Справочник товаров
  12. Задача2. Поиск ближайшего числа
  13. Функция ВПР в Excel – общее описание и синтаксис
  14. Синтаксис функции ВПР
  15. Примеры с функцией ВПР
  16. Как, используя ВПР, выполнить поиск на другом листе Excel
  17. Поиск в другой рабочей книге с помощью ВПР
  18. Как использовать именованный диапазон или таблицу в формулах с ВПР
  19. Использование символов подстановки в формулах с ВПР
  20. Пример 1: Ищем текст, начинающийся или заканчивающийся определенными символами
  21. Пример 2: Сочетаем символы подстановки и ссылки на ячейки в формулах с ВПР
  22. Точное или приближенное совпадение в функции ВПР
  23. Пример 1: Поиск точного совпадения при помощи ВПР
  24. Пример 2: Используем ВПР для поиска приблизительного совпадения
  25. ВПР в Excel – это нужно запомнить!

ФУНКЦИЯ ВПР используется, если нужно найти элементы в таблице или диапазоне по строкам. Например, можно найти цену на автомобильную часть по номеру детали или получить имя сотрудника на основе его кода сотрудника.

Совет: Чтобы получить дополнительную справку о функции ВПР, просмотрите эти видео с YouTube от экспертов сообщества Excel!

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

= ВПР (необходимые условия для поиска, номер столбца в диапазоне, содержащий возвращаемое значение, возвращаемое приближенное или точное соответствие — обозначено как 1/истина или 0/ложь).

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

Используйте функцию ВПР для поиска значения в таблице.

ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])

= ВПР (A2; A10: C20; 2; ИСТИНА)

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

Например, если Таблица-массив охватывает ячейки B2: D7, то искомое_значение должен находиться в столбце B.

Искомое_значение может являться значением или ссылкой на ячейку.

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

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

Номер столбца (начиная с 1 для самого левого столбца массива « инфо_таблица»), содержащего возвращаемое значение.

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

Приближенное соответствие: 1/истина предполагает, что первый столбец в таблице отсортирован либо в числовом формате, либо в алфавитном порядке, а затем будет искать ближайшее значение. Это способ по умолчанию, если не указан другой. Например, = ВПР (90; a1: B100; 2; истина).

Точное совпадение: 0/ложь осуществляет поиск точного значения в первом столбце. Например, = ВПР ("Смит"; a1: B100; 2; ложь).

Начало работы

Для построения синтаксиса функции ВПР вам потребуется следующая информация:

Значение, которое вам нужно найти, то есть искомое значение.

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

Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона задано значение B2: D11, число B должно быть первым столбцом, а в качестве второго — "C" и т. д.

При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.

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

= ВПР (искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением, приближенное соответствие (истина) или точное совпадение (ложь)).

Примеры

Вот несколько примеров использования функции ВПР.

Пример 1

Пример 2

Пример 3

Пример 4

Пример 5

Неправильное возвращаемое значение

Если аргумент интервальный_просмотр имеет значение ИСТИНА или не указан, первый столбец должны быть отсортирован по алфавиту или по номерам. Если первый столбец не отсортирован, возвращаемое значение может быть непредвиденным. Отсортируйте первый столбец или используйте значение ЛОЖЬ для точного соответствия.

Если аргумент интервальный_просмотр имеет значение ИСТИНА, а значение аргумента искомое_значение меньше, чем наименьшее значение в первом столбце таблицы, будет возвращено значение ошибки #Н/Д.

Если аргумент интервальный_просмотр имеет значение ЛОЖЬ, значение ошибки #Н/Д означает, что найти точное число не удалось.

Дополнительные сведения об устранении ошибок #Н/Д в функции ВПР см. в статье Исправление ошибки #Н/Д в функции ВПР.

#ССЫЛКА! в ячейке

Если значение аргумента номер_столбца больше, чем число столбцов в таблице, появится значение ошибки #ССЫЛКА!.

Дополнительные сведения об устранении ошибок #ССЫЛКА! в функции ВПР см. в статье Исправление ошибки #ССЫЛКА!.

Если значение аргумента таблица меньше 1, появится значение ошибки #ЗНАЧ!.

Дополнительные сведения об устранении ошибок #ЗНАЧ! в функции ВПР см. в статье Исправление ошибки #ЗНАЧ! в функции ВПР.

Значение ошибки #ИМЯ? чаще всего появляется, если в формуле пропущены кавычки. Во время поиска имени сотрудника убедитесь, что имя в формуле взято в кавычки. Например, в функции =ВПР("Иванов";B2:E7;2;ЛОЖЬ) имя необходимо указать в формате "Иванов" и никак иначе.

Дополнительные сведения см. в статье Исправление ошибки #ИМЯ?.

Ошибки #ПЕРЕНОС! в ячейке

Это конкретная ошибка #SPILL! Обычно это означает, что формула полагается на неявное пересечение для искомого значения и использует в качестве ссылки весь столбец. Например, = ВПР ( а:а, а:к, 2; ложь). Проблему можно устранить, заменив ссылку для подстановки с помощью оператора @ следующим образом: = ВПР ( @ а:а, а:к, 2, ложь). Кроме того, вы можете использовать традиционный метод ВПР и ссылаться на одну ячейку, а не на весь столбец: = ВПР ( a2; а:к; 2; ложь).

Используйте абсолютные ссылки в аргументе интервальный_просмотр

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

Не сохраняйте числовые значения или значения дат как текст.

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

Сортируйте первый столбец

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

Используйте подстановочные знаки

Если значение аргумента интервальный_просмотр — ЛОЖЬ, а аргумент искомое_значение представляет собой текст, то в аргументе искомое_значение допускается использование подстановочных знаков: вопросительного знака (?) и звездочки (*). Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (

) перед искомым символом.

Например, функция = ВПР ("Ивано?"; B2: E7; 2; ложь) будет искать все вхождения Иванов с последней буквой, которая может измениться.

Убедитесь, что данные не содержат ошибочных символов.

При поиске текстовых значений в первом столбце убедитесь, что данные в нем не содержат начальных или конечных пробелов, недопустимых прямых (‘ или ") и изогнутых (‘ или “) кавычек либо непечатаемых символов. В этих случаях функция ВПР может возвращать непредвиденное значение.

Для получения точных результатов попробуйте воспользоваться функциями ПЕЧСИМВ или СЖПРОБЕЛЫ.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Читайте также:  Axis сброс на заводские настройки

См. также

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

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

Функция ВПР() является одной из наиболее используемых в EXCEL, поэтому рассмотрим ее подробно.

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

Синтаксис функции

ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)

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

Таблица – ссылка на диапазон ячеек. В левом столбце таблицы ищется Искомое_значение, а из столбцов расположенных правее, выводится соответствующий результат (хотя, в принципе, можно вывести можно вывести значение из левого столбца (в этом случае это будет само искомое_значение)). Часто левый столбец называется ключевым. Если первый столбец не содержит искомое_значение , то функция возвращает значение ошибки #Н/Д.

Номер_столбца – номер столбца Таблицы, из которого нужно выводить результат. Самый левый столбец (ключевой) имеет номер 1 (по нему производится поиск).

Параметр интервальный_просмотр может принимать 2 значения: ИСТИНА (ищется значение ближайшее к критерию или совпадающее с ним) и ЛОЖЬ (ищется значение в точности совпадающее с критерием). Значение ИСТИНА предполагает, что первый столбец в таблице отсортирован в алфавитном порядке или по возрастанию. Это способ используется в функции по умолчанию, если не указан другой.

Ниже в статье рассмотрены популярные задачи, которые можно решить с использованием функции ВПР() .

Задача1. Справочник товаров

Пусть дана исходная таблица (см. файл примера лист Справочник ).

Задача состоит в том, чтобы, выбрав нужный Артикул товара, вывести его Наименование и Цену.

Примечание. Это "классическая" задача для использования ВПР() (см. статью Справочник).

Для вывода Наименования используйте формулу =ВПР($E9;$A$13:$C$19;2;ЛОЖЬ) или = ВПР($E9;$A$13:$C$19;2;ИСТИНА) или = ВПР($E9;$A$13:$C$19;2) (т.е. значение параметра Интервальный_просмотр можно задать ЛОЖЬ или ИСТИНА или вообще опустить). Значение параметра номер_столбца нужно задать =2, т.к. номер столбца Наименование равен 2 (Ключевой столбец всегда номер 1).

Для вывода Цены используйте аналогичную формулу =ВПР($E9;$A$13:$C$19;3;ЛОЖЬ) (значение параметра номер_столбца нужно задать =3).

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

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

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

В файле примера лист Справочник также рассмотрены альтернативные формулы (получим тот же результат) с использованием функций ИНДЕКС() , ПОИСКПОЗ() и ПРОСМОТР() . Если ключевой столбец (столбец с артикулами) не является самым левым в таблице, то функция ВПР() не применима. В этом случае нужно использовать альтернативные формулы. Связка функций ИНДЕКС() , ПОИСКПОЗ() образуют так называемый "правый ВПР": =ИНДЕКС(B13:B19;ПОИСКПОЗ($E$9;$A$13:$A$19;0);1)

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

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

Задача2. Поиск ближайшего числа

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

Чтобы использовать функцию ВПР() для решения этой задачи нужно выполнить несколько условий:

  1. Ключевой столбец, по которому должен производиться поиск, должен быть самым левым в таблице;
  2. Ключевой столбец должен быть обязательно отсортирован по возрастанию;
  3. Значение параметра Интервальный_просмотр нужно задать ИСТИНА или вообще опустить.

Для вывода Наименования товара используйте формулу =ВПР($A7;$A$11:$B$17;2;ИСТИНА)

Для вывода найденной цены (она не обязательно будет совпадать с заданной) используйте формулу: =ВПР($A7;$A$11:$B$17;1;ИСТИНА)

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

Найденное значение может быть далеко не самым ближайшим. Например, если попытаться найти ближайшую цену для 199, то функция вернет 150 (хотя ближайшее все же 200). Это опять следствие того, что функция находит наибольшее число, которое меньше или равно заданному.

Если нужно найти по настоящему ближайшее к искомому значению, то ВПР() тут не поможет. Такого рода задачи решены в разделе Ближайшее ЧИСЛО. Там же можно найти решение задачи о поиске ближайшего при несортированном ключевом столбце.

Примечание. Для удобства, строка таблицы, содержащая найденное решение, выделена Условным форматированием. Это можно сделать с помощью формулы =ПОИСКПОЗ($A$7;$A$11:$A$17;1)=СТРОКА()-СТРОКА($A$10) .

Примечание: Если в ключевом столбце имеется значение совпадающее с искомым, то функция с параметром Интервальный_просмотр =ЛОЖЬ вернет первое найденное значение, равное искомому, а с параметром =ИСТИНА – последнее (см. картинку ниже).

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

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

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

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

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

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

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

Читайте также:  Аналоговые часы для windows 10

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

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

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

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

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

Если искомое значение будет меньше, чем наименьшее значение в первом столбце просматриваемого диапазона, функция ВПР сообщит об ошибке #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 и так далее. Теперь Вы можете прочитать всю формулу:

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

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

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

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

Примеры с функцией ВПР

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

Как, используя ВПР, выполнить поиск на другом листе Excel

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

Чтобы, используя ВПР, выполнить поиск на другом листе Microsoft Excel, Вы должны в аргументе table_array (таблица) указать имя листа с восклицательным знаком, а затем диапазон ячеек. К примеру, следующая формула показывает, что диапазон A2:B15 находится на листе с именем Sheet2.

Конечно же, имя листа не обязательно вводить вручную. Просто начните вводить формулу, а когда дело дойдёт до аргумента table_array (таблица), переключитесь на нужный лист и выделите мышью требуемый диапазон ячеек.

Формула, показанная на скриншоте ниже, ищет текст “Product 1” в столбце A (это 1-ый столбец диапазона A2:B9) на листе Prices.

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)
=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

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

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

Поиск в другой рабочей книге с помощью ВПР

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

Например, ниже показана формула, которая ищет значение 40 на листе Sheet2 в книге Numbers.xlsx:

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

  1. Откройте обе книги. Это не обязательно, но так проще создавать формулу. Вы же не хотите вводить имя рабочей книги вручную? Вдобавок, это защитит Вас от случайных опечаток.
  2. Начните вводить функцию ВПР, а когда дело дойдёт до аргумента table_array (таблица), переключитесь на другую рабочую книгу и выделите в ней нужный диапазон поиска.

На снимке экрана, показанном ниже, видно формулу, в которой для поиска задан диапазон в рабочей книге PriceList.xlsx на листе Prices.

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

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

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

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

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

Теперь Вы можете записать вот такую формулу для поиска цены товара Product 1:

=VLOOKUP("Product 1",Products,2)
=ВПР("Product 1";Products;2)

Большинство имен диапазонов работают для всей рабочей книги Excel, поэтому нет необходимости указывать имя листа для аргумента table_array (таблица), даже если формула и диапазон поиска находятся на разных листах книги. Если же они находятся в разных книгах, то перед именем диапазона нужно указать название рабочей книги, к примеру, вот так:

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)
=ВПР("Product 1";PriceList.xlsx!Products;2)

Так формула выглядит гораздо понятнее, согласны? Кроме того, использование именованных диапазонов – это хорошая альтернатива абсолютным ссылкам, поскольку именованный диапазон не меняется при копировании формулы в другие ячейки. Значит, Вы можете быть уверены, что диапазон поиска в формуле всегда останется корректным.

Если преобразовать диапазон ячеек в полноценную таблицу Excel, воспользовавшись командой Table (Таблица) на вкладке Insert (Вставка), то при выделении диапазона мышью, Microsoft Excel автоматически добавит в формулу названия столбцов (или название таблицы, если Вы выделите всю таблицу).

Готовая формула будет выглядеть примерно вот так:

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)
=ВПР("Product 1";Table46[[Product]:[Price]];2)

А может даже так:

=VLOOKUP("Product 1",Table46,2)
=ВПР("Product 1";Table46;2)

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

Использование символов подстановки в формулах с ВПР

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

  • Знак вопроса (?) – заменяет один любой символ.
  • Звёздочка (*) – заменяет любую последовательность символов.

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

  • Когда Вы не помните в точности текст, который нужно найти.
  • Когда Вы хотите найти какое-то слово, которое является частью содержимого ячейки. Знайте, что ВПР ищет по содержимому ячейки целиком, как при включённой опции Match entire cell content (Ячейка целиком) в стандартном поиске Excel.
  • Когда в ячейке содержатся дополнительные пробелы в начале или в конце содержимого. В такой ситуации Вы можете долго ломать голову, пытаясь понять, почему формула не работает.
Читайте также:  Как найти максимум функции в маткаде

Пример 1: Ищем текст, начинающийся или заканчивающийся определенными символами

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

Теперь, когда Вы уверены, что нашли правильное имя, можно использовать эту же формулу, чтобы найти сумму, оплаченную этим клиентом. Для этого достаточно изменить третий аргумент функции ВПР на номер нужного столбца. В нашем случае это столбец C (3-й в диапазоне):

Вот ещё несколько примеров с символами подстановки:

Находим имя, заканчивающееся на “man”:

Находим имя, начинающееся на “ad” и заканчивающееся на “son”:

Находим первое имя в списке, состоящее из 5 символов:

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

Пример 2: Сочетаем символы подстановки и ссылки на ячейки в формулах с ВПР

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

Это можно сделать, используя вот такую формулу:

Эта формула ищет значение из ячейки C1 в заданном диапазоне и возвращает соответствующее значение из столбца B. Обратите внимание, что в первом аргументе мы используем символ амперсанда (&) до и после ссылки на ячейку, чтобы связать текстовую строку.

Как видно на рисунке ниже, функция ВПР возвращает значение “Jeremy Hill”, поскольку его лицензионный ключ содержит последовательность символов из ячейки C1.

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

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

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

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

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

=VLOOKUP(4,A2:B15,2,FALSE)
=ВПР(4;A2:B15;2;ЛОЖЬ)

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

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

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

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

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

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

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

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

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

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

    =VLOOKUP(69,$A$2:$B$15,2,TRUE) или =VLOOKUP(69,$A$2:$B$15,2)
    =ВПР(69;$A$2:$B$15;2;ИСТИНА) или =ВПР(69;$A$2:$B$15;2)

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

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

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

    ВПР в Excel – это нужно запомнить!

    1. Функция ВПР в Excel не может смотреть налево. Она всегда ищет значение в крайнем левом столбце диапазона, заданного аргументом table_array (таблица).
    2. В функции ВПР все значения используются без учета регистра, то есть маленькие и большие буквы эквивалентны.
    3. Если искомое значение меньше минимального значения в первом столбце просматриваемого диапазона, функция ВПР сообщит об ошибке #N/A (#Н/Д).
    4. Если 3-й аргумент col_index_num (номер_столбца) меньше 1, функция ВПР сообщит об ошибке #VALUE! (#ЗНАЧ!). Если же он больше количества столбцов в диапазоне table_array (таблица), функция сообщит об ошибке #REF! (#ССЫЛКА!).
    5. Используйте абсолютные ссылки на ячейки в аргументе table_array (таблица), чтобы при копировании формулы сохранялся правильный диапазон поиска. Попробуйте в качестве альтернативы использовать именованные диапазоны или таблицы в Excel.
    6. Когда выполняете поиск приблизительного совпадения, не забывайте, что первый столбец в исследуемом диапазоне должен быть отсортирован по возрастанию.
    7. И, наконец, помните о важности четвертого аргумента. Используйте значения TRUE (ИСТИНА) или FALSE (ЛОЖЬ) обдуманно, и Вы избавитесь от многих головных болей.

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

    Оцените статью
    ПК Знаток
    Добавить комментарий

    Adblock
    detector