Excel使いこなしの基礎と応用


絶対参照・相対参照・複合参照を徹底的にわかりやすく

Excelを操作していると必ずぶつかるのが「絶対参照」と「相対参照」。イメージはうっすら把握できるんだけど、こういう時はこっち!っていう確信がイマイチ持てない…そんな悩ましいExcel語をここらでガツンとシメてみましょう。

目次


絶対参照には「$」が付く

A1」「B2」など普通にセル番地を入力した場合、それは全て「相対参照」です。

これを「絶対参照」に変えるには、「$A$1」「$B$2」のように「」ドル記号を列アルファベットと行番号に付ける必要があります。

※ セル番地に$を付けるにはキーボードで入力する他に、「F4」を押す方法があります。
参照セル「A1」をクリックした後、F4キーを押していくと「$A$1」「A$1」「$A1」「A1」と表記が変わります。「絶対参照」「複合参照(絶対行参照)」「複合参照(絶対列参照)」「相対参照」の順ですね。

$A$1」って、見た目ガッチリ鍵がかかった印象ですよね。見た目だけじゃなく、実際「書き換え禁止」の厳しいオキテで守られているのが「絶対参照」なのです。

なにせ、二つも鍵がかかってますから。

そう、セル番地には二つのドアがあり、それぞれに鍵をかけたり外したりできるんです。このイメージを念頭に置けば絶対参照・相対参照・複合参照の区別は自ずとついてくるはずです。特に、後述する複合参照への理解を深めるのに役立ちます。

さて、まず絶対参照の使用例を上げてみましょう。
絶対参照の使用例に使う表サンプル
2割引セールの商品ごとの値引き価格を算出するため、E5セルに「=D5*H2」と数式を入れました。D5セルには商品Aの価格の数値、H2セルは値引率20%の数値が入っています。どちらのセルにもまだ鍵がかかってないから相対参照ってことです。

この数式を商品Eのところまでフィルコピーすると、
相対参照での結果
正常な計算結果は出ません…。0が並んじゃいました。
・・・で、「H2」に鍵かけて絶対参照にします。「=D5*$H$2
数式を絶対参照に修正
再度オートフィルコピーしてみましょう。
絶対参照の式をフィルコピー
商品B~商品Eまで価格に応じた値引き額が正常に表示されましたね。
数式表示にしてみると、絶対参照にしたセルは最後まで可変無しの同一コピーなのが確認できます。
絶対参照の結果を数式で表示
既におわかりでしょうが、絶対参照の性格を一言で表せば「不変です。これに対して、相対参照の性格は「可変です。

スポンサーリンク

次は相対参照の実例を見ながら、この相反する二つの参照形式への理解を更に深めましょう。

可変するのが相対参照

上記の実例で2割引セールの商品ごとの値引き価格を算出するのに、参照セルを相対参照にしたら、下図のように0が並んじゃいましたね。
相対参照での結果
この結果を導き出した状況について簡単に説明すると、値引率の参照セル「H2」が「相対参照」なので行番号が1つずつズレてしまい、空白セルと掛け算しちゃってるために「0」が並んでしまった・・・ということなのです。

下図を見て下さい。(計算結果を数式表示にしています)
相対参照で間違った結果が導かれる構図
一番上「=D5*H2」はD5(商品Aの価格200円)にH2(0.2)を掛けて40円
二番目「=D6*H3」はD6(商品Bの価格600円)にH3(空)を掛けて0円
三番目以降も同様ということで、上記例ではコピー元の数式「=D5*H2」の「H2」に鍵をかけて「$H$2」にし、価格は可変させながら値引率の0.2は不変にして計算させたわけですね。

このように、相対参照のセルはコピー動作の規則性をそのまま反映して行番号や列アルファベットを変化せていくのです。

もう一つ、相対参照の使用例として、この表の価格欄を見てみましょう。

相対参照の使用例に使う表サンプル
商品Aの価格(D5セル)に数量(B5セル)単価(C5セル)を掛け算する式を「=B5*C5」と入力します。
数量×単価の数式を入力
「=B5*C5」という数式の意味をこの課題に則した言い方に換えれば、「B5セル」にある数値と「C5セル」にある数値を相対参照し、掛け合わせるということになります。Enterを押して計算結果を表示させ、この相対参照セルが入った数式「=B5*C5」を商品B~Eの位置まで下へオートフィルコピーすると、
相対参照の数式をフィルコピー
行ごとの数量と単価が掛け合わされて価格が算出されます。
行ごとに数式が可変

