Zhrnutie
Ak chcete sumarizovať mesiac po stĺpcoch, môžete použiť funkciu SUMIFS spolu s funkciou EOMONTH. V zobrazenom príklade je vzorec v G5:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Tento vzorec používa tri pomenované rozsahy: „množstvo“ (D5: D15), „klient“ (B5: B15) a „dátum“ (C5: C15).
Vysvetlenie
Funkcia SUMIFS je navrhnutá tak, aby sumarizovala hodnoty v rozsahu založenom na jednom alebo viacerých kritériách. V tomto prípade potrebujeme tri kritériá:
- Názov klienta = klient v stĺpci F
- Dátum> = prvý mesiac (od dátumu v riadku 4)
- Dátum <= koniec mesiaca (od dátumu v riadku 4)
Kľúčový bod: Názvy mesiacov v G4: I4 sú skutočne platné dátumy, formátované pomocou vlastného číselného formátu „mmm“. To nám umožňuje používať hodnoty dátumu v G4: I4 priamo pre kritériá č. 2 a # 3 vyššie.
Späť v SUMIFS sa na kontrolu názvu klienta používa prvý pár rozsahov a kritérií:
client,$F5
Poznámka F5 je zmiešaná referencia so zamknutým stĺpcom. Toto umožňuje zmenu riadku pri kopírovaní vzorca cez tabuľku, ale meno klienta vždy pochádza zo stĺpca F.
Druhá dvojica rozsahov a kritérií sa používa na kontrolu dátumov oproti prvému v mesiaci:
date,">="&G$4
Ako už bolo spomenuté vyššie, hodnoty v G4: I4 sú vlastne dátumy: 1. januára 2019, 1. februára 2019 a 1. marca 2019. Toto kritérium teda jednoducho kontroluje dátumy väčšie ako dátumy prvého z mesiaca v riadku 4. Všimnite si, že toto je ďalší zmiešaný odkaz, tentokrát so zamknutým riadkom. Toto umožňuje zmenu stĺpca pri kopírovaní vzorca do tabuľky, ale zachováva pevné číslo riadku. Zreťazenie s ampersandom (&) je potrebné pri vytváraní kritérií, ktoré používajú logický operátor a hodnotu z inej bunky.
Tretí pár rozsahov a kritérií sa používa na kontrolu dátumov oproti poslednému dňu v mesiaci:
date,"<="&EOMONTH(G$4,0)
Aby sme dostali posledný deň každého mesiaca, použijeme funkciu EOMONTH v dátume zo záhlavia stĺpca v riadku 4. S argumentom mesiace zadaným nulou vráti EOMONTH posledný deň v rovnakom mesiaci. Opäť je odkaz na G4 zmiešaný, aby sa riadok nezmenil.
Riešenie kontingenčnej tabuľky
Kontingenčná tabuľka by bola vynikajúcim riešením tohto problému, pretože sa môže automaticky zoskupovať podľa mesiacov bez akýchkoľvek vzorcov. Podrobné porovnanie vzorcov a kontingenčných tabuliek nájdete v tomto videu: Prečo kontingenčné tabuľky.
