情報処理演習 第7回
前回までの課題の提出こちらの連絡事項を見て、前回課題の提出状況を確認してください。
今回のファイル計算式と関数計算式「計算式」とは、数字やセルの値を使って計算や処理をするものです。四則演算(加減乗除)のような計算だけではなく、関数を使った複雑な処理を記述できます。 書き方のルールは簡単です。最初に「=」を入力すると計算式となります。たとえば、A1セルに入力されている円の半径を使って、円周や円の面積を計算するには、次のような数式になります(3.14は円周率)。 =2*A1*3.14 (円周) =A1*A1*3.14 または A1^2*3.14 (円の面積) 計算式に使える演算子は次のとおりです。括弧()を使うこともできます。
関数「関数」とは、数学ででてくる関数と同じ意味で、 「数字や文字など『値』を与えると、いろいろな『処理』をして、新しい値を『結果』として返す」ような仕掛けのことです。たとえば、自動販売機の仕掛けも関数に例えることができます。自動販売機という「関数」に、お金と選択した商品ボタン「値」を処理させると、ジュースやコーヒーという「結果」が返ってきます。 お金・商品ボタン(値)−<与える>→自動販売機(関数)−<返す>→コーヒー(結果) 関数の場合は、数字や文字のデータだけでなく他のセルの番地を、引数として関数に与えると、結果が返ってきます。 引数(数字や文字、他のセルの番地)−<与える>→(関数)−<返す>→(結果) 具体的は、次のような計算式を入力します。引数の数は、関数によって変わります。 =関数名(引数1, 引数2, 引数3, ...) 関数のネスト(入れ子)セルに入力できる計算式は1つだけです。しかし、いくつかの関数を組み合わせたい場合があります。そのようなときには、ある関数の引数として別の関数を使う、関数のネスト(入れ子)をします。 たとえば、関数Aの2番目の引数に関数Bを使う場合は、次のような計算式を入力します。 =関数A(引数A-1, 関数B(引数B-1, 引数B-2, ...)) 成績表の作成(基本的な関数の利用)あるクラスの成績表を作成します。 「出席点」「課題点」「期末テストの点」の3つの評価から、 いくつかの関数を使って、成績を集計・判定します。 番号の入力まず、表の最初に新しい列を1列挿入します。
次に、A列に連番の番号を入力します。連番を入力するには、「オートフィル」を使います。1〜50版までの番号を入力します。
個人成績の合計個人の成績の合計を計算しましょう。 まず、F1セルに「合計」と入力してください。 次に、合計を求める計算式を入力します。 出席点と課題点と期末テストの点を足したものが合計になります。 ここでは、関数SUMを使います。
今まではオートSUM機能を使っていましたが、今回からは他の関数と同じように操作することにします。 関数を利用するには、次のように操作します。
相対参照次に、F2セルに入力した計算式をF3〜F51セルにコピーします。
ここで、F3セルにコピーされた計算式を見てみましょう。「=SUM(C3:E3)」となっていて、コピー元の「=SUM(C2:E2)」という計算式とは異なっています。これはどういうことでしょうか? F2セルの計算式の意味は、次のような意味になります。
つまり、計算式のあるセル自身を基準にしたセルの位置(相対的な位置)の情報を使って計算しています。したがって、F2セルの計算式をコピーしたF3セルでは、次のような意味になります。
このような、自分自身のセルを基点としたセルの位置情報をつかい、コピーすると参照するセルが変わるような、 相対的なセルの参照のしかたを「相対参照」といいます。 全体成績の計算採点する人数や平均点・最高点・最低点を計算します。 まず、B53セルに「人数」、B54セルに「平均点」、B55セルに「最高点」、B56セルに「最低点」と入力してください。 次に関数を使って計算をします。使用する関数は、COUNT, AVERAGE, MAX, MINです。
では、次のようにして計算をします。
同じような操作をして、C54セルにAVERAGE関数で平均点を、C55セルにMAX関数で最高点を、C56セルにMIN関数で最低点を計算してください。計算できると次のような計算式が設定されます。いずれの関数も分類は「統計」です。
これで出席点の全体成績が計算できました。 これらの計算式をコピーして、課題・期末テスト・合計の全体成績(D53〜F56)も計算してください。 上の「相対参照」の説明を参考にして、マウスを使って、計算式をコピーしてください(ヒントは、まずC53〜C56までのセルをマウスで範囲指定します)。 成績表の作成(さまざまな関数の利用)ある条件の人数を数えるここでまでに紹介した関数(SUM, COUNT, AVERAGE, MAX, MIN)は、オートSUMからも利用できる、基本的な関数です。ここからは、少し複雑な処理をする関数を紹介します。 出席・課題・期末テストの各評価について、60点以上の人数を計算しましょう。 特定の条件を満たすセルの数を数える関数COUNTIFを使います。
検索条件には、次のような比較演算子を使います。ここでは、「60点以上」という条件があるので「>=60」が検索条件になります。
まず、B57セルに「60点以上の人数」と入力してください。 そして、次のようにして計算をします。
計算できたら、C57の計算式をD57とE57のセルにコピーしてください。 評価をまとめるふたたび、各個人の成績にもどります。 出席・課題・期末テストの各評価をまとめて、最終的な評価を決めましょう。 各評価の平均を最終的な評価にすることにします。 まず、G1セルに「評価」と入力してください。 次に、G2セルに1番目の最終評価を計算する計算式を入力します。 ここで平均を求めるAVERAGE関数を使うこともできますが、ひとつ問題があります。 各評価のいずれかが空白の場合、AVERAGE関数では正しい結果が得られません。 AVERAGE関数は、与えられた範囲に空白のセルがあると、そのセルを除いた個数の平均を計算してしまいます。 ここでは、評価の合計を評価の数で割って計算します。 指定された範囲の空白でないセルの個数を求めるCOUNTA関数を使います。 COUNTA関数を使って、評価のラベル(出席・課題・期末テスト)の数を数えることにします。
ここでは、「関数の入力」ボタンを使わずに、直接関数を入力します。
絶対参照次に、入力した数式をG3〜G51にコピーすることにしましょう。
ここで、G3セルにコピーされた計算式を見てみましょう。「=SUM(C3:E3)/COUNTA(C2:E2)」となっていています。コピー元のG2の数式が相対参照だったので、このような結果になりましたが、これでよかったのでしょうか? 「COUNTA(C2:E2)」では、もしC2〜E2の評価のうち、ひとつでも空白があると計算結果がおかしくなってしまいます。数式をコピーしても、C1〜E1の空白でないセルの個数が求められるように、セルの参照を固定しなくてはなりません。 特定のセルを参照するために、計算式をコピーしても参照先のセルが変わらない、セルの番地を基準にした絶対的な位置を使った参照のしかたを、「絶対参照」といいます。 絶対参照を使うには、動かしたくないセル番地の行番号や列番号の前に「$」をつけます。相対参照とまとめると次のようになります。
ここでは、セルか行を固定すればよいのがわかりますので、「COUNTA($C$1:$E$1)」を使うことになります。
次に、前の説明のように、入力した数式をG3〜G51にコピーしてください。 そして、正しくコピーできているかどうか確認しましょう。 G3セルの計算式は「=SUM(C3:E3)/COUNTA($C$1:$E$1)」となっているはずです。 関数のネスト最終評価ができあがりましたが、小数のついた評価もあると思います。 セルの書式設定で小数点以下を表示させなくすることもできますが、 ここでは新たな関数ROUNDを使って、小数点以下を四捨五入することにします。
もう一度計算式を入力しなおしましょう。整数を求めたいので、引数の桁数の部分は0にします。
このように、関数の引数に、さらに別の関数を使うことを、関数のネスト(入れ子)といいます。 成績の順位を調べる最終評価をもとに、個人の成績の順位を調べます。 順位を調べるには、RANK関数を使います。
まずH1セルに「順位」と入力します。 そして、次のようにして、順位を設定します。
ここで、この計算式をコピーする前に、よく見てみましょう。 順番を求める範囲「G2:G51」は相対参照になっていますので、計算式をコピーすると、コピー先の計算式では行番号の部分が変わってしまいます。これでは正しい順位が求められません。 順番を求める範囲「G2:G51」を絶対参照にすれば、計算式をコピーしても、参照するセルの範囲は変わりません。直接計算式を編集することもできますが、次のような方法でも簡単に絶対参照形式にできます。
数式を変更できたら、H2セルの計算式を、マウスをドラッグして、H3〜H51セルにコピーしてください。 合否を判定する最後に、最終評価をもとに合格か不合格かを判定します。 60点以上であれば「合格」、59点以下であれば「不合格」と表示されるようにします。 このような、条件によって結果を変えるには、IF関数を使います。
次の比較演算子を使うとすると、論理式は「セルの値>=60」となります。また、真の場合と偽の場合はそれぞれ「"合格"」「"不合格"」となります。文字列を引数にするときには「"(ダブルクォーテーション)」で文字列を囲みます。
まずI1セルに「合否」と入力します。 そして、次のようにして、順位を設定します。
あと、I2セルの計算式をI3〜I51セルにコピーするれば、できあがりです。 マウスを ここまでできたら、課題の説明にすすんでください。 |