Na vyhľadanie polohy hodnoty v 2D poli môžete použiť funkciu SUMPRODUCT. V uvedenom príklade vzorce na vyhľadanie čísel riadkov a stĺpcov maximálnej hodnoty v poli sú tieto:
= SUMPRODUCT ((data= MAX (data))* ROW (data))- ROW (data)+1
kde „údaje“ sú pomenovaný rozsah C5: G14.
Poznámka: v tomto prípade ľubovoľne nachádzame umiestnenie maximálnej hodnoty v dátach, ale údaje = MAX (údaje) môžete nahradiť akýmkoľvek iným logickým testom, ktorý danú hodnotu izoluje. Upozorňujeme, že tieto vzorce nebudú fungovať, ak sa v poli nachádzajú duplicitné hodnoty.
nájsť ďalšiu neprázdnu bunkuVysvetlenie
Na získanie čísla riadka sú údaje porovnané s maximálnou hodnotou, ktorá generuje pole TRUE FALSE výsledkov. Tieto sú vynásobené výsledkom ROW (údajov), ktoré generuje a radu čísel riadkov priradených k pomenovanému rozsahu údajov:
= SUMPRODUCT ((data= MAX (data))* ROW (data))- ROW (data)+1 = SUMPRODUCT ((data= MAX (data))* COLUMN (data))- COLUMN (data)+1
Operácia násobenia spôsobuje, že Excel vynúti PRAVDIVÉ NEPRAVDIVÉ hodnoty v prvom poli na 1 s a 0 s, takže prechodný krok môžeme vizualizovať takto:
zadajte, že vybratá bunka musí obsahovať celé číslo
= 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 potom vráti výsledok 9, ktorý zodpovedá 9. riadku v pracovnom hárku. Na získanie indexu vzhľadom na pomenovaný rozsah „údajov“ používame:
= 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ýsledkom je pole {543210-1}, z ktorého sa zobrazuje iba prvá hodnota (5).
Vzorec na určenie polohy stĺpca funguje rovnako.
Poznámka: Na tento prístup som narazil v komentári od Mike Ericksona na MrExcel.com. V tomto vlákne je aj niekoľko ďalších dobrých nápadov, vrátane možnosti vzorca pre pole.
Autor Dave Bruns