Как извлечь данные из электронной таблицы, используя VLOOKUP, MATCH и INDEX

() translation by (you can also view the original English article)

Когда вам нужно найти и извлечь столбец данных из одной таблицы и поместить ее в другую, используйте функцию VLOOKUP. Эта функция работает в любой версии Excel под Windows и на Mac, а также в Google Sheets. Она позволит вам найти данные в одной таблице используя некоторый идентификатор в ней, общий с другой таблицей. Обе таблицы могут находиться на разных листах или даже в в разных книгах. Есть также функция HLOOKUP, которая делает то же самое, но с данными, расположенными горизонтально, по строкам.

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

Лучшие варианты

Перед тем, как мы погрузимся в функции Excel, вы в курсе, что Envato Market содержит массу скриптов и плагинов Excel, которые позволят вам выполнять продвинутые функции?

Например, вы можете:

Excel scripts and plugins on Envato Market — Скрипты и плагины Excel на Envato Market

Скринкаст

Если вы хотите следовать инструкциям этого руководства, используя свой файл Excel, вы можете это сделать. Или, если хотите, скачайте zip файл, прилагаемый к этому руководству, который содержит электронную таблицу с примером, она называется vlookup example.xlsx.

Использование VLOOKUP

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

unique identifiers for vlookup — Уникальный идентификатор должен быть как серийный номер, не должно быть двух одинаковых идентификаторов в одной таблице.

Синтаксис

Синтаксис функции VLOOKUP:

=VLOOKUP(искомое значение, диапазон таблицы, номер столбца, [true/false])

Вот что означают эти аргументы:

Искомое значение. Ячейка, в которой находится уникальный идентификатор.
Диапазон таблицы. Диапазон ячеек, которые содержат идентификатор в первом столбце, а в последующих столбцах располагаются остальные данные.
Номер столбца. Номер столбца, в котором находятся данные, которые вам нужны. Не путайте его с буквой столбца. На рисунке выше, штаты находятся в столбце 4.
True/False. Этот аргумент необязателен. True означает, что приемлемо приблизительное совпадение, а False означает, что допустимо только точное совпадение.

Мы хотим найти количество продаж из таблицы на рисунке ниже, поэтому используем такие аргументы:

syntax of vlookup — Синтаксис функции VLOOKUP

Определение имени диапазона для создания абсолютной ссылки

В Vlookup example.xlsx посмотрите на лист Sales Amounts. Мы введем формула в ячейку B5, затем используем возможность автозаполнения, чтобы скопировать формула в ячейки ниже нее. Это значит, что диапазон ячеек в формуле должен быть абсолютной ссылкой. Хороший способ сделать это - это задать имя для диапазона ячеек.

Задание имени диапазона в Excel

