【エクセル超初心者向け】Excelで1つの数式をコピペで使い回すために知っておきたい絶対参照・相対参照の使い分け方

Aさん

数式はセル参照することが有効なのは理解しました。しかし、数式を使うセルが多いと設定が大変ですね。何か良い方法はありますか？

森田

そうですね。数式を使う際のコツは、「いかに1つの数式を複数セルへ使いまわすか」ということです。
そのために知っておいてほしい知識として、絶対参照・相対参照があります。
それでは、詳細をわかりやすく解説していきますね。

はじめに

本題に入る前に、この記事がおすすめな人を挙げてみます。

• エクセルの数式や関数を使う頻度が高い人
• エクセルの数式や関数を効率的に使いたい人
• 数式を使う際に絶対参照・相対参照をどう設定すれば自信がない人

ちなみに、最低限「エクセルの数式はセル参照が原則であること」を理解している想定での記事となります。
※自信がない方はこちらの記事で学んでください。
→【超初心者向け】エクセルの数式・関数は「セル参照」を原則とすべき4つの理由

1つの数式をコピペで使い回すことが基本！

まず、数式を使う上での基本的な考え方ですが、なるべく1つの数式を使い回せるところはすべてコピペで済ましましょう。

仮に100セルへそれぞれ数式を入れなければならない際、すべて1セルずつ数式を入力していくのは骨が折れますね。

しかし、数式のパターンはすべていっしょであれば、1つのセルの数式を仕上げ、それをその他の99セルへコピペすれば最小限の労力で作業が完了します。

イメージ的には次のとおりですね。

複数セルへ数式を入力しなければならない際は、一旦手を動かす前に「何パターンの数式をまず入力すればコピペで使い回すことができそうか」を考えることを基本にしましょう。

絶対参照・相対参照の意味を理解しよう！

「数式をコピペで使い回す」という基本を理解したら、次に必要なことはそのための設定方法です。

ちなみに、その設定を行わないと、次のようになります。

上記は九九の表で、九九の計算結果になるように、B2～J10セルの数式はA列と1行目にある値をそれぞれ掛けた値を掛け算させたかったのですが、掛け算の対象のセルがすべてずれておかしな計算結果になっていますね。

この例の正しい設定方法は本記事の後半で解説していきますが、コピペの起点となるセルの数式がコピペで使い回すことを想定したものになっていなかったため、このような残念な結果になったのです。

なお、コピペで使い回すために必要な設定は絶対参照、相対参照という2つの参照形式です。

それぞれ「数式中でどのようにセル参照するか」という設定だと認識してくださいね。

絶対参照とは？

絶対参照とは、「絶対」という名のとおり、コピペなどで数式の位置が変わったとしても、セル参照を行なうセルの行・列のいずれか、もしくは両方を固定するための参照形式です。

特徴として、参照しているセルが「$A$1」のように”$”が追加されます。

ちなみに、上記のケースはA1セルの列（A）も行（1）も絶対参照のため、それぞれの左側に”$”がついていますね。

上記は行列ともに絶対参照の例ですが、行（1）のみが絶対参照されていれば「A$1」、列（A）のみが絶対参照されていれば「$A1」となります。

相対参照とは？

相対参照とは、絶対参照と違いコピペなどで数式の位置が変わったら、セル参照を行なうセルの行・列のいずれか、もしくは両方が相対的に移動させるための参照形式です。

特徴としては、絶対参照の目安となる行列の左側の”$”がないことですね。

ちなみに、数式でセル参照をはじめて行なうと、デフォルトでは行列ともに相対参照です。

先ほどと同じくA1セルで例えると、行（1）も列（A）ともに相対参照の場合は「A1」、行（1）のみが相対参照なら「$A1」、列（A）のみが相対参照なら「A$1」となります。

絶対参照・相対参照の切り替え方法

続いて、実際にどのように絶対参照と相対参照を設定するか、操作方法を解説していきます。

主な操作方法は3種類ありますので、順番に見ていきましょう。

【方法①】F4キーを押下する

方法①が一番オーソドックスです。

操作方法は、数式バーの中で参照形式を設定したいセルにカーソルを合わせてF4キーを押せばOKです。

ちなみに、デフォルトは行列ともに相対参照ですが、F4キーを押すたびにこの参照形式が以下のように切り替わります。（4回目は再度デフォルト状態へ戻ります）

