Totalul general iese dublu: SUM adună și rândurile de subtotal
Dacă totalul general e de două ori mai mare decât ar trebui, SUM adună și rândurile de subtotal din interval. Scrie subtotalurile și totalul cu SUBTOTAL(9,...), care ignoră alte SUBTOTAL-uri din interval, sau adună doar subtotalurile.
Explicat de Tudor Andrei Dumitrescu, Senior Software Developer, fondatorul Academiei ProgramatorilorActualizat la
Ce înseamnă
Un tabel cu grupuri (regiuni, luni, departamente) are adesea un rând „Total Nord”, „Total Sud” după fiecare grup și un „Total general” la final. Dacă totalul general este =SUM(C2:C7), intervalul cuprinde și rândurile de subtotal, deci fiecare valoare e adunată de două ori: o dată pe rândul ei și o dată în subtotal.
Funcția SUBTOTAL rezolvă exact asta: când un interval conține alte formule SUBTOTAL, ele sunt sărite. Cu =SUBTOTAL(9,C2:C3) pe rândurile de subtotal și =SUBTOTAL(9,C2:C7) pe totalul general, fiecare vânzare intră o singură dată. Codul 9 înseamnă sumă (109 ignoră și rândurile ascunse).
Alternativele: totalul ca sumă a subtotalurilor (=C4+C7) sau SUMIF pe coloana etichetelor (=SUMIF(A2:A7,"<>Total*",C2:C7)). Data → Subtotal din Excel desktop scrie automat formule SUBTOTAL, tocmai din acest motiv.
De ce apare: cauzele frecvente
1Total general cu SUM peste subtotaluri
C4: =SUM(C2:C3)
C7: =SUM(C5:C6)
C8: =SUM(C2:C7)C4: =SUBTOTAL(9,C2:C3)
C7: =SUBTOTAL(9,C5:C6)
C8: =SUBTOTAL(9,C2:C7)SUBTOTAL sare peste celulele care conțin deja SUBTOTAL, deci subtotalurile nu se mai adună a doua oară.
2Sumă pe toată coloana
=SUM(C:C)=SUMIF(A:A,"<>Total*",C:C)Coloana întreagă include subtotalurile și, uneori, chiar celula totalului (referință circulară).
3Total din subtotaluri
=SUM(C2:C7)=C4+C7Când sunt puține grupuri, adunarea subtotalurilor e cea mai clară.
Repară eroarea, direct în pagină
Totalul general din C8 afișează 8900, deși vânzările adunate sunt 4450, pentru că =SUM(C2:C7) adună și subtotalurile din C4 și C7. Repară foaia ca C4 să rămână 2700, C7 1750, iar C8 să afișeze 4450, toate cu formule.
Indiciu
Există o funcție de sumă care sare peste alte subtotaluri din interval.
Reparația
Scrie subtotalurile și totalul cu SUBTOTAL:
C4: =SUBTOTAL(9,C2:C3)
C7: =SUBTOTAL(9,C5:C6)
C8: =SUBTOTAL(9,C2:C7)Sau lasă subtotalurile și scrie în C8 =C4+C7. C8 afișează 4450.
De ce merge
SUM(C2:C7) aduna vânzările (2700 + 1750) și apoi încă o dată subtotalurile lor, adică 8900. SUBTOTAL ignoră celulele din interval care conțin la rândul lor SUBTOTAL, așa că în C8 intră doar cele patru vânzări: 1200 + 1500 + 800 + 950 = 4450.
Alte forme ale mesajului
SUM double counting subtotalsExcel grand total doubleSUBTOTAL ignore subtotalsTotalul general e dubluExcel total includes subtotals
Cum o eviți pe viitor
- Folosește SUBTOTAL(9,...) pentru toate subtotalurile și totalurile dintr-un tabel cu grupuri.
- Sau ține datele brute într-un tabel fără rânduri de total și fă rapoartele cu PivotTable sau SUMIFS.
- Verifică totalul general cu o sumă calculată altfel (de exemplu din bara de stare, selectând doar rândurile de date).
Întrebări frecvente
Ce înseamnă 9 din SUBTOTAL(9,...)?
Este codul funcției: 9 = SUM, 1 = AVERAGE, 2 = COUNT, 4 = MAX. Codurile cu 100 în față (109, 101) ignoră și rândurile ascunse manual.
SUBTOTAL ignoră și subtotalurile scrise cu SUM?
Nu. Ignoră doar celulele care conțin formula SUBTOTAL. Dacă subtotalurile sunt cu SUM, schimbă-le și pe ele în SUBTOTAL.
Erori înrudite
SUM adună și rândurile ascunse sau filtrate SUM adună toate celulele din interval, inclusiv rândurile ascunse cu Hide sau de un filtru. Pentru totalul doar al rândurilor vizibile folosește SUBTOTAL: =SUBTOTAL(109,B2:B8) ignoră rândurile ascunse manual și pe cele filtrate (=SUBTOTAL(9,…) le ignoră doar pe cele filtrate). There are one or more circular references where a formula refers to its own cell either directly or indirectly. Referința circulară înseamnă că formula se folosește pe ea însăși, direct (=SUM(B2:B7) în B7) sau printr-un lanț de formule. Excel nu poate calcula rezultatul și afișează 0 sau o valoare veche. Scoate celula formulei din propriul interval: =SUM(B2:B6). SUM nu include rândul adăugat (intervalul nu s-a extins) Dacă adaugi date sub ultimul rând al unui interval (sau imediat deasupra totalului, fără Insert), formula =SUM(B2:B5) nu se extinde singură. Corectează intervalul (=SUM(B2:B6)) sau transformă datele în tabel Excel (Ctrl+T), care include automat rândurile noi. Totalul nu se potrivește cu suma valorilor afișate (rotunjire) Formatul cu 2 zecimale doar ascunde zecimalele, nu le elimină: SUM adună valorile reale, nu cele afișate, așa că totalul poate diferi cu un ban de suma pe care o vezi. Rotunjește chiar valorile cu ROUND, de exemplu =ROUND(B2*1.19,2), înainte să le aduni. Învață Excel de la zero, cu exerciții la fiecare lecție
Curs Excel: lecții scurte, fiecare cu un exercițiu verificat automat, direct în browser. Primele 20 lecții sunt gratuite.
