Spreadsheet-query-Eyecatch

広告

スプレッドシートquery関数の使い方!vlookupより断然オススメ!

2017年8月14日

複数あるシートやデータの中から「ほしいデータ」だけが抽出出来たら一覧表の作成なんかが捗りますよね。

QUERY関数の実行後イメージ

スプレッドシート独自関数の1つQUERY関数なら

  • 指定したデータの抽出
  • 複数条件でも抽出

といったことが可能です。

Excelには「QUERY関数」はありません!

今回は別シートから「欲しいデータ」だけを簡単に抽出出来る関数、スプレッドシート・QUERY関数の使い方についてご説明していきます!

スプレッドシートQUERY関数の使い方をマスターしよう!

Excelには出てこない、あまり馴染みのない「QUERY関数」。でもマスターしてしまえば非常に使える関数でもあるんです。

QUERY関数の概要

先ずはQUERY関数の概要をご説明します。

QUERY関数とは、Google Visualization API のクエリ言語を使用して、データ全体に対するクエリを実行します。

引用元:QUERY

あひる
あひる
え...(Visualization APIとか何それうまいの?)

簡単に言うとGoogleのクエリ言語を使用して、データ全体を抜き出すことを可能にした関数 ということです。

とはいっても使えないと意味ないので、あまり気にせず構文を見ていきましょう↓↓

QUERY関数の構文

QUERY関数の構文の内訳です。

QUERY関数の構文

=QUERY(データ、クエリ、[見出し])

各内訳の意味をご紹介します。

データ(範囲)

QUERY関数を実行する「データの範囲」のことを指します。

QUERY関数のデータ範囲

QUERY関数で取得したデータの、各列に指定できるのは

  • プール値
  • 数値(日付/時刻など)
  • 文字列の値

といった3つの値のみです。

別シートを指定する場合はシングルクオテーション( ' )で囲みます。(自動付与)

クエリ(抽出条件)

QUERY関数のクエリとは、「Google Visualization API」のクエリ言語で記述された実行対象のクエリのこと。

例えば「”名前一覧”という別シートからD列だけを抽出するクエリ」は、以下(赤フォント)の様に表します。

クエリを抽出する例

=query('名前一覧'!A1:Z1000,"select D")

クエリは("")ダブルクオーテーション・二重引用符で囲むか、適切なテキストを含むセルへの参照にする必要があります。

見出し(省略可)

データ上部の見出し行(下図)の数。

QUERY関数での見出し

省略したり、「-1 」と指定した場合はデータの内容に基づいて推測された結果が反映されます。

QUERY関数がVLOOKUPより優れていると思った点

VBAもscriptも分からない管理人の個人的な意見です。

実行対象をクエリで指定=「テキスト」でも指定出来るという点がVLOOKUPより分かり易いな〜と感じました。

抽出がオートフィルを実行する感覚に近くて操作しやすいな〜って。

VLOOKUPは実行対象を「テキスト」で指定できないので...

あひる
あひる
え?これだけ?VLOOKUPより優れてるって言いすぎだろ
ううこ
ううこ
この記事書いたやつちょっとイカれてるな

と、大まかにQUERY関数について掴んだところで実際に使ってみましょう

スプレッドシートquery関数の使い方

QUERY関数を試しに使ってみましょう。

先ずは2つのシートを用意します。以下のtoori2種類の

  1. 名前一覧シート
  2. 取得シート

といったシートを作成しました。

名前一覧シートの「東京都」出身の人だけを、取得シートに抽出していきます。

抽出後イメージ

別シートに抽出したいデータの「列・テキスト」といった内容をメモっておくとスムーズです。

任意のシートから別シートへQuery関数でデータを抽出する

次に、別シート(取得シート)を開いてQUERY関数を記入します。

QUERY関数を入力したイメージ

今回はB1セルに「 =QUERY('シート名'!範囲,"where 列='抽出クエリ'") 」と記入しました。

B1セルへ記入した式

=QUERY('名前一覧'!A1:Z1000,"where D='東京都'")

表示したい範囲を減らす時は「A1:B1000」など絞ることも可能です。

そしてEnterを押すと...

絞ったデータが抽出された

「東京都」に該当するデータのみ抽出されてますね!

アニメーションだとこんな感じ(見づらくてごめんなさい)↓

queryを実行した様子

これ、元のシートをフィルターをかけてコピペすれば出来る内容ではあります。

Query関数はデータ更新もしてくれる

だがしかし!!

しかしですよ!QUERY関数なら

元シートがデータ更新された途端、QUERY関数の方も自動更新してくれるんですよね。

OYAKUN
OYAKUN
「VBAもJavascriptも初心者」なジブンにとっては神関数です

まとめ. query関数で別シートのデータを1つにまとめよう!

というわけでスプレッドシートでQUERY関数は上手く使えましたか?

シート内だけでなく、別シートのデータを抽出・自動更新してくれる超便利なQUERY関数。

  1. 抽出したいデータの列・テキスト(クエリ)をメモっておこう!
  2. 抽出セルに=QUERY('シート名'!範囲,"where 列="クエリ")を記入しよう!
  3. 抽出結果を見てニッコリ

まだまだこれから開拓の余地がありそうですね!

あひる
あひる
importrange関数などと組み合わせて使ってみると、かなり応用ができそうだね
ううこ
ううこ
君が早速紹介したまえ
あひる
あひる
え、えええw