スプレッドシートでフィルタ機能を使わずに絞り込みを行う

はじめに

ExcelやSpreadSheetにはフィルタ機能が存在していますよね。
ある項目が、指定された値と一致しているものを抽出する。っていうあれです。
だけど、表計算ソフトを使い慣れていない人や、覚える気がない人にとっては、なかなか難しい機能です。

本記事は、とある実際のお客さんからの要望で、
「シート上に3つくらい項目名と値を入れる枠を用意して、その条件に合致するデータを抽出して欲しい。」
という正にフィルタ使えば済む要件に立ち向かったときのノウハウを書き記したものです。

今回説明するのに使うデータ

侍JAPANのメンバー一覧(笑)

フィルタ機能で

例えば、守備が「内野手」で、所属が「スワローズ」で、出身地が「熊本県」という絞り込みを行うとします。

絞り込み方は大きく分けて2種類で、単純に値が一致するものだけを抽出するのであれば、方法１を使うのが楽だと思いますし、もうちょっと複雑な条件で抽出したい(名前が「山」から始まるとか)場合は、方法２を使います。
方法１．抽出したい値だけをチェックして絞り込む

方法２．条件でフィルタを使って絞り込む

3つの絞り込み条件を設定した結果はこんな感じ。

簡単だと思うんですけどね・・・。却下されました。

代替案

しょうがないので、こんなイメージですか？って持って行ったのが、これ。

お客さん「そうそう、これこれ」

要件をまとめると

• 絞り込み条件は最大3つ
• すべてAND条件
• 完全一致のみでOK
• 絞り込みに使う項目も任意に変更可

実装

実際のデータが格納されているシート名を「データ」、絞り込みを行うシート名を「絞り込み」としておきます。

「データ」シートのデータを「絞り込み」シートに表示

他のシートのデータを持ってくるには、QUERY関数を使います。
A7セルに以下を設定すれば、全データが「絞り込み」シートに表示されます。
=QUERY('データ'!A:E)
しかし、この状態だと後で絞り込みを行う際に、ヘッダ行(項目名が入った行)も絞り込みに入ってしまうため、ヘッダ行とデータを別に取得するように変更します。

A7セルに=QUERY('データ'!A1:1)を設定します。
これで、「データ」シートの1行目のデータのみ取得し、「絞り込み」シートに表示します。

次に、A8セルに、=QUERY('データ'!A2:E)を設定します。
これで、「データ」シートの2行目以降のデータを取得し、「絞り込み」シートに表示します。

後のためにもう1つ変更を加えます。
A8セルを、=QUERY('データ'!A2:E, "select *")に書き換えます。
QUERY関数の第2引数にはクエリが指定でき、"select *"というのは、抽出条件なしに全項目取得するっていう意味です。

1つの条件で絞り込みを行う

まずは、画面の入力を気にせず、固定値で「守備」が「内野手」という条件で絞り込んでみます。
A8セルを、=QUERY('データ'!A2:E, "select * where A='内野手'")に書き換えます。
where句で絞り込みを行う条件を指定しています。A=の「A」は列の記号です。A列で絞り込むのでA=となります。
これで「守備」が「内野手」のデータのみが表示されます。

つぎに、B2セルに入力した内容で絞り込まれるようにします。
A8セルを以下に書き換えるだけです。
=QUERY('データ'!A2:E, "select * where A='"&B2&"'")
クエリにセルの値を使用するときは、"&セル位置&"と指定するというお約束のようなものです。

難関1　絞り込みに使う項目も任意に変更可

今回は絞り込みに使う項目も任意に変更可、すなわち、A2が「守備」ではなく、「所属」となることもあるわけです。
「守備」の場合は、where A=ですが、「所属」の場合は、where D=とならなくてはいけません。

A2の値によって列の記号を取得するには、以下の式を使用します。
=SPLIT(ADDRESS(1, MATCH(A2, 7:7, 0), 4),"1")

分解して説明します。
=MATCH(A2, 7:7, 0)
これで、7行目の中で、A2セルの値と一致するのは何番目かが取れます。最後の0は、完全一致を表しています。
A2が「守備」の場合は、1。「所属」の場合は、4が取れます。見つからなかった場合は、#N/Aというエラーになります。
=ADDRESS(1, MATCH(A2, 7:7, 0), 4)
ADDRESS関数は、指定された値に該当するセルの位置が取得できます。
これによって、A2が「守備」の場合は、A1。「所属」の場合は、D1が取れます。
=SPLIT(ADDRESS(1, MATCH(A2, 7:7, 0), 4),"1")
SPLIT関数は、区切り文字で分割をします。この場合、"1"という文字列で分解されます。
従って、A2が「守備」の場合は、A。「所属」の場合は、Dが取れます。

難関1の内容を踏まえ、A8セルを下記に書き換えます。
=QUERY('データ'!A2:E, "select * where "&SPLIT(ADDRESS(1, MATCH(A2, 7:7, 0), 4),1)&"='"&B2&"'")
これで、A2セルに任意の項目名、B2セルにその値を設定することで、絞り込みが行えるようになりました。

3つの条件に増やす

クエリで絞り込み条件を増やすには、A='内野手' and D='スワローズ' and E='熊本県'のようにandで繋ぎます。
シートに下記の条件を設定します。
A2：「守備」、B2：「内野手」
A3：「所属」、B3：「スワローズ」
A4：「出身県」、B4：「熊本県」

