Excel。会員の誕生月ごとの人数を算出したい。
＜SUMPRODUCT関数＆MONTH関数とCOUNTIF関数＞
会員が誕生月を迎えたらDMやメールを送りたいけど、その人数を事前に把握しておきたいなど、日付から月ごとの件数を把握するにはどうしたらいいのでしょうか？次のような顧客名簿があります。
B列には日付が入力されていて、1月だったら何名という結果をF列に算出したいわけです。
最初に、E列の誕生月を説明しておきます。
1月と表示されていますが、表示形式のユーザー定義をつかって、「月」を表示させているだけです。
セルの値は、1から12の数値が入力されています。
今回は、単純にG/標準のうしろに、"月"を追記しています。
最初に紹介する方法は、手間はかかりますが、わかりやすいかと思う方法です。
【MONTH関数とCOUNTIF関数】
日付の状態では何月なのかがわかりませんので、C列に月だけを算出していきます。日付を抽出するのは、MONTH関数を使えばOKですね。
C2をクリックして、数式を入力していきます。
=MONTH(B2)
あとは、オートフィルを使って数式をコピーします。
これで、月を抽出することができましたので、F列に月ごとに何件あるのか、COUNTIF関数を使って算出していきます。
F2をクリックして、COUNTIF関数ダイアログボックスを表示します。
範囲には、$C$2:$C$16と設定します。オートフィルを使って数式をコピーしますので、絶対参照を設定しておきます。
検索条件には、E2を設定します。
仮にE2に「1月」と入力していると、合致しないので件数を算出することができません。
そのために、先程、表示形式のユーザー定義を使って「～月」と設定したわけです。
このように、月ごとの件数を算出することができました。
しかしこの方法は、一度月を算出させる必要があるので、わかりやすい反面、少々面倒です。
【SUMPRODUCT関数＆MONTH関数】
一発で算出する方法もあることはあるのですが、ちょっとヤヤコシイ。G2に次の数式を作成します。
=SUMPRODUCT((MONTH($B$2:$B$16)=E2)*1)
オートフィルを使って数式をコピーしてみると、同じ結果になります。
数式を説明してきます。
SUMPRODUCT関数は、範囲の積を合計した結果を算出する関数なのですが、この説明は後回しとして、引数で使用している、MONTH関数から説明していきます。
I2に引数のところだけで数式をつくって確認してみましょう。
B2:B16のそれぞれのデータが、E3すなわち、2月と合致しているのかを算出しているのがI列です。
合致しているなら、「TRUE」。合致していないなら「FALSE」と算出されるわけです。
ここまでが、MONTH($B$2:$B$16)の処理。
なぜ、MONTH($B$2:$B$16)に「×1」しているのかというと、TRUEやFALSEのままでは合算することができないので、「×1」をすると、TRUEは、1。FALSEは、0と算出されます。
それが、J列。
あとは、SUMPRODUCT関数をつかうことで、この範囲を合算してくれるという仕組みです。
ということで、
=SUMPRODUCT((MONTH($B$2:$B$16)=E2)*1)
という数式でも、算出することができます。
