По многочисленным просьбам ... :-), плюс есть пара проблем с INDIRECT
версией, которую я опишу ниже.
Во-первых, два других решения. Единственные существенные различия заключаются в том, как рассчитывается второй диапазон для поиска. Энди использовал INDIRECT
, я использую INDEX
и OFFSET
соответственно. Еще одно небольшое отличие, которое вы заметите, вместо того, чтобы жестко задавать значение для поиска, я получаю его из ячейки, что упрощает проверку формул путем изменения искомого числа. Я включил все три здесь, так что их легко увидеть рядом друг с другом, первый просто скопирован из ответа Энди (с жестко закодированным 0 заменяется той же ссылкой на ячейку, которую я использовал в других, B3):
=MATCH(B3,INDIRECT("R1C"&MATCH(B3,B1:G1,0)+1&":R1C7",0),0)+MATCH(B3,B1:G1,0)
=MATCH(B3,INDEX(B1:G1,0,MATCH(B3,B1:G1,0)+1):G1,0)+MATCH(B3,B1:G1,0)
=MATCH(B3,OFFSET(B1:G1,0,MATCH(B3,B1:G1,0)):G1,0)+MATCH(B3,B1:G1,0)
Второй аргумент первого (в формуле соответствует второму вхождению поискового номера) MATCH
- это разница:
INDIRECT("R1C"&MATCH(B3,B1:G1,0)+1&":R1C7",0)
INDEX(B1:G1,0,MATCH(B3,B1:G1,0)+1):G1
OFFSET(B1:G1,0,MATCH(B3,B1:G1,0)):G1
Анди описал первое. Вторая и третья работа - вычисление ссылки на ячейку после сопоставления (INDEX(...)
и OFFSET(...)
), а затем ее расширение до диапазона, который заканчивается последней ячейкой (часть :G1
). Таким образом, если поиск 0, MATCH(B3,B1:G1,0)
возвращает 2, то версия INDEX
становится:
INDEX(B1:G1,0,2+1):G1
INDEX(B1:G1,0,3):G1
D1:G1
Версия OFFSET
ведет себя аналогично.
Несмотря на то, что эти три понятия эквивалентны, на самом деле есть две вещи, которые заставили бы меня не использовать INDIRECT
версию. Во-первых, поскольку он вычисляет абсолютный столбец, он не работает при немного других обстоятельствах. Допустим, окончательное значение в таблице изменено с 9 на 3. Затем, если мы поместим 3 в B3 для поиска вторых 3, все формулы должны вернуть 6. Однако версия INDIRECT
возвращает 5. Вот почему В этом случае, MATCH(B3,B1:G1,0)
возвращает 4, поэтому INDIRECT
становится:
INDIRECT("R1C"&4+1&":R1C7",0)
INDIRECT("R1C"&5&":R1C7",0)
INDIRECT("R1C5:R1C7",0)
что эквивалентно E1:G1. Однако первая ячейка E1:G1 - это ячейка, содержащая первые 3, поэтому MATCH
просто найдет первые 3 снова и вернет 1, что добавится к первому значению MATCH
(4), возвращая 5.
Проблема заключается в том, что вычисление столбца является абсолютным и не учитывает тот факт, что поиск таблицы начинается во втором столбце. Таким образом, даже при поиске 0 формула INDIRECT
является "неправильной" в том смысле, что она дважды находит первый 0, но выглядит так, как будто работает, потому что второй 0 находится сразу справа от первого 0, поэтому MATCH
ищет второй 0 возвращает 1, что является разницей между номерами столбцов двух нулей. Если разница между номерами столбцов двух искомых чисел не равна 1, тогда формула INDIRECT
вернет неправильное значение.
Это можно исправить, сделав ссылку на столбец относительной (и предположив, что формула находится в том же столбце, что и начало таблицы):
=MATCH(B3,INDIRECT("R1C["&MATCH(B3,B1:G1,0)&"]:R1C7",0),0)+MATCH(B3,B1:G1,0)
Теперь INDIRECT
это:
INDIRECT("R1C["&4&"]:R1C7",0)
INDIRECT("R1C[4]:R1C7",0)
это F1:G1, так что теперь второй MATCH
начнет поиск в ячейке справа от первого совпадения и вернет 2, в результате всего 6, что правильно.
Это работает, но все еще есть проблема, связанная с моим заключенным в скобки «предположением, что формула находится в том же столбце, что и начало таблицы». INDIRECT
версия (даже фиксированная) довольно хрупкая. Например, если я вставлю новую строку над таблицей чисел, версии INDEX
и OFFSET
продолжат работать, потому что Excel автоматически обновляет все ссылки. Но поскольку все ссылки, кроме одной в версии INDIRECT
являются текстовыми, Excel не может их обновить, поэтому они будут продолжать ссылаться на первую строку, которая теперь является чем-то другим. Если вы удалите первый столбец или добавите дополнительные столбцы слева от таблицы, у него возникнут аналогичные проблемы. Так что в этом случае я бы, вероятно, выбрал версию INDEX
или OFFSET
, просто для того, чтобы немного "на будущее" проверить электронную таблицу.