こちらの記事は次のようなことを知りたいという方に向けて書いています。
- Excelの数式について知っておいたほうがいいテクニックがあったら教えて欲しい！
- Excelの絶対参照、相対参照ってどういう意味？
- Excelの絶対参照、相対参照をいつも間違える・・どうやって覚えたらいい？
楽天ポイント集計用Excelとは？
楽天ポイント集計用Excelとは、楽天ポイント集計をするために私が作成したExcel集計表のことです。それぞれの項目にデータを入れていくと、獲得できる楽天ポイントがわかるという、個人的には利用頻度のかなり高いツールです。
楽天ポイントの概要ならびに集計用Excelのダウンロード、使用方法の説明についてはこちらの記事に記載しておりますので、よろしければご参照ください。
ちなみに、今回の記事は下記の記事の続きになっています。下記の記事はExcel集計表を効率的に作成するために知っておいたほうがいいことや考えておくべきことを解説しています。
私は、少し複雑なExcel集計表を作成する際に、後から変更になることを考えて
- 要件定義・・・Excelで実現したい内容を羅列して、共通する部分をまとめる
- 実装・・・共通する部分を中心にExcelの数式を組んで表を作成していく
- テスト・・・仮のデータを入れてみながら結果を確認する
という流れで進めていくことを推奨していますが、今回の記事では
「実装・・・共通する部分を中心にExcelの数式を組んで表を作成していく」の部分に特化して説明していきます。何回かに分けて解説していきますが、今回はExcelの数式で必須となる絶対参照・相対参照についての解説です。
絶対参照と相対参照を使いこなすための知識
絶対参照と相対参照とは？
Excel集計表で必ず覚えておいた方がいい内容の１つが、絶対参照と相対参照です。この２つを簡単に言い表せば、
- 絶対参照・・・Excelの数式をコピーしたとき、コピー先の場所に合わせて参照先が変化しない
- 相対参照・・・Excelの数式をコピーしたとき、コピー先の場所に合わせて参照先が変化する
ということになります。絶対参照、相対参照を設定するには＄の記号を使用します。行および列ごとにそれぞれ設定することができるため、行が２パターン、列が２パターンで４パターンの設定が可能です。
絶対参照と相対参照の覚え方①～右と下で分けてみる～
数式が絶対参照になるか相対参照になるかは、どちらに＄を置くかで決まるのですが、それをどちらに置くかを迷うことが多いです。もちろん行も列も絶対参照、または行も列も相対参照、ということであれば迷うことはないと思います。
ですが、行だけ相対参照、列だけ相対参照といった場合、どちらに＄を置けばいいの？と迷うことがあるのではないでしょうか。
そのために絶対参照、相対参照の覚え方をまとめてみましたのでご紹介します。A１セルを参照する数式で考えます。
数式をコピーする場合に、右と下のどちらにコピーしていくときに変わってほしいか、というのを覚え方のポイントにしています。
- →（右）にコピーするときに変わってほしい⇨列名（A、B、C・・）表示は右側⇨＄をアルファベット（A）の右側に置く
- ↓（下）にコピーするときに変わってほしい⇨行名（１，２，３・・）表示は左側⇨＄をアルファベット（A）の左側に置く
と覚えれば、間違えることはほぼ無くなると思います。
絶対参照と相対参照の覚え方②～F4キーで体で覚える～
Excelを使用するとき、絶対参照と相対参照の操作にはF4キーを使用するのが便利です。F4キーを押す回数で行と列の絶対参照、相対参照を切り替えるのですが、その回数と絶対・相対参照のどちらかを体で覚えてしまうと便利です。
回数と、絶対・相対参照の関係は下記のとおりとなります。
F４キーの回数で
１回は全部、２回は右、３回は下、４回で戻る
という感覚を覚えてしまうと、間違えることが少なくなります。
- →（右）にコピーするときに変わってほしい⇨２回は右なのでF4を２回
- ↓（下）にコピーするときに変わってほしい⇨３回は下なのでF4を３回
と言う感じですね。
相対参照と絶対参照のExcelでの使用例
さて、ここからは絶対参照と相対参照のExcelでの使用例を解説します。前述の楽天ポイント集計用Excelを用いて説明をしていきます。
（行）絶対参照×（列）絶対参照
まずは行も列も絶対参照のパターンです。パーセンテージの計算のような、分母を固定して、分子だけ変えていくような場合や、為替レートを設定して、表内のすべてに為替レートを掛け算するような場合によく使いますね。
楽天ポイント集計Excelでは、楽天会員種別と楽天モバイルによる条件を、別シートのマスタにリンクする際に、右にコピーしても下にコピーしても同じマスタを参照できるように行も列も絶対参照の数式を入れています。
- 右にコピーしても、参照元のマスタが右にずれないため、同じマスタを参照できる
- 下にコピーしても、参照元のマスタが下にずれないため、同じマスタを参照できる
ということで、右へのコピーも下へのコピーも、どちらも参照元がずれないため同じマスタを使用し続けることが可能です。
（行）絶対参照×（列）相対参照
次に行は絶対参照、列は相対参照のパターンです。列⇨列名は右側だから、右側に＄のあるパターンですね。
このパターンは、楽天ポイント集計用Excelでは楽天会員種別により決定される、SPUのポイント計算に使用しています。
商品の購入価格に倍率やポイント上限を加味した計算をおこない、それを右と下にコピーしていく形になります。まとめると、
- 右にコピーしたら、参照元のSPUの列を右にずらしていくため、各SPUを計算できる
- 下にコピーしても参照元が下にずれないため、同じセルを参照できる
ということになりますね。
（行）相対参照✕（列）絶対参照
続いて、行は相対参照、列は絶対参照という、先ほどの例とは逆のパターンです。
楽天ポイント集計用Excelでは、各商品の購入日付をお買い物マラソンやキャンペーンの開始月日、終了月日の範囲内に入っているかの判定に使用しています。
対象の年月日に購入月日が含まれているかの判定をおこない、それを右と下にコピーしていく形になります。まとめると、
- 右にコピーしても参照元が右にずれないため、購入月日を参照SPUの列を右にずらしていくため、各SPUを計算できる
- 下にコピーすると参照元の購入年月を下にずらしていくため、各購入商品の購入年月を参照していくことができる
ということになります。
（行）相対参照✕（列）相対参照
最後に、行も列も相対参照の例です。これはExcel数式のデフォルトでの参照となりますので、何もしなければこうなるため、多くの説明はいらないと思います。
楽天ポイント集計用Excelでは、店舗や商品で個別に加算されるポイント集計で使用しています。店舗や商品で個別に加算されるポイントは、マスターを参照するために行か列を動かさない、といった処理を必要としませんので、行も列も相対参照になります。
まとめ
以上、楽天ポイント集計用Excelを例にとって、Excelで重要な絶対参照、相対参照の覚え方を説明してきましたが、いかがでしたでしょうか？
- 絶対参照はExcelの数式をコピーしたとき、コピー先の場所に合わせて参照先が変化しない
- 相対参照はExcelの数式をコピーしたとき、コピー先の場所に合わせて参照先が変化する
- 絶対参照・相対参照の覚え方①：右と下で分けてみる。右にコピーするときにずらしたければ右に＄、下にコピーするときにずらしたければ左に＄を入れる
- 絶対参照・相対参照の覚え方②：F４キーを押す回数で覚える。右にコピーするときは２回、下にコピーするときは３回F４キーを押す
絶対参照と相対参照は覚えるのが難しいかもしれませんが、一度体に覚え込ませてしまえば、サクサクと数式を作成していけると思います。今回ご紹介した覚え方を参照してもらえると嬉しいです。
以上、お読みいただきましてありがとうございました。
