Yleinen kaava
=SUM(firstcell:INDEX(data,rows,cols))
Yhteenveto
Määritä alue toisen solun arvon perusteella käyttämällä INDEX-toimintoa. Esitetyssä esimerkissä kaava kohdassa J7 on:
=SUM(C5:INDEX(data,J5,J6))
missä "data" on nimetty alue B5: G9.
Selitys
Tämä kaava perustuu INDEXin tiettyyn käyttäytymiseen - vaikka näyttää siltä, että INDEX palauttaa arvon tietyssä paikassa, se tosiasiassa palauttaa viitteen sijaintiin. Useimmissa kaavoissa et huomaa eroa - Excel yksinkertaisesti arvioi viitteen ja palauttaa arvon. Tämä kaava käyttää tätä ominaisuutta dynaamisen alueen rakentamiseen laskentataulukon syötteen perusteella.
Summafunktion sisällä ensimmäinen viite on yksinkertaisesti ensimmäinen solu alueella, joka kattaa kaikki mahdolliset solut:
=SUM(C5:
Viimeisen solun saamiseksi käytämme INDEX-koodia. Tässä annetaan INDEXille nimetty alue "data", joka on suurin mahdollinen arvoalue, ja myös arvot J5 (rivit) ja J6 (sarakkeet). INDEX ei palauta aluetta, se palauttaa vain yhden solun kyseisessä paikassa, esimerkissä E9:
INDEX(data,J5,J6) // returns E9
Alkuperäinen kaava on supistettu:
=SUM(C5:E9)
joka palauttaa 300, kaikkien arvojen summa ryhmässä C5: E9.
J8: n kaava on melkein sama, mutta keskiarvon laskemiseen käytetään keskiarvoa summan sijasta. Kun käyttäjä muuttaa arvoja J5: ssä tai J6: ssa, alue päivitetään ja uudet tulokset palautetaan.
Vaihtoehto OFFSET: n kanssa
Voit rakentaa samanlaiset kaavat OFFSET-toiminnolla, joka näkyy alla:
=SUM(OFFSET(C5,0,0,J5,J6)) // sum =AVERAGE(OFFSET(C5,0,0,J5,J6)) // average
OFFSET on suunniteltu palauttamaan alue, joten kaavat ovat ehkä yksinkertaisempia ymmärtää. OFFSET on kuitenkin epävakaa toiminto, ja se voi aiheuttaa suorituskykyongelmia, kun sitä käytetään suuremmissa, monimutkaisemmissa laskentataulukoissa.
