Ved hjelp av INDEX for å trekke ut en verdi fra en Excel Array Formula resultat - dummies

Ikke alle arrayformler returnerer arrays med flere kolonner og / eller flere rader til regnearket. Men når de gjør det, kan det hende at du er interessert i å se bare én verdi i gruppen. Du kan bruke Excels INDEX-funksjon for å hjelpe deg med det.

LINEST er for eksempel en av regnearksfunksjonene som bare vil fungere hvis du angir array - skriv inn formelen som inneholder funksjonen. Men antar at du vil ha tilgang til bare en celleverdi i LINEST-resultatene, kanskje for å imøtekomme et regnearkoppsett i en rutinemessig rapport. I så fall ønsker du ikke nødvendigvis hele settet av LINEST-resultater, og du kan bruke Excels INDEX-funksjon for å plukke ut og vise bare den du er interessert i å vise.

Her kan du for eksempel angi LINEST for flere regresjon:

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

Hvis du angir array den formelen i en 5 rad med 4 kolonneintervall inneholder krysset mellom det tredje radets og den første kolonnens regresjonens R-kvadrert verdi. Så hvis du velger bare en enkelt celle og skriver inn følgende formel, får du kun R-kvadrert verdien:

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

Her leverer du INDEX med en rekke verdier returnert av LINEST-funksjonen. Det er det første argumentet til INDEX. Det andre og tredje argumentet til INDEX er tallene 3 og 1, som instruerer INDEX for å finne verdien i tredje rad og første kolonne i arrayet og returnere den til regnearket.

Du kan skrive inn hele INDEX-formelen som bare gitt normalt, med en rekke LINEST-resultater som det første argumentet, uten Ctrl og Shift og Enter-kombinasjonen - det vil si uten å matche inn det. (Prøv det begge veier, begge gruppene skriver inn det og skriver det normalt.)

Og likevel, hvis du prøver å skrive inn en enkelt-celle array formel, produserer den feilen #VALUE! Hvis du prøver å skrive det normalt:

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

Når formelen kaller en funksjon som Excel forventer å ta en matrise som et argument, kan formelen være angitt normalt. Det er tilfellet med denne formelen:

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

LINEST resultatene er nestet i INDEX-funksjonen, hvor de fungerer som sin første argument. Excel forventer at INDEX skal ta en rekke verdier som det første argumentet - å analysere en matrise er hva INDEX ble født til å gjøre. Så formelen som gitt trenger ikke å være innstilt.

I motsetning må denne enkeltcelleoppsettformelen være innstilt:

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

I dette tilfellet gjør Excel ikke forvent at IF-funksjonen vil ta en rekke verdier som et argument, men her presenterer vi ikke en, men bare to, verdiverdier til IF: rekkevidde A23: A25 og B2: B25.(Du kan til og med ta stilling til at det er en rekke 24 forekomster av "" som er underforstått av de to første argumentene.) Fordi formelen ikke oppfyller Excels opprinnelige forventning om argumentene til IF, må du tegne Excels oppmerksomhet på situasjonen, og du gjør det ved å matche inn i formelen.