Как использовать функцию ВПР в Excel
. Пошаговая инструкция
РБК Life объясняет, где может понадобиться функция ВПР и как ее правильно применять
ВПР (Vlookup, вертикальный просмотр) — функция в Excel, которую используют при работе с большим объемом данных. Она помогает найти нужное значение в определенном диапазоне ячеек или объединить данные из двух таблиц [1]. РБК Life объясняет, где может понадобиться функция ВПР и как ее правильно применять.
Как использовать ВПР в Excel: поиск данных
При помощи ВПР можно быстро отыскать необходимое значение в большой таблице в Excel. Например, в таблице с расписанием занятий администратору фитнес-клуба нужно найти, в какой день недели проходит обучение плаванию. Функция ВПР проведет поиск по указанным параметрам.
- Откройте файл Excel с таблицей, в которой будете проводить поиск значения. Кликните на любую пустую ячейку рядом с таблицей. Разверните вкладку «Формулы» в верхней части экрана и выберите «Вставить функцию».
- В поисковой строке открывшегося окна введите название «ВПР», нажмите кнопку «Найти». Левой кнопкой мыши выберите функцию из предложенного списка и кликните «ОК».
- Появится окно для ввода аргументов функции, их всего четыре. В поле «Искомое значение» введите текст, по которому будете вести поиск. В данном случае это содержимое ячейки А21 — «Школа плавания». Далее нужно указать диапазон поиска. Для этого поставьте курсор мыши в строку «Таблица», а затем, кликнув по ячейке таблицы и зажав левую кнопку мыши, выделите всю таблицу или ее часть. В графу «Номер столбца» введите значение того столбца, в котором будете вести поиск. Нумерацию ведут от начала выделенной области. В образце указана цифра 2, соответствующая столбцу «День недели». Четвертую строчку заполнять необязательно. При желании напишите в графе «Интервальный просмотр» слово «ЛОЖЬ» для точного поиска или «ИСТИНА» — для обнаружения приблизительных значений. Когда введете все аргументы, под ними появится искомый параметр. Функция проведет поиск по выделенному диапазону ячеек сверху вниз и, дойдя до указанного в первой графе параметра, скопирует значение из соседнего столбца в этой же строке. В данной таблице задача будет выполнена: ВПР найдет нужный день недели, и под критериями поиска появится слово «среда». Чтобы результат отобразился в выделенной ячейке на листе Excel, нажмите «ОК».
- Если нужно изменить критерии поиска, щелкните по ячейке с результатом и выберите «Вставить функцию», чтобы снова открыть окно для ввода аргументов.
Как найти данные в таблице при помощи функции ВПР
ВПР в Excel по двум условиям: как найти данные
В некоторых случаях одного параметра поиска может не хватить для корректной работы функции. Например, требуется узнать, в какой день недели в фитнес-центре занятие по растяжке проходит в 15:00. Если в окне аргументов функции ввести данные по такому же принципу, как и при обычном поиске, ВПР найдет первое сверху слово «растяжка» и даст неверный ответ «понедельник», хотя тренировка в этот день проходит в 10:00. Добавить дополнительный аргумент для поиска нельзя, поэтому придется вручную редактировать параметры.
- Откройте файл с таблицей. Во вкладке «Формулы» нажмите кнопку «Вставить функцию».
- Введите слово «ВПР» в поисковую строку развернувшегося окна, нажмите «Найти». Выберите функцию из списка и кликните «ОК».
- Заполните поля с аргументами функции так же, как при простом поиске по одному критерию.
- Отредактируйте графу «Таблица». Возьмите указанный ранее диапазон в скобки. Перед ними напишите слово «ЕСЛИ». Сразу после открывающей скобки впишите второй параметр с указанием столбца и точного значения, поставьте точку с запятой. Например, «D: D=«15.00»;» где «D: D» — выбранный столбец D, а «15.00» в кавычках — дополнительный критерий для поиска из этого столбца. В данном примере первоначальный параметр в графе «Таблица» выглядел как «Таблица1[#Все]», а после добавления второго параметра стал таким: «ЕСЛИ (D: D=«15.00»; Таблица1[#Все])». Кликните «ОК».
- Функция ВПР верно определит запрашиваемое значение. В образце это «вторник».
Как найти данные в таблице по двум параметрам с помощью ВПР
Как в Excel объединить данные из нескольких таблиц при помощи ВПР
Иногда, чтобы объединить данные из двух разных файлов Excel, нельзя просто скопировать и вставить нужный столбец, поскольку данные могут располагаться вразнобой и строки в таблицах не совпадают. Например, в файле Excel создали два листа с таблицами. В первой — расписание занятий фитнес-клуба, а во второй — расписание работы тренеров. В таблицах есть общие столбцы с названием тренировки. Для объединения данных нужно добавить в первую таблицу столбец с фамилиями тренеров, но очередность строк в таблицах разная. Скопировать и вставить столбец нельзя, а искать и вводить данные вручную — долго и трудозатратно. Решить вопрос можно с помощью функции ВПР.
- Откройте файл с таблицами для объединения данных. Левой кнопкой мыши выделите соседний с первой таблицей столбец. Во вкладке «Вставка» выберите «Таблица». В появившемся окне поставьте галочку в окошке рядом со строчкой «Таблица с заголовками» и нажмите «ОК». У таблицы появится дополнительный столбец для новых данных. Отредактируйте название столбца.
- Левой кнопкой мыши щелкните по верхней пустой ячейке в новом столбце. Во вкладке «Формулы» нажмите «Вставить функцию».
- В поисковую строку открывшегося окна введите «ВПР», нажмите «Найти». Выберите функцию из перечня, кликните «ОК».
- Поставьте курсор в графу «Искомое значение» и левой кнопкой мыши кликните по первой ячейке в столбце, который присутствует в обеих таблицах. В данном случае это ячейка 2А — «Пилатес ур. 1» в столбце «Тренировка».
- Установите курсор в поле «Таблица» и перейдите во вкладку со второй таблицей. Мышью выделите диапазон ячеек, по которому будет вестись поиск. В образце выделена вся таблица.
- В поле «Номер столбца» введите порядковый номер того столбца выделенного диапазона второй таблицы, откуда будете переносить данные. В примере это значения из столбца с фамилиями тренеров, поэтому в строку ввели цифру 1.
- Вернитесь во вкладку с первой таблицей и нажмите «ОК».
- Нужное значение появится в первой строке нового столбца. Чтобы заполнить остальные строки, наведите курсор на нижний правый угол ячейки с найденными данными и, зажав левую кнопку мыши, растяните формулу до нижней ячейки столбца.
- Столбец заполнили нужными значениями. Для внесения правок в формулу при необходимости кликните по одной из ячеек столбца мышью и нажмите кнопку «Вставить функцию» во вкладке «Формулы». Откроется окно ввода аргументов, и вы сможете ввести корректные значения.
Как объединить данные в таблицах при помощи ВПР
Функцией ВПР можно также воспользоваться в Google-таблицах. Неудобство состоит в том, что в сервисе нельзя открыть окно для ввода аргументов функции и формулу необходимо вводить вручную.
