多くの項目のあるデータから特定のデータの合計を出すときには、SUMIF関数やSUMIFS関数を使うと便利です。
自分で確認しながら1つずつ合計することもできますが、関数を使えば簡単に合計の値を集計できます。SUMIFやSUMIFS関数は、経理などビジネスでもよく使われている関数なのでしっかり身に付けておきましょう。
今回は「SUMIF関数」と複数条件で合計できる「SUMIFS関数」の使い方を紹介します。
SUMIF関数が使われるケース
SUMIF関数やSUMIFS関数は多くのデータから特定のデータの合計を出すため、次のような集計表を作成することが可能です。
- 担当者別の売上合計
- 顧客ごとの来店回数
- 支店ごとの売上表
SUMIF関数の構文
SUMIF関数の構文は次のとおりです。
=SUMIF(範囲,検索条件,[合計範囲])
- 第1引数「範囲」：検索する範囲を指定します。
- 第2引数「検索条件」：検索する値やセル番号、演算子を使った式が入れられます。
- 第3引数「合計範囲」：第1関数と同じ場合は省略可能です。
SUMIF関数の使い方
SUMIF関数もSUMIFS関数も、同じ操作で関数の入力を始められます。
あらかじめ関数を入力するセルを選択しておき、メニューバーの「挿入」から「関数」を選びましょう。
「SUMIF」と「SUMIFS」は「数学」の関数です。「数学」から関数名を選択するとセル内に関数が入ります。
見本のデータでは、売上管理表から取引先別の売上合計を出します。関数の引数はドラッグやクリックして範囲を選択することもできますが、慣れてきたら直接入力することも可能です。
この例では、次のような関数を入れました。
=SUMIF($C$3:$C$36,G3,$D$3:$D$36)
このデータではH3に入れた関数をH9までオートフィルでコピーする予定があるため、第1引数と第3引数には絶対参照を使用しています。
絶対参照の使い方に慣れていない場合は、次の説明も参考にして入力してください。
絶対参照を使った入力手順
絶対参照を使用したSUMIF関数の入力方法を簡単に紹介します。
- セルにSUMIF関数を入れた後、「=SUMIF()」のかっこの中にカーソルが入っている状態で、検索する範囲であるC3からC36までをドラッグして選択しましょう。
- 選択した直後にキーボートで「F4」キーを押すと=SUMIF($C$3:$C$36)と入力されます。
- 「,」で区切り、次に検索条件「G3」を選択します。G3の取引先名はコピーする範囲に合わせて移動させたいので絶対参照は使用しません。
- 「,」で区切り、第3引数のD3:D36をドラッグして指定します。その後、F4キーで絶対参照にしてください。
条件にワイルドカードを使う方法
先ほどSUMIFを使って取引先別売上を見てみると、B社の合計が「0」になってしまいました。
これは、「B社神奈川支店」や「B社東京支店」が別の取引先とみなされて、正しく集計されなかったためです。
「B社〇〇」の取引先を検索するためには、B社の後ろにワイルドカードの「*」を付ける必要があります。
Point ワイルドカードについて
ワイルドカードは、特定の文字の代わりに使用できる記号です。SUMIF関数では「*」や「?」がよく使われています。
- 「*」：文字列の前後に付けて、複数の文字の代用とする
- 「?」：文字列の前後に付けて、ある1文字の代用とする
SUMIF関数の引数にワイルドカードを使う場合に、注意するポイントがあります。
「*」は文字列として扱われるためダブルクオーテーション「”」で囲むことが必要です。
また、条件のセルG4と文字列を結びつけるために「&」を使用します。
このデータでは、「G4&”*”」と入力すると、「B社〇〇」が検索できるようになります。
完成した関数は次のとおりです。
=SUMIF($C$3:$C$36,G4&”*”,$D$3:$D$36)
SUMIFS関数で複数条件を使用する方法
SUMIFで条件を指定できるのは1つだけでしたが、SUMIFS関数を使えば複数の条件で集計が可能です。
SUMIFS関数の構文は次のとおりになります。
=SUMIFS(合計対象範囲,条件範囲1,条件1,条件範囲2,条件2)
見本のデータでは、担当者別売上表のH13のセルにSUMIFS関数を入れて担当者別の月ごとの売上合計を計算します。
第1関数は合計する範囲なので、売上の値を指定しましょう。このデータでは他のセルに数式をオートフィルでコピーする予定があるため、絶対参照を忘れないようにしてください。
次に第2と第3引数に条件1を指定しましょう。このデータでは担当者G列の担当者を探すため、F4キーを3回押して「$G13」とします。
Point 絶対参照と相対参照について
行と列を固定することを参照といい、行か列のどちらかだけ固定することを相対参照といいます。操作は次のとおりです。
| 行と列を固定 | F4を1回押す |
| 行を固定 | F4を2回押す |
| 列を固定 | F4を3回押す |
| 固定を解除 | F4を4回押す |
同様に、2つ目の条件をします。完成した関数は次のとおりです。この関数は1月2月…と横方向にもコピーする予定のため、条件2のH12はF4キーを2回押して行を固定して「H$12」としておきましょう。
=SUMIFS($D$3:$D$36,$E$3:$E$36,$G13,$A$3:$A$36,H$12)
オートフィルを使って担当者別売上表全体にコピーしてください。
