Lataa esimerkkityökirja
Tämä opetusohjelma osoittaa, kuinka SUMIFS -funktion avulla voidaan laskea yhteen tiettyjä kuukausia vastaavat tiedot Excelissä ja Google Sheetsissä.
Summa jos kuukausittain
Ensinnäkin osoitamme, kuinka yhteenlasketut tiedot, jotka vastaavat tietyn kuukauden ja vuoden päivämääriä.
Voimme käyttää SUMIFS -funktiota yhdessä DATE, YEAR, MONTH ja EOMONTH -toimintojen kanssa Myynnien määrä jokaisen sisällä Kuukausi.
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (VUOSI (E3), KUUKAUSI (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Yllä olevan kaavan luomiseksi aloitamme määrittämällä ajanjakson kullekin kuukaudelle. Käytämme DATE -funktiota kuukauden ensimmäisen päivän määrittämiseen (esim. 1.5.2021). Voimme tehdä tämän "koodaamalla" päivämäärän kaavaan:
| 1 | "> =" & PÄIVÄYS (2021,5,1) |
Tai vielä parempaa, voimme tehdä kriteereistä joustavia viittaamalla päivämäärään solussa E3, poimimalla päivämäärän vuoden ja kuukauden (ja asettamalla päiväksi yhden), kuten yllä olevassa esimerkissä:
| 1 | "> =" & PÄIVÄYS (VUOSI (E3), KUUKAUSI (E3), 1) |
Kuukauden viimeisen päivän määrittämiseksi voimme käyttää EOMONTH -funktiota:
| 1 | "<=" & EOMONTH (E3,0) |
Yhdistämällä kaikki nämä kriteerit voimme kirjoittaa seuraavan SUMIFS -kaavan:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (VUOSI (E3), KUUKAUSI (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Soluviitteiden lukitseminen
Kaavojemme lukemisen helpottamiseksi olemme näyttäneet kaavat ilman lukittuja soluviittauksia:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (VUOSI (E3), KUUKAUSI (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Mutta nämä kaavat eivät toimi kunnolla, kun kopioit ja liität muualle tiedostoosi. Käytä sen sijaan lukittuja soluviittauksia seuraavasti:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "> =" & DATE (VUOSI (E3), KUUKAUSI (E3), 1), $ B $ 3: $ B $ 9, "<=" & EOMONTH (E3,0)) |
Lue artikkelimme soluviitteiden lukitsemisesta saadaksesi lisätietoja.
Kuukauden arvojen muotoilu
Tässä esimerkissä sarakkeessa E on lueteltu kuukaudet. Nämä kuukausiarvot ovat itse asiassa päivämääriä, jotka on muotoiltu jättämään päivä pois käyttämällä mukautettua numeromuotoa.
Muokattu tietomuoto on ”kk vvvv”, joka näytetään toukokuussa 2022.
Summa kuukausittain useiden vuosien aikana
Yllä oleva esimerkki koosti tiedot päivämääristä, jotka olivat tietyn kuukauden ja vuoden sisällä. SUMPRODUCT -funktion avulla voit sen sijaan summata tiedot päivämääriin, jotka ovat kuukauden sisällä.
| 1 | = SUMPRODUCT (C3: C8,-(KUUKAUSI (B3: B8) = KUUKAUSI (G3))) |
Tässä esimerkissä käytämme SUMPRODUCT -funktiota monimutkaisten summa -laskelmien suorittamiseen. Käydään läpi kaava.
Tämä on viimeinen kaavamme:
| 1 | = SUMPRODUCT (C3: C8,-(KUUKAUSI (B3: B8) = KUUKAUSI (G3))) |
Ensinnäkin SUMPRODUCT -toiminto luettelee Myynnien määrä jokaiselle Myyntipäivä ja sitten vertaa kunkin kuukautta Myyntipäivä määritettyä vastaan Kuukausi, palauttaa TOSI, jos kuukaudet vastaavat, tai EPÄTOSI, jos ne eivät:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34},-({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE})) |
Seuraavaksi kaksoisviivat (-) muuntaa TOSI- ja EPÄTOSI-arvot 1: ksi ja 0: ksi:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34}, {1; 0; 1; 0; 1; 0}) |
SUMPRODUCT -funktio kertoo sitten jokaisen taulukon merkintäparin:
| 1 | = SUMPRODUCT ({30; 0; 51; 0; 17; 0}) |
Lopuksi taulukon numerot lasketaan yhteen:
| 1 | =98 |
Lisätietoja Boolean-lauseiden käytöstä ja SUMPRODUCT-funktion komennosta “-” löytyy täältä
Summa jos kuukausittain Google Sheetsissä
Nämä kaavat toimivat täsmälleen samalla tavalla Google Sheetsissä kuin Excelissä.
