条件付き集計関数：概要
「条件付き集計関数」は、特定の条件を満たすデータを対象に集計を行うExcelの関数群です。これにはAVERAGEIF, SUMIF, COUNTIFなどの関数があり、これらは単一の条件を基にデータを集計します。さらに高度なAVERAGEIFS, SUMIFS, COUNTIFSなどの関数もあり、これらは複数の条件を指定して集計を行うことができます。ただし複数条件が設定可能である関数でも条件指定はAND条件のみでありOR条件の指定ができないことに留意してください。
- 検索対象が単一条件のみ
- 検索対象に複数の条件が使用可能(複数指定時はAND条件処理)
- COUNTIFS: 複数の条件を満たすセルの数を範囲内でカウントします。
- SUMIFS: 複数の条件を満たすセルの合計値を範囲内で計算します。
- AVERAGEIFS: 複数の条件を満たすセルの平均値を範囲内で計算します。
- MAXIFS: 複数の条件を満たすセルの中での最大値を取得します。
- MINIFS: 複数の条件を満たすセルの中での最小値を取得します。
「条件付き集計関数」の「条件」や「検索条件」という引数は、英語版Excelでは共に「criteria」として表されます。これは特定の条件を設定して、その条件を満たすデータを集計対象とするために用いられる引数です。例えば、特定の値が閾値を超えるかどうかを基準にデータを集計する場合などに利用します。この「条件」を利用することで、大規模なデータセットから必要なデータだけを効率的に抽出して集計を行うことが可能になります。
このコーナーでは「条件付き集計関数」の引数である「条件」「検索条件」の設定方法について解説します。
「条件」「検索条件」「criteria」の指定例
以下は「条件付き集計関数」の引数である「条件」「検索条件」「criteria」の意味と使用サンプルです。「A」は『文字データ』、「N」は『数値データ』、「D」が『日付/時刻データ』とします。
サンプルにはわかりやすくCOUNTIF関数を使っています。第一引数が「範囲」、第二引数が「検索条件(criteria)」ですが、他の条件付き集計関数でも使用方法は同じです。
一致と不一致
| 名称 | 意味：集計対象 | 使用例とルール |
|---|---|---|
| 完全一致(文字) | Aと等しい | =COUNTIF(C2:C28,”会社法”) 文字データは”で囲む |
| 完全一致(数字) | Nと等しい | =COUNTIF(E2:E28,1400) 数値単独ならば記号では囲まない |
| 完全一致(日付/時刻) | Dと等しい | =COUNTIF(C2:C28,”2014/4/27″) スラッシュ(/)区切りの日付・コロン(:)区切りの時刻は「”」で囲む。 |
| 不一致(文字) | Aと等しくない A以外 |
=COUNTIF(C2:C28,”<>会社法”) 先頭に半角不等号ペアを左から並べて記号と文字を”で囲む |
| 不一致(数字) | Nと等しくない N以外 |
=COUNTIF(E2:E28,”<>1200″) 数値でも不一致検索ならば検索条件を”で囲む |
| 空白一致 | 空白・空欄である | =COUNTIF(E2:E28,””) 空白は「””」 |
| 空白不一致 | 空白・空欄以外 セルに値がある |
=COUNTIF(E2:E28,”<>”) 空白以外は「<>」 |
数値・日付・時刻との大小比較
不等号とイコールを同時使用する場合は不等号が先になります。
| 名称 | 意味：集計対象 | 使用例とルール |
|---|---|---|
| 大なり・超 | Nより大きい Dより後 |
=COUNTIF(E2:E28,”>1400″) 半角の>で開始し、比較対象と共に”で囲む。 |
| 以上・以降 | Nを含めてNより大きい N以上 Dを含めてDより後 D以降 |
=COUNTIF(E2:E28,”>=1400″) 半角の>=で開始し、比較対象と共に”で囲む。 |
| 小なり・未満 | Nより小さい Dより前 |
=COUNTIF(E2:E28,”<1400″) 半角の<で開始し、比較対象と共に”で囲む。 |
| 以下・以前 | Nを含めてNより小さい N以下 Dを含めてDより前 D以前 |
=COUNTIF(E2:E28,”<=1400″) 半角の<=で開始し、比較対象と共に”で囲む。 |
文字列の部分一致
半角の「*」「?」はワイルドカートとよびます。そこへは何が入力されていても可とします。「*」は文字数不特定のワイルドカードです。「?」は1文字限定のワイルドカードです。
| 名称 | 意味：集計対象 | 使用例とルール |
|---|---|---|
| 前方一致 | Aで始まる | =COUNTIF(C2:C28,”会社法*”) *には何文字が入っても可とする |
| 前方不一致 | Aで始まらない | =COUNTIF(C2:C28,”<>会社法*”) |
| 後方一致 | Aで終わる | =COUNTIF(C2:C28,”*入門”) |
| 後方不一致 | Aで終わらない | =COUNTIF(C2:C28,”<>*入門”) |
| 部分一致1 | Aを含む | =COUNTIF(C2:C28,”*税*”) |
| 部分不一致 | Aを含まない | =COUNTIF(C2:C28,”<>*税*”) |
| 部分一致2 | Aを含む(文字数限定) | =COUNTIF(C2:C28,”?法???”) 半角?は1文字分を意味する |
| 部分一致3 | Aを含む(*?の検索) | =COUNTIF(C2:C28,”*~?”) 「*」や「?」を検索文字列にする場合は前に「~」を付ける |
練習用のサンプル表
上記の計算式のテストをするためのサンプル表です。Excelへコピーして計算設定のテストをすることができます。※「=””」は画面には表示されません。
| 書籍番号 | 書名 | 発刊日 | 金額 | 在庫 | 条件1 | 会社法 | |
| 1001 | ケーススタディ民法 | 2016/10/28 | 1400 | 有 | 条件2 | 1400 | |
| 1002 | 交通六法 | 2016/02/27 | 2700 | 無 | 条件3 | 2014/04/27 | |
| 1003 | 法人税法の基礎 | 2015/12/27 | 2700 | 無 | 条件4 | 1200 | |
| 1004 | 会社法 | 2014/10/27 | 2800 | 無 | 条件5 | 入門 | |
| 1005 | 民法総則 | 2015/03/27 | 2700 | 条件6 | 税 | ||
| 1006 | 判例六法 | 2016/01/27 | 2400 | 有 | 条件7 | ||
| 1007 | 基礎法入門 | 2014/09/27 | 2500 | 有 | 条件8※ | =”” | |
| 1008 | やさしい経済法 | 2015/08/27 | 2500 | 無 | 条件9 | <> | |
| 1009 | 刑法各論 | 2016/07/28 | 2100 | ||||
| 1010 | 現近代民法 | 2014/10/27 | 1400 | 有 | |||
| 1011 | 会社法概論 | 2014/04/27 | 2000 | 有 | |||
| 1012 | 商法総則 | 2014/04/27 | 1900 | 有 | |||
| 1013 | 憲法 | 2016/09/28 | 2500 | 無 | |||
| 1014 | 商法わかるかな? | 2015/11/27 | 1600 | 有 | |||
| 1015 | 倒産法入門 | 2014/01/27 | 2300 | 無 | |||
| 1016 | 破産法 | 2015/02/27 | 1200 | 有 | |||
| 1017 | 法社会学 | 2016/10/28 | 1500 | 無 | |||
| 1018 | 民事訴訟法学 | 2016/08/28 | 2800 | ||||
| 1019 | 法学入門 | 2015/09/27 | 2500 | 有 | |||
| 1020 | 労働法の要点 | 2015/05/27 | 2400 | 無 | |||
| 1021 | はやわかり刑法 | 2016/05/28 | 3000 | 無 | |||
| 1022 | 講義式憲法 | 2016/10/28 | 3000 | 有 | |||
| 1023 | 刑法講義 | 2016/05/28 | 2600 | 有 | |||
| 1024 | 会社法入門 | 2016/09/27 | 1300 | 無 | |||
| 1025 | 民法事例集 | 2015/06/27 | 1200 | 無 | |||
| 1026 | 六法全書 | 2014/03/27 | 2300 | 無 | |||
| 1027 | 労働法実務辞典 | 2014/09/27 | 2700 | 有 |
各種詳細情報
条件付き集計関数の「完全一致検索(文字対象)」の解説
条件付き集計関数の「完全一致検索(文字対象)」の設定方法 下記の「Excel 条件付き集計関数：一覧」にある関数群において、引数「条件」もしくは「検索条件」(英語版の引数名は共に...
Read more条件付き集計関数の「完全一致検索(数字対象)」の解説
条件付き集計関数の「完全一致検索(数字対象)」の設定方法 下記の「Excel 条件付き集計関数：一覧」にある関数群において、引数「条件」もしくは「検索条件」(英語版の引数名は共に...
Read more条件付き集計関数の「完全一致検索(日付/時刻対象)」の解説
条件付き集計関数の「完全一致検索(日付/時刻対象)」の設定方法 下記の「Excel 条件付き集計関数：一覧」にある関数群において、引数「条件」もしくは「検索条件」(英語版の引数名...
Read more条件付き集計関数の「(完全)不一致検索」の解説
条件付き集計関数の「(完全)不一致検索」の設定方法 下記の「Excel 条件付き集計関数：一覧」にある関数群において、引数「条件」もしくは「検索条件」(英語版の引数名は共に「cr...
Read more条件付き集計関数の「空白・空欄検索」の解説
条件付き集計関数の「空白・空欄検索」の設定方法 下記の「Excel 条件付き集計関数：一覧」にある関数群において、引数「条件」もしくは「検索条件」(英語版の引数名は共に「crit...
Read more条件付き集計関数の「空白・空欄不一致検索」の解説
条件付き集計関数の「空白・空欄不一致検索」の設定方法 下記の「Excel 条件付き集計関数：一覧」にある関数群において、引数「条件」もしくは「検索条件」(英語版の引数名は共に「c...
Read more条件付き集計関数の「大なり・超」検索の解説
条件付き集計関数の「大なり・超」検索の設定方法 下記の「Excel 条件付き集計関数：一覧」にある関数群において、引数「条件」もしくは「検索条件」(英語版の引数名は共に「crit...
Read more条件付き集計関数の「以上・以降」検索の解説
条件付き集計関数の「以上・以降」検索の設定方法 下記の「Excel 条件付き集計関数：一覧」にある関数群において、引数「条件」もしくは「検索条件」(英語版の引数名は共に「crit...
Read more条件付き集計関数の「小なり・未満・より前」検索の解説
条件付き集計関数の「小なり・未満・より前」検索の設定方法 下記の「Excel 条件付き集計関数：一覧」にある関数群において、引数「条件」もしくは「検索条件」(英語版の引数名は共に...
Read more条件付き集計関数の「以下・以前」検索の解説
条件付き集計関数の「以下・以前」検索の設定方法 下記の「Excel 条件付き集計関数：一覧」にある関数群において、引数「条件」もしくは「検索条件」(英語版の引数名は共に「crit...
Read more条件付き集計関数の「前方一致検索」の解説
条件付き集計関数の「前方一致検索」(～で始まる)の設定方法 下記の「Excel 条件付き集計関数：一覧」にある関数群において、引数「条件」もしくは「検索条件」(英語版の引数名は共...
Read more条件付き集計関数の「後方一致検索」の解説
条件付き集計関数の「後方一致検索」(～で終わる)の設定方法 下記の「Excel 条件付き集計関数：一覧」にある関数群において、引数「条件」もしくは「検索条件」(英語版の引数名は共...
Read more条件付き集計関数の「部分一致検索」の解説
条件付き集計関数の「部分一致検索」(～を含む)の設定方法 下記の「Excel 条件付き集計関数：一覧」にある関数群において、引数「条件」もしくは「検索条件」(英語版の引数名は共に...
Read more
コメント