3.成績処理 3.1 成績の平均点など ・・・AVERAGE,MAX,MIN関数 3.2 成績(合計点) ・・・SUM関数 3.3 合計点が平均点以上か ・・・IF関数 3.4 5段階表示 ・・・IF関数の入れ子 3.5 5段階表示 ・・・VLOOKUP関数 3.6 ソート(並べ換え) ■ ファイルは: 「2010実習.xls」 1
3.1 成績の平均点など (1) 平均点 セルD13に,「=AVERAGE(D3:D12)」 を入力しましょう 3.1 成績の平均点など (1) 平均点 セルD13に,「=AVERAGE(D3:D12)」 を入力しましょう [形式] =AVERAGE(セル範囲) (2) 最高点 セルD14に,「=MAX(D3:D12) 」 を入力しましょう [形式] =MAX(セル範囲) (3) 最低点 セルD15に,「=MIN(D3:D12) 」 を入力しましょう [形式] =MIN(セル範囲) セルが空白なら,そのデータはデータ数に入りません. 2
■小数点以下の桁数の指定 ① 対象セルを,指定(クリック)します. ② 桁数を指定します. (a) 小数点以下の桁を多くするには 小数点以下の桁を少なくするには (b) 「書式ーセル」で, 「表示形式」タブ の「数値」で, 桁数を入力
(4)式をコピーしましょう. ・・・ここでは,オートフィルを使います ① D13~D15をドラッグ (4)式をコピーしましょう. ・・・ここでは,オートフィルを使います ① D13~D15をドラッグ ② 右下にマウスポインタを合わせて,G15までドラッグ. 4
3.2 成績(合計点を計算)・・・SUM関数 各人の合計点を計算させましょう. ①SUM関数の入力 セルG3に, 「=SUM(D3:F3)」を,入力しましょう. ②G4からG12まで,オートフィルでコピーしましょう. ■ 数値の合計を求めるには「SUM関数」が便利です. [形式] SUM(計算対象のセル番地) (a) セルが連続していれば,範囲で指定. (b) セルが不連続ならば, 「,」で区切って指定. 5
3.3 合計点が平均点以上か・・・IF関数 生徒毎に,合計点が 平均点以上なら,「○」を, そうでなれば,「×」を表示させる. 平均点以上なら,「○」を, そうでなれば,「×」を表示させる. ここでは,IFを使ってみましょう まず,IF文を説明して,その練習をします. 6
IF関数 [形式] IF(条件,a, b) a: 条件が成立した時に取る, 値/式を指定します. a: 条件が成立した時に取る, 値/式を指定します. b: 条件が成り立たない時に取る,値/式を指定します. . ■「条件」で,2つの値を比較するときには, 等しいは「=」,大小は「>又は<」 以上は「>=」,以下は「<=」 の記号を使います. 7
IF文を使う前に,1番の生徒について,下線部を埋めましょう -2つの値を比較した結果で,処理を変える- 1)比較するセル番地又は値 →① [1人の点数] 2)比較するセル番地又は値 →② [平均点] 3) ①と②の大小・等値関係→③ [平均点以上] <= [以下],< [未満],= [等しい],< (越える),<= (以上) 4)3)の関係が成立するときに, すること/表示するもの→④ [平均点以上の場合は○] 〃 不成立のときに 〃 / 〃 →⑤ [そうでない場合は×] 6) 上の①~⑥を,下の式にあてはめましょう =IF(①②③,④,⑤) =IF( , , ) 8
① セルI3に,「=IF(G3>=$G$13,"○","×")」を入力して, ② I12までドラッグ. そうでなければ「×」 を表示させましょう. ① セルI3に,「=IF(G3>=$G$13,"○","×")」を入力して, ② I12までドラッグ. * 文字を扱うときは,「“」で囲みます. 9
(注) 前の節では,「比較」と表現していました. 3.4 5段階表示・・・IF関数の入れ子 合計点が 0~40点 なら 1 41~50 〃 2 51~60 〃 3 61~80 〃 4 81~100 〃 5 ■ 順に検査(注)して, 合計点が40点以下なら 1 50 〃 2 60 〃 3 80 〃 4 そうでなければ 5 を表示させます. ここでは,IF文を使ってみましょう まず,IF文とその練習をします. (注) 前の節では,「比較」と表現していました. 10
■1番の阿久津についてについて■ : IF文を使う前に,下線部を埋めましょう -2つの値の比較で,処理を変える- ■<1> 40点以下について 1)検査したい(「合計」)セル番地 → ① 2)検査したい値 → ② 3)検査したい①と②の大小関係は? ③ <= [以下],< [未満],= [等しい],< (越える),<= (以上) 4)3)の関係が成立するときに, すること/表示するもの→④ 〃 不成立のときに 〃 / 〃 →⑤ ■<2> 50点以下について 1)検査したい(「合計」)セル番地 → ① 2)検査したい値 → ② 3)検査したい①と②の大小関係は? ③ <= [以下],< [未満],= [等しい],< (越える),<= (以上) 4)3)の関係が成立するときに, すること/表示するもの→④ 〃 不成立のときに 〃 / 〃 →⑤ <1>のIF文 =IF( , , ) <2>のIF文 =IF( , , ) ■<3> 60点以下について 1)検査したい(「合計」)セル番地 → ① 2)検査したい値 → ② 3)検査したい①と②の大小関係は? ③ <= [以下],< [未満],= [等しい],< (越える),<= (以上) 4)3)の関係が成立するときに, すること/表示するもの→④ 〃 不成立のときに 〃 / 〃 →⑤ ■<4> 80点以下について 1)検査したい(「合計」)セル番地 → ① 2)検査したい値 → ② 3)検査したい①と②の大小関係は? ③ <= [以下],< [未満],= [等しい],< (越える),<= (以上) 4)3)の関係が成立するときに, すること/表示するもの→④ 〃 不成立のときに 〃 / 〃 →⑤ <3>のIF文 =IF( , , ) <4>のIF文 =IF( , , ) 2.出席簿他,3.成績 11
■1番の阿久津について: 下線部には,次が入ります(答え). -2つの値の比較で,処理を変える- ■1番の阿久津について: 下線部には,次が入ります(答え). -2つの値の比較で,処理を変える- ■<1> 40点以下について 1)検査したい(合「計」)セル番地 → ① G3 2)検査したい値 → ② 40 3)検査したい①と②の大小関係は? ③ <= <= [以下],< [未満],= [等しい],< (越える),<= (以上) 4)3)の関係が成立するときに, すること/表示するもの→④ 1 〃 不成立のときに 〃 / 〃 →⑤ <2>のIF文 ■<2> 50点以下について 1)検査したい(「合計」)セル番地 → ① G3 2)検査したい値 → ② 50 3)検査したい①と②の大小関係は? ③ <= <= [以下],< [未満],= [等しい],< (越える),<= (以上) 4)3)の関係が成立するときに, すること/表示するもの→④ 2 〃 不成立のときに 〃 / 〃 →⑤<3>のIF文 <1>のIF文 =IF( G3 <= 40 , 1 , <2>のIF文) <2>のIF文 =IF( G3 <= 50 , 2 , <3>のIF文) ■<3> 60点以下について 1)検査したい(「合計」)セル番地 → ① G3 2)検査したい値 → ② 60 3)検査したい①と②の大小関係は? ③ <= <= [以下],< [未満],= [等しい],< (越える),<= (以上) 4)3)の関係が成立するときに, すること/表示するもの→④ 3 〃 不成立のときに 〃 / 〃 →⑤ <4>のIF文 ■<4> 80点以下について 1)検査したい(「合計」)セル番地 → ① G3 2)検査したい値 → ② 80 3)検査したい①と②の大小関係は? ③ <= <= [以下],< [未満],= [等しい],< (越える),<= (以上) 4)3)の関係が成立するときに, すること/表示するもの→④ 4 〃 不成立のときに 〃 / 〃 →⑤ 5 <3>のIF文 =IF( G3 <= 60 , 3 , <4>のIF文) <4>のIF文 =IF( G3 <= 80 , 4 , 5 ) 2.出席簿他,3.成績 12
<1>から<4>を合わせると, 下線部には,次が入ります(答え) <1>から<4>を合わせると, =IF(G3<=40,1,IF(G3<=50,2,IF(G3<=60,3,IF(G3<=80,4,5))))
では,実際に,次を操作して下さい ①セルH3に, 「=IF(G3<=40,1,IF(G3<=50,2,IF(G3<=60,3,IF(G3<=80,4,5))))」 を入力します. ②このセルを,H12までドラッグ. (オートフィル) ■ 5段階が表示されましたでしょうか.
3.5 5段階表示・・・ VLOOLUP関数 「3.4のIF」ではなく, 「2.名簿」で使った,VLOOKUP関数を使ってみましょう. 手順 (1)表2を作ります.(既に作ってあります) (2)VLOOKUP関数を作ります (①1人目を作り,②他の生徒にコピーします)
(2-1) 1人目を作ります VLOOKUP関数 : 形式: VLOOKUP(検索値, 範囲,列番号,検索の型) 1) 検索値: 範囲 の左端の列で検索する値 ■ここでは, G3 2) 範囲 :検索と返す値が入力されている範囲 ☆ ここでは,表1のL2~P7.(ドラッグし,絶対番地に) 3) 列番号: 範囲内で目的のデータが入力されている列を、 左端からの列数で指定 ☆ ここでは, 評価点があるのは, 5列目. 4) 検索の型: (a)省略かTRUE( 検索値が見つからない場合に、 検索値未満の最大値を返す) (b)FALSE(完全に一致する値だけを検索) ☆ ここでは,どの区間か,を探すので,「TRUE」.
VLOOKUPを使う前に,下線部を埋めましょう (どの区間か,を見つける場合) 1)検索値のセル番地 → ① 2)検索先の範囲 → : . [形式]範囲の左上端のセル番地:右下端のセル番地 絶対番地では→② $ $ :$ $ . 3)返す値が入力されている列は左から③ 番目. 4)この①~③の値を,下の式にあてはめましょう =VLOOKUP(①,②,③, TRUE) =VLOOKUP( , : , ,TRUE) *一般的には,検索先は,昇順に並べ換えておく(この時、同じ値がない方が良い
(2-2) やってみましょう ① 1人目: セルJ3に, 「=VLOOKUP (G3,$L$2:$P$7,5,TRUE) 」 (2-2) やってみましょう 1番の人のデータを指定 ① 1人目: セルJ3に, 「=VLOOKUP (G3,$L$2:$P$7,5,TRUE) 」 ② 2人目以降: セルJ3を,J4~J12にコピーします. 評価点を 表示させる
3.6 ソート(並べ換え) ■ 「並べ替え」機能で,全部/一部のデータを並べ替えることができます。 3.6 ソート(並べ換え) ■ 「並べ替え」機能で,全部/一部のデータを並べ替えることができます。 ☆ ソートすると,基本的には,戻せません。 ・ 1回は,「編集ー元に戻す」で戻せます。 ・ セル番地を式に含むデータがある場合には, 「コピー」&「形式を指定して貼り付け」-(値)でコピーして,そのデータに対してソートしましょう. ・ ソートする前に,1からの通し番号を付与しておくと, その値を元に,並べ替えを戻すことができます。 3.成績 19
形式を指定して貼り付け ☆この方式は,コピー&ペーストの時に, 貼り付け付けるデータを指定できます. ■ 「値」を指定すると, 貼り付け付けるデータを指定できます. ■ 「値」を指定すると, 計算された値が貼り付けられます. ・・・■元データが変更されても 貼り付け先には影響しません. ■ソートの様に, データが入れ替わる操作の 場合には必要です. 2.出席簿他,3.成績 20
[練習] 合計の値で,降順で並べ替えましょう [練習] 合計の値で,降順で並べ替えましょう ①ソートの範囲(表3)を 指定(ドラッグで)。 ここでは, A25 からG35 ②「データ」-「並べ替え」をクリック。 下のダイヤログボックスが表示されるので, ③ をクリックして, リストの中の ④「計」をクリック。 ⑤ 降順なので,「降順」をクリック。 ⑥ をクリックして終了。 2.出席簿他,3.成績 21