KAWANO's PukiWiki Plus! - Lecture/InfoPrac2003/7th

情報処理演習 第7回

• 今回のテーマ「計算式と関数」「相対参照と絶対参照」
  • 関数を探して、計算式をつくれるようになる
  • 相対参照と絶対参照の２つのセルの参照の使い分けをマスターする
  • 関数のネスト（入れ子：関数の引数に関数を使う）をマスターする

前回までの課題の提出

こちらの連絡事項を見て、前回課題の提出状況を確認してください。

• まだ提出していない人は、課題を作成して提出してください。
• 再提出するように指示があった人は、［マイドキュメント］にあるファイルの修正をして、提出用フォルダに再提出してください。

今回のファイル

今回は、 あるクラスの成績表を作成します（学生の名前には阪神タイガースの選手名をお借りしました）。 「出席点」「課題点」「期末テストの点」の3つの評価から、 いくつかの関数を使って、成績を集計・判定します。

&ref(): The style ref(filename,pagename) is ambiguous and become obsolete. Please try ref(pagename/filename);をクリックして、データファイルを［マイドキュメント］に保存してください。保存できたら、ファイルを開いてください。 なお、各評価の点数は、乱数機能を使って作っているので、ひとり一人数字が違います。また、何か操作をすると、数字が変わりますが、実習と課題には影響ありません。

計算式と関数

計算式

「計算式」とは、数字やセルの値を使って計算や処理をするものです。四則演算（加減乗除）のような計算だけではなく、関数を使った複雑な処理を記述できます。 書き方のルールは簡単です。最初に「=」を入力すると計算式となります。たとえば、A1セルに入力されている円の半径を使って、円周や円の面積を計算するには、次のような数式になります（3.14は円周率）。

=2*A1*3.14 （円周）
=A1*A1*3.14 または A1^2*3.14 （円の面積）

計算式に使える演算子は次のとおりです。括弧()を使うこともできます。

関数

「関数」とは、数学ででてくる関数と同じ意味で、 「数字や文字など『値』を与えると、いろいろな『処理』をして、新しい値を『結果』として返す」ような仕掛けのことです。たとえば、自動販売機の仕掛けも関数に例えることができます。自動販売機という「関数」に、お金と選択した商品ボタン「値」を処理させると、ジュースやコーヒーという「結果」が返ってきます。

お金・商品ボタン（値）−＜与える＞→自動販売機（関数）−＜返す＞→コーヒー（結果）

関数の場合は、数字や文字のデータだけでなく他のセルの番地を、引数として関数に与えると、結果が返ってきます。

引数（数字や文字、他のセルの番地）−＜与える＞→（関数）−＜返す＞→（結果）

具体的は、次のような計算式を入力します。引数の数は、関数によって変わります。

=関数名(引数1, 引数2, 引数3, ...)

関数のネスト（入れ子）

セルに入力できる計算式は１つだけです。しかし、いくつかの関数を組み合わせたい場合があります。そのようなときには、ある関数の引数として別の関数を使う、関数のネスト（入れ子）をします。 たとえば、関数Aの2番目の引数に関数Bを使う場合は、次のような計算式を入力します。

=関数A(引数A-1, 関数B(引数B-1, 引数B-2, ...))

成績表の作成（基本的な関数の利用）

番号の入力

まず、表の最初に新しい列を1列挿入します。

1. A列の列番号をクリックし、A列全体を範囲指定する
2. メニューバーから「挿入」→「列」を選択する
3. 列を挿入できたら、A1セルに「番号」と入力する

次に、A列に連番の番号を入力します。連番を入力するには、「オートフィル」を使います。1〜50版までの番号を入力します。

1. A2セルに「1」、A3セルに「2」と入力する
2. マウスをドラッグして、A2〜A3セルを範囲指定する
3. 範囲指定した枠の右下の四角形（フィルハンドル）をマウスでA51セルまでドラッグする
   

個人成績の合計

個人の成績の合計を計算しましょう。 まず、F1セルに「合計」と入力してください。

