Excel

Získajte umiestnenie hodnoty v 2D poli

Get Location Value 2d Array

Excel vzorec: Získajte umiestnenie hodnoty v 2D poliGenerický vzorec | _+_ | Zhrnutie

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 bunku
Vysvetlenie

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


^