Obecný vzorec
=COUNTIFS(rng1,1,rng2,">="&A1,rng2,"<="&EOMONTH(A1,0))
souhrn
Chcete-li počítat nové zákazníky podle měsíce, můžete použít pomocný sloupec a funkci COUNTIFS. V zobrazeném příkladu je vzorec v H5:
=COUNTIFS(new,1,date,">="&G5,date,"<="&EOMONTH(G5,0))
kde „nový“ (E5: E15) a „datum“ (C5: C15) jsou pojmenované rozsahy.
Vysvětlení
Tento vzorec spoléhá na pomocný sloupec, kterým je v uvedeném příkladu sloupec E. Zkopírovaný vzorec v E5 je:
=(COUNTIFS($B$5:B5,B5)=1)+0
Tento vzorec vrací 1 pro nové zákazníky a 0 pro opakující se zákazníky a je zde podrobně vysvětleno. Jakmile je tento vzorec na místě, lze k počítání nových zákazníků v každém měsíci použít funkci COUNTIFS.
První rozsah a kritéria uvnitř COUNTIFS počítá 1 ve sloupci „nový“:
=COUNTIFS(new,1
bez dalších kritérií by to vrátilo počet všech jedinečných zákazníků v datech. Chceme však počet po měsících, takže musíme omezit počet na každý měsíc zobrazený ve sloupci G.
Názvy měsíců ve sloupci G jsou ve skutečnosti daty „prvního měsíce“: 1. ledna 2019, 1. února 2019 a 1. března 2019. Data jsou formátována ve formátu čísla zákazníka „mmm“, aby se zobrazovala jako názvy tří písmen v měsíci: To nám umožňuje psát jednoduchá kritéria pro počítání podle měsíce pomocí přímých dat.
Chcete-li omezit počet pouze na 1, která jsou větší nebo rovna prvnímu měsíci ve sloupci G, použijeme pojmenovaný rozsah "datum" zřetězený na větší nebo rovný operátoru:
=COUNTIFS(new,1,date,">="&G5
Abychom počet dále omezili, aby do konce každého měsíce došlo pouze k 1, přidáme jeden poslední pár rozsah / kritéria:
=COUNTIFS(new,1,date,">="&G5,date,"<="&EOMONTH(G5,0))
Zde opět použijeme pojmenovaný rozsah „date“ a spojíme operátor menší než nebo rovný (<=) s posledním dnem měsíce, vytvořeným pomocí funkce EOMONTH.
Když se tento vzorec zkopíruje, vrátí počet nových zákazníků v každém měsíci.
Opakujte zákazníky za měsíc
Chcete-li počítat opakující se zákazníky podle měsíce, můžete upravit vzorec tak, aby počítal nuly místo 1. Zkopírovaný vzorec v I5 je:
=COUNTIFS(new,0,date,">="&G5,date,"<="&EOMONTH(G5,0))
