Google Spreadsheetだけで作る資材管理システム(数式、スプレッドシート編)

## まえがき
まず作るのに至った経緯などは概要編を参照いただくこととして

https://qiita.com/Kohei_Fujita/private/3279e29926a1d831f020

本編では作ったGoogle Spreadsheetでの数式やマスターデータと日々の管理シート
との関連などを示して行くために作成しました。
## マスターデータ
### シートの関連と使われる数式
#### シートの関連
こちらはすでに概要編にも記載していますが
想定シナリオは、以下のように**棚卸しで一旦シートごと切り替え**を考えました。  
**（ただこれは使いづらさが伴い、本案が企業側にとって魅力的に見えなかったと思われます）**
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/6ea8346c-cb6c-66ef-e859-a97ac4cb5a4e.png">
#### シートに使われる数式
**瓶在庫数算定**
まず上から出庫、入庫、棚卸しの履歴を記載しているシートから **SUMIF** で
現状の在庫量を算定する（資材Aの在庫=資材Aの(棚卸し総数+入庫総数)‐出庫総数)

<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/1b10bd05-17fb-dea2-07cd-6964928450e2.png">

以下のように
**棚卸し、入庫の商品名に合致する数値**を足し合わせ **(SUMIF)**
**出庫の商品名に合致する数値**を足し合わたものを **(SUMIF)**
**引いて(ー(マイナス))**、在庫数とするものです。
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/56e77bfb-88ce-8d05-a893-b776a1ee74d2.png">

**マスターデータ連携**
以下のように各マスター作成、必要な部分はマスター同士連携させることで
マスターの部分が変わっても別のマスターのテーブルを修正しなくてもマスターだけ
変えるだけで対応できるようにしている。

