Excel VBAで絶対参照⇔相対参照を自動切り替え|マクロで手間をゼロに！

Excelで数式をコピーしたとき、「思っていたセルが参照してくれない」、「なぜか計算結果がずれてしまう」、こんなことありませんか？

その原因、多くは「絶対参照」・「相対参照」の違いです。セルに「$A$1」といった$マークを見たことがある人も多いと思います。この「$マーク」で絶対参照・相対参照を切り替えることができ、うまく使い分けることが、Excel作業を一気に楽にする鍵となります。

この記事では、「そもそも絶対参照・相対参照って何？」という疑問から、参照を簡単に切り替えることができるVBAの作り方まで、幅広く解説していきます。

「なんとなく使ってたけど、あまり詳しくは知らなかったな〜」や、「手入力は面倒くさいから、もっと効率的に絶対・相対参照を切り替えたい」と思われた方、ぜひこの記事を読んで、Excelスキルをアップさせていきましょう！

★最終的には、以下のような参照変換ツールが作れるようになります。

🔍今回の記事で分かること

絶対参照・相対参照の違い
絶対参照・相対参照の切り替え方
絶対参照・相対参照を一気に反映するVBAの作り方

INDEX

Excelの絶対参照・相対参照とは？
絶対参照・相対参照を使い分ける理由/具体的なケース
1. 料金一覧表
絶対参照⇔相対参照を一括で切り替えるメリットは？
ExcelVBAで絶対参照・相対参照を自動変換するコード一覧
（応用）複数シートを一気に変更する場合！
まとめ：参照の切り替えもVBAで効率を爆上げしよう！

Excelの絶対参照・相対参照とは？

エクセルには「絶対参照」、「相対参照」とそれを組み合わせた「複合参照」という参照方法があります。

・絶対参照: セルをコピー・移動しても参照先が変わらない。
→絶対参照は、セル参照の前に「$」マークが付きます。
例えば「=$B$2」となっているセルを一つ右にずらしてコピーしても、「=$B$2」と参照先が固定されます。

・相対参照: セルをコピー・移動したときに、参照先のセルが変わる。
→例えば「=B2」となっているセルを一つ右にずらしてコピーすると、「=C2」と参照先がずれます。

・複合参照: 行・列別々に絶対参照・相対参照を使用する。
→例えば「=$B2」とあるセルは、列のみB列を固定で参照するということになります。
「=$B2」一つ右（列方向）にコピーしても、「=$B2」のまま固定されますが、一つ下（行方向）にコピーすると「=$B3」となり、参照先がずれます。

表にまとめると、以下のようになります。

参照方法	説明	参照例（セルA1の場合）	行・列ともに1つずつずらしたセルにコピーした結果
絶対参照	行・列どちらも固定	=$A$1	=$A$1
相対参照	行・列どちらも固定しない	=A1	=B2
複合参照	行・列いずれかを固定	行固定：=A$1 列固定：=$A1	行固定：=B$1 列固定：=$A2

「$マーク」は、セルの数式部分を選択しながら「F4」キーを押すことで表示させることができます。

ちなみに「F4」キーを押すと、
・①絶対参照　→　②複合参照（行）　→　③複合参照（列）　→　④相対参照
の順番で切り替えることができます。

絶対参照・相対参照を使い分ける理由/具体的なケース

ここまでは、絶対参照・相対参照の説明と設定の仕方を紹介しました。では、この「絶対参照・相対参照を使い分ける理由って何？」と疑問を持たれた方もいると思います。私自身Excelを習いたてのころ、この違いにあまりピンときていませんでした。

しかし、この参照を使い分けることは、Excelをより効率的に活用するための大きな意味があります。イメージしやすいように、具体的なケースを用いて解説します。

料金一覧表

例えばある施設で、一人あたり/1時間で100円の利用料金を設定をしていたとします。これをもとに、料金一覧表を作りたいと考えた場合、「単価×人数×時間」という計算をそれぞれ行う必要があります。

