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

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

Предположим, у вас есть таблица с данными о работниках. В первой колонке хранится табельный номер сотрудника, в остальных – другие данные (ФИО, отдел и т.д.). Если у вас есть табельный номер, то можно воспользоваться функцией ВПР, чтобы вернуть определенную информацию о сотруднике. Синтаксис формулы =ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр]). Она говоритExcel: «Найди в таблице строку, первая ячейка которой совпадает с искомым_значением, и верни значение ячейки с порядковым номером номер_столбца ».

Но случаются ситуации, когда у вас есть имя сотрудника и необходимо вернуть табельный номер. На рисунке в ячейке A10 – имя работника и требуется определить табельный номер в ячейке B10.

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

Функция ИНДЕКС()

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

Начнем с функции ИНДЕКС. Кошмарное название. Когда кто-нибудь говорит «индекс», у меня в голове не возникает ни единой ассоциации, чем же занимается эта функция. А требует она целых три аргумента: =ИНДЕКС(массив; номер_строки; [номер_столбца]).

Говоря по-простому, Excel идет в массив данных и возвращает значение, находящееся на пересечении указанной строки и столбца. Как будто бы просто. Таким образом, формула =ИНДЕКС($A$2:$C$6;4;2) вернет значение, находящееся в ячейке B5.

Применительно к нашей проблеме, чтобы вернуть табельный номер работника, формула должна выглядеть следующим образом =ИНДЕКС($A$2:$A$6;?;1). Выглядит как бессмыслица, но если мы заменим знак вопроса формулой ПОИСКПОЗ, у нас есть решение.

Функция ПОИСКПОЗ()

Синтаксис этой функции таков: =ПОИСКПОЗ(искомое_значение; просматриваемы_массив; [тип_сопоставления]).

Она говорит Excel: «Найди искомое_значение в массиве данных и верни номер строки массива, в которой это значение встречается». Таким образом, чтобы найти в какой строке находиться имя сотрудника в ячейке A10, необходимо прописать формулу =ПОИСКПОЗ(A10; $B$2:$B$6; 0). Если в ячейке A10 будет имя «Колин Фарел», тогда ПОИСКПОЗ вернет 5-ю строку массива B2:B6.

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

ИНДЕКС($A$2:$B$6;ПОИСКПОЗ(A10;$B$2:$B$6;0);1)

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

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

Синтаксис и описание функции ВПР в Excel

Итак, так как второе название этой статьи «Функция ВПР в Excel для чайников », начнем с того что узнаем, что же такое функция ВПР и что она делает? Функция ВПР на английском VLOOKUP, ищет указанное значение и возвращает соответствующее значение из другого столбца.

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

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

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

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

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

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

1. искомое_значение - это значение для поиска.

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

• Поиск числа : =ВПР(40; A2:B15; 2) - формула будет искать число 40.
• Поиск текста : =ВПР(«яблоки»; A2:B15; 2) - формула будет искать текст «яблоки». Обратите внимание, что вы всегда включаете текстовые значения в «двойные кавычки».
• Поиск значения из другой ячейки : =ВПР(C2; A2:B15; 2) - формула будет искать значение в ячейке C2.

1. таблица - это два или более столбца данных.

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

Итак, наша формула =ВПР(40; A2:B15; 2) будет искать «40» в ячейках от A2 до A15, потому что A - это первый столбец таблицы A2: B15.

1. номер_столбца - номер столбца в таблице, из которой должно быть возвращено значение в соответствующей строке.

Самый левый столбец в указанной таблице равен 1, второй столбец - 2, третий - 3 и т. д.

4. интервальный_просмотр определяет, ищете ли вы точное соответствие (ЛОЖЬ) или приблизительное соответствие (ИСТИНА или опущено). Этот последний параметр является необязательным, но очень важным.

Функция ВПР в Excel примеры

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

Функция ВПР на разных листах

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

Чтобы использовать функцию ВПР с другого листа Excel, вы должны ввести имя рабочего листа и восклицательный знак в аргументе таблица перед диапазоном ячеек, например, =ВПР(40;Лист2!A2:B15;2). Формула указывает, что диапазон поиска A2:B15 находится в Лист2.

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

Формула, которую вы видите на изображении ниже, ищет текст в ячейке А2 («Продукт 3 ») в столбце A (1-й столбец диапазона поиска A2:B9) на листе «Цены »:

ВПР(A2;Цены!$A$2:$B$8;2;ЛОЖЬ)

Функция ВПР в Excel - Функция ВПР на разных листах

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

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

Чтобы создать именованный диапазон, просто выберите ячейки и введите любое имя в поле «Имя », слева от панели «Формула ».

Функция ВПР в Excel - Присвоение имени диапазону

Теперь вы можете написать следующую формулу ВПР, чтобы получить цену Продукта 1:

ВПР(«Продукт 1»;Продукты;2)

Функция ВПР в Excel - Пример функции ВПР с именем диапазона

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

Если вы преобразовали диапазон ячеек в полнофункциональную таблицу Excel (вкладка «Вставка» --> «Таблица»), вы можете выбрать диапазон поиска с помощью мыши, а Microsoft Excel автоматически добавит имена колонок или имя таблицы в формулу:

Функция ВПР в Excel - Пример функции ВПР с именем таблицы

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

ВПР("Продукт 1";Таблица6[[Продукт]:[Цена]];2)