①**商品の大分類**(純米大吟醸酒、純米酒、吟醸酒、本醸造酒など日本酒の造りの違い）
②**商品一覧**(①大分類の情報を **VLOOKUP** で連携)
③**瓶（資材）マスター**

最終的なマスターとして
**商品‐資材一覧表**(②、③を **VLOOKUP** で連携)を用意する。
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/1fea0cb1-0071-1586-9586-c0f4697072e6.png">


**大分類**（＝大分類コード）を入力すると大分類マスターを **VLOOKUP** で参照し
日本酒の大分類名称、扱い（高級酒、輸出）などを表記する。
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/8fb8e5ed-ad76-2383-5544-ef33ea10860c.png">

**識別用コード** (=商品コード+商品サブコード(商品を細かく規定したもの)）を入力すると
商品一覧表を **VLOOKUP** で参照し
商品名や生酒、ひやおろしなどの火入れ条件などのサブ品目、容量などが表示される。
<img width="800" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/2da9aa29-9b71-9d03-ee19-5cfcd9902516.png">

またこちらの商品‐資材一覧で、使用する瓶コードを指定することで
**瓶マスター**　を **VLOOKUP** で参照し瓶の種類、瓶色などが表示される

また同時に商品の火入れや輸送などの関係で
**新瓶のみを使う**(万が一の破損を防止するため(もちろん回収瓶(リサイクル瓶)も十分な検査を行っているが念のため、瓶詰めした状態で火入れするような商品は新瓶を使うなど)
**新瓶/回収瓶をいずれでも良い**
**回収瓶優先**(輸送も輸出など長期間でなく、瓶も回収しやすい地域で販売する商品など)
といった指標を決め
さらに瓶自体がリサイクルを行っているものいないものがあるので、最終的に新瓶、回収瓶の利用方針を決める。
<img width="800" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/7998f528-c979-b482-2ffa-af4e732a96df.png">

また同じ商品名だが容量が違うなどを区別するため　**&**　（文字列結合）を用いて
識別用名称を作成する。
<img width="800" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/4ab4ade6-04eb-dcce-464d-e9ca247c844d.png">

**まとめシートでの連携**
日々の生産商品および生産数を入力すれば使用する資材（瓶）の種類と数が分かるように
するため、ここまでに作った瓶の在庫数、商品ー資材の連携、基本 **VLOOKUP** で
連携している（一部は **IF**, **IFERROR**, **COUNTIF**, **IFS** を用いて対応）
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/fe7e0433-0d82-3aed-8a6f-75244936cfd9.png">


ただし商品の大分類コラムでプルダウンから選ぶことで、次に商品コラムで選択できる商品
を絞り込めるようにするため、**データの入力規則**を**カテゴリー**(=日本酒の大分類)を**大分類**のマスターから選べるようにする。

<img width="300" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/23683d94-cb67-7f74-4323-cc3178cd895a.png">

<img width="400" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/ea58573b-4783-9b40-1bb5-43f4ebfe22cd.png">

これによるカテゴリー欄でプルダウンで選択することが出来る。
<img width="400" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/b69f9d23-9c8c-2f52-2aeb-63ee22337dd1.png">

また選んだ商品の大分類に従った商品をプルダウンで選べるように少し工夫を加えている  
別途商品大分類と商品の表を作っておいてこれを元データとする。(連動1)
<img width="700" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/7ee2b4dc-0066-e03b-6f5f-b20a59223f6f.png">

別にシートを用意し、カテゴリーは**まとめ**シートの選択項目を参照するようにする
<img width="500" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/a0574f0d-37a8-2b13-1aee-fd7f4d3ec4a8.png">

またそれ以降の列がカテゴリーで選んだ日本酒分類に従った商品を
**連動1**から**VLOOKUP**で引っ張ってくる(連動２）
<img width="500" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/32d5ba1d-60a3-c104-9de9-3565ca574550.png">

そうしておいてから
**データの入力規則**を**連動2**のシートの相当する行から選ぶようにする。
（例えばまとめ２行目=連動２行目）
<img width="300" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/79920011-2c93-c7dd-aa58-dbf48d115e2c.png">

こうすることでカテゴリーを決めれば、自動でそのカテゴリーに従った商品が絞り込まれて表示される。
<img width="500" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/eaad1ae2-88ae-9e47-7eef-ada0a6ddbd30.png">

以下のサイトなどを参考にしましたが

https://tori33.com/linked-pulldown/

あとから考えますと以下の参考サイトのように **VLOOKUP** より **XLOOKUP** を使った方が楽だったりしますし

https://dekiru.net/article/23398/

Arrayformula関数とVlookup関数を使う方法があることを知りました。

https://gosuke-blog.com/other-sheet-pull-down-list/

（商品‐資材一覧表との連携）
更に選んだ商品から、使用する瓶を**商品‐資材一覧表**から**VLOOKUP**で
商品が使用する瓶などの情報を持ってくる。
もしデータの不備などで、商品名に合致するものが無いケースを処理するために
**IFERROR**で対応する。
<img width="750" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/8d7566ab-8b08-0a88-53a2-feacd77b394d.png">

（瓶在庫との連携）
あわせて選んだ商品から、使用する瓶を**瓶在庫**から**VLOOKUP**で
商品が使用する瓶の在庫数を持ってくる。
<img width="750" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/96e9e167-2e8f-d09e-892f-91d3ae2e27b4.png">

また使用瓶の種類判定では再利用の観点から、リターナル瓶をできれば優先して使いたいので
商品的にリターナル瓶（回収瓶）が商品ー資材一覧の商品カテゴリーや運輸などを考慮した
使用瓶指定はあるが（新瓶のみ、新瓶優先、新瓶/回収瓶、回収瓶優先）
これに回収瓶という言葉が含まれれば（新瓶/回収瓶、回収瓶優先のいずれか）
回収瓶をまず表示する、そうでなければ新瓶を表示
<img width="750" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/157b000e-d4b9-937e-f434-a3ccb35ce456.png">

ただ新瓶。回収瓶（リターナル瓶）両方使えるケースであれば、ケースによって、
新瓶・回収瓶を判断しないといけないので、**IFS** を用いて複数の条件を判断する。
<img width="750" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/f5d6cf40-94e0-457d-757c-4e623da9e022.png">

**使用資材数のまとめ**
まとめシートの情報のうち、出庫に関わる情報を別シートに出庫シートと同じ形式で引用し
出庫シートへコピーし、出庫情報に出来るようにした。
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/31687161-2fcd-5254-57a6-421a6ed10ef4.png">

まとめシートの各セルを指定し、同じ値を引用できるようにしている。
<img width="750" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/6544e273-df5e-6de5-0616-eb76e3f7426c.png">




**情報共有用のシート**
まとめシートの情報は製造する商品と使用する資材が明記されるため
瓶へ日本酒を詰める作業をする作業員の方の参考となるため共有したいが
うっかりシートを触って数値や情報を壊してしまう可能性がありうるそのため
**IMPORTRANGE**関数を用いて**URL設定**シートに記載のまとめシートのURLを呼ぶことで
まとめシートに記載されている情報を別のスプレッドシート上に表記し、なおかつ編集することができない

<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/03e1619a-cc3f-9290-a4ac-9f0ea97e3406.png">

**URL指定シート**
シートの黄色の部分に資材管理のSpreadsheetのURLをコピーする。
<img width="750" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/3ac58d5d-480c-e3bc-2c14-9e5e85526f59.png">

**日次作業リスト**
日次のお酒の瓶詰め作業の商品や数量などを示すために
部分部分のデータを **IMPORTARNGE**　関数でデータを移してくる
ただし以下のSpreadsheetの緑のセルに関数をはめ込んでいるだけで
指定したコピー対象の列が表示される。
またデータは直接関数を触らない限りにおいては編集や破損も出来ない
<img width="750" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/4802b752-8f18-0e78-f764-85a358fed1f7.png">

URLを指定した直後は**アクセスを許可**することでリンク先の情報が表示される。
<img width="400" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552279/288dbd80-5f9f-7995-903c-956e7f119d24.png">

## 最後に
概要編でも書かせていただきましたが
こうして数式編を作ってみたのですが、
やはり使いにくい資材管理システムにしてしまったと反省です。
GAS(Google App Script)などを毛嫌いせず使えば、その辺りの解消は出来ると思うのですが
企業様でのメンテナンスや拡張を考えると、SpreadsheetやGASの知識が社内に十分にないと
難しいと言わざると得ません。

このあたりは
業務効率化と社内のITリテラシーを双方上げていくのがよいのか？
まずは効率化を行って、そこからITリテラシーを上げるのか？

今回は双方の中間的な部分を狙ったのですが
長期に企業様に関われると前者のように腰を据えて教育も含め、
伴走し効率化も出来るのかもしれませんが

まずDXクエストの企業協働のように短期に期間が切られている場合、
わかりやすく期限内にデジタル化の効果や将来性を感じていただけなかった場合、
その後、継続的に社内で教育やデジタル化の推進を進めてもらえないデメリットがあります。

そのため、後者のように効率化は狙うが、数式の教育資料をつくり
継続的に使いながら学んでいただくことを考えたのではありますが、
使いやすさ、わかりやすさを欠き、中途半端な出来になってしまったと言えます。

後出しのアイデアであるが、例えばデータの蓄積や出力などを配慮したSaaSなどの商品を
お試しで使ってもらうなどの方法に合わせて、データの可視化や分析をセットにした方が
まず省力化の効果が見え、さらに将来に向けてのお話が出来たかもしれません。

SaaSで月額制のような商品であれば、気に入らなければ契約を止めることで
コストも最小化でき、そういった点での選択のサポートに入り、
ソフト選択や選択時の考え方を共に考えていくことが良かったのかもと思っています。
例えば
**・コスト**だけではなく
**・将来性**
**・メンテナンス性**
　　将来のバージョンアップのコスト、スクラッチやパッケージソフトなどでも　　
  　　改造が多いとここのコストが増大したり、特定のベンダーさんに依存する形に
    　　なってしまう。
さらには
**・使いこなすための同じソフトの利用者の多さ**　　
　　多くの人が使うソフトは事例も多く、種々の使い方がQiitaのようなもので  
　　書かれていたりしますが、そうでないマイナーなアプリの場合はベンダー以外の情報が
　　ほとんどないケースも多く、使いこなすのに負担が大きい、
　　特に将来、連携や拡張などを考えている場合は特に重要かと
**・溜まったデータの取得の安易さ**
　　APIなどで自動でかつ低コストで取得できるのか？
　　それともそういった機能がなく手動での取得になるのか？
　　は将来の可視化やデータ分析などの妨げやボトルネックになる。

がポイントかと思っています。

## 謝辞
最後に概要編でも書かせてもらっていますが改めてこのような投稿を許諾いただいた
協働先の黒澤酒造様には感謝いたします。
このような中途半端な提案にしか出来なかった点はひとえに私の力不足ですが
最後にでも反省で書かせていただいたようなポイントなど
今後も、長野らしいお酒を醸したいという思い、継続、さらには発展ができますよう
ささやかではございますが本報告が一助になればと思っております。

また協働でご一緒させていただいたチームメンバーの方々にも
改めて振り返っても中途半端な提案しか出来ず、恥ずかしい限りですが
反省点など近くで一緒に作業されたメンバーの方であれば自社や今後の活動に
使っていただけると思っております。