エクセルでコピペなどをすると計算式の中で参照されていたセルがずれてしまうことがあります。それを回避する方法が幾つかありますが、今回は絶対参照についてご説明します。
計算式のコピペでエラーになるパターン
たとえば、次のようなパターンで考えてみましょう。購買履歴のようなリストです。品目ごとに単価や数量の項目があります。そうして、消費税率に関してはセルG2を参照して税込金額を算出しています。
さて、この状態でセルE3をセルE4にコピペするとどうなるでしょうか。消費税率としてセルG3を参照しています。これでは、正しく税込金額を計算することができません。では、この場合は、どのような計算式にすれば良いのでしょうか。$を使った絶対参照をする方法と関数を使う方法があります。
コピペしても同じセルを参照する方法－$を使った絶対参照
絶対参照という方法を用いることで同じセルを参照することができます。下図をご覧ください。上の例では、「G2」となっていた箇所が「$G$2」という表記になっています。このように「$」を付けることでどのような変化があるのでしょうか。セルE3をセルE4にコピーしてみましょう。
そうすると今回は、正しくセルG2の消費税率を用いて税込金額が計算できていることが分かります。
では、次に参照先であるセルG2をセルH2にコピーしたらどうなるでしょうか。セルE4の計算式に変化はないようです。
では、セルG2をセルH2にカット(切り取り)してペーストしたらどうなるでしょうか。セルE4の計算式に変化がみられることが分かります。貼り付けた先のセルH2を参照しているので引き続き正しく税込計算をすることができています。
まとめてみますと「$」を使った絶対参照を活用することにより、常に同じセルを参照することができます。ただし、参照先のセルとが切り取られた場合は、そのペースト先のセルを新たに参照するようになります。なかなか良くできていますね。
絶対参照の設定を簡単に行なう方法
「$」を使った絶対参照ですが、これを手で入力するのは面倒ですし、ミスの原因にもなります。そこで簡単に絶対参照の設定をする方法をご紹介します。
手順は以下の通りです。
- 絶対参照の設定をしたいセル(下図の場合、セルE4)にカーソルを持っていきます。
- F2キーを押して、編集モードにします。
- 絶対参照にしたい箇所（下図の場合は、H2の部分）に、カーソル持っていきます。
- F4キーを押します。
こうしますと比較的に簡単に入力ミスもなく、絶対参照の設定をすることができます。
コピペしても同じセルを参照する方法－indirect関数を使う方法
indirect関数を使って絶対参照をする方法もあります。indirect関数はとても便利な関数なのでさまざまな使い方があるのですが、ここではこの関数を使って絶対参照をする方法をご紹介します。さて、この状態でセルE3をセルE4にコピペするとどうなるでしょうか。
セルE3をセルE4にコピペしたのが下図の状態です。引き続き、セルG2の消費税率を用いて税込金額を算出しています。ここまでは、「$」を使った絶対参照と同じ動きです。
では、セルG2の消費税率を切り取って、セルH2に貼り付けるとどうなるでしょうか。セルE4の計算式を見てみますと引き続きセルG2を参照していることが分かります。そのため、税込金額を正しく計算することができていません。参照先のセルをカット＆ペーストした場合は、「$」を使った絶対参照とindirect関数とで動きが異なっているようです。
まとめ
「$」を使った絶対参照は、参照先のセルがカット＆ペーストされたり、行削除されると参照先が書き換えられることあります。
一方、indirect関数は、参照先のセルがカット＆ペーストされたり、行削除されても常に同じセルを参照しつづけようとします。
微妙に動きがことなりますので、用途に応じて使い分けることができるでしょう。では、今回はここまでです。