Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 7全般 : Excel 2003)
(現在庫) − (同一商品の受注合計) < 0の場合、マイナスになる日付を表示させる方法
投稿日時: 18/03/26 19:39:46
投稿者: ちゅーたこ
メールを送信

以前投稿させていただいておりましたが、内容が複雑すぎることに気が付き、関数である程度まで欲しいデータに近づけました。
 
SheetA(在庫状況と入荷情報のデータ)
※ データ範囲は、200行程度、A〜AEまであり、1行ごとに各商品のデータ(在庫数・次回入庫予定・入庫予定日)が入力されています。
 
A列 商品名ごとの現在庫が入力されています。
G列 在庫数から入荷と出荷の日付から判断して、在庫数から入庫情報(在庫が増える)とsheetBの
   受注情報(在庫数が減る)を差し引いた予想在庫数が入力されています。
S列 入庫予定の商品名(sheetBのB列と同じ)が入力されています。
AE列 いま現在の在庫数から受注数を差し引いたとき、−(マイナス)在庫になる日付を表示させたいです。
 
SheetB(受注情報)
※ データ範囲は、2000行程度、A〜Mまであり、sheetAと同様に各商品のデータ(受注数・お客様の納期)が入力されています。
B列 受注を受けた商品名(sheetAのS列と同じ)が入力されています。
C列 受注数(受注を受けた商品の受注数)が入力されています。
K列 お客様の希望する納期が入力されています。(yyyy/mm/ddの形式)
 
 
やりたいことは、sheetAのG列(予想在庫)がマイナスの場合、各商品名の欠品する日付を自動で表示。
↓詳細↓
sheetAのS列(商品名)と一致するsheetBのB列(商品名)の中で、sheetAのA列(現在庫)からsheetBのC列(受注数)の引き算を行い、sheetAのA列(在庫数)がマイナス()になる日時を調べたいと思います。
その結果をsheetAのAE列に表示させる。
 
VBA初心者のため、様々な投稿を見て途中まで作成しましたが、この先が分かりません。
 
Dim lastrow As Long '最終行の定義
Dim i As Long 'sheetsAの定義
Dim sumC As Variant 'sheetsBの定義
Dim lastrow2 As Long 'sheetsBの定義
Dim ShA As Worksheet, ShB As Worksheet
      
     Set ShA = Worksheets("sheetA")
     Set ShB = Worksheets("sheetB")
     Set sumC = Worksheets("sheetB").Range("C3:C2000")
         
     lastrow = ShA.Range("A" & Rows.Count).End(xlUp).Row 'sheetsA/Bの最終行
     For i = 3 To lastrow
        ’AE列に在庫切れの日時を記入
            If ShA.Range("G" & i).Value < 0 Then
                  sumC = sumC + WorksheetFunction.SumIf(ShB.Range("C3:C2000").Value, ShA.Range("G" & i).Value, ShB.Range("C" & i).Value)
 
この先が分かりません。また、ここまでがあっているのかも分かりません。
 
 
説明不足もあるかもしれませんが、何とか完成させたいと思っております。
どうかご指導よろしくお願いいたします。

回答
投稿日時: 18/03/26 20:50:16
投稿者: simple

現在継続中のスレッドをきちんと処理(コメントに対して回答すること)してからじゃないですか?
自分勝手なことだと、コメントがつきにくいのでは?

投稿日時: 18/03/26 21:28:12
投稿者: ちゅーたこ
メールを送信

simple さんの引用:
現在継続中のスレッドをきちんと処理(コメントに対して回答すること)してからじゃないですか?
自分勝手なことだと、コメントがつきにくいのでは?

 
すみません。
本当にそうですよね。。。
色々なことに捕らわれすぎて、焦りもあり、お礼もしないまま、自分勝手でした。
ご指摘ありがとうございます。

回答
投稿日時: 18/03/26 22:46:41
投稿者: simple

具体的なサンプル(現実と同じでなくてもよい)を使って説明してください。
その際、

    A列    B     C    D  
1
2
3
のような形式で示して下さい。

投稿日時: 18/03/27 01:46:21
投稿者: ちゅーたこ
メールを送信

◇sheetA
     A     G     S     AE
   (在庫数) (予想在庫数) (商品名) 【!不足日!】
