入出庫表を作成したいのですが・・・。教えてください。

質問

前回説明不足とのご指摘を受けましたので改めて質問させていただきます。前回答えていた方、ありがとうございました。
では・・・、
このような仕様がエクセルで出来るかどうかわかりませんが、
出来るのであれば教えてください。

A列に入庫数、B列に出庫数、C列に在庫数を入れたいのですが、

(1)入庫と出庫の数量に差異があった場合、在庫数のセルの色が変わる
　若しくは一目で異常が認識できる設定。
　入庫が10に対して出庫が10を超えている。要は”-”（マイナス）
　になってしまっている。
　例えば　　１日に10入庫して、２日に3出庫、３日に8出庫
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　↑
　　　　　　　　　　　　　　この時点にて一目で認識できる設定
　　　　　　　　　　　　　　　（マイナス表示はさせない）
　又、商品を入庫し、個数を入力したら入庫時間が表示される設定。
　　　出庫も同じ設定。　

(2)入庫されてから一定期間（例　12時間）全く出庫されない場　合セルの色が変わる　若しくは一目で異常が認識できる設定。

あくまでも、A，B，C列は仮的な物ですので、どう設定していただいても問題ありません。
上記2点ですが、分かる方いましたら教えてください。
素人ゆえ、説明不足かと思いますがよろしくお願いします。

回答No.2

noname#95859 · ベストアンサー

基本的に、貴兄の欲するものを達成するには、VBAを使うしかないです。
条件付書式を使って、在庫が0以下になった時、セルに印をつけるにしても、入庫、出庫
の累積値として、在庫があるゆえに、品番が入り混じっての記入がなされていると、単純にはいきません。
ある日数出庫が無い場合に、セルに印を付けるのは、何をトリガとして、チェックするのかを考える必要があります。

前提を設けて、やってみました。
１．アイテムが複数存在し、混在する。
２．入庫欄に数値が入ったら、Time欄(入庫)に時刻を入力する
３．出庫欄に数値が入ったら、Time欄(出庫)に時刻を入力する
４．出庫欄に数値が入ったら、在庫を再計算する。また、出庫の時間チェックをする
５．ワークシートとして”Temp”を設定します。
６．下記のようなデータをサンプルとして使います。（シート名は”Data”）

item in out now T-in T-out
aa 12 10 2 2007/3/13 12:42 2007/3/13 12:41
bb 5 3 2 2007/3/13 12:42 2007/3/13 12:42
aa 9 2 9 2007/3/13 12:42 2007/3/13 12:42
cc 23 34 -11 2007/3/13 21:14 2007/3/13 21:14
ee 12 2 10 2007/3/13 22:26 2007/3/13 22:26
aa 3 4 8 2007/3/13 22:26 2007/3/13 22:26
cc 2 -9 2007/3/13 22:26
ff 2 2 0 2007/3/13 22:27 2007/3/13 22:27
aa 3 4 7 2007/3/13 22:28 2007/3/13 22:28
aa 3 4 6 2007/3/13 22:29 2007/3/13 22:29
ff 5 6 -1 2007/3/13 22:31 2007/3/13 22:31
ee 3 2 11 2007/3/13 22:32 2007/3/13 22:32
dd 3 3 2007/3/13 22:33
aa 3 4 5 2007/3/13 22:33 2007/3/13 22:33
cc 4 2 -7 2007/3/14 22:34 2007/3/14 22:34
dd 2 2 3 2007/3/14 22:34 2007/3/15 23:52

-------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range) '”Data"シートの裏側に書き込む
If Target.Column = 2 Or Target.Column = 3 Then
Target.Offset(0, 3).Value = Now()
End If
If Target.Column = 3 Then
rowpos = 2
Do While Cells(rowpos, 1).Value <> ""
Call dataentry
rowpos = rowpos + 1
Loop
End If
End Sub

