SUMIF関数の検索条件を使いこなして効率化しよう
SUMIF関数は、条件付きの合計を出す関数です。SUM関数は指定範囲の数値をすべて合計しますが、SUMIF関数は指定範囲の、条件に一致する数値だけ合計します。検索条件を工夫することで、さまざまな集計表が作成可能です。SUMIF関数の検索条件を使いこなして、日々の作業を効率化しましょう。
目次
SUMIF関数を使うとき
SUMIF関数を使う場面は、条件を付けて集計したいときです。日々の受注データから、特定の日付だけ、特定の商品だけといった条件のデータを集計できます。指定できる条件は、文字列・数値・日付・関数・式・セル参照です。
SUMIF関数は、1つの条件に対応した集計が可能です。複数の条件を指定するときは、SUMIFS関数を使用します。
SUMIF関数の基本
SUMIF関数の記述方法をご紹介します。
| =SUMIF(検索範囲,検索条件,[合計範囲]) |
SUMIF関数は、2つか3つの引数（ひきすう）を使います。3つ目の合計範囲を省略したときは、検索範囲が合計範囲を兼ねます。
引数とは、関数が結果を出すために必要な要素です。カッコ「()」内に記述します。
検索範囲の指定
検索範囲の指定は必須です。
データから、条件を指定する項目のセル範囲を指定します。例えば、商品名ごとに集計するときは、商品名のセル範囲を指定してください。
検索条件の指定
検索条件の指定は必須です。
文字列・数値・日付・関数・式・セル参照のいずれかを指定します。数値・セル参照・関数以外の値を入力するときは、「”」ダブルクオーテーションで前後を囲んでください。また、ワイルドカード文字を使用できます。
※ワイルドカード文字の詳細は、後述します。
合計範囲の指定
合計範囲の指定は省略可能です。省略すると、検索範囲の数値を合計します。例えば、金額が1,000円以上の商品に限って、金額を集計するときは、検索範囲も合計範囲も金額になるため、合計範囲は省略できます。
合計範囲を指定するときは、検索範囲と同じ行数・列数を指定しないと正しく計算されないため、範囲をよく確認してください。
h2：検索条件に使う記号
複雑な検索条件の指定には、比較演算子やワイルドカード文字といった記号を使います。SUMIF関数で使える記号をご紹介します。
SUMIF関数に使える比較演算子
SUMIF関数では、検索条件に「>」といった比較演算子を使うことができます。比較演算子と数値を使って、以下のような条件を指定できます。数値のほかに、日付も比較が可能です。日付は過去に向かって小さくなり、未来に向かって大きくなります。
| 記号と数値 | 意味 |
| >5 | 5より大きい |
| <5 | 5より小さい |
| >=5 | 5以上 |
| <=5 | 5以下 |
| =5 | 5と等しい |
| <>5 | 5と等しくない |
SUMIF関数に使えるワイルドカード文字
ワイルドカード文字を使うと、文字列の一部が一致するかしないかを判定できます。ワイルドカード文字は、「*」と「?」です。例えば、「田中*」は、田中で始まる文字列です。「田中正」も「田中一郎」も一致と判定します。「*」と「?」の違いは文字数です。「?」は任意の1文字を表し、文字数が合わないと不一致と判定します。「*」は文字数が関係ありません。「田中?」で指定すると、「田中正」は一致ですが「田中一郎」は文字数が合わないため不一致です。
条件指定のバリエーション
検索条件指定の方法は、大きく分けて2つあります。文字列や数式を直接入力する方法と、条件が入力されたセルを参照する方法です。項目ごとの合計一覧を作るときは、セル参照を使いこなすと便利です。例えば、鉛筆・ノート・消しゴムといった数種類の受注金額をSUMIF関数で表すとします。受注集計表の商品名欄に、鉛筆・ノート・消しゴムと入力し、このセルを検索条件として参照してください。この方法であれば、ひとつひとつの条件を変えなくても、数式のコピーで商品名を参照します。このとき、検索範囲と合計範囲は絶対参照、検索条件は相対参照としてください。
絶対参照と相対参照についてはこちらの記事を参考にしてください。
Excel 関数を使うときの必須条件 相対参照と絶対参照 | 伊川直助が、ExcelとACCESSを解説
文字列や数字を指定する
鉛筆・ノート・消しゴムといった商品名や商品番号、文房具や雑貨といった分類名を使って集計するときは、集計表の商品名を参照するか、直接入力します。
文字列を入力するときは、「”」ダブルクオーテーションで前後を囲んでください。
| =SUMIF($C$2:$C$9,”鉛筆”,$D$2:$D$9) |
キャラクター鉛筆・鉛筆・キャラクターノート・ノートといった商品のうち、「キャラクター」を含む商品の金額を集計したいときは、「*」ワイルドカードを使います。
| =SUMIF($C$2:$C$9,”*キャラクター*”,$D$2:$D$9) |
数値だけを指定するときは、そのまま入力します。商品の分類コードが「2010」の金額を集計するときは「2010」と直接入力します。
| =SUMIF($B$2:$B$9,2010,$D$2:$D$9) |
比較演算子を使って条件を絞る
「>」大なり「<」小なりといった比較演算子を使って、条件を絞りましょう。
■数字を使う
年齢や商品コードといった数字に、比較演算子を付けます。年齢が30歳以上の人の数値を集計するときは”>30”と入力します。
| =SUMIF($A$2:$A$9,”>=30″,$D$2:$D$9) |
■日付を使う
指定する日より前か後かによって、集計条件を変える方法です。2021/12/21より後の受注金額を集計するときは”>2021/12/21″と入力します。前であれば”<2021/12/21″です。
| =SUMIF($A$2:$A$9,”>2021/12/21″,$D$2:$D$9) |
■直近1か月の集計をする
TODAY関数とEDATE関数を使って、1か月前の日付を使って集計します。日々の受注金額データから、直近1か月分のみ抽出するときに使える方法です。
| =SUMIF($A$2:$A$9,”>=”&EDATE(TODAY(),-1),$D$2:$D$9) |
| TODAY関数 | TODAY() | 現在の日付を取得する |
| EDATE関数 | EDATE(開始日,月) | 開始日から〇か月前、〇か月後の日付を取得する。
〇は月で指定した数字。月で指定した数字がマイナスは前、プラスは後。 |
空白だけ・空白を除く
元データに空白があるときは、以下のように条件を指定します。
■空白だけ集計
空白だけを集計するときは、条件に””と入力してください。例えば、手配済みの明細は「〇」未手配は空白のままといった一覧から、空白の未手配のみ集計するときに使用します。
「”」の間になにも存在しないため、””は空白の意味を表します。
| =SUMIF($E$2:$E$9,””,$D$2:$D$9) |
■空白以外を集計
逆に、空白以外のデータを集計するときは、条件に”<>”と入力します。
「”<>”」は「”<>”&””」(空白と等しくない）を省略したものです。
| =SUMIF($E$2:$E$9,”<>”,$D$2:$D$9) |
検索範囲の空白が、数式の計算結果によって表されているときは、上記の方法では空白と認識されず、集計に含まれてしまいます。そのようなときは、SUM関数で全体を集計したものから、SUMIF関数で空白だけ集計したものを引きます。
| =SUM($D$2:$D$9)-SUMIF($E$2:$E$9,””,$D$2:$D$9) |
SUMIF関数を使って集計作業を効率化しよう
SUMIF関数は、集計作業の効率をあげてくれます。表の作成や集計を日頃から行っているのであれば、SUMIF関数をはじめとしたExcel関数を使いこなしましょう。
説明に使用したデータのイメージです。
| 注文日 | 商品コード | 商品名 | 受注金額 | 手配済み |
| 11/19 | 1010 | 鉛筆 | 500 | 〇 |
| 11/30 | 1010 | ノート | 2400 | 〇 |
| 12/5 | 1020 | キャラクター鉛筆 | 900 | 〇 |
| 12/16 | 1010 | 鉛筆 | 600 | |
| 12/19 | 2010 | キーホルダー | 1500 | △ |
| 12/21 | 1010 | 消しゴム | 300 | △ |
| 12/25 | 1020 | キャラクターノート | 3100 | |
| 12/26 | 2010 | ヘアゴム | 1900 |
今回のサンプルファイルは、No284です。
わからない事を延々と考えるのは、無駄です。
- なんで作動かないの？
- もうやだ！VBAなんか嫌い！
- ネットで調べても情報がない！
必ず作動するコードが、ここにあります。
