SpreadSheetのQuery関数でデータ索引を手軽にやってみる

Spreadsheetにてデータを加工するために関数を利用して索引を掛ける際、左端に列を追加調整してVLOOKUPを使う事が度々あります。

ただ、キーとなる列が増えたり、色々なパターンで索引を掛ける必要が出てくると、流石に随時対応で乗り切り辛くなってきます。

Query関数を使うことで若干の手間が省けるようになります。

Query関数とは

Google Visualization APIのクエリ言語を利用した、データ全体へのクエリ実行機能です。

QUERY(Column:Column, "SELECT * ")

第二引数にSQLを指定しますが、最初に検索範囲のカラムを指定するためFrom句が存在しないところが特徴です。

指定できる句は以下の通りです。

機能	意味
SELECT	指定したカラムの値を取得します。
WHERE	該当する行のみ返します。指定がない場合は全行が取得されます。
GROUP BY	行を横断して値を集約します。
PIVOT	該当列の各値を軸にして、新しい列を生成します。
ORDER BY	列の値で行を並び替えます。
LIMIT	返す行数に上限を付けます。
OFFSET	返す行のうち、指定数を最初からスキップします。
LABEL	カラム名を指定します。Asのような扱いです。
FORMAT	出力する値に型指定をかけます。
OPTION	追加設定を入れます。

見慣れないキーワードもありますが、機能として全く新しいものがあるわけではありません。

VLOOKUPとの違い

条件文の見通しのよくなる点がポイントです。

=VLOOKUP(E5, A5:C10, 2,FALSE)

QUERYを使うと以下のようになります。

=QUERY(A5:C10, "SELECT B WHERE A=" & E5, 0)

SQL自体は結合を組み合わせることで、別のセルの値を参照することも可能です。

WHERE句への指定時の注意

そのままパラメータを渡しても正常に受け付けないケースがいくつかあります。

日付指定

DATEにて日付データとして認識させます。

=QUERY(A:B, "SELECT A WHERE B= DATE 'YYYY-MM-DD'", 0)

まとめ

Spreadsheet上にて、VLOOKUPによる索引に対応させるためにカラムを追加する必要がでてきた場合等、なるだけ表そのものへの調整を加えたくない場合には使いやすい関数だと思います。

集計で困った時等に思い出せると楽になるかもしれません。