SUMIFSの使い方!複数条件・OR条件の指定方法も解説

SUMIFSの使い方!複数条件・OR条件の指定方法も解説 OPPOの使い方

ExcelのSUMIFS関数は、複数の条件をすべて満たすデータを特定の範囲から探し出し、対応する数値データの合計を計算するための非常に強力で便利な関数です。単一条件の合計に使うSUMIF関数よりも複雑な集計が可能で、データ分析やレポート作成の効率を大幅に向上させます。本記事では、SUMIFS関数の基本的な構文から、複数条件(AND条件)、OR条件の指定方法、さらには実用的なヒントまで、詳しく丁寧に解説します。

SUMIFS関数の基本的な使い方

SUMIFS関数は、指定した複数の「条件範囲」の中からそれぞれの「条件」に一致する行を特定し、それらの行に対応する「合計対象範囲」の数値の合計を返します。

構文

=SUMIFS(合計対象範囲, 条件範囲1, 条件1, [条件範囲2, 条件2], ...)
  • 合計対象範囲 (sum_range): 必須。実際に合計したい数値が含まれるセル範囲を指定します。(例: `C2:C10`)
  • 条件範囲1 (criteria_range1): 必須。1つ目の条件で検索するセル範囲を指定します。(例: `B2:B10`)
  • 条件1 (criteria1): 必須。1つ目の条件範囲内で検索する条件を指定します。数値、文字列、日付、セル参照、または比較演算子を含む文字列(例: `”関東”`, `”>=400″`, `A1`)で指定します。
  • [条件範囲2, 条件2], …: 任意。2つ目以降の条件範囲と条件のペアを指定します。最大127個の条件ペアまで指定可能です。

重要: 合計対象範囲 とすべての 条件範囲 は、同じ行数・列数である必要があります。サイズが異なると `#VALUE!` エラーが発生します。

基本的な使用例

以下のような売上データ(A1:C5セルにあると仮定)があるとします。

名前 (A列) エリア (B列) 売上 (C列)
田中 関東 500
鈴木 関西 300
佐藤 関東 450
高橋 東北 600

このデータから「関東」エリアの売上合計を求めるには、次のように入力します。

=SUMIFS(C2:C5, B2:B5, "関東")
  • C2:C5: 合計したい売上データの範囲。
  • B2:B5: 条件(”関東”)で検索するエリアデータの範囲。
  • "関東": 検索する条件。

結果: 500 (田中) + 450 (佐藤) = 950

複数条件の指定方法 (AND条件)

SUMIFS関数の最大の特長は、複数の条件をすべて満たす (AND条件) データを集計できることです。条件ペアを追加していくだけで、AND条件として扱われます。

例: エリアが「関東」かつ売上が400以上

上記のデータ表で、関東エリアで売上が400以上の合計を求めるには、次のように条件ペアを追加します。

=SUMIFS(C2:C5, B2:B5, "関東", C2:C5, ">=400")
  • C2:C5: 合計対象範囲 (売上)。
  • B2:B5, "関東": 1つ目の条件ペア (エリアが関東)。
  • C2:C5, ">=400": 2つ目の条件ペア (売上が400以上)。

結果: 500 (田中) + 450 (佐藤) = 950 (両方の条件を満たす)

OR条件の指定方法

SUMIFS関数は、標準ではAND条件で動作します。OR条件(いずれかの条件を満たす)を指定するには、いくつかの工夫が必要です。

方法1: 同じ列に対するOR条件 (配列定数を使う)

同じ列に対して複数のOR条件を指定する場合(例: エリアが「関東」または「関西」)、配列定数 {} を使うのが最もスマートです。

例: エリアが「関東」または「関西」の売上合計

=SUM(SUMIFS(C2:C5, B2:B5, {"関東","関西"}))
  • {"関東","関西"}: 条件として配列定数を指定します。これにより、SUMIFSは「関東」の合計と「関西」の合計をそれぞれ計算した配列を返します。
  • SUM(...): SUMIFSが返した配列(各条件ごとの合計)を、最後にSUM関数で合計します。

結果: [950 (関東), 300 (関西)] という配列が内部的に作られ、SUM関数で合計されて 1250 となります。

方法2: 異なる条件の合計を加算 (SUMIFSを複数回使う)

異なる種類の条件に対するOR条件のような集計(例: 「関東エリアの合計」または「売上500以上の合計」)を行いたい場合、それぞれの条件でSUMIFS関数を計算し、その結果を単純に足し合わせる方法があります。

例: 「関東エリアの合計」と「売上500以上の合計」を足し合わせる

=SUM(SUMIFS(C2:C5, B2:B5, "関東"), SUMIFS(C2:C5, C2:C5, ">=500"))

