Загальна формула
=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. У цьому ланцюжку є ще кілька хороших ідей, включаючи опцію формули масиву.
