本記事で扱う内容

「SUMIF系関数って何それ美味しいの」のレベル0から、
SUMIFS関数に加え、OFFSET関数, MATCH関数, INDEX関数も活用する応用レベルまで順を追って解説する

本記事はSUMIFS系関数を使いこなすことを通して、
アプリケーションの仕様を理解し、Excelマスターを目指すための物語である

最後まで読むと、行列の入れ替わりに自動対応する集計式を自力で作れるようになっている（ハズ）
正直記事が長くなり過ぎたと反省しているので、1日1章のペースで読むことをオススメ

はじめに

「IFS集計関数（SUMIFS,COUNTIFS,AVERAGEIFS）」はExcel関数初心者がVLOOKUPの次ぐらいに超えるハードルである

結論だけ欲しい人は以下の2つの章を参照

Lv3. テーブルの構造化参照を使用する
Lv.6(ex) 数式で名前付き範囲を定義し、列の入れ替わりに対応させる

本記事は、わからないときは1つ前の章に戻っていけば全体が理解できる構成となっている

一覧「IF系集計関数／IFS系集計関数」

説明	IF系集計関数	IFS系集計関数
合計	SUMIF	SUMIFS
セル件数	COUNTIF	COUNTIFS
平均	AVERAGEIF	AVERAGEIFS

上記の内、本コラムではSUMIF関数とSUMIFS関数を扱う

Lv.0 IF系集計関数

IF系集計関数は単一の条件式で表の集計を行う場合に使う関数である

成果物

本章で完成する集計用関数

example

=SUMIF($B$2:$B$6,"男*", $D$2:$D$6)

説明

【お題】
SUMIF関数を使用し、男性の顧客の売上データを合計する

表

⊿	A	B	C	D
1	顧客名	性別年齢	購入日	売上
2	村田	女性20代	2014/10/05	1500
3	田中	男性50代	2015/03/12	5000
4	佐藤	男性30代	2015/11/02	2000
5	鈴木	男	2016/03/05	500
6	田中	男性不明	2016/08/12	3000

前提

男は必ず「性別年齢」に「男」という漢字から始まるデータが入っている
「男ではない」などというフザケたデータは入らない

前提から導き出される条件


条件判定範囲	`$B$2:$B$6` (性別年齢列)
条件	`"男*"`
集計対象範囲	`$D$2:$D$6` (売上列)

Tips. ワイルドカード文字について

SUMIFの数式は以下のとおりなので…

formula

=SUMIF(条件判定範囲,条件, 集計対象範囲)

集計用関数は以下のようになる

example

=SUMIF($B$2:$B$6,"男*", $D$2:$D$6)

result

Lv.1 IFS集計関数化

IFS集計関数化することにより、複数条件で集計対象データを絞り込むことができる

成果物

本章で完成する集計用関数

example

=SUMIFS($D$2:$D$6, $B$2:$B$6,"男*", $C$2:$C$6,">=2015/04/01", $C$2:$C$6,"<2016/04/01")

説明

【お題】

表

⊿	A	B	C	D
1	顧客名	性別年齢	購入日	売上
2	村田	女性20代	2014/10/05	1500
3	田中	男性50代	2015/03/12	5000
4	佐藤	男性30代	2015/11/02	2000
5	鈴木	男	2016/03/05	500
6	田中	男性不明	2016/08/12	3000

上記の表で、以下の条件を満たす売上データを合計したい

性別年齢："男*"
購入日：2015年4月1日から
購入日：2016年3月31日まで

手順1.　SUMIF関数をSUMIFS関数化する

SUMIFSの数式は以下のとおりなので…

formula

=SUMIFS(集計対象範囲, 条件判定範囲1,条件1, 条件判定範囲2,条件2,… )

SUMIFの最後の引数「集計対象範囲（$D$2:$D$6）」を最初の引数に移動させる

example_before

=SUMIF($B$2:$B$6,"男*", $D$2:$D$6)

集計用関数は以下のようになる

example_after

=SUMIFS($D$2:$D$6, $B$2:$B$6,"男*")

result

手順2.　条件2「購入日：2015年4月1日から」を追加する

⇒ Tips「【Excel】日時情報の正体とよくある計算ミス」