次に、合計を求める計算式を入力します。 出席点と課題点と期末テストの点を足したものが合計になります。 ここでは、関数SUMを使います。

• SUM（合計を計算する）
  • 書式 : SUM(数値1, 数値2, ...)
  • 引数 : 数値1, 数値2, ... ：合計を計算するセルの範囲や数字

今まではオートSUM機能を使っていましたが、今回からは他の関数と同じように操作することにします。 関数を利用するには、次のように操作します。

1. F2セルをクリックします。
2. 「関数の挿入」ボタンをクリックします。
   
3. 利用する関数を選びます。
   利用したい関数の分類を選んだあと、関数名を選びます。「関数の分類」から「数学／三角」を選び、「関数名」から「SUM」を選びます。「OK」ボタンをクリックします。
   
4. 次に、関数に与える引数を設定します。ここでは出席点、課題点、期末テストの点のセルを指定します。
   1. 「数値1」の入力欄の中を削除します。
   2. C2〜E2のセルをマウスをドラッグして範囲指定します。
   3. 「数値1」に「C2:E2」と設定されているのを確認できたら、「OK」ボタンをクリックします。
      
5. F2セルに「=SUM(C2:E2)」という計算式が設定されます。
   

相対参照

次に、F2セルに入力した計算式をF3〜F51セルにコピーします。

1. F2セルをクリックして、アクティブにします。
2. F2セルの枠の右下の四角形（フィルハンドル）をマウスでF51セルまでドラッグします。
3. F3〜F51セルに計算式がコピーされます。
   

ここで、F3セルにコピーされた計算式を見てみましょう。「=SUM(C3:E3)」となっていて、コピー元の「=SUM(C2:E2)」という計算式とは異なっています。これはどういうことでしょうか？

F2セルの計算式の意味は、次のような意味になります。

「計算式のあるセル（F2）から3つ左のセル（C2）から、計算式のあるセル（F2）から1つ左のセル（E2）までの合計を計算する」

つまり、計算式のあるセル自身を基準にしたセルの位置（相対的な位置）の情報を使って計算しています。したがって、F2セルの計算式をコピーしたF3セルでは、次のような意味になります。

「計算式のあるセル（F3）から3つ左のセル（C3）から、計算式のあるセル（F3）から1つ左のセル（E2）までの合計を計算する」

このような、自分自身のセルを基点としたセルの位置情報をつかい、コピーすると参照するセルが変わるような、 相対的なセルの参照のしかたを「相対参照」といいます。

全体成績の計算

採点する人数や平均点・最高点・最低点を計算します。 まず、B53セルに「人数」、B54セルに「平均点」、B55セルに「最高点」、B56セルに「最低点」と入力してください。

次に関数を使って計算をします。使用する関数は、COUNT, AVERAGE, MAX, MINです。

• COUNT（数値が入ったセルの個数を計算する）
  • 書式 : COUNT(数値1, 数値2, ...)
  • 引数 : 数値1, 数値2, ... ：個数を計算するセルの範囲
• AVERAGE（平均を計算する）
  • 書式 : AVERAGE(数値1, 数値2, ...)
  • 引数 : 数値1, 数値2, ... ：平均を計算するセルの範囲
• MAX（指定された範囲の最大の数値を返す）
  • 書式 : MAX(数値1, 数値2, ...)
  • 引数 : 数値1, 数値2, ... ：最大を求めるセルの範囲
• MIN（指定された範囲の最小の数値を返す）
  • 書式 : MIN(数値1, 数値2, ...)
  • 引数 : 数値1, 数値2, ... ：最小を求めるセルの範囲

では、次のようにして計算をします。

1. C53セルをクリックしてアクティブにします。
2. 「関数の挿入」ボタンをクリックします。
   
3. 利用する関数を選びます。
   利用したい関数の分類を選んだあと、関数名を選びます。「関数の分類」から「統計」を選び、「関数名」から「COUNT」を選びます。「OK」ボタンをクリックします。
   
