Vispārēja formula
=SUM(firstcell:INDEX(data,rows,cols))
Kopsavilkums
Lai definētu diapazonu, pamatojoties uz vērtību citā šūnā, varat izmantot funkciju INDEX. Parādītajā piemērā formula J7 ir:
=SUM(C5:INDEX(data,J5,J6))
kur "dati" ir nosauktais diapazons B5: G9.
Paskaidrojums
Šī formula balstās uz noteiktu INDEX darbību - lai gan šķiet, ka INDEX atgriež vērtību noteiktā vietā, tā faktiski atgriež atsauci uz vietu. Lielākajā daļā formulu jūs nepamanīsit atšķirību - Excel vienkārši novērtē atsauci un atgriež vērtību. Šī formula izmanto šo funkciju, lai izveidotu dinamisko diapazonu, pamatojoties uz darblapas ievadi.
Funkcijas summa iekšpusē pirmā atsauce ir vienkārši pirmā šūna diapazonā, kas aptver visas iespējamās šūnas:
=SUM(C5:
Lai iegūtu pēdējo šūnu, mēs izmantojam INDEX. Šeit mēs piešķiram INDEX nosaukto diapazonu "dati", kas ir maksimāli iespējamais vērtību diapazons, kā arī vērtības no J5 (rindas) un J6 (kolonnas). INDEX neatgriež diapazonu, tajā tiek atgriezta tikai viena šūna, piemērā E9:
INDEX(data,J5,J6) // returns E9
Sākotnējā formula ir samazināta līdz:
=SUM(C5:E9)
kas atgriež 300, visu vērtību summa C5: E9.
J8 formula ir gandrīz vienāda, taču vidējās vērtības aprēķināšanai SUM vietā izmanto AVERAGE. Kad lietotājs maina J5 vai J6 vērtības, diapazons tiek atjaunināts un tiek atgriezti jauni rezultāti.
Alternatīva ar OFFSET
Varat izveidot līdzīgas formulas ar funkciju OFFSET, kas parādīta zemāk:
=SUM(OFFSET(C5,0,0,J5,J6)) // sum =AVERAGE(OFFSET(C5,0,0,J5,J6)) // average
OFFSET ir paredzēts diapazona atgriešanai, tāpēc formulas ir varbūt vienkāršāk saprotamas. Tomēr OFFSET ir nepastāvīga funkcija, un tā var radīt veiktspējas problēmas, ja to izmanto lielākās, sarežģītākās darblapās.
