Excelで簡単なソフトを作るStep2｜簡単な入出庫や在庫管理

今回は「Excelでバーコードリーダーを使用して、簡単な入出庫や在庫管理するサンプル」の作り方になります。

Excelのマクロ（VBA）も使用しますので、使ったことがない人も良かったらこの機会に是非チャレンジしてみてください。

前回の「Excelで簡単なソフトを作る【Step1】」をまだ見ていない方は、こちら。

https://www.e-tecinc.co.jp/excelstep1/

概要

以下の事をやりたいと思います。

• [入庫]シートと[出庫]シートでバーコードを読取り、履歴を記録。
• [データ]シートで、在庫状況が確認できる。

構成

• [入庫]シート

バーコードを読むと、データ文字列のほか、入庫日を記録します。

• [出庫]シート

バーコードを読むと、データ文字列のほか、出庫日を記録します。

• [データ]シート

在庫数などのリストを表示します。

手順

作成手順をまとめてみました。
Excelを使ったことがあれば誰でも作れるような内容になっていると思います。

１．[データ]シートの作成

• 「Sheet1」のシート名を「データ」に変えます。
• １行目にタイトルを入れます。
  （左から、バーコード、名称、入庫数、出庫数、在庫数）

• [列A]をクリックすると、列Aすべて選択状態になります。
• 右クリック-[セルの書式設定]を選択します。

• 表示形式を[文字列]に変更して、OKボタンでダイアログを閉じます。

• A2だけ選択状態にします。

• バーコードリーダーをパソコンに接続します。

今回、使用したバーコードはこちら。「SD500」

https://www.e-tecinc.co.jp/item/sd500

• バーコードを読込みます。
  すると、数値がセルに入力され、次の行にカーソル移動します。

• 同じようにバーコードを読み続けます。

• ２番目の列にバーコード名を手入力します。そのバーコードの商品名など好きな文字を入力します。

以上で、ひとまず[データ]シートの土台ができました。
まだこの後も変更がありますが、続きは、入庫・出庫シート作成後になります。

２．[入庫]シートの作成

入庫時、バーコードを読込んだら、読込んだ日時も記録できるように作成します。

• 「Sheet2」のシート名を「入庫」に変えます。
• １行目にタイトルを入れます。
  （左から、バーコード、入庫日）
• [列A]をクリックすると、列Aすべて選択状態なります。
• 右クリック-[セルの書式設定]を選択します。
• 表示形式を[文字列]に変更し、OKボタンでダイアログを閉じます。

• [開発]-[Visual Basic]を選択します。
  
  「開発」リボンが表示されていない場合は、下記の「メモ：Excel画面の[開発]リボンを表示する方法」を参照してください。

• [開発]-[Visual Basic]を選択すると、VBAの画面が新たに表示されます。
• 左側のリストの「Sheet2（入庫）」でダブルクリックすると、右側にSheet2ウィンドウが表示されます。
• Sheet2ウィンドウヘッダにあるリストボックスを選択します。選択内容は以下の通りです。

左側のリストボックス：Worksheet 右側のリストボックス：Change

→ Worksheet_Changeという関数が追加されます。
※Worksheet_SelectionChange()は、Worksheetを選択したときに作られてしまいますが、未使用なので、削除または無視して構わないです。

• Worksheet_Change関数内に、以下を入力します。

Private Sub Worksheet_Change(ByVal Target As Range)
    '１列目が編集された
    If Target.Column = 1 Then
        '２行目以降が編集された
        '（１行目はタイトル行）
        If Target.Row > 1 Then
            '２列目に現在日時を設定
            Target.Offset(0, 1) = Now
        End If
    End If
End Sub

• マクロを使用するため、名前を付けて保存します。

ファイルの種類：Excelマクロ有効ブック（.xlsm）
※.xlsm以外で保存すると、上記マクロ（VisualBasicで編集した内容）が保存されないので注意です。

• 保存できたら、Excel画面の「入庫」シートに戻ります。
• セルA2にカーソルを合わせて、さっそくバーコードを読込んでみましょう
  → 読込んだバーコードデータと、日時が表示されました。

