Обща формула
=COUNTIFS(rng1,1,rng2,">="&A1,rng2,"<="&EOMONTH(A1,0))
Обобщение
За да преброите новите клиенти по месеци, можете да използвате помощна колона и функцията COUNTIFS. В показания пример формулата в H5 е:
=COUNTIFS(new,1,date,">="&G5,date,"<="&EOMONTH(G5,0))
където „ново“ (E5: E15) и „дата“ (C5: C15) са наречени диапазони.
Обяснение
Тази формула разчита на помощна колона, която е колона Е в показания пример. Формулата в E5, копирана надолу, е:
=(COUNTIFS($B$5:B5,B5)=1)+0
Тази формула връща 1 за нови клиенти и 0 за повторни клиенти и е обяснена подробно тук. След като тази формула е въведена, функцията COUNTIFS може да се използва за преброяване на нови клиенти през всеки месец.
Първият диапазон и критерии вътре в COUNTIFS отчита 1 в колоната „нов“:
=COUNTIFS(new,1
без допълнителни критерии, това би върнало броя на всички уникални клиенти в данните. Искаме обаче преброяване по месец, така че трябва да ограничим броя до всеки месец, показан в колона G.
Имената на месеците в колона G всъщност са датите „първи от месеца“: 1 януари 2019 г., 1 февруари 2019 г. и 1 март 2019 г. Датите са форматирани с формат на клиентски номер "mmm", за да се покажат като имена на месеци с 3 букви: Това ни позволява да напишем прости критерии за преброяване по месеци, като използваме датите директно.
За да ограничим броя само до 1, които са по-големи или равни на първото от месеца в колона G, използваме имената на диапазона "дата", обединени в по-голяма или равна на оператора:
=COUNTIFS(new,1,date,">="&G5
За да ограничим броят допълнително, за да включим само 1, които се случват в края на всеки месец, добавяме една последна двойка диапазон / критерии:
=COUNTIFS(new,1,date,">="&G5,date,"<="&EOMONTH(G5,0))
Тук отново използваме посочения диапазон "дата" и обединяваме по-малко или равно на оператор (<=) до последния ден от месеца, създаден с функцията EOMONTH.
Когато тази формула се копира, тя връща броя на новите клиенти за всеки месец.
Повтаряйте клиентите на месец
За да броите повтарящите се клиенти по месец, можете да настроите формулата да брои нули вместо 1. Формулата в I5, копирана надолу, е:
=COUNTIFS(new,0,date,">="&G5,date,"<="&EOMONTH(G5,0))