4. 次に、関数に与える引数を設定します。
   1. 「数値1」の入力欄の中を削除します。
   2. C2〜C51のセルをマウスをドラッグして範囲指定します。
   3. 「数値1」に「C2:C51」と設定されているのを確認できたら、「OK」ボタンをクリックします。
      
5. C53セルに「=COUNT(C2:C51)」という計算式が設定されます。

同じような操作をして、C54セルにAVERAGE関数で平均点を、C55セルにMAX関数で最高点を、C56セルにMIN関数で最低点を計算してください。計算できると次のような計算式が設定されます。いずれの関数も分類は「統計」です。

• C54セル : =AVERAGE(C2:C51)
• C55セル : =MAX(C2:C51)
• C56セル : =MIN(C2:C51)

これで出席点の全体成績が計算できました。 これらの計算式をコピーして、課題・期末テスト・合計の全体成績（D53〜F56）も計算してください。 上の「相対参照」の説明を参考にして、マウスを使って、計算式をコピーしてください（ヒントは、まずC53〜C56までのセルをマウスで範囲指定します）。

成績表の作成（さまざまな関数の利用）

ある条件の人数を数える

ここでまでに紹介した関数（SUM, COUNT, AVERAGE, MAX, MIN）は、オートSUMからも利用できる、基本的な関数です。ここからは、少し複雑な処理をする関数を紹介します。

出席・課題・期末テストの各評価について、60点以上の人数を計算しましょう。 特定の条件を満たすセルの数を数える関数COUNTIFを使います。

• COUNTIF（指定された範囲のセルのうち、検索条件に一致するセルの個数を返す）
  • 書式 : COUNTIF(範囲, 検索条件)
  • 引数 : 範囲 : 個数を求めるセルの範囲
  • 引数 : 検索条件 : 個数を求めるセルの検索条件

検索条件には、次のような比較演算子を使います。ここでは、「60点以上」という条件があるので「>=60」が検索条件になります。

まず、B57セルに「60点以上の人数」と入力してください。 そして、次のようにして計算をします。

1. C57セルをクリックしてアクティブにします。
2. 「関数の挿入」ボタンをクリックします。
3. 利用する関数を選びます。
   利用したい関数の分類を選んだあと、関数名を選びます。「関数の分類」から「統計」を選び、「関数名」から「COUNTIF」を選びます。「OK」ボタンをクリックします。
   
4. 次に、関数に与える引数を設定します。
   1. 「範囲」の入力欄の中を削除します。
   2. C2〜C51のセルをマウスをドラッグして範囲指定します。
   3. 「検索条件」に「">=60"」と入力します。
   4. 「OK」ボタンをクリックします。
      
5. C57セルに「=COUNTIF(C2:C51,">=60")」という計算式が設定されます。

計算できたら、C57の計算式をD57とE57のセルにコピーしてください。

評価をまとめる

ふたたび、各個人の成績にもどります。

出席・課題・期末テストの各評価をまとめて、最終的な評価を決めましょう。 各評価の平均を最終的な評価にすることにします。 まず、G1セルに「評価」と入力してください。

次に、G2セルに1番目の最終評価を計算する計算式を入力します。 ここで平均を求めるAVERAGE関数を使うこともできますが、ひとつ問題があります。 各評価のいずれかが空白の場合、AVERAGE関数では正しい結果が得られません。 AVERAGE関数は、与えられた範囲に空白のセルがあると、そのセルを除いた個数の平均を計算してしまいます。

ここでは、評価の合計を評価の数で割って計算します。 指定された範囲の空白でないセルの個数を求めるCOUNTA関数を使います。 COUNTA関数を使って、評価のラベル（出席・課題・期末テスト）の数を数えることにします。

• COUNTA（空白でないセルの個数を計算する）
  • 書式 : COUNTA(数値1, 数値2, ...)
  • 引数 : 数値1, 数値2, ... ：個数を計算するセルの範囲

ここでは、「関数の入力」ボタンを使わずに、直接関数を入力します。

1. G2セルをクリックして、アクティブにします。
2. 「=SUM(C2:E2)/COUNTA(C1:E1)」と数式を入力します。
3. Enterキーを押すと、計算式が入力されます。
   

