Excel(エクセル)の関数や数式でセル・セル範囲を絶対参照にする方法

Excel(エクセル)数式や関数でセルまたはセル範囲の絶対参照をする方法を紹介します。

そもそも絶対参照ってなに?という方のために、絶対参照とは何なのか前提の話から、相対参照との違いについても一緒に説明していきたいと思います。

まずは結論からチェック

  • 絶対参照とは「$」マークを使ってセルやセル範囲を固定すること
  • 絶対参照にするには、セル・セル範囲の横に「$」マークをつければOK
    参照の記述形式一覧表(A1:相対参照 $A$1:絶対参照 A$1:行だけ絶対参照 $A1:列だけ絶対参照)


    数式で使う例

    =A1+$A$2
    =B2/B$5
    =C3*$C1

    関数で使う例
    =VLOOKUP($F3,$A$2:$D$11,2,0)
    =COUNTIF($B$4:$B$11,E4)
    =SUMIF($A$4:$C$13,"1-*",$C$4:$C$13)

そもそも絶対参照って?範囲を固定するための記述の仕方

絶対参照とは、指定するセルやセル範囲を固定する参照方法のことです。


なぜこの絶対参照が必要なのでしょう?

それは関数式をオートフィル機能でコピーしたときに指定範囲をずらさないようにするためです。
絶対参照の範囲固定イメージ

Excelでは関数式を使用する際に入力した式を別セルにもコピーして処理することが多いため、コピーすることを前提に範囲や条件の指定をするのが一般的です。また絶対参照でセルやセル範囲を指定するには「$(ドルマーク)」を使います。

 絶対参照に関する入門編はチェック

  • 絶対参照とは?相対参照との違いは
  • 絶対参照の記述の仕方($マークの使い方)
  • 絶対参照の3つの記述パターンを解説
  • ショートカットキーで$マークを簡単入力する方法

ここからは、絶対参照を理解したうえでの実践的な話をしていきたいと思います。

Excelの数式と関数で絶対参照を活用してみよう

さて、では本題です。

具体的にどういうときにどう使えばいいの?という疑問を解決するために、次の章では実際の活用方法を解説していきます。

数式や関数で計算をするとき
『 絶対参照を使うべきなのか、相対参照を使うべきか 』

シチュエーションに応じて必要となる参照方法は異なります。

参照をずらさずコピーすることで成立する → 絶対参照
参照をずらしてコピーすることで成立する → 相対参照

どういった場合にどちらの参照方法を使えばいいのか、パターン別に話していきたいと思います。

絶対参照を使う必要がある場合

数式の例

数式で絶対参照が必要な2つの例を紹介します。


行だけ絶対参照
例)合計に対する店舗あたりの数値割合を日ごとに算出したい、以下の表形式の場合数式で行だけ絶対参照する例

セルB5の数式をコピーしたときに、参照を下にはずらしたくないですが左にはずらしたいので「=B2/B$5」というように『行だけ絶対参照』にする

セルE2の数式をオートフィル機能で下横にコピーすると表全体の数式はこのようになります

割合割合割合
=B2/B$5=C2/C$5=D2/D$5
=B3/B$5=C3/C$5=D3/D$5
=B4/B$5=C4/C$5=D4/D$5
範囲E1:G4の拡大データ



列だけ絶対参照
例)合計に対する1日あたりの数値割合を店舗ごとに算出したい、以下の表形式の場合数式で列だけ絶対参照する例

セルE2の関数コピー時に、参照は下にはずらしたいですが左にはずらしたくないので「=B2/$E2」のように『列だけ絶対参照』にする

セルE2の数式をオートフィル機能で他セルへコピーすると表全体の数式はこのようになります

割合割合割合
=B2/$E2=C2/$E2=D2/$E2
=B3/$E3=C3/$E3=D3/$E3
=B4/$E4=C4/$E4=D4/$E4
範囲F1:H4の拡大データ

行列(上下左右)どちらも固定したい場合は、
「=B2/$E$2」のようにセル番号の英数字の両端を「$(ドルマーク)」で囲うようにします。

このように、逆算してどういう参照パターンにすればのちのちの作業が楽になるのか見極めて、状況に応じて『行・列の絶対参照』を使い分けしましょう。

関数の例

関数で絶対参照が必要になるパターンとして、使用頻度の多いVLOOKUP関数の例を紹介します。

VLOOKUP関数とは?という方はこの章の末尾にある補足を参照してみてくださいね。


