【Excel】SUMPRODUCT関数を使用して曜日別に集計する方法
先日日付と売上の表から曜日別に集計する場面に遭遇しました。集計対象の表に「曜日」の列があればSUMIF関数だけで事足りる簡単な事ですが、
「曜日」を持っていない表から曜日別に集計するにはちょっと小細工が必要になります。
いろいろ方法はあると思いますが、今回はSUMPRODUCT関数を使った方法をご紹介します。
SUMPRODUCT関数とは
まずSUMPRODUCT関数の説明です。
機能：引数として配列を指定し、各配列の対応する値の積の合計値を返す。
構文：SUMPRODUCT(配列1 [, 配列2] [, 配列3] ...)
| 引数 | 指定 | 内容 |
| 配列1 | 必須 | 計算対象となる配列を指定します。 |
| 配列2,3... | 省略可 | 省略可能です。計算対象となる配列を指定します。 要素数は配列1と同じである必要があります。要素数が配列1と異なる場合、 #VALUE! のエラーが返されます。 |
※配列要素に数値以外の値があった場合は 0 と見なされます。
SUMPRODUCT関数の基本的な使い方
SUMPRODUCT 関数は「配列要素×配列要素の和」を返します。以下の表のように「単価×個数の合計額」を算出する場合などでよく使われます。
内部的には以下のように配列の先頭からそれぞれの「単価*個数」を計算しその合計値を返しています。
(210*1) + (400*1) + (1,000*2) + ... = 15,540例では集計元となる表にリボンの [数式] タブにある [選択範囲から作成] で各列に名前を付けています。
曜日別に集計する
それでは先ほどの表から曜日別に集計するにはどうしたらよいでしょうか。冒頭で述べたように表に「曜日」列があればSUMIF関数で出来そうですが、
その場合は SUMIF 関数の引数[合計範囲]に指定するための「単価*個数」を算出した「小計」列も必要になります。
曜日別に集計するには SUMPRODUCT を使用して以下のとおりにします。
日曜日を集計：=SUMPRODUCT(単価,個数,N(WEEKDAY(売上日)=1))
月曜日を集計：=SUMPRODUCT(単価,個数,N(WEEKDAY(売上日)=2))
...
土曜日を集計：SUMPRODUCT(単価,個数,N(WEEKDAY(売上日)=7))
第1引数の「単価」と第2引数の「個数」の部分は先述の通りなので説明は割愛しますが、
変わったところは N(WEEKDAY(売上日)=1~7) の箇所です。
N関数とWEEKDAY関数を使用しています。少しややこしい部分もあるのでそれぞれの関数の説明も交えながら順を追って説明します。
WEEKDAY 関数で曜日を取得
WEEKDAY関数は、引数の日付の曜日を整数で返します。以下、リファレンスです。
機能：指定した日付の曜日を整数で返す。
構文：WEEKDAY (日付 [, 週の基準] )
| 引数 | 指定 | 内容 |
| 日付 | 必須 | 曜日を取得する日付(シリアル値)を指定します。 |
| 週の基準 | 省略可 | 省略可能です。週の始まりが何曜日かを数値で指定します。 |
引数 [週の基準] には以下の値が指定できます。
| 週の基準 | 内容（戻り値） |
| 1 | 日曜日基準。1 (日曜) ~ 7 (土曜) を返します。 [週の基準]を省略した場合はこの戻り値になります。 |
| 2 | 月曜日基準。1 (月曜) ~ 7 (日曜) を返します。 |
| 3 | 日曜日基準。0 (日曜) ~ 6 (土曜) を返します。 |
| 11 | 月曜日基準。1 (月曜) ~ 7 (日曜) を返します。 |
| 12 | 火曜日基準。1 (火曜) ~ 7 (月曜) を返します。 |
| 13 | 水曜日基準。1 (水曜) ~ 7 (火曜) を返します。 |
| 14 | 木曜日基準。1 (木曜) ~ 7 (水曜) を返します。 |
| 15 | 金曜日基準。1 (金曜) ~ 7 (木曜) を返します。 |
| 16 | 土曜日基準。1 (土曜) ~ 7 (金曜) を返します。 |
| 17 | 日曜日基準。1 (日曜) ~ 7 (土曜) を返します。 |
使用例：
=WEEKDAY(DATE(2014,11,25))
⇒3(火曜日)を返します。
※2014年11月25日は火曜日です
先述した日曜日を集計する数式の「WEEKDAY(売上日)=1」の部分は「売上日」が「1(日曜)」、つまり集計対象かどうかを判定しており、
日曜日(集計対象)の場合はTRUE、日曜日意外(集計対象外)の場合はFALSEになります。
月曜日を集計するなら「WEEKDAY(売上日)=2」、木曜日なら「WEEKDAY(売上日)=5」です。これで「売上日」が集計対象の曜日かどうかを取得することができます。
但し、このまま(TRUE/FALSE)では計算に使用できないので、次に説明するN関数を使用します。
通常は論理値(TRUE/FALSE)はそのまま計算に使用できます。例えば「=1*TRUE」は 1 に、「=1*FALSE」は 0 になります。
但し、先述の通りSUMPRODUCT は数値以外の配列要素は 0 と見なしてしまうため N 関数で敢えて数値に変換します。
N 関数で数値に変換
WEEKDAY 関数で「売上日」が集計対象の曜日かどうかをTRUE/FALSEで取得できましたが、
SUMPRODUCT の計算で使用できる数値に変換するために N 関数を使用します。
機能：引数に指定した値を数値に変換する。
構文：N( 値 )
引数 [値] に指定する内容によって以下の通り変換されます。
| 値 | 戻り値 |
| 数値 | そのままの数値 |
| 日付 | 日付のシリアル値 |
| TRUE | 1 |
| FALSE | 0 |
| エラー値 | そのままのエラー値 |
| その他 | 0 |
先述の「売上日が集計対象の曜日かどうか」はTRUE/FALSEで返されるので、TRUE(集計対象)は1に、FALSE(集計対象外)は0に変換されます。
曜日別集計の計算プロセス
WEEKDAY 関数、N 関数の説明を終えたところで、もう一度SUMPRODUCT を使用した曜日別集計の数式を見てみましょう。以下は日曜日の集計を行う数式です。
=SUMPRODUCT(単価,個数,N(WEEKDAY(売上日)=1))
「単価」と「個数」の部分は先述の通り配列(表)の先頭から順に (210*1) + (400*1) + (1,000*2) + ... という計算を行なっています。
ここに「N(WEEKDAY(売上日)=1)」で算出した「1(TRUE)/0(FALSE)」が加わるので以下のようになります。
(210*1*1) + (400*1*0) + (1,000*2*0) + ...
= 210 + 0 + 0 + ...>
= 1,210>
集計対象の曜日は 1 を乗じるのでそのままの金額となり、集計対象外の曜日は 0 を乗じるので 0 になる、つまり集計されないって仕組みになってます。
まとめ
今回は曜日別集計の事例を通して SUMPRODUCT 関数の説明をしてみました。
SUMPRODUCT関数の引数に渡した数式は配列数式の様に振る舞うんですね。
今回は曜日を条件にしましたが、他にも色々使えそうです。
ご質問などありましたらお気軽にお問い合わせください。
HAYs
コメントを残す