３．[出庫]シートの作成

• 「Sheet3」のシート名を「出庫」に変えます。
• １行目にタイトルを入れます。
• （左から、バーコード、出庫日）

※入庫シートの作成と同じ項目を実施しますので、手順は省略します。
「入庫」/「Sheet2」を、「出庫」/「Sheet3」に置き換えて同じように作成してください。

• 「出庫」シートのセルA2にカーソルを合わせて、バーコードを読込んでみましょう
  →読込んだバーコードと、日時が表示されました。

４．[データ]シートの修正

入庫・出庫シートが完成しましたので、今度は[データ]シートの中で、在庫数などを表示するように対応します。

• 「データ」シートで作業します。
• 列３番目に入庫数が表示されるように対応します。
• 列３番目の先頭（C2）を選択します。
• 以下の数式を入力します。

=COUNTIF(入庫!A:A,データ!A2)

どんなことをしているか一言でいうと、「入庫シートにある同じバーコードの総数を表示する」という動きになります。

「入庫!A:A」とは「入庫」シート内の「A列すべて」を指します。

「データ!A2」とは「データ」シート内の「A2セルのみ」を指します。

COUNTIF()とは「入庫」シート内のA列すべての”個数”を、「データ」シートのA2に表示する関数になります。

• コピーして、最後のデータまで貼り付けします。貼り付けすると、Excelの仕様上、「データ!A2」から「データ!A3」「データ!A4」・・・と自動で行位置も更新されます。（これはこれで問題ありません）

• 列４番目に出庫数が表示されるように対応します。
• 列４番目の先頭（D2）を選択します。
• 以下の数式を入力します。

=COUNTIF(出庫!A:A,データ!A2)

どんなことをしているか一言でいうと、「出庫シートにある同じバーコードの総数を表示する」という動きになります。

• コピーして、最後のデータまで貼り付けします。

• 列５番目に在庫数を表示します。
• 列５番目の先頭（E2）を選択します。
• 以下の数式を入力します。

=C2-D2

どんなことをしているか一言でいうと、「入庫数－出庫数＝在庫数」という意味になります。

• コピーして、最後のデータまで貼り付けします。

以上で、入庫数と出庫数の差分の個数が表示されるようになります。

完成品「sample_step2.xlsm」

※マクロファイルはセキュリティ上、ダウンロードできませんので、「xlsx」ファイルとしております。
ダウンロード後、「xlsx」→「xlsm」に変更してご利用ください。

※もし、例えば会社内でセキュリティの都合でダウンロードが禁止されているようでしたら、上記手順通り実施して頂くことで、同じものが作成できます。

※本サンプルは勉強目的で作られたものですので、このサンプルを使用したことによる問題等に関しては、当社では責任を負いません。ご了承ください。

使い方

• ファイルを開いた後は、コンテンツの有効化（マクロの有効化）を押してご利用ください。

• 入庫したい場合は、「入庫」シートで１列目の一番最後にカーソルを合わせ、バーコードを読み取ります。

• 出庫したい場合は、「出庫」シートで１列目の一番最後にカーソルを合わせ、バーコードを読み取ります。

• 在庫数を確認したい場合は、「データ」シートで確認します。
• 新しいバーコードデータは、「データ」シートの１列目の最後に追加してください。

最後に

これで簡単な入出庫や在庫管理が行えるソフトが完成しました。

ですが、やはり簡単なものなので、あまり実用化には向いておりません。
ずっと使い続けていくと、データが膨大になり、Excelファイルの表示が遅くなりますし、ユーザの入力ミスはフォローされていない等、課題点は山積みです。

このExcelファイルを参考に改良を重ねるのもよいですが、もっと本格的に・大容量で快適に動作させたい場合は、Excelだけでは処理能力不足なところがありますので、例えばWindowsアプリケーションを作成して、情報はデータベースで管理していくのが良いと思います。

さらにもっとたくさんの、例えば離れた事務所の間でもデータ管理をしたい場合は、Webアプリケーションとして作成するのがよいと思います。

以上ご参考になればと思います。最後まで読んで頂き、誠にありがとうございました。