この場合、表としては以下のようなイメージになると思います。例えばこの表のセルC3にに計算式を入力する場合、以下のようになります。

=B1*B3*C2

では、相対参照のまま計算式をコピーしてみるとどうでしょう。以下のように参照先がずれてしまって、うまく計算することができません。

これでは、いちいち参照先を手入力で直す必要があり、とても非効率ですよね。

では、どうしたら一発で上手くいくのか。そこで活躍するのが、絶対参照となります。「単価×人数×時間」が表どおりに計算されるために、以下のように参照方法を組み合わせます。

例）セルC3に入力する場合
・単価→セルB1を絶対参照（=$B$1）
・時間→B列を絶対参照（=$B3）
・人数→3行目を絶対参照（=C$3）

結果、セルC3には、以下の計算式を入力することになります。

=$B$1*$B3*C$2

では、この計算式をコピーしてみるとどうでしょう。
結果としては、表のとおりに計算することができました。

上記一覧表のように、特定の行・列、またはセルを参照して計算したいときに、絶対参照が役に立ちます。

絶対参照⇔相対参照を一括で切り替えるメリットは？

ここまでの説明で、絶対参照を使うメリットは理解いただけたと思います。

ただ、参照方法を切り替えるには、セルの数式を選択した状態で、「F4」キーを押す必要があります。つまり、セル1つずつ作業しなければならないということですね。
参照を切り替える範囲が小さければいいですが、作業する範囲が大きくなると、とてもじゃないけど手入力では対応しきれないですよね。

そのため、絶対参照⇔相対参照をVBAで一括で切り替えできると、多くのメリットを享受できます。
私もツールとしてよく活用しているので、実際に感じているメリットを紹介します。

作業スピードを大幅にアップすることができる！

まずはこれ。業務効率が格段に上がることです。
「F4」キーは一つずつ参照方法を切り替えることができますが、大量のセル範囲を切り替えることはできません。F4キーでの手入力では、とてつもない時間がかかってしまいますね。

参照方法を一括で切り替えることで、大量のセル範囲参照を一気に変えることができます。これで作業時間を大幅に削減することができます。

ミスの削減でストレス軽減！

次にあげるのが、ミスが削減できることです。
手作業だと、どうしてもヒューマンエラーが発生してしまう可能性がありますよね。
一つずつ作業しないといけないため、セルによっては切替作業が漏れてしまうなど、どうしてもミスが出てきてしまいます。
とても非効率なうえに、ミスも発生しやすい原因となってしまいます。

参照方法を一括で切り替えることで、指定した範囲は全て参照切替をすることができます。
そのため、切替ミスの発生を限りなく減らすことができます。作業効率を大幅にアップすることができます。

一括で変更できるため、数式の管理がラク！

3つ目に上げるのが、数式の管理が簡単になることです。
手作業で参照切替をすると、「あれ、今どこまでやったっけ？」と間違いなくなると思います。
そうなったら、一つずつできているかを確認する羽目になってしまい、とても効率が悪いですよね。。

一括で参照変更できると、一部参照切替ができていなかったセルも調整できるため、ファイルのメンテナンスが簡単にできます。
つまりセルを一つずつ確認する必要もないため、効率的な作業が可能となります。

以上が、私が実際に使ってみて感じたメリットとなります。
とはいえ、手入力で対応するほうが早いというケースもありますので、目安となる比較表を作成してみました。

状況に応じて使い分けることがよいと思いますので、比較表をチェックしてみて活用してみてください。

▼参照形式の切り替え方法を比較！手動 or 自動、あなたはどっち派？

方法	メリット	デメリット	向いている場面
手動（F4キー）	・操作が簡単・すぐ使える	・大量データだと大変・見落としがち	・少数の数式変更・個別対応したいとき
VBAによる一括変換	・大量の式を一括処理・ミスが減る	・コードが必要・初心者には少しハードルが高い	定型レポートや大量数式のあるファイル