導き出される条件


条件判定範囲2	`$C$2:$C$6` (日付列)
条件2	`">=2015/04/01"`

日時は比較演算子により比較可能である

集計用関数は以下のようになる

example

=SUMIFS($D$2:$D$6, $B$2:$B$6,"男*", $C$2:$C$6,">=2015/04/01")

result

手順3.　条件3「購入日：2016年3月31日まで」を追加する

「2016年3月31日まで」は「2016年3月31日23時59分59秒」を含む
⇒「2016年4月1日0時0分0秒より前」なので

導き出される条件


条件判定範囲3	`$C$2:$C$6` (日付列)
条件3	`"<2016/04/01"`

集計用関数は以下のようになる

example

=SUMIFS($D$2:$D$6, B$2:B$6,"男*", $C$2:$C$6,">=2015/04/01", $C$2:$C$6,"<2016/04/01")

result

xxIFS関数の注意

すべての行列の数が等しくなければならない

xxIFS関数は引数に指定したすべての範囲の行列数が同じでないと#VALUE!エラーになるので注意

example

=SUMIFS($D$2:$D$5, $B$2:$B$6,"男*", $C$2:$C$6,">=2015/04/01")

result

#VALUE!

数式が汚くなりやすい

数式が長くなり、読みづらくなりやすい
→ メンテナンス性の低下

Lv.2 「名前付きセル範囲」を利用する

【お題】
名前の定義機能により集計で「名前付きセル範囲」を利用する

成果物

本章で完成する集計用関数

example

=SUMIFS(売上, 性別年齢,"男*",購入日,">=2015/04/01",購入日,"<2016/04/01")

本章で完成する名前の定義

性別年齢 　: =Sheet1!$B$2:$B$6
購入日　 　: =Sheet1!$C$2:$C$6
売上　　 　: =Sheet1!$D$2:$D$6

説明

→ 名前の定義機能で計算に使用する範囲を「名前付きセル範囲」にすることで数式を ~~分かりやすく~~ 読みやすくすることができる

表

⊿	A	B	C	D
1	顧客名	性別年齢	購入日	売上
2	村田	女性20代	2014/10/05	1500
3	田中	男性50代	2015/03/12	5000
4	佐藤	男性30代	2015/11/02	2000
5	鈴木	男	2016/03/05	500
6	田中	男性不明	2016/08/12	3000

example_before

=SUMIFS($D$2:$D$6, $B$2:$B$6,"男*", $C$2:$C$6,">=2015/04/01", $C$2:$C$6,"<2016/04/01")

集計用関数は以下のようになる

example_after

=SUMIFS(売上, 性別年齢,"男*",購入日,">=2015/04/01",購入日,"<2016/04/01")

表中で定義している名前

しかし、この状態ではデータ行が増えた時に上手く対応できない
~~=Sheet1!\$C:\$Cみたいに列で名前を定義すれば~~
↑列全参照は、

大量のデータを読み込ませる用途
何度も集計元データを変更して長期的に使うファイル

の場合、シートが重くなりやすいので、可能なかぎり避ける
簡単なものなら列全参照を利用してもOK

Lv3. テーブルの構造化参照を使用する

本章で完成する集計用関数

example_table

=SUMIFS(テーブル1[売上],テーブル1[性別年齢],"男*",テーブル1[購入日],">=2015/04/01",テーブル1[購入日],"<2016/04/01")

テーブル化すればデータが増えても範囲が自動で拡張され、
新しい列の追加や削除、列の入れ替え、列名の変更にも自動的に柔軟に対応してくれるのでオススメ
名前の定義も自動で行われる

表

⊿	A	B	C	D
1	顧客名	性別年齢	購入日	売上
2	村田	女性20代	2014/10/05	1500
3	田中	男性50代	2015/03/12	5000
4	佐藤	男性30代	2015/11/02	2000
5	鈴木	男	2016/03/05	500
6	田中	男性不明	2016/08/12	3000

集計用関数

example_table

=SUMIFS(テーブル1[売上],テーブル1[性別年齢],"男*",テーブル1[購入日],">=2015/04/01",テーブル1[購入日],"<2016/04/01")

