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





Быстро и правильно сверить две таблицы или заполнить отчёт можно с помощью функций ВПР и ГПР.

Конспекты

Курсы

Практикум


Скачать файл
Ссылка на курс
Теги: конспекты

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

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

ФАЙЛЫ ЭТОГО УРОКА по Excel МОЖНО СКАЧАТЬ ВНИЗУ СТРАНИЦЫ

 

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

В файле [VLookup 1 Tires and Furniture.xlsx] на листе Шины1 в столбцах E:J расположена таблица (выгрузка из учётной системы, прислана коллегой и т.д.), содержащая информацию по всем товарам, с которыми работает компания. Предположим, Вы получили запрос от коллег (клиента, руководителя) по трём товарам, названия которых указаны в зелёных ячейках A3:A5. Из таблицы нужно узнать производителя этих товаров (вписать их в жёлтые ячейки B3:B5) и дату покупки (заполнить жёлтые ячейки C3:C5). Для первого эксперимента таблица содержит совсем мало строк, чтобы Вы могли легко проверить полученные результаты, но в жизни такие исходные таблицы зачастую содержат сотни или даже тысячи строк.

Чтобы вписать в B3 производителя шин Pirelli, нужно найти в таблице E:J ячейку, расположенную на пересечении строки с названием Pirelli и столбца с заголовком Производитель. То есть задача сводится к обычному поиску ячейки по двум координатам – строке и столбцу. Столбец в большинстве случаев очевиден, а вот поиск строки в многострочных таблицах как раз и помогает выполнить функция ВПР.

В ячейке B3 вписать =ВП и под курсором мыши появится полное название нашей функции, выделенное синим цветом.

Рисунок 1

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

Рисунок 2

На экране появится окно аргументов функции ВПР.

Рисунок 3

Искомое_значение – название для поиска строки, в нашем примере это значение Pirelli из ячейки A3. Поле можно заполнить от руки, а можно щёлкнуть по ячейке с нужным текстом.

Таблица – показать рабочую таблицу, начиная выделять с того столбца, по которому будем искать ячейку с названием Pirelli (Excel всегда воспринимает первый столбец как названия строк, при работе с функцией ВПР эту настройку отрегулировать нельзя) и как минимум до того столбца, из которого хотите получить данные (но можно и больше). В нашем примере таблицу можно выделить по заголовкам столбцов F:J.

Таблицу можно выделить и как диапазон F3:J11, но в таком случае для корректного копирования формулы придётся фиксировать этот диапазон клавишей F4, чтобы получилось $F$3:$J$11. Попробуйте самостоятельно в ячейках B7:B9 написать ВПР и применить такой способ выделения таблицы.
Выделяя диапазон ячеек, стоит помнить – когда в таблице появятся новые строки, формулы также потребуют корректировки. Кстати, чтобы всё-таки не исправлять формулы в подобных ситуациях, попробуйте применить к исходной таблице команду вкладки Главная Форматировать как таблицу. Подробнее об этом инструменте можно почитать в соответствующем уроке.

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

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

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

Рисунок 4

Интервальный_просмотр – очень интересный параметр и отличный помощник, но как его использовать я поясню в самом конце этого урока. Пока нам нужно его выключить, так как по умолчанию, оказывается, он включён, даже если в поле Интервальный_просмотр ничего не вписано. Поставьте в него 0 или слово ЛОЖЬ, которое на языке программы читается как слово нет. Всё, теперь программа будет искать точное совпадение и никаких сюрпризов не будет. В окне аргументов показан правильный ответ Италия, можно нажимать ОК.

Рисунок 5

Получить ответы по Goodyear и Bridgestone очень просто – потяните ячейку B3 за нижний правый угол вниз, когда курсор примет вид маленького чёрного крестика, или же просто выполните двойной щелчок левой кнопкой мыши по этому чёрному крестику.

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

Рисунок 6

Если вместо Pirelli написать Пирелли, то функция покажет ошибку вида #Н/Д (читайте как НЕТ ДАННЫХ) и это будет означать, что программа не нашла строки с таким названием.

Рисунок 7

 

ПОИСК ДВУХ НАЗВАНИЙ В ДВУХ СТОЛБЦАХ ТАБЛИЦЫ

В этом же файле [VLookup 1 Tires and Furniture.xlsx] на листе Шины2 задача несколько усложнилась. Теперь для поиска строки не одно название, а два – наименование товара Pirelli и его характеристика R 16. Используя эти данные, необходимо получить из таблицы номер партии. В таблице имеются два столбца Наименование F и Размер G. Но функция ВПР не сможет использовать для поиски строки два столбца таблицы, поэтому необходимо соединить их в один. Помочь в этом может функция СЦЕПИТЬ или знак амперсанд &. В примере будем использовать знак &, так как это проще.

