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

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

前回 kowaretaibe.hatenablog.com

やりたいこと

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

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

検索ソート機能

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

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

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

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

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

試しに商品名で絞り込むと次のようになります。

where C = 'ボールペン'

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

・contains

条件を含むかどうか。「ペン」とすると「ボールペン」「シャープペンシル」がヒットします。

正式名称は忘れたけどなんだっけ？という場合には=よりもcontainsの方がいいかもしれません。（containsなのでご注意下さい）

・starts with (ends with)

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

・matches

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

・like

%と_の2つのワイルドカードを駆使して探します。
%で0以上の文字と一致。_で任意の1文字と一致です。

お気づきの方もいると思いますが、%〇〇%で調べるとcontainsと同じ結果が得られます。同様に〇〇%(%〇〇)でstarts with (ends with)の代用になります。likeでは中間一致やもっと複雑な探し方もできるのでこれは覚えておきたいですね。

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

上でwhere ~で絞り込みが可能にはなりましたが、毎回数式を変更するのは流石に面倒なのでリストから選択できるようにします。

イメージはこんな感じです。
A1に入力していたqueryの数式をそのまま3つ下に移動して、上に「注文日」「商品名」「カテゴリ」の欄を作ってます。（見やすいように注文日とカテゴリは横のセルと繋げてます。） 実際のリストは作りたいセルで右クリックから「プルダウン」を選びます。
その後設定欄で条件の項目から「プルダウン（範囲内）」を選び、その下の欄の範囲に='シート1'!$A$2:$Aと入力します。ここでのAは欄が「注文日」の場合なので「商品名」の場合はC、「カテゴリ」の場合はD、を入れます。

スプレッドシートのプルダウンの便利なところなのですが、重複するリストは省いて作ってくれます。
最後に「詳細オプション」で「表示スタイル」を「矢印」に設定してあげると使いやすいです。（「チップ」だとちょっと見にくくなるので変更してます。）

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

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

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

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

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

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

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

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

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

これで一致条件を変更できます。

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

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

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

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

以下答えです。

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

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

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

セルと文字列の連結には関数もありますが、&と使うと簡単にできます。

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

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

最後に

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

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

はじめに

毎週発注書を作るたびに過去の発注書を1つずつ遡ってコピーしたり、確認していたので勢いで作ってみました。
最初はlibreofficeで作ろかと考えてましたが、データベースを使う際に追加でインストールするものがあったので諦めました。（会社の共有PCです）
Excelはそもそもないです。

ということで暫定的にスプレッドシートになりました！
スプレッドシートにはquery関数があったので結果的に色々と楽でした。

やりたいこと

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

サンプルデータ集め

いいサンプルないかなと思ってみましたが、中々見つからないので流行り？のChatGPTに出してもらいました。

発注先をアスクルにだけしたのでその項目はなかったり、プリンターインクはどの色とか詳細もいるだろとかありますが、とりあえずこれを使います。

質問流れは以下になります。

思った通りにデータが得られなかったので何回か質問の内容を変更して欲しいデータを出してもらいました。
データは何回かに分けて出すといいみたいです。（別にいい方法があれば教えてください）

品目毎の集計

集めたデータから発注頻度を調べる必要があるので、品目毎にソートする。
とりあえずシートの数式です。

=QUERY('シート1'!A1:H,"select C,D,E,max(A),datediff(max(A),min(A))/count(C)/7 where A is not null group by C,D,E label max(A) '前回注文日',datediff(max(A),min(A))/count(C)/7 '発注頻度' format datediff(max(A),min(A))/count(C)/7 '0.0週'")

query関数1つで出来るのは便利ですね。

中身を詳しく見ていくと、まずquery関数は、

QUERY(<データ範囲>,"<クエリ>")

の形になります。次にクエリで、

select C,D,E,max(A),datediff(max(A),min(A))/count(C)/7

の部分は、max(A)が選んだ列の最大値（この場合は一番新しい日付）、datediff(max(A),min(A))/count(C)/7で差分日数(最新日,開始日)/回数/週を出して期間内での要素の頻度を計算してます。

where A is not null

では空白行の除外をしてます。書き方はSQL構文が基本ですが、IF文の知識があれば比較的簡単にわかるかと思います。

group by C,D,E

ではselect文でmax(A)などを処理するために基準となる項目を列挙しています。グループ化した列のそれぞれの組み合わせに対して集計するので、例えばこれがD列のみであった場合、文房具・収納用品・パソコン用品・オフィス用品の4つにまで絞れます。がその場合、文房具の中の項目は細分化されないのでC列は表示ができなくなります。（E列も同様です）
難しい場合は関数以外の列はグループ化すると覚えておくと良いかもしれません。

label max(A) '前回注文日',datediff(max(A),min(A))/count(C)/7 '発注頻度' format datediff(max(A),min(A))/count(C)/7 '0.0週'

はラベル付けと書式設定をしてます。注意すべきはどちらも列 '設定値',・・・としているところです。
列と設定値の間は半角スペースで区切るのと設定値は'(シングルクォーテーション)で囲む必要があります。
これはクエリ自体が"(ダブルクォーテーション)で囲まれているためです。

"select C,D,E,max(A),・・・'0.0週'"

だいぶ省略しながらの説明だったかと思いますが、ひとまずここまでにしようと思います。
query関数はsort関数なんかと同じで配列関数であるため、書く量がグンと減らせるので本当にオススメです！
色々な方が解説しているので参考にしてみてください。 公式載せときます。

最後に（まだ終わってないけど）

はじめて書くので分からないことが多いです。
コレこうしたがいいよ！とかいうのがあったらガンガン突っ込んでください。

そもそも必要なものであれば使ってる別のツールやソフトがあるだろとか思いましたが、久しぶりに何かやったなぁ感があったので忘れないうちに書き留めました。