Formula generik
=COUNTIFS(rng1,1,rng2,">="&A1,rng2,"<="&EOMONTH(A1,0))
Ringkasan
Untuk mengira pelanggan baru mengikut bulan, anda boleh menggunakan kolom pembantu dan fungsi COUNTIFS. Dalam contoh yang ditunjukkan, formula dalam H5 adalah:
=COUNTIFS(new,1,date,">="&G5,date,"<="&EOMONTH(G5,0))
di mana "baru" (E5: E15), dan "tarikh" (C5: C15) dinamakan julat.
Penjelasan
Rumus ini bergantung pada kolom penolong, yang merupakan lajur E dalam contoh yang ditunjukkan. Rumus dalam E5, disalin, adalah:
=(COUNTIFS($B$5:B5,B5)=1)+0
Formula ini mengembalikan 1 untuk pelanggan baru dan 0 untuk pelanggan berulang, dan dijelaskan secara terperinci di sini. Setelah formula ini dilaksanakan, fungsi COUNTIFS dapat digunakan untuk mengira pelanggan baru dalam setiap bulan.
Julat dan kriteria pertama di COUNTIFS menghitung 1 dalam lajur "baru":
=COUNTIFS(new,1
tanpa kriteria lebih lanjut, ini akan mengembalikan jumlah semua pelanggan unik dalam data. Walau bagaimanapun, kami menginginkan jumlah mengikut bulan, oleh itu kami perlu mengehadkan kiraan untuk setiap bulan yang ditunjukkan dalam lajur G.
Nama bulan di lajur G sebenarnya adalah tarikh "pertama bulan": 1-Jan-2019, 1-Feb-2019, dan 1-Mar-2019. Tarikh diformat dengan format nombor pelanggan "mmm" untuk dipaparkan sebagai nama 3 huruf bulan: Ini membolehkan kita menulis kriteria mudah untuk mengira bulan menggunakan tarikh secara langsung.
Untuk mengehadkan kiraan hanya 1 yang lebih besar dari atau sama dengan bulan pertama di lajur G, kami menggunakan julat bernama "tarikh" yang digabungkan menjadi lebih besar daripada atau sama dengan operator:
=COUNTIFS(new,1,date,">="&G5
Untuk mengehadkan kiraan untuk memasukkan hanya 1 yang berlaku pada akhir setiap bulan, kami menambahkan satu pasangan julat / kriteria terakhir:
=COUNTIFS(new,1,date,">="&G5,date,"<="&EOMONTH(G5,0))
Di sini sekali lagi kami menggunakan julat bernama "tarikh", dan kami menggabungkan bilangan operator yang kurang dari atau sama dengan (<=) hingga hari terakhir bulan itu, dibuat dengan fungsi EOMONTH.
Oleh kerana formula ini disalin, ia mengembalikan jumlah pelanggan baru pada setiap bulan.
Pelanggan berulang setiap bulan
Untuk mengira pelanggan berulang setiap bulan, anda boleh menyesuaikan formula untuk mengira angka nol dan bukannya angka 1. Rumus dalam I5, disalin, adalah:
=COUNTIFS(new,0,date,">="&G5,date,"<="&EOMONTH(G5,0))