絶対参照

次に、入力した数式をG3〜G51にコピーすることにしましょう。

1. Gセルをクリックして、アクティブにします。
2. G2セルの枠の右下の四角形（フィルハンドル）をマウスでG51セルまでドラッグします。
3. G3〜G51セルに計算式がコピーされます。

ここで、G3セルにコピーされた計算式を見てみましょう。「=SUM(C3:E3)/COUNTA(C2:E2)」となっていています。コピー元のG2の数式が相対参照だったので、このような結果になりましたが、これでよかったのでしょうか？

「COUNTA(C2:E2)」では、もしC2〜E2の評価のうち、ひとつでも空白があると計算結果がおかしくなってしまいます。数式をコピーしても、C1〜E1の空白でないセルの個数が求められるように、セルの参照を固定しなくてはなりません。

特定のセルを参照するために、計算式をコピーしても参照先のセルが変わらない、セルの番地を基準にした絶対的な位置を使った参照のしかたを、「絶対参照」といいます。 絶対参照を使うには、動かしたくないセル番地の行番号や列番号の前に「$」をつけます。相対参照とまとめると次のようになります。

ここでは、セルか行を固定すればよいのがわかりますので、「COUNTA($C$1:$E$1)」を使うことになります。

1. G2セルをクリックして、アクティブにします。
2. 「=SUM(C2:E2)/COUNTA($C$1:$E$1)」と数式を入力します。
3. Enterキーを押すと、計算式が入力されます。

次に、前の説明のように、入力した数式をG3〜G51にコピーしてください。 そして、正しくコピーできているかどうか確認しましょう。 G3セルの計算式は「=SUM(C3:E3)/COUNTA($C$1:$E$1)」となっているはずです。

関数のネスト

最終評価ができあがりましたが、小数のついた評価もあると思います。 セルの書式設定で小数点以下を表示させなくすることもできますが、 ここでは新たな関数ROUNDを使って、小数点以下を四捨五入することにします。

• ROUND（指定された桁で、四捨五入する）
  • 書式 : ROUND(数値, 桁数)
  • 引数 : 数値 : 四捨五入する数値
  • 引数 : 桁数 : 四捨五入する桁数
    • 0か省略なら、最も近い整数
    • 正の整数なら、指定された小数点以下の桁になるように四捨五入
    • 負の整数なら、指定された整数部分の桁（1の位が0, 10の位が1）で四捨五入

もう一度計算式を入力しなおしましょう。整数を求めたいので、引数の桁数の部分は0にします。

1. G2セルをクリックして、アクティブにします。
2. 「=ROUND(SUM(C2:E2)/COUNTA($C$1:$E$1),0)」と数式を入力します。
3. Enterキーを押すと、計算式が入力されます。
4. 入力した数式を、マウスをドラッグして、G3〜G51にコピーします。
   

このように、関数の引数に、さらに別の関数を使うことを、関数のネスト（入れ子）といいます。

成績の順位を調べる

最終評価をもとに、個人の成績の順位を調べます。 順位を調べるには、RANK関数を使います。

• RANK（数値が指定された範囲で何番目に位置するかを返す）
  • 書式 : RANK(数値, 範囲, 順序)
  • 引数 : 数値 : 順位を調べる数値
  • 引数 : 範囲 : 調べるセルの範囲
  • 引数 : 順序 : 順位の並べ方
    • 0か省略なら、大きいもの順に順位をつける（降順）
    • 0以外の数字なら、小さいもの順に順位をつける（昇順）

まずH1セルに「順位」と入力します。 そして、次のようにして、順位を設定します。

1. H2セルをクリックしてアクティブにします。
2. 「関数の挿入」ボタンをクリックします。
   
3. 利用する関数を選びます。
   利用したい関数の分類を選んだあと、関数名を選びます。「関数の分類」から「統計」を選び、「関数名」から「RANK」を選びます。「OK」ボタンをクリックします。
   
