バーコードを利用した在庫管理
今回はFormeシリーズを使ってExcelで在庫管理システムを作成します。この在庫管理システムは原則としてワークシート関数のみを使用して実装します。 また、バーコードはISBNを利用したいと思います。
書籍在庫管理
ピックアップした書籍がマスターデータに登録されているかを照合し、登録されている書籍のみを数量、入出庫時間、保管区画を記録していきます。Excelでは記録されたデータを集計し、書籍ごとに設定された安全在庫数を下回った場合、警告を表示するシステムを作成します。 (マスターデータの元になる書籍データはすでにあるものとします。)
手順
大まかな手順としては
- Forme Pro(lite)からforme.zipをPCに共有する
- forme.zipを解凍し、FormeCollector.xlsmをマクロを有効化して開く
- FormeCollector.xlsmでForme Pro(lite)の設定データを作成
- 3をコピーし、FormeQRCoderでQRコード化する
- Forme Pro(lite)で4のQRコードを読み込み、フォームを作成
- マスターデータを登録後、ダミーのデータを入力し、PCへデータを送る
- FormeCollector.xlsmで6のデータを元にデータベースシートを作成
- データベースシートの集計シートを作成
- 完成
尚、Android端末とPCはBluetoothファイル交換でファイルの共有をしています。(ここでは説明しません。)USBケーブルでの共有(外部メモリ)も可能です。 マスターデータはすでに作成されているものとします。
手順は多いように見えますが、実際の作業時間は多くありません。
手順 1 から 3
メインメニュー > 設定シート共有 > forme.zip を選択し、PCへ送る。 受信したforme.zipを解凍後にマクロを有効化したFormeCollector.xlsm上のシートに以下のような表を作成する。 (表組みのルールはFormeTableDoc.pdfに記載されています。)
次にFormeCollector.xlsmのシート上に作成した表をコピーする。
手順 4 から 6
forme.zip内のformeQRcoder.htmlをクリックして、FormeQRCoderを起動。 FormeQRCoderに表を貼り付け、QRコードを作成する。
Androidアプリ FormeでQRコードを読み込む。フォームの内容が問題ないことを確認し、フォームを作成する。
マスターデータを登録後、マスターデータからFormeCollector.xlsmのデータベースシート設定の為のダミーデータを入力する。
入力したダミーデータをCSVデータとしてPCへ送る。この時OK:FORMECOLLECTOR.XLSMになっている事を確認する。
手順 7 から 9
設定用の表を作成したFormeCollector.xlsmのFormeタブ > CSVデータベースシート追加をクリックし、defaltをクリックする。 (ダミーデータの文字エンコードがShift_jisの場合)
受信したダミーデータを選択する。(次からダミーデータがあったフォルダからデータを取り込みます。)
次に取り込み済みのデータを保存するフォルダを選択します。
追加されたCSVデータベースシートを見やすいようにボタン等のオブジェクトの移動やテーブルの色や幅を調整し、ヘッダー行に項目を入力していきます。 テーブルの最後尾列であれば、列の追加ができます。
次にCSVデータベースシート上のテーブルオブジェクトの名前を変更します。数式タブ > 名前の管理 > 定義された名前をクリックします。ダイアログからテーブルオブジェクトの 1 名前を選択し、 2 編集をクリックします。
名前をLOG_DATAに変更します。名前の変更をする事で参照がしやすくなります。
次に新しくシートを作成後、名前を管理にします。テーブルオブジェクトを揷入し、以下の表のようにヘッダー行を入力します。
| No | ISBN | 書籍名 | 在庫 | 警告 | A区画 | B区画 | C区画 | 出庫数(1ヶ月) | 出庫数(3ヶ月) | 警告数 |
|---|---|---|---|---|---|---|---|---|---|---|
ISBNと書籍名はすでに元データがあり、そこから転記するものとします。警告数は任意の数値とします。 残りの列には以下の数式を入力します。
No =ROW() - 4
在庫 =SUMIF(LOG_DATA[ISBN],[@ISBN],LOG_DATA[数量(マイナスは出庫)])
警告 =IF([@在庫]<[@警告数],"警告","")
A区画 =SUMIFS(LOG_DATA[数量(マイナスは出庫)],LOG_DATA[ISBN],[@ISBN],LOG_DATA[区画],csv_table[[#見出し],[A区画]])
B区画 =SUMIFS(LOG_DATA[数量(マイナスは出庫)],LOG_DATA[ISBN],[@ISBN],LOG_DATA[区画],csv_table[[#見出し],[B区画]])
C区画 =SUMIFS(LOG_DATA[数量(マイナスは出庫)],LOG_DATA[ISBN],[@ISBN],LOG_DATA[区画],csv_table[[#見出し],[C区画]])
出庫数(1ヶ月) =SUMIFS(LOG_DATA[数量(マイナスは出庫)],LOG_DATA[ISBN],[@ISBN],LOG_DATA[数量(マイナスは出庫)],"<0",LOG_DATA[入出庫時間],">"&NOW()-30)
出庫数(3ヶ月) =SUMIFS(LOG_DATA[数量(マイナスは出庫)],LOG_DATA[ISBN],[@ISBN],LOG_DATA[数量(マイナスは出庫)],"<0",LOG_DATA[入出庫時間],">"&NOW()-90)
全体の状況を判定するためにA1のセルに以下を入力します。
=IF(COUNTIF(csv_table[在庫],"<=0")>0,"欠品があります",IF(COUNTIF(csv_table[警告],"=警告")>0,"補充が必要です","正常です"))
条件付き書式を設定して完成です。
運用
Androidアプリ Formeでデータ入力を行い、そのデータを指定した取り込み先フォルダに置いた上でFormeタブ > ファイルの監視をクリックすればデータの取り込みが行われます。
正常
警告-補充が必要
欠品-欠品あり
今回はISBNがある書籍で在庫管理を作成してみましたが、バーコードフォントを利用する事で独自のバーコードで管理をすることができます。Excel側もワークシート関数と条件付き書式で最低限の在庫管理システムが作成できました。
Formeを使えば、ハンディターミナルを利用した在庫管理システムを簡単に作成できます。是非使ってみてください。