Descărcați exemplul de registru de lucru
Acest tutorial va arăta cum să calculați subtotalurile pe grup folosind funcția SUMIFS în Excel și Foi de calcul Google.
Subtotal Tabel după categorie sau grup
În primul rând, vom arăta cum să creați un tabel rezumat subtotal dinamic dintr-un interval de date fie în Excel 365, fie în Foi de calcul Google.
Folosim funcția UNIQUE și funcția SUMIFS pentru a subtotaliza automat Numărul de produse de Grup de produse:
| 1 | = SUME (C3: C11, B3: B11, E3) |
Pentru a crea acest tabel subtotal, folosim aplicația standard a funcției SUMIFS pentru a însuma Numărul de produse care se potrivesc fiecare Grup de produse. Cu toate acestea, înainte ca acest lucru să fie posibil, trebuie să creăm o listă de elemente unice Grupuri de produse. Utilizatorii Microsoft Excel 365 și Google Sheets au acces la funcția UNIQUE pentru a crea o listă dinamică de valori unice dintr-un interval de celule. În acest exemplu, adăugăm următoarea formulă la celula E3:
| 1 | = UNIC (B3: B11) |
Când se introduce această formulă, se creează automat o listă sub celulă pentru a afișa toate valorile unice găsite în Grup de produse intervalul de date. În acest exemplu, lista sa extins pentru a acoperi E3: E5 pentru a afișa toate cele 3 unice Grup de produse valori.
Aceasta este o funcție din matrice dinamică în care nu este necesară definirea dimensiunii listei de rezultate și se va micșora automat și va crește pe măsură ce valorile datelor de intrare se schimbă.
Rețineți că în Excel 365, funcția UNIQUE nu este sensibilă la majuscule, dar în Foi de calcul Google este. Luați în considerare lista {„A”; "A"; „B”; „C”}. Ieșirea funcției UNIQUE depinde de program:
- {"A"; „B”; „C”} în Excel 365
- {"A"; "A"; „B”; „C”} în Foi de calcul Google
Dacă utilizați o versiune Excel înainte de Excel 365, va trebui să luați o abordare diferită. Acest lucru este discutat în secțiunea următoare.
Tabel subtotal după categorie sau grup - Pre Excel 365
Dacă utilizați o versiune de Excel înainte de Excel 365, funcția UNIQUE nu este disponibilă pentru utilizare. Pentru a reproduce același comportament, puteți combina funcția INDEX și funcția MATCH cu o funcție COUNTIF pentru a crea o formulă matrice pentru a produce o listă de valori unice dintr-o gamă de celule:
| 1 | {= INDEX ($ B $ 3: $ B $ 11, MATCH (0, COUNTIF ($ E $ 2: E2, $ B $ 3: $ B $ 11), 0))} |
Pentru ca această formulă să funcționeze, referințele fixe ale celulei trebuie scrise cu atenție, funcția COUNTIF făcând referire la intervalul $ E $ 2: E2, care este intervalul care începe de la E2 până la celula de deasupra celulei care conține formula.
Formula trebuie, de asemenea, să fie introdusă ca formulă matrice apăsând CTRL + SHIFT + ENTER după ce a fost scrisă. Această formulă este a Formula matricei cu 1 celulă, care poate fi apoi copiat-lipit în celulele E4, E5 etc. Nu introduceți acest lucru ca o formulă matrice pentru întregul interval E3: E5 într-o singură acțiune.
În același mod ca în exemplul anterior, o funcție SUMIFS este apoi utilizată pentru a subtotaliza Numărul de produse de Grup de produse:
| 1 | = SUME (C3: C11, B3: B11, E3) |
Suma după categorie sau grup - subtotale în tabelele de date
Ca alternativă la metoda tabelului sumar prezentat mai sus, putem adăuga subtotale direct într-un tabel de date. Vom demonstra acest lucru folosind funcțiile IF împreună cu funcția SUMIFS pentru a adăuga un Subtotal pe grup la tabelul de date original.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Acest exemplu folosește o funcție SUMIFS imbricată într-o funcție IF. Să împărțim exemplul în pași:
Pentru a adăuga statistici sumare direct într-un tabel de date, putem utiliza funcția SUMIFS. Începem prin a totaliza Numărul de produse care se potrivesc cu cele relevante Grup de produse:
| 1 | = SUME (C3: C11, B3: B11, B3) |
Această formulă produce o valoare subtotală pentru fiecare rând de date. Pentru a afișa subtotale numai în primul rând de date al fiecăruia Grup de produse, folosim funcția IF. Rețineți că datele trebuie să fie deja sortate după Grup de produse pentru a vă asigura că subtotalurile sunt afișate corect.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Funcția IF compară fiecare rând de date Grup de produse valoare cu rândul de date deasupra acestuia și, dacă au aceeași valoare, scoate o celulă goală („”).
Dacă Grup de produse valorile sunt diferite, se afișează suma. În acest fel, fiecare Grup de produse suma este afișată o singură dată (pe rândul primei instanțe).
Sortarea seturilor de date după grup
Dacă datele nu sunt deja sortate, putem folosi aceeași formulă pentru subtotal.
Setul de date de mai sus nu este sortat după Grup de produse, asa ca Subtotal pe grup coloana afișează fiecare subtotal de mai multe ori. Pentru a obține datele în formatul dorit, putem selecta tabelul de date și faceți clic pe „Sortare A la Z”.
Blocarea referințelor celulei
Pentru a simplifica citirea formulelor, am arătat câteva dintre formule fără referințe de celule blocate:
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Dar aceste formule nu vor funcționa corect atunci când sunt copiate și lipite în altă parte a fișierului. În schimb, ar trebui să utilizați referințe de celule blocate ca aceasta:
| 1 | = IF (B3 = B2, "", SUME ($ C $ 3: $ C $ 11, $ B $ 3: $ B $ 11, B3)) |
Citiți articolul nostru despre blocarea referințelor celulei pentru a afla mai multe.
Utilizarea tabelelor pivot pentru a afișa subtotale
Pentru a elimina cerința de a sorta în prealabil datele după Grup de produse, putem folosi puterea tabelelor pivot pentru a rezuma datele. Tabelele pivot calculează subtotalurile automat și afișează totalurile și subtotalurile în mai multe formate diferite.
Suma după categorie sau grup în Foi de calcul Google
Aceste formule funcționează la fel în Foi de calcul Google ca în Excel. Cu toate acestea, funcția UNIQUE este sensibilă la majuscule și minuscule în Foi de calcul Google.
