Vlookup формула: Использование функции ВПР (VLOOKUP) для подстановки значений

Мы надеемся, что эта статья ВПР(VLOOKUP) для чайников помогла объяснить, как ВПР работает в Google Таблицах.

Формулы для SEO в Google Таблицах — Топвизор–Журнал

Содержание

Перевод статьи Джеки Чу из блога Search Engine Land

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

Управление ключевыми словами

VLOOKUP (ВПР): объединение наборов данных

=ВПР(запрос; диапазон_поиска; номер_столбца_с_возвращаемыми_значениями; [отсортированы_ли_данные в_первом_столбце_диапазона_поиска])

ВПР позволяет объединять по общим значениям два набора данных. Название формулы VLOOKUP (ВПР) означает «vertical lookup», то есть «вертикальный просмотр» или «вертикальный поиск». Эта формула Google Таблиц — одна из тех, которую стоит изучить в первую очередь. Можно сказать, что это примитивная версия оператора JOIN из языка SQL.

Формула поможет получить расширенную информацию о наборах ключевых слов (keyword sets), добавляя данные по частоте, контекстной рекламе или показатели, связанные с воронкой продаж, например количество новых регистраций.

Последний параметр формулы (в квадратных скобках) принимает значения ИСТИНА или ЛОЖЬ. При помощи этого параметра можно указать, какой тип сопоставления значений нужен: 

Пример работы формулы VLOOKUP

Совет: зафиксируйте диапазон поиска, используя символ $ ($E$4:$E$6 в примере выше), чтобы растянуть одну и ту же формулу на несколько строк.

О формуле ВПР в Google Справке

CONCATENATE (CЦЕПИТЬ): объединение строк и символов 

=CЦЕПИТЬ(диапазон;диапазон)

СЦЕПИТЬ может применяться для разных задач, включая создание списков ключевых слов (объединение двух и более переменных), создание строк запроса или для массовой шаблонизации метаданных.

Как понятно из названия формулы, вы можете использовать её для того, чтобы объединять любое количество строк в одну. 

Если вам нужен пробел между ключевыми словами (скорее всего, он нужен, потому что иначе все слова склеятся в одно), то его нужно добавлять вручную — в качестве аргумента » «. Например:

Пример работы формулы СЦЕПИТЬ

О формуле «СЦЕПИТЬ» в Google Справке

FLATTEN (ВЫРОВНЯТЬ): создание единых столбцов из диапазонов

=ВЫРОВНЯТЬ(диапазон:диапазон)

После того, как вы объедините несколько тысяч ключевых слов с помощью формулы «СЦЕПИТЬ», вам, вероятно, нужно загрузить эти ключевые слова в свой инструмент для отслеживания позиций — через пользовательский интерфейс или CSV-файл.

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

С помощью функции «ВЫРОВНЯТЬ» вы выбираете диапазон с нужными данными и в результате получаете один столбец, в котором собраны все ключевые слова, благодаря чему их очень легко куда-нибудь скопировать.

Пример работы формулы flatten

О формуле «ВЫРОВНЯТЬ» в Google Справке  

LOWER (СТРОЧН): перевод в нижний регистр

=СТРОЧН(диапазон)

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

Пример работы формулы СТРОЧН

Есть и противоположная по смыслу формула — ПРОПИСН, которая пригодится, если вам нужно автоматически сделать все буквы заглавными.

О формуле «СТРОЧН» в Google Справке

COUNTIF (СЧЁТЕСЛИ): подсчёт ячеек по определённому условию

=СЧЁТЕСЛИ(диапазон; "текст_условия_или_функция")

СЧЁТЕСЛИ позволяет точно подсчитать количество ячеек, содержимое которых совпадает с указанным текстовым значением. Кроме того, с помощью этой функции можно подсчитывать количество ячеек с числовыми значениями, которые соответствуют заданным условиям.

Эта формула особенно эффективна при группировке страниц, управлении расписанием публикации контента или при сортировке ключевых слов по общим параметрам, таким как тип страницы или товар, с которым они связаны.

