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

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

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

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

スポンサード リンク

ズレの原因は「相対参照」だから

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

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

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

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

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

「絶対参照」ならズレない

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

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

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

「絶対参照」の指定方法

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

関数や計算式内にある引数のセル番地の「$記号」を付ける位置で「固定する」「固定しない」を設定できます。 「$」は半角です。色を付ける必要はありません。

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

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

「$」入力を簡単にする

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

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

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

カーソルの位置は「A2」「A2」「A2」と、どこでもOKです。

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

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

一つの計算式に入った複数の参照先セルは、個々に「相対参照」と「絶対参照」を指定することができます。

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

私は実際にこの方法を使っていますので、皆さんもぜひ「相対参照」と「絶対参照」を使いこなせるように研究してみてください。

スポンサード リンク