◎VBAを使わずにExcelで在庫管理表を作成する 〜 復刻版 〜
さて、今回は商品の在庫管理表をVBA(プログラム)を使わずに作成してみたいと思います。
どこまでご期待に沿えるか分かりませんが、このページが皆様に少しでもお役に立てば幸いかと存じます。
作者: kenggy
@ まず、どんなものを作るのか(機能概要)を考える
今回在庫管理表を作成するにあたり、私の考えた機能概要(どんなものを作りたいのか?)は、
◎ 入出庫表にデータを入力すると、在庫一覧表の現在庫数が自動的に更新される。
例えば、Aという商品が本日10個出庫されたとします。このことを入出庫表にデータ入力するだけで、在庫一覧表の現在庫数
が自動的に−10少ない数字に更新されるというものです。
↓
というのが、私のイメージです。
◎ その他にも、便利な機能を付加する。
(1) 入出庫表において、商品コードを入力すると商品番号が自動的に表示される。
(2) 入出庫表において、仕入先及び納入先はリスト形式で選択できるようにする。
(3) 在庫一覧表において、発注点を設け、それ以下になった時”発注要否欄”に赤字で「要」を入れる(自動的に)。
<< つまり、発注しなさいという警告を出す役割を果たすものとします >>
(4) 入出庫データを検索する機能を付加する。
では上記の機能概要を踏まえて、実際に在庫管理表を作ってみましょう。
A 画面設計をしてみる
まず、入出庫表から始めます。
項目として、C列から商品コード・商品番号・日付・仕入先・納入先・入庫数・出庫数・入出庫数・備考とあります。
ここでお気づきになった方も多くいらっしゃると思いますが、一番最初の画像では入出庫数の項目がないのに今回の
画像では項目が増えています。でも、よく見てください。
一番最初の画像ではJ列がありませんねぇ。つまり、非表示しています(列を表示しない)。
項目”入出庫数”では、入庫数をプラスの数字に、出庫数をマイナスの数字に置き換えています。
こうすることによって、各商品毎の集計をした時に正しい残数(現在庫数)を表示できるのです。本当は入出庫数の項目
さえあれば、入庫数・出庫数という項目は必要ないのですが、在庫の履歴テーブルとして考えた場合にはこちらの方が
わかり易いのです。また入庫数・出庫数という項目があるのに、”入出庫数”という項目を表示していると混乱を招く恐れ
がありますので、その列を非表示にしています。
では、どのようにして仕掛けを作ればいよのでしょうか!!
Excel関数を使えば簡単に作れます。
まず、入庫数に数字が入力された時には入出庫数はプラス表示に、出庫数に数字が入力された時には入出庫数は
マイナス表示になるようにします。
それは、入出庫数のセル(例えばJ8の場合)に次のように関数を入力します。
=IF(H8=””,-I8,H8)
この意味は、”もしセルH8が空白ならセルI8にマイナス付けた数字を表示する、それ以外(H8が空白でないの)なら
そのままH8を表示しなさい”というものです。後は、下に好きなだけドラックしてこの関数の入ったセルを増やします。
ちなみに、どちらも入力されていない時は入出庫数に”0”が表示されます。 エラーにはなりません。
さて肝心の”在庫一覧表の現在庫数が自動的に更新される”はどうするのかというと、次のようになります。
=DSUM(入出庫表!C7:K500,入出庫表!J7,L7:L8)
(注)上記画像では、C7:J500となっていますが、後で入出庫表に項目”備考”を付け加えたので、正式にはC7:K500となります。
◎DSUM関数について
DSUM関数は、特定のセル範囲内(みなしデータベース)の指定されたフィールド列を検索して、条件と合致するレコードの合計を
求める関数です。DSUM(みなしデータベース,検索フィールド,検索条件)というのが関数式です。
ここで注意して頂きたいこは、項目行(見出し行)も含めた範囲を指定するというこです。また、検索フィールドの指定
に至ってはのその項目名のセル番地を入力するだけです。
で、在庫一覧表でも少し仕掛けがあります。
L列〜O列に、次のような表を作ります。これは、商品コードで検索をするためです。通常商品コードで検索をするなら
C6とかC7のC列セルを指定するばいいのですが、この関数では”項目行(見出し行)も含めた範囲を指定する”とい
ことで別の列に検索条件用範囲を作りました。ここでひと工夫して、L列〜O列の商品コード名はC列とリンクしてある
ので(=C6のように)、C列が変更されても更新されます。
これで、入出庫表にデータを入力すると、在庫一覧表の現在庫数が自動的に更新されるという機能が出来上がりました。
後の機能については、
(1) 入出庫表において、商品コードを入力すると商品番号が自動的に表示される。
(2) 入出庫表において、仕入先及び納入先はリスト形式で選択できるようにする。
これは”VBAを使わずExcelで勤務表を作成”でお話した入力規則を使います。
↓
(3) 在庫一覧表において、発注点を設け、それ以下になった時”発注要否欄”に赤字で「要」を入れる(自動的に)。
セルのフォントを赤に設定してください。
(4) 入出庫データを検索する機能を付加する。
↓
↓
と、オートフィルタはいろいろ使い道があります。
これで説明は終わりです。ご理解して頂けたでしょうか?
最後に今までやりましたことをサンプルにしてあります。ご自由にダウンロードしてください。
それとサンプルに入っている入出庫表のデータは500行までしかありません。
今回は一切VBA(プログラム)を使用していません。なんとなく物足りなさがありますが、これでご容赦下さい。
(姉妹ブログ)
・クラウド(GoogleSpreadsheets)で売上管理表を作成する
・クラウド(GoogleSpreadsheets)で勤務計画表を作成する
・クラウド(GoogleSpreadsheets)で在庫管理表を作成する
・クラウド(GoogleSpreadsheets)で在庫管理表(2)を作成する