[Googleスプレッドシート]ソート機能ありのリスト表示を実現する

「ソートするためのリストをソートしたい！」

ゲーム制作をしている上で仕組んだモノの備忘録的なやつです。
大量のデータの中から要素を抽出し組み合わせを作成する作業の最中でUXの悪さに辟易し作成しました。
「スプレッドシート リスト ソート」とかで検索しても狙ったものが出てこなかったので残しておきます。
自分が忘れるので細かく書いてはいますが、本筋から逸れる不要なところは閉じています。
特に特殊なことはしていないので読み飛ばしていただいて問題ありません。

完成形

データの内容はさておいて、完成形です。
マスターから選択した要素のみをフィルタリングして選択できる形になっています。

動画内で使用しているスプレッドシートのURLです。コピーした上でご確認ください。
https://docs.google.com/spreadsheets/d/1Bn2-Q21e3oR4xiTlYhP2dE4zaec-O1fCegZEC2Z3CgU/edit?usp=sharing

手順

①まずはソートしたい内容をリストにする

空白やNoneなどを入れておくと条件を消す際に便利かもしれないです。
(自分はDeleteキーで毎回消しています。)

リストの作り方

別のシートへリストにしたい項目を並べておくのをオススメします。
手入力の場合だと要素が増えたり減ったり変更になった場合かなりめんどくさいためです…

〇範囲指定

A2:A のように列全体を囲っておけば要素が増えても弄らなくて良いので楽です。
[データが無効の場合]は[警告を表示]の方が柔軟性はあります。

手入力での変更も可能になるため。ソート要素のリストの場合は完全にお好みで。

②データ一覧からソートしたデータを転記する

↓使用した式

=FILTER(
    	'シート名'!E2:E, 
		SEARCH(A2, 'シート名'!E2:E) * 
		SEARCH(B2, 'シート名'!E2:E) * 
		SEARCH(C2, 'シート名'!E2:E) 
	)

動画では隠していましたが、 FILTER関数 の力を借りてE列にデータを持ってきています。
この式を好きな場所に設置すればOKです。
すると以下の画像のように縦にズラッとデータが並びます。

FILTER関数の説明

FILTER関数公式ドキュメント

FILTER関数 は勝手にデータを抽出して下のセルに値を埋めてくれます。
逆に下になにかしら値が入ってしまっていると#REFがでます。

FILTER関数は以下の構文となっており、条件2が省略可能です。

=FILRTER(フィルタリングする範囲,フィルターの条件1,[条件2, ...])

今回は条件1のみ使用しました。
範囲はフィルタリングした結果表示して欲しい列を指定するのが一般的かと思います。
今回は、縦のデータで1列のみだったのでE2:Eとなっています。

ですが、FILTER関数は *"完全一致"での検索となります。
なので以下は完全一致するデータを使ってフィルタリングするFILTER関数の使い方です。

条件1は、[含みたい条件]と[探す範囲]を指定します。

=FILTER(A2:D,(A2:A="A"))
↓
＝FILTER(A2:Dの範囲を対象,(A2:Aの範囲に"A"が含まれていたら))

↓上記の式を入れるとこうなります。

[条件の範囲]は[フィルタリングする範囲]と同じ長さか幅になるようにしてください

条件の範囲の行数を100にすると、以下のように範囲足りてへんぞとエラーが出ます。

そしてフィルターの条件1は、+でOR条件、*でAND条件を追加できます。

↓OR条件の場合、A列に"A"と"B"が含まれるデータを抜き出してくれている

=FILTER(A2:D,(A2:A="A")+(A2:A="B"))

↓AND条件の場合、A列に"A"かつB列に"攻撃力"が含まれるデータを抜き出してくれている

=FILTER(A2:D,(A2:A="A")*(B2:B="攻撃力"))

SEARCH関数の説明

FILTER関数のみだと完全一致で検索となるので、
A_攻撃力_＋_3.4 だったり東京都杉並区のような複数の要素を持つ場合は抜き出すことができません。
そこでSEARCH関数を使って部分一致を行います。

