Формула впн как работает эксель

Как пользоваться впр в экселе

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

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

Функция впр в excel для чайников

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

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

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

У Вас уже есть шаблон ведомости, в котором заполнены табельные номера и ФИО.

Осталось внести суммы зарплат и собрать подписи.

Шаблон зарплатной ведомости

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

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

Заполнение таблицы вручную

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

ВПР эксель как пользоваться

Функция ВПР находится в разделе функций ссылок и массивов.

Как найти ВПР

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

Функция впр в excel примеры: где писать функцию

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

Искомое значение – значение для поиска. Значение, которое вы хотите найти. Поиск этого значения будет в первом столбце диапазона ячеек, указанного в аргументе — Таблица.

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

Функция впр в excel примеры: Искомое значение.

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

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

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

Функция впр в excel примеры: Таблица

Номер столбца – номер столбца начиная с 1 для самого левого столбца Таблицы, содержащего возвращаемое значение.

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

Функция впр в excel примеры: Номер столбца.

Интервальный просмотр – число 0 или 1.

0 (Ложь [False]) Точное совпадение–осуществляет поиск точного значения в первом столбце(если не находит –#Н/Д [#N/A]).

1 (Истина [True]) Приближенное соответствие–если нет совпадения, то выдает max_значение

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

А пока просто запомните, что здесь всегда ставим 0.

Функция ВПР

Совет: Попробуйте использовать новую функцию ПРОСМОТРX, улучшенную версию функции ВПР, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что делает ее проще и удобнее в использовании, чем предшественницу.

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

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

=ВПР(искомое значение; место для его поиска; номер столбца в диапазоне с возвращаемым значением; возврат приблизительного или точного совпадения — указывается как 1/ИСТИНА или 0/ЛОЖЬ).

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

Технические подробности

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

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

• =ВПР(A2;A10:C20;2;ИСТИНА)
• =ВПР(«Иванов»;B2:E7;2;ЛОЖЬ)
• =ВПР(A2;’Сведения о клиенте’!A:F;3;ЛОЖЬ)

Имя аргумента

искомое_значение (обязательный)

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

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

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

таблица (обязательный)

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

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

номер_столбца (обязательный)

Номер столбца (начиная с 1 для крайнего левого столбца таблицы), содержащий возвращаемое значение.

интервальный_просмотр (необязательный)

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

• Вариант Приблизительное совпадение — 1/ИСТИНА предполагает, что первый столбец в таблице отсортирован в алфавитном порядке или по номерам, а затем выполняет поиск ближайшего значения. Это способ по умолчанию, если не указан другой. Например, =ВПР(90;A1:B100;2;ЛОЖЬ).
• Вариант Точное совпадение — 0/ЛОЖЬ осуществляет поиск точного значения в первом столбце. Например, =ВПР(«Иванов»;A1:B100;2;ЛОЖЬ).

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

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

1. Значение, которое вам нужно найти, то есть искомое значение.
2. Диапазон, в котором находится искомое значение. Помните, что для правильной работы функции ВПР искомое значение всегда должно находиться в первом столбце диапазона. Например, если искомое значение находится в ячейке C2, диапазон должен начинаться с C.
3. Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона вы указываете B2:D11, следует считать B первым столбцом, C — вторым и т. д.
4. При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.

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

=ВПР(искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением; приблизительное совпадение (ИСТИНА) или точное совпадение (ЛОЖЬ)).

Примеры

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

Пример 1

Пример 2

Пример 3

Пример 4

Пример 5

Объединение данных из нескольких таблиц на одном листе с помощью функции ВПР

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

Здесь столбцы A–F и H содержат значения или формулы, которые используют значения только на этом листе, а в остальных столбцах используется функция ВПР и значения столбца А (код клиента) и столбца B (адвокат) для получения данных из других таблиц.

1. Скопируйте таблицу с общими полями на новый лист и присвойте имя.
2. Щелкните Данные >Работа с данными >Отношения, чтобы открыть диалоговое окно «Управление отношениями».

• Поле, которое связывает таблицы (указано в скобках в диалоговом окне). Это искомое_значение для вашей формулы ВПР.
• Имя связанной таблицы подстановки. Это таблица в вашей формуле ВПР.
• Поле (столбец) в связанной таблице подстановки, содержащее данные, которые вам нужны в новом столбце. Эта информация не отображается в диалоговом окне «Управление отношениями». Чтобы увидеть, какое поле нужно получить, посмотрите на связанную таблицу подстановки. Обратите внимание на номер столбца (A=1) — это номер_столбца в вашей формуле.

Распространенные неполадки

Возможная причина

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

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

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

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

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

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

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

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

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

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

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

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

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

Рекомендации

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

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

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

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

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

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

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

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

Например, с помощью функции =ВПР(«Ивано?»;B2:E7;2;ЛОЖЬ) будет выполнен поиск всех случаев употребления Иванов с последней буквой, которая может меняться.

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

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

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

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

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Функция ВПР в Excel для чайников и не только

Функция ВПР в Excel позволяет данные из одной таблицы переставить в соответствующие ячейки второй. Ее английское наименование – VLOOKUP.

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

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

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

Стоимость материалов – в прайс-листе. Это отдельная таблица.

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

1. Приведем первую таблицу в нужный нам вид. Добавим столбцы «Цена» и «Стоимость/Сумма». Установим денежный формат для новых ячеек.
2. Выделяем первую ячейку в столбце «Цена». В нашем примере – D2. Вызываем «Мастер функций» с помощью кнопки «fx» (в начале строки формул) или нажав комбинацию горячих клавиш SHIFT+F3. В категории «Ссылки и массивы» находим функцию ВПР и жмем ОК. Данную функцию можно вызвать перейдя по закладке «Формулы» и выбрать из выпадающего списка «Ссылки и массивы».
3. Откроется окно с аргументами функции. В поле «Искомое значение» — диапазон данных первого столбца из таблицы с количеством поступивших материалов. Это те значения, которые Excel должен найти во второй таблице.
4. Следующий аргумент – «Таблица». Это наш прайс-лист. Ставим курсор в поле аргумента. Переходим на лист с ценами. Выделяем диапазон с наименованием материалов и ценами. Показываем, какие значения функция должна сопоставить.
5. Чтобы Excel ссылался непосредственно на эти данные, ссылку нужно зафиксировать. Выделяем значение поля «Таблица» и нажимаем F4. Появляется значок $.
6. В поле аргумента «Номер столбца» ставим цифру «2». Здесь находятся данные, которые нужно «подтянуть» в первую таблицу. «Интервальный просмотр» — ЛОЖЬ. Т.к. нам нужны точные, а не приблизительные значения.

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

Теперь найти стоимость материалов не составит труда: количество * цену.

Функция ВПР связала две таблицы. Если поменяется прайс, то и изменится стоимость поступивших на склад материалов (сегодня поступивших). Чтобы этого избежать, воспользуйтесь «Специальной вставкой».

1. Выделяем столбец со вставленными ценами.
2. Правая кнопка мыши – «Копировать».
3. Не снимая выделения, правая кнопка мыши – «Специальная вставка».
4. Поставить галочку напротив «Значения». ОК.

Формула в ячейках исчезнет. Останутся только значения.

Быстрое сравнение двух таблиц с помощью ВПР

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

1. В старом прайсе делаем столбец «Новая цена».
2. Выделяем первую ячейку и выбираем функцию ВПР. Задаем аргументы (см. выше). Для нашего примера: . Это значит, что нужно взять наименование материала из диапазона А2:А15, посмотреть его в «Новом прайсе» в столбце А. Затем взять данные из второго столбца нового прайса (новую цену) и подставить их в ячейку С2.

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

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

До сих пор мы предлагали для анализа только одно условие – наименование материала. На практике же нередко требуется сравнить несколько диапазонов с данными и выбрать значение по 2, 3-м и т.д. критериям.

Таблица для примера:

Предположим, нам нужно найти, по какой цене привезли гофрированный картон от ОАО «Восток». Нужно задать два условия для поиска по наименованию материала и по поставщику.

Дело осложняется тем, что от одного поставщика поступает несколько наименований.

1. Добавляем в таблицу крайний левый столбец (важно!), объединив «Поставщиков» и «Материалы».
2. Таким же образом объединяем искомые критерии запроса:
3. Теперь ставим курсор в нужном месте и задаем аргументы для функции: . Excel находит нужную цену.

Рассмотрим формулу детально:

1. Что ищем.
2. Где ищем.
3. Какие данные берем.

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

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

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

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

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

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

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

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

• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Функция ВПР() в EXCEL

Функция ВПР() является одной из наиболее используемых в 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)

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

Примечание . Для удобства, строка таблицы, содержащая найденное решение, выделена Условным форматированием . (см. статью Выделение строк таблицы в MS EXCEL в зависимости от условия в ячейке ).

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

Задача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) .

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

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