エクセルのデーターベースについて

質問

教えて下さい。
飲食業の在庫管理をエクセルで作りたいのですが、仕入台帳と売上台帳を別で作成したいと思っています。困っているのが売上台帳です。一つの商品を売上げた時、その商品名と数量を入力するだけで、その商品にかかる原材料すべてと、その卸売業者名、原価をデーターベースに落とし込みたいのですが、尚、その原材料は、多種の商品で使用しています。
複雑になってもかまわないので、教えて下さい。

回答No.3

hallo-2007 · ベストアンサー

>VBAを習得していないので、マクロでやってみようかと思います。
エクセルではVBAもマクロも同じもののことです。
アクセスでは、VBAとマクロは全く別物です（同じように機能を作成する方法ではありますが）
試しに、新しいブックに
シート名　材料使用量マスター　として
商品ID　 材料ID　 数量
10001 100000 2
10001 200003 1
10001 300001 2
10002 100001 1
10002 200003 2
といったように商品ごとに使用する材料と数量を入れた表を準備
シート名　使用材料一覧　として
商品ID　 材料ID　 数量
とタイトル行を準備
もひとつのシートに（名前は自由）
日付　 納品No　 商品ID　 数量
10月1日 1 10001 2
と売れた商品を入力するシートを準備します。
そのシート名のタブを右クリック、コードの表示をクリックすると　VBエディターが起動します。
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
商品ID = Range("C" & Target.Row)
数量 = Target.Value
MsgBox 商品ID
For i = 2 To Sheets("材料使用量マスター").Range("A65536").End(xlUp).Row
If Sheets("材料使用量マスター").Range("A" & i).Value = 商品ID Then
GYOU = Sheets("使用材料一覧").Range("A65536").End(xlUp).Row + 1
Sheets("使用材料一覧").Range("A" & GYOU).Value = Date
Sheets("使用材料一覧").Range("B" & GYOU).Value = Sheets("材料使用量マスター").Range("B" & i).Value
Sheets("使用材料一覧").Range("C" & GYOU).Value = Sheets("材料使用量マスター").Range("C" & i).Value * Target.Value
End If
Next
End If
End Sub
上記をコピィして貼り付けてください。
細かいことまでは説明いたしませんが、
If Target.Column = 4 Then　　つまり
売上を入力するシートの4列目D列に数量を入力したときに
記述したVBAが実行されて
使用材料一覧のシートに　使用した材料が書き加えられていきます。
コードは、わかりやすいように
Sheets("使用材料一覧").とか何度も記述しています。少し勉強していただけると
応用やもっとシンプルなコードになると思います。
注意
数量を入れたときでなく、間違って入力して、後で変更の作業を行ったときも実行されます。

お礼

本当にありがとうございます。
早速試しました。
問題が、一気に解決しました。
親切にVBAまで、教えて頂き、大変感謝しています。
ありがとうございます。

回答No.2

hallo-2007

商品を仕入れて、その商品を販売するのであれば、在庫の管理も意外と簡単なのですが、
材料を仕入れて、その材料を使って製品を組み立て、製品を出荷し、材料の在庫の管理を行う
場合は、在庫管理でも結構上級です。
この様なWebサイトで全ての状況を把握するのは難しいので、一案のみです。
データベースでは、テーブル（アクセスでは、テーブルと呼びますが、この場合、シートに準備するシートの構成とでも覚えてください
の準備の仕方が、重要で色々な経験からわかってきます。
この場合
シート　材料一覧マスター
材料ID　材料名　　　　現在在庫　仕入先　仕入単位　仕入単価・・・
100000　ハンバーグ
200003　ウインナー
・・・
シート　商品一覧マスター
商品ID　商品名　　　　　商品単価
10001　ハンバーグ弁当　　　800
・・・
シート　商品仕様材料マスター
商品ID　材料ID　数量
10001　　100000　2
10001 200003　1
10001　　300001　2
・・・
10002　　100001　1
10002　　200003　2
・・・・
と商品ごとに使用する材料の一覧表を準備します。
シート　売上データ
日付　納品No　商品ID　商品名　数量　単価　小計
10/1　00001　100001　VLOOKUP　2 VLOOKUP 計算式
・・・
と売上のデータを日付、商品ID、数量を日々入力します。
シート　使用材料一覧
日付　使用商品ID　材料ID　使用数量・・・
10/1　　10001　　　100000　2
10/1　　10001　 200003　1
10/1　　10001　　　300001　2
・・・
売上の一覧から使用した材料の一覧表を作成するのは関数では大変ですので、VBAを使用したほうが良いでしょう。
操作方法として、一日の売上を入力したらマクロのボタンを押すと、一日に使用した材料が
使用材料の一覧シートに材料ごとに追加されるVBAとかです。
シート　材料仕入データ
日付　　材料ID　仕入数量・・・
10/1　　100000　　100
10/1　　200003　　100
・・・
と仕入れのデータを入力していけば
材料別の在庫数（材料一覧シートの現在在庫数）は、SUMIF関数で得られると思います。
状況によっては、更にシート数は増えるとかもしれませんが、最低でもこの6種類は必要かと思います。
データの数にもよりますが、末永く使用するのであれば、アクセスなど
データベースをお勧めします。

お礼

回答ありがとうございます。
返事が遅くなりすみません。
だいぶイメージができるようになりましたが、
VBAを習得していないので、マクロでやってみようかと思います。
おっしゃる通り、在庫管理表などは、数年続けて意味があると思うので、来年度は、アクセスに挑戦したいと思います。
（時間があればですが、、、）
いろいろと、ありがとうございます。

回答No.1

rivoisu

ここで教えられるほど単純ではありません。
本当に必要なら信頼できるソフト業者を探して作るべきだと思います。

補足

ありがとうございます。
そうですね。ソフト業者に依頼できれば、楽ですね。
見積はとったのですが、ただ高額なので無理なのです。