Я пытаюсь вернуть номер столбца, который соответствует второму появлению нуля в следующей таблице:
B C D E F G
2 0 0 3 4 9
Например, желаемая формула вернет 3, представляющий столбец D.
Если невозможно вернуть номер столбца в массиве, тогда можно ли вместо этого вернуть 4 (номер столбца на листе)?
Спасибо заранее за вашу помощь.
Я уверен, что у кого-то будет намного более аккуратный способ сделать это, но вот быстрый ответ, который я взломал вместе.
Для этого требуется указать диапазон (B1:G1 в вашем примере) в некоторой MATCH формулах, а также указать , что диапазон снова в формате R1C1 для использования в INDIRECT функции.
=MATCH(0,INDIRECT("R1C"&MATCH(0,B1:G1,0)+1&":R1C7",0),0)+MATCH(0,B1:G1,0)
Прохождение
Начнем с нахождения столбца, содержащего ваше первое совпавшее значение, вот этот бит формулы: MATCH(0,B1:G1,0) .
Затем, когда мы узнаем, что первое совпадение находится в столбце C, мы можем запустить другой MATCH который просматривает диапазон справа от столбца C - т.е. столбец D и далее. Вот что делает INDIRECT . Он использует ссылки R1C1, так что он может принять ваш первый MATCH в качестве входа: INDIRECT("R1C"&MATCH(0,B1:G1,0)+1&":R1C7",0) .
Итак, мы запускаем наш второй MATCH так: MATCH(0,INDIRECT("R1C"&MATCH(0,B1:G1,0)+1&":R1C7",0),0) .
Это выведет номер столбца, считающего от столбца D (т.е. 1 в этом случае). Поэтому нам нужно добавить число пропущенных слева столбцов, чтобы получить наше окончательное число - это просто повторение первого MATCH: +MATCH(0,B1:G1,0) .
Примечание. Для большей гибкости используйте INDEX() или OFFSET(), а не INDIRECT().
Blm поднимает отличные замечания о негибкости использования INDIRECT - прочитайте его ответ, предлагая вместо этого INDEX или OFFSET .
По многочисленным просьбам ... :-), плюс есть пара проблем с 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 , просто для того, чтобы немного "на будущее" проверить электронную таблицу.