Пример работы с функцией впр. Почему не работает формула ВПР (VLOOKUP) в Excel — Решение

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

ВПР

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

Вставьте в формулу функцию ВПР, указав в первом ее аргументе искомое значение, по которому осуществляется связь [ 1 ] . Во втором аргументе задайте диапазон ячеек, в которых следует производить выборку [ 2 ] , в третьем [ 3 ] - номер столбца, из которого будут подставляться данные, а в четвертом введите значение ЛОЖЬ, если хотите найти точное соответствие, или ИСТИНА, если нужен ближайший приблизительный вариант [ 4 ] .

Создание диаграмм

Для создания диаграммы введите в Excel данные с указанием заголовков столбцов [ 1 ] , выберите на вкладке «Вставка» пункт «Диаграммы» [ 2 ] и укажите требуемый тип диаграммы. В Excel 2013 имеется вкладка «Рекомендуемые диаграммы» [ 3 ] , на которой размещены типы, соответствующие введенным вами данным. После определения общего характера диаграммы Excel открывает вкладку «Конструктор», где производится ее более точная настройка. Огромное количество наличиствующих здесь параметров позволяет придать диаграмме тот внешний вид, который вам нужен.

Функции ЕСЛИ и ЕСЛИОШИБКА

К числу наиболее популярных функций Excel относятся ЕСЛИ и ЕСЛИОШИБКА . Функция ЕСЛИ позволяет определить условную формулу, которая при выполнении условия вычисляет одно значение, а при его невыполнении - другое. Например, студентам, получившим за экзамен 80 баллов и больше (оценки выставлены в столбце C), можно присвоить признак «Сдал», а тем, кто получил 79 баллов и меньше, - признак «Не сдал».

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

Сводная таблица

Сводная таблица, по сути, представляет собой итоговую таблицу, позволяющую подсчитывать число элементов и вычислять среднее значение, сумму и другие функции на основе определенных пользователем опорных точек. В версии Excel 2013 дополнительно появились «Рекомендуемые сводные таблицы», упрощающие создание таблиц, в которых будут отображаться нужные вам данные. Например, чтобы подсчитать средний балл студентов в зависимости от их возраста, переместите поле «Возраст» в раздел Строки [ 1 ] , а поля с оценками - в раздел «Значения» [ 2 ] . В меню значений выберите пункт «Параметры полей значений» и в качестве операции укажите «Среднее» [ 3 ] . Таким же образом можно подсчитывать итоги и по другим категориям, например, вычислять число сдавших и не сдавших экзамен в зависимости от пола.

Сводная диаграмма

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

Мгновенное заполнение

Лучшая, пожалуй, новая функция Excel 2013 - «Мгновенное заполнение» - позволяет эффективно решать повседневные задачи, связанные с быстрым переносом нужных блоков информации из смежных ячеек. В прошлом, при работе со столбцом, представленным в формате «Фамилия, Имя», пользователю приходилось вручную извлекать из него имена или искать какие-то очень сложные обходные пути. Предположим, что тот же самый столбец с фамилиями и именами есть и в Excel 2013. Достаточно ввести имя первого человека в ближайшую справа ячейку [ 1 ] и на вкладке «Главная» выбрать «Заполнить» и «Мгновенное заполнение» [ 2 ] . Excel автоматически извлечет все прочие имена и заполнит ими ячейки справа от исходных.

Быстрый анализ

Новый инструмент быстрого анализа Excel 2013 помогает ускорить создание диаграмм из простых наборов данных. После выделения данных рядом с правым нижним углом выделенной области появляется характерный значок. Щелкнув на нем, вы переходите в меню «Быстрого анализа». Там представлены инструменты «Форматирования», «Диаграмм», «Итогов», «Таблиц» и «Спарклайнов». Щелкая мышью на этих инструментах, вы увидите поддерживаемые ими возможности.

Power View

