Формула впр в excel для сравнения двух таблиц — 4 способа

Особенности использования формулы ВПР в Excel

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

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

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

А вот поле «Таблица» должно иметь абсолютную ссылку (адрес диапазона прописывается через $). Если этого не сделать, то при копировании формулы диапазон «поедет» вниз и многие значения просто не найдутся, так как искать будет негде.

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

3. Функция ВПР из диапазона с искомыми данными выдает первое сверху значение. Это значит, что, если во второй таблице, откуда мы пытаемся «подтянуть» некоторые данные, присутствуют несколько ячеек с одинаковым критерием, то в рамках выделенного диапазона ВПР захватит первое сверху значение. Об этом следует помнить. К примеру, если мы хотим к цене товара подтянуть количество из другой таблицы, а там этот товар встречается несколько раз (в нескольких строках), то к цене подтянется первое сверху количество. 

4. Последний параметр формулы, который 0 (нуль), ставить нужно обязательно. Иначе формула может криво работать.

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

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

Это позволит удалить сразу все внешние ссылки. 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=ВПР(«Продукт 1»;Таблица6:];2)

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

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

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

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

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

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

Примеры для наглядности: в таблицах 1,2 – исходные данные, таблице 3 – что должно получиться.

Исходные данные таблица 1

Ф. И. О.	Штраф
Петров	12 000,00 ₽
Сидоров	200,00 ₽
Иванов	38 000,00 ₽

Исходные данные таблица 2

Ф. И. О.	З.П.
Иванов	20 000 ₽
Петров	19 000 ₽
Сидоров	21 000 ₽

Объединенные данные таблица 3

Ф. И. О.	З.П.	Штраф
Иванов	20 000 ₽	38 000 ₽
Петров	19 000 ₽	12 000 ₽
Сидоров	21 000 ₽	200 ₽

Если вдруг Вы еще не знали, то советуем прочитать о том, как объединить ячейки в Exсel.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция ВПР в Экселе: пошаговая инструкция

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

Во второй – цены:

Если перечень товаров в обеих таблицах совпадает, то, зная магическое сочетание Ctrl+C и Ctrl+V, данные о ценах можно легко подставить к данным о количестве. Однако очередность позиций в обеих таблицах не совпадает. Тупо скопировать цены и подставить к количеству не получится.

Поэтому мы не можем прописать формулу умножения и «протянуть» вниз на все позиции.

Что делать? Надо как-то цены из второй таблицы подставить к соответствующему количеству в первой, т.е. цену товара А к количеству товара А, цену Б к количеству Б и т.д.

Вот так.

Функция ВПР в Эксель легко справится с задачей.

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

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

Кликаем по надписи «ВПР». Открывается следующее диалоговое окно.

Теперь нужно заполнить предлагаемые поля. В первом окошке «Искомое_значение» нужно указать критерий для ячейки, в которую мы вписываем формулу. В нашем случае это ячейка с наименованием товара «А».

Следующее поле «Таблица». В нем нужно указать диапазон данных, где будет осуществляться поиск нужных значений. В нашем случае это вторая таблица с ценой. При этом крайний левый столбец выделяемого диапазона должен содержать те самые критерии, по которым осуществляется поиск (столбец с наименованиями товаров). Затем таблица выделяется вправо минимум до того столбца, где находятся искомые значения (цены). Можно и дальше вправо выделить, но это уже ни на что не влияет. Главное, чтобы выделенная таблица начиналась со столбца с критериями и захватывала нужный столбец с данными

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

Следующее поле «Номер_столбца» — это число, на которое столбец с искомыми данными (ценами) отстоит от столбца с критерием (наименованием товара) включительно. То есть отсчет идет, начиная с самого столбца с критерием. Если у нас во второй таблице оба столбца находятся рядом, то нужно указать число 2 (первый – критерий, второй — цены). Часто бывает, что данные отстоят от критерия на 10 или 20 столбцов

Это не важно, Excel все сосчитает

Последнее поле «Интервальный_просмотр», где указывается тип поиска: точное (0) или приблизительное (1) совпадение критерия. Пока ставим 0 (или ЛОЖЬ). Второй вариант рассмотрен ниже.

Нажимаем ОК. Если все правильно и значение критерия есть в обеих таблицах, то на месте только что введенной формулы появится некоторое значение. Остается только протянуть (или просто скопировать) формулу вниз до последней строки таблицы.

Теперь легко рассчитать стоимость простым умножением количества на цену.

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

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

Предположим, вы заведующий складом, в котором хранятся разные материалы, такие как упаковки и контейнеры для хранения разных предметов. И вам завезли продукцию в конкретном количестве, и оно задается в Excel.

1

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

2

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

Если говорить более точно, последовательность действий следующая:

1. Приводим внешний вид таблицы в нужный нам вид путем вставки двух колонок, которые называются «Цена» и «Стоимость/Сумма». При этом нужно применить к ячейкам денежный формат. 

