Použití INDEX k extrahování hodnoty z výsledku vzorců pole Excel

Ne všechny maticové vzorce vracejí do listu pole s více sloupci a/nebo více řádky. Ale když se tak stane, může se stát, že vás bude zajímat pouze jedna hodnota v poli. K tomu můžete použít funkci INDEX aplikace Excel.

Například funkce LINREGRESE je jednou z funkcí listu, která bude správně fungovat, pouze pokud zadáte do pole vzorec obsahující funkci. Předpokládejme však, že chcete ve výsledcích funkce LINREGRESE získat přístup pouze k jedné hodnotě buňky, možná kvůli přizpůsobení rozložení listu v rutinní sestavě. V takovém případě nutně nepotřebujete úplnou sadu výsledků LINREGRESE a můžete použít funkci INDEX Excelu k vyjmutí a zobrazení pouze toho, který vás zajímá.

Zde je například návod, jak můžete zadat pole LINREGRESE pro vícenásobnou regresi:

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

Pokud tento vzorec zadáte do pole v rozsahu 5 řádků na 4 sloupce, průsečík třetího řádku a prvního sloupce tohoto rozsahu obsahuje hodnotu R-kvadrát regrese. Pokud tedy vyberete pouze jednu buňku a zadáte následující vzorec, získáte pouze hodnotu R na druhou:

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

Zde dodáváte INDEXu pole hodnot vrácených funkcí LINREGRESE. To je první argument pro INDEX. Druhým a třetím argumentem INDEXu jsou čísla 3 a 1, která INDEXu přikazují, aby našel hodnotu ve třetím řádku a prvním sloupci pole a vrátil ji do listu.

Můžete zadat celý vzorec INDEX tak, jak je právě uveden normálně, s polem výsledků LINREGRESE jako jeho prvním argumentem, bez kombinace Ctrl a Shift a Enter – tedy bez zadávání pole. (Zkuste to oběma způsoby, jak pole zadávat, tak normálně.)

A přesto, když se pokusíte zadat následující jednobuňkový maticový vzorec, zobrazí se chyba #HODNOTA! pokud to zkusíte zadat normálně:

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

Když vzorec zavolá funkci, od které Excel očekává, že vezme pole jako argument, lze vzorec zadat normálně. To je případ tohoto vzorce:

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

Výsledky LINREGRESE jsou vnořeny do funkce INDEX, kde fungují jako její první argument. Excel očekává, že INDEX vezme pole hodnot jako svůj první argument – ​​k analýze pole se INDEX zrodil. Takže vzorec, jak je uveden, nemusí být zadán do pole.

Naproti tomu tento maticový vzorec jedné buňky musí být zadán do pole:

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

V tomto případě Excel neočekává, že funkce KDYŽ bude mít jako argument pole hodnot, ale zde pro KDYŽ nepředkládáme jedno, ale dvě pole hodnot: rozsah A23:A25 a B2:B25. (Můžete dokonce zaujmout postoj, že existuje pole 24 instancí „“ implikovaných prvními dvěma argumenty.) Protože vzorec nesplňuje původní očekávání Excelu ohledně argumentů IF, musíte Excel upozornit na situaci, a uděláte to zadáním vzorce do pole.