解説: SUMIFS(C2:C5, B2:B5, "関東") は関東の合計 (950) を計算し、SUMIFS(C2:C5, C2:C5, ">=500") は売上500以上の合計 (500 + 600 = 1100) を計算します。これらをSUM関数(または単純な `+`)で合計します。

結果: 950 + 1100 = 2050

注意: この方法は、両方の条件を満たすデータ(例: 関東エリアで売上500以上の田中さんのデータ)が重複してカウントされることに注意してください。純粋なOR条件(どちらかの条件を満たす行を重複なく合計)とは異なります。

方法3: 複雑な条件の組み合わせ (方法1とAND条件の組み合わせ)

複数のOR条件(同じ列)とAND条件(他の列)を組み合わせることも可能です。

例: エリアが「関東」または「関西」で、かつ売上が400以上の合計

=SUM(SUMIFS(C2:C5, B2:B5, {"関東","関西"}, C2:C5, ">=400"))

解説: 配列定数を使ってエリア条件を指定し、さらに売上条件を追加します。SUMIFSは「関東で400以上」の合計と「関西で400以上」の合計の配列を返し、それをSUM関数で合計します。

結果: [950 (関東&400以上), 0 (関西&400以上)] という配列が作られ、合計は 950 となります。

(元の記事の例 `=SUM(SUMIFS(C2:C5, B2:B5, “関東”, C2:C5, “>=400”), SUMIFS(C2:C5, B2:B5, “関西”, C2:C5, “>=400”))` も同じ結果になりますが、配列定数を使う方が簡潔です。)

条件での比較演算子とワイルドカード

条件 (criteria) の指定には、比較演算子やワイルドカードも利用でき、より柔軟な条件設定が可能です。

  • 比較演算子:
    • ">500": 500より大きい
    • "<=1000": 1000以下
    • "<>0": 0以外 (等しくない)
    • "<>東京": 文字列「東京」以外
    • ">"&A1: セルA1の値より大きい(セル参照と組み合わせる場合)
  • ワイルドカード (文字列検索用):
    • "田中*": 「田中」で始まる文字列(例: 田中太郎, 田中商事)
    • "*工業": 「工業」で終わる文字列(例: 佐藤工業, ABC工業)
    • "*システム*": 「システム」を含む文字列
    • "??木": 任意の2文字+「木」で構成される3文字の文字列(例: 鈴木, 高木)

ワイルドカード文字 (`*`, `?`, `~`) 自体を検索したい場合は、チルダ (`~`) を前につけます (例: `~*`, `~?`, `~~`)。

SUMIFS関数の注意点とベストプラクティス

  • 範囲のサイズ: 合計対象範囲 とすべての 条件範囲 の行数・列数が完全に一致している必要があります。ずれていると `#VALUE!` エラーになります。
  • 空白セルの扱い: 条件で空白セルを指定する場合は "" (空の文字列) ではなく "=" を使います。空白でないセルは "<>" を使います。
  • 大文字/小文字: 文字列の条件では、大文字と小文字は区別されません(”関東” と “かんとう” は区別されますが、”Tokyo” と “tokyo” は区別されません)。
  • 数値と文字列: 見た目が数字でも文字列として格納されているデータは、数値の条件では一致しません。データ型を確認しましょう。
  • パフォーマンス: 非常に大きなデータ範囲(数十万行以上)に対して多数のSUMIFS関数を使用すると、再計算に時間がかかることがあります。必要に応じて計算方法を手動にしたり、Power Pivotなどの利用を検討したりしましょう。

SUMIF関数との違い

SUMIF関数は、単一の条件に基づいて合計を計算する関数です。SUMIFS関数はSUMIF関数の上位互換のような位置づけで、複数の条件を扱えます。

  • SUMIF構文: `=SUMIF(条件範囲, 条件, [合計範囲])`
    • 合計範囲は省略可能(省略時は条件範囲が合計される)。
    • 引数の順番がSUMIFSと異なります(合計範囲が最後)。
  • 使い分け:
    • 条件が1つだけの場合は、SUMIF関数の方がシンプルです。
    • 条件が2つ以上ある場合は、SUMIFS関数を使用します。
    • SUMIFS関数は条件が1つの場合でも使用できるため、「常にSUMIFSを使う」とルール化するのも一つの方法です。

まとめ

SUMIFS関数は、複数のAND条件に基づいて特定のデータを効率的に集計するためのExcel/スプレッドシートにおける必須関数のひとつです。基本的な使い方から、配列定数を使ったOR条件の指定、比較演算子やワイルドカードの活用までマスターすれば、複雑なデータ分析やレポート作成が格段にスムーズになります。引数の指定順序や範囲のサイズに注意し、ぜひこの強力な関数を日々の業務で活用してみてください。

コメント