エクセルの数式で使用する絶対参照ですが、
「F4」を押して絶対参照と相対参照を切り替える事ができます。
そこからフィルダウンやセルのコピーで
参照先を固定したり、行や列のみ固定したりなどできるとても便利な機能です。
そんな絶対参照・相対参照ですが
一括で設定したい場合や解除したい場合などには、とても一苦労です。
そこで今回は絶対参照・相対参照を一括で切り替えれる方法について紹介します。
それでは見ていきましょう。
⬛︎こんな事が出来るようになる！
絶対参照と相対参照を一括で切り替えれる
⬛︎通常の絶対参照・相対参照について
まず、おさらいとして通常の絶対参照と相対参照を見ていきましょう。
下記の見本を見ていきましょう。
C3のセルに「＝A1」を入力して、A1を参照しています。
この数式の「A1」を選択した状態で「F4」キーを押す事で
A1に「$」が追加されて「＝$A$1」となります。
これが「絶対参照」を表す記号になります。
この状態でフィルダウン、コピーをしても参照先は動く事がありません。
対して「$」がついてない相対参照は
フィルダウン・コピーをすると参照先もあわせて動きます。
また、絶対参照には「絶対行参照」「絶対列参照」があります。
先程の「＝$A$1」の状態で、再度「F4」を押すと
「＝A$1」となります。
この状態が「絶対行参照」で行方向のみ固定となります。
さらに「F4」を押すと「＝$A1」の形になり、
「絶対列参照」となります。こちらで列方向の固定が可能です。
では本題に入りましょう。
通常は「F4」を押して絶対参照と相対参照を切り替えていましたが
複数の数式の絶対参照・相対参照を変更するのには
とても苦労します。
しかし、エクセルの機能を活用すれば簡単に一括で切り替えることが可能です。
では絶対参照から相対参照への切り替えを見ていきましょう。
⬛︎絶対参照から相対参照への一括変更
まずは絶対参照から相対参照に一括で変更する方法を見ていきましょう。
下記の見本をご覧ください。
こちらは簡単で「置き換え」の機能を活用します。
置き換えは「ホーム」の「検索と選択」から
「置換」を選択します。
すると下図のメニューが表示されます。
「検索する文字列」には「$」を入力して
「置換後の文字列」は空白にします。
ここで「すべて置換」を押すと一括で相対参照にする事が可能です。
注意点として1つのセルを選択しているとシート内全ての置き換えとなるので
範囲を絞りたい場合は対象範囲を選択した状態で「すべて置換」を押してください。
結果はこのようになります。
こちらの仕組みとしては絶対参照は「$」で指定しているので
「$」をなくす事で相対参照になります。
今回は置き換えで「$」を空白に変えるわけですから
置き換え後は相対参照になるという仕組みです。
⬛︎相対参照から絶対参照への一括変更
今度は相対参照から絶対参照へ一括で変更する方法を紹介します。
変更する方法として、3つパターンを用意しました。
・列が同一の場合
・行が同一の場合
・行と列がどちらとも統一されてない場合
では各パターンの対処法を紹介します。
・列が同一の場合
まずは列が同一の場合から見ていきましょう。
例えば「＝A1」や「＝A8」などのように列が同一である場合に有効です。
では下図の例を見てみましょう。
B列の相対参照の数式が並んでいます。
こちらを絶対参照にしてみたいと思います。
方法は絶対参照から相対参照に変更する際に使用した「置き換え」を活用します。
「検索する文字列」には対象の列の英字を入力します。
見本では「B」になります。
「置換後の文字列」は対象の列の英字に「$」を合わせます。
見本では「$B$」になります。
では「すべて置換」を実行してみましょう。
結果はこのようになりました。
すべて絶対参照に変わっていますね。
・行が同一の場合
今度は行が同一の場合の相対参照から絶対参照へ変更する方法を紹介します。
例えば「＝A8」や「＝D8」など行番号が同じ場合に使用できます。
まずは絶対列参照へ一括で変更します。
今回も「置き換え」の機能を活用していきます。
「検索する文字列」には「＝」を入力して
「置換後の文字列」は「＝$」にします。
これで置き換え後は絶対列参照になります。
では実行してみましょう。
結果はこのようになります。
絶対列参照になってますね。
次に絶対行参照を行います。
「検索する文字列」には対象の行番号を入力します。
見本では「8」になりますね。
「置換後の文字列」は$＋行番号にします。
見本では「$8」となります。
それでは実行してみましょう。
結果はこのようになります。
先ほどの結果とあわせて相対参照から絶対参照へ
変更する事ができました。
・行と列がどちらとも統一されてない場合
これまでは、行または列が同一である場合の
置き換えによる絶対参照への変更方法を見ていきました。
今回は行または列がどちらも同一ではない場合を紹介します。
例えば「＝A1」「＝B5」のような形ですね。
結論から言うと置き換えでの一括変更は不可能です。
行や列の種類が数通りであれば置き換えを繰り返すことを
オススメしますが、多くの数式を対象とする場合はVBAしかありません。
VBAを触った事がない人は少しハードルが高いように思えますが
実行するのみであれば簡単です。
先にコードを紹介します。
Sub 選択セルを相対参照から絶対参照に一括変更() Dim Targetcell As Range For Each Targetcell In Selection If Targetcell.HasFormula Then Targetcell.Formula = Application.ConvertFormula( _ Formula:=Targetcell.Formula, _ FromReferenceStyle:=xlA1, _ ToAbsolute:=xlAbsolute) End If Next End Sub
意味としては下記の通りです。
「For Each Targetcell In Selection」から「Next」までの選択しているセルを繰り返し処理します。
「 If Targetcell.HasFormula Then」では対象のセルが数式であるか確認します。
この指示がなければ空白でも実行してしまうため、「#VALUE!」のエラーが発生します。
「Application.ConvertFormula」では実際に数式を変更するコードなのですが
重要なのは「ToAbsolute:=xlAbsolute」になります。
こちらのコードで絶対参照に変換しています。
では補足です。「xlAbsolute」を下表に置き換えると様々な参照が可能になります。
| コード | 内容 | 見本 |
|---|---|---|
| xlAbsolute | 行・列ともに絶対参照 | $A$1 |
| xlAbsRowRelColumn | 行のみ絶対参照 | A$1 |
| xlRelRowAbsColumn | 列のみ絶対参照 | $A1 |
| xlRelative | 行・列ともに相対参照 | A1 |
では実践です。
開発タブから「Visual Basic」を選択します。
すると下図の画面が表示されるので実行したいファイルの「ThisWorkbook」を選択します。
その後先ほどのコードをコピーペーストして、実行ボタンを押してください
これで選択したセルの相対参照を絶対参照に変えてくれます。
⬛︎まとめ
いかがだったでしょうか。
使用頻度の高い絶対参照を一括で変えるのには
少しテクニックが必要です。
是非今回の内容を活用して一括での絶対参照・相対参照を切り替えてみてください。
それでは次回の記事でお会いしましょう。