[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『Excelでの在庫管理（先入先出）』（ゆ）
ExcelのVBAを使って在庫管理を先入先出で行いたいのですが
<仕入>のシートに
A B C D E
1 商品名 仕入日 単価 数量 合計金額
2 みかん 2013/5/1 3.4 15 ×××
3 りんご ××× ×× ×× ××
4
5
のようにズラッと項目ごとに記入されています。
<出庫>のシートに
A B C
1 商品名 棚卸/月 数量
2 みかん 2013/5/31 45
3 ×× ×× ××
など予め決められた商品名が肇から入力されており
棚卸/月にも予め決められた月末の日付けが入力されています。
sheet３に結果として
商品名 仕入日（古い物から） 単価 仕入数量 出庫数量 を抽出したいです。
みかん 2013/5/1 3.4 30 30 0
みかん 2013/5/2 ×× 50 15 35
（45個出庫した場合）
になるのが理想です。
よろしくお願い致します。
＜ 使用 Excel：Excel2010、使用 OS：unknown ＞
関数で？ ＶＢＡで？
追加で
出庫シートの意味がよくわかりません。 なんとなく、5月の出庫総数が 45 だった というようにも読めますし でも「棚卸」となっているので、これは棚卸数？ もし、棚卸数なら、それは5月の月末時点？ それとも5月の月初時点? で、これが棚卸数なら、出庫数はどこにある？
逆に、出庫数なら、4月末（5月月初）の在庫数はどこにある？
（β） 2015/03/10(火) 12:57
にしたいです。
商品名は仕入にも棚卸にもいろんな種類があります。
（ゆ） 2015/03/10(火) 13:38
たとえば
仕入れシートに
みかん 5/1 数量 15 この１行だけがあったとして
棚卸シートが
みかん 5月末 45
こうなっていたとして、
ここから、どういうロジックで 5月の出庫数が割り出せるのですか？
棚卸シートに
みかん 4月末 50
といったものがあるとすれば
(50+15)-45 で、5月の出庫数は 20 だと、そう計算することはできますけど・・・
（β） 2015/03/10(火) 13:54
棚卸シートに
みかん 5月末 数量50
sheet３に
みかん ５/１ 数量５０ 出庫５０ 残 0
みかん ５/25 数量100 出庫50 残50
仕入シートにはランダムで仕入たさまざまな商品が入力されています。
6月末に時点で前月のあまり
みかん5/25 数量50からまた古い物順で同じように計算したいです。
（ゆ） 2015/03/10(火) 14:07
う～～ん・・・・
質問の意図をなかなか伝えるとこができないなぁ。
Sheet3 は 「結果」ですよね。つまり、ＶＢＡで作成するんですよね。
たとえば最初の 5/1 出庫 50 。これをつくろうとして、
5/1 に出庫があったかどうか、どうやったら知ることができるのですか？ で、その日の出庫が 50 だったと、どうやったら知ることができるんですか？
5/1時点で与えられる情報は、5/1 に 50 仕入れしたよ ということだけですよね？
(ゆ)さんの会社の出庫のルールを説明いただかないと。 会社によっては、あるいは商品によっては、仕入れ後、検査のために1週間は、倉庫に保管なんてところもあるかもしれないですから。
（β） 2015/03/10(火) 16:58
トータルで100出庫して残は5/25に仕入れた50個が5月末時点での余り
という事にしたいです。
5/1の仕入れから出したかは実際にわからなくても5月末の時点での棚卸し在庫が50個なら
出庫は100個と考え古い在庫から(5/1が一番古い在庫として)単純に先入れ先出しで引いていきたいのです。
6月末の時点で同じ処理をする場合は
5/25の余りのみかん50個と
新たに6月に仕入れた商品をまた日付の若い順から出庫したと考えて処理結果をシート3にどんどん同じ商品名ごとにためていきたいです。
仕入れを5/1から商品はランダムに入力していき
5月末に棚卸シートに棚卸結果を入力
その後にシート3に上記のような結果を反映させたいです。
つまり、毎月月末に棚卸しの結果を入力し終わった後にのみ
シート3に結果を反映させるようにしたいです。
出庫は月末時点(棚卸シートに結果入力後)で知ることになります、、
（ゆ） 2015/03/10(火) 22:48
説明ありがとう。 Sheet3 を出庫明細だと勘違いしていましたが、入庫されたものに対する仕入れ日をロット番号としてみたときの在庫状況表だったんですね。 だいぶわかってきましたが、いくつか、クリアにしておきたいので。
・5/1 50、5/25 100 の仕入れ（入庫）があり、5月末に 50 の棚卸があったから出庫は 100 だったという判断は、 5月月初の、この在庫が ゼロ だったということです。この ゼロ だったということは、どうしたらわかりますか？
１）仕入れシートに、4月あるいはそれ以前の仕入れのデータがまったくない？ ２）棚卸シートに 4月（前月）の月末在庫の情報がない？
いずれですか？ あるいは、そのほかの判断基準がありますか？
・上記とも関連しますが、仕入れシートには、ある月のデータのみ（5月なら5月だけ）が存在するのですか、 それとも、過去からの履歴がず～っと記載されているのですか？
・過去からの履歴が、すべて記載されているとしたら、Sheet3にはその中のどの月の仕入れに関する出庫状況を作成するのですか？ （5月分のデータを相手にする、その 5月 を どうやって特定できますが？） （β） 2015/03/11(水) 04:39
のようにどんどん繰り返してシート3に
商品名ごとにためていくことはvbaでは可能でしょうか？
今回新しく先入先出用のファイルを作る事になったので
今回は2015/2末の棚卸から載せていき
3月4月とどんどん入力していきたいと思っています。
（ゆ） 2015/03/11(水) 08:51
>のようにどんどん繰り返してシート3に >商品名ごとにためていくことはvbaでは可能でしょうか？
それは可能ですけど、どんどんためて１００年・・具合悪いでしょうね、実務としては。 なので、Sheet3にため込んだとしても、そこで過去月末の在庫がどうだったかさかのぼって調べるのではなく （もし、そうやって調べるとしたら、極端な話、明治時代にさかのぼって、今日までの入出庫を計算しなきゃいけない？）
棚卸シートに 各月末に残っている商品があれば必ず記載しておく。 たとえばみかんが4月末にのこっていれば5月末に、このシートを見れば
みかん 4月末 100 みかん 5月末 50
こうなっているとか。
みかんが4月末に残っていなかったら
みかん 5月末 50
これだけ。そうしておくと、マクロで判断できますね。
ところで、棚卸シートの 5月末 ですけど、これは文字列ですか？ それとも、日付型データで表示書式で ○月末 にしているんですか？
それと、仕入シートには、5月なら5月の分だけが存在するんですね？
（β） 2015/03/11(水) 09:34
5月末は表示書式で○月末にしてます。
2015/5/31など実際には入ってます。
仕入シートも
過去のものからずらっと記載していきたいと思ってます。
重くなるようでしたら一年用とかにして2015年版、2016年版など
年ごとにファイルを変えていこうかと考えています。
（ゆ） 2015/03/11(水) 09:54
>それは棚卸シートにどんどんある商品だけためていっても大丈夫ということでしょうか？
はい。むしろ（逆にいえば）必ず毎月末に在庫のあるものは記載する必要があります。
>仕入シートも 過去のものからずらっと記載していきたいと思ってます。
それはそれでかまいませんが、そうすると、マクロで実行してSheet3に書き込むデータの月を どう決めるかということが問題になります。
・何月の処理をするかメッセージを出して操作者に指定してもらう？ ・仕入れシートの最後の行（どの商品か、それは構いません）の仕入れ日をみて、そこで判断する？
（β） 2015/03/11(水) 10:32
All Noted です。 今から外出するので、少し時間ください。
（β） 2015/03/11(水) 10:52
仕入シートには
変わらずに仕入を入力していきます。
商品名 仕入月(表示書式で1月などにしてます。入ってる日付はその月の1日で統一）
単価 数量 合計金額(単価ｘ数量)
棚卸シートには
在庫のある商品だけ毎月末溜めていきます。
商品名 前月末時点の単価(平均) 棚卸月（表示書式で1月末などの表示。入ってる日付はその月の月末の日付け）
数量を入力する予定です。
sheet３に
商品名 月（何月末か） 平均単価（前月数量の合計金額+当月仕入の合計金額/前月残数量+当月仕入数量）この平均単価を仕入れから同じ商品ごとに合計して
棚卸しシートの前月の在庫もプラスしてそこから平均をだしたいです。
後は在庫合計 出庫数(仕入と棚卸の差）
出庫数量 を抽出したいです。
なのでシート3には先入れ先出しと違い棚卸しのあった商品のみの平均単価等を
1行表示したいです。
いきなりの変更すみません。
お願いできますでしょうか？
（ゆ） 2015/03/11(水) 14:57
移動平均なのか先入先出なのかは、在庫評価（金額評価）の問題ですよね。 数量としてどういった状況になっているか、その姿を作り上げるのは同じ（だと思う）ですので まずは、当初の処理を正しくコード化する方向で行きませんか。 それができた後、移動平均対応をしましょう。
なお、レイアウトについては、（ゆ） 2015/03/11(水) 14:57 で説明されたものにします。
いずれにしても、まだ外出先ですので、少し待ってくださいね。
（β） 2015/03/11(水) 15:50
とりあえず当初の要件で。 β版というか、本来は、（処理のやり直しも考慮すると）5月処理なら、Sheet3に5月分あるなら、それを 削除してから新しく5月分を展開すべきだけど、そうしていない。 なので、やり直すときには、手作業で当該月のデータをSheet3から削除して実行お願い。 また、仕入シートは商品別に見たときに昇順になっていることが前提。 （もちろん、離れ離れでもいいけど、上のほうに みかん の 5/25 があって したのほうに みかんの 5/2 があるということは想定していない）
Sub Test()
Dim thisYM As Date
Dim lastYM As Date
Dim yyyymm1 As String
Dim yyyymm2 As String
Dim shP As Worksheet
Dim shV As Worksheet
Dim shS As Worksheet
Dim c As Range
Dim wkym As String
Dim chk As Object
Dim dic As Object
Dim w As Variant
Dim v As Variant
Dim k As Variant
Set chk = CreateObject("Scripting.Dictionary")
Set dic = CreateObject("Scripting.Dictionary")
Set shP = Sheets("仕入")
Set shV = Sheets("棚卸")
Set shS = Sheets("Sheet3")
'仕入シートの最終行の年月(今月)
thisYM = shP.Range("B" & Rows.Count).End(xlUp).Value
thisYM = DateSerial(Year(thisYM), Month(thisYM) + 1, 0) '今月末日
lastYM = DateAdd("m", -1, thisYM) '前月末日
yyyymm1 = Format(thisYM, "yyyymm")
yyyymm2 = Format(lastYM, "yyyymm")
'仕入シートから今月データを抜出し、Dicに格納
'あわせて処理管理用のchk を作成
For Each c In shP.Range("A2", shP.Range("A" & Rows.Count).End(xlUp))
wkym = Format(c.Offset(, 1).Value, "yyyymm")
If wkym = yyyymm1 Then
'仕入れ明細のA列～D列を、作業域としての2列を加えて、とりあえず、そのまま格納
dic(dic.Count) = c.Resize(, 6).Value
If chk.exists(c.Value) Then
w = chk(c.Value)
w(2) = w(2) + c.Offset(, 3).Value
chk(c.Value) = w
Else
chk(c.Value) = Array(0, 0, c.Offset(, 3).Value, 0, 0)
End If
End If
Next
'棚卸シートの今月、前月の棚卸情報を取得し管理用chkを完成
For Each c In shV.Range("A2", shV.Range("A" & Rows.Count).End(xlUp))
If chk.exists(c.Value) Then
w = chk(c.Value)
wkym = Format(c.Offset(, 1).Value, "yyyymm")
Select Case wkym
Case yyyymm1 '今月末
w(1) = c.Offset(, 2).Value
Case yyyymm2 '先月末
w(0) = c.Offset(, 2).Value
End Select
w(3) = w(0) + w(2) - w(1)
w(4) = w(3) - w(0)
If w(4) < 0 Then w(4) = 0
chk(c.Value) = w
End If
Next
'先入先出処理
For Each k In dic
v = dic(k)
w = chk(v(1, 1))
If v(1, 4) <= w(4) Then
v(1, 5) = v(1, 4)
v(1, 6) = 0
Else
v(1, 5) = w(4)
v(1, 6) = v(1, 4) - v(1, 5)
End If
w(4) = w(4) - v(1, 5)
dic(k) = v
chk(v(1, 1)) = w
Next
shS.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(dic.Count, 6).Value = _
WorksheetFunction.Transpose(WorksheetFunction.Transpose(dic.items))
End Sub
（β） 2015/03/11(水) 18:09
棚卸シートに
商品名 棚卸月 数量
みかん 1月末 40
と入力してみて試したのですが
sheet3で
商品名 月 単価 在庫 出庫 残
みかん 1月末 3.4 100 100 0
みかん 1月末 5.4 50 50 0
となってしまいます。
先入先出だとたぶんシート３では
みかん 1月末 3.4 100 100 0
みかん 1月末 5.4 50 10 40
となってほしいのですが在庫残が全て0になってしまいます。
（ゆ） 2015/03/12(木) 09:47
＜移動平均関係です＞
＞途中で移動平均にすると言われた
移動平均なら、棚卸をした商品の数量と金額について 当月分だけ合計して行く簡易処理ですから、すんごい変更になったと 私は思うのですけど。。
引き続き、先入先出方式を先に対応すると云うことなので、 混線しない様に＜移動平均関係です＞とお断りしておいてお聞きします。 (疑問点を明らかにするだけの目的です。コードを書くとか言う話ではありません）
＞商品名 月（何月末か） 平均単価（前月数量の合計金額+当月仕入の合計金額/前月残数量+当月仕入数量） ＞この平均単価を仕入れから同じ商品ごとに合計して 棚卸しシートの前月の在庫もプラスしてそこから平均をだしたいです。 2行目の説明は、1行目の説明とダブっていないですか？ 1行目の処理で商品ごとの平均が出ると思うのですが。
＞ 後は在庫合計 出庫数(仕入と棚卸の差） ＞ 出庫数量 を抽出したいです。 在庫合計とは棚卸シートに入力した数値そのものですよね？（棚卸シートからの単なる転記ですね？）
Sheet3のレイアウトを決めて頂いた方がいいと思います。 下図みたいな感じかなと推測してはおりますけど。
<Sheet3 レイアウト> 行 ___A___ _____B_____ _______C_______ _____D_____ _____E_____ _______F_______ _____G_____ ______H______ _____I_____ ____J____ 1 商品名 前月末在庫 前月末平均単価 当月仕入数 同左仕入額 当月末平均単価 当月出庫数 当月末在庫数 当月売上高 当月損益 2
あと、インターネットで調べると 【材料費の単価の計算方法】 http://www.sk-genka.com/step/st03_tanka.html
＞3.移動平均法 ＞ 移動平均法は、 ＞ 材料の単価＝（残高金額＋仕入金額）÷（残高数量＋仕入数量） ＞ 移動平均法の単価は、安定しています。 ＞ ＞4.総平均法 ＞ 総平均法は、移動平均法と基本的には同じ考えです。 ＞ １ヶ月など一定期間で計算します。 ＞ 材料単価＝（期首在庫金額＋期中仕入金額）÷（期首在庫数量＋期中仕入数量）
となっていますので、そのページの分類なら、「1箇月の総平均法」に当たりますね？
（半平太） 2015/03/12(木) 12:21
在庫合計は、すみません！仕入の合計です。
当月の仕入数量合計ですね
今の所シート３のレイアウトは
商品名 何月末か 前月在庫数 前月末平均単価 当月仕入数 当月平均単価 当月出庫数 当月在庫(残) 当月出庫金額までに
したいなと思っています。
１ヶ月の総平均法でお願い致します。
（ゆ） 2015/03/12(木) 12:39
＞１ヶ月の総平均法でお願い致します。
済みません。 m(__)m 今の処、私自身はコードを書くつもりありません。
先入先出にこだわると遠回りではないかと危惧したための投稿です。
まぁ、上層部の判断で、また先入先出に戻る可能性が残っているなら また別の話になりますけど。。
（半平太） 2015/03/12(木) 13:15
（ゆ） 2015/03/12(木) 13:50
（ゆ） 2015/03/12(木) 09:47 このデータで （β） 2015/03/11(水) 18:09 のコードを動かすと、こちらの結果は
みかん 2015/1/1 3.4 100 100 0 みかん 2015/1/2 3.4 50 10 40
こうなりましたけど？
いずれにしても、移動平均で というのは あくまで 在庫金額の評価の問題です。 もし、(ゆ)さんの要件が、その金額計算のための仕掛けをつくるということであれば、 半平太さんも指摘されているように、現在のテーマ（金額は関係なく、あくまで在庫現物のロット管理）で、これ以上進めていっても無意味です。
全く新しいテーマですから、このトピを閉じて、別途、「数量の管理」ではなく「金額評価」のテーマとして 仕切り直しをされてはいかがですか？
（β） 2015/03/12(木) 14:38
になると思うんですが、それを移動平均に変えて
みかん 1月末 平均単価 仕入数量150 出庫数110 残40
ただ間に単価の平均が入っているだけで
根本は1月の同商品の仕入れ合計と出庫数余りが何個かを反映したいです。
平均単価はあったほうがわかりやすいので載せたいです。
移動平均というよりは仕入れ合算して出庫数を知りたいです。
ただ平均単価を間に入れたかったので移動平均と書いたほうがわかりやすいかと思って
移動平均と書きました。
平均単価でなく、単価の部分は仕入れの合算で一番高い単価などでも構わないです。
後、上のコードで何度もためしたのですがなぜか
3/12 9：47 のように入力すると残が全て0になってしまいます。
仕入と棚卸のシートをどの様に入力しましたか？
（ゆ） 2015/03/12(木) 14:57
仕入シート
商品 仕入れ日 単価 数量 金額 みかん 2015/1/1 3.4 50 みかん 2015/1/2 3.4 100
棚卸シート
商品 みかん 2015/1/31 40
ですけど？
↑は、実際のシートからそのままコピペしています。
処理前にSheet3はクリアしていますか？
前にコメントしましたけど、処理結果は、Sheet3の下に【追加】しますので
もし、前の不具合のある結果が、上のほうに残っていたら、それは、変わりませんので。
下に追加された部分をみてください。
（β） 2015/03/12(木) 15:23
>このコードを少し変えて仕入れ算で計算することは可能ですか？？
はい、できますよ。 でも、それは、「移動平均」や「総平均棚卸評価」とは、ほど遠いものですので そのあたりは、使う人の誤解の無いようにされたらよろしいかと。
（β） 2015/03/12(木) 16:59
それで、現行のコードの実行結果はどうでした？ 新しいコードは、その結果を見てからにしましょう。
（β） 2015/03/12(木) 21:36
とりあえず、平均単価表示の集約版を。要件を取り違えているかもしれませんが。 Sheet4 に作成します。
Sub Test2()
Dim thisYM As Date
Dim lastYM As Date
Dim yyyymm1 As String
Dim yyyymm2 As String
Dim shP As Worksheet
Dim shV As Worksheet
Dim shX As Worksheet
Dim c As Range
Dim wkym As String
Dim dic As Object
Dim w As Variant
Dim k As Variant
Dim tmplt(1 To 8)
Set dic = CreateObject("Scripting.Dictionary")
Set shP = Sheets("仕入")
Set shV = Sheets("棚卸")
Set shX = Sheets("Sheet4")
'仕入シートの最終行の年月(今月)
thisYM = shP.Range("B" & Rows.Count).End(xlUp).Value
thisYM = DateSerial(Year(thisYM), Month(thisYM) + 1, 0) '今月末日
lastYM = DateAdd("m", -1, thisYM) '前月末日
yyyymm1 = Format(thisYM, "yyyymm")
yyyymm2 = Format(lastYM, "yyyymm")
'仕入シートから今月データを抜出し、Dicに格納
For Each c In shP.Range("A2", shP.Range("A" & Rows.Count).End(xlUp))
wkym = Format(c.Offset(, 1).Value, "yyyymm")
If wkym = yyyymm1 Then
If Not dic.exists(c.Value) Then
dic(c.Value) = tmplt
End If
w = dic(c.Value)
w(1) = c.Value
w(2) = thisYM
w(4) = w(4) + c.Offset(, 3).Value
w(7) = w(7) + c.Offset(, 4).Value
w(3) = w(7) / w(4)
dic(c.Value) = w
End If
Next
'Dicに棚卸シート情報の反映
For Each c In shV.Range("A2", shV.Range("A" & Rows.Count).End(xlUp))
If dic.exists(c.Value) Then
w = dic(c.Value)
wkym = Format(c.Offset(, 1).Value, "yyyymm")
Select Case wkym
Case yyyymm1 '今月末
w(6) = c.Offset(, 2).Value
Case yyyymm2 '先月末
w(8) = c.Offset(, 2).Value
End Select
w(5) = w(8) + w(4) - w(6)
dic(c.Value) = w
End If
Next
'Sheet4の書き込み
shX.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(dic.Count, 6).Value = _
WorksheetFunction.Transpose(WorksheetFunction.Transpose(dic.items))
End Sub
（β） 2015/03/13(金) 08:56
平均単価表示の集約版もなぜか出庫数量に仕入の数量合計がそのまま
きてしまい
当月末の在庫の残も表示されないのですが私のやりかたが何か悪いのでしょうか…？
仕入シート
商品名 仕入日 単価 数量 金額
みかん 1/1 3.4 50 170
みかん 1/2 3.4 100 340
棚卸シート
商品名 前月単価 棚卸月 数量 在庫金額
みかん 3.4 1/31 40 136
と入力しました。
（ゆ） 2015/03/13(金) 09:58
仕入をしていて棚卸には在庫がゼロになったため記載していない商品の場合
棚卸の在庫があった物だけ記載しているので
sheet4で出庫数量と当月末在庫が空白になってしまうのですが
これはゼロにできたりしますでしょうか？
あとsheet4の平均単価なんですが、仕入が同じ月に同商品２回など仕入れている場合
同じ単価ならそのまま反映
もしどっちかのほうが高かったら高い方を反映などはできますか？
商品名 何月末か 前月棚卸在庫数 D列に作業用列 当月仕入単価 当月仕入数量 当月仕入金額(ここは数式入れようと思います） 当月出庫数 当月末在庫数(前月棚卸在庫+当月仕入数量-出庫金額）
前月の棚卸在庫数と仕入数量を足した数量から出庫数割り出してとのことだったので
お願いしたいです…！
（ゆ） 2015/03/13(金) 10:30
はい。少し時間くださいね。
（β） 2015/03/13(金) 12:13
今月仕入れないものの反映は未対応だけど、とりあえず。
>当月仕入金額(ここは数式入れようと思います）
Sheet4が出来上がった後、手作業でいれるということ？ コードでは、一応、仕入れシートの金額の合計をセットしている。 項目の追加や並び順の変更があった時に、配列の要素番号でコードを書くと、変更作業が大変なので コードの最初の Enum ステートメントで、項目の順序を記述している。 なので、この項目と、この項目を入れ替えたいということがあれば、ここを変えるだけで、コードの中身は変更不要。 （もちろん、【追加】の場合は、コードも追加しなきゃいけないけど）
Enum ListItem
ListStart
'帳票項目
Commodity
EndDate
LastInv
WorkSpace
Price
PurQ
PurA
OutQ
Inventry
'ここまで
ListEnd
End Enum
Sub Test3()
Dim thisYM As Date
Dim lastYM As Date
Dim yyyymm1 As String
Dim yyyymm2 As String
Dim shP As Worksheet
Dim shV As Worksheet
Dim shX As Worksheet
Dim c As Range
Dim wkym As String
Dim dic As Object
Dim w As Variant
Dim k As Variant
Dim tmplt(1 To ListEnd - 1)
Set dic = CreateObject("Scripting.Dictionary")
Set shP = Sheets("仕入")
Set shV = Sheets("棚卸")
Set shX = Sheets("Sheet4")
'仕入シートの最終行の年月(今月)
thisYM = shP.Range("B" & Rows.Count).End(xlUp).Value
thisYM = DateSerial(Year(thisYM), Month(thisYM) + 1, 0) '今月末日
lastYM = DateAdd("m", -1, thisYM) '前月末日
yyyymm1 = Format(thisYM, "yyyymm")
yyyymm2 = Format(lastYM, "yyyymm")
'仕入シートから今月データを抜出し、Dicに格納
For Each c In shP.Range("A2", shP.Range("A" & Rows.Count).End(xlUp))
wkym = Format(c.Offset(, 1).Value, "yyyymm")
If wkym = yyyymm1 Then
If Not dic.exists(c.Value) Then
dic(c.Value) = tmplt
End If
w = dic(c.Value)
w(Commodity) = c.Value
w(EndDate) = thisYM
w(PurQ) = w(PurQ) + c.Offset(, 3).Value
w(PurA) = w(PurA) + c.Offset(, 4).Value
If c.Offset(, 2).Value > w(Price) Then w(Price) = c.Offset(, 2).Value
dic(c.Value) = w
End If
Next
'Dicに棚卸シート情報の反映
For Each c In shV.Range("A2", shV.Range("A" & Rows.Count).End(xlUp))
If dic.exists(c.Value) Then
w = dic(c.Value)
wkym = Format(c.Offset(, 1).Value, "yyyymm")
Select Case wkym
Case yyyymm1 '今月末
w(Inventry) = c.Offset(, 2).Value
Case yyyymm2 '先月末
w(LastInv) = c.Offset(, 2).Value
End Select
If IsEmpty(w(LastInv)) Then w(LastInv) = 0
w(OutQ) = w(LastInv) + w(PurQ) - w(Inventry)
dic(c.Value) = w
End If
Next
'Sheet4の書き込み
shX.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(dic.Count, UBound(tmplt)).Value = _
WorksheetFunction.Transpose(WorksheetFunction.Transpose(dic.items))
End Sub
（β） 2015/03/13(金) 19:50
仕入れがないもので棚卸があるものの反映バージョン。 ここを空白ではなく 0 にしてほしいという要望があれば、言ってください。 なお、通常のデータの下に、表示します。
Private Enum ListItem
ListStart
'帳票項目
commodity
EndDate
LastInv
WorkSpace
Price
PurQ
PurA
OutQ
Inventry
'ここまで
ListEnd
End Enum
Sub Test4()
Dim thisYM As Date
Dim lastYM As Date
Dim yyyymm1 As String
Dim yyyymm2 As String
Dim shP As Worksheet
Dim shV As Worksheet
Dim shX As Worksheet
Dim c As Range
Dim wkym As String
Dim dic As Object
Dim dicX As Object
Dim w As Variant
Dim k As Variant
Dim tmplt(1 To ListEnd - 1)
Set dic = CreateObject("Scripting.Dictionary")
Set dicX = CreateObject("Scripting.Dictionary")
Set shP = Sheets("仕入")
Set shV = Sheets("棚卸")
Set shX = Sheets("Sheet4")
'仕入シートの最終行の年月(今月)
thisYM = shP.Range("B" & Rows.Count).End(xlUp).Value
thisYM = DateSerial(Year(thisYM), Month(thisYM) + 1, 0) '今月末日
lastYM = DateAdd("m", -1, thisYM) '前月末日
yyyymm1 = Format(thisYM, "yyyymm")
yyyymm2 = Format(lastYM, "yyyymm")
'仕入シートから今月データを抜出し、Dicに格納
For Each c In shP.Range("A2", shP.Range("A" & Rows.Count).End(xlUp))
wkym = Format(c.Offset(, 1).Value, "yyyymm")
If wkym = yyyymm1 Then
If Not dic.exists(c.Value) Then
dic(c.Value) = tmplt
End If
w = dic(c.Value)
w(commodity) = c.Value
w(EndDate) = thisYM
w(PurQ) = w(PurQ) + c.Offset(, 3).Value
w(PurA) = w(PurA) + c.Offset(, 4).Value
If c.Offset(, 2).Value > w(Price) Then w(Price) = c.Offset(, 2).Value
dic(c.Value) = w
End If
Next
'Dicに棚卸シート情報の反映
For Each c In shV.Range("A2", shV.Range("A" & Rows.Count).End(xlUp))
wkym = Format(c.Offset(, 1).Value, "yyyymm")
If dic.exists(c.Value) Then
w = dic(c.Value)
Select Case wkym
Case yyyymm1 '今月末
w(Inventry) = c.Offset(, 2).Value
Case yyyymm2 '先月末
w(LastInv) = c.Offset(, 2).Value
End Select
If IsEmpty(w(LastInv)) Then w(LastInv) = 0
w(OutQ) = w(LastInv) + w(PurQ) - w(Inventry)
dic(c.Value) = w
Else
Select Case wkym
Case yyyymm1, yyyymm2 '今月末あるいは先月末
If Not dicX.exists(c.Value) Then
w = tmplt
Else
w = dicX(c.Value)
End If
w(commodity) = c.Value
w(EndDate) = thisYM
If wkym = yyyymm1 Then
w(Inventry) = c.Offset(, 2).Value
Else
w(LastInv) = c.Offset(, 2).Value
End If
w(OutQ) = w(LastInv) - w(Inventry)
dicX(c.Value) = w
End Select
End If
Next
'Sheet4の書き込み
shX.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(dic.Count, UBound(tmplt)).Value = _
WorksheetFunction.Transpose(WorksheetFunction.Transpose(dic.items))
If dicX.Count > 1 Then
shX.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(dic.Count, UBound(tmplt)).Value = _
WorksheetFunction.Transpose(WorksheetFunction.Transpose(dicX.items))
End If
End Sub
（β） 2015/03/13(金) 20:36
（ゆ） 2015/03/15(日) 14:02
試してみた結果なのですが
前月末棚卸在庫があっても0になってしまうのですが
また私のやりかたが悪いのでしょうか…
仕入シート
みかん 12/3 3.4 50
みかん 1/1 3.4 100
棚卸シート
みかん 12/31 40
みかん 1/31 50
sheet４
みかん 1/31 前月末在庫0 作業列 当月仕入単価3.4 当月仕入100 当月仕入金額￥340 出庫50 当月末在庫50
になってしまいます。
本当は
みかん 1/31 前月末在庫40 作業列 仕入単価3.4 当月仕入100 当月仕入金額￥340 出庫50 当月末在庫90
となってほしいのですが…。
（ゆ） 2015/03/16(月) 11:43
1/31 棚卸が 50 だから、当然 当月末在庫は 50 だよね？ なぜ 90 にしたいのかな？
で、上記データで、Test4 を実行すると、こちらでは
商品 月末日 前棚 作業 単価 仕入Ｑ 仕入Ａ 出庫 棚卸 みかん 2015/1/31 40 3.4 100 340 90 50
になるけど、なぜ結果が違うんだろうね。
（β） 2015/03/16(月) 13:36
2015/3/16 11：43のとおりに入力しましたが前月末棚卸が0になってしまいます。
また何かフォーマットがずれてたりするんでしょうか？
仕入シート
商品名 仕入日 仕入単価 数量 仕入金額
棚卸シート
商品名 棚卸月 数量
sheet４
商品名 月 前月末在庫 作業列 当月仕入単価 当月仕入 当月仕入金額 出庫 当月末在庫 作業列 作業列 作業列 作業列 作業列
になってます。
空白ではなく0になるのでどこかズレてそうなんですが....
あと、仕入れなく棚卸しがあると商品名も反映されないのです。
（ゆ） 2015/03/16(月) 14:17
2月の仕入れがなく棚卸しだけ50と入力した場合に
反映されないみたいです。
どちらの月も仕入れがあれば正常に反映されています。
（ゆ） 2015/03/16(月) 15:08
提示してもらっているサンプルデータでテストしているけど、 対象は 1月、だから、前月棚卸は 12月。 なのに、↑の説明で 2月の仕入れとか２月の棚卸がでてくるのは、なぜ？
それと、各シートの12月の日付のところだけど、表示されているものには、年がないね。 そのセルに入っている、実際の日付の年が 【2015】になっていることはない？
で、次に、言葉の定義というか、項目の扱いとしての「当月末在庫」と「当月末棚卸」 こちらでは、同じものだと考えている。
当月末に、みかんが、倉庫に 50個 残っていたとしたら、当月末在庫は、50、当月末棚卸も 当然 50。 たからコード内では、当月末在庫は計算しないで、棚卸シートの値をそのまま使っている。
計算しているのは、前月末在庫と今月末在庫と（仕入れがあれば仕入れと）、これらから計算される、出庫。
何か誤解しているだろうか？
（β） 2015/03/16(月) 16:48
なので上記の説明は1月と2月にしました。
セルの中にある数字はどこも実際は2015年が入っています。
当月末在庫は前月から繰り越されてきた在庫も含めての在庫残です。
今月末の在庫残は棚卸しシートで閲覧できますので
シート4では前月末棚卸し在庫に仕入れ数を足して今月の出庫数を引いたものを
当月末在庫としたかったのですが、説明不足でした...
申し訳ありません。
（ゆ） 2015/03/16(月) 20:18
まず 12月の不具合（？）の件、こちらでは正常な結果であることと、
>12月のデータから1月2月と入力していたら1月と2月の仕入れの部分を入力したら反映されて >2月のみ仕入れをなくし棚卸しだけにした時点で反映されなくなることがわかりました。
この報告から、推測も含めてですが、原因がわかったような気がします。
まず、棚卸シートに12/31 の棚卸を記入する際、セルに 12/31 といれてエンターしていませんか？ 表示書式が mm/dd でしょうから表面上はわかりませんが、こうすると、セルの実際の値は 2015/12/31。 つまり、年を省略した入力をすると、エクセルは、今年扱いをします。
で、仕入れシートのデータは 2015/1月分ですから、処理月は 2015/1、前月は 2014/12 として実行します。 そうすると棚卸シートには 2014/12 の棚卸がありませんので、前月末棚卸はなかったと判断します。
次に 2月の仕入れをいれますと、処理月は 2015/2、前月は 2015/1 。 いずれも、ちゃんと棚卸シートにあるので採用され、思い通りの結果になります。
●実務上も12月棚卸を記入するのは翌年になりますね。入力時には、必ず yyyy/mm/dd としましょう。 これを、いやいや、うっかりと入力することが多いので、yyyy を無視して mm だけで判断するように変えてほしいと 思うかもしれませんが、これについては、お断りします。間違いデータを相手に変なロジックをつくると あとあとの保守面で問題が発生することが多いですから。 むしろ、棚卸シートの月の欄は表示書式を yyyy/mm/dd なり yyyy/mm なりにして、入力時に、間違いに気づきやすいように しておくことがよろしいかと。
次の、当月末在庫ですけど、
>当月末在庫は前月から繰り越されてきた在庫も含めての在庫残です。
これは、至極当然のことです。前月末の残りと今月仕入れたものを出庫していきますので その結果、月末に残った分が在庫残です。
で、それは計算しなくても、棚卸シートに数値が記載されていますねと申し上げています。
そもそも、出庫は(ゆ)さんの提示要件にもあったように
出庫=前月末棚卸+今月仕入-今月末棚卸 ですよね。
この数式の今月末棚卸を左辺にもっていき、左辺の出庫を右辺にもっていくと
今月末棚卸 = 前月末棚卸 + 今月仕入れ - 出庫
すでに（というのも変ですが）
>シート4では前月末棚卸し在庫に仕入れ数を足して今月の出庫数を引いたものを当月末在庫したかった
これは計算しなくても棚卸シートにある数字だということはわかりますよね。
●で、1月、2月のデータでの結果はいかがでしたか？ コードのロジックは変えていませんが、仕入れ数量と仕入れ金額を ゼロ表示したものを以下に。 このコードで 1月、2月のデータを処理して、結果を教えてください。
Sub Test5()
Dim thisYM As Date
Dim lastYM As Date
Dim yyyymm1 As String
Dim yyyymm2 As String
Dim shP As Worksheet
Dim shV As Worksheet
Dim shX As Worksheet
Dim c As Range
Dim wkym As String
Dim dic As Object
Dim dicX As Object
Dim w As Variant
Dim k As Variant
Dim tmplt(1 To ListEnd - 1)
Set dic = CreateObject("Scripting.Dictionary")
Set dicX = CreateObject("Scripting.Dictionary")
Set shP = Sheets("仕入")
Set shV = Sheets("棚卸")
Set shX = Sheets("Sheet4")
'仕入シートの最終行の年月(今月)
thisYM = shP.Range("B" & Rows.Count).End(xlUp).Value
thisYM = DateSerial(Year(thisYM), Month(thisYM) + 1, 0) '今月末日
lastYM = DateAdd("m", -1, thisYM) '前月末日
yyyymm1 = Format(thisYM, "yyyymm")
yyyymm2 = Format(lastYM, "yyyymm")
'仕入シートから今月データを抜出し、Dicに格納
For Each c In shP.Range("A2", shP.Range("A" & Rows.Count).End(xlUp))
wkym = Format(c.Offset(, 1).Value, "yyyymm")
If wkym = yyyymm1 Then
If Not dic.exists(c.Value) Then
dic(c.Value) = tmplt
End If
w = dic(c.Value)
w(commodity) = c.Value
w(EndDate) = thisYM
w(PurQ) = w(PurQ) + c.Offset(, 3).Value
w(PurA) = w(PurA) + c.Offset(, 4).Value
If c.Offset(, 2).Value > w(Price) Then w(Price) = c.Offset(, 2).Value
dic(c.Value) = w
End If
Next
'Dicに棚卸シート情報の反映
For Each c In shV.Range("A2", shV.Range("A" & Rows.Count).End(xlUp))
wkym = Format(c.Offset(, 1).Value, "yyyymm")
If dic.exists(c.Value) Then
w = dic(c.Value)
Select Case wkym
Case yyyymm1 '今月末
w(Inventry) = c.Offset(, 2).Value
Case yyyymm2 '先月末
w(LastInv) = c.Offset(, 2).Value
End Select
If IsEmpty(w(LastInv)) Then w(LastInv) = 0
w(outQ) = w(LastInv) + w(PurQ) - w(Inventry)
dic(c.Value) = w
Else
Select Case wkym
Case yyyymm1, yyyymm2 '今月末あるいは先月末
If Not dicX.exists(c.Value) Then
w = tmplt
w(PurQ) = 0
w(PurA) = 0
'★もし単価欄も 0 表示したいなら w(Price)=0
Else
w = dicX(c.Value)
End If
w(commodity) = c.Value
w(EndDate) = thisYM
If wkym = yyyymm1 Then
w(Inventry) = c.Offset(, 2).Value
Else
w(LastInv) = c.Offset(, 2).Value
End If
w(outQ) = w(LastInv) - w(Inventry)
dicX(c.Value) = w
End Select
End If
Next
'Sheet4の書き込み
shX.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(dic.Count, UBound(tmplt)).Value = _
WorksheetFunction.Transpose(WorksheetFunction.Transpose(dic.items))
If dicX.Count > 0 Then
shX.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(dic.Count, UBound(tmplt)).Value = _
WorksheetFunction.Transpose(WorksheetFunction.Transpose(dicX.items))
End If
End Sub
（β） 2015/03/16(月) 21:16
表示形式を◯月◯日にしていたので、そのまま打っていたので
2015/12になっていたみたいなので年から入力するようにして
また結果ご報告します！
（ゆ） 2015/03/16(月) 22:20
（ゆ） 2015/03/17(火) 10:31
>2月のみ仕入がなく、棚卸のみ記載のバージョンでは1月末の結果しか反映されません。
もう少し正確に説明してください。
みかん 1月末棚卸 100 みかん 2月末棚卸 60
処理自体は 2月度、ただし、みかんの2月仕入れなし。
こういう状態ですか？
この場合、みかんの2月出庫は 40、2月末棚卸は 60 ですけど、そうなっていないということですか？
（β） 2015/03/17(火) 10:37
みかん 1/1 3.4 100 \340
みかん 2/1 3.4 100 \340
棚卸シート
みかん 1月末 50
みかん 2月末 50
sheet4
みかん 1月末 前月末在庫0 作業列 当月仕入単価3.4 当月仕入100 仕入金額340 出庫50 当月末在庫50
みかん 2月末 前月末在庫50 作業列 当月仕入単価3.4 当月仕入100 仕入金額340 出庫100 当月末在庫50
と正常になるのですが
仕入シート
みかん 1/1 100 \340
棚卸シート
みかん 1月末 50
みかん 2月末 50
sheet4
みかん 1月末 前月末在庫0 作業列 当月仕入単価3.4 当月仕入100 仕入金額340 出庫50 当月末在庫50
がなんども反映される結果になり
本来はsheet4に
みかん 1月末 前月末在庫0 作業列 当月仕入単価3.4 当月仕入100 仕入金額340 出庫50 当月末在庫50
みかん 2月末 前月末在庫50 作業列 当月仕入単価0 当月仕入0 仕入金額0 出庫50 当月末在庫0
のようになってほしいのですが
2月にも仕入れをした場合のみしか正常に反映されず
2月に仕入なかったパターンでは1月末の結果しか反映されないようになっております。
（ゆ） 2015/03/17(火) 10:58
まず、いくつか（コードではなく）明確にしておきたいことがあります。
以前のSheet3への展開もそうでしたが、Sheet4の展開も、 ・当月分の動き（仕入れ）を抽出してSheet4に「追加」 ・当月分に動き（仕入れ）がなかった場合も、当月末棚卸があれば、前月末棚卸も参照して、当月出庫を計算したものをSheet4に「追加」
こうなっているということは申し上げていますね。
>がなんども反映される結果になり
何度も処理すれば、何度も書き込まれますよ。 もちろん、このあたりは、この先、処理前に当該月のデータをマクロで削除してから転記という「置換」方式に 変更していくのは、たやすいことですが、今は、現行の構えが、そうなっているということは、頭にいれておいてくださいね。 （このことは、前のほうで説明しているはずです）
次に。
↑で提示のデータに対する処理ですが、この処理は何月度の処理なのかは、仕入れシートの一番下（どの商品かはわかりませんが） のデータの仕入れ日の月で判断するということを決めましたね。 なので、たとえば、ぶどうが最後にあって、その仕入れ日が 1月なら 1月度、2月なら 2月度の処理。
で、おそらく2月度の処理なんでしょうね。
その場合、繰り返しになりますが処理後の Sheet4 には みかんについては、2月末として、前月末 50 今月末 50 という１行が【追加】されるだけですよ。 あいかわらず、2月末のみかんの出庫が 50 で 在庫が 0 とレスしておられる、その根拠もわかりませんし
>みかん 1月末 ・・・のようになってほしいのですが
の部分、1月末の行は、2月度処理では作成しませんよ。
たとえば、Sheet4 は、まっさらな形から、棚卸シートと、仕入シートの過去からの内容をもとに【作り直す】という仕様であれば それは、また別の方式になりますし、コードも、それなりに書けばいいことですが。
それでも、みかん の 2月の出庫は 0 ですし、2月末の在庫は 50 ですよ。
（β） 2015/03/17(火) 11:37
1つの商品でやっていたので仕入が無い月におかしくなっていました。
本当に感謝しています！ありがとうございます！
（ゆ） 2015/03/17(火) 11:55
よかったです。
この仕入シートの最終データから自動的に処理月を決定するのは便利なようで 不都合もあり得ます。たとえば、極端にいって、ある月に仕入が全くなく、でも出庫はあった。 この場合、その月のデータが仕入シートにあにので、処理月は自動的に前月になってしまう。
ほとんどの場合、月初に前月のデータを処理すると思われますので、初期値を前月にして メッセージを出して、操作者に年月を確認させる、違っていれば指定可能にしておくといった構えが いいかもしれません。 たとえば今が3月なら、初期値として 2015年2月 とだして、そのままＯＫ，あるいは打ち直してＯＫ。 （あるいはキャンセル）
打ち直す場合は 2015年3月 の形でもいいですし、2015/3 の形でもＯＫ。
現在のコードの
'仕入シートの最終行の年月(今月)
thisYM = shP.Range("B" & Rows.Count).End(xlUp).Value
これをなくして、かわりに
Dim d As Variant
d = Application.InputBox("処理月をいれてください", Default:=Format(DateAdd("m", -1, Date), "yyyy年m月"), Type:=1)
If d = False Then Exit Sub 'キャンセルボタン
thisYM = d '処理月
こうしておくほうがいいかもしれませんよ。
（β） 2015/03/17(火) 12:48
（ゆ） 2015/03/19(木) 14:27
はい、見てますよ。 今、外出から戻ってきたところですので、ちょっと時間くださいね。
（β） 2015/03/19(木) 15:13
手入力で入れたデータは、仕入シートですか。棚卸シートですか。それともSheet4 ですか？
>中には1月度は反映されていても2月度は反映されない商品(商品名自体がでてきません。前月末棚卸しから以下の項目も)
これについても、具体的によくわかりません。Sheet4に表示はされたけど、完全な空白行？ あるいは、どこかの項目は入っているけど、どこかの項目が空白？
また、ある商品は 2月度も反映されたけど、ある商品は2月度が反映しない？
OKだったものは 棚卸シートがこうこう、仕入シートがこうこう、Sheet4がこうこう。 NGだったものは、棚卸シートがこうこう、仕入シートがこうこう、Sheet4がこうこう。
具体的に教えてもらえますか？
それと、やはり、仕入れシートの最後のデータから自動的に基準月を決定するのはよくないですね。 操作者は2月度をやっているつもりが1月度の処理になってしまっているとか、そういうことがしょっちゅう 発生しそうです。
追記 16:05
>前に書いたコードなのですが当月末棚卸在庫、前月末在庫、出庫が0の場合、現時点では空白となるように
アップした Test5でそうなるということですか？ もし、そうでないなら「前に書いたコード」をアップしてください。
それと、前月末在庫も（データとして）なく、当月末在庫も（データとしてなく）なく かつ、出庫ゼロということですから当月仕入もないわけですよね？ これって、Sheet4への表示対象にはなりませんが？
（β） 2015/03/19(木) 16:00
２月度のほかの商品は載っていても中には載っていない商品もあるという意味です。
Test5で前月末在庫と当月末在庫、出庫が０表示になっていませんでした
前のコード試してみます。
（ゆ） 2015/03/20(金) 09:46
数値欄がゼロではなく空白になっているものを、ゼロ表示にするのはいとも簡単にできますので、最後に対応します。
それより、そちらで発生して困っていることを、こちらでも再現して、コードの不具合を修正する必要がありますね。
正直言って
>仕入シートの最終行は２月になっており >棚卸シートにはその商品の商品名と２月末ということと数量をのせましたが >sheet４には空白行とかの前に他の商品は入っていても >入らない商品は存在すらしてません。
これだと、雲をつかむような話なんです。具体的にお願いします。 （ゆ） 2015/03/17(火) 10:58 でアップいただいたような具体的な形で、それを、こちらがシートにコピペして試せるような そんな形で教えてもらえませんか？
（β） 2015/03/20(金) 11:40
今までの棚卸をやっていた分は古いブックにあり
今回作って頂いたコードで今年の１月から仕入や棚卸に載せていきました。
なので１月に仕入で存在しない商品も棚卸シートにのせてある状態なのですが
はじめの月だけsheet４の前月末棚卸在庫から手入力で修正していき
２月から正常に動くようにしたかったのですが
仕入シート
商品1 １月の日付け 数量
商品2
商品3
商品4
などずらっとのせていき
棚卸シートに
商品１
商品２
商品３
商品４
日用品１
日用品２
日用品３
とのせました。
sheet４では商品しかででこず日用品は反映されませんでした。
なので手入力で直すにも日用品自体が存在しないので
このまま日用品名から手入力で入力
２月をまた上記のように商品と日用品をのせ実行
日用品が出てくる商品もあれば出てこない商品もある形になってしまいました。
ちなみになんですが１月分を空白のものも0に今手入力で入力したりしてたのですが
これは手入力すると２月分が正しく反映されなくなってしまうせいなのでしょうか？
（ゆ） 2015/03/20(金) 13:01
棚卸ｓｈｅｅｔ
商品１に関しての入力なし
ｓｈｅｅｔ４で
前月末在庫0 作業列 仕入単価 当月仕入50 仕入金額 出庫が空白 当月末在庫（空白）
になってしまっています。
出庫は空白でなく５０でなくてはならないはずなのですが。。。
（ゆ） 2015/03/20(金) 13:24
以下のデータの 各数量を教えてください
仕入シート 商品1 １月の日付け 数量 商品2 商品3 商品4
以下のデータのB列の日付と C列の数量を教えてください。
棚卸シートに 商品１ 商品２ 商品３ 商品４ 日用品１ 日用品２ 日用品３
（β） 2015/03/20(金) 18:00
速報！！
アップしたコードにとんでもない構造欠陥があるのがわかりました。 頭の中では仕入れのみ、あるいは棚卸のみと、そう思っていたんですが、コード実態としては 最初の先入れ先出仕入れ表にあしをひっぱられて、一方のみの場合に、商品が空白だったり、出庫が計算されなかったり いろんな不具合がでてしまいます。
すんませ～ん。
改訂版アップしますのでちょっと待っててください。
（β） 2015/03/20(金) 18:43
改訂版です。
これで、大丈夫だと思うなぁ・・・・・・？？ 追記 21:10 もう dicX は使っていないので削除。（あっても害にはならないけど、後から見るとややこしいので）
Sub Test6()
Dim thisYM As Date
Dim lastYM As Date
Dim yyyymm1 As String
Dim yyyymm2 As String
Dim shP As Worksheet
Dim shV As Worksheet
Dim shX As Worksheet
Dim c As Range
Dim wkym As String
Dim dic As Object
Dim w As Variant
Dim k As Variant
Dim tmplt(1 To ListEnd - 1)
Set dic = CreateObject("Scripting.Dictionary")
Set shP = Sheets("仕入")
Set shV = Sheets("棚卸")
Set shX = Sheets("Sheet4")
'仕入シートの最終行の年月(今月)
thisYM = shP.Range("B" & Rows.Count).End(xlUp).Value
thisYM = DateSerial(Year(thisYM), Month(thisYM) + 1, 0) '今月末日
lastYM = DateAdd("m", -1, thisYM) '前月末日
yyyymm1 = Format(thisYM, "yyyymm")
yyyymm2 = Format(lastYM, "yyyymm")
'仕入シートから今月データを抜出し、Dicに格納
For Each c In shP.Range("A2", shP.Range("A" & Rows.Count).End(xlUp))
wkym = Format(c.Offset(, 1).Value, "yyyymm")
If wkym = yyyymm1 Then
If Not dic.exists(c.Value) Then dic(c.Value) = tmplt
w = dic(c.Value)
w(commodity) = c.Value
w(EndDate) = thisYM
w(PurQ) = w(PurQ) + c.Offset(, 3).Value
w(PurA) = w(PurA) + c.Offset(, 4).Value
If c.Offset(, 2).Value > w(Price) Then w(Price) = c.Offset(, 2).Value
dic(c.Value) = w
End If
Next
'Dicに棚卸シート情報の反映
For Each c In shV.Range("A2", shV.Range("A" & Rows.Count).End(xlUp))
wkym = Format(c.Offset(, 1).Value, "yyyymm")
Select Case wkym
Case yyyymm1, yyyymm2 '今月末あるいは先月末
If Not dic.exists(c.Value) Then dic(c.Value) = tmplt
w = dic(c.Value)
Select Case wkym
Case yyyymm1 '今月末
w(Inventry) = c.Offset(, 2).Value
Case Else '先月末
w(Lastinv) = c.Offset(, 2).Value
End Select
w(commodity) = c.Value
w(EndDate) = thisYM
dic(c.Value) = w
End Select
Next
'Dic内 出庫計算 と数値項目 空白 をゼロに。
For Each k In dic
w = dic(k)
w(outQ) = w(Lastinv) + w(PurQ) - w(Inventry)
If IsEmpty(w(Lastinv)) Then w(Lastinv) = 0
If IsEmpty(w(PurQ)) Then w(PurQ) = 0
If IsEmpty(w(PurA)) Then w(PurA) = 0
If IsEmpty(w(outQ)) Then w(outQ) = 0
If IsEmpty(w(Inventry)) Then w(Inventry) = 0
dic(k) = w
Next
'Sheet4の書き込み
shX.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(dic.Count, UBound(tmplt)).Value = _
WorksheetFunction.Transpose(WorksheetFunction.Transpose(dic.items))
End Sub
（β） 2015/03/20(金) 19:26
Sheet4は、作表しているだけで、Sheet4にすでにある値をみて、どうこうということは一切してませんよね？ 見ているのは仕入シートと棚卸シートです。
新しい年度の仕入シートは当然、2016年1月の仕入れデータを入れるので問題ないですね。 もんだいは、棚卸シートでしょう。2016年1月の処理では、棚卸シートの 2015年12月を見に行きますから。 これがないと前月末在庫がゼロになりますからね。
ですから繰り越し処理をするとすれば、前の年の棚卸シートの最終月（たぶん12月）のデータを新しい年の ブックの棚卸シートにコピペすることになります。
これぐらいは、手作業でコピペのほうが、むしろ簡単だと思いますが、ＶＢＡ処理をお望みですか？
（β） 2015/03/25(水) 12:08
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.