新たに追加、並び替えでも参照の値を追従させた

質問

WINDOWS XP EXCELL ２００３です。
いつもお世話になります。

参考に参照図を添付しています。
添付図は「４月」「５月」のシートです。

例えば「５月」のシート場合、
　C２には　=４月!C2　を参照していますが新たに
「５月」シートに　　２００１　の番号を追加し並び換えすると行がずれます。

　番号２０１０の適正在庫は２００であるべきなのに行のずれで３００になります。

各月別の適正在庫表を別シートに作成してVLOOKUPとか考えましたがもっと簡単な何かいい方法はありませんでしょうか。
御指導を仰ぎたいのは、
新たに　番号　商品名　が追加されても　適正在庫　は行の変化に関係なく追従する方法が、もしも可能な方法があるならご教授願えませんでしょうか。
よろしく御願いします。

回答No.3

keithin · ベストアンサー

今回のご質問は

＞例えば「５月」のシート場合、
＞C２には　=４月!C2　を参照していますが新たに
＞「５月」シートに　　２００１　の番号を追加し並び換えすると行がずれます。

＞　番号２０１０の適正在庫は２００であるべきなのに行のずれで３００になります。

という部分についてです。

番号2010の５月の適正在庫が
・４月と同じ値を引っ張ってきた200のままでいいのか
・５月は新たに何かの数字に変える必要があるのか
それは勿論適切に運用すればよいことですが，いずれにしても並べかえて「300にずれる」という事は起こりません。
それでもう一度ご相談でお困りの症状をよく再確認してくださいと回答しました。

ご質問の補足で掲示されたVLOOKUPの式には幾つか問題があるように見えますが，そこは「並べ替えと行ズレ」とは別の問題ですので，新しいご相談を投稿して正しいVLOOKUPの式について皆さんのアドバイスを求めて，条件付き書式が正しく反応するようにしてください。

お礼

下記のように補足説明で説明させていただきましたが私の説明能力に問題がありご迷惑かけています。
申し訳ありません

「例えば
４月のシートの　C２　には　=３月!C2　という数式が入っています。
図で言うと
２０１０　AA　２００　
時勢の変化で「適正在庫」を当然として対応していかなければなりません。
この時　C２　のセルに　１００　とかダイレクトに数値を入れたとき当然の如く元の数式(=３月!C2)が無くなります。」

色々と試行錯誤しました結果下記の方法で私なりに解決しましたのでご報告いたします。

BS列の適正在庫を月度によっては変更したいときは
　　　例えば、１列新たに増やして
　　　　BS201 (1000)Iに　=BT201(500)
にして私なりの解決策を見いだしました。

大変ご迷惑を掛けて申し訳ありません。
ご協力には重ね重ね感謝しています。
今後ともこれに懲りず御指導の程よろしく御願いいたします。

回答No.2

keithin

いいえ。あなたのご相談は，どこかに思い違いや確認間違いがあります。

手順：次の通りに，実際に手を動かして確認して下さい
まっさらなブックに４月シートと５月シートを用意
４月シートのA1:C6に，ご相談で書かれたとおりのデータを準備
４月シートのA2:C6をコピー
５月シートのA2に，形式を選んで貼り付けのリンク貼り付けをクリック
以上で４月データが５月に再掲されました。

実施例１）
５月シートの
A7　に　2001
B7　に　YY
C7　に　200
を記入し，A列昇順で並べ替えます

実施例２）
５月シートの２行目を行番号右クリックして空行を１行挿入します
A2　に　2001
B2　に　YY
C7　に　200
を記入します。

いずれの手段を行っても，C3に移動した2010/AAはご質問で希望の通り，キチンと４月の300を表示し続けます。
もう一度現物のエクセルに立ち返り，上述手順に照らして再度作成し直してみて下さい。

補足

説明の仕方が悪くて済みません。
例えば
４月のシートの　C２　には　=３月!C2　という数式が入っています。
図で言うと
２０１０　AA　２００　
時勢の変化で「適正在庫」を当然として対応していかなければなりません。
この時　C２　のセルに　１００　とかダイレクトに数値を入れたとき当然の如く元の数式(=３月!C2)が無くなります。

C列に何もしなければ貴方がおっしゃるとおりで今までもそれで続けてて来ました。

質問の中にも書かせてもらいましたが
=IF(A7="","",VLOOKUP(A2,在庫目標表!$A$7:$M$556,2,FALSE)) & ""　※2は１月を指します。

この表は
　番号　　　　1月　　２月
２０１０　２００　３００
　２０１５　３００　３００
　２０４５　１００　１００
ところがこれを採用すると質問時の参照図には表現していませんが「適正在庫」の列があって　ここには
条件書式が　=E7<BS7 で　書式は　赤　で塗りつぶしています。
BS列にVLOOKUPの数式が入ると　条件付書式を設定している列のすべてが赤くなり機能しなくなります。
※ この件の一部には昨日貴方に御指導いただきました。

なるべく簡易な方法でいい方法がないかと思い質問させていただきました。
よろしく御願いします。

回答No.1

KURUMITO

最初に作成するのは面倒でしょうが、適正在庫表のシートを別に作って対応することが最も便利でしょう。
適正在庫表のシートでは例えばA1セルに番号、B1セルに商品名、C1セルには4、D1セルには5、…と3月までの数値をN1セルまで入力します。
番号や商品名と月ごとの適正在庫数を下の行に入力します。

その上で4月のシートでしたら例えばA1セルには4月と入力します。
A2セルには番号、B2セルには商品名、C2セルには適正在庫数とでも項目名を入力します。
A3セル以下には必要な商品の番号を入力します。
B3セルには次の式を入力してC3セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF($A3="","",VLOOKUP($A3,適正在庫表!$A:$N,IF(COLUMN(A1)=1,2,MATCH(LEFT($A$1,LEN($A$1)-1)*1,適正在庫表!$A$1:$N$1,0)),FALSE))

これで商品番号を入力すれば商品名と適正在庫数が自動的に表示されますね。
なお、行を挿入して途中の行に新たな商品番号を入力した場合には商品名や適正在庫数が表示されません。その場合には例えば1行下のB列およびC列を選択して上の行に式をドラッグするようにすれば式がコピーされて表示されるようになります。
なお、このシートをコピーして他の月のシートに貼り付けることで、しかも、A1セルに月を入力することで対応すればよいでしょう。

お礼

私の説明能力に問題がありご迷惑かけています。
申し訳ありません

色々と試行錯誤しました結果下記の方法で私なりに解決しましたのでご報告いたします。

BS列の適正在庫を月度によっては変更したいときは
　　　例えば、１列新たに増やして
　　　　BS201 (1000)Iに　=BT201(500)
にして私なりの解決策を見いだしました。

大変ご迷惑を掛けて申し訳ありません。
今後ともこれに懲りず御指導の程よろしく御願いいたします。