写真提供:マイナビニュース

写真拡大

今回は、さまざまな計算をExcelで行うときに必須となるセル参照について解説していこう。Excelには「相対参照」と「絶対参照」の2種類のセル参照が用意されている。両者の違いを十分に理解していれば、数式をスムーズに入力できるようになり、Excel上級者に一歩近づくことが可能となる。この機会によく仕組みを学んでおこう。

○数式の入力とオートフィル

まずは、数式の基本を簡単に解説しておこう。セルに数式を入力するときは、最初に「=」の記号を入力し、続いて「C3」や「D3」などのセル参照を利用して数式を記述していく。数式内で使用する演算記号は、足し算を「+」、引き算を「-」、掛け算を「*」、割り算を「/」で記述すればよい。これらの記述は半角文字でも全角文字でも構わない(自動的に半角文字に変換される)。最後に「Enter」キーを押すと、数式の計算結果がセルに表示される。

ある程度Excelの使った経験がある方なら、ここまでの内容はすでに知っていると思われる。もちろん、入力した数式をオートフィルでコピーすることも可能だ。この場合は、数式内のセル参照が自動調整されながら数式がコピーされていく仕組みになっている。

こちもExcelの基本的な知識といえるので、あえて詳しく解説しなくても理解できだろう。

○相対参照と絶対参照の使い分け

通常、数式内にセル参照を記述するときは、「C3」や「E5」のように、列番号と行番号を続けて記述するのが一般的だ。このような記述方法のことを「相対参照」と呼ぶ。

一方、Excelには「絶対参照」と呼ばれるセルの参照方法も用意されている。こちらは「$C$3」や「$E$5」のように、列番号や行番号の前に「$」(ドル)の記号を付けてセル参照を記述する方法となる。

「相対参照」と「絶対参照」は、いずれも「指定したセル」の値を数式に代入して計算するための記述となる。よって、単に数式を入力するだけなら、どちらの方法でセル参照を記述しても計算結果に違いは生じない。両者に違いが生じるのは、オートフィルなどを使って数式をコピーしたときだ。

具体的な例で解説していこう。以下は、アンケート調査の結果をまとめた表となる。この表で「回答者の割合」(%)を算出するときは、(各選択肢の回答数)/(全体の回答数)を計算すればよい。

このとき、あらかじめ「パーセンテージ」の表示形式を指定しておけば、計算結果を百分率(%)で示すことが可能となる。数式と表示形式の使い方を知っていれば、ここまでの作業は特に問題なく理解できるだろう。

では、この数式をオートフィル―でコピーするとどうなるだろうか? 今回の例の場合、分子の「C4」だけでなく、分母の「C9」も自動調整されながら数式がコピーされていくため、コピーされた数式は以下の図のようにエラーが発生してしまう。

上記の例の場合、D5セルには「=C5/C10」という数式がコピーされることになる。同様に、D6セルには「=C6/C11」、D7セルには「=C7/C12」という数式がコピーされていく。ことのき、C10やC11といったセルには数値データが入力されていないため、「0での割り算」が行われたと解釈され、結果として「#DIV/0!」のエラーが表示されてしまう。

このようなミスを防ぐには、オートフィルを使わずに、そのつど数式を入力していく必要がある。とはいえ、これはそれなりに面倒な作業だ。このような場合に活用できるのが「絶対参照」を使った数式の記述となる。

今回の例の場合、分母の部分を「$C$9」と絶対参照で記述しておくと、オートフィルを使って数式を正しくコピーできるようになる。

「絶対参照」の特徴は、数式をコピーしてもセル参照が変化しないこと。つまり、分子の「C4」だけが自動調整され、分母の「$C$9」はそのままコピーされることになる。

このように、オートフィルを使って数式を正しくコピーするには、「相対参照」と「絶対参照」を上手に使い分けて数式を記述しておく必要がある。念のため、もう一度まとめておこう。

相対参照・・・コピーするとセル参照が自動調整される(記述例:C9)
絶対参照・・・コピーしてもセル参照は変化しない(記述例:$C$9)

このような仕組みを理解しておけば、たいていの数式をオートフィルでコピーできるようになる。慣れるまでに少し時間がかかるかもしれないが、数式を手間なく入力するための必須知識といえるので、余裕があるときによく研究しておくとよいだろう。

ちなみに、この仕組みは「Ctrl+C」キーと「Ctrl+V」キーでセル(数式)をコピーした場合にも適用される。相対参照は「コピー元」のセルとの位置関係に応じて自動調整される。一方、絶対参照は「コピー元」と「コピー先」の位置に関係なく、元のセル参照がそのままコピーされる。念のため、こちらも覚えておく必要があるだろう。

○相対参照と絶対参照をF4キーで切り替える

最後に、絶対参照を素早く入力すためのショートカットキーを紹介しておこう。絶対参照を利用するときは、列番号や行番号の前に「$」の記号を入力しなければならない。このとき、「$」の記号を入力する代わりに「F4」キーを押しても構わない。すると、「相対参照」で入力したセル参照を「絶対参照」に変換することができる。

続けて、もう一度「F4」キーを押すと、セル参照の記述は「C$9」(行番号のみ固定)に変換される。さらに「F4」キーを押すと、セル参照の記述は「$C9」(列番号のみ固定)に変換される。その後、再度「F4」キーを押すと、元の記述である「C9」(相対参照)に戻る。このように、「F4」キーを押す回数に応じて、セル参照の記述を自由に変換していくことが可能だ。

「F4」キーを使ったセル参照の変換
 相対参照 → 絶対参照 → 行番号のみ固定 → 列番号のみ固定 → …
この操作は、入力済みの数式にも利用できる。セルをダブルクリックして数式を表示し、変換したいセル参照の部分にカーソルを移動する。続いて「F4」キーを押すと、「C9」→「$C$9」→「C$9」→「$C9」…といった具合にセル参照の記述を変換していくことが可能となる。間違って「相対参照」で入力してしまったセル参照を、「絶対参照」に変更する場合などに活用できるだろう。意外と利用頻度は高いので、ぜひ覚えておくとよい。

(相澤裕介)