Excelで
関数や数式コピー時の
セル番地の”ズレ”を固定する
(絶対参照&相対参照)

この記事は約5分で読むことができます。

2015-05-19 2018-05-09

  • キーワード
  • Excel
  • その他
  • 関数
  • 数式
  • 移動
  • コピー
  • 貼付け
  • 引数
  • セル番地
  • ズレ
  • 固定
  • 相対参照
  • 絶対参照
  • $使い方

エクセル MOS上級を保有し、20年以上ほぼ毎日Excelを使う「ヒサ」です。無意識に他と比較してしまうのは疲れる生き方だなぁと思う今日この頃... この記事は、エクセルで「計算式をコピーや移動するとセル番地がズレる...」「セル番地を固定する方法は?」「「$」記号ってどう使う?」と お悩みの初心者さんに向けた「絶対参照」「相対参照」に関する、やさしく・わかりやすい解説です。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

パターン 指定 列(この場合A列) 行(この場合2行目)
1 A2 可変(相対参照) 可変(相対参照)
2 $A$2 固定(絶対参照) 固定(絶対参照)
3 $A2 固定(絶対参照) 可変(相対参照)
4 A$2 可変(相対参照) 固定(絶対参照)

4.$記号の入力を簡単にする(絶対参照のショートカット)

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

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

参照を切り替えるショートカットキー

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

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

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

この「相対参照」と「絶対参照」を理解して使いこなせれば、長く多くのセル参照を含んだ数式や関数を大量にコピーしても、参照先を自在にコントロールできます。

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

=AVERAGE($A$1:F$10)

この様に、一つは列も行も絶対参照、一つは列は相対参照で行は絶対参照のようなしても可能です。

6.「相対参照」からひとつづつ「絶対参照」へ設定するのが近道

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

  • 手順1.セル番地を相対参照で入力
  • 手順2.関数や数式に問題がないか確認
  • 手順3.隣のセルに関数や数式をコピー
  • 手順4.元のセルに入った関数や数式で固定したいセル番地を決定
  • 手順5.セル番地を$で絶対参照に設定

私も上記の流れで計算式を作っています。皆さんもぜひ「相対参照」と「絶対参照」を覚えて、使いこなせるようになってください。

同じカテゴリの記事