GoogleフォームとGoogleスプレッドシートで在庫管理システムを作った話

私が勤めている会社で「製品在庫の管理シートを月ごとに集計できるものを作ってくれない？」

と言われたので在庫管理用のスプレッドシートを作りました。

月ごとの集計はタイムスタンプの日付をSUMIFS関数を使って集計しています。

管理する商品の数も多かったので「スマホとテプラのバーコード機能」を使ってシステム化しました。

作ったイメージはこのような感じです。

フォームとシートの作り方を解説していきたいと思います。

作ったシートは無料で公開しています。

Googleフォームの事前入力機能を使ったQRコード在庫管理もご紹介中です。

整理整頓で使用したオススメの商品も紹介しています。

利用に関する注意事項

商用・非商用に関係なく無料でご利用頂くことが出来ますが、再配布や販売は禁止とさせていただいています。

ダウンロード後のファイルはご自由にお使いくだいさい。改変されてもチーム内で共有されても大丈夫です。

ただし、再配布などの行為を見つけてしまうと私としても嫌な気持ちになります。心無い行為だけはどうかお辞めください。

すべてのファイルは私自身のMac･iPad･iPhoneで最新のOSのもとで使っています。常に動作確認済のものを配布していますが、万一ファイルのご利用時に発生した損害等の責任を負うことはできません。

ご利用はあくまでも自己責任で、何卒ご理解の程お願いいたします。

一般公開しているスプレッドシート・GASのダウンロード方法に関してはこちらの記事を御覧ください。

作った在庫管理シートはこちらからダウンロードしてください。

Google スプレッドシートの作成

まずは入出庫の数量を集計するスプレッドシートを作成します。

Googleドライブの左上「＋新規」から「Google スプレッドシート」を作成します。

スプレッドシートが開いたら「シート1」に商品名とバーコードの項目を作ります。

管理するデータを入力してください。

今回は文房具を入力します。

消耗品を管理する場合は製品にもともと付いているバーコードを登録してあげると便利です！

Google フォームの作成

次にGoogle フォームを２つ作成します。

「入出荷フォーム」と「棚卸フォーム」です。

Googleフォームは「＋新規」→「Googleフォーム」で作成可能です。

まずは入出荷フォームの作り方から紹介します。

入出庫フォームの作成

入出庫Google フォームの入力項目は以下のとおりです。

❏ 担当者（プルダウン）
❏ 分類（ラジオボタン）
❏ 商品バーコード（記述式）
❏ 数量（プルダウン）

作業者の手間を減らしたいので内容はとてもシンプルです。

どのように入力するしてもらうかを選ぶかは

作業する方が楽でミスをしないためにどうしたらよいか

という視点で考えると良いと思います。

私は考えた結果、数量も記載式ではなくプルダウンで選んでもらったほうがミスがないと判断したので記述式はやめました。

最後にフォームの回答先を「作成したスプレッドシート」に設定します。

「Googleフォーム」→「回答」→「右上の緑のボタン」をクリック

「既存のスプレッドシート」にチェックを入れて「選択」をクリックします。

先程作ったスプレッドシートを選択してください。

Googleフォームとスプレッドシートの連携がよくわからない方はこちらの記事で詳しく解説しています↓

「フォームの回答 1」という名前の紫のシートが出来上がります。

上記の名前だと「何のシートだ？」となるので

シートの名前も「入出荷フォーム」に変更しています。

棚卸フォーム

先程作った「入出庫フォーム」と同様の手順で「棚卸フォーム」を作成します。

棚卸フォームの入力項目は以下のとおりです。

❏ 担当者（プルダウン）
❏ 商品バーコード（記述式）
❏ 数量（プルダウン）※−5から+5までを選択

「入出荷フォームがあれば棚卸フォームって必要ないのでは？」と思いましたか？

結論から言うと必要になると思います。

みなさん使ってはくれるのですが、急いでいたりうっかりで入力漏れが発生しちゃうんですよね(^^;)

なので私がいる会社は商品をランク（重要度）で分けて

