エクセルファイルのダウンロード
HOME 便利テク集
今回は便利テクというよりは、きっちり理解しておいた方が良いと思われる、セル参照について、お話したいとおもいます。セル参照には、絶対参照と相対参照があります。このことを説明するために、以下の表データを使いたいと思います。以下は、とある学校のテスト結果だと思ってください。
エクセルファイルのダウンロード
説明するにあたって、以下のデータを集計していきたいと思います。
まず、初めに英語の平均値を求めます。C3セルに、平均値を算出するAVERAGE関数を設定し、引数には、英語のテスト結果である、セル範囲:C6:C17を指定します。この、C6:C17への参照を相対参照といいます。
続いて、C3セルをコピーし、D3セルを【右クリック】して、【貼り付けオプション】で【数式】の指定して貼り付けます。
C3セルと、D3セルの参照しているセルを表示すると以下のようになります。
※リボンの【数式】-【参照元のトレース】で参照を青線表示できます。【トレース矢印の削除】で表示を消去できます。
このようにC3からD3へ参照元が、列方向へ＋1移動した場合に、参照先も、C6:C17からD6:D17へ、列方向へ＋1移動するような、参照のことを、相対参照といいます。
この要領で、他の3科目(数理社)についても、平均点を算出してください。今回は、相対参照を説明するために、英語の平均点に設定されている数式を、国語の平均点のみに、コピーしましたが、一気に、他の科目の平均点に、コピーしても構いません。
続いて、5科目全ての平均点を算出します。平均値の算出には、各科目ごとの平均値の時と同じで、AVERAGE関数を使います。算出の対象範囲には、下図のように全ての得点結果を指定してください。
次は、生徒ごとの差分得点(各科目の得点－平均点)を計算します。まずは、近松さんの英語の得点と、平均点との差分を求めるために、H6セルに式: C6 - C$3 を設定します。この式に含まれる $ 記号が、絶対参照を表しています。今回の場合、行番号3の前に $ が、付与されていますので、行方向への絶対参照となります。
H6セルの式をコピーし、H7～H17セルを【右クリック】して、【貼り付けオプション】の【数式】を指定して、貼り付けます。これで、各生徒のΔ英語の値が、算出できたと思います。ここで、もう少し、行方向への絶対参照について、説明したいと思いますので、以下の図を確認してみてください。これは、英語の平均点C3セルを参照しているセルを表示したものです。
このように、 $ を付与したことにより、式を設定したセルの、位置が行方向に変化しても、参照している英語の平均値の位置が変わっていないことが分かります。このことを、絶対参照(行方向)といいます。
同様に、他の科目についても、Δ値を算出してみてください。
最後に、生徒ごとの差分得点2(各科目の得点－全科目の平均点)を計算します。まず、近松さんの英語の得点と、全科目の平均点との差分を求めるために、M6セルに式: C6 - $I$3 を設定します。今度は、行番号3だけではなく、列番号Iの前にも、 $ が、付与されていますので、行方向と列方向への絶対参照となります。
M6セルの式をコピーし、M6～Q17セルを【右クリック】して、【貼り付けオプション】の【数式】を指定して、一気に貼り付けます。これで、全生徒の科目ごとの、Δ2値が、算出できているはずです。確認のために、全科目の平均点を参照しているセルを表示すると以下となります。
これから、列と行番号のいずれにも、 $ を付与したことにより、式を設定した全てのセルが、全科目の平均値を絶対参照(列と行方向のいずれも)していることが分かります。
以上のことより、エクセルでは、式に含まれる、セル番号に $ を付与することにより、セルの絶対・相対参照を使い分けることが出来ます。
エクセルには、【条件付き書式】という非常に便利な機能が実装されています。この機能を使いこなすには、セル参照の理解が必要不可欠だと思います。また、機会があれば、【条件付き書式】についても、解説したいと思いますので、今回の内容を、きっちり理解しておいてください。
サンプルのデータは、私の創作によるものです、サンプルデータに含まれる個人名などの情報は、実在の人物とはまったく関係ありません。
ご意見・ご要望等ありましたら、画面最下部のメールアドレスまでご連絡ください。
