Вот краткое руководство для тех, кому нужна помощь в использовании функции ВПР в Excel. ВПР – очень полезная функция для удобного поиска по одному или нескольким столбцам в больших таблицах с целью поиска связанных данных. Вы можете использовать HLOOKUP, чтобы сделать то же самое для одной или нескольких строк данных. По сути, при использовании ВПР вы спрашиваете: «Вот значение, найдите это значение в другом наборе данных, а затем верните мне значение другого столбца в том же наборе данных».
Вы можете спросить, чем это может быть полезно? Хорошо, возьмем, к примеру, следующий образец таблицы, который я создал для этого урока. Таблица очень проста: на одном листе содержится информация о нескольких владельцах автомобилей, такая как имя, идентификатор автомобиля, цвет и мощность. На втором листе указаны идентификаторы автомобилей и их фактические названия моделей. Общим элементом данных на двух листах является идентификатор автомобиля.
Теперь, если я хочу отобразить название автомобиля на листе 1, я могу использовать ВПР для поиска каждого значения в таблице владельцев автомобилей, найти это значение на втором листе, а затем вернуть второй столбец (модель автомобиля). ) в качестве желаемого значения. Так как же ты это сделаешь? Итак, сначала вам нужно ввести формулу в ячейку H4 . Обратите внимание, что я уже ввел полную формулу в ячейки с F4 по F9 . Мы рассмотрим, что на самом деле означает каждый параметр в этой формуле.
Вот как формула выглядит в готовом виде:
=VLOOKUP(B4,Sheet2!$A$2:$B$5,2,FALSE)
Эта функция состоит из 5 частей:
<р>1. =ВПР – знак = означает, что эта ячейка будет содержать функцию, в нашем случае это функция ВПР для поиска по одному или нескольким столбцам данных. <р>2. B4 – первый аргумент функции. Это фактический поисковый запрос, который мы хотим найти. Искомое слово или значение — это то, что введено в ячейку B4. <р>3. Sheet2!$A$2:$B$5 – диапазон ячеек на листе Sheet2, в которых мы хотим выполнить поиск, чтобы найти значение поиска в B4. Поскольку диапазон находится на Листе2, нам нужно указать перед диапазоном имя листа, за которым следует знак !. Если данные находятся на одном листе, префикс не нужен. Если хотите, вы также можете использовать здесь именованные диапазоны. <р>4. 2 – это число указывает столбец в определенном диапазоне, для которого вы хотите вернуть значение. Итак, в нашем примере на Листе 2 мы хотим вернуть значение столбца B или название автомобиля, как только будет найдено совпадение в столбце A. Однако обратите внимание, что положение столбца на листе Excel не имеет значения. Итак, если вы переместите данные из столбцов A и B в D и E, скажем, если вы определили свой диапазон в аргументе 3 как $D$2:$E$5 , номер возвращаемого столбца все равно будет 2. Это относительная позиция, а не абсолютный номер столбца..<р>5. False . Значение False означает, что Excel вернет значение только при точном совпадении. Если вы установите значение True, Excel будет искать ближайшее совпадение. Если для него установлено значение False и Excel не может найти точное совпадение, он вернет #N/A .
Надеюсь, теперь вы понимаете, какую пользу может принести эта функция, особенно если у вас есть много данных, экспортированных из нормализованной базы данных. Может существовать основная запись, значения которой хранятся в таблицах поиска или справочных таблицах. Вы можете получить другие данные, «объединив» их с помощью ВПР.
Вы, возможно, заметили еще одну вещь: использование символа $ перед буквой столбца и номером строки. Символ $ сообщает Excel, что при перетаскивании формулы в другие ячейки ссылка должна оставаться прежней. Например, если вы скопируете формулу из ячейки F4 в H4, удалите символы $, а затем перетащите формулу вниз в H9, вы заметите, что последние 4 значения станут #N/A.
Причина этого заключается в том, что при перетаскивании формулы вниз диапазон изменяется в соответствии со значением этой ячейки. Как вы можете видеть на рисунке выше, диапазон поиска для ячейки H7 — Sheet2!A5:B8 . Он просто продолжал добавлять 1 к номерам строк. Чтобы сохранить этот диапазон фиксированным, вам нужно добавить символ $ перед буквой столбца и номером строки.
Одно замечание: если вы собираетесь установить для последнего аргумента значение True, вам необходимо убедиться, что данные в вашем диапазоне поиска (второй лист в нашем примере) отсортированы в порядке возрастания, иначе это не будет работать! Есть вопросы, оставляйте комментарии. Наслаждайтесь!.