スプレッドシートの絶対参照($)のショートカットキー設定！オートフィル・コピペで参照セルが変化しない
Googleのスプレッドシートには計算式でセルを参照した際に、相対参照ではなく$(ドルマーク)付きの絶対参照にすると、コピペやオートフィルしても参照先が変化しません。
今回、スプレッドシートの絶対参照の機能と、セル参照を絶対参照にする手順(ショートカットキー)を解説します。
スプレッドシートの通常のセル参照は相対参照
スプレッドシートでセルに数式や関数を入力する際に、数式・関数の引数にセルを指定して結果を出力させることがあります。
このようにスプレッドシートの数式や関数でセルを引数にすることを「セル参照」と言います。
セルに「=」を入力し、数式入力モード中にセルをクリックして選択した場合、セル参照は「相対参照」という状態です。
セルの相対参照はコピペやオートフィルを使うと、次の行や列で参照先のセルがそれぞれの位置に連動して変化します。
セルの相対参照だと、オートフィルで不便なケースも
コピペやオートフィルで自動的にセル位置に応じて参照するセル位置を変更してくれるセルの相対参照ですが、不便なケースがあります。
例えば、下の図のような原価と予め定めた利益率から販売価格を算出するスプレッドシートの表があったとします。
原価から50%の利益が出るように販売価格を算出するため、各商品の原価にC1セルの利益率を掛け算して算出する仕組みです。
1つ目の商品の場合、販売価格を求める計算式は以下のような数式となります。
=B3+B3*C1
原価の1000円に50%の利益率を乗せるため、1000+1000×0.5=1500という計算結果です。
今回の数式では、いずれも相対参照によって計算する形となっています。
このとき他の商品の販売価格も算出しようと、そのままオートフィルを実行するとおかしな数値になります。
相対参照の場合、オートフィルした行(列)に応じて、セル参照の位置が連動して変化します。
その結果、C4セルの数式ではC1セルではなく、「販売価格」が入っているC2セルが参照され、「#VALUE!」のエラーが表示されています。
コピペやオートフィルでも連動しない絶対参照
上記のようにオートフィルやコピペを行なっても、セルの位置が連動して変更されないよう１箇所を参照したままにできるのが求められます。
そんなときに用いるのがセルの「絶対参照」です。
絶対参照は、セル参照のアルファベットと数字の前にそれぞれ$(ドルマーク)をつけることで表現します。
=B3+B3*$C$1
セルの絶対参照では、オートフィルやコピペをしても、参照するセルの位置が変わりません。
相対参照の場合はエラーが生じましたが、絶対参照だと参照セルの連動変更が起こらずに、C1セルの利益率を参照し続けてくれます。
セルの絶対参照は全部で3種類
スプレッドシートのセルの絶対参照は、前述したものを含め、全部で3種類あります。
- $(列記号)$(行番号)：行・列ともに変動しない
- (列記号)$(行番号)：列方向のみ連動
- $(列記号)(行番号)：行方向のみ連動
１つ目が行方向にも列方向にもセル参照が連動しない、完全な絶対参照です。
2つ目は行方向には連動しないものの、列方向にはセル参照が連動します。
3つ目は逆で列方向には連動しないものの、行方向にはセル参照が連動します。
スプレッドシートの表構造に応じて、これらの絶対参照を組み合わせることで、オートフィルやコピペ時に想定した動きを実現できます。
スプレッドシートの相対参照を絶対参照に切り替えるショートカット手順
スプレッドシートの相対参照から絶対参照に切り替えるには、数式内のセル相対参照先に「$(ドルマーク)」を付与します。
ただ、いちいち$を手打ちして絶対参照に変更するのは面倒ですし、手間もかかります。
実はセルの相対参照を絶対参照に変更するショートカットキーが用意されています。
絶対参照に切り替えるショートカットキーは「F4キー」です。
F4キーを押すと、「C1」の相対参照の場合、「$C$1」に切り替わります。
なお、F4キーを押す回数に応じて、絶対参照の種類も変更することが可能です。
- 1回目：$(列記号)$(行番号)【例. $C$1】
- 2回目：(列記号)$(行番号)【例. C$1】
- 3回目：$(列記号)(行番号)【例. $C1】
- 4回目：相対参照に戻る
F4キーを4回押すと、元の相対参照のセル参照に戻ってきます。
F4キーのショートカットキーで相対参照と絶対参照を簡単に切り替えることができます。
まとめ・終わりに
今回、スプレッドシートでオートフィルやコピペで参照セルが連動しない絶対参照と相対参照を絶対参照に切り替えるショートカットキーを紹介しました。
通常のセル参照は相対参照のため、コピペやオートフィルを行うと、セル位置に連動して参照先が変化します。
ただ、定数などが存在するスプレッドシートの表によっては、オートフィルで参照先が変わると、うまく計算ができない場合もあります。
そうした場合には、$マークを付けて絶対参照に切り替えることで、オートフィルでも参照先の連動が発生しません。
また、F4キーのショートカットキーで相対参照から絶対参照に切り替えられるようになっています。
ぜひスプレッドシートのショートカットキーを覚えて絶対参照を使いこなしましょう。