VLOOKUP – это одна из самых полезных функций Excel, а также одна из наименее понятных. В этой статье мы демистифицируем VLOOKUP на примере из реальной жизни. Мы создадим Шаблон счета-фактуры для вымышленной компании.
VLOOKUP – это функция Excel . В этой статье предполагается, что читатель уже имеет некоторое представление о функциях Excel и может использовать основные функции, такие как SUM, AVERAGE и TODAY. В своем наиболее распространенном использовании VLOOKUP – это функция database , что означает, что она работает с таблицами базы данных – или, проще говоря, списками вещей на листе Excel. Что за вещи? Ну, любая вещь. У вас может быть рабочий лист со списком сотрудников, продуктов, клиентов или компакт-дисков в вашей коллекции компакт-дисков или звезд на ночном небе. Это не имеет значения.
Вот пример списка или базы данных. В данном случае это список продуктов, которые продает наша фиктивная компания:
Обычно списки, подобные этому, имеют своего рода уникальный идентификатор для каждого элемента в списке. В этом случае уникальный идентификатор находится в столбце «Код товара». Примечание. Чтобы функция VLOOKUP работала с базой данных/списком, этот список должен иметь столбец, содержащий уникальный идентификатор (или «ключ», или «идентификатор»), и этот столбец должен быть первым столбцом в таблице. . Приведенная выше примерная база данных удовлетворяет этому критерию.
Самая сложная часть использования VLOOKUP – это понимание того, для чего он предназначен. Итак, давайте посмотрим, сможем ли мы сначала получить это ясно:
VLOOKUP извлекает информацию из базы данных/списка на основе предоставленного экземпляра уникального идентификатора.
В приведенном выше примере вы вставили бы функцию VLOOKUP в другую электронную таблицу с кодом товара, и она вернет вам либо описание соответствующего товара, его цену или его доступность (количество на складе), как описано в вашем оригинале. список. Какую из этих частей информации она передаст вам обратно? Ну, вы должны решить это, когда создаете формулу.
Если все, что вам нужно, это один фрагмент информации из базы данных, то будет очень сложно перейти к построению формулы с функцией VLOOKUP. Как правило, вы будете использовать такую функциональность в многократно используемой электронной таблице, такой как шаблон. Каждый раз, когда кто-то вводит действительный код товара, система извлекает всю необходимую информацию о соответствующем товаре.
Давайте создадим пример этого: Шаблон счета-фактуры , который мы можем использовать снова и снова в нашей фиктивной компании.
Сначала мы запускаем Excel и создаем себе пустой счет:
Вот как это будет работать: лицо, использующее шаблон счета, заполняет серию кодов товаров в столбце «А», и система извлекает описание и цену каждого товара из нашей базы данных продуктов. Эта информация будет использоваться для расчета итоговой суммы для каждой позиции (при условии, что мы введем правильное количество).
В целях упрощения этого примера мы разместим базу данных продуктов на отдельном листе в той же книге:
В действительности, более вероятно, что база данных продукта будет расположена в отдельной рабочей книге. Это не имеет большого значения для функции VLOOKUP, которая на самом деле не заботится, расположена ли база данных на том же листе, на другом листе или в совершенно другой книге.
Итак, мы создали нашу базу данных продуктов, которая выглядит следующим образом:
Чтобы протестировать формулу VLOOKUP, которую мы собираемся написать, мы сначала вводим действительный код товара в ячейку A11 нашего пустого счета:
Затем мы перемещаем активную ячейку в ячейку, в которой мы хотим, чтобы информация, извлеченная из базы данных с помощью VLOOKUP, сохранялась. Интересно, что это шаг, который большинство людей ошибаются. Для дальнейшего объяснения: Мы собираемся создать формулу VLOOKUP, которая будет извлекать описание, соответствующее коду элемента в ячейке A11. Куда мы хотим поместить это описание, когда получим его? В камере В11, конечно. Так вот, где мы пишем формулу VLOOKUP: в ячейке B11. Выберите ячейку B11 сейчас.
Нам нужно найти список всех доступных функций, которые может предложить Excel, чтобы мы могли выбрать VLOOKUP и получить некоторую помощь в заполнении формулы. Для этого сначала нажмите вкладку Формулы , а затем нажмите Вставить функцию :
Появится окно, которое позволяет нам выбрать любую из функций, доступных в Excel.
Чтобы найти искомое, мы могли бы ввести поисковый термин, например «поиск» (потому что интересующая нас функция – это lookup ). Система выдаст нам список всех связанных с поиском функций в Excel. VLOOKUP – второй в списке. Выберите его, нажав ОК .
Появится окно Аргументы функций , предлагающее нам все аргументы (или параметры ), необходимые для завершения функции VLOOKUP. Вы можете думать об этом поле как о функции, задающей нам следующие вопросы:
- Какой уникальный идентификатор вы ищете в базе данных?
- Где база данных?
- Какую часть информации из базы данных, связанную с уникальным идентификатором, вы хотите получить для себя?
Первые три аргумента показаны жирным шрифтом , что указывает на то, что они являются обязательными аргументами (функция VLOOKUP без них неполна и не будет возвращать допустимое значение). Четвертый аргумент не выделен жирным шрифтом, что означает, что он необязательный:
Мы закончим аргументы по порядку, сверху вниз.
Первый аргумент, который нам нужно заполнить, это аргумент Lookup_value . Функция требует, чтобы мы указали ей, где найти уникальный идентификатор (в данном случае код элемента ), который должен возвращать описание. Мы должны выбрать код товара, который мы ввели ранее (в A11).
Нажмите на значок селектора справа от первого аргумента:
Затем нажмите один раз на ячейку, содержащую код товара (A11), и нажмите Enter :
Значение «A11» вставляется в первый аргумент.
Теперь нам нужно ввести значение для аргумента Table_array . Другими словами, нам нужно сообщить VLOOKUP, где найти базу данных/список. Нажмите на значок селектора рядом со вторым аргументом:
Теперь найдите базу данных/список и выберите весь список – не включая строку заголовка. В нашем примере база данных расположена на отдельном листе, поэтому сначала мы нажимаем на вкладку этого листа:
Далее мы выбираем всю базу данных, не включая строку заголовка:
… И нажмите Enter . Диапазон ячеек, представляющих базу данных (в данном случае «База данных продуктов»! A2: D7 ») автоматически вводится для нас во второй аргумент.
Теперь нам нужно ввести третий аргумент, Col_index_num . Мы используем этот аргумент, чтобы указать VLOOKUP, какую часть информации из базы данных, связанную с нашим кодом товара в A11, мы хотели бы вернуть нам. В этом конкретном примере мы хотим, чтобы описание элемента было возвращено нам. Если вы посмотрите на лист базы данных, вы заметите, что столбец «Описание» является столбцом second в базе данных. Это означает, что мы должны ввести значение «2» в поле Col_index_num :
Важно отметить, что здесь мы не вводим «2», потому что столбец «Описание» находится в столбце B на этом листе. Если бы база данных начиналась в столбце K рабочего листа, мы все равно вводили бы «2» в это поле, поскольку столбец «Описание» – это второй столбец в наборе ячеек, который мы выбрали при указании «таблица_массив».
Наконец, нам нужно решить, вводить ли значение в последний аргумент VLOOKUP, Range_lookup . Этот аргумент требует либо значения true , либо false , либо его следует оставить пустым. При использовании VLOOKUP с базами данных (как это верно в 90% случаев), способ решить, что добавить в этот аргумент, можно представить следующим образом:
Если первый столбец базы данных (столбец, содержащий уникальные идентификаторы) отсортирован в алфавитном/числовом порядке в порядке возрастания, то в этот аргумент можно ввести значение true или оставить его пустым.
Если первый столбец базы данных не отсортирован, или он отсортирован по убыванию, то вы должны ввести значение false в этот аргумент
Поскольку первый столбец нашей базы данных не отсортирован, мы вводим false в этот аргумент:
Это оно! Мы ввели всю информацию, необходимую для того, чтобы VLOOKUP вернул нам необходимое нам значение.Нажмите кнопку ОК и обратите внимание, что описание, соответствующее коду товара “R99245”, было правильно введено в ячейку B11:
Формула, которая была создана для нас, выглядит следующим образом:
Если мы введем другой код товара в ячейку A11, мы начнем видеть силу функции VLOOKUP: ячейка описания изменится в соответствии с новым кодом товара:
Мы можем выполнить аналогичный набор шагов, чтобы вернуть цену элемента в ячейку E11. Обратите внимание, что новая формула должна быть создана в ячейке E11. Результат будет выглядеть так:
… и формула будет выглядеть так:
Обратите внимание, что единственная разница между этими двумя формулами заключается в том, что третий аргумент ( Col_index_num ) изменился с «2» на «3» (потому что мы хотим, чтобы данные извлекались из 3-го столбца в базе данных).
Если бы мы решили купить 2 из этих предметов, мы ввели бы «2» в ячейку D11. Затем мы ввели бы простую формулу в ячейку F11, чтобы получить итоговое значение строки:
= D11 * E11
… который выглядит так …
Заполнение шаблона счета
Мы многое узнали о VLOOKUP. Фактически, мы узнали все, что мы собираемся изучить в этой статье. Важно отметить, что VLOOKUP может использоваться и в других случаях, кроме баз данных. Это встречается реже и может быть рассмотрено в будущих статьях TutoryBird.
Наш шаблон счета еще не завершен. Чтобы завершить это, мы сделаем следующее:
-
Мы удалили образец кода элемента из ячейки A11 и «2» из ячейки D11. Это приведет к тому, что наши вновь созданные формулы VLOOKUP будут отображать сообщения об ошибках:
Мы можем исправить это, используя разумные функции Excel IF () и ISBLANK () . Мы изменяем нашу формулу с этого… = 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)) … на это… = IF (ISBLANK (A11), ””, VLOOKUP (A11, Products, 2, FALSE)) … и then копировать формулы вниз в остальные строки элемента счета-фактуры.
- Мы, вероятно, «заблокируем» ячейки, которые содержат наши формулы (или, скорее, разблокируют другие ячейки), а затем защитим лист, чтобы гарантировать, что наши тщательно построенные формулы случайно не перезаписываются, когда кто-то приходит, чтобы заполнить счет.
- Мы сохраним этот файл как шаблон , чтобы его могли использовать все сотрудники нашей компании.
Если бы мы чувствовали себя действительно умными, мы создали бы базу данных всех наших клиентов на другом листе, а затем использовали бы идентификатор клиента, введенный в ячейку F5, для автоматического заполнения имени и адреса клиента в ячейках B6, B7 и B8.
Если вы хотите попрактиковаться с VLOOKUP или просто посмотреть наш полученный Шаблон счета, его можно скачать здесь.
