Excelで実務効率化｜壊れにくい自動抽出 FILTER関数の実践テクニック

なぜFILTER関数は列ずれに強く、実務向きなのか

本記事では、FILTER関数の基本的な使い方は理解している前提で進めます。
関数の仕様解説ではなく、実務でどう使うと壊れにくいか／後から困らないかという観点で整理します。

FILTER関数の基本的な使い方は「FILTER関数の基本編」をご覧ください。

なぜ「位置」に依存した設計はズレやすいのか

実務では、次のような変更が頻繁に起こります。

• 列が途中に追加される
• 項目名が変わる
• 並び順が入れ替わる
• データ範囲が伸びる

このとき問題になるのが、「列の位置」を前提にした設計です。

従来はVLOOKUP関数やINDEX関数 + MATCH関数のように、列番号や列位置を指定して値を取得する方法が一般的でしたが、現在はXLOOKUP関数が主流になりつつあり、列番号を直接書かずにすむので、改善されています。

ただし、XLOOKUP関数であっても

• 検索列・取得列を「位置」で指定している
• 構造変更（列追加・並び替え）が起きると参照範囲そのものが変わる

といった設計の場合、意図しない列を参照してしまうリスクは残ります。
つまり問題の本質は「どの関数か」ではなく、「位置を前提にしているかどうか」 にあります。

FILTER関数は、抽出条件そのものが「意味」なので

列の追加や並び替えが起きてもロジックが壊れにくい

という特徴があります。

どうすれば列ずれに強い抽出設計になるのか

単にFILTER関数を使うだけでは、必ずしも壊れにくくはなりません。
以下の設計ポイントを意識すると、実務で安心して使えます。

① 条件は必ずセル参照にする

条件を数式に直接書くと、以下のような問題が起きやすくなります。

壊れにくくするために、条件は必ずセルに置き、そのセルを参照する設計にします。

実際に見てみましょう。

練習用ファイル「データ」シートのデータ内から下記の条件にあてはまるデータを行ごと抽出してみます。

抽出条件は、条件変更やメンテナンスの観点から、別シートを準備します。
ここでは「条件」シートに担当者とステータスの抽出条件を入れてみましょう。

条件を設定できたら「抽出」シートのA2セルにFILTER関数を入れていきましょう。

=FILTER(データ!A:E,(データ!B:B=条件!B1)*(データ!D:D=条件!B2))

先ほど「条件」シートで設定した担当者のうち、ステータス完了のデータのみが抽出されました。

② 抽出結果は「そのまま使う」前提で作る

抽出結果をコピーして別資料に貼る運用は、以下のような人為ミスを生みやすくなります。

FILTER関数の出力は 「中間結果」ではなく「最終成果物」 として扱うのがポイントです。

たとえば

• データ
• 条件
• 抽出
• ヘッダー参照
• 印刷用
• ピボット元データ

上記の様に、使用用途に応じてシートを分け、直接参照する設計にすることで、修正・更新コストを大きく減らすことが可能です。

③列番号ではなく列名で取得列を指定する

ここまでは、「抽出条件」や「抽出結果」の設計について整理してきました。
次に考えたいのが、「どの列を取得するか」をどう指定するか、という点です。

単純に考えると「この列を使いたい」というときは、列番号を指定すればよさそうに思えます。

たとえば、COLUMN関数を使えば「今どの列か」は簡単に取得可能だし、COLUMN関数を使って取得列を指定しようと考える方も多いと思います。

ですが、COLUMN関数はあくまで「位置」を返す関数であり、「この列は何を意味するか」までは教えてくれません。

そのため、列の追加・並び替え・見出し変更などが起きると、意図しない列を参照するリスクが残ります。

ここでは「その限界をどう超えるか」という実装例として列名（見出し）から取得列を指定する方法を見ていきます。

列番号ではなく「列名（見出し）」を使って取得列を指定する方法を見ていきます。
そのために使用するのがLET関数となります。

下記のように、練習用ファイル「データ」シートのデータ内から、条件シートで指定したステータス「完了」の顧客名と請求金額だけを「抽出2」シートに抽出していきます。

ここでのポイントは、元のデータから「顧客名」と「請求金額」だけをピンポイントで抜き出している点です。実務では必要な項目だけを抜粋して資料を作ることが多いため、この「列を自在に選べる設計」をマスターすると活用の幅がぐっと広がります。

まずは、顧客名列に入れる数式の全体を見てみましょう。

=LET(h,データ!A1:E1,col,XMATCH(抽出2!A$1,h,0),FILTER(INDEX(データ!A:E,,col),INDEX(データ!A:E,,XMATCH("ステータス",h,0))=条件!B2))

一見すると少し長く感じるかもしれませんが、順番に読むとやっていることは意外と単純です。

① 見出し行を変数にする

まずLET関数の第1引数を見ていきます。下記の青線部分がLET関数の第1引数となります。

=LET(h,データ!A1:E1,col,XMATCH(抽出2!A$1,h,0),FILTER(INDEX(データ!A:E,,col),INDEX(データ!A:E,,XMATCH("ステータス",h,0))=条件!B2))

ここでは、データシートの1行目（A1：E1）を「h」という名前で定義しています。
以降の数式では、「見出し一覧」と言えば「h」を指す、という意味になります。

② 列名から列番号を求める

続いてLET関数の第2引数を見ていきます。下記の青線部分がLET関数の第2引数となります。

=LET(h,データ!A1:E1,col,XMATCH(抽出2!A$1,h,0),FILTER(INDEX(データ!A:E,,col),INDEX(データ!A:E,,XMATCH("ステータス",h,0))=条件!B2))

ここでやっているのは、列名から実際の列番号を求める処理です。

まずポイントとなるのは「抽出2シートのA1セルに書かれた列名」を基準にしていることです。この列名を元に、データシートの見出し行の中で何列目かを探しています。

このとき使用しているのがXMATCH関数です。
XMATCH関数は指定した値が配列の中で何番目にあるかを返す検索関数で、ここでは「見出し行 h の中から A1 の列名が何番目か」を返しています。

こうすることで、列番号を数式内に直接書かずに、列名から自動で取得列を決める仕組みになっています。
この方法により、データシートに列が追加されても、列名が変わらなければ数式は壊れません。

数式内に列番号を直接入力する設計に比べて、列名から取得する方式は圧倒的に壊れにくく、実務向きの設計と言えます。

③FILTER関数で条件に合う行を抽出する

最後に、LET関数で定義した「取得列番号」を使って、条件に合う行を抽出していきます。

=LET(h,データ!A1:E1,col,XMATCH(抽出2!A$1,h,0),FILTER(INDEX(データ!A:E,,col),INDEX(データ!A:E,,XMATCH("ステータス",h,0))=条件!B2))

ここで使うのがFILTER関数です。
FILTER関数は、指定した条件に合う行だけを抽出できる関数なので、実務では「抽出結果をそのまま別シートで使う」という設計に向いています。

今回の数式では、次のような処理をしています。

1. INDEX関数で取得列を指定
   LET関数で求めた列番号「col」を使って、データシートの該当列だけを抽出対象にします。
2. INDEX関数で条件列を指定
   「ステータス」列の番号を XMATCH 関数で求め、FILTER関数の条件に使用します。
3. 条件に一致する行だけを抽出
   INDEX関数で取り出した条件列の値が「条件シートのB2セル（ここではステータス『完了』）」と一致する行だけを抽出します。

こうすることで、抽出する列も条件列も「列番号ではなく列名で取得」しているため、列が追加されたり順序が変わっても列ずれが起きにくく、数式が壊れにくくなります。詳しくは【ROW関数・COLUMN関数の使い方｜行・列変更でも壊れにくいExcel設計】の記事でも解説しています。

また、この設計によりFILTER関数の条件部分も意味に沿ったものになるため、抽出ロジックの意図がわかりやすく、将来的な修正や追加もスムーズに行えます。

同じように請求金額列にも数式を入れることで、列ごとに独立して管理でき、後からの修正や追加も容易です。

まとめ

本記事では、FILTER関数を使った列ずれに強い抽出設計について解説しました。
ポイントは、抽出列や条件列を「列番号ではなく列名で取得」することにあります。
これにより、列の追加や並び替えが起きても数式が壊れにくく、メンテナンス性も高くなります。

さらに、列ごとに独立した数式で管理することで、後から列を追加したり修正したりする場合も安心です。
まとめて複数列を表示する方法については、後日解説を追加予定です。ぜひそちらもあわせてご活用ください。