はじめに
毎週発注書を作るたびに過去の発注書を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関数なんかと同じで配列関数であるため、書く量がグンと減らせるので本当にオススメです！
色々な方が解説しているので参考にしてみてください。
公式載せときます。
最後に（まだ終わってないけど）
はじめて書くので分からないことが多いです。
コレこうしたがいいよ！とかいうのがあったらガンガン突っ込んでください。
そもそも必要なものであれば使ってる別のツールやソフトがあるだろとか思いましたが、久しぶりに何かやったなぁ感があったので忘れないうちに書き留めました。