ExcelVBAで絶対参照・相対参照を自動変換するコード一覧

上記メリットで説明したとおり、大量のセル範囲を手動で参照を切り替えることは非現実的です。
そのため、VBAを活用することで、一括で参照変換ができるようにすることが重要となります。

とはいえ、「VBAって難しいんじゃないの？」と思われる方もいらっしゃるかもしれません。
しかし、実際はそんなに難しい作業ではありませんのでご安心ください。

ここでは、紹介しているVBAを貼るだけで、簡単に参照変換ツールを作ることができます。

今回は、
・絶対参照に変更するVBAコード
 ・相対参照に変更するVBAコード
 ・複合参照に変更するVBAコード（行のみ/列のみ）
のパターンに分けて紹介していきます。
画像も踏まえて分かりやすく紹介していきますので、ぜひチェックしてみてください。

絶対参照に変更するVBAコード

■絶対参照に変更する場合

まずは、絶対参照に切り替えるVBAを紹介します。
VBAを実行するには、VBAコードを「標準モジュール」に登録する必要があるため、画像を用いて流れを説明していきます。

１．「Alt」＋「F11」キーを押し、VBAエディタを開く→「挿入」タブより「標準モジュール」を選択

２．VBAコードをコピペして、上書き保存

以下にある「絶対参照」に切り替えるVBAをコピー&ペーストしてください。
（このVBAはどういう意味なの？と思った方は、「＜補足＞Application.ConvertFormulaとは？」にて詳しく解説していますので、チェックしてみてください。）

sub 絶対参照に変換()

  Dim cell as Range
  '選択範囲のセルを1つずつ処理
  For Each cell In Selection
    'セルの数式を絶対参照に変換
    if cell.HasFormula Then
      cell.Formula = Application.ConvertFormula(cell.Formula,xlA1,xlA1,xlAbsolute)
    End If
  Next cell
  End Sub

💡ポイント

新規ブックで作成される場合、「ファイルの種類」を「Excelマクロ有効ブック」で保存することを忘れずに！

これで、VBAを登録することができました。
あとは、参照切替したい範囲を選択して実行すると、絶対参照に切り替えることができます。

３．エクセルの画面で、「Alt」＋「F8」キーで実行したいマクロを選択し、実行する

これで、選択しているセルが絶対参照に変換されます。
以下のとおり、ちゃんと絶対参照になっていることが確認できます。

相対参照に変更するVBAコード

■相対参照に変更する場合

このコードは、選択した範囲内のセルが数式を含んでいる場合、その数式を相対参照に変換します。
先ほどと同じように、「標準モジュール」に登録することで使用することができます。

sub 相対参照に変換()

  Dim cell as Range
  '選択範囲のセルを1つずつ処理
  For Each cell In Selection
    'セルの数式を相対参照に変換
    if cell.HasFormula Then
      cell.Formula = Application.ConvertFormula(cell.Formula,xlA1,xlA1,xlRelative)
    End If
  Next cell
  End Sub

複合参照に変更するVBAコード

■複合参照に変更する場合（行のみ絶対参照）

このコードは、選択した範囲内のセルが数式を含んでいる場合、数式を行のみ絶対参照に変換します。

sub 行だけ絶対参照に変換()

  Dim cell as Range
  '選択範囲のセルを1つずつ処理
  For Each cell In Selection
    'セルの数式を絶対参照に変換
    if cell.HasFormula Then
      cell.Formula = Application.ConvertFormula(cell.Formula,xlA1,xlA1,xlAbsRowRelColumn)
    End If
  Next cell
  End Sub

■複合参照に変更する場合（列のみ絶対参照）

このコードは、選択した範囲内のセルが数式を含んでいる場合、数式を列のみ絶対参照に変換します。

