こんにちは。EXCYテックブログの萱場です。
今回は、スプレッドシートのQUERY関数に関して、紹介します。
皆様の会社にもエクセルやスプレッドシートで表を作り、そこにどんどん内容を追記していくようなファイルがあると思います。
スプレッドシートでは、その溜まったデータを簡易的なデータベースとして使用できるような関数が用意されており、溜まったデータから特定の条件で、対象条件を絞り込む検索いフォームを作成することができます。
今回は、顧客様からの問い合わせ内容を入力したシートから、過去の問い合わせを検索するフォームを作ってみたいと思います。
完成イメージ
動画で見たほうがわかりやすいかなと思うので動画を用意しました。
問い合わせの内容を記入するシートと検索フォームがあります。
それでは、早速やっていきます。
データの作成
データの作成と言っても、下記画像の用に必要な項目名とレコード(ここでは、データ一行のことをレコードと呼びます)を追加するだけです。
サンプルはこんな感じで用意しました。
検索フォームの作成手順
以下の様な手順で進めていきます。画像にも下の番号に対応した数字が振ってあります。
- 入力するフォームを準備
- クエリの準備
- クエリ関数の実装
1. 入力するフォームを準備
形はどんな形でも良いのですが、抽出したい条件が記入できるようにします。(例えば顧客様名や担当者など)
2クエリの準備
上の画像だと、この②のエリアは何も無いように見えますが、書式を変更してわかりやすくするとこの様になっています。↓
D5からD9までで入力されたものをI列の方で、QUERY関数で使える条件の書式に変換して、I10では、そのすべての条件を、D3かE3でチェックがついている方の文字列で、結合しています。
どの様な内容になっているか見てみましょう。
まずは、I3です。
=IF($D$3,"OR",IF($E$3,"AND",""))IF関数の書式は、以下のとおりです。
IF(論理式, TRUE値, FALSE値)ちなみにD3、E3は、チェックボックスになっていますが、チェックボックスは、チェックが付いているときはTRUE、チェックがついてないときは、FALSEとなります。
またIF関数でセルのみ指定した場合、そのセルがTRUEかどうかの条件式になります。
ですので、上の数式は、D3がTRUEの場合ORと出力し、D3がFALSEの場合は、E3がTRUEの場合は、ANDを出力して、E3がFALSEだった場合は、空文字を出力する、といった内容になっています。
それ以外のI5:I9までの数式は、基本的に一緒の書式になっています。
条件文に関しては、ちょっと割愛させていただきますが、数式の解説だけさせていただきます。
C,B,D,Fというのは、どの列かと言うのを表しています。この列名は元のデータシートの列名です。
$D$5<>””という条件は、【D5が空白では無い】という意味になります。($マークをつけて絶対参照にする必要も無い気がしますが、念の為)
=だと、完全一致、containsだと部分一致です。
// C列にD5の値が含まれている。
I5 = if($D$5<>"","C contains '"&$D$5&"'","")
// B列の値はD6と等しい。
I6 = if($D$6<>"","B = '"&$D$6&"'","")
// D列の値はD7と等しい。
I7 = if($D$7<>"","D = '"&$D$7&"'","")
// E列にD8の値が含まれている。
I8 = if($D$8<>"","E contains '"&$D$8&"'","")
// F列にD9の値が含まれている。
I9 = if($D$9<>"","F contains '"&$D$9&"'","")最後にI10は上の条件を最初に選択した、ORかANDですべてを結合しています。
ORは【または】、ANDは【かつ】です。
I10 =TEXTJOIN(" "&$I$3&" ",TRUE,$I$5:$I$9)TEXTJOINは、文字を任意の文字を挿入して結合してくれます。
書式
TEXTJOIN(区切り文字, 空のセルを無視, テキスト1, [テキスト2, ...])つまり、空白のセルは無視して、【 AND 】で、I5:I9を結合するという内容です。
そうすると、I10には次のような値が出力されます。
C contains 'A' AND B = '中村' AND D = 'メール' AND E contains '在庫' AND F contains '001'このI10の値を最後のクエリ関数ので使用します。
3クエリ関数の実装
最後に、クエリ関数です。
以下公式ドキュメントのリンクです。
https://support.google.com/docs/answer/3093343?hl=ja
クエリ関数は、以下の様な書式で使います。
書式
QUERY(データ, クエリ, [見出し])B13にこのクエリ関数を使用した、数式を入力します。
B13 = IFERROR(QUERY('データ'!A:G,"select * where "&I10&" ",1))IFERROR関数は、その中で使用している数式がエラーを出力したときに、代わりの処理を設定できる数式です。
IFERROR(値, [エラー値])エラー値は、デフォルトでは、空白なので、空白で良い場合は特に書く必要は有りません。
QUERY関数に戻って、ここで使っている関数を確認します。
第一引数のデータには、データシートのA列からG列を指定しています。
クエリの【select *】の部分は、出力する列名をアルファベットで書きます。*アスタリスクは、全ての列を出力します。
(データ自体が複数になると、アルファベットではなくCol1 Col2というような書き方に変わるので注意)
クエリの【where 〜】の部分で2で作った条件文を参照して、その条件に合うレコードを抽出します。
最後の見出しは、何行目までを見出しとして使うかというものです。デフォルトは1です。ここでは、明示的に1と記載しています。
これで、最初の動画の様な検索フォームができました。
最後に
最後までお読みいただきありがとうございました。
結構割愛した部分も多いですが、割愛した部分は、直感的に理解できそうなところや調べれば、すぐ出てくるところです。
今回は、チュートリアルとして、実際にクエリ関数をどんな時に使用するかに重きを置きブログを書いてみました。
検索結果の列を指定して、SUM関数などを使用すれば、表で管理している勤怠簿から任意のスタッフを抽出し、勤怠時間の計算や給与計算にも活かせるかもしれません。
株式会社デザインXでは、アパレル業界のお客様に向けて、ECサイトや業務基幹システム(ERP)の開発・導入支援を行っております。ご興味をお持ちの方は、是非下記リンクよりお気軽にお問い合わせください。
また、株式会社デザインXでは、ソフトウェアエンジニア・社内SEなど、共に働く仲間を募集しています。テクノロジーでアパレル業界のBtoBビジネスを変えたいという熱意をお持ちの方、お待ちしております。ご興味のある方は、以下のリンクからお問い合わせください！
