Toderelt.ru

ПК Журнал ТодерельТ
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Спасаемся от рутинной работы посредством функции ВПР в Excel

Спасаемся от рутинной работы посредством функции ВПР в Excel

Наверняка многим активным пользователям табличного редактора Excel периодически приходилось сталкиваться с ситуациями, в которых возникала необходимость подставить значения из одной таблицы в другую. Вот представьте, на ваш склад зашёл некий товар. В нашем распоряжении имеется два файла: один с перечнем наименований полученного товара, второй — прайс-лист этого самого товара. Открыв прайс-лист, мы обнаруживаем, что позиций в нём больше и расположены они не в той последовательности, что в файле с перечнем наименований. Вряд ли кому-то из нас понравится идея сверить оба файла и перенести цены из одного документа в другой вручную. Разумеется, в случае, когда речь идёт о 5–10 позициях, механическое внесение данных вполне возможно, но что делать, если число наименований переваливает за 1000? В таком случае справиться с монотонной работой нам поможет Excel и его волшебная функция ВПР (или vlookup, если речь идёт об англоязычной версии программы).

Функция VLOOKUP в Excel

Как создать функцию ВПР в Excel

Необходимая последовательность значений в функции называется синтаксис. Обычно функция начинается с символа равенства «=», затем идет название функции и аргументы в скобках.

Записываем формулу в столбик цены (С2). Это можно сделать двумя способами:

Выделить ячейку и вписать функцию.

Выделить ячейку → нажать на Fx (Shift +F3) → выбрать категорию «Ссылки и массивы» → выбрать функцию ВПР → нажать «ОК».

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

аргументы функции ВПР

Синтаксис функции ВПР выглядит так:

В нашем случае получится такая формула:

Аргументы ВПР в Эксель

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

Сейчас разберемся что и куда писать.

Со знаком равенства «=» и названием «ВПР» все понятно. Поговорим об аргументах. Они записываются в скобках через точку с запятой или заполняются в ячейки в окне функции. Формула ВПР имеет 4 аргумента: искомое значение, таблица, номер столбца и интервальный просмотр.

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

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

Функция ВПР, искомое значение

Таблица – это диапазон ячеек, из которых мы будем «подтягивать» данные для искомого значения. В этом аргументе используем абсолютные ссылки. Это значит, что в формуле таблица будет выглядеть как «$G$2:$H$11» вместо «G2:H11». Знаки «

ВПР и приблизительный интервальный просмотр

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

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

Задача для Excel, ВПР

Решение. Заполняем формулу ВПР в ячейке «Партия», как было показано в предыдущем примере.

Окно для формулы ВПР в Excel

Разница в том, что теперь искомое значение – число, а интервальный просмотр – истина, что означает приблизительный поиск. Получается вот такой результат:

Сортировка в Excel с помощью ВПР

Что произошло? Аргумент «интервальный просмотр» имеет значение 1. Это значит, что формула ВПР ищет в таблице ближайшее меньшее искомое значение.

В нашем случае количество товара «Кофе» – 380. ВПР берет это число в виде искомого значения, после чего ищет ближайшее меньшее в соседней таблице – число 300. В конце функция «подтягивает» данные из столбца напротив («Крупная»). Если количество товара «Кофе» = 340 – это «Крупная партия». Важно, чтобы крайний левый столбец таблицы, которая указана в формуле, был отсортирован по возрастанию. В противном случае ВПР не сработает.

Некорректная работа функции ВПР

raquo; можно поставить вручную, а можно выделить «G2:H11» внутри формулы и нажать F4. Если этого не сделать, таблица не зафиксируется в формуле и изменится при копировании.

В нашем случае – это таблица с прайсом. Формула будет искать в ней совпадение с ячейкой, которую указали в первом аргументе формулы – A2 (Кофе). Нажимаем F4 и делаем ссылку абсолютной.

Читать еще:  Как узнать, какие драйвера нужны на компьютер

Функция ВПР, таблица

Номер столбца – это столбец таблицы, из которой нужно взять данные. Именно из него мы будем «подтягивать» результат.

Формула сканирует таблицу по вертикали.

Находит в самом левом столбце совпадение с искомым значением.

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

Передает данные в ячейку с формулой.

В нашем случае – это столбец с ценой продуктов в прайсе. Формула ищет искомое значение ячейки A2 (Кофе) в первом столбце прайса и «подтягивает» данные из второго столбца (потому что мы указали цифру 2) в ячейку с формулой.

Функция ВПР, принцип работы

Интервальный просмотр – это параметр, который может принимать 2 значения: «истина» или «ложь». Истина обозначается в формуле цифрой 1 и означает приблизительное совпадение с искомым значением. Ложь обозначается цифрой 0 и подразумевает точное совпадение. Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.

