Povzetek
Za seštevanje po mesecih v stolpce lahko uporabite funkcijo SUMIFS skupaj s funkcijo EOMONTH. V prikazanem primeru je formula v G5:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Ta formula uporablja tri imenovane obsege: "znesek" (D5: D15), "odjemalec" (B5: B15) in "datum" (C5: C15).
Pojasnilo
Funkcija SUMIFS je zasnovana tako, da sešteje vrednosti v obseg na podlagi enega ali več meril. V tem primeru potrebujemo tri kriterije:
- Ime odjemalca = odjemalec v stolpcu F
- Datum> = prvi v mesecu (od datuma v vrstici 4)
- Datum <= konec meseca (od datuma v vrstici 4)
Ključna točka: imena mesecev v G4: I4 so dejansko veljavni datumi, oblikovani s formatom števil po meri "mmm". To nam omogoča, da vrednosti datumov v G4: I4 uporabimo neposredno za zgornji merili # 2 in # 3.
Nazaj v SUMIFS se za preverjanje imena odjemalca uporablja prvi par obsega / meril:
client,$F5
Obvestilo F5 je mešana referenca z zaklenjenim stolpcem. To omogoča spreminjanje vrstice med kopiranjem formule skozi tabelo, vendar ime odjemalca vedno prihaja iz stolpca F.
Drugi par obsega / merila se uporablja za preverjanje datumov glede na prvi v mesecu:
date,">="&G$4
Kot smo že omenili, so vrednosti v G4: I4 dejansko datumi: 1. januar 2019, 1. februar 2019 in 1. marec 2019. To merilo preprosto preverja datume, večje od datumov prvega meseca v vrstici 4. Upoštevajte to je še ena mešana referenca, tokrat z zaklenjeno vrstico. To omogoča spreminjanje stolpca, ko se formula kopira po tabeli, vendar ohranja številko vrstice fiksno. Združevanje z znakom (&) je potrebno pri gradnji meril, ki uporabljajo logični operator in vrednost iz druge celice.
Tretji par obsega / merila se uporablja za preverjanje datumov glede na zadnji dan v mesecu:
date,"<="&EOMONTH(G$4,0)
Da dobimo zadnji dan vsakega meseca, uporabimo funkcijo EOMONTH na datum iz glave stolpca v vrstici 4. Če je za argument mesecev na voljo nič, vrne EOMONTH zadnji dan istega meseca. Ponovno se sklic na G4 meša, da se vrstica ne spremeni.
Rešitev vrtilne tabele
Vrtilna tabela bi bila odlična rešitev za to težavo, saj se lahko samodejno razvrsti po mesecih brez kakršnih koli formul. Za vzporedno primerjavo formul in vrtilnih tabel si oglejte ta video: Zakaj vrtilne tabele.
