Công thức chung
=COUNTIFS(rng1,1,rng2,">="&A1,rng2,"<="&EOMONTH(A1,0))
Tóm lược
Để đếm khách hàng mới theo tháng, bạn có thể sử dụng cột trợ giúp và hàm COUNTIFS. Trong ví dụ được hiển thị, công thức trong H5 là:
=COUNTIFS(new,1,date,">="&G5,date,"<="&EOMONTH(G5,0))
trong đó "mới" (E5: E15) và "ngày" (C5: C15) là các phạm vi được đặt tên.
Giải trình
Công thức này dựa vào cột trợ giúp, là cột E trong ví dụ được hiển thị. Công thức trong E5, được sao chép xuống, là:
=(COUNTIFS($B$5:B5,B5)=1)+0
Công thức này trả về 1 cho khách hàng mới và 0 cho khách hàng lặp lại và được giải thích chi tiết tại đây. Khi công thức này có sẵn, hàm COUNTIFS có thể được sử dụng để đếm khách hàng mới trong mỗi tháng.
Phạm vi và tiêu chí đầu tiên bên trong COUNTIFS đếm 1 trong cột "mới":
=COUNTIFS(new,1
mà không có tiêu chí nào khác, điều này sẽ trả về tổng số tất cả các khách hàng duy nhất trong dữ liệu. Tuy nhiên, chúng tôi muốn số lượng theo tháng, vì vậy chúng tôi cần giới hạn số lượng theo từng tháng được hiển thị trong cột G.
Tên tháng trong cột G thực tế là các ngày "đầu tiên của tháng": 1 tháng 1 năm 2019, 1 tháng 2 năm 2019 và 1 tháng 3 năm 2019. Ngày được định dạng với định dạng số khách hàng "mmm" để hiển thị dưới dạng tên tháng gồm 3 chữ cái: Điều này cho phép chúng tôi viết các tiêu chí đơn giản để đếm theo tháng bằng cách sử dụng ngày trực tiếp.
Để giới hạn số lượng chỉ là 1 lớn hơn hoặc bằng đầu tiên của tháng trong cột G, chúng tôi sử dụng phạm vi được đặt tên "ngày" được nối với toán tử lớn hơn hoặc bằng:
=COUNTIFS(new,1,date,">="&G5
Để giới hạn số lượng hơn nữa để chỉ bao gồm 1 số xảy ra vào cuối mỗi tháng, chúng tôi thêm một cặp phạm vi / tiêu chí cuối cùng:
=COUNTIFS(new,1,date,">="&G5,date,"<="&EOMONTH(G5,0))
Ở đây một lần nữa chúng tôi sử dụng phạm vi được đặt tên là "ngày" và chúng tôi nối toán tử nhỏ hơn hoặc bằng với (<=) với ngày cuối cùng của tháng, được tạo bằng hàm EOMONTH.
Khi công thức này được sao chép xuống, nó sẽ trả về số lượng khách hàng mới trong mỗi tháng.
Khách hàng lặp lại mỗi tháng
Để đếm khách hàng lặp lại theo tháng, bạn có thể điều chỉnh công thức để đếm số không thay vì số 1. Công thức trong I5, được sao chép xuống, là:
=COUNTIFS(new,0,date,">="&G5,date,"<="&EOMONTH(G5,0))
