[[20190315090237]] 『納期毎の在庫を数式でだしたいです』(ともこ) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『納期毎の在庫を数式でだしたいです』(ともこ)

はじめまして。
EXCELの難しさにつまづき、この賢者がおおい質問版にたどり着きました。
いろいろ参考にさせていただいており、この度私個人の質問をご相談させていただくにあたり大変恐縮ではございますが、お力添えを頂ければ幸いです。

現在下記のような在庫管理をしています。

A1 品番
B1 倉庫にある在庫
C1 3/中
D1 3/下
E1 4/上
・・・・4/中 4/下 5/上 5/中 5/下 6/上 6/中 6/下 7/上 7/中
とO1まで続き、最後のP1に現在庫というタイトル行があります。
日付が3月21日になれば、C1は3/下に変えて、4/上から7/下までO1を変更しています。

やりたいことは、Pのセルに現在出せる在庫を書いていましたが、複数入荷がある場合、欠品のままお客さんに案内すると、次はいつの入荷になるのか?という問い合わせ対応が多く、一度にP列に案内できたらと考えました。
 
品番 在庫  3/中  3/下  4/上   4/中  4/下・・・・現在庫
AFR 300 −200  0     0    250  200・・・100
BHY 162 −162  1     400  −74  0  ・・・欠品
YEG 115 −160  33    192  −46  0  ・・・欠品

といういままでの表記を、下記のP列になるように変えたいです
品番 在庫  3/中  3/下  4/上  4/中  4/下 ・・・・現在庫(P列)
AFR 300 −200  0     0   250  200 ・・・(即納100 4/中250 4/末200)
◆数字が合計0以上にならない納期は飛ばしたいです・出荷できる納期飲み表記◆
BHY 162 −162  1     400  −74  0  ・・・(即納0 3/下1 4/上326 )
YEG 115 −160  33    192  −46  0  ・・・(即納0 4/上134)
YEGの場合は4/中で46個予約があるといういみなので、前の在庫から引く事をしないといけないので
複雑で悩んでおりました。

この管理方法は当分変えられないので、これをもとに、一番最後のP列に、いつの納期からいくつ出荷できるという表記を一度に出せれたらと考えています。

どうぞ、ご教授の程よろしくお願い致します。

< 使用 Excel:Excel2016、使用 OS:Windows10 >


こんにちは。
関数でやるとおそろしく長い数式になりますがIFとSUMとSUMIFだけでできそうです。
下の方の4末までの式で数式を考えてみると、
最初の即納値はB列とC列の差がマイナスでないならB列とC列の和を返す式、
それ以降の在庫値は、D列について言えば
『D列が0より大きい (入荷がある)
B〜D列の和が0より大きい (在庫がある)
B〜G列の和が0より大きい (出荷数より入庫数と在庫の和が大きい)
のときに
D1の値("3/下")、
半角スペース(" ")、
【「B〜D列の和」と  (ここまでの在庫(A))
「もし[B〜G列の和が0より大きい]&[E〜G列の中のマイナスの値の和よりB〜D列の和が大きい]なら、  (この先在庫切れにならずこの先の出庫よりもこれまでの入庫&在庫の方が大きいなら)
[E〜G列の中のマイナスの値の和] (この先の出庫(B))
」を入れる式】』 
を3/下〜4/下の分4つ入れる
でできそうです。
「G列」のところを大きな表で最終列(上の式で言うと「O列」にしてしまうとかなり先の出庫まで拾ってしまうのでそのあたりは調整してもよいかもしれません。
(げん) 2019/03/15(金) 14:19

 ちょっと質問

 5行目(SAG)の場合だったら、どうなりますか?

  行  __A__  _______B_______  __C__  __D__  __E__  __F__  __G__
   1  品番   倉庫にある在庫   3/中   3/下   4/上   4/中   4/下
   :    :
   5  SAG          300      -100   -150    -40     0       0

(半平太) 2019/03/15(金) 16:09


げんさん
たいへん光栄なアドバイスありがとうございます。
現在いろいろな方法でやってはおりますが途方に暮れています><
しかしげんさんのアドバイスをもとに組み立て手みたいと思います。
(ともこ) 2019/03/15(金) 16:45

半平太さん
ご質問ありがとうございます。
SAGの場合はPセルは
即納10個
のみの表示が望ましいです。
すべての在庫(次回入荷も含め)が0以下であれば、Pセルは
完売
という表記が望ましく考えております。

どうぞよろしくお願い致します。
(ともこ) 2019/03/15(金) 16:49


  >『納期毎の在庫を数式でだしたいです』

 「数式で」ということなので、作業用のシートを一枚挿入してください。

  ※作業シート名は「舞台裏」とします。
  ※目的シート名は「在庫管理」と仮決めします。

 1.舞台裏シート
   2行目に数式を入力(※1行目は空白です)

 (1) A2セル =SUM(在庫管理!$B2:C2)
   M2セルまでコピー

 (2) O2セル =MIN(B2:$N2)
   Z2セルまでコピー

 (3) AA2セル =Z2
 (4) AC2セル =MAX(0,MIN(A2,O2))
   上記2つはコピーなし

 (5) AD2セル =MAX(0,MIN(B2,P2))-SUM($AC2:AC2)
   AQ2セルまでコピー

 (6) AP2セル =CONCATENATE(AQ2,AR2,AS2,AT2,AU2,AV2,AW2,AX2,AY2)
 (7) AQ2セル =TEXT(AC2,"即納 ?,??0")
   上記2つは単独

 (8) AR2セル =IFERROR("  "&INDEX(在庫管理!$C$1:$O$1,AGGREGATE(15,6,COLUMN($A$1:$M$1)/($AC2:$AO2>0),COLUMN(A1)+SIGN($AC2))) &TEXT(INDEX($AC2:$AO2,AGGREGATE(15,6,COLUMN($A$1:$M$1)/($AC2:$AO2>0),COLUMN(A1)+SIGN($AC2))),"  #,0"),"")
   AY2セルまでコピー  

 2.在庫管理シート

 (1) P2セル =IF(SUM(B2:O2)<=0,"完売",舞台裏!AP2)
    下にコピー

 <在庫管理シート 結果図>
  行 __A__ _______B_______ __C__ __D__ __E__ __F__ __G__ __H__ .. __O__ ________________P________________
   1 品番  倉庫にある在庫  3/中  3/下  4/上  4/中  4/下  5/上  .. 7/中  現在庫                           
   2 AFR              300  -200     0     0   250   200        ..       即納   100  4/中  250  4/下  200 
   3 BHY              162  -162     1   400   -74     0        ..       即納     0  3/下  1  4/上  326   
   4 YEG              115  -160    33   192   -46     0        ..       即納     0  4/上  134            
   5 SAM              300  -100  -150   -40    25              ..       即納    10  4/中  25             
   6 YOS              300  -100  -150   -40   -10              ..       完売                              

(半平太) 2019/03/15(金) 19:52


半平太さん

たいへんな金のお知恵を拝借した気分です。
こんな高度な方法、1年経っても思いつきませんです。
数式も頑張って理解しようとしましたがものすごく複雑でとても1日で解決するとは
あっぱれです。
本当にありがとうございました。

皆様、感謝いたします。
(ともこ) 2019/03/15(金) 21:39


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.