Excelの課題一覧をA2:D9のような固定範囲で集計・抽出していると、範囲外へ追加した行が結果に入らない場合があります。対策は、元データをExcelテーブルにし、TaskTable[ステータス]のような構造化参照を使うことです。
この記事では、8行の架空データをTaskTableに変換し、COUNTIFSとFILTERの参照先をテーブルへ変更します。最後に課題Iをテーブルの新しいデータ行として追加し、未完了件数が5件から6件、未完了一覧が5行から6行へ変わるところまで確認します。
この記事の完成例を確認するには、検証済みExcelファイルをダウンロード（.xlsx）してください。検証結果はPDFで確認できます。
完成例｜課題Iを追加すると件数5→6、一覧5行→6行になる
今回の「未完了」は、説明用の業務ルールとして未着手＋進行中とします。完了と中止は除外します。中止を未完了に含めるかは業務によって異なるため、実際の運用では自社の定義に合わせてください。
| 確認項目 | 課題Iの追加前 | 課題Iの追加後 |
|---|---|---|
| 佐藤の未完了件数 | 2 | 2 |
| 鈴木の未完了件数 | 2 | 2 |
| 高橋の未完了件数 | 1 | 2 |
| 未完了件数の合計 | 5 | 6 |
| FILTERの抽出行数 | 5行 | 6行 |
追加後に高橋の件数とFILTERの一覧へ課題Iが現れれば、追加行が今回の集計・抽出の参照対象へ入ったと判断できます。テーブル機能全般の網羅ではなく、この確認をゴールにします。
今回使う8行の課題一覧
A1:D9へ次の表を入力します。人物名、課題名、日付はすべて説明用の架空データです。列順は課題／担当者／ステータス／期限で統一します。
| 課題 | 担当者 | ステータス | 期限 |
|---|---|---|---|
| 課題A | 佐藤 | 未着手 | 2026/10/01 |
| 課題B | 鈴木 | 進行中 | 2026/10/02 |
| 課題C | 佐藤 | 完了 | 2026/10/03 |
| 課題D | 高橋 | 未着手 | 2026/10/04 |
| 課題E | 佐藤 | 進行中 | 2026/10/05 |
| 課題F | 鈴木 | 完了 | 2026/10/06 |
| 課題G | 高橋 | 中止 | 2026/10/07 |
| 課題H | 鈴木 | 未着手 | 2026/10/08 |
元データをExcelテーブルにする
- A1:D9の範囲内を選択します。
- Ctrl＋Tを押します。リボンから操作する場合は、挿入タブのテーブルを選びます。
- 対象範囲が
$A$1:$D$9で、「先頭行をテーブルの見出しとして使用する」がオンになっていることを確認します。 - OKを選びます。
- テーブル内を選択し、テーブル デザインタブのテーブル名を
TaskTableへ変更してEnterキーを押します。
作成後は、数式で次の名前を使える状態になります。
TaskTable[課題]TaskTable[担当者]TaskTable[ステータス]TaskTable[期限]
Microsoft公式では、Excelテーブルの作成方法としてCtrl＋Tが案内されています。テーブル名は、ブック内で分かりやすい名前へ変更できます。
構造化参照とは
構造化参照は、A2:A9のようなセル番地ではなく、テーブル名や列名でテーブルのデータを参照する書き方です。たとえば、TaskTable[担当者]はTaskTableの担当者列、TaskTableは今回のテーブルのデータ部分を参照します。
Microsoft公式では、テーブルに行や列を追加・削除すると、構造化参照が調整されると説明されています。また、テーブル外の数式からも構造化参照を使えます。今回の集計表と抽出結果は元データのテーブル外へ置きます。
列名を変更すると、ブック内で使われている構造化参照の列見出し部分もExcelによって更新されます。ただし、この記事では列名を変更せず、課題・担当者・ステータス・期限の4つを固定して使います。
COUNTIFSをTaskTableの列で集計する
K1:L4へ担当者別の集計表を作ります。K1は「担当者」、L1は「未完了件数」とし、K2からK4へ佐藤、鈴木、高橋を入力します。
| セル | 入力内容 |
|---|---|
| K2 | 佐藤 |
| K3 | 鈴木 |
| K4 | 高橋 |
L2へ次の式を入力し、L4までコピーします。
=COUNTIFS(TaskTable[担当者],K2,TaskTable[ステータス],"未着手")+COUNTIFS(TaskTable[担当者],K2,TaskTable[ステータス],"進行中")初期結果は、佐藤2件、鈴木2件、高橋1件です。合計は5件になります。
ここではCOUNTIFSの原理を詳しく繰り返しません。ポイントは、担当者列とステータス列の参照先を固定セル範囲ではなく、TaskTable[担当者]とTaskTable[ステータス]にしたことです。
FILTERをTaskTableへ変更する
F1:I1へ、課題、担当者、ステータス、期限の見出しを入力します。F2へ次の式を入力してください。
=FILTER(TaskTable,(TaskTable[ステータス]="未着手")+(TaskTable[ステータス]="進行中"),"該当なし")初期状態では、次の5行がF2:I6へ表示されます。
- 課題A
- 課題B
- 課題D
- 課題E
- 課題H
課題Cと課題Fは完了、課題Gは中止なので、今回の未完了一覧から除外されます。
FILTER式はテーブルの外側へ置く
FILTERは複数の結果を返し、必要な大きさの隣接セルへ結果をスピルします。Microsoft公式では、スピルする動的配列数式はExcelテーブルそのものの内部ではサポートされないため、テーブル外の通常セル領域へ置くよう案内しています。
今回の配置は次のとおりです。
- A:D列：元データのExcelテーブル
TaskTable - F:I列：テーブル外に置くFILTER結果
- K:L列：担当者別のCOUNTIFS集計
F2:I列のスピル先に別の値があると、#SPILL!エラーになる場合があります。抽出結果が広がる範囲は空けておきます。
新しい課題Iをテーブルへ追加する
次のデータを、TaskTableの新しいデータ行として追加します。
| 課題 | 担当者 | ステータス | 期限 |
|---|---|---|---|
| 課題I | 高橋 | 進行中 | 2026/10/09 |
たとえば、テーブルの最後のセルD9を選択してTabキーを押し、新しく作られたテーブル行へ入力します。右クリックの挿入からテーブル行を追加する方法でも構いません。入力後は、課題Iの行にもテーブルの書式が付き、TaskTableの範囲に含まれていることを確認してください。
テーブルから離れた別のセルへ入力しただけでは、TaskTableの行を追加したことにはなりません。今回の自動追従は、課題IがTaskTableの新しいデータ行になっていることが条件です。
追加後にCOUNTIFSとFILTERを確認する
課題Iを追加したら、L2:L4とF2からの抽出結果を確認します。
| 担当者 | 追加前 | 追加後 |
|---|---|---|
| 佐藤 | 2 | 2 |
| 鈴木 | 2 | 2 |
| 高橋 | 1 | 2 |
| 合計 | 5 | 6 |
FILTER結果は、課題A、課題B、課題D、課題E、課題Hに課題Iが加わり、6行になります。
追加前のCOUNTIFS合計とFILTER行数は5、追加後はどちらも6です。この変化により、構造化参照を使った今回のCOUNTIFSとFILTERが、追加したTaskTableの行を参照していることを確認できます。
ただし、Excelテーブルにすれば、あらゆる数式や設定が必ず意図どおりに追従するという意味ではありません。この記事で確認した対象は、上記のCOUNTIFS式とFILTER式です。
固定範囲と構造化参照の違い
固定範囲で元データを指定する式と、テーブル全体を指定する式を比べます。
| 参照方法 | 式の例 | 追加行の扱い |
|---|---|---|
| 固定範囲 | =FILTER(A2:D9,...) |
D9より下へ追加した行は、式の参照範囲に入らない場合がある |
| 構造化参照 | =FILTER(TaskTable,...) |
TaskTableのデータ行を参照する |
範囲をテーブルへ変換しても、既存の固定セル参照が必ず構造化参照へ自動変換されるわけではありません。Microsoft公式も、範囲をテーブルへ変換した場合、既存のセル参照は同等の構造化参照へ自動変更されないと案内しています。既存数式は、何を参照しているかを個別に確認してください。
うまく反映されないときの確認ポイント
新しい行がTaskTable内にあるか
課題Iのセルを選択し、テーブルのデザインや書式が適用され、TaskTableのデータ行として認識されているか確認します。離れたセルへ入力しただけでは対象になりません。
数式が固定セル範囲のままではないか
COUNTIFSが$A$2:$A$9などを、FILTERがA2:D9などを参照していないか確認します。今回の式ではTaskTable[担当者]、TaskTable[ステータス]、TaskTableを使います。
ステータス文字列が一致しているか
「進行中」と「対応中」は別の文字列です。TaskTableを使っても、表記ゆれた値を同じものとして集計するわけではありません。入力値を統一する場合は、Excelでステータスをプルダウンにする方法を使い分けてください。
FILTER結果のスピル先が空いているか
F2から右方向・下方向に既存の値や結合セルがあると、結果が広がれません。F:I列の抽出領域を空け、#SPILL!が解消するか確認します。
既存Excel記事との使い分け
ステータスをプルダウンにする方法は、未着手・進行中・完了・中止という入力する値をそろえる記事です。今回のテーブルは、行追加時の参照範囲を安定させる役割です。
COUNTIFSで担当者別の未完了件数を数える方法は、未完了を担当者ごとに集計する考え方が中心です。今回の記事では、固定範囲からTaskTable[担当者]などのテーブル列へ参照先を変え、行追加に追従させます。
FILTERで未完了の行だけ抽出する方法は、未着手＋進行中の具体的な行を表示する方法が中心です。今回の記事では、その元データと条件範囲をTaskTableへ変えます。
期限切れの未完了行を強調したい場合は、Excelで期限切れの未完了行だけ色を付ける方法を参照してください。条件付き書式には適用先や数式参照があるため、テーブル化だけで必ず適切に広がるとは限りません。この記事の検証対象には含めていません。
対応するExcel環境
Microsoft公式の「Excelテーブルでの構造化参照の使い方」は、Excel for Microsoft 365、Microsoft 365 for Mac、Excel 2024／2024 for Mac、Excel 2021／2021 for Mac、Excel 2019、Excel 2016、Excel Mobileを適用先として掲載しています。
一方、FILTER関数の公式ページに掲載されている適用先は、Excel for Microsoft 365、Microsoft 365 for Mac、Excel 2024／2024 for Mac、Excel 2021／2021 for Mac、Excel for iPad、iPhone、Androidタブレット、Androidスマートフォンです。利用中のExcelでFILTER関数が使えるかは、この対応範囲と実際の環境を確認してください。
まとめ
固定範囲の外へ追加した行を集計・抽出から漏らさないため、元データをTaskTableへ変換し、COUNTIFSではテーブル列、FILTERではテーブル全体とステータス列を参照しました。
=COUNTIFS(TaskTable[担当者],K2,TaskTable[ステータス],"未着手")+COUNTIFS(TaskTable[担当者],K2,TaskTable[ステータス],"進行中")=FILTER(TaskTable,(TaskTable[ステータス]="未着手")+(TaskTable[ステータス]="進行中"),"該当なし")課題IをTaskTableの新しい行として追加し、高橋が1件から2件、合計が5件から6件、FILTER結果が5行から6行へ変わることを確認してください。テーブルを作っただけで終えず、追加行が実際の集計と一覧に現れたことを完成条件にします。
参考：Microsoft「Excelテーブルでの構造化参照の使い方」、Microsoft「FILTER関数」、Microsoft「Dynamic array formulas and spilled array behavior」
