お久しぶりです。ＴＨです。
久しぶりに社員技術ブログを書きます。
今回はタイトルに記載してありますが、QUERY関数の条件式の値をプルダウンで変更し表示させる方法について書きます。
なぜQUERY関数が出てきたかというと・・・、ツールを作った際に、私はグーグルのスプシにデータを追記してたのですが、そのままだと見にくいので整理するためにQUERY関数を使用しました。
QUERY関数とは
まず、QUERY関数とは何か？こちらから簡単に説明します。
QUERY関数は指定した範囲から特定の条件を指定してデータを出力できる関数です。文字だけではわかりにくいと思いますので実際に使用すると下図なります。
赤枠がデータで、青枠がQUERY関数で表示した部分になります。
F1のセルに「=QUERY(A1:C22,“select * where(A) = ‘Ｂチーム'”)」を入力するだけで赤枠内のデータから条件にあったものを抜粋し青枠のように表示してくれます。※見やすいようにセルの背景の色を変更したり、枠線を設定しています。
QUERYの使い方をネットで検索すれば出てくると思いますが、QUERY（データ範囲,クエリ,見出し(省略可)）となります。見出し部分には何行目が見出しかの数値を入れます。1列目が見出しなら「１」を入力。
今回の場合、下記になりますね。
| データ範囲 | A1:C22 |
| クエリ | “select * where(A) = ‘Ｂチーム'” |
| 見出し | 省略 |
ここで一つ、テクニックを紹介しておきますと、行を追加するなどでデータ範囲が変わる場合、毎回「A1:C22」を書き換えるのは面倒だと思います。
実はここは「A:C」と置き換えることができます。そうすると、A列～C列をみるようになり、行を追加した際でも「A1:C23」などに書き換える必要はなくなります。
QUERY関数についてはこれでマスターしましたね。
クエリ文を改良してみよう
次に、指定したチーム変更してQUERY関数で表示してみます。
書き換える引数は「”select * where(A) = ‘Ｂチーム'”」の部分です。現状だと、Bチームのみとなっているので、ここをAチームにします。
Aチームに切り替わりましたね。だけど、このQUERY関数の中身を毎回書き換えるのは面倒ですよね。
どこかのセルで指定したチームのみを表示させてみましょう。今回はE1にチーム名を入れてみましょう。
「”select * where(A) = ‘Ｂチーム'”」を「”select * where(A) = ‘E1′”」に変えればいいか？と思う人もいるかと思うのですが、”で囲っている部分は、文字として扱ってしまうので、このままではE１という文字を探しにいきます。
ここで使うのが文字を連結してくれる「&」です。使い方は「文字&文字」です。例えば「=A1&B1」とすればA1の中身とB1の中身を合体させた文字を表示してくれます。「=”Aさん”&B1」とすれば、AさんとB１の中身を結合します。
つまり、「”select * where(A) = ‘” & E1 & “‘”」とすることでE１で指定した文字を検索してくれることになります。クエリの書き方の問題上「’」を使うので「”」と一緒になってわかりにくいですのでご注意ください。
これでE1を切り替えると、QUERY関数で表示している箇所も自動で切り替えてくれます。
プルダウンの設定
最後にプルダウンの設定方法を説明します。
この状態だと、E１にAチームやCチームをキーボードを叩いて入力する必要があります。
手動だとタイピングのミスなどで正しい値が入らない場合もあるので、固定の文字から選択式にしたいですよね。
「データ」→「データ入力の規則」→「ルールの追加」でプルダウンを簡単に作成できます。
これで完成です。私の場合、引っ張ってくるデータとQUERY関数で表示したいシートを分けています。
データ部分はツールで自動で書き出されていくので、それを別のシートで条件を選択して表示するという感じです。
スプレッドシートの作成を終えて
C#で作成したツールのデータをスプシに書き込んでいるだけだったので、あとで見やすいように調べて作ってみましたが、スプシでここまでできるのか！というのが素直な感想です。まだまだ知らない関数もあるので、スプシでなにか作っているときは調べて使ってみたいと思います。ツール側で関数を埋め込んでいったり関数を編集するというのもいいなと思ったり思わなかったりします。
いがいといろんな関数を組み合わせればツールとして活用できると思いますが、複雑な処理をさせたいと思うときはやっぱりC#でツールを作るほうが私はいいかなと思います。
ほかにもいろんな使い方をしているのでまた機会があれば紹介したいと思います。