Перед тем, как вводить формулу, перейдите на лист source data.
Выделите ячейки от A4 (заголовок для столбца Order #) до H203. Быстрый способ это сделать - это кликнуть по A4, затем нажать Ctrl-Shift-End (Command-Shift-End на Маке).
Кликните внутри поля Name Box (Имя) над столбцом A (сейчас в поле Name Box отображается A4).
Напечатайте data, затем нажмите Enter.
Теперь вы можете использовать в формуле имя data вместо $A$4:$H$203.

Name box displaying a range name — Поле name box (имя) обычно отображает адрес текущей ячейки. Кликните в него и напечатайте имя, чтобы определить диапазон.

Задание имени диапазона в Google Sheets.

В Google Sheets имя задается немного по другому.

Кликните по заголовку первого столбца ваших исходных данных, затем нажмите Ctrl-Shift-Стрелка вправо (Command-Shift-Стрелка вправо на Маках). Затем выберите строку заголовков столбцов.
Нажмите Ctrl-Shift-Стрелка вниз (Command-Shift-Стрелка вниз на Маке). Затем выберите актуальные данные.
Кликните в меню Data (Данные), затем выберите Named and protected ranges (Именованные и защищенные диапазоны).
В поле Name and protected ranges box справа напечатайте data, затем кликните Done.

Data menu in Google Sheets — Определение имени диапазона в Google Sheets

Ввод формулы

Чтобы ввести формулу, перейдите на лист Sales Amounts и кликните по ячейке B5.

Введите формулу:

=VLOOKUP(A5,data,8,FALSE)

Нажмите Enter.

entering the vlookup function — Ввод функции VLOOKUP

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

mouse pointer on the autofill dot — Когда вы поместите курсор мыши на точку в правом нижнем углу ячейки, он превратится в перекрестие Автозаполнения.

Кликните дважды, чтобы заполнить значения ниже по столбцу.

worksheet with data after using autofill — Дважды кликните перекрестием Автозаполнения, чтобы скопировать формулу ниже по столбцу

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

Использование MATCH

Функция MATCH не возвращает вам значение из данных; вы задаете значение, которое вы ищете и функция возвращает позицию этого значения. Это как спросить, где находится Мейн стрит, 135, и получить ответ, что это четвертое здание вниз по улице.

Синтаксис

Синтаксис функции MATCH:

=MATCH(искомое значение, диапазон таблицы, [тип совпадения])

Аргументы:

Искомое значение. Ячейка, содержащая уникальный идентификатор.
Диапазон таблицы. Диапазон ячеек, в котором мы ищем.
Тип совпадения. Необязательный. Определяет, насколько точным должно быть совпадение, согласно:

Следующее большее значение	-1	Значения должны располагаться в убывающем порядке.
Целевое значение	0	Значения могут идти в любом порядке.
Следующее меньшее значение	1	Тип по умолчанию. Значения должны располагаться в возрастающем порядке.

Как и в случае с функцией VLOOKUP, возможно, вы найдете использование функции MATCH более простым, если назначите имя диапазону. Перейдите на лист Source Data, выделите диапазон от B4 (заголовок столбца c номером заказа) до низа, кликните в поле Name box (Имя), расположенное над столбцом A, и назовите его order_number. Обратите внимание, что значения расположены в возрастающем порядке.

name box with one column defined — Именованный диапазон может быть просто одним столбцом, одной строкой, или даже одной клеткой

Перейдите на вкладку Match листа. В ячейку B5 введите функцию MATCH:

=MATCH(A5,order_number,1)

entering the match function — Ввод функции MATCH

Если вы не задавали имя диапазону, вам надо написать функцию так:

=MATCH(A5,'Source Data'!A5:A203,0)

В любом случае вы увидите, что значение находится в 14й позиции (что делает его 13^м по порядку).

completed match function — Результат функции MATCH

Использование INDEX

Функция INDEX противоположна функции MATCH и похожа на VLOOKUP. Вы сообщаете функции, какая строка и столбец данных вам нужны, и она сообщает вам значение, которое находится в ячейке.

Синтаксис

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

=INDEX(диапазон данных, номер строки, [номер столбца])

Аргументы:

Диапазон данных. Как и в остальных двух функциях, это таблица с данными.
Номер строки. Номер строки с данными, которая необязательно является строкой листа. Если диапазон начинается на 10-й строке листа, тогда это строка №1.
Номер столбца. Номер столбца диапазона данных. Если диапазон начинается в столбце E, тогда номер столбца - 1.

Документация Excel говорит нам, что номер столбца это необязательный аргумент, но номер строки тоже является своего рода необязательным. Если диапазон таблицы состоит только из одной строки или одного столбца, вам не нужно использовать другой аргумент.

Перейдите на лист Index и кликните на ячейке C6. Сначала мы хотим найти, что содержится в строке 9, столбце 3 таблицы. В формуле мы используем имя диапазона, которое мы создали ранее.

Введите формулу:

=INDEX(data,A6,B6)

entering the index function — введение функции index

Она возвращает фамилию покупателя: Strevell. Изменяйте значения в ячейках A6 и B6, и ячейка C6 будет показывать разные результаты (обратите внимание, что многие строки содержат одинаковые штаты и названия продуктов).

Заключение

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