Prenesite primer delovnega zvezka
Ta vadnica bo pokazala, kako izračunati vmesne vsote po skupinah s funkcijo SUMIFS v Excelu in Google Preglednicah.
Tabela vmesnih seštevkov po kategorijah ali skupinah
Najprej bomo pokazali, kako ustvariti dinamično povzetek tabele vmesnih seštevkov iz obsega podatkov v Excelu 365 naprej ali v Google Preglednicah.
Funkcijo UNIQUE in SUMIFS uporabljamo za samodejni vmesni seštevek Število izdelkov avtor: Skupina izdelkov:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Za ustvarjanje tabele vmesnih seštevkov uporabljamo standardno aplikacijo funkcije SUMIFS za seštevanje Število izdelkov ki se ujemata vsaka Skupina izdelkov. Preden je to mogoče, moramo sestaviti seznam edinstvenih Skupine izdelkov. Uporabniki Microsoft Excel 365 in Google Preglednic imajo dostop do funkcije UNIQUE za ustvarjanje dinamičnega seznama edinstvenih vrednosti iz obsega celic. V tem primeru v celico E3 dodamo naslednjo formulo:
| 1 | = ENOTNO (B3: B11) |
Ko vnesete to formulo, se pod celico samodejno ustvari seznam, ki prikazuje vse edinstvene vrednosti, najdene v Skupina izdelkov obseg podatkov. V tem primeru se je seznam razširil na E3: E5 in prikazal vse 3 edinstvene Skupina izdelkov vrednote.
To je funkcija dinamičnega niza, pri kateri velikosti seznama rezultatov ni treba določiti in se bo samodejno skrčila in rasla, ko se bodo spremenile vrednosti vhodnih podatkov.
Upoštevajte, da v Excelu 365 funkcija UNIQUE ne razlikuje velikih in malih črk, v Google Preglednicah pa je. Upoštevajte seznam {“A”; "A"; "B"; "C"}. Izhod funkcije UNIQUE je odvisen od programa:
- {"A"; "B"; “C”} v Excelu 365
- {"A"; "A"; "B"; »C«} v Google Preglednicah
Če uporabljate različico Excela pred Excelom 365, boste morali uporabiti drugačen pristop. O tem je govora v naslednjem razdelku.
Tabela vmesnih seštevkov po kategorijah ali skupinah - pred Excelom 365
Če uporabljate različico Excela pred Excelom 365, funkcija UNIQUE ni na voljo za uporabo. Če želite ponoviti isto vedenje, lahko združite funkcijo INDEX in funkcijo MATCH s funkcijo COUNTIF, da ustvarite matrično formulo za izdelavo seznama edinstvenih vrednosti iz obsega celic:
| 1 | {= INDEX ($ B $ 3: $ B $ 11, MATCH (0, COUNTIF ($ E $ 2: E2, $ B $ 3: $ B $ 11), 0))} |
Da bi ta formula delovala, je treba skrbno zapisati sklice na fiksne celice, pri čemer se funkcija COUNTIF sklicuje na obseg $ E $ 2: E2, ki je obseg od E2 do celice nad celico, ki vsebuje formulo.
Formulo je treba vnesti tudi kot formulo matrike s pritiskom na CTRL + SHIFT + ENTER, potem ko je napisana. Ta formula je a Formula enocelične matrike, ki jih lahko nato kopirate v celice E4, E5 itd. Tega ne vnesite kot formulo matrike za celotno območje E3: E5 v enem dejanju.
Na enak način kot v prejšnjem primeru se funkcija SUMIFS nato uporabi za vmesni seštevek Število izdelkov avtor: Skupina izdelkov:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Vsota po kategoriji ali skupini - vmesne vsote v podatkovnih tabelah
Kot alternativo zgoraj prikazani metodi zbirne tabele lahko dodamo vmesne vsote neposredno v podatkovno tabelo. To bomo pokazali z uporabo funkcij IF skupaj s funkcijo SUMIFS za dodajanje Vmesni seštevek po skupinah v izvirno podatkovno tabelo.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Ta primer uporablja funkcijo SUMIFS, ugnezdeno v funkciji IF. Primer razčlenimo na korake:
Če želite povzetek statistike dodati neposredno v podatkovno tabelo, lahko uporabimo funkcijo SUMIFS. Začnemo s seštevanjem Število izdelkov ki se ujemajo z ustreznimi Skupina izdelkov:
| 1 | = SUMIFS (C3: C11, B3: B11, B3) |
Ta formula ustvari vrednost vmesnega seštevka za vsako podatkovno vrstico. Za prikaz vmesnih seštevkov samo v prvi podatkovni vrstici vsake Skupina izdelkov, uporabljamo funkcijo IF. Upoštevajte, da morajo biti podatki že razvrščeni po Skupina izdelkov za zagotovitev pravilnega prikaza vmesnih seštevkov.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Funkcija IF primerja vsako vrstico podatkov Skupina izdelkov vrednost z vrstico podatkov nad njo, in če imajo enako vrednost, prikaže prazno celico (»«).
Če je Skupina izdelkov vrednosti so različne, prikaže se vsota. Na ta način, vsak Skupina izdelkov vsota je prikazana samo enkrat (v vrstici prve stopnje).
Razvrščanje naborov podatkov po skupinah
Če podatki še niso razvrščeni, lahko za vmesni seštevek še vedno uporabimo isto formulo.
Zgornji nabor podatkov ni razvrščen po Skupina izdelkov, torej Vmesni seštevek po skupinah stolpec prikazuje vsak vmesni seštevek več kot enkrat. Če želimo podatke dobiti v želeni obliki, lahko izberemo podatkovno tabelo in kliknemo »Razvrsti od A do Ž«.
Zaklepanje referenc celic
Za lažje branje naših formul smo prikazali nekatere formule brez zaklenjenih sklicev na celice:
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Toda te formule ne bodo delovale pravilno, če jih kopirate in prilepite drugam v datoteko. Namesto tega uporabite zaklenjene sklice na celice, kot je ta:
| 1 | = IF (B3 = B2, "", SUMIFS ($ C $ 3: $ C $ 11, $ B $ 3: $ B $ 11, B3)) |
Če želite izvedeti več, preberite naš članek o zaklepanju referenc celic.
Uporaba vrtilnih tabel za prikaz vmesnih seštevkov
Za odstranitev zahteve po predhodnem razvrščanju podatkov po Skupina izdelkov, namesto da povzamemo podatke, lahko uporabimo moč vrtilnih tabel. Vrtilne tabele samodejno izračunajo vmesne vsote in prikažejo vsote in vmesne vsote v več različnih oblikah.
Vsota po kategorijah ali skupinah v Google Preglednicah
Te formule delujejo enako v Google Preglednicah kot v Excelu. Vendar pa funkcija UNIQUE v Google Preglednicah razlikuje velike in male črke.
