エクセルで数式を作成する際、絶対参照は数式のコピー時に参照先がずれないようにするための重要な機能です。
既存の数式を後から絶対参照に変換したい場合や、大量の数式の参照形式を一括変更したい場面は多くあります。
本記事では、エクセルで絶対参照を一括変換・設定・変更する方法をショートカット・数式・範囲指定・VBAの観点から解説していきます。
エクセルの絶対参照を効率よく設定・変換する基本的な方法
それではまず、エクセルの絶対参照を効率よく設定・変換するための基本的な方法について解説していきます。
絶対参照の設定にはF4キーを使ったショートカットが最も手軽で、数式入力中にF4キーを押すだけで参照形式を瞬時に切り替えられます。
F4キーによる絶対参照の設定方法
数式入力中にセルアドレスにカーソルを合わせた状態でF4キーを押すと参照形式が切り替わります。
F4を1回押すと$A$1の絶対参照、2回押すとA$1の行のみ絶対参照、3回押すと$A1の列のみ絶対参照、4回押すとA1の相対参照に戻ります。
目的の参照形式になるまでF4キーを繰り返し押すだけで設定が完了するため、手動でドルマークを入力するよりはるかに効率的です。
既に入力済みの数式を編集する場合も、セルアドレス部分にカーソルを置いてF4キーを押せば切り替えが可能です。
絶対参照を使うべき場面の判断基準
数式を縦横両方向にコピーする場合は$A$1の完全絶対参照が適しています。
縦方向にのみコピーする場合は$A1の列のみ絶対参照、横方向にのみコピーする場合はA$1の行のみ絶対参照が適切です。
参照先が固定の単価テーブルや税率セルなどを参照する数式では絶対参照が必須です。
参照形式の選択を誤ると数式コピー後に予期しない結果になるため、事前にどの方向にコピーするかを考えてから設定しましょう。
複数のセルに絶対参照を一括設定する方法
複数のセルの数式を一括で絶対参照に変換したい場合は、VBAのConvertFormulaメソッドが最も確実な方法です。
後述のVBAセクションで詳しく解説しますが、手動で行う場合は各セルを順番にF4で変換していくのが基本です。
数式の量が少ない場合はF4キーで対応し、大量の場合はVBAを活用するのが合理的な判断です。
検索と置換を使った絶対参照の一括変換
続いては、検索と置換機能を使った絶対参照の一括変換方法を確認していきます。
特定のセルアドレスへの参照を一括で絶対参照に変換する場合、検索と置換が有効な場面があります。
特定のアドレスパターンに絞った変換には置換機能が手軽に活用できます。
相対参照を絶対参照に変換する置換操作の手順
「Ctrl＋H」で検索と置換ダイアログを開き、「検索する文字列」に変換したいアドレス（例：C1）、「置換後の文字列」に絶対参照形式（例：$C$1）を入力します。
「すべて置換」を実行すると、シート上の該当する相対参照がすべて絶対参照に変わります。
ただし、数式以外のセルにも同じ文字列パターンがある場合は意図しない変換が起きる可能性があるため注意が必要です。
ドルマークを一括削除して相対参照に戻す方法
逆に絶対参照を相対参照に戻したい場合は、「検索する文字列」に「$」、「置換後の文字列」を空欄にして置換します。
これですべての数式からドルマークが削除されて相対参照に変換されます。
操作前に対象範囲を選択して範囲限定の置換にすることで、影響範囲を制御できます。
置換操作の注意点と確認手順
置換操作を実行する前に必ず「次を検索」で変換対象のセルを確認しましょう。
想定外のセルが対象になっていないかを事前確認することで誤変換を防げます。
また、操作前にファイルを保存してバックアップを確保しておくことも重要です。
VBAを使った絶対参照の一括変換自動化
続いては、VBAを活用して絶対参照を一括変換する自動化方法を確認していきます。
大量の数式を処理する場合やシート全体の参照形式を統一したい場合にVBAは非常に強力なツールです。
ConvertFormulaメソッドを使った一括変換は精度が高く安全な方法です。
選択範囲の数式を絶対参照に変換するVBAコード
Sub 絶対参照に一括変換()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula( _
cell.Formula, xlA1, xlA1, xlAbsolute)
End If
Next cell
MsgBox “絶対参照への変換が完了しました”
End Sub
このコードは選択セル範囲のすべての数式を完全絶対参照（$A$1形式）に変換します。
実行前に変換したいセル範囲を選択してからマクロを起動しましょう。
シート全体の数式を相対参照に一括変換するVBAコード
Sub 相対参照に一括変換()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula( _
cell.Formula, xlA1, xlA1, xlRelative)
End If
Next cell
End Sub
xlRelativeに変更することですべての参照を相対参照に変換できます。
UsedRangeを使うことでデータが存在するセルのみを対象にした効率的な処理が可能です。
変換結果を確認するためのデバッグ方法
変換後の数式が意図通りになっているかを確認するために、変換前後の数式をシートに書き出す処理を追加するのも有効です。
変換ログを別シートに記録することで、問題が発生した際の原因特定が容易になります。
テスト用のサンプルデータで動作確認をしてから本番データに適用する手順を徹底しましょう。
まとめ
本記事では、エクセルで絶対参照を一括変換・設定・変更する方法をF4キー・検索置換・VBAの観点から解説しました。
F4キーによる参照形式の切り替えは数式入力時に必ず習得しておきたい基本テクニックです。
検索と置換を使ったドルマークの一括削除・追加は手軽に実行できる方法です。
VBAのConvertFormulaメソッドを使えば大量の数式の参照形式変換も正確・安全に自動化できます。
絶対参照の操作を使いこなして、エクセル数式の管理と活用をさらに効率化させていきましょう。