2. Нажимаем на ячейку, являющуюся первой в нашей колонке «Цена». В случае с нами она имеет адрес D2. С использованием мастера функций пользователь всегда может найти ВПР в категории «Ссылки и массивы», независимо от версии Excel. А для вызова мастера функций есть два метода. Первый – это нажать на кнопку fx рядом со строкой ввода формулы. Второй же – комбинация клавиш SHIFT + F3. После того, как нужная нам функция будет выбрана, надо нажать на клавишу ОК, чтобы подтвердить свои действия. Есть еще один способ вызова этой функции. Нужно перейти на вкладку «Формулы» и там найти тот же пункт «Ссылки и массивы».

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

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

   • Таблица. Это набор ячеек, в которых будет осуществляться поиск. В данном примере это вторая таблица с прайс-листом. Осуществляем переход на нее и выбираем необходимые значения. 

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

   • Номер столбца. В этом аргументе мы пишем цифру два.

   • Интервальный просмотр. Этот параметр нужен, если ищутся только приблизительные данные. Этот аргумент может принимать два значения «Истина» и «Ложь». Мы запишем второй вариант, поскольку нам требуется точная информация.

4. После этого нажимаем кнопку «ОК».
5. Далее функция размножается на всю колонку, воспользовавшись маркером автозаполнения, потянув за правый нижний угол ячейки по направлению вниз. 

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

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

Как это сделать? Можно применить «Специальную вставку». Последовательность действий следующая:

1. Выделяем нужную колонку и делаем правый клик мыши.
2. Копируем колонку.
3. Оставляем выделение, опять делаем правый клик мыши и нажимаем «Специальная вставка», после чего появится меню, в котором нужно установить радиокнопку возле пункта «Значения».

В самом конце нужно подтвердить свои действия с помощью кнопки «ОК».

7

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

Усложняем задачу

Давайте немного усложним задачу. Установим ещё одно пороговое значение: если продавец зарабатывает более $40000, тогда ставка комиссионных возрастает до 40%:

Вроде бы всё просто и понятно, но наша формула в ячейке B2 становится заметно сложнее. Если Вы внимательно посмотрите на формулу, то увидите, что третий аргумент функции IF (ЕСЛИ), превратился в ещё одну полноценную функцию IF (ЕСЛИ). Такая конструкция называется вложением функций друг в друга. Excel с радостью допускает такие конструкции, и они даже работают, но их гораздо сложнее читать и понимать.

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

Как бы там ни было, формула усложняется! А что, если мы введем еще один вариант ставки комиссионных, равный 50%, для тех продавцов, кто сделал объём продаж более $50000. А если кто-то продал на сумму более $60000 – тому заплатить 60% комиссионных?

Теперь формула в ячейке B2, даже если она записана без ошибок, стала совершенно не читаемой. Думаю, что найдется мало желающих использовать формулы с 4-мя уровнями вложенности в своих проектах. Должен же существовать более простой способ?!

И такой способ есть! Нам поможет функция ВПР.

Функция ВПР в Excel – общее описание и синтаксис

Итак, что же такое ВПР? Ну, во-первых, это функция Excel. Что она делает? Она ищет заданное Вами значение и возвращает соответствующее значение из другого столбца. Говоря техническим языком, ВПР ищет значение в первом столбце заданного диапазона и возвращает результат из другого столбца в той же строке.

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

Первая буква в названии функции ВПР (VLOOKUP) означает Вертикальный (Vertical). По ней Вы можете отличить ВПР от ГПР (HLOOKUP), которая осуществляет поиск значения в верхней строке диапазона – Горизонтальный (Horizontal).

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

Синтаксис функции ВПР

Функция ВПР (VLOOKUP) имеет вот такой синтаксис:

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

lookup_value (искомое_значение) – значение, которое нужно искать.Это может быть значение (число, дата, текст) или ссылка на ячейку (содержащую искомое значение), или значение, возвращаемое какой-либо другой функцией Excel. Например, вот такая формула будет искать значение 40:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)

Если искомое значение будет меньше, чем наименьшее значение в первом столбце просматриваемого диапазона, функция ВПР сообщит об ошибке #N/A (#Н/Д).

table_array (таблица) – два или более столбца с данными.Запомните, функция ВПР всегда ищет значение в первом столбце диапазона, заданного в аргументе table_array (таблица). В просматриваемом диапазоне могут быть различные данные, например, текст, даты, числа, логические значения. Регистр символов не учитывается функцией, то есть символы верхнего и нижнего регистра считаются одинаковыми.Итак, наша формула будет искать значение 40 в ячейках от A2 до A15, потому что A – это первый столбец диапазона A2:B15, заданного в аргументе table_array (таблица):
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)

col_index_num (номер_столбца) – номер столбца в заданном диапазоне, из которого будет возвращено значение, находящееся в найденной строке.Крайний левый столбец в заданном диапазоне – это 1, второй столбец – это 2, третий столбец – это 3 и так далее. Теперь Вы можете прочитать всю формулу:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)

Формула ищет значение 40 в диапазоне A2:A15 и возвращает соответствующее значение из столбца B (поскольку B – это второй столбец в диапазоне A2:B15).

Если значение аргумента col_index_num (номер_столбца) меньше 1, то ВПР сообщит об ошибке #VALUE! (#ЗНАЧ!). А если оно больше количества столбцов в диапазоне table_array (таблица), функция вернет ошибку #REF! (#ССЫЛКА!).

