Použitie INDEXU na extrahovanie hodnoty z výsledkov vzorcov poľa Excel

Nie všetky vzorce poľa vrátia do pracovného hárka polia s viacerými stĺpcami a/alebo viacerými riadkami. Ale keď tak urobia, môže sa stať, že budete mať záujem vidieť iba jednu hodnotu v poli. Na pomoc s tým môžete použiť funkciu INDEX v Exceli.

Napríklad LINREGRESE je jednou z funkcií pracovného hárka, ktorá bude fungovať správne, iba ak zadáte do poľa vzorec, ktorý obsahuje funkciu. Predpokladajme však, že chcete získať prístup iba k jednej hodnote bunky vo výsledkoch funkcie LINREGRESE, možno na prispôsobenie rozloženia pracovného hárka v rutinnej zostave. V takom prípade nutne nepotrebujete celú množinu výsledkov LINEST a môžete použiť funkciu INDEX v Exceli na vybratie a zobrazenie len toho, ktorý vás zaujíma.

Tu je napríklad postup, ako môžete zadať pole LINEST pre viacnásobnú regresiu:

=LINEST(A2:A51;B2:D51,,PRAVDA)

Ak tento vzorec zadáte do poľa v rozsahu 5 riadkov x 4 stĺpce, priesečník tretieho riadka a prvého stĺpca tohto rozsahu bude obsahovať hodnotu R-kvadratúry regresie. Ak teda vyberiete iba jednu bunku a zadáte nasledujúci vzorec, získate iba hodnotu R:

=INDEX(LINEST(A2:A51;B2:D51,,PRAVDA);3;1)

Tu dodávate INDEXu pole hodnôt vrátených funkciou LINEST. To je prvý argument pre INDEX. Druhým a tretím argumentom pre INDEX sú čísla 3 a 1, ktoré inštruujú INDEX, aby našiel hodnotu v treťom riadku a prvom stĺpci poľa a vrátil ju do pracovného hárka.

Môžete zadať celý vzorec INDEX tak, ako je to normálne zadané, s poľom výsledkov LINREGRESE ako jeho prvým argumentom, bez kombinácie Ctrl a Shift a Enter – teda bez zadávania poľa. (Vyskúšajte to oboma spôsobmi, pole zadáva aj normálne.)

Ak sa však pokúsite zadať nasledujúci vzorec poľa s jednou bunkou, zobrazí sa chyba #HODNOTA! ak sa ho pokúsite zadať normálne:

=IF(H44639:H44644>0,G44639:G44644,0)

Keď vzorec zavolá funkciu, od ktorej Excel očakáva, že použije pole ako argument, vzorec možno zadať normálne. To je prípad tohto vzorca:

=INDEX(LINEST(A2:A51;B2:D51,,PRAVDA);3;1)

Výsledky LINREGRESE sú vnorené do funkcie INDEX, kde fungujú ako jej prvý argument. Excel očakáva, že INDEX vezme pole hodnôt ako svoj prvý argument – ​​na analýzu poľa sa INDEX zrodil. Takže vzorec, ako je uvedený, nemusí byť zadaný do poľa.

Na rozdiel od toho tento vzorec poľa s jednou bunkou musí byť zadaný do poľa:

=AVERAGE(IF(A2:A25="Zig",B2:B25,""))

V tomto prípade Excel neočakáva, že funkcia IF bude brať pole hodnôt ako argument, ale tu uvádzame nie jedno, ale dve polia hodnôt pre IF: rozsah A23:A25 a B2:B25. (Môžete dokonca zaujať stanovisko, že prvé dva argumenty naznačujú pole 24 inštancií „“.) Pretože vzorec nespĺňa pôvodné očakávania Excelu týkajúce sa argumentov IF, musíte Excel upozorniť na situáciu, a urobíte to zadaním vzorca do poľa.