Формулы с функциями впр и поискпоз для выборки данных в 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 сам удаляет или добавляет апострофы, во всех формулах, указывающих на переименованный лист.

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

Перейдите на лист с таблицей подстановки.
Выделите весь диапазон, включающий таблицу подстановки. В данном примере A2:С29.
Щелкните в поле имя слева от строки формул.
Введите имя и нажмите 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К ячеек, прежде чем ссылаться на закрытую книгу.

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

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

Начните создавать формулу =ВПР(А2;
В этот момент нажмите Ctrl+Tab, чтобы переключиться на другую книгу. Обратите внимание, что вы все еще находитесь в режиме редактирования формулы; при этом активной стала книга, содержащая таблицу подстановки.
Используйте мышь или клавиши со стрелками для выбора таблицы подстановки. Обратите внимание, что, как только вы выберите таблицу, Excel автоматически добавит знак доллара в этой части формулы (рис. 6.25). Майкрософт считает, что в 90% случаев ссылки на связанные книги должны быть абсолютными, поэтому он автоматически делает их такими. Не нажмите по привычке F
Введите оставшуюся часть формулы: ;3;ЛОЖЬ) .
Нажмите 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 строк, скрытая в столбцах АА:АВ. Торговые представители, которые используют файл, могут случайно удалить строку в своих данных, не учитывая, что при этом удалиться часть таблицы подстановки. Как мне надежно спрятать таблицу подстановки?

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

Выделите ячейку, содержащую формулу с ВПР (в примере ниже – С4).
Нажмите F2, чтобы перейти в режим редактирования формулы.
Выделите часть формулы, относящейся к таблице подстановки (рис. 6.38).
Нажмите F Это приведет к замене ссылок на значения (рис. 6.39).
Нажмите 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 – это функция ВПР (VLOOKUP). О функции можно сказать многое, но самое первое, что стоить отметить, это то, что функция относится к сложным и наименее понятной.

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

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

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

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

искомое_значение – это именно то значение, которое нам нужно искать, а это может быть любое значение: число, дата, текст, ссылка на ячейку, которая содержит нужное значение или значение, получаемое другой формулой;
таблица – это два и больше столбика с разнообразными данными, кстати, регистр символов в поиске, функцией не учитывается;
номер_столбца – это номер столбика в указанном диапазоне, из которого будет получено значение, которое находится в найденной строке;
интервальный_просмотр – этот параметр определяет, что же именно будем искать, для точного совпадения аргумент будет равен «ЛОЖЬ» (FALSE) или приблизительное совпадение, аргумент станет равным ИСТИНА (TRUE) . Этот параметр не является обязательным, но тем не менее он важен. В примерах, далее, я продемонстрирую, как создать формулы для точного и приблизительного совпадения.

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

ВПР(«GM»;$A$5:$B$10;2)

Формула ищет текст «GM», в столбике А на текущем листе.
Совет! При использовании аргумента «таблица», желательно использовать, такой вариант, как (это адрес ячейки со знаком $). В этом случае диапазон поиска станет закреплённым и не изменится при копировании формулы.

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

ВПР(”GM”;[Путь к файлу]База!А2:В10;2)

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

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

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

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

«?» (знак вопроса ) – позволит заменить один любой символ;
«*» (звёздочка) – заменит любое количество и последовательность символов.

ВПР(“A*”;$A$2:$B$10;1;ЛОЖЬ)

Совет! Для того что бы функция ВПР, корректно работала нужно в качестве четвертого аргумента использовать параметр «ЛОЖЬ».
Ну, если мы уже затронули тему точного или приближенного совпадения в синтаксисе функции ВПР, то давайте ее рассмотрим поподробнее:

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

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

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

=ВПР(200;$A$2:$B$15;2;ЛОЖЬ)

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

Для поиска используем ВПР следующего вида:

=ВПР(260;$A$2:$B$15;2;ИСТИНА)

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

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

А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Если произвести поиск по функциям подстановки, 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, и её многочисленные ограничения могут помешать Вам получить желаемый результат во многих ситуациях. С другой стороны, функции ИНДЕКС и ПОИСКПОЗ – более гибкие и имеют ряд особенностей, которые делают их более привлекательными, по сравнению с ВПР .

Базовая информация об ИНДЕКС и ПОИСКПОЗ

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

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

ИНДЕКС – синтаксис и применение функции

Функция INDEX (ИНДЕКС) в Excel возвращает значение из массива по заданным номерам строки и столбца. Функция имеет вот такой синтаксис:

