「昨日まで普通に動いていたFILTERが、列を1本消しただけで真っ赤なエラー」「QUERYの結果が突然ズレて、商品名のはずの列に単価が表示される」。こんな経験、ありませんか。
スプレッドシートでデータ集計の自動化を進めるほど、行や列の削除1つで関数が連鎖的に壊れてしまうリスクは大きくなります。特に FILTER関数と QUERY関数は、参照範囲を絶対位置で指定するため、データ構造の変更にとても弱い関数です。
この記事では、FILTER と QUERY が「行・列削除によって崩れる3つのパターン」を整理し、それぞれの原因の見分け方、具体的な修正手順、IMPORTRANGE 経由のケースの対処、そして二度と同じトラブルに巻き込まれないための予防策まで、ビフォー・アフター形式の数式例を使って解説します。
FILTERやQUERYが崩れる3つのパターン
「数式を直したいのに、そもそも何が壊れているのか分からない」。これがこの問題のいちばん厄介なところです。
行・列を削除したあとに FILTER や QUERY が崩れる現象は、見た目こそ似ていますが、内部的には大きく3つのパターンに分類できます。まずはそれぞれの症状を把握しておきましょう。
パターン1：参照先が空欄になる・#REF!が表示される
もっとも分かりやすい崩れ方が、セルに #REF! という赤いエラーが表示されるケースです。
#REF! は「参照先がもう存在しません」というスプレッドシートからのメッセージで、数式が参照していたセル・範囲が削除されたときに発生します。たとえば C列を参照していた数式があったとして、その C列を削除すると、数式の中の C2:C100 という部分が #REF! という文字列に置き換わってしまいます。
[削除前] =FILTER(A2:D100, C2:C100="東京")
[削除後] =FILTER(A2:D100, #REF!="東京") ← C列を消した結果数式バーを見ると #REF! という文字がそのまま埋め込まれているので、原因の特定自体は比較的かんたんです。
パターン2：「FILTERの範囲サイズが一致しません」エラー
FILTER関数特有のエラーで、数式が壊れているように見えるのに #REF! ではなく「範囲サイズが一致しません」というメッセージが返るケースです。
このエラーは、絞り込み対象の範囲（行数または列数）と、条件式が評価する範囲のサイズが一致しないときに発生します。列を削除した影響で、範囲指定と条件式の対応関係がずれてしまうことが原因です。
[削除前] =FILTER(A2:D100, B2:B100="東京") ← A〜D（4列）×100行に対し条件はB列
[削除後] =FILTER(A2:C100, B2:B100="東京") ← データ範囲は3列に縮んだが条件範囲はそのままこの場合はエラーメッセージこそ出ますが、参照先の文字列としては正しい範囲が残っているため、#REF! のような分かりやすい印は残りません。
パターン3：エラーは出ないが意図しない列が表示される
実はいちばん怖いのが、この「無言で壊れる」パターンです。
QUERY関数で SELECT A, C, D のようにアルファベット指定をしているとき、間にある列を削除すると、列の繰り上がりが起きてしまいます。SELECT 句の文字列自体は変わらないため、エラーは出ませんが、取り出している列は元の意図とは別のものになります。
[削除前] =QUERY(A1:E100, "SELECT A, C, D") ← A=日付, C=商品名, D=単価
[C列削除] =QUERY(A1:D100, "SELECT A, C, D") ← C列削除で単価がC列に繰り上がり、DはもとのE列C列（商品名）を削除すると、もともと D列にあった単価が C列に繰り上がります。SELECT 句で C を指定しているので、結果として「商品名のつもりだったセル」に単価が表示されることになります。
集計結果が間違っていることに気づかないまま、レポートやダッシュボードに流してしまうリスクがあるので、特に注意したいパターンです。
原因の診断方法（どこが壊れているかの特定）
崩れの3パターンを頭に入れたら、次は「目の前の数式がどれに該当するか」を素早く見極めましょう。
ここでは「数式を読む順番」と「チェックすべきポイント」を紹介します。直すよりも先に、原因の切り分けが重要です。
ステップ1：エラーセルを選択して数式バーを確認する
最初にやるべきは、エラーが出ているセルをクリックして数式バーを開くことです。
スプレッドシート上のセル表示だけを見て直そうとすると、隣接する別の数式の影響と混ざってしまうことがあります。必ず数式バーで「いまの数式の中身」を確認しましょう。
確認するポイントは次の3つです。
- 数式の中に
#REF!という文字列が含まれているか - 範囲指定（例:
A2:D100）が、いま見えているデータの範囲と合っているか - QUERYの SELECT 句や ORDER BY 句に書かれた列記号が、現在の列構成と一致しているか
これだけで、パターン1〜3のどれに該当するかがほぼ判別できます。
ステップ2：エラーメッセージのバリエーションで切り分ける
スプレッドシートが返すエラーや警告には種類があります。代表的なものを整理しておきましょう。
| 表示されているもの | 想定される原因 | 該当パターン |
|---|---|---|
#REF! がセル全体に表示 | 数式内の参照（範囲・列・セル）が削除された | パターン1 |
| 「FILTERの範囲サイズが一致しません」 | 範囲と条件の行数または列数が違う | パターン2 |
#N/A（該当データなし） | 条件に合致する行が存在しないだけ | 削除と無関係なケースもあり |
| エラーなしだが値がズレている | 列削除により SELECT の列記号が別データを指している | パターン3 |
| 「数式の解析エラーです」 | SELECT 句などの文法ミス、またはデータ範囲外の列を指定している | パターン3の一種 |
「エラーが出ていないから大丈夫」ではなく、「結果の中身が想定どおりか」まで確認するのがポイントです。
ステップ3：参照先のデータを別の場所に展開して確認する
QUERY や IMPORTRANGE のように内部で配列を扱う関数は、何列・何行のデータが返ってきているか直感的に分かりにくい場合があります。
このときは、いったん空きセルに次のような確認用の数式を置いてみるのが有効です。
=QUERY(A1:Z100, "SELECT *")SELECT * で全列をそのまま表示させると、現在の列構成を一目で確認できます。何列目に何のデータが入っているかが見えれば、SELECT 句で何を指定すべきかも明確になります。
FILTER関数の修正方法
「とりあえず動かしたい」と思っても、慌てて修正するとさらに壊してしまうことがあります。
ここでは、パターンごとの具体的な修正手順を、ビフォー・アフター形式で見ていきましょう。FILTER関数の基本構文を確認したい方は FILTER関数の使い方 もあわせてご覧ください。
修正例1：#REF!が条件式に出ている場合
C列（都道府県）を誤って削除してしまったケースを想定します。
[崩れた状態]
=FILTER(A2:D100, #REF!="東京")
[修正後]
=FILTER(A2:C100, C2:C100="東京")修正の流れは次のとおりです。
- 削除した「都道府県」のデータを別の列に復元する（バックアップから貼り直す・元データから再取得する）
- データの新しい配置で、対象範囲を
A2:C100のように書き直す - 条件式の参照を、新しい列の位置（例:
C2:C100）に修正する - 入力後に Enter を押し、抽出結果が想定どおりか確認する
「削除した列をそのまま復元せず、別の列に置き直す」ケースもあります。その場合は条件式の参照先を、実際にデータが置かれた列に変更してください。
修正例2：範囲サイズが一致しないエラーの場合
「FILTERの範囲サイズが一致しません」エラーが出ているケースです。
[崩れた状態]
=FILTER(A2:C100, B2:B500="東京") ← データは100行までしかないのに条件は500行まで
[修正後]
=FILTER(A2:C100, B2:B100="東京") ← 範囲と条件の行数を揃えるこのエラーは、行の削除や挿入、行範囲のコピーミスでよく発生します。修正のポイントは「範囲と条件の行数（または列数）を完全に揃える」ことです。
可能であれば、行数を厳密に指定せず列全体で指定する書き方に変えると、行の増減に対しても強くなります。
=FILTER(A2:C, B2:B="東京") ← 行数指定を外し、列全体を対象にただし、列全体指定にすると下に空行が大量にある場合に処理が重くなったり、空行が結果に混ざることがあります。データ量や運用に合わせて使い分けましょう。
修正例3：列指定が「意図しない列」になっている場合
#REF! は出ていないのに、抽出結果の列が想定とずれているケースです。
[崩れた状態]
=FILTER(A2:C100, B2:B100="東京") ← 期待は「氏名・都道府県・売上」だが、実は「氏名・売上・備考」
[修正後]
=FILTER({A2:A100, C2:C100, E2:E100}, C2:C100="東京")FILTER関数では、抽出対象の範囲を波括弧 {} を使って組み立て直すことで、必要な列だけを取り出すことができます。列の並びが変わってしまった環境でも、「ほしい列を明示的に指定する」スタイルに変えることで、意図しないデータを表示してしまう事故を防げます。
修正のチェックリスト
修正後は必ず以下を確認してください。
- 抽出された行数が、手動で数えた件数と一致しているか
- 抽出されたデータの列順が想定どおりか
- 条件に該当しないはずのデータが混ざっていないか
- 元データに新しい行を追加したとき、結果に反映されるか
特に1番目の「件数の一致」は、見落としに気づくための最重要チェックポイントです。
QUERY関数の修正方法（Col番号のずれ対策含む）
QUERYは強力ですが、SELECT 句の中身が「文字列」のため、列削除に対して特に弱い関数です。
ここでは、アルファベット指定で壊れたパターンと、Col1, Col2… のような相対番号指定のずれを、それぞれ修正していきましょう。QUERY関数そのものの使い方を復習したい方は QUERY関数の使い方 を参照してください。
修正例1：SELECT句のアルファベット指定がずれた場合
D列（商品名）を削除して、E列以降が繰り上がったケースを想定します。
[崩れた状態]
=QUERY(A1:E100, "SELECT A, D, E")
→ Aは日付（OK）、DはもともとEだった単価、Eはもう存在しない
[修正後]
=QUERY(A1:D100, "SELECT A, C, D")
→ A=日付, C=単価, D=数量 のように、現在の列構成に合わせて書き直す修正手順は次のとおりです。
- 元データの何列目に何があるかを
SELECT *で確認する - ほしいデータが何列目（A・B・C…）にあるかを把握する
- SELECT 句の列記号を、現在の列構成に合わせて書き直す
- WHERE 句・ORDER BY 句・GROUP BY 句にも同じ列記号が含まれていないか確認する
特に WHERE 句・ORDER BY 句は見落としやすいポイントです。たとえば SELECT A, D WHERE D > 1000 ORDER BY D のように同じ列を3か所で参照している場合、3か所すべてを修正する必要があります。
修正例2：Col番号指定のずれを修正する
Col1, Col2, Col3… という相対番号指定は、IMPORTRANGE を組み合わせた場合や、データ範囲が動的に変わる場合に使われます。
Col番号はデータ範囲の「一番左の列を1とした相対位置」を表します。たとえば A1:F100 を対象にしている場合、Col1 はA列、Col6 はF列を意味します。
[崩れた状態]
=QUERY(A1:F100, "SELECT Col1, Col3, Col5 WHERE Col2='東京'")
→ B列を削除したため、もともとCol3だった商品名が今はCol2に繰り上がっている
[修正後]
=QUERY(A1:E100, "SELECT Col1, Col2, Col4 WHERE Col1='東京'")
→ B列削除で列が1つずつ前にずれたので、それに合わせて番号を1つずつ前にずらすCol番号は「データ範囲内での相対位置」なので、データ範囲そのものが変わった場合は、SELECT 句の番号も連動して変更が必要です。
QUERY独自のチェックポイント
QUERYを修正したあとは、次の項目も確認しておきましょう。
- 集計関数（SUM, AVG, COUNT）の対象列が正しいか
- WHERE 句の比較値が、列のデータ型と一致しているか（数値列に文字列を指定していないか）
- ORDER BY 句のソート列が、SELECT 句に含まれているか（必須ではないが、含まれていないと並び順の意図が分かりにくくなる）
- LABEL 句で付けた列名が、修正後の列に対して妥当か
これらをまとめてチェックすると、「動くけど結果がおかしい」状態を見逃しにくくなります。
IMPORTRANGEを使っている場合の対処
別ファイルからデータを取り込んでいる場合、崩れたときの調査がさらに複雑になります。
IMPORTRANGE 経由のFILTER/QUERYは、自分のファイルだけを見ても原因がわからないため、参照元シートも合わせて確認する必要があります。IMPORTRANGE関数の基本については IMPORTRANGEの完全ガイド を参考にしてください。
IMPORTRANGE経由でずれる典型パターン
QUERYとIMPORTRANGEを組み合わせた書式は次のようになります。
=QUERY(
IMPORTRANGE("スプレッドシートURL", "Sheet1!A:F"),
"SELECT Col1, Col3, Col5 WHERE Col2='東京'"
)このとき、参照元のスプレッドシートでB列を削除すると、IMPORTRANGEが返す配列の列構成も変わります。元の Col3（商品名）が Col2 に繰り上がる、といった現象が起こります。
QUERY側の SELECT 句は文字列なので変わらず、結果として「商品名が表示されるはずの列に単価が出る」といったパターン3の事故が発生します。
対処の手順
IMPORTRANGE経由の崩れを直すときは、次の順番で確認しましょう。
- 参照元のシートを開く：実際の列構成を
SELECT *相当で確認する - IMPORTRANGEだけで取得結果を確認：いったん
=IMPORTRANGE("URL", "Sheet1!A:F")だけを別セルに置き、現在何列・どんなデータが返ってくるかを見る - Col番号と実列の対応をマッピング：「いまの Col1 は何のデータか」を一覧化する
- QUERYの SELECT 句を書き直す：現在の列構成に合わせて Col番号を修正する
参照元のシートでよく列の追加・削除が発生する場合は、IMPORTRANGE で取得する範囲を A:Z のように広めにとっておき、QUERY側で SELECT Col1, Col5… と必要な列だけ取り出すスタイルにしておくと、追加には強くなります（ただし削除には依然として弱いので、後述の予防策も併用してください）。
アクセス許可エラーが混ざるケース
崩れと同時にアクセス許可エラーが出ている場合は、まず許可エラーの解消が先です。
IMPORTRANGE のアクセス許可は、参照元シートの所有者が変わったり、共有設定が変更されたりすると外れることがあります。詳しい対処は IMPORTRANGEのアクセス許可エラー対処 を参照してください。
許可エラーを解消したうえで、改めて Col番号のマッピングを行うのが安全な進め方です。
今後のための予防策
「直しても、また誰かが列を削除して壊れる」。これは多くのチーム運用で発生する悩みです。
最後の砦として、ここでは数式そのものを「行・列削除に強い書き方」へ変える方法を3つ紹介します。
予防策1：名前付き範囲を活用する
スプレッドシートには「データ → 名前付き範囲」というメニューがあり、特定の範囲に名前を付けて関数から参照できます。
名前付き範囲のメリットは、範囲内で行を挿入・削除しても、スプレッドシートが自動的に範囲定義を更新してくれる点です。
[名前付き範囲を使わない場合]
=FILTER(顧客マスタ!A2:D1000, 顧客マスタ!C2:C1000="東京")
[名前付き範囲を使う場合]
=FILTER(顧客データ, 都道府県列="東京")名前付き範囲の登録手順は次のとおりです。
- 範囲指定したいセルをドラッグで選択する
- メニューの「データ」→「名前付き範囲」をクリック
- 範囲に分かりやすい名前を付ける（例:
顧客データ、都道府県列） - 「完了」を押して登録する
数式の読みやすさも上がるため、チームで共有するスプレッドシートには特におすすめです。ただし、名前付き範囲そのものを削除すると参照している数式が一斉にエラーになるため、命名と削除のルールはチームで合意しておきましょう。
予防策2：INDIRECT関数で参照を文字列化する
INDIRECT関数の使い方 は、文字列で指定したセル番地を参照に変換する関数です。
文字列はセルの移動や行・列の削除では変化しないため、INDIRECT を介した参照は「位置が固定される」性質を持ちます。
[通常の参照]
=QUERY(Sheet1!A1:E100, "SELECT A, C, D")
[INDIRECT経由]
=QUERY(INDIRECT("Sheet1!A1:E100"), "SELECT A, C, D")通常の参照では、シート内で行を挿入すると A1:E100 が自動的にずれてしまいます。一方 INDIRECT で文字列指定にすれば、A1:E100 という位置は変わらず、常に同じ番地を参照し続けます。
注意点として、INDIRECTは「揮発性関数」と呼ばれる種類の関数で、シート内の何かが変わるたびに再計算が走ります。大量に使うと、ファイル全体の動作が重くなることがあります。
- データ件数が数千行以下：問題なく使える
- データ件数が数万行・複数シートにまたがる：パフォーマンス劣化に注意
- 数十のセルで INDIRECT を多用する：他の方法（名前付き範囲・テーブル構造）の併用を検討
「行削除で位置がずれない」というメリットと、「再計算負荷が大きい」というデメリットを天秤にかけて使い分けるのがポイントです。
予防策3：列全体指定・テーブル構造で運用する
データ範囲を「列全体」で指定する方法もあります。
[行数まで指定]
=FILTER(A2:D100, B2:B100="東京")
[列全体で指定]
=FILTER(A2:D, B2:B="東京")A2:D のように行数を省略すると、A〜D列のヘッダー行を除く全データが対象になります。行が増えても自動的に範囲が広がるため、行の増減に対して堅牢です。
ただし、これは「行」の増減には強い一方で、「列」の削除には依然として弱いので注意しましょう。列削除に対しては、上の名前付き範囲・INDIRECTと組み合わせる必要があります。
予防策4：データ構造を「テーブル形式」で固定する
そもそも、行・列の削除がしょっちゅう発生するスプレッドシートは「データ構造として弱い」状態です。
長く運用するスプレッドシートでは、データ構造を以下のように整えるとトラブルが激減します。
- 1行目はヘッダー固定：列の意味（日付・氏名・売上 など）をヘッダー行に明記する
- 列を消さない運用：使わなくなった列も削除せず「非表示」にする、または「アーカイブ列」として残す
- 集計用シートと入力用シートを分離：入力するシートと集計するシートを分けることで、構造変更の影響範囲を限定する
- テンプレートを文書化：「この列は削除厳禁」「列の挿入は右端のみ」といったルールをシート内に記載する
技術的な工夫よりも、まずは「壊れにくいデータ構造で運用する」ことを最初に検討してください。
予防策の組み合わせ早見表
それぞれの予防策の特性を表にまとめておきます。
| 予防策 | 行追加に強い | 行削除に強い | 列削除に強い | パフォーマンス影響 |
|---|---|---|---|---|
| 名前付き範囲 | ◎ | ◎ | △（範囲内のみ） | なし |
| INDIRECT | ○ | ◎ | × | あり（揮発性） |
| 列全体指定 | ◎ | ◎ | × | 大データ時に注意 |
| テーブル構造ルール | ◎ | ◎ | ◎ | なし |
完全な防止策は存在しません。複数の予防策を組み合わせて、運用に合った形に整えるのが現実的な解です。
修正後の確認方法
「直したつもりが、別の場所が静かに壊れている」。これも自動化スプレッドシートで起こりがちな事故です。
数式の修正は、それ自体が新しい変更行為です。最後に、修正後の確認手順をまとめておきます。
確認1：抽出件数と元データの突き合わせ
まず、FILTER や QUERY が返す件数が、元データの該当件数と一致するかをチェックします。
[確認用]
=COUNTIF(B2:B100, "東京")COUNTIF で「東京」の件数を数え、FILTER の結果と一致していれば、抽出条件は正しく動いていると判断できます。QUERY で集計をしている場合は、SELECT COUNT(A) WHERE B='東京' のような簡易クエリと結果を見比べると確実です。
確認2：列順・列内容のサンプリング
抽出結果の上から数行を目視で確認し、列順が想定どおりか・データ型が混ざっていないかを見ましょう。
特に、もともと「文字列だった列」と「数値だった列」が入れ替わっていないかは、見た目では気づきにくいポイントです。セルの右寄せ・左寄せの違いを利用すると、ざっと識別できます。
確認3：依存している他の数式・グラフへの影響
FILTER や QUERY の結果を、別のセルや別シートで参照しているケースは多いはずです。
修正後は、次の点もあわせて確認しましょう。
- FILTER/QUERY の結果を
SUM・AVERAGEなどで集計しているセルの値が妥当か - FILTER/QUERY の結果を元にしているグラフが、想定どおり描画されているか
- FILTER/QUERY の結果を IMPORTRANGE で別ファイルから読み込んでいる場合、そのファイルでも値が正しく表示されているか
スプレッドシートは1か所の変更が広範囲に波及します。「点」ではなく「線」で動作確認するのがコツです。
確認4：実際に行・列を追加して再テストする
最後に、修正後の数式が「次の変更」にも耐えられるかを軽くテストしておくと安心です。
- 元データに新しい行を1行追加してみる → FILTER/QUERY の結果が増えるか
- 該当しないデータを1行追加してみる → 結果に混ざらないか
- ヘッダーは触らず、不要な列を非表示にしてみる → 結果に変化がないか
「変更しても壊れないこと」を確認できれば、その数式は実務運用に十分に耐えうる品質と言えます。
まとめ
FILTER関数と QUERY関数が行・列削除によって崩れる現象は、見た目こそ似ていても、原因と対処はそれぞれ違います。
この記事のポイントを最後にまとめます。
- 崩れ方は大きく3パターン：①
#REF!エラー、②範囲サイズ不一致、③エラーなしで列がずれる - 原因の特定は「数式バーで参照を確認」「エラーメッセージで切り分け」「SELECT * で元データを確認」の3ステップ
- FILTER関数の修正は、削除後の列構成に合わせて「範囲」と「条件式」の両方を書き直す
- QUERY関数の修正は、SELECT 句・WHERE 句・ORDER BY 句のすべての列記号を見直す
- IMPORTRANGE経由のケースは、参照元シートの状態確認と、Col番号の再マッピングが必須
- 予防策は「名前付き範囲」「INDIRECT」「列全体指定」「テーブル構造ルール」の組み合わせで対応する
- 修正後は、件数の一致・列順・依存先・追加テストの4段階で確認する
特に「エラーが出ていないのに値が間違っている」パターン3は、業務データの信頼性に直結する重大なリスクです。日々の数式運用に、ぜひ予防策まで含めて取り入れてみてください。
数式は一度組み立てたら終わりではなく、「壊れにくい形に育てていく」ものです。今回紹介した修正と予防のテクニックが、毎日のスプレッドシート運用の助けになれば幸いです。
