まずは結論
同一スプレッドシート内の場合
= QUERY('【検索対象シート名】'!A:ZZ,"select " & LEFT(ADDRESS(1,MATCH("【探している文字列】", '【検索対象シート名】'!A1:ZZ1, 0),2), FIND("$",ADDRESS(1,MATCH("【探している文字列】", '【検索対象シート名】'!A1:ZZ1, 0),2))-1),1)
別のスプレッドシートの場合
= QUERY(IMPORTRANGE(【検索対象スプレッドシートURL】, "【検索対象シート名】!A:ZZ"),"select Col" & MATCH("【探している文字列】", IMPORTRANGE(【検索対象スプレッドシートURL】, "【検索対象シート名】!A1:ZZ1"), 0), 1)
見出し行を文字列で探して、その列を一括で取得したくなったことはありませんか？
はい、僕はあります。
例えば、あるテーブルではこのようにデータが記録され、
| くだもの | 値段 | 在庫数 |
|---|---|---|
| りんご | 50 | 10 |
| みかん | 100 | 5 |
| バナナ | 200 | 3 |
| 別のテーブルではこのようにデータが記録されていた場合、 |
| さかな | 在庫数 | 値段 |
|---|---|---|
| アジ | 10 | 500 |
| タイ | 5 | 1000 |
| ノドグロ | 3 | 2000 |
| どちらも変わらず「値段」の列のみを取得したい…… | ||
| つまり前者では |
| 値段 |
|---|
| 50 |
| 100 |
| 200 |
| 後者では |
| 値段 |
|---|
| 500 |
| 1000 |
| 2000 |
| を抜き出したい場合です。 |
この手のは今までスクリプトエディタを開いて、GASでfor文を使い、タイトル行にその文字列があるのを探し、そこから列を取ってきたのですが、これをする場合、手動実行もしくはトリガーの設定が必要になってしまうので、頑張った割には見返りが少ない感じが否めません。
スプレッドシートのテンプレートを作り、その複製を作って運用するみたいな場合は、なおさらあまり賢い選択ではない感じがするためできればスクリプトエディタを使わずに、普通の関数で運用したいところです。
## それでは解説
別のスプレッドシートの場合の解説
この関数を作るにあたって、別スプレッドシートのほうが簡単に作れるという珍しいパターンなので、順番は上記のサンプルと違ってしまいますが、まずこちらを解説します。
QUERY
別スプレッドシートのデータを読みに行く IMPORTRANGE と組み合わせた場合、Select で列を選択する時に列はアルファベット「A」や「B」ではなく、列番号「Col1」や「Col2」で指定することになります。
QUERY(【範囲】,"select Col1", 1)
※QUERY自体の使い方
https://support.google.com/docs/answer/3093343?hl=ja
#### IMPORTRANGE 検索範囲を「A:ZZ」や「A1:ZZ1」としているのは、おそらくこれでシート全体や、見出し行全体をカバーできるというアテで入れています。もしものすごく列が大きいスプレッドシートを扱う場合は、適宜変更してください。 （もしこんなことしなくても最大範囲を取れるという方法を御存知の方がいらっしゃったら教えていただけると助かります。）
IMPORTRANGE(【検索対象スプレッドシートURL】, "【検索対象シート名】!A:ZZ")
IMPORTRANGE(【検索対象スプレッドシートURL】, "【検索対象シート名】!A1:ZZ1")
※IMPORTRANGE自体の使い方
https://support.google.com/docs/answer/3093340?hl=ja
#### MATCH MATCHで返ってきた列番号を取得。（例：1） それを「Col」と連結し「Col1」を取得しています。
"select Col" & MATCH("【探している文字列】", IMPORTRANGE(【検索対象スプレッドシートURL】, "【検索対象シート名】!A1:ZZ1"), 0)
※MATCH自体の使い方
https://support.google.com/docs/answer/3093378?hl=ja
これで別スプレッドシートの列は好きに取得することができるようになります。
同一スプレッドシート内の場合
QUERY
同一スプレッドシートの場合も、基本的には別のスプレッドシートの場合と同じ手順になるのですが、問題は Select で列番号ではなく、列のアルファベットで指定することになります。
QUERY(【範囲】,"select A", 1)
つまりこの「A」の部分を列番号から変換しなければならない分、別スプレッドシートに比べて複雑になっているわけです。
#### ADDRESS 前述の「くだものテーブル」で値段の列「B」を取得したいので ADDRESS を使用します。ただアルファベットは A - Z のような1文字だけでなく、AA や AB のような2文字のケースも考えられるので「B\$1」のような、列は相対参照、行は絶対参照のモード「2」を使用し「\$」を区切り文字として扱うことを目論んでいます。
ADDRESS(1,MATCH("【探している文字列】", '【検索対象シート名】'!A1:ZZ1, 0),2)
※ADDRESS自体の使い方
https://support.google.com/docs/answer/3093308?hl=ja
#### LEFTとFIND 「B\$1」の中の「\$」の位置をFINDで探し出すと「2」が返ってきます。 その1つ前の文字までをLEFTで抜き出したいので、FINDで返ってきた数字に -1 をします。
LEFT(ADDRESS(1,MATCH("【探している文字列】", '【検索対象シート名】'!A1:ZZ1, 0),2), FIND("$",ADDRESS(1,MATCH("【探している文字列】", '【検索対象シート名】'!A1:ZZ1, 0),2))-1)
※LEFT自体の使い方
https://support.google.com/docs/answer/3094079?hl=ja
※FIND自体の使い方
https://support.google.com/docs/answer/3094126?hl=ja
## 長々と…… 書いてきましたが、基本的には最初の結論部分をコピーしていただいて、必要な個所を書き換えていただければ使えるようになっているはずです！
みなさまの快適なGoogleスプレッドシート生活のお役に立てたら幸いです。
🙇🏻♂️