В файле заготовлен вспомогательный столбец H, в котором должен оказаться текст из двух левых колонок. В ячейке H3 пишем знак равно = , щёлкаем по ячейке F3, переключаем раскладку клавиатуры на английский EN язык нажатием клавиш ALT+SHIFT (клавиши могут быть изменены на сочетание CTRL+SHIFT) и нажимаем SHIFT+7 (цифру 7 используем из длинного ряда над буквами), далее щёлкаем по G3 и нажимаем клавишу ENTER Для копирования формулы до конца таблицы, выполните двойной щелчок левой кнопкой мыши по  нижнему правому углу выделенной ячейки H3 (курсор должен принять вид чёрного крестика). Формула и результат представлены на рисунке ниже.
В получившемся результате нет пробела между фрагментами текста, но так как столбец вспомогательный, то этим можно не озадачиваться. О соединении текста из разных ячеек в одну с добавлением пробела или других символов можно прочитать в соответствующем уроке.

Рисунок 8

В ячейке C3 вписать =ВПР( и нажать SHIFT+F3 или значок fx (расположен слева от строки формул) и заполнить аргументы функции, как показано на рисунке ниже. Все поля заполняем по уже рассмотренным выше правилам, единственная небольшая хитрость заметна в первом поле Искомое_значение –в него записана формула, которая соединяет два искомых значения в том же логическом порядке, в котором мы соединяли текст из двух колонок таблицы в одну. И не забываем, что таблицу нужно начать выделять строго с того столбца, по которому искать название строки – в данном примере это вспомогательный столбец H, он должен быть первым из выделенных.

Рисунок 9

Для закрепления примера на листе Мебель решите аналогичную задачу самостоятельно. В столбцах A:D расположена таблица с данными по остаткам мебели. В ячейку G3 с помощью функции ВПР нужно подставить значение остатка по Столам (ячейка G1) из категории Мебель для взрослых (коротко название категории указано в ячейке G2).  Во вспомогательном столбце A с помощью знака & соединить текст колонок B и C, а потом в ячейке G3 вписать функцию ВПР.

 

СВЕРКА ДВУХ ТАБЛИЦ С ПОМОЩЬЮ ВПР

В файле [VLookup 2 Animals.xlsx] смоделирована одна из наиболее распространённых ситуаций использования ВПР – сверка двух отчётов (по остаткам, долгам, за разные периоды и т.п.). Суть задачи – перенести данные из старого отчёта в новый и проверить, что изменилось.

Рисунок 10

Функция ВПР вписана в ячейку C2 листа Новый и далее скопирована до конца таблицы. Будьте аккуратны при заполнении поля Таблица – нужно поставить в это поле курсор, перейти на лист Старый щелчком по его названии в нижней части файла, выделить столбцы B:C и не уходя с листа Старый переставить курсов в поле Номер_столбца – программа сама вернёт Вас на стартовый лист Новый.

По строке Лиса возникает ошибка #Н/Д – в старом отчёте действительно нет такого зверя, то есть мы видим новые позиции, с которыми ранее компания не работала. Эту ошибку можно отфильтровать и удалить, чтобы она не мешала посчитать общую сумму в ячейке C10, а можно вложить ВПР в функцию ЕСЛИОШИБКА (подробнее в соответствующем уроке) и вывести вместо стандартного сообщения об ошибке свой текст или число ноль, как на скрине ниже.

Рисунок 11

В ячейке C10 показан результат 885, хотя по старому отчёту зверей должно было быть 900 – некоторые позиции исчезли и мы об этом можем узнать, только если в старом отчёте тоже впишем ВПР в какой-нибудь столбец и отфильтруем ошибки #Н/Д. Добавлять недостающие строки в новом отчёте и копировать туда данные исчезнувших позиций нужно будет вручную, стандартные инструменты не сумеют этого сделать, но быстро провести сверку и подготовить таблицы для анализа ещё как помогают! Далее на листе Новый можно посчитать разницу между колонками B и C и т.д.
Кстати, в теме Условное форматирование можно узнать, как без функции ВПР выявить отличия между таблицами, или наоборот – найти совпадающие значения. В соответствующем уроке есть такой пример, это на удивление просто :)

Рисунок 12

Вероятно, Вы заметили, что в поле Искомое_значение указана ссылка на столбец B вместо ссылки на одну ячейку. Да, так тоже можно делать, но в любом случае функция ВПР для расчёта результата в одной ячейке может использовать только одно название и возьмёт его из той же строки, в которой сама записана – это логично и видно справа от поля Искомое_значение.
Я использовала здесь такой приём для того, чтобы на одном скрине показать и отфильтрованную таблицу с окончательным результатом, и формулу для ячейки A2, которую сейчас не видно, но она точно такая же, как и в A4.

 