Её также можно использовать для сопоставления значений, например, подсчитывать те ключевые слова, у которых CPC > $10,00 или частота > 100.

Пример работы формулы СЧЁТЕСЛИ

О формуле «СЧЁТЕСЛИ» в Google Справке 

SUMIF (СУМЕСЛИ): суммирование данных по определенному условию

=СУМЕСЛИ(диапазон_поиска; "условие"; [диапазон_суммирования])

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

Пример работы формулы СУМЕСЛИ

О формуле «СУМЕСЛИ» в Google справке 

Перевод: GOOGLETRANSLATE 

=GOOGLETRANSLATE(ячейка; "исходный_язык" или "auto"; "целевой_язык")

• «исходный язык» — это либо двухбуквенный код языка оригинала (например, RU), либо «auto» — в этом случае Google будет пытаться определить язык автоматически;

• «целевой язык» — это двухбуквенный код языка перевода, например, ES для перевода на испанский язык.

Google не поддерживает перевод абсолютно на все языки, например, на канадский французский. Но поддерживает такие языки, как европейский португальский (PT‑PT) и бразильский португальский (PT‑BR), а также китайские языки, включая традиционный китайский (ZH‑TW) и упрощенный китайский (ZH‑CN).

Пример работы формулы GOOGLETRANSLATE

О формуле GOOGLETRANSLATE в Google Справке 

Управление контентом и URL‑адресами

SPLIT: разделение текста

=SPLIT(ячейка; "разделитель"; [тип_разделителя]; [выполнять_ли_удаление_пустых_ячеек])

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

Хотя на панели инструментов в меню «Данные» есть функция «Разделить текст на столбцы», вы также можете разделить текст, разграниченный определённым символом, словом или даже пробелами, на отдельные столбцы с помощью функции SPLIT.

Пример работы формулы SPLIT

О формуле SPLIT в Google Справке 

LEN (ДЛСТР): подсчёт символов

=ДЛСТР(ячейка)

ДЛСТР — простая формула Google Таблиц, которую можно использовать для SEO, чтобы посчитать количество символов в текстовой строке.

Эта формула поможет не выходить за пределы допустимого количества символов при создании контента, например, чтобы метаданные не были обрезаны из‑за чрезмерной длины.

Пример работы формулы LEN

О формуле «ДЛСТР» в Google Справке

REGEXREPLACE: «очистка» адресов

=REGEXREPLACE(ячейка с текстом; "регулярное_выражение"; "текст_в_качестве_замены")

Регулярные выражения — мощный инструмент интеллектуального анализа данных при работе с крупными сайтами.

REGEXREPLACE используют при очистке или обрезке URL‑адресов на рабочем листе, когда нужно получить только путь без доменного имени или управлять редиректами.

Пример работы формулы REGEXREPLACE

О формуле REGEXREPLACE в Google Справке 

Создание дашбордов: SPARKLINE

=SPARKLINE(B3:G3)

Функция SPARKLINE позволяет создавать простые дашборды в Google Таблицах. Многие типы данных, связанные с SEO и сайтами, отлично смотрятся в виде временных рядов, и в Google Таблицах можно легко создавать их.

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

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

Линейные диаграммы (временные ряды)

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

У этой функции есть параметры: «linewidth» (толщина линии), «charttype» (тип диаграммы) и «color» (цвет). Их можно не указывать, формула будет работать. Но лучше использовать во временных рядах линии немного увеличенной толщины и контрастный цвет, привлекающий внимание к диаграмме.

=SPARKLINE(B3:G3; {«charttype»»line»; «color»»indigo»; «linewidth»2}) — вариант вызова этой функции с цветом индиго и более жирной линией.

Пример работы формулы SPARKLINE

Столбчатые диаграммы и гистограммы

Чтобы сделать такой тип диаграммы, просто измените тип на «column» для создания столбчатой диаграммы, как показано ниже:

Пример работы формулы SPARKLINE с атрибутом column