テーブル名「テーブル1」部分はデフォルトのテーブルの名前。
名前の管理機能で変更可能

とっつきにくいイメージがあるが、構造化参照の文法がよく分からない人でも数式編集中にテーブル内目的列のデータ範囲を全選択することにより、構造化参照が自動的に数式に組み込まれるので、誰でもすぐに使いこなすことができる

また、同じ列中で数式が部分的に異なるというミスの確率を最小化できる
内部処理的にも軽いので、テーブル化できるなら構造化参照で対応したい

参考：

テーブルのすすめ　構造化参照 - Road to Cloud Office

構造化参照の文法(日本語版)
ただし機械翻訳のためところどころおかしい。意味がわからないときは英字リファレンスに目を通すことを推奨

Lv.4(ex) 「名前付きセル範囲」を可変長にする

テーブル機能が諸事情により使えない人向け
OFFSET関数を活用して「名前付きセル範囲」を「可変長の名前付きセル範囲」にする

成果物

本章で使用する集計用関数(LV.2のもの)

example

=SUMIFS(売上, 性別年齢,"男*",購入日,">=2015/04/01",購入日,"<2016/04/01")

本章で完成する名前の定義

性別年齢 　: =OFFSET(Sheet1!$B$2, 0, 0, COUNTIF(Sheet1!$B$2:$B$500,"*"), 1)
購入日　 　: =OFFSET(Sheet1!$C$2, 0, 0, COUNT(Sheet1!$C$2:$C$500), 1)
売上　　 　: =OFFSET(Sheet1!$D$2, 0, 0, COUNT(Sheet1!$D$2:$D$500), 1)

説明

表

⊿	A	B	C	D
1	顧客名	性別年齢	購入日	売上
2	村田	女性20代	2014/10/05	1500
3	田中	男性50代	2015/03/12	5000
4	佐藤	男性30代	2015/11/02	2000
5	鈴木	男	2016/03/05	500
6	田中	男性不明	2016/08/12	3000

集計条件

以下の条件を満たす売上データを合計したい

性別年齢："男*"
購入日：2015年4月1日から
購入日：2016年3月31日まで

表中で定義している名前

性別年齢 　: =Sheet1!$B$2:$B$6
購入日　 　: =Sheet1!$C$2:$C$6
売上　　 　: =Sheet1!$D$2:$D$6

Lv.2で作成した「名前付きセル範囲」による数式には以下の問題がある

範囲が固定なので後から変更するのが面倒
範囲の列数を余分に取って余分に計算させるか、ギリギリの列数に設定しなければならない

→ OFFSET関数で「可変長の名前付きセル範囲」にする

Tips 「OFFSET関数」について

性別年齢のセル範囲を動的に決定する

前提

1.データは最大で500行目のセルまで入る
2.データ列の最初から最後までの間に空白セルが入ることはない
3.性別年齢の列中に入力されているデータはすべて文字列である(数値は入らない)

→ COUNTIF(範囲,"*")でデータ件数(=行数)を特定可能

定義された名前「性別年齢」の参照範囲

性別年齢のデータはSheet1!B2から始まるので…

example_before

=Sheet1!$B$2:$B$6

example_after

=OFFSET(Sheet1!$B$2, 0, 0, COUNTIF(Sheet1!$B$2:$B$500,"*"), 1)

数値と文字列が入り混じっている場合は
CONTIF(Sheet1!B$2:B$500,"*")を
COUNTA(Sheet1!B$2:B$500)に変える。

購入日のセル範囲を動的に決定する

前提
1. データは最大で500行目のセルまで入る
2. データ列の最初から最後までの間に空白セルが入ることはない
3. 列中に入っているのはすべて数値である
→ COUNT(範囲)でデータ件数(=行数)を特定可能

定義された名前「購入日」の参照範囲

購入日のデータはSheet1!C2から始まるので…

example_before

=Sheet1!$C$2:$C$6

example_after

=OFFSET(Sheet1!$C$2, 0, 0, COUNT(Sheet1!$C$2:$C$500), 1)

定義された名前「売上」の参照範囲

購入日と同様に、売上は数値データであり、
売上のデータはSheet1!D2から始まるので…

