考え方としては、在庫ですから、商品の「仕入」により在庫が増えて、販売により在庫が減る。これが大きな流れです。
そして管理するわけですから、毎日の在庫の動きを入力していく必要があるわけです。
入力する項目をこのように設定しました。
注目点
①「倉庫ID」～倉庫が２つ以上あることを想定しています。
②処理区分～「仕入」「販売」のほか、倉庫間の「転送」「転受」さらに「値引き」や初年度の「繰越」などの処理を指定します。
③「在庫数量」「在庫仕入」「在庫販売」～在庫のみを管理するために設けました。②の「処理区分」により値の表示を変化させます。
この他にも入力した日付や数量の単位、税込金額、などいろんな項目が想定されると思いますが、自分の管理で必要と思う項目はどんどん追加してください。
項目の入力が終わったらデータ追加用のフィールドも別シートに作成しておきます。
※上記の項目作成からフィールド作成の流れは（「データ変換①～コードを貼り付ける」と「データ変換②～dhenkanマクロの使い方」）を参照してください。
参照用に倉庫マスタ、商品マスタを別シートに作成しておきます。私はシート名を「マスタ」にしました。
このシートから商品名や単価などを関数により参照します。
それでは、項目に必要な設定を行います。
倉庫と商品にはVLOOKUP関数で、それぞれ「ID」を入力すると「マスタ」シートから名前が自動で表示されるよう設定しましょう。エラー時になにも表示されないようIFERROR関数を合わせて使いましょう。
「処理区分」には、処理を選択できるよう入力規則を設定します。この処理区分により在庫項目の表示が変化するので、大事な設定になります。
※図はOffice2007での解説になります。
処理区分のセル「B5」を選択し、「データ」タブの「データツール」グループから「入力規則」をクリックします。
※Office2003ではデータメニューの「入力規則」になります。
入力規則ダイアログボックスの「入力値の種類」に「リスト」を選択します。
次に「元の値」に処理名を入力します。処理ごとにカンマ「,」で区切ります。
指定した処理区分は「仕入」「販売」「値引き」「転送」「転受」「繰越」「訂正増」「訂正減」です。
※「訂正増」「訂正減」は年度末などなんらかの理由で在庫を調整するとき使います。この他にもいろんな処理が考えられるかもしれません。
このように処理をリストから選択できるようになります。
「仕入単価」「販売単価」には商品IDを検索値としてVLOOKUP関数を使い「マスタ」シートからそれぞれの単価を表示しましょう。（図は仕入単価を設定しています。）
「仕入金額」は基本的には「数量×仕入単価」ですが、「処理区分」が「値引き」の場合は販売金額にのみ影響をあたえる（後半でも説明しています。）ので「０」を表示させるように設定します。
=IF($B$5="値引き",0,B7*B8)
「処理区分」セル「B5」が「値引き」の場合は「０」表示し、それ以外は「数量×仕入単価」を表示。という意味になります。
販売金額も基本的には「数量×販売単価」ですが、「処理区分」が「値引き」の場合は「数量」項目を値引き金額欄として一時的に置き換えるので「数量」欄をそのまま表示するよう設定します。
=IF($B$5="値引き",B7,B7*B9)
「処理区分」セル「B5」が「値引き」の場合は数量セル「B7」表示し、それ以外は「数量×販売単価」を表示。という意味になります。
それでは在庫管理用に用意した項目への設定です。
この項目は、「処理区分」により在庫がプラス（増）になるのかマイナス（減）になるのか判断し表示します。
この項目を集計することにより、現在の在庫を把握することができるわけです。
まずは「在庫数」と「在庫仕入」の設定を行います。
下記条件が考えられます。
①在庫がプラス（増）になる処理は「仕入」「転受」「繰越」「訂正増」です。
②在庫がマイナス（減）になる処理は「販売」「転送」「訂正減」です。
③ここで問題になるのは「値引き」という処理です。値引きは商品が古かったり、または破損したときの処理なので、数量や仕入値には影響を与えません。与えるのは販売額のみです。したがって「値引き」時の処理は「０」表示にする必要があります。
上記の３つの条件分岐をIF関数とOR関数を使って処理します。
※図はクリックすると大きく表示されます。
=IF(OR($B$5="仕入",$B$5="転受",$B$5="繰越",$B$5="訂正増"),B7,IF(B5="値引き",0,B7*-1))
「処理区分」セル「B5」が「仕入」「転受」「繰越」「訂正増」のいずれかの場合は数量セル「B7」をそのまま表示、「値引き」の場合は「０」を表示、それ以外の処理（「販売」「転送」「訂正減」）の場合は数量セル「B7」に「-1」を掛けた数量を表示する。という意味になります。
在庫仕入にも同じ条件で、仕入金額を表示させます。
=IF(OR($B$5="仕入",$B$5="転受",$B$5="繰越",$B$5="訂正増"),B10,IF(B5="値引き",0,B10*-1))
次の「在庫販売」欄は「値引き」の場合はマイナス表示になりますので、上記の値引き用の条件分岐はいらなくなります。
=IF(OR($B$5="仕入",$B$5="転受",$B$5="繰越",$B$5="訂正増"),B11,B11*-1)
「処理区分」が「仕入」「転受」「繰越」「訂正増」のいずれかの場合は販売金額セル「B11」をそのまま表示、それ以外は「販売金額」に「-1」を掛けた金額を表示。という意味になります。
入力箇所は「倉庫ID」「商品ID」「処理区分」「数量」のみです。あとは入力した時点で、自動で単価や金額が計算されるように設定がされました。
最後に、入力支援マクロ「dhenkan」マクロを図形（星にしてみました。）に登録して、項目設定の終了になります（「データ変換①～コードを貼り付ける」及び「データ変換②～dhenkanマクロの使い方」）を参照してください。
毎日の在庫データを入力する準備が整いました。
次回は実際に入力し、確認をします。
ピボットテーブルで在庫管理②～データ入力・・・・・へつづく
0 件のコメント:
コメントを投稿