Обща формула
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
Обобщение
За да намерите позицията на стойност в 2D масив, можете да използвате функцията SUMPRODUCT. В показания пример формулите, използвани за намиране на номера на редове и колони на максималната стойност в масива, са:
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1
където "данни" е наименуваният диапазон C5: G14.
Забележка: за този пример ние произволно намираме местоположението на максималната стойност в данните, но можете да замените data = MAX (данни) с всеки друг логически тест, който ще изолира дадена стойност. Също така имайте предвид, че тези формули ще се провалят, ако в масива има дублирани стойности.
Обяснение
За да получите номера на реда, данните се сравняват с максималната стойност, която генерира масив от TRUE FALSE резултати. Те се умножават по резултата от ROW (данни), който генерира и масив от номера на редове, свързани с посочения диапазон "данни":
=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 да принуждава стойностите TRUE FALSE в първия масив до 1s и 0s, така че можем да визуализираме междинна стъпка по следния начин:
=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))
След това SUMPRODUCT връща резултат от 9, който съответства на 9-ия ред на работния лист. За да получим индекс спрямо посочения диапазон "данни", използваме:
-ROW(data)+1
Крайният резултат е масивът (5; 4; 3; 2; 1; 0; -1), от който се показва само първата стойност (5).
Формулата за определяне на позицията на колоната работи по същия начин.
Забележка: Сблъсках се с този подход в коментар на Майк Ериксън на MrExcel.com. В тази нишка има и някои други добри идеи, включително опция за формула на масив.
