マルチメデア実習室
ここでは、[セル参照]と[オートフィル]が如何に重要な役割をしているか、 「北海道教育大学 広重研究室」の言を借りて説明いたします。
ずばり「計算をするため」です。「表を作るため」ではありません。 いわんや、きれいに飾り立てた見栄えのよい表を印刷するためでもありません。
もちろん、表の形で計算をしますから、表も作れます。印刷もできます。きれいに飾り立てることもできます。
しかし、それらは副次的な「おまけ」であり、本来の Excel の威力は「計算をすること」によって強力に発揮されるのです。 計算をしない、ただ決まった文字を並べただけの表をつくるのなら、Excel を使う必要はありません。Word でも作れます。
ここでは大胆に、2つだけ挙げます。この2つが、私は最も重要だと思います。
この2つを押さえれば、あとはこれらの応用で理解できます。
1.セルの参照
2.オートフィルの動作
特に、2. は強力です。
「参照」などというと恐ろしげですが、むずかしいことはありません。 「このセルには、どこそこのセルの値を入れてね」基本はこれだけです。
具体的にこの「どこそこ」をどうやって書き表すか、とか、 「このセルからこのセルまで全部」などの効率のよい表し方は、・・・次回。
このオートフィルは、とにかく強力です。これを使わなければ Excel のウマミをほとんど味わえません。
セルにデータ(数値,文字,数式)を入れます。
(入れただけでは、ただの表にすぎません。)
セル参照で、データを出して使いましょう。
その為のワークシート関数がデフォルトで238、
アドイン関数(DL)も100近く用意されてます。
A | B | C | D | E | F | G | H | |
1 | 高校入試 | 国語 | 数学 | 英語 | 合計点 | 平均点 | 割合 | 合格不合格判定 |
2 | 山田太郎 | 80 | 90 | 80 | 250 | 83.3 | 83.3% | , |
3 | 川田花子 | 75 | 70 | 70 | 215 | |||
4 | 草田一郎 | 60 | 85 | 65 | 210 | |||
5 | 木田一美 | 50 | 80 | 80 | 210 | 70.0 | 70.0% | |
6 | 上田春子 | 65 | 75 | 80 | 220 | |||
7 | 3科目満点 | [100] | [100] | [100] | 300 | |||
7 | 合格基準 | 国語か数学のどれかが | 80 | 点以上の人 | ||||
7 | 合格基準 | 若しくは英語か | 80 | 点以上の人 |
セルを参照しただけで、[F4]操作は不要です。(4回押すことも有ります。)
数式の入ったセルをオートフィルで[下(行を替える)]に、または、[右(列を替える)]に移動すると、
それに対応してセル番地は自動的に変化します。
参照先が平行移動しているのが分かります。
下表をコピーして、エクセルの[A1]セルに貼り付け、お試し下さい。
高校入試 | 国語 | 数学 | 英語 | 合計点 | 平均点 | 割合(%) | 合格不合格判定 (IF関数にOR関数をネスト) |
山田太郎 | 80 | 90 | 80 | =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),"合格","不合格")) |
川田花子 | 75 | 70 | 70 | 215 | |||
草田一郎 | 60 | 85 | 65 | 210 | |||
木田一美 | 50 | 80 | 80 | 210 | 70.0 | 70.0% | |
上田春子 | 65 | 75 | 80 | 220 | |||
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]セルまで、フィルハンドル コピーします。
トーレス図で、割られる側(合計点)の参照セルは平行移動しますが、 割る側のピンク色の[300]は固定のため、トーレスが集中しています。
トーレス図で、引数に使われたデータ(国語と数学に●点有り)の参照セルは平行移動 しますが、参照元のピンク色の[80]は固定のため、トーレスが集中しています。
参照先のピンク色の[80]は固定のため、トーレスが集中しています。
上の図で、いづれも、計算式は各列のデータ先頭行を対象に立てられ、[オートフィル]で以下各行に数式をコピーしています。
[オートフィル]機能なしでは、2〜300有るエクセル関数の機能が発揮されません。
また、[オートフィル]を可能にするため、[絶対参照]や[複合参照]が存在するのです。
1.[列のみ固定] 下表の[$A]は、A列が固定で[2]の行は、[3][4]・・行と移動します。
2.[行のみ固定] 下表の[B]は、列が[C][D]・・列と移動し、[$1]で1の行は、固定します。
$記号の付け方。
セル参照で番地を入力したら、直ちに[F4]キーの操作で、
複合参照にしたい場合、2回押すと、列は相対参照、行は絶対参照になり、$記号が付きます。
同様に[F4]キーの操作で、3回押すと、列は、絶対参照で$記号が付き、行は相対参照になります。
下表をコピーして、エクセル[A1]セルに貼り付けて、オートフィルで計算式をコピーすると掛け算が表示されます。
掛け算 | 2 | 4 | 6 | 8 | 10 | |
3 | =$A2*B$1 | |||||
5 | ||||||
7 | ||||||
9 | ||||||
11 |
オートフィル:+のフィルハンドルでコピー。上下左右に数式をコピーした図が下図です。