или даже =ВПР("Продукт 1";Таблица6;2).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Тип ошибки	Причина	Решение
	Неверное расположение столбца, по которому происходит поиск	Столбец таблицы, по которому происходит поиск ОБЯЗАТЕЛЬНО должен быть крайним левым. Перенесите столбец, по которому происходит поиск в крайнее левое положение таблицы. Или создайте вспомогательный дублирующий столбец, слева в таблице.
	Не закреплен диапазон таблицы	Если первое значение было выведено правильно, а после протягивания формулы ВПР в некоторых ячейках встречается ошибка #Н/Д, то диапазон таблицы не закреплен. Используйте ($) для закрепления диапазона таблицы, чтобы при заполнении формула использовала один и тот же диапазон. Или используйте
	Не удалось найти точное совпадение (если в интервальном просмотре выбран поиск точного значения (0)
	В интервальном просмотре выполняется поиск ближайшего значения (1), а таблица , по которой происходит поиск не отсортирована.	Отсортируйте первый столбец таблицы по возрастанию наименований.
		Используйте функции ПЕЧСИМВ или СЖПРОБЕЛЫ.
	Значение номер столбца превышает число столбцов в таблице	Проверьте номер столбца, содержащий возвращаемое значение.
	В формуле пропущены кавычки	Если вы используете в качестве искомого значения не ссылку на ячейку, а текст, то его необходимо заключить в кавычки. Например: ВПР("Продукт 1"; Цены!$A$2:$B$8;2;0)

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

Это шестая глава книги Билла Джелена. .

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

Скачать заметку в формате или , примеры в формате

Стратегия: вы можете решить задачу с помощью нескольких операторов ЕСЛИ или с использовав необычную формой функции ВПР. При использовании функции ЕСЛИ начните проверку с самого большого значения. Например, в ячейке F2 (рис. 6.2.) содержится значение $22810. Проверка F2 > 20000 возвратит значение ИСТИНА, но и проверка F2 > 1000 также вернет ИСТИНА. В формуле ниже выбрана правильная последовательность проверок. Результат ЕСЛИ умножается на выручку в F2. Это избавит вас от необходимости вводить F2 пять раз.

Использована формула:

ЕСЛИ(F2>20000;0,02;
ЕСЛИ(F2>15000;0,0125;
ЕСЛИ(F2>10000;0,01;
ЕСЛИ(F2>7500;0,0025;
ЕСЛИ(F2>1000;0,001;0)))))*F2

Как бонусный план становится еще более сложным, вы должны увеличить количество вложений. Excel2013 позволяет вложить 32 оператора ЕСЛИ. Еще совсем недавно (в Excel 2003) можно было вложить только 7 операторов ЕСЛИ. Довольно быстро этот метод становится громоздким.

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

В таблице подстановки (L2:M8) продажи $22 810 отсутствуют. Используя типичную ВПР с третьим аргументом ЛОЖЬ, мы получили бы ошибку #Н/Д. Когда вы опускаете третий аргумент (или используете значение ИСТИНА), ВПР будет искать ближайшее значение, меньшее, чем $22 810. В нашем случае ВПР вернет значение 2% для значения $20 000 чуть меньшего, чем $22 810.

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

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

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

В этом примере, пользователь с помощью выпадающих меню в ячейках J2 и J3 выбирает артикул и месяц (рис. 6.4). Найдите объем продаж (в штуках) по артикулу А397 за апрель (рис. 6.5).

Ваша первая формула будет использовать ПОИСКПОЗ, чтобы найти строку в левом столбце таблицы. Формула в J5 =ПОИСКПОЗ(J2;$A$2:$A$20;0). Ответ 2 указывает, что A397 находится во второй строке таблицы подстановки. Вторая формула в J6 (рис. 6.6) =ПОИСКПОЗ(J3;B1:G1;0) ищет столбец в первой строке. Результат 4 указывает на то, что Апрель найден в четвертом столбце таблицы подстановки. Наконец, ИНДЕКС возвращает значение из таблицы B2:G20, беря столбец из J5 (второй) и строку из J6 (четвертую). В J7 формула =ИНДЕКС(B2:G20;J5;J6).

Избавьтесь от промежуточных вычислений, и создайте мега-формулу. Возьмите за основу формулу в J7 и замените ссылки на промежуточные вычисления, т.е. подставьте вместо J5 –> ПОИСКПОЗ(J2;$A$2:$A$20;0), а вместо J6 –> ПОИСКПОЗ(J3;B1:G1;0). Используйте для этого копирование из строки формул, не беря в буфер знак равенства. Вот что у вас получится: =ИНДЕКС(B2:G20;ПОИСКПОЗ(J2;$A$2:$A$20;0);ПОИСКПОЗ(J3;B1:G1;0)).

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

Поиск по двум столбцам. Проблема: мне нужно найти объем продаж (в штуках) по коду компании и номеру Центра (рис. 6.9).

Рис. 6.9. Поиск по Компании и Центру

Стратегия: есть три варианта решения этой проблемы: а) сцепленный ключ, (б) СУМЕСЛИМН, (с) СМЕЩ. Сцепленный ключ будет работать только если вы можете добавить новый столбец слева от столбца С. СУММЕСЛИМН будет работать только если возвращаемое значение является числовым. СМЕЩ будет работать только если столбцы Компания и Центр отсортированы, как показано выше.

Сцепленный ключ. Вставьте новый столбец между В и C. Вы хотите объединить содержимое столбца А, разделитель, и содержимое столбца В. Например, =А2& " - " &В2 подготовит ключ 100-1010 (рис. 6.10).

Разделитель текста использовать необязательно. Однако, если вы не используете разделитель, у вас может получиться один и тот же ключ для двух разных связок Компания/Центр (рис. 6.11). Использование дефиса предотвратит эту двусмысленность. Если ваши данные уже содержат дефис, в качестве разделителя можно использовать что-то вроде вертикальной черты |.

