Laadige alla töövihiku näide
See õpetus näitab, kuidas kasutada funktsiooni SUMIFS, et liita Excelis ja Google'i arvutustabelites konkreetsetele kuudele vastavad andmed.
Summa kuude kaupa
Esiteks demonstreerime, kuidas summeerida andmeid, mis vastavad konkreetsele kuule ja aastale langevatele kuupäevadele.
Me saame kasutada funktsiooni SUMIFS koos funktsioonidega DATE, YEAR, MONTH ja EOMONTH Müükide arv igaühe sees Kuu.
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (AASTA (E3), KUU (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Ülaltoodud valemi koostamiseks alustame iga kuu kuupäevavahemiku määratlemisega. Kuu esimese päeva määratlemiseks kasutame funktsiooni DATE (nt 05.01.2021). Seda saame teha, kuupäeva kõvasti kodeerides valemisse:
| 1 | "> =" & KUUPÄEV (2021,5,1) |
Või veel parem, saame muuta kriteeriumid paindlikuks, viidates lahtris E3 olevale kuupäevale, ekstraheerides kuupäeva aasta ja kuu (ning seades päeva võrdseks 1), nagu on näidatud ülaltoodud näites:
| 1 | "> =" & KUUPÄEV (AASTA (E3), KUU (E3), 1) |
Kuu viimase päeva määramiseks saame kasutada funktsiooni EOMONTH:
| 1 | "<=" & EOMONTH (E3,0) |
Kõik need kriteeriumid kokku pannes saame kirjutada järgmise SUMIFSi valemi:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (AASTA (E3), KUU (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Lahtrite viidete lukustamine
Meie valemite lugemise hõlbustamiseks oleme näidanud valemeid ilma lahtriviideta:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (AASTA (E3), KUU (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Kuid need valemid ei tööta korralikult, kui kopeerite ja kleebite mujale oma faili. Selle asemel peaksite kasutama selliseid lukustatud lahtriviiteid:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "> =" & DATE (AASTA (E3), MONTH (E3), 1), $ B $ 3: $ B $ 9, "<=" & EOMONTH (E3,0)) |
Lisateabe saamiseks lugege meie artiklit lahtrite viidete lukustamise kohta.
Kuuväärtuste vormindamine
Selles näites oleme veerus E loetlenud kuud. Need kuu väärtused on tegelikult kuupäevad, mis on vormistatud päeva väljajätmiseks, kasutades kohandatud numbrivormingut.
Kohandatud andmevorming on „kk -aaaa”, et kuvada 2022. aasta mai.
Summa kuude kaupa mitme aasta jooksul
Ülaltoodud näites võeti kokku andmed kuupäevadega, mis langesid kindlasse kuusse ja aastasse. Funktsiooni SUMPRODUCT abil saate selle asemel liita andmed kuupäevadega, mis jäävad kuu aja sisse igal aastal.
| 1 | = SUMPRODUCT (C3: C8,-(KUU (B3: B8) = KUU (G3))) |
Selles näites kasutame funktsiooni SUMPRODUCT keeruliste „summa, kui” arvutuste tegemiseks. Lähme läbi valemi.
See on meie viimane valem:
| 1 | = SUMPRODUCT (C3: C8,-(KUU (B3: B8) = KUU (G3))) |
Esiteks loetleb funktsioon SUMPRODUCT Müükide arv igaühele Müügikuupäev ja seejärel võrdleb iga kuu Müügikuupäev määratud vastu Kuu, tagastades tõe, kui kuud sobivad, või vale, kui need ei vasta:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34},-({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE})) |
Seejärel teisendavad topeltkriipsud (-) väärtused TRUE ja FALSE väärtusteks 1 ja 0:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34}, {1; 0; 1; 0; 1; 0}) |
Funktsioon SUMPRODUCT korrutab seejärel massiivide iga kirjepaari:
| 1 | = SUMPRODUCT ({30; 0; 51; 0; 17; 0}) |
Lõpuks summeeritakse massiivi numbrid:
| 1 | =98 |
Lisateavet Boole'i lausete ja käsu „-” kasutamise kohta funktsioonis SUMPRODUCT leiate siit
Summa kuude kaupa Google'i arvutustabelites
Need valemid töötavad Google'i arvutustabelites täpselt samamoodi nagu Excelis.
