Excelで数式のコピーでこまったら 絶対参照と複合参照

Excel

絶対参照とは

Excelで表を作る時は、数式を入力して、それをコピーして制作の手間を省くことをよくやるでしょう。いや、数式の入力とコピーってよくわかんない! という人は、まずはこちらの記事をお読みください。

Excelで計算をしてみよう
Excelで数式を使って計算を行う方法を、基本的なことから解説いたします。

オートフィルを使って数式のコピーをすれば、非常に楽ですが、たまに困ったことが起こります。

時給から給与を計算する表を、作っている途中とします。

上の画像の通り、給与を計算する数式を入力して、これをオートフィルでコピーしようとしたら、

何やらおかしなことになってしまいました。給与の数値が並ぶはずが0だったり謎のメッセージが出てたり、あり得ない数字が出たりしています。

原因を探るために、セルを選択して入力されている数式を見てみましょう。

11行目の「0」となっている、給与のセルの数式です。なぜ「0」になっているかわかるでしょうか?

実は時給を書いているセル、C7の数値をかける箇所が、空白のセルであるC8に書き換わってしまっているのです。空白だから数値がない、すなわち「0」をかけてしまったので、答が0になってしまったのです。

12行目の「#VALUE!」という謎のメッセージが出ているセルの数式です。これもC7の数値をかけるべきところで、C9のセルをかけてしまっています。C9は「給与」という文字が書かれているセルです。数式なのに、文字の情報が混じってしまったせいで、数式が成立しなくなってしまったから、このようなメッセージが現れたのです。

「#VALUE!」は数式が成立しない時に、現れるエラーメッセージだ。せっかくだから覚えておくといいぞ。

13行目に「39200」という数値が出ているのは、C10の「7840」を最後にかけてしまっているせいです。14行目はC11の「0」をかけてしまっています。

なぜこうなってしまうのか? オートフィルで数式をコピーすれば、セルの位置に合わせて、セルの番号を書き換えてくれる仕様となっています。これは便利な時が多いのですが、今回のように困ったことになることもあります。

どうするべきか? 方法はあります。

書き換えてほしくない番号のセルを、「絶対参照」に設定して番号を変えないように設定するのです。

絶対参照なんて、いかにも数学的でややこしそうな言葉が出てきて、これからやることもややこしそうと思われそうですが、そんなことはありません。操作自体は簡単です。

セルをクリックして選択してから、セルの中の数式か、画面上部の数式が表示される欄の中の、固定したいセル番号の前の箇所をクリックしてから、F4キーを1回だけ押してください。

すると、画像のように、列番号である「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回押す、間違えないよう覚えておくのだぞ。

タイトルとURLをコピーしました