Функции INDEX и MATCH в Google Таблицах (пошаговое руководство)

Содержание:

Функции INDEX и MATCH в Google Таблицах, когда используются сами по себе, могут иметь ограниченное применение. Однако в сочетании они могут быть довольно мощными. Фактически, когда они вместе, они могут предоставить отличную (и даже лучшую) альтернативу функции VLOOKUP.

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

Функция MATCH

Функция MATCH в Google Таблицах обеспечивает относительное положение или «индекс» элемента в диапазоне ячеек. Он принимает диапазон ячеек и значение и возвращает положение этого значения в диапазоне ячеек.

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

=MATCH(search_key, range, search_type)

Здесь,

search_key — это элемент, который мы хотим сопоставить. Это может быть текстовое или числовое значение, ссылка на ячейку или формула.
range — это диапазон ячеек, в котором мы хотим найти элемент, соответствующий search_key.
search_type — необязательный параметр. Он определяет тип соответствия, который мы хотим. Это может быть одно из следующих значений:
- 0: это значение указывает, что поиск должен выполняться для точно совпадающего элемента. Этот параметр обычно используется, когда предполагается, что наш диапазон не отсортирован в каком-либо порядке.
- 1: это значение по умолчанию. Этот параметр предполагает, что диапазон уже отсортирован в порядке возрастания . Если задать для этого параметра значение 1, будет возвращено наибольшее значение, меньшее или равное search_key.
- -1: эта опция предполагает, что диапазон уже отсортирован в порядке убывания. Если задать для этого параметра значение -1, возвращается наименьшее значение, большее или равное search_key.

Давайте рассмотрим простой пример, чтобы понять, как работает функция MATCH. Допустим, у вас есть следующий список имен сотрудников:

Если вы хотите узнать положение Cierra Vega в списке, вы можете использовать функцию MATCH следующим образом:

=MATCH(‘Cierra Vega’,A2:A8,0)

Это вернет значение 4, потому что соответствующий элемент является четвертым в списке, начиная с ячейки A2. Обратите внимание, что 4 — это не номер строки соответствующего элемента. Это позиция или индекс элемента в указанном диапазоне (A2: A8).

Функция INDEX

Функция INDEX Google Таблиц предоставляет содержимое определенной ячейки или диапазона ячеек. Другими словами, он принимает диапазон ячеек, индекс строки и индекс столбца и возвращает значение в ячейке, которая находится на пересечении указанной строки и столбца.

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

INDEX(reference, [row], [column])

Здесь,

reference — это диапазон ячеек, из которого мы хотим извлечь элемент.
row — это смещение строки в ссылке, из которой мы хотим извлечь элемент.
column — это смещение столбца в ссылке, из которого мы хотим извлечь элемент. Этот параметр не является обязательным.

Давайте рассмотрим простой пример, чтобы понять, как работает функция INDEX. Допустим, у вас есть следующий список имен сотрудников:

Если вы хотите узнать имя сотрудника, который находится на 4-й позиции в списке имен, вы можете использовать функцию INDEX следующим образом:

=INDEX(A2:A8,4,1)

Это вернет значение «Cierra Vega», потому что это элемент в позиции 4 столбца 1, начиная с ячейки A2. Обратите внимание, что 4 — это не номер строки соответствующего элемента. Это позиция или индекс элемента в указанном диапазоне (A2: A8).

Зачем использовать функции INDEX и MATCH в Google Таблицах?

Как вы уже могли заметить, функции MATCH и INDEX на самом деле не представляют особой ценности, как они есть. Но вот изюминка. Когда вы объединяете функции INDEX и MATCH в Google Таблицах вместе, они могут делать очень полезные вещи!

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

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

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

Здесь может пригодиться команда INDEX-MATCH. С помощью этой пары функций вы можете получить доступ к отделу или зарплате любого сотрудника по его именам или к имени любого сотрудника по его идентификатору.

В следующих разделах мы обсудим, как именно этого добиться.

Как объединить функции INDEX и MATCH в Google Таблицах

Общая формула для объединения функций INDEX-MATCH:

=INDEX(range2,MATCH(search_key,range1,0))

Здесь,

search_key — это значение, которое мы хотим искать в диапазоне1.
range1 — это диапазон ячеек, в котором функция MATCH находит индекс для значения, соответствующего search_key.
range2 — это диапазон ячеек, из которого функция INDEX извлекает значение, соответствующее позиции / индексу, возвращаемому MATCH.

Другими словами, функция MATCH помогает функции INDEX определить позицию возвращаемого значения.

Давайте рассмотрим пример, чтобы понять это немного подробнее.

Использование INDEX и функции MATCH со ссылками в один столбец

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

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

В этом случае идеальным вариантом будет сочетание функций MATCH и INDEX. Вот формула, которую мы можем использовать:

=INDEX(C2:C8,MATCH(B10,B2:B8,0))

