参照式の入ったセルをコピペ（コピーして他のセルへ貼り付け）したときに、エクセルが参照先のアドレスを勝手に変えてくれます。
この機能がエクセルの売りだと言っても過言ではないと思います。コピーするたびに式の参照先を編集で修正するなんてことは、現実的に無理ですからね。
優れた機能ですが、変わってほしくない（どの式からも必ずある一つのセルを参照させたい）という時にも参照先が勝手に変わってしまい、うまくいきません。
じゃあ、そういう時は、いちいち編集で訂正しなくちゃいけないのか？というとそうではなく、ちゃんと対応するための機能が用意されています。
その機能（対応方法）が今回説明する、 絶対参照と INDIRECT 関数の2つです。
絶対参照 で参照先を固定する
参照式の入ったセルを別のセルにコピペしたときに、エクセルが参照先のアドレスを勝手に変えてくれます。
たとえば、C3のセルに（＝B3*A1）という式が入っているときに、C3のセルを（一つ下の）C4へコピぺするとC4のセルの式は（＝B4*A2）となり、B3がB4に、A1がA2にそれぞれ一つ下のセルを参照した形に自動的に変更されます。
行の中での計算式（例えば、単価×数量など）を他の行も同じようにで計算したいときにはコピペすると自動で思うように変更されるのでとても便利なのですが、この例のように、共通で使う定数（消費税率）を参照しているときなど、参照先を絶対に変えたくないときには、気をつけなければいけません。
こういう風に、コピペで勝手に変わって欲しくないとき、参照式の書き方を 絶対参照 とすることで参照先を固定（コピペしても変わらないように）することが出来ます。
上の例では、A1に消費税率が入っていて、B3には価格が入っているときに、C3の式の消費税率の参照セル表記に 絶対参照 を使うと（＝B3*$A$1）となります。
この、ドルマーク（$）が絶対参照を表しています。
Aの前と、1の前どちらにも付いているときは、列と行のどちらも絶対参照（コピペで変わらない）の指定です。
このように絶対参照の指定をしておけば、次に、C3の式をC4にコピーしても参照先は変わらず式は（＝B4*$A$1）となり、思った通りの結果が得られます。
因みに、列だけを絶対参照にするには $A1 とします。 その場合は、右（または左）にコピペしたときは変わらず、下（または上）にコピペしたときに $A2 に変わります。
また、行だけを絶対参照にするには、A$1とします。 この場合は、右（または左）にコピペでB$1に変わりますが、下（または上）にコピペでは変わりません。
相対参照を絶対参照に変換するには、F4キーを使います。
=A1のA1のところにカーソルがある状態で、F4キーを押すと$A$1に一発変換されます。
その後、F4キーを押すとA$1（行絶対参照）に変換され、さらにF4キーを押すと$A1（列絶対参照）に変換され、もう一度押すと元に戻ります。
何度か押して、確認してみてください。
INDIRECT 関数を使って固定する
次は、絶対参照を使わずに、INDIRECT 関数を使う方法です。
INDIRECT 関数は文字列をセルアドレスに変換する関数です。
たとえば、A1を参照する式（＝A1）を INDIRECT 関数で書くと（＝ INDIRECT (”A1″)）となり、次のようになります。
C3セルをC4セルにコピーしたとき、（B3）は（B4）に入れ替わりますが、（ INDIRECT (”A1″))の部分は アドレスではなく文字列なので書き換わらずそのままです。
上の図では、C4セルを入力モードにしたときに、B4が青表示されB4セルが青枠で表示されていますが、A1セルに枠は出ません。
文字列の”A1″なのでEXCELはまだセル参照してないことが分かります。 関数が実行されて INDIRECT (“A1”)がアドレスのA1に変換されたときに初めて参照をすることになるのです。
それでは次に 絶対参照 と INDIRECT 関数の比較をしてみます。
INDIRECT まずは簡単な使い方から
Sheet1のA1に定数が入力されているとします。
例えば消費税率10％がA1に入力されているような場合です。
表の中で、税込み額の計算をするときに絶対参照を使うと、式は次のようになります。
=B2*$A$1
同様に、これを INDIRECT 関数で記述すると、式は次のようになります。
=B2*INDIRECT("A1")
どちらの参照式も、セルを下にコピーすると、B2の部分は行に合せてB3、B4と変化していきますが、絶対参照している部分は変化せず、うまく思惑通りの計算式を作ることが出来ます。
別シートのセルを参照
次に、Sheet2に同じ表を作って、先程のSheet1のA1（消費税率％）を参照してみます。
参照式はそれぞれ以下のようになります。
- 絶対参照の場合、
=B2*Sheet1!$A$1
- INDIRECT 関数の場合、
=B2* INDIRECT ("Sheet1!A1")
これも問題なく動作し、絶対参照でも INDIRECT 関数でも同じ結果が得られます。
参照先をドラッグして移動させてみる
次に、Sheet1のA1（消費税率）のセルの枠をマウスで掴みドラッグしてA2へ横に移動させてみます。
C3へ入力されていた、=B3*$A$1という式が、自動的に=B3*$A$2へ変更されました。
ここはエクセルが勝手にやってくれるんですよね。計算結果は変化なしです。
ところが、D3へ入力していた=B3*INDIRECT(“A1”)という式は、そのままで全く変らずです。エクセルは認識してないので当然です。
従って計算結果は全て0になってしまいます。
これは、別のシートからの参照でも結果は同じです。
エクセルの参照先管理機能はめっちゃ便利ですよね。
それに対してINDIRECT関数では参照が切れてしまい、訂正をしなくてはいけなくなってしまいます。
シートを別のブックにコピーしてみる
つぎは、このシート（sheet2）を別の新しいBookにコピーしてみます。
やり方は簡単。コピーしたいシート（sheet2）の下の方のシート名が表示しているタブで右クリックします。
続いて、表示されたダイアログで移動またはコピーを選択
移動先ブック名のプルダウンメニューから（新しいブック）を選んで、ダイアログの下の方にあるコピーを作成するにチェックしてからOKを押します。
新しいBookが作成されて、シートSheet2がコピーされました。
そして、A1セルの参照式（絶対参照）を確認すると、＝[Book1]Sheet1!A1になっているのが分かります。
絶対参照の結果は正しく表示されます。
また、B1セルの参照式（INDIRECT関数）を確認すると、＝INDIRECT（”Sheet1!A1“）のままであって、参照結果はエラー表示（#REF！）となります。
そして、新しいシートを作成してシート名をSheet1に変更して、A1セルに”Sheet2A1”と入力してみます。
今まで＃REF!であったセルは、Sheet2A1と表示されました。
INDIRECT 関数まとめ
今回は絶対参照と INDIRECT 関数の簡単な比較をしてみました。
それぞれメリットとデメリットがあり、使う場面によって使い分けるというのがお勧めです。
INDIRECT 関数の記事は、こちらにもあります。
MS-EXCEL 今年のデータを、昨年作成した SUMIFS 関数集計表を 流用 して使う方法。INDIRECT 関数の効果的な使い方
最後まで読んでいただき、有り難うございました。
コメント