No Image

Что делает функция впр

СОДЕРЖАНИЕ
481 просмотров
10 марта 2020

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

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

Видео-урок: Как использовать функцию ВПР в Excel

Функция ВПР в Excel для чайников

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

Синтаксис

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

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

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

  • lookup_value(искомое_значение) – это величина для поиска, из крайнего левого столбца таблицы. Это может быть значение, ссылка на ячейку или текстовая строка. В примере со студентами это их фамилии;
  • table_array (таблица) – это диапазон данных, в котором будет осуществлен поиск. Это может быть ссылка на диапазон ячеек или именованный диапазон. В примере с таблицей со студентами, это будет вся таблица, которая содержит оценку и фамилии студентов;
  • col_index (номер_столбца) – это порядковый номер столбца в диапазоне с данными, из которого будет получена искомая величина;
  • [range lookup]([интервальный_просмотр]) – этот аргумент указывает на точность совпадения данных при поиске. Укажите “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($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 с поиском по несколькими критериям.

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

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

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

Теперь, нам необходимо создать уникальный >=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) :

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

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

Функция ЕСЛИОШИБКА(IFERROR) проверяет, если в результате вычислений по формуле выдается ошибка, то она выдает, то значение, которое вы указали в функции ЕСЛИОШИБКА(IFERROR), например “Нет данных”.

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

Что такое ВПР в Excel?

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

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

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

Совет: научиться правильно применять функцию VLOOKUP чуть сложнее, чем построить диаграмму в Excel; если планируется однократный поиск по единственной или нескольким строкам таблицы, проще будет воспользоваться комбинацией клавиш Ctrl + F и обработать данные вручную.

Как пользоваться ВПР в 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.

Вопросы от новичков

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

Читайте также:  Как называется песня где есть такие слова

Как сравнить таблицы с помощью ВПР?

Сравнение двух и более таблиц проводится почти так же, как добавление нового ряда данных:

  • Открыть обе таблицы, а при необходимости — перенести их на один лист.

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

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

  • Когда в ячейке появится требуемой значение, достаточно «растянуть» его на все позиции — данные подставятся автоматически.

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

Как с помощью ВПР сделать выпадающий список?

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

  • Открыть исходную таблицу и способом, подробно описанным в первом примере, подготовить «результирующую».

  • Перейти на вкладку «Данные» и найти в «ленте» инструмент «Проверка данных».

  • Щёлкнув по нему, вызвать выплывающее меню, а далее — кликнуть по одноимённой строчке.

  • Выбрать в новом выплывающем меню «Тип данных» пункт «Список».

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

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

Важно: при изменении параметров в исходной таблице соответствующие коррективы будут внесены Excel и в «результирующую».

Подводим итоги

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

ФУНКЦИЯ ВПР используется, если нужно найти элементы в таблице или диапазоне по строкам. Например, можно найти цену на автомобильную часть по номеру детали или получить имя сотрудника на основе его кода сотрудника.

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

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

= ВПР (необходимые условия для поиска, номер столбца в диапазоне, содержащий возвращаемое значение, возвращаемое приближенное или точное соответствие — обозначено как 1/истина или 0/ложь).

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

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

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

= ВПР (A2; A10: C20; 2; ИСТИНА)

= ВПР (a2; сведения о клиенте)! А:Ф, 3, ЛОЖЬ)

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

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

Искомое_значение может являться значением или ссылкой на ячейку.

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

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

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

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

Приближенное соответствие: 1/истина предполагает, что первый столбец в таблице отсортирован либо в числовом формате, либо в алфавитном порядке, а затем будет искать ближайшее значение. Это способ по умолчанию, если не указан другой. Например, = ВПР (90; a1: B100; 2; истина).

Точное совпадение: 0/ложь осуществляет поиск точного значения в первом столбце. Например, = ВПР ("Смит"; a1: B100; 2; ложь).

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

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

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

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

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

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

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

= ВПР (искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением, приближенное соответствие (истина) или точное совпадение (ложь)).

Примеры

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

Пример 1

Пример 2

Пример 3

Пример 4

Пример 5

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

Здесь в столбцах A-F и H есть значения или формулы, которые используют только значения на листе, а остальные столбцы используют функцию ВПР и значения столбца A (клиентский код) и столбец B (юрист) для получения данных из других таблиц.

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

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

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

Поле, связывающее таблицы (в диалоговом окне отображаются в круглых скобках). Это lookup_value формулы функции ВПР.

Имя связанной таблицы подстановки. Это table_array формулы функции ВПР.

Поле (столбец) в связанной таблице подстановки с данными, которые вы хотите добавить в новый столбец. Эти сведения не отображаются в диалоговом окне Управление связями. чтобы узнать, какое поле вы хотите извлечь, вам потребуется ознакомиться со связанной таблицей подстановки. Вы хотите отметить номер столбца (A = 1) – это col_index_num в формуле.

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

В нашем примере столбец G использует юрист ( lookup_value) для получения данных о тарифных курсах из четвертого столбца ( col_index_num = 4) из таблицы судебные тблатторнэйс ( table_array) с формулой = ВПР ([@Attorney], tbl_Attorneys, 4, ложь).

В формуле также могут использоваться ссылки на ячейки и ссылки на диапазоны. В нашем примере это было бы = ВПР (a2; "Юристы"! А:Д, 4, ложь).

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

Комментировать
481 просмотров
Комментариев нет, будьте первым кто его оставит

Это интересно
Adblock
detector