QUERY関数とは、必要なデータだけを抽出する関数です。「QUERY」という用語には「問い合わせ」という意味があり、条件に該当するデータがないかデータに問い合わせ、抽出するようなイメージで使われることが多いです。
しかし、「スプレッドシートではQUERY関数が使えるけど、ExcelでもQUERY関数が使えるの？」と気になる方も多いのではないでしょうか。この記事では、Excel上でQUERY関数が使えるか、代用できる関数があるかを解説します。（本記事では、Microsoft Excel 2021を利用）。
ExcelでQUERY関数は使えないの？
ExcelでQUERY関数を使うことはできません。QUERY関数はGoogleスプレッドシート上でデータを抽出する関数であり、Excel上で使うことができません。
Excel上のデータを抽出したいときは、別の関数で代用する必要があります。
関連記事：【スプレッドシート】QUERY関数の使い方とは？基本から応用まで徹底解説します
ExcelでQUERY関数の代用で使える関数
QUERY関数の代用として使えるのは、「FILTER関数」です。下記では、FILTER関数の使い方を解説します。FILTER関数は、QUERY関数と同じく指定条件に合致するデータだけを抽出できる関数です。
【1】FILTER関数の基本構文は、「=FILTER(範囲,条件)」です。全体のデータから、該当するデータを抽出します。
【2】空きスペースに、FILTER関数を入力します。A店の商品・個数だけを抽出したい場合は、「=FILTER(B3:D12,B3:B12=”A店”)」と入力します。
この関数は「B3からD12までの表全体のうち、B3からB12の縦列に、A店と記載のあるもの」を意味します。
【3】実行するとA店のリストだけが抽出されるので、店舗別の状態を可視化しやすくなります。
【4】同様の関数で、B店やC店のリストも作れます。参照元に入力されている内容が変われば、関数で表示された内容も連携して変わるので便利です。
フリーランスエンジニアに最高の待遇を【Free Tech Hub】
QUERY関数が使える場面は？
QUERY関数が使える場面は、Googleスプレッドシートに限定されます。GoogleスプレッドシートとExcelは、どちらも表計算ソフトであり共通して使える関数も多いです。しかし、QUERY関数のようにどちらか片方でしか機能しない関数もあるので注意しましょう。
QUERY関数の構文
ここでは、QUERY関数を使う場合の構文を紹介します。
QUERY関数は「=QUERY(データ, クエリ, [見出し])」の構文で使います。それぞれ、「データ」の部分を第一引数、「クエリ」の部分を第二引数、「見出し」の部分を第三引数として設定します。
基本的な構文は、FILTER関数と変わりません。
第一引数
第一引数は、基本構文の「QUERY(データ, クエリ, [見出し])」における「データ」の部分です。「どの範囲から情報を抽出するか」を示す項目であり、下記の青で囲った部分のことを指します。
第二引数
第二引数は、基本構文の「QUERY(データ, クエリ, [見出し])」における「クエリ」の部分です。「どの範囲を対象に検索するか」を示す項目であり、下記の例における赤で囲った部分のことを指します。
第三引数
第三引数は、基本構文の「QUERY(データ, クエリ, [見出し])」における「見出し」の部分です。「どのキーワードで検索するか」を示す項目であり、下記の例では「A店」と指定しています。
QUERY関数と一緒に利用する関数
QUERY関数を使いたいときは、他構文と一緒に利用します。
Select
Selectは、選択範囲内で表示させたい列を指定する言語です。特定列の情報だけが欲しいときに役立ちます。
「=QUERY(選択範囲,“Select 列1,列2,列3,…”)」
Order by
Order byは、抽出したデータをソートするときに使う言語です。ソートとは、数値・日付・時間・金額などを並び替える機能です。Order byを使えば数字の並び変えができるので、別途ソートしなおす手間がかかりません。
「=QUERY(A1:F10,”select(B),F ORDER BY F desc”)」
Where
Whereは、選択範囲内で表示させたい行を指定する言語です。特定の行情報だけが欲しいときに役立ちます。
「=Query(選択範囲,“Where 条件範囲列=条件”)」
Group by
Group byは、選択した範囲における合計値・平均値・最大値・最小値を出すときに役立ちます。
「=Query(選択範囲,使いたいGroup by(条件))」
合計値は「COUNT」、平均値は「AVG」、最大値は「MAX」、最小値は「MIN」で算出できます。
QUERY関数で別シートのデータを抽出する方法
QUERY関数で別シートのデータを参照する場合、対象シートの名称をダブルクオートで囲んで設定します。「QUERY(“対象シートの名称”データ, クエリ, [見出し])」と入力すれば、関数の入力先シート以外でも参照できます。
QUERY関数を利用する際の注意点
QUERY関数を使用する際の注意点を解説します。
外部のスプレッドシート上のデータを参照するときの注意点
QUERY関数では、外部のスプレッドシートのデータを参照することが可能です。「別シートではなくスプレッドシート自体が違うデータを参照したい」というときは、下記を使います。
「=QUERY(別スプレッドシートの名称,”範囲″,条件)」
Excelで使うFILTER関数の場合、別のExcel上にあるデータを参照することはできません。データを統合してひとつのExcelにしてFILTER関数を使いましょう。どうしてもファイルを分けたいときはGoogleスプレッドシート上にコピーし、QUERY関数を使うのもおすすめです。
表示形式のズレに関する注意点
形式は、「表示形式」タブの「数字」から設定できます。項目を抽出したい場合は、あらかじめ設定しておいた方が良いでしょう。
- 「￥」や「＄」など指定の通貨マークを入れたい場合
- 「￥」や「円」など通貨の単位を揃えたい場合
- 「2023/01/01」や「2023年1月1日」など日付の表示を揃えたい場合
- 「23時30分」や「23：30」など時間の表示を揃えたい場合
例えば、「2023/01/01」のキーワードで抽出したくても、元データが「2023年1月1日」で入力されている場合、QUERY関数では抽出できません。表記ブレによる見落としがないよう、確認しておくのがおすすめです。
関連記事：Googleスプレッドシートの使い方で知っておきたい操作を紹介！Excelとの違いは？
まとめ
Excel上で、QUERY関数を使うことはできません。Googleスプレッドシートで利用できる関数のため、Excelでデータを抽出したいときは別の方法を試しましょう。
代替手段として使えるのが、FILTER関数です。使い方は同じなので、第一引数・第二引数・第三引数を理解して設定すれば問題ありません。
QUERY関数に関係する用語集
| 用語 | 意味 |
| QUERY関数 | 特定の範囲において、指定のキーワードに合致するデータをピックアップする関数です。効果はFILTER関数と同じですが、Googleスプレッドシートのみで使えます。 |
| FILTER関数 | 特定の範囲において、指定のキーワードに合致するデータを抽出する関数です。効果はQUERY関数と同じですが、Excel上のみで使えます。 |
| 第一引数 | QUERY関数における「データ」の部分です。どの範囲から情報を抽出するか」を示す項目でもあります。 |
| 第二引数 | QUERY関数における「クエリ」の部分です。「どの範囲を対象に検索するか」を示す項目でもあります。 |
| 第三引数 | QUERY関数における「見出し」の部分です。「どのキーワードで検索するか」を示す項目でもあります。 |
