Googleスプレッドシートで発注管理シート作ってみた-2

query spreadsheet スプレッドシート 正規表現

今回は前回に続いてGoogleスプレッドシートで発注管理シートを作っていこうと思います。

前回 kowaretaibe.hatenablog.com

やりたいこと

  • 発注履歴の検索ソート機能
    品目や発注先、カテゴリ毎等。
  • 発注頻度の集計
    今週いるもん無いっけ？と周りに聞くとは大体発注した後にアレがいるとか言われる。発注が近くなると自動的に候補をリストアップされてると助かる。
  • ドロップダウンリストで選択式
    書式設定とか単純な入力ミスを避けるため入力は最小限にしたい。
  • 発注書のフォーマットに自動記載
    発注品を探したり選ぶだけでメールで送るのに必要な発注書が出来てる！が理想。

ということで、まだやりたい事の1つ目も終わってないのでそこを終わらせていきます！

検索ソート機能

前回はQuery関数の大体の説明（本当にザッとした説明ですみませんm( )m）をしたので今回は欲しい機能の検索ソートをできるようにしていきます。

まずは全体をクエリしていきましょう！
全体クエリ.png

=QUERY('シート1'!A1:H,"select *")

selectには*を使ってますが、これは列全体を示すワイルドカード的な働きをします。場合によって使い分けるといいと思います。

次に検索をして必要なデータのみに絞り込む式を書いていきますが、これが前回したwhere ~の部分にあたります。

試しに商品名で絞り込むと次のようになります。
ソート_1.png

where C = 'ボールペン'

の部分は文字列を''(シングルクォーテーション)で囲むのがポイントですね。
基本的にはIF文と思って貰えば簡単に書けますが、以下に特殊な条件も挙げておきます。

・contains

条件を含むかどうか。「ペン」とすると「ボールペン」「シャープペンシル」がヒットします。
ソート_2.png
正式名称は忘れたけどなんだっけ？という場合には=よりもcontainsの方がいいかもしれません。（containsなのでご注意下さい）

・starts with (ends with)

接頭辞（接尾辞）が一致するかを探します。ends withで先ほどの「ペン」を探すと「ボールペン」のみヒットします。
ソート_3.png

・matches

正規表現で一致するかどうか調べます。後から使う場面が出てくるので、ここでは説明を後回しにします。m( )m
代わりに参考になるページを紹介します。 developer.mozilla.org

・like

%と_の2つのワイルドカードを駆使して探します。
%で0以上の文字と一致。_で任意の1文字と一致です。
ソート_4.png
お気づきの方もいると思いますが、%〇〇%で調べるとcontainsと同じ結果が得られます。同様に〇〇%(%〇〇)でstarts with (ends with)の代用になります。likeでは中間一致やもっと複雑な探し方もできるのでこれは覚えておきたいですね。

ドロップダウンリストを作る

上でwhere ~で絞り込みが可能にはなりましたが、毎回数式を変更するのは流石に面倒なのでリストから選択できるようにします。
リスト_1.png
イメージはこんな感じです。
A1に入力していたqueryの数式をそのまま3つ下に移動して、上に「注文日」「商品名」「カテゴリ」の欄を作ってます。（見やすいように注文日とカテゴリは横のセルと繋げてます。） 実際のリストは作りたいセルで右クリックから「プルダウン」を選びます。
その後設定欄で条件の項目から「プルダウン（範囲内）」を選び、その下の欄の範囲に='シート1'!$A$2:$Aと入力します。ここでのAは欄が「注文日」の場合なので「商品名」の場合はC、「カテゴリ」の場合はD、を入れます。
リスト_2.png
スプレッドシートのプルダウンの便利なところなのですが、重複するリストは省いて作ってくれます。
最後に「詳細オプション」で「表示スタイル」を「矢印」に設定してあげると使いやすいです。（「チップ」だとちょっと見にくくなるので変更してます。）
リスト_3.png

検索ソート機能（本当に）

色々と説明が長引きましたが、いよいよ検索ソートをしていきます。

検索ワードの部分をリストのセルにする

現状like '%ペン%'となっている部分のペンを"&C2&"に変えます。 置換後はlike '"&C2&"'になります。色々一気に増えてややこしいですが後から説明します。 挙動は以下、
vソート_1.gif

「ペン」をC2に変更することにより、実際には%プリンターカートリッジ%が評価されていることがわかります。
そしてC2を空欄にすることで%%になるので、中身は「何でもいい」ということで元のデータが全て表示されています。このようにlikeによるソートは使い方により、「完全一致」「部分一致」それ以外にも様々なカスタムフィルタができるので覚えておいて損はないです！

一致パターンを変えてみる-その1

ということで早速「完全一致」と「部分一致」を切り替えできるようにしてみましょう♪
横の空いたスペースに「一致パターン」を追加しています。
ソート_5.png

次に先ほど変更した"%&C2&%"をC2のみもう一度「ペン」に戻して、IF関数で置き換えます。
先に答えを言いますが、

"&IF(G2="完全一致","ペン","%ペン%")&"

これで一致条件を変更できます。
一致パターン.gif

一致パターンを変えてみる-その2

ついでに前方一致や後方一致も追加しちゃいましょう♪ 条件分岐がいくつかある場合はSWITCH関数を使うと綺麗に書けます。 書き方は

SWITCH("対象","ケース1","値1"[,"ケース2","値2"・・・])

と分岐をいくつも増やすことができます。

以下答えです。

"&SWITCH(G2,"完全一致","ペン","部分一致","%ペン%","前方一致","ペン%","後方一致","%ペン")&"

「ペン」と書いているのも別のワードで調べたい時に面倒なので、この際別のセル（H2）を指定しちゃいます。

"&SWITCH(G2,"完全一致",H2,"部分一致","%"&H2&"%","前方一致",H2&"%","後方一致","%"&H2)&"

セルと文字列の連結には関数もありますが、&と使うと簡単にできます。
一致パターン_2.gif

これで一致パターン別のソートができるようになりましたが、基本的に商品・日付・カテゴリーはリストからの完全一致で調べるので必要ないです笑

代わりに「備考」で検索して〇〇のケースはいつだっけ？となる時などに調べられるといいですね。

最後に

また今回も書きたいところまで終わりませんでしたが、とりあえずここまでにします。次回は検索ソート機能を完全に終わらせて次に進みたいと思います！！！