Obecný vzorec
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
souhrn
Chcete-li vyhledat pozici hodnoty v 2D poli, můžete použít funkci SUMPRODUCT. V zobrazeném příkladu jsou vzorce použité k vyhledání čísel řádků a sloupců maximální hodnoty v poli:
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1
kde „data“ je pojmenovaný rozsah C5: G14.
Poznámka: v tomto příkladu libovolně najdeme umístění maximální hodnoty v datech, ale data = MAX (data) můžete nahradit jakýmkoli jiným logickým testem, který danou hodnotu izoluje. Všimněte si také, že tyto vzorce selžou, pokud jsou v poli duplicitní hodnoty.
Vysvětlení
Chcete-li získat číslo řádku, data se porovná s maximální hodnotou, která generuje pole výsledků TRUE FALSE. Ty jsou vynásobeny výsledkem ROW (data), který generuje a pole čísel řádků asociovaných s pojmenovaným rozsahem „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))
Operace násobení způsobí, že aplikace Excel vynutí hodnoty TRUE FALSE v prvním poli na 1 s a 0 s, takže můžeme vizualizovat přechodný krok takto:
=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 poté vrátí výsledek 9, což odpovídá 9. řádku v listu. K získání indexu vzhledem k pojmenovanému rozsahu „data“ používáme:
-ROW(data)+1
Konečným výsledkem je pole (5; 4; 3; 2; 1; 0; -1), ze kterého se zobrazí pouze první hodnota (5).
Vzorec pro určení polohy sloupce funguje stejným způsobem.
Poznámka: Na tento přístup jsem narazil v komentáři Mika Ericksona na MrExcel.com. V tomto vlákně jsou také některé další dobré nápady, včetně možnosti maticového vzorce.
