Uporaba INDEX za ekstrahiranje vrednosti iz rezultata formule matrike Excel

Vse formule matrik ne vrnejo matrik z več stolpci in/ali več vrsticami na delovni list. Toda ko to storijo, se lahko zgodi, da vas zanima samo ena vrednost v matriki. Pri tem lahko uporabite Excelovo funkcijo INDEX.

Na primer, LINEST je ena od funkcij delovnega lista, ki bo pravilno delovala le, če v matriko vnesete formulo, ki vsebuje funkcijo. Toda predpostavimo, da želite dostop do samo ene vrednosti celice v rezultatih LINEST, morda za prilagoditev postavitve delovnega lista v rutinsko poročilo. V tem primeru ne želite nujno celotnega nabora rezultatov LINEST in lahko uporabite Excelovo funkcijo INDEX, da izvlečete in prikažete samo tistega, ki ga želite prikazati.

Tako lahko na primer vnesete matriko LINEST za večkratno regresijo:

=LINEST(A2:A51,B2:D51,,TRUE)

Če to formulo vnesete z nizom v obsegu 5 vrstic za 4 stolpce, presečišče tretje vrstice in prvega stolpca tega obsega vsebuje vrednost R-kvadrata regresije. Torej, če izberete samo eno celico in vnesete naslednjo formulo, boste dobili samo vrednost R-kvadrata:

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

Tukaj dobavljate INDEX z nizom vrednosti, ki jih vrne funkcija LINEST. To je prvi argument za INDEX. Drugi in tretji argument za INDEX sta številki 3 in 1, ki navodita INDEX, naj poišče vrednost v tretji vrstici in prvem stolpcu matrike in jo vrne na delovni list.

Celotno formulo INDEX lahko vnesete kot običajno, z nizom rezultatov LINEST kot prvim argumentom, brez kombinacije Ctrl in Shift in Enter – torej brez vnosa v matriko. (Poskusite v obeh smereh, tako matrika vnesete in vnesete normalno.)

In vendar, če poskusite vnesti naslednjo formulo matrike z eno celico, se pojavi napaka #VALUE! če ga poskusite normalno vnesti:

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

Ko formula pokliče funkcijo, za katero Excel pričakuje, da bo vzela matriko kot argument, lahko formulo vnesete normalno. Tako je s to formulo:

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

Rezultati LINEST so ugnezdeni znotraj funkcije INDEX, kjer delujejo kot njen prvi argument. Excel pričakuje, da bo INDEX vzel matriko vrednosti kot prvi argument – ​​razčlenjevanje matrike je tisto, za kar je bil INDEX rojen. Formule, kot je podana, torej ni treba vnesti v matriko.

Nasprotno pa je treba to formulo matrike ene celice vnesti v matriko:

=POVPREČNO(ČE(A2:A25="Zig",B2:B25,""))

V tem primeru Excel ne pričakuje, da bo funkcija IF vzela niz vrednosti kot argument, vendar tukaj ne predstavljamo enega, temveč dveh nizov vrednosti IF: obseg A23:A25 in B2:B25. (Lahko celo zavzamete stališče, da obstaja niz 24 primerkov »«, ki jih implicirata prva dva argumenta.) Ker formula ne ustreza prvotnemu Excelovemu pričakovanju argumentov za IF, morate Excel opozoriti na situacijo, in to storite tako, da vnesete formulo v matriko.