Generisk formel
=SUM(firstcell:INDEX(data,rows,cols))
Sammanfattning
För att definiera ett intervall baserat på ett värde i en annan cell kan du använda INDEX-funktionen. I exemplet som visas är formeln i J7:
=SUM(C5:INDEX(data,J5,J6))
där "data" är det namngivna området B5: G9.
Förklaring
Denna formel bygger på ett specifikt beteende hos INDEX - även om det verkar som att INDEX returnerar värdet på en viss plats, returnerar den faktiskt referensen till platsen. I de flesta formler märker du inte skillnaden - Excel utvärderar helt enkelt referensen och returnerar värdet. Denna formel använder den här funktionen för att konstruera ett dynamiskt omfång baserat på kalkylbladets inmatning.
Inom sumfunktionen är den första referensen helt enkelt den första cellen i intervallet som täcker alla möjliga celler:
=SUM(C5:
För att få den sista cellen använder vi INDEX. Här ger vi INDEX namngivna intervall "data", vilket är det maximala möjliga intervallet för värden, och även värdena från J5 (rader) och J6 (kolumner). INDEX returnerar inte ett intervall, det returnerar bara en enda cell på den platsen, E9 i exemplet:
INDEX(data,J5,J6) // returns E9
Den ursprungliga formeln reduceras till:
=SUM(C5:E9)
som returnerar 300, summan av alla värden i C5: E9.
Formeln i J8 är nästan densamma, men använder AVERAGE istället för SUM för att beräkna ett genomsnitt. När en användare ändrar värden i J5 eller J6 uppdateras intervallet och nya resultat returneras.
Alternativ med OFFSET
Du kan skapa en liknande formel med OFFSET-funktionen, som visas nedan:
=SUM(OFFSET(C5,0,0,J5,J6)) // sum =AVERAGE(OFFSET(C5,0,0,J5,J6)) // average
OFFSET är utformat för att returnera ett intervall, så formlerna är kanske enklare att förstå. OFFSET är dock en flyktig funktion och kan orsaka prestandaproblem när den används i större, mer komplexa kalkylblad.
