エクセルのセル参照とデータ並べ替え

質問

エクセルで、データを並べ替え後、セル参照の式を作ります。そして、元の通りデータを並べ替えます。そうすると、参照のセルが自動で変わってしまいます。絶対参照にしても、相対参照にしても、思い通りの結果が得られません。
どうしたらよいか簡単な方法を教えてください！！

回答No.2

mshr1962 · ベストアンサー

内容がわからないのでなんともいえませんが...
よくあるミスとしてなら
１．例としてA:G列に表　同じシートのJ2:J10に参照用の表があり、並べ替え時にJ列を一緒に並べ替えた為、J列の範囲が崩れた。
参照用の表は別シートに作ることで回避する。（並べ替えの影響がなくなる。）

２．LOOKUP,VLOOKUP,MATCH等の検索関数で最後の引数TRUE又は1(-1)で参照している。
この場合、昇順が絶対条件のため異なる値が表示されるかエラーになる。
引数をFALSE又は0にするか、この関数を使わないようにする。
もしくは別シートに昇順の状態の表を作成してこちらを参照する。

お礼

回答ありがとうございました！
参照用の表は別シートに作らないといけないのですね。参考になりました。ありがとうございました。

回答No.4

banker_U

>「＞それと…」以下の、絶対参照でセル参照の式を作ったセルを基準列にした並び替えは意図していません。
→しっかりやってますがな。これがだめだと言うんです。
　ちょっと下の例で考えてみてください。

　　A　　B
1　1　　=$A$2
2　2　　=$A$3
3　3　　=$A$1★

上記の場合、B1,B2,B3セルの値はそれぞれ2,3,1となるわけですが、
ここで、B列で昇順に並び替えることを考えましょう。

B列で一番値が小さいのは値が1であるB3セル(星のついたセル)です。
これを一番上にもってくる必要がありますね。
そうすると、A列も一緒に移動するので、こうなります。

　　A　　B
1　3　　=$A$1★
2　1　　=$A$2
3　2　　=$A$3

ところが、星の着いたセルはA1セルを参照しているので、この時点で、値は3になっています。
つまり一番小さい値になっていません。
と言うわけで、いつまで経っても並び替えが終わらないことになってしまいます。

あなたのやっていることはよく見えませんが、これと同等のことをやっていると思います。
つまり、絶対参照にすれば、セルが移動しても参照しているセル(のアドレス)は変わりません。
でも、参照先のセルの内容(値)が変わってしまえば、当然参照している値は変わってくるということです。

あと、書いていただいたサンプルでは、やりたいことがさっぱりわかりません。
数式が入っているセルは数式で書いてください。

お礼

回答ありがとうございました！
分かりづらい質問ですみませんでした。ご丁寧に回答してくださり、ありがとうございます。
とりあえず、No.２、３の方の回答で、理解しました。
また何かありましたらよろしくお願いします！！

回答No.3

takkuni

元の通りに戻せば、絶対参照にしたところで行番号が動くわけですから参照先も当然変わると思います。
手っ取り早い方法としては、A列にB列に応じたデータが参照されたら、隣のセルにでも値として貼り付けてしまえば、元に戻しても関数とは関係なく、只のデータに変わりますから大丈夫ではないでしょうか。
貼り付け後は、関数を入れておいたA列は削除するか、非表示にしておくかどちらでも構いません。

お礼

回答ありがとうございました！
希望していることはできないということがはっきりしました。
値として貼り付ける方法、いいですね。ただ、今回は、あくまで参照を維持したかったので…。でも、別件で使わせていただこうと思います。ありがとうございました。

回答No.1

banker_U

絶対参照なら、並び替えても参照セルは変わらないと思うんですけどね。セルに色とかつけておくとよくわかるかもしれません。例えば、セルに黄色の色をつけて内容を並び替えの前後で、比べてみてください。内容は変わってないはずです。

正直したいことがよくわからないのですが、多分なにか勘違いをされていると思います。
セル参照の式を作った列を、並び替えの列に入れたり、入れなかったり、絶対参照にしたり相対参照にしたり、計２×２＝４つの組み合わせを試してみれば、どれかがあなたの求める答えになると思います。

どうしてもできないのなら、こういうサンプルでできなかったという例を出していただければ、適切な回答もできるかもしれません。

それと、絶対参照でセル参照の式を作ったセルを基準列にして、参照先もセルもいっしょに並び替えをすると、並び替え自体がうまくいきません。

補足

回答ありがとうございます！質問が分かりづらくてすみません。アドバイスを試してみましたが、やっぱり、どうしてもできません。「＞それと…」以下の、絶対参照でセル参照の式を作ったセルを基準列にした並び替えは意図していません。
長いサンプルで申し訳ないのですが、よろしくお願いします。

A列／B列／C列
data1／1／1
　／1／2
data2／2／3
　／2／4
data3／3／5
　／　／6
　／　／7
　／1／8
　／2／9

というデータがあります。A列の空欄の所は、B列=3ならdata3というように、B列に応じたデータが参照できるようにしたいのです。でも、最終的にはC列の順番に並べ替えたいのです。ちなみにB列の空欄の所もあります。そこは参照しません。