После создания столбца со сцепленным ключом в таблице подстановки, вы можете использовать ВПР (рис. 6.12). Формула в G5: =ВПР(G2& " - " &G3;$C$2:$D$22;2;ЛОЖЬ).

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

СУМЕСЛИМН. Если значение, которое вы пытаетесь вернуть, численное, вы можете использовать СУММЕСЛИМН, БДСУММ, СУММПРОИЗВ. Из этих трех, СУММЕСЛИМН самая простая, но она будет работать, лишь начиная с версии Excel 2007 (рис. 6.13).

Рис. 6.13. Решение с помощью СУММЕСЛИМН (здесь и далее, если рисунок кажется мелким, кликните на нем правой кнопкой мыши, и выберите Открыть картинку в новой вкладке )

Первый аргумент СУММЕСЛИМН – диапазон суммирования, в нашем случае С2:С22. Именно данные из этого диапазона войду в итоговую сумму при выполнении условий. Вот почему требуется, чтобы эти данные были числовыми. В противном случае, функция не будет работать. Далее у вас есть пары аргументов, которые определяют диапазон соответствия критерию и сам критерий (можно ввести до 127 пар). Первый диапазон критериев – Компании (А2:А22). Вы хотите найти записи, в которых значение в столбце Компания равно 200 (значение из F2). Следующий диапазон критериев – Центры (В2:В22). Вы хотите найти записи, в которых Центр совпадает со значением в F3. В нашем случае обоим критериям (компания = 200 и центр = 1030) отвечает единственная строка – 11. Только она даст вклад в сумму и СУММЕСЛИМН вернет единственное значение – 15 097 из ячейки D11. Формула в F5: =СУММЕСЛИМН($C$2:$C$22;$A$2:$A$22;F2;$B$2:$B$22;F3).

Если вы можете вместо вертикального диапазона критериев (F2:F3) использовать горизонтальный (например, F2:G2), вам подойдет и слегка устаревшая функция БДСУММ (рис. 6.15). Синтаксис функции: БДСУММ(база_данных;поле;условия). Укажите А1:С22 в качестве базы данных, в поле – 3 (результат извлечь из третьего столбца таблицы), условия – диапазон F1:G2.

Рис. 6.15. БДСУММ также справится с проблемой, но диапазон критериев должен иметь заголовок и располагаться горизонтально

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

Синтаксис: СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина]). Например, вы могли бы начать со ссылки на В1 (начало отсчета), спуститься вниз на N строк, сместиться вправо на 0 столбцов (выбрать новую точку отсчета – левую верхнюю ячейку диапазона, который будет возвращен функцией СМЕЩ), и выбрать сам диапазон в 7 строк в высоту и 2 столбца в ширину (желтая область на рисунке).

Функции ПОИСКПОЗ ищет первую строку в диапазоне А2:А22, в которой встречается искомая компания = 200, и возвращает значение 8. Это значение будет указывать функции СМЕЩ насколько сместиться по столбцу В, определяя левый верхний угол диапазона – В9. В ячейке F6 СЧЕТЕСЛИ выясняет, как много ячеек в столбце А, содержащих значение 200 (из ячейки F2). Функция СЧЕТЕСЛИ возвращает значение 7, которое будет использовано в четвертом аргументе функции СМЕЩ и определит высоту диапазона – (с 9-й по 15-ю строку). Можете составить единую формулу, избавившись от промежуточных вычислений:

ВПР(F3;СМЕЩ($B$1;ПОИСКПОЗ(F2;A2:A22;0);0;СЧЁТЕСЛИ(A:A;F2);2);2;ЛОЖЬ)

Сумма всех поисков. Проблема: есть ли какие-то интересные трюки со старой функцией поиска – ПРОСМОТР, которые можно было бы использовать в этой главе, рассказывающей о расширенном поиска?

Допустим, вы хотите оценить общие премиальные выплаты за месяц (рис. 6.17). Вы пока не рассчитываете бонусы по каждому сотруднику, а лишь хотите знать общую сумму бонусов. Формула СУММ(ВПР) не будет работать, даже если вы используете Ctrl+Shift+Enter, чтобы ввести формулу массива. А вот СУММ(ПРОСМОТР) введенная с помощью Ctrl+Shift+Enter будет работать. Правда, функция ПРОСМОТР выполняет лишь приблизительный поиск (т.е., подобна ВПР с аргументом ИСТИНА). В ячейку G8 введите формулу =СУММ(ПРОСМОТР(C2:C26;G2:G6;H2:H6)), но не нажимайте Enter, а нажмите Ctrl+Shift+Enter, завершая ввод формулы массива (если вы не знакомы с формулами массива, см. ).

ВПР, ссылающаяся на другой лист. Проблема: таблица подстановки хранится на другом листе книги. Как сделать ВПР, указывающую на другой лист (рис. 6.18)?

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

Ссылки типа А2:С29 предполагает, что диапазон расположен на том же листе, что и формула. Чтобы сослаться на другой рабочий лист, официальный синтаксис требует набрать ‘имя листа’!А2:С29. Вы заключаете имя листа в апострофы, затем ставите восклицательный знак, и, наконец, даете ссылку на диапазон ячеек. Если вы введите формулу ВПР с указанным синтаксисом, то получите (рис. 6.19):

ВПР(A2;
‘Таблица просмотра’!$A$1:$C$29;
3;ЛОЖЬ)

Синтаксис слегка изменится если лист, на который ссылаются, не имеет пробелов в имени. В таком случае, можно опустить в апострофы. Формула становится: =ВПР(A38;ТаблицаПросмотра!$A$1:$C$29;3;ЛОЖЬ). Обратите внимание, что при переименовании листа в формуле ВПР изменения происходят автоматически. Excel сам удаляет или добавляет апострофы, во всех формулах, указывающих на переименованный лист.

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

1. Перейдите на лист с таблицей подстановки.
2. Выделите весь диапазон, включающий таблицу подстановки. В данном примере A2:С29.
3. Щелкните в поле имя слева от строки формул.
4. Введите имя и нажмите Enter. Не используйте пробелы в имени.

Когда вы доберетесь до второго аргумента функции ВПР, начните вводить «Та…». Появится подсказка (рис. 6.21). Для выбора из списка используйте стрелку вниз для перемещения к строке выбора. Затем нажмите клавишу Tab, чтобы вставить этот фрагмент в формулу. Не пытайтесь использовать Enter. Так как формула еще не завершена, вы получите ошибку. Вместо этого можете дважды щелкнуть мышью на строке «ТабПодст».

В результате вы получите формулу: =ВПР(A2;ТабПодст;3;ЛОЖЬ). Она легче для восприятия, и не требует знания специальных синтаксических правил.

Помните, что одно и тоже имя можно использовать лишь один раз в книге. В принципе, вы можете создать именованный диапазон ТабПодст еще и на Лист1 (рис. 6.22), но он будет определен не во всей книге, а только на Лист1. В этом случае вы можете использовать оба имени диапазона, но опять, как и выше предварить имя нужно будет названием листа и восклицательным знаком: Лист1!ТабПодст. Чтобы не усложнять себе жизнь используйте только разные имена в одной книге.

Альтернативная стратегия: используйте сочетания клавиш, чтобы перейти на другой лист. Начните набирать формулу =ВПР(А2; (рис. 6.23). В этот момент нажмите одновременно Ctrl+PgDn, и вы перейдете на следующий лист. Вы можете повторно нажать Ctrl+PgDn, чтобы перейти к следующему листу. Когда вы окажетесь на нудном листе выберите мышкой диапазон таблицы подстановки, и продолжите набор формулы.

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

• Пройдите по меню Вид –> Новое окно ; появится вторая такая же по содержанию книга Excel; к имени первого файла прибавится двоеточие и цифра 1, второго – цифра 2.
• Пройдите по меню Вид –> Упорядочить все .
• В открывшемся диалоговом окне Расположение окон выберите Рядом и Только окна текущей книги (последняя опция пригодится, если у вас было первоначально открыто несколько книг, а работать вы хотите только с одной книгой и ее дублем; рис. 6.24).
• Во втором окне перейдите на лист с таблицей подстановки; создайте формулу ВПР с помощью набора с клавиатуры и мыши.
• Активизируйте окно с:2 в названии; закройте окно кликнув на крестик в правом верхнем углу.

Рис. 6.24. Два окна одной книги рядом; в разных окнах активны разные листы книги

ВПР, ссылающаяся на другую книгу. Проблема: как создать ВПР, ссылающуюся на другую книгу?

В этом разделе для иллюстрации использованы два файла: , .

Подводный камень: этот прием хорошо работает, когда вторая книга открыта. Когда книга закрыта, вы ограничены чтением 10 000 ячеек из закрытой книги. Это не так много, как кажется: если столбцов три, то это 3333 записей, а если столбцов 20, то лишь 499 записей. Проверьте, что ваша таблица подстановки не превышает лимит в 10К ячеек, прежде чем ссылаться на закрытую книгу.

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

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

1. Начните создавать формулу =ВПР(А2;
2. В этот момент нажмите Ctrl+Tab, чтобы переключиться на другую книгу. Обратите внимание, что вы все еще находитесь в режиме редактирования формулы; при этом активной стала книга, содержащая таблицу подстановки.
3. Используйте мышь или клавиши со стрелками для выбора таблицы подстановки. Обратите внимание, что, как только вы выберите таблицу, Excel автоматически добавит знак доллара в этой части формулы (рис. 6.25). Майкрософт считает, что в 90% случаев ссылки на связанные книги должны быть абсолютными, поэтому он автоматически делает их такими. Не нажмите по привычке F
4. Введите оставшуюся часть формулы: ;3;ЛОЖЬ) .
5. Нажмите Enter. Вы вернетесь в исходную книгу

Давайте рассмотрим синтаксис формулы: =ВПР(A2;"Product Table’!$A$2:$C$29;3;ЛОЖЬ).

• Открывающий апостроф
• Имя файла в квадратных скобках
• Имя листа
• Закрывающий апостроф
• Восклицательный знак
• Ссылка на диапазон

Есть два иных варианта синтаксиса формулы. Если, ни имя файла, ни имя листа не содержать пробелов, цифр, или специальных символов, формула не содержит апострофов: =ВПР(А2;ProductTable!$А$2:$С$29;3;ЛОЖЬ).

Если книга с таблицей подстановки закрыта, то Excel вставит в формулу ВПР путь к книге: =ВПР(A2;’D:\Dropbox\ДжеленВПР\Глава 6\Product Table’!$A$2:$C$29;3;ЛОЖЬ)

Рис. 6.28. При открытии файла выберите Обновить

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

Подводный камень: не переименовывайте и перемещайте книгу, после того как вы создали ссылку на нее. Если вы это сделаете, то должны будете отредактировать ссылки в окне Изменение связей (см. рис. 6.29).

Вернуть последнюю запись. Проблема: некто регистрируется данные по группам. Одна группа – один столбец. Для каждой группы данные начинаются в 5-й строке. Существует различное количество данных в каждом столбце. Мне нужно получить последнюю запись в каждой группе.

Стратегии: существует несколько способов решения задачи. Например, вы могли бы совместить СМЕЩ с СЧЁТЗ, но сейчас проблема будет решить с использованием ВПР с параметром ИСТИНА.

Вернемся к рис. 6.2 и 6.3, где ВПР ищет ставку комиссионного вознаграждения. В таблице подстановки были записи 1000, 7500, 10 000, 15 000 и 20 000. Когда у кого-то была продажа $12 345, то ВПР искала комиссию для ближайшего меньшего значения – $10 000.

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

Обратите внимание, что в столбце G формула не сбилась из-за наличия пробела. В то же время, формула проигнорировала ZZZ в Н9 и #Н/Д в F10.

Если вы ищите последнюю текстовую запись в столбце, то используйте в качестве поисковой строки текст, который будет превосходить всё разумное в алфавитном порядке, например, ЯЯЯЯЯ (рис. 6.32). Столбцы F и H иллюстрируют проблему этого метода. Если среди значений есть, и текст, и числа, и значения ошибок, ВПР не будет работать. Точнее, вернет последнее текстовое значение.

Что делать, если данные повернуто боком, и вам нужно получить последнее значение из каждой строки? Использовать ГПР вместо ВПР (рис. 6.33).

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

Возвращение последнего из найденных значений. Проблема: функция ВПР возвращает первое найденное значение. Например, на рис. 6.34 для буквы А ВПР вернет значение 15. Мне нужно получить значение для последнего вхождения. На рис. 6.34 – значение 12 для А .

Стратегия: используйте формулу =ПРОСМОТР(2;1/($A$1:$A$7=D2);$B$1:$B$7).

Во-первых, ПРОСМОТР – устаревшая функция, которая оставлена в Excel для обратной совместимости с Quattro Pro. Это необычная функция, имеющая три аргумента:

• искомое значение – значение, которое функция ПРОСМОТР ищет в первом векторе;
• просматриваемый вектор – диапазон, состоящий из одной строки или одного столбца; значения должны быть расположены в порядке возрастания: …, -2, -1, 0, 1, 2, …, A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может возвратить неправильный результат (текст в нижнем и верхнем регистрах считается эквивалентным);
• вектор результатов – диапазон, состоящий из одной строки или столбца; вектор результатов должен иметь тот же размер, что и просматриваемый вектор.

Функция ПРОСМОТР в отличие от ВПР не имеет четвертого аргумента – тип сравнения. Она всегда использует приблизительное совпадение (аналог ИСТИНА в ВПР). Как и любой иной приблизительный поиск, функция ПРОСМОТР дает корректные результаты если просматриваемый вектор (аналог таблицы подстановки в ВПР) отсортирован по возрастанию. Однако, так как в нашем примере вы пытаетесь обмануть Excel, таблицу А1:В7 сортировать не следует. (Пользователи применяют ПРОСМОТР вместо ВПР, как правило в случае, если поиск ведется внутри формулы массива, так как ВПР в этом случае не работает. Об этом см. ниже.)

За работой формулы =ПРОСМОТР(2;1/($A$1:$A$7=D2);$B$1:$B$7) можно проследить в окне Вычисление формулы (рис. 6.36). Чтобы открыть его выделите ячейку Е2 и пройдите по меню ГЛАВНАЯ –> Зависимости формул –> Вычислить формулу . Нажимайте кнопку Вычислить , и изучайте, как меняется содержимое окна Вычисление .

Рис. 6.36. Окно Вычисление формулы

Сравнение $A$1:$A$7=D2 сгенерит массив значений ЛОЖЬ/ИСТИНА (рис. 6.37). В нашем случае {ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ). Далее формула делит единицу на этот массив. Excel считает, что ИСТИНА = 1, а ЛОЖЬ = 0. Так что 1/1 =1, а 1/0 возвращает ошибку #ДЕЛ/0! После выполнения деления у вас образовался массив, состоящий из единиц и ошибок #ДЕЛ/0! Приблизительный ВПР игнорирует текстовые записи и значения ошибок. Аналогично и ПРОСМОТР. Теперь самое интересное. Почему я ищу 2 (первый аргумент функции ПРОСМОТР)? Потому, что массив возвращает только единицы и ошибки #ДЕЛ/0! Так что двойка будет заведомо больше любого значения массива (вспомните трюк с 9999999 выше). В итоге ПРОСМОТР вернет последнее значение, соответствующее единице (ошибки игнорируются). Что нам и было нужно))

Рис. 6.37. Пошаговое вычисление формулы =ПРОСМОТР(2;1/($A$1:$A$7=D2);$B$1:$B$7)

Дополнительные сведения: эту формулу я подсмотрел на форуме MrExcel.com . Кроме того, я нашел там немало диких формул, которые я когда-либо видел. Я взял коллекцию этих формул и издал их в своей книге Excel Gurus Gone Wild .

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

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

1. Выделите ячейку, содержащую формулу с ВПР (в примере ниже – С4).
2. Нажмите F2, чтобы перейти в режим редактирования формулы.
3. Выделите часть формулы, относящейся к таблице подстановки (рис. 6.38).
4. Нажмите F Это приведет к замене ссылок на значения (рис. 6.39).
5. Нажмите Enter, завершая редактирование формулы. Скопируйте формулу в другие ячейки.

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

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

• Выделите массив из формулы, включая фигурные скобки.
• Скопируйте символы из буфера обмена.
• Выберите в свободной части листа пустой диапазон из двух столбцов и пяти строк.
• В строке формул введите знак равенства. Нажмите Ctrl+V. Нажмите одновременно Ctrl+Shift+Ввод. Excel поместит массив обратно на лист как формулу массива. Он выглядит как обычная таблица, но нельзя изменять отдельные ячейки (рис. 6.40).
• Выделите таблицу целиком скопируйте ее в буфер, вставьте, как значения. Теперь вы можете редактировать отдельные ячейки в таблице.

Функция ВЫБОР вместо ВПР. Проблема: мне надо выбрать из пяти вариантов. Я не люблю использовать кучу вложенных ЕСЛИ, но и таблицу подстановки размещать на листе не хотелось бы. Есть ли функция, которая позволит мне указать возможные значения внутри самой функции?

Стратегии: в этой ситуации, вы можете использовать функцию ВЫБОР (рис. 6.41).

Первый аргумент функции – число от 1 до 254 (или ссылка на число). Затем необходимо указать значения для каждого возможного числа, введенные в виде отдельных аргументов. Например, =ВЫБОР(B6; " RO – без питания " ; " BB – только завтрак " ; " HB – завтрак и ужин " ; " FB – завтрак, обед и ужин " ; " AI – все включено " ; " UAI – ультра все включено ").

Немного неудобно, что вы должны указать каждый вариант как отдельный аргумент. Я всегда хочу указать все аргументы одним диапазоном, например, В8:В13, но это не будет работать. Однако, если у вас где-то на листе уже есть список аргументов, вы не нуждаетесь в использовании функции ВЫБОР; примените ВПР или ИНДЕКС.

В нашем примере функция ВЫБОР возвращает описание типа питания на основе кода, взятого в ячейке В6. Обратите внимание, что ВЫБОР работает только если у вас цифровые коды (1, 2, 3…). Если у вас буквенные коды (а, б, в…), вам, вероятно, не обойтись без таблицы подстановки. Или вы можете преобразовать буквы в цифры, используя функцию КОДСИМВ(). Например, для строчных русских букв: =КОДСИМВ([ссылка на ячейку с буквой])-223, а для прописных английских: =КОДСИМВ()-64.

Если всё же у вас есть где-то на листе список названий, у вас может появиться искушение ввести формулу =ВЫБОР(В8;В9;В10;В11;В12;В13). Вместо этого, проще использовать =ИНДЕКС(B8:B13;B6). Функция возвратит 3-й пункт из списка в В8:В13 (рис. 6.42).

Я не хочу использовать ВПР. Проблема: я дочитал книгу до этой страницы, и не полюбил ВПР. Есть ли что-нибудь, что будет делать ВПР для меня?

Стратегия: возможны два варианта: PowerPivot и Easy-XL.

Easy-XL – это сторонняя утилита, которая, в частности, включает механизмы подобные ВПР. Скачайте бесплатную 30-дневную пробную версию Easy-XL с сайта

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

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

Итак, чтобы решить задачу в нашем примере нам необходимо сначала заполнить столбец «С» в таблице заказов, т.е найти цену товаров в таблице «Прайс лист», а затем, чтобы узнать стоимость — перемножить цену на количество товаров.

Синтаксис функции ВПР (VLOOKUP) в Excel

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

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

Разберем функцию на нашем примере. Вставим курсов в ячейку C3 и наберем формулу =ВПР(A3;$F$2:$H$22;3;0)

в английской версии =VLOOKUP (A3;$F$2:$H$22;3;0)

В данной формуле с ВПР (англ. VLOOKUP):

A3 – искомое_значение . В нашем случае это «Ведро», т.е нам необходимо найти «ведро» в таблице «Прайс лист»

$F$2:$H$22 – таблица . В нашем примере это таблица «Прайс лист» (F2:H22). В диапазон данной таблицы вставлены знаки $ для его закрепления, чтобы он не сдвигался вниз, когда мы будем протягивать формулу. Знак доллара в Excel превращает относительный диапазон в абсолютный.

3 – номер столбца . В нашем случае это цифра «3», так как цена находится в третьем столбце нашей таблицы «Прайс лист».

0 – интервальный просмотр. Может принимать только два значения 0 или 1: 0 – ищет точное совпадение, 1 – приблизительное. В 99% случаях требуется искать точное значение (в нашем случае нам необходимо искать слово «Ведро»). Поэтому практически всегда указывается цифра 0.

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

После этого переходим в ячейку D3 и находит стоимость товаров. Прописываем формулу =C3*B3 , т.е перемножаем цену товара на количество.

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

Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (3)

Как найти значение в другой таблице или сила ВПР

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

и из первой таблицы необходимо подставить во вторую дату для каждой фамилии. Для трех записей это не проблема и руками сделать - все очевидно. Но в жизни это таблицы на тысячи записей и поиск с подстановкой данных вручную может занять не один час. Плюс еще пара ложек дегтя: мало того, что ФИО расположены совершенно в разном порядке в обеих таблицах и количество записей в таблицах разное, так еще таблицы расположены на разных листах/книгах. Я полагаю, что убедил вас в том, что подстановка данных руками вообще не вариант. Зато ВПР (VLOOKUP) здесь будет незаменима. При этом практически ничего не надо будет делать - только записать в первую ячейку столбца С второй таблицы(туда, куда необходимо подставить даты из первой таблицы) такую формулу:
=ВПР($A2 ;Лист1!$A$2:$C$4 ;3;0)
Записать формулу можно либо непосредственно в ячейку, либо воспользовавшись диспетчером функций , выбрав в категории Ссылки и массивы ВПР и по отдельности указав нужные критерии. Теперь копируем(Ctrl +C ) ячейку с формулой, выделяем все ячейки столбца С до конца данных и вставляем(Ctrl +V ).

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

Описание аргументов ВПР
$A2 - аргумент Искомое_значение (назовем его Критерий для краткости). Это то, что мы ищем. Т.е. для первой записи второй таблицы это будет Петров С.А. Здесь можно указать либо непосредственно текст критерия(в этом случае он должен быть в кавычках - =ВПР("Петров С.А" ;Лист1!$A$2:$C$4;3;0) , либо ссылку на ячейку, с данным текстом(как в примере функции) . Есть небольшой нюанс: так же можно применять символы подстановки: "*" и "?". Это очень удобно, если Вам надо найти значения лишь по части строки. Например, Вы можете не вводить полностью "Петров С.А", а ввести лишь фамилию и знак звездочки - "Петров*". Тогда будет выведена любая запись, которая начинается на "Петров". Если же Вам надо найти запись, в которой в любом месте строки встречается фамилия "Петров" , то можно указать так: "*петров*" . Если хотите найти фамилию Петров и неважно какие инициалы будут у имени-отчества(если ФИО записаны в виде Иванов И.И.), то здесь в самый раз такой вид: "Иванов?.?." . Часто необходимо для каждой строки указать свое значение(в столбце А Фамилии и надо их все найти). В таком случае всегда указываются ссылки на ячейки столбца А. Например, в ячейке A1 записано: Иванов. Так же известно, что Иванов есть в другой таблице, но после фамилии могут быть записаны и имя и отчество(или еще что-то). Но нам нужно найти только строку, которая начинается на фамилию. Тогда необходимо записать следующим образом: A1 &"*" . Эта запись будет равнозначна "Иванов*" . В A1 записано Иванов, амперсанд(&) используется для объединения в одну строку двух текстовых значений. Звездочка в кавычках (как и положено быть тексту внутри формулы). Таким образом и получаем:
A1&"*" =>
"Иванов"&"*" =>
"Иванов*"
Очень удобно, если значений для поиска много.
Если надо определить есть ли хоть где-то слово в строке, то звездочки ставим с обеих сторон: "*"& A1 &"*"

Лист1!$A$2:$C$4 - аргумент Таблица . Указывается диапазон ячеек. Только диапазон должен содержать данные от первой ячейки с данными до самой последней. Это не обязательно должен быть указанный в примере диапазон. Если строк 100, то Лист1!$A$2:$C$100 . Важно помнить три вещи: первое, это Таблица всегда должна начинаться с того столбца, в котором ищем Критерий . И никак иначе. В противном случае ничего найдено не будет или результат будет совсем не тот, которого ожидаете. Второе: аргумент Таблица должен быть "закреплен" . Что это значит. Видите знаки доллара - $? Это и есть закрепление(если точнее, то это называется абсолютной ссылкой на диапазон) . Как это делается. Выделяете текст ссылки(только один диапазон - один критерий) и жмете F4 до тех пор, пока не увидите, что и перед обозначением имени столбца и перед номером строки не появились доллары. Если этого не сделать, то при копировании формулы аргумент Таблица будет "съезжать" и результат опять-таки будет неверным. И последнее - таблица должна содержать столбцы от первого(в котором ищем) до последнего(из которого необходимо возвращать значения). В примере Лист1!$A$2:$C$4 - значит не получится вернуть значение из столбца D(4), т.к. в таблице только три столбца.

3 - Номер_столбца . Здесь просто указываем номер столбца в аргументе Таблица , значения из которого нам необходимо подставить в качестве результата. В примере это Дата принятия - т.е. столбец №3. Если бы нужен был отдел, то указали бы 2, а если бы нам понадобилось просто сравнить есть ли фамилии одной таблицы в другой, то можно было бы указать и 1. Важно: аргумент Номер_столбца не должен превышать кол-во столбцов в аргументе Таблица . Иначе результатом формулы будет ошибка #ССЫЛКА! . Например, если в качестве указан диапазон $B$2:$C$4 и необходимо вернуть данные из столбца С, то правильно указать 2. Т.к. аргумент Таблица ($B$2:$C$4) содержит только два столбца - В и С. Если же попытаться указать номер столбца 3(каким по счету он является на листе), то получим ошибку #ССЫЛКА! , т.к. третьего столбца в указанном диапазоне просто нет.

Практический совет: если аргумент Таблица имеет слишком большое кол-во столбцов и Вам необходимо вернуть результат из последнего столбца, то совсем необязательно высчитывать их кол-во. Можно указать так: =ВПР($A2 ;Лист1! $A$2:$C$4 ;ЧИСЛСТОЛБ(Лист1! $A$2:$C$4);0) . К слову в данном случае Лист1! тоже можно убрать, как лишнее: =ВПР($A2 ;Лист1! $A$2:$C$4 ;ЧИСЛСТОЛБ($A$2:$C$4);0) .

0 - Интервальный_просмотр - очень интересный аргумент. Может быть равен либо ИСТИНА либо ЛОЖЬ. Сразу возникает вопрос: а почему в моей формуле там 0? Все очень просто - Excel в формулах может воспринимать 0 как ЛОЖЬ, а 1 как ИСТИНА. Если в ВПР указать данный параметр равный 0 или ЛОЖЬ, то будет происходить поиск точного соответствия заданному Критерию. Это не имеет никакого отношения к знакам подстановки("*" и "?"). Если же использовать 1 или ИСТИНА(или же вообще не указывать последний аргумент, т.к. по умолчанию он равен ИСТИНА), то...Очень долгая история. Вкратце - ВПР будет искать наиболее похожее значение, подходящее под Критерий . Иногда очень полезно. Правда, если использовать данный параметр, то необходимо, чтобы список в аргументе Таблица был отсортирован по возрастанию. Обращаю внимание на то, что сортировка необходима только в том случае, если аргумент Интервальный_просмотр у Вас равен ИСТИНА или 1. Если же 0 или ЛОЖЬ - сортировка не нужна.

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

Как избежать ошибки #Н/Д(#N/A) в ВПР?
Еще частая проблема - многие не хотят видеть #Н/Д результатом, если совпадение не найдено. Это легко обойти:
=ЕСЛИ(ЕНД(ВПР($A2 ;Лист1! $A$2:$C$4 ;3;0));"";ВПР($A2 ;Лист1! $A$2:$C$4 ;3;0)))
Теперь если ВПР не найдет совпадения, то ячейка будет пустой.
А пользователям версий Excel 2007 и выше можно использовать ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ВПР($A2 ;Лист1! $A$2:$C$4 ;3;0);"")

Обещанная ПОИСКПОЗ

Данная функция ищет значение, указанное параметром Искомое_значение в аргументе Просматриваемый_массив . А результатом функции является номер позиции найденного значения в Просматриваемом_массиве . Именно номер позиции, а не само значение. В принципе её я не буду расписывать так же подробно, потому как основные моменты ровно такие же. Если бы мы хотели применить её для таблицы выше, то она была бы такой:
=ПОИСКПОЗ($A2 ;Лист1! $A$2:$A$4 ;0)
$A2 - Искомое_значение. Здесь все ровно так же, как и с ВПР. Так же допустимы символы подстановки и ровно в таком же исполнении.

Лист1! $A$2:$A$4 - Просматриваемый_массив. Основное отличие от ВПР - допускается указать массив лишь с одним столбцом. Это должен быть тот столбец, в котором мы собираемся искать Искомое_значение . Если попытаться указать более одного столбца, то функция вернет ошибку.

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

С основным разобрались. Но ведь нам надо вернуть не номер позиции, а само значение. Значит ПОИСКПОЗ в чистом виде нам не подходит. По крайней мере одна, сама по себе. Но если её использовать вместе с функцией ИНДЕКС - то это то, что нам нужно и даже больше.
=ИНДЕКС(Лист1! $A$2:$C$4 ;ПОИСКПОЗ($A2 ;Лист1! $A$2:$A$4 ;0);2)
Такая формула результатом вернет то же, что и ВПР.

Аргументы функции ИНДЕКС
Лист1! $A$2:$C$4 - Массив. В качестве этого аргумента мы указываем диапазон, из которого хотим получить значения. Может быть как один столбец, так и несколько. В случае, если столбец один, то последний аргумент функции указывать не надо. К слову - данный аргумент может совершенно не совпадать с тем, который мы указываем в аргументе Просматриваемый_массив функции ПОИСКПОЗ.

Далее идут Номер_строки и Номер_столбца. Именно в качестве Номера_строки мы и подставляем ПОИСКПОЗ, которая возвращает нам номер позиции в массиве. На этом все и строится. ИНДЕКС возвращает значение из Массива, которое находится в указанной строке(Номер_строки) Массива и указанном столбце(Номер_столбца), если столбцов более одного. Важно знать, что в данной связке кол-во строк в аргументе Массив функции ИНДЕКС и кол-во строк в аргументе Просматриваемый_массив функции ПОИСКПОЗ должно совпадать. И начинаться с одной и той же строки. Это в обычных случаях, если Вы не преследуете иные цели.
Так же как и в случае с ВПР, ИНДЕКС в случае не нахождения искомого значения возвращает #Н/Д. И обойти подобные ошибки можно так же:
Для всех версий Excel(включая 2003 и раньше):
=ЕСЛИ(ЕНД(ПОИСКПОЗ($A2 ;Лист1! $A$2:$A$4 ;0));"";ИНДЕКС(Лист1! $A$2:$C$4 ;ПОИСКПОЗ($A2 ;Лист1! $A$2:$A$4 ;0);2))
Для версий 2007 и выше:
=ЕСЛИОШИБКА(ИНДЕКС(Лист1! $A$2:$C$4 ;ПОИСКПОЗ($A2 ;Лист1! $A$2:$A$4 ;0);2);"")

Работа с критериями длиннее 255 символов
Есть у ИНДЕКС-ПОИСКПОЗ и еще одно преимущество перед ВПР. Дело в том, что ВПР не может искать значения, длина строки которых содержит более 255 символов . Это случается редко, но случается. Можно, конечно, обмануть ВПР и урезать критерий:
=ВПР(ПСТР($A2 ;1;255);ПСТР(Лист1!$A$2:$C$4 ;1;255);3;0)
но это формула массива . Да и к тому же далеко не всегда такая формула вернет нужный результат. Если первые 255 символов идентичны первым 255 символам в таблице, а дальше знаки различаются - формула этого уже не увидит. Да и возвращает формула исключительно текстовые значения, что в случаях, когда возвращаться должны числа, не очень удобно.

Поэтому лучше использовать такую хитрую формулу:
=ИНДЕКС(Лист1!$A$2:$C$4 ;СУММПРОИЗВ(ПОИСКПОЗ(ИСТИНА;Лист1!$A$2:$A$4 =$A2 ;0));2)
Здесь я в формулах использовал одинаковые диапазоны для удобочитаемости, но в примере для скачивания они различаются от указанных здесь.
Сама формула построена на возможности функции СУММПРОИЗВ преобразовывать в массивные вычисления некоторых функций внутри неё. В данном случае ПОИСКПОЗ ищет позицию строки, в которой критерий равен значению в строке. Подстановочные символы здесь применить уже не получится.

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

Скачать пример

(26,0 KiB, 13 776 скачиваний)

Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}