А чтобы сделать гистограмму, поменяйте тип на «bar»:

Пример работы формулы SPARKLINE с атрибутом bar

О формуле SPARKLINE в Google Справке 

Независимо от того, насколько продвинутым образом вы будете пользоваться всеми этими формулами, они позволят вам без больших финансовых затрат упорядочить SEO‑данные и анализировать ключевые слова.

Интересное по теме: 

Отслеживаем влияние апдейтов Google на метрики сайта с помощью Google Data Studio 

57 бесплатных инструментов для SEO‑специалиста

Учебное пособие по функции ВПР в Excel с примерами формул

Сегодня мы рассмотрим, как использовать ВПР в Excel со множеством подробных пошаговых примеров. Вы узнаете, как выполнять ВПР с другого листа и другой книги, выполнять поиск с использованием подстановочных знаков и многое другое.

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

Функция Excel ВПР

Что такое ВПР? Начнем с того, что это функция Excel 🙂 Что она делает? Он ищет указанное вами значение и возвращает соответствующее значение из другого столбца. С технической точки зрения, функция ВПР ищет значение в первом столбце заданного диапазона и возвращает значение в той же строке из другого столбца.

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

Буква «V» означает «вертикальный» и используется для отличия ВПР от функции ГПР, которая ищет значение в строке, а не в столбце (H означает «горизонтальный»).

Функция доступна во всех версиях Excel 365 — Excel 2007.

Совет. В Excel 365 и Excel 2021 вы можете использовать функцию XLOOKUP, которая является более гибкой и мощной преемницей VLOOKUP.

Синтаксис ВПР

Синтаксис функции ВПР следующий:

ВПР(искомое_значение, массив_таблиц, индекс_столбца, [диапазон_просмотра])

Где:

• Lookup_value (обязательно) — значение для поиска.
  Это может быть значение (число, дата или текст), ссылка на ячейку (ссылка на ячейку, содержащую значение поиска) или значение, возвращаемое какой-либо другой функцией. В отличие от чисел и ссылок на ячейки, текстовые значения всегда должны быть заключены в «двойные кавычки».
• Table_array (обязательно) — это диапазон ячеек, где искать искомое значение и из которого извлекать совпадения. Функция ВПР всегда ищет в первом столбце массива таблиц , который может содержать различные текстовые значения, числа, даты и логические значения.
• Col_index_num (обязательно) — это номер столбца, из которого нужно вернуть значение. Подсчет начинается с крайнего левого столбца в массиве таблиц, который равен 1.
• Range_lookup (необязательный) — определяет, искать приблизительное или точное совпадение:
  • ИСТИНА или опущено (по умолчанию) — примерное совпадение. Если точное совпадение не найдено, формула ищет наибольшее значение, которое меньше искомого значения. Требуется сортировка столбца поиска в порядке возрастания.
  • FALSE — точное совпадение. Формула ищет значение, точно равное искомому значению. Если точное совпадение не найдено, возвращается значение #Н/Д.

Базовая формула ВПР

Вот пример формулы Excel ВПР в самой простой форме. Пожалуйста, взгляните на приведенную ниже формулу и попробуйте «перевести» ее на английский язык:

.

=ВПР("лев", A2:B11, 2, ЛОЖЬ)

• 1 ^{ст 9Аргумент 0069 ( lookup_value ) ясно указывает, что формула ищет слово «лев».}
• Аргумент 2 ^и ( table_array ) равен A2:B11. Имея в виду, что поиск выполняется в крайнем левом столбце, вы можете прочитать приведенную выше формулу чуть дальше: искать «лев» в диапазоне A2:A11. Пока все хорошо, правда?
• Аргумент 3 ^rd col_index_num равен 2. Это означает, что мы хотим вернуть соответствующее значение из столбца B, который является вторым в массиве таблиц.
• Аргумент 4 ^th range_lookup имеет значение FALSE, что означает, что мы ищем точное совпадение.

