バーコード活用 簡単な在庫管理表をExcelで作成してみよう!

表画像

バーコード活用編①でExcelでバーコードの事や出力方法を確認できたと思います。次にパーコードを活用していくために簡単な在庫管理表を作成していきます。

使用するEXCEL関数について

3つの表では 「SUM」,「VLOOKUP」,「IFERROR」の3つの関数を使用しています。

■ SUM関数 

セル範囲に含まれる数値の合計値を求める関数

SUM関数の使い方

数値の合計値が表示されます。

画像5

■ VLOOKUP関数

指定した検索値を検索したい範囲の表を指定して縦方向にデータを検索して、一致した値と同じ行の値を表示します。

VLookup関数の使い方

検索値「B3」の値を使って在庫表から自動的に「C3」に金額を表示したい時に「VLOOKUP」を使用します。

VLOOKUP関数の仕様に当てはめると「C3」に関数を入力して

検索値は「B3」、検索範囲は「B6:C9」、列番号は、2、検索方法は0を入力します。

・「検索値」

在庫金額表から検索したいキーになる品名です。今回、「B3」の値で「C3」に金額を自動的に表示したいので VOOKUP(“B3″,”検索範囲”,”列番号”,”検索方法”)

・「検索範囲」

「検索値」を使って検索するための表なので在庫金額表を範囲しています。

VOOKUP(“B3″,”B6:C6″,”列番号”,”検索方法”)

「列番号」

「検索範囲」指定した表の何列目の値を表示するかになります。今回1列目品名、2列目、金額な ので2列目の2を表示します。

・「検索方法」

検索する値が完全に一致する場合は0、いちばん近い値は1を入力します。

■ IFERROR関数

関数や数式の結果がエラーの場合に指定した値を表示します。 数式がエラーの場合は空白にしたり0を表示したりできます。

合計金額を求めるために「価格」×「8月売上個数」を「合計金額」で求めています。

はさみの売り上げ個数が確認中のためセルE6がエラーになっています。

Excelの表

FERRORの仕様 IFERROR(値, エラーの場合の値)

エラーを表示させないために「値」にC4*D4 「エラーの場合の値」は今回は空欄を表示させたいので””を入力します。

Excel IFERROR関数

在庫表を作ってみよう!

バーコードリーダー在庫表

在庫表は現在の数量を把握するための表になります。B4のセルにバーコードをスキャンすると品名、在庫単価、在庫金額、数量が自動的に表示するように「VLOOKUP」関数を使います。

Cに入力する関数=IFERROR(VLOOKUP($B4,$B$7:$F$25,2,0),””)

同じようにD4,E4,F4に関数を入力しますが、表示する列番号が違いますので

在庫単価 「3」、在庫金額「4」、数量「5」に変更します。

IFERRORとVLOOKUP関数の使い方

今回の在庫表の範囲を $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,から検索して品名は列番号2,金額は列番号3を指定して表示します。

パーコード出力ページVlookup関数

バーコードは「開発タブ」→「挿入」→「コントロールの選択」をクリックします。

バーコードの挿入

「Microsoft BarCode Control 16.0」を選択して表示します。

Microsoft BarCode Control

バーコードを右クリックして「プロパティ」を表示します。

「LinkedCell」にバーコード番号が書かれたセル「D3」を入力して完了です。

バーコードプロパティの変更

以上が簡易的なバーコード在庫管理表のつくり方になります。

改善効果・利用頻度

改善

シェアしてくれるとうれしいです!
YU SAKURAI
YU SAKURAI
ABOUT この記事を書いた人
北海道在住の社内SE 日々の業務を通じてITの世界に貢献しています。新しいガジェットやサービスに対する情熱が止まらず、最新のテクノロジーを探求するのが趣味、仕事をより効率的に改善する方法を探し続け、その成果やアイデアをブログで共有しています。ITとテクノロジーの世界で新たな発見や学びを共に楽しんでいきたいです。