Платежи 

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 с сайта

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

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

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

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

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

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

Синтаксис

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

Например:

    ВПР(105;A2:C7;2;ИСТИНА)

    ВПР("Иванов";B2:E7;2;ЛОЖЬ)

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

Описание

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

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

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

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

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

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

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

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

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

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

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

    Вариант ЛОЖЬ осуществляет поиск точного значения в первом столбце.

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

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

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

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

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

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

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

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

Примеры

Вот несколько примеров функции ВПР:

Пример 1


Пример 2


Пример 3


Пример 4


Пример 5


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

Проблема

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

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

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

#Н/Д в ячейке

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

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

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

Если значение " Номер_столбца " больше, чем число столбцов в таблице , вы получите #REF! В противном случае TE102825393 выдаст ошибку «#ЗНАЧ!».

#ЗНАЧ! в ячейке

Если инфо_таблица меньше 1, вы получите #VALUE! В противном случае TE102825393 выдаст ошибку «#ЗНАЧ!».

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

#ИМЯ? в ячейке

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

Действие

Результат

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

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

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

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

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

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

Если Интервальный_просмотр имеет значение ложь, а Искомое_значение - текст, можно использовать подстановочные знаки - вопросительный знак (_км_) и звездочку (*) - в Искомое_значение . Вопросительный знак соответствует одному символу. Звездочка соответствует любой последовательности знаков. Если вы хотите найти реальный вопросительный знак или звездочку, введите знак тильда (~) перед символом.

Например, с помощью функции =VLOOKUP("Fontan?",B2:E7,2,FALSE) можно выполнить поиск всех случаев употребления фамилии Иванов в различных падежных формах.

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

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

Для получения точных результатов попробуйте воспользоваться функциями

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

Большинство пользователей не знают, что аббревиатура «ВПР» расшифровывается как «Вертикальный Просмотр». На английском функция называется «VLOOKUP», которая означает «Vertical LOOK UP»

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

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

  1. Создайте таблицу, по которой можно будет сделать какой-нибудь поиск информации.
  1. Добавим несколько полей, которые будем использовать для демонстрации формул.
  1. В поле «Искомая фамилия» введем какую-нибудь на выбор из тех, что есть в таблице.
  2. Затем переходим на следующую ячейку и вызываем окно «Вставка функции».
  3. Выбираем категорию «Полный алфавитный перечень».
  4. Находим нужную нам функцию «ВПР». Для продолжения нажимаем на кнопку «OK».
  1. Затем нас попросят указать «Аргументы функции»:
    • В поле «Искомое выражение» указываем ссылку на ячейку, в которой мы написали нужную нам фамилию.
    • Для того чтобы заполнить поле «Таблица», достаточно просто выделить все наши данные при помощи мышки. Ссылка подставится автоматически.
    • В графе «Номер столбца» указываем номер 2, поскольку в нашем случае имя находится во второй колонке.
    • Последнее поле может принимать значения «0» или «1» («ЛОЖЬ» и «ИСТИНА»). Если укажете «0», то редактор будет искать точное совпадение по заданным критериям. Если же «1» – то во время поиска не будут учитываться полные совпадения.
  2. Для сохранения кликните на кнопку «OK».
  1. В результате этого мы получили имя «Томара». То есть, всё правильно.

Теперь нужно воспользоваться этой же формулой и для остальных полей. Простое копирование ячейки при помощи Ctrl +C и Ctrl +V не подойдёт, поскольку у нас используются относительные ссылки и каждый раз будет меняться номер столбца.

Для того чтобы всё сработало правильно, нужно сделать следующее:

  1. Кликните на ячейку с первой функцией.
  2. Перейдите в строку ввода формул.
  3. Скопируйте текст при помощи Ctrl +C .
  1. Сделайте активной следующее поле.
  2. Снова перейдите в строку ввода формул.
  3. Нажмите на горячие клавиши Ctrl +V .

Только таким способом редактор не изменит ссылки в аргументах функции.

  1. Затем меняем номер столбца на нужный. В нашем случае это 3. Нажимаем на клавишу Enter .
  1. Благодаря этому мы видим, что данные из столбца «Год рождения» определились правильно.
  1. После этого повторяем те же самые действия для последнего поля, но с корректировкой номера нужного столбца.

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

То есть нумерация начинается не с начала листа, а с начала указанной области ячеек.

Как использовать функцию «ВПР» для сравнения данных

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

  1. Добавим второй лист с точно такой же таблицей (копировали при помощи горячих клавиш Ctrl +C и Ctrl +V ).
  2. Повысим стажеров до «Младший сотрудник». Эта информация будет отправной точкой для сравнения.
  1. Добавим ещё один столбец в нашу старую таблицу.
  1. Переходим в первую клетку нового столбца и вводим там следующую формулу.
=ВПР($B$3:$B$11;Лист2!$B$3:$E$11;4;ЛОЖЬ)

Она означает:

  • $B$3:$B$11 – для поиска используются все значения первой колонки (применяются абсолютные ссылки);
  • Лист2! – эти значения нужно искать на листе с указанным названием;
  • $B$3:$E$11 – таблица, в которой нужно искать (диапазон ячеек);
  • 4 – номер столбца в указанной области данных;
  • ЛОЖЬ – искать точные совпадения.
  1. Новая информация выведется в том месте, где мы указали формулу.
  2. Результат будет следующим.
  1. Теперь продублируйте эту формулу в остальные ячейки. Для этого нужно потянуть мышкой за правый нижний угол исходной клетки.
  1. В итоге мы увидим, что написанная нами формула работает корректно, поскольку все новые должности скопировались как положено.

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

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

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

