1.3.5 IF 関数による評価評価の欄は IF 関数を使って A B C の三段階評価を行う IF 関数の書式は次のとおりである IF( 論理式, 真の場合, 偽の場合 ) [ 例 ]IF(G2>=70, 合格, 不合格 ): セル G2 が 70 以上であれば 合格 を そうでなければ 不合格 を IF 関数の入っているセルに表示する 論理式で使用する比較演算子には次のようなものがある 比較記号 ( 半角 ) 意味 = 等しい > より大 >= 以上 < より小さい <= 以下 <> 等しくないここでは平均点が 70 点以上は A 70 点未満 50 点以上は B 50 点未満は C の評価をする IF 関数は論理式の結果が真か偽しかないので 処理結果を 3 分岐以上にする場合は 真または偽の場合にさらに IF 関数を使って分岐する たとえば IF(70 点以上の場合, A,IF(50 点以上の場合, B, C )) とする (70 点以上でない場合の処理 ) 関数の引数の中で関数を使うことをネストという Excel では数式の中の関数を 7 段階までネストすることができる 次に 各教科の最高点と最低点を抽出し 表の下 (16 行目から 18 行目 ) に次表のような表を作って表示する 16 国語英語数学総点平均 17 最高点 18 最低点セル C17 に最大値を求める関数 セル 18 に最小値を求める関数を入れてそれぞれセル G17 セル G18 までドラッグしてコピーする 最大値を求める関数 :MAX( 範囲 ) 最小値を求める関数 :MIN( 範囲 ) 範囲 = 最初のセル : 最後のセル [ 例 ] セル C17 に入る式は =MAX(C2:C10)) である 問題 1 この成績データ (Sheet1) を使って 生徒ごとの国語 数学 英語の各科目ごとの偏差値と順位を算出しなさい この表は次表のような形式とし Sheet2に作成しなさい 1
Sheet2 に作成する表 問題 2 前問の成績表 (Sheet2) の各教科の順位の列の次に評価の列を作って 生徒ごとに各教科の評価をし なさい なお評価は 各教科 A(100~85) B(84~70) C(69~55) D(54 以下 ) とする 1.4 集中力度チェックテスト自分の集中力がどの程度なのか とても興味深いところである 処理条件に従って次のような表を作成しなさい 処理条件 1 列幅 罫線 文字や数値の形式など 表の体裁は見やすいように設定する 2 F 列に得点を入力し 合計得点 (B7) を求める (SUM 関数 ) 3 合計得点が 20 点以上の場合は 集中力あり 10~19 点の場合は まあまあ 10 点未満の場合は 集中力欠如 と 診断結果(B9) に表示する (IF 関数 ) 2
集中力度チェックテストの実施例 1.5 のぞみの時刻表新幹線 のぞみ は 東京 ~ 博多間を約 5 時間で走行する超高速列車である 処理条件に従って次のような表を作成しなさい 処理条件 1 次表のような新幹線 のぞみ の駅間の距離と時間を求める表を作成する 2 列幅 罫線 文字や数値の形式など 表の体裁は見やすいように設定する 3 発駅コード (B3) と着駅コード (B4) を入力すると (A6:A9) に駅名と距離 所要時間を表示する 4 使用する関数は IF VLOOKUP ABS である VLOOKUP: 指定した範囲の 1 列目を検索し 該当するデータがあるとそのデータと同じ行にある指定されたデータを出力する 書式 :VLOOKUP( 検索値, 範囲, 列番号, 検索の型 ) 使用例 :VLOOKUP(B4,D4:F11,3,0) セル B4 の値が範囲 (D4:F11) の 1 列目にあるかどうか探し あればそのデータと同じ行にある左から 3 番目のデータを持ってくる 検索の型 =0 でるから検索値と一致する値がない場合はエラーとなる 検索の型 =1 の場合は 検索値と一致する値がないと 検索値未満で最も大きい値の行にある左から 3 番目のデータを持ってくる ABS: 絶対値を求める 書式 :ABS( 数値 ) 使用例 :ABS(-3.7) 3.7 ABS(B3-F3) セル B3 の値 - セル F3 の値 がマイナスの場合はプラスにする 3
新幹線 のぞみ の駅間距離と駅間時間を求める表 5 A6 の式 =B3 が未入力なら何も表示せず そうでなければ VLOOKUP(B3,D4:E11,2,0) を表示する すなわち セル A6=if(B3=,,VLOOKUP(B3,D4:E11,2,0)) 6 A7 の式 =A6 の式に準じる 7 A8 の式 =B4 が未入力なら何も表示せず そうでなければ ABS(VLOOKUP(B4,D4:F11,3,0)- VLOOKUP(B3,D4:F11,3,0)) を表示する 8 A9 の式 =B4 が未入力なら何も表示せず そうでなければ ABS(VLOOKUP(B4,D4:G11,4,0)-VLOOKUP(B3,D4:F11,4,0)) を表示する 9 A9 の書式 : 時刻 実施例 1.6 万年カレンダーの作成 ( ) ( 注 )( ) のある問題は 遅れている人は飛ばして下さい 遅れている人と進んでいる人との調整を取るための問題です 以下の説明で 説明 の部分は理解を深めるための数式等の説明で 操作の説明ではないから 読み飛ばしても万年カレンダー作成に影響はない 4
年と月を入力すると日付が自動的に変更されるカレンダーを作成する (1) 次図のように入力する (2) セルに名前を定義する 1A1 をクリックしてアクティブにする 2 数式バーの左横にある [ 名前 ] ボックスをクリックする 名前ボックス 3 A1 と表示されているところに 年 を入れて [Enter] キーを押す セル A1 に年という名前がついて 名前ボックスの表示が A1 から年に変わる 4 同様に A2 に 月 という名前を G1 に 月初の曜日 という名前をつける (3) 指定した年月の 1 日が何曜日かを求める 1G1 に =WEEKDAY( 年 & / & 月 & / &1,1) を 年月以外は半角で入力する 7 が現れる 説明 & は文字を結合するので 入力した式は =WEEKDAY( 年 / 月 /1,1) となる この式は指定した年月の 1 日目は何曜日かを求めるもので 曜日が数字 ( 日 =1 月 =2 土 =7) で示される セル G1 には 2011 年 10 月 1 日の曜日 7(= 土曜日 ) が現れる 月(A2) を変えるとその月の 1 日目の曜日が数字で G1 に現れる (4) 日付の設定カレンダーの第 1 週目の各セルは 空白 1 前日の値 +1 のいずれかである 1A5 に =IF( 月初の曜日 =A3,DAY(DATE( 年, 月,1)), ) を入力する 英字 記号は半角 説明 年に 2011 月に 10 が入っているので DATE( 年 月 1) は DATE(2011,10,1) となり 2011 年 10 月 1 日のシリアル値 (1900 年 1 月 1 日を 1 とし その後 1 日に 1 ずつ増やしたときの 2011 年 10 月 1 日の値 ) が求まる DAY( シリアル値 ) は シリアル値がそのシリアル値に該当する月の何日目になるかを求める 上の式は月初を表しており 1 となる 月初の曜日 (G1)=A3 であれば 月初の曜日は 1 即ち日曜日となり A5( 日曜日の欄 ) に 1 が入る 月初の曜日 A3 であれば 月初の曜日は 1 ではない 即ち日曜日ではないから A5 は空白になる 2B5 に =IF( 月初の曜日 =B3,DAY(DATE( 年, 月,1)),IF(A5=,,DAY(DATE( 年, 月,A5)+1))) を入力する 説明 1 日が月曜日 (2) から土曜日 (7) の場合は B3(2)~G3(7) のいずれかが月初の曜日 (G1) と 5
一致する 一致した数字の曜日が月初の曜日であるから その曜日の欄に 1 が入る 一致しない場合は 1 以外 即ち空白か 2 以上の値が入る 前日が空白なら空白で 前日が空白でなければ前日に 1 加えた値になる このような処理をするのが2の式である 3B5 の式を G5 までコピーする G5 に 1 が現れる 4 第 2 週 ~ 第 6 週のセルは前日のセルの値に 1 加えた値である A6 に =G5+1 B6 に =A6+1 を入力する B6 の数式を G6 までコピーする A6:G6 を範囲指定して 10 行目までコピーする (5) 翌月の日付部分の処理第 5 第 6 週の日付が翌月になるときは 条件付書式でフォントの色を白に設定して見えないようにする 1 A9:G10 を範囲指定 2 [ ホーム ] タブの [ スタイル ] で [ 条件付書式 ] をクリックし [ ルールの管理 ] をクリックする 条件付き書式ルールの管理 ダイアログボックスが表示される 3 新規ルール をクリックする 新しい書式ルール ダイアログボックスで 数式を使用して 書式設定するセルを決定 をクリックする 4 次の数式を満たす場合に値を書式設定 のボックスに[=MONTH(DATE( 年, 月,A9))<>$A$2 ] を入力する [ 書式 ] をクリックすると セルの書式設定 ダイアログボックスが表示される 4 のボックスをクリックして 白色 を選択 [OK] 新しい書式ルール ダイアログボックスに戻る OK 条件付き書式ルールの管理 ダイアログボックスに戻る [OK] 翌月の部分が白字になり見えなくなる (6) 万年カレンダー A1 に年 A2 に月を入力すると その月のカレンダーが表示される 1.7 年賀はがきの当選番号 ( ) 毎年たくさんの年賀はがきをもらっても 1 等はなかなか当たらないものである 処理時要件に従って次のような表を作成しなさい 処理条件 1 次表に示す年賀はがき当選番号判定表を作成する 2 列幅 罫線 文字や数値の形式など 表の体裁は見やすいように設定する 3 当選番号 (B3) を入力し D 列の番号が当たっている場合は 当たり と表示し そうでない場合は はずれ と表示する 4 (B5) には 当選枚数を表示する 6