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

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

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

Содержание:

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

Сама аббревиатура ВПР означает «вертикальный просмотр».

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

Также, в Excel есть противоположная функция под названием HLOOKUP или ГПР – горизонтальный просмотр.

Единственное отличие работы опций состоит в том, что ГПР производит поиск в таблице по перебору столбцов, а не строчек.

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

Как выглядит синтаксис ВПР?

Синтаксис функции в Excel – это набор параметров, с помощью которых её можно вызвать и задать. Запись аналогична с методом записи математических функций.

• Используйте уже созданный документ, либо откройте новый пустой лист;
• Кликните на клавишу «Формулы» , как показано на рисунке ниже;
• В строке поиска напечатайте «ВПР» или «VLOOKUP» в зависимости от языка программы;
• Настройте категорию «Полный перечень» ;
• Кликните на «Найти» .

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

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

Общий вид описания для ВПР выглядит так:

Рис.3 – перечень параметров

Рассмотрим детальнее каждое из значений, которое описывается в скобках:

• <ЧТО> - первый элемент. Вместо него вам нужно прописать именно то значение, которое вы хотите найти в таблице. Также, можно вписывать адрес ячейки в таблице;
• <НОМЕР_СТОЛБЦА> - тут нужно напечатать номер столбика, в рамках которого будет осуществляться перебор данных.
• <ГДЕ> - здесь юзер определяет количество ячеек, задавая их размерность в виде двумерного массива данных. Первый столбик – это элемент «ЧТО»;
• <ОТСОРТИРОВАНО> - этот элемент функции ВПР отвечает за сортировку первого столбца по возрастанию (первый столбик для «ГДЕ»). В результате успешной сортировки, значение становится истинным (единица). Если возникают какие-либо неточности или ошибки во время ввода параметров – появляется ложное значение сортировки (ноль). Стоит заметить, что во время задания ВПР <ОТСОРТИРОВАНО> можно пропустить, и тогда его значение по умолчанию принимается как истина.

Как работает ВПР. Полезный пример

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

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

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

Рис.4 – пример поиска в простой таблице

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

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

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

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

Поиск будет остановлен автоматически, как только найдется совпадение параметра «ЧТО» и имени товара.

Если в таблице нет введенного вами идентификатора для имени продукции, в результате выполнения поиска VLOOKUP будет получено значение «Н/Д», что означает отсутствие элемента для заданного номера.

Рис.5 – второй пример для ВПР

Когда использовать ВПР?

Выше описаны два варианта применения VLOOKUP.

Первая вариация VLOOKUP подойдет для следующих случаев:

• Когда необходимо разделить значения объекта табличного процессора по его диапазонам;
• Для тех таблиц, в которых параметр ГДЕ может содержать несколько идентичных значений. В таком случае, формула вернет только то, которое находится в последней строчке относительно массива;
• Когда нужно искать значения, которые больше того, которое может содержаться в первом столбике. Так вы найдете последнюю строку таблицы практическим мгновенно.

Первый вариант правописания VLOOKUP не может найти элемент, если не было найдено значения меньше искомого или равное ему. В ячейке для результата вернется только «Н/Д».

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

VLOOKUP позволит легко оперировать данными, ведь возвращает первую найденную строку.

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

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

Рис.6 – пример поиска текстового значения

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

Рис.7 – ВПР при удалении пробелов

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Большинство пользователей не знают, что аббревиатура «ВПР» расшифровывается как «Вертикальный Просмотр». На английском функция называется «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. Кликните на указанный инструмент и выберите пункт «Проверка данных».

 are some of the most useful functions in Excel.

Note: The Lookup Wizard feature is no longer available in Excel.

Here"s an example of how to use VLOOKUP.

=VLOOKUP(B2,C2:E7,3,TRUE)

In this example, B2 is the first argument -an element of data that the function needs to work. For VLOOKUP, this first argument is the value that you want to find. This argument can be a cell reference, or a fixed value such as "smith" or 21,000. The second argument is the range of cells, C2-:E7, in which to search for the value you want to find. The third argument is the column in that range of cells that contains the value that you seek.

The fourth argument is optional. Enter either TRUE or FALSE. If you enter TRUE, or leave the argument blank, the function returns an approximate match of the value you specify in the first argument. If you enter FALSE, the function will match the value provide by the first argument. In other words, leaving the fourth argument blank-or entering TRUE-gives you more flexibility.

This example shows you how the function works. When you enter a value in cell B2 (the first argument), VLOOKUP searches the cells in the range C2:E7 (2nd argument) and returns the closest approximate match from the third column in the range, column E (3rd argument).

The fourth argument is empty, so the function returns an approximate match. If it didn"t, you"d have to enter one of the values in columns C or D to get a result at all.