Интерактивный инструмент исследования и визуализации данных, Power View, предназначен для извлечения и анализа больших объемов данных из внешних источников. В Excel 2013 для вызова функции Power View перейдите на вкладку «Вставка» и нажмите кнопку «Отчеты». Отчеты, созданные с помощью Power View, уже готовы к презентации и поддерживают режимы чтения и полноэкранного представления. Интерактивную их версию можно даже экспортировать в PowerPoint. Руководства по бизнес-анализу, представленные на сайте Microsoft, помогут вам в кратчайшие сроки стать специалистом в этой области.

Условное форматирование

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

Транспонирование столбцов в строки и наоборот

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

Важнейшие комбинации клавиш

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

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

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

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

Синтаксис

=VLOOKUP(lookup_value, table_array, col_index_num, ) – английская версия

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

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

• lookup_value (искомое_значение) – это величина поиска, которую вы ищете в крайнем левом столбце таблицы. Это может быть значение, ссылка на ячейку или текстовая строка. В примере со студентами это их фамилии;
• table_array (таблица) – это диапазон данных, в котором вы ищете значение. Это может быть ссылка на диапазон ячеек или именованный диапазон. В примере с таблицей со студентами, это будет вся таблица, которая содержит оценку и фамилии студентов;
• col_index (номер_столбца) – Это порядковый номер столбца диапазона данных, из которого вы хотите получить искомую величину.
• ([интервальный_просмотр]) – этот аргумент указывает, вы ищете точное совпадение данных поиска или приблизительное. Для этого используйте “0” – если точное, “1” – если приблизительное совпадение.

Дополнительная информация

• совпадение искомых данных может быть точным и приблизительным;
• при сопоставлении по приблизительной точности данных убедитесь, что данные в таблицах отсортированы в убывающем порядке (от большого к малому). В противном случае, результат сопоставления будет некорректным;
• при сопоставлении данных по приблизительной точности:
  – если функция не найдет искомое значение, она выдаст наибольшую величину, которая будет меньше чем значения поиска;
  – если функция при сопоставлении выдает ошибку #N/A, это означает что искомое значение меньше чем самая маленькая величина в искомом диапазоне;
  – вы можете использовать подстановочные знаки для искомых значений, указанных в текстовом формате.

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

Примеры использования ВПР в 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. Двухфакторный поиск данных

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

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

Ниже пример использования двухфакторного поиска с помощью VLOOKUP (ВПР) :

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

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

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

– английская версия

– русская версия

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

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

Пример 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) – русская версия

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

Пример 9. Обработка ошибок с помощью функции ВПР

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

В Excel есть возможность заменить сообщения об ошибке на заданное вами текстовое или числовое значение типа “Нет”, “Не доступно”, “Нет данных” и.т.д.

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

Для того, чтобы заменить сообщение ошибки на какое-то более формальное значение нам поможет функция :

=IFERROR(VLOOKUP(D2,$A$2:$B$7,2,0),”Нет данных”) – английская версия

=ЕСЛИОШИБКА(ВПР(D2;$A$2:$B$7;2;0);”Нет данных”) – русская версия

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

Изучив материал, изложенный в этой статье, вы можете быть уверены в том, что вы знаете о функции ВПР(VLOOKUP) всё!

Вам также может быть интересно

Функция СЖПРОБЕЛЫ (TRIM) в Excel используется для удаления лишних пробелов в тексте. Содержание статьиЧто возвращает

Функция НАИМЕНЬШИЙ (SMALL) в Excel используется для получения минимального значения из заданного диапазона ячеек. Более

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

Немного о функции ВПР

Итак, что же такое ВПР ? Думаю, Вы уже догадались, что это одна из множества функций Excel.

Данная статья рассчитана на читателя, который владеет базовыми знаниями о функциях Excel и умеет пользоваться такими простейшими из них как SUM (СУММ), AVERAGE (СРЗНАЧ) и TODAY(СЕГОДНЯ).

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