Sub dataentry()　　　'標準モジュールに書き込む
Application.ScreenUpdating = False
xday = 1
hani = Worksheets("Data").Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Address
Range(hani).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Worksheets("Data").Range(hani).Copy Worksheets("Temp").Cells(1, 1)
E_rowpos = Worksheets("Temp").Cells(1, 1).End(xlDown).Row
With Worksheets("Temp")
hani = .Range(.Cells(2, 1), .Cells(E_rowpos, 4)).Address
myArray = .Range(hani).Value
End With

r = UBound(myArray, 1)
Worksheets("Data").ShowAllData
Worksheets("Data").Cells.Sort _
Key1:=Worksheets("Data").Columns("E"), _
header:=xlYes

rowpos = 2
With Worksheets("Data")
Do While .Cells(rowpos, 1).Value <> ""
test = .Cells(rowpos, 1).Value
For i = 1 To r
If test = myArray(i, 1) Then myPosition = i: Exit For
Next
myArray(myPosition, 2) = myArray(myPosition, 2) + .Cells(rowpos, 2).Value - .Cells(rowpos, 3).Value
.Cells(rowpos, 4).Value = myArray(myPosition, 2)
If myArray(myPosition, 2) < 0 Then .Cells(rowpos, 4).Interior.ColorIndex = 6
If .Cells(rowpos, 6).Value <> "" Then myArray(myPosition, 3) = .Cells(rowpos, 6).Value
rowpos = rowpos + 1
Loop
End With

For i = 1 To r
a = myArray(i, 3)
b = Now
c = DateDiff("d", a, b)
If DateDiff("d", myArray(i, 3), Now) > xday Then
myArray(i, 4) = 1
Else
myArray(i, 4) = 0
End If
Next

Columns("F").Interior.ColorIndex = xlNone
For j = rowpos - 1 To 2 Step -1
test = Cells(j, 1).Value
For i = 1 To r
If test = myArray(i, 1) And myArray(i, 4) = 1 Then
Cells(j, 6).Interior.ColorIndex = 15
myArray(i, 4) = 0
Exit For
End If
Next
Next
Application.ScreenUpdating = True
End Sub

------------------------------------
考え方
イベントプロシージャを使います。入庫、出庫の欄に入力があった場合、イベントプロシージャを使って、
日時を該当の場所に入れます。
出庫に入力があった場合には、サブルーチンSub dataentry　を呼んで、在庫の計算、入力を実施します。
また、一定期間、出庫が無かったアイテムの判別もしています。

F8を使って、ステップ動作をさせて、動きをつかんでください。

コメント：作りを工夫する必要があります。処理スピードが遅い。仮にたくさんのデータとなったときに問題となります。
処理スピードが遅い理由は、アイテムが混在するということを前提に置いたからです。
（小生、このアイテムを無視することは、全くの間違いだと考えています）

総じて、リアルタイムで、在庫が表示される仕組みを望まれるなら、条件をつける（アイテムをなくすとか）必要があります。
今回のトライアルは、全くの参考です。お役に立てば、幸いです。

お礼

ありがとうございました。
これを参考にさせていただき勉強します。

回答No.1

TTak

（１）できます。
在庫数のフィールドで、「条件付き書式」の設定を行います。
[セルの値が][次の値より小さい]として、値を0にします。
条件を満たす場合のセルの書式も決められますので、セルの色を赤なりなんなりに指定します。

個数を入力したら入庫時間が表示される設定は、マクロが必要です。
ワークシート関数でも日時を返すものがありますが、どこかのセルが編集されたタイミングで、すべての関数が実行されますので、特定のレコードのみに日時の値を張り付けるコーディングになります。

（２）は条件付きで可能
まず、前記の日時の値が入るフィールドを入庫・出庫双方に追加する必要があります。

次に、時間経過で異常を判定する方法はワークシート関数で可能ですが、前述のとおり、ワークシート関数の計算実行のタイミングはどこかのセルが編集されたときか、ブックを開いたときになりますので、例えば、朝、入出庫表を開いて、夕方までほったらかしにしておいて、昼頃に期限を迎える出庫があったとしても、警告を発することはできません。

この場合、入出庫表側ではイベントを発生させる「異常チェック」などのコマンドボタンを配置して再計算させるなど、運用面のカバーが必要でしょう。