SEARCH関数公式ドキュメント
※この記事では深い使い方をしていないのでざっくりとした説明となります。

SEARCH関数は以下の構文となっており、開始位置が省略可能です。

SEARCH(検索文字列, 検索対象のテキスト, [開始位置])

今回は[検索文字列]と[検索対象のテキスト]のみ使用しています。
この関数が返す値は、[検索対象のテキスト]内で[検索文字列]が何文字目から始まるかを返してくれます。

=SEARCH(A2,B2)

↓結果。

この記事のタイトル([Googleスプレッドシート]ソート機能ありのリスト表示を実現する)から[ソート]の文字が何文字目にあるかを出しています。

で？それをどうすんの？
となりますがSEARCH関数の説明は以上で終わります。
本筋から逸れすぎるので、おまけで説明します。

③フィルタリングした列をプルダウンできるようにデータの入力規則を使ってリストにする

FILTER関数を設置した列でプルダウンを作成することで、フィルタリングされた要素のみが出てくるリストが作成できます。
今回はE列に式を配置したのでE列を範囲に指定しました。

④条件の増やし方

式内にある、SEARCH関数を増やします。

SEARCH(A2, 'シート名'!E2:E) * 

A2が条件となるリストなので、D2に条件を追加したら

=FILTER(
    	'シート名'!E2:E, 
		SEARCH(A2, 'シート名'!E2:E) * 
		SEARCH(B2, 'シート名'!E2:E) * 
		SEARCH(C2, 'シート名'!E2:E) *
        SEARCH(D2, 'シート名'!E2:E) 
	)

となります。

この際、前のSEARCH関数の後ろに * をつけ忘れないようにします。
全ての条件を含むデータを出してほしいので、AND条件にする必要があるためです。

---

まとめ

=FILTER(
    	データの範囲, 
		SEARCH(条件を入れたセル1, データの範囲) * 
		SEARCH(条件を入れたセル2, データの範囲)
	)

FILTERとSEARCHを使用して抜き出したデータの列を[データの入力規則]に登録する。

大層なものではないですが、動画で使用したスプレッドシートを公開しているので
コピーして参考にしていただいても問題ありません。
https://docs.google.com/spreadsheets/d/1Bn2-Q21e3oR4xiTlYhP2dE4zaec-O1fCegZEC2Z3CgU/edit?usp=sharing

この記事を見て、長すぎるリストに困っている人の助けになれば幸いです。
(質問などがございましたら、添付しているTwitterのリプ欄かこの記事のコメントに投げていただければと思います。)

---

おまけ(SEARCH関数を使う理由)

SEARCH関数は、文字の位置の場所を返すと説明しましたが、
今回はその値を直接使用してはいません。

FILTER関数は
・配列データとして範囲指定したセルの値を持っている
・条件としてTRUEが帰ってきさえすれば、データを抽出してくれる
・数値をTRUEとしてみなしてくれる
という仕様です。

そのため、
SEARCH関数でどこにあるか数値で返してもらう＝その文字列が含まれている
ということになるのです。

イメージ的にはこんな感じです。

データ	フィルタ条件	SEARCH関数
Hoge1_Huga1	Hoge1	1(抽出！)
Hoge2_Huga1	Hoge1	FALSE
Huga1_Hoge1	Hoge1	7(抽出！)
Hoge2_Huga2	Hoge1	False

更にSEARCH関数は、大文字小文字を区別しないといった仕様があるため汎用性の高さで採用しました。

今回は既に決まっているリストからフィルタリングしましたが、
A2に直接文字を入力してCtrl＋Fのように入力したものを抜き出すとかに作り変える際は
大文字小文字の区別のない方が良いパターンもあるのかなといった感じです。

以上となります。
自分も調べながら書いたので間違ったことを書いているかもしれません。
その際にはご指摘いただければと思います。