Lataa esimerkkityökirja
Tämä opetusohjelma osoittaa, kuinka välisummat lasketaan ryhmittäin käyttämällä SUMIFS -toimintoa Excelissä ja Google Sheetsissä.
Välisumma taulukko luokan tai ryhmän mukaan
Ensin esittelemme, kuinka luodaan dynaaminen välisummayhteenvetotaulukko tietoalueesta joko Excel 365: stä eteenpäin tai Google Sheetsissä.
Käytämme UNIQUE -funktiota ja SUMIFS -funktiota automaattisen välisumman laskemiseen Tuotteiden lukumäärä käyttäjältä Tuoteryhmä:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Tämän välisumman luomiseksi käytämme SUMIFS -funktion vakiosovellusta summan laskemiseen Tuotteiden lukumäärä joka vastaa kutakin Tuoteryhmä. Ennen kuin tämä on mahdollista, meidän on kuitenkin luotava luettelo ainutlaatuisista Tuoteryhmät. Microsoft Excel 365- ja Google Sheets -käyttäjät voivat käyttää UNIQUE -toimintoa luodakseen dynaamisen luettelon ainutlaatuisista arvoista solualueelta. Tässä esimerkissä lisätään seuraava kaava soluun E3:
| 1 | = AINUTLAATUINEN (B3: B11) |
Kun tämä kaava on syötetty, solun alle luodaan automaattisesti luettelo, joka näyttää kaikki Tuoteryhmä dataväli. Tässä esimerkissä luettelo laajeni kattamaan E3: E5 ja näyttää kaikki kolme ainutlaatuista Tuoteryhmä arvot.
Tämä on dynaaminen matriisitoiminto, jossa tulosluettelon kokoa ei tarvitse määrittää, ja se kutistuu ja kasvaa automaattisesti, kun syöttötietojen arvot muuttuvat.
Huomaa, että Excel 365: ssä UNIQUE -toiminto ei erota kirjainkokoa, mutta Google Sheetsissä se on. Harkitse luetteloa {“A”; "A"; "B"; "C"}. UNIQUE -toiminnon lähtö riippuu ohjelmasta:
- {"A"; "B"; "C"} Excel 365: ssä
- {"A"; "A"; "B"; "C"} Google Sheetsissä
Jos käytät Excel -versiota ennen Excel 365: tä, sinun on lähestyttävä eri tavalla. Tästä keskustellaan seuraavassa osassa.
Välisumma -taulukko luokittain tai ryhmittäin - Ennen Excel 365: tä
Jos käytät Excel -versiota ennen Excel 365: tä, UNIQUE -toiminto ei ole käytettävissä. Jos haluat toistaa saman toiminnan, voit yhdistää INDEX- ja MATCH -funktion COUNTIF -funktion kanssa ja luoda taulukkokaavan, joka tuottaa luettelon ainutlaatuisista arvoista solualueelta:
| 1 | {= INDEKSI ($ B $ 3: $ B $ 11, MATCH (0, COUNTIF ($ E $ 2: E2, $ B $ 3: $ B $ 11), 0))} |
Jotta tämä kaava toimisi, kiinteät soluviittaukset on kirjoitettava huolellisesti.
Kaava on myös annettava taulukkokaavaksi painamalla CTRL + VAIHTO + ENTER sen kirjoittamisen jälkeen. Tämä kaava on a 1-soluinen taulukkokaava, joka voidaan sitten kopioida ja liittää soluihin E4, E5 jne. Älä kirjoita tätä taulukon kaavaksi koko alueelle E3: E5 yhdellä toiminnolla.
Samalla tavalla kuin edellisessä esimerkissä SUMIFS -funktiota käytetään sitten välien välisumman laskemiseen Tuotteiden lukumäärä käyttäjältä Tuoteryhmä:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Summa luokan tai ryhmän mukaan - Välisummat tietotaulukoissa
Vaihtoehtona edellä esitetylle yhteenvetotaulukkomenetelmälle voimme lisätä välisummia suoraan tietotaulukkoon. Näytämme tämän käyttämällä IF -toimintoja yhdessä SUMIFS -funktion kanssa lisätäksesi Välisumma ryhmittäin alkuperäiseen tietotaulukkoon.
| 1 | = JOS (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Tässä esimerkissä käytetään SUMIFS -funktiota, joka on sijoitettu IF -funktioon. Jaetaan esimerkki vaiheiksi:
Jos haluat lisätä yhteenvetotilastoja suoraan tietotaulukkoon, voimme käyttää SUMIFS -funktiota. Aloitamme laskemalla yhteen Tuotteiden lukumäärä jotka vastaavat asiaa Tuoteryhmä:
| 1 | = SUMIFS (C3: C11, B3: B11, B3) |
Tämä kaava tuottaa välisumman jokaiselle tietoriville. Näyttää välisummat vain kunkin ensimmäisellä tietorivillä Tuoteryhmä, käytämme IF -toimintoa. Huomaa, että tiedot on jo lajiteltava Tuoteryhmä varmistaaksesi, että välisummat näytetään oikein.
| 1 | = JOS (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
IF -toiminto vertaa kunkin tietorivin tietoja Tuoteryhmä arvo ja sen yläpuolella oleva tietorivi, ja jos niillä on sama arvo, se antaa tyhjän solun (“”).
Jos Tuoteryhmä arvot ovat erilaisia, summa näytetään. Tällä tavalla jokainen Tuoteryhmä summa näytetään vain kerran (ensimmäisen esiintymän rivillä).
Tietojoukkojen lajittelu ryhmän mukaan
Jos tietoja ei ole jo lajiteltu, voimme silti käyttää samaa kaavaa välisummalle.
Yllä olevaa tietojoukkoa ei ole lajiteltu Tuoteryhmä, joten Välisumma ryhmittäin sarake näyttää kunkin välisumman useammin kuin kerran. Saadaksesi tiedot haluamaamme muotoon, voimme valita tietotaulukon ja napsauttaa "Lajittele A - Z".
Soluviitteiden lukitseminen
Kaavojemme lukemisen helpottamiseksi olemme näyttäneet joitain kaavoja ilman lukittuja soluviittauksia:
| 1 | = JOS (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Mutta nämä kaavat eivät toimi kunnolla, kun kopioit ja liität muualle tiedostoosi. Käytä sen sijaan lukittuja soluviittauksia seuraavasti:
| 1 | = JOS (B3 = B2, "", SUMIFS ($ C $ 3: $ C $ 11, $ B $ 3: $ B $ 11, B3)) |
Lue artikkelimme soluviitteiden lukitsemisesta saadaksesi lisätietoja.
Pivot -taulukoiden käyttäminen välisummien näyttämiseen
Poistaa vaatimuksen tietojen esilajittelusta Tuoteryhmä, voimme käyttää pivot -taulukoiden voimaa tietojen tiivistämiseen. Pivot -taulukot laskevat välisummat automaattisesti ja näyttävät yhteensä ja välisummat useissa eri muodoissa.
Summa luokan tai ryhmän mukaan Google Sheetsissä
Nämä kaavat toimivat samalla tavalla Google Sheetsissä kuin Excelissä. UNIQUE -toiminto erottaa Google -taulukoissa isot ja pienet kirjaimet.