A8のセルを以下に書き換えれば、指定された条件で絞り込んだ結果が得られます。

=QUERY('データ'!A2:E, "select * where "&
  SPLIT(ADDRESS(1, MATCH(A2, 7:7, 0), 4),"1")&"='"&B2&"'"&
  " and "&SPLIT(ADDRESS(1, MATCH(A3, 7:7, 0), 4),"1")&"='"&B3&"'"&
  " and "&SPLIT(ADDRESS(1, MATCH(A4, 7:7, 0), 4),"1")&"='"&B4&"'")

難関2 絞り込み条件が設定されていない時は無視するよ

先ほどの条件から、「出身地」の「熊本県」を消してみます。
すると、結果が「#N/A」となり、マウスカーソルを当ててみると、
「クエリが空の出力で完了しました」というエラーとなってしまいます。
本来であれば、守備、所属だけを条件に検索して欲しいところですが、正しく出ません。
実は、このままの式だと、「出身地」が空欄のデータを検索していて、
今回のデータの場合、全員の出身地が設定されているので、該当データがなくなるというわけです。

そこで、値が設定されていなければ、その条件は無視する。という式に書き換えます。
使うのは、IF。これによって条件を満たす場合とそうでない場合で処理を変えることができます。
=IF(論理値, TRUE値, FALSE値)
今回の場合、値が設定されていれば、検索条件とし、値が設定されていなければ、検索条件にしないという記述をします。
3つ目の検索条件に対して、書き換えると、こんな感じになります。
IF(B4<>"", "and "&SPLIT(ADDRESS(1, MATCH(A4, 7:7, 0), 4),1)&"='"&B4&"'", "")
これで、3つ目の条件が空欄の場合は、2つ目までの条件のみで検索された結果が表示されるようになります。

同様に1つ目、2つ目の条件についても対応した結果が以下の状態です。

=QUERY('データ'!A2:E, "select * where "&
  IF(B2<>"", SPLIT(ADDRESS(1, MATCH(A2, 7:7, 0), 4),"1")&"='"&B2&"'", "")&
  IF(B3<>"", " and "&SPLIT(ADDRESS(1, MATCH(A3, 7:7, 0), 4),"1")&"='"&B3&"'", "")&
  IF(B4<>"", " and "&SPLIT(ADDRESS(1, MATCH(A4, 7:7, 0), 4),"1")&"='"&B4&"'", ""))

これで、値が設定されている条件のみで検索ができるようになりました。

難関3 絞り込み条件が1つもないときは全部出すんだよ

つぎに、すべての条件を消してみます。すると･･･
#VALUE!　エラー：関数 QUERY のパラメータ 2 のクエリ文字列を解析できません
というエラーになってしまいました。

これは、すべての検索条件を消したことにより、クエリが以下のようになってしまうことが原因です。
select * where
whereがあるのにその後ろに抽出条件がないため、クエリの構文として正しくないのです。

簡単に考えられる方法としては、
1つでも条件が入っていたら、whereをつけて条件を入れるという分岐を入れる。
という方法が考えられるかと思います。

しかし、1行目に値が設定されず、2行目に値が設定された場合は、以下のクエリになります。
select * where and D='スワローズ'
whereの直後にandが来るため、これもクエリの構文として正しくないため、エラーになります。

それも考慮して、1つ目の条件だったら、"and"を付けないみたいな分岐を増やす？
それだと、もう、IFだらけで、訳が分からなくなります。実際にやろうとしましたが断念しました。

実は、こういうときに使える素敵な方法があるのです。

=QUERY('データ'!A2:E, "select * where 1=1 "&
  IF(B2<>"", " and "&SPLIT(ADDRESS(1, MATCH(A2, 7:7, 0), 4),"1")&"='"&B2&"'", "")&
  IF(B3<>"", " and "&SPLIT(ADDRESS(1, MATCH(A3, 7:7, 0), 4),"1")&"='"&B3&"'", "")&
  IF(B4<>"", " and "&SPLIT(ADDRESS(1, MATCH(A4, 7:7, 0), 4),"1")&"='"&B4&"'", ""))

その方法は、whereの後ろに必ずTRUEとなる条件(1=1)を入れておくのです。
そうすることで、条件が1つも設定されていない場合は、
select * where 1=1
となり、構文的に正しく、すべてのデータが抽出できるクエリとなります。

1行目のみ値が設定された場合は、select * where 1=1 and A='内野手'
2行目のみ値が設定された場合は、select * where 1=1 and D='スワローズ'
3行目のみ値が設定された場合は、select * where 1=1 and E='熊本県'
また、それぞれを組み合わせても正しいクエリとなります。

まとめ

フィルタ機能を使わずに、任意の条件でデータを抽出する方法を紹介しました。
これによって、フィルタ機能を使いこなせない人にも、自由にデータの抽出ができるようになります。
しかし、フィルタ機能では、もっと柔軟な抽出ができます。本当なら、ぜひフィルタ機能を使って頂きたいです。

また、where 1=1の仕組みは、開発をしていると色々な場面で応用ができます。
開発に携わる人としては、是非覚えておいて欲しいです。