Lejupielādējiet darbgrāmatas piemēru
Šī apmācība parādīs, kā aprēķināt starpsummas pa grupām, izmantojot funkciju SUMIFS programmā Excel un Google izklājlapas.
Starpsumma tabula pēc kategorijas vai grupas
Pirmkārt, mēs parādīsim, kā izveidot dinamisku starpsummu kopsavilkuma tabulu no datu diapazona programmā Excel 365 vai vēlāk, vai Google izklājlapās.
Mēs izmantojam funkciju UNIQUE un SUMIFS, lai automātiski summētu Produktu skaits pēc Produktu grupa:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Lai izveidotu šo starpsummu tabulu, mēs izmantojam funkcijas SUMIFS standarta lietojumprogrammu, lai summētu Produktu skaits kas atbilst katram Produktu grupa. Tomēr, pirms tas ir iespējams, mums ir jāizveido unikālo saraksts Produktu grupas. Microsoft Excel 365 un Google izklājlapu lietotājiem ir piekļuve funkcijai UNIQUE, lai no šūnu diapazona izveidotu dinamisku unikālo vērtību sarakstu. Šajā piemērā šūnai E3 pievienojam šādu formulu:
| 1 | = UNIKĀLS (B3: B11) |
Ievadot šo formulu, zem šūnas automātiski tiek izveidots saraksts, kurā tiek parādītas visas unikālās vērtības, kas atrodamas Produktu grupa datu diapazons. Šajā piemērā saraksts paplašinājās, iekļaujot E3: E5, lai parādītu visus 3 unikālos Produktu grupa vērtības.
Šī ir dinamiska masīva funkcija, kurā rezultātu saraksta lielums nav jādefinē, un tā automātiski samazināsies un pieaugs, mainoties ievades datu vērtībām.
Ņemiet vērā, ka programmā Excel 365 UNIQUE funkcija nav reģistrjutīga, bet Google izklājlapās tā ir. Apsveriet sarakstu {“A”; “A”; “B”; “C”}. UNIQUE funkcijas izvade ir atkarīga no programmas:
- {“A”; “B”; “C”} programmā Excel 365
- {“A”; “A”; “B”; “C”} Google izklājlapās
Ja pirms Excel 365 izmantojat Excel versiju, jums būs jāizmanto cita pieeja. Tas tiek apspriests nākamajā sadaļā.
Starpsummas tabula pēc kategorijas vai grupas - pirms Excel 365
Ja pirms Excel 365 izmantojat Excel versiju, funkcija UNIKĀLA nav pieejama lietošanai. Lai atkārtotu to pašu darbību, varat apvienot funkciju INDEX un MATCH ar funkciju COUNTIF, lai izveidotu masīva formulu, lai no šūnu diapazona izveidotu unikālu vērtību sarakstu.
| 1 | {= INDEKSS ($ B $ 3: $ B $ 11, MATCH (0, COUNTIF ($ E $ 2: E2, $ B $ 3: $ B $ 11), 0))} |
Lai šī formula darbotos, fiksēto šūnu atsauces ir rūpīgi jāraksta, izmantojot funkciju COUNTIF, atsaucoties uz diapazonu $ E $ 2: E2, kas ir diapazons, sākot no E2 līdz šūnai virs šūnas, kas satur formulu.
Formula ir jāievada arī kā masīva formula, pēc rakstīšanas nospiežot CTRL + SHIFT + ENTER. Šī formula ir a 1 šūnu masīva formula, ko pēc tam var kopēt un ielīmēt šūnās E4, E5 utt. Neievadiet to kā masīva formulu visam diapazonam E3: E5 vienā darbībā.
Tādā pašā veidā kā iepriekšējā piemērā, SUMIFS funkcija tiek izmantota, lai starpsummu Produktu skaits pēc Produktu grupa:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Summa pēc kategorijas vai grupas - starpsumma datu tabulās
Kā alternatīvu iepriekš parādītajai kopsavilkuma tabulas metodei mēs varam pievienot starpsummas tieši datu tabulā. Mēs to parādīsim, izmantojot IF funkcijas kopā ar funkciju SUMIFS, lai pievienotu Starpsumma pa grupām uz sākotnējo datu tabulu.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Šajā piemērā tiek izmantota funkcija SUMIFS, kas ievietota IF funkcijā. Sadalīsim piemēru soļos:
Lai statistikas kopsavilkumu pievienotu tieši datu tabulai, mēs varam izmantot funkciju SUMIFS. Mēs sākam, summējot Produktu skaits kas atbilst attiecīgajam Produktu grupa:
| 1 | = SUMIFS (C3: C11, B3: B11, B3) |
Šī formula rada starpsummas vērtību katrai datu rindai. Lai starpsummas tiktu rādītas tikai katras datu pirmajā rindā Produktu grupa, mēs izmantojam funkciju IF. Ņemiet vērā, ka dati jau ir jāsakārto pēc Produktu grupa lai nodrošinātu, ka starpsummas tiek parādītas pareizi.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Funkcija IF salīdzina katru datu rindu Produktu grupa vērtību ar datu rindu virs tās, un, ja tiem ir vienāda vērtība, tiek izvadīta tukša šūna (“”).
Ja Produktu grupa vērtības ir atšķirīgas, tiek parādīta summa. Šādā veidā katrs Produktu grupa summa tiek parādīta tikai vienu reizi (pirmās instances rindā).
Datu kopu kārtošana pēc grupas
Ja dati vēl nav sakārtoti, mēs joprojām varam izmantot to pašu formulu starpsummai.
Iepriekš minētā datu kopa nav sakārtota pēc Produktu grupa, tātad Starpsumma pa grupām slejā tiek parādīta katra starpsumma vairāk nekā vienu reizi. Lai iegūtu datus vajadzīgajā formātā, mēs varam atlasīt datu tabulu un noklikšķināt uz “Kārtot no A līdz Z”.
Šūnu atsauču bloķēšana
Lai mūsu formulas būtu vieglāk lasāmas, mēs esam parādījuši dažas formulas bez bloķētām šūnu atsaucēm:
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Bet šīs formulas nedarbosies pareizi, kopējot un ielīmējot citur jūsu failā. Tā vietā jums vajadzētu izmantot šādas bloķētas šūnu atsauces:
| 1 | = JA (B3 = B2, "", SUMIFS ($ C $ 3: $ C $ 11, $ B $ 3: $ B $ 11, B3)) |
Lai uzzinātu vairāk, izlasiet mūsu rakstu par šūnu atsauču bloķēšanu.
Pivot tabulu izmantošana starpsummu rādīšanai
Lai atceltu prasību datus iepriekš kārtot pēc Produktu grupa, tā vietā mēs varam izmantot rakurstabulu spēku, lai apkopotu datus. Pivot tabulas automātiski aprēķina starpsummas un parāda kopsummas un starpsummas vairākos dažādos formātos.
Summa pēc kategorijas vai grupas Google izklājlapās
Šīs formulas darbojas Google izklājlapās tāpat kā programmā Excel. Tomēr funkcija UNIQUE Google izklājlapās ir reģistrjutīga.
