Bendroji formulė
=SUM(firstcell:INDEX(data,rows,cols))
Santrauka
Norėdami apibrėžti diapazoną pagal kitos ląstelės vertę, galite naudoti INDEX funkciją. Parodytame pavyzdyje formulė J7 yra:
=SUM(C5:INDEX(data,J5,J6))
kur „duomenys“ yra įvardytas diapazonas B5: G9.
Paaiškinimas
Ši formulė remiasi konkrečiu INDEX elgesiu - nors atrodo, kad INDEX grąžina vertę konkrečioje vietoje, ji iš tikrųjų grąžina nuorodą į vietą. Daugumoje formulių nepastebėsite skirtumo - „Excel“ tiesiog įvertina nuorodą ir grąžina vertę. Ši formulė naudoja šią funkciją dinaminio diapazono sukūrimui pagal darbalapio įvestį.
Funkcijos „suma“ viduje pirmoji nuoroda yra tiesiog pirmasis langelis diapazone, apimantis visas įmanomas ląsteles:
=SUM(C5:
Norėdami gauti paskutinį langelį, mes naudojame INDEX. Čia mes suteikiame INDEX pavadintą diapazoną „duomenys“, kuris yra didžiausias galimas reikšmių diapazonas, taip pat vertes iš J5 (eilutės) ir J6 (stulpeliai). INDEX negrąžina diapazono, šioje vietoje pateikia tik vieną langelį, pavyzdyje E9:
INDEX(data,J5,J6) // returns E9
Pradinė formulė sumažinama iki:
=SUM(C5:E9)
kuri grąžina 300, visų C5: E9 reikšmių sumą.
J8 formulė yra beveik ta pati, tačiau vidurkiui apskaičiuoti vietoj SUM naudojama AVERAGE. Kai vartotojas pakeičia J5 arba J6 reikšmes, diapazonas atnaujinamas ir pateikiami nauji rezultatai.
Alternatyva su OFFSET
Panašias formules galite sukurti naudodamiesi funkcija OFFSET, kuri parodyta žemiau:
=SUM(OFFSET(C5,0,0,J5,J6)) // sum =AVERAGE(OFFSET(C5,0,0,J5,J6)) // average
OFFSET yra skirtas grąžinti diapazoną, todėl formules galbūt paprasčiau suprasti. Tačiau OFFSET yra nepastovi funkcija ir gali sukelti našumo problemų, kai naudojama didesniuose, sudėtingesniuose darbalapiuose.