Aランクは月1回、Bランクは3ヶ月に1回、Cランクは半年に1回で棚卸を実施しています。

この時に使用しているのがこの「棚卸フォーム」となるわけです。

このフォームを使って商品在庫数とスプレッドシートに表示されている在庫数に差分があった場合、

その差分を入力してもらうようにしています。

スプレッドシートに在庫数を表示する

次にスプレッドシートの中身の関数を設定していきます。

完成形は以下のような形です。

商品名とバーコード項目

こちらは先程と一緒です。文房具とバーコードを入力します。

SUMIFS関数で「入荷数」「出荷数」「棚卸」を集計する

ここからはSUMIFS関数を使って該当する項目の合算を行っていきます。

SUMIFS関数の説明は以下の記事で詳しく解説しています↓

「入荷数」を集計する

スプレッドシートのC4に以下の関数を入れます。

=sumifs(‘入出荷フォーム’!$E:$E,‘入出荷フォーム’!$D:$D,$B4,‘入出荷フォーム’!$C:$C,”入荷”)

SUMIFSとはある条件を満たしたものだけを合算する関数です。

赤いマーカーが合計する数の範囲（入出荷フォームで入力された数量）

緑マーカーが合計する範囲の条件①（入出荷フォームで入力された「商品バーコード」が一致する）

黄色マーカーが合計する範囲の条件②（入出荷フォームで入力された「分類」で「入荷」と選択された）

言葉にすると

入出荷フォームの商品バーコードと入荷と分類されたものを合計します

「$」のマークは「ダラーマーク」といいます。

米通貨の「ドル」の記号です。

セルをコピペしたときに絶対参照とするか相対参照とするかを指定することが出来ます。

これがあると商品が追加されたときのコピペがラクなので設定しておいてください。

「出荷数」を集計する

続いて出荷数の合計を行います。

D4セルに以下の数式を入力してください。

=sumifs(‘入出荷フォーム’!$E:$E,‘入出荷フォーム’!$D:$D,$B4,‘入出荷フォーム’!$C:$C,”出荷”)

この数式は黄色のマーカー部分を変えただけです。

先程の入荷ではなく、分類が「出荷」になっている条件で合算します。

「棚卸」を集計する

次に棚卸の条件付き合計値を求めるSUMIFS関数を設定していきます。

=sumifs(‘棚卸フォーム’!$D:$D,‘棚卸フォーム’!$C:$C,$B4)

ここは条件が1つしかないのでSUMIF関数でも構いません。

シート「棚卸フォーム」のC列がB4の値（バーコード）と一致している場合に合算します。

こちらもダラーマーク「$」を忘れずにつけてください。

SUM関数を使って在庫数を表示する

在庫数はSUM関数を使って入荷数・出荷数・棚卸の数を合算した値となります。

条件付き書式設定で発注ラインを下回った項目が赤くなるようにする

条件付き書式を設定して、発注ラインを下回ったら在庫数が赤く表示されるようにしています。

スプレッドシートのメニューから「表示形式」→「条件付き書式」を選択

書式のルールは指定したセルの値より小さいと赤字になるように設定します。

今まで作ったものをコピペ

最後はコピペです。

これまで作って来たC4:G4をコピーして

C5:G8に貼り付けてください！

以上で在庫管理表は完成です。

月ごとの入荷・出荷推移を集計・グラフ化

ここからは何がいつ出ていったのかを分析できるように月ごとの推移とグラフ化をしています。

月ごとの表示

B12セルは今日の日付が表示されるようにします。

=TODAY()

N15セルの数式は

「=EOMONTH(B12,-1)+1」

EOMONT関数はその日付けの月末を求める関数です。これに+1をすると一日追加されて月初が表示サれるというわけです。

これによってN15セルに月初めの日付が入るようになります。

お隣の「M15」は「B12」の1ヶ月前にしたいので月の以下の式を入れます。

=EDATE(N15,-1)

edate関数は指定された月数だけ前、もしくは後ろの値を表示する関数です。