sub 列だけ絶対参照に変換()

  Dim cell as Range
  '選択範囲のセルを1つずつ処理
  For Each cell In Selection
    'セルの数式を絶対参照に変換
    if cell.HasFormula Then
      cell.Formula = Application.ConvertFormula(cell.Formula,xlA1,xlA1,xlRelRowAbsColumn)
    End If
  Next cell
  End Sub

＜補足＞Application.ConvertFormulaとは？

今回は対象範囲の参照スタイルを変更するために、「Application.ConvertFormula」メソッドを使用しました。こちらを使用することで、絶対参照や相対参照に変換することが可能となります。

「Application.ConvertFormula」メソッドの書式は以下のとおりです。

=Application.ConvertFormula（Formula,FromReferenceStyle,[ToReferenceStyle],[ToAbsolute],RelativeTo）

とはいえ、これだけではよく分からないですよね。
各パラメータごとに解説していきます。

名前	必須/省略可	説明
Formula	必須	変換する数式を含む文字列を指定
FromReferenceStyle	必須	変換する前の参照形式を指定・A1形式：xlA1 ・R1C1形式：xlR1C1
ToReferenceStyle	省略可能	変換後の参照形式を指定（省略した場合、FromReferenceStyleと同じ形式）
ToAbsolute	省略可能	セルの参照スタイルを指定（XlReferenceType） ※省略可能ですが、今回の絶対・相対参照切替時は設定必須
RelativeTo	オプション	指定した場合、指定したセルを起点とした相対参照に変換

■Formula
→今回は選択したセル範囲の数式を、絶対・相対参照に切り替えることが目的です。
そのため、「Cell.Formula」メソッドを使用して、選択したセルの数式を指定しました。

■FromReferenceStyle
→参照形式は、「A1」形式と「R1C1」形式の2種類あります。
同じセル（A1=R1C1）を意味するのですが、「A1」形式が見慣れている方が多いと思います。
そのため、今回は「A1」形式を指定する「xlA1」を記述しました。

「A1」と「R1C1」形式の違いについて知りたい！という方は、こちらの記事で紹介しています。
ぜひチェックしてみてください。

■ToAbsolute
→今回はここがポイントとなります。
目的は、選択したセルの絶対・相対参照を切り替えることなので、ここでセルの参照スタイルを選択してあげる必要があります。

実際のVBAコードにも記載しましたが、セルの参照スタイル（XlReferenceType）は以下のとおり4種類あります。
「Absolute＝絶対参照」、「Relative=相対参照」を意味します。

名前	値	説明	例
xlAbsolute	1	行列いずれも絶対参照に変更	=$A$1
xlAbsRowRelColumn	2	行のみ絶対参照に変更	=A$1
xlRelRowAbsColumn	3	列のみ絶対参照に変更	=$A1
xlRelative	4	行列いずれも相対参照に変更	=A1

先ほど紹介したVBAコードも、この「ToAbsolute」部分を書き換えているだけなのがお分かりいただけると思います。

（応用）複数シートを一気に変更する場合！

各シートをループすることにより、選択したシート・選択したセル範囲全ての数式を一気に絶対参照にすることもできます。
複数シートを一気に変更するVBAコードは以下の通りです。

Sub 選択したシートの選択したセルを絶対参照にする()

  Dim ws As Worksheet
  Dim cell As Range
  Dim formulaStr As String

  'アクティブウインドウにある選択した複数シートをループ
  For Each ws In ActiveWindow.SelectedSheets
    'シートの選択したセル範囲をループ
    For Each cell In ws.Range(Selection.Address)
      '数式があるセルがある場合
      if cell.HasFormula Then
        'セル内の数式を取得し、絶対参照に変更
        cell.Formula = Application.ConvertFormula(cell.Formula,xlA1,xlA1,xlAbsolute)
      End If
    Next cell
  Next ws
End Sub

※状況によっては（複雑な数式や名前付き範囲など）うまく動作しない可能性あるため、そのような場合はちゃんと参照変換されるか確認されることをおすすめします。