絶対参照とは
Excelで表を作る時は、数式を入力して、それをコピーして制作の手間を省くことをよくやるでしょう。いや、数式の入力とコピーってよくわかんない！ という人は、まずはこちらの記事をお読みください。
オートフィルを使って数式のコピーをすれば、非常に楽ですが、たまに困ったことが起こります。
時給から給与を計算する表を、作っている途中とします。
上の画像の通り、給与を計算する数式を入力して、これをオートフィルでコピーしようとしたら、
何やらおかしなことになってしまいました。給与の数値が並ぶはずが０だったり謎のメッセージが出てたり、あり得ない数字が出たりしています。
原因を探るために、セルを選択して入力されている数式を見てみましょう。
11行目の「0」となっている、給与のセルの数式です。なぜ「0」になっているかわかるでしょうか？
実は時給を書いているセル、C7の数値をかける箇所が、空白のセルであるC8に書き換わってしまっているのです。空白だから数値がない、すなわち「0」をかけてしまったので、答が0になってしまったのです。
12行目の「＃VALUE!」という謎のメッセージが出ているセルの数式です。これもC7の数値をかけるべきところで、C9のセルをかけてしまっています。C9は「給与」という文字が書かれているセルです。数式なのに、文字の情報が混じってしまったせいで、数式が成立しなくなってしまったから、このようなメッセージが現れたのです。
「#VALUE!」は数式が成立しない時に、現れるエラーメッセージだ。せっかくだから覚えておくといいぞ。
13行目に「39200」という数値が出ているのは、C10の「7840」を最後にかけてしまっているせいです。14行目はC11の「0」をかけてしまっています。
なぜこうなってしまうのか？ オートフィルで数式をコピーすれば、セルの位置に合わせて、セルの番号を書き換えてくれる仕様となっています。これは便利な時が多いのですが、今回のように困ったことになることもあります。
どうするべきか？ 方法はあります。
書き換えてほしくない番号のセルを、「絶対参照」に設定して番号を変えないように設定するのです。
絶対参照なんて、いかにも数学的でややこしそうな言葉が出てきて、これからやることもややこしそうと思われそうですが、そんなことはありません。操作自体は簡単です。
セルをクリックして選択してから、セルの中の数式か、画面上部の数式が表示される欄の中の、固定したいセル番号の前の箇所をクリックしてから、F4キーを１回だけ押してください。
すると、画像のように、列番号である「C」と行番号である「7」の前に、$マークが追加されました。これで、C7のセルを絶対参照した状態になります。このときに、Enterキーを押して、入力を確定しましょう。
絶対参照とは、ざっくりと説明すれば、数式をコピーしてもこのセル番号は絶対に変えないでと指定することです。
「C7」を絶対参照にした数式をコピーすれば、この通りちゃんとした給与の計算ができるようになります。
11行目の数式を見れば、ちゃんとセル番号がそのままになっているのがわかります。この後の行の数式も同じです。
ちなみに、コピーするとセル番号が変わる、普通の状態のことは「相対参照」というぞ。
複合参照とは
ちょっと複雑で、単なる数式のコピーでは作れない表の作成の際には、絶対参照が便利ですが、もうひとつ覚えてほしい「○○参照」があります。
この表ですが、「5年保証のサービスには価格を\2000上乗せ、3年保証には価格を\1500上乗せする」という数式を書いて、コピーして手間を減らしたいと思うのですが、その数式は絶対参照では作れません。
では、どうすべきか？ 「複合参照」を使います。
まずは、PC本体の価格に保証サービスによる上乗せ価格を足す数式を、セルをクリックして入力します。
まず、PCの価格を、数式をコピーしてもちゃんと行に合わせるように設定する必要があります。
「B3」の前の部分をクリックして、F4キーを3回押しましょう。すると、「B」という列番号の前だけに$マークが現れます。これで、列番号のBはコピーしても書き換えられなくなり、行番号はセルに合わせて書き換えられます。すなわち、数式をどこにコピーしてもBの列に書かれているPCの価格を計算に用いるように設定されたのです。
この状態を、列番号のBを絶対参照に、行番号は相対参照にするために、複合参照と呼ぶのです。
次に、保証ごとの上乗せ価格を列に合わせるように設定します。
「C2」のセル番号の前をクリックして、F4キーを2回押しましょう。今度は、「2」という行番号の前に$マークが現れます。これで数式をコピーしても、行番号の2は書き換えられなくなり、列番号はセルの位置に合わせて書き換えられるようになります。2000を追加したい行に数式をコピーすれば、「C2」の2000を足す数式になり、1500を追加したい行なら、「D2」の1500を足すようになります。
「=$B3+C$2」という数式が入力できたところで、Enterキーを押して確定させましょう。
「\188,000」と、正しい数値が入力されています。次に、この数式をオートフィルを使ってコピーして、隣の列に入力してみましょう。
この通り、「\186,000」に「\1500」を足した、「\187,500」という正しい数値がちゃんと入力されています。数式を見れば、ちゃんとセルの番号が変わっているのがわかります。
残りの範囲までセルをコピーすれば、この通りちゃんと入力されています。
セル番号を固定する「絶対参照」セルの行か列、どちらか片方を固定する「複合参照」覚えておけば役に立つときがあるでしょう。
セル番号自体を固定したいときはF4キーを1回押す、行を固定するときはF4キー2回押す、列を固定するときはF4キーを3回押す、間違えないよう覚えておくのだぞ。