• range_lookup (интервальный_просмотр) – определяет, что нужно искать:
  • точное совпадение, аргумент должен быть равен FALSE (ЛОЖЬ);
  • приблизительное совпадение, аргумент равен TRUE (ИСТИНА) или вовсе не указан.

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

Примеры

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

Пример 5

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

) перед искомым символом.

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

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

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

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

Что такое ВПР и как ею пользоваться?

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

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

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

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

Теперь активируйте первую ячейку в блоке «Цена» и вызовите «Мастер функций». Сделать это можно, нажав на кнопку «fx», расположенную перед строкой формул, или зажав комбинацию клавиш «Shift+F3». В открывшемся диалоговом окне отыщите категорию «Ссылки и массивы». Здесь нас не интересует ничего кроме функции ВПР. Выберите её и нажмите «ОК». Кстати, следует сказать, что функция VLOOKUP может быть вызвана через вкладку «Формулы», в выпадающем списке которой также находится категория «Ссылки и массивы».

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

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

Укажите Excel, какие именно значения необходимо сопоставить функции VLOOKUP.
Для того чтобы Excel не путался и ссылался на нужные вам данные, важно зафиксировать заданную ему ссылку. Чтобы сделать это, выделите в поле «Таблица» требуемые значения и нажмите клавишу F4

Если всё выполнено верно, на экране должен появиться знак $.

Теперь мы переходим к полю аргумента «Номер страницы» и задаём ему значения «2». В этом блоке находятся все данные, которые требуется отправить в нашу рабочую таблицу, а потому важно присвоить «Интервальному просмотру» ложное значение (устанавливаем позицию «ЛОЖЬ»). Это необходимо для того, чтобы функция ВПР работала только с точными значениями и не округляла их.

Теперь, когда все необходимые действия выполнены, нам остаётся лишь подтвердить их нажатием кнопки «ОК». Как только в первой ячейке изменятся данные, нам нужно будет применить функцию ВПР ко всему Excel документу. Для этого достаточно размножить VLOOKUP по всему столбцу «Цена». Сделать это можно при помощи перетягивания правого нижнего уголка ячейки с изменённым значением до самого низа столбца. Если все получилось, и данные изменились так, как нам было необходимо, мы можем приступить к расчёту общей стоимости наших товаров. Для выполнения этого действия нам необходимо найти произведение двух столбцов — «Количества» и «Цены». Поскольку в Excel заложены все математические формулы, расчёт можно предоставить «Строке формул», воспользовавшись уже знакомым нам значком «fx».

Точное или приближенное совпадение в функции ВПР

И, наконец, давайте рассмотрим поподробнее последний аргумент, который указывается для функции ВПР – range_lookup (интервальный_просмотр). Как уже упоминалось в начале урока, этот аргумент очень важен. Вы можете получить абсолютно разные результаты в одной и той же формуле при его значении TRUE (ПРАВДА) или FALSE (ЛОЖЬ).

Для начала давайте выясним, что в Microsoft Excel понимается под точным и приближенным совпадением.

• Если аргумент range_lookup (интервальный_просмотр) равен FALSE (ЛОЖЬ), формула ищет точное совпадение, т.е. точно такое же значение, что задано в аргументе lookup_value (искомое_значение). Если в первом столбце диапазона table_array (таблица) встречается два или более значений, совпадающих с аргументом lookup_value (искомое_значение), то выбрано будет первое из них. Если совпадения не найдены, функция сообщит об ошибке #N/A (#Н/Д).Например, следующая формула сообщит об ошибке #N/A (#Н/Д), если в диапазоне A2:A15 нет значения 4:

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

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

Чтобы лучше понять важность выбора TRUE (ИСТИНА) или FALSE (ЛОЖЬ), давайте разберём ещё несколько формул с функцией ВПР и посмотрим на результаты

Пример 1: Поиск точного совпадения при помощи ВПР

Как Вы помните, для поиска точного совпадения, четвёртый аргумент функции ВПР должен иметь значение FALSE (ЛОЖЬ).

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

Обратите внимание, что наш диапазон поиска (столбец A) содержит два значения 50 – в ячейках A5 и A6. Формула возвращает значение из ячейки B5

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

Пример 2: Используем ВПР для поиска приблизительного совпадения

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

Это очень важно, поскольку функция ВПР возвращает следующее наибольшее значение после заданного, а затем поиск останавливается. Если Вы пренебрежете правильной сортировкой, дело закончится тем, что Вы получите очень странные результаты или сообщение об ошибке #N/A (#Н/Д)

Вот теперь можно использовать одну из следующих формул:

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

Как видите, формула возвратила результат Антилопа (Antelope), скорость которой 61 миля в час, хотя в списке есть также Гепард (Cheetah), который бежит со скоростью 70 миль в час, а 70 ближе к 69, чем 61, не так ли? Почему так происходит? Потому что функция ВПР при поиске приблизительного совпадения возвращает наибольшее значение, не превышающее искомое.

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

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

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

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

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

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

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

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

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