数式を入力するセルの数だけいちいち式を入力していては効率が悪い!そんな時どうするか? セルに入力した数式も文字データや数値データと同様にコピーして使用することが可能です。実際に表で 計算をする場合にはセルの数値を用いて計算式を作成するよりも、セル参照で指定する方が一般的です。 セルの数値で数式を作成すると、数式の参照元データが変わっても変更されたデータで再計算が出来なく なりますが、セル参照で数式を作成しておけばセルの数値が変化してもそれに合わせて再計算出来ます。 ここでは数式をコピーする際に必ず発生する相対参照と絶対参照について説明してゆきます。 |
先ずは下の図を見てみましょう。
A1:C4の範囲で金額を求める表が作成されています。
先ずはC2に金額を求める為の数式「単価」×「数量」を入力。
左図のケースでは"=A1*B1"という数式が入ります。
ここまでは初級編でも解説してある通り、最初に"="をつけて
入力すればEXCELが数式と判断しているので計算結果が
"250" と表示されます。
今度はC2の数式をオートフィルでC4までコピーします。
フィルハンドルをドラッグして、C4でドロップします。
この作業で数式をコピーしたことになります。
さて、コピーされた数式はどうなっているでしょうか?
数式をコピーすると計算結果が各セルに表示されました。
セルに設定された数式を確認するにはメニューバーの「ツール」から
「オプション」→「表示」タブ→ウィンドウオプションの「数式」チェックボックス
をオンにします。すると、次の図の様に数式を一目で確認出来ます。
右の図で言うと、C2には「2つ左のセルと1つ左のセルを乗算せよ」
という数式が設定されています。
C2の数式を下のセルにコピーすると、コピー先の数式のセル参照は
コピーした位置に合わせて自動的に置き換わります。
つまり、相対参照とは数式を作成するセルを基点とし、セル参照を座標で指定する方法と言えます |
◆絶対参照◆
簡単な例で考えてみましょう
左の図は商品A〜Cの消費税額を求める表です。
ここでは先ず、C3に商品Aの消費税額を求める数式、
「商品金額」×「消費税率」を入力します。
C3には"=B3*B1"という数式が入りますね。
さて、この数式をC5までコピーしたらどうなるでしょうか?
先ほどの相対参照の時と同様にして、
C3の数式をオートフィルを使ってC5までコピーします。
数式の計算結果は一体どうなるでしょうか?
次の図で検証してみましょう。
C4とC5の計算結果にエラーが出てしまいました。
C4の「#VALUE!」はこのケースでは数式を確立する
上で必要なセル参照が数値では無く文字列になっている為、
正しい計算結果を算出不能であることを示しています。
又、C5は列幅に対してセルのデータが入りきれない場合に
でるエラーです。列幅を広くする事でデータを表示できます。
では、どうしてエラーが出たのでしょう?
それではセルC3の数式を確認してみましょう!
C5をアクティブにした状態でキーボード上段の
F2キーを押してみて下さい。
右の図の様に数式の参照元が色分けされて
確認できる様になります。
これを「カラーリファレンス」といい、計算式で参照
している実際のセル範囲と式のセル参照を色で
表示する機能です。
※セル範囲とセル参照は右上図でも確認できる通り、同色で対応しています。
式を編集したり、参照元を参照する時に非常に便利な機能です。
参照範囲に誤りがある場合には 色枠線をドラッグしなおすことで参照範囲の変更修正が可能です。
●エラーの出た数式の参照元を同様に確認してみましょう!●
先ず、C4の数式の参照元を確認してみましょう!
このセルの数式に必要なセル参照が数値では無く
文字列になっている…と先ほど述べましたが、
カラーリファレンスで確認してもわかりますね(^^)
消費税率のB1セルを参照せずにB2の「商品金額」
という文字列を参照しています。
これでは計算結果がエラーになるわけです(^^;
C5の数式の参照元を確認してみましょう!
ここでも消費税率のB1セルを参照せずにB3の
「10,000」を参照しています。
これでは計算結果が20,000×10,000で200,000,000となり
通常の列幅では表示しきれない訳ですね。
どうしてこんなことが起こってしまったのでしょうか?
★理由は相対参照で数式をコピーしてしまったからです。
カラーリファレンスでも確認出来るように数式のセルの参照元がコピーした位置に合わせて
自動的に置き換えられたのが原因となっています。
C3に入力した数式は「一つ左のセルとその二つ上のセルを掛け合わせなさい」という数式です。
それをそのままコピーしてしまうと、商品金額は自動的に置き換えられてもいいのですが、
消費税率のセル参照が自動的に相対参照で一つずつ下にずれてしまいました。
これでは正しい計算が出来ません…。
ここでは消費税率のセル参照を固定しなくてはなりませんね。
では、どうするのか?それをこれから解説していきましょう!
では、早速C3に数式を入力していきます。
"=B3*B1"まで入力するのは同じです。
ここから、消費税率のB1セルを参照元として
固定させる為の処理をします。
その処理もキーボード操作のみで出来る簡単
なものなので是非覚えてくださいね。
●このタイミングでキーを押します●
すると、消費税率セルのB1に$マークが付きます。
この$マークこそ、参照セルを固定させる為の重要なマークです。
左の図を見てみましょう。
Bと1の前のそれぞれに$マークが付いています。
これはB列を固定しなさい、1行目を固定しなさい…つまり
セルのB1を参照元として絶対に動かさずに固定しなさいという
命令になります(^^)
左図のように$B$1の状態にしてENTERキーを押します。
今度はエラーも出ず、正しい計算結果が導かれましたね。
それでは、本当に参照元のB1セルが固定されているか
どうかをカラーリファレンスで確認してみましょう!
C4セルの数式をカラーリファレンスで見てみましょう。
消費税率のB1セルは絶対参照として間違い無く固定されてますね!
$マークが行・列両方にきちんと設定されているのが確認できます。
商品金額のセル参照が自動的に置き換えられても、消費税額の
セル参照は置き換えられてはいません。
C5セルはどうでしょうか?
C4同様に消費税額はきちんと固定されています。
カラーリファレンスで確認してもB1が絶対参照になっています(^^)
数式バー上でもB1セルは絶対参照に設定されています。
このように数式を作成する上で参照元として動かしたくないセル
がある場合には絶対参照を使うと計算エラーが出なくなります。
覚えておくと便利ですので是非覚えてください!
絶対参照とは、常にこのセルを参照しなさい!という参照方法を言います。 相対参照とは異なり、数式をコピー・移動しても、常に参照先のセルが固定されます。 この為、一つの表で共通に使う項目(税率や利率など)には絶対参照を指定するとよいでしょう |