Všeobecný vzorec
=COUNTIFS(rng1,1,rng2,">="&A1,rng2,"<="&EOMONTH(A1,0))
Zhrnutie
Ak chcete počítať nových zákazníkov podľa mesiaca, môžete použiť pomocný stĺpec a funkciu COUNTIFS. V zobrazenom príklade je vzorec v H5:
=COUNTIFS(new,1,date,">="&G5,date,"<="&EOMONTH(G5,0))
kde „nový“ (E5: E15) a „dátum“ (C5: C15) sú pomenované rozsahy.
Vysvetlenie
Tento vzorec sa spolieha na pomocný stĺpec, ktorým je v uvedenom príklade stĺpec E. Vzorec v E5, skopírovaný nadol, je:
=(COUNTIFS($B$5:B5,B5)=1)+0
Tento vzorec vracia 1 pre nových zákazníkov a 0 pre opakujúcich sa zákazníkov a je tu podrobne vysvetlený. Keď bude tento vzorec zavedený, bude možné pomocou funkcie COUNTIFS počítať nových zákazníkov v každom mesiaci.
Prvý rozsah a kritériá vo vnútri COUNTIFS počítajú 1 v stĺpci „nové“:
=COUNTIFS(new,1
bez ďalších kritérií by to vrátilo počet všetkých jedinečných zákazníkov v dátach. Chceme však počet podľa mesiaca, takže je potrebné obmedziť počet na každý mesiac uvedený v stĺpci G.
Názvy mesiacov v stĺpci G sú vlastne dátumy „prvého mesiaca“: 1. januára 2019, 1. februára 2019 a 1. marca 2019. Dátumy sú naformátované vo formáte čísla zákazníka „mmm“ tak, aby sa zobrazovali ako trojpísmenové názvy mesiacov: To nám umožňuje písať jednoduché kritériá na počítanie podľa mesiaca pomocou priamych dátumov.
Na obmedzenie počtu iba na 1, ktoré sú väčšie alebo rovné prvému mesiacu v stĺpci G, použijeme pomenovaný rozsah „dátum“ zreťazený na väčší alebo rovný operátoru:
=COUNTIFS(new,1,date,">="&G5
Aby sme počet ďalej obmedzili tak, aby sa na konci každého mesiaca vyskytli iba 1, pridáme jeden posledný pár rozsah / kritérium:
=COUNTIFS(new,1,date,">="&G5,date,"<="&EOMONTH(G5,0))
Opäť tu použijeme pomenovaný rozsah „dátum“ a spojíme operátor menší alebo rovný operátoru (<=) do posledného dňa v mesiaci, ktorý je vytvorený pomocou funkcie EOMONTH.
Keď sa tento vzorec skopíruje nadol, vráti počet nových zákazníkov v každom mesiaci.
Opakujte zákazníkov mesačne
Ak chcete počítať opakujúcich sa zákazníkov podľa mesiaca, môžete upraviť vzorec tak, aby počítal nuly namiesto 1. Vzorec v I5, skopírovaný nadol, je:
=COUNTIFS(new,0,date,">="&G5,date,"<="&EOMONTH(G5,0))
