Formuła ogólna
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
Podsumowanie
Aby zlokalizować pozycję wartości w tablicy 2D, możesz użyć funkcji SUMPRODUCT. W przedstawionym przykładzie formuły używane do lokalizowania numerów wierszy i kolumn maksymalnej wartości w tablicy to:
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1
gdzie „dane” to nazwany zakres C5: G14.
Uwaga: w tym przykładzie arbitralnie znajdujemy lokalizację maksymalnej wartości w danych, ale można zastąpić data = MAX (dane) dowolnym innym testem logicznym, który wyodrębni daną wartość. Należy również pamiętać, że te formuły zakończą się niepowodzeniem, jeśli w tablicy występują zduplikowane wartości.
Wyjaśnienie
Aby uzyskać numer wiersza, dane są porównywane z wartością maksymalną, która generuje tablicę wyników TRUE FALSE. Są one mnożone przez wynik WIERSZ (dane), który generuje tablicę numerów wierszy powiązanych z nazwanym zakresem „dane”:
=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))
Operacja mnożenia powoduje, że program Excel przekształca wartości PRAWDA FAŁSZ w pierwszej tablicy na 1 i 0, więc możemy wizualizować pośredni krok w następujący sposób:
=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 zwraca następnie wynik 9, co odpowiada dziewiątemu wierszowi w arkuszu. Aby uzyskać indeks odnoszący się do nazwanego zakresu „dane”, używamy:
-ROW(data)+1
Ostatecznym wynikiem jest tablica (5; 4; 3; 2; 1; 0; -1), z której wyświetlana jest tylko pierwsza wartość (5).
Formuła określająca położenie kolumny działa w ten sam sposób.
Uwaga: wpadłem na to podejście w komentarzu Mike'a Ericksona na MrExcel.com. W tym wątku są też inne dobre pomysły, w tym opcja formuły tablicowej.
