エクセルで関数や数式をコピーするとズレるのを固定する
(絶対参照&相対参照)

最終更新日:2017年10月2日

エクセルで関数をコピーするとズレる?(絶対参照&相対参照)

エクセルの関数や計算をコピーや移動して、他のセルに貼り付けると、おかしい場合があります。その場合、セル番地がズレていませんんか?計算を固定して、ズレないようにする方法は?そんな疑問に答えます。

スポンサード リンク

ズレる原因はデフォルトが「相対参照」だから

エクセルで、セルに入力されている関数や計算式を、他のセルにコピーしたり移動したりすると、参照先のセル番地ズレます。これが正常で、デフォルトなんです。 実はこの機能が、エクセルが自動で行ってくれるとても便利な機能なのです。エクセルが壊れたり、バグが発生したり、おかしくなったわけではありません。 (下図では、C5に入力した計算式をC6とC7にコピーしましたが、参照先のセルがズレています。)

関数をコピーするとズレるのは「相対参照」だから

この自動でズレることを、「相対参照」と言います。これは関数や計算式が入っているセルを基準として、参照先を"相対的"にズラす機能です。 エクセル自身が自動ですらしてくれるので、人の作業を軽減してくれるので便利〜(^^)。この相対参照がエクセルの初期設定(デフォルト)です。

同じ計算式を行方向(縦↓)や列方向(横→)に「ダーッ」とコピーしたいことがありますよね。でも計算の対象のセルはひとつづズレている。こんな時に便利なのが、デフォルトの「相対参照」です。 もし相対参照の機能がなかったら、数百とか数千の計算式をひとつずつズラすのは、時間がかかりますし、正直気が狂いそうですw

一方、「消費税」や「なんらかの係数」を一か所で設定しておいて、後から一括変更したい時(例えば、消費税を8%から10%に変更したいとき)は、参照先を固定してずらしたくない場合もあります。 この時の解決方法が「絶対参照」です。(「相対」と「絶対」)

$記号で「絶対参照」にすればズレない

関数や計算式をコピーや移動しても、参照先セルをズレなくする(固定する)方法は、引数に指定しているセル番地に「$」を付けることでズレなくなります。 これが「絶対参照」です。関数や計算式の中で、相対参照になっているセル番地に「$」記号を付けることで、強制的にセル番地を固定させることが出来る機能です。

関数をコピーしても参照セルのズレを起こさない「絶対参照」

具体的にはひとつの計算式内で「相対参照」と「絶対参照」を混ぜて使うのです。 上図の様に、個数(A5,A6,A7)と価格(B5、B6、B7)は「相対参照」のままで、消費税(A2)だけ「絶対参照」に設定できます。

$記号による「絶対参照」の指定方法

$記号による絶対参照は「行のみ固定」、「列のみ固定」、「行も列も固定」と3種類あります。

関数や計算式内にあるセル番地に「$記号」を付ける位置で「固定する(絶対参照)」「固定しない(相対参照)」を指定できます。 「$」は半角です。(ちなみに、色を付ける必要はありません。)

$の場所ですが、参照先セルが「A2」だった場合の指定方法を以下で説明します。

「$」入力を簡単にする(絶対参照のショートカット)

「$」入力を簡単にする

絶対参照を設定する場合、初めは「$」をどこに付けるのか試行錯誤します、その時「$」の場所を動かすのが案外面倒です。

こんな場合、絶対参照と相対参照を切り替えするショートカットキーがあります。絶対参照したいセル番地にカーソル(縦棒が点滅してるやつ)を移動し、キーボードの【F4キー】を押すと$が入力され、さらにF4の入力を繰り返すと$の位置が自動で移動します。

「A2」→ 「$A$2」→ 「A$2」→ 「$A2」→ 「A2」と【F4キー】キーを4回押下すると一周して元に戻ります。

カーソルの位置は「A2」「A2」「A2」と、セル番地の「直ぐ前」、「真ん中」、「すぐ後ろ」のどこでもOKです。

「相対参照」と「絶対参照」で参照先を自在に変える

この「相対参照」と&記号による「絶対参照」を理解し、使いこなせれば、長い計算式を大量にコピーしても、参照先を自在にコントロールすることができます。

一つの計算式で指定されている複数の参照先セルは、個々に「相対参照」と「絶対参照」を指定できます。

計算式を入力しながら「絶対参照」or「相対参照」を決めるのはハードルが高いので、とりあえず「相対参照($なし)」で入力し、計算式をコピーしてみて ズレてほしくない場合は「絶対参照($あり)」に指定すると、間違いなく、迷わず、設定が可能です。

実際に私も、まずは相対参照で入力しちゃんと計算されているか妥当性を確認し、そのあと計算式をひとつだけ隣のセルにコピーして、固定したいセル番地に$で絶対参照を設定するという流れで計算式を作っています。 皆さんもぜひ「相対参照」と「絶対参照」を研究して、使いこなせるようになってください。

スポンサード リンク