農場の経営管理・資材管理システムを作るために活用したいと思い、QUERY関数の使い方を学んでいます。
これまで、実データを使ってQUERY関数の使い方をいくつか試してきました。
今回の記事では、複数のデータ範囲を横にくっつけて新しくテーブルを作成する方法を見ていきます。
スプレッドシートをデータベースにした資材管理のシステムを作るのに、QUERY関数を使うとうまくいく気がしている。
— おおさき🥔小さなIT活用で快適な農場づくりを！ (@massa_potato) 2022年9月17日
ノンプログラマーのためのスキルアップ研究会 | 会社員がVLOOKUPの次に覚えるQUERY関数超入門 #技術書典 https://t.co/wFKypNKWbx
やりたいこと
まずは、データを見てみます。1つのスプレッドシートの中に、「農薬情報」シートと「単価情報（期間抽出）」シートがあります。
- 「農薬情報」シートは、農薬に関する情報が入ったマスターデータ
- 「単価情報（期間抽出）」シートは、以前の記事で作成した、各農薬に対して適用中の単価を抽出した一意のデータ。
「農薬情報」シート
「単価情報（期間抽出）」シート
この「農薬情報」シートの各データに、該当する農薬の規格や単価を「単価情報（期間抽出）」シートから持ってきてくっつけたような、新しいテーブルを作りたい、というのが今回実現したいことです。
作成したシートと関数
作成方法は、以下の通りです。「農薬詳細情報」シートを新しく作成し、以下のように関数を入力します。
セルA1
=QUERY('農薬情報'!A:J, "select A, B")
セルC2
=ARRAYFORMULA(IFERROR(VLOOKUP($A2:$A,'単価情報（期間抽出）'!$C:$K,{3, 4, 5, 6, 7},0)))
画像で見てわかりやすいように、セルに色付けしてみました。黄色部分が「農薬情報」シートより抽出、青色部分が「単価情報（期間抽出）」シートより抽出した部分です。
ちなみに、1行目の列名については少し注意が必要です。
QUERY関数を使った「農薬情報」の列名（A列・B列）はQUERY関数で自動で入ってきますが、VLOOKUP関数＋ARRAYFORMULA関数を使った「単価情報（期間抽出）」の列名（C列〜G列）は手入力する必要があります。
QUERY関数を使ってデータ範囲を横に結合する方法
今回のケースでは、2つのデータ範囲を横に結合するような形になっています。
テーブルを横に結合するやり方について、QUERY関数だけで実現する方法もあるようです（下記の記事参照）。ですが数式のわかりやすさを考えると、今回のようなQUERY関数とVLOOKUP関数＋ARRAYFORMULA関数の合わせ技を使った方が便利そうですね。
その他の関数の活用
複数の関数の入れ子構造になっているので、一番内側から順番に見ていきます。
VLOOKUP関数での複数列の参照
ここではVLOOKUP関数を使う際に、複数列を指定して参照するというテクニックを使っています。
VLOOKUP関数では、第3引数に数値を入力して列の指定をします。この時に複数列の指定をするには、第3引数に波括弧{}を使って列番号をカンマ区切りで入力します。
以下、比較。
VLOOKUP関数で1列のみ参照
=VLOOKUP($A2:$A,'単価情報（期間抽出）'!$C:$K, 3, 0)
VLOOKUP関数で複数列を参照
=VLOOKUP($A2:$A,'単価情報（期間抽出）'!$C:$K,{3, 4, 5, 6, 7},0)
公式ドキュメントはこちら。
IFERROR関数でエラー表示を無くす
IFERROR関数を使って、VLOOKUP関数でエラーが出た際にセルが空白になるようにしています。
公式ドキュメントはこちら。
ARRAYFORMULA関数を使う
最後にスプレッドシート独自のARRAYFORMULA関数を使って、1つの数式を複数行にまとめて適用させています。
本来、スプレッドシートの1つのセルに記入した数式を各行に適用させたい場合は、オートフィル機能を使うなどで各行にコピーする必要があります。しかしこのARRAYFORMULA関数を使用すると、数式を1つのセルに入力するだけで良いんですね。
ARRAYFORMULA関数の引数に入れるのは、「配列関数」と呼ばれるものです。配列関数の記述の仕方は、通常の関数で1つのセル番号を入れるところを、セル範囲を入れれば良い…という感じでしょうか？
以下、比較。第1引数が変わっています。
通常のVLOOKUP関数
=VLOOKUP($A2,'単価情報（期間抽出）'!$C:$D, 2 ,0))
配列関数のVLOOKUP関数
=VLOOKUP(A2:A,'単価情報（期間抽出）'!$C:$D, 2 ,0))
実際には、この配列関数を単体でセルに入力しても、通常の関数と表示は変わりません。配列関数はARRAYFORMULA関数で使うことで効果が現れます。
公式ドキュメントはこちら。
おわりに
QUERY関数を使って、複数のデータ範囲を横に結合する方法を見てきました。
また今回、ARRAYFORMULA関数を初めて使いました。QUERY関数と合わせてスプレッドシート独自の関数で、どちらも使いこなせるとすごく便利そうですね。
ひとまずここまでで実現したいことができそうなので、今回の記事でQUERY関数の学習は終わりにします。カワムラさん、ありがとうございました〜。
参考
- 参考記事
- 公式ドキュメント
- カワムラさん著「会社員がVLOOKUPの次に覚えるQUERY関数超入門」
