絶対参照にすべきエクセルファイルとは。相対参照のままだとストレスの原因に？【Excel講師の仕事術】

Excelのセル参照は、何もしないと相対参照のままです。しかし、定期的に更新するようなファイルでは、相対参照ではなく絶対参照にしたほうがいいものが多くあります。絶対参照にすべきファイルや、誤って相対参照になっているファイルの修正方法などを解説しました。

【Excel講師の仕事術】自己流のエクセル、卒業しませんか？この連載では、エクセルを操作するときの「ルール」を決めることで、ミスを減らして業務を効率化していく仕事術を解説します。

ルール 26
定期更新ファイルでは絶対参照のほうが安心
更新のストレスが減ってミスも抑えてくれる

Excelのセル参照は、何もしないと相対参照のままです。よって、絶対参照にする必要があって切り替えたセル以外は、すべて相対参照になっていることが多いのではないでしょうか。

しかし時として、相対参照を放置していたことが、のちのち大きなストレスの原因になることがあります。その典型例が、定期的にデータを更新していく形式のファイルです。

例で説明しましょう。以下は関東エリア（1都6県）の売上実績を月ごとにまとめていくための報告書です。

みなさんは関東エリアを総括するマネージャーで、毎月各エリアから上がってくる売上実績を集計して本部に報告するために、このシートを管理しているとイメージしてください。各エリアの数値は、同じファイル内にあるエリア別のシートの決められたセルを参照することで表示しています。

上図を見ると、東京エリアの2020年4月を表すセルC8には「=東京!B2」と入力されており、「東京」シートのセルB2への相対参照となっています。神奈川、千葉など、ほかのエリアも同様です。もちろん、このままでも間違いではありません。

しかし、2020年5月の売上実績を隣の列に入力するため、2020年4月の列から数式をコピーすると、困ったことが起こります。相対参照なので「=東京!B2」が「=東京!C2」となり、正しいセルを参照できなくなってしまいました。

結果、以下のように数値が空欄になりましたが、すぐにおかしいと気付けるので、まだいいほうです。仮に、間違った参照先にそれらしい数値があった場合、それが2020年5月の売上実績であると誤認し、本部に報告してしまうかもしれません。これはマネージャーとして、あってはならないミスです。

では、どうすればよかったのでしょう？答えは簡単です。セル参照を、あらかじめ絶対参照にしておけばいいのです。以下は東京の例ですが、ほかのエリアも同様に絶対参照に切り替えます。

その後、先ほどと同じように2020年4月を2020年5月にコピーすると、5月にはいったん、4月と同じ数値が表示されることになります。これだけで作業が完了するわけではありませんが、「正しい数値を更新する準備が整った」といえるでしょう。

そして、参照先の各エリアのシートを5月のデータに更新する前に、4月のデータをコピーして「値」として貼り付けておきます。操作としては、以下のようにそのまま貼り付けて、［貼り付けのオプション］から［値］を選択するのが定番です。

最後に、各エリアのシートを5月のデータに更新すれば、報告書のシートに正しい数値が自動的に入ります。以降、月が変わるたびに同じことを繰り返せばいいので、ミスは抑えられるでしょう。

この例のような、月ごとの売上推移を年単位で記録していくファイルは、多くの現場で使われていると思います。こうしたファイルは初回の作成自体が大変なので、最初の月の数式があっていると、そこで「できた！」と満足してしまいがちです。

しかし、本当は絶対参照にしておくべきセルを相対参照のまま放置してしまったがために、セル参照がずれてミスをすることがあります。何を隠そう、筆者自身も経験済みです。

定期的に更新していくファイルでは、翌月以降も同一の操作で正しい数値が求められる状態になっているか、初回の作成時によく確認してください。このルールを怠ると、その先もずっと集計ミスを連発してしまうおそれがあるためです。

とはいえ、ファイルを作成してしばらくしてから、あるいは、前任者から引き継いだファイルを見て、絶対参照にすべきだったと気付くこともあるでしょう。先ほどの例なら関東エリアの1都6県、つまり7つのセルで相対参照を絶対参照に切り替えていけばいいので、大した手間ではありません。

しかし、みなさんが全国47都道府県の売上実績をまとめるファイルを管理する立場だとしたら、どうでしょうか？ 1つずつ修正するとなると、約50回の単純作業を強いられます。それでも絶対参照にすべきなのですが......提出期限が迫っていて難しい場合、よい代替手段があります。置換機能を使いましょう。

数式はすべて「=」（イコール）から始まります。この「=」を、例えば「#」に置換すれば、Excelは数式ではなく文字列とみなします。文字列なので相対参照ともみなされず、隣の列にコピーしても、参照先は変わりません。

コピーした後に再び置換機能で「#」を「=」に戻せば、元のセル参照のままで大量の数式をコピーできる、というわけです。あくまでも緊急措置ですが、必要に駆られたときは、以下から一連の操作を確認しつつ試してみてください。