Generisk formel
=SUM(INDEX(range,N(IF(1,(1,2,3)))))
Resumé
For at få INDEX til at returnere en række elementer til en anden funktion, kan du bruge et uklart trick baseret på IF- og N-funktionerne. I det viste eksempel er formlen i E5:
=SUM(INDEX(data,N(IF(1,(1,2,3)))))
hvor "data" er det navngivne område B5: B10.
Forklaring
Det er overraskende vanskeligt at få INDEX til at returnere mere end en værdi til en anden funktion. For at illustrere kan følgende formel bruges til at returnere de første tre emner i det navngivne interval "data", når de indtastes som en formel med flere celler.
(=INDEX(data,(1,2,3)))
Resultaterne kan ses i området D10: F10, som korrekt indeholder 10, 15 og 20.
Men hvis vi pakker formlen i SUM-funktionen:
=SUM(INDEX(data,(1,2,3)))
Det endelige resultat er 10, mens det skal være 45, selvom det indtastes som en matrixformel. Problemet er, at INDEX kun returnerer det første element i arrayet til SUM-funktionen. For at tvinge INDEX til at returnere flere varer til SUM kan du pakke arraykonstanten i N- og IF-funktionerne på denne måde:
=SUM(INDEX(data,N(IF(1,(1,2,3)))))
der returnerer et korrekt resultat på 45. Tilsvarende denne formel:
=SUM(INDEX(data,N(IF(1,(1,3,5)))))
returnerer korrekt 60, summen af 10, 20 og 30.
Denne uklare teknik kaldes undertiden "dereferencing", fordi den forhindrer INDEX i at håndtere resultater som cellereferencer og efterfølgende slippe alt undtagen det første element i arrayet. I stedet leverer INDEX et komplet udvalg af værdier til SUM i. Jeff Weir har en god forklaring her på stackoverflow.
Bemærk til læserne: Jeg er ikke helt sikker på, hvorfor dette fungerer. Hvis du kan give en klar forklaring, opdaterer jeg, så den inkluderer.
