Функция ВПР в Excel

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

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

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

Функция ВПР в Excel

Для решения задачи нам необходимо подтянуть цены на товары из таблицы «Прайс-лист» в столбец «Цена за кг» таблицы «Заказы». Т.е. подставить цены, которые соответствуют каждому товару из нижней таблицы в верхнюю.

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

Функция ВПР в Excel

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

Функция ВПР в Excel

У функции ВПР 4 аргумента. Первым аргументом, который мы подставим в формулу, является то самое искомое значение, цену для которого нужно найти во второй таблице «Прайс-лист». После написания названия функции и добавления открывающей скобки, щелкаем по искомой ячейке и добавляем ее аргументом. В моем случае это ячейка «E4» со значением «Яблоки». Далее ставим разделитель — точка с запятой.

Функция ВПР в Excel

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

Функция ВПР в Excel

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

Функция ВПР в Excel

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

Функция ВПР в Excel

Последний параметр называется «Интервальный просмотр» — это логический аргумент и может принимать только 2 значения: 0 или 1 (включено/выключено). Данным значением мы определяем, точно, цифра 0, или приблизительно, цифра 1, мы ищем наименование в прайс-листе. В случае с текстовыми наименованиями лучше использовать точный поиск (цифра 0), потому что приблизительный более менее точно работает в случае ячеек с числами. Вводим интервальный просмотр как 0 и закрываем скобку. После этого нажимаем клавишу Enter, чтобы формула сработала. В итоге мы получим ячейку с ценой, подтянутой из таблицы прайс-листа.

Функция ВПР в Excel

Функция ВПР в Excel

В колонку «Итоговая стоимость» прописываем простую формулу умножения веса партии на цену за кг.

Функция ВПР в Excel

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

Функция ВПР в Excel

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

Функция ВПР в Excel

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

Функция ВПР в Excel

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

Функция ВПР в Excel

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

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

Функция ВПР в Excel

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

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

Функция ВПР в Excel

Функция ВПР в Excel

Есть некоторые особенности работы, которые необходимо пояснить, чтобы было понятно, что здесь произошло. Мы видим, что при определенном весе функция вытащила из второй таблицы определенную партию. Чтобы более точно понять логику, давайте посмотрим на вес, к примеру, в 15 кг. В таблице с размерами партий, такого критерия нет. Но когда мы ставим интервальный просмотр в значение 1, функция будет вытаскивать значение, которое является ближайшим меньшим. При весе партии в 15 кг, ВПР вытащит из таблицы значение с критерием 10. Если вес партии будет 47 кг, то функция вытащит значение с критерием 30, которое будет ближайшим и меньшим.

Функция ВПР в Excel

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

Функция ВПР в Excel

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

Не забудьте поделиться ссылкой на статью ⇒

Переключение листов Excel

Сегодня расскажу про быстрое переключение между листами Excel. Это может вам понадобиться, когда в открытом документе слишком много листов и добраться к нужному не так-то просто. В Экселе на этот случай есть несколько решений, поэтому вы сможете выбрать для себя наиболее подходящий.

  • 28.12.2015
  • Просмотров: 17168
  • Excel

Плюс или ноль перед числом в Excel

В этом уроке расскажу как поставить в начале ячейки знак плюс или ноль перед числом в Excel. Давайте представим ситацию, что вам необходимо ввести в ячейку номер телефона в формате «+7 987. «. В обычном состоянии программа Эксель просто удалит этот знак плюс.

  • 23.12.2015
  • Просмотров: 36982
  • Excel
  • Видеоурок

Как закрепить строку или столбец в Excel

В этом уроке расскажу как закрепить строку или столбец в Excel. Закрепленные области будут всегда видны на экране при вертикальной или горизонтальной прокрутке.

  • 25.11.2015
  • Просмотров: 7868
  • Excel
  • Видеоурок

Текст по столбцам в Excel

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

  • 15.12.2015
  • Просмотров: 10218
  • Excel
  • Видеоурок

Как объединить текст в ячейках в Excel (Функция Сцепить)

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

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

Наша таблица — ниже, из нее нужно отобрать ячейку по двум условиям, “а” и “б”.

Для этого в функции ВПР нужно объединить оба условия поиска и диапазоны поиска. Объединим и выведем для наглядности.

Добавляем все в ВПР. Формула массива (arrayformula) нужна, чтобы “на лету” объединить столбцы.

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

Конструкцией ниже мы создали массив из двух столбцов (объединенного A2:A3 и B2:B3 и C2:C3), обратная косая черта (“\”) — отделяет столбцы, фигурные скобки <> — создают массив.

