【Googleスプレッドシート】QUERY関数で列番号がずれる時のデータ範囲確認

GoogleスプレッドシートのQUERY関数は、データベースのようなSQLライクな構文でデータの抽出・集計が行える便利な関数です。しかし、列番号を指定して参照していると、想定と異なる列のデータが表示されたり、列の順序がずれてしまう「列番号ずれ」が発生することがあります。この問題の多くは、QUERY関数の第1引数で指定するデータ範囲の設定に原因があります。本記事では、列番号がずれる原因を整理し、具体的な確認手順と対処法を解説します。

QUERY関数で列番号がずれる主な原因

列番号ずれの原因は、大きく分けて3つのケースに分類できます。それぞれの仕組みを理解することで、問題解決の糸口が見えてきます。

データ範囲の指定が固定されている

QUERY関数の第1引数に具体的なセル範囲(例:A2:C100)を指定している場合、その範囲は絶対参照や相対参照で固定されます。元データの先頭に列を挿入すると、固定範囲は自動的に拡張されないため、参照する列が実際の列とずれてしまいます。例えば、A2:C100を指定してselect A, Cと記述していたとき、B列の左側に新しい列を挿入すると、元のA列はB列に、C列はD列に移動します。しかしQUERY関数の範囲はA2:C100のままなので、select A, Cは実際にはB列とD列を参照することになります。

元データの列が増減・非表示になっている

上記のように列の挿入・削除が行われると、QUERY関数のデータ範囲内の列構成が変わります。特に非表示の列もデータとしては存在するため、QUERY関数の結果には非表示列のデータも含まれます。例えば、B列を非表示にした状態で範囲A2:C100を指定すると、実際の列構成はA列(表示)、B列(非表示)、C列(表示)ですが、QUERYは見た目の列順ではなくシート上の並び順で処理します。このため、select A, Cと指定しても、内部的にはA列とC列を正しく参照できているように見えて、非表示列が挿入されたり削除されたりするとズレが発生します。

QUERY関数内のselect句の指定ミス

select句で列番号(例:select Col1, Col3)を使う場合、その番号はQUERY関数のデータ範囲内での列の位置を表します。例えば範囲がA2:C100の場合、Col1はA列、Col2はB列、Col3はC列です。しかし範囲をA2:D100に変更したのにselect Col1, Col3のままにしていると、意図しない列(A列とC列)が抽出されます。また、範囲をA2:C100からA2:B100に狭めた場合、Col3は存在しないためエラーになります。

列番号のずれを確認する手順

問題が発生したら、以下の手順で原因を1つずつ確認していきます。

1. 元データの列構成を確認する
   シート上で実際にどのような列が並んでいるか、非表示の列があるかどうかを確認します。列ヘッダー(A、B、C…)とデータの内容を照らし合わせます。
2. QUERY関数のデータ範囲を特定する
   セルにQUERY関数が入力されている箇所をクリックし、数式バーで第1引数の範囲を確認します。絶対参照($A$2:$C$100)や範囲指定(A2:C100)の形式をメモします。
3. 範囲内の列順序とselect句の列番号を照合する
   データ範囲の左端から列順に番号を振り(1,2,3…)、select句で指定している列番号(Col1, Col2など)が実際に取得したい列と一致しているか確認します。列番号がずれていれば、select句の番号を修正します。
4. 元データに列の挿入・削除がないか確認する
   QUERY関数を作成したときと現在のシートの列構成を比較します。列が挿入・削除されていれば、データ範囲が適切かどうか再評価します。
5. データ範囲を可変にする
   列の増減に対応できるよう、データ範囲を列全体(例:A:C)やINDIRECT関数を利用した可変範囲に変更します。ただし、データ最終行より下に関係ないデータがあると意図しない結果になるため注意が必要です。
6. 必要に応じて名前付き範囲を利用する
   管理者と相談し、元データの領域に対して名前付き範囲を設定します。名前付き範囲は列挿入時に自動拡張されるため、QUERY関数の範囲指定を名前で行うことでずれを防止できます。

正しい範囲指定と誤った範囲指定の比較表

状態	データ範囲指定	QUERY関数の記述	結果
列が増えていない	A2:C100	select A, C	正常にA列とC列が表示される
左に列を挿入後	A2:C100(固定)	select A, C	実際のB列とD列が表示される(ずれ)
範囲を列全体に変更	A:C	select A, C	常にシート上のA列とC列を参照(ずれにくい)
非表示列がある	A2:C100	select Col1, Col3	非表示列のデータも含まれ、見た目と異なる行が表示されることがある

よくある失敗パターンとその対処法

実際に業務で発生しやすい失敗例をいくつか紹介します。

パターン1:データ範囲が途中の行までしか指定されていない
QUERY関数で=QUERY(A2:C100, …)としていたが、データが101行目以降に追加された。その場合、新しい行は範囲外のため抽出されません。対処法:範囲をA2:Cのように列全体にするか、最終行を動的に取得するCOUNTA関数などと組み合わせます。

パターン2:列番号指定と列ラベル指定の混同
select句で「select A, C」と列ラベルを使っているのに、データ範囲にヘッダー行がないとエラーになります。また、select Col1, Col3と列番号を使う場合、データ範囲内の列番号を常に意識する必要があります。対処法:ヘッダーの有無を第3引数(header)で正しく指定し、列の指定方法を統一します。

パターン3:複数シートを参照している場合の範囲間違い
別シートのデータをQUERY関数で参照するとき、シート名にスペースが含まれていると引用符の扱いを間違えて範囲がずれることがあります。対処法:シート名をシングルクォーテーションで囲み、正しい範囲を記述します。

管理者に確認すべきこと

会社で共有されているスプレッドシートでは、複数のメンバーが同時に編集するため、予期せぬ列の挿入や削除が行われる可能性があります。管理者に以下の点を確認しておくとトラブルを防げます。

• データ範囲の変更履歴:最近、誰かが列を挿入・削除していないか、バージョン履歴で確認してもらいましょう。
• 名前付き範囲の設定:よく使うデータ範囲を名前付き範囲として登録し、QUERY関数ではその名前を参照する方法を推奨します。管理者に設定を依頼してください。
• 編集権限の制限:重要なデータが含まれるシートについては、列の挿入・削除を制限するよう管理者に相談します。

よくある質問

Q1: QUERY関数で列番号を変えずに列を挿入した場合、必ずずれますか?

A: はい、ずれます。QUERY関数のデータ範囲が固定セル範囲(例:A2:C100)の場合、列の挿入によって参照する列が変わります。列全体を範囲に指定している場合は、挿入によりシート上の列位置が変わるため、相対的な列ラベル(A,B,C)を使っていればずれません。ただし、列番号(Col1, Col2)を使っているときは範囲の開始列がずれるため注意が必要です。

Q2: 非表示の列があるとQUERYの結果に影響しますか?

A: 影響します。QUERY関数は非表示の列もデータとして認識します。例えば、B列を非表示にした状態で範囲A2:C100を指定し、select A, Cと記述しても、内部的にはA列とC列が正しく参照されるため、一見問題ないように見えます。しかし、後に列の表示・非表示を変更したり、列の順序を入れ替えたりすると、結果が変わる可能性があります。対策として、QUERY関数では非表示列を含めた列構成を常に意識するか、表示列のみを抽出したい場合はFILTER関数を検討してください。

Q3: QUERY関数のデータ範囲を動的にするにはどうすればいいですか?

A: いくつかの方法があります。1つは範囲を列全体で指定する方法(例:A:C)です。ただし、この場合、データが入っていない行も範囲に含まれるため、QUERYの結果に空行が含まれることがあります。別の方法として、INDIRECT関数とCOUNTA関数を組み合わせて、最終行を自動計算する方法があります。例えば、=QUERY(INDIRECT(“A2:C”&COUNTA(A:A)), …) とすると、A列のデータがある行までを範囲とできます。ただし、COUNTAは空白セルをカウントしないため、データに隙間がある場合は注意が必要です。

まとめ

QUERY関数で列番号がずれる原因は、データ範囲の固定指定、元データの列変更、select句の列番号ミスの3つに集約されます。まずは現在のデータ範囲と元データの列構成を確認し、問題に応じて範囲を列全体に変更するか、名前付き範囲を利用することが有効です。共有シートでは管理者と連携し、列の変更履歴を確認しながら対策を進めてください。QUERY関数の正しい範囲指定を習慣づけることで、予期せぬずれを防ぎ、安定したデータ集計を実現できます。