Excel。誕生月を数える方法を教えてほしいというリクエストがありまして その２
SUMPRODUCT関数＆MONTH関数
前回に引き続き、名簿の方々を誕生月ごとに何名いるのかを算出する方法の2回目。
前回は、MONTH関数とCOUNTIF関数をそれぞれ使って２ステップで算出させましたが、
今回は、ワンステップで算出させてみようというのが、
今回ご紹介する、SUMPRODUCT関数＆MONTH関数のネストテクニックです。
下記の表があります。
今回使用するSUMPRODUCT関数は、あまり使うことがない関数だと思いますが、
この関数は、配列内の条件に一致する個数を算出してくれます。
動きとしましては、
誕生日のセル範囲を配列として考えて、
ネストで登場するMONTH関数で求めた月が誕生月とイコールなのか、
そうでないのかを判定して、イコールのものを合計した値を算出するという処理になります。
それでは、実際に作っていきましょう。
F3をクリックします。
SUMPRODUCT関数ダイアログボックスを表示しましょう。
配列1のボックスをクリックして、MONTH関数を挿入ので、
MONTH関数ダイアログボックスを表示させましょう。
シリアル値には、誕生日のデータが該当しますので、
$C$3:$C$20
オートフィルを使って数式をコピーしますので、絶対参照を忘れないように設定しましょう。
そして、ポイントですが、数式は完成しておりませんので、
数式バーのSUMPRODUCTをクリックして、
SUMPRODUCT関数ダイアログボックスに戻りましょう。
そして、配列1をこのように修正します。
(MONTH($C$3:$C$20)=E3)*1
E3とイコールつまり、月と比べるわけですね。
ただこの時点で合致していた場合、1という論理値を算出しているので、和算が出来ません。
そこで、×１を数式に加えて、数値化させます。
論理値とは、合致していると、TRUEを算出してきます。このTRUEをExcelでは1としております。
合致していない場合は、FALSEを算出します。
このFALSEをExcelでは、0としております。ただ数値ではないので、計算で使えません。
Accessなどのデータベース的な考え方だと、データ型が数値型になっていないので、
計算できないということです。
そこで、×1 。1をかけてあげることによって、数値型に変えることが出来るわけなのです。
このTRUEが1でFALSEが0というのは、VLOOKUP関数の検索方法もそうなっていますよね。
TRUEとかFALSEとか入力しなくても、0とか1とかで省略できますよね。それと同じです。
修正が終わりましたら、OKボタンをクリックしてみましょう。
ちなみに、数式は、
=SUMPRODUCT((MONTH($C$3:$C$20)=E3)*1)
となっています。
この数式をオートフィルを使ってコピーしましょう。
これで完成ですね。
今回は、ワンステップで算出させましたが、ちょっとわかりにくい関数が登場してきましたので、
前回のようにステップを分けて、算出させるのもいい方法だと思います。
さて、ところが、このような集計。
関数を使わない方が簡単に算出できると思いますので、
次回はその方法をご紹介したいと思います。