Со всеми установленными аргументами у вас не должно возникнуть проблем с чтением всей формулы: найдите «лев» в A2:A11, найдите точное совпадение и верните значение из столбца B в той же строке.

Для удобства вы можете ввести интересующее вас значение в какую-нибудь ячейку, скажем, E1, заменить «жестко закодированный» текст ссылкой на ячейку и получить формулу для поиска любого значения, которое вы вводите в E1:

= ВПР(E1, A2:B11, 2, ЛОЖЬ)

Что-то осталось неясным? Тогда попробуйте посмотреть на это так:

Как сделать Vlookup в Excel

При использовании формул ВПР в реальных рабочих листах основное практическое правило заключается в следующем: заблокируйте массив таблицы с абсолютными ссылками на ячейки (например, $A$2:$C$11), чтобы предотвратить его изменение при копировании формулы в другие ячейки. .

Искомое значение в большинстве случаев должно быть относительной ссылкой (например, E2), или вы можете заблокировать только координату столбца ($E2). Когда формула копируется вниз по столбцу, ссылка автоматически корректируется для каждой строки.

Чтобы увидеть, как это работает на практике, рассмотрим следующий пример. В нашу примерную таблицу мы добавили еще один столбец, ранжирующий животных по скорости (столбец A), и хотим найти 1  , 5   и 10   самого быстрого спринтера в мире. Для этого введите ранги поиска в некоторые ячейки (E2:E4 на скриншоте ниже) и используйте следующие формулы:

Чтобы получить имена животных из столбца B:

=ВПР($E2, $A$2:$C$11, 2, ЛОЖЬ)

Чтобы извлечь скорость из столбца C:

= ВПР($E2, $A$2:$C$11, 3, ЛОЖЬ)

Введите приведенные выше формулы в ячейки F2 и G2, выберите эти ячейки и перетащите формулы в следующие строки:

Если вы исследуете формулу в нижней строке, вы заметите, что ссылка на искомое значение скорректирована для этой конкретной строки, в то время как массив таблицы не изменился:

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

Excel VLOOKUP — 5 вещей, которые нужно помнить!

1. Функция ВПР не может смотреть влево . Он всегда ищет в крайнем левом столбце массива таблиц и возвращает значение из столбца справа. Если вам нужно извлечь значения слева, используйте комбинацию INDEX MATCH (или INDEX XMATCH в Excel 365), которая может не заботиться о расположении столбцов поиска и возврата.
2. Функция ВПР нечувствительна к регистру , что означает, что символы верхнего и нижнего регистра обрабатываются как эквивалентные. Чтобы различать регистр букв, используйте формулы ВПР с учетом регистра.
3. Помните о важности последнего параметра. Используйте TRUE для приблизительного совпадения и FALSE для точного совпадения. Подробную информацию см. в разделе ВПР ИСТИНА и ЛОЖЬ.
4. При поиске приблизительного совпадения убедитесь, что данные в столбце подстановки отсортированы по возрастанию.
5. Если искомое значение не найдено, возвращается ошибка #Н/Д. Сведения о других ошибках см. в статье Почему функция ВПР Excel не работает.

Примеры ВПР Excel

Надеюсь, вертикальный поиск стал для вас немного более знакомым. Чтобы закрепить ваши знания, давайте построим еще несколько формул ВПР.

Как выполнять визуальный поиск с другого листа в Excel

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

Для ВПР с другого листа Excel поместите имя рабочего листа, а затем восклицательный знак в аргумент table_array перед ссылкой на диапазон. Например, для поиска в диапазоне A2:B10 на Листе2 используйте следующую формулу:

.

=ВПР("Товар1", Лист2!A2:B10, 2)

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

Например, так можно найти значение A2 в диапазоне A2:A9.на листе Prices и вернуть соответствующее значение из столбца C:

=ВПР(A2, Цены!$A$2:$C$9, 3, ЛОЖЬ)

Примечания:

• Если имя электронной таблицы содержит пробела или неалфавитных символов , оно должно быть заключено в одинарные кавычки, например ‘Прайс-лист’!$A$2:$C$9.
• Если вы используете формулу ВПР для нескольких ячеек, не забудьте заблокировать table_array знаком $, например $A$2:$C$9.

Как выполнить визуальный поиск из другой книги в Excel

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

Например, вот формула для поиска значения A2 на листе с именем Prices в рабочей книге Price_List.xlsx :

=ВПР(A2, [Price_List.xlsx]Цены!$A$2:$C$9, 3, ЛОЖЬ)

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

=ВПР(A2, '[Прайслист. xlsx]Цены'!$A$2:$C$9, 3, ЛОЖЬ)

Самый простой способ создать формулу ВПР, которая ссылается на другую книгу:

1. Откройте оба файла.
2. Начните вводить формулу, переключитесь на другую книгу и выберите массив таблицы с помощью мыши.
3. Введите оставшиеся аргументы и нажмите клавишу Enter, чтобы завершить формулу.

Результат будет примерно таким, как на скриншоте ниже:

Как только вы закроете файл со своей таблицей поиска, формула ВПР продолжит работу, но теперь она будет отображать полный путь к закрытой книге:

Дополнительные сведения см. в разделе Как ссылаться на другой лист или книгу Excel.

Как выполнить визуальный поиск из именованного диапазона на другом листе

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

Чтобы создать именованный диапазон, просто выберите ячейки и введите нужное имя в поле Имя слева от строки формул. Подробные инструкции см. в разделе Как назвать диапазон в Excel.

В этом примере мы дали имя Prices_2020 ячейкам данных (A2:C9) в таблице поиска и получили следующую компактную формулу:

=ВПР(A2, Цены_2020, 3, ЛОЖЬ)

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

Если именованный диапазон находится в другой рабочей книге , поместите имя рабочей книги перед именем диапазона, например:

=ВПР(A2, 'Прайс-лист.xlsx'!Цены_2020, 3, ЛОЖЬ)

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

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

=ВПР(A2, Таблица_цен, 3, ЛОЖЬ)

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

Использование подстановочных знаков в формуле ВПР

Как и многие другие формулы, функция ВПР Excel поддерживает следующие подстановочные знаки:

• Вопросительный знак (?) для соответствия любому одиночному символу.
• Звездочка (*) для соответствия любой последовательности символов.

Подстановочные знаки оказываются очень полезными во многих ситуациях:

• Когда вы не помните точный текст, который вы ищете.
• Когда вы ищете текстовую строку, которая является частью содержимого ячейки.
• Когда столбец подстановки содержит начальные или конечные пробелы. В этом случае вы можете поломать голову, пытаясь понять, почему обычная формула не работает.

Пример 1. Поиск текста, начинающегося или заканчивающегося определенными символами

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

Чтобы вернуть фамилию из столбца A, используйте следующую формулу подстановочного знака Vlookup:

=ВПР("подтверждение*", $A$2:$B$10, 1, ЛОЖЬ)

Чтобы получить лицензионный ключ из столбца B, используйте этот (разница только в порядковом номере столбца):

=ВПР("подтверждение*", $A$2:$B$10, 2, ЛОЖЬ)

Вы также можете ввести известную часть имени в какую-нибудь ячейку, скажем, E1, и объединить подстановочный знак со ссылкой на ячейку:

=ВПР(E1&"*", $A$2:$B$10, 1, ЛОЖЬ)

На снимке экрана ниже показаны результаты:

Ниже приведены еще несколько формул ВПР с подстановочными знаками.

Найти фамилию, оканчивающуюся на «сын»:

=ВПР("*сын", $A$2:$B$10, 1, ЛОЖЬ)

Получить имя, начинающееся с «joh» и заканчивающееся на «son»:

=ВПР("джох*сын", $A$2:$B$10, 1, ЛОЖЬ)

Получить 5-значную фамилию:

=ВПР("?????", $A$2:$B$10, 1, ЛОЖЬ)

Пример 2.