計算結果を数式表示に換えてみましょう。

コピーされた位置の行番号を反映して参照セルが変化しているのがわかりますね?

複合参照を知る

複合参照」とは行と列のどちらかを絶対参照にする参照形式です。

【絶対列参照】
参照セルは「G5」。C5セルに「=$G5」と入力。
列に鍵をかけてG列以外は参照できないようにし、行は可変自由です。
複合参照の絶対列参照
上下左右どこにフィルコピーしてもG列参照だけは変わりません。


【絶対行参照】
参照セルは「C10」。C5セルに「=C$10」と入力。
行に鍵をかけて10行以外は参照できないようにし、列は可変自由です。
複合参照の絶対行参照
上下左右どこにフィルコピーしても10行参照だけは変わりません。


複合参照の最もわかりやすい実例として、九九を表にしてみましょう。
複合参照の使用例で九九の表を作成
B2セルに2×2の式を「=B1*A2」と入力。Enterを押せば、「4」という計算結果が表示されます。この式をこのままで右方向にフィルコピーしてみると、
最初の九九の式を横方向へフィルコピー
うっかり者の私でもこの九九は間違ってることがわかります・・・
なぜこうなるかといえば、A2が相対参照になっているため、数式の参照セルが B2 ⇒ C2 ⇒ D2 と移動してしまっているからです。C2セルには3×2(=C1*A2)の式が入るべきなのに、3×4(=C1*B2)の式が入っちゃってるんですね。で、結果が12。
九九の計算結果が間違う構図

そこで、A2の列アルファベットに鍵をかけて複合参照(絶対列参照)にします。

B2セルに入力した数式を「=B1*$A2」と修正。再度フィルコピーすると、
絶対列参照に修正して再度コピー
正常ですね。「=B1*$A$2」と絶対参照にしてもこの場合は結果は同じだけど、縦のコピーで行番号が変わらないとまずいので行の鍵は外しておくのです。

今度はB2セルを下へフィルコピーしてみると、繰り返し説明しなくても上記の結果の縦バージョンになるのはわかると思います。

…で、必然の流れとして、数式の修正箇所もわかりますね?
「=B1*$A2」を「=B$1*$A2」にします。行番号に鍵をかけて複合参照(絶対行参照)にしたわけです。

この式を分解してみましょう。

=

B$1(複合参照の絶対行参照)

行番号1に鍵をかけました。行番号1は全てのセルで固定されます
=掛け算に使う前の値は行番号1に並ぶ数字です!という宣言
絶対行参照で固定された1行目
列番号Bには鍵はかかってません。横方向にコピーした時に列番号は可変
(縦方向にコピーしても列番号が変わらないのは当たり前。AはずっとA)
横に移動すれば、行番号1に並ぶ数字は列に応じた値が参照されます。C列なら3、E列なら5となります

*

$A2(複合参照の絶対列参照)

列番号Aに鍵をかけました。列番号Aは全てのセルで固定されます
=掛け算に使う後の値は列番号Aに並ぶ数字です!という宣言
絶対列参照で固定されたA列
行番号2には鍵はかかってません。縦方向にコピーした時に行番号は可変
(横方向にコピーしても行番号が変わらないのは当たり前。2はずっと2)
縦に移動すれば、列番号Aに並ぶ数字は行に応じた値が参照されます。4行目なら4、6行目なら6となります


絶対行参照に修正して再度フィルコピー
=B$1*$A2」という式をB2セルに入力し、これを上下左右にフィルコピーすれば九九表が出来上がる仕組みがお分かりいただけたでしょうか?

セルには「このセルから見て何列目、何行目を参照」という情報が隠れてる。コピーする時はこの情報を意識すべきだけど、これが一番解りにくいんだよね。この位置情報を明確にするには参照形式をR1C1に変えてみるといいよ

一応、全セルを数式表示にした画像↓も貼っておきますね。
九九表の数式表示

スポンサーリンク

Excelを操作中につまずいた時、「絶対参照」「相対参照」「複合参照」をふと思い出してみると案外それが突破口になるかもしれません。

Officeで作れるもの