1    39     187    みかん        ← 予想在庫がプラスなので空白のまま
2    39      87    みかん        ← 予想在庫がプラスなので空白のまま
3    39     -222    みかん   ???  ← 予想在庫がマイナスなので日にちを表示
4  商品名が変わるため空白行
5   1,363     563    リンゴ        ← 予想在庫がプラスなので空白のまま   
6   1,363    1,288    リンゴ        ← 予想在庫がプラスなので空白のまま
7   1,363    -71     リンゴ   ???  ← 予想在庫がマイナスなので日にちを表示
8   1,363    -71     リンゴ   ???  ← 予想在庫がマイナスなので日にちを表示
9  商品名が変わるため空白行
10    26    626     バナナ        ← 予想在庫がプラスなので空白のまま   
11    26    626     バナナ        ← 予想在庫がプラスなので空白のまま
 
◆sheetB
     B     C     K     
   (商品名)  (受注数)  (user納期)
 
1   みかん    2    2018/3/22
2   みかん    200    2018/3/29
3   みかん    150    2018/4/2
4   みかん    250    2018/4/27
5   みかん    50    2018/5/1
6   リンゴ    500    2018/3/29
7   リンゴ    300    2018/4/2
8   リンゴ    150    2018/4/10
9   バナナ    500    2018/8/30
10   バナナ    120    2018/10/1
 
このような感じのデータです。
不足情報などがありましたら、大変お手数ですが、ご連絡お願いいたします。
 
 
 
 
 

回答
投稿日時: 18/03/27 11:20:56
投稿者: もこな2

simpleさんが既に指摘されてますけど、先の投稿したほうのトピックで問われていることなり、アドバイスされたことにちゃんと向きあったほうがよいとおもいますが・・・
たとえば、見よう見まねで作りましただけだと、解らない部分が回答者にわからないので、〇〇〇という動作になるとおもって、×××という記述をしたのですが、△△△という結果になりますなど、どう思って、どうしたら、どうなったのかという提示があると、回答者側で質問者さんの状況がつかみやすくなります。
 
また、回答ではありませんし、超個人的意見ですが全文引用は控えるべきと思います。全文引用を多用すれば単純に見づらくなるだけなので、逆質問や投げかけの部分に返答するなら、その部分だけ引用するか、〇〇さんの×××へのレスです。と一言書いておけば、いいんじゃないでしょうか。
 
以下、本題。

引用:
Set ShB = Worksheets("sheetB")
Set sumC = Worksheets("sheetB").Range("C3:C2000")
Worksheets("sheetB") を「ShB」としてるのだから、
Set sumC = ShB.Range("C3:C2000")
のほうがよいとおもいます。
 
引用:
lastrow = ShA.Range("A" & ShA.Rows.Count).End(xlUp).Row 'sheetsA/Bの最終行
こちらは、コメントの意味がわからず。「lastrow」に何が格納されると理解されてるのでしょうか?(ヒントがてら、ちょっと修正しました。)
 
引用:
Dim sumC As Variant 'sheetsBの定義
Set sumC = Worksheets("sheetB").Range("C3:C2000")
sumC = sumC + WorksheetFunction.SumIf(ShB.Range("C3:C2000").Value, _
                            ShA.Range("G" & i).Value, ShB.Range("C" & i).Value)
こちらもコメントの意味がよくわからず。「sumC」ってsheetsBの定義じゃないですよね?
で、「sumC」ってSumIf関数の結果をどんどん累積していく変数として使いたいのではないかと推測しますが、はじめにセル範囲をセットしてますから、何をやりたいのかよくわからなくなってます。(というか、このまま実行したら型が違うってエラーにならないのでしょうか・・・)
 
つぎに、記述のものがSUMIF関数としてこれで良いのか疑問です。
SIMIF関数は、「=SUMIF(範囲,検索条件,合計範囲)」と記述するのが本来のルールだとおもいます。
しかし、ご呈示のコードでは、合計範囲に単位セルを与えているので範囲ではなくなっています。ただ、合計範囲の部分について、配列の起点となる単一セルを与えてやれば、Excel側でうまく処理してエラーにならないというのを理解して記述しているなら話は別です。
また、(検索)範囲と、合計範囲が同じシートの同じ列になってますが、これは意図したものなんでしょうか?一般的には検索条件がある列と、合計したい数値がある列は別になるとおもいます。
 
 
また、 18/03/27 01:46:21のイメージもよくわかりません。
sheet2の「みかん」は5レコードあるのに、出力はなんで3レコードになってるんですか?(月ごと?)
sheet2の「りんご」を3レコードしかないのに、出力はなんで4レコードになってるんですか?(7,8行目が同じなので打ち間違い?)
「バナナ」は2レコードで、レコード数だけでいえば問題ないですね。
また、それぞれの商品の在庫の提示がないので数量の検証はできませんし、入荷(入庫?)はないんでしょうか
 
