Excel

Excelで書式をコピーすると計算式のセルがずれる?参照するセルを固定する方法

Excelで書式をコピーすると計算式のセルがずれる?参照するセルを固定する方法

$を使う方法は?絶対参照とと相対参照の違い

$を使う方法は?絶対参照とと相対参照の違い

エクセルのセルの参照には絶対参照、相対参照、複合参照がありますが、どれもセルの指定をするときにを使うものです。

違いとしては

  • 絶対参照ではセル指定時に列と行に「$」を使う
  • 相対参照ではセル指定に「$」は使わない
  • 複合参照ではセル指定時に列か行のどちらかに「$」を使う

といった違いがあります。

セルの位置を表すものに「$」を使うことで、書式をコピーしたときにセルの位置を固定することができます。

そもそも「$」の使いかい方は?

そもそも「$」の使いかい方は?

セルの指定で「$」を使うといわれても、実例を見ないとピンとこないと思いますのでご紹介しましょう。

A1セルを例にすると

  • 絶対参照:「=$A$1」
  • 相対参照:「=A1」
  • 複合参照(列固定):「=$A1」
  • 複合参照(行固定):「=A$1」

となります。

エクセルでセルを固定しない相対参照の考え方

エクセルで初心者にとって難しい考え方に、セルの参照があります。

セルの値を参照して計算するときにの話ですが、エクセルで一覧表を作ったあとに決まった列に計算式を作り、コピーして表を完成させますよね。

計算式のあるセルをコピーしたときに、張り付けた先のセルも良い感じで計算式が埋め込まれています。

これが相対参照で計算式がコピーされたことになります。実際に例を使ってご紹介しますね。

書式のコピーを使って計算式を相対参照でコピーする方法

相対参照を使って商品の単価に数量を掛けて、金額を計算する表を作成します。

取引先が「A社」の行を見てみましょう。

書式のコピーを使って計算式を相対参照でコピーする方法

「C2:1500」と「D2:100」を掛けたものを「E2」に表示していますが、E2には「=C2*D2」の計算式が指定されています。

E2の計算式を、E3とE4にコピーするとどうなるか確認してみましょう。

コピーのやり方は色々ありますので、1例をご紹介します。

E2セルを選択して、マウスをE2セルの右下の角に移動すると「+」に切り替わります。

E2の計算式を、E3とE4にコピーするとどうなるか確認してみましょう。

左クリックを押したまま、E4セルまで引っ張ってから左クリックを話します。

E2の計算式を、E3とE4にコピーするとどうなるか確認してみましょう。

  • E3には「=C3*D3」
  • E4には「=C4*D4」

の計算式がコピーされました。

これは計算式をコピーしたときに、貼り付け先に合わせて計算で使うセルの場所を変更しているため意図した計算ができています。

これが相対参照によるコピーです。

エクセルでセルを固定するどういうこと?絶対参照の考え方

相対参照と違って絶対参照は書式をコピーしたときに、参照するセルの位置は変更されないことです。

一般的な表を作成するのであれば絶対参照する必要が分かりにくいので、実際に例を使ってご紹介しますね。

書式のコピーを使って計算式を相対参照でコピーしてしまった場合は?正しい結果が得られない

商品の単価に数量を掛けて、金額を計算したものに対して、割引を適用した表を作成します。

取引先が「A社」の行を見てみましょう。

書式のコピーを使って計算式を相対参照でコピーしてしまった場合は?正しい結果が得られない

表の金額に対して、割引率の10%を値引きしたものを割引後金額に表示します。

F4には「=E4*(1-B1)」の計算式を設定していますが、この計算式を取引先「B社」「C社」にコピーするとどうなるか確認してみましょう。

書式のコピーを使って計算式を相対参照でコピーしてしまった場合は?正しい結果が得られない

書式のコピーを使って計算式を相対参照でコピーしてしまった場合は?正しい結果が得られない

F5セルは誤った値が表示され、F6セルはエラーが表示されました。

実際にコピーされた計算式を確認してみると

  • F5には「=E5*(1-B2)」
  • F6には「=E6*(1-B3)」

が設定されています。

書式のコピーを使って計算式を相対参照でコピーしてしまった場合は?正しい結果が得られない

割引率の値は「B1」に設定されていますが、下にコピーするたびにB2やB3と下のセルを参照するように計算式がコピーされていました。

書式のコピーを使って計算式を絶対参照でコピーする方法

表以外の値を参照して計算したい場合に、相対参照ではなく絶対参照を使います。

つまり「$」を使った指定方法になりますね。

取引先が「A社」の行の割引後金額の計算式に、割引率のセル指定に「$」を使うとこうなります。

書式のコピーを使って計算式を絶対参照でコピーする方法

F4には「=E4*(1-$B$1)」の計算式を設定しましたが、割引後金額には正しい値が表示されていることが分かります。

この計算式を取引先「B社」「C社」にコピーするとどうなるか確認してみましょう。

書式のコピーを使って計算式を絶対参照でコピーする方法

正しい値が表示されることを確認しました。計算式は

  • F5には「=E5*(1-$B$1)」
  • F6には「=E6*(1-$B$1)」

となり、「$」を指定することで、B1セルが固定されることが分かりました。

複合参照ってなに?セル指定に$を1つ使った指定方法

複合参照は相対参照と絶対参照を組み合わせたもので、「$」を1つ使う方法になります。

列か行のどちらかに「$」を使うことで、書式をコピーしたときに「$」指定したものを固定することができます。

実際に見た方が分かりやすいので、例をご紹介しますね。

B2からC4に1~6の数字を入力した表を作成し、同じ形のものを3つ用意します。

複合参照ってなに?セル指定に$を1つ使った指定方法

左上のセルにB2セルの値をそのまま表示しますが、「$」を使います。

  • E2に「=B2」を設定
  • H2に「=$B2」(列を固定)を設定
  • K2に「=B$2」(行を固定)を設定

列と行を固定する複合参照を使うと、どのような書式がコピーされるか確認します。

複合参照ってなに?セル指定に$を1つ使った指定方法

複合参照ってなに?セル指定に$を1つ使った指定方法

  • H2のコピーは、B列を固定することで、C列にコピーしてもB列の値を表示
  • K2のコピーは、2行目を固定することで、3行目、4行目にコピーしても2行目の値を表示

エクセルで$を使った固定のまとめ

エクセルで$を使った固定のまとめ

今回は計算式の参照するセルに「$」を使うことで、コピーした書式を固定する方法をご紹介しました。

「$」を使う機会は少ないかも知れませんが、表以外の値を参照するときに必ず必要になるものです。

間違えてしまうと、計算が合わなくなる大問題が発生しかねませんので、しっかりと覚えておきましょう。

-Excel

© 2021 富山 パソコン教室「キュリオステーション掛尾店」