Excelで参照セルを固定する方法【絶対参照】

Excelの特性として「相対参照」があります。

Excelはセル番地や中に入っているデータで数式を管理しているのではなく、数式を入れたそのセルから「一つ上のセル」、「一つ左のセル」といった、距離・配置で覚えている。というものでした。

特定の同じセルを参照しておきたい場合には、セル参照が変更されてしまうと正しい結果が得られません。

その際に、セル参照の形式を絶対参照にしておくと、数式をコピーしてもセル参照が変更されないようになります。

絶対参照を覚えておかないと、数式を一つずつ作成するといったことになってしまいます。

下記では、相対参照での失敗例も入れながら解説していきます。

 

 

目次

  1. 相対参照の失敗例
  2. 絶対参照とは?
  3. 絶対参照の設定方法

 

1. 相対参照の失敗例

計算式をコピーしたとき(オートフィル)、ちゃんと計算されずにおかしな具合になったことありませんか?

計算式はちゃんと入れているのに、エラー表示が出たり・・

絶対参照①

 

これは、セル参照が変更される相対参照でされているからです。なので、数式をコピーしたら、エラーになってしまいます。

F10セルの「VALUE!」は、このケースでは数式を確立する上で必要なセル参照が数値では無く文字列(G6セルの「税込金額」)になっている為、正しい計算結果が算出されないことを示しています。

 

2. 絶対参照とは

相対参照が「参照元のデータ位置をずらしたい」時に利用するのに対し、絶対参照は「参照元のデータ位置を固定したい」に利用します。

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

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

この「$」ドル記号は、言ってみれば鍵のようなものです。

鍵をかけることで「書き換え禁止」(セルをずらさない)にするのです。

鍵を開けたままの状態が「相対参照」鍵を閉めた状態が「絶対参照」です。

この「鍵」で開け閉めするというイメージを持っておくと理解しやすいと思います。

 

3. 絶対参照の設定方法

 

では早速絶対参照の設定をしていきたいのですが、まずは以下のような表があります。

 

絶対参照③

 

① 金額の計算をします。

E7セルをクリックし、「=C7*D7」と入力します。E11セルまでオートフィルで数式をコピーしましょう。

絶対参照④

 

②きちんと計算式がコピーされました。

絶対参照⑤

 

今回の数式には、「鍵」は掛けていません。なので相対参照です。

③ では、F7セルをクリックし、「=E7*G3」と入力しましょう。

絶対参照⑥

 

各商品金額の消費税を計算するため「F7」セルに「=E7*G3」と入力しました。E7セルにはA4コピー用紙の価格の数値、G3セルには消費税8%の数値が入っています。どちらのセルにもまだ鍵がかかってないので、相対参照ですね。

 

④セル11までオートフィルで数式をコピーしましょう。

絶対参照⑦

 

計算結果がおかしな具合になりました。

 

絶対参照①

 

⑤ では、G3セルにかぎを掛けましょう。F7セルをダブルクリックすると、計算式が表示されます。Gの前にカーソルを置きF4キーを1回押します。Enterキーで確定しましょう。

絶対参照⑧

 

F4キー

 

※「F4」を押す方法以外に、直接「$」を入力して頂いても大丈夫です。

参照セル「B1」をクリックした後、F4キーを押していくと$B$1」「B$1」「$B1」「B1と表記が変わりますが、

今回は、この消費税8%は絶対にこの場所にしたいので、2か所に$記号をつけ、カギを完全につけてしまいます。

F4キー切り替わり

 

⑥ 計算結果が出ました。F11までオートフィルで数式をコピーしましょう。

絶対参照⑨

 

⑦価格に応じた消費税額が表示されましたね。

絶対参照10

数式表示にしてみると、絶対参照にしたセルは最後まで可変無しの同一コピーなのが確認できます。

絶対参照11

 

この状況を説明すると、消費税の参照セル「G3」が「相対参照」なので行番号が1つずつズレ、割引率や、空白、文字列と掛け算をしてしまった・・・ということなのです。

絶対参照12

 

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

なので、消費税を変化させたくない今回は、G3セルにかぎをかけ($記号をつけ)、金額は移動しながらも消費税は移動させないようにしたわけです。

では、請求書を完成させましょう。

 

⑧ G7セルを選択し、「=E7+F7」と入力、Enterで確定します。オートフィルで数式をコピーしましょう。

絶対参照13

 

⑨ G20セルを選択し、ホームタブ、編集グループの「ΣオートSUM」の画をクリックします。

絶対参照14

「=SUM(G7:G19)」と空白の部分まで選択されていますが、もし、空白部分にも数値が入ると自動的に合計額を計算してくれます。

 

さあ、では次に割引額を求めましょう。小計に割引率を掛けます。鍵をかける($記号をつける)必要があるでしょうか?

 

⑩ G21を選択し、「=G20*G4」と入力します。割引率は消費税と同じく動いては困るものです。ということは、鍵をかける=絶対参照にする必要がありますね。G4を入力したらそのままF4キーを押してください。Enterで確定しましょう。

絶対参照15

 

もし計算結果が小数点がついていたら、「小数点以下を減らす」を小数点以下の分クリックすればOK!

小数点以下を減らす

 

最後です。割引後金額を求めましょう。

⑪ G22セルを選択し、「=G21-G20」と入力、Enterで確定しましょう。

絶対参照16

 

まとめ

  • 相対参照:数式をコピーしたとき、コピー先でそこのセル範囲に合わせて行番号と列番号が変化する参照方法
  • 絶対参照:行・列ともに固定させる参照方法。
  • 行番号、列番号を固定させるには、セル番地の行番号、列番号の前に「$」(ドル記号)を付けます。
  • 「$」(ドル記号)を付けることは、動かないように鍵をつけるイメージです。
  • 「$」は、手入力することも出来ますが、セル番地を入力状態にして「F4」キーを押すと簡単に入力出来ます。
  • 「F4」キーを押すごとに、 $A$1→A$1→$A1→A1の順で変化します。

この記事が参考になった方は
いいね!して下さい

MOS対策テキスト【オススメ】

初心者向けExcelマクロ講座

学習サイト