Подстановочный знак ВПР на основе значения ячейки

Из предыдущего примера вы уже знаете, что можно объединить амперсанд (&) и ссылку на ячейку для создания строки поиска. Чтобы найти значение, которое содержит заданный символ (символы) в любой позиции, поставьте амперсанд до и после ссылки на ячейку.

Допустим, вы хотите получить имя, соответствующее определенному лицензионному ключу, но не знаете всего ключа, только несколько символов. С ключами в столбце A, именами в столбце B и частью целевого ключа в E1 вы можете выполнить Vlookup с подстановочными знаками следующим образом:0005

Извлечь ключ:

=ВПР("*"&E1&"*", $A$2:$B$10, 1, ЛОЖЬ)

Извлечь имя:

=ВПР("*"&E1&"*", $A$2:$B$10, 2, ЛОЖЬ)

Примечания:

• Чтобы формула ВПР работала правильно, используйте точное совпадение (ЛОЖЬ — последний аргумент).
• Если найдено более одного совпадения, возвращается первое .

ВПР ИСТИНА против ЛОЖЬ

А теперь пора более подробно рассмотреть последний аргумент функции ВПР Excel. Хотя параметр range_lookup необязателен, он очень важен. В зависимости от того, выберете ли вы ИСТИНА или ЛОЖЬ, ваша формула может дать разные результаты.

ВПР Excel точное совпадение (ЛОЖЬ)

Если для range_lookup задано значение FALSE, формула Vlookup ищет значение, которое точно равно искомому значению. Если найдено два или более совпадения, возвращается 1-е. Если точное совпадение не найдено, возникает ошибка #Н/Д.

Excel VLOOKUP приблизительное совпадение (ИСТИНА)

Если для параметра range_lookup установлено значение TRUE или оно опущено (по умолчанию), формула ищет ближайшее совпадение. Точнее, он сначала ищет точное совпадение, а если точное совпадение не найдено, ищет следующее наибольшее значение, которое меньше искомого значения.

Приблизительное соответствие Vlookup работает со следующими оговорками:

• Столбец поиска должен быть отсортирован в порядке возрастания , от наименьшего к наибольшему, в противном случае правильное значение может быть не найдено.
• Если значение поиска меньше наименьшего значения в массиве поиска, возвращается ошибка #Н/Д.

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

Пример 1. Как сделать точное совпадение Vlookup

Чтобы найти точное совпадение, просто введите FALSE в последний аргумент.

Для этого примера возьмем таблицу скорости животных, поменяем столбцы местами и попробуем найти животных, которые могут бегать со скоростью 80, 50 и 30 миль в час. Со значениями поиска в D2, D3 и D4 введите приведенную ниже формулу в E2, а затем скопируйте ее еще в две ячейки:

= ВПР(D2, $A$2:$B$12, 2, ЛОЖЬ)

Как видите, формула возвращает «Лев» в E3, потому что они выполняются ровно 50 раз в час. Для двух других значений поиска точное совпадение не найдено, и появляются ошибки #Н/Д.

Пример 2.

Как выполнить Vlookup для приблизительного соответствия

Чтобы найти приблизительное совпадение, вам нужно сделать две важные вещи:

• Отсортировать первый столбец table_array от меньшего к большему.
• Используйте TRUE для аргумента range_lookup или опустите его.

Сортировка столбца подстановки очень важна, поскольку функция ВПР прекращает поиск, как только находит близкое совпадение, меньшее, чем значение подстановки. Если данные не отсортированы должным образом, вы можете получить действительно странные результаты или кучу ошибок #N/A.

Для нашего примера данных формула приблизительного совпадения Vlookup выглядит следующим образом:

= ВПР(D2, $A$2:$B$12, 2, ИСТИНА)

И возвращает следующие результаты:

• Для искомого значения «80» возвращается «Cheetah», поскольку его скорость (70) является ближайшим соответствием, которое меньше искомого значения.
• Для искомого значения «50» возвращается точное совпадение (лев).
• Для искомого значения «30» возвращается ошибка #Н/Д, поскольку искомое значение меньше наименьшего значения в столбце поиска.

