特集

PC活用講座 - Excel

「Excelの便利機能活用術」
絶対参照と相対参照を使い分ける

ITライター 立山秀利

Excelのセルの参照方式には大きく分けて、「絶対参照」と「相対参照」といった2通りの方法が用意されている。1つのセルに入力した式を他のセルにコピーして展開する際、両者の参照形式を適切に用いる必要がある。今回は、絶対参照と相対参照の使い分け方を紹介する

「絶対参照」を理解しよう

入力した式を他のセルにオートフィル機能などでコピーする際、式の中で参照するセルを「絶対参照」で指定すると、必ず同じセルを固定して参照するようになる。絶対参照で指定するには、行と列それぞれに「$」を冒頭に付けて記述すればよい。例えば、A1セルなら「$A$1」と記述する。

一方、相対参照で指定すると、行と列の番号がコピー先のセルに応じて自動で変化することになる。相対参照で指定するには、行と列ともに冒頭には何も付けない。A1セルなら「A1」と記述する。これら2通りの参照方式を適切に使い分ける必要がある。

例えば、ある商品の価格リストがB7~C7セルに入力されているとする。そして、割引率をF4セルに入力しておき、元の価格の各データを掛け合わせ、割引後の価格をF7~G9セルにそれぞれ算出したいとする。その方法として、1つ目の商品にて割引後の価格を求める式をF7セルに入力し、その式を残りの商品にコピーしたいとする。

もし、F7セルの式で、割引率のF4セルを相対参照で指定してしまうと、他セルにコピーした際、割引率のセルの参照先がずれてしまい、正しく計算できなくなる。

図

(画像をクリックすると拡大表示します)

・F4セル:割引率。数値の「0.2」を入力し、表示形式を「パーセンテージ」に設定している
・F7セル:割引後の価格を求める式を入力
・数式バー:割引率のF4セルは相対参照で指定
・G7およびF8~G9セル:F7セルの式をコピー

F7セルに「=B7*(1-F4)」と式を入力する。割引率のF4セルを相対参照で指定しているため、F7セルの式を残りのセルにコピーすると・・・

図

(画像をクリックすると拡大表示します)

・G7セル:割引後の価格が正しくない
・数式バー:割引率は右隣のG4セルを参照してしまっている
・F8~G9セル:G7セルと同様

相対参照のため、割引率のF4セルがずれてコピーされ、割引後の価格が正しく求められない

一方、F7セルの式で、割引率のF4セルを絶対参照で指定すれば、他セルにコピーしても、F4セルが常に参照されるので、正しく計算できる。

図

(画像をクリックすると拡大表示します)

・F7セル:割引後の価格を求める式を入力
・数式バー:割引率のF4セルは絶対参照で指定
・G7およびF8~G9セル:F7セルの式をコピー

F7セルに「=B7*(1-$F$4)」と式を入力する。割引率のF4セルを相対参照で指定しているため、F7セルの式を残りのセルにコピーすると・・・

図

(画像をクリックすると拡大表示します)

・G7セル:割引後の価格は正しい
・数式バー:割引率は常にF4セルを参照
・F8~G9セル:G7セルと同様

絶対参照のため、コピーしても割引率のF4セルが必ず参照され、割引後の価格が正しく求められる

また、元の価格のB7セルを絶対参照で指定すると、コピーした際に他のセルでもB7セルの商品の価格が計算に使われてしまうため、正しく計算できなくなる。よって、相対参照で指定しなければならない。

「複合参照」も使える

さらには、行と列のいずれかを絶対参照、残りを相対参照で指定する「複合参照」での指定も可能。式をコピーした場合、行または列のいずれかが固定されることになる。

例えば、F4セルを式の中で参照する場合、コピーした際に行のみ固定したいならば、行のみを絶対参照とした複合参照で指定する。

-----------------------
F$4
-----------------------

逆に列のみ固定したいならば、列のみを絶対参照とした複合参照で指定すればよい。

-----------------------
$F4
-----------------------

参照方式を素早く切り替えるには

また、式を入力中、目的のセルの箇所にカーソルが点滅した状態にして、F4キーを押すと、参照形式が次の順番で押す度に切り替わる。いちいち「$」を入力/削除する手間が省け、式の入力を効率化できるだろう。

相対参照

絶対参照

行のみ固定の複合参照

列のみ固定の相対参照

図

(画像をクリックすると拡大表示します)

数式バーの反転部分:F4キーで参照形式を切り替えている最中は反転して表示される

このようにExcelの絶対参照と相対参照を正しく理解して使い分けられれば、複数のセルに式を適切に入力できるようになるだろう。

その他の【Excel】を見る

PC活用講座 一覧へ

立山秀利(たてやま・ひでとし)

カーナビのソフトウェア開発、Webプロデュース業務を経て、現在は、システムやネットワーク、Microsoft Officeを中心に執筆中。 主な著書に『Excel VBAのプログラミングのツボとコツがゼッタイにわかる本』などがある。

(監修:日経BPコンサルティング)