Všeobecný vzorec
=SUMIFS(values,date_range,">="&A1,date_range,"<="&EOMONTH(A1,0))
Zhrnutie
Ak chcete zhrnúť mesiac, môžete použiť vzorec založený na funkcii SUMIFS s pomocou funkcie EOMONTH. V zobrazenom príklade je vzorec v F4:
=SUMIFS(amount,date,">="&E4,date,"<="&EOMONTH(E4,0))
Tento vzorec používa pomenované rozsahy „množstvo“ (C4: C9) a „dátum“ (B4: B9).
Vysvetlenie
Funkcia SUMIFS môže sčítať hodnoty v rozmedzí na základe viacerých kritérií.
V tomto prípade nakonfigurujeme SUMIFS tak, aby sumarizoval sumy po mesiacoch pomocou dvoch kritérií: jedno, aby sa zhodovali s dátumami väčšími alebo rovnými prvému dňu v mesiaci, druhý, aby sa zhodovali s dátumami menšími alebo rovnými poslednému dňu mesiaca. Inými slovami, potrebujeme vzorec ako tento:
=SUMIFS(amount,date,">="&"1/1/2016",date,"<="&"1/31/2016")
Alebo nepriestrelnejšia alternatíva:
=SUMIFS(amount,date,">="&DATE(2016,1,1),date,"<="&DATE(2016,1,31))
Táto verzia je robustnejšia, pretože dátumy sú zostavené z osobitných hodnôt rokov, mesiacov a dní a nie sú zadávané ako text, ktorý je potrebné interpretovať v programe Excel.
Spravidla je to nepríjemné, pretože ak do stĺpca E pridáte názvy mesiacov ako text (tj. „Január“, „február“, „marec“ atď.), Musíte vytvoriť ďalšie problémy, aby ste vytvorili dátumy, ktoré môžete použiť pre kritériá . V takom prípade však na uľahčenie použijeme jednoduchý trik: Do stĺpca E namiesto zadávania názvov mesiacov pridávame skutočné dátumy (1. 1. 2016, 2. 2. 2016, 1. 3. 2016), potom použite vlastný formát dátumu ("mmmm") na zobrazenie názvov mesiacov.
Takto je možné ľahko vytvoriť kritériá, ktoré pre SUMIFS potrebujeme. Na priradenie dátumov väčších alebo rovných prvému v mesiaci používame:
">="&E4
A aby sme priradili dátumy menšie alebo rovné poslednému dňu v mesiaci, použijeme toto:
"<="&EOMONTH(E4,0)
S nulou pre mesiace funkcia EOMONTH automaticky vráti posledný deň v mesiaci poskytnutý dátumom.
Zreťazenie s ampersandom (&) je potrebné pri vytváraní kritérií, ktoré používajú logický operátor s číselnou hodnotou.
Riešenie kontingenčnej tabuľky
Kontingenčná tabuľka je vynikajúcim riešením, keď potrebujete sumarizovať údaje podľa rokov, mesiacov, štvrťrokov atď., Pretože takéto zoskupenie urobia za vás bez akýchkoľvek vzorcov. Podrobné porovnanie vzorcov a kontingenčných tabuliek nájdete v tomto videu: Prečo kontingenčné tabuľky.
