VLOOKUP - одна из самых полезных функций Excel, и она также является одной из наименее понятных. В этой статье мы демистифицируем VLOOKUP на примере реальной жизни. Мы создадим пригодный для использования Шаблон счета-фактуры для фиктивной компании.
VLOOKUP - это Excel функция, В этой статье предполагается, что читатель уже имеет понимание функций Excel и может использовать базовые функции, такие как SUM, AVERAGE и TODAY. В своем наиболее распространенном использовании VLOOKUP является база данных функция, означающая, что она работает с таблицами базы данных - или, проще говоря, списки вещей на листе Excel. Какие вещи? Что ж, любой Такие вещи. У вас может быть рабочий лист, содержащий список сотрудников, продуктов или клиентов, или компакт-диски в вашей коллекции компакт-дисков, или звезды в ночном небе. Это не имеет большого значения.
Вот пример списка или базы данных. В этом случае это список продуктов, которые продается нашей фиктивной компанией:
Обычно такие списки имеют свой уникальный идентификатор для каждого элемента в списке. В этом случае уникальный идентификатор находится в столбце «Код товара». Примечание. Чтобы функция VLOOKUP работала с базой данных / списком, этот список должен иметь столбец, содержащий уникальный идентификатор (или «ключ» или «идентификатор»), и этот столбец должен быть первым столбцом в таблице, Наша база данных примеров выше удовлетворяет этому критерию.
Самая сложная часть использования VLOOKUP - это то, для чего именно. Итак, давайте посмотрим, сможем ли мы сначала понять это:
VLOOKUP retrieves information from a database/list based on a supplied instance of the unique identifier.
В приведенном выше примере вы должны вставить функцию VLOOKUP в другую таблицу с кодом элемента, и она вернет вам либо описание соответствующего элемента, его цену, либо ее доступность (количество «на складе»), как описано в вашем оригинале список. Какую из этих частей информации он передаст вам? Ну, вы должны решить это, когда создаете формулу.
Если вам нужна только одна часть информации из базы данных, было бы очень сложно найти формулу с функцией VLOOKUP в ней. Как правило, вы будете использовать эту функцию в таблице многократного использования, например в шаблоне. Каждый раз, когда кто-то вводит действительный код товара, система будет извлекать всю необходимую информацию о соответствующем элементе.
Давайте создадим пример этого: Шаблон счета-фактуры что мы можем повторно использовать много раз в нашей фиктивной компании.
Сначала мы запускаем Excel, и мы создаем себе пустой счет:
Вот как это будет работать: лицо, использующее шаблон счета-фактуры, заполнит серию кодов товаров в столбце «А», и система будет извлекать описание и цену каждого элемента из нашей базы данных продукта. Эта информация будет использоваться для расчета общей суммы для каждого элемента (при условии ввода правильного количества).
Для удобства простого примера мы найдем базу данных продукта на отдельном листе в той же книге:
Итак, мы создали нашу базу данных продуктов, которая выглядит так:
Нам нужно найти список всех доступных функций, которые предлагает Excel, чтобы мы могли выбрать VLOOKUP и получить помощь в заполнении формулы. Это можно найти, нажав сначала Формулы и нажмите Вставить функцию:
Чтобы найти тот, который мы ищем, мы можем ввести поисковый запрос, например «lookup» (потому что функция, которую мы интересуем, является уважать функция). Система вернет нам список всех связанных с поиском функций в Excel. ВПР является вторым в списке. Выберите его щелчок Хорошо.
Аргументы функции появится окно с запросом аргументы (или же параметры), необходимые для завершения функции VLOOKUP. Вы можете подумать об этом окне как о функции, задающей нам следующие вопросы:
- Какой уникальный идентификатор вы ищете в базе данных?
- Где находится база данных?
- Какую часть информации из базы данных, связанную с уникальным идентификатором, вы хотите получить для вас?
Показаны первые три аргумента жирным шрифтом, указывая, что они обязательный аргументы (функция VLOOKUP незавершенна без них и не вернет действительное значение). Четвертый аргумент не выделен жирным шрифтом, что означает, что он необязателен:
Первый аргумент, который нам нужно выполнить, - это lookup_Value аргумент. Функция требует от нас указать, где найти уникальный идентификатор ( код изделия в этом случае), что он должен возвращать описание. Мы должны выбрать код товара, который мы ввели ранее (в A11).
Нажмите на значок селектора справа от первого аргумента:
Затем нажмите один раз на ячейку, содержащую код элемента (A11), и нажмите Войти:
Теперь нам нужно ввести значение для таблица_массив аргумент. Другими словами, нам нужно сообщить VLOOKUP, где найти базу данных / список. Нажмите на значок селектора рядом со вторым аргументом:
… и нажмите Войти, Диапазон ячеек, представляющих базу данных (в этом случае «База данных продуктов»! A2: D7 ») вводится автоматически для нас во второй аргумент.
Теперь нам нужно ввести третий аргумент, Col_Index_Num, Мы используем этот аргумент, чтобы указать VLOOKUP, какая часть информации из базы данных, связанная с нашим кодом товара в A11, мы хотим вернуть нам. В этом конкретном примере мы хотим, чтобы описание вернулся к нам. Если вы посмотрите на листе базы данных, вы заметите, что столбец «Описание» - это второй столбца в базе данных. Это означает, что мы должны ввести значение «2» в Col_Index_Num коробка:
Важно отметить, что мы не вводим здесь «2», потому что столбец «Описание» находится в В столбец на этом листе. Если база данных возникла в столбце К на рабочем листе мы все равно будем вводить «2» в этом поле, потому что столбец «Описание» является вторым столбцом в наборе ячеек, которые мы выбрали при указании «Table_array».
Наконец, нам нужно решить, следует ли вводить значение в конечный аргумент VLOOKUP, диапазон_просмотра, Этот аргумент требует либо правда или же ложный значение, или оно должно быть пустым. При использовании VLOOKUP с базами данных (как и 90% времени) способ решить, что добавить в этот аргумент, можно представить следующим образом:
If the first column of the database (the column that contains the unique identifiers) is sorted alphabetically/numerically in ascending order, then it’s possible to enter a value of true into this argument, or leave it blank.
If the first column of the database is not sorted, or it’s sorted in descending order, then you must enter a value of false into this argument
Поскольку первый столбец нашей базы данных не отсортированы, мы вводим ложный в этот аргумент:
Это оно! Мы ввели всю информацию, необходимую для VLOOKUP, чтобы вернуть нужное нам значение. Нажмите Хорошо и обратите внимание, что описание, соответствующее коду товара «R99245», было правильно введено в ячейку B11:
Если мы введем разные код элемента в ячейку A11, мы начнем видеть силу функции VLOOKUP: ячейка описания изменяется в соответствии с новым кодом элемента:
Мы можем выполнить аналогичный набор шагов, чтобы получить цена возвращается в ячейку E11. Обратите внимание, что новая формула должна быть создана в ячейке E11. Результат будет выглядеть так:
Обратите внимание, что единственная разница между двумя формулами - это третий аргумент (Col_Index_Num) изменилось с «2» на «3» (потому что нам нужны данные, полученные из третьего столбца в базе данных).
Если мы решили купить 2 из этих предметов, мы должны ввести «2» в ячейку D11. Затем мы вводим простую формулу в ячейку F11, чтобы получить общую сумму:
=D11*E11
… который выглядит так …
Заполнение шаблона счета
До сих пор мы много узнали о VLOOKUP. Фактически, мы узнали все, что мы узнаем в этой статье. Важно отметить, что VLOOKUP можно использовать в других обстоятельствах помимо баз данных. Это менее распространено и может быть рассмотрено в будущих статьях How-To Geek.
Наш шаблон счета еще не завершен. Чтобы завершить его, мы сделаем следующее:
-
Мы удалили бы код элемента образца из ячейки A11 и «2» из ячейки D11. Это заставит наши недавно созданные формулы VLOOKUP отображать сообщения об ошибках:
Image Мы можем исправить это путем разумного использования Excel ЕСЛИ() а также ЕПУСТО () функции. Из этой формулы мы меняем формулу … = VLOOKUP (A11, «База данных продуктов»! A2: D7,2, FALSE) …к этому… = IF (ISBLANK (A11), "", VLOOKUP (A11, 'База данных продуктов'! A2: D7,2, FALSE))
- Мы скопировали формулы в ячейках B11, E11 и F11 до остальной части строк позиции счета-фактуры. Обратите внимание: если мы это сделаем, полученные формулы больше не будут правильно ссылаться на таблицу базы данных. Мы могли бы исправить это, изменив ссылки на ячейки для базы данных на абсолютный ссылки на ячейки. Альтернативно - и даже лучше - мы могли бы создать название диапазона для всей базы данных продукта (например, «Продукты») и используйте это имя диапазона вместо ссылок на ячейки. Формула изменилась бы от этого … = IF (ISBLANK (A11), "", VLOOKUP (A11, 'База данных продуктов'! A2: D7,2, FALSE)) …к этому… = ЕСЛИ (ЕПУСТО (А11),»», ВПР (А11, продукты, 2, FALSE)) …а также затем скопируйте формулы до остальных строк позиций счета.
- Мы, вероятно, «заблокировали» ячейки, содержащие наши формулы (точнее отпереть Другой ячейки), а затем защитите рабочий лист, чтобы гарантировать, что наши тщательно сконструированные формулы не будут случайно перезаписаны, когда кто-то придет, чтобы заполнить счет-фактуру.
- Мы сохранили бы файл как шаблон, чтобы его можно было повторно использовать всеми в нашей компании
Если бы мы чувствовали действительно умный, мы создали бы базу данных всех наших клиентов на другом листе, а затем с помощью идентификатора клиента, введенного в ячейку F5, автоматически заполнили имя и адрес клиента в ячейках B6, B7 и B8.