Каждый аргумент имеет очень простое объяснение:

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

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

Вот простейший пример функции INDEX (ИНДЕКС):

INDEX(A1:C10,2,3)
=ИНДЕКС(A1:C10;2;3)

Формула выполняет поиск в диапазоне A1:C10 и возвращает значение ячейки во 2-й строке и 3-м столбце, то есть из ячейки C2 .

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

ПОИСКПОЗ – синтаксис и применение функции

Функция MATCH (ПОИСКПОЗ) в Excel ищет указанное значение в диапазоне ячеек и возвращает относительную позицию этого значения в диапазоне.

Например, если в диапазоне B1:B3 содержатся значения New-York, Paris, London, тогда следующая формула возвратит цифру 3 , поскольку “London” – это третий элемент в списке.

MATCH("London",B1:B3,0)
=ПОИСКПОЗ("London";B1:B3;0)

Функция MATCH (ПОИСКПОЗ) имеет вот такой синтаксис:

MATCH(lookup_value,lookup_array,)
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

lookup_value (искомое_значение) – это число или текст, который Вы ищите. Аргумент может быть значением, в том числе логическим, или ссылкой на ячейку.
lookup_array (просматриваемый_массив) – диапазон ячеек, в котором происходит поиск.
match_type (тип_сопоставления) – этот аргумент сообщает функции ПОИСКПОЗ , хотите ли Вы найти точное или приблизительное совпадение:
- 1 или не указан – находит максимальное значение, меньшее или равное искомому. Просматриваемый массив должен быть упорядочен по возрастанию, то есть от меньшего к большему.
- 0 – находит первое значение, равное искомому. Для комбинации ИНДЕКС /ПОИСКПОЗ всегда нужно точное совпадение, поэтому третий аргумент функции ПОИСКПОЗ должен быть равен 0 .
- -1 – находит наименьшее значение, большее или равное искомому значению. Просматриваемый массив должен быть упорядочен по убыванию, то есть от большего к меньшему.

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

Позвольте напомнить, что относительное положение искомого значения (т.е. номер строки и/или столбца) – это как раз то, что мы должны указать для аргументов row_num (номер_строки) и/или column_num (номер_столбца) функции INDEX (ИНДЕКС). Как Вы помните, функция ИНДЕКС может возвратить значение, находящееся на пересечении заданных строки и столбца, но она не может определить, какие именно строка и столбец нас интересуют.

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

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

Ещё не совсем понятно? Представьте функции ИНДЕКС и ПОИСКПОЗ в таком виде:

INDEX(,(MATCH (искомое значение ,столбец в котором ищем ,0))
=ИНДЕКС(столбец из которого извлекаем ;(ПОИСКПОЗ(искомое значение ;столбец в котором ищем ;0))

Думаю, ещё проще будет понять на примере. Предположим, у Вас есть вот такой список столиц государств:

Давайте найдём население одной из столиц, например, Японии, используя следующую формулу:

INDEX($D$2:$D$10,MATCH("Japan",$B$2:$B$10,0))
=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ("Japan";$B$2:$B$10;0))

Теперь давайте разберем, что делает каждый элемент этой формулы:

Функция MATCH (ПОИСКПОЗ) ищет значение “Japan” в столбце B , а конкретно – в ячейках B2:B10 , и возвращает число 3 , поскольку “Japan” в списке на третьем месте.
Функция INDEX (ИНДЕКС) использует 3 для аргумента row_num (номер_строки), который указывает из какой строки нужно возвратить значение. Т.е. получается простая формула:
INDEX($D$2:$D$10,3)
=ИНДЕКС($D$2:$D$10;3)
Формула говорит примерно следующее: ищи в ячейках от D2 до D10 и извлеки значение из третьей строки, то есть из ячейки D4 , так как счёт начинается со второй строки.

Вот такой результат получится в Excel:

Важно! Количество строк и столбцов в массиве, который использует функция INDEX (ИНДЕКС), должно соответствовать значениям аргументов row_num (номер_строки) и column_num (номер_столбца) функции MATCH (ПОИСКПОЗ). Иначе результат формулы будет ошибочным.

Стоп, стоп… почему мы не можем просто использовать функцию VLOOKUP (ВПР)? Есть ли смысл тратить время, пытаясь разобраться в лабиринтах ПОИСКПОЗ и ИНДЕКС ?

VLOOKUP("Japan",$B$2:$D$2,3)
=ВПР("Japan";$B$2:$D$2;3)

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

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

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

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

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

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

Например, если у Вас есть таблица A1:C10 , и требуется извлечь данные из столбца B , то нужно задать значение 2 для аргумента col_index_num (номер_столбца) функции ВПР , вот так:

VLOOKUP("lookup value",A1:C10,2)
=ВПР("lookup value";A1:C10;2)

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

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

3. Нет ограничения на размер искомого значения. Используя ВПР , помните об ограничении на длину искомого значения в 255 символов, иначе рискуете получить ошибку #VALUE! (#ЗНАЧ!). Итак, если таблица содержит длинные строки, единственное действующее решение – это использовать ИНДЕКС /ПОИСКПОЗ .

Предположим, Вы используете вот такую формулу с ВПР , которая ищет в ячейках от B5 до D10 значение, указанное в ячейке A2 :

VLOOKUP(A2,B5:D10,3,FALSE)
=ВПР(A2;B5:D10;3;ЛОЖЬ)

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

INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))
=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))

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

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

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

ИНДЕКС и ПОИСКПОЗ – примеры формул

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

Как выполнить поиск с левой стороны, используя ПОИСКПОЗ и ИНДЕКС

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

Функции ПОИСКПОЗ и ИНДЕКС в Excel гораздо более гибкие, и им все-равно, где находится столбец со значением, которое нужно извлечь. Для примера, снова вернёмся к таблице со столицами государств и населением. На этот раз запишем формулу ПОИСКПОЗ /ИНДЕКС , которая покажет, какое место по населению занимает столица России (Москва).

Как видно на рисунке ниже, формула отлично справляется с этой задачей:

INDEX($A$2:$A$10,MATCH("Russia",$B$2:$B$10,0))

Теперь у Вас не должно возникать проблем с пониманием, как работает эта формула:

Во-первых, задействуем функцию MATCH (ПОИСКПОЗ), которая находит положение “Russia” в списке:
MATCH("Russia",$B$2:$B$10,0))
=ПОИСКПОЗ("Russia";$B$2:$B$10;0))
Далее, задаём диапазон для функции INDEX (ИНДЕКС), из которого нужно извлечь значение. В нашем случае это A2:A10 .
Затем соединяем обе части и получаем формулу:
INDEX($A$2:$A$10;MATCH("Russia";$B$2:$B$10;0))
=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("Russia";$B$2:$B$10;0))

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

Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)

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

1. MAX (МАКС). Формула находит максимум в столбце D C той же строки:

INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))

Результат: Beijing

2. MIN (МИН). Формула находит минимум в столбце D и возвращает значение из столбца C той же строки:

INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))

Результат: Lima

3. AVERAGE (СРЗНАЧ). Формула вычисляет среднее в диапазоне D2:D10 , затем находит ближайшее к нему и возвращает значение из столбца C той же строки:

INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1))

Результат: Moscow

О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ

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

Если указываете 1 , значения в столбце поиска должны быть упорядочены по возрастанию, а формула вернёт максимальное значение, меньшее или равное среднему.
Если указываете -1 , значения в столбце поиска должны быть упорядочены по убыванию, а возвращено будет минимальное значение, большее или равное среднему.

В нашем примере значения в столбце D упорядочены по возрастанию, поэтому мы используем тип сопоставления 1 . Формула ИНДЕКС /ПОИСКПО З возвращает “Moscow”, поскольку величина населения города Москва – ближайшее меньшее к среднему значению (12 269 006).

Как при помощи ИНДЕКС и ПОИСКПОЗ выполнять поиск по известным строке и столбцу

Эта формула эквивалентна двумерному поиску ВПР и позволяет найти значение на пересечении определённой строки и столбца.

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

Как Вы помните, синтаксис функции INDEX (ИНДЕКС) позволяет использовать три аргумента:

INDEX(array,row_num,)
ИНДЕКС(массив;номер_строки;[номер_столбца])

И я поздравляю тех из Вас, кто догадался!

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

