セル参照

「3.5.便利な関数や機能の利用」で行なった「式のオートフィル」の解説や、 途中で作業を止めていた「科目ごとの平均」の続編です。

「3.5.便利な関数や機能の利用」、オートフィルを使って、簡単に式をコピーできることを体験しました。 式をオートフィルでコピーすると(通常のコピー＆ペーストでも同じ)、 我々がそうなって欲しいように、自動的に参照範囲が変更されていましたね。

これは、式をコピーする際には、 式の書かれているセルとの相対的な位置関係でセルを参照する機能が、 スプレッドシートに備わっているからです。 これを「相対参照」機能と呼びます。

例えば、「Ｂ７」に書かれている「=SUM(B3:B6)」という式は、 コピーされるときには、

• 「式の書かれるセル(Ｂ７)から見て、 同じ列の４つ上(Ｂ３の、Ｂ７からの相対位置)のセルから、 同じ列の１つ上(Ｂ６の、Ｂ７からの相対位置)のセルまでの合計を求めよ」

という式として解釈されます。
そのために、オートフィルでこの式をコピー＆ペーストすると、それぞれの式は、

• 「式の書かれるセルから見て、 同じ列の４つ上のセルから、同じ列の１つ上のセルまでの合計を求めよ」

という式になりますので、我々の求める式が自動的に書かれることになります。

この機能は、非常に有用です。複雑な式であっても、 一度書くと同じ計算を別の行や列で行なうときには、 単にコピーするだけで良いということになりますので、 大きな表であっても、同様な計算は簡単に行なえることになります。

「相対参照」は、非常に有用なのですが、場合によっては、 それだけではうまく行かないことがあります。

その一例が、科目ごとの平均を求める時です。
科目ごとの平均は、科目ごとの合計と、受験者数を既に求めていますので、 以下のようにすればできそうです。

1. まず、セル「Ｂ８」で、算数の全生徒の合計である「Ｂ７」を、 受験者数「Ｂ１０」で割り、Enter 等を押すことで、 算数の平均を表示するようにします。





2. 次に、セル「Ｂ８」をアクティブセルにし、 オートフィルハンドルを「Ｆ８」までドラッグします。 これで、科目ごとの平均はすべて表示できるはずです。

ところが、結果を見ると分かりますように、オートフィル (コピー＆ペーストでも同じ)を行なったところは、エラーになっています。 どうしてでしょう？

その理由を調べるために、「Ｆ８」セルに書かれた式を見てみると、 下図のようになっている事が分かります。

分子は、英語の合計点数を表すセル「Ｆ７」になっていますので問題ありませんが、 分母がおかしいですね。受験者数「Ｂ１０」で無ければいけないのに、 「Ｆ１０」になっています。 (これでは、数値328を「平均」という文字では割ることになり、 当然エラーとなりますね。)

でも、良く考えてみて下さい。前節で説明したように、 「式をコピーするときには、式中のセル参照は、 相対参照で行なわれる」ので、当然の結果なのです。

コピー元の「Ｂ８」には、「＝Ｂ７／Ｂ１０」と書いたのですから、

• 「式の書かれているセル(Ｂ８)と同列の一つ上のセル(Ｂ７)を、 同列の２つ下のセル(Ｂ１０)で割る」

という式をコピーすることになります。
ですから、「Ｆ８」にコピーされた式も上のコピー元の式と同じで、

• 「式の書かれているセル(Ｆ８)と同列の一つ上のセル(Ｆ７)を、 同列の２つ下のセル(Ｆ１０)で割る」

になっており、正常な動作をしていることになります。
でも、これでは平均値が求まりませんね。

上での問題は、

• 式の分母では、みな、受験者数を表す「Ｂ１０」を参照したいのに、 相対参照なので、自動的に参照セルのセル番地を計算して変更してしまう

ことに、起因しています。 スプレッドシート以外のソフトでは、 コピーしても自動的に計算はされませんので、 こんな問題は起きないですね。

つまり、一般的に言うと、

• コピーしても(正確には、コピーして貼り付けても)、 参照セルを自動計算するのを止めて同じセルを参照することができる

と、問題が解決することになります。

この要求に答えるのが、「絶対参照」機能です。
セルを参照するときに、 式の書かれているセルからの相対的な位置でなく、 式の書かれているセルとは関係の無い、 絶対的な位置を参照する機能です。

