Med hjälp av INDEX för att extrahera ett värde från en Excel Array Formula resultat - dummies

Inte alla arrayformler returnerar arrayer med flera kolumner och / eller flera rader till arbetsbladet. Men när de gör det kan det hända att du är intresserad av att bara se ett värde i matrisen. Du kan använda Excels INDEX-funktion för att hjälpa till med det.

LINEST är till exempel en av kalkylbladsfunktionerna som bara fungerar korrekt om du matar in - mata in den formel som innehåller funktionen. Men anta att du bara vill ha tillgång till ett cellvärde i LINEST-resultaten, kanske för att rymma ett arbetsbladslayout i en rutinrapport. I det fallet vill du inte nödvändigtvis ha den fullständiga uppsättningen LINEST-resultat, och du kan använda Excels INDEX-funktion för att plocka ut och visa bara den du är intresserad av att visa.

Så här kan du för exempel mata in LINEST för en multipelregression:

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

Om du matar in den här formeln i en 5 rad med 4 kolumnintervall innehåller korsningen av det intervallets tredje rad och första kolumnen regressionens R-kvadrerade värde. Så om du bara väljer en enda cell och anger följande formel får du bara R-kvadrerat värde:

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

Här levererar du INDEX med värdena som returneras av LINEST-funktionen. Det är det första argumentet till INDEX. Det andra och tredje argumentet till INDEX är siffrorna 3 och 1, som instruerar INDEX för att hitta värdet i tredje raden och första kolumnen i arrayen och returnera den till arbetsbladet.

Du kan ange den fullständiga INDEX-formuläret som bara givet normalt, med en rad LINEST-resultat som sitt första argument, utan Ctrl och Shift och Enter-kombinationen - det vill säga utan att mata in det. (Prova det båda sätten, båda matriserna anger det och anger det normalt.)

Och om du försöker skriva in följande encells array formel ger den felet #VALUE! Om du försöker skriva in det normalt:

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

När formeln kallar en funktion som Excel förväntar sig att ta en array som ett argument kan formeln inkommit normalt. Det här är fallet med denna formel:

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

LINEST-resultaten är nestade inom INDEX-funktionen, där de fungerar som första argument. Excel förväntar sig att INDEX tar en rad värden som sitt första argument - att analysera en array är vad INDEX föddes att göra. Så formeln som ges behöver inte matas in.

I motsats härtill måste denna enkelcells arrayformel matas in:

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

Excel gör i detta fall Förvänta inte att IF-funktionen kommer att ta en rad värden som ett argument, men här presenterar vi inte en men två värdesätt till IF: intervallet A23: A25 och B2: B25.(Du kan till och med ta ställningen att det finns en uppsättning av 24 instanser av "" som indikeras av de två första argumenten.) Eftersom formuläret inte uppfyller Excels ursprungliga förväntningar på argumenten till IF måste du rita exakthetens uppmärksamhet på situationen, och du gör det genom att mata in i formeln.