Функция «ВПР» и выпадающие списки

Рассмотрим примеры использования этих двух инструментов одновременно. Для этого нужно выполнить следующие действия.

  1. Перейдите в ячейку, в которой происходит выбор фамилии.
  2. Откройте вкладку «Данные».
  3. Кликните на указанный инструмент и выберите пункт «Проверка данных».

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

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

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

В данном отчете необходимо найти показатель выручки для определенного торгового представителя в определенную дату. Учитывая условия поиска наш запрос должен содержать 2 условия:

  1. – Дата сдачи выручки в кассу.
  2. – Фамилия торгового представителя.

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

  1. В ячейке С1 введите первое значение для первого критерия поискового запроса. Например, дата: 22.03.2017.
  2. В ячейку C2 введите фамилию торгового представителя (например, Новиков). Это значение будет использоваться в качестве второго аргумента поискового запроса.
  3. В ячейке C3 мы будем получать результат поиска, для этого там следует ввести формулу:
  4. После ввода формулы для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве.

Результат поиска в таблице по двум условиям:


Найдена сумма выручки конкретного торгового представителя на конкретную дату.



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

Первым аргументом функции =ВПР() является первым условием для поиска значения по таблице отчета выручки торговых представителей. Во втором аргументе находится виртуальная таблица создана в результате массивного вычисления логической функцией =ЕСЛИ(). Каждая фамилия в диапазоне ячеек B6:B12 сравнивается со значением в ячейке C2. Таким образом в памяти создается условный массив данных с элементами значений ИСТИНА и ЛОЖЬ.

Потом благодаря формуле, в памяти программы каждый истинный элемент заменяется на 3-х элементный набор данных:

  1. элемент – Дата.
  2. элемент – Фамилия.
  3. элемент – Выручка.

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

А из какого столбца брать возвращаемое значение указывается уже в третьем аргументе.

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

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

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

Скачать надстройку

Сохраните файл на компьютере и установить его в Excel, выбрав Файл -> Параметры -> Надстройки . Нажмите на кнопку Перейти... , в открывшемся окне выберите Обзор . Выберите сохраненный файл и нажмите кнопку OK, чтобы завершить установку. После установки появится сообщение "Reverse_Lookup" в списке доступных надстроек и функция rLOOKUP становится доступной во всех книгах.

Описание функции

Аргументы для функции rLOOKUP такие же, как и для функции ВПР, см. скриншоты ниже. Чтобы выполнить обратный поиск, просто введите отрицательное число в аргумент "Col_Index_Num" (смотри примеры ниже). Формула будет Lookup в направлении влево.

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

Пример функции

Следующие данные Excel показывает адреса офисов, имена сотрудников и должность.

Для ответа на этот запрос "Кто работает офисе B43?"мы можем использовать функцию ВПР:

ВПР ("B43"; A1:C6;2; ЛОЖЬ) возвращает "Dilbert".

Тем не менее, если мы хотим знать, "Где находится офис Дилберта?", то ВПР не будет работать. Вот тут пригодится функция rLOOKUP:

rLOOKUP ("Dilbert"; A1:B6, -2).

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

Вообще, функция работает в обоих направлениях:

rLOOKUP ("B43";A1:C6;2) возвращает "Dilbert" и

rLOOKUP ("Dilbert"; A1:B6; -2) возвращает "B43".

Другие примеры приведены ниже:

Не забудьте сначала установить надстройку, в противном случае будет возвращаться ошибка #NAME? .

Как работает функция

Функция использует функции ПОИСКПОЗ (MATCH) и ИНДЕКС (INDEX) для запуска обратного поиска.

Исходный код для функции смотрите ниже.

Function rLOOKUP(Lookup_value, Table_array As Range, Col_index_num As Integer, Optional Range_lookup As Boolean)
"
"Custom function that allows forward and reverse vertical lookups
"Works like VLookup for positive Col_index_num, and does reverse lookups for negative Col_index_num
"Unlike VLOOKUP, it looks for the exact value by default
"
"Nitin Mehta, www.Engineers-Excel.com
"
"
Dim Source_Col As Range, Dest_Col_num As Integer

"The Source Column is the Column where the Lookup_value is located. It is the leftmost column if
"Col_index_number is positive. If Col_index_number is negative, it is the rightmost column
Set Source_Col = Table_array.Columns(IIf(Col_index_num > 0, 1, Table_array.Columns.Count))

"Dest_col_num is the column number within the Table_array range from where we have to pick up the entry against the Lookup_value
Dest_Col_num = IIf(Col_index_num > 0, Col_index_num, Table_array.Columns.Count + Col_index_num + 1)

"Use MATCH and INDEX functions for the Lookup.
"Use of CVErr(xlErrRef) suggested by Brian Canes
rLOOKUP = IIf(Dest_Col_num < 1, CVErr(xlErrRef), Application.Index(Table_array, Application.Match(Lookup_value, Source_Col, Range_lookup), Dest_Col_num))

End Function

Оставьте свой комментарий!



Есть вопросы?

Сообщить об опечатке

Текст, который будет отправлен нашим редакторам:

Отправить