Laadige alla töövihiku näide
See õpetus näitab, kuidas Exceli ja Google'i arvutustabelite funktsiooni SUMIFS abil rühmade kaupa vahesummasid arvutada.
Vahesumma tabel kategooriate või rühmade kaupa
Esiteks demonstreerime, kuidas luua dünaamiline vahekokkuvõtete tabel andmevahemikust kas Excel 365 -st alates või Google'i arvutustabelites.
Kasutame funktsiooni UNIQUE ja funktsiooni SUMIFS, et automaatselt kokku liita Toodete arv kõrval Tooterühm:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Selle vahesumma tabeli loomiseks kasutame summa summeerimiseks standardrakendust SUMIFS Toodete arv mis sobivad igaühega Tooterühm. Kuid enne kui see on võimalik, peame koostama ainulaadsete loendi Tooterühmad. Microsoft Excel 365 ja Google'i arvutustabelite kasutajatel on juurdepääs funktsioonile UNIQUE, et luua lahtrivahemikust ainulaadsete väärtuste dünaamiline loend. Selles näites lisame lahtrisse E3 järgmise valemi:
| 1 | = UNIKAALNE (B3: B11) |
Selle valemi sisestamisel luuakse lahtri alla automaatselt loend, mis näitab kõiki unikaalseid väärtusi Tooterühm andmevahemik. Selles näites laiendati loendit nii, et see hõlmaks ka E3: E5, et kuvada kõik kolm unikaalset Tooterühm väärtused.
See on dünaamilise massiivi funktsioon, kus tulemuste loendi suurust pole vaja määratleda ning see väheneb ja kasvab automaatselt, kui sisendandmete väärtused muutuvad.
Pange tähele, et rakenduses Excel 365 ei ole UNIQUE -funktsioon tõstutundlik, kuid Google'i arvutustabelites küll. Mõelge loendile {“A”; "A"; "B"; “C”}. UNIQUE funktsiooni väljund sõltub programmist:
- {“A”; "B"; “C”} rakenduses Excel 365
- {“A”; "A"; "B"; „C”} Google'i arvutustabelites
Kui kasutate enne Excel 365 Exceli versiooni, peate kasutama teistsugust lähenemist. Seda arutatakse järgmises osas.
Vahesumma tabel kategooria või rühma järgi - enne Excel 365
Kui kasutate Exceli versiooni enne Excel 365, pole funktsioon UNIQUE kasutamiseks saadaval. Sama käitumise kordamiseks võite kombineerida funktsiooni INDEX ja funktsiooni MATCH funktsiooniga COUNTIF, et luua massiivivalem, mille abil saab koostada lahtrivahemiku ainulaadsete väärtuste loendi.
| 1 | {= INDEKS ($ B $ 3: $ B $ 11, MATCH (0, COUNTIF ($ E $ 2: E2, $ B $ 3: $ B $ 11), 0))} |
Selle valemi toimimiseks tuleb fikseeritud lahtri viited hoolikalt kirjutada, kusjuures funktsioon COUNTIF viitab vahemikule $ E $ 2: E2, mis on vahemik alates E2 kuni valemit sisaldava lahtri kohal oleva lahtri.
Valem tuleb sisestada ka massiivivalemina, vajutades CTRL + SHIFT + ENTER pärast selle kirjutamist. See valem on a 1-lahtrimassiivi valem, mille saab seejärel kopeerida lahtritesse E4, E5 jne. Ärge sisestage seda massiivivalemina kogu vahemiku E3: E5 jaoks ühe toiminguna.
Samamoodi nagu eelmises näites, kasutatakse seejärel SUMIFS -funktsiooni Toodete arv kõrval Tooterühm:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Summa kategooriate või rühmade kaupa - andmetabelites vahesummad
Ülaltoodud kokkuvõtva tabeli meetodi alternatiivina saame lisada vahesummad otse andmetabelisse. Me demonstreerime seda, kasutades IF -funktsioone koos funktsiooni SUMIFS lisamiseks a Vahesumma rühmade kaupa algsesse andmetabelisse.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
See näide kasutab funktsiooni SUMIFS, mis on sisestatud IF -funktsiooni. Jagame näite sammudeks:
Kokkuvõtliku statistika lisamiseks otse andmetabelisse saame kasutada funktsiooni SUMIFS. Alustuseks liidame kokku Toodete arv mis vastavad asjakohasele Tooterühm:
| 1 | = SUMIFS (C3: C11, B3: B11, B3) |
See valem tekitab iga andmerida vahesumma. Vahesummade kuvamiseks ainult iga andmereal Tooterühm, kasutame funktsiooni IF. Pange tähele, et andmed tuleb juba sortida Tooterühm tagamaks, et vahesummad kuvatakse õigesti.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Funktsioon IF võrdleb iga andmerida Tooterühm väärtus koos selle kohal asuva andmeridaga ja kui neil on sama väärtus, väljastab see tühja lahtri (“”).
Kui Tooterühm väärtused on erinevad, kuvatakse summa. Sel viisil iga Tooterühm summa kuvatakse ainult üks kord (esimese astme real).
Andmekogumite sortimine gruppide kaupa
Kui andmed pole veel sorteeritud, saame vahekokkuvõtte jaoks siiski kasutada sama valemit.
Ülaltoodud andmekogumit pole sorteeritud Tooterühm, seega Vahesumma rühmade kaupa veerus kuvatakse iga vahesumma mitu korda. Andmete saamiseks soovitud vormingusse saame valida andmetabeli ja klõpsata nupul „Sorteeri A kuni Z”.
Lahtrite viidete lukustamine
Meie valemite lugemise hõlbustamiseks oleme näidanud mõnda valemit ilma lahtriviideta:
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Kuid need valemid ei tööta korralikult, kui kopeerite ja kleebite mujale oma faili. Selle asemel peaksite kasutama selliseid lukustatud lahtriviiteid:
| 1 | = IF (B3 = B2, "", SUMIFS ($ C $ 3: $ C $ 11, $ B $ 3: $ B $ 11, B3)) |
Lisateabe saamiseks lugege meie artiklit lahtrite viidete lukustamise kohta.
Pivot -tabelite kasutamine vahekokkuvõtete kuvamiseks
Selleks, et tühistada nõue andmete eel sortimiseks Tooterühm, saame selle asemel andmete kokkuvõtteks kasutada liigendtabelite võimsust. Pivot -tabelid arvutavad vahesummad automaatselt ning kuvavad kogusummad ja vahesummad mitmes erinevas vormingus.
Summa Google'i arvutustabelite kategooria või grupi järgi
Need valemid töötavad Google'i arvutustabelites samamoodi nagu Excelis. Funktsioon UNIQUE on aga Google'i arvutustabelites tõstutundlik.
