Общая формула
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
Резюме
Чтобы определить положение значения в 2D-массиве, вы можете использовать функцию СУММПРОИЗВ. В показанном примере формулы, используемые для определения номеров строк и столбцов максимального значения в массиве, следующие:
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1
где «данные» - именованный диапазон C5: G14.
Примечание: в этом примере мы произвольно находим местоположение максимального значения в данных, но вы можете заменить data = MAX (data) любым другим логическим тестом, который будет изолировать данное значение. Также обратите внимание, что эти формулы не работают, если в массиве есть повторяющиеся значения.
Объяснение
Чтобы получить номер строки, данные сравниваются с максимальным значением, в результате чего создается массив результатов ИСТИНА ЛОЖЬ. Они умножаются на результат ROW (data), который генерирует, и массив номеров строк, связанных с именованным диапазоном «data»:
=SUMPRODUCT((FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE)*(5;6;7;8;9;10;11))
Операция умножения заставляет Excel приводить значения ИСТИНА ЛОЖЬ в первом массиве к 1 и 0, поэтому мы можем визуализировать промежуточный шаг следующим образом:
=SUMPRODUCT((0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,1,0,0;0,0,0,0,0;0,0,0,0,0)*(5;6;7;8;9;10;11))
Затем СУММПРОИЗВ возвращает результат 9, что соответствует 9-й строке на листе. Чтобы получить индекс относительно именованного диапазона «data», мы используем:
-ROW(data)+1
Конечным результатом является массив (5; 4; 3; 2; 1; 0; -1), из которого отображается только первое значение (5).
Формула для определения позиции столбца работает точно так же.
Примечание: я столкнулся с этим подходом в комментарии Майка Эриксона на MrExcel.com. В этом потоке есть и другие хорошие идеи, включая вариант формулы массива.
