Získejte umístění hodnoty ve 2D poli

Chcete -li vyhledat polohu hodnoty ve 2D poli, můžete použít funkci SUMPRODUCT. V uvedeném příkladu vzorce používané k vyhledání čísel řádků a sloupců maximální hodnoty v poli jsou:

= SUMPRODUCT ((data= MAX (data))* ROW (data))- ROW (data)+1

kde „data“ jsou pojmenovaný rozsah C5: G14.

Poznámka: pro tento příklad jsme libovolně našli 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.

vzorec pro výpočet věku v aplikaci Excel

K získání čísla řádku jsou data porovnána s maximální hodnotou, která generuje pole SKUTEČNÝCH NEPRAVDIVÝCH výsledků. Ty jsou vynásobeny výsledkem ROW (data), které generuje a pole čísel řádků spojených s pojmenovaným rozsahem 'data':

 
= SUMPRODUCT ((data= MAX (data))* ROW (data))- ROW (data)+1 = SUMPRODUCT ((data= MAX (data))* COLUMN (data))- COLUMN (data)+1

Operace násobení způsobí, že Excel vynucuje hodnoty TRUE FALSE v prvním poli na 1 s a 0 s, takže můžeme mezikrok vizualizovat takto:

vzorec pro oddělení textu v aplikaci Excel

 
= SUMPRODUCT ({FALSE,FALSE,FALSE,FALSE,FALSEFALSE,FALSE,FALSE,FALSE,FALSEFALSE,FALSE,FALSE,FALSE,FALSEFALSE,FALSE,FALSE,FALSE,FALSEFALSE,FALSE,TRUE,FALSE,FALSEFALSE,FALSE,FALSE,FALSE,FALSEFALSE,FALSE,FALSE,FALSE,FALSE}*{567891011})

SUMPRODUCT pak vrátí výsledek 9, který odpovídá 9. řádku na listu. K získání indexu relativního k pojmenovanému rozsahu 'data' používáme:

 
= SUMPRODUCT ({0,0,0,0,00,0,0,0,00,0,0,0,00,0,0,0,00,0,1,0,00,0,0,0,00,0,0,0,0}*{567891011})

Konečným výsledkem je pole {543210-1}, ze kterého se zobrazuje 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 Mike Ericksona na MrExcel.com. V tomto vlákně je také několik dalších dobrých nápadů, včetně možnosti vzorce pole.