2016年11月 3日(木) 12時27分14秒 [Office系ソフト]

Excelで常に絶対参照されたいセルには固有の名前を付けておくと便利かも知れない

Excelでセル番号を含む計算式をコピーした場合、セルの参照方法はデフォルトでは相対参照になります。
しかし、セルに固有の名称を付加しておき、その名称を使って計算式を書けば、デフォルトの参照方法は絶対参照になります。
つまり、常に絶対参照で参照されたいセルには固有の名称を付けておけば、その後の操作が楽になります。Excelファイルを複数人で共有する場合にも操作ミスがなくて便利かもしれません。

Excelで、特定のセルに固有の名前を付与する方法

Excelのワークシートで特定のセルを選択しているとき、画面左上にはセルの番号が表示されています。下図左側では「B1」というセル名が表示されています。
この部分をクリックして任意の文字列を入力すれば、そのセルに固有の名前を付与できます。
下図右側では「桜」という名称を入力しています。

これで、これ以降はこの「B1」セルを「桜」という名前で参照できます。この固有の名前は計算式にも使えるので、「=桜+B2」みたいな計算式にもできます。
税率とかのセルに「税率」という名称を付けておくと、計算式が分かりやすくなるメリットもあると思います。
このようにセルに固有の名前を付けた場合、参照方法が絶対参照になる副作用(？)があります。

セルに固有の名称を付加しておくとデフォルトで絶対参照になる例

下図では「B1」セルに固有の名前「さくら」を付加してあります。
そして、そのセルと複数のセルとでかけ算を実行しています。

このとき、「C3」セルには計算式として「=さくら*B3」と入力してあります。
これを下方向にコピーすると、「さくら」セルだけは絶対参照で、「B3」セルは相対参照としてコピーされるため、

• 手動入力したC3セルの内容： =さくら*B3
• C3をコピーしたC4セルの内容： =さくら*B4
• C3をコピーしたC5セルの内容： =さくら*B5

……というように、計算式内の「さくら」部分だけは変わらずにコピーされます。
このようにしておけば、複数人でセルを編集するような場合や、Excel初心者にファイルを渡す場合などで、他人がセルB1（さくら）を含む計算式を新たに加えるようなとき、このセルだけは常に絶対参照で参照できるようにできます。
なかなか便利な気がします。

セルに固有の名称を付けなかった場合に計算式をコピーするとどうなるか

Excelでは、デフォルトのセルの番号(名称)を計算式に書いた場合、そのままでは相対参照になります。
例えば「=A1*100」と書いたセルを下方向にコピーすれば、「=A2*100」、「=A3*100」、「=A4*100」などのように参照するセルが下方向に(行番号が)相対的に変化します。
同様に、右方向にコピーすれば、「=B1*100」、「=C1*100」、「=D1*100」のように参照するセルが横方向に(列番号が)相対的に変化します。

計算式でセルを相対参照のままにしたために、おかしくなった例

計算式の中で常に固定的に参照したいセルがあるにも関わらず、デフォルトの相対参照のままにしたために、計算結果がおかしく(意図しないものに)なった例が下図です。

C3セルには、「=B1*B3」を入力しているので、100×1が実行されて、結果100が表示されています。
これを下方向に単純にコピーすると……、

• 手動入力したC3セルの内容： =B1*B3 (※「100」×「1」になるので計算結果は100)
• C3をコピーしたC4セルの内容： =B2*B4 (※「かけ算↓」×「2」になるので計算結果はエラー)
• C3をコピーしたC5セルの内容： =B3*B5 (※「1」×「3」になるので計算結果は3)

……のように、かけ算の計算式で使われているすべてのセルが相対的に移動するため、意図しない計算結果になります。
このようなことを防ぐには、セル「B1」を絶対参照にするよう計算式を書いておきます。

計算式でセルを絶対参照にすることで、コピーしても参照セルを変化させなくする方法がある

で、Excelでは「$」記号を使うことで、セルを絶対参照させることができます。下図だと意図通りの計算結果になっています。

C3セルには、「=$B$1*B3」と入力しているので「B1」セルだけは絶対参照になっています。
これを下方向にコピーすると……、

• 手動入力したC3セルの内容： =$B$1*B3
• C3をコピーしたC4セルの内容： =$B$1*B4
• C3をコピーしたC5セルの内容： =$B$1*B5

……このように、計算式をコピーしてもセル「B1」だけは絶対参照として固定したままコピーされます。これなら意図した結果になります。

※ここでは下方向にしかコピーしないので、「$B$1」ではなく「B$1」とだけ書いても可。(その場合、横方向にコピーした場合は相対参照になります。)

問題は、このような絶対参照にするためには、計算式を書くたびにセル番号に「$」記号を加えなければならない点です。
その事実を知らない初心者がワークシートを引き継いで編集する場合には、ちょっと困るかも知れません。

セルの固有名称を計算式に使えば、デフォルトで絶対参照になる

そこで便利なのが、冒頭で紹介した「セルに固有の名前を付けておく方法」です。
セルに固有の名称を付けておいて、計算式内でもその固有の名前を使えば常に絶対参照になりますから。

もっとも、セルに固有の名前が付与されていても、計算式内でセル番号を使えば(デフォルトでは)相対参照になるわけですけども。(^_^;)
計算式を書くときに、参照セルをマウスで選択したときには、セル番号ではなくセルに付与されている固有の名前が使われます。

▲セルに固有の名前が付与されているとき、セル固有の名前を計算式に使えば絶対参照になる。ただし、セル番号でセルを参照できなくなるわけではないので、セル番号を使えばデフォルトの参照方法(＝$記号なしなら相対参照で、$記号があれば絶対参照)になる。

というわけで、セルに固有の名前を付与すれば、その名前を計算式に使ったときにはデフォルトで絶対参照になるよ、という話でした。

◇(参考記事)『Excel：セルに「名前」をつけると作業が超ラクに! 意外と知らない数式入力の便利ワザ』(GetNavi)