When you"re comfortable with VLOOKUP, the HLOOKUP function is equally easy to use. You enter the same arguments, but it searches in rows instead of columns.

Using INDEX and MATCH instead of VLOOKUP

There are certain limitations with using VLOOKUP-the VLOOKUP function can only look up a value from left to right. This means that the column containing the value you look up should always be located to the left of the column containing the return value. Now if your spreadsheet isn"t built this way, then do not use VLOOKUP. Use the combination of INDEX and MATCH functions instead.

This example shows a small list where the value we want to search on, Chicago, isn"t in the leftmost column. So, we can"t use VLOOKUP. Instead, we"ll use the MATCH function to find Chicago in the range B1:B11. It"s found in row 4. Then, INDEX uses that value as the lookup argument, and finds the population for Chicago in the 4th column (column D). The formula used is shown in cell A14.

For more examples of using INDEX and MATCH instead of VLOOKUP, see the article https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ by Bill Jelen, Microsoft MVP.

Give it a try

If you want to experiment with lookup functions before you try them out with your own data, here"s some sample data.

VLOOKUP Example at work

Copy the following data into a blank spreadsheet.

Tip: Wrap Text (Home tab, Alignment group).

	Viscosity	Temperature
	Description
VLOOKUP(1,A2:C10,2)	Using an approximate match, searches for the value 1 in column A, finds the largest value less than or equal to 1 in column A which is 0.946, and then returns the value from column B in the same row.
VLOOKUP(1,A2:C10,3,TRUE)	Using an approximate match, searches for the value 1 in column A, finds the largest value less than or equal to 1 in column A, which is 0.946, and then returns the value from column C in the same row.
VLOOKUP(0.7,A2:C10,3,FALSE)	Using an exact match, searches for the value 0.7 in column A. Because there is no exact match in column A, an error is returned.
VLOOKUP(0.1,A2:C10,2,TRUE)	Using an approximate match, searches for the value 0.1 in column A. Because 0.1 is less than the smallest value in column A, an error is returned.
VLOOKUP(2,A2:C10,2,TRUE)	Using an approximate match, searches for the value 2 in column A, finds the largest value less than or equal to 2 in column A, which is 1.29, and then returns the value from column B in the same row.

HLOOKUP Example

Tip: Before you paste the data into Excel, set the column widths for columns A through C to 250 pixels, and click Wrap Text (Home tab, Alignment group).

	Bearings
	Description
HLOOKUP("Axles", A1:C4, 2, TRUE)	Looks up "Axles" in row 1, and returns the value from row 2 that"s in the same column (column A).
HLOOKUP("Bearings", A1:C4, 3, FALSE)	Looks up "Bearings" in row 1, and returns the value from row 3 that"s in the same column (column B).
HLOOKUP("B", A1:C4, 3, TRUE)	Looks up "B" in row 1, and returns the value from row 3 that"s in the same column. Because an exact match for "B" is not found, the largest value in row 1 that is less than "B" is used: "Axles," in column A.
HLOOKUP("Bolts", A1:C4, 4)	Looks up "Bolts" in row 1, and returns the value from row 4 that"s in the same column (column C).
HLOOKUP(3, {1,2,3;"a","b","c";"d","e","f"}, 2, TRUE)	Looks up the number 3 in the three-row array constant, and returns the value from row 2 in the same (in this case, third) column. There are three rows of values in the array constant, each row separated by a semicolon (;). Because "c" is found in row 2 and in the same column as 3, "c" is returned.

INDEX and MATCH Examples

This last example employs the INDEX and MATCH functions together to return the earliest invoice number and its corresponding date for each of five cities. Because the date is returned as a number, we use the TEXT function to format it as a date. The INDEX function actually uses the result of the MATCH function as its argument. The combination of the INDEX and MATCH functions are used twice in each formula – first, to return the invoice number, and then to return the date.

Copy all the cells in this table and paste it into cell A1 on a blank worksheet in Excel.

Tip: Before you paste the data into Excel, set the column widths for columns A through D to 250 pixels, and click Wrap Text (Home tab, Alignment group).

		Invoice Date	Earliest invoice by city, with date
			="Atlanta = "&INDEX($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),3),"m/d/yy")
			="Austin = "&INDEX($A$2:$C$33,MATCH("Austin",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("Austin",$B$2:$B$33,0),3),"m/d/yy")
			="Dallas = "&INDEX($A$2:$C$33,MATCH("Dallas",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("Dallas",$B$2:$B$33,0),3),"m/d/yy")
			="New Orleans = "&INDEX($A$2:$C$33,MATCH("New Orleans",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("New Orleans",$B$2:$B$33,0),3),"m/d/yy")
			="Tampa = "&INDEX($A$2:$C$33,MATCH("Tampa",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("Tampa",$B$2:$B$33,0),3),"m/d/yy")