Formula generică
=SUM(firstcell:INDEX(data,rows,cols))
rezumat
Pentru a defini un interval bazat pe o valoare dintr-o altă celulă, puteți utiliza funcția INDEX. În exemplul prezentat, formula din J7 este:
=SUM(C5:INDEX(data,J5,J6))
unde „date” este intervalul numit B5: G9.
Explicaţie
Această formulă se bazează pe un comportament specific al INDEX - deși se pare că INDEX returnează valoarea la o anumită locație, de fapt returnează referința la locație. În majoritatea formulelor, nu ați observa diferența - Excel pur și simplu evaluează referința și returnează valoarea. Această formulă utilizează această caracteristică pentru a construi un interval dinamic bazat pe introducerea foii de lucru.
În funcția sumă, prima referință este pur și simplu prima celulă din intervalul care acoperă toate celulele posibile:
=SUM(C5:
Pentru a obține ultima celulă, folosim INDEX. Aici, oferim INDEX intervalul numit „date”, care este intervalul maxim posibil de valori, precum și valorile din J5 (rânduri) și J6 (coloane). INDEX nu returnează un interval, returnează doar o singură celulă în acea locație, E9 în exemplu:
INDEX(data,J5,J6) // returns E9
Formula originală este redusă la:
=SUM(C5:E9)
care returnează 300, suma tuturor valorilor din C5: E9.
Formula din J8 este aproape aceeași, dar folosește MEDIA în loc de SUMĂ pentru a calcula o medie. Când un utilizator modifică valorile în J5 sau J6, intervalul este actualizat și se returnează rezultate noi.
Alternativă cu OFFSET
Puteți crea o formulă similară cu funcția OFFSET, prezentată mai jos:
=SUM(OFFSET(C5,0,0,J5,J6)) // sum =AVERAGE(OFFSET(C5,0,0,J5,J6)) // average
OFFSET este conceput pentru a returna un interval, astfel încât formulele sunt probabil mai ușor de înțeles. Cu toate acestea, OFFSET este o funcție volatilă și poate provoca probleme de performanță atunci când este utilizat în foi de lucru mai mari și mai complexe.