Итак, формула:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    ВПР не работает

    Как работает ВПР в Excel читайте в посте “Функция ВПР – чудо экономии времени”. Здесь же вы узнаете, что делать, если ВПР не работает. Так бывает. Скорее всего вы допустили ошибку. Есть четыре наиболее распространенных варианта ошибок:

    1. Ошибка Н/Д в каждой ячейке.
    2. Ошибка Н/Д преимущественно в нижней части таблицы.
    3. Ошибка Н/Д в отдельных ячейках.
    4. Ошибка #ССЫЛКА.

    впр excel ошибки

    А теперь разберем каждый вариант подробнее.

    Вариант 1. ВПР вернула ошибку #Н/Д во всех ячейках

    • Вы неправильно выделили диапазон поиска – начали не с того столбца:

    впр excel ошибка #Н/Д

    Все будет в порядке, как только вы исправите ошибку:

    =впр(B3;'[прайс-лист.xlsx]Лист1′! $C$3:$E$7 ;3;ложь)

    • Не совпадает текст, по которому ВПР ищет данные.

    Например, в одном файле текст “Компьютер AF”, в другом файле “КомпьютерAF”. Если не заметили, разница всего лишь в наличии дефиса во втором случае. Тем не менее, ВПР не считает такое расхождение мелочью и, следовательно, не сработает.

    Единственное исключение – допускаются разные регистры. Например, “Компьютер AF” и “компьютер af” не составит проблемы для ВПР.

    Идентичность текста удобно проверять функцией =ЯЧЕЙКА=ЯЧЕЙКА. Смотрите пример на рисунке ниже и описание под ним:

    excel проверка ячеек

    1. Введите в ячейку D2 знак “=”
    2. Выделите ячейку B2 с первым значением Планшет DC.
    3. Опять введите “=”.
    4. Выделите ячейку C2 со вторым значением.
    5. Кликните Enter и протяните формулу вниз.

    В результате в ячейках отобразится либо “ИСТИНА” (значения совпадают), либо “ЛОЖЬ” (значения не совпадают).

    Как видите, не совпадают значения в строке 4: “Компьютер А” и “Компьютер F” (в ячейке D4 видим слово “ЛОЖЬ”).

    Использовать функцию можно как в одном, так и в разных файлах.

    Что можно предпринять, если текст не совпадает?

    1. Если вы регулярно работаете с этими файлами, поменяйте текст вручную. Единожды потратив на это время, вы значительно сэкономите его в будущем. Если смена текста невозможна, создайте дополнительный столбец, куда введете текст из второго файла.
    2. Иногда текст не совпадает из-за каких-то наборов знаков, которые повторяются в каждой ячейке. К примеру, в одном из файлов везде добавлен артикул: “010-01583 Компьютер AF”, а в другом файле этого артикула нет “Компьютер AF”. В этом случае текст можно заменить автоматически. Об этом читайте в следующих постах.
    • В одном из файлов в тексте содержатся невидимые пробелы. Это хитрая разновидность несовпадения текста. Хитрая, потому что не всегда ее можно заметить. Вот полюбуйтесь:

    excel как проверить текст в ячейке

    Как быть? Воспользуйтесь формулой =СЖПРОБЕЛЫ(ЯЧЕЙКА). Эта формула убирает все лишние пробелы. Подробнее – в посте о работе с текстом.

    • В одном файле числа сохранены в виде текста, в другом – в виде чисел:

    excel число в виде текста

    Для устранения проблемы сделайте следующее:

    1. Выделите весь столбец с числами в виде текста (на рисунке выше диапазон B2:В5).
    2. Подведите курсор к значку “!” и кликните по появившейся стрелочке.
    3. Выберите из выпадающего списка “Преобразовать в число” – см. рисунок ниже.

    excel преобразовать текст в число

    Вариант 2. ВПР подтянула значения только в верхней части таблицы, в нижней — #Н/Д

    • Смещение диапазона. Чаще всего это происходит, если данные подтягиваются из одного и того же файла. Например, из Листа 1 на Лист 2 или даже из одного и того же листа. Что происходит? Мы тянем формулу вниз, а вместе с ней тянется и диапазон. Вот так:

    впр выдает ошибку

    Как исправить? Надо закрепить диапазон:

    1. Кликните дважды по ячейке с формулой (С5) – отобразится формула.
    2. В отобразившейся формуле поместите курсор на В12 и нажмите клавишу F4 на клавиатуре.
    3. Переместите курсор на С14 в этой же формуле и опять нажмите F4.
    4. Кликните Enter и формула преобразится, из а) в б)

    а) =ВПР(В5; В12:С14 ;2;ложь)

    б) =ВПР(В5; $B$12:$С$14 ;2;ложь)

    Значок “$” указывает на то, что диапазон закреплен. Теперь при протягивании формулы вниз, он не будет смещаться.

    Вариант 3. ВПР подтягивает только часть значений, остальные – #Н/Д

    • В некоторых ячейках текст не совпадает на 100%.

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

    Вариант 4. ВПР возвращает ошибку #ССЫЛКА

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

    впр excel номер столбца

    И еще несколько советов по функции ВПР в Excel:

    1. При вводе формулы используете точку с запятой, а не запятые.
    2. Если протягиваете формулу вправо, не забывайте закреплять ссылку в начале формулы, чтобы формула имела вид: =ВПР( $В5 ;$B$12:$С$14;2;ложь). Для этого наведите курсор на В5 (прямо в формуле) и трижды нажмите клавишу F4 на клавиатуре.
    3. Не используйте аргумент “ИСТИНА” в ВПР, если работаете с текстом. Это приблизительный поиск, в котором будет много ошибок.

    9 комментариев к “ ВПР не работает ”

    Очень хорошая статья! Без воды и написано именно то, что надо по сути
    Но, чтобы статья стала почти исчерпывающей я бы добавил, что иногда ВПР может возвращать ошибку, которая может быть не замечена пользователем, так как не появляется #Н/Д или #ССЫЛКА, однако, при этом функция ВПР() работает некорректно, так как результат ошибочный.
    Это происходит в тех случаях, когда в формуле последний аргумент не ЛОЖЬ, а ИСТИНА (или вообще не приведен, а это приравнивается к тому, как если бы была указана ИСТИНА) и при этом первый столбец не отсортирован. Формула вида =ВПР(В5;$B$12:$С$14;2) корректно работать то будет.

    Спасибо за комментарий! Да, конечно, это все совершенно верно для ВПР с аргументом ИСТИНА (или без него). Но поскольку статья является фактически продолжением статьи, в которой рассматривалась исключительно ВПР с аргументом ЛОЖЬ, то данная ошибка не приведена.

    Примеры использования ВПР в Excel

    Пример 1. Ищем результат экзамена для студента

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

    Перед нами стоит задача узнать, какой балл получил студент Петров по Физике.

    Узнать это мы можем с помощью формулы ниже:

    =VLOOKUP(“Петров”,$A$3:$E$10,3,0) – английская версия

    =ВПР(“Петров”;$A$3:$E$10;3;0) – русская версия

    Формула выше состоит из четырех аргументов:

    • “Петров” – фамилия, данные по которой, ищем;
    • $A$3:$E$10 – диапазон данных с результатами экзаменов;
    • “3” – порядковый номер столбца диапазона данных со значением оценки по Физике;
    • “0” – точное совпадение искомого значения.

    Ниже, наглядно изображено как работает по примеру наша функция.

    Сначала, она ищет в крайней левой колонке диапазона данных фамилию “Петров”. Поиск происходит сверху вниз.

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

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

    Например, чтобы найти результаты экзамена для студента Пескова по Химии, нам нужна формула:

    =VLOOKUP(“Песков”,$A$20:$E$28,4,0) – английская версия

    =ВПР(“Песков”;$A$20:$E$28;4;0) – русская версия

    В приведенном выше примере фамилия студента указана в двойных кавычках. Также, можно использовать ссылку на ячейку с искомым значением. Ссылка на ячейку делает формулу динамической.

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

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

    Пример 2. Двухфакторный поиск данных

    На примере выше мы указывали вручную в качестве атрибута “номер_столбца” порядковый номер колонки с данными.

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

    Ниже пример двух факторного поиска:

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

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

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

    =VLOOKUP($G$5,$A$4:$E$12,MATCH($H$4,$A$3:$E$3,0),0) – английская версия

    =ВПР($G$5;$A$4:$E$12;ПОИСКПОЗ($H$4;$A$3:$E$3;0);0) – русская версия

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

    Если указать “Математика”, Excel вернет “2”, поскольку “Математика” находится в ячейке “B2” (вторая по счету в этом массиве).

    Пример 3. Используем выпадающий список при двух факторном поиске

    Используя способ из примера №2, нам требуется делать много ручной работы. Высок риск допустить ошибку и потратить много времени, особенно, если вы работаете с большим объемом данных.

    Здесь лучше использовать выпадающие списки как значения для поиска (в нашем примере это фамилии студентов и учебные предметы).

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

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

    Ниже мы рассмотрим, как сделать такую таблицу.

    Для этого нам потребуется формула из Примера №2:

    =VLOOKUP($G$5,$A$4:$E$12,MATCH($H$4,$A$3:$E$3,0),0) – английская версия

    =ВПР($G$5;$A$4:$E$12;ПОИСКПОЗ($H$4;$A$3:$E$3;0);0) – русская версия

    Данные поиска были преобразованы в выпадающие списки.

    Чтобы сделать выпадающий список, нужно:

    • Выбрать ячейку, в которой вы хотите создать выпадающий список. В нашем примере в ячейке “G4” мы указали фамилии студентов.
    • Перейти к вкладке Данные -> Проверка данных.
    • В диалоговом окне «Проверка данных» на вкладке «Настройки» выбрать «Список» и в раскрывающемся списке выберите «Разрешить».
    • В источнике выбрать $A$3:$A$10
    • Нажать «ОК».

    Теперь у вас появится раскрывающийся список в ячейке “G4”. Аналогично, вы можете создать его в “H3” для предметов.

    Пример 4. Трех факторный поиск данных ВПР

    Что такое трех факторный поиск?

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

    Теперь предположим, что к концу года студент прошел три уровня экзаменов: «Вступительный», «Полугодовой» и «Финальный экзамен».

    Трехсторонний поиск – это возможность получить отметки студента по предмету с определенным уровнем экзамена.

    Вот пример трехстороннего поиска:

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

    Для таких расчетов нам поможет формула:

    =VLOOKUP(G5,CHOOSE(IF(H3=”Вступительный”,1,IF(H3=”Полугодовой”,2,3)),$A$4:$E$12,$A$16:$E$24,$A$28:$E$36),MATCH(H4,$A$3:$E$3,0),0) – английская версия

    =ВПР(G5;ВЫБОР(ЕСЛИ(H3=”Вступительный”;1;ЕСЛИ(H3=”Полугодовой”;2;3));$A$4:$E$12;$A$16:$E$24;$A$28:$E$36);ПОИСКПОЗ(H4;$A$3:$E$3;0);0) – русская версия

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

    CHOOSE(IF(H3=”Вступительный”,1,IF(H3=”Полугодовой”,2,3)),$A$4:$E$12,$A$16:$E$24,$A$28:$E$36),MATCH(H4,$A$3:$E$3,0),0) – английская версия

    ВЫБОР(ЕСЛИ(H3=”Вступительный”;1;ЕСЛИ(H3=”Полугодовой”;2;3));$A$4:$E$12;$A$16:$E$24;$A$28:$E$36);ПОИСКПОЗ(H4;$A$3:$E$3;0);0) – русская версия

    Первый аргумент формулы (IF(H3=”Вступительный”,1,IF(H3=”Полугодовой”,2,3) или (ЕСЛИ(H3=”Вступительный”;1;ЕСЛИ(H3=”Полугодовой”;2;3)

    проверяет ячейку “H3” и определяет, на какой уровень экзамена ссылаются. Если это “Вступительный”, функция возвращает данные из диапазона $A$4:$E$12, который содержит оценки для вступительного экзамена.

    Если выбран уровень “Полугодовой”, то система возвращает данные из диапазона $A$16:$E$24, в другом случае она возвращает данные из диапазона $A$28:$E$36.

    Такой подход делает массив таблиц динамическим и трех факторным.

    Пример 5. Получаем последнее значение колонки с помощью функции VLOOKUP (ВПР)

    С помощью ВПР вы можете вычислить последнее число из списка.

    Наибольшее положительное число, которое вы можете использовать в Excel, равно 9.99999999999999E + 307. Это также означает, что самый большой номер поиска в номере VLOOKUP также 9.99999999999999E + 307.

    Я не думаю, что вам когда-нибудь понадобится какой-то расчет, в котором участвует такое большое число. Но с его помощью мы можем получить последнее число в списке.

    Предположим, у вас есть набор данных (в диапазоне ячеек A1:A14), как показано ниже, и вы хотите получить число из последней ячейки в списке.

    Для этого нам подойдет следующая формула:

    =VLOOKUP(9.99999999999999E+307,$A$1:$A$14,TRUE) – английская версия

    =ВПР(9.99999999999999E+307;$A$1:$A$14;ИСТИНА)

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

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

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

    В нашем примере работает третий сценарий.

    Число 9.99999999999999E + 307 это самое большое число, которое можно использовать в Excel, когда оно используется как критерий поиска, тогда функция VLOOKUP (ВПР) возвращает последнее число из списка.

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

    =VLOOKUP(“яяя”,$A$1:$A$14,1,TRUE) – английская версия

    =ВПР(“яяя”;$A$1:$A$14;1;ИСТИНА) – русская версия

    Здесь действует та же логика. Система просматривает все названия из списка. Так как “яяя” самое большое текстовое значение => результатом вычисления будет самое крайнее значение из списка данных.

    Пример 6. Частичный поиск с использованием символов подстановочных знаков и ВПР

    Символы подстановки в Excel могут быть полезными во многих ситуациях.

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

    Например, у вас есть набор данных, как показано ниже, и вы хотите найти компанию “ABC” в списке, но в списке есть только “ABC Ltd” вместо “ABC”.

    Вы не можете использовать “ABC” в качестве значения поиска, так как в столбце “A” нет точного соответствия. Приблизительное совпадение часто приводит к ошибочным результатам и требует сортировки списка в порядке возрастания.

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

    Введите следующую формулу в ячейку “D2” и перетащите ее в другие ячейки:

    =VLOOKUP(“*”&C2&”*”,$A$2:$A$8,1,FALSE) – английская версия

    =ВПР(“*”&C2&”*”;$A$2:$A$8;1;ЛОЖЬ) – русская версия

    Как это работает?

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

    Таким образом, осуществляя поиск, Excel будет понимать, что необходимо искать значение, которое содержит слово из ячейки “С2” (в нашем примере).

    Например, в ячейке “C2” указано название компании “ABC”, поэтому функция просматривает значения в диапазоне данных “A2:A8” и ищет “ABC”. Она находит соответствие в ячейке “A2”, так как та содержит слово “ABC” в “ABC Ltd”. Не имеет значения, есть ли какие-либо символы слева или справа от “ABC”.

    Примечание: ВПР всегда возвращает первое совпадающее значение и перестает искать дальше. Поэтому, если у вас есть название компании “ABC Ltd”. и “ABC Corporation” в списке, она вернет первый по порядку и проигнорирует остальные.

    Пример 7. Функция ВПР возвращает ошибку, несмотря на совпадение в значении поиска

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

    Например, у вас в таблице есть очевидное соответствие (Иван), но система по-прежнему возвращает ошибку.

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

    Решение есть, и зовут его – функция TRIM. Она удаляет все лишние пробелы в значениях.

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

    =VLOOKUP(“Иван”,TRIM($A$2:$A$15),1,0) – английская версия

    =ВПР(“Иван”,СЖПРОБЕЛЫ($A$2:$A$15);1;0) – русская версия

    Для того, чтобы функция TRIM (СЖПРОБЕЛЫ) заработала при использовании VLOOKUP (ВПР), следует вводить формулу после набора не с помощью клавиши Enter на клавиатуре, а с помощью сочетания клавиш Ctrl + Shift + Enter.

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

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

    Но часто нам необходимо использовать VLOOKUP с поиском по несколькими критериям.

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

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

    Осуществить поиск по студенту + названию предмета + уровню экзамена можно с помощью создания вспомогательного столбца, как показано на примере ниже:

    Теперь, нам необходимо создать уникальный ID для каждого студента и уровня экзамена с помощью формулы во вспомогательном столбце: =A2&”|”&B2.

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

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

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

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

    =VLOOKUP($F3&”|”&G$2,$C$2:$D$19,2,0) – английская версия

    =ВПР($F3&”|”&G$2;$C$2:$D$19;2;0) – русская версия

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

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

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

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

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

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

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

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

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

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

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

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

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

    Двумерный поиск по известным строке и столбцу

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

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

    Существует несколько способов выполнить двумерный поиск. Познакомьтесь с возможными вариантами и выберите наиболее подходящий.

    Функции ВПР и ПОИСКПОЗ

    Вы можете использовать связку из функций ВПР (VLOOKUP) и ПОИСКПОЗ (MATCH), чтобы найти значение на пересечении полей Название продукта (строка) и Месяц (столбец) рассматриваемого массива:

    Формула выше – это обычная функция ВПР , которая ищет точное совпадение значения “Lemons” в ячейках от A2 до A9. Но так как Вы не знаете, в каком именно столбце находятся продажи за март, то не сможете задать номер столбца для третьего аргумента функции ВПР . Вместо этого используется функция ПОИСКПОЗ , чтобы определить этот столбец.

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

    • Ищем символы “Mar” – аргумент lookup_value (искомое_значение);
    • Ищем в ячейках от A1 до I1 – аргумент lookup_array (просматриваемый_массив);
    • Возвращаем точное совпадение – аргумент match_type (тип_сопоставления).

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

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

    Функция СУММПРОИЗВ

    Функция СУММПРОИЗВ (SUMPRODUCT) возвращает сумму произведений выбранных массивов:

    Функции ИНДЕКС и ПОИСКПОЗ

    В следующей статье я буду объяснять эти функции во всех деталях, так что сейчас можете просто скопировать эту формулу:

    Именованные диапазоны и оператор пересечения

    Если Вы не в восторге от всех этих сложных формул Excel, Вам может понравиться вот такой наглядный и запоминающийся способ:

    При вводе имени, Microsoft Excel будет показывать подсказку со списком подходящих имен, так же, как при вводе формулы.

    1. Нажмите Enter и проверьте результат

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

    Использование функции

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

    Аргументы функции

    ВПР содержит 4 аргумента.

    Искомое значение

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

    Вторым — указывается именно та таблица (или диапазон ячеек), в которой следует произвести этот поиск.

    Номер столбца

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

    Интервальный просмотр

    Последний аргумент – интервальный просмотр, здесь может быть 2 значения: 0 – ЛОЖЬ, 1 — ИСТИНА. отвечает за точный поиск (совпадения при просмотре сверху вниз). Если ничего не находит, то возвращается ошибка Н/Д (нет данных), 1приблизительный.

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

    Аргумент «Искомое значение»

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

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

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

    Перевести в числовой формат просто — нужно умножить массив на единицу.

    Формулы ВПР и выпадающие списки

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

    Для создания раскрывающегося списка следует произвести ряд действий:

    Поставить курсор в ячейку Е8, где планируется размещение списка.

    Открыть закладку «Данные». Меню «Проверить данные».

    проверка данных

    Выбрать тип данных – «Списки». Источники – диапазон с названиями материалов.

    параметры список источники

    При нажатии кнопки ОК – будет создан раскрывающийся список.

    раскроется список

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

    Открыть «Мастер функций» и выбрать ВПР.

    Первый аргумент – «Искомое значение» — ячейки с раскрывающимися списками. Таблица – диапазон с наименованием материала и цен. Колонка — 2. Функция будет отображаться в следующем формате:

    отобразится формула

    Остается нажать ВВОД и наслаждаться результатом.

    получить результат

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

    изменение цены

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

    Описание функции ВПР

    Смотрите также: “Как убрать колонтитулы в Экселе”

    ВПР – это аббревиатура, которая расшифровывается как “функция вертикального просмотра”. Английское название функции – VLOOKUP.

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

    ВПР по двум условиям в Excel

    Вопрос, который вызывает ступор: как сделать поиск с помощью ВПР по нескольким условиям, ведь функция этого не предусматривает. Но мы можем сделать это с помощью объединения колонок. Используем для этого оператор конкатенации «&», или функцию СЦЕПИТЬ.

    Рассмотрим на примере предыдущей таблицы. Заметили, что в ней — два Соколова? Первый – в Центральном районе, второй — в Южном. Как найти продажи второго? Точный поиск ВПР вернет первого, если искать по фамилии. Придется искать по двум параметрам – ФИО и району.

    Для этого добавим дополнительный столбец D в таблице с исходными данными. С помощью функции СЦЕПИТЬ, объединим в ней имена и регионы. Вот так:

    дополнительный столбец

    Теперь с помощью ВПР будем искать в массиве D3:D17 связку Фамилия+Регион:

    ВПР по двум параметрам

    Первым аргументом записано «I2 & I3», т.е. упомянутая выше связка Фамилия+Регион. Оператор конкатенации «&» объединяет строки, записанные в I2 («Соколов») и I3 («Южный»). Тогда ВПР будет искать значение «СоколовЮжный». И найдет её в ячейке D11, вернув те продажи, что мы искали.

    Аналогично можно использовать три и более параметров.

    If you are on a personal connection, like at home, you can run an anti-virus scan on your device to make sure it is not infected with malware.

    If you are at an office or shared network, you can ask the network administrator to run a scan across the network looking for misconfigured or infected devices.

    Another way to prevent getting this page in the future is to use Privacy Pass. You may need to download version 2.0 now from the Chrome Web Store.

    Cloudflare Ray ID: 67cff00d18deb4d8 • Your IP : 188.121.15.4 • Performance & security by Cloudflare

    Вложенные функции Excel (функция ЕСЛИ и функция ВПР)

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

    вложенные функции

    Мы хотели бы что бы при вводе в ячейку С111 нужного артикула выводилась цена товара либо в рублях либо в у.е. Договоримся, что в соседней ячейке Е111 мы будем указывать вручную в каких единицах выводить стоимость (в следующих статьях мы рассмотрим как избавиться от ручных операций и создать например Чекбокс (флажек) или список выбираемых значений) . Итак поиск по артиклу мы выполняем с помощью функции ВПР: ВПР( C111 ; C115:E128 ;2;0) — поиск по цене в рублях, и ВПР(C111;C115:E128;3;0) — поиск по цене в у.е. Теперь осталось применить функцию Если для выбора между двумя функциями ВПР, напомним как работает функция Если: ЕСЛИ(лог_выражение; [значение_если_истина]; [значение_если_ложь]), а в нашем случае ЕСЛИ(условие; [ВПР 1 ]; [ВПР 2]), получили вложенные функции:

    =ЕСЛИ( E111 =»рубли»;ВПР( C111 ; C115:E128 ;2;0);ВПР( C111 ; C115:E128 ;3;0))

    пример вложенных функций

    Таким образом мы получили готовую форму, с двумя условиями: артикул, валюта.

    вложенные формулы

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

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

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

    1. Перейдите в ячейку, в которой происходит выбор фамилии.
    2. Откройте вкладку «Данные».
    3. Кликните на указанный инструмент и выберите пункт «Проверка данных».
    1. В новом окне в графе «Тип данных» выберите пункт «Список».
    1. После этого появится новое поле «Источник». Кликните туда.
    2. Затем выделите первый столбец. Ссылка на ячейки подставится автоматически.
    3. Для продолжения нажмите на «OK».
    1. Благодаря этому у вас появится список всех фамилий, которые находятся в базе. Выберите что-нибудь из предложенного.
    1. Вы увидите, что данные автоматически изменились. При этом всё подставится согласно выбранной фамилии.

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

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

    Да как бы все есть

    The opinion expressed by me is not an official position of Microsoft

    • Изменено Vector BCO Moderator 21 июля 2016 г. 18:29
    • Предложено в качестве ответа Vector BCO Moderator 31 июля 2016 г. 14:33
    • Помечено в качестве ответа Alexander Rusinov Moderator 8 августа 2016 г. 10:08

    Все ответы

    Да как бы все есть

    The opinion expressed by me is not an official position of Microsoft

    • Изменено Vector BCO Moderator 21 июля 2016 г. 18:29
    • Предложено в качестве ответа Vector BCO Moderator 31 июля 2016 г. 14:33
    • Помечено в качестве ответа Alexander Rusinov Moderator 8 августа 2016 г. 10:08

    А Excel 2016 у Вас по ключу или по подписке Office 365?

    У меня по ключу (все обновления установлены). Отсутствие ВПР через поиск функции подтверждаю:

    Также есть возможность проверить и в офисе по подписке. Функция также отсутствует, но еще не стоят последние обновления. Сейчас скачаю обновления и тоже проверю.

    Поиск приблизительного значения ВПР в Excel

    В примере (снимок № 2) ВПР используется для вычисления прибавки на основе текущего оклада. Критерий отбора – это заработная плата, написана в F3. Матрица – это диапазон $I$3:$J$10 (весь массив).

    Адрес является абсолютным (это показывают знаки $), чтобы осталась возможность копировать всю получившуюся конструкцию без изменения диапазона. Col_index_num равен 2 (процент повышения находится в соседнем массиве).

    Параметр порядка сортировки Range_lookup – 1, потому что нужно, чтобы формула выдавала результат, даже если нет идеального соответствия. Если Lookup_value составляет 35 850, ВПР не может найти идеальное совпадение в массиве, но ближайшее минимальное число – 35 000, поэтому ответом на выражение будет 4%. На этом ликбез «ВПР в Excel для чайников» заканчивается.

    Lookup

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

    Альтернатива ВПР в Excel. Функции СМЕЩ и ПОИСКПОЗ

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

    Альтернатива ВПР в Excel. Функции СМЕЩ и ПОИСКПОЗ

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

    Ссылка — ячейка, от которой рассчитывается смещение по строкам и столбцам.

    Смещение по строкам — указываем число, на какое количество строк надо сдвинуться. Положительное число — количество строк вниз, отрицательное число — количество строк вверх.

    Смещение по столбцам — указываем число, на какое количество столбцов надо сдвинуться. Положительное число — количество столбцов вправо, отрицательное число — количество столбцов влево.

    К примеру для таблицы в скриншоте сверху функция СМЕЩ(C3;-1;1) вернет значение Петрович. Почему именно так? В качестве стартовой ячейки мы указали ячейку С3 (в ней содержится имя Иван). Относительно этой ячейки мы сдвигаемся на одну строку вверх (второй параметр -1) и на один столбец вправо (третий параметр 1).

    Искомое значение — это то значение, что мы ищем в таблице

    Просматриваемый массив — таблица, где мы ищем искомое значение

    Тип сопоставления — как будет искать Excel это значение. Используйте значение 0 для поиска точного результата.

    К примеру для таблицы в скринщоте выше функция ПОИСКПОЗ(«Петров»;B2:B6;0) вернет значение 3. Мы ищем фамилию Петров в перечне фамилий. Она там третья по списку, поэтому формула вернула число 3.

    Теперь давайте объединим наши знания и решим задачу по альтернативному ВПР в Экселе.

    Альтернатива ВПР в Excel. Функции СМЕЩ и ПОИСКПОЗ

    Итак, для нашего примера формула будет следующая:

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

    Как пользоваться ВПР в Excel?

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

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

    Пример №1

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

    Решение:

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

    • В любом удобном месте (для облегчения работы — лучше на новой странице) создать таблицу с искомыми параметрами. Названия могут совпадать с исходными или отличаться от них; главное — чтобы было прямое соответствие значений, иначе выведенная информацию окажется неверной или бесполезной.

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

    • Выделить нажатием ячейку, следующую за наименованием товара, и перейти на вкладку Excel «Формулы».

    • Щёлкнуть по кнопке «Вставить функцию» — самой крайней слева в открывшейся «ленте».

    • В открывшемся окошке вызвать верхний выпадающий список и выбрать кликом мыши пункт «Полный алфавитный перечень».

    • Пролистать перечень вниз, выделить строчку «ВПР» и щёлкнуть по кнопке «ОК».

    • Другой способ вызвать функцию VLOOKUP — на той же вкладке «Формулы» открыть выпадающий список «Ссылки и массивы» и выбрать пункт «ВПР».

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

    • Во второй строчке того же окна — зажав левую клавишу мыши, выделить исходную таблицу Excel целиком.

    • В третьей — порядковый номер столбца, в котором требуется осуществить поиск…

    • …Или сам столбец, выделив его аналогичным образом.

    • В четвёртой — указать, требуется точный поиск по таблице Excel (значение «0») или программа должна будет подобрать ближайший подходящий параметр (значение «1»). В этом примере и при решении большинства других задач, связанных с применением функции ВПР, нужны точные совпадения — следовательно. Необходимо вписать в текстовое поле «0», а затем нажать на «ОК».

    • Если значение в строке появилось, но оно не соответствует ожидаемому, тогда следует проверить правильность установленного формата ячейки.

    • В нашем примере в результате должно получиться не абсолютное значение, а дата. Чтобы привести данные в требуемый вид, необходимо щёлкнуть по «неправильной» ячейке правой кнопкой мыши и выбрать в открывшемся контекстном меню пункт «Формат ячеек»…

    • …После чего найти требуемый — «Дата» — и кликнуть по кнопке «ОК».

    • Готово! Ячейка приобрела приемлемый для работы вид — таким он и будет оставаться вплоть до нового изменения.

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

    • Чтобы добиться требуемого результата, следует или повторить для новой строчки все перечисленные манипуляции, или выделить уже готовую и скопировать в верхнем текстовом поле Excel формулу…

    • …После чего вставить её в нужную строку…

    • …Не забыв заменить номер столбца, в котором на этот раз должен быть осуществлён поиск — теперь это не «3», а «4».

    • Нажав на клавишу Enter и при необходимости вновь подкорректировав формат ячейки, пользователь получит заполненную краткую таблицу с достоверной информацией из большой сводной.

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

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

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

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

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

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

    Пример №2

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

    Решение:

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

    • Теперь нужно добавить к одной из таблиц Excel (обычно — более содержательной) новый столбец и назвать его в соответствии с переносимыми данными.

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

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

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

    • В третьей — указать номер столбца второй таблицы, который планируется сопоставить с первым. Последняя строка, как и в первом примере, должна принять значение «0», иначе получить результаты, точно соответствующие заданным параметрам, не выйдет.

    • Щёлкнув по кнопке «ОК», пользователь получит в первой строке искомый результат.

    • Теперь не нужно подставлять формулу в каждую строку — достаточно зажать левой клавишей мыши крестик в правом нижнем углу ячейки и «растянуть» её на все позиции — данные будут подставлены автоматически и в нужном порядке.

    • Соответствия сохранятся даже при пересортировке строк в первой таблице.

    • А кроме того, в неё не будут включены «лишние» данные, отсутствующие во второй.

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

    Возможность работы с несколькими условиями

    Ещё одним несомненным достоинством функции VLOOKUP является его способность работать с несколькими параметрами, присущими вашему товару. Чтобы найти товар по двум или более характеристикам, необходимо:

    1. Создать два (или, при необходимости, более) условий для поиска.
    2. Добавить новый столбец. в который в процессе работы функции добавятся все прочие столбцы, по которым происходит поиск товара.
    3. В полученном столбце, по вышеописанному алгоритму, вводим уже знакомую нам формулу функции VLOOKUP.

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

Ссылка на основную публикацию