Excelの「名前付き範囲」は、飛び飛びのセルやL字型など“非矩形（非連続）”でも定義できます。しかし集計で定番のCOUNTIFは「1つの連続範囲」を前提にしているため、そのままだと期待通りに数えられません。本記事では、非矩形の名前付き範囲をCOUNTIF相当で数える実務的な解決策を、Excelの新旧環境別に整理して解説します。
起きている現象：非矩形の名前付き範囲にCOUNTIFが効かない
次のような状況が典型例です。
- 「名前付き範囲」を作った（例：対象セル）
- 対象セルが長方形ではなく、L字型・一部欠け・複数ブロック（非連続）になっている
=COUNTIF(対象セル, 条件)で一致数を数えたい
ところが実際には、エラーになる／一部しか数えない／期待と違う数になるといったトラブルが発生します。これは「名前付き範囲」の作成自体は可能でも、COUNTIFの処理系が“複数エリア参照（非連続参照）”を前提にしていないことが原因です。
なぜCOUNTIFは非矩形（複数エリア）に弱いのか
Excelでは、非連続のセル集合を名前として定義すると、内部的には「複数エリア（Area）の集合」として扱われることがあります。たとえば「名前の管理」で参照範囲を見ると、次のようにカンマ区切りで表示されるケースです。
='Sheet1'!$B$2:$B$10,'Sheet1'!$D$2:$D$10,'Sheet1'!$F$2:$F$10このような“複数エリア参照”は、SUMやAVERAGEのように複数範囲の合算を得意とする関数だと問題になりにくい一方、COUNTIF/COUNTIFSのような「範囲を走査して条件判定する」系では、単一の連続範囲を想定した実装になっていることが多く、想定外の挙動になりがちです。
| 項目 | 連続（矩形）範囲 | 非連続（複数エリア）範囲 |
|---|---|---|
| 名前付き範囲として定義 | 問題なし | 問題なし（定義はできる） |
| COUNTIFで集計 | 想定通りに数える | 一部だけ／エラー／想定外になりやすい |
| SUMで合算 | 想定通り | 多くのケースで想定通り |
まず確認：あなたの「名前付き範囲」は複数エリアか
対処法を選ぶ前に、対象の名前が「複数エリア参照」になっているかを確認します。
- Excelの数式タブ → 名前の管理を開く
- 該当の名前を選択し、参照範囲（Refers to）を確認する
参照範囲にカンマ（,）が含まれていれば、複数エリア参照である可能性が高いです。L字型や欠けた形は、内部的に「複数の長方形の寄せ集め」として表現されやすいため、このパターンがよく出ます。
解決策A：TOCOLで「1列」に潰してから数える（動的配列が使える環境）
Excel 365 / Microsoft 365（動的配列対応）を中心に、TOCOLが使える環境なら、最も汎用的でメンテしやすい方法です。発想はシンプルで、非矩形をいったん“1列の配列”に正規化してから条件一致を数えるだけです。
基本形（完全一致を数える）
=SUM(--(TOCOL(名前付き範囲, 1) = 条件セル))TOCOL(…, 1)：範囲を1列に並べる。第2引数を1にすると空白を無視しやすい= 条件セル：一致判定（TRUE/FALSE）--：TRUE/FALSE → 1/0 へ変換SUM：1の合計＝一致数
COUNTIFの条件式（”>=10″ や ワイルドカード）をそのまま使いたい場合
「条件セル」に >=10 や *東京* のようなCOUNTIFの条件文字列を入れている場合、比較演算（=）ではなく、COUNTIFに戻したほうが意図が一致します。TOCOLで配列化したあとなら、COUNTIFが素直に働くことが多いです。
=COUNTIF(TOCOL(名前付き範囲, 1), 条件セル)たとえば、条件セルに次のような値を入れておく運用ができます。
| 条件セルの例 | 意味 |
|---|---|
完了 | 「完了」と等しいセルの数 |
<> | 空白以外の数（※空白判定はデータの実態に注意） |
>=10 | 10以上の数値の数 |
*東京* | 「東京」を含む文字列の数 |
実務向け：LETで読みやすく・高速にする
同じTOCOL結果を式の中で何度も参照すると、計算が重くなることがあります。LETで配列を一度だけ作り、再利用すると安定します。
=LET(r, TOCOL(名前付き範囲, 1),
SUM(--(r = 条件セル)))エラー値が混ざるときの安全策
対象範囲に #N/A や #VALUE! 等のエラーが含まれると、比較の途中で式全体がエラーになりやすいです。集計を止めたくない場合は、IFERRORで0扱いにします。
=LET(r, TOCOL(名前付き範囲, 1),
SUM(--IFERROR(r = 条件セル, 0)))注意：TOCOLが「複数エリア参照」を受け取れない場合の対処
環境や参照の形によっては、名前付き範囲が複数エリア参照だと TOCOL/TOROW がうまく配列化できず、#VALUE! になることがあります。その場合は、「名前付き範囲（セル参照）」ではなく「名前付き数式（配列を返す定義）」に作り替えるのが近道です。
例：複数のブロック（範囲1〜範囲3）を縦に連結して一覧化する名前を作る
（名前：対象一覧）
=TOCOL(VSTACK(範囲1, 範囲2, 範囲3), 1)この「対象一覧」は単一のスピル配列になるので、以降は次のように素直に数えられます。
=COUNTIF(対象一覧, 条件セル)“非矩形のまま集計で苦しむ”より、集計用に一次元リストへ正規化する名前を用意するほうが、ファイル全体の保守性が上がります。
解決策B：REDUCE + TOROWで走査して数える（LAMBDA系が使える環境）
LAMBDAが使える環境では、配列を1要素ずつ処理して「一致したら加算する」というロジックを直接書けます。式の見た目は少し難しく感じますが、考え方はプログラムのfor文に近いです。
基本形
=REDUCE(0, TOROW(名前付き範囲),
LAMBDA(acc, x, acc + (x = 条件セル)))TOROW：範囲を横1列に潰す（1次元化）REDUCE：左から順にacc（累計）へ足し込む(x = 条件セル)はTRUE/FALSEで返るので、加算すると 1/0 として扱われる
空白無視・エラー回避を入れた実務形
実データでは空白やエラーが混ざることが多いので、条件を噛ませた方が事故が減ります。
=LET(r, TOROW(名前付き範囲),
REDUCE(0, r,
LAMBDA(acc, x,
acc + IFERROR(--(x = 条件セル), 0)
)))TOROWは「並べ替え方（行優先/列優先）」の癖が出る場合があります。順序が重要なケース（例：先頭から何件目、など）で使うときは、期待の並びになっているかを一度だけ目視確認してください。
解決策C：範囲を分割してCOUNTIFを足し算（古い環境でも可）
Excel 2016以前や、動的配列・LAMBDAが使えない環境なら、最も確実なのは「非矩形を構成する各エリアごとにCOUNTIFして合計する」方法です。地味ですが互換性が高く、ファイル共有の現場では今でも有効です。
基本形（足し算）
=COUNTIF(範囲1, 条件) + COUNTIF(範囲2, 条件) + COUNTIF(範囲3, 条件)読みやすい形（SUMにまとめる）
=SUM(COUNTIF(範囲1, 条件),
COUNTIF(範囲2, 条件),
COUNTIF(範囲3, 条件))「非矩形の名前付き範囲」を無理に1つの名前にまとめるのではなく、エリアごとに名前を分けておくと保守が楽になります。
- 名前：
対象_左、対象_右、対象_下のように分割 - 集計式：
=SUM(COUNTIF(対象_左,条件),COUNTIF(対象_右,条件),COUNTIF(対象_下,条件))
どの解決策を選ぶべきか（環境別のおすすめ）
| 解決策 | 必要なExcel機能 | 強み | 弱み | おすすめ度 |
|---|---|---|---|---|
| TOCOLで1列化して集計 | 動的配列（主にMicrosoft 365） | 式が短い／汎用性が高い／再利用しやすい | 環境差で複数エリア参照が扱いづらい場合がある | 高 |
| REDUCE+TOROW | LAMBDA/REDUCE/TOROW | ロジックを自由に拡張できる | 式が難しく見えやすい／学習コスト | 中〜高 |
| COUNTIFを足し算 | ほぼ全バージョン | 互換性が最強／挙動が読みやすい | 範囲追加のたびに式修正が必要になりがち | 中 |
実例：L字の対象セルで「完了」を数える
たとえばチェックシートで、入力エリアがL字（上段の見出し行＋左側の項目列）になっており、そこに「完了」と入った数だけ集計したいケースを考えます。対象を名前付き範囲入力対象、条件セルをH2（値：完了）とします。
Microsoft 365でのおすすめ
=COUNTIF(TOCOL(入力対象, 1), $H$2)完全一致以外（「完了」を含む）を数えたい場合
入力が「完了（確認済）」のようにブレる運用なら、ワイルドカードで数えます。
=COUNTIF(TOCOL(入力対象, 1), "*" & $H$2 & "*")古い環境の例（3ブロックに分割している場合）
=SUM(COUNTIF(入力対象_左, $H$2),
COUNTIF(入力対象_上, $H$2),
COUNTIF(入力対象_右, $H$2))COUNTIFSで同じことをしたいときの考え方
「条件が1つ」なら上記でほぼ解決しますが、実務では「部署=営業 かつ ステータス=完了」のように条件が増えがちです。ここでつまずくのが COUNTIFS です。COUNTIFSは複数範囲を渡しますが、基本的に範囲サイズや形状が一致している必要があり、非矩形・非連続では破綻しやすいです。
Microsoft 365であれば、配列に潰してからブール演算（掛け算）でAND条件を作るのが実務的です。
例：同じセル群から「完了」かつ「営業」を数える（同一セルに両条件が入る想定ではなく、別列条件の場合は設計を見直します）
=LET(r, TOCOL(名前付き範囲, 1),
SUM(--(r="完了") * --(別の配列="営業")))別の配列側も同じ順序で潰せている必要があるため、非矩形を扱う場合は、“見た目のレイアウト”と“集計用データ”を分離する設計（テーブル化、縦持ち化）が結果的に近道になることが多いです。
つまずきポイントと対処（チェックリスト）
| 症状 | よくある原因 | 対処 |
|---|---|---|
| COUNTIFが一部しか数えない | 名前付き範囲が複数エリアで、COUNTIFが先頭エリアだけ見ている | TOCOL/TOROWで1次元化、またはエリア別にCOUNTIFして合計 |
| #VALUE! が出る | 複数エリア参照を動的配列関数が扱えない／式の引数が合っていない | 名前を「配列を返す数式」に作り替える（VSTACK+TOCOLなど） |
| 同じ見た目なのに数えられない | 数値が文字列になっている、全角半角スペースが混ざっている | TRIM/CLEAN/VALUEで正規化してから比較する |
| 空白を数えたいのに数えられない | TOCOLの第2引数で空白を無視している | TOCOL(…,0) を使う、またはCOUNTIF側の条件を調整 |
| 計算が重い | TOCOLや比較を何度も再計算している | LETで配列を一度だけ作り、式を簡素化する |
“非矩形のまま集計しない”ための設計アイデア
レイアウト都合でL字や飛び飛びが避けられないシートはありますが、集計が増えるほど苦しくなります。長期運用するファイルなら、次のどれかを用意すると安定します。
- 集計用の一覧（縦持ち）を別シートに作る：入力は見た目重視、集計はテーブル重視に分離
- 名前付き数式で「対象一覧」を作る：VSTACK/TOCOLで一次元配列化し、集計はその配列に対して行う
- 入力規則やチェック列を追加：対象セルにフラグを立て、矩形範囲でCOUNTIFできるように寄せる
特に「対象一覧」という考え方は強力で、COUNTIFだけでなく、UNIQUE/SORT/FILTERなどにも繋がります。非矩形は“見せ方”として割り切り、集計は“データ”として整形する、という発想に切り替えると、Excel運用が一段ラクになります。
最後に：最短で解決するならこの1行
Microsoft 365を使っていて、まずは「一致数を数える」だけなら、次の形から始めるのが最短です。
=COUNTIF(TOCOL(名前付き範囲, 1), 条件セル)これでうまくいかない場合は、名前付き範囲が複数エリア参照として扱われている可能性が高いので、VSTACKで一覧化する「名前付き数式」を作る方針に切り替えると、同じ悩みを繰り返さずに済みます。
コメント