Atsisiųskite darbaknygės pavyzdį
Ši pamoka parodys, kaip apskaičiuoti tarpines sumas pagal grupes naudojant „SUMIFS“ funkciją „Excel“ ir „Google“ skaičiuoklėse.
Tarpinė lentelė pagal kategoriją ar grupę
Pirmiausia parodysime, kaip iš „Excel 365“ arba „Google“ skaičiuoklių duomenų diapazono sukurti dinaminę tarpinės sumos lentelę.
Mes naudojame UNIQUE funkciją ir SUMIFS funkciją, kad automatiškai tarpinė suma būtų Produktų skaičius pagal Produktų grupė:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Norėdami sukurti šią tarpinę lentelę, mes naudojame standartinę SUMIFS funkcijos programą, kad susumuotume Produktų skaičius kurie atitinka kiekvieną Produktų grupė. Tačiau, kol tai neįmanoma, turime sukurti unikalių sąrašą Produktų grupės. „Microsoft Excel 365“ ir „Google“ skaičiuoklių vartotojai turi prieigą prie UNIKALIOS funkcijos, kad sukurtų dinaminį unikalių verčių sąrašą iš langelių diapazono. Šiame pavyzdyje į langelį E3 pridedame šią formulę:
| 1 | = UNIKALUS (B3: B11) |
Įvedus šią formulę, po langeliu automatiškai sukuriamas sąrašas, kuriame rodomos visos unikalios reikšmės, esančios Produktų grupė duomenų diapazonas. Šiame pavyzdyje sąrašas išsiplėtė ir apėmė E3: E5, kad būtų parodyti visi 3 unikalūs Produktų grupė vertybes.
Tai dinaminio masyvo funkcija, kuriai nereikia nustatyti rezultatų sąrašo dydžio, o ji automatiškai susitrauks ir augs, kai pasikeis įvesties duomenų vertės.
Atminkite, kad programoje „Excel 365“ UNIQUE funkcija neskiria didžiųjų ir mažųjų raidžių, tačiau „Google“ skaičiuoklėse yra. Apsvarstykite sąrašą {„A“; „A“; „B“; „C“}. UNIKALIOS funkcijos išvestis priklauso nuo programos:
- {„A“; „B“; „C“} „Excel 365“
- {„A“; „A“; „B“; „C“} „Google“ skaičiuoklėse
Jei naudojate „Excel“ versiją prieš „Excel 365“, turėsite pasirinkti kitokį metodą. Tai aptariama kitame skyriuje.
Tarpinė lentelė pagal kategoriją ar grupę - „Excel Excel 365“
Jei naudojate „Excel“ versiją prieš „Excel 365“, UNIKALIOS funkcijos naudoti negalima. Norėdami pakartoti tą patį elgesį, galite sujungti funkciją INDEX ir MATCH su funkcija COUNTIF, kad sukurtumėte masyvo formulę, pagal kurią būtų sukurtas unikalių verčių sąrašas iš langelių diapazono:
| 1 | {= INDEKSAS ($ B $ 3: $ B $ 11, MATCH (0, COUNTIF ($ E $ 2: E2, $ B $ 3: $ B $ 11), 0))} |
Kad ši formulė veiktų, fiksuotos langelio nuorodos turi būti parašytos atsargiai, o funkcija COUNTIF nurodo diapazoną $ E $ 2: E2, kuris yra diapazonas nuo E2 iki langelio virš langelio, kuriame yra formulė.
Formulę taip pat reikia įvesti kaip masyvo formulę, paspaudus CTRL + SHIFT + ENTER po to, kai ji buvo parašyta. Ši formulė yra a 1 ląstelių masyvo formulė, kurį vėliau galima kopijuoti ir įklijuoti į langelius E4, E5 ir tt Neįveskite to kaip masyvo formulės visam diapazonui E3: E5 vienu veiksmu.
Kaip ir ankstesniame pavyzdyje, tada suma SUMIFS naudojama tarpinei sumai Produktų skaičius pagal Produktų grupė:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Suma pagal kategoriją ar grupę - tarpinės sumos duomenų lentelėse
Kaip alternatyvą aukščiau pateiktam suvestinės lentelės metodui, mes galime pridėti tarpines sumas tiesiai į duomenų lentelę. Mes tai parodysime naudodami IF funkcijas kartu su funkcija SUMIFS, kad pridėtume Tarpinė suma pagal grupes prie pradinės duomenų lentelės.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Šiame pavyzdyje naudojama SUMIFS funkcija, įdėta į IF funkciją. Suskirstykime pavyzdį į veiksmus:
Norėdami įtraukti suvestinę statistiką tiesiai į duomenų lentelę, galime naudoti funkciją SUMIFS. Mes pradedame nuo sumos Produktų skaičius kurie atitinka atitinkamus Produktų grupė:
| 1 | = SUMIFS (C3: C11, B3: B11, B3) |
Ši formulė sukuria kiekvienos duomenų eilutės tarpinę sumą. Kad tarpinės sumos būtų rodomos tik pirmoje kiekvieno duomenų eilutėje Produktų grupė, mes naudojame IF funkciją. Atminkite, kad duomenys jau turi būti surūšiuoti pagal Produktų grupė kad tarpinės sumos būtų rodomos teisingai.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Funkcija IF lygina kiekvieną duomenų eilutę Produktų grupė reikšmę su duomenų eilute virš jos, o jei jos turi tą pačią reikšmę, ji pateikia tuščią langelį („“).
Jei Produktų grupė vertės yra skirtingos, rodoma suma. Tokiu būdu, kiekvienas Produktų grupė suma rodoma tik vieną kartą (pirmojo egzemplioriaus eilutėje).
Duomenų rinkinių rūšiavimas pagal grupes
Jei duomenys dar nėra surūšiuoti, tarpinei sumai vis tiek galime naudoti tą pačią formulę.
Aukščiau pateiktas duomenų rinkinys nėra surūšiuotas Produktų grupė, Taigi Tarpinė suma pagal grupes stulpelyje kiekviena tarpinė suma rodoma daugiau nei vieną kartą. Norėdami gauti duomenis norimu formatu, galime pasirinkti duomenų lentelę ir spustelėti „Rūšiuoti nuo A iki Z“.
Ląstelių nuorodų užrakinimas
Kad mūsų formules būtų lengviau skaityti, parodėme kai kurias formules be užrakintų langelių nuorodų:
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Tačiau šios formulės neveiks tinkamai, kai bus nukopijuotos ir įklijuotos kitur jūsų faile. Vietoj to turėtumėte naudoti užrakintas langelių nuorodas:
| 1 | = IF (B3 = B2, "", SUMIFS ($ C $ 3: $ C $ 11, $ B $ 3: $ B $ 11, B3)) |
Norėdami sužinoti daugiau, perskaitykite mūsų straipsnį apie ląstelių nuorodų užrakinimą.
„Pivot“ lentelių naudojimas tarpinėms sumoms rodyti
Siekiant panaikinti reikalavimą iš anksto surūšiuoti duomenis pagal Produktų grupė, mes galime panaudoti „Pivot“ lentelių galią, kad apibendrintume duomenis. „Pivot“ lentelės automatiškai apskaičiuoja tarpines sumas ir pateikia sumas bei tarpines sumas keliais skirtingais formatais.
Suma pagal kategoriją ar grupę „Google“ skaičiuoklėse
Šios formulės veikia taip pat „Google“ skaičiuoklėse kaip ir „Excel“. Tačiau „UNIQUE“ funkcija „Google“ skaičiuoklėse skiria didžiąsias ir mažąsias raides.
