Excel・VBA

ITストラテジストが解説するExcel集計表作成の進め方② 絶対参照と相対参照を使いこなすための覚え方を伝授!

こちらの記事は次のようなことを知りたいという方に向けて書いています。

  • Excelの数式について知っておいたほうがいいテクニックがあったら教えて欲しい!
  • Excelの絶対参照、相対参照ってどういう意味?
  • Excelの絶対参照、相対参照をいつも間違える・・どうやって覚えたらいい?
まっすーです。中小企業診断士試験他多くの資格に合格した実績をベースに数々の資格試験の合格アドバイスをしています。今回は私が作成した楽天ポイント集計用Excelを使って、Excelで数式を作る際に知っておいたほうがいい絶対参照・相対参照の内容と、覚え方のコツを解説しますね。

楽天ポイント集計用Excelとは?

楽天ポイント集計用Excelとは、楽天ポイント集計をするために私が作成したExcel集計表のことです。それぞれの項目にデータを入れていくと、獲得できる楽天ポイントがわかるという、個人的には利用頻度のかなり高いツールです。

楽天ポイントの概要ならびに集計用Excelのダウンロード、使用方法の説明についてはこちらの記事に記載しておりますので、よろしければご参照ください。

https://massu-keiei.com/rakuten_point_excel/

ちなみに、今回の記事は下記の記事の続きになっています。下記の記事はExcel集計表を効率的に作成するために知っておいたほうがいいことや考えておくべきことを解説しています。

ITストラテジストが解説するExcel集計表作成の進め方① Excel表の効率的な作成方法を解説!この記事では複雑な計算が必要なExcelを作るのは大変だけど、どうやって作っていけばいいの?Excelで表を作るときに考えておいたほうがいいことって何?Excelで集計表を効率的に作る方法を教えて!といった疑問に回答しています。...

私は、少し複雑なExcel集計表を作成する際に、後から変更になることを考えて

  1. 要件定義・・・Excelで実現したい内容を羅列して、共通する部分をまとめる
  2. 実装・・・共通する部分を中心にExcelの数式を組んで表を作成していく
  3. テスト・・・仮のデータを入れてみながら結果を確認する

という流れで進めていくことを推奨していますが、今回の記事では

「実装・・・共通する部分を中心にExcelの数式を組んで表を作成していく」の部分に特化して説明していきます。何回かに分けて解説していきますが、今回はExcelの数式で必須となる絶対参照・相対参照についての解説です。

絶対参照と相対参照を使いこなすための知識

絶対参照と相対参照とは?

Excel集計表で必ず覚えておいた方がいい内容の1つが、絶対参照と相対参照です。この2つを簡単に言い表せば、

  • 絶対参照・・・Excelの数式をコピーしたとき、コピー先の場所に合わせて参照先が変化しない
  • 相対参照・・・Excelの数式をコピーしたとき、コピー先の場所に合わせて参照先が変化する

ということになります。絶対参照、相対参照を設定するには$の記号を使用します。行および列ごとにそれぞれ設定することができるため、行が2パターン、列が2パターンで4パターンの設定が可能です。

絶対参照と相対参照の覚え方①~右と下で分けてみる~

数式が絶対参照になるか相対参照になるかは、どちらに$を置くかで決まるのですが、それをどちらに置くかを迷うことが多いです。もちろん行も列も絶対参照、または行も列も相対参照、ということであれば迷うことはないと思います。

ですが、行だけ相対参照、列だけ相対参照といった場合、どちらに$を置けばいいの?と迷うことがあるのではないでしょうか。

そのために絶対参照、相対参照の覚え方をまとめてみましたのでご紹介します。A1セルを参照する数式で考えます。

数式をコピーする場合に、右と下のどちらにコピーしていくときに変わってほしいか、というのを覚え方のポイントにしています。

  • →(右)にコピーするときに変わってほしい⇨列名(A、B、C・・)表示は右側⇨$をアルファベット(A)の右側に置く
  • ↓(下)にコピーするときに変わってほしい⇨行名(1,2,3・・)表示は左側⇨$をアルファベット(A)の左側に置く

と覚えれば、間違えることはほぼ無くなると思います。

絶対参照と相対参照の覚え方②~F4キーで体で覚える~

Excelを使用するとき、絶対参照と相対参照の操作にはF4キーを使用するのが便利です。F4キーを押す回数で行と列の絶対参照、相対参照を切り替えるのですが、その回数と絶対・相対参照のどちらかを体で覚えてしまうと便利です。

回数と、絶対・相対参照の関係は下記のとおりとなります。

