スプレッドシート QUERY ソート順を選べるシステムを作る

kabu

先日作成したフリーキーワード検索システムですが、
これを「ソート」順を選べる仕様にアップデートしたいと思います。

帰家

え・・・
この前のでも結構むずかしかったのに・・・
更に複雑にするんですか？？

kabu

今回は、そんなに難しくないとは思います。
① 一つのセルから、どの列でソートするか選べるシステム
② 数式に使用した関数の説明
ってことで話をしていきましょう。

帰家

前回の記事をまだ見ていない方は、
先にこちらの記事を確認して見てください！

スプレッドシートでの検索・抽出システム範囲全体からフリー入力で検索

スプレッドシートでセルに「任意のテキスト」を入力すると、全ての列に対して検索・抽出をおこなうシステムが作りたい。ということで、QUERY関数を使いフリー入力から検索・抽出をおこなえるシステムの作り方をご紹介します。

できるYoutuber式 Googleスプレッドシート現場の教科書できるYouTuber式シリーズ

ブラウザだけで学べる Googleスプレッドシートプログラミング入門

図解！ Googleスプレッドシートのツボとコツがゼッタイにわかる本

頭がよくなるスプレッドシート問題集入門編

QUERY関数を使い、セルからソート順を選べるシステム
- 「ドロップダウンリスト用」シート
- 「出力」シート
数式に使用した関数の説明
まとめ

QUERY関数を使い、セルからソート順を選べるシステム

kabu

今回のシステムの挙動は、この記事の一番上の（アイキャッチ）画像になります。
ドロップダウンリストから、「どの列で昇順・降順ソートするか」を選ぶと、リストのソート順が変更されるって仕様ですね。

使用するシートは次の3つ

「MasterData」シート（元データのシート）
「出力」シート（ソート項目をドロップダウンリストから取得し、QUERY関数でリストを出力）
「ドロップダウンリスト用」シート（ソート項目を並べたリスト）

一つ目と二つ目については、以下の記事で作成方法を見てみてください。

スプレッドシートでの検索・抽出システム範囲全体からフリー入力で検索

「ドロップダウンリスト用」シート

kabu

QUERY関数に渡すソート順を選べるように、ドロップダウンリストを作成しておきたいと思います。

今回は「[参照]DDリスト」と名付けて作成しています。
内容は以下のとおり。

DDリスト	QUERYテキスト
番号昇順	order by Col1 asc
番号降順	order by Col1 desc
会社名昇順	order by Col2 asc
会社名降順	order by Col2 desc
宛先昇順	order by Col3 asc
宛先降順	order by Col3 desc

文章の先頭の「」も入れてくださいね。

QUERY関数「order by」の意味

 order by Col1 asc

「order by 〜 asc」では、指定した列を基準に「昇順」の並び替え指示をしています。

 order by Col1 desc

対して「order by 〜 desc」では、指定した列を基準に「降順」の並び替え指示をしています。
この場合は、共に「Col1」を基準に並び替えを指示しています。

帰家

えっと・・・
このテキストだけでは動かないですよね？
これをどう使うんでしょうか・・・？？

 =QUERY(A1:C10,"Where Col1 is not null order by Col1 desc")

kabu

そうですね。
QUERY関数はこのように記載して使います。

 =QUERY(A1:C10,"Where Col1 is not null order by Col1 desc")

kabu

この赤字部分が、今回のテキストです。
ここを可変させることで、ソート順を変えることができます。

kabu

どのように変えるのかを次の項で話しましょう。

「出力」シート

ドロップダウンリストの設定

kabu

検索画面である「出力」シートはこちらの記事で作成したものを元にしています。
簡単ですのでぜひ見て作ってみてください。

帰家

・・・簡単・・・。

「ソート用」ドロップダウンリストを設置するセルを用意します。
スクリーンショットの場合、C3に作成しています。

C3セルを選択した状態でドロップダウンリストの設定をおこないます。
（「ドロップダウンリスト」、「プルダウン」リストいいます。）
上部メニュー　＞　挿入　＞　プルダウン
で設定を追加していきます。

画面の右側に出てきたメニューから「プルダウン（範囲内）」を選びます。

データの範囲には、先ほど作成した「[参照]DDリスト」シートのA列該当部分を指定して「OK」を押します。

リストメニューを下にスクロールすると「詳細オプション」を指定出来ます。

これは、お好みですが、
「表示スタイル」を「矢印」に変更して、「完了」を押します。

すると、C3セルにドロップダウンリストが設定出来ました。

ドロップダウンリストからソート順を取得するQUERY関数の数式

kabu

では、本題。
QUERY関数の数式について説明していきます。

このスクリーンショット、A6セルにはQUERY関数の数式を書いています。

=QUERY('[MasterData]'!A3:C,"Where Col1 contains '"&C2&"'"&
SUBSTITUTE(CONCATENATE(ARRAYFORMULA(" or Col"&COLUMN('[MasterData]'!A3:C)&
" contains '"&C2&"'"))," or Col1 contains '"&C2&"'","")&
IFERROR(VLOOKUP(C3,'[参照]DDリスト'!A2:B7,2,FALSE),""))

帰家

おおおお・・・！！

kabu

大丈夫です。
（前回をやっていれば）書き足したのはこちらのみです。

&IFERROR(VLOOKUP(C3,'[参照]DDリスト'!A2:B7,2,FALSE),"")

この数式では、
VLOOKUP関数で、C3（このスクリーンショットの場合「会社名昇順」）を

