エクセルで並べ替えると参照がずれるのは、並べ替える表の中の式が、表の外のセルを相対参照(=C10 のような $ のない参照)しているときです。
並べ替えると式も一緒に動き、行が変わった分だけ参照先がずれてしまいます。$ を付けた絶対参照にすると、ずれません。この記事では、実際の Excel で確かめた結果を説明します。
練習用Excelファイル(無料ダウンロード)
この記事と同じ注文の表と単価表です。
Excelファイル(.xlsx)/登録不要/数式・練習データ入り
目次
原因と直し方のまとめ
| 式の書き方 | 並べ替えたあと |
|---|---|
表の外を相対参照 =C10 | ずれる(のりが 150 になった) |
表の外を絶対参照 =$C$11 | ずれない |
品名で引く =XLOOKUP(B3,$B$9:$B$11,$C$9:$C$11) | ずれない |
同じ行の値だけ使う =E3*F3 | ずれない |
並べ替える前の表
C 列は単価表を =C9・=C10・=C11 と相対参照、D 列は =$C$9 などの絶対参照です❶。単価表は表の下にあります❷。

相対参照の式はずれた
品名で並べ替えると、のりの行の C4 は =C10(ボールペンの単価)を見ていて❸、のりの単価が 150 になりました❹。本当は 120 です。

$ を付けた絶対参照はずれない
D4 は =$C$11 と、のりの単価を見たままです❺。D 列は 200・120・150 と正しい単価のままでした❻。

うまくいかないときの確認表
| 症状 | 原因 | 対処 |
|---|---|---|
| 並べ替えたら値がおかしい | 表の外を相対参照している | $ を付ける(F4 キー)か、XLOOKUP で引く |
| #REF! になった | 上の行を使う累計の式(=C2+B3)を並べ替えた | 累計の列は並べ替えたあとに作り直す |
| 元に戻したい | — | すぐに Ctrl+Z で戻す |
よくある質問
別のシートを見る式もずれますか?
実機で =単価!A1 のように別のシートを見る式を並べ替えると、参照先は変わらず、正しい値のままでした。ずれるのは、同じシートの表の外を相対参照しているときです。
並べ替えても困らない表にするには?
単価は品名から XLOOKUP で引くと、どの順番に並べても正しくなります(絶対参照・相対参照とは)。
練習用Excelファイル(無料ダウンロード)
注文の表と単価表で試せます。
Excelファイル(.xlsx)/登録不要/数式・練習データ入り

コメント