example_before

=Sheet1!$D$2:$D$6

example_after

=OFFSET(Sheet1!$D$2, 0, 0, COUNT(Sheet1!$D$2:$D$500), 1)

表中で定義している名前

性別年齢 　: =OFFSET(Sheet1!$B$2, 0, 0, COUNTIF(Sheet1!$B$2:$B$500,"*"), 1)
購入日　 　: =OFFSET(Sheet1!$C$2, 0, 0, COUNT(Sheet1!$C$2:$C$500), 1)
売上　　 　: =OFFSET(Sheet1!$D$2, 0, 0, COUNT(Sheet1!$D$2:$D$500), 1)

以上の変更で、SUMIFSの参照範囲が500行目までの範囲で動的に指定されるようになった
しかし、現在の数式は一切の空欄を許容しないため、以下の状況のとき、名前ごとのセル範囲の行数が一致せず
#VALUE!エラーとなる

購入日の可変長の名前付きセル範囲

calculate

=OFFSET(Sheet1!$C$2, 0, 0, COUNT(Sheet1!$C$2:$C$500), 1)
=OFFSET(Sheet1!$C$2,0,0, 5,1)
=$C$2:$C$6

性別年齢の可変長の名前付きセル範囲

calculate

=OFFSET(Sheet1!$B$2,0,0, COUNTIF(Sheet1!$B$2:$B$500,"*"), 1)
=OFFSET(Sheet1!$B$2,0,0, 3,1)
=$B$2:$B$4

↑$B$2:$B$6になっていない

Lv.5(ex) #VALUE!エラーを防ぐ

「可変長の名前付きセル範囲」内のOFFSET第4引数を共通化することにより、#VALUE!エラーを防ぐ

成果物

本章で使用する集計用関数(LV.2のもの)

example

=SUMIFS(売上, 性別年齢,"男*",購入日,">=2015/04/01",購入日,"<2016/04/01")

本章で完成する名前の定義

データ件数 : =COUNT(Sheet1!$C$2:$C$500)
性別年齢 　: =OFFSET(Sheet1!$B$2, 0, 0, データ件数, 1)
購入日　 　: =OFFSET(Sheet1!$C$2, 0, 0, データ件数, 1)
売上　　 　: =OFFSET(Sheet1!$D$2, 0, 0, データ件数, 1)

説明

表

⊿	A	B	C	D
1	顧客名	性別年齢	購入日	売上
2	村田	女性20代	2014/10/05	1500
3	田中	男性50代	2015/03/12	5000
4	佐藤	男性30代	2015/11/02	2000
5	鈴木	男	2016/03/05	500
6	田中	男性不明	2016/08/12	3000

Lv.4の集計法の問題

名前ごとのセル範囲の行数(OFFSET第4引数)が一致せず、#VALUE!エラーとなることがある
→ 必ず正しいデータ件数が得られる式を共通のOFFSET第4引数として使用する

今回の表で「購入日データが必ず連続した行として存在する（途中に絶対に空白セルが入らない）」のであれば、
「購入日の可変長の名前付きセル範囲」のOFFSET第4引数COUNT(Sheet1!$C$2:$C$500)を
「性別年齢の可変長の名前付きセル範囲」と「売上の可変長の名前付きセル範囲」の第4引数に代入する

あるいは、
「購入日の可変長の名前付きセル範囲」のOFFSET第4引数COUNT(Sheet1!$C$2:$C$500)を参照範囲とした
「データ件数」という名前を新規に作成し、
「性別年齢の可変長の名前付きセル範囲」「購入日の可変長の名前付きセル範囲」「売上の可変長の名前付きセル範囲」の第4引数に代入する

名前「データ件数」

calculate

=データ件数
=COUNT(Sheet1!$C$2:$C$500)
=5

名前「データ件数」が代入された状態

データ件数 : =COUNT(Sheet1!$C$2:$C$500)
性別年齢 　: =OFFSET(Sheet1!$B$2, 0, 0, データ件数, 1)
購入日　 　: =OFFSET(Sheet1!$C$2, 0, 0, データ件数, 1)
売上　　 　: =OFFSET(Sheet1!$D$2, 0, 0, データ件数, 1)

