INDEX&MATCHではIMPORTRANGEをしながらARRAYFORMULAできないので代わりにVLOOKUPする
本記事はGoogleスプレッドシート&Excelアドベントカレンダー2020の8日目です。
昨日はkamocycさんのExcelのコマンド名を指定して実行 Command Paletteでした。
課題
Googleスプレッドシートを使用していると、SQLみたいにデータベースのようなシートとjoinしたいときがあります。
例えば、社内勉強会の申し込み用Googleフォームでフォームの送信時にメールアドレスを自動取得して、参加者の名前などは社員リストから引っ張りたい、みたいなものです。(こちらを後ほど例でご紹介します)
スプレッドシート上では、
- 他のシートからインポートするIMPORTRANGE関数
- キーワードをもとに関連する対象の情報を検索するVLOOKUP/INDEX&MATCH関数
- 数式を配列で展開するARRAYFORMULA関数
あたりを組み合わせればできそうです。
しかし、それぞれの関数は組み合わせると動かなかったり要件を満たせなかったりします。
具体的には以下のような課題があります。
- VLOOKUPだとキーの左側のセルを取得できない[1]
- QUERYだと列にそれぞれ記載されているキーに対してARRAYFORMULAが対応できない[2]
- IMPORTRANGEとARRAYFORMULAとINDEX&MATCHが両立できない[3]
とにかく、「1つのセルの数式だけでキーの左側を含むすべての情報をインポートする」方法を探しました。
結論
2回IMPORTRAMGEをして配列を再構成することでVLOOKUPできるようにしました。
=ARRAYFORMULA(VLOOKUP([キーの範囲],{IMPORTRANGE("[DBシートのID]","[キーの参照先]"),IMPORTRANGE("[DBシートのID]","[引用したい範囲]")},COLUMN([引用した列の範囲]-COLUMN([キーの列]:[キーの列])+2),FALSE))
[]とその中身はよしなに代入してあげてください。
例: Googleフォームでメールアドレスを集め、他の情報を名簿からインポートする
Googleフォーム回答シート(数式を入力するシート)
黒塗りのセルにのみ数式が入っています。
名簿(元データ)
手順
-
回答シートのA列とB列が入力されている状態で、名簿シートをIMPORTRANGEする数式を適当に貼り、アクセスの許可を選択する(上記例ではC1に見出しをIMPORTRANGEする数式を入力)[4]
-
任意の列の2行目に以下の数式を入力する
=ARRAYFORMULA(IFERROR(VLOOKUP($B2:$B,{IMPORTRANGE("[名簿のシートID]","test!F:F"),IMPORTRANGE("[名簿のシートID]","test!A:G")},COLUMN(C:I)-COLUMN(C:C)+2,false)))
この例の場合7列インポートするので7列以上空白を用意してください。
参考
-
一般にINDEX&MATCHで解決 ↩︎
-
それぞれの行にQUERYの数式を入れれば可能 ↩︎
-
INDEX&MATCHの代わりにVLOOKUPなら可能。参考URL: https://stackoverflow.com/questions/59693276/arrayformula-not-working-with-index-and-match-google-sheets-formula ↩︎
-
これは先に手順2の数式を貼ってもアクセス許可のボタンが表示されないためです。許可後は数式を削除しても大丈夫です。 ↩︎
Discussion