ИНТЕРВАЛЬНЫЙ ПРОСМОТР

Примеры использования этого полезнейшего параметра функции ВПР разберём на примере файла [VLookup 3 range_lookup], лист Доставка. Здесь представлена таблица с планом отгрузок по дням, в колонке B указано плановое количество коробок, которое нужно отвезти в каждый из дней. В столбце C – Транспорт нужно вписать название транспорта, который может выполнить такой объём доставки. Правее приведена таблица с нормами загрузки, по которой легко определить, что 54 коробки (ячейка B2) можно отвезти на газели, 102 коробки (ячейка B3) – на фургоне с прицепом и т.д. Для автоматизации подобных задач очень часто используют сложные конструкции вложенных ЕСЛИ, но можно и проще – ведь первый столбец таблиц с нормами загрузки представляет из себя числовые интервалы.

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

Рисунок 13

Таблицу я выделяла не по столбцам, а как группу ячеек и фиксировала её знаками долларов, для чего нажала F4. Дело в том, что функция ВПР будет проверять любое число первого выделенного столбца E, поэтому во избежание неприятностей лучше ограничить таблицу только нужной группой ячеек $E$3:$F$6.
В поле Интервальный_просмотр можно было вписать 1 или ИСТИНА, это равнозначные записи и означают использование логики интервальной оценки первого столбца выделенной таблицы.
Попробуйте вписать в этом примере в поле Интервальный_просмотр0 или слово ЛОЖЬ и результатов не будет, так как точного соответствия значений из ячеек B2:B16 и первого столбца таблицы данных E3:E6 в самом деле нет. Можно найти только интервал, которому принадлежит искомое число.

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

Рисунок 14

 

ГПР – ГОРИЗОНТАЛЬНЫЙ ПРОСМОТР

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

В файле [VLookup 4 Fishes.xlsx] на листе Рыбки в ячейках A1:A4 указаны названия компаний, по которым в ячейках B1:B5 нужно проставить объём продаж рыбок Гуппи. Найти один раз строку с рыбками Гуппи несложно – она 13 на листе. А вот каждый раз искать столбец с названием фирмы не хочется. Для таких случаев и пригодится ГПР.

Рисунок 15

Таблицу начинаем выделять с той строки, по которой будем искать название фирмы,
а в поле Номер_строки и нужно вписать номер строки с рыбками Гуппи, но нельзя использовать её номер на листе, нужно посчитать её порядковый номер в выделенной таблице, то есть начиная с 9-ой строки (с первой выделенной) и получается 5.

 

ОШИБКИ ФУНКЦИИ ВПР

#Н/Д – в таблице нет строки с таким названием. Может означать отсутствие данных или ошибку в названии (пробелы, опечатки и пр. неприятности при ручном создании записей)

#ЗНАЧ – в названии больше 255 символов. Никому не пожелаю подобного монстра. У меня не было таких таблиц, но у коллег бывало… Приходится использовать ЛЕВСИМВ, чтобы оставить и в искомом значении, и в первом столбце таблицы по 255 знаков для каждого названия.

#ССЫЛКА – номер столбца превышает общее количество колонок выделенной таблицы. Как на видео, например, таблицу выделили в 2 столбца, а данные хотим их 3-го.

#ИМЯ – неверно указано название функции или ссылка на ячейку (если писать ссылки от руки, то запросто можно перепутать и написать русские буквы для обозначения столбцов), либо в поле искомое_значение вписан текстовый запрос без кавычек; в таком случае EXCEL пытается найти у себя команду или ячейку с таким названием и сообщает, что Вы указали незнакомое ему имя.

Похожие материалы

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

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

Консолидация данных, операции с группой листов
Консолидация
Обычная СУММ и команда Консолидация позволяют получить итоговый отчёт на основании нескольких исходных таблиц данных. Они могут быть идентичными ли отличаться друг от друга.

Относительные и абсолютные ссылки в формулах
Типы ссылок
Основные правила составления формул в таблицах Excel. Логика копирования и переноса формул. Зачем ячейке нужны знаки доллара $ и как их поставить.

Заказчики

Я сотрудничаю с многими известными компаниями. Вот некоторые из них:

ВТБ МегаФон Глобус Абсолют

Связаться с автором

Оставьте ваше имя и адрес электронной почты, в ближайшее время я свяжусь
с вами для консультации и отвечу на вопросы!



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