Как видно из изображения ниже, эта формула возвращает отдел, соответствующий названию «Cierra Vega»:

Объяснение формулы

Чтобы понять, как работает эта формула, давайте разберемся с ней. Начнем с внутренней функции формулы:

MATCH(B10,B2:B8,0)

Эта функция ищет значение в B10 в диапазоне B2: B8 и возвращает положение этого значения в диапазоне. Поскольку Cierra Vega — это 4-е имя, начинающееся с ячейки B2, эта функция возвращает позицию 4.

Теперь давайте посмотрим на внешнюю функцию формулы:

INDEX(C2:C8,MATCH(B10,B2:B8,0))

Эта формула ищет значение в 4-й позиции диапазона C2: C8 и возвращает значение в этой позиции, которым является «Продажи». Это отдел, которому принадлежит «Сьерра Вега».

Теперь, даже если мы изменим имя в ячейке B10, мы все равно получим правильный результат в ячейке B11:

Таким образом, функции MATCH-INDEX помогли нам использовать одно значение, чтобы получить доступ к другому значению в другом столбце в той же строке.

Использование INDEX и функции MATCH с несколькими критериями

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

В нашем примере рабочего листа допустим, что у нас может быть название отдела, зарплата или даже идентификатор в качестве метки в ячейке A11. Другими словами, что, если бы метка в ячейке A11 также была динамической и могла изменяться?

В этом случае нам нужно будет рассмотреть несколько столбцов, и столбец, к которому мы обращаемся, будет зависеть от метки в ячейке A11. Что ж, этого также можно достичь с помощью INDEX-MATCH следующим образом:

=INDEX(A2:D8,MATCH(B10,B2:B8,0),MATCH(A11,A1:D1,0))

На этот раз мы также использовали третий параметр функции INDEX (который позволяет нам указать индекс столбца в диапазоне).

Как видно из изображения ниже, если ячейка A11 содержит текст «Зарплата», эта формула возвращает зарплату, соответствующую имени, указанному в ячейке B10:

Если ячейка A2 содержит текст «ID», эта же формула возвращает ежемесячный идентификатор, соответствующий имени, указанному в ячейке B10:

Даже если вы измените имя в ячейке B10, вы все равно получите правильный идентификатор:

Объяснение формулы

Чтобы понять, как работает эта формула, давайте разберемся с ней. Начнем с первой функции MATCH:

MATCH(B10,B2:B8,0)

Как объяснялось ранее, эта функция возвращает позицию имени Cierra Vega в диапазоне B2: B8. Таким образом, он возвращает индекс 4.

Теперь давайте посмотрим на вторую функцию MATCH:

MATCH(A11,A1:D1,0)

Эта функция ищет в диапазоне A1: D1 значение в ячейке A11 и возвращает положение этого значения в диапазоне. Когда A11 содержит текст «Department», функция обнаруживает, что это третий элемент, начиная с ячейки A1. Итак, эта функция возвращает позицию 3.

Наконец, давайте посмотрим на внешнюю функцию:

INDEX(A2:D8,MATCH(B10,B2:B8,0),MATCH(A11,A1:D1,0))

Эта формула ищет значение в 4-й позиции по строке и в 3-й позиции по столбцу в диапазоне A2: D8 (со смещением строки и столбца 4 и 3 соответственно) и возвращает значение в этой позиции, которое является ‘Продажи ‘. Это отдел, соответствующий Cierra Vega.

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

Почему использование INDEX и MATCH лучше, чем использование VLOOKUP?

Прочитав весь учебник, вы можете подумать:

«Разве все это нельзя сделать с помощью функции VLOOKUP?»

Что ж, возможно! Фактически, то, что делает INDEX-MATCH, — это, по сути, VLOOKUP. Однако есть определенные вещи, которые может делать INDEX-MATCH, чего не может делать VLOOKUP.

INDEX-MATCH позволяет искать столбцы как слева, так и справа от данного столбца поиска, тогда как VLOOKUP позволяет искать только слева от столбца поиска. Он ищет первый столбец в заданном диапазоне, а затем ищет совпадения только справа от него. Попытка получить доступ к столбцу слева от диапазона с помощью VLOOKUP возвращает ошибку #N/A error.
Добавление новых столбцов или перемещение существующих столбцов не влияет на результаты формулы INDEX-MATCH, поскольку она обращается к ссылкам на ячейки. Таким образом, порядок или изменение индекса столбца не имеет значения. Однако результаты VLOOKUP полностью искажаются, если столбец изменяется или удаляется, потому что он обращается к порядку столбцов, а не к ссылкам на ячейки.

В заключение отметим, что функция Index и Match в Google Таблицах представляет собой отличную альтернативу VLOOKUP, добавляя большую гибкость операциям поиска с использованием динамических ссылок на ячейки. Мы надеемся, что это руководство было полезным и легким для понимания и последующего использования.

Вас могут заинтересовать следующие статьи: