MENU
  1. ホーム
  2. ソフトウェア
  3. Excel
  4. Excelで複数条件の合計をスマートに求める方法：SUMPRODUCTと配列数式の活用術

Excelで複数条件の合計をスマートに求める方法：SUMPRODUCTと配列数式の活用術

Excel

Excelで、特定の条件を満たすデータだけを合計したいケースはよくありますよね。縦方向にステータス、横方向に月ごとの数値が並ぶ表から、ConfirmedやPipelineだけを素早く集計できれば業務効率はぐんとアップします。今回は、SUMPRODUCTや配列数式を使って、複数条件に合ったデータをきれいに取り出す方法を詳しくご紹介します。

目次
  1. やりたいことと課題
    1. よくあるSUMIF関数の制限
  2. SUMPRODUCT関数を活用する
    1. 基本的なSUMPRODUCTの書き方
    2. SUMPRODUCTのメリット・デメリット
  3. Office 365(現Microsoft 365)での配列数式
    1. 配列数式で条件付き合計を行う方法
  4. 実際のシート例：ステータスと月別合計
    1. 合計列を追加してピボットテーブル化する方法
  5. SUMPRODUCT vs. 配列数式：使い分けのポイント
  6. その他の実用的なテクニック
    1. 文字列の部分一致を条件にする
    2. 日時や日付を条件にする
    3. OR条件を実現する
  7. トラブルシューティングと注意点
    1. 数値データが文字列に変換されている問題
    2. 配列の範囲がずれている問題
    3. 絶対参照と相対参照の使い分け
  8. まとめ：条件付き合計を自在に操るExcelテクニック
    1. 次のステップへ

やりたいことと課題

Excelを使った集計作業の中でも、「縦方向に条件があり、横方向にもデータが並んでいて、特定の条件に合う部分だけを合計したい」というニーズは非常に多いです。たとえば、営業管理のシートにおいて、ステータス（Confirmed/Pipeline）を縦軸、月ごとの受注金額や予測金額を横軸に並べているケースを想像してみてください。

  • 縦軸：A列がステータス（「Confirmed」「Pipeline」など）
  • 横軸：B列からF列にかけて月別の数値が入力されている

このようなレイアウトだと、一般的なSUMIF関数だけでは「縦の条件」で合計ができても、「横方向にまたがる複数列を一度に合計」することが少し面倒になる場合があります。さらに、複数条件（たとえばステータス＋他の条件）を指定したい、もしくは月別の範囲をもっと柔軟に扱いたいとなると、SUMPRODUCTや配列数式のほうがパワフルに機能することが多いのです。

よくあるSUMIF関数の制限

SUMIFは条件を1つだけ指定して合計を出す関数として有名です。しかし以下のような制約があります。

  • 条件範囲と合計範囲のサイズが同じである必要がある
  • 複数条件を扱いたい場合にはSUMIFSを使う必要がある
  • SUMIF関数のみでは、縦方向だけではなく横方向にも複雑な条件を課すと難しくなる

もちろんSUMIFS関数を使えば、「ステータスがConfirmed」で「売上月が2月」というように複数の縦方向条件を設定できます。ただ、配列演算や合計範囲が複数列に及ぶ場合など、さらに複雑な要件が出てきたときに柔軟に対処するのは少々骨が折れます。

SUMIFSでも対応できるケース

とはいえ、たとえば「ステータスがConfirmed」で「月が4月」というように区切りがはっきりしていれば、SUMIFS関数を使って解決できる場面も多いです。下記のように書けば、特定の月だけを絞り込む合計が可能です。

=SUMIFS($B:$B, $A:$A, "Confirmed", $C:$C, "4月")

上記のケースのように、月を文字列で保持しているとか、集計列が1列だけなど、条件指定がシンプルであればSUMIFSで十分です。しかし、複数列にわたる合計や、特定の文字列部分一致などを同時に扱う場合は、SUMPRODUCT関数や配列数式のほうが力を発揮することもあるでしょう。

SUMPRODUCT関数を活用する

まずは、もっとも汎用性が高いSUMPRODUCT関数を使う方法を見ていきます。SUMPRODUCTは、もともと「複数の範囲同士を要素ごとに掛け合わせ、その合計を求める」ための関数です。配列演算ができるため、条件式を掛け合わせることで非常に柔軟な集計を実現できます。

