Formula generică
=IF(A2=A1,"",SUMIF(A:A,A2,B:B))
rezumat
Pentru subtotalul datelor pe grup sau etichetă, direct într-un tabel, puteți utiliza o formulă bazată pe funcția SUMIF.
În exemplul prezentat, formula din D5 este:
=IF(B5=B4,"",SUMIF(B:B,B5,C:C))
Notă: datele trebuie să fie sortate după coloana de grupare pentru a obține rezultate rezonabile.
Explicaţie
Cadrul acestei formule se bazează pe IF, care testează fiecare valoare din coloana B pentru a vedea dacă este aceeași cu valoarea din „celula de mai sus”. Când valorile se potrivesc, formula nu returnează nimic (""). Când valorile sunt diferite, funcția IF apelează SUMIF:
SUMIF(B:B,B5,C:C)
În fiecare rând în care SUMIF este declanșat de IF, SUMIF calculează o sumă a tuturor rândurilor potrivite din coloana C (C: C). Criteriile utilizate de SUMIF sunt valoarea curentă a rândului coloanei B (B5), testată în raport cu toate coloanele B (B: B).
Referințele complete ale coloanelor de acest gen sunt cool și elegante, deoarece nu trebuie să vă faceți griji cu privire la locul unde încep și se termină datele, dar trebuie să vă asigurați că nu există date suplimentare deasupra sau dedesubtul tabelului care ar putea fi surprinse de SUMIF
Performanţă
Ar putea părea că utilizarea unei referințe complete a coloanei este o idee proastă, deoarece versiunile actuale de Excel conțin mai mult de 1m rânduri. Cu toate acestea, testarea a arătat că Excel evaluează doar datele din „intervalul folosit” (A1 la adresa intersecției ultimei coloane utilizate și a ultimului rând utilizat) cu acest tip de formulă.
Charles Williams de la Fast Excel are un articol bun despre acest subiect, cu un set complet de rezultate de sincronizare.
De ce despre tabelele pivot?
Acest exemplu se referă la modul în care funcționează referințele coloane complete și modul în care pot fi utilizate direct într-un tabel de date. Tabelele pivot rămân o modalitate excelentă de grupare și rezumare a datelor.
