[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『納期毎の在庫を数式でだしたいです』(ともこ)
はじめまして。
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 >
ちょっと質問
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: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.