以上の変更により、#VALUE!エラーが解消された

集計用関数

example

=SUMIFS(売上, 性別年齢,"男*", 購入日,">=2015/04/01", 購入日,"<2016/04/01")

エラーが解消された表

Lv.6(ex) 列の入れ替わりに対応させる

OFFSETの基準セル（第1引数）を
INDEX関数とMATCH関数で動的に定義することにより、列の順序の入れ替わりに対応する

成果物

本章で使用する集計用関数(LV.2のもの)

example

=SUMIFS(売上, 性別年齢,"男*",購入日,">=2015/04/01",購入日,"<2016/04/01")

本章で完成する名前の定義

データ件数 : =COUNT(Sheet1!$C$2:$C$500) 
見出し　　 : =Sheet1!$A$1:$Z$1
性別年齢 　: =OFFSET(INDEX(見出し,MATCH("性別年齢",見出し,0)),1,0, データ件数,1)
購入日　 　: =OFFSET(INDEX(見出し,MATCH("購入日",見出し,0)),1,0, データ件数,1)
売上　　 　: =OFFSET(INDEX(見出し,MATCH("売上",見出し,0)),1,0, データ件数,1)

説明

表

⊿	A	B	C	D
1	顧客名	性別年齢	購入日	売上
2	村田		2014/10/05	1500
3	田中	男性50代	2015/03/12	5000
4	佐藤	男性30代	2015/11/02	2000
5	鈴木		2016/03/05	500
6	田中	男性不明	2016/08/12	3000

データを外部のCSVファイルなどから貼り付けて集計するとき、列の並びが頻繁に入れ替わるシチュエーションがある
このとき、Lv.5のやり方では都度OFFSETの第1引数を修正しなければならないので実用的ではない

→ INDEX関数とMATCH関数により列名の位置を探し、OFFSETの基準セルを動的に決定する

Tips. INDEX関数について
Tips. MATCH関数について

目的の列名のセルを参照する数式

先ずは、列名のセルを見つける数式を作成する

formula_idx

=INDEX(列名が含まれる１行の範囲, MATCH(目的の列名,列名が含まれる１行の範囲,0))

例）「性別年齢」の文字列が入力されているセル範囲を参照する

example_idx

=INDEX(Sheet1!$A$1:$Z$1,MATCH("性別年齢",Sheet1!$A$1:$Z$1,0))

MATCH()の結果は、以下の演算により2である

calculate_match

=MATCH("性別年齢",Sheet1!$A$1:$Z$1,0)
=2

数式に結果を代入して

calculate_idx

=INDEX(Sheet1!$A$1:$Z$1,2)

result_idx

=Sheet1!$B$1

目的の列名のデータ範囲を参照する数式

formula

=OFFSET(INDEX(列名が含まれる１行の範囲, MATCH(目的の列名,列名が含まれる１行の範囲,0)),1,0,データ件数,1)

OFFSETの第1引数がデータ列の集計開始セルではなく、その1行上のデータ行の見出しセルを指すようになったため、
OFFSET第2引数を0から1に修正している

例）「性別年齢」のデータが含まれるセル範囲を参照する

example

=OFFSET(INDEX(Sheet1!$A$1:$Z$1,MATCH("性別年齢",Sheet1!$A$1:$Z$1,0)),1,0, データ件数,1)

INDEX(Sheet1!$A$1:$Z$1,MATCH("性別年齢",Sheet1!$A$1:$Z$1,0))部分は、
「目的の列名のセルを参照する数式」で計算済み

結果Sheet1!$B$1をOFFSETの第1引数に代入して

calculate

=OFFSET(Sheet1!$B$1,1,0, データ件数,1)

名前「データ件数」は、以下の演算により5である

calculate_dataNum

=データ件数
=COUNT(Sheet1!$C$2:$C$500)
=5

数式に結果を代入して

calculate2

=OFFSET(Sheet1!$B$1,1,0, 5,1)
=OFFSET(Sheet1!$B$1,1,0, 5,1)

result

=Sheet1!$B$2:$B$6

以上の処理により、
「性別年齢」のデータが含まれるセル範囲を参照することができた

