PR

Excel VBAで絶対参照⇔相対参照を自動変換する方法|マクロで手間をゼロに！

eyecatch_reference-absolute-relative Excel
2025.03.30
記事内に広告が含まれています。

エクセル作業をやっていると、このような悩みを持ったことはないでしょうか？

・参照切替を毎回F4キーで手直ししているので、手間やミスが増えるし面倒くさい。。
・絶対参照⇔相対参照を一括で変換できたら便利なんだけどな～。。
・そもそも絶対参照・相対参照の違いって何？

エクセルで絶対参照・相対参照を切り替える機会って、とても多いですよね！
ただ参照が適切にされていないと、数式をコピーするときに参照位置がずれてしまったり、、
設定しようとしたらF4キー連打でめちゃくちゃ時間がかかったり。。

当時は私もどうしたらいいだろう？と困っておりました。

でも、もう大丈夫！
この記事では、これらの問題を解決に導くテクニックを紹介します！

「自動で参照を切り替えて、作業効率を上げたい！」
「なんとなく使っていたけど、絶対参照と相対参照の違いって何だろう？」と思われている方！
ぜひ一度目を通していただけると嬉しいです！

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

　エクセルには「絶対参照」、「相対参照」と「複合参照」という参照方法があります。

　■絶対参照: セルをコピー・移動しても参照先が変わらない。
　　絶対参照は、セル参照の前に「$」マークが付きます。例えば、「$A$1」という形になります。

　■相対参照: セルをコピー・移動したときに、参照先のセルが変わる。
　　例えば、A1を参照しているセルをコピーすると、コピー先のセルに合わせて参照先も変わります。

　■複合参照: 行・列別々に絶対参照・相対参照を使用する。
　　例えば、行を絶対参照する場合は「A$1」、列を絶対参照する場合は「$A1」となります。

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

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

ちなみに「$」は、セルの数式部分を選択しながら「F4」キーを押すと表示させることができます。
「F4」キーを押すと、
・①絶対参照　→　②複合参照（行）　→　③複合参照（列）　→　④相対参照
の順番で切り替えることができます。

少しのセルであれば、簡単に参照方法を切り替えることができますね！

ただこの作業が増えてくると、いちいち「F4」キーで切り替えていくわけにもいかないですよね。。

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

参照方法を切り替えるには「F4」キーを押すことで、一つずつ実行することができます。
ただその範囲が大きくなると、とてもじゃないけど対応しきれないですよね。。

そのため、絶対参照⇔相対参照を一括で切り替えできると、多くのメリットを受けることができます。

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

「F4」キーは一つずつ参照方法を切り替えることができますが、大量のセル範囲を切り替えることはできません。そのような場合、とてつもない時間がかかってしまいますよね。。

そのため、参照方法を一括で切り替えることができると、作業時間を大幅に削減できるため、効率な作業が可能となります。

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

手作業で切り替えていると、どうしてもヒューマンエラーが発生してしまう可能性があります。
ミスが発生したとなると、どれが原因になっているか探さないといけなくなるので、作業中イライラしてしまいますよね。。

参照方法を一括で変更できれば、「値がずれた」「合計が合わない」といったトラブルが限りなく減るため、作業効率アップにもつながります。

一括で変更できるため、数式のメンテナンスがラク！

手作業で参照切替をしたり、データの追加・削除による増減があったとき、参照範囲の切り替えができていなかったケースもあると思います。
そのときに、一つずつ確認するのは非常に手間ですよね。。

一括で参照変更できると、一部参照切替ができていなかったセルも調整できるため、ファイルのメンテナンスが簡単にできます。

参照を一括で切り替えるVBAコード一覧

ではさっそく、参照を一括で切り替えることができるVBAコードを紹介していきます。

今回、
・絶対参照に変更するVBAコード
・相対参照に変更するVBAコード
・複合参照に変更するVBAコード（行のみ/列のみ）
に加え、
・複数シートも一括で参照切替できるVBAコード
の順に紹介していきます。

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

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

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

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

VBAコードの実行方法は、以下手順で実行します。

　・エクセルを開いた状態で「Alt」＋「F11」キーを押し、VBAエディタを開く
　　↓
　・「挿入」タブより「標準モジュール」を選択

reference_VBA-edit_No1

　・VBAコードをコピペして、上書き保存
　　※新規ブックで作成される場合、「ファイルの種類」を「Excelマクロ有効ブック」で保存することを忘れずに！

reference_VBA-edit_No2

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

reference_VBA-edit_No3

　・相対参照が、無事に絶対参照に変換された！

reference_VBA-edit_No4

相対参照に変更する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

＜応用＞複数シートを一気に変更する場合

各シートをループすることにより、選択したシート・選択したセル範囲全ての数式を一気に絶対参照にすることもできます！
複数シートを一気に変更する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

状況によっては（複雑な数式や名前付き範囲など）うまく動作しない可能性あるため、そのような場合はちゃんと参照変換されるか確認の上、ご活用ください！
うまく活用すれば自動化による作業効率化にもつながりますので、ぜひ活用してみてください！

＜補足＞Application.ConvertFormulaとは？

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

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

それぞれの説明は以下の通りです！

reference_convertformula-explanation_No1

参照形式は、「A1」と「R1C1」があります（いずれも同じセルを識別）が、見慣れた参照形式を指定することで問題ないです！（私はA1形式が見慣れているので、今回はこちらの参照形式を指定）

実際のVBAコードにも記載しましたが、セルの参照スタイル（XlReferenceType）は以下のとおり4種類あります！

reference_convertformula-explanation_No2

まとめ：参照の切り替えもVBAでスマートに！効率を爆上げしよう！

絶対参照と相対参照を自在に切り替えることができると、作業効率が格段にアップします。
VBAを使えば、面倒な手作業も一括で処理することができます。

今回紹介したVBAコードを活用し、作業効率を爆上げしていきましょう！

最後までご覧いただき、ありがとうございました！

コメント

タイトルとURLをコピーしました