В нашем случае искомое значение – это текстовое наименование. Поэтому используем точный поиск – ставим цифру 0 и закрываем скобку.

Использование функции ВЫБОР для создания новой таблицы просмотра

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

Использование функции ВЫБОР подразумевает создание новой таблицы для просмотра, в котором значения столбцов Месяц и Город уже объединены. Наша формула будет выглядеть следующим образом:

Основной момент данной формулы заключается в части ВЫБОР(<1;2>;B2:B13&C2:C13;D2:D13), который делает две вещи:

  1. Объединяет значения столбцов Месяц и Город в один массив: ЯнвМосква, ФевМосква …
  2. Объединяет два массива в таблицу, состоящую из двух столбцов.

Результатом работы данной функции будет таблица, которая выглядит следующим образом:

Массив-для-просмотра

Теперь формула стала более понятной.

ВАЖНО: Так как мы использовали формулу массива, по окончании ввода формулы нажмите Ctrl+Shift+Enter, чтобы дать знать программе о наших намерениях. После нажатия данной комбинации клавиш, программа автоматически установит фигурные скобки в начале и в конце формулы.

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

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

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

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

Для сортировки первого столбца следует:

  • выделить фамилии-имена первого столбца от Морозова до Петровой (визуальное отсутствие выделения фамилии Морозов – кажущееся);
  • в контекстном меню выделенного первого столбца установить курсор на строку «Сортировка»;
  • в её выпадающем списке щёлкнуть пункт «Сортировка от А до Я».

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

В следующем окне программа Excel предлагает пользователю «умную» сортировку». Иными словами, будет произведена сортировка не только первого левого столбца, но и связанных с ним остальных трёх. Очевидно, что если этого не сделать, то список сотрудников будет абсурдным. Поэтому в этом окне, ничего не меняя, следует щёлкнуть пункт «Сортировка».

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

ВПР пример 1

  1. После создания отсортированного учебного файла щёлкнуть любую свободную ячейку (например, F9): в ней будет возвращён результат.
  1. На строке формул щёлкнуть fx.

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

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

Читать еще:  Как пронумеровать страницы Word

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

4. Выделить функцию ВПР и кликнуть OK.

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

5. В появившемся окне «Аргументы функции» обратите внимание на рекомендацию, подчёркнутую синим цветом. Её игнорирование делает выдаваемые функцией результаты непредсказуемыми. Правильной будет только часть результатов, поэтому перед началом работы сортировка ключевого столбца обязательна.

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

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

6. Как видно, заданное или ключевое значение в окне аргументов именуется «Искомым». Такое название не кажется мне удачным: оно задаётся изначально, а не ищется, и используется как ключ к поиску. Предположим, что нас интересует зарплата, получаемая сотрудницей Ириной Соколовой. Самый простой способ задать верхний параметр – щёлкнуть на ячейке A8, после чего – кликнуть на строке параметра «Таблица».

7. В качестве параметра «Таблица» выделяется весь заполненный участок Excel, исключая заголовки столбцов. А на строке «Номер столбца» следует ввести номер столбца, содержащего список зарплат, т. е. 2. Как видно, правильное значение зарплаты сотрудницы Ирины Соколовой появляется прямо в окне аргументов.

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

8. После клика на OK возвращённое функцией значение появится и в ячейке, зарезервированной для результата.

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

ВПР пример 2

Естественно, что аналогичным образом могут быть извлечены данные из других информационных столбцов. Предположим, что нам нужно узнать дату рождения всё той же Ирины Соколовой. Аргументы функции ВПР претерпят единственное изменение на строке «Номер столбца». Как видно, функция возвращает, на первый взгляд, непонятное число 29025.

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

После щелчка на ОК оно появится и в ячейке результата.

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

Опытные пользователи Excel знают, что в этой программе даты (и время) хранятся в числовом формате, начиная с условно принятой даты 0 января 1900 года. Для отображения полученного значения в привычном человеку виде достаточно придать ячейке результата формат даты. Для этого сначала открыть её контекстное меню, и щёлкнуть пункт «Формат ячеек».

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

В появившемся одноимённом окне:

  • щёлкнуть пункт «Дата»;
  • в правом списке при необходимости поменять вариант отображения;
  • щёлкнуть OK.

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

После этого отображаемая в ячейке результата дата примет «человеческий» вид.

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

ВПР пример 3

Этот пример отличается от первого двумя аргументами – первым и заполненным последним. Как видно, в первом аргументе вместо имени использован подстановочный знак звёздочки *. Если не заполнять последний аргумент, то возвращаемое функцией значение будет ошибочным. Ввод логического значения ЛОЖЬ сделает результат правильным.

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

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

