FILTER関数で条件に合う行を抽出したあと、合計や件数、平均を出したい場面は多く、「=SUM(FILTER(...)) と書くか、それとも =SUMIFS(...) を使うか」で迷いやすい場所です。この記事では、Excel 2024 の実機計測つきで両方の書き方を整理し、FILTER と SUMIFS・COUNTIFS の使い分けの基準をまとめます。
結論を先に言うと、行を並べたいなら FILTER、数値を 1 つ返したいなら SUMIFS・COUNTIFS・AVERAGEIFS が基本です。速度差は 10 万行・100 セル規模のダッシュボードでおよそ 3 倍、A:A 全列参照を混ぜると 14 倍以上になりました。
FILTER と SUMIFS・COUNTIFS の使い分け早見表
先に全体像を表で示します。やりたいことごとに「どれを使うべきか」が決まっています。
| やりたいこと | 推奨関数 | 理由 |
|---|---|---|
| 条件に合う行を一覧表示 | FILTER | 行を返せるのは FILTER だけ |
| 合計を求める | SUMIFS | 最速・最シンプル（FILTER 比 約 3 倍速） |
| 件数を求める | COUNTIFS | 同上（FILTER 比 約 3.5 倍速）。A:A 全列参照との相性も最良 |
| 平均・最大・最小 | AVERAGEIFS / MAXIFS / MINIFS | 同上 |
| 複雑な条件を組み合わせたい | SUM(FILTER(...)) | 列をまたいだ OR や、条件側に関数・計算式を使う判定に強い |
| 同じ数式内で抽出結果を再利用 | LET + FILTER | FILTER 呼び出しを 1 回で済ませられる（約 1.9 倍速） |
以降では、FILTER の結果を合計・カウント・平均する具体的な書き方と、SUMIFS・COUNTIFS のほうが速い場面、どうしても FILTER で集計する場面、それぞれを実機データつきで整理します。
FILTER 結果を合計する書き方（SUM+FILTER）
FILTER で絞り込んだ結果の合計を出すには、外側を SUM で包みます。次の数式は、売上表から「地域が東京」の金額だけを合計します。
=SUM(FILTER(G2:G11, B2:B11="東京"))
結果がスピルしない（単一値を返す）ため、動的配列に不慣れでも扱いやすい書き方です。FILTER が 0 件を返したときは #CALC! エラーになるので、その対策は Excel のスピル #N/A や空結果をまとめて処理する方法 を参考にしてください。
FILTER 結果を数える 3 パターン（ROWS / COUNTA / COUNT）
FILTER の結果件数を数えるには ROWS・COUNTA・COUNT の 3 通りが使えます。どれも「東京の件数」を数える用途では同じ 5 を返します。ただし、空文字や 0 を含む列では返る件数が変わるため、用途に応じて使い分けます。
- ROWS は配列の「行数」を返すため、抽出された行数を確実に得られる。最も意図がブレない
- COUNT は「数値が入っているセル」だけを数えるため、文字列だけの列に対して数えると 0 になる。数値列に対してだけ使う
- COUNTA は「空でないセル」を数えるが、FILTER の戻り値では元の空セルが
0や空文字に変換されるため、想定より大きい数を返すことがある。期待とズレやすいので注意する
「何行抽出されたか」を知りたい場面では、ROWS を選んでおけば間違いがありません。COUNTA は FILTER の戻り値で想定とズレやすく、避けたほうが無難です。
FILTER 結果の平均・最大・最小
平均・最大・最小も、SUM と同じように外側で包むだけです。
=AVERAGE(FILTER(G2:G11, B2:B11="東京"))=MAX(FILTER(G2:G11, B2:B11="東京"))=MIN(FILTER(G2:G11, B2:B11="東京"))
FILTER が 0 件を返したときは、AVERAGE や MAX で #DIV/0! や #CALC! になります。事前に FILTER の第 3 引数（見つからなかったときの値）を指定するか、IFERROR で外包する対策が必要です。
でも基本は SUMIFS・COUNTIFS を使おう
ここまでで FILTER 経由で合計・件数・平均を出す書き方を見てきましたが、実務で最初に検討すべきは SUMIFS・COUNTIFS・AVERAGEIFS などの IFS 系関数です。判断基準は 1 つだけ、「返したいものが行の一覧か、数値 1 つか」です。
=SUMIFS(G2:G11, B2:B11, "東京")同じ結果（14,500）を返しますが、書き方がシンプルで、後述のとおり計算速度にも差が出ます。件数は COUNTIFS 関数の基本、複数条件の合計は SUMIFS で日付が期間内の合計を求める方法 などが参考になります。
速度比較 ― 10 万行のダッシュボードで実測
「FILTER で集計するのと IFS 系でやるのとで、どのくらい差が出るのか」を、配布サンプルブック（10 万行）で実機計測しました。同じ条件式を 100 セルに並べた「コピーして下方向にドラッグ」の典型シナリオで、3 回測った中央値を採用しています。サンプルブックには 速度計測 シートが入っており、計算方法を「手動」に切り替えてから対象セクションを選択し、F9 で計測すれば読者の環境でも同じ手順で再現できます。具体的な手順はシート冒頭に書いてあります。
| 数式 | 実行時間 | 基準との比 |
|---|---|---|
=SUMIFS(金額, 地域, "東京") | 0.32 秒 | 基準 |
=SUM(FILTER(金額, 地域="東京")) | 0.94 秒 | 約 2.9 倍 |
=COUNTIFS(地域, "東京") | 0.26 秒 | 基準 |
=ROWS(FILTER(地域, 地域="東京")) | 0.92 秒 | 約 3.5 倍 |
単一セルでの差はミリ秒単位で、体感できるレベルではありません。ただし、50 セル以上のダッシュボードや、条件を変えて再計算を繰り返すブックでは合計で 0.5 秒以上の差として見えてきます。数値 1 つで済むなら、FILTER で包まず IFS 系で書くほうが安全です。サンプルブックの 速度計測 シートで「計算方法を手動 → 対象セクションを選択 → F9」の手順で測ると、同じ傾向を読者の環境で確認できます。
速度はお使いの PC の性能や Excel のビルドによって変動します。ここで示した値は Excel 2024 Build 19929、Windows 11 Pro の環境で計測した目安です。
それでも FILTER で集計すべき場面
SUMIFS・COUNTIFS が速いからといって、すべての集計を IFS 系で書けるわけではありません。次のような場面は FILTER 経由の集計が正解になります。
複数の条件を組み合わせて「含まない」を判定したいとき
単純な「含まない」なら SUMIFS でも書けます。ワイルドカードを <> で打ち消す形です。
=SUMIFS(G2:G11, D2:D11, "<>*ファイル*")複数列を AND で除外するのも SUMIFS で書けます。たとえば「商品カテゴリが『ファイル』を含まず、かつ担当者が『佐藤』を含まない」なら次のようになります。
=SUMIFS(G2:G11, D2:D11, "<>*ファイル*", C2:C11, "<>*佐藤*")FILTER のほうが向くのは、除外条件を OR でまとめたい、列をまたいだ条件を組みたい、SEARCH や計算式の結果を他の条件と論理演算で組み合わせたいといったケースです。論理式の積や和で自由に組み立てられます。
=SUM(FILTER(G2:G11, ISERROR(SEARCH("ファイル", D2:D11)) * (B2:B11<>"札幌")))SUMIFS の <>*ファイル* はそれ自体が部分一致の否定として機能しますが、SEARCH の結果を他の条件と論理演算で組み合わせるような書き方には向きません。「SEARCH + ISERROR で部分一致しない判定をしつつ、別列の条件と AND/OR で重ねる」ような場面では FILTER に切り替えます。
OR 条件で複数値をまとめて集計したいとき
同一列の単純な OR なら、SUMIFS に配列定数を渡して SUM で外包する書き方が短くて済みます。
=SUM(SUMIFS(G2:G11, B2:B11, {"東京","大阪","名古屋","福岡"}))FILTER で同じ結果を出すには、論理式を加算でつなぎます。SUMIFS と FILTER の結果は完全に一致します。
=SUM(FILTER(G2:G11, (B2:B11="東京")+(B2:B11="大阪")+(B2:B11="名古屋")+(B2:B11="福岡")))FILTER のほうが向くのは、列をまたいだ OR を書きたい、条件側に式や関数を挟みたいといった場面です。たとえば「地域が東京、または金額が 5,000 以上」のように 2 列以上を OR でまとめるときは FILTER が素直に書けます。
=SUM(FILTER(G2:G11, (B2:B11="東京")+(G2:G11>=5000)))COUNTIFS で同じ問題が起きる場面の整理は、COUNT・COUNTIFS 関数で OR 条件が使用できないため代替手段を検討する も参考になります。
抽出結果と集計を 1 枚の表で両方出したいとき
同じ条件で「行の一覧」と「合計・件数」を両方出したい場合、FILTER の結果をスピル範囲演算子 # で再利用すると、FILTER を 1 回だけ計算して済ませられます。
=FILTER(A2:G11, B2:B11="東京")=SUM(INDEX(I1#, , 7))LET で FILTER を 1 回に圧縮して約 2 倍速にする
同じ数式内で FILTER の結果を複数回使うときは、LET で変数化するだけで大きく高速化できます。たとえば「東京の金額の平均」を FILTER 経由で出すとき、素朴に書くと FILTER が 2 回計算されます。
=SUM(FILTER(G2:G11, B2:B11="東京"))/ROWS(FILTER(G2:G11, B2:B11="東京"))これを LET で書き換えると、FILTER は 1 回だけ計算されます。
=LET(x, FILTER(G2:G11, B2:B11="東京"), SUM(x)/ROWS(x))
なお、単純な平均値を出すだけなら =AVERAGEIFS(G2:G11, B2:B11, "東京") を使うのが最速（LET 版の約 2 倍）です。LET が価値を持つのは、AVERAGEIFS では書けない条件（列をまたいだ OR、条件側に関数や計算式を挟む条件など）で FILTER を使う必要があり、かつ FILTER の結果を 1 つの数式の中で 2 回以上参照する場面です。複数セル間で FILTER の結果を共有する効果はないため、LET の効きは「同じ数式の中で何回 FILTER を使い回すか」だけに依存します。
A:A 全列参照は FILTER で書くと約 14 倍遅くなる
「データの行数が今後増えていく前提で、範囲を A:A で指定しておきたい」というケースはよくあります。この書き方は IFS 系では問題ありませんが、FILTER と組み合わせると劇的に遅くなります。
これは、FILTER が引数の範囲全体（最大 1,048,576 行）を一度マスク配列に展開するために起きます。範囲のサイズがそのまま計算コストになるため、A:A や B:B のような全列指定と FILTER の相性は悪いと覚えておいてください。
対策は 2 つあります。1 つは $A$2:$A$10001 のように行数を明示すること。もう 1 つはデータ範囲をテーブル（Ctrl+T）に変換して、テーブル名[列名] の構造化参照で渡すことです。どちらも FILTER のスキャン範囲を実データの行数に絞れます。
サンプルブックのダウンロード
記事内で使ったサンプルデータ（日付・地域・担当者・商品カテゴリ・数量・単価・金額の 7 列、10 万行）と、主要な速度比較を手元で再現できる 速度計測 シートを配布します。シートには 100 セルの基本比較・50 セルの LET 比較・10 セルの全列参照比較の 3 セクションが入っています。計算方法を「手動」に切り替えてから対象セクションを選択し、F9 を押してストップウォッチで時間を測る流れです。手順はシート冒頭にも記載しています。
まとめ
- 条件に合う「行」がほしいなら
FILTER、数値 1 つでよいならSUMIFS/COUNTIFS/AVERAGEIFS - ダッシュボード規模（100 セル・10 万行）で計ると、FILTER 経由の集計は IFS 系の約 3 倍の時間がかかる
- 同一数式内で FILTER を 2 回以上参照するなら、
LETで変数化すると約 1.9 倍速になる A:A全列参照を FILTER と組み合わせると 10〜15 倍遅くなるため、範囲は実データ行に絞るかテーブル化する- 件数の確実な数え方は
ROWS、数値だけを数えるならCOUNT。COUNTAは FILTER の戻り値で挙動が変わるので避ける
「行を並べたいなら FILTER、数値 1 つなら IFS 系」の原則を軸に、速度が気になる場面だけ LET や範囲指定の工夫を足していく、という順番がもっとも迷いません。
コメント