Formuła ogólna
=COUNTIFS(rng1,1,rng2,">="&A1,rng2,"<="&EOMONTH(A1,0))
Podsumowanie
Aby policzyć nowych klientów według miesiąca, możesz użyć kolumny pomocnika i funkcji LICZ.WARUNKI. W pokazanym przykładzie formuła w H5 to:
=COUNTIFS(new,1,date,">="&G5,date,"<="&EOMONTH(G5,0))
gdzie „nowy” (E5: E15) i „data” (C5: C15) to nazwane zakresy.
Wyjaśnienie
Ta formuła opiera się na kolumnie pomocnika, którą jest kolumna E w pokazanym przykładzie. Wzór w E5 skopiowany poniżej to:
=(COUNTIFS($B$5:B5,B5)=1)+0
Ta formuła zwraca 1 dla nowych klientów i 0 dla stałych klientów i została szczegółowo wyjaśniona tutaj. Po wprowadzeniu tej formuły funkcja LICZ.WARUNKI może służyć do liczenia nowych klientów w każdym miesiącu.
Pierwszy zakres i kryteria wewnątrz LICZ.WARUNKÓW liczą 1 w kolumnie „nowa”:
=COUNTIFS(new,1
bez dalszych kryteriów zwróciłoby to liczbę wszystkich unikalnych klientów w danych. Chcemy jednak liczyć według miesięcy, więc musimy ograniczyć liczbę do każdego miesiąca pokazanego w kolumnie G.
Nazwy miesięcy w kolumnie G są w rzeczywistości datami „pierwszego miesiąca”: 1 stycznia 2019 r., 1 lutego 2019 r. I 1 marca 2019 r. Daty są formatowane przy użyciu formatu numeru klienta „mmm”, aby były wyświetlane jako 3-literowe nazwy miesięcy: Dzięki temu możemy napisać proste kryteria liczenia według miesięcy bezpośrednio przy użyciu dat.
Aby ograniczyć liczbę tylko do jedynek, które są większe lub równe pierwszemu miesiącowi w kolumnie G, używamy nazwanego zakresu „data” połączonego z operatorem większe lub równe:
=COUNTIFS(new,1,date,">="&G5
Aby jeszcze bardziej ograniczyć liczbę i uwzględnić tylko jedynki występujące na koniec każdego miesiąca, dodajemy ostatnią parę zakres / kryteria:
=COUNTIFS(new,1,date,">="&G5,date,"<="&EOMONTH(G5,0))
Tutaj ponownie używamy nazwanego zakresu „data” i łączymy operator mniejszy lub równy (<=) z ostatnim dniem miesiąca, utworzonym funkcją EOMONTH.
Ponieważ ta formuła jest kopiowana, zwraca liczbę nowych klientów w każdym miesiącu.
Powtarzających się klientów miesięcznie
Aby liczyć powracających klientów według miesiąca, możesz dostosować formułę tak, aby liczyła zera zamiast jedynek. Wzór w I5, skopiowany poniżej, to:
=COUNTIFS(new,0,date,">="&G5,date,"<="&EOMONTH(G5,0))
