Všeobecný vzorec
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
Zhrnutie
Na vyhľadanie polohy hodnoty v 2D poli môžete použiť funkciu SUMPRODUCT. V zobrazenom príklade sú vzorce použité na vyhľadanie čísel riadkov a stĺpcov maximálnej hodnoty v poli:
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1
kde „údaj“ je pomenovaný rozsah C5: G14.
Poznámka: V tomto príklade svojvoľne nájdeme umiestnenie maximálnej hodnoty v dátach, ale údaj = MAX (dáta) môžete nahradiť akýmkoľvek iným logickým testom, ktorý izoluje danú hodnotu. Upozorňujeme, že tieto vzorce zlyhajú, ak sú v poli duplicitné hodnoty.
Vysvetlenie
Ak chcete získať číslo riadku, údaje sa porovnajú s maximálnou hodnotou, ktorá vygeneruje pole výsledkov TRUE FALSE. Tieto sa vynásobia výsledkom ROW (údaje), ktoré generuje a pole čísel riadkov spojených s pomenovaným rozsahom „údaje“:
=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))
Operácia násobenia spôsobí, že program Excel vynúti hodnoty TRUE FALSE v prvom poli na 1 s a 0 s, takže môžeme vizualizovať medzikrok 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 potom vráti výsledok 9, ktorý zodpovedá 9. riadku v hárku. Na získanie indexu vo vzťahu k pomenovanému rozsahu „data“ používame:
-ROW(data)+1
Konečným výsledkom je pole (5; 4; 3; 2; 1; 0; -1), z ktorého sa zobrazí iba prvá hodnota (5).
Rovnakým spôsobom funguje vzorec na určenie polohy stĺpca.
Poznámka: Na tento prístup som narazil v komentári Mika Ericksona na MrExcel.com. V tomto vlákne sú aj niektoré ďalšie dobré nápady, vrátane možnosti maticového vzorca.