「[参照]DDリスト」から探し、該当行の2列目を出力しているって内容になります。

kabu

この数式が、エラーなら（IFERROR）「””」何も表示しない。
って数式ですね。

=QUERY('[MasterData]'!A3:C,"Where Col1 contains '"&C2&"'"&
SUBSTITUTE(CONCATENATE(ARRAYFORMULA(" or Col"&COLUMN('[MasterData]'!A3:C)&
" contains '"&C2&"'"))," or Col1 contains '"&C2&"'","")&
IFERROR(VLOOKUP(C3,'[参照]DDリスト'!A2:B7,2,FALSE),""))

この部分は、C2セルの「検索キーワード」と一致した項目を「MasterData」シートから抽出するって内容でした。

たとえば「会社」と入力すると・・・
「A：C」列の中の「会社」という文言が入っている行が抽出される。

=QUERY('[MasterData]'!A3:C,
　　　　　　"Where Col1 contains '会社' or Col2 contains '会社' or Col3 contains '会社'"
　　)

kabu

長々と書いていた数式は、最終的には、こういう結果を出すためでした。
「”Col1〜Col3”で、”会社”って入っている行を抜き出して」って内容ですね。

=QUERY('[MasterData]'!A3:C,
　　　　　　"Where Col1 contains '会社' or Col2 contains '会社' or Col3 contains '会社'" &
   IFERROR(VLOOKUP(C3,'[参照]DDリスト'!A2:B7,2,FALSE),"")
)

それにこれが追加されました。
「会社名昇順」が選択されていたとすると・・・

=QUERY('[MasterData]'!A3:C,
　　　　　　"Where Col1 contains '会社' or Col2 contains '会社' or Col3 contains '会社'" &
   " order by Col2 asc"
)

帰家

おおお・・・
数式がスッキリしてようやくなんとなく意味が分かった！！

【SALE】ロジクールワイヤレスマウストラックボール無線 M575Sa Bluetooth Unifying トラックボールマウスワイヤレスマウス windows mac iPad 国内正規品 1年間無償保証

Anker PowerCore 10000 (10000mAh 大容量モバイルバッテリー)【PSE技術基準適合/PowerIQ搭載】 iPhone&Android対応 (ブラック)

【Amazon.co.jp限定】SanDisk SSD 外付け 1TB USB3.2Gen2 読出最大1050MB/秒防滴防塵 SDSSDE61-1T00-GH25 エクストリームポータブルSSD V2 Win Mac PS4 PS5 エコパッケージ 5年保証

Anker 547 USB-C ハブ (7-in-2, for MacBook) Thunderbolt 4 100W USB PD対応 4K HDMIポート microSD & SDカードスロット 5Gbps USB-Cポート USB-Aポート搭載高速データ転送

数式に使用した関数の説明

kabu

今回はこんな関数を使用していますね。

QUERY

QUERY関数は、Google スプレッドシートで提供されている強力な関数の一つで、指定された範囲からデータをクエリ（問い合わせ）し、条件に一致するデータを抽出または操作することができます。この関数は、Google Visualization API Query Languageを使用して、データをフィルタリング、ソート、集計するための複雑なクエリを記述することが可能です。

QUERY(データ範囲, クエリ文字列, [ヘッダー数])

データ範囲: クエリを実行するデータの範囲を指定します。
クエリ文字列: 実行するクエリを文字列として指定します。この言語はSQLに似ており、SELECT, WHERE, ORDER BY, LIMITなどの命令を使用してデータを操作します。
ヘッダー数 (オプション): データ範囲の最初の何行をヘッダーとして扱うかを指定します。この引数を省略すると、Google スプレッドシートが自動でヘッダーを判断します。

QUERY関数は、大量のデータから特定の情報を抽出したい場合や、データを特定の条件でフィルタリングして表示したい場合、またはデータをソートや集計したい場合などに非常に便利です。たとえば、特定の条件を満たす行のみを抽出したり、特定の列のデータを基に集計を行ったりする場合に使用できます。

IFERROR

IFERROR関数は、式を評価してエラーが発生した場合に、指定した値を返します。これにより、エラーが発生してもスプレッドシートが読みにくくなるのを防げます。

IFERROR(値, エラー時に返す値)

VLOOKUP

VLOOKUP関数は、スプレッドシート内で縦方向にデータを検索し、指定した値に一致する最初の行から特定の列のデータを返します。この関数は、ある表やリストから特定の情報を際立たせますとても便利です。

スクスコードをコピーする

VLOOKUP(検索値, 範囲, インデックス番号, [検索が完全一致するかどうか])

検索値: 検索する値。この値に一致するデータを範囲内から見つけます。
範囲: 検索を行う範囲。この範囲の最初の列で検索値を検索します。
インデックス番号: 範囲内で検索値に一致する行から、どの列のデータを返すか指定する数値。たとえば、1 なら範囲の最初の列、2 なら次の列のデータを返します。
検索が完全一致するかどうか(オプション): TRUEの場合は近似値検索を行います、FALSEの場合は完全一致検索を行います。省略しますが、一応TRUEが適用されます。

VLOOKUP関数は、顧客リストから顧客IDに基づいて顧客名を取得する、商品コードに基づいて価格を検索するなど、多様な流れで使用されますVLOOKUP。にのみ使用でき、右方向にデータを検索します。これが制限となる場合は、INDEXとMATCH関数の組み合わせなど他の方法を検討することもあります。