具体例は以下のとおりです。

デフォルト（or F4×4回）：行列ともに相対参照

F4×1回：行列ともに絶対参照

F4×2回：行は絶対参照、列は相対参照

F4×3回：行は相対参照、列は絶対参照

【方法②】数式中に直接”$”を入力・削除してもOK！

もちろん、数式バーの中の数式に対して直接的に”$”を入力あるいは削除しても問題ないです。

もし、F4を何回も押すのが面倒な場合はこちらで対応しても良いですよ。

【方法③】置換機能で一括変換！

複数セルを一括で変換したい場合は置換機能を使うこともおすすめです。

【Ctrl＋H】で「検索と置換」ダイアログを起動させ、参照セルの一部分に対し、「検索する文字列」には変更前のものを、「置換後の文字列」には変更後のものを入力して「OK」ボタンをクリックすればOKです。

具体例は以下のとおりです。

上記は、B列を相対参照から絶対参照に変更したかったため、「検索する文字列」には”B”を、「置換後の文字列」には”$B”を入力しています。

ちなみに、「検索する文字列」に指定する文字列が短い場合は、関数名やその他のセル番地まで変更されてしまう場合もありますのでご注意くださいね。

【活用例】参照パターン3種類

絶対参照と相対参照の概念は何となくわかってきたと思いますが、実際にどのような場合にどの参照形式を用いれば良いか、活用例をご紹介します。

【パターン1】行列ともに相対参照＋行列ともに絶対参照

パターン1は、商品に対してそれぞれ「①定価（税抜）」・「②割引率」が決まっています。

この「①定価（税抜）」と「②割引率」から「③特価（税抜）」を数式で計算し、さらにその「③特価（税抜）」に「⑤消費税」を加味した「④税込価格」も数式で計算を行った表が以下の内容です。

ワークシート上の表記

数式バーの中身

今回は大別して「③特価（税抜）」と「④税込価格」の2パターンの数式をそれぞれ2行目のセルにつくり、3行目以降のセルへコピペしています。

その際、「④税込価格」の数式の中にある「$G$2」のみE列の全セルの数式で共通のため、行列ともに絶対参照にしているわけですね。

それ以外のセルはコピペ先でスライドすれば問題ないため、デフォルトの行列ともに相対参照のままにしています。

【パターン2】行が絶対参照・列が相対参照＋行が相対参照・列が絶対参照

パターン2は九九の表です。（冒頭の例ですね）

ワークシート上の表記

数式バーの中身

この例では、B2～J10セルの数式はA列と1行目にある値をそれぞれ掛けた値を計算するので、B2セルの数式を全セルへ流用できます。

そのため、B2セルで数式を組む際に、1行目を固定するために行のみ絶対参照にした「B$1」と、A列を固定するために列のみ絶対参照にした「$A2」を掛け算にしているわけですね。

【パターン3】セル範囲の複合パターン

最後のパターン3は応用ですね。

A1～A10セルへ1～10の各数値が入っており、隣のB列にはB1セルを起点とした自セルまでの累計を出したいとします。

その場合は、以下のようにセル範囲の起点となるセルのみを行列ともに絶対参照にする、ということで実現できますよ。

ワークシート上の表記

数式バーの中身

「A1:A2」のようなセル範囲は、コロン[：]の前後の2つのセルそれぞれで個別の参照形式を設定できる、ということも知っておきましょう。

さいごに

今回の参照形式の設定方法は、数式の基本ではありますが、きちんと使いこなせるとだいぶ生産性に違いが出てくる重要なポイントですね。

最初は、行列のどちらに”$”をつければ良いか混同してしまいますが、コピペ先の計算結果が正しいか確認しながら、何度も反復練習を積み重ねましょう。

その積み重ねで感覚的に身につく部分でもありますので、あきらめずに試行錯誤してくださいね。

ご参考になれば幸いですm(_ _)m

森田

本記事は、実は私の書籍である「すごい！関数」の中で盛り込むはずだったコンテンツのひとつです。ページ数の都合で泣く泣くカットした部分でしたが、数式を使う方全員に理解してほしい重要ポイントなので記事にまとめて直してみました。
手を動かしながら、頑張って覚えてほしいですね。