基本的なSUMPRODUCTの書き方

実際にConfirmedのデータだけを合計するには、次のような式がよく使われます。

=SUMPRODUCT(B2:F6 * (A2:A6="Confirmed"))
  • B2:F6：合計対象の数値範囲
  • A2:A6="Confirmed"：縦方向にあるステータスが「Confirmed」であるかどうかをTRUE/FALSEの配列で返す式

ExcelではTRUEを1、FALSEを0として扱うため、掛け合わせると「条件を満たす行だけが合計される」という仕組みになっています。たとえば行2が「Confirmed」なら、その行に対応する数値のみ計算され、行3が「Pipeline」なら、そこは0（FALSE）扱いとなり足し算に加わりません。

複数条件を掛け合わせる

SUMPRODUCTの良いところは、複数条件を簡単に組み合わせられる点です。ステータスが「Confirmed」かつ、別の列で「担当者がAさん」などの追加条件を入れる場合、以下のように書けます。

=SUMPRODUCT(B2:F6 * (A2:A6="Confirmed") * (G2:G6="Aさん"))

ここで(G2:G6="Aさん")がさらにTRUE/FALSEの配列として機能し、ConfirmedかつAさんの行のみが合計されるのです。このように、AND条件を多数重ねられるため、よくある営業管理シートや在庫管理シートなどで非常に便利に使えます。

SUMPRODUCTのメリット・デメリット

  • メリット
  1. 古いバージョンのExcelでも問題なく動く（配列数式を明示的に入力する必要がない）。
  2. 複数条件をAND（掛け合わせ）するのが容易。
  3. OR条件も工夫すれば扱える（+演算を使うなど）。
  • デメリット
  1. 配列演算をたくさん重ねると複雑な式になりやすい。
  2. 大量データを扱うと計算速度が遅くなる場合がある。
  3. OR条件の指定がSUMIF系に比べるとやや複雑。

また、SUMPRODUCTは元々配列演算に強い関数なので、縦軸と横軸を自由に掛け合わせて集計できるという特徴があります。ステータスが複数種類ある場合でも、同じロジックで集計範囲を拡張できるため、単純なSUMIFよりも柔軟性が高いことが多いです。

Office 365(現Microsoft 365)での配列数式

次に、Microsoft 365のExcelであれば使える「動的配列数式」について触れてみましょう。旧バージョンのExcelでは配列数式を入力する際に「Ctrl+Shift+Enter」（CSE）で確定する必要がありましたが、Microsoft 365では自動で配列数式が有効になるため、より簡潔に書くことができます。

配列数式で条件付き合計を行う方法

SUMPRODUCTとほぼ同様の考え方で、下記のように書きます。

=SUM((B2:F6)*(A2:A6="Confirmed"))

ここで、(A2:A6="Confirmed")の部分はTRUE/FALSEの配列を返します。TRUEが1、FALSEが0として扱われ、最終的にSUM関数が数値の合計を返す仕組みです。Microsoft 365では特に「Ctrl+Shift+Enter」を押す必要がなく、単純にEnterを押せばOK。CSE配列数式の煩わしさがなくなったのは大きなメリットと言えます。

配列数式の長所と注意点

  • 長所
  1. 数式がシンプル：=SUM(...)で完結するため、可読性が高い。
  2. 自動的に配列認識：古いExcelのようにCSE入力がいらない。
  3. スピル機能との相性が良い：条件に応じた行データを配列として一気に展開できる。
  • 注意点
  1. 旧バージョンとの互換性：Microsoft 365以外で開くと数式が機能しないケースがある。
  2. 大規模データの計算速度：大量の配列演算を行うと、ブック全体の再計算が重くなる場合もある。
  3. OR条件の組み合わせ：AND条件（掛け算）は簡単だが、OR条件（足し算）を組むときは式が複雑になりがち。

たとえば「ConfirmedまたはPipelineの行を合計したい」場合は、(A2:A6="Confirmed")+(A2:A6="Pipeline")という配列演算にし、さらに数値範囲を掛け合わせることになります。慣れるまで少し分かりにくく感じるかもしれません。

実際のシート例：ステータスと月別合計

ここで、実際に表を例示してみましょう。下記のような表を想定します。

ABCDEF
Status1月2月3月4月5月
Confirmed10020015010050
Pipeline8010012090110
Confirmed200150180120130
Pipeline100130140160100
Confirmed150170130180200

