Как извлечь данные из электронной таблицы, используя 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, которые позволят вам выполнять продвинутые функции?
Например, вы можете:
- экспортировать данные WordPress в Excel
- синтаксически анализировать и извлекать данные из Excel с помощью PHP класса
- конвертировать таблицу Excel в соответствующую HTML таблицу
- конвертировать файлы Excel в объект .NET DataTable
- и многое другое



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



Синтаксис
Синтаксис функции VLOOKUP:
=VLOOKUP(искомое значение, диапазон таблицы, номер столбца, [true/false])
Вот что означают эти аргументы:
- Искомое значение. Ячейка, в которой находится уникальный идентификатор.
- Диапазон таблицы. Диапазон ячеек, которые содержат идентификатор в первом столбце, а в последующих столбцах располагаются остальные данные.
- Номер столбца. Номер столбца, в котором находятся данные, которые вам нужны. Не путайте его с буквой столбца. На рисунке выше, штаты находятся в столбце 4.
- True/False. Этот аргумент необязателен. True означает, что приемлемо приблизительное совпадение, а False означает, что допустимо только точное совпадение.
Мы хотим найти количество продаж из таблицы на рисунке ниже, поэтому используем такие аргументы:



Определение имени диапазона для создания абсолютной ссылки
В 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.



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



Ввод формулы
Чтобы ввести формулу, перейдите на лист Sales Amounts и кликните по ячейке B5.
Введите формулу:
=VLOOKUP(A5,data,8,FALSE)
Нажмите Enter.



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



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



Если хотите, вы можете запустить функцию VLOOKUP в следующих нескольких столбцах, чтобы извлечь другие поля, такие как фамилия или штат.
Использование MATCH
Функция MATCH не возвращает вам значение из данных; вы задаете значение, которое вы ищете и функция возвращает позицию этого значения. Это как спросить, где находится Мейн стрит, 135, и получить ответ, что это четвертое здание вниз по улице.
Синтаксис
Синтаксис функции MATCH:
=MATCH(искомое значение, диапазон таблицы, [тип совпадения])
Аргументы:
- Искомое значение. Ячейка, содержащая уникальный идентификатор.
- Диапазон таблицы. Диапазон ячеек, в котором мы ищем.
- Тип совпадения. Необязательный. Определяет, насколько точным должно быть совпадение, согласно:
Следующее большее значение | -1 | Значения должны располагаться в убывающем порядке. |
Целевое значение | 0 | Значения могут идти в любом порядке. |
Следующее меньшее значение | 1 | Тип по умолчанию. Значения должны располагаться в возрастающем порядке. |
Как и в случае с функцией VLOOKUP, возможно, вы найдете использование функции MATCH более простым, если назначите имя диапазону. Перейдите на лист Source Data, выделите диапазон от B4 (заголовок столбца c номером заказа) до низа, кликните в поле Name box (Имя), расположенное над столбцом A, и назовите его order_number. Обратите внимание, что значения расположены в возрастающем порядке.



Перейдите на вкладку Match листа. В ячейку B5 введите функцию MATCH:
=MATCH(A5,order_number,1)



Если вы не задавали имя диапазону, вам надо написать функцию так:
=MATCH(A5,'Source Data'!A5:A203,0)
В любом случае вы увидите, что значение находится в 14й позиции (что делает его 13м по порядку).



Использование INDEX
Функция INDEX противоположна функции MATCH и похожа на VLOOKUP. Вы сообщаете функции, какая строка и столбец данных вам нужны, и она сообщает вам значение, которое находится в ячейке.
Синтаксис
Синтаксис функции INDEX следующий:
=INDEX(диапазон данных, номер строки, [номер столбца])
Аргументы:
- Диапазон данных. Как и в остальных двух функциях, это таблица с данными.
- Номер строки. Номер строки с данными, которая необязательно является строкой листа. Если диапазон начинается на 10-й строке листа, тогда это строка №1.
- Номер столбца. Номер столбца диапазона данных. Если диапазон начинается в столбце E, тогда номер столбца - 1.
Документация Excel говорит нам, что номер столбца это необязательный аргумент, но номер строки тоже является своего рода необязательным. Если диапазон таблицы состоит только из одной строки или одного столбца, вам не нужно использовать другой аргумент.
Перейдите на лист Index и кликните на ячейке C6. Сначала мы хотим найти, что содержится в строке 9, столбце 3 таблицы. В формуле мы используем имя диапазона, которое мы создали ранее.
Введите формулу:
=INDEX(data,A6,B6)



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