同様に、「購入日」「売上」についても数式に変更を加えると、
定義された名前の参照は以下のようになる

データ件数 : =COUNT(Sheet1!$C$2:$C$500)
性別年齢 　: =OFFSET(INDEX(Sheet1!$A$1:$Z$1,MATCH("性別年齢",Sheet1!$A$1:$Z$1,0)),1,0, データ件数,1)
購入日　 　: =OFFSET(INDEX(Sheet1!$A$1:$Z$1,MATCH("購入日",Sheet1!$A$1:$Z$1,0)),1,0, データ件数,1)
売上　　 　: =OFFSET(INDEX(Sheet1!$A$1:$Z$1,MATCH("売上",Sheet1!$A$1:$Z$1,0)),1,0, データ件数,1)

また、何度も数式内に現れているSheet1!$A$1:$Z$1を名前付き範囲「見出し」として抜き出した場合、
定義された名前の参照は以下のようになる

定義された名前

データ件数 : =COUNT(Sheet1!$C$2:$C$500) 
見出し　　 : =Sheet1!$A$1:$Z$1
性別年齢 　: =OFFSET(INDEX(見出し,MATCH("性別年齢",見出し,0)),1,0, データ件数,1)
購入日　 　: =OFFSET(INDEX(見出し,MATCH("購入日",見出し,0)),1,0, データ件数,1)
売上　　 　: =OFFSET(INDEX(見出し,MATCH("売上",見出し,0)),1,0, データ件数,1)

「データ件数」についてのみ、日付セルの列が変更になった場合手動で範囲を変更する

例）日付データがA列にあるとき

データ件数 : =COUNT(Sheet1!$A$2:$A$500)

集計用関数

example

=SUMIFS(売上, 性別年齢,"男*", 購入日,">=2015/04/01", 購入日,"<2016/04/01")

以上の変更により、
列の入れ替わりに対応した動的な集計を行うことができるようになった

Lv.7(ex) 列の入れ替わりに完全対応させる

Lv.6の数式では、定義された名前「データ件数」についてのみデータ列の入れ替わりに手動で変更しなければならない
→ 「データ件数」の列も動的に定義することにより、柔軟性を最大限に高めることができる

「データ件数」として使用する列の位置が動かないとき、
この改善は処理速度を落とし、式を複雑化させるだけであるため、不要である

以下、説明を省略し、Lv.6で使用したテクニックを用いて「データ件数」の列を動的に定義する

定義された名前

before

データ件数 : =COUNT(Sheet1!$C$2:$C$500)

after

データ件数 : =COUNT(OFFSET(INDEX(見出し,MATCH("購入日",見出し,0)),1,0,499))

おわりに

以上の内容を理解すれば、Excelの関数集計スキルについて中級者クラスといえる状態だと思います。
数式が複雑化するとバグが出やすいので、表を作成した後は必ずテストを行いましょう。

【Excel】 SUMIFS入門からOFFSETを活用した動的集計まで

本記事で扱う内容

はじめに

一覧「IF系集計関数／IFS系集計関数」

Lv.0 IF系集計関数

成果物

説明

Lv.1 IFS集計関数化

成果物

説明

手順1. SUMIF関数をSUMIFS関数化する

手順2. 条件2「購入日：2015年4月1日から」を追加する

手順3. 条件3「購入日：2016年3月31日まで」を追加する

xxIFS関数の注意

Lv.2 「名前付きセル範囲」を利用する

成果物

説明

Lv3. テーブルの構造化参照を使用する

Lv.4(ex) 「名前付きセル範囲」を可変長にする

成果物

説明

性別年齢のセル範囲を動的に決定する

購入日のセル範囲を動的に決定する

Lv.5(ex) #VALUE!エラーを防ぐ

成果物

説明

Lv.6(ex) 列の入れ替わりに対応させる

成果物

説明

目的の列名のセルを参照する数式

目的の列名のデータ範囲を参照する数式

Lv.7(ex) 列の入れ替わりに完全対応させる

おわりに

手順1.　SUMIF関数をSUMIFS関数化する

手順2.　条件2「購入日：2015年4月1日から」を追加する

手順3.　条件3「購入日：2016年3月31日まで」を追加する