Googleスプレッドシートでは、他の表計算と同様データ一覧から特定のレコードを抽出できる機能がついています。
これ、フツーに多機能ですし通常利用では問題ない場合が大半です。ですが、同じシート内でレコード一覧も見たい、抽出もしたい、集計もしたい。なんてことはありませんか？....ありますよね？(強引)
そんな迷えるアナタの助け舟となる仕組みをこの度作ってみました。さっさと結論から教えろ！という方、関数まで詳しく解説しろ！という方、筆者の駄文に付き合ってくださる方...みなさまに対応した記事にしましたので、ぜひ見ていってください。
📊 先に仕組みを見たい方はこちら (Googleスプレッドシート／無料)01. 作った経緯：(既存のフィルタ機能じゃ)いかんのか？
冒頭触れましたが、Googleスプレッドシートくんにはちゃんとフィルタ機能が備わっており、これを十分活用できるならばそもそも今回紹介するような仕組みは不要。ですが、わざわざこんなものを作ったのは大きく3つ理由があります。
1.同じシート内で閲覧を簡潔させたい
既存のフィルタ機能は便利ですし、個別に値で絞り込むことはもちろん、特定条件やセルの塗りつぶし色でフィルタリングをかけることが可能であり、既に十分多機能です。
ですが、元のデータ一覧にフィルタをかけると、フィルタがかかった行が非表示になります。その結果、データ一覧の横に集計表などを配置していると、その集計表まで一部非表示になってしまいます。集計結果を見ながらその内訳のレコードを確認したい...なんて場合には不便です。
2.フィルタ機能を使いこなせる人ばっかりじゃない
ファイルを作った本人や、フィルタ機能を使いこなせる人が見落としがちな点がこれ。ビジネスで使っていないという会社がないのでは？ってくらい超メジャーですし、ちゃんと使える人からすると既にフィルタ機能があるじゃないか、と思うわけです。
ですが意外や意外。単純なデータ入力や方眼紙として使うことはできても、レコードの抽出や集計(ピボットテーブル)、関数が出てくると途端にお手上げという人は結構います。使えるようになってくれよ！と言いたいところですが、この手のソフト全般にアレルギー反応を示す人はそれなりにいるので、全員にマスターしてもらうのは至難の業。
3.元のデータ一覧を更新したいケースがある
対象とするデータ一覧が特定日時点のデータである場合は、大人しくシートをコピーしそれを参照するような運用にした方がよいかもしれません。ですがこの運用にすると、対象データが日々データ更新されるようなケースだと、コピーした時点の古いデータを参照し続けてしまうことになります。
特定の関数を使い最新のデータ一覧を表示する専用のシートを作成してもよいですが、メンテナンス対象が増えることも懸念としてあります。かといって元データを参照するとなると、関数を使わざるを得ないですし、関数を使わないプレーンな文字列が入力されたテーブルにするにはGASを使う必要があります。
これらのような点があるので、その解決策として考えたのが今回ご紹介する、既存のフィルタ機能を使わないレコード抽出の仕組みなのです。既存機能ほど多機能ではなく弱点もありますが、操作がシンプルなので初心者でも使いやすく作れたかな？と思っています。
多機能なものであっても、普段使うユーザーにとって使いづらいファイルだと、結局誰にも見られなくなって終わりです。何でもかんでも時間をかけて作り込むことを推したいわけではないのですが、ピンポイントでこういう工夫もできるのね、という観点で参考にしていただけると嬉しいです。
02. 活用方法：こんな感じで使えます
それでは、今回作成したファイルでできることを見ていきましょう。冒頭にもリンクを貼りましたが、改めて以下のファイルをご覧ください。(閲覧権限のみ付与しているので、ご自身のGoogleアカウントにコピーしてお使いください🙇)
📊 レコード抽出の仕組み (Googleスプレッドシート／無料)このファイルは社員の名簿を想定して作ったもので、仮で20人分のダミー社員情報を用意してみました。データ一覧の範囲にフィルタをかければレコードを簡単に抽出できますが、前の章で述べた課題があるので、それらを避けたのがこのファイル。
赤文字で補足した箇所に配置したプルダウンをクリックすると、以下に示す各列に対し、該当するレコードを抜き出して表示することができます。(プルダウンで何も選んでいない場合は、すべての値を選択されたものとして扱っています)
- 1列目：レコードNo.
- 2列目：氏名
- 3列目：社員番号
- 4列目：入社年月日
- 5列目：役職
- 6列目：所属部署
また同様に、赤文字で補足した画像上部のチェックボックスとその直下にあるプルダウンを選ぶと、昇順／降順の切り替え、およびどの列にその順序を適用するかを設定することができます。
それでは、この機能の「キモ」である関数の中身を、次の章で触れていきましょう。
03. 解説：詳しく中身の関数を見てみよう
こういうファイルをネットで見つけたとき、その中身を解説してくれると嬉しいなぁといつも思っていました。なので、この記事を見つけてくれたあなたもそのはず！と思うので解説していきます！
まず、関数の全容はこんな感じ。関数全体を見ると「うげっ...」と思いがちですが、一つずつ分解していくと動きを理解しやすいです。順番に見てきましょう。
=SORT(
FILTER(B3:G22,
IF(Q5="",B3:B22<>"",REGEXMATCH(TO_TEXT(B3:B22),"^(" & SUBSTITUTE(Q5, ", ", "|") & ")$")) *
IF(R5="",C3:C22<>"",REGEXMATCH(TO_TEXT(C3:C22),"^(" & SUBSTITUTE(Q5, ", ", "|") & ")$")) *
IF(S5="",D3:D22<>"",REGEXMATCH(TO_TEXT(S5:S22),"^(" & SUBSTITUTE(S5, ", ", "|") & ")$")) *
IF(T5="",E3:E22<>"",REGEXMATCH(TO_TEXT(T5:T22),"^(" & SUBSTITUTE(T5, ", ", "|") & ")$")) *
IF(U5="",F3:F22<>"",REGEXMATCH(TO_TEXT(F3:F22),"^(" & SUBSTITUTE(U5, ", ", "|") & ")$")) *
IF(V5="",G3:G22<>"",REGEXMATCH(TO_TEXT(V5:G22),"^(" & SUBSTITUTE(V5, ", ", "|") & ")$")))
,IFERROR(MATCH(Q3,Q6:U6,0),1),Q2)ブロック1：全体を並び替える外枠の部分
この関数の一番外側の枠組みで、最終的な表示データ一覧の並び順を操作している部分です。各関数で行っている内容は以下の通り。
- SORT(データ, 並び替える列の番号, 昇順か降順か)
- 指定したルールに従って、データを並び替える関数です。「TRUE」なら昇順、「FALSE」なら降順になります。上記の画像で示した、チェックボックスに✔が付いていればTRUE=昇順ですし、付いていなければFALSE=降順です。
- IFERROR(MATCH(Q3,Q6:U6,0),1)
- 何番目の列を基準に並び替えるかを判定しています。チェックボックス直下のセル(Q3)に入力した選択肢(例：入社年月日)が、見出し一覧の範囲(セルQ6:U6)の内、左から何番目にあるかをMATCH 関数で探しています。もしエラー(空欄またはプルダウンの設定値以外の値を入力)が起きた場合は、強制的に1列目を基準とするよう指定しています。
- Q2
- セル2のチェックボックスの値を読み取っています。Googleスプレッドシートではチェックボックスが設置されたセルを読み込む際、✔を付けるとTRUE、外すとFALSEとして扱われますので、ここでTRUE(昇順_小さい順)かFALSE(降順_大きい順)が入ることにより、並び順をどちらか一方に切り替えています。
ブロック2：対象のデータを抽出する関数の部分
ここが条件に合うデータだけを絞り込む部分です。FILTER関数自体は、抽出対象の範囲とその条件を記述するというシンプルなルールなので、ここは簡単な解説で済ませます。
- FILTER(範囲, 条件)
- 指定したデータ範囲(ここではセル範囲B3:G22)から、下記ブロック3で指定する各種条件をすべて満たすレコードだけを抜き出します。元のデータは書き換えないので、プルダウンやチェックボックスの内容は好きなだけこねくりまわせます。
ブロック3：FILTER関数の条件を指定する部分(「レコードNo.」～「所属部署」の判定部分)
ここが今回の仕組みで最も理解が難しいであろう部分です。レコードNo.(B列)から所属部署(G列)まで、それぞれの列に対して「プルダウンやチェックボックスで選ばれた条件に合っているか」を1列ずつ判定しています。
IF～以降の部分は各行の仕組みが同じなので、1行目を例に分解して解説します。
- IF(Q5="", B3:B22<>"","...")で、空欄の場合を条件付ける
- もしレコードNo.のプルダウン(=セルQ5)が空ならば、B3:B22<> ""( = B列が空欄ではないデータ、つまり全てのレコードが該当)を返します。これにより、「プルダウンで何も選んでいない時はフィルターを無視して全レコードを表示する」という挙動をしてくれます。
- REGEXMATCH／SUBSTITUTEを使った、複数選択への対応
- プルダウンで複数選択された選択肢は、選択肢同士が「, 」(半角カンマ、半角空白)という2文字で接続され、「A, B, C,...」という値をもつセルとして扱われます。これらにSUBSTITUTE関数を適用しカンマ「, 」から縦棒「|」に置き換え、「A|B|C 」(A，BまたはC)という正規表現に形式を変換しています。 さらにその結果をREGEXMATCH関数で一致判定することで、プルダウンで選択した複数の選択肢の内、いずれかに一致するデータを厳密に検索します。
- TO_TEXT によるエラー回避
- REGEXMATCH関数で判定するセルは、読み込むデータ型がテキスト型である必要があります。今回挙げた例の中で、「レコードNo.」と「入社年月日」はテキスト型ではなく、それぞれ数値型／日付型なので、TO_TEXT関数でテキスト型に変換しています。
- 「*」(アスタリスク)を用いたAND条件同士の結合
- 各行の条件の間が「*」で接続されています。スプレッドシートでは、条件同士を「*」で繋げることで、「条件1と2と3、すべて満たすレコードだけを抽出する(=AND条件)」という意味になります。
こういった条件で抽出元のデータ範囲やセル値を読み込み関数に与えることで、今回の目的である既存機能を使わないレコード抽出の仕組みを実現しています。また、「IF～」の部分を増やしていけば、もっと列数が多いデータにも適用できるはずです！👍✨
04. 注意点：使いづらい点もあります
少し調べてみたのですが、Googleスプレッドシートの機能として、プルダウン選択肢の中身の✔を全てつける／外す機能は残念ながら存在しないようです。なので、✔を一つずつポチポチと入力しないといけないのが、このファイルの難点です。
上記章で解説した「役職」や「所属部署」といった、ある程度選択肢の数が絞られるような列は簡単に絞り込めるのですが、「苗字が佐藤の人」や「社員番号が〇番以前の人」、「入社が××～△△年度の人」というような、範囲／部分検索はできませんので注意です。
他にも、スプレッドシート自体の処理能力に限界がありますので、列数がもう数十行多かったり、抽出対象のデータが数千・数万行に及ぶような大規模なものである場合は、動作が途中で止まってしまう恐れがあります。
なお、この関数はGoogleスプレッドシートでの利用を前提に作成しています。Microsoft Excel等他の表計算ソフトでは使えない場合もありますし、バージョンによっては使えるかもしれません。他の表計算ソフトできちんと動くかどうかは試していないので、何卒ご容赦を...！！
(ちなみに、私はExcelのライセンス自体もってません。。。😅)
05. まとめ：結局は流用できるだけのスキルが大事
私が過去にぶち当たった壁だったので、みなさんにも似たようなお困りごとがあるかも？と思い本記事を作成してみましたが、いかがでしたでしょうか。表計算ソフトに馴染みのない方でも、プルダウン／チェックボックスは直観的に使い方を想像しやすいので、利用のハードルは低く実装できたかなと思っております。
とはいえ、職場のニーズや利用者のツール習熟度は千差万別。似たような課題でも、今回ご紹介したものでは役に立てられない場合もあると思います。そんなときのためにも、やはりファイルを作る人自身が学び、個別の課題毎に解決できる能力を身に着けることが結局は大事になってくるのかなと思います。
またそもそもの話、最近はGoogleスプレッドシート(Excel)以外でもAppsheetやLookerStudioなど、データ閲覧／抽出といった用途ならば他にも便利なアプリがあるので、表計算ソフト上での解決にこだわる必要もありません。各々の職場で使えるリソースが何なのかをまず調べ、ユーザーのリテラシーを把握し、その中で最も好ましい手段や組み合わせを検討することが重要です。それらをうまく見極められれば、あなたの業務はきっと一歩前に進むはずです。
今後も業務改善のヒントになるような記事を増やしていきたいと思いますので、参考になった方は是非このサイトをブックマークしてくださいね。それではみなさん、最後までご覧いただきありがとうございました！🙇🏆