[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『賞味期限付き在庫管理。レイアウト相談』（うめ）
↓前回の質問『賞味期限を早い順に処理する在庫管理表を作りたい。』です。↓
http://www.excel.studio-kazu.jp/kw/20150406105928.html
初めに賞味期限付き在庫管理表を作るようになった経緯を簡単に書きます。
1.最近自社加工商品を販売するようになった。
2.それに伴い在庫管理表を関数で作成。
3.商品の管理をよりわかりやすくするために、賞味期限で各商品を管理。
4.VBAが必要と知り現在質問に至っています。
sheet1(製品在庫)
A B C D E F
1 商品コード 商品名 総在庫 在庫 賞味期限 備考
2 a-1 梅干し 50 50 20150401
3 a-1 梅干し 70 20 20150501
4 b-1 梅酒 30 30 20150515
･
･
やりたいこと
・賞味期限で古い順に各行を並べ替える。
・賞味期限が現在の日付から3ヶ月前、2ヶ月前で文字の色を変化。
・在庫が0になった商品の行は削除し、上につめる。
sheet2(入出庫表)
A B C D E F G H I
1 商品コード 商品名 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 備考
2 a-1 梅干し 20150105 A社 50 20150401
3 b-1 梅酒 20150215 B社 20
･
･
やりたいこと
・入庫した場合、sheet1の賞味期限が同じであれば同じ行に在庫を追加。賞味期限が違えば、新しく行を追加する。
・出庫した場合、sheet1の賞味期限が古い在庫順に在庫を減らす。
sheet3(入出庫履歴)
shee2(入出庫表)を転記
sheet4(商品コード)
A B C D E F G
1 商品コード 商品名 価格 分類 割引率
2 a-1 梅干し 1000 蜂蜜 20%
3 b-1 梅酒 1500 お酒 30%
･
･
今考えている疑問
・バカバカしい疑問ですが、入出庫表では、一度処理したデータは、何度も処理されないか不安です。
・将来的には、売上表とリンクさせるのですが、入出庫表はわけたほうが管理はやりやすいですか？
賞味期限が、3ヶ月前、2ヶ月前ごとに割り引き率を設定し、売上表に反映したいのですが・・・
お力添えの程、よろしくお願いします。
＜ 使用 Excel：Excel2003、使用 OS：Windows8 ＞
＞・・、入出庫表では、一度処理したデータは、何度も処理されないか不安です。 入出庫表に「在庫反映済」欄を設けて、処理が済んだ行に「反映した日時」を自動入力するようにすれば たとえ入出庫表にデータが残っていても重複処理は避けられると思います。
まぁ、反映後、自動クリアするようにしてしまえば、何の不安もないですけどね。
逆に言うと、クリアすることで何か不都合が生じることがあるのかどうか、 つまり、データを残して置くとなにかメリットがあるのかどうか？ですね。
(えーと、さっきの入荷は処理したっけー、なんてことになると、残っていた方がいいような気もします。 もっとも、クリアするときは入出庫履歴に転記される仕掛けになるでしょうから、 そっちの帳簿を見れば分かるから、クリアしても大丈夫とも云えそう）
＞・将来的には、売上表とリンクさせるのですが、入出庫表はわけたほうが管理はやりやすいですか？ ＞ 賞味期限が、3ヶ月前、2ヶ月前ごとに割り引き率を設定し、売上表に反映したいのですが・・・ 売上表のイメージがアップされていないので具体的なことは判断できません。 そう難しいことでは無かろうと思います。
いずれにしても、これまでのご説明にあるシートに「日付欄」がないのは問題だと思います。 日付がないと今後何をするにも困ることになると思います。
入出庫表は分けた方がいいかどうかについては、 何故一緒の案が示されたのかをご説明頂く必要があると思います。
同じ商品コードだけなら分かりますが、全然別の商品の入出庫情報を同じ表に書き込んでいく構想って、 そうしたい何かありますか？
こちらは、現場の人がどんなことを考え、どんな手順で処理しているのか分からないので、 そちらの説明がない限り判断できないです。
（半平太） 2015/04/07(火) 12:17
入庫と出庫を別の表にする。
出庫データを入庫に反映させ、在庫のあるデータを在庫表に抽出する。
入庫IDなり、ロットなりを設定しておかれたほうがよろしいかと。
一度処理したデータは、何度も処理されないか 出庫のタイミングにもよるかと。つまり、入庫した量を一度に出庫するのか、分けて出庫するのか。
売上表とリンクさせるのですが、入出庫表はわけたほうが管理はやりやすいですか 売上表の有り無しと関係あるのですか。
売り上げと出庫の関連も不明です。
（くろにくる） 2015/04/07(火) 12:25
あとは伝票番号も一意の値で持っているといいかもしれませんね。 いつ処理したものか後追いできますし。
入庫IDとかロットの問題とかは、同じ商品でも別々の場所で製造している場合や 何らかのトラブルで、15時までの商品は出荷OKだけど、16時以降は検査結果が出てからとか 出庫の段階で同じ賞味期限でも管理が別のものが後々必要になると思います。
ここまで来ると、アクセスのほうが簡単な気が・・・
（稲葉） 2015/04/07(火) 12:40
＞賞味期限が、3ヶ月前、2ヶ月前ごとに割り引き率を設定し、売上表に反映したいのですが・・・
これが、先方の注文でわざと割引率が低くても、賞味期限が長い方がいいと云ってきた場合、 単に「出庫した場合、sheet1の賞味期限が古い在庫順に在庫を減らす」と言う様なロジックでは 対応できないことになりませんか？
また、入庫と出庫の一日の前後関係によっては、 一日を通してみれば、注文通り新しい商品が出庫できるはずが、 実際は、新しい商品はその日の終り頃に入荷しており、出荷時刻には まだ古い商品しかないなんてことも考えられます。
そうなると、入庫と出庫データの処理の順番をどう決定していくか、 キッチリ詰める必要があるかも知れません。
・・・考え過ぎかな？
（半平太） 2015/04/07(火) 12:46
＞・・・考え過ぎかな？ いやー質問者さんがやろうとしていることは、そのくらいのレベルで考えないと どこかで躓くと思いますよ。 単純に 「FIFOで在庫が必ず減っていくので、とにかく品番と数量を入れたら賞味期限の古いものから減らしてくれ」 ってオーダーなら考えなくてもいいことでしょうけど。 最初にぶらっとさんが提案されたコードはその通りでしたし。
（稲葉） 2015/04/07(火) 13:29
＞売上表のイメージがアップされていないので具体的なことは判断できません。
今考えているのは、下記の表です。ただ、割引率やセット販売欄などを追加していくと考えています。現在、レイアウトを模索中です・・・
A B C D E F G H I J K
1 商品コード 商品名 売上日 月度 納入先 分類 単価 消費税 税込 数量 金額
＞いずれにしても、これまでのご説明にあるシートに「日付欄」がないのは問題だと思います。
＞日付がないと今後何をするにも困ることになると思います。
「日付欄」というのは、各シートの更新した日時ということでしょうか。
入出庫表の入出庫日とはまた別物でしょうか。
＞入出庫表は分けた方がいいかどうかについては、
＞何故一緒の案が示されたのかをご説明頂く必要があると思います。
説明不足で申し訳ありません。
現在、入出庫表で入庫と出庫を同じ表で管理しています。
なので、今までと同じレイアウトにした方が、使いやすいと思ったので入出庫表にしました。
＞同じ商品コードだけなら分かりますが、全然別の商品の入出庫情報を同じ表に書き込んでいく構想って、
＞そうしたい何かありますか？
特に何も考えずにネットで調べた表をもとにレイアウトを考えていました。
ただ、現在使っている入出庫表では、入庫と出庫を別シートに別けた方が、入力の間違いが少なくなるのではないかと思っています。
入庫数と出庫数の欄を間違えて入力することがある気がしています。
＞これが、先方の注文でわざと割引率が低くても、賞味期限が長い方がいいと云ってきた場合、
＞単に「出庫した場合、sheet1の賞味期限が古い在庫順に在庫を減らす」と言う様なロジックでは
＞対応できないことになりませんか？
仰る通りでした。考えていませんでした。現在、割引している商品と割引していない商品を同事に販売することになるので、在庫がおかしいことになります。
なので、入庫ID(ロット)を各商品に設定し、出荷した商品は賞味期限の処理ではなく、入荷IDでの処理でした方が、よいということですよね？
＞実際は、新しい商品はその日の終り頃に入荷しており、出荷時刻には
＞まだ古い商品しかないなんてことも考えられます。
こちらに関しては現在問題はないと考えています。
生産は自社で完全にコントロールできていますので。ただ、出荷量の増加、大手スーパーと契約となると、この問題にも対応していかなければならないと考えています・・・
くろにくるさん
＞入庫IDなり、ロットなりを設定しておかれたほうがよろしいかと。
仰るとおりです。入庫日＋商品コードで設定したいと考えています。
＞売り上げと出庫の関連も不明です。
出荷した商品を売上表に反映し、売上を把握したいのです。現在手作業なので・・・
稲葉さん
＞あとは伝票番号も一意の値で持っているといいかもしれませんね。
＞いつ処理したものか後追いできますし。
伝票番号＝入庫IDで大丈夫でしょうか。また別に設定したほうがよいのでしょうか。
＞ここまで来ると、アクセスのほうが簡単な気が・・・
自分自身、ここまで複雑になるとは考えていませんでした。
未熟さを身に染みています。ただ、アクセスは使ったことがないので想像ができません。
エクセルでは限界なのでしょうか・・・
頭がパンク状態ですので、追加でやりたいことなどまとめます。
・入庫ID(入庫日+商品コード)を設定。
・在庫の増減は、賞味期限ではなく、入庫IDで行う。これによって、「古い在庫順に在庫 を減らす」のみの対応ではなくなる。
・在庫表、入出庫表に引当数、発注数を設定し、正確な在庫の管理(未定)。現在は、必要ではないような気がするのですが、将来的には必要な気がします・・・
現場作業について補足します。
・完全自社生産、加工商品を販売している。
・工場は1つなので、生産には融通が利く。
入庫IDを追加しました
sheet1(製品在庫)
A B C D E F G
1 商品コード 商品名 入庫ID 総在庫 在庫 賞味期限 備考
2 a-1 梅干し 20150101a-1 50 50 20150401
3 a-1 梅干し 20150201a-1 70 20 20150501
4 b-1 梅酒 20150215b-1 30 30 20150515
･
sheet2(入出庫表)
A B C D E F G H I J
1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 備考
2 a-1 梅干し 20150105a-1 20150105 A社 50 20150401
3 b-1 梅酒 20150215b-1 20150215 B社 20
･
･
返信が遅くなりました。申し訳ありません。
以上、追加点なども踏まえてよろしくお願いします。
（うめ） 2015/04/07(火) 15:31
伝票番号は伝票を切った日で入庫IDとは別ものです。 1つでも出庫・入庫があれば、伝票が発行されますので、入庫時点で変更されない入庫IDでは対応できませんよね？
あと入庫IDも現物と照合できなければ意味がありません。 生産した商品だけの在庫管理だと思っていましたが、仕入先の欄があるところを見ると、 原料の管理もされたいということですよね？
そうなると、原料には原料メーカーのロットが記載されていると思います。 暗号型、賞味期限型、製造日型等さまざまな形で入ってきますので、それらを「外部ロット」として、 管理することで「現物とシステムが一致しているか」を見る目安になると思います。
入庫ID（入庫日）は、「内部ロット」として、自社でFIFOの目安に使えばよいと思います。 また同じ日付で別の外部ロットの原料が入ることも想定されますので、 提示の入庫IDでは管理できませんから、入庫日の他に、枝番号もつけてはどうでしょう？（20150401-01等）
また入出庫表と在庫表に備考がありますが、入出庫表の備考と在庫表の備考は結びつかないことは理解できていますよね？ 在庫表1に対して履歴はnの関係ですので、数値のように足していくわけにはいきませんから。
食い物作るってことは人の命預かるのと同じなんですから、 システムに金を掛けるって安いと思いませんか？ 小売ならともかく、メーカーにはちゃんとしてもらいたいです。 （稲葉） 2015/04/07(火) 18:07
＞ 「日付欄」というのは、各シートの更新した日時ということでしょうか。 ＞ 入出庫表の入出庫日とはまた別物でしょうか。 済みません。見落しました。m(__)m
＞なので、今までと同じレイアウトにした方が、使いやすいと思ったので入出庫表にしました。 その方が慣れていて、使い易いと云うことなら了解です。
＞ただ、現在使っている入出庫表では、入庫と出庫を別シートに別けた方が、 ＞入力の間違いが少なくなるのではないかと思っています。 ＞ 入庫数と出庫数の欄を間違えて入力することがある気がしています。 条件付書式で、「納入先が入っているのに出庫欄に数値が入力されたら、 当該セルを赤くする」などとすれば、そのミスは防げると思います。
＞>実際は、新しい商品はその日の終り頃に入荷しており、出荷時刻には ＞>まだ古い商品しかないなんてことも考えられます。 ＞こちらに関しては現在問題はないと考えています。 ＞ 生産は自社で完全にコントロールできていますので。 ＞ただ、出荷量の増加、大手スーパーと契約となると、 ＞この問題にも対応していかなければならないと考えています・・・ 今回は、どうするお積りですか？ 将来のことも考えるなら、それなりの検討が必要ですけど、こちらには、何の予備知識もないので、 そちらで如何にすべきかまとめて頂かないと、手が付けられません。
＞・入庫ID(入庫日+商品コード)を設定。 ・在庫の増減は、賞味期限ではなく、入庫IDで行う。 ＞これによって、「古い在庫順に在庫 を減らす」のみの対応ではなくなる。 入庫IDって、事務方の人がチャンと入れられるのですか？
まぁ、出庫データの入力時、商品コードを入力した途端に残高があるロットが K列辺りに一覧として表示され、選びたいロットのセルを右クリックすると 入庫ID欄に自動入力になる、なんて仕掛けを作れば問題はないかも知れません。
・・にしても、少数のロットを足し合わせて、一つの出荷にする場合、 どんな出庫データを入力することになるんですか？
（半平太） 2015/04/07(火) 22:28
＞あと入庫IDも現物と照合できなければ意味がありません。
＞生産した商品だけの在庫管理だと思っていましたが、仕入先の欄があるところを見ると、
＞原料の管理もされたいということですよね？
製品在庫のみ管理したいと考えています。
加工対象の原料(野菜、魚など)は自社生産です。加工のための材料(塩、砂糖など)は仕入れるのですが・・・
これらも管理して、どの製品にどの材料を入れたのかを管理するのは、将来的に必要だと考えています。
現在は、管理に問題がでることはないと考えているので、製品管理に力を注ぎたいと思っています。
そう考えれば、仕入先欄は必要ないですよね・・・
製品入庫は自社のみですから。前回の入出庫表にあったので、考えずに入れていました。
＞また入出庫表と在庫表に備考がありますが、入出庫表の備考と在庫表の備考は結びつかないことは理解できていますよね？
＞在庫表1に対して履歴はnの関係ですので、数値のように足していくわけにはいきませんから。
はい、大丈夫です。
＞食い物作るってことは人の命預かるのと同じなんですから、
＞システムに金を掛けるって安いと思いませんか？
＞小売ならともかく、メーカーにはちゃんとしてもらいたいです。
仰る通りです。お客さんに何かあってからでは遅いですよね・・・
システムにお金をかけるのは、安いと思います。
今でもいくつもの問題点がありますので、それをなくして完璧に近い形で手に入れることができるのですから。
ただ、ここからは自分の我儘になってしまいますが、こうしたやり取りを通して、生産管理の知識や認識、エクセルの知識を深められると確信しています。
そしてより一層お客さんによいサービスを提供できるのではないかと、考えています。ただの自己満足な気もしますが、システムが立派でも扱う人間が扱えるレベルになければ宝の持ち腐れですし・・・
稲葉さんとのやり取りで、自分の認識の甘さ、知識の浅さ思い知りました。ありがとうございます。
教えていただいたことは、印刷して今後のテーマとして忘れないようにノートに書き留めます。
（うめ） 2015/04/07(火) 23:12
＞今回は、どうするお積りですか？
＞将来のことも考えるなら、それなりの検討が必要ですけど、こちらには、何の予備知識もないので、
＞そちらで如何にすべきかまとめて頂かないと、手が付けられません。
対応できるようにしたいと思います。各シートのレイアウトを変更させてください。
sheet1(製品在庫)
A B C D E F G H I J K L M
1 商品コード 商品名 入庫ID 発注点 総在庫 在庫 引当数 発注数 有効在庫数 有効残数 賞味期限 発注要否 備考
2 a-1 梅干し 20150101a-1 50 50 20150401
3 a-1 梅干し 20150201a-1 20 70 20 20150501
4 b-1 梅酒 20150215b-1 30 10 5 5 10 5 15 20150515
・
・
追加点
1.発注点、発注要否・・・発注点の数字を有効残数が下回ると発注要否に[要]がでる
2.引当数・・・予約注文数、出庫予定数
3.発注数・・・入庫予定数
4.有効在庫数・・・総在庫-引当数(使用可能な在庫数)
5.有効残数・・・(総在庫+発注数)-引当数(発注要否)
sheet2(入出庫表)
A B C D E F G H I J K L
1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 発注数 備考
2 a-1 梅干し 20150105a-1 20150105 A社 50 20150401
3 b-1 梅酒 20150215b-1 20150215 B社 20
4 a-1 梅干し 20150220a-1 20150220 C社 60
･
･
追加点
・引当数、発注数に数字を入力している行のクリアはやめてほしいです。入庫、出庫した際に、引当数または発注数の数字を消して、入庫または出庫に数字を書き換えたらクリアになるようにしたいです。
また引当数、発注数は在庫表に反映し、書き換え後は入庫、出庫扱いにしたいのですが、できるのでしょうか。
＞入庫IDって、事務方の人がチャンと入れられるのですか？
現在主に扱っているのは、私ともう1人の現場作業員の2名です。
事務の人が触ることはあまりないので、心配ないかと思います。
＞まぁ、出庫データの入力時、商品コードを入力した途端に残高があるロットが
＞K列辺りに一覧として表示され、選びたいロットのセルを右クリックすると
＞入庫ID欄に自動入力になる、なんて仕掛けを作れば問題はないかも知れません。
すごく便利そうですね！やってみたいです。
＞・・にしても、少数のロットを足し合わせて、一つの出荷にする場合、
＞どんな出庫データを入力することになるんですか？
盲点でした・・・これは思っていた以上に複雑ですね。
入庫IDで処理すると、足りない場合の処理が難しいですね。
すみませんが、明日上司と相談させてください。
（うめ） 2015/04/08(水) 00:09
このやり取りをプロと対面で話し合ってシステムを作っていくんですよ。 結局まかせっきりなくせに、エクセルの知識がーってどの口で言えるんですか。
http://www.lucinasoftware.co.jp/ny_system/index.htm
急いでいないなら、インターネットで簡単に調べられるのですから、 こういうもの作って何が足りないか改めて考え直してくださいよ。 ほかの回答者には申し訳ないけど・・・ （稲葉） 2015/04/08(水) 06:02
結局ワークフローを理解していないで質問しちゃっているからメチャメチャになるんだよね。 一昔前ではこういう仕事依頼は（業者によっては）カモネギ扱いされてたようだけど... とにかく不必要な出費につながってしまう。
まず、このようなシステムを構築するのに、 なぜ あなたが担当なのか？
もっと仕事内容を理解している方に相談するなり、バトンタッチした方が... （seiya） 2015/04/08(水) 09:06
＞結局まかせっきりなくせに、エクセルの知識がーってどの口で言えるんですか。
返す言葉もありません。
＞急いでいないなら、インターネットで簡単に調べられるのですから、
少し急いでいます。
6月の繁忙期までにはある程度管理体制を整えたいと思っています。
＞こういうもの作って何が足りないか改めて考え直してくださいよ。
ありがとうございます。とても参考になるサイトです。
このサイトを見て、勉強していきます。
seiyaさん
＞まず、このようなシステムを構築するのに、
＞なぜ あなたが担当なのか？
＞もっと仕事内容を理解している方に相談するなり、バトンタッチした方が...
生産管理やエクセルの知識を持っている人が社内にいないのです・・・
それほど規模が小さく、人材が少ない会社だと思って下さい。
無茶な質問、他力本願で申し訳ありません。
考えを改めます。
外注のサイトも見ているのですが、予想以上に価格が高くて驚きました。
（うめ） 2015/04/08(水) 10:16
一般論ですけど、サンプルの数値は、それぞれ個性あるものにしてください。
「5」のオンパレードでは、事情を知らない回答側は、どう関連しているのか分からないです。 (計算で必然的にそうなるのか、あくまで例であって偶々同じになっているだけなのか）
１．「発注点」とは何ですか？
２．「総在庫」が同一商品の累計であることは分かりますが、 それ以外にも累計を扱っていると思われる列(有効在庫とか）がありますよね。 同一商品の最終行(個別のIDがある行）に、別のIDが関係する数値を表示させるのは、レイアウト的に無理がありませんか？
※なんか、サンプルが少なすぎて、推測を強いられる部分が多すぎます。 できれば、一連の流れを示して頂きたいです。 無在庫状態→発注(複数)→引当(複数)→入荷・出庫(複数・一部出庫あり)と推移すると、 「製品在庫」と「入出庫表」がどうなって行くベキなのか。(面倒なので一つの商品だけでいいですけど）
＞・引当数、発注数に数字を入力している行のクリアはやめてほしいです。 と云うことは、発注数・引当数がある行には「在庫表へ反映済」フラグ欄がまた必要になったと云うことになりますね？ （在庫表へ反映後、追加で発注数・引当数・入出庫数のデータが入力されるので、 反映済／未済のレコード行が混在することになりますよね）
＞また引当数、発注数は在庫表に反映し、書き換え後は入庫、出庫扱いにしたいのですが、できるのでしょうか。 作業手順のイメージが合っているかどうか分かりませんが、 入出庫表の引当数・発注数(予定)を消して、出庫数・入庫数(実績)に入れれば実績データに変わるので、 当然そう云う結果になると思います。
（半平太） 2015/04/08(水) 10:41
自分自身やりたいことがしっかりと定まっていない
どういうことができるのかしっかりとわからない状況では
このシステムは作っていただくことさえ、できないと判断しました。
皆様には、色々とお力添えをいただいのですが、
認識の甘さ、知識の浅さを痛感しました。
特に、半平太さん、稲葉さんにはエクセルには関係のないことまで
助言をいただきありがとうございました。
今後は、アナログで管理しつつ、外注での開発をしてもらえる場所を探していきます。
皆様、最後まで自分勝手ですみません。
ご迷惑をおかけしました。
ありがとうございました。
（うめ） 2015/04/08(水) 15:31
あれれ、またまた早計な。。。
やるなら今の時期しかないですよぉ。(移行作業だって時間が掛かるんですから)
私としてはもうちょっとだと思っていたのですけどねぇ。 逆質問され過ぎたですか？
では、取りあえず、以下の２点を教えてください。
＞ 「発注点」とは何ですか？（ただ、手入力しただけですか？）
＞少数のロットを足し合わせて、一つの出荷にする場合、 ＞どんな出庫データを入力することになるんですか？
まぁ、発注点の方だけでもそれが何なのか分かれば（ただ手入力しただけのデータなのか）、 あとの問題は私のセンスで決めてもいいですけど。
(人の都合を聞きながらやるより、自分のアイデアでやる方がよっぽど楽。 もっとも、それで人の望むものが作れるかは大いに疑問ではありますけどね）
（半平太） 2015/04/08(水) 16:15
＞「発注点」とは何ですか？（ただ、手入力しただけですか？） 手入力しただけです。
＞少数のロットを足し合わせて、一つの出荷にする場合、
＞どんな出庫データを入力することになるんですか？
すみません。
正直に全然処理の仕方がわかりませんでした。
なんとなく思ったのは
sheet1(製品在庫)
A B C D E F G H I J K L M
1 商品コード 商品名 入庫ID 発注点 総在庫 在庫 引当数 発注数 有効在庫数 有効残数 賞味期限 発注要否 備考
2 a-1 梅干し 20150101a-1 50 50 20150401
3 a-1 梅干し 20150201a-1 20 77 27 20150501
[入庫ID20150101a-1]が63出庫したときに[在庫F2が-13]になり、
[入庫ID20150101a-1]の賞味期限より一つ新しい賞味期限を[商品コードa-1]の中から処理されるというものです。
なんか難しくてできなさそうですけど・・・
＞無在庫状態→発注(複数)→引当(複数)→入荷・出庫(複数・一部出庫あり)と推移すると、
＞製品在庫」と「入出庫表」がどうなって行くベキなのか。(面倒なので一つの商品だけでいいですけど）
無在庫状態
sheet1(製品在庫)
A B C D E F G H I J K L M
1 商品コード 商品名 入庫ID 発注点 総在庫 在庫 引当数 発注数 有効在庫数 有効残数 賞味期限 発注要否 備考
2
sheet2(入出庫表)
A B C D E F G H I J K L
1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 発注数 備考
2
sheet3(入出庫履歴)
A B C D E F G H I J K L
1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 発注数 備考
2
発注(複数)
梅干しを4/7に13発注、6/29に26発注
sheet1(製品在庫)
A B C D E F G H I J K L M
1 商品コード 商品名 入庫ID 発注点 総在庫 在庫 引当数 発注数 有効在庫数 有効残数 賞味期限 発注要否 備考
2 a-1 梅干し 10 39 39
sheet2(入出庫表)
A B C D E F G H I J K L
1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 発注数 備考
2 a-1 梅干し 自社工場 13 13個4/7発注
3 a-1 梅干し 自社工場 26 26個6/29発注
sheet3(入出庫履歴)
A B C D E F G H I J K L
1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 発注数 備考
2 a-1 梅干し 自社工場 13 13個4/7発注
3 a-1 梅干し 自社工場 26 26個6/29発注
引当(複数)
梅干しを4/15に8引当、7/3に30引当
sheet1(製品在庫)
A B C D E F G H I J K L M
1 商品コード 商品名 入庫ID 発注点 総在庫 在庫 引当数 発注数 有効在庫数 有効残数 賞味期限 発注要否 備考
2 a-1 梅干し 10 38 39 -38 1 要
sheet2(入出庫表)
A B C D E F G H I J K L
1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 発注数 備考
2 a-1 梅干し 自社工場 13 13個4/7発注
3 a-1 梅干し 自社工場 26 26個6/29発注
4 a-1 梅干し お客 8 8個4/15引当
5 a-1 梅干し お客 30 30個7/3引当
sheet3(入出庫履歴)
A B C D E F G H I J K L
1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 発注数 備考
2 a-1 梅干し 自社工場 13 13個4/7発注
3 a-1 梅干し 自社工場 26 26個6/29発注
4 a-1 梅干し お客 8 8個4/15引当
5 a-1 梅干し お客 30 30個7/3引当
入荷・出庫(複数・一部出庫あり)
梅干しを7/20に4/7発注分13入庫 7/23に6/29発注分26、7入庫
梅干しを 7/23に4/15引当分8出庫 8/11に19出庫
7/20在庫_処理前
梅干しを4/7発注分13入庫
sheet1(製品在庫)
A B C D E F G H I J K L M
1 商品コード 商品名 入庫ID 発注点 総在庫 在庫 引当数 発注数 有効在庫数 有効残数 賞味期限 発注要否 備考
2 a-1 梅干し 10 38 39 -38 1 要
sheet2(入出庫表)
A B C D E F G H I J K L
1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 発注数 備考
2 a-1 梅干し 20150720a-1 20150720 自社工場 13 20150920 13個4/7発注
3 a-1 梅干し 自社工場 26 26個6/29発注
4 a-1 梅干し お客 8 8個4/15引当
5 a-1 梅干し お客 30 30個7/3引当
sheet3(入出庫履歴)
A B C D E F G H I J K L
1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 発注数 備考
2 a-1 梅干し 自社工場 13 13個4/7発注
3 a-1 梅干し 自社工場 26 26個6/29発注
4 a-1 梅干し お客 8 8個4/15引当
5 a-1 梅干し お客 30 30個7/3引当
7/20在庫_処理後
sheet1(製品在庫)
A B C D E F G H I J K L M
1 商品コード 商品名 入庫ID 発注点 総在庫 在庫 引当数 発注数 有効在庫数 有効残数 賞味期限 発注要否 備考
2 a-1 梅干し 20150720a-1 10 13 13 38 26 -25 1 20150920 要
sheet2(入出庫表)
A B C D E F G H I J K L
1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 発注数 備考
2 a-1 梅干し 自社工場 26 26個6/29発注
3 a-1 梅干し お客 8 8個4/15引当
4 a-1 梅干し お客 30 30個7/3引当
sheet3(入出庫履歴)
A B C D E F G H I J K L
1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 発注数 備考
2 a-1 梅干し 自社工場 13 13個4/7発注
3 a-1 梅干し 自社工場 26 26個6/29発注
4 a-1 梅干し お客 8 8個4/15引当
5 a-1 梅干し お客 30 30個7/3引当
6 a-1 梅干し 20150720a-1 20150720 自社工場 13 20150920 13個4/7発注
7/23在庫_処理前
梅干しを6/29発注分26、7入庫 4/15引当分8出庫
sheet1(製品在庫)
A B C D E F G H I J K L M
1 商品コード 商品名 入庫ID 発注点 総在庫 在庫 引当数 発注数 有効在庫数 有効残数 賞味期限 発注要否 備考
2 a-1 梅干し 20150720a-1 10 13 13 38 26 -25 1 要
sheet2(入出庫表)
A B C D E F G H I J K L
1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 発注数 備考
2 a-1 梅干し 20150723a-1 20150723 自社工場 33 20150923 26個6/29発注
3 a-1 梅干し 20150720a-1 20150723 お客 8 8個4/15引当
4 a-1 梅干し お客 30 30個7/3引当
sheet3(入出庫履歴)
A B C D E F G H I J K L
1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 発注数 備考
2 a-1 梅干し 自社工場 13 13個4/7発注
3 a-1 梅干し 自社工場 26 26個6/29発注
4 a-1 梅干し お客 8 8個4/15引当
5 a-1 梅干し お客 30 30個7/3引当
6 a-1 梅干し 20150720a-1 20150720 自社工場 13 20150920 13個4/7発注
7/23在庫_処理後
sheet1(製品在庫)
A B C D E F G H I J K L M
1 商品コード 商品名 入庫ID 発注点 総在庫 在庫 引当数 発注数 有効在庫数 有効残数 賞味期限 発注要否 備考
2 a-1 梅干し 20150720a-1 10 38 5 30 8 8 要
3 a-1 梅干し 20150723a-1 10 38 33 30 8 8 要
sheet2(入出庫表)
A B C D E F G H I J K L
1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 発注数 備考
2 a-1 梅干し お客 30 7/3引当
sheet3(入出庫履歴)
A B C D E F G H I J K L
1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 発注数 備考
2 a-1 梅干し 自社工場 13 4/7発注
3 a-1 梅干し 自社工場 26 6/29発注
4 a-1 梅干し お客 8 4/15引当
5 a-1 梅干し お客 30 7/3引当
6 a-1 梅干し 20150720a-1 20150720 自社工場 13 20150920 4/7発注
7 a-1 梅干し 20150723a-1 20150723 自社工場 33 20150923 6/29発注
8 a-1 梅干し 20150720a-1 20150723 お客 8 4/15引当
8/11在庫_処理前
梅干しを19出庫
sheet1(製品在庫)
A B C D E F G H I J K L M
1 商品コード 商品名 入庫ID 発注点 総在庫 在庫 引当数 発注数 有効在庫数 有効残数 賞味期限 発注要否 備考
2 a-1 梅干し 20150720a-1 10 38 5 30 8 8 要
3 a-1 梅干し 20150723a-1 10 38 33 30 8 8 要
sheet2(入出庫表)
A B C D E F G H I J K L
1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 発注数 備考
2 a-1 梅干し お客 30 7/3引当
3 a-1 梅干し 20150720a-1 20150811 お客 19
sheet3(入出庫履歴)
A B C D E F G H I J K L
1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 発注数 備考
2 a-1 梅干し 自社工場 13 4/7発注
3 a-1 梅干し 自社工場 26 6/29発注
4 a-1 梅干し お客 8 4/15引当
5 a-1 梅干し お客 30 7/3引当
6 a-1 梅干し 20150720a-1 20150720 自社工場 13 20150920 4/7発注
7 a-1 梅干し 20150723a-1 20150723 自社工場 33 20150923 6/29発注
8 a-1 梅干し 20150720a-1 20150723 お客 8 4/15引当
8/11在庫_処理後
sheet1(製品在庫)
A B C D E F G H I J K L M
1 商品コード 商品名 入庫ID 発注点 総在庫 在庫 引当数 発注数 有効在庫数 有効残数 賞味期限 発注要否 備考
3 a-1 梅干し 20150723a-1 10 19 19 30 -11 -11 要
sheet2(入出庫表)
A B C D E F G H I J K L
1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 発注数 備考
2 a-1 梅干し お客 30 7/3引当
sheet3(入出庫履歴)
A B C D E F G H I J K L
1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 発注数 備考
2 a-1 梅干し 自社工場 13 4/7発注
3 a-1 梅干し 自社工場 26 6/29発注
4 a-1 梅干し お客 8 4/15引当
5 a-1 梅干し お客 30 7/3引当
6 a-1 梅干し 20150720a-1 20150720 自社工場 13 20150920 4/7発注
7 a-1 梅干し 20150723a-1 20150723 自社工場 33 20150923 6/29発注
8 a-1 梅干し 20150720a-1 20150723 お客 8 4/15引当
9 a-1 梅干し 20150720a-1 20150811 お客 19
処理のやり方は、以上の流れで考えています。できるだけ実際に処理しているようにやってみました。
sheet1(製品在庫)の発注点、備考は手入力。有効在庫数、有効残数、発注要否、は数式を入れるのを考えています。
sheet1(製品在庫)のM列に現在の日にちから、賞味期限が後何日で切れるのかを、測定する数式を入れると思います。N列備考になると思います。
(今ぱっと思いつきました)賞味期限欄での条件付き書式で、いい感じにできなければですが・・・
＞私としてはもうちょっとだと思っていたのですけどねぇ。
そうなのですか！？私は1か月はこのやり取りが必要なんじゃないかと思っていました・・・
＞逆質問され過ぎたですか？
これは完全に自分の責任です。
みなさん心から気遣ってくださっていると感じていました。
＞(人の都合を聞きながらやるより、自分のアイデアでやる方がよっぽど楽。
＞もっとも、それで人の望むものが作れるかは大いに疑問ではありますけどね）
コンサルタント業などされているんですか？
考え方がすごく尊敬できます。
色々と考えてくださり、ありがとうございます。
説明下手ですみません。
（うめ） 2015/04/08(水) 22:28
＞[入庫ID20150101a-1]が63出庫したときに[在庫F2が-13]になり、 ＞[入庫ID20150101a-1]の賞味期限より一つ新しい賞味期限を[商品コードa-1]の中から処理されるというものです。 ＞なんか難しくてできなさそうですけど・・・
１．それを自動的にやるのは、今回はちょっと難しそうです。 基本的に、いつの賞味期限のものを何個出すことにするかは人間が決める事、との認識でいます。 (その判断材料は、表の右端にでも表示して、人間がそれを参考にして決める・・てな考えですけど）
なので、 20150101a-1は50個出庫(残高全部出庫) 20150201a-1を13個出庫(一部出庫) と云うデータを手作業で入れて頂くことになると思います。、
＞7/23在庫_処理後 ＞ sheet1(製品在庫) ＞ A B C D E F G H I J K L M ＞1 商品コード 商品名 入庫ID 発注点 総在庫 在庫 引当数 発注数 有効在庫数 有効残数 賞味期限 発注要否 備考 ＞2 a-1 梅干し 20150720a-1 10 38 5 30 8 8 要 ＞3 a-1 梅干し 20150723a-1 10 38 33 30 8 8 要
２．以前の説明ですとE2(総在庫)は「5」になると思うのですけど？(順次累計方式) ↓ 2 a-1 梅干し 20150720a-1 10 5 5 30 8 8 要 3 a-1 梅干し 20150723a-1 10 38 33 30 8 8 要
３．引当数と発注数がちょっと分かりません。 同じ商品コードで、入庫IDがないデータ(＝引当・発注のデータ）が入出庫表に複数行あり、 また、入庫IDが確定しているデータも複数行あった場合、引当数と発注数をどう計上するのでしょうか？ (サンプルにはないと思うので、分からないです。） 当初は、総計を最終行に出せばいいの思ったのですが、そんなに明らかな事でも無い様なので・・・
（半平太） 2015/04/09(木) 15:12
＞20150101a-1は50個出庫(残高全部出庫)
＞20150201a-1を13個出庫(一部出庫)
＞と云うデータを手作業で入れて頂くことになると思います。、
そうですよね。ありがとうございます。
このやり方のほうが柔軟性があって、よさそうです！
＞＞7/23在庫_処理後
＞＞ sheet1(製品在庫)
＞＞ A B C D E F G H I J K L M
＞＞1 商品コード 商品名 入庫ID 発注点 総在庫 在庫 引当数 発注数 有効在庫数 有効残数 賞味期限 発注要否 備考
＞＞2 a-1 梅干し 20150720a-1 10 38 5 30 8 8 要
＞＞3 a-1 梅干し 20150723a-1 10 38 33 30 8 8 要
＞２．以前の説明ですとE2(総在庫)は「5」になると思うのですけど？(順次累計方式)
↓ ＞2 a-1 梅干し 20150720a-1 10 5 5 30 8 8 要 ＞3 a-1 梅干し 20150723a-1 10 38 33 30 8 8 要
申し訳ありません。
ご指摘通りの処理でお願いします。
＞当初は、総計を最終行に出せばいいの思ったのですが、そんなに明らかな事でも無い様なので・・・
総計を最終行に表示でお願いします。
意味不明なことを書いていました。
申し訳ありません。
よろしくお願いします。
（うめ） 2015/04/09(木) 19:16
プログラムの流れを以下(１〜６）にしようと思っています。
１．更新前のデータについて形式的整合性を事前チェックする ２．在庫表のデータについて商品別のまとめ(集計）を行う ３．入出庫表について、２でまとめたものに追加反映させる
※以上の過程で問題が発生しなければ、４へ進む。 問題が発生した場合は、１〜３の過程でプログラムはストップする。
４．データ更新の前の状態を別名(○○BKUP.xls) で保存する ※本体ブックと同一のフォルダ内に自動作成されます
５．データ更新を実行する（実際にシートのデータが書換わります） ６．念の為、更新後のデータについて形式的整合性をチェックする
※以上の処理で問題が発生した場合、４で作った別名ブックを本体ブックとして戻す。 万一、この復元作業が必要になった場合は、手作業でやってください。 プログラムでも難しくはないですが、作業自体が気軽に行うものでもないので。。
「１と６」の形式的整合性チェックは下記にする予定ですが、問題・追加要望などありますか？
ただし、まとめ(集計）を開始する前のチェックなので、全体を見てのチェックは趣旨に反します。 どう考えてもそれはあり得ない、と云うものだと助かります。
製品在庫のチェック (1)在庫がマイナス (2)入庫IDがあるのに賞味期限がない
入出庫表のチェック (1)入庫IDがないのに賞味期限が入っている (2)出庫ファクタ(納入先、出庫数、引当数のどこか）に何かデータが入っているのに 入庫ファクタ(仕入先、入庫数、発注数のどこか）にもデータが入っている。
（半平太） 2015/04/10(金) 10:09
プログラムの流れ、確認しました。
概ねかしこまりました。
ただ、一点確認したいことがあります。
＞(2)出庫ファクタ(納入先、出庫数、引当数のどこか）に何かデータが入っているのに 入庫ファクタ(仕入先、入庫数、発注数のどこか）にもデータが入っている。
入力する際に、出庫するとき、入庫するときで、行を変えるということで、よろしいでしょうか。
例えば、今日入庫した商品を今日出庫するときは、
同じ行に、出庫ファクタ、入庫ファクタは入力できず、分けて入力しなければならない。
（うめ） 2015/04/10(金) 13:46
＞入力する際に、出庫するとき、入庫するときで、行を変えるということで、よろしいでしょうか。
いや、そちらのやり方が分からなかったので、私がそう思い込んでしまったものです。
受注生産でない限り、出庫と入庫は同じ行には入力されないと思い込んでいたのですが、 同一行に 仕入先名と納入先名 が入力されることもあると云うことでしたら、 それはそれで結構です。
・・と言うことは、事前・事後のデータ整合性のチェックは、全く不要と判断していいでしょうか？
ついでですが、現在、商品在庫のデータ行数は何行くらいになっていますか？ 大量だと処理速度に影響するので、念頭に入れて置きたいのですけど。。
（半平太） 2015/04/10(金) 15:06
＞・・と言うことは、事前・事後のデータ整合性のチェックは、全く不要と判断していいでしょうか？
その他の機能は入れていただきいです。
特に在庫がマイナスになることはありえないので・・・
何度も同じ質問になってしまい、申し訳ありません。
＞ (2)出庫ファクタ(納入先、出庫数、引当数のどこか）に何かデータが入っているのに
＞ 入庫ファクタ(仕入先、入庫数、発注数のどこか）にもデータが入っている
このチェックは、出庫ファクタ、入庫ファクタが同時に存在しない行ではできるのでしょうか？
以前に条件式書式で対応できると、教えていただいのですが、機械のチェックもあれば安心できるので。
＞ついでですが、現在、商品在庫のデータ行数は何行くらいになっていますか？
現在は、35行です。
最低80行までは増えると思います。
以上、よろしくお願いします。
（うめ） 2015/04/10(金) 20:32
＞何度も同じ質問になってしまい、申し訳ありません。 ＞＞ (2)出庫ファクタ(納入先、出庫数、引当数のどこか）に何かデータが入っているのに ＞＞ 入庫ファクタ(仕入先、入庫数、発注数のどこか）にもデータが入っている ＞このチェックは、出庫ファクタ、入庫ファクタが同時に存在しない行ではできるのでしょうか？ ＞ 以前に条件式書式で対応できると、教えていただいのですが、機械のチェックもあれば安心できるので。
話がかみ合っていない感じですけど、 私は、「全ての各行単位で、出庫ファクタ、入庫ファクタが同時に存在してはいけない」と云うチェックをするつもりでいました。
「ある行は同時に存在してもいいけど、ある行はいけない」と云うことになるなら、 その違いはどこで判定するのかについてご説明いただかないと、具体的なコーディングに落とせません。
（半平太） 2015/04/10(金) 21:17
＞私は、「全ての各行単位で、出庫ファクタ、入庫ファクタが同時に存在してはいけない」と云うチェックをするつもりでいました。
そうですよね。
わがままなことを言いました。
申し訳ありません。
（うめ） 2015/04/10(金) 22:23
１．プログラムは3つのモジュールに書くことになります。 (1)標準モジュール（ポピュラーですね） (2)シートモジュール(入出庫表のシート見出しを右クリックして、コードの表示を選ぶと出てくるモジュールです） (3)クラスモジュール(VBEメニューの挿入タブから挿入します） デフォルトでは「Class1」というオブジェクト名になっていて、気分が出ないので 「商品」と云うオブジェクト名に変更してください。
※それぞれのモジュールに貼り付けるプログラムは最後尾にあります。
２．データの正確性が大切なので、処理前の整合性チェックを
以下の様に入れてありますので、適否をご確認ください。
(1)製品在庫シート
"在庫がマイナスです"
"入庫IDがあるのに、賞味期限が無い"
"入庫IDがないのに、賞味期限がある"
"在庫があるのに、入庫IDがない"
(2)入出庫シート
"入庫IDが無いのに、賞味期限がある"
"入出庫日があるのに、入庫数も出庫数もない"
"入出庫日と入庫数があるのに、賞味期限がない"
"入出庫日がないのに、入庫数または出庫数がある"
"入庫数と発注数が、両方入力されている"
"出庫数と引当数が、両方入力されている"
"仕入先がないのに、入庫数または発注数がある"
"納入先がないのに、出庫数または引当数がある"
"入庫数または出庫数があるのに、発注数または引当数がある"
"入庫IDと仕入先があるのに、賞味期限がない"
３．たしか、製品在庫シートで、数式で対応する列があったと思うのですけど、 以下の列と考えていいですね。その列は、数式が壊れない様に上書きの対象から外します。
商品名(B)、有効在庫数(I)、有効残数(J)、発注要否(L)
※数式でエラーが出ているセルがあると、トラブル可能性がありますのでご留意願います。
４．使い方
入出庫表を通常通り作成する。(入力支援機能については下の方で述べます）
↓
マクロ「Main」を実行する。
↓
「事前チェックのみか、本番処理までするか」聞かれるので「はい/いいえ」で答える。
※どちらを選んでも、整合性チェックまでは、同じプロセスです。(以外の選択はないです。
整合性チェックでエラーが発見されると、「不整合」シートに理由が書かれるので、それを読んでください。
「不整合」シートはプログラムで自動的に挿入されます。(予め増やす必要はありません）
不整合が発見されると、「本番処理までする」を選んであったとしても、処理中止になります。
原因を直して「Main」を再実行してください。
↓
この後、在庫マイナスチェックも行われます。
こちらは、不整合シートに原因は書かれず、単にメッセージが出るだけです。
このチェックにも合格しないと、やはり処理中止になります。
↓
「本番処理まで行く」を選択してあると、「バックアップファイル=（本体ブックBKUP.xls」が作成されたあと、
各シートのデータ書き換えが行われます。
↓
データ書換後、事前チェックと同じ内容の事後チェックが行われます。
これは事後なので、エラーが発見されても、データ書換え前には戻れません。
簡単なエラーは手で直し、重篤ならば、上で作成された「バックアップファイル」を
本体ブック名に手動で変更して(=元に戻って）原因を排除し、「Main」再実行する事になります。
５．入力支援機能について
<製品在庫 サンプル> 行 _____A_____ ___B___ _____C_____ ___D___ ___E___ __F__ ___G___ 1 商品コード 商品名 入庫ID 発注点 総在庫 在庫 引当数 2 d aa2 4 4 3 d aa3 10 781 777 4 f aa4 66 66 5 tys bbbc 100 100 6 a-1 20150720a-1 13 13 7 a-1 20150723a-1 46 33 38
<入出庫表 サンプル> 行 _____A_____ ___B___ ___C___ ____D____ ____E____ ___F___ ___G___ ____H____ ___I___ ___J___ 1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 2 a-1 梅干し お客1 8 3 a-1 梅干し お客2 30
以上のような状況において、例えば顧客２の出庫をしたい状況になった場合、 3行目のAA列を右クリックしてください。 ※AA列は遠すぎると感じるときは、使っていない列(多分、N:Z列） を非表示にしてM列の右隣りに見える様に塩梅してください。
<入出庫表 AA3セルを右クリックした時> 行 _____AA_____ ___AB___ _AC_ __AD__ 2 在庫ID 賞味期限 在庫 行番号 3 20150720a-1 20150920 13 3 4 20150723a-1 20150923 33 3
以上のデータが出現し、賞味期限が短い方の在庫IDセル(サンプルだとAA3が黄色くなっています）
ここで、通常は黄色のセルをダブルクリックすることになります。 すると在庫ID列(C3セル）に「20150720a-1」と自動的に入ります。
でも、その在庫IDは13個しかありませんので、マイナスになります。 実際、顧客２の入出庫日にデータを入れ、出庫数欄に30と入れて（なお、こうすると引当数は自働クリアされます） 再度AA3セルを右クリックすると、残高がマイナスで現れます。
<入出庫表 入庫ID自動入力、入出庫日と出庫数を手動入力> 行 _____A_____ ___B___ _____C_____ ____D____ ____E____ ___F___ ___G___ ____H____ ___I___ ___J___ 1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 2 a-1 梅干し お客1 8 3 a-1 梅干し 20150720a-1 2015/4/13 お客2 30
<入出庫表 AA3セルを試しに右クリック> 行 _____AA_____ ___AB___ _AC_ __AD__ 2 在庫ID 賞味期限 在庫 行番号 3 20150720a-1 20150920 -17 3 ←マイナスになっている（今やった自分の出庫数も反映される） 4 20150723a-1 20150923 33 3
なので、残高不足になる場合は2行に分けて出庫データを入力してください。
<入出庫表 3行目の出庫数を13個に変え、4行目に同じデータをコピーして、AA4を右クリックしてみる> （下の行にコピーする方法の例 → A4:L4をドラッグ選択して、Ctrlキーを押しながら「D」キーを押し下げる） その後、AA4セルを右クリックしてみる>
行 _____A_____ ___B___ _____C_____ ____D____ ____E____ ___F___ ___G___ ____H____ ___I___ ___J___ _____AA_____ ___AB___ _AC_ __AD__ 1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 2 a-1 梅干し お客1 8 3 a-1 梅干し 20150720a-1 2015/4/13 お客2 13 在庫ID 賞味期限 在庫 行番号 4 a-1 梅干し 20150720a-1 2015/4/13 お客2 13 20150720a-1 20150920 -13 4 5 20150723a-1 20150923 33 4
※ACセルがマイナス13になっているのは、4行目のデータも反映されている為です。
そこで、黄色になっているセル(一番賞味期限が短いもの）の下のセル（AA5セル）をダブルクリックします。 すると、C4セルが新しい入庫IDに書き換わります。 行 _____A_____ ___B___ _____C_____ ____D____ ____E____ ___F___ ___G___ ____H____ ___I___ ___J___ 1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 2 a-1 梅干し お客1 8 3 a-1 梅干し 20150720a-1 2015/4/13 お客2 13 4 a-1 梅干し 20150723a-1 2015/4/13 お客2 13
そこで4行目の出庫数を本来の残り個数である「17」個に変えます。
試にAA4セルを再度右クリックすると(通常はこんなお試しは必要ありません・・・説明の都合上やっております） <入出庫表 結果図> 行 _____A_____ ___B___ _____C_____ ____D____ ____E____ ___F___ ___G___ ____H____ ___I___ ___J___ _____AA_____ ___AB___ _AC_ __AD__ 1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 2 a-1 梅干し お客1 8 3 a-1 梅干し 20150720a-1 2015/4/13 お客2 13 在庫ID 賞味期限 在庫 行番号 4 a-1 梅干し 20150723a-1 2015/4/13 お客2 17 20150720a-1 20150920 0 4 5 20150723a-1 20150923 16 4 この後、本番更新すれば、以下の結果が得られます。
<製品在庫 結果図> 行 _____A_____ ___B___ _____C_____ ___D___ ___E___ __F__ ___G___ ___H___ 1 商品コード 商品名 入庫ID 発注点 総在庫 在庫 引当数 発注数 2 d aa2 4 4 3 d aa3 10 781 777 4 f aa4 66 66 5 tys bbbc 100 100 6 a-1 20150723a-1 16 16 8
<入出庫表 結果図> 行 _____A_____ ___B___ ___C___ ____D____ ____E____ ___F___ ___G___ ____H____ ___I___ ___J___ 1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 引当数 2 a-1 梅干し お客1 8
こちらは、お座なりなテストしかやっておりません。 そちらでは、入念に行ってください。
６．貼り付けるマクロ (1)標準モジュール用ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
'Option Explicit
Const wsBalName As String = "製品在庫" Const wsInOutName As String = "入出庫表" Const wsHistoryName As String = "入出庫履歴" Const wsUnmatchName As String = "不整合" Const wsMasterName As String = "商品コード"
Type 総計
'製品在庫
製品在庫残(1 To 2) '1=オリジナル、2=更新後
製品引当数(1 To 2)
製品発注数(1 To 2)
' 入出庫表
日付がある入出庫(1 To 2)
引当数(1 To 2)
発注数(1 To 2)
End Type
Sub Main()
Dim 関所 As 総計
Dim wsBal As Worksheet, wsInOut As Worksheet
Dim wsHist As Worksheet, wsMast As Worksheet
Dim Products() As 商品
Dim oneMarchan As 商品
Dim idx As Long
Dim Balances As Variant
Dim printlines() As Variant
Dim sumAdded As Variant
Dim Dummy As Variant
Dim CodeList As Range 'コードリスト
Dim CodeNameList As Range '対照リスト
Dim NN As Long, KK As Long
Dim rowsBal As Long
Dim rowsInOut As Long
Dim rngOfBal As Range
Dim rngOfInOut As Range
Dim valOfBal As Variant
Dim ValOfInOut As Variant
Dim CodeDic As Object
Dim codeKey As Variant
Dim msg As String
Dim ans, balCkResult
Set wsBal = Sheets(wsBalName)
Set wsInOut = Sheets(wsInOutName)
Set wsHist = Sheets(wsHistoryName)
Set wsMast = Sheets(wsMasterName)
Application.EnableEvents = False
wsInOut.Range("AA1:AD1000").Clear '入出庫の入力支援エリアをクリア
Application.EnableEvents = True
msg = "処理を選んでください" & vbCr & vbCr & " はい (Y) = 事前チェックだけ"
ans = MsgBox(msg & vbCr & vbCr & " いいえ(N) = 在庫情報の更新処理", vbYesNo)
Rem データの形式的整合性事前チェック
rowsBal = wsBal.Cells(wsBal.Rows.count, "A").End(xlUp).Row
rowsInOut = wsInOut.Cells(wsInOut.Rows.count, "A").End(xlUp).Row
Set rngOfBal = wsBal.Range("A1:M" & rowsBal)
valOfBal = rngOfBal.Value2
Set rngOfInOut = wsInOut.Range("A1:M" & rowsInOut) '転記情報列を含める
ValOfInOut = rngOfInOut.Value2
If dataValidation(rowsBal, valOfBal, rowsInOut, ValOfInOut) = False Then
MsgBox "事 前 整 合 性 チ ェ ッ ク で す" & vbCr & vbCr & _
"整合性が取れてないデータがあります。" & vbCr & vbCr & _
"詳細は「" & wsUnmatchName & "」シートをご参照ください。→ 処理中止"
Sheets(wsUnmatchName).Select
Exit Sub
Else
balCkResult = MinusBalDefDeadL(valOfBal, ValOfInOut)
If balCkResult <> "" Then
MsgBox "事 前 残 高 チ ェ ッ ク で す" & vbCr & vbCr & _
Replace(balCkResult, "賞味", "") & _
IIf(Right(balCkResult, 2) <> "賞味", "はマイナス残になります", _
"は同じ在庫IDなのに、賞味期限が異なるものがあります")
Exit Sub
ElseIf ans = vbYes Then
MsgBox "整合性は問題なしです"
End If
End If
If ans <> vbNo Then Exit Sub '事前チェックのみで終了
Rem 更新データ存在確認 ' If rowsInOut < 2 Then '処理可能データの存在確認 ' MsgBox "入出庫表に処理データがありません → 処理中止" ' Exit Sub ' End If
Set CodeDic = CreateObject("Scripting.dictionary") ' 連想配列で各商品のキーを管理
Rem 製品在庫のまとめ処理(商品オブジェクト群で管理）
If rowsBal > 1 Then
For NN = 2 To UBound(valOfBal)
codeKey = valOfBal(NN, 1) '商品コードをKeyとする
If Not CodeDic.Exists(codeKey) Then
If idx = 0 Then
ReDim Products(1 To 1)
Else
ReDim Preserve Products(1 To UBound(Products) + 1)
End If
CodeDic.Add codeKey, UBound(Products) '新規の商品コードをDictionaryに登録
Set Products(UBound(Products)) = New 商品
Products(UBound(Products)).MaCodeInit codeKey, valOfBal(NN, 2) '初期値設定
End If
idx = CodeDic(codeKey)
Call Products(idx).update(NN, valOfBal, True) '製品在庫シートの情報でupdate処理
Next NN
' Erase valOfBal
Else
'Do nothing"
End If
Rem 入出庫表をまとめに追加反映
For NN = 2 To UBound(ValOfInOut)
codeKey = ValOfInOut(NN, 1) '商品コードをKeyとする
If Not CodeDic.Exists(codeKey) Then
If idx = 0 Then
ReDim Products(1 To 1)
Else
ReDim Preserve Products(1 To UBound(Products) + 1)
End If
CodeDic.Add codeKey, UBound(Products) '新規の商品コードをDictionaryに登録
Set Products(UBound(Products)) = New 商品
Products(UBound(Products)).MaCodeInit codeKey, ValOfInOut(NN, 2) '初期値設定
End If
idx = CodeDic(codeKey)
Call Products(idx).update(NN, ValOfInOut, False) '入出庫表の情報でupdate処理
Next NN
' Erase valOfInOut
Rem 商品オブジェクトの在庫ありデータ関連を集計して、製品在庫表の書換えに備える
If CodeDic.count > 0 Then
'用済みのDictionaryのデータを一旦全部クリア
CodeDic.RemoveAll
'商品名リストを把握(商品名検索準備)
Set CodeList = wsMast.Range("A2", wsMast.Range("A1000").End(xlUp)) 'コードリスト
Set CodeNameList = CodeList.Resize(, 2)
ReDim printlines(0)
For NN = 1 To UBound(Products)
sumAdded = 0
Balances = Products(NN).Balance '各商品オブジェクトを順次取り出し
For KK = 0 To UBound(Balances) 'オブジェクトの在庫配列を順にチェック
If Balances(KK) < 0 Then '在庫がマイナス
MsgBox "マイナス残です" & vbCr & vbCr & _
Products(NN).code(KK) & "→ 処理中止"
Exit Sub
ElseIf Balances(KK) > 0 Then '残高があるデータのみピックアップ
sumAdded = sumAdded + Balances(KK)
ReDim Preserve printlines(0 To UBound(printlines) + 1)
printlines(UBound(printlines)) = Products(NN).PrintLine(KK, False)
printlines(UBound(printlines))(5) = sumAdded '在庫累計処理
End If
Next KK
'最終行処理
If UBound(printlines) > 0 Then
Dummy = Products(NN).PrintLine(0, True) '0次データを取得
If printlines(UBound(printlines))(1) = Products(NN).code Then
' '数式と同等の数値を算出
With Application
If Dummy(1) <> "" Then
If .CountIf(CodeList, Dummy(1)) Then
Dummy(2) = .VLookup(Dummy(1), CodeNameList, 2, False)
End If
End If
End With
Dummy(9) = Dummy(5) - Dummy(7)
Dummy(10) = Dummy(9) + Dummy(8)
Dummy(12) = IIf(Dummy(9) <= Dummy(4), "要", Empty)
'在庫があるなら、最終行に引当・発注数を割り込み書き
If sumAdded > 0 Then
printlines(UBound(printlines))(2) = Dummy(2)
printlines(UBound(printlines))(4) = Dummy(4)
printlines(UBound(printlines))(7) = Dummy(7)
printlines(UBound(printlines))(8) = Dummy(8)
printlines(UBound(printlines))(9) = Dummy(9)
printlines(UBound(printlines))(10) = Dummy(10)
printlines(UBound(printlines))(12) = Dummy(12)
End If
Else '在庫はないが引当・発注があれば、一行強制挿入後書込み
If Dummy(6) <> 0 Or Dummy(7) <> 0 Or Dummy(8) <> 0 Then
ReDim Preserve printlines(0 To UBound(printlines) + 1)
printlines(UBound(printlines)) = Dummy
End If
End If
End If
Next NN
End If
Rem 総計比較準備
Call 総計比較(valOfBal, ValOfInOut, 関所, 1) '1=更新前オリジナルデータ集計
Erase valOfBal
Erase ValOfInOut
'Rem 更新前の状態を別名ブック（○○BKUP.xls) に保存（同一フォルダとします）
ThisWorkbook.SaveCopyAs Replace(ThisWorkbook.FullName, ".xls", "BACK.xls")
Rem 製品在庫のデータ書換
Call displayResult(wsBal, printlines)
Rem 転記と行削除
Call shiftNdelete(wsHist, wsInOut, wsBal)
Rem 念の為、更新後のデータの形式的整合性を事後チェック
rowsBal = wsBal.Cells(wsBal.Rows.count, "A").End(xlUp).Row
rowsInOut = wsInOut.Cells(wsInOut.Rows.count, "A").End(xlUp).Row
Set rngOfBal = wsBal.Range("A1:M" & rowsBal)
valOfBal = rngOfBal.Value2
Set rngOfInOut = wsInOut.Range("A1:L" & rowsInOut)
ValOfInOut = rngOfInOut.Value2
If dataValidation(rowsBal, valOfBal, rowsInOut, ValOfInOut) = False Then
MsgBox "事後チェックですが・・・・" & vbCr & vbCr & _
"整合性が取れてないデータがあります。" & vbCr & vbCr & _
"詳細は「" & wsUnmatchName & "」シートをご参照ください。"
Exit Sub
End If
Rem 総計比較
Call 総計比較(valOfBal, ValOfInOut, 関所, 2) '2=更新後データ集計
Dim rslt(1 To 4) As Boolean
rslt(1) = 関所.製品在庫残(2) = 関所.製品在庫残(1) + 関所.日付がある入出庫(1)
rslt(2) = 関所.製品引当数(2) = 関所.引当数(1)
rslt(3) = 関所.製品発注数(2) = 関所.発注数(1)
rslt(4) = True
For NN = 1 To 3
rslt(4) = rslt(4) * rslt(NN)
Next
MsgBox "更新終了ーーー→総計比較の結果、" & IIf(rslt(4), "「全て合致」しております", "「一部不一致」がありました") & vbCr & vbCr & _
"新在庫総数は、前回在庫数＋前回の日付ある入出庫ネットと→ " & IIf(rslt(1), "合致", "不一致！！") & vbCr & _
"新在庫引当総数は、新入出庫表の引当総数と――――――→ " & IIf(rslt(2), "合致", "不一致！！") & vbCr & _
"新在庫発注総数は、新入出庫表の発注総数と――――――→ " & IIf(rslt(3), "合致", "不一致！！")
End Sub
'在庫がマイナスにならないか及び同入庫IDで賞味期限が違うものがないか(空白はチェック外)事前チェック
Function MinusBalDefDeadL(valOfBal, ValOfInOut) 'CodeとIDの結合Keyを返す
Dim NN As Long, Balance As Variant
'
Dim CdIdBalDic As Object
Dim CDIDkey As Variant
Dim CDIDDeadkey As Variant '賞味期限の異動チェック用
Set CdIdBalDic = CreateObject("Scripting.dictionary") ' 連想配列で商品コードを管理
'
Rem 製品在庫からのデータ集め
For NN = 2 To UBound(valOfBal)
'残高チェック工程
If valOfBal(NN, 3) <> "" Then '入庫IDが存在する
CDIDkey = valOfBal(NN, 1) & "の在庫ID=" & valOfBal(NN, 3) '商品コードと入庫IDをキーにする
If CdIdBalDic.Exists(CDIDkey) Then
CdIdBalDic(CDIDkey) = CdIdBalDic(CDIDkey) + valOfBal(NN, 6)
Else '新規
CdIdBalDic.Add CDIDkey, valOfBal(NN, 6)
End If
End If
'賞味期限チェック工程
If valOfBal(NN, 3) <> "" And valOfBal(NN, 11) <> "" Then '入庫IDと賞味期限が存在する
CDIDDeadkey = valOfBal(NN, 1) & "の在庫ID=" & valOfBal(NN, 3) & _
" 賞味" '商品コードと入庫IDと”賞味”をキーにする
If CdIdBalDic.Exists(CDIDDeadkey) Then
If CdIdBalDic(CDIDDeadkey) <> valOfBal(NN, 11) Then
MinusBalDefDeadL = CDIDDeadkey
Exit Function '同在庫IDで異賞味期限あり、直ちに中止
End If
Else '新規
CdIdBalDic.Add CDIDDeadkey, valOfBal(NN, 11)
End If
End If
Next NN
'
Rem 入出庫表からのデータ集め
For NN = 2 To UBound(ValOfInOut)
'残高チェック工程
If ValOfInOut(NN, 3) <> "" Then '入庫IDが存在する
CDIDkey = ValOfInOut(NN, 1) & "の在庫ID=" & ValOfInOut(NN, 3)
If CdIdBalDic.Exists(CDIDkey) Then
CdIdBalDic(CDIDkey) = CdIdBalDic(CDIDkey) _
+ ValOfInOut(NN, 7) - ValOfInOut(NN, 9)
Else '新規
CdIdBalDic.Add CDIDkey, ValOfInOut(NN, 7) - ValOfInOut(NN, 9)
End If
End If
'賞味期限チェック工程
If ValOfInOut(NN, 3) <> "" And ValOfInOut(NN, 8) Then '入庫IDと賞味期限が存在する
CDIDDeadkey = ValOfInOut(NN, 1) & "の在庫ID=" & ValOfInOut(NN, 3) & " 賞味"
If CdIdBalDic.Exists(CDIDDeadkey) Then
If CdIdBalDic(CDIDDeadkey) <> ValOfInOut(NN, 8) Then
MinusBalDefDeadL = CDIDDeadkey
Exit Function '同在庫IDで異賞味期限あり、直ちに中止
End If
Else '新規
CdIdBalDic.Add CDIDDeadkey, ValOfInOut(NN, 8)
End If
End If
Next NN
Rem マイナス残をチェック
NN = 0
If CdIdBalDic.count > 0 Then
For Each Balance In CdIdBalDic.Items
NN = NN + 1
If Balance < 0 Then
MinusBalDefDeadL = Application.Index(CdIdBalDic.Keys, NN)
End If
Next
End If
CdIdBalDic.RemoveAll
End Function
Rem データの整合性をチェック
Private Function dataValidation(rowsBal, valOfBal, rowsInOut, ValOfInOut) As Boolean
Dim NN As Long, KK As Long, rowToWrite As Long, OKNG As Boolean
Dim wsWork As Worksheet
Dim msgNoB(1 To 4) As Long
Dim errMsgB(1 To 4) As String
Dim msgNoIO(1 To 11) As Long
Dim errMsgIO(1 To 11) As String
On Error Resume Next
Set wsWork = Sheets(wsUnmatchName)
If Err.Number <> 0 Then
Set wsWork = ThisWorkbook.Sheets.Add(after:=Worksheets(Worksheets.count))
wsWork.Name = wsUnmatchName
End If
On Error GoTo 0
wsWork.Range("A:B").ClearContents
wsWork.Range("A1:B1").Value2 = [{"シート名と位置-商品コード","不整合理由"}]
OKNG = True
rowToWrite = 1
If rowsBal > 1 Then
'メッセージセット
errMsgB(1) = "在庫がマイナスです"
errMsgB(2) = "入庫IDがあるのに、賞味期限が無い"
errMsgB(3) = "入庫IDがないのに、賞味期限がある"
errMsgB(4) = "入庫IDがないのに、在庫がある"
For NN = 2 To UBound(valOfBal)
Erase msgNoB
If valOfBal(NN, 6) < 0 Then msgNoB(1) = 1
If valOfBal(NN, 3) <> "" And valOfBal(NN, 11) = "" Then msgNoB(2) = 1
If valOfBal(NN, 3) = "" And valOfBal(NN, 11) <> "" Then msgNoB(3) = 1
If valOfBal(NN, 3) = "" And valOfBal(NN, 6) <> 0 Then msgNoB(4) = 1
If Application.Sum(msgNoB) > 0 Then
OKNG = False
For KK = 1 To UBound(msgNoB)
If msgNoB(KK) = 1 Then
rowToWrite = rowToWrite + 1
wsWork.Cells(rowToWrite, 1) = "製品在庫 " & NN & "行目の" & valOfBal(NN, 1)
wsWork.Cells(rowToWrite, 2) = errMsgB(KK)
End If
Next KK
End If
Next NN
End If
If rowsInOut > 1 Then
'メッセージセット
errMsgIO(1) = "入庫IDが無いのに、賞味期限がある"
errMsgIO(2) = "入出庫日があるのに、入庫数も出庫数もない又は入庫IDが無い"
errMsgIO(3) = "入出庫日と入庫数があるのに、在庫ID・賞味期限がない"
errMsgIO(4) = "入出庫日がないのに、入庫数または出庫数がある"
errMsgIO(5) = "入庫数と発注数が、両方入力されている"
errMsgIO(6) = "出庫数と引当数が、両方入力されている"
errMsgIO(7) = "仕入先がないのに、入庫数または発注数がある"
errMsgIO(8) = "納入先がないのに、出庫数または引当数がある"
errMsgIO(9) = "入庫数または出庫数があるのに、発注数または引当数がある"
errMsgIO(10) = "入庫IDと仕入先があるのに、賞味期限がない"
errMsgIO(11) = "入庫ファクタがあるのに、出庫ファクタもある"
For NN = 2 To UBound(ValOfInOut)
Erase msgNoIO
If ValOfInOut(NN, 3) = "" And ValOfInOut(NN, 8) <> "" Then msgNoIO(1) = 1
If ValOfInOut(NN, 4) <> "" And ((ValOfInOut(NN, 7) = "" And ValOfInOut(NN, 9) = "") Or _
ValOfInOut(NN, 3) = "") Then msgNoIO(2) = 1
If ValOfInOut(NN, 4) <> "" And ValOfInOut(NN, 7) <> "" And _
(ValOfInOut(NN, 3) = "" Or ValOfInOut(NN, 8) = "") Then msgNoIO(3) = 1
If ValOfInOut(NN, 4) = "" And _
(ValOfInOut(NN, 7) <> "" Or ValOfInOut(NN, 9) <> "") Then msgNoIO(4) = 1
If ValOfInOut(NN, 7) <> "" And ValOfInOut(NN, 11) <> "" Then msgNoIO(5) = 1
If ValOfInOut(NN, 9) <> "" And ValOfInOut(NN, 10) <> "" Then msgNoIO(6) = 1
If ValOfInOut(NN, 5) = "" And _
(ValOfInOut(NN, 7) <> "" Or ValOfInOut(NN, 11) <> "") Then msgNoIO(7) = 1
If ValOfInOut(NN, 6) = "" And _
(ValOfInOut(NN, 9) <> "" Or ValOfInOut(NN, 10) <> "") Then msgNoIO(8) = 1
If (ValOfInOut(NN, 7) <> "" And ValOfInOut(NN, 11) <> "") Or _
(ValOfInOut(NN, 9) <> "" And ValOfInOut(NN, 10) <> "") Then msgNoIO(9) = 1
If ValOfInOut(NN, 3) <> "" And _
ValOfInOut(NN, 5) <> "" And ValOfInOut(NN, 8) = "" Then msgNoIO(10) = 1
If (ValOfInOut(NN, 5) <> "" Or ValOfInOut(NN, 7) <> "" Or _
ValOfInOut(NN, 11) <> "") And (ValOfInOut(NN, 6) <> "" Or _
ValOfInOut(NN, 9) <> "" Or ValOfInOut(NN, 10) <> "") Then msgNoIO(11) = 1
If Application.Sum(msgNoIO) > 0 Then '整合性に何か問題がある場合
OKNG = False
For KK = 1 To UBound(msgNoIO)
If msgNoIO(KK) = 1 Then
rowToWrite = rowToWrite + 1
wsWork.Cells(rowToWrite, 1) = "入出庫表 " & NN & "行目の" & ValOfInOut(NN, 1)
wsWork.Cells(rowToWrite, 2) = errMsgIO(KK)
End If
Next KK
End If
Next NN
End If
dataValidation = OKNG
End Function
Private Sub displayResult(wsBal, arrayToPrint) '製品在庫に書き出し
Dim colToWrite As Long, NN As Long
Dim rowToWrite As Long
Application.ScreenUpdating = False
wsBal.Range("A2:M1000").ClearContents
For rowToWrite = 2 To UBound(arrayToPrint) + 1
For colToWrite = 1 To 13
With wsBal
.Cells(rowToWrite, colToWrite).Value = arrayToPrint(rowToWrite - 1)(colToWrite)
End With
Next colToWrite
Next rowToWrite
Application.ScreenUpdating = True
End Sub
Private Sub shiftNdelete(wsHist, wsInOut, wsBal) '履歴処理
Dim rowsInOut As Long, rowsBal As Long
Dim NN As Long
'転記処理
Application.EnableEvents = False
Application.ScreenUpdating = False
rowsInOut = wsInOut.Cells(wsInOut.Rows.count, "A").End(xlUp).Row
For NN = 2 To rowsInOut
If Left(wsInOut.Cells(NN, "M"), 3) <> "転記済" Or _
wsInOut.Cells(NN, "D") <> "" Then '未転記レコード、又は入出庫日欄に入力あるもの
wsInOut.Cells(NN, "M").Value2 = Format(Now, "転記済 yyyy-mm-dd hh:mm")
wsInOut.Rows(NN).Copy wsHist.Cells(Rows.count, "A").End(xlUp).Offset(1)
End If
Next NN
'実績に移ったデータ行を下方から行削除する
rowsInOut = wsInOut.Cells(wsInOut.Rows.count, "A").End(xlUp).Row
For NN = rowsInOut To 2 Step -1
If wsInOut.Cells(NN, "D") <> "" Then '入出庫日に記入あり
wsInOut.Rows(NN).Delete
End If
Next NN
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Private Sub 総計比較(valOfBal, ValOfInOut, ByRef 関所 As 総計, idx)
Dim pos As Long
For pos = 2 To UBound(valOfBal)
'製品在庫
関所.製品在庫残(idx) = 関所.製品在庫残(idx) + valOfBal(pos, 6)
If idx = 2 Then '更新後
関所.製品引当数(idx) = 関所.製品引当数(idx) + valOfBal(pos, 7)
関所.製品発注数(idx) = 関所.製品発注数(idx) + valOfBal(pos, 8)
End If
Next pos
For pos = 2 To UBound(ValOfInOut)
' 入出庫表 = 入出庫表
If ValOfInOut(pos, 4) <> "" Then
関所.日付がある入出庫(idx) = 関所.日付がある入出庫(idx) + ValOfInOut(pos, 7) - ValOfInOut(pos, 9)
End If
関所.引当数(idx) = 関所.引当数(idx) + ValOfInOut(pos, 10)
関所.発注数(idx) = 関所.発注数(idx) + ValOfInOut(pos, 11)
Next pos
End Sub
(2)「入出庫」のシートモジュールに貼り付けるマクローーーーーーーーーーーーーーーーー
'Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.count > 1 Or Target.Value2 = "" Then Exit Sub
If Target.Column = 27 And Target.Row > 1 Then 'AA列 がダブルクリックされた
If Cells(Target.Offset(0, 3).Value2, "F").Value2 <> "" Then '納入先が記入されている
Cancel = True
Application.EnableEvents = False
Cells(Target.Offset(0, 3).Value2, "C").Value2 = Target.Value2 '在庫IDを補充
' Cells(Target.Offset(0, 3).VAlue2, "H").VAlue2 = Target.Offset(0, 1).VAlue2 '賞味期限を補充(不要？)
Range("AA1:AD500").Clear
Application.EnableEvents = True
End If
End If
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim wsBal As Worksheet
Dim NN As Long
Dim valOfBal As Variant
Dim ValOfInOut As Variant
Dim CodeDic As Object
Dim IDkey As Variant
Dim Mcode As Variant
Dim Cname As Variant
Dim dataToPush(1 To 1, 1 To 4) As Variant
Dim dataToStore As Variant
Dim rowToWrite As Long
Dim oldestDate As Variant
Dim oldestRow As Long
If Target.count > 1 Then Exit Sub
If Target.Column = 27 And Target.Row > 1 Then 'AA列のワンセルが右クリックされた
Cancel = True
Application.EnableEvents = False
Range("AA1:AA1000").Clear 'AB列は書式もクリア(色を消す)
Range("AB1:AD1000").ClearContents '入出庫の入力支援エリアのデータクリア
Application.EnableEvents = True
Mcode = Target.EntireRow.Cells(1, "A").Value2
Cname = Target.EntireRow.Cells(1, "F").Value2
If Mcode <> "" And Cname <> "" Then '商品コードがあって、納入先の記入がある場合
Set CodeDic = CreateObject("Scripting.dictionary") ' 連想配列で商品コードを管理
Set wsBal = Sheets("製品在庫")
valOfBal = _
wsBal.Range("A1", wsBal.Cells(wsBal.Rows.count, "A").End(xlUp).Offset(0, 10)).Value2
ValOfInOut = _
Range("A1", Cells(Rows.count, "A").End(xlUp).Offset(0, 12)).Value2
Rem 製品在庫からのデータ集め
For NN = 2 To UBound(valOfBal)
If valOfBal(NN, 1) = Mcode Then '商品コードが合致
IDkey = valOfBal(NN, 3) '入庫IDをキーにする
If CodeDic.Exists(IDkey) Then
dataToStore = CodeDic(IDkey)
If dataToStore(1, 2) <> valOfBal(NN, 11) And _
dataToStore(1, 2) <> "" And valOfBal(NN, 11) <> "" Then
MsgBox IDkey & "は入庫IDが同じで賞味期限が異なるデータがあります→処理中止"
Exit Sub
Else
dataToStore(1, 3) = dataToStore(1, 3) + valOfBal(NN, 6)
CodeDic(IDkey) = dataToStore
End If
Else '新規
dataToPush(1, 1) = valOfBal(NN, 3) '在庫ID
dataToPush(1, 2) = valOfBal(NN, 11) '賞味期限
dataToPush(1, 3) = valOfBal(NN, 6) '在庫残
dataToPush(1, 4) = Target.Row 'トリガーとなったtargetの行をメモ
CodeDic.Add IDkey, dataToPush
End If
End If
Next NN
Rem 入出庫表からのデータ集め
For NN = 2 To UBound(ValOfInOut)
If ValOfInOut(NN, 1) = Mcode And ValOfInOut(NN, 3) <> "" And _
ValOfInOut(NN, 4) <> "" Then '商品コードが合致、入庫IDと入出庫日が決定している
IDkey = ValOfInOut(NN, 3) '入庫IDをキーにする
If CodeDic.Exists(IDkey) Then '入庫IDが既存
dataToStore = CodeDic(IDkey)
If dataToStore(1, 2) <> ValOfInOut(NN, 8) Then
If ValOfInOut(NN, 8) <> "" Then
MsgBox IDkey & "は入庫IDが同じで賞味期限が異なるデータがあります→処理中止"
Exit Sub
End If
End If
dataToStore(1, 3) = dataToStore(1, 3) + ValOfInOut(NN, 7) _
- ValOfInOut(NN, 9)
CodeDic(IDkey) = dataToStore
Else '入庫IDが新規
dataToPush(1, 1) = ValOfInOut(NN, 3) '在庫ID
dataToPush(1, 2) = ValOfInOut(NN, 8) '賞味期限
dataToPush(1, 3) = ValOfInOut(NN, 7) _
- ValOfInOut(NN, 9) '入庫数-出庫数
dataToPush(1, 4) = Target.Row 'トリガーとなったtargetの行をメモ
CodeDic.Add IDkey, dataToPush
End If
End If
Next NN
rowToWrite = Target.Row
'タイトル記入
Application.EnableEvents = False
Cells(rowToWrite - 1, 27).Resize(1, 4).Value2 = [{"在庫ID","賞味期限","在庫","行番号"}]
Application.EnableEvents = True
If CodeDic.count > 0 Then
For Each dataToStore In CodeDic.Items
' If dataToStore(1, 3) > 0 Then ’在庫がゼロチェック不要
If oldestDate = 0 Then
oldestDate = dataToStore(1, 2)
oldestRow = rowToWrite
ElseIf dataToStore(1, 2) < oldestDate Then
oldestDate = dataToStore(1, 2)
oldestRow = rowToWrite
End If
Application.EnableEvents = False
Cells(rowToWrite, 27).Resize(1, 4).Value2 = dataToStore
Application.EnableEvents = True
rowToWrite = rowToWrite + 1
' End If
Next
End If
CodeDic.RemoveAll
If oldestRow = 0 Then
Application.EnableEvents = False
Cells(Target.Row, "AA").Value2 = "無し"
Application.EnableEvents = True
Else
Cells(oldestRow, "AA").Interior.ColorIndex = 6 '最も古い賞味期限のセルを黄塗り
End If
End If
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsMast As Worksheet
Dim cel As Range
Dim CodeList As Range, CodeNameList As Range
If Target.count > 200 Then
MsgBox "対象セルが多すぎます。超品名の自働検索中止"
Exit Sub
End If
If Target.Column = 1 Then
'商品名リストを把握(商品名検索準備)
Set wsMast = Worksheets("商品コード")
Set CodeList = wsMast.Range("A2", wsMast.Range("A1000").End(xlUp)) 'コードリスト
Set CodeNameList = CodeList.Resize(, 2)
With Application
For Each cel In Target
If cel.Column = 1 And cel.Value2 <> "" Then
If .CountIf(CodeList, cel.Value) > 0 Then
.EnableEvents = False
cel.Offset(, 1).Value = .VLookup(cel.Value2, CodeNameList, 2, False)
.EnableEvents = True
Else
.EnableEvents = False
cel.Offset(, 1).Value = "Missing"
.EnableEvents = True
End If
End If
Next
End With
End If
If Target.count > 1 Then Exit Sub
If Target.Value = "" Then
Exit Sub
ElseIf Target.Column = 7 Then 'G列=入庫数欄
Application.EnableEvents = False
Target.EntireRow.Cells(1, "K").ClearContents '発注数をクリア
Application.EnableEvents = True
ElseIf Target.Column = 9 Then 'I列=出庫数欄
Application.EnableEvents = False
Target.EntireRow.Cells(1, "J").ClearContents '引当数をクリア
Application.EnableEvents = True
End If
End Sub
(3)クラスモジュール用ーーー(オブジェクト名をClass1から「商品」にするのをお忘れなく)−−−−
'Option Explicit Private count As Long '入庫ID総数管理
Private 商品コード(0) As Variant '1つのみ Private 商品名(0) As Variant Private 入庫ID() As Variant '動的配列１-3 Private 発注点(0) As Variant Private 総在庫(0) As Variant Private 在庫() As Variant '動的配列２-6 Private 引当数(0) As Variant Private 発注数(0) As Variant Private 有効在庫数(0) As Variant Private 有効残数(0) As Variant Private 賞味期限() As Variant '動的配列３-11 Private 発注要否(0) As Variant Private 備考() As Variant '動的配列４-13
Public Sub MaCodeInit(MaCode, MaName) '初期値設定(商品コード・商品名をセット)
商品コード(0) = MaCode
商品名(0) = MaName
ReDim 入庫ID(0)
ReDim 在庫(0)
ReDim 賞味期限(0)
ReDim 備考(0)
End Sub
Private Sub expandArray(ByVal count) '動的配列の拡張
ReDim Preserve 入庫ID(count)
ReDim Preserve 在庫(count)
ReDim Preserve 賞味期限(count)
ReDim Preserve 備考(count)
End Sub
Public Sub update(PP, ByRef valDataAry, fromBal As Boolean) '在庫情報を集計
Dim KK As Long, posToProc As Long, IdExist As Boolean
Dim idx As Long
'製品在庫と入出庫共通処理
If valDataAry(PP, 3) <> "" Then '入庫IDがある場合、それが既存かチェック
For KK = 0 To count
If 入庫ID(KK) = valDataAry(PP, 3) Then '既存
IdExist = True
posToProc = KK '処理対象の配列の位置を掌握
Exit For
End If
Next
If IdExist = False Then
count = count + 1
Call expandArray(count)
posToProc = count '処理対象の配列の位置を掌握
End If
End If
If fromBal Then '製品在庫からのデータ更新
If valDataAry(PP, 3) <> "" Then '在庫IDがある場合
'動的配列データを処理
入庫ID(posToProc) = valDataAry(PP, 3)
在庫(posToProc) = valDataAry(PP, 6)
賞味期限(posToProc) = valDataAry(PP, 11)
備考(posToProc) = valDataAry(PP, 13)
End If
'共通処理→全て0次にまとめる
総在庫(0) = 総在庫(0) + valDataAry(PP, 6)
発注点(0) = IIf(発注点(0) = "", valDataAry(PP, 4), 発注点(0))
Else '入出庫表からのデータ更新
If valDataAry(PP, 3) <> "" Then '入庫ID情報が含まれている場合
入庫ID(posToProc) = valDataAry(PP, 3)
在庫(posToProc) = 在庫(posToProc) + valDataAry(PP, 7) - valDataAry(PP, 9) '入出庫差引で算出
If 賞味期限(posToProc) = "" Then
賞味期限(posToProc) = valDataAry(PP, 8)
ElseIf valDataAry(PP, 8) <> "" And 賞味期限(posToProc) <> valDataAry(PP, 8) Then
MsgBox 入庫ID(posToProc) & "は賞味期限が不統一です。→ 処理中止"
End '強制終了
End If
End If
Rem 入出庫表からのデータは、入庫IDの有無に関わらずこの処理は行う
総在庫(0) = 総在庫(0) + valDataAry(PP, 7) - valDataAry(PP, 9)
引当数(0) = 引当数(0) + IIf(valDataAry(PP, 9) = 0, valDataAry(PP, 10), 0) '出庫数が無い場合のみ加算。
発注数(0) = 発注数(0) + IIf(valDataAry(PP, 7) = 0, valDataAry(PP, 11), 0) '入庫数が無い場合のみ加算
End If
End Sub
Public Property Get Balance()
Balance = 在庫()
End Property
Public Property Get code()
code = 商品コード(0)
End Property
Public Property Get PrintLine(IdNum, isLast As Boolean) '出力データ作成
Dim oneRec(1 To 13) As Variant
oneRec(1) = 商品コード(0)
oneRec(2) = 商品名(0)
oneRec(3) = 入庫ID(IdNum)
oneRec(4) = IIf(isLast, 発注点(0), Empty)
oneRec(5) = IIf(isLast, 総在庫(0), Empty)
oneRec(6) = 在庫(IdNum)
oneRec(7) = IIf(isLast, IIf(引当数(0) = 0, Empty, 引当数(0)), Empty)
oneRec(8) = IIf(isLast, IIf(発注数(0) = 0, Empty, 発注数(0)), Empty)
oneRec(9) = 有効在庫数(0)
oneRec(10) = 有効残数(0)
oneRec(11) = 賞味期限(IdNum)
oneRec(12) = 発注要否(0)
oneRec(13) = 備考(IdNum)
PrintLine = oneRec
End Property
’以上です
（半平太） 2015/04/13(月) 10:51 →全面改定(2015/04/15(水) 17:36)
短期間でこのような素晴らしいプログラムを
ありがとうございます！
まだプログラムを試せていませんが、
早く使いたいとワクワクしています。
翌日には、導入・試験を行えるようにします。
（うめ） 2015/04/13(月) 22:09
バクを一つ見つけてしまった。（汗 あと、ロジカルバグらしきものも一つ。
１．バグ 同じ入庫IDなのに、異なる賞味期限が入っているデータがあった場合、 (通常は起こらない入力ミスなんですけど、万一あった場合)
事前チェックには引っかからず、 更新プロセスで「賞味期限が不統一の為、処理中止」と云う警告は出るものの、 実際は中止にならず、データを更新してしまう。
この解決は、事前チェックの方で通さない様にすればいいので、そんなに面倒じゃないです。、
2．ロジカルミスらしきもの 以前、1行の中に入庫ファクタ(仕入れ先、入庫数、発注数）と出庫ファクタ(納入先、出庫数、引当数）が 混在することがあるとお聞きしましたが、そこで入庫数と出庫数が入っているなら問題はないですけど(通常はこうなるのでしょうが） 入出庫日、入庫数、納入先、引当数が、同時に入っていた場合、更新後も客のオーダー(引当数）は入出庫表に残らないといけないのしょうが、 入出庫日が入っている行は削除される運命にあるので、客のオーダーは消えてしまう。
こっちはかなり厄介な気がします。 ・・と云うか、どうなればいいのか私の一存では決められません。 データの入力ミスと見なして削除してしまうのか、 入出庫日が入っていても入庫の処理後、入庫日と入庫ファクタをクリアにして、その行は生かし続けるのか はたまた、何かの条件で判断して、削除／生かす、を分岐させるのか
（半平太） 2015/04/14(火) 12:08
ご報告ありがとうございます。
＞ロジカルミスらしきもの
厄介ですね…
こちらは、以前に相談した入庫と出庫を完全に分けて対処したいと思います。
入力ミスも減りそうですし。
申し訳ありませんが、以下の整合性の追加をお願いしたいのですが、よろしいでしょうか。
＞(2)出庫ファクタ(納入先、出庫数、引当数のどこか）に何かデータが入っているのに ＞入庫ファクタ(仕入先、入庫数、発注数のどこか）にもデータが入っている。
最初から、入庫と出庫を分けておけばよかったです…
面倒なことになってしまい、申し訳ありません。
以上、よろしくお願いします。
（うめ） 2015/04/14(火) 12:29
入庫と出庫が別行になるということであれば切り分けがすごく簡単になります。
そうじゃなくても、もっとズーット簡単に作れたなぁと、今、思っている所です。
なので、テストを中止していただけますか？
もっと簡単なプログラム(考え方)で処理しようと思っております。
今は漠然としたアイデアなので具現化できたら、またアップします。
数式の入力列についてのご確認をお願いしているところなので、そちらの回答をお願いします。
と言うか、それも数式ではなく、プログラムで一緒に処理した方が楽なんですけどね。・・ （どんな数式なのかお聞きしていないので、考え方だけの話ですけど、今は・・）
（半平太） 2015/04/14(火) 13:46
色々と考えてくださり、ありがとうございます！
一旦テストを中止します。
＞＞３．たしか、製品在庫シートで、数式で対応する列があったと思うのですけど、
＞＞ 以下の列と考えていいですね。その列は、数式が壊れない様に上書きの対象から外します。
＞＞ 商品名(B)、有効在庫数(I)、有効残数(J)、発注要否(L)
＞数式の入力列についてのご確認をお願いしているところなので、そちらの回答をお願いします
回答をしていなくて申し訳ありません。
回答を忘れていました・・・
商品名(B2)
=IF(A2=""),"",VLOOKUP(A2,商品コード!A2:B10,2))
有効在庫数(I2)
=E2-G2
有効残数(J2)
=(E2+H2)-G2
発注要否(L2)
=IF((I2<=D2),"要","")
＞と言うか、それも数式ではなく、プログラムで一緒に処理した方が楽なんですけどね。・・
そうだったんですか・・・
プログラムより数式のほうが楽かと思っていました。
以上、よろしくお願いします。
（うめ） 2015/04/14(火) 20:55
漠然とシンプルになりそうと思ったのですが、 実際トライしてみたら、ゴチャゴチャした部分は結局変わらなかったので 以前の案にチェックを強化する路線で行くことにします。 m(__)m
１．チェック強化の内容 (1)出庫ファクタ(納入先、出庫数、引当数のどこか）と 入庫ファクタ(仕入先、入庫数、発注数のどこか）が混在している。
(2)同じ入庫IDなのに、異なる賞味期限が入っているデータがある。
(3)更新前後で総計比較 処理結果が総計ベースで理論値と合うか検証する。
※処理が終わると、総計ベースで「合致か不一致か」メッセージが出ます。 ダメなことが分かったってどうなるか、と云われそうですけど、 兎に角、不安でしょうがなかった。
総計チェック 処理前 処理後の理論値
------------------------------------------------
製品在庫 商品Code毎小計 商品Code毎小計
在庫残 A#1 A#1+A#2-A#3
引当数 無視 A#4
発注数 無視 A#5
入出庫表
日付がある入庫 A#2
日付がある出庫 A#3
引当数 A#4 A#4
発注数 A#5 A#5
２．数式で処理している部分はマクロで対応する
書き直しをアップしますが、量が多いので、前回レスしたコードに上書きします。 ↓ ＞2015/04/13(月) 10:51 →全面改定(2015/04/15(水) 17:36)
しかし、大したことないと思って始めたのですが、かなり厄介です。 専門家に頼んだ方がよさそうな気がします。
1ケ月もしたら、私自身、どうメンテしていいか分からなくなりそうな予感です。
多分、考え方から変えた方がいいような気がしています。 アウトプットが簡単に見えて、結構、込み入ったロジックが必要になっています。 私だけかも知れないですけど (^^ゞ
（半平太） 2015/04/15(水) 17:44
請け負った以上、米寿位まで面倒見ないとだめですよ＾＾ なあんて言いますが、やっぱり凄いですね。 コードもですが、論理的な思考、大変勉強になります。
（稲葉） 2015/04/15(水) 18:22
ありがとうございます。
翌日には導入・試験を行います。
＞ しかし、大したことないと思って始めたのですが、かなり厄介です。
＞ 専門家に頼んだ方がよさそうな気がします。
厄介ですか・・・
そうですよね。
今回のやり取りを踏まえて専門家と相談します。
＞ 多分、考え方から変えた方がいいような気がしています。
在庫管理のやり方を変えたほうがいいということでしょうか。
このサイトの趣旨と違う気がしますが、
今後はこれ以上に複雑な管理が要求されると考えています。
こういった仕組みづくりを考えるうえで、
参考になる本やサイトなど教えていただければ嬉しいです。
以上、よろしくお願いします。
（うめ） 2015/04/15(水) 21:33
稲葉さん
前にも書きましたけど、貴兄が参考になるようなものは私には作れません。
ただこの程度のことは、今でも大したことないハズなんだけどなぁと思っています。 ・・と言ったところで、出来なかったじゃないかと云われると引っ込むしかないです。
＞請け負った以上、米寿位まで面倒見ないとだめですよ
業務のやり方がよく分からなかったのが、ちょっとわざわいした所はあります。 ・・が、だんだん分かってきたので、もう一回トライしてもいい気にはなっています。 そこそこ面白い案件です。
困ったところは、つまんない所なんですけど、 在庫がある場合はその最終行に引数・発注数を一緒に書く、 在庫が無い場合は、引数・発注数を単独で書出す、 と云うのに引っ掛かかっちゃったんです。
普通の人だったら、そんなの大してことないですよね、 私もそう思うのですけど、2回考えたのに自信持てるものが出来なかった。
逆に言うとそこだけなんで、やっぱり大したことはないハズだと思っちゃうんですよねぇ。
うめさん
＞＞ 多分、考え方から変えた方がいいような気がしています。 ＞在庫管理のやり方を変えたほうがいいということでしょうか。
いや、これは私自身のアプローチの仕方に問題があるのではないかと云う意味合いが強いです。 もっとスマートにできそうなものなので。。
勿論、専門家なら別の(信頼性の高い）解決策を持っている可能性は高いです。 今回、最初に困ったのは、入力データの信頼性です。 凄く入力ミスが起こりそうな入出庫表なので、これには注文が付くと思いますよ。
入力画面の設計が済めば、多分終わったも同然なんじゃないですかね、専門家なら。
ユーザーとしては、どういうことをしたいのかをまとめればいいです。 最小限のデータ入力で最大限の使い勝手と管理機能が得られるように求めればいいです。 当然のことながら、最小限のデータは何なのか理解できている必要があります。 (こんだけのデータがあれば、こんなもんが作れるハズだー、と云う見極めです）
私としては、100行位の情報管理に会社が金を掛けるかなぁとは思っています。 「アナログでやります」と云われた時は、泣き顔で言っているのだろうなぁと思っていましたが、 実際、アナログでもできそうな量と云う気もしています。
＞今後はこれ以上に複雑な管理が要求されると考えています。 ＞こういった仕組みづくりを考えるうえで、 ＞参考になる本やサイトなど教えていただければ嬉しいです。
そうなんですか。私は専門外なのでよく分かりません。m(__)m
（半平太） 2015/04/15(水) 23:13
再度トライするとしたら、こんな感じですかね。
(データ量が少ないので、更新後の状態に問題があったら元に戻れるようにも作れそう)
１．データの整合性チェック（製品在庫と入出庫表） ２．現在の商品別・入庫ID別残高のマイナスチェック ３．更新後の在庫マイナス残発生予見チェック
４．現況データの疎開（他ブックではなく、本ブックの他シートに疎開）
５．製品在庫と入出庫表から「作業」シートに実在商品を抽出する 「作業」シート上で、商品別・在庫ID別に集計する 再度、マイナス残がないかチェックする
６．上記５のチェックで問題がなければ、 作業シートを利用して製品在庫を再作成 入出庫表の引当数・発注数を集計して、製品在庫に結果をシフト (製品在庫に該当がない商品は、行を追記する)
入出庫表から履歴シートに転記 入出庫表から入出庫日の入った行を削除
７．最終チェック 更新後のデータが、疎開データから作った理論値と合うか総計チェックを行う。 このチェックで問題が発見されれば、疎開データを元に戻して、原因解明作業にはいる。
（半平太） 2015/04/16(木) 12:15
まとまった時間が取れなかったため、自宅のパソコン(エクセル2010)でテストをしています。
テストの方法まで教えていただきありがとうございます。
私の理想としていた在庫表です！
とても扱いやすくて、出庫時の入力支援は感動しました。
作業中に気になったことがあります。
賞味期限は、2015/4/16で入庫処理をすると、
42220など5ケタの数字で在庫表に表示されます。
対策としては、数字を直接打ち込むと問題ありませんでした。
特に問題はないのですが、少し気になったので聞いてみました。
こちらは、会社のパソコン(エクセル2003)でも確認しています。
簡単であればもう一つ機能を追加していただきのですが・・・
在庫表シートで商品コードが変わると、同じ商品コード塗りつぶし。
また違う商品コードになると、塗りつぶしなし。
例
商品コード 1 a-1 塗りつぶしなし 2 a-1 塗りつぶしなし 3 b-4 塗りつぶしあり 4 ttg 塗りつぶしなし 5 ttg 塗りつぶしなし
在庫が少し見にくかったので、できればよろしくお願いします。
＞ 凄く入力ミスが起こりそうな入出庫表なので、これには注文が付くと思いますよ。
テスト段階で入庫IDの入力ミスがありました・・・
不整合シートでなんとかわかりましたが、今後の課題が浮き彫りになった気がします。
＞ ユーザーとしては、どういうことをしたいのかをまとめればいいです。
＞ 最小限のデータ入力で最大限の使い勝手と管理機能が得られるように求めればいいです。
＞> 当然のことながら、最小限のデータは何なのか理解できている必要があります。
＞ (こんだけのデータがあれば、こんなもんが作れるハズだー、と云う見極めです）
とても勉強になります。
まだまだ知識・経験ともに不足していますが、このことを考えながら作業をします。
＞ 実際、アナログでもできそうな量と云う気もしています。
私も頑張ればできると思っていました。
ただ、これからのことを考えると、製品在庫だけでも
しっかりと管理できる体制を整えて置かないと
厳しいと考えていました。
（うめ） 2015/04/16(木) 21:27
本件、（参照された元スレ含めて）詳細においかけていませんし、横レスで、せっかく前に進んでいる案件を混乱させるのは本意ではありませんので あくまで、あぁ、そんな例もあるんだなという程度に眺めてください。
・おそらく、関連のデータで、ＤＢとしてのキモは入出庫履歴でしょうね。 ・レイアウトはすこしずつ変わってきているようですが、現時点の 製品在庫表は、ＤＢというより、あくまでＤＢから作り上げる１つのレポートだと考えます。 ・そうすると、在庫をどういった形に持っておくかがポイントになってきますが、ＷＭＳを考えるときに（というか、それを使う業務面での必要要件に） 「時点在庫」というものを把握できるような仕組みを持っておくことが重要になってきます。 ・もう１つ、倉庫業務では宿命になる、「帳簿在庫」と「現物在庫」の差異を考えておく必要があります。 これをあわせるために、どの倉庫でも「棚卸」を行うわけですね。で、ここで出てきた「実棚」との差異を、調整出庫や調整入庫（どちらで行うかは それぞれの会社の決め事でしょうけど）で、必ず、「入出庫履歴」に反映させます。 ・そう考えますと、「在庫ＤＢ」は、 「商品コード」「ロット番号（賞味期限 等）」「棚卸時点（会社により月１であったり週１であったり、その日付」「実棚数量」 現在、アップされている項目群では、これだけに絞るほうがよろしいと思ったりします。（商品名は、別途マスタをもつのが一般的です） ・もちろん、この「実棚」の部分を、入出庫履歴と、直前の「実棚数量」から、「帳簿在庫」を計算して記載。棚卸担当者は、この情報を元に 棚卸を行い、差異を記載していき、最終的には、調整入出庫データを生成するとともに、この時点の在庫数量を確定させて上書きする。
で、じゃぁ、これらを、どれだけの期間持つのかがどの会社にとっても悩ましいテーマになります。過去１００年分を持つ・・・なんてことをすれば データ量も膨大になりますし、処理時間にも影響します。なので、ある程度のみを残し、残りはアーカイブ。必要があればアーカイブ分もあわせた 処理を行ったりしています。（税務調査で５年前の状況を調べるとか、本件は自社倉庫でしょうけど、営業倉庫の場合、荷主から２年前の６月の状況を レポートしろといった要求も、しょっちゅう出ますので）
在庫ゼロなら表示しない（削除する）といったことは、あくまで「レポート編集」として行うことであって ＤＢとしては、ずっと保持しておくことが必要でしょうね。
（β） 2015/04/17(金) 08:57
＞賞味期限は、2015/4/16で入庫処理をすると、 ＞42220など5ケタの数字で在庫表に表示されます。
当初のサンプルでは「20150401」と云うようなデータだったので、 日付シリアル型ではなく、単純な数値型を想定して作成しております。
全部が日付型なのでしたら、後で対処しますので、そこは気にしないでテストしてください。 全部ではない場合、どんな状況で混在することになるのかご説明ください。
＞簡単であればもう一つ機能を追加していただきのですが・・・ ＞在庫表シートで商品コードが変わると、同じ商品コード塗りつぶし。 ＞また違う商品コードになると、塗りつぶしなし。
取りあえず、2行目以下のセルに下記の条件付書式を設定して対応してください。
条件式 =MOD(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2)),2)
あと、現在こちらは、上述した再トライの方向で進めています。
使い方はそう変わらないですが、メンテナンスがやり易いロジックに出来ると思っています。 （商品クラスは消滅させる。更新後に理論値と違った場合は自働で元の状態に復帰する）
これなら、米寿までのサポートは無理としても、 1ヶ月間のサポートを6ヶ月間くらいには伸ばせそう。＾＾ ただし、新機能は不対応です。今回の仕様に関するもののみです。
そんな事情なので、今のテストは中止して頂いて構いません。
ただ、上述の話は裏方の問題であって、ユーザーの使い勝手はそんなに変わらないので、 今のうちに改善の余地を洗い出しておく意味で、テストを続行して頂くのは充分意義あります。 （レイアウトは変更なしでお願いします。列番号を決め打ちで作っているので)
（半平太） 2015/04/17(金) 09:47
半平太さん ＞ これなら、米寿までのサポートは無理としても、 ＞1ヶ月間のサポートを6ヶ月間くらいには伸ばせそう。＾＾ ジョークに付き合っていただきありがとうございます^o^
面白い案件、という意見に関してはとても賛成です！ 自分も混ざりたかったけど、「できないじゃないか」になりそうなので、質問者さんに 発破掛けて退散しました。
うめさん 色々言わせてもらいましたが、めげずに頑張る姿に尊敬致します！ 頑張ってください。 http://www.obc.co.jp/click/bugyo-i/kura/
（稲葉） 2015/04/17(金) 10:01
＞あくまで、あぁ、そんな例もあるんだなという程度に眺めてください。
とても勉強になります！
ありがとうございます。
在庫表＝在庫DBと思っていました。
生産管理の本を読んでいるのですが、棚卸しのやり方に悩んでいました。
少しずつですが、在庫管理のイメーができました。
ありがとうございます。
半平太さん
＞取りあえず、2行目以下のセルに下記の条件付書式を設定して対応してください
早速の対応ありがとうございます。
＞全部が日付型なのでしたら、後で対処しますので、そこは気にしないでテストしてください。
＞全部ではない場合、どんな状況で混在することになるのかご説明ください。
日付型で統一しようと考えています。
ただ後述していますが、入庫時の自動入力をしたいと考えています。
その際に、数値型のほうがよいのかな、と考えていて悩んでいます・・・
＞ただ、上述の話は裏方の問題であって、ユーザーの使い勝手はそんなに変わらないので、
＞今のうちに改善の余地を洗い出しておく意味で、テストを続行して頂くのは充分意義あります。
＞（レイアウトは変更なしでお願いします。列番号を決め打ちで作っているので)
テストを行っている際に、改善したい点が4点できました。
1.発注点の自動入力
申し訳ありません、手動と指定していたのですが、在庫表を行ごと消すのを忘れていました・・・
ですので、商品名と同じように、商品コードが在庫表に入力されると発注点が自動入力されるようにしたいと考えています。
「商品コード」シートのC列に発注点を追加しようと考えているのですが可能でしょうか。
2.入庫時の入庫IDの自動入力
商品コード、仕入先、入出庫日を入力すると、入庫IDが自動入力される。
入庫ID「入出庫日＋商品コード」を考えています。
この場合は、入出庫日は数値入力のほうがよいのでしょうか。
出庫時の入庫ID入力支援機能が便利過ぎて、入庫時もあれば、入力ミス減らせて嬉しいです。
3.在庫表の発注要否の「要」を赤色
細かいですが、意外と見落としてしまうかもしれないので・・・
4.在庫表の商品が、賞味期限3ヶ月前や2ヶ月になると、セルの色が変化
この機能は、商品コードの塗りつぶしと被ることになると思いますが、
文字の色の変更でも可能でしょうか。
貴重な時間を割いていただいているのに、まとまった時間を取れずに申し訳ありません。
思いつきで書いてる？と思われる部分もあるかと思いますが、よろしくお願いします。
稲葉さん
応援ありがとうございます。
参考サイトも載せていただきありがとうございます。
見るだけでも、今後の在庫管理に必要な仕組みや機能がわかり、勉強になります！
（うめ） 2015/04/18(土) 01:17
以下、再トライ案です。
※コードは最後尾に記します。
※βさんのアドバイスを加味して、シート構成は以下とします。 無いものはシートを挿入して、シート名をそれらに変更してください。
ーーーシート構成ーーーーーーーーーーーーーーー （1)既存シート 入出庫表 入出庫履歴 商品コード 不整合
(2)シート名変更 製品現状 ← 製品在庫から変更（機能は従来どおり） ※在庫DBとは、在庫が0となったレコードも存在すべきであり、 このシートにそれはなく、現時点の在庫、引当、発注の集計結果であるとの解釈から。 在庫DBに近いものは、後述する時点在庫で作ります。(まぁ、それもレポート的な性格ですけどね）
・・とは言いながら、今回の作りでは「製品現状」の数値は在庫計算のベースにしておりますので、
「製品現状」シートを軽視しないようにしてください。
(3)追加シート 疎開現状 ←バックアップ用 疎開入出←バックアップ用 疎開履歴←バックアップ用 時点在庫←指定日における在庫状況表(引当数・発注数は無視) 作業 ←集計作業用
ーーー再トライ案の流れーーーーーーーーーーーーーーーーーーー
____________________ 概要 ＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿ _______________ 備考 ____________
１．データの整合性チェック（製品現状と入出庫表） 前案と同じ
２．現在の商品別・入庫ID別残高のマイナスチェック 前案と同じ
３．更新後の在庫マイナス残発生予見チェック 前案と同じ
４．製品現状と入出庫表のデータを使用して、商品別・在庫ID別に集計する 商品クラスの役割の代わりは
(更新後の製品現状を作業シート上に作成する) 「作業」シートで行う
※この過程で、念のためマイナス残がないかも再チェックする 商品クラスは不要化する。
５．現況データの疎開（他ブックではなく、本ブック内のシートに疎開） 前案は別名ブックへバックアップする
製品現状 →疎開製品シート こととしていた。
入出庫表 →疎開入出シート
入出庫履歴 →疎開履歴シート
６．製品現状へ、４で出来た作業シートの表を移す 作業シートを使うので 集計ロジックや
集計結果が検証し易くなる。
７. 入出庫表から履歴シートに転記 前案と同じ
入出庫表から入出庫日の入った行を削除 前案と同じ
８．最終チェック
更新後のデータが、疎開データ(=更新前データ）で算出した理論値と合うかどうか
総計チェックを行う。もし、不一致があれば、疎開データを自働的に元に戻す。 前案はバックアップファイルを使用して手動で復旧としていた。
※不一致原因究明は、作業シート(=更新後の製品現状と同じ）をチェックすることになります。
※数式での対応を一部復活させます。 「製品現状」内に数式を自働入力する(計算結果の自働入力ではありません)
※時点在庫は、指定日における商品毎・入庫ID別のネット残を算出します。 入出庫履歴のデータを使用します。引当数・発注数は考慮外です。
マクロ「balanceOnAday」を実行すると、作成基準日を聞かれますので、 それに応えると、基準日時点の在庫数(あるべき数値）が作成されます。
デフォルトでは、一番新しい入出庫日が基準日になります。
なので、単にOKボタンをクリックすれば最新時点の在庫が集計されます。 入庫履歴の記録が運用開始以降の全部揃っていれば、その商品別残は、「製品現状」の残と一致します。
※実棚との過不足対応（普通は不足しかないでしょうけど。。） 調整用のデータは入出庫表に入力してください。
常識的には、こんなデータを入力することになると思います。 入出庫日=調整実施日、納入先=棚卸調整減、発注先=棚卸調整増、出庫数=不足数、入庫数=余分数
※賞味期限と入出庫日は、日付型で統一するものとします。 もし以前のような(大きな）数値が入っているとトラぶりますので、 賞味期限や入出庫日付を全部日付型に変更してから、諸テストを行ってください。
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
＞入庫時の自動入力をしたいと考えています。 ＞その際に、数値型のほうがよいのかな、と考えていて悩んでいます・・・
これについては、プログラム側で対応しますので、全て日付型で統一してください。
＞1.発注点の自動入力 ＞ 申し訳ありません、手動と指定していたのですが、在庫表を行ごと消すのを忘れていました・・・ ＞ ですので、商品名と同じように、商品コードが在庫表に入力されると発注点が自動入力されるようにしたいと考えています。 ＞ 「商品コード」シートのC列に発注点を追加しようと考えているのですが可能でしょうか。
下記レイアウトで作成してください。
<商品コード > 行 _A_ ___B___ _C_ 2 a-1 商名A1 14 3 a-2 商名A2 4 a-3 商名A3 5 B-1 商名B1 8 6 B-2 商名B2 9
＞2.入庫時の入庫IDの自動入力 ＞ 商品コード、仕入先、入出庫日を入力すると、入庫IDが自動入力される。 ＞ 入庫ID「入出庫日＋商品コード」を考えています。 ＞ この場合は、入出庫日は数値入力のほうがよいのでしょうか。 ＞ 出庫時の入庫ID入力支援機能が便利過ぎて、入庫時もあれば、入力ミス減らせて嬉しいです。
入庫日が入っていて、出庫ファクタの欄が何も入力されていないと云う条件で、 その行の入庫ID欄(C列）のセルをダブルクリックすると、在庫IDが自働的に入るようにしました。
<入出庫表 結果図> 行 _____A_____ ___B___ _____C_____ ____D____ 1 商品コード 商品名 入庫ID 入出庫日 2 B-1 商名B1 20150418B-1 2015/4/18 ↑ ダブルクリックすると出てくる。
＞3.在庫表の発注要否の「要」を赤色 ＞ 細かいですが、意外と見落としてしまうかもしれないので・・・
これは条件付き書式でも対応できそうですけど？
＞4.在庫表の商品が、賞味期限3ヶ月前や2ヶ月になると、セルの色が変化 ＞ この機能は、商品コードの塗りつぶしと被ることになると思いますが、 ＞ 文字の色の変更でも可能でしょうか。
ここはまず、条件を整理した方がいいと思います。 2003の条件付き書式は3つの色までなので、場当たり的に対処すると行き詰ってしまいます。 何に何色使うのか整理してください。
まぁ、マクロを使っているので、最終的には何とでもなるところですけど、まずは整理です。
別のアプローチとしては、要チェックの商品を別途リストアップする方法もあります。 それか、その作業の時だけ要チェックの商品に色づけして、終わったら、その色付けは解除する、なんて手もあります。
上の方で触れた、プログラムで自働的に「製品現状に数式を入れる」件ですが、 以下の数式が自動入力されます。適否をご確認ください。
(1) B2セル =IF(A2="","",IF(COUNTIF(商品コード!A:A,A2),VLOOKUP(A2,商品コード!A:B,2,FALSE))) (2) D2セル =IF(A2=A3,"",IF(B2="","",VLOOKUP(A2,商品コード!A:C,3,FALSE))) (3) I2セル =IF(A2<>A3,E2-G2,"") (4) J2セル =IF(A2<>A3,I2+H2,"") (5) L2セル =IF(A2<>A3,IF(J2<=D2,"要",""),"") ↑ ここは、I2ではなく、J2ですよね？
<製品現状 結果図> 行 _____A_____ ___B___ _____C_____ ___D___ ___E___ __F__ ___G___ ___H___ _____I_____ ____J____ ____K____ ____L____ 1 商品コード 商品名 入庫ID 発注点 総在庫 在庫 引当数 発注数 有効在庫数 有効残数 賞味期限 発注要否 2 a-1 商名A1 20150102a-1 1 1 2015/2/2 3 a-1 商名A1 20150203a-1 14 27 26 12 15 15 2015/3/1 4 b-1 商名B1 20150204b-1 8 5 5 5 5 2015/4/1 要
<時点在庫>最新日までを指定した場合 行 _____A_____ ___B___ ________C________ ____D____ ____E____ ___F___ ___G___ ____H____ ___I___ ________J________ 1 商品コード 商品名 入庫ID 入出庫日 仕入れ先 納入先 入庫数 賞味期限 出庫数 在庫ID別ネット残 2 a-1 商名A1 20150101a-1 2015/1/1 自社工場 13 2015/2/1 3 a-1 商名A1 20150101a-1 2015/1/3 お客 8 4 a-1 商名A1 20150101a-1 2015/1/6 お客 5 5 20150101a-1 集計 13 13 0 6 a-1 商名A1 20150102a-1 2015/1/2 自社工場 26 2015/2/2 7 a-1 商名A1 20150102a-1 2015/1/6 お客 25 8 20150102a-1 集計 26 25 1 9 a-1 商名A1 20150203a-1 2015/2/3 自社工場 26 2015/3/1 10 20150203a-1 集計 26 0 26 11 b-1 商名B1 20150204b-1 2015/2/4 自社工場 15 2015/4/1 12 b-1 商名B1 20150204b-1 2015/4/2 顧客1 10 13 20150204b-1 集計 15 10 5
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
貼り付けるマクロ
なお、以前作った商品クラスは削除してください。 ※削除方法 「商品」を右クリック→商品の開放を選択→削除する前の商品をエキスポートしますか？の問に「いいえ」で対応
(1)標準モジュールに
’Option Explicit
Const wsBalName As String = "製品現状" Const wsInOutName As String = "入出庫表" Const wsHistoryName As String = "入出庫履歴" Const wsUnmatchName As String = "不整合" Const wsMasterName As String = "商品コード"
Const wsWorkName As String = "作業" Const wsExtractName As String = "時点在庫"
Const wsSaveBalName As String = "疎開現状" Const wsSaveInOutName As String = "疎開入出" Const wsSaveHistName As String = "疎開履歴"
Const scopeBal As String = "A1:A%,C1:C%,D1:D%,F1:F%,M1:M%"
Type 総計
'製品現状
製品現状残(1 To 2) '1=オリジナル、2=更新後
製品引当数(1 To 2)
製品発注数(1 To 2)
' 入出庫表
日付がある入出庫(1 To 2)
引当数(1 To 2)
発注数(1 To 2)
End Type
Sub Main()
Dim 関所 As 総計
Dim wsBal As Worksheet, wsInOut As Worksheet
Dim wsHist As Worksheet, wsMast As Worksheet
Dim wsSaveBal As Worksheet, wsSaveInOut As Worksheet
Dim wsSaveHist As Worksheet, wsWork As Worksheet
Dim oneRecord(1 To 13) As Variant 'Dictionaryに書き込む1行
Dim eachRcd As Variant 'Dictionaryから取り出す1レコード
Dim valOfWork As Variant
Dim nextCode As Variant '後戻り
Dim CarryedOver As Double
Dim finalRowWP As Long
Dim NN As Long, KK As Long
Dim rowsBal As Long
Dim rowsInOut As Long
Dim valOfBal As Variant
Dim valOfInOut As Variant
Dim Dict As Object
Dim cdNidKey As Variant
Dim msg As String
Dim ans, balCkResult
Set wsBal = Sheets(wsBalName)
Set wsInOut = Sheets(wsInOutName)
Set wsHist = Sheets(wsHistoryName)
Set wsMast = Sheets(wsMasterName)
Set wsWork = Sheets(wsWorkName)
Set wsSaveBal = Sheets(wsSaveBalName)
Set wsSaveInOut = Sheets(wsSaveInOutName)
Set wsSaveHist = Sheets(wsSaveHistName)
Application.EnableEvents = False
wsInOut.Range("AA1:AD1000").Clear '入出庫の入力支援エリアをクリア
Application.EnableEvents = True
msg = "処理を選んでください" & vbCr & vbCr & " はい (Y) = 事前チェックだけ"
ans = MsgBox(msg & vbCr & vbCr & " いいえ(N) = 在庫情報の更新処理", vbYesNo)
Rem データの形式的整合性事前チェック
rowsBal = wsBal.Cells(wsBal.Rows.count, "A").End(xlUp).Row
rowsInOut = wsInOut.Cells(wsInOut.Rows.count, "A").End(xlUp).Row
valOfBal = ValArrayBasedOnA1Cell(wsBal, 13)
valOfInOut = ValArrayBasedOnA1Cell(wsInOut, 13)
If dataValidation(rowsBal, valOfBal, rowsInOut, valOfInOut) = False Then
MsgBox "事 前 整 合 性 チ ェ ッ ク で す" & vbCr & vbCr & _
"整合性が取れてないデータがあります。" & vbCr & vbCr & _
"詳細は「" & wsUnmatchName & "」シートをご参照ください。→ 処理中止"
Sheets(wsUnmatchName).Select
Exit Sub
Else
balCkResult = MinusBalDefDeadL(valOfBal, valOfInOut)
If balCkResult <> "" Then
MsgBox "事 前 残 高 チ ェ ッ ク で す" & vbCr & vbCr & _
Replace(balCkResult, "賞味", "") & _
IIf(Right(balCkResult, 2) <> "賞味", "はマイナス残になります", _
"は同じ在庫IDなのに、賞味期限が異なるものがあります")
Exit Sub
ElseIf ans = vbYes Then
MsgBox "整合性は問題なしです"
End If
End If
If ans <> vbNo Then Exit Sub '事前チェックのみで終了
rowsBal = wsBal.Cells(wsBal.Rows.count, "A").End(xlUp).Row
rowsInOut = wsInOut.Cells(wsInOut.Rows.count, "A").End(xlUp).Row
If rowsBal < 2 And rowsInOut < 2 Then Exit Sub
'本番処理へ進む
Application.ScreenUpdating = False
'商品毎・在庫ID毎に集計
Set Dict = CreateObject("Scripting.dictionary") '連想配列
'＜キーの種類＞
'通常は、 → 商品コード+"#$#"+入庫ID
'在庫IDのないデータ→ 商品コード+"#$#"
For NN = 2 To UBound(valOfBal) '(1) 製品現状ありのみを処理
'製品現状のデータ登録
Erase oneRecord '入れ物を初期化
cdNidKey = valOfBal(NN, 1) & "#$#" & valOfBal(NN, 3) ''キーは商品コード+"#$#+在庫ID とする"
oneRecord(1) = valOfBal(NN, 1)
oneRecord(3) = valOfBal(NN, 3)
oneRecord(6) = valOfBal(NN, 6)
oneRecord(11) = valOfBal(NN, 11)
oneRecord(13) = valOfBal(NN, 13)
If Dict.Exists(cdNidKey) Then
eachRcd = Dict(cdNidKey)
eachRcd(6) = eachRcd(6) + oneRecord(6) '製品現状と同じ順。在庫を加算
eachRcd(13) = oneRecord(13) '備考は後のデータを優先
Dict(cdNidKey) = eachRcd
Else
Dict.Add cdNidKey, oneRecord
End If
Next NN
For NN = 2 To UBound(valOfInOut) '(2) 入出庫表を処理、入出庫日の有無で分別登録
If valOfInOut(NN, 4) = "" Then '(2-1) 入出庫日無し。引当数と発注数を集計
Erase oneRecord '入れ物を初期化
cdNidKey = valOfInOut(NN, 1) & "#$#" & valOfInOut(NN, 3)
oneRecord(1) = valOfInOut(NN, 1)
oneRecord(3) = valOfInOut(NN, 3)
oneRecord(7) = valOfInOut(NN, 10) '引当数
oneRecord(8) = valOfInOut(NN, 11) '発注数
If Dict.Exists(cdNidKey) Then
eachRcd = Dict(cdNidKey)
eachRcd(7) = eachRcd(7) + oneRecord(7)
eachRcd(8) = eachRcd(8) + oneRecord(8)
Dict(cdNidKey) = eachRcd
Else
Dict.Add cdNidKey, oneRecord
End If
Else '(2-2)入出庫日あり。入庫数と出庫数を集計
cdNidKey = valOfInOut(NN, 1) & "#$#" & valOfInOut(NN, 3)
oneRecord(1) = valOfInOut(NN, 1)
oneRecord(3) = valOfInOut(NN, 3)
oneRecord(6) = valOfInOut(NN, 7) - valOfInOut(NN, 9) '在庫のネット
oneRecord(11) = valOfInOut(NN, 8) '賞味期限
If Dict.Exists(cdNidKey) Then
eachRcd = Dict(cdNidKey)
eachRcd(6) = eachRcd(6) + oneRecord(6) '製品現状と順序は同一。在庫を加算
Dict(cdNidKey) = eachRcd
Else
Dict.Add cdNidKey, oneRecord
End If
End If
Next NN
'在庫0がなく、引数・発注数もないデータをDictionaryから排除
If Dict.count > 0 Then
For Each eachRcd In Dict.items
If eachRcd(6) = 0 And eachRcd(7) = 0 And _
eachRcd(8) = 0 Then '引当数・発注数・在庫が0
Dict.Remove eachRcd(1) & "#$#" & eachRcd(3)
End If
Next
End If
If Dict.count > 0 Then '作業シートに在庫・引当数・注文数があるデータを書出し,並べ替え
wsBal.Range("A1:M1").Copy wsWork.Range("A1") '製品現状と同じタイトルを書込み
wsWork.Range("A2:M1000").ClearContents
NN = 1
For Each eachRcd In Dict.items
If eachRcd(6) <> 0 Or eachRcd(7) <> 0 Or eachRcd(8) <> 0 Then
NN = NN + 1
wsWork.Cells(NN, 1).Resize(1, 13).Value = eachRcd
End If
Next
With wsWork '並べ替え
.Range("A1:M1").Resize(Dict.count + 1).Sort _
Key1:=.Range("A1"), Order1:=xlAscending, _
Key2:=.Range("C1"), Order2:=xlAscending, Header:=xlYes
End With
Else
Debug.Print "現状在庫は空になる" '通常業務ではあり得ない。
End If
'在庫累積プロセス
valOfWork = ValArrayBasedOnA1Cell(wsWork, 13)
nextCode = ""
CarryedOver = 0
For NN = 2 To UBound(valOfWork)
CarryedOver = CarryedOver + valOfWork(NN, 6)
valOfWork(NN, 5) = CarryedOver
If NN = UBound(valOfWork) Then
nextCode = "" '最終行到達時準備
Else
nextCode = valOfWork(NN + 1, 1)
End If
If nextCode <> valOfWork(NN, 1) Then '各コード最終行
If valOfWork(NN, 6) <> 0 Then '最終行に在庫があった場合
finalRowWP = NN
ElseIf valOfWork(NN - 1, 1) = valOfWork(NN, 1) Then '直前行が同じ商品コードなら
finalRowWP = NN - 1
valOfWork(finalRowWP, 7) = valOfWork(NN, 7) '引当数を直前行に移す
valOfWork(finalRowWP, 8) = valOfWork(NN, 8) '注文数を直前行に移す
For KK = 1 To 13
valOfWork(NN, KK) = Empty '自行は消える
Next KK
Else
finalRowWP = 0
End If
CarryedOver = 0
nextCode = valOfWork(NN, 1)
End If
'残0のEmpty化
For KK = 5 To 8
valOfWork(NN, KK) = IIf(valOfWork(NN, KK) = 0, Empty, valOfWork(NN, KK))
Next KK
'念の為、マイナス残の再チェック
If valOfWork(NN, 6) < 0 Then
MsgBox valOfWork(NN, 1) & " " & valOfWork(NN, 3) & "は、マイナス残です→処理を中止します。"
Exit Sub
End If
Next NN
'作業シートをクリア後、上で補正したデータを再度吐出して並べ替え（Empty化した行は下方に落とす)
With wsWork
.Range("A2:M1000").ClearContents
.Range("A1:M1").Resize(UBound(valOfWork)).Value = valOfWork
.Range("A1:M1").Resize(UBound(valOfWork)).Sort _
Key1:=.Range("A1"), Order1:=xlAscending, _
Key2:=.Range("C1"), Order2:=xlAscending, Header:=xlYes
Dict.RemoveAll '以前の集計データをクリア
Dim rowsToFil As Long
'数式を入力して補完する
rowsToFil = .Cells(.Rows.count, "A").End(xlUp).Row - 1
.Range("B2").Resize(rowsToFil).FormulaLocal = "=IF(A2="""","""",IF(COUNTIF(商品コード!$A:$A,A2)," & _
"VLOOKUP(A2,商品コード!$A:$B,2,FALSE)))"
.Range("D2").Resize(rowsToFil).FormulaLocal = "=IF(A2=A3,"""",IF(B2="""","""",VLOOKUP(A2,商品コード!$A:$C,3,FALSE)))"
.Range("I2").Resize(rowsToFil).FormulaLocal = "=IF(A2<>A3,E2-G2,"""")"
.Range("J2").Resize(rowsToFil).FormulaLocal = "=IF(A2<>A3,I2+H2,"""")"
.Range("L2").Resize(rowsToFil).FormulaLocal = "=IF(A2<>A3,IF(J2<=D2,""要"",""""),"""")"
End With
'
Rem 更新前の状態を別シートに疎開する
' 製品現状 →疎開現状
' 入出庫表 →疎開入出
' 入出庫履歴 →疎開履
wsBal.Range("A1:M1000").Copy wsSaveBal.Range("A1")
Application.EnableEvents = False
wsInOut.Range("A1:M1000").Copy wsSaveInOut.Range("A1")
Application.EnableEvents = True
wsHist.Range("A:M").Copy wsSaveHist.Range("A1") 'データ量不明につき（列全体）
'バックアップファイルの作成(省略可) 'ThisWorkbook.SaveCopyAs Replace(ThisWorkbook.FullName, ".xls", "BACK.xls")
Rem 作業シートの表を製品現状に移す
wsWork.Range("A1:M1000").Copy wsBal.Range("A1")
Rem 転記と行削除
Call shiftNdelete(wsHist, wsInOut, wsBal)
'
Rem 念の為、更新後のデータの形式的整合性を事後チェック
rowsBal = wsBal.Cells(wsBal.Rows.count, "A").End(xlUp).Row
rowsInOut = wsInOut.Cells(wsInOut.Rows.count, "A").End(xlUp).Row
valOfBal = ValArrayBasedOnA1Cell(wsBal, 13)
valOfInOut = ValArrayBasedOnA1Cell(wsInOut, 13)
If dataValidation(rowsBal, valOfBal, rowsInOut, valOfInOut) = False Then
MsgBox "事後チェックですが・・・・" & vbCr & vbCr & _
"整合性が取れてないデータがあります。" & vbCr & vbCr & _
"詳細は「" & wsUnmatchName & "」シートをご参照ください。"
Exit Sub
End If
Rem 総計比較
'疎開先(＝オリジナル)の数値を集計
valOfBal = ValArrayBasedOnA1Cell(wsSaveBal, 13)
valOfInOut = ValArrayBasedOnA1Cell(wsSaveInOut, 13)
Call 総計比較(valOfBal, valOfInOut, 関所, 1) '←1=疎開先(=オリジナル）集計
Erase valOfBal
Erase valOfInOut
'更新後の数値を集計
valOfBal = ValArrayBasedOnA1Cell(wsBal, 13)
valOfInOut = ValArrayBasedOnA1Cell(wsInOut, 13)
Call 総計比較(valOfBal, valOfInOut, 関所, 2) '2=更新後データ集計
Erase valOfBal
Erase valOfInOut
Dim rslt(1 To 4) As Boolean
rslt(1) = 関所.製品現状残(2) = 関所.製品現状残(1) + 関所.日付がある入出庫(1)
rslt(2) = 関所.製品引当数(2) = 関所.引当数(1)
rslt(3) = 関所.製品発注数(2) = 関所.発注数(1)
rslt(4) = True
For NN = 1 To 3
rslt(4) = rslt(4) * rslt(NN)
Next
If rslt(4) Then
MsgBox "更新終了ーーー→総計比較の結果は「全て合致」"
Else
MsgBox "更新の結果、「一部不一致」がありました" & vbCr & "更新前に戻します" & vbCr & vbCr & _
"新在庫総数は、前回在庫数＋前回の日付ある入出庫ネットと→ " & IIf(rslt(1), "合致", "不一致！！") & vbCr & _
"新在庫引当総数は、新入出庫表の引当総数と――――――→ " & IIf(rslt(2), "合致", "不一致！！") & vbCr & _
"新在庫発注総数は、新入出庫表の発注総数と――――――→ " & IIf(rslt(3), "合致", "不一致！！")
End If
If rslt(4) = False Then '総計チェックで不一致があったので疎開先からデータを復旧する
'疎開現状→製品現状
'疎開入出→入出庫表
'疎開履歴→入出庫履歴
wsSaveBal.Range("A1:M1000").Copy wsBal.Range("A1")
Application.EnableEvents = False
wsSaveInOut.Range("A1:M1000").Copy wsInOut.Range("A1")
Application.EnableEvents = True
wsSaveHist.Range("A:M").Copy wsHist.Range("A1")
MsgBox "総計チェックで一部不一致がありましたので、更新前に戻しました", vbOKOnly
End If
End Sub
'在庫がマイナスにならないか及び同入庫IDで賞味期限が違うものがないか(空白はチェック外)事前チェック
'賞味期限の管理簿をついでに作成する→最後の工程で、商品コードシートのC,D,E列に書き出し
Function MinusBalDefDeadL(valOfBal, valOfInOut) 'CodeとIDの結合Keyを返す
Dim NN As Long, Balance As Variant
'
Dim CdIdBalDic As Object
Dim CDIDkey As Variant
Dim CDIDDeadkey As Variant '賞味期限の異動チェック用
Set CdIdBalDic = CreateObject("Scripting.dictionary") ' 連想配列で商品コードを管理
'
Rem 製品現状からのデータ集め
For NN = 2 To UBound(valOfBal)
'残高チェック工程
If valOfBal(NN, 3) <> "" Then '入庫IDが存在する
CDIDkey = valOfBal(NN, 1) & "の在庫ID=" & valOfBal(NN, 3) '商品コードと入庫IDをキーにする
If CdIdBalDic.Exists(CDIDkey) Then
CdIdBalDic(CDIDkey) = CdIdBalDic(CDIDkey) + valOfBal(NN, 6)
Else '新規
CdIdBalDic.Add CDIDkey, valOfBal(NN, 6)
End If
End If
'賞味期限チェック工程
If valOfBal(NN, 3) <> "" And valOfBal(NN, 11) <> "" Then '入庫IDと賞味期限が存在する
CDIDDeadkey = valOfBal(NN, 1) & "の在庫ID=" & valOfBal(NN, 3) & _
" 賞味" '商品コードと入庫IDと”賞味”をキーにする
If CdIdBalDic.Exists(CDIDDeadkey) Then
If CdIdBalDic(CDIDDeadkey) <> valOfBal(NN, 11) Then
MinusBalDefDeadL = CDIDDeadkey
Exit Function '同在庫IDで異賞味期限あり、直ちに中止
End If
Else '新規
CdIdBalDic.Add CDIDDeadkey, valOfBal(NN, 11)
End If
End If
Next NN
'
Rem 入出庫表からのデータ集め
For NN = 2 To UBound(valOfInOut)
'残高チェック工程
If valOfInOut(NN, 3) <> "" Then '入庫IDが存在する
CDIDkey = valOfInOut(NN, 1) & "の在庫ID=" & valOfInOut(NN, 3)
If CdIdBalDic.Exists(CDIDkey) Then
CdIdBalDic(CDIDkey) = CdIdBalDic(CDIDkey) _
+ valOfInOut(NN, 7) - valOfInOut(NN, 9)
Else '新規
CdIdBalDic.Add CDIDkey, valOfInOut(NN, 7) - valOfInOut(NN, 9)
End If
End If
'賞味期限チェック工程
If valOfInOut(NN, 3) <> "" And valOfInOut(NN, 8) Then '入庫IDと賞味期限が存在する
CDIDDeadkey = valOfInOut(NN, 1) & "の在庫ID=" & valOfInOut(NN, 3) & " 賞味"
If CdIdBalDic.Exists(CDIDDeadkey) Then
If CdIdBalDic(CDIDDeadkey) <> valOfInOut(NN, 8) Then
MinusBalDefDeadL = CDIDDeadkey
Exit Function '同在庫IDで異賞味期限あり、直ちに中止
End If
Else '新規
CdIdBalDic.Add CDIDDeadkey, valOfInOut(NN, 8)
End If
End If
Next NN
Rem マイナス残をチェック
NN = 0
If CdIdBalDic.count > 0 Then
For Each Balance In CdIdBalDic.items
NN = NN + 1
If Balance < 0 Then
MinusBalDefDeadL = Application.Index(CdIdBalDic.keys, NN)
End If
Next
End If
'Rem ついでに賞味期限の管理簿を作成 (不要化? 後で復活もあるかも）
' Dim 賞味key
' Sheets(wsMasterName).Range("E1:F1000").ClearContents
' Sheets(wsMasterName).Range("E1:F1").Value = Array("商品CD&在庫ID合成キー", "賞味期限")
' NN = 0
' For Each 賞味key In CdIdBalDic.keys
' If Right(賞味key, 2) = "賞味" Then
' NN = NN + 1
' Sheets(wsMasterName).Range("E1:F1").Offset(NN).Value = _
' Array(Replace(賞味key, " 賞味", ""), CdIdBalDic.Item(賞味key))
' End If
' Next
CdIdBalDic.RemoveAll
End Function
Rem データの整合性をチェック
Private Function dataValidation(rowsBal, valOfBal, rowsInOut, valOfInOut) As Boolean
Dim NN As Long, KK As Long, rowToWrite As Long, OKNG As Boolean
Dim wsUnMatch As Worksheet
Dim msgNoB(1 To 4) As Long
Dim errMsgB(1 To 4) As String
Dim msgNoIO(1 To 11) As Long
Dim errMsgIO(1 To 11) As String
On Error Resume Next
Set wsUnMatch = Sheets(wsUnmatchName)
If Err.Number <> 0 Then
Set wsUnMatch = ThisWorkbook.Sheets.Add(after:=Worksheets(Worksheets.count))
wsUnMatch.Name = wsUnmatchName
End If
On Error GoTo 0
wsUnMatch.Range("A:B").ClearContents
wsUnMatch.Range("A1:B1").Value = [{"シート名と位置-商品コード","不整合理由"}]
OKNG = True
rowToWrite = 1
If rowsBal > 1 Then
'メッセージセット
errMsgB(1) = "在庫がマイナスです"
errMsgB(2) = "入庫IDがあるのに、賞味期限が無い"
errMsgB(3) = "入庫IDがないのに、賞味期限がある"
errMsgB(4) = "入庫IDがないのに、在庫がある"
For NN = 2 To UBound(valOfBal)
Erase msgNoB
If valOfBal(NN, 6) < 0 Then msgNoB(1) = 1
If valOfBal(NN, 3) <> "" And valOfBal(NN, 11) = "" Then msgNoB(2) = 1
If valOfBal(NN, 3) = "" And valOfBal(NN, 11) <> "" Then msgNoB(3) = 1
If valOfBal(NN, 3) = "" And valOfBal(NN, 6) <> 0 Then msgNoB(4) = 1
If Application.Sum(msgNoB) > 0 Then
OKNG = False
For KK = 1 To UBound(msgNoB)
If msgNoB(KK) = 1 Then
rowToWrite = rowToWrite + 1
wsUnMatch.Cells(rowToWrite, 1) = "製品現状 " & NN & "行目の" & valOfBal(NN, 1)
wsUnMatch.Cells(rowToWrite, 2) = errMsgB(KK)
End If
Next KK
End If
Next NN
End If
If rowsInOut > 1 Then
'メッセージセット
errMsgIO(1) = "入庫IDが無いのに、賞味期限がある"
errMsgIO(2) = "入出庫日があるのに、入庫数も出庫数もない又は入庫IDが無い"
errMsgIO(3) = "入出庫日と入庫数があるのに、在庫ID・賞味期限がない"
errMsgIO(4) = "入出庫日がないのに、入庫数または出庫数がある"
errMsgIO(5) = "入庫数と発注数が、両方入力されている"
errMsgIO(6) = "出庫数と引当数が、両方入力されている"
errMsgIO(7) = "仕入先がないのに、入庫数または発注数がある"
errMsgIO(8) = "納入先がないのに、出庫数または引当数がある"
errMsgIO(9) = "入庫数または出庫数があるのに、発注数または引当数がある"
errMsgIO(10) = "入庫IDと仕入先があるのに、賞味期限がない"
errMsgIO(11) = "入庫ファクタがあるのに、出庫ファクタもある"
For NN = 2 To UBound(valOfInOut)
Erase msgNoIO
If valOfInOut(NN, 3) = "" And valOfInOut(NN, 8) <> "" Then msgNoIO(1) = 1
If valOfInOut(NN, 4) <> "" And ((valOfInOut(NN, 7) = "" And valOfInOut(NN, 9) = "") Or _
valOfInOut(NN, 3) = "") Then msgNoIO(2) = 1
If valOfInOut(NN, 4) <> "" And valOfInOut(NN, 7) <> "" And _
(valOfInOut(NN, 3) = "" Or valOfInOut(NN, 8) = "") Then msgNoIO(3) = 1
If valOfInOut(NN, 4) = "" And _
(valOfInOut(NN, 7) <> "" Or valOfInOut(NN, 9) <> "") Then msgNoIO(4) = 1
If valOfInOut(NN, 7) <> "" And valOfInOut(NN, 11) <> "" Then msgNoIO(5) = 1
If valOfInOut(NN, 9) <> "" And valOfInOut(NN, 10) <> "" Then msgNoIO(6) = 1
If valOfInOut(NN, 5) = "" And _
(valOfInOut(NN, 7) <> "" Or valOfInOut(NN, 11) <> "") Then msgNoIO(7) = 1
If valOfInOut(NN, 6) = "" And _
(valOfInOut(NN, 9) <> "" Or valOfInOut(NN, 10) <> "") Then msgNoIO(8) = 1
If (valOfInOut(NN, 7) <> "" And valOfInOut(NN, 11) <> "") Or _
(valOfInOut(NN, 9) <> "" And valOfInOut(NN, 10) <> "") Then msgNoIO(9) = 1
If valOfInOut(NN, 3) <> "" And _
valOfInOut(NN, 5) <> "" And valOfInOut(NN, 8) = "" Then msgNoIO(10) = 1
If (valOfInOut(NN, 5) <> "" Or valOfInOut(NN, 7) <> "" Or _
valOfInOut(NN, 11) <> "") And (valOfInOut(NN, 6) <> "" Or _
valOfInOut(NN, 9) <> "" Or valOfInOut(NN, 10) <> "") Then msgNoIO(11) = 1
If Application.Sum(msgNoIO) > 0 Then '整合性に何か問題がある場合
OKNG = False
For KK = 1 To UBound(msgNoIO)
If msgNoIO(KK) = 1 Then
rowToWrite = rowToWrite + 1
wsUnMatch.Cells(rowToWrite, 1) = "入出庫表 " & NN & "行目の" & valOfInOut(NN, 1)
wsUnMatch.Cells(rowToWrite, 2) = errMsgIO(KK)
End If
Next KK
End If
Next NN
End If
dataValidation = OKNG
End Function
Private Sub shiftNdelete(wsHist, wsInOut, wsBal) '履歴処理
Dim rowsInOut As Long, rowsBal As Long
Dim NN As Long
'転記処理
Application.EnableEvents = False
Application.ScreenUpdating = False
rowsInOut = wsInOut.Cells(wsInOut.Rows.count, "A").End(xlUp).Row
For NN = 2 To rowsInOut
If Left(wsInOut.Cells(NN, "M"), 3) <> "転記済" Or _
wsInOut.Cells(NN, "D") <> "" Then '未転記レコード、又は入出庫日欄に入力あるもの
wsInOut.Cells(NN, "M").Value = Format(Now, "転記済 yyyy-mm-dd hh:mm")
wsInOut.Rows(NN).Copy wsHist.Cells(Rows.count, "A").End(xlUp).Offset(1)
End If
Next NN
'実績に移ったデータ行を下方から行削除する
rowsInOut = wsInOut.Cells(wsInOut.Rows.count, "A").End(xlUp).Row
For NN = rowsInOut To 2 Step -1
If wsInOut.Cells(NN, "D") <> "" Then '入出庫日に記入あり
wsInOut.Rows(NN).Delete
End If
Next NN
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Private Sub 総計比較(valOfBal, valOfInOut, ByRef 関所 As 総計, idx)
Dim pos As Long
For pos = 2 To UBound(valOfBal)
'製品現状
関所.製品現状残(idx) = 関所.製品現状残(idx) + valOfBal(pos, 6)
If idx = 2 Then '更新後
関所.製品引当数(idx) = 関所.製品引当数(idx) + valOfBal(pos, 7)
関所.製品発注数(idx) = 関所.製品発注数(idx) + valOfBal(pos, 8)
End If
Next pos
For pos = 2 To UBound(valOfInOut)
' 入出庫表 = 入出庫表
If valOfInOut(pos, 4) <> "" Then
関所.日付がある入出庫(idx) = 関所.日付がある入出庫(idx) + valOfInOut(pos, 7) - valOfInOut(pos, 9)
End If
関所.引当数(idx) = 関所.引当数(idx) + valOfInOut(pos, 10)
関所.発注数(idx) = 関所.発注数(idx) + valOfInOut(pos, 11)
Next pos
End Sub
'所用範囲の値データ配列をA1セルを基準に取得する
Private Function ValArrayBasedOnA1Cell(Wsh As Worksheet, numOfCols As Long)
ValArrayBasedOnA1Cell = Wsh.Range("A1", Wsh.Cells(Wsh.Rows.count, "A").End(xlUp).Offset(0, numOfCols - 1)).Value
End Function
''ある日付における在庫残高を求める
Sub balanceOnAday()
Dim wsHist As Worksheet
Dim wsExtracrt As Worksheet
Dim LatestDate
Dim Condition
Set wsHist = Sheets(wsHistoryName)
Set wsExtracrt = Sheets(wsExtractName)
Application.ScreenUpdating = False
LatestDate = Application.Max(Sheets(wsHistoryName).Columns("D"))
Condition = InputBox("入出庫履歴の抽出基準日を" & vbCr & _
"入力してください", , Format(LatestDate, "yyyy/m/d"))
If IsDate(Condition) = False Then
MsgBox "日付が確認できませんでした → 処理中止"
Exit Sub
End If
With wsExtracrt
.Range("A:Z").ClearContents
'履歴(I列まで）を時点在庫シートにコピー
wsHist.Range("A:I").Copy .Range("L1")
'条件をセット
.Range("J1:K1").Value = "入出庫日"
.Range("J2").Value = ">0"
.Range("K2").Value = "<=" & Condition
.Columns("L:T").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("J1:K2"), CopyToRange:=.Range("A1:I1"), Unique:=False
.Columns("J:T").ClearContents
'並べ替え
.Columns("A:I").Sort _
Key1:=.Range("A1"), Order1:=xlAscending, _
Key2:=.Range("C1"), Order2:=xlAscending, Header:=xlYes
' ’小計'
.Columns("A:I").Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(7, 9), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
.Columns("A:I").ClearOutline
'在庫ID毎のネット残をJ列に算出
With .Range("J1").Resize(.Cells(.Rows.count, "C").End(xlUp).Row)
.FormulaLocal = "=IF(RIGHT(C1,2)=""集計"",G1-I1,"""")"
.Value = .Value
End With
.Range("J1").Value = "在庫ID別ネット残" 'タイトル
.Columns("A:J").EntireColumn.AutoFit
.Activate
.Range("A1").Select
End With
Application.ScreenUpdating = False
End Sub
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
(2)入出庫表のモジュールに
’Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.count > 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub 'タイトル行は対象外
Select Case Target.Column
Case 3 '在庫ID欄がダブルクリックされた
Cancel = True
'入力支援エリアをクリアする
Application.EnableEvents = False
Range("AA1:AD1000").Clear
Application.EnableEvents = True
'入出庫日があり、出庫ファクタが何も入力されていなければ(それは(入庫確定データである）
With Target
If .Offset(, 1) <> "" And .Offset(, 3) = "" And _
.Offset(, 6) = "" And .Offset(, 7) = "" Then
Application.EnableEvents = False
.FormulaR1C1Local = _
"=IF(OR(RC[-2]="""",RC[1]=""""),"""",TEXT(RC[1],""yyyymmdd"")&RC[-2])"
.Value = .Value
Application.EnableEvents = True
End If
End With
Case 27 'AA列 がダブルクリックされた
Cancel = True
If Cells(Target.Offset(0, 3).Value, "F").Value <> "" Then '納入先が記入されている
Application.EnableEvents = False
Cells(Target.Offset(0, 3).Value, "C").Value = Target.Value '在庫IDを補充
' Cells(Target.Offset(0, 3).Value, "H").Value = Target.Offset(0, 1).Value '賞味期限を補充(不要？)
Range("AA1:AD500").Clear
Application.EnableEvents = True
End If
End Select
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsMast As Worksheet
Dim cel As Range
Dim CodeList As Range, CodeNameList As Range
If Target.count > 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub 'タイトル行は対象外
Select Case Target.Column
Case 1 '商品コード欄
Application.EnableEvents = False
With Target.Offset(, 1)
.FormulaR1C1Local = _
"=IF(RC[-1]="""","""",IF(COUNTIF(商品コード!C1,RC[-1]),VLOOKUP(RC[-1],商品コード!C1:C2,2,FALSE),""""))"
.Value = .Value
End With
Application.EnableEvents = True
Case 7 '入庫数欄
If Target.Value = "" Then
Exit Sub
Else
Application.EnableEvents = False
Target.EntireRow.Cells(1, "K").ClearContents '発注数をクリア
Application.EnableEvents = True
End If
Case 9 '出庫欄
If Target.Value = "" Then
Exit Sub
Else
Application.EnableEvents = False
Target.EntireRow.Cells(1, "J").ClearContents '引当数をクリア
Application.EnableEvents = True
End If
End Select
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim wsBal As Worksheet
Dim NN As Long
Dim valOfBal As Variant
Dim valOfInOut As Variant
Dim codedic As Object
Dim IDkey As Variant
Dim Mcode As Variant
Dim Cname As Variant
Dim dataToPush(1 To 1, 1 To 4) As Variant
Dim dataToStore As Variant
Dim rowToWrite As Long
Dim oldestDate As Variant
Dim oldestRow As Long
If Target.count > 1 Then Exit Sub
If Target.Column = 27 And Target.Row > 1 Then 'AA列のワンセルが右クリックされた
Cancel = True
Application.EnableEvents = False
Range("AA1:AA1000").Clear 'AB列は書式もクリア(色を消す)
Range("AB1:AD1000").ClearContents '入出庫の入力支援エリアのデータクリア
Application.EnableEvents = True
Mcode = Target.EntireRow.Cells(1, "A").Value
Cname = Target.EntireRow.Cells(1, "F").Value
If Mcode <> "" And Cname <> "" Then '商品コードがあって、納入先の記入がある場合
Set codedic = CreateObject("Scripting.dictionary") ' 連想配列で商品コードを管理
Set wsBal = Sheets("製品現状")
valOfBal = _
wsBal.Range("A1", wsBal.Cells(wsBal.Rows.count, "A").End(xlUp).Offset(0, 10)).Value
valOfInOut = _
Range("A1", Cells(Rows.count, "A").End(xlUp).Offset(0, 12)).Value
Rem 製品現状からのデータ集め
For NN = 2 To UBound(valOfBal)
If valOfBal(NN, 1) = Mcode Then '商品コードが合致
IDkey = valOfBal(NN, 3) '入庫IDをキーにする
If codedic.Exists(IDkey) Then
dataToStore = codedic(IDkey)
If dataToStore(1, 2) <> valOfBal(NN, 11) And _
dataToStore(1, 2) <> "" And valOfBal(NN, 11) <> "" Then
MsgBox IDkey & "は入庫IDが同じで賞味期限が異なるデータがあります→処理中止"
Exit Sub
Else
dataToStore(1, 3) = dataToStore(1, 3) + valOfBal(NN, 6)
codedic(IDkey) = dataToStore
End If
Else '新規
dataToPush(1, 1) = valOfBal(NN, 3) '在庫ID
dataToPush(1, 2) = valOfBal(NN, 11) '賞味期限
dataToPush(1, 3) = valOfBal(NN, 6) '在庫残
dataToPush(1, 4) = Target.Row 'トリガーとなったtargetの行をメモ
codedic.Add IDkey, dataToPush
End If
End If
Next NN
Rem 入出庫表からのデータ集め
For NN = 2 To UBound(valOfInOut)
If valOfInOut(NN, 1) = Mcode And valOfInOut(NN, 3) <> "" And _
valOfInOut(NN, 4) <> "" Then '商品コードが合致、入庫IDと入出庫日が決定している
IDkey = valOfInOut(NN, 3) '入庫IDをキーにする
If codedic.Exists(IDkey) Then '入庫IDが既存
dataToStore = codedic(IDkey)
If dataToStore(1, 2) <> valOfInOut(NN, 8) Then
If valOfInOut(NN, 8) <> "" Then
MsgBox IDkey & "は入庫IDが同じで賞味期限が異なるデータがあります→処理中止"
Exit Sub
End If
End If
dataToStore(1, 3) = dataToStore(1, 3) + valOfInOut(NN, 7) _
- valOfInOut(NN, 9)
codedic(IDkey) = dataToStore
Else '入庫IDが新規
dataToPush(1, 1) = valOfInOut(NN, 3) '在庫ID
dataToPush(1, 2) = valOfInOut(NN, 8) '賞味期限
dataToPush(1, 3) = valOfInOut(NN, 7) _
- valOfInOut(NN, 9) '入庫数-出庫数
dataToPush(1, 4) = Target.Row 'トリガーとなったtargetの行をメモ
codedic.Add IDkey, dataToPush
End If
End If
Next NN
rowToWrite = Target.Row
'タイトル記入
Application.EnableEvents = False
Cells(rowToWrite - 1, 27).Resize(1, 4).Value = [{"在庫ID","賞味期限","在庫","行番号"}]
Application.EnableEvents = True
If codedic.count > 0 Then
For Each dataToStore In codedic.items
' If dataToStore(1, 3) > 0 Then ’在庫がゼロチェック不要
If oldestDate = 0 Then
oldestDate = dataToStore(1, 2)
oldestRow = rowToWrite
ElseIf dataToStore(1, 2) < oldestDate Then
oldestDate = dataToStore(1, 2)
oldestRow = rowToWrite
End If
Application.EnableEvents = False
Cells(rowToWrite, 27).Resize(1, 4).Value = dataToStore
Application.EnableEvents = True
rowToWrite = rowToWrite + 1
' End If
Next
End If
codedic.RemoveAll
If oldestRow = 0 Then
Application.EnableEvents = False
Cells(Target.Row, "AA").Value = "無し"
Application.EnableEvents = True
Else
Cells(oldestRow, "AA").Interior.ColorIndex = 6 '最も古い賞味期限のセルを黄塗り
End If
End If
End If
End Sub
（半平太） 2015/04/19(日) 14:39
お忙しい中、ありがとうございます。
＞これについては、プログラム側で対応しますので、全て日付型で統一してください。
ありがとうございます。
全て日付型で統一します。
＞これは条件付き書式でも対応できそうですけど？
すみません。
書式で対応しました
＞入庫日が入っていて、出庫ファクタの欄が何も入力されていないと云う条件で、
＞その行の入庫ID欄(C列）のセルをダブルクリックすると、在庫IDが自働的に入るようにしました。
ありがとうございます。
すごく便利になりました。
＞以下の数式が自動入力されます。適否をご確認ください。
数式を確認しました。
間違いありません。
＞ (5) L2セル =IF(A2<>A3,IF(J2<=D2,"要",""),"")
＞ ↑
＞ ここは、I2ではなく、J2ですよね？
J2で大丈夫です。
＞何に何色使うのか整理してください。
商品コードの色分けで1色(薄い水色)
賞味期限の色分けで2色(ゴールドと赤色)
3ヶ月前：ゴールド 2ヶ月前：赤色
を考えています。
まだ増えそうな気がしますが、現状ではこれで大丈夫かと考えています。
＞別のアプローチとしては、要チェックの商品を別途リストアップする方法もあります。
＞それか、その作業の時だけ要チェックの商品に色づけして、終わったら、その色付けは解除する、なんて手もあります。
商品が増えてきた際に、とても便利になりそうです。
商品検索機能をイメージしています。
＞マクロ「balanceOnAday」
こんな素晴らしい機能も追加していただき、ありがとうございます。
とても便利です！重宝すること間違いなしです。
テストをしていて、気になった事が2点あります。
1.「入出庫表」シートで商品コードを入力しても、商品名が表示されないことがあった。
入力した商品コードをダブルクリックしてから、別セルをクリックすると表示されましたが・・・
今は一応、正常に動いているのですが、原因を知りたいと思いまして。
2.「時点在庫」シートで、在庫ID別ネット残が表示されない。
マクロ「balanceOnAday」を実行後に、在庫ID別ネット残のみ計算されていませんでした。
原因がわからず、対応ができません。
テストは無事に終了しました。
上記2点以外は、特に気になった点はありませんでした。
＞'Rem ついでに賞味期限の管理簿を作成 (不要化? 後で復活もあるかも）
マクロを見ていて気になる文字を発見しました。(日本語しか理解できていません・・・)
この機能があれば、賞味期限を色分けする必要がないかもしれないと思っています。
賞味期限の管理簿に残り何日で賞味期限が切れるかあれば最高です。
以上、よろしくお願いします。
（うめ） 2015/04/20(月) 11:55
＞＞'Rem ついでに賞味期限の管理簿を作成 (不要化? 後で復活もあるかも） ＞マクロを見ていて気になる文字を発見しました。(日本語しか理解できていません・・・) ＞この機能があれば、賞味期限を色分けする必要がないかもしれないと思っています。 ＞ 賞味期限の管理簿に残り何日で賞味期限が切れるかあれば最高です。
そちらでイメージしている「賞味期限管理簿」のレイアウトを示してください。
＞1.「入出庫表」シートで商品コードを入力しても、商品名が表示されないことがあった。 ＞ 入力した商品コードをダブルクリックしてから、別セルをクリックすると表示されましたが・・・ ＞今は一応、正常に動いているのですが、原因を知りたいと思いまして。
コードを見た限り、そう言うことは起こらないと思うので、原因は分かりません。 (商品コードはワンセルずつ、入力してください。複数いっぺんだと反応しません）
＞2.「時点在庫」シートで、在庫ID別ネット残が表示されない。 ＞マクロ「balanceOnAday」を実行後に、在庫ID別ネット残のみ計算されていませんでした。
(1)「エクセルのバージョンの違い([集計]と[合計]の言葉の違い)」と (2)「バージョンに関わらず、エクセルの集計機能にバクらしきもの(総計が極端に下の行に出力される」 が影響しています。
バグ対応版として以下に書き換えましたので、balanceOnAdayに上書きしてください。
''ある日付における在庫残高を求める
Sub balanceOnAday()
Dim wsHist As Worksheet
Dim wsExtracrt As Worksheet
Dim LatestDate
Dim Condition
Set wsHist = Sheets(wsHistoryName)
Set wsExtracrt = Sheets(wsExtractName)
Application.ScreenUpdating = False
LatestDate = Application.Max(Sheets(wsHistoryName).Columns("D"))
Condition = InputBox("入出庫履歴の抽出基準日を" & vbCr & _
"入力してください", , Format(LatestDate, "yyyy/m/d"))
If IsDate(Condition) = False Then
MsgBox "日付が確認できませんでした → 処理中止"
Exit Sub
End If
With wsExtracrt
.Range("A:Z").Clear
'履歴(I列まで）を時点在庫シートにコピー
wsHist.Range("A:I").Copy .Range("L1")
'条件をセット
.Range("J1:K1").Value = "入出庫日"
.Range("J2").Value = ">0"
.Range("K2").Value = "<=" & Condition
.Columns("L:T").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("J1:K2"), CopyToRange:=.Range("A1:I1"), Unique:=False
.Columns("J:T").ClearContents
'並べ替え
.Columns("A:I").Sort _
Key1:=.Range("A1"), Order1:=xlAscending, _
Key2:=.Range("C1"), Order2:=xlAscending, Header:=xlYes
' ’小計'
.Columns("A:I").Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(7, 9), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
.Columns("A:I").ClearOutline
'バグ対応
If Application.CountBlank(.Range("C1", .Cells(.Rows.count, "C").End(xlUp))) > 0 Then
.Range("C1", .Cells(Rows.count, "C").End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End If
'在庫ID毎のネット残をJ列に算出
With .Range("J1").Resize(.Cells(.Rows.count, "C").End(xlUp).Row)
.FormulaLocal = "=IF(RIGHT(C1)=""計"",G1-I1,"""")"
.Value = .Value
End With
.Range("J1").Value = "在庫ID別ネット残" 'タイトル
.Columns("A:J").EntireColumn.AutoFit
.Activate
.Range("A1").Select
End With
Application.ScreenUpdating = True
End Sub
（半平太） 2015/04/20(月) 19:46
＞そちらでイメージしている「賞味期限管理簿」のレイアウトを示してください。
行 _____A_____ ___B___ _____C_____ ___D___ ___E___ ____F____ ___G___ 1 商品コード 商品名 入庫ID 総在庫 在庫 賞味期限 残り日数 2 a-1 商名A1 20150102a-1 16 7 2015/2/1 30 3 a-1 商名A1 20150203a-1 16 9 2015/3/1 60 4 b-1 商名B1 20150204b-1 5 5 2015/4/1 90
イメージしているレイアウトです。
賞味期限管理簿でやりたいこと
・残り日数が短い入庫ID順に並べたいと考えています。
・総在庫や在庫などは「製品現状」シートから、引っ張ります。
・残り日数が、60日前(赤色)、90日前(ゴールド)でセルの色を変更します。
・残り日数は、現在の日にちから賞味期限までの日数です。
3色までしか使えないのがボディブローのように効きますね・・・
＞(商品コードはワンセルずつ、入力してください。複数いっぺんだと反応しません）
これが原因だと思います。
ご迷惑をおかけしました。
＞バグ対応版として以下に書き換えましたので、balanceOnAdayに上書きしてください。
素早い対応ありがとうございます！
バージョン違いの影響もあるとのことなので、
明日、会社のパソコンで確認します。
取り急ぎ、賞味期限管理簿のイメージレイアウトを連絡いたします。
以上、よろしくお願いします。
（うめ） 2015/04/20(月) 22:45
＞賞味期限管理簿でやりたいこと ＞・残り日数が短い入庫ID順に並べたいと考えています。 ＞・総在庫や在庫などは「製品現状」シートから、引っ張ります。 ＞・残り日数が、60日前(赤色)、90日前(ゴールド)でセルの色を変更します。 ＞・残り日数は、現在の日にちから賞味期限までの日数です
残り日数が短い順に並べるとしたら、商品コードは入り乱れます。 それ自体は管理簿の目的からして当然ですけど、 そんな状態の中、「総在庫」の欄は存在意義がないと思うのですけど・・不要なのでは？
（半平太） 2015/04/20(月) 23:57
＞ そんな状態の中、「総在庫」の欄は存在意義がないと思うのですけど・・不要なのでは？
確かに総在庫は必要ないです。
欲をかいてしまいました。
（うめ） 2015/04/21(火) 10:14
新しいシート「賞期管理」を挿入して、マクロ「bestCondToEat」を標準モジュールに追記してください。 ゴールドなんて色があるのかどうか私は知らないので、そこは適当に修正してください。
<賞期管理> 行 _____A_____ ___B___ _____C_____ __D__ ____E____ ______F______ 1 商品コード 商品名 入庫ID 在庫 賞味期限 残り日数 2 a-1 商名A1 20150102a-1 1 2015/4/1 期限オーバー 3 b-1 商名B1 20150204b-1 5 2015/5/1 10日 4 a-1 商名A1 20150203a-1 26 2015/7/10 80日
Sub bestCondToEat()
Dim wsBal As Worksheet
Dim wsBestCond As Worksheet
Dim rowsNumTocolor60 As Long
Dim rowsNumTocolor90 As Long
Set wsBal = Sheets(wsBalName)
Set wsBestCond = Sheets("賞期管理")
'製品現状からデータをコピー
Application.ScreenUpdating = False
With wsBestCond
.Range("A:Z").Clear
'製品現状(K列まで）を賞期管理シートにコピー
wsBal.Range("A:K").Copy .Range("A1")
.Range("A1:K1000").Value = .Range("A1:K1000").Value
.Range("D:E,G:J").Delete Shift:=xlToLeft
'賞味期限のないデータ行を削除
If Application.CountBlank(.Range("E1", .Cells(.Rows.count, "A").End(xlUp).Offset(, 4))) > 0 Then
.Range("E1", .Cells(.Rows.count, "A").End(xlUp).Offset(, 4)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End If
'賞味期限で並べ替え
.Range("A:E").Sort _
Key1:=.Range("E1"), Order1:=xlAscending, Header:=xlYes
With .Range("F2", .Cells(.Rows.count, "A").End(xlUp).Offset(, 5))
.NumberFormatLocal = "0日;期限オーバー"
.FormulaLocal = "=E2-TODAY()"
.Value = .Value
End With
.Range("F1").Value = "残り日数"
.Columns("A:F").EntireColumn.AutoFit
rowsNumTocolor60 = Application.CountIf(.Columns("F:F"), "<61")
If rowsNumTocolor60 > 0 Then
With .Range("F2").Resize(rowsNumTocolor60)
.Interior.Color = vbRed
.Font.Color = vbYellow
End With
End If
rowsNumTocolor90 = Application.CountIf(.Columns("F:F"), "<91") - rowsNumTocolor60
If rowsNumTocolor90 > 0 Then
With .Range("F" & rowsNumTocolor60 + 2).Resize(rowsNumTocolor90)
.Interior.Color = vbBlue
.Font.Color = vbYellow
End With
End If
End With
Application.ScreenUpdating = True
End Sub
（半平太） 2015/04/21(火) 12:49
＞バグ対応版として以下に書き換えましたので、balanceOnAdayに上書きしてください。
バグ対応版確認しました。
しっかりと動きました。
＞新しいシート「賞期管理」を挿入して、マクロ「bestCondToEat」を標準モジュールに追記してください。
＞ゴールドなんて色があるのかどうか私は知らないので、そこは適当に修正してください。
ありがとうございます！
理想通りです。感動しています。
色の変更の件、かしこまりました。
初めに考えていた在庫管理システムよりも、高性能で使い勝手も最高です！
長い時間、私の我儘に付き合っていただきありがとうございました。
とても感謝しています。
ありがとうございました！
（うめ） 2015/04/21(火) 18:50
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.