4. Как сделать двусторонний поиск с ВПР

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

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

Но с помощью функции ПОИСКПОЗ можно сделать номер столбца также изменяемым параметром.

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

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

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

=ВПР (искомое значение1; таблица; ПОИСКПОЗ (искомое значение2, столбец, 0), 0)

Функция ПОИСКПОЗ сопоставляет требуемое значение из указанного диапазона и возвращает ссылку на нужный столбец.

В нашем случае формула примет вид:

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

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

В качестве альтернативы можно использовать функции ИНДЕКС и ПОИСКПОЗ:

=ИНДЕКС(B3:H7; ПОИСКПОЗ(K2;B3:B7;0); ПОИСКПОЗ(K3;B2:H2;0))

Функция впр в excel

Для удобства просмотра я перенес Таблицу №2 на первый лист. Все совпало, протягиваем или копируем формулу в ячейки D5 и D6, Таблице №1 добавляем название столбца- Цена товара. Вот так с помощью функции впр мы совместили данные из двух таблиц, находящихся на разных листах, хотя можно и на одном листе и даже в разных книгах.

Читать еще:  Где и как загрузить бесплатно плагин adobe flash player?

ВПР в Excel

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

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

Рекомендуем смотреть видео в полноэкранном режиме, в настойках качества выбирайте 1080 HD, не забывайте подписываться на канал в YouTube, там Вы найдете много интересного видео, которое выходит достаточно часто. Приятного просмотра!

Функция ВПР — это один из самых полезных компьютерных трюков не только в Excel. Кто узнал о нем, тот без нее жить не может, серьезно. Итак, предположим, что у нас есть две таблицы с текстом. Нужно значения одного списка (Фамилии) передать в ячейки другого, в зависимости от текста-условий (Номера ТС). Если конкретнее пример ниже:

ВПР

Задача1. В одном файле хранится список ФИО сотрудников и транспортных средств (Таблица1). В Таблице2 для некоторых автомобилей заполнены номера накладных. Причем таблицы не совпадают по количеству строк. Цель. Для каждой строки Таблицы2 заполнить ФИО сотрудников. Для этого как раз пригодится функция ВПР.

Функция ВПР. Решение задачи

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

Функция ВПР ищет значение в левом столбце Таблицы1 и возвращает (записывает) значение ячейки, находящейся в столбце Таблицы2 под определенным номером, той же строки. Во как 🙂 Но на самом деле все проще. Для этого нам нужно понять из чего состоит ВПР

Переменные. Функция ВПР

  • Искомое_значение — то самое значение, которое мы ищем в левом столбце Таблицы1. Номер ТС в Таблице2.
  • Таблица — все столбцы Таблицы1, причем первый столбец должен быть, который мы ищем (Номер ТС)
  • Номер_столбца — номер столбца в Таблице1, из которого возвращаем значения (ФИО)
  • [интервальный_просмотр] — может принимать только два значения — Ложь или Истина: Ложь – ищет точное совпадение, Истина – приблизительное. В 95% случаев требуется искать точное значение, т.е. выбирать ЛОЖЬ.

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

Итак, напишем функцию для нашей задачи:

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

E:E — это диапазон значений, по которым Excel будет сравнивать условия с Таблицей 1. A:B — вся Таблица1 (обязательно, чтобы первым столбцом был столбец для поиска условий). Число 2 это тот по счету столбец в Таблице 1, который мы будем переносить в ячейку F1. ЛОЖЬ — смотрите выше.

Я тысячу раз слышал как люди говорят: «Давайте заВПРим это» или «ну тут можно ВПРом сделать», и это отлично! Значит люди экономят время, зная о простых и действенных методах. Не отставайте!

Не забудьте растащить функцию до конца диапазона. Не знаете как это сделать быстро? Читайте тут.

А как можно сделать ту же функцию с удобными названными диапазонами (на картинке ниже)? Нравится? Читайте тут.

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

Комментарии по ВПР

  • Если для одной ячейки в Таблице2 есть несколько значений, в Таблице1 будет выдаваться первое значение.
  • Если совпадающих данных нет, будет возвращаться ошибка «не найдено» (Н/Д#). Чтобы ее не отображать, используйте формулы =ЕСЛИОШИБКА()
  • Чтобы определить какой именно номер столбца записать в формулу, при выделении диапазона показывается, номер столбца.

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

  • Чтобы посчитать сумму для нескольких значений из одной таблицы в другой, воспользуйтесь СУММЕСЛИ

Что в итоге:

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector