【Google sheets】Query関数で抽出したデータのついて。参照先に列を追加したときのずれを解消したい。

Question

#達成したいこと
Query関数で参照先のデータの範囲を指定すると思いますが(第一引数)、参照先で列を追加したとき、取り出したデータも連動してずれてしまうのを解消したいです。

#試したこと
importrangeを使って列の指定を固定できるか試しましたが、結果はimportrangeを使わなかったときとおなじでした。

=Query('MT5'!A1:EI1121,"Select A,N,AF,EG")
↓
=Query(IMPORTRANGE("シートID","MT5!A1:EI1121"),"Select Col1,Col14,Col137")

#質問したいこと
このことが達成できる方法があれば教えてください。

Answer 1

本当に質問文の関数なら、同じスプレッド内の別シートなので、Query使わなければ良いです。

={'MT5'!A:A,MT5'!N:N,MT5'!AF:AF,MT5'!EG:EG}

いや実は質問は簡略化してまうが、実関数はwhereで色々条件絞り込んでるんですって場合は、qnoirさんの回答を使ってください。

Comments

[Por]

シンプルでわかりやすかったです。おかげ様で解決できました。ありがとうございました。

Answer 2

MATCHと名前付き範囲を使用してみてはいかがでしょうか。

（※この方法は
・データ参照元の1行目がヘッダ列になっており、実質的なデータは2行目以降にある
かつ
・ヘッダ列における抽出したい列のセルの値が、いずれも、ヘッダ列の全体の中でユニークな値である
場合にしか適用できません）

• IMPORTRANGEを使用する場合

前提：
MT5シートの1行目の1列目、14列目,137列目にそれぞれ「A」「N」「EI」という文字列が入っていると仮定します。
そして、1行目の他のセルには、これらと同じ文字列は入っていないと仮定します。

手順：
１．データ参照元シート（MT5シート）のA1:EI1を選択し、選択した範囲に「HEADER」という名前を付けます。

２．データ参照元（MT5シート）の2行目以降の実質的データ範囲（A2:EI～）を選択し、選択した範囲に「DATA」という名前を付けます。

３．抽出を表示したいセルの数式に下記を設定します。

=Query(IMPORTRANGE("シートID","DATA"),"Select Col" & MATCH("A",HEADER) & ",Col" & MATCH("N",HEADER) & ",Col" & MATCH("EI",HEADER))

結果：
DATAの範囲内で列を挿入した場合、DATAの範囲は自動的に拡張されます。
ヘッダ文字列を消したり変えたりしない限り、MATCH関数による位置取得は列の挿入に合わせて正常に行われるため、取得対象のデータは列挿入前から変わりません。

DATAの範囲外に列を挿入した場合、DATAの範囲は自動的に移動されます。
ヘッダ文字列を消したり変えたりしない限り、MATCH関数による位置取得は変わらないため、取得対象のデータは列挿入前から変わりません。

---

• IMPORTRANGEを使用しない場合：

大まかな考え方は上記と同様になりますが、IMPORTRANGEを使用しない場合、クエリ文はCol＋数字ではなく、アルファベットで指定する必要があります。
アルファベットの列番号は、下記のような式で取得できます。

下記は1行目に「M」というキーがある列の列番号（アルファベット）を取得する例になります。

LEFT(ADDRESS(1, MATCH("M",MT5!$1:$1,0),4),LEN(ADDRESS(1,MATCH("M",MT5!$1:$1,0),4))-1)

また、列番号をそのまま抽出する必要があるため、HEADERはヘッダとなる行全体（例：「シート名!$1:$1」）を参照している必要があります。
（そうしないと、指定範囲外に列を挿入した場合、うまくデータが取れません）

例（HEADERが範囲「MT5!$1:$1」、DATAが「MT5!$A$2:$Z$1000」の範囲で、ヘッダ列に「A」と「M」が入っている列を指定して抽出する場合）：

=QUERY(DATA,"Select "&LEFT(ADDRESS(1,MATCH("A",HEADER,0),4),LEN(ADDRESS(1,MATCH("A",HEADER,0),4))-1)&","&LEFT(ADDRESS(1,MATCH("M",HEADER,0),4),LEN(ADDRESS(1,MATCH("M",HEADER,0),4))-1))

Comments

[Por]

ご丁寧にありがとうございました。とても勉強になりました。