バーコード活用編①でExcelでバーコードの事や出力方法を確認できたと思います。次にパーコードを活用していくために簡単な在庫管理表を作成していきます。
使用するEXCEL関数について
３つの表では 「SUM」,「VLOOKUP」,「IFERROR」の３つの関数を使用しています。
■ SUM関数
セル範囲に含まれる数値の合計値を求める関数
数値の合計値が表示されます。
■ VLOOKUP関数
指定した検索値を検索したい範囲の表を指定して縦方向にデータを検索して、一致した値と同じ行の値を表示します。
検索値「B３」の値を使って在庫表から自動的に「C３」に金額を表示したい時に「VLOOKUP」を使用します。
VLOOKUP関数の仕様に当てはめると「C３」に関数を入力して
検索値は「B３」、検索範囲は「B6:C9」、列番号は、２、検索方法は０を入力します。
・「検索値」
在庫金額表から検索したいキーになる品名です。今回、「B3」の値で「C3」に金額を自動的に表示したいので VOOKUP(“B3″,”検索範囲”,”列番号”,”検索方法”)
・「検索範囲」
「検索値」を使って検索するための表なので在庫金額表を範囲しています。
VOOKUP(“B3″,”B6:C6″,”列番号”,”検索方法”)
・「列番号」
「検索範囲」指定した表の何列目の値を表示するかになります。今回１列目品名、２列目、金額な ので２列目の2を表示します。
・「検索方法」
検索する値が完全に一致する場合は０、いちばん近い値は１を入力します。
■ IFERROR関数
関数や数式の結果がエラーの場合に指定した値を表示します。 数式がエラーの場合は空白にしたり０を表示したりできます。
合計金額を求めるために「価格」×「８月売上個数」を「合計金額」で求めています。
はさみの売り上げ個数が確認中のためセルE６がエラーになっています。
FERRORの仕様 IFERROR(値, エラーの場合の値)
エラーを表示させないために「値」にC4*D4 「エラーの場合の値」は今回は空欄を表示させたいので””を入力します。
在庫表を作ってみよう！
在庫表は現在の数量を把握するための表になります。B４のセルにバーコードをスキャンすると品名、在庫単価、在庫金額、数量が自動的に表示するように「VLOOKUP」関数を使います。
Cに入力する関数=IFERROR(VLOOKUP($B4,$B$7:$F$25,2,0),””)
同じようにD４,E4,F4に関数を入力しますが、表示する列番号が違いますので
在庫単価 「３」、在庫金額「４」、数量「５」に変更します。
今回の在庫表の範囲を $B$7:$F$25 にしていますが、製品がどんどん増えていく可能性がある場合は
多めに $B$7:$F$100 セル範囲を指定するといいでしょう。
入出庫表 を作ってみよう！
在庫管理の基本は在庫数を上書きしないこと必ず入庫と出庫の履歴を取ります。日付ごとの表を作成してSUM関数で入庫と出庫の数量を合計します。
パーコードの出力ページ
製品に貼ったり在庫を置いてある棚に貼るためのラベルの作成ページを作成します。
「D4」にバーコードの番号をスキャンすると品名「D5」 と金額「H5」が表示されるようにします。
品名を表示する関数は =VLOOKUP(D3,在庫表!B7:F25,2,0)
金額を表示する関数は=VLOOKUP(D3,在庫表!B7:F25,3,0)
二つとも検索値に「D3」バーコードの番号が入力されたら在庫表の範囲から 在庫表!B7:F25,から検索して品名は列番号２，金額は列番号３を指定して表示します。
バーコードは「開発タブ」→「挿入」→「コントロールの選択」をクリックします。
「Microsoft BarCode Control 16.0」を選択して表示します。
バーコードを右クリックして「プロパティ」を表示します。
「LinkedCell」にバーコード番号が書かれたセル「D３」を入力して完了です。
以上が簡易的なバーコード在庫管理表のつくり方になります。
改善効果・利用頻度