4. 次に、関数に与える引数を設定します。
   1. 「数値」の入力欄をクリックします。
   2. G2のセルをクリックします。「数値」に「G2」と設定されます。
   3. 「範囲」の入力欄をクリックします。
   4. G2〜G51のセルをマウスをドラッグして範囲指定します。「範囲」に「G2:G51」と設定されます。
   5. 「順序」の入力欄をクリックします。
   6. 「0」を入力します。「OK」ボタンをクリックします。
      
5. H2セルに「=RANK(G2,G2:G51,0)」という計算式が設定されます。

ここで、この計算式をコピーする前に、よく見てみましょう。 順番を求める範囲「G2:G51」は相対参照になっていますので、計算式をコピーすると、コピー先の計算式では行番号の部分が変わってしまいます。これでは正しい順位が求められません。

順番を求める範囲「G2:G51」を絶対参照にすれば、計算式をコピーしても、参照するセルの範囲は変わりません。直接計算式を編集することもできますが、次のような方法でも簡単に絶対参照形式にできます。

1. H2セルをクリックします。
2. 数式バーの計算式「=RANK(G2,G2:G51,0)」の「G2:G51」の「G2」をクリックします。
3. F4キーを何回か押して、「$G$2」か「G$2」に変更します。
4. 「G51」をクリックして、F4キーを何回か押し「$G$51」か「G$51」に変更します。
   

数式を変更できたら、H2セルの計算式を、マウスをドラッグして、H3〜H51セルにコピーしてください。

合否を判定する

最後に、最終評価をもとに合格か不合格かを判定します。 60点以上であれば「合格」、59点以下であれば「不合格」と表示されるようにします。 このような、条件によって結果を変えるには、IF関数を使います。

• IF（指定された条件（論理式）によって、あっているか（真）違っているか（偽）を判断し、真と偽のそれぞれの場合の結果を返す）
  • 書式 : IF(論理式, 真の場合, 偽の場合)
  • 引数 : 論理式 : 評価する条件（比較演算子を使う）
  • 引数 : 真の場合 : 論理式の結果が真(TRUE)の場合に返される値
  • 引数 : 偽の場合 : 論理式の結果が偽(FALSE)の場合に返される値

次の比較演算子を使うとすると、論理式は「セルの値>=60」となります。また、真の場合と偽の場合はそれぞれ「"合格"」「"不合格"」となります。文字列を引数にするときには「"（ダブルクォーテーション）」で文字列を囲みます。

まずI1セルに「合否」と入力します。 そして、次のようにして、順位を設定します。

1. I2セルをクリックしてアクティブにします。
2. 「関数の挿入」ボタンをクリックします。
3. 利用する関数を選びます。
   利用したい関数の分類を選んだあと、関数名を選びます。「関数の分類」から「論理」を選び、「関数名」から「IF」を選びます。「OK」ボタンをクリックします。
4. 次に、関数に与える引数を設定します。
   
   1. 「論理式」の入力欄をクリックし、「G2>=60」と入力します。
   2. 「真の場合」の入力欄をクリックし、「"合格"」と入力します。
   3. 「偽の場合」の入力欄をクリックし、「"不合格"」と入力します。
   4. 「OK」ボタンをクリックします。
      
5. I2セルに「=IF(G2>=60,"合格","不合格")」という計算式が設定されます。

あと、I2セルの計算式をI3〜I51セルにコピーするれば、できあがりです。 マウスをドラッグして、コピーしてください。

仕上げ

最終評価の全体平均点・最高点・最低点を計算します。 出席の評価の計算をコピーしましょう。次のように操作してください。

1. C53〜C57のセルを、マウスをドラッグして、範囲指定します。
2. 範囲指定したところを右クリックし、メニューから「コピー」を選択します。
3. G53セルを右クリックして、メニューから「貼り付け」を選択します。

最後に見た目を整えるために、次の設定をしてください。

• A列の幅を「5.00」にする
• 1行目にある表の項目名（番号、名前、課題など）を中央揃えにする

ここまでできたら、課題の説明にすすんでください。