マルチメデア実習室

エクセルのキモ[関数]を支える、[セル参照]と[オートフィル]

ここでは、[セル参照]と[オートフィル]が如何に重要な役割をしているか、 「北海道教育大学 広重研究室」の言を借りて説明いたします。

何故エクセルを使うか

ずばり「計算をするため」です。「表を作るため」ではありません。 いわんや、きれいに飾り立てた見栄えのよい表を印刷するためでもありません。
もちろん、表の形で計算をしますから、表も作れます。印刷もできます。きれいに飾り立てることもできます。
しかし、それらは副次的な「おまけ」であり、本来の Excel の威力は「計算をすること」によって強力に発揮されるのです。 計算をしない、ただ決まった文字を並べただけの表をつくるのなら、Excel を使う必要はありません。Word でも作れます。

「手順」(=数式)のキモ

ここでは大胆に、2つだけ挙げます。この2つが、私は最も重要だと思います。
この2つを押さえれば、あとはこれらの応用で理解できます。
 1.セルの参照
   2.オートフィルの動作
     特に、2. は強力です。

セルの参照

「参照」などというと恐ろしげですが、むずかしいことはありません。 「このセルには、どこそこのセルの値を入れてね」基本はこれだけです。
具体的にこの「どこそこ」をどうやって書き表すか、とか、 「このセルからこのセルまで全部」などの効率のよい表し方は、・・・次回。

オートフィルの動作

このオートフィルは、とにかく強力です。これを使わなければ Excel のウマミをほとんど味わえません。

以上、北海道教育大学 広重研究室ホームページより抜粋(一部加工)


ライン

セルにデータ(数値,文字,数式)を入れます。
(入れただけでは、ただの表にすぎません。)

セル参照で、データを出して使いましょう。
その為のワークシート関数がデフォルトで238、
アドイン関数(DL)も100近く用意されてます。

ライン

[セル参照]に3つの方法

セルの[絶対参照]と[相対参照](灰色が相対参照)

ABCDEFGH
1高校入試国語数学英語合計点平均点割合合格不合格判定
2山田太郎80908025083.383.3%,
3川田花子757070215
4草田一郎608565210
5木田一美50808021070.070.0%
6上田春子657580220
73科目満点[100][100][100]300
7合格基準国語か数学のどれかが80点以上の人
7合格基準若しくは英語か80点以上の人

相対参照

セルを参照しただけで、[F4]操作は不要です。(4回押すことも有ります。)
数式の入ったセルをオートフィルで[下(行を替える)]に、または、[右(列を替える)]に移動すると、 それに対応してセル番地は自動的に変化します。

参照先のト−レス図

参照先が平行移動しているのが分かります。

セルの[絶対参照](ピンク色)と[相対参照]の[関数式表示]

下表をコピーして、エクセルの[A1]セルに貼り付け、お試し下さい。

高校入試国語数学英語合計点平均点割合(%)合格不合格判定 (IF関数にOR関数をネスト)
山田太郎809080=SUM(B2:D2)=AVERAGE(B2:D2)=E2/$E$7=IF(OR(B2>=$E$8,C2>=$E$8),"合格",IF(OR(B2>=$E$8,C2>=$E$8,D2>=$E$9),"合格","不合格"))
川田花子757070215
草田一郎608565210
木田一美50808021070.070.0%
上田春子657580220
3科目満点[100][100][100]300
合格基準国語か数学のどれかが80点以上の人
合格基準若しくは英語が80点以上の人

但し、オートフィルする前に、セル[G2]には、予め「パーセンテージ、0.1桁表示の設定をしておかなければなりません。
設定は、[書式]メニュー →[セル]→[表示形式]タブをクリック[パーセンテージ]をクリックして、小数点以下の桁数を[1]にします。


絶対参照

$マークの付け方
セル参照で番地を入力したら、直ちに(答えが得られない時で気が付いた時でもかまいませんが) [F4]キーの操作で、絶対参照にしたい場合、
[F4]キーを 1回押すと、[列]、[行]の セル番地両方に半角の$記号が付き、参照先が固定されます。
上の表[=E2/$E$7]で、[$E$7]は、ピンク色の値[300]のところになりますが、参照先に固定されました。

セル番地や$記号は、マウスで対象セルをクリックしなくても、キーボードから直接入力することも 出来ます。(臨機応変でやり易い方法で行って下さい。また、セル内で編集しなくても、数式バーで編集 しても構いません。)

[G2]セルの( =E2/$E$7 )を[G2]から[G6]セルまで、フィルハンドル コピーします。

参照元のトーレス図 1

トーレス図で、割られる側(合計点)の参照セルは平行移動しますが、 割る側のピンク色の[300]は固定のため、トーレスが集中しています。

参照元のトーレス図 2

トーレス図で、引数に使われたデータ(国語と数学に●点有り)の参照セルは平行移動 しますが、参照元のピンク色の[80]は固定のため、トーレスが集中しています。

参照先のトーレス図 3

参照先のピンク色の[80]は固定のため、トーレスが集中しています。

上の図で、いづれも、計算式は各列のデータ先頭行を対象に立てられ、[オートフィル]で以下各行に数式をコピーしています。
[オートフィル]機能なしでは、2〜300有るエクセル関数の機能が発揮されません。
また、[オートフィル]を可能にするため、[絶対参照]や[複合参照]が存在するのです。

セルの複合参照 (2種類有ります。)

1.[列のみ固定] 下表の[$A]は、A列が固定で[2]の行は、[3][4]・・行と移動します。
2.[行のみ固定] 下表の[B]は、列が[C][D]・・列と移動し、[$1]で1の行は、固定します。

複合参照

$記号の付け方。
セル参照で番地を入力したら、直ちに[F4]キーの操作で、
複合参照にしたい場合、2回押すと、列は相対参照、行は絶対参照になり、$記号が付きます。
同様に[F4]キーの操作で、3回押すと、列は、絶対参照で$記号が付き、行は相対参照になります。

下表をコピーして、エクセル[A1]セルに貼り付けて、オートフィルで計算式をコピーすると掛け算が表示されます。

掛け算246810
3=$A2*B$1
5
7
9
11

オートフィル:+のフィルハンドルでコピー。上下左右に数式をコピーした図が下図です。

トップページへ戻る     ページのトップへ     EXCEL関数特集-総目次へ   総目次へ戻る

inserted by FC2 system