総じて、単純に入出庫計画表みたいなのがあって、現在の計画だと、何日出庫分が欠品になってしまうのか確認できる表を作りたいってことだけではないかとおもうのですがいかがでしょうか?

回答
投稿日時: 18/03/27 15:32:03
投稿者: もこな2

追加で。
 
入出庫計画が別にあるとして、
こんな表をつくりたいってことじゃないのかなぁ・・・・
 

引用:
(日付)  (商品)(増:事由)(増:数量)(減:事由)(減:数量)(在庫数量)
2018/ 3/ 1 みかん  繰越    2,000             2,000
       リンゴ  繰越    1,500             1,500
       バナナ  繰越    100              100
2018/ 3/ 8 リンゴ             出庫     500   1,000
2018/ 3/ 10 バナナ  入庫    1,000   出庫     500    600
2018/ 3/ 15 みかん             出庫    1,500    500
       バナナ             出庫    1,000  ★欠品エラー

 
表ずれは想像力でカバーしてください。

回答
投稿日時: 18/03/27 22:30:15
投稿者: simple

すでに指摘がありますが、説明すべきは、単なるセルの場所ではありません。
・二つのシートに入っているデータをもとに、
・どういう論理で、
・どのような結果を得たいのか
を具体的に説明して下さい。
 
今のままだと入っているデータはまるでバラバラですよね。もう少し頑張りましょう。

回答
投稿日時: 18/03/28 10:39:53
投稿者: 細雪

構成・・というか、考え方から整理したほうが良いと思いますけどね、ほんとは。
 

ちゅーたこ さんの引用:
◇sheetA
     A     G     S     AE
   (在庫数) (予想在庫数) (商品名) 【!不足日!】
1    39     187    みかん        ← 予想在庫がプラスなので空白のまま
2    39      87    みかん        ← 予想在庫がプラスなので空白のまま
3    39     -222    みかん   ???  ← 予想在庫がマイナスなので日にちを表示
 
◆sheetB
     B     C     K     
   (商品名)  (受注数)  (user納期)
 
1   みかん    2    2018/3/22
2   みかん    200    2018/3/29
3   みかん    150    2018/4/2
4   みかん    250    2018/4/27
5   みかん    50    2018/5/1

 
あなた(貴社)の理論では上記の「???」にはどんな日付が入るのでしょうか。
 
そもそも1行目、みかんの「予想在庫数=187」になるべき計算式及び考え方をお示しください。
 
というか、「入庫予定」はどこにあるのでしょうか。
 
 
以上、補足ください。
ってことです。
 
 
みかんだけピックアップしてみると・・
    商品  前日在庫   入庫   出庫   当日在庫
3/1   みかん     0   
3/2   みかん     0    100           100
3/3   みかん    100         61      39
(略)
3/21  みかん    39    150           189
3/22  みかん    189          2      187
(略)
3/29  みかん    187         200      -13   足りないよ!
のような感じだと思うのです。
集計開始時点(3/1)での前日在庫が0だったら、
  [入庫の合計]−[出庫の合計]=集計時点での[在庫]
に絶対になるはずです。
これだと別の商品のデータが途中に紛れていてもSUMIF関数とIF関数だけで組めます。
何か付加価値を付けたいのでなければ、VBAは不要ですよ。
そういう意味でも、まずは整理をオススメします。

回答
投稿日時: 18/03/31 01:14:35
投稿者: もこな2

しばらく質問者さんからのレスがないけど、がんばって整理中でしょうか
とっくに気づいてるし、最初からそうしてるかもですが、在庫管理の話なので一応・・・
 
おそらくご質問のことは入出庫予定から情報を累積して、オーダーを受けることができるかどうかを調べるシステムを作りたいといったことだとおもいますが、そういった予定上の数量を積み上げたものは”操作在庫”なんて呼ばれ方をします。
 
一方で、実際の入出庫に伴って変動する在庫は”実在庫”なんて呼んだりします。
 
この二つは似てるようで別ものですので、一つの”在庫”というものだけで全部を整理しようとすると話がこんがらがってしまいますので注意したほうがよいように思います。
 
また、計画はあくまで計画ですから、一度入力した入庫予定や出庫予定は、発注番号や受注番号などを付してデータを追えるようしておいて必要が生じたときには修正可能になるようなシステム開発が求められるようにおもいます。
 
もし、これらについてお気づきでなければこの点にも留意して整理されたほうがよいとおもいますし、すでに御承知のことであれば、釈迦に説法 お目汚し失礼しました。

トピックに返信