M15に入れたらC15からL15までコピペしてください。

日付の表示は「表示形式」「数字」「カスタム日時」を使って年と月のみ表示するようにしています。

入荷と出荷で集計結果を変える項目を設定

入荷と出荷で集計結果を変えたいのでB13にプルダウンで「入荷・出荷」を選択できるようにします。

月ごとの集計もSUMIFS関数を使用

月ごとの集計もSUMIFS関数を使って条件に合致したものを集計するようにしています。

C16に以下の数式を入れます。

=SUMIFS(‘入出荷フォーム’!$E:$E,‘入出荷フォーム’!$C:$C,$B$13,‘入出荷フォーム’!$D:$D,$B16,‘入出荷フォーム’!$A:$A,”>=”&C$15,‘入出荷フォーム’!$A:$A,”<=”&EOMONTH(C$15,0))

SUMIFS関数はの内容は以下のとおりです。

月末は月初で求めたEOMONTH関数を出して集計しています。

これで月ごとの商品入出荷が見えるようになりました。

グラフなんか作ってあげるとさらに見やすくなるのでおすすめです。

SUMIFS関数を使った指定期間内の集計方法はこちらの記事で詳しく解説しています。

入出力方法はスキャナー付きスマートフォンを使用する

上記のシートで直接商品名を選択するだけでも使用できるようになりました。

だだウチの会社は製品が多いので「該当商品はどれだ？」と探すのも一苦労…

なので入出荷はスキャナー付きスマートフォンを使ってバーコード入力するようにしました。

スキャナー付きスマホはユニテック EA520を使用しています。

こいつ個人的にはめっちゃ優秀だと思っています！

• スマホなのでトップ画面にGoogleフォームのショートカットを作れる
• スキャンした時に自動で半角英数字にしてくれる（←これ分かる人には地味に良い機能）
• スキャン後にTab→Enterキーを押してくれるサフィックス設定が出来る（ターミネーターとも言う）

ちょっとお金をかけて在庫管理をしてもいいのであればオススメします！

詳細はこちらの記事でどうぞ▼

テプラーを使ってバーコードを作成

---

バーコード作成はテプラーを活用しています。

イメージはこんな感じです。

Google フォームの「商品バーコード」項目にスキャナー付きスマホで読み込むと文字が反映されるというわけです。

使っているテプラーはキングジムのテプラPRO SR5900Pです。

みなさんテプラーってキレイに文字を印刷するだけの商品と思っていませんか？

それだけだと思っているのであれば人生の半分を損していますよ！！

専用ソフトを使えばバーコードだって印刷できちゃうんです！

たとえばMAC版の「SMA3」だとこんな感じでQRコードが作れます！

Windowsの方は「SPC10」というとっても優秀なソフトがあるんです！

同じ型のラベルをいっきに印刷してくれる流し込み機能がとてもすごいんですね。

会社ではWindows PCなのでとても重宝しています。

MACでも使えるようにしてほしい…泣

SPC10を使ったQRコードの作成方法はこちらの記事にまとめています。

おまけ｜ピボットテーブルを使って月推移の表を作成

タイムスタンプはピボットテーブルを使っても集計できます。

ピボットテーブルは簡単に表が作成できて非常に便利なのですが、日付が変わる度に集計する月を変化させるためにはGASを組む必要があります。

今回は参考程度の紹介でとどめます。

まとめ

今回はGoogle FormフォームとGoogleスプレッドシートで作った在庫管理システムについて紹介しました。

システム自体は基本的に無料で作ることが出来るので、お金をかけずに在庫管理したい方は参考にしてください。

お仕事のご依頼

Google Workspaceを使ったお仕事もお受けしています。

GoogleAppsScript（GAS）を使って自社の業務効率化用のプログラムを導入した実績があります。

本職はメカエンジニアですので、ゴリゴリのプログラマーよりも機器の連携･取り扱いに長けております。

相談は無料ですのでお気軽にご連絡ください。

スプレッドシートを学ぶためにオススメの本

在庫管理におすすめの本