「絶対参照」をするには、 セル番号を「Ｂ１０」のよう、書くのではなく、 行番号並びに列番号(記号)の前に「＄」記号をつけ、 「＄Ｂ＄１０」のように書きます。
StarSuite Calcでは、この絶対参照への変更は、直接「＄」を付けるのではなく、 Shiftを押しながらＦ４キーを押すことで行なうことができます。

それでは、この機能を使って、科目ごとの平均を求めてみましょう。

分かりやすくするために、まずは、先に書いたものを全部消去しましょう。

セル内のデータの消去は、
  消す対象のセルを選択し、以下のどれかを行います。

• Back spaceキーを押す
• Ctrlキーを押しながら Xキーを押す
• 右クリックして「切り取り」を選択する
• Deleteキーを押した後、「すべて削除」を選び「OK」をクリックする
• 右クリックして「内容の削除」を選んだ後、 「すべて削除」を選び「OK」をクリックする

「Ｂ８」から「Ｆ８」をドラッグして選択し、 上のどれかを行なって消して下さい。

きれいになったところで、以下のようにします。

1. まず、セル「Ｂ８」で、「=」を入力した後、 算数の全生徒の合計である「Ｂ７」をクリックし、続けて、 割り算演算子の「/」を入力します。そして、 受験者数「Ｂ１０」をクリックします。 ここまでは、以前と同じです。





2. まだ、「Ｂ１０」が選ばれている間に、 シフトキーを押しながらF4キーを押します。これにより、 「Ｂ１０」が、 絶対参照の「＄Ｂ＄１０」に変わります。 変わったらそのまま確定します。
(註) もし間違って、F4キーを複数回押すと、「＄」が一つになったり、 無くなったりします。そのときは、更に何度か押すことで元に戻ります。





3. 最後に、セル「Ｂ８」をアクティブセルにし、 オートフィルハンドルを「Ｆ８」までドラッグします。 これで、科目ごとの平均はすべて表示できました。

以上のような手順で、教科ごとの平均を求めることができました。
式がちゃんとコピーされたか確認するために、「Ｆ８」を見てみると、 下図のように、 英語の合計(Ｆ７)を受験者数(Ｂ８)で割る式となっていることがわかります。

せっかくですから「3.5.便利な関数や機能の利用」行なったように、平均点を整数表示にしましょう。 3.5では、セルの属性を変更しましたが、ここでは、 roundという新しい関数を使ってみましょう。

関数 round()は、 以下の機能をもっています。

• 第１引数に、表示する数値を置き、 第２引数に、小数点以下の桁数を指定することで、 数値の表示形式を変更します。

ここでは、整数のみ表示したいので、小数点以下は０桁とすれば良いですね。

そこで、実際の変更は、「Ｂ８」をアクティブセルにした後、 数式入力ボックスで、
「=round(B8/$B$10;0)」と入力して、確定した後、 「Ｂ８」を「Ｆ８」までオートフィルでコピーすれば完成です。

ついでに、 同じ要領で各人の１科目あたりの平均点も「Ｈ」列に求めておきましょう。

教育一さんの科目あたりの平均点を表す「Ｈ６」が、 合計点数の「Ｇ６」を「Ｂ１１」で割られ、その値をround関数で、 小数点以下が四捨五入されていることが分かります。


(註) 式をコピーしない時は、 「相対参照」なのか「絶対参照」なのかを、 気にする必要はありません。 「＄」が付いていても、付いていなくても同じセルを参照しています。

(注意) 式をオートフィルやコピー＆ペーストしたときは、 必ずコピー先のセルをいくつか確認して下さい。 そして、自分が思っている通りにコピーされていなかったときは、 「絶対参照」でうまく行かないか考えて下さい。

(注意) 「相対参照」と「絶対参照」が区別されるのは、 「式をコピー」して利用するときだけです。 コピー＆ペーストが終わってしまった後は、 「相対参照」と「絶対参照」の区別は無くなってしまいます。 (正確には、「そのセルを更にコピーしてペーストを行なわなければ」 ということになりますが。)

例えば、下の例では、受験者数を表す「Ｂ１０」を一旦、「切り取り」 (Ctrlを押しながらXを押す)をおこない、すぐに、「Ｅ１１」に「貼り付け」 (Ctrlを押しながらVを押す)を行なっています。

切り取られたときには、科目ごとの平均は、 それぞれの式の分母にあたるセルが空になったので、 すべてエラーになっています。しかし、 別の場所(セル)に貼り付けても、 元のように正確に計算が行なわれています。
試しに、「Ｆ８」の式を確認してみると、分母は絶対参照のままですが、 こちらがなって欲しいように自動的にセル番地が変更されていることが分かります。