Поиск и смещение

В этом разделе мы поговорим подробнее про пару функций MATCH и OFFSET (ПОИСКПОЗ и СМЕЩ в русском варианте).

Введение

Функция MATCH ищет указанное значение в указанном одномерном диапазоне. У неё три входа: искомое значение, диапазон, вид поиска. На выходе — порядковый номер найденного значения (нумерация начинается с единицы).

Поговорим подробнее про вид поиска: он бывает бинарным (значения входа -1 и 1) и линейным (значение входа 0). Бинарный поиск быстрее, но предполагает упорядоченность массива данных (для 1 — в порядке возрастания, для -1 — в порядке убывания).

Основной подвох: по умолчанию вид поиска равен 1 (бинарный по возрастающему массиву данных), а в реальности чаще требуется поиск по неупорядоченным данным. Поэтому важно следить за тем, чтобы третий вход был равен 0, если только данные не упорядочены.

Функция OFFSET позволяет по ссылке и вектору смещения получить ссылку, смещённую относительно входной на указанный вектор. Её входы: смещаемая ссылка, вертикальное смещение (сверху вниз), горизонтальное смещение (слева направо).

С этой функцией связано два подвоха:

  1. Нетрадиционный порядок координат вектора смещения. Более того, он даже не соответствует порядку координат в стандартном синтаксисе для ссылок. То есть на первый взгляд кажется, что OFFSET(A1,2,4) — это C5, но на самом деле OFFSET(A1,2,4) — это E3.
  2. Нумерация «с единицы» у функции MATCH, которая очень часто используется в паре с OFFSET. В итоге, чтобы получить найденную ячейку, приходится дописывать минус единицу: OFFSET($A$1; MATCH($G$42;$A$1:$A$1000;0)-1; 0)

Стандартные ситуации применения

Наиболее стандартная (и почти единственная) ситуация применения пары MATCH и OFFSET — ассоциативный массив. С точки зрения электронных таблиц ассоциативный массив — это два столбца (или две строчки). В одной — ключи, в другой — значения.

Запрос к такому массиву — это ответ на вопрос «какое значение находится напротив указанного ключа?»

В предположении, что ключи находятся в столбце A, значения — в столбце B, а запрашиваемый ключ — в ячейке C1, сам запрос полностью выглядит как

=OFFSET($B$1; MATCH($C$1;$A$1:$A$1000;0)-1; 0)

Зачастую в реальных таблицах однородные данные оказываются разнесены сразу на несколько ассоциативных массивов. В таком случае рекомендуется следующая схема организации вычислений:

  1. Каждый из массивов обработать по отдельности.
  2. Результаты обработки выстроить друг за другом.
  3. Предположим, для определённости, что было 10 массивов и результаты запросов к ним оказались в диапазоне P1:P10.
  4. В ячейку Q1 пишем формулу =P1.
  5. В ячейку Q2 пишем формулу =IFERROR(Q1;P2).
  6. Копируем ячейку Q2 во все остальные ячейки диапазона Q2:Q10.

В итоге в ячейке Q10 окажется ответ на запрос ко всему множеству данных.

Примитивная реализация

Наконец, покажем, как можно реализовать MATCH и OFFSET (по модулю обработки ошибок) в терминах примитивного IF.

Сначала MATCH. Допустим, что нам нужно найти значение ячейки H1 в диапазоне A1:A1000. Для этого можно выполнить следующие действия:

  1. Поместить в ячейку B1 число 1 (порядковый номер первого значения).
  2. Поместить в ячейку B2 формулу =B1+1 и скопировать её на весь диапазон B2:B1000. Теперь в этом диапазоне порядковые номера соответствующих значений.
  3. Поместить в ячейку C1 формулу =IF(A1=$H$1; B1; NA()).
  4. Поместить в ячейку C2 формулу =IF(A2=$H$1; B2; C1). Скопировать эту формулу на весь диапазон C2:C1000.
  5. В клетке C1000 находится ответ.

Это — аналог запроса =MATCH($H$1; $A$1:$A$1000; 0). Бинарный поиск оставляем в качестве (необязательного и весьма трудного) упражнения.

Теперь реализуем аналог запроса =OFFSET($A$1;$H$1;0) (вариант с двумя ненулевыми координатами можно реализовать как два последовательных сдвига, в каждом из которых одна из координат нулевая). Достаточно выполнить следующее:

  1. Поместить в ячейку B1 число 0 (смещение первого значения).
  2. Поместить в ячейку B2 формулу =B1+1 и скопировать её на весь диапазон B2:B1000. Теперь в этом диапазоне смещения соответствующих значений.
  3. Поместить в ячейку C1 формулу =IF(B1=$H$1; A1; NA()).
  4. Поместить в ячейку C2 формулу =IF(B2=$H$1; A2; C1). Скопировать эту формулу на весь диапазон C2:C1000.
  5. В клетке C1000 находится ответ.

Как можно заметить, примитивные реализации MATCH и OFFSET очень похожи друг на друга. Это и не удивительно: первая восстанавливает порядковый номер по значению, а вторая — значение по порядковому номеру. Другое дело, что реальная функция OFFSET работает гораздо эффективнее примитивной реализации (а вот MATCH имеет ровно ту же асимптотическую вычислительную сложность, что и примитивная реализация).