INDEX(Ваша таблица ,(MATCH(,столбец, в котором искать ,0)),(MATCH(,строка в которой искать ,0))
=ИНДЕКС(Ваша таблица ,(MATCH(значение для вертикального поиска ,столбец, в котором искать ,0)),(MATCH(значение для горизонтального поиска ,строка в которой искать ,0))

Обратите внимание, что для двумерного поиска нужно указать всю таблицу в аргументе array (массив) функции INDEX (ИНДЕКС).

А теперь давайте испытаем этот шаблон на практике. Ниже Вы видите список самых населённых стран мира. Предположим, наша задача узнать население США в 2015 году.

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

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

ПОИСКПОЗ для столбца – мы ищем в столбце B , а точнее в диапазоне B2:B11 , значение, которое указано в ячейке H2 (USA). Функция будет выглядеть так:
MATCH($H$2,$B$1:$B$11,0)
=ПОИСКПОЗ($H$2;$B$1:$B$11;0)
4 , поскольку “USA” – это 4-ый элемент списка в столбце B (включая заголовок).
ПОИСКПОЗ для строки – мы ищем значение ячейки H3 (2015) в строке 1 , то есть в ячейках A1:E1 :
MATCH($H$3,$A$1:$E$1,0)
=ПОИСКПОЗ($H$3;$A$1:$E$1;0)

Результатом этой формулы будет 5 , поскольку “2015” находится в 5-ом столбце.

Теперь вставляем эти формулы в функцию ИНДЕКС и вуаля:

INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))
=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0))

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

INDEX($A$1:$E$11,4,5))
=ИНДЕКС($A$1:$E$11;4;5))

Эта формула возвращает значение на пересечении 4-ой строки и 5-го столбца в диапазоне A1:E11 , то есть значение ячейки E4 . Просто? Да!

Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ

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

Предположим, у нас есть список заказов, и мы хотим найти сумму по двум критериям – имя покупателя (Customer) и продукт (Product). Дело усложняется тем, что один покупатель может купить сразу несколько разных продуктов, и имена покупателей в таблице на листе Lookup table расположены в произвольном порядке.

Вот такая формула ИНДЕКС /ПОИСКПОЗ решает задачу:

{=INDEX("Lookup table"!$A$2:$C$13,MATCH(1,(A2="Lookup table"!$A$2:$A$13)*
(B2="Lookup table"!$B$2:$B$13),0),3)}
{=ИНДЕКС("Lookup table"!$A$2:$C$13;ПОИСКПОЗ(1;(A2="Lookup table"!$A$2:$A$13)*
(B2="Lookup table"!$B$2:$B$13);0);3)}

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

MATCH(1,(A2="Lookup table"!$A$2:$A$13),0)*(B2="Lookup table"!$B$2:$B$13)
ПОИСКПОЗ(1;(A2="Lookup table"!$A$2:$A$13);0)*(B2="Lookup table"!$B$2:$B$13)

В формуле, показанной выше, искомое значение – это 1 , а массив поиска – это результат умножения. Хорошо, что же мы должны перемножить и почему? Давайте разберем все по порядку:

Берем первое значение в столбце A (Customer) на листе Main table и сравниваем его со всеми именами покупателей в таблице на листе Lookup table (A2:A13).
Если совпадение найдено, уравнение возвращает 1 (ИСТИНА), а если нет – 0 (ЛОЖЬ).
Далее, мы делаем то же самое для значений столбца B (Product).
Затем перемножаем полученные результаты (1 и 0). Только если совпадения найдены в обоих столбцах (т.е. оба критерия истинны), Вы получите 1 . Если оба критерия ложны, или выполняется только один из них – Вы получите 0 .

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

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

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

Если всё сделано верно, Вы получите результат как на рисунке ниже:

ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel

Как Вы, вероятно, уже заметили (и не раз), если вводить некорректное значение, например, которого нет в просматриваемом массиве, формула ИНДЕКС /ПОИСКПОЗ сообщает об ошибке #N/A (#Н/Д) или #VALUE! (#ЗНАЧ!). Если Вы хотите заменить такое сообщение на что-то более понятное, то можете вставить формулу с ИНДЕКС и ПОИСКПОЗ в функцию ЕСЛИОШИБКА .

Синтаксис функции ЕСЛИОШИБКА очень прост:

IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)

Где аргумент value (значение) – это значение, проверяемое на предмет наличия ошибки (в нашем случае – результат формулы ИНДЕКС /ПОИСКПОЗ ); а аргумент value_if_error (значение_если_ошибка) – это значение, которое нужно возвратить, если формула выдаст ошибку.

Например, Вы можете вставить в функцию ЕСЛИОШИБКА вот таким образом:

IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),
"Совпадений не найдено. Попробуйте еще раз!") =ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0));
"Совпадений не найдено. Попробуйте еще раз!")

И теперь, если кто-нибудь введет ошибочное значение, формула выдаст вот такой результат:

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

IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),"")
ЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);"")

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

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

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

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

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

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

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

Алгоритм действий:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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