こんにちは！ てらっちです♪
Excelで検索してもなかなか結論が出なかったことで、なんとか解決したものを記録しておきます。
『並べ替えしたら、計算式の参照先が変わってしまった』問題です。
ちょっとわかりにくいかもしれませんが、なるべくわかりやすく書きますね。
仕事で納品管理のマクロを作成してまして、同じ品を作成するたびに当然在庫が変わるわけです。
並べ替えた時に参照していたセルが変わってしまう
以下が例です。
AAAという品を1月1日に100個注文が入った表の在庫管理表です。
先行して作っている在庫の残が、100個（H2）、
在庫の残-注文（納入支持数100個)=０（J2）、
そしてまた100個作成した（L2）という表です。
そしてまた2月2日に100個の注文が入り、同じ作業をしています。
今回は、分かりやすくするために、2月は101個、3月は102個、4月は103個作成したことにしました。
◯をしてあるH3には、”=L2”と計算式が入っています。
M 列は、H列に入っている計算式を書き出してみました。
このL2の100個を、次のH3でそのまま参照したいわけです。
このままなら、問題ありませんが、作業の都合上、この表を日付順でソートしなければならないんです。
日付（D 列）で並べ替えてみたら、こうなります。
お分かりになりますでしょうか？
AAAの2月2日分（H5）は、本来100個であったのに、300個になっています。
M列を見ていただければわかるのですが、はじめは品名順に並んでいるためすぐ上の行を参照しています。
が、これを日付順に並べ変えた時、計算式は、すぐ上の行を参照しつづけるため、違う品名の在庫数を参照してしまうわけです。
2月のAAAは、本来在庫が100個でなければならないのに、すぐ上の行の在庫、300個を参照してしまっています。
『並べ替えた後も、同じ品名の、１つ前の日付の在庫数を参照してほしい』
つまり、
『並べ替える前も後も、同じセルを参照してほしい』
これを解決する方法を見つけました。
《STEP1》COUNTIF関数を使う
まず、COUNTIF関数を使います。
I列にこのように打ちまして、それを下までコピーしていきます。
初めの範囲の指定を＄で囲むことで、範囲の始まりだけが固定されるわけです。
すると、一つずつセルを広げて参照していくことで、この前にある同じ部品番号のみカウントしていくんですね。だから、後に現れた機種に関してはカウントされないのです。
つまり、この全データの中で、参照しているのがAAAの部品のうち何番目のデータなのかがわかるわけです。
▼M列にI列のカウント関数のデータを表記してみました。
《STEP2》
B列に個別の名前を割り当てます。"=C2&I2"と入力して、下までコピーします。
部品番号に、カウント関数から得られたカウントを足したことで、唯一の名前ができました。
《STEP3》
STEP2で得られた個別の番号をLOOKUP関数で見つけてもらいます。H2に書き込んだのがこちら。
▼M 列にI 列の計算式を入力してみました。参考にしてください。
これで、準備作業は終わりです。早速並べ替えてみましょう。
並べ替えてみよう
▼B列まで範囲を指定して、日付（D列）で並べ替えてみます。
▼2月のAAA（H5）の先行残が、ちゃんと1月のAAAを参照しています！
まとめ
かなり無理矢理なやり方になりますが、これで問題は解決しました。
このやり方を教えてくれたとても参考になったブログがあり、本当に感謝しています。もう一度探しているのですが、見つけられません。すみません。また見つけたらリンクを貼らせていただきます。
どうしても並べ替えた後も同じセルを参照してほしい場合、この関数を使った裏技をぜひお試しください。
てらっちでした♪