F4キーの回数で

1回は全部、2回は右、3回は下、4回で戻る

という感覚を覚えてしまうと、間違えることが少なくなります。

  • →(右)にコピーするときに変わってほしい⇨2回は右なのでF4を2回
  • ↓(下)にコピーするときに変わってほしい⇨3回は下なのでF4を3回

と言う感じですね。

相対参照と絶対参照のExcelでの使用例

さて、ここからは絶対参照と相対参照のExcelでの使用例を解説します。前述の楽天ポイント集計用Excelを用いて説明をしていきます。

(行)絶対参照×(列)絶対参照

まずは行も列も絶対参照のパターンです。パーセンテージの計算のような、分母を固定して、分子だけ変えていくような場合や、為替レートを設定して、表内のすべてに為替レートを掛け算するような場合によく使いますね。

楽天ポイント集計Excelでは、楽天会員種別と楽天モバイルによる条件を、別シートのマスタにリンクする際に、右にコピーしても下にコピーしても同じマスタを参照できるように行も列も絶対参照の数式を入れています。

  • 右にコピーしても、参照元のマスタが右にずれないため、同じマスタを参照できる
  • 下にコピーしても、参照元のマスタが下にずれないため、同じマスタを参照できる

ということで、右へのコピーも下へのコピーも、どちらも参照元がずれないため同じマスタを使用し続けることが可能です。

(行)絶対参照×(列)相対参照

次に行は絶対参照、列は相対参照のパターンです。列⇨列名は右側だから、右側に$のあるパターンですね。

このパターンは、楽天ポイント集計用Excelでは楽天会員種別により決定される、SPUのポイント計算に使用しています。

商品の購入価格に倍率やポイント上限を加味した計算をおこない、それを右と下にコピーしていく形になります。まとめると、

  • 右にコピーしたら、参照元のSPUの列を右にずらしていくため、各SPUを計算できる
  • 下にコピーしても参照元が下にずれないため、同じセルを参照できる

ということになりますね。

(行)相対参照✕(列)絶対参照

続いて、行は相対参照、列は絶対参照という、先ほどの例とは逆のパターンです。

楽天ポイント集計用Excelでは、各商品の購入日付をお買い物マラソンやキャンペーンの開始月日、終了月日の範囲内に入っているかの判定に使用しています。

対象の年月日に購入月日が含まれているかの判定をおこない、それを右と下にコピーしていく形になります。まとめると、

  • 右にコピーしても参照元が右にずれないため、購入月日を参照SPUの列を右にずらしていくため、各SPUを計算できる
  • 下にコピーすると参照元の購入年月を下にずらしていくため、各購入商品の購入年月を参照していくことができる

ということになります。

(行)相対参照✕(列)相対参照

最後に、行も列も相対参照の例です。これはExcel数式のデフォルトでの参照となりますので、何もしなければこうなるため、多くの説明はいらないと思います。

楽天ポイント集計用Excelでは、店舗や商品で個別に加算されるポイント集計で使用しています。店舗や商品で個別に加算されるポイントは、マスターを参照するために行か列を動かさない、といった処理を必要としませんので、行も列も相対参照になります。

まとめ

以上、楽天ポイント集計用Excelを例にとって、Excelで重要な絶対参照、相対参照の覚え方を説明してきましたが、いかがでしたでしょうか?

ITストラテジストが解説する絶対参照・相対参照
  1.  絶対参照はExcelの数式をコピーしたとき、コピー先の場所に合わせて参照先が変化しない
  2. 相対参照はExcelの数式をコピーしたとき、コピー先の場所に合わせて参照先が変化する
  3. 絶対参照・相対参照の覚え方①:右と下で分けてみる。右にコピーするときにずらしたければ右に$、下にコピーするときにずらしたければ左に$を入れる
  4. 絶対参照・相対参照の覚え方②:F4キーを押す回数で覚える。右にコピーするときは2回、下にコピーするときは3回F4キーを押す

絶対参照と相対参照は覚えるのが難しいかもしれませんが、一度体に覚え込ませてしまえば、サクサクと数式を作成していけると思います。今回ご紹介した覚え方を参照してもらえると嬉しいです。

以上、お読みいただきましてありがとうございました。

ABOUT ME
まっすー
中小企業診断士のまっすーです。 社会保険労務士やITストラテジストなど、多くの難関資格に合格した実績をベースとした資格試験の学習方法、ExcelマクロやPythonを活用した自動化の推進、経営に役立つ管理会計の理論解説、ITを活用した経営資源の有効活用などの情報を発信しています。

COMMENT

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

CAPTCHA