【Excel】F4キーで時短もできる!「絶対参照」と「相対参照」の使い方
Excelで、セルに数式を入力し、別のセルにコピーしてみると
思っていたのと違う結果になってしまった…なんて経験、ありませんか?
それはおそらく、数式内のセル番地が相対参照になっているためです。
この場合絶対参照に切り替えれば解決ですが、
「そもそも相対参照がなんなのかわからない!」という方もいらっしゃると思うので、
今回は絶対参照とあわせて相対参照についても説明したいと思います。
手動で入力することもできますが、後述するF4キーを使えば一瞬です。
ぜひ作業の効率化にお役立てください。
「絶対参照」と「相対参照」とは
まず、Excelで数式を扱う際、数値や文字列の入力方法は
①数式に直接入力する
②数値や文字列が入力されたセルを指定してその値を参照する
の2通りあります。
絶対参照や相対参照を使う場合は、
②の「セルを指定してその値を参照する」方式を使います。これを『セル参照』といいます。
この『セル参照』ですが、絶対参照と相対参照とでは
参照される情報の種類が変わるのです。
具体的にどこが違うのかといいますと、
相対参照は、数式が入力されているセルの番地を基準として、
参照元セルとの”位置関係”を相対的に指定する参照方式です。
数式で参照するセルを指定した後、なにも操作しなければ相対参照となります。
この状態で数式が入力されているセルをコピーすると自動的に参照元セルも移動します。
これに対し絶対参照は、数式が入力されているセルの番地とは関係なく、
単純に、参照元セルの行番号と列番号を”座標”として指定する参照方式です。
絶対参照をするには、列番号と行番号の前にそれぞれドルマーク($)を入力します。
参照元セルを移動させたくない場合は、絶対参照に切り替えると、
セルをコピーした時にも参照元セルは固定されます。
絶対参照を使うと、一気に見た目のややこしさが増したように感じますが、
実際のところドルマーク自体に大きな意味はありません。
ドルマークはあくまで固定するための目印のようなものであって、
それは「ピン」であったり「釘」であったり、
自分が覚えやすい何かに例えてみると分かりやすいと思います。
アルファベットの前に$があれば、列が固定される。
数字の前に$があれば、行が固定される。
アルファベットと数字の両方の前に$があれば、列も行も固定された絶対参照となる。
数式を使用するセルが一つのみであるときは、どちらを使っても計算結果は変わりません。
が、数式をコピーして、複数のセルで使いたいときには注意が必要です。
「絶対参照」と「相対参照」の使い分け
それでは、実際に絶対参照と相対参照をどう活用していくか、
簡単に下の表を使って見ていきたいと思います。
まず、金額(税抜)を計算するために、E4セルに”=C4*D4”(数量×単価)と入力します。
二行目以降にも計算結果を表示したいので、オートフィルを使ってE9セルまで数式をコピーします。
そして、試しにE9セルの数式を見てみると、
“=C9*D9”となっているのが分かります。
この計算式にはドルマーク($)をつけていないので、相対参照ですね。
つまり、”数式が入力されているセルをコピーした際、自動的に参照元セルも移動している”状態です。
同じように、金額(税込)を計算します。
税込みの金額を計算したい場合、計算式は「税抜金額×(1+消費税率)」となりますので、
F4セルに“=E4*(1+H3)”と入力します。
これもドルマーク($)をつけていないので相対参照です。
試しに数式を下にコピーして結果を見てみましょう。
どうやら、F5セル以降の税込み金額が正しく計算されていないようです。
税抜き金額の数式をコピーしたときは問題なかったのに、なぜ今回はうまくいかなかったのでしょうか?
それは、今回でいう“H3セル(消費税率が入力されたセル)”のように、
移動してしまうと数式にズレが生じる場合、
参照方式が相対参照のままだと都合が悪いのです。
つまり、上の図の通り「計算式に空白セルが参照される」という辻褄が合わない状態になってしまいます。
これでは、この表は成り立ちません。
そこで、今度は絶対参照の出番です。
先ほどF4セルに入力した“=E4(1+H3)”という式の”H3”の部分にドルマーク($)を付け加えて、
“=E4(1+$H$3)”
と書きかえます。
これは“H3”セルが絶対参照されている状態です。
そして、前回と同じように数式をコピーしてみます。
消費税率が入力されたセルを絶対参照に切り替えることによって、
計算式を他のセルにコピーしても、消費税率のセルが正しく参照されるのです。
このように、
“数式が入力されているセルと一緒に参照元セルを移動させたい”場合には相対参照、
“参照元セルを固定させたい”場合には絶対参照が使われるのです。
F4キーを使った参照方式の切り替え方
相対参照と絶対参照を切り替える方法ですが、先述したように直接入力する方法と、
ファンクションキーを使う方法があります。
ファンクションキーとは、キーボードの上部にあるF1~F12からなる12個のキーのことです。
今回は、この内の「F4キー」を使います。
使い方は、絶対参照にしたいセルをクリックし、そのままF4キーを押すだけです。
また、すでに入力された式を絶対参照にしたいときは、
数式バーの任意のセル番地にカーソルを置き、F4キーを押せば、$が2つの絶対参照になります。
続けてF4キーを押すと、順次参照方式が切り替わっていきます(下表を参考にしてください)。
なお、PCやキーボードの設定によっては「Fnキー」を押しながらでないと切り替えられないこともありますので、
デフォルトでファンクションキーを使いたい場合は設定を変えてみてください。
ちなみに、Excel上での「F4キー」は参照方式の切り替えのほかに、直前の操作を繰り返すときにも使うことができます。
同じ操作を繰り返す単純作業にもってこいなので、この機能を活用して、時間短縮に役立ててみてください。
ほかにもファンクションキーにはExcelを効率よく使えるようになる機能が多数あります。
取り入れて損はないツールですので、一度詳しく調べてみるのもいいかもしれません。
まとめ
いかがでしたか?「絶対参照」も「相対参照」も漢字ばかりで複雑に見える上に、ドルマーク($)のような日本人にはあまりなじみのない記号が使われます。
見ていると頭が痛くなりそうですが、こうしてシステムを理解してみると、案外単純な仕組みであるように思えてきませんか?
相対参照、絶対参照を理解することは、Excel上の関数を活用していくうえで必須ともいえるほど重要です。
これを機に、Excelでいろんな関数を使いこなしてみましょう!