KAWANO's PukiWiki Plus! - Lecture/InfoPrac2003/7th のバックアップ(No.3)

• バックアップ一覧
• 差分 を表示
• 現在との差分 を表示
• 現在との差分 - Visual を表示
• ソース を表示
• Lecture/InfoPrac2003/7th へ行く。
  • 1 (2003-11-11 (火) 07:42:02)
  • 2 (2003-11-11 (火) 13:43:32)
  • 3 (2003-11-11 (火) 19:28:29)
  • 4 (2003-11-11 (火) 20:31:01)
  • 5 (2003-11-12 (水) 04:27:22)
  • 6 (2003-11-12 (水) 04:27:22)

---

情報処理演習 第7回

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

前回までの課題の提出

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

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

今回のファイル

計算式と関数

計算式

「計算式」とは、数字やセルの値を使って計算や処理をするものです。四則演算（加減乗除）のような計算だけではなく、関数を使った複雑な処理を記述できます。 書き方のルールは簡単です。最初に「=」を入力すると計算式となります。たとえば、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, ...))

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

あるクラスの成績表を作成します。 「出席点」「課題点」「期末テストの点」の3つの評価から、 いくつかの関数を使って、成績を集計・判定します。

番号の入力

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

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

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

1. A2セルに「2」、A3セルに「3」と入力する
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（指定された桁で、四捨五入する）
  • 書式 : COUNTIF(数値, 桁数)
  • 引数 : 数値 : 四捨五入する数値
  • 引数 : 桁数 : 四捨五入する桁数
    • 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関数を使います。

合否を判定する

---

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