Специальные инструменты для Vlookup в Excel

Несомненно, ВПР — одна из самых мощных и полезных функций Excel, но она также и одна из самых запутанных. Чтобы сделать кривую обучения менее крутой и сделать ее более приятной, мы включили несколько инструментов, позволяющих сэкономить время, в наш Ultimate Suite for Excel.

Мастер ВПР — простой способ написания сложных формул

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

Чтобы получить индивидуальную формулу, вам нужно сделать следующее:

1. Запустите мастер ВПР.

1. Выберите основную таблицу и таблицу поиска.
2. Укажите следующие столбцы (во многих случаях они выбираются автоматически):
   • Ключевой столбец — столбец в вашей основной таблице, содержащий значения для поиска.
   • Столбец поиска — столбец для поиска.
   • Возврат столбца — столбец, из которого нужно получить значения.
3. Нажмите кнопку Вставить .

В следующих примерах показан мастер в действии.

Стандартный ВПР

Если столбец поиска ( Животное ) является крайним левым столбцом в таблице поиска, вставляется обычная формула ВПР для точного совпадения:

ВПР слева

Когда столбец поиска ( Животное ) находится справа от столбца возврата ( Speed ​​ ), мастер вставляет формулу INDEX MATCH в Vlookup справа налево:

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

Объединить две таблицы — альтернатива ВПР Excel без формул

Если ваши файлы Excel очень большие и сложные, крайний срок проекта приближается, и вы ищете кого-то, кто может протянуть вам руку помощи, попробуйте мастер объединения таблиц.

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

1. Выберите основной стол.
2. Выберите таблицу поиска.
3. Выберите один или несколько общих столбцов в качестве уникальных идентификаторов.
4. Укажите, какие столбцы необходимо обновить.
5. При необходимости выберите столбцы для добавления.
6. Дайте мастеру слияния таблиц несколько секунд на обработку… и наслаждайтесь результатами 🙂

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

Доступные загрузки

Примеры формул Excel VLOOKUP (файл .xlsx)
Полнофункциональная версия Ultimate Suite на 14 дней (файл .exe)

Вас также может заинтересовать

Как использовать функцию ВПР в Excel (в простых шагах)

Точное совпадение | Примерное совпадение | ВПР выглядит правильно | Первый матч | Частичное совпадение | Vlookup нечувствителен к регистру | Множественные критерии | #Н/Д ошибка | Несколько таблиц поиска | Индекс и соответствие | Магия стола | Xlookup

Функция ВПР — одна из самых популярных функций в Excel . Эта страница содержит много простых примеров VLOOKUP.

Точное совпадение

Большую часть времени вы ищете точное совпадение при использовании функции ВПР в Excel. Давайте посмотрим на аргументы функции ВПР.

1. Приведенная ниже функция ВПР ищет значение 53 (первый аргумент) в крайнем левом столбце красной таблицы (второй аргумент).

2. Значение 4 (третий аргумент) указывает функции ВПР вернуть значение в той же строке из четвертого столбца красной таблицы.

Примечание: логическое значение FALSE (четвертый аргумент) указывает функции ВПР вернуть точное совпадение. Если функция ВПР не может найти значение 53 в первом столбце, она вернет ошибку #Н/Д.

3. Вот еще пример. Вместо зарплаты приведенная ниже функция ВПР возвращает фамилию (третьему аргументу присвоено значение 3) идентификатора 79.

Приблизительное совпадение

Давайте рассмотрим пример функции ВПР в режиме приблизительного совпадения ( четвертый аргумент установлен в значение TRUE).

1. Приведенная ниже функция ВПР ищет значение 85 (первый аргумент) в крайнем левом столбце красной таблицы (второй аргумент). Есть только одна проблема. В первом столбце нет значения 85.