たとえば、A列2行目から6行目に「Confirmed」「Pipeline」などのステータスが入り、B列2行目からF列6行目に月ごとの数値が入力されているイメージです。この場合、Confirmedだけの合計を出したいのであれば、先ほどのSUMPRODUCT式を使い以下のようにします。

=SUMPRODUCT(B2:F6 * (A2:A6="Confirmed"))

もし「Pipelineだけの合計」も同様に求めたければ、

=SUMPRODUCT(B2:F6 * (A2:A6="Pipeline"))

と書くだけで完了です。もちろん、配列数式を使うなら

=SUM((B2:F6)*(A2:A6="Pipeline"))

としてもOKです。さらに担当者やプロジェクト名など、別の列に条件が追加になったときも、掛け合わせを増やすだけで柔軟に対応できます。

合計列を追加してピボットテーブル化する方法

余談ですが、Excelで条件付き集計を行う上で、ピボットテーブルを使う方法も非常に有効です。対象データが縦長の構造（行がレコード、列がフィールド）になっていれば、ピボットテーブルを組むことで、ステータス別や月別の合計をドラッグ＆ドロップだけで簡単に作成できるからです。

ただし、ピボットテーブルは自動集計のための機能なので、元データの配置によってはレイアウトを変更しなければならない場合もあります。それが難しいときや、数式だけで完結させたいときには、SUMPRODUCTや配列数式がベストな選択肢になるでしょう。

SUMPRODUCT vs. 配列数式：使い分けのポイント

では、SUMPRODUCTと配列数式のどちらを使うべきか。実は、両者は非常に似た動きをします。しかし、次のような基準で判断すると良いでしょう。

  1. Excelバージョンの互換性が重要かどうか
  • 旧バージョンを使っているメンバーもいる場合はSUMPRODUCTがおすすめ。
  • Microsoft 365環境だけなら配列数式でも問題なく動く。
  1. 式の見やすさを重視するかどうか
  • SUMPRODUCTは慣れるとわかりやすいが、複数条件が増えるとかえってごちゃごちゃすることも。
  • 配列数式のほうがシンプルに書ける場合も多い。
  1. 計算負荷
  • どちらも配列演算なので、データ量が増えると負荷は相応に大きくなる。
  • 特定のケースではSUMPRODUCTのほうが高速だったり、配列数式のほうが高速だったり、Excelのバージョンや実装によって差がある。試行錯誤が必要。

その他の実用的なテクニック

ここでは、複数条件を扱うときに知っておくと便利なテクニックをいくつかご紹介します。

文字列の部分一致を条件にする

たとえばステータスが「Confirmed – urgent」「Pipeline – high」など、少し複雑な文字列になっている場合は、部分一致を使って条件を設定できます。以下のようにSEARCH関数を組み合わせると柔軟です。

=SUMPRODUCT((B2:F6) * (ISNUMBER(SEARCH("Confirmed", A2:A6))))
  • SEARCH("Confirmed", A2:A6)：文字列「Confirmed」がA列内に含まれている場合は文字位置を返す
  • ISNUMBER(...)：文字位置が存在すればTRUE、見つからなければFALSE
  • TRUEを1、FALSEを0として扱い、最終的に合計を算出

日時や日付を条件にする

月別の集計をさらに細かく日付ごとに行いたい場合や、特定の期間内だけ合計したい場合は、(日付セル >= 開始日) * (日付セル <= 終了日)のように掛け合わせると、範囲が条件を満たすものだけ合計可能です。

=SUMPRODUCT(B2:B100 * (A2:A100 >= "2025/01/01") * (A2:A100 <= "2025/01/31"))

ここでA列が日付データの場合、1月分だけ合計するといった処理が実現できます。もちろん文字列が混在しているとエラーになりやすいので注意しましょう。

OR条件を実現する

たとえば「ステータスがConfirmedまたはPipelineのデータだけ」を合計したい場合は、以下のように+で繋ぐと「OR条件」になります。

=SUMPRODUCT((B2:F6) * ((A2:A6="Confirmed") + (A2:A6="Pipeline")))
  • (A2:A6="Confirmed") + (A2:A6="Pipeline")
  • Confirmedの場合はTRUE+FALSE=1、Pipelineの場合はFALSE+TRUE=1、その他の場合はFALSE+FALSE=0

