月毎の合計数を集計する - Excel 関数

Microsoft Excel で、たとえば次のような表があったとします。

このような表から、各果物における各月毎の個数を集計してみます。

その時に使用するのが SUMPRODUCT という関数です。この関数を使用することで、複数の範囲を同じ場所に位置するセル同士で掛け算して、全てのセルの値を足し合わせた値を取得することができます。

 

これを応用することで、各果物における各月毎の個数を集計することができます。

ただ、SUMPRODUCT の動きは慣れないと複雑なので、順を追ってゆっくりとやってみたいと思います。

とりあえず、次のような集計表を用意します。

そしてまずは、個数を足し合わせたいのですから、それだけで考えれば、次のような式を集計表に記入します。

もちろんこれだと、全種類の全期間の総和で表が埋まってしまいます。

 

次にここから、集計表の左側に記載された果物に絞って集計します。

果物の種類は、集計表内の同じ行の E 列にあるので、B 列の各行に入力されている値と E 列のそれとが同じ値であるかを調べます。

SUMPRODUCT 関数内では、範囲に式や関数を使用すると、指定された範囲のそれぞれのセルについて、その式や関数が適用されるようになっていますので、"$B$1:$B$13=$E3" というようにすると、B1:B13 のそれぞれのセルが、E3 の値に応じて True または False に置き換わります。

この True または False を INT 関数を使って整数値に変換してあげると、True なら 1 に、False なら 0 に置き換わります。つまり "INT($B$1:$B$13=$E3)" とすることで、B1:B3 の範囲で E3 の値と一致するところは 1 で、一致しないところは 0 で取得することができます。

 

SUMPRODUCT は、縦横が同じ範囲を複数指定して、それぞれの各セル毎を掛け算するので、これによって 1 が掛けられたところは残って、0 が掛けられたところは残らないようになります。そしてそれらの総和を計算することで、E3 と一致するものだけで C1:C13 を計算したのと同じ結果が得られます。

ここまでを式で表すと、次のようになります。

図でイメージすると次のような感じでしょうか。

 

同じように、年と月とでも篩分けをして行きます。

集計表の F3 から見た場合、集計したい年月は、その上の F2 に記載されています。よって、先ほどまでの式に加えて、これの年月と A1:A13 との年月が一致するところだけを計算対象とすれば、果物毎のその年月の個数を集計することができます。

日付から年を取得するには YEAR 関数を使用します。

つまり日付は A1:A13 にあるので "YEAR($A$1:$A$13)" と "YEAR(F$2)" とが一致するかを判定して、その結果を INT 関数で数値に変換したものを、SUMPRODUCT の配列 3 として指定します。月については MONTH 関数で取得できるので、同じようにこれを配列 4 に指定します。

これを数式で表すと、次のようになります。

これを図でイメージすると、次のような感じになります。

この数式を、最初の集計表の各セルに適切に入力してあげることで、A1:C13 の表から、月毎の果物の個数の集計ができました。

 

このときの数式と参照範囲については、Excel で見ると次のようになっています。

今回は次の数式を F3 に設定したので、オートフィルで他のセルにも移してあげれば、ちゃんと計算されると思います。

ただ、もしも範囲を絶対指定や相対指定の仕方が間違っていると、オートフィルの時に全く違うセルを参照してしまうこともあるので、集計表の他のセルでも適切な範囲が参照されているか注意して確認しておくのがいいと思います。