[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『受注管理表＆出庫表』（るいこ）
はじめまして。会社でのある商品の管理にexcelをつかっています。小々ややこしい質問でございます。 このＥＸＣＥＬで管理している商品は人気商品であり、現在庫よりも多い注文が多々あって、 次回入荷の商品の分も確保しておいてほしい依頼がかかると、在庫表示が以前の管理台帳ではマイナス表記になり、 即納分の少数注文の売り逃しがありました。
そこで売り逃しを少しでも防ぎたく、新しく管理台帳を作成することにしました。
こちらにアップ致しました。 http://dl.dropbox.com/u/26934517/%E5%95%86%E5%93%81%E7%AE%A1%E7%90%86%E5%8F%B0%E5%B8%B3.xls L列より注文を入力するようになっています。基本的に取引先名、直送先、受注日、希望納期、出荷済み欄となっています。 行６の希望納期は倉庫在庫＆入荷予定のシートに反映するようドロップダウンリストで記入できるようにしました。 基本的にはほとんどのお客様は即納希望です。 たとえば管理NO１のハクサイの注文は希望納期は即納なので問題ないのですが、管理no２，３のキャベツ、しいたけの注文で 1-A-AIの当初在庫１００ケがマイナス30となりました。 そこで、管理NO４のなすの10この注文は、本来なら、あと１０ケ即納分でだせるのに、注文順でいくと、マイナスになっているので、 9月末の案内しかできなく、売り逃しとなっていました。 ここを自動で色分けされ、なおかつフリー在庫の欄（Ｈ１０：Ｋ３０）に自動的に反映していくようにしたいです。 あと、とくに困る注文で、今出せるものがあればだせるだけ出してほしいという注文です。 たとえば、先ほどの管理NO３のしいたけが、今１０ケだせるなら出してほしい。残り３０ケは次回入荷したら即納してくれ。 という注文の場合、メッセージボックスかなにかで、「即納できる分はする。次回入荷に回す」というようなウィンドウがでてきて、 「即納できる分はする」というメッセージを選択した場合、新たに右の列が挿入され、管理NOは３−注残 のような表記になって、 のこり３０ケが黄色くなり、9月末よりひかれている風にできますでしょうか？？ あとは、注文記入が多くなってくると探すのが大変なので、入力画面のユーザーフォームに管理NOを記入すると情報がでて、 変更や入荷予定のセルの色も反映されている風にできますか？ あとは、行７の出荷明細に１を入れると出荷済みとして列が全て赤くなる。そして表示される。（コマンドの出荷済みの表示を押すと表示され、非常時を押すと非表示になるようにしたいです） 複数注文時の部分出荷済みについては、入力画面のユーザーフォームにチェックを入れるとセルが赤くなるようにしたいです。 全出荷済みに対してのみ、列全て赤く塗りつぶされ、非表示になるようにします。
ほんとにこんな長い文章で、ご迷惑おかけします。 どうぞ、お知恵をお貸しくださいませ。宜しくお願い致します。
WINDOWS ＸＰ
EXCEL ２００７
（るいこ）
>ほんとにこんな長い文章で、ご迷惑おかけします。 血の通った質問文で、読んでて楽しいです。
ファイルのダウンロードはリスクがあるので、やらない回答者は少なくないです。 できるだけ多くの回答者に見て貰う為には、ここにレイアウトを貼った方がいいです。 (今回、代わりに私が下の方に貼っておきます)
それにしてもご要望がてんこ盛りですね。 全て一人で対応しなければならなくなるかも、と思ったら手を出す人は少なくなりそうです。 全体像はそれとして、少しずつ進めた方がいいと思います。 私としても全部はお付き合いできません。
まずは、「フリー在庫をイメージ通り表示させる」部分をやってみたいと思います。
現在の疑問点
１．一回で複数の品番があるのに、希望納期がワンセルしか用意されていません。 希望納期は１つでもいいかも知れませんが、確定納期(まだ設定されていない項目ですけど）は 在庫の関係で複数に分かれる可能性があります。
それらをどう管理するのか(管理できるのか)見えません。
２．ファイルの拡張子が「xls」でしたが、エクセルのバージョンは、本当にXL2007ですか？
<受注管理一覧> 行 ___A___ ___B___ _____C_____ _____D_____ ___E___ __F__ __G__ ___H___ __I__ __J__ ____K____ ____L____ ____M____ ____N____ ___O___ ___P___ 1 2 ┃入力画面 ┃ボタン 納期色指定 管理NO 1 2 3 4 5 3 色付きセル 即納 取引先名 ハクサイ キャベツ しいたけ なす えのき 4 ┃出荷済み表示 ┃ボタン 色付きセル 9/末 直送先名 イチゴ レモン みかん 5 色付きセル 11/中 受注日 8月1日 8月2日 8月4日 8月5日 8月6日 6 ┃出荷済み非表示┃ボタン 色付きセル 未定 希望納期 即納 即納 即納 9/末 11/中 7 出荷明細 1 1 1 2 2 8 ←ーーーーー受注合計ーーーー→ ←ーーーーﾌﾘｰ在庫ーーーー→ 9 品番 サイズ 色 即納 9/末 11/中 未定 即納 9/末 11/中 未定 10 1-A-AI 大 アイボリー 140 -40 200 500 0 10 80 40 10 11 1-A-BR 大 ブラウン 0 100 200 500 0 200 450 12 1-A-OR 大 オレンジ 0 100 200 500 0
<倉庫在庫＆入荷予定> 行 _____A_____ _____B_____ ______C______ __D__ __E__ 1 在庫調査日 8月2日 入荷予定/数量 2 品番 倉庫在庫数 9/末 11/中 未定 3 1-A-AI 100 200 500 0 4 1-A-BR 100 200 500 0 5 1-A-OR 100 200 500 0 6 1-A-P 100 200 500 0 7 1-A-RD 100 200 500 0 8 1-A-S 100 200 500 0
(半平太) 2011/08/04 13:42
＞血の通った質問文で、読んでて楽しいです。
すごくうれしかったです。きっともう、見過ごされるのだろうなぁとおもっていました。
全ての要望は希望しますが、必要なことができれば構いません。
１、希望納期とかいてありますが、お客様は基本即納です。たしかに確定納期という言葉がただしいですね。いままでは、セルを手動でいろわけして、納期を分けていました。出荷すれば赤くぬる。
納期色に合わせてセルをいろわけしてました。ようは全て手作業で管理していました。
２、はい。２００７です。97−2003の保存形式で保存しました。
（るいこ）
ごめんなさい、アイデアだけです。 在庫─┬─受注時以前にある在庫（即納在庫） └─受注時以後入庫する在庫（入荷予定在庫） 受注─┬─即納在庫を引き当てる受注（出荷済受注） └─未入荷在庫を引き当てる受注（未出荷受注） のような内容の区別ができるデータがあると処理の流れも考えやすいかな・・・。 私なら、次のようなリストを作る仕組みをまず考えます。 [在庫データ] A B C D E [1] 在庫No. 品番 入荷日 数量 引当済数量 [2] 1 1-A-AI 7/31 100 [3] 2 1-A-AI 9/末 200 [4] 3 1-A-AI 11/中 500 [5] 4 1-A-BR 7/31 100 [6] 5 1-A-BR 9/末 200 [7] 6 1-A-BR 11/中 500 [受注データ] A B C D E F G H [1] 受注No. 得意先名 直送先名 受注日 希望納期 品番 数量 引当 [2] 1 ハクサイ イチゴ 8/1 即納 1-A-AI 10 [3] 2 ハクサイ イチゴ 8/1 即納 1-A-P 100 [4] 3 キャベツ レモン 8/2 即納 1-A-AI 80 [5] 4 しいたけ 8/4 即納 1-A-AI 40 [6] 5 なす 8/5 即納 1-A-AI 10 [7] 6 なす 8/5 即納 1-A-BR 200 [8] 7 えのき みかん 8/6 即納 1-A-BR 450 で、受注データを在庫データに引き当てていったリストを作ります。 どの受注に対して、どの在庫がいつ出荷されたのかのリストになります。 [出荷データ] A B C D E F G H [1] 出荷No. 受注No. 得意先名 直送先名 受注日 出荷日 在庫No. 入荷日 品番 数量 [2] 1 1 ハクサイ イチゴ 8/1 8/1 1 7/31 1-A-AI 10 [3] 2 2 ハクサイ イチゴ 8/1 8/1 13 7/31 1-A-P 100 [4] 3 3 キャベツ レモン 8/2 8/2 1 7/31 1-A-AI 80 [5] 4 4 しいたけ 8/4 2 9/末 1-A-AI 40 [6] 5 5 なす 8/5 8/5 1 7/31 1-A-AI 10 [7] 6 6 なす 8/5 7/31 4 7/31 1-A-BR 100 [8] 7 6 なす 8/5 5 9/末 1-A-BR 100 そして、例えば、在庫に対してどれだけ引き当てたかを記録したり、 [在庫データ] A B C D E [1] 在庫No. 品番 入荷日 数量 引当済数量 ←残量でも良いけど [2] 1 1-A-AI 7/31 100 100 [3] 2 1-A-AI 9/末 200 40 [4] 3 1-A-AI 11/中 500 [5] 4 1-A-BR 7/末 100 100 [6] 5 1-A-BR 9/末 200 100 [7] 6 1-A-BR 11/中 500 引当処理が済んだ（出荷したか、出荷予定となった）受注にチェックを入れてみたり [受注データ] A B C D E F G H [1] 受注No. 得意先名 直送先名 受注日 希望納期 品番 数量 引当 [2] 1 ハクサイ イチゴ 8/1 即納 1-A-AI 10 済 [3] 2 ハクサイ イチゴ 8/1 即納 1-A-P 100 済 [4] 3 キャベツ レモン 8/2 即納 1-A-AI 80 済 [5] 4 しいたけ 8/4 即納 1-A-AI 40 済 [6] 5 なす 8/5 即納 1-A-AI 10 [7] 6 なす 8/5 即納 1-A-BR 200 [8] 7 えのき みかん 8/6 即納 1-A-BR 450 で、こう言うデータをもとに受注一覧データ等、出したい資料を期間指定して転記なり 集計なりさせていくみたいな。 (みやほりん)(-_∂)b
>セルを手動でいろわけして、納期を分けていました。 >出荷すれば赤くぬる。 >納期色に合わせてセルをいろわけしてました。 >ようは全て手作業で管理していました。
それと同じやり方を (人間ではない)エクセルにやらせる訳にも行きません。
一つの注文の中で、或る品番は即納でき、別の品番は即納出来ない場合は、 『同じ品番に中で「即納できる分はする」というケース』と同じ様に取り扱う方針にします。
つまり、即納できない品番が一つでもあったら「分割納付」として、 新しい列を挿入して対応するものとします。
備考欄を新設して、「分割納付」と云う言葉を手入力するか、入力規則のリストから その言葉を選択するものとします。
列挿入マクロは上述「分割納付」と云う言葉が入ったセルが ダブルクリックされると作動するものにします。
同じ注文が2つ(以上)の列に分かれるので、これは枝番で管理することにします。
ただ、管理番号に枝番を直接付加すると、管理番号が文字列型になってしまいます。 他の管理番号とデータ型が違うものになるのは、将来何かと問題が生ずるかも 知れませんので、枝番は別の行を新設して対応したいと思います。
下図レイアウトにおいて、 D12セル =SUMIF($L$7:$T$7,D$11,$L12:$T12) と入力し、D12:G32へコピーする。
<受注管理一覧> O8セルをダブルクリックして、分割納付による新列挿入し、数量の調整をした後、O7セルに「即納」と入れた図
行 _____D_____ ___E___ __F__ __G__ ___H___ __I__ __J__ ____K____ ____L____ ____M____ ____N____ ____O____ ___P___ ___Q___ ___R___ _____U_____ 1 2 納期色指定 管理NO 1 2 3 4 4 5 6 総受注合計 3 ・・・ 即納 新設→枝番 1 4 ・・・ 9/末 取引先名 ハクサイ キャベツ しいたけ なす なす えのき ROTETO 5 ・・・ 11/中 直送先名 イチゴ レモン みかん 6 ・・・ 未定 受注日 8月1日 8月2日 8月4日 8月5日 8月5日 8月6日 8月15日 7 希望納期 即納 即納 9/末 即納 9/末 8 新設→備考 分割納付 9 出荷明細 1 1 1 10 受注合計 ﾌﾘｰ在庫 11 即納 9/末 11/中 未定 即納 9/末 11/中 未定 12 100 40 0 0 0 160 500 0 10 80 40 10 15 13 100 100 0 0 0 100 500 0 100 100 450 14 0 0 0 0 100 200 500 0 15 100 0 0 0 0 200 500 0 100
希望納期決定前(7行目が空白の間)に、在庫が充分あるかどうか一目で分かる様に、以下の条件付き書式をL12:T32の範囲に設定する。
条件式 =ISBLANK(L$7)*(L12>$H12) 色 目立つ色に設定
マクロで列新設が済んだら、オリジナルの列の数量を「即納できるレベル」に引き下げる。 その調整数値が正しいと条件付き書式の色が消えるので、そのあと、希望納期セルに「即納」と入力する。
受注管理一覧シートのモジュールに貼り付ける「列挿入マクロ」は以下の通りです。
↓
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
With Target
If .Count = 1 Then
If .Value = "分割納付" Then
If MsgBox("右隣りに列を挿入します", vbYesNoCancel) = vbYes Then
Cancel = True
Application.EnableEvents = False
Columns(Columns.Count).Clear
.Offset(, 1).EntireColumn.Insert
.EntireColumn.Copy
.Offset(, 1).EntireColumn.PasteSpecial xlPasteAll
.Offset(-5, 1).Value = .Offset(-5, 0).Value + 1
.Offset(, 1).Select
Range(.Offset(, 1), Cells(Rows.Count, Target.Column + 1)) _
.Value = Empty
Application.CutCopyMode = False
Application.EnableEvents = True
End If
End If
End If
End With
End Sub
(半平太) 2011/08/04 23:35
> マクロで列新設が済んだら、オリジナルの列の数量を「即納できるレベル」に引き下げる。 > その調整数値が正しいと条件付き書式の色が消えるので、そのあと、希望納期セルに「即納」と入力する。
考えたら・・ この部分は、ある程度機械的な処理ができるので、マクロで列新設と同時にやってしまい、 その結果を見て、人間が必要なら修正すると云う事にした方がよかったです。
後刻、そのように修正する予定です。
(半平太) 2011/08/05 08:08
アイデアありがとうございます。
たしかにデータベース的な考えで大変参考になりました。
ただ扱う方は、ecxelをよく知らない方が多いので、ただ単に得意先＆直送先＆数量だけを
入力するような１枚のシート上が理想でした。
現場のレベルアップの為にも、みんなで勉強しようと思います。
＞半平太様
いま試しに仰せのとおりに試してみました。
自動的に納期別に計算されていてとても管理がしやすいと思いました。
納期色も自動的に配置できたり、同じ品番行で、注文番号の若いお客が
追加などを足した場合など、その後ろも自動的にいろわけなどできますでしょうか？
（るいこ）
>後刻、そのように修正する予定です。 １．後掲したコードに貼り替えてください。
>納期色も自動的に配置できたり、同じ品番行で、 >注文番号の若いお客が追加などを足した場合など、 >その後ろも自動的にいろわけなどできますでしょうか？ ２．ちょっと良くわからないです。 下図を使って、具体的にどんな状況になった場合なのかご説明ください。
理論的に可能なら、条件付き書式でできると思います。 XL2007じゃないと、いろんな色は使えません。 バージョンはXL2007ですね? (再確認して置きます）
<受注管理一覧> 行 ____L____ ____M____ ____N____ ____O____ ___P___ ___Q___ ___R___ ___S___ 1 2 1 2 3 4 4 5 6 7 3 1 4 ハクサイ キャベツ しいたけ なす なす えのき ROTETO IMO 5 イチゴ レモン みかん イチゴ 6 8月1日 8月2日 8月4日 8月5日 8月5日 8月6日 8月15日 8月16日 7 即納 即納 9/末 即納 9/末 9/末 8 分割納付 9 1 1 1 10 11 12 10 80 40 10 10 15 13 100 200 450 14 15 100
<数量を自動的に調整して、「即納」と自動入力するバージョン> ↓ Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim lastRow As Long Dim tblOrder As Variant Dim NN As Long
With Target
If .Count = 1 Then
If .Value = "分割納付" Then
If MsgBox("右隣りに列を挿入します", vbYesNoCancel) = vbYes Then
Cancel = True
Application.ScreenUpdating = False
Columns(Columns.Count).Clear
.Offset(, 1).EntireColumn.Insert
.EntireColumn.Copy
.Offset(, 1).EntireColumn.PasteSpecial xlPasteAll
.Offset(-5, 1).Value = .Offset(-5, 0).Value + 1
Application.CutCopyMode = False
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
tblOrder = Cells(12, .Column).Resize(lastRow - 11, 2).Value
For NN = 1 To lastRow - 11
If tblOrder(NN, 1) <> Empty And tblOrder(NN, 1) > Cells(NN + 11, "H").Value Then
tblOrder(NN, 1) = Cells(NN + 11, "H").Value
tblOrder(NN, 2) = tblOrder(NN, 2) - tblOrder(NN, 1)
Else
tblOrder(NN, 2) = Empty
End If
Next
Cells(12, .Column).Resize(lastRow - 11, 2).Value _
= tblOrder
.Offset(-1, 1).Select
.Offset(-1).Resize(2, 2).Value = [{"即納","";"分割納付",""}]
Application.ScreenUpdating = True
End If
End If
End If
End With
End Sub
(半平太) 2011/08/05 10:36
いま頭がこんがらがってきて、よく考えたら追加注文はうしろに回せばよいですね・・・・。
やっぱりあと100個おとといの注文についかしといて〜という電話があったりするものですから、
おとといの注文の欄に１００個足してかいても自動で計算されるのかなぁ〜〜とおもったのですが、
それが即納の場合はもう出荷済みですもんね。
たとえ9月納期としても新たな注文列を設ければすむはなしですし。
すみません。
納期が指定された場合の、納期色に色付け方法をご教授いただいてもよろしいでしょうか？？
（るいこ）
>やっぱりあと100個おとといの注文についかしといて〜という電話があったりするものですから >おとといの注文の欄に１００個足してかいても自動で計算されるのかなぁ〜〜とおもったのですが、 １．在庫が充分あって、発送が同梱できる状態なら全く問題ないです。
・・ですが、基本的にデータをさかのぼって修正するのはリスキーではあります。 希望納期(確定納期)は、それ以前のデータの状況を勘案して決定されていますので、 決定後にその前提が崩れると不測の事態が起きる懸念が残ります。
>納期が指定された場合の、納期色に色付け方法 ２.XL2007は順位の低い方から設定してください。(つまり未定から)
(1) 設定範囲 L12:U32（合計列の手前まで）
優先順位 条件 条件数式
1 出荷明細が１ =L$9=1
2 「即納」確定前の在庫不十分時の警告 =ISBLANK(L$7)*(L12>$H12)
3 即納 =(L$7=$H$11)*ISNUMBER(L12)
4 9/末 =(L$7=$I$11)*ISNUMBER(L12)
5 11/中 =(L$7=$J$11)*ISNUMBER(L12)
6 未定 =(L$7=$K$11)*ISNUMBER(L12)
(2)追加で以下の処置をするといいかもです。 L11セルに数式をセット =IF(AND(L$7="",SUMPRODUCT(N($H12<L12:L32))),"即納×","") U11セルまでフィルコピー
以上で、一つでも即納ができない品番がある場合は11行目に「即納×」と表示されます。
ついでに条件付き書式で以下を設定 設定範囲 L11:U11 条件式 =L11="即納×" 警告色は上記(1)の「優先順位2」と同じすると、平仄があっていて、見やすくなると思います
<受注管理一覧> 行 ___H___ __I__ ___J___ ____K____ ____L____ ____M____ ____N____ ____O____ ___P___ ___Q___ ____R____ 1 2 管理NO 1 2 3 4 4 5 6 3 新設→ 枝番 1 4 取引先名 ハクサイ キャベツ しいたけ なす なす えのき ROTETO 5 直送先名 イチゴ レモン みかん 6 受注日 8月1日 8月2日 8月4日 8月5日 8月5日 8月6日 8月15日 7 希望納期 即納 即納 9/末 即納 9/末 8 新設→ 備考 分割納付 9 出荷明細 1 1 1 2 2 10 ﾌﾘｰ在庫 11 即納 9/末 11/中 未定 即納× ←警告色 12 0 150 500 0 10 80 40 10 10 13 0 0 500 0 100 200 14 100 200 500 0 10 15 0 200 500 0 100 5 ←警告色
(半平太) 2011/08/05 13:37
<追記> ついでに、これも条件付き書式を設定するといいかも知れません。
設定範囲 L7:U7 条件式 =IF(L7<>"",COUNTIF(INDEX($H$12:$K$32,0,MATCH(L7,$H$11:$K$11,0)),"<0"))
以上で、希望納期が確定した後でフリー在庫がマイナスになっていないかチェックできます。 (7行目の希望納期に色が付きます)
>L11セルに数式をセット =IF(AND(L$7="",SUMPRODUCT(N($H12<L12:L32))),"即納×","") > U11セルまでフィルコピー
１．上の数式はこっちの方がよかったです。 ↓ =IF(L4="","",IF(AND(L$7="",COUNT(L12:L32)),IF(SUMPRODUCT(N($H12:$H32<L12:L32)),"即納×","即納○"),""))
これなら全品番が即納できるときは、「即納○」と11行目に出てくるので、 フリー在庫欄を見ないでも、安心して客に「即納できます！」って回答できます。
２．出荷明細が１の列を表示/非表示するには、 実行ボタンを以下のコードに紐付けしてください。
Private Sub CommandButton1_Click() '全部表示
Cells.EntireColumn.Hidden = False
End Sub
Private Sub CommandButton3_Click() ’非表示
Dim rightEdge As Long
rightEdge = Cells(2, Columns.Count).End(xlToLeft).Column
Call hideColumn(12, rightEdge) '非表示をコール
End Sub
'別の目的でも使うかも知れないので別プロシージャとしました Private Sub hideColumn(ByVal colToStart As Long, ByVal rightEdge As Long)
Dim NN As Long
Application.ScreenUpdating = False
For NN = colToStart To rightEdge
If Cells(9, NN).Value = 1 Then
Columns(NN).Hidden = True
End If
Next NN
Application.ScreenUpdating = True
End Sub
３．取引画面の呼び出しについては、
管理項目の意味の確認作業や別種の工夫(発送管理簿の作成) 等が必要になりますので、
私はこれで降ります。
(半平太) 2011/08/05 17:04
（るいこ）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.