ままここmamacoco

ありのままで、イマここを生きている「ままここ」。アラフィフにしてフリーランスに転向した女は果たしてフリーとして生きていけるのか?

EXCEL 並べ替えても同じセルを参照しない問題は、関数で解決できる。

ペライチのHP制作はじめました♪

安くて簡単に作れるサイト、ペライチ。自分で作りたい人、やっぱり作ってもらいたい人、文章書くのが嫌な人、それぞれに対応できます♪県外の方はZOOMで対応しています。詳しくはこちらまで。

ペライチ自分で作るホームページ相談。静岡の個人事業主、イベント、セミナー、お教室に最適。

こんにちは! てらっちです♪

 

Excelで検索してもなかなか結論が出なかったことで、なんとか解決したものを記録しておきます。

 

 

 

『並べ替えしたら、計算式の参照先が変わってしまった』問題です。

 

 

ちょっとわかりにくいかもしれませんが、なるべくわかりやすく書きますね。

仕事で納品管理のマクロを作成してまして、同じ品を作成するたびに当然在庫が変わるわけです。

 

並べ替えた時に参照していたセルが変わってしまう

以下が例です。

AAAという品を1月1日に100個注文が入った表の在庫管理表です。

先行して作っている在庫の残が、100個(H2)、

在庫の残-注文(納入支持数100個)=0(J2)、

そしてまた100個作成した(L2)という表です。

そしてまた2月2日に100個の注文が入り、同じ作業をしています。

今回は、分かりやすくするために、2月は101個、3月は102個、4月は103個作成したことにしました。

◯をしてあるH3には、”=L2”と計算式が入っています。

M 列は、H列に入っている計算式を書き出してみました。

f:id:teratchi:20190408150210p:plain


このL2の100個を、次のH3でそのまま参照したいわけです。

このままなら、問題ありませんが、作業の都合上、この表を日付順でソートしなければならないんです。

 

日付(D 列)で並べ替えてみたら、こうなります。

f:id:teratchi:20190408150651p:plain

お分かりになりますでしょうか?

 

AAAの2月2日分(H5)は、本来100個であったのに、300個になっています。

 

M列を見ていただければわかるのですが、はじめは品名順に並んでいるためすぐ上の行を参照しています。

が、これを日付順に並べ変えた時、計算式は、すぐ上の行を参照しつづけるため、違う品名の在庫数を参照してしまうわけです。

2月のAAAは、本来在庫が100個でなければならないのに、すぐ上の行の在庫、300個を参照してしまっています。 

 

 

『並べ替えた後も、同じ品名の、1つ前の日付の在庫数を参照してほしい』

 

つまり、

 

『並べ替える前も後も、同じセルを参照してほしい』

 

これを解決する方法を見つけました。

 

《STEP1》COUNTIF関数を使う

まず、COUNTIF関数を使います。

I列にこのように打ちまして、それを下までコピーしていきます。

初めの範囲の指定を$で囲むことで、範囲の始まりだけが固定されるわけです。

f:id:teratchi:20190408151450p:plain

すると、一つずつセルを広げて参照していくことで、この前にある同じ部品番号のみカウントしていくんですね。だから、後に現れた機種に関してはカウントされないのです。

つまり、この全データの中で、参照しているのがAAAの部品のうち何番目のデータなのかがわかるわけです。

 

▼M列にI列のカウント関数のデータを表記してみました。

f:id:teratchi:20190408152003p:plain

《STEP2》

B列に個別の名前を割り当てます。"=C2&I2"と入力して、下までコピーします。

部品番号に、カウント関数から得られたカウントを足したことで、唯一の名前ができました。

f:id:teratchi:20190408152754p:plain

《STEP3》

STEP2で得られた個別の番号をLOOKUP関数で見つけてもらいます。H2に書き込んだのがこちら。

f:id:teratchi:20190408153349p:plain

 

 

▼M 列にI 列の計算式を入力してみました。参考にしてください。

f:id:teratchi:20190408153358p:plain

 

これで、準備作業は終わりです。早速並べ替えてみましょう。

 

並べ替えてみよう

▼B列まで範囲を指定して、日付(D列)で並べ替えてみます。

f:id:teratchi:20190408153821p:plain

 

▼2月のAAA(H5)の先行残が、ちゃんと1月のAAAを参照しています!

f:id:teratchi:20190408154102p:plain

 

まとめ

かなり無理矢理なやり方になりますが、これで問題は解決しました。

このやり方を教えてくれたとても参考になったブログがあり、本当に感謝しています。もう一度探しているのですが、見つけられません。すみません。また見つけたらリンクを貼らせていただきます。

 

どうしても並べ替えた後も同じセルを参照してほしい場合、この関数を使った裏技をぜひお試しください。

 

てらっちでした♪

 

www.teracchi.com

 

 

www.teracchi.com

 

 

www.teracchi.com