MENU

エクセルで並べ替えると参照がずれる原因と直し方|表の外を見る式は$を付ける【実機で解説】

エクセルで並べ替えると参照がずれる原因と直し方|表の外を見る式は$を付ける【実機で解説】

エクセルで並べ替えると参照がずれるのは、並べ替える表の中の式が、表の外のセルを相対参照(=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)/登録不要/数式・練習データ入り

あわせて読みたい:並べ替えと参照の関連記事

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

この記事を書いた人

コメント

コメントする

目次