このように、AND条件（掛け算）とOR条件（足し算）を組み合わせることで、複雑なロジックを表現できます。

トラブルシューティングと注意点

SUMPRODUCTや配列数式を使用する上で、意外にハマりやすいポイントをいくつか整理しておきます。

数値データが文字列に変換されている問題

Excelでは、見た目が数字でも実は文字列として入力されているケースがあります。例えば、CSVから取り込んだデータなどで起こりがちです。この場合、SUMPRODUCTで掛け算しても「#VALUE!」エラーが出たり、あるいは計算結果がおかしくなったりします。数値として正しく認識されるように、VALUE関数や一括変換を使って整形すると安定します。

配列の範囲がずれている問題

SUMPRODUCTを使う際には、条件となる範囲と合計対象の範囲の行数・列数が一致している必要があります。例えば、合計範囲がB2:F7、条件範囲がA2:A6のようにずれていると正しい結果が得られません。集計する範囲をしっかり確認しておくことが大事です。

絶対参照と相対参照の使い分け

たとえば、数式を下の行へコピーして使いたい場合には、$A$2:$A$6などの絶対参照にしておかないと、コピー時に範囲が動いてしまいます。一方、縦に範囲をずらしながら集計したいときには相対参照が便利です。使いどころを誤ると、意図しない結果になるので要注意です。

まとめ：条件付き合計を自在に操るExcelテクニック

縦軸と横軸の両方に条件が散らばっているデータから合計を取り出すには、SUMIFやSUMIFSだけでは対応しきれない場面が出てきます。そんなときはSUMPRODUCTや配列数式を使うことで、配列演算を駆使して柔軟に合計が可能です。

  • SUMPRODUCT関数
  • 古いバージョン含めどのExcelでも使える
  • AND条件やOR条件を掛け算・足し算で表現できる
  • 大量データでも安定動作しやすいが、複雑な式になりがち
  • Microsoft 365の配列数式
  • よりシンプルな書き方が可能
  • 自動で配列演算を認識してくれる
  • 旧バージョンとの互換性はやや注意が必要

また、状況によってはピボットテーブルを使う、あるいはPower QueryやVBAなども検討すると、さらに効率的な集計が実現できます。とはいえ、何よりまずは数式で合計したいという要望に対して、SUMPRODUCTと配列数式は非常に頼れる味方になるはずです。

次のステップへ

実際の業務では、ステータスだけでなくプロジェクトの進捗状況や担当者の組み合わせなど、条件がたくさんあるかもしれません。SUMPRODUCTで条件を追加するだけでもかなり対応できますが、さらに高度な分析をするならピボットテーブルやPower Pivot、BIツールなどへのステップアップも視野に入れると良いでしょう。

もしExcelのバージョン差による不具合や、式のパフォーマンスが気になる場合は、まずは少量のデータで検証し、同僚やチームメンバーのExcel環境でも問題なく動くかをチェックしてから本番シートに適用すると安心です。

以上を踏まえて、複数条件の合計が必要な場面では、ぜひSUMPRODUCTと配列数式を使いこなし、自分にとってもっともわかりやすくメンテナンスしやすい方法を探ってみてください。慣れてしまえば非常に強力で自由度の高い集計を実現できます。

Excel
Excel SUMPRODUCT 配列数式 関数活用

コメント

コメントする

目次
  1. やりたいことと課題
    1. よくあるSUMIF関数の制限
  2. SUMPRODUCT関数を活用する
    1. 基本的なSUMPRODUCTの書き方
    2. SUMPRODUCTのメリット・デメリット
  3. Office 365(現Microsoft 365)での配列数式
    1. 配列数式で条件付き合計を行う方法
  4. 実際のシート例：ステータスと月別合計
    1. 合計列を追加してピボットテーブル化する方法
  5. SUMPRODUCT vs. 配列数式：使い分けのポイント
  6. その他の実用的なテクニック
    1. 文字列の部分一致を条件にする
    2. 日時や日付を条件にする
    3. OR条件を実現する
  7. トラブルシューティングと注意点
    1. 数値データが文字列に変換されている問題
    2. 配列の範囲がずれている問題
    3. 絶対参照と相対参照の使い分け
  8. まとめ：条件付き合計を自在に操るExcelテクニック
    1. 次のステップへ