Вот пример списка или базы данных. В данном случае, это список товаров, которые продаёт вымышленная компания:

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

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

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

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

Другими словами, если Вы введёте в ячейку функцию ВПР и передадите ей в качестве аргумента один из уникальных идентификаторов Вашей базы данных, то в результате в ячейке появится какой-то кусок информации, связанный с этим уникальным идентификатором. Применительно к примеру, приведенному выше: если бы мы ввели в качестве аргумента значение из столбца Item Code , то как результат могли бы получить соответствующее ему описание товара (Description), его цену (Price), или наличие (In Stock). Какую именно информацию должна вернуть формула, Вы сможете решить в процессе её создания.

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

Создаем шаблон

Давайте создадим шаблон счёта, который мы сможем использовать множество раз в нашей вымышленной компании.

Для начала, запустим Excel…

… и создадим пустой счёт.

Вот как это должно работать: пользователь шаблона будет вводить коды товаров (Item Code) в столбец А. После чего система будет извлекать для каждого товара описание и цену, а далее рассчитывать итог по каждой строке. Количество необходимо указать самостоятельно.

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

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

Вставляем функцию ВПР

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

Далее делаем активной ту ячейку, в которой должна появиться информация, извлекаемая функцией ВПР из базы данных. Любопытно, что именно на этом шаге многие путаются. Поясню, что мы будем делать далее: мы создадим формулу, которая извлечёт из базы данных описание товара, код которого указан в ячейке A11. Куда мы хотим поместить это описание? Конечно, в ячейку B11. Следовательно, и формулу мы запишем туда же.

Итак, выделите ячейку B11:

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

Появляется диалоговое окно, в котором можно выбрать любую существующую в Excel функцию. Чтобы найти то, что нам необходимо, мы можем ввести в поле Search for a function (Поиск функции) слово lookup (или поиск в русскоязычной версии), поскольку нужная нам функция – это функция поиска. Система покажет список всех связанных с этим понятием функций Excel. Найдите в списке VLOOKUP (ВПР), выберите её мышкой и нажмите ОК .

Появится диалоговое окно Function Arguments (Аргументы Функции), предлагающее ввести все необходимые аргументы для функции ВПР . Представьте себе, что это сама функция задаёт Вам следующие вопросы:

1. Какой уникальный идентификатор Вы ищите в этой базе данных?
2. Где находится база данных?
3. Какую информацию Вы бы хотели извлечь из базы данных?

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

Заполняем аргументы функции ВПР

Первый аргумент, который надо указать, это Lookup_value (Искомое_значение). Функция просит нас указать, где искать значение уникального кода товара, описание которого надо извлечь. В нашем случае, это значение в столбце Item code , которое мы ввели раньше в ячейку A11.

Нажмите на иконку выбора справа от строки ввода первого аргумента.

Затем кликните один раз по ячейке, содержащей код товара и нажмите Enter .

Значение ячейки A11 взято в качестве первого аргумента.

Теперь нужно задать значение аргумента Table_array (Таблица). Другими словами, надо объяснить функции ВПР, где находится база данных, в которой необходимо выполнять поиск. Кликните по иконке выбора рядом со вторым аргументом:

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

… и нажимаем Enter . В строке для ввода второго аргумента автоматически отобразится диапазон ячеек, в котором содержится вся база данных. В нашем случае это "Product Database"!A2:D7 .

Теперь займёмся третьим аргументом Col_index_num (Номер_столбца). С помощью этого аргумента мы указываем функции ВПР , какой именно кусок информации из базы данных мы хотим извлечь. В данном случае нам необходимо извлечь описание товара (Description). Если Вы посмотрите на базу данных, то увидите, что столбец Description это второй столбец в таблице. Это значит, что для аргумента Col_index_num (Номер_столбца) мы вводим значение 2:

Важно заметить, что мы указываем значение 2 не потому, что столбец Description находится во втором по счету столбце от начала листа Excel, а потому, что он второй по счёту в диапазоне, который указан в качестве аргумента Table_array (Таблица) функции ВПР (первым является столбец с уникальным идентификатором). Если наша база данных будет начинаться где-то со столбца K листа Excel, то мы всё равно укажем значение 2 в этом поле.

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

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

Так как первый столбец нашей базы данных не отсортирован, мы вводим для этого аргумента значение FALSE (ЛОЖЬ):

Последний штрих…

Вот и всё! Мы ввели всю информацию, которая требуется функции ВПР , чтобы предоставить нам то значение, которое нас интересует. Жмите ОК и обратите внимание, что описание товара, соответствующее коду R99245 , появилось в ячейке B11.

Например, можно найти цену автомобильной детали по ее номеру.

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

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

Это видео - часть учебного курса Использование функции ВПР .

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

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

Синтаксис

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

Например:

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

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

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

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

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

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

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

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

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

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

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

В ячейке D13 указано искомое_значение, т. е. значение, которое вам нужно найти.

Ячейки B2:E11 (выделены в таблице желтым цветом) представляют собой таблицу или диапазон, в котором находится искомое значение.

3 - это номер_столбца в таблице, которая содержит возвращаемое значение. В приведенном примере третьим столбцом в является столбец Цена детали , поэтому результатом формулы будет значение из этого столбца.

Для аргумента интервальный_просмотр указано значение ЛОЖЬ, поэтому возвращаемым значением будет точное совпадение.

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

Примеры

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

Пример 1

Пример 2

Пример 3

Пример 4

Пример 5

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

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

Сегодня мы рассмотрим:

Вводная часть: Синтаксис

Данная функция имеет четыре параметра:

• «ЧТО» - редко использующееся значение, указывающее на объект поиска или же конкретная ссылка на ячейку с искомым значением. Последнее можно смело причислить к самому используемому параметру при работе с функцией ВПР.

• «ГДЕ» - ссылка на диапазон ячеек (массив двумерный), в первом столбце которого и будет происходить поиск значения параметра «ЧТО».

• «НОМЕР СТОЛБЦА» - номер столбца в диапазоне, из которого будет возвращено значение;

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

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

Простой пример: сводим данные двух таблиц

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

• Становимся на ячейку «D6».

• Вызываем служебное окно консоли «fx», нажатием соответствующей клавиши, и в заданном окне мастера функций активируем чек бокс «Категории».

• Выбираем пункт «Ссылки и массивы».

• В боксе выбора функции устанавливаем значение «ВПР».

• Нажимаем кнопку «ОК» и переходим к следующему шагу - вводу аргументов этой функции.

• Используя левую кнопку мышки, сделайте клик по первой ячейки вашего списка наименований, в нашем примере этому действию назначается активация ячейки «B6». Итак, пункту «Искомое значение» соответствует значение «B6».

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

• Теперь требуется превратить ссылку на таблицу, так сказать, в абсолютную - выделяем аргумент из примера «G6:I10» и жмем клавишу «F4».

• В итоге мы видим, что прежняя ссылка изменилась: исходные символы стали окружены долларовыми знаками «$G$6:$I$10», чего и требовалось достигнуть.

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

• Ну и наконец, четвертый параметр, который нам необходимо указать - это «нуль», в графе «Интервальный просмотр». Так как значение «1» соответствует числовым параметрам данных, в нашем же случае используется поиск искомого объекта, так сказать, в текстовом виде, поэтому наш выбор очевиден - «нуль».

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

Теперь осталось одно - ввести формулу умножения цены на количество и наслаждаться конечным результатом.

• В ячейке «E6» ставим знак равенства.

• Перемещаем маркер на позицию «С6».

• Далее нажимаем знак умножения.

• Переходим на ячейку «D6» и жмем клавишу «Enter».

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

На этом, все - точных расчетов вам, уважаемый читатель!