2. К счастью, логическое значение ИСТИНА (четвертый аргумент) указывает функции ВПР вернуть приблизительное совпадение. Если функция ВПР не может найти значение 85 в первом столбце, она вернет наибольшее значение меньше 85. В этом примере это будет значение 80.

3. Значение 2 (третий аргумент) указывает функции ВПР вернуть значение в той же строке из второго столбца красной таблицы.

Примечание: всегда сортируйте крайний левый столбец красной таблицы в порядке возрастания, если вы используете функцию ВПР в режиме приблизительного совпадения (четвертый аргумент имеет значение ИСТИНА).

ВПР ищет правильно

Функция ВПР всегда ищет значение в крайнем левом столбце таблицы и возвращает соответствующее значение из столбца в

.0611 справа .

1. Например, функция ВПР ниже ищет имя и возвращает фамилию.

2. Если вы измените порядковый номер столбца (третий аргумент) на 3, функция ВПР ищет имя и возвращает зарплату.

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

Первое совпадение

Если крайний левый столбец таблицы содержит дубликаты, функция ВПР сопоставляет первое совпадение. Например, взгляните на функцию ВПР ниже.

Объяснение: функция ВПР возвращает зарплату Мии Кларк, а не Мии Рид.

Частичное совпадение

Функция ВПР поддерживает подстановочные знаки для частичных совпадений. Например, взгляните на функцию ВПР ниже.

Объяснение: звездочка (*) соответствует нулю или более символов. Функция ВПР сопоставляет первый экземпляр, начинающийся с «Джесс». Используйте «*» и G2 для совпадения «заканчивается на». Используйте «*»&G2&»*» для соответствия «содержит».

ВПР нечувствителен к регистру

Функция ВПР в Excel выполняет поиск без учета регистра. Например, приведенная ниже функция ВПР ищет MIA (ячейка G2) в крайнем левом столбце таблицы.

Объяснение: функция ВПР нечувствительна к регистру, поэтому она ищет МИА, МИА, МИА или МИА и т. д. В результате функция ВПР возвращает зарплату Мии Кларк (первый экземпляр). Используйте ИНДЕКС, ПОИСКПОЗ и ИСТОЧНИК в Excel для выполнения поиска с учетом регистра.

Несколько критериев

Вы хотите найти значение на основе нескольких критериев? Используйте ИНДЕКС и ПОИСКПОЗ в Excel, чтобы выполнить поиск по двум столбцам.

Примечание: приведенная выше формула массива ищет зарплату Джеймса Кларка, а не Джеймса Смита или Джеймса Андерсона.

Ошибка #Н/Д

Если функция ВПР не может найти совпадение, она возвращает ошибку #Н/Д.

1. Например, приведенная ниже функция ВПР не может найти значение 28 в крайнем левом столбце.

2. Если хотите, вы можете использовать функцию IFNA, чтобы заменить ошибку #N/A дружественным сообщением.

Примечание. Функция IFNA была введена в Excel 2013. Если вы используете Excel 2010 или Excel 2007, объедините ЕСЛИ и ISNA или используйте ЕСЛИОШИБКА.

Несколько таблиц поиска

При использовании функции ВПР в Excel у вас может быть несколько таблиц поиска. Вы можете использовать функцию ЕСЛИ, чтобы проверить, выполняется ли условие, и вернуть одну таблицу поиска, если ИСТИНА, и другую таблицу поиска, если ЛОЖЬ.

1. Создайте два именованных диапазона: Table1 и Table2.

2. Выберите ячейку E4 и введите функцию ВПР, показанную ниже.

Объяснение: бонус зависит от рынка (Великобритания или США) и суммы продаж. Второй аргумент функции ВПР делает свое дело. В Великобритании функция ВПР использует Таблицу 1, в США функция ВПР использует Таблицу 2. Установите для четвертого аргумента функции ВПР значение ИСТИНА, чтобы возвратить приблизительное совпадение.

3. Нажмите Enter.

4. Выберите ячейку E4, щелкните в правом нижнем углу ячейки E4 и перетащите ее вниз к ячейке E10.

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