例:セルF3に入力したNO.をもとに該当するポケモンのスペックを一覧表(A2:D11)から別表(F2:I7)に取り出し、関数を表全体にコピーしたいVLOOKUP関数で絶対参照する(例のイメージ)

VLOOKUP関数の公式(※)は
=(検索値 , 検索範囲 , 列番号 [ , 検索方法 ])

セルG3に入力した関数式をコピーするときに、【検索値】はF3,F4,F5...というように下にはずらしたいけど横にはずらしたくありませんよね?なので検索値は「列だけ絶対参照」にします

続いて【検索範囲】は、下にも横にもずらさずずっと固定しておきたいので検索範囲は「完全な絶対参照」にします


そうするとVLOOKUP関数の式は「=VLOOKUP($F3,$A$2:$D$11,2,0)」となる
VLOOKUP関数で絶対参照する(VLOOKUP関数に引数を入れる)


これでセルF3に入力したNO.のポケモンスペックが表示されたのを確認したら、セルG3の関数をオートフィルでコピーしてみましょう
VLOOKUP関数で絶対参照する(G3の数式をコピーする)

指定した参照がずれずに同じ条件で計算することができるようになっていますね

G列下部の「#N/A」は現在はF列が未入力のためエラーになっていますがF列を入力すれば問題なく処理されます

この関数計算を相対参照で行ってしまい指定がずれて意図しない処理となってしまう可能性大なので、できるだけ絶対参照を使う心がけが大切です。

同時に多くの関数を使ったりなど複雑になればなるほど、気がつかない間に参照がずれていた…なんてこともあるので注意しましょう。

補足:VLOOKUP関数とは?
VLOOKUP関数は、指定した範囲のなかで指定した条件に一致するデータを検索し(同じ行にあるデータを)取り出してくれる関数です。

VLOOKUP関数の公式
=VLOOKUP(検索値 , 検索範囲 , 列番号 [ , 検索方法 ])

VLOOKUPを使うときの大抵が絶対参照を要するパターンであることが多いので、併せて絶対参照の方法もしっかり理解しましょう。

また、VLOOKUP関数を使って2つのデータ群を比較して重複を探す方法についてはこちらの記事で書いているのでチェックしてみてくださいね。

相対参照を使わなくてはならない場合

数式の例

今度は、相対参照を要する場合です。まずは数式の例から。


例:以下のように、3日間(B列C列D列)のセル値の合計を店舗ごとに算出したい(数式はセルE2の式をコピーする)
数式で相対参照する(例のイメージ)

セルE2の数式をコピーするときに、B2・C2・D2の参照はすべて下にずらしたいので「相対参照」にする

なので、$マークはつけずにそのままの状態で下段へコピーしなければなりません
数式で相対参照する(E2の数式をコピーする)

そして数式を表全体にコピーすると以下のようになります

1日目2日目3日目合計
147=B2+C2+D2
258=B3+C3+D3
369=B4+C4+D4
セル範囲B1:E4の抜粋データ

左にずれないように列だけ絶対参照にしてもいいのですが、この場合は左方向にコピーすることがないためあえて絶対参照にはしなくてよい、というパターンになります。

関数の例

今度は、関数で相対参照が必要になるパターンとして、使用頻度の多いSUM関数の例を紹介します。

数式で用いた例とほぼ同じです。


例:以下のように、3日間(B列C列D列)のセル値合計をSUM関数で行ごとに算出したい(関数式はセルE2の式をコピーする)
関数で相対参照する(例のイメージ)

SUM関数の公式:=SUM(合計範囲)

セルE2の数式をコピーするときに、B2:D2の参照合計範囲はそのまま下にずらしたいので「相対参照」にする

なので「$マーク」をつけずにそのままの状態で下段へコピーしてOK
相対参照の関数コピーのやり方

最終的に表全体の関数式は以下のようになります

1日目2日目3日目合計
147=SUM(B2:D2)
258=SUM(B3:D3)
369=SUM(B4:D4)
セル範囲B1:E4の抜粋データ

数式と同様で、左にずれないように列だけ絶対参照にしてもいいのですがこの場合は左方向にコピーすることがないためあえて絶対参照にはしなくてよい、という症例になります。

以上、今日は絶対参照の実践編として、関数や数式でセル・セル範囲の絶対参照を指定する方法と具体例を紹介してきました。

最初は少し小難しく感じるかもしれませんが、これをマスターすれば作業も大幅に効率化できますのでぜひ実践してみてください。

WordPress初心者さんにおすすめの本(Amazonレビュー: