1 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）を使って、生徒ごとの国語、数学、英語の各科目ごとの偏差値と順位を算出 しなさい。この表は次表のような形式とし、Sheet２に作成しなさい。
2 Sheet2 に作成する表 問題２ 前問の成績表（Sheet２）の各教科の順位の列の次に評価の列を作って、生徒ごとに各教科の評価をし なさい。なお評価は、各教科A（100～85）、B（84～70）、C（69～55）、D（54 以下）とする。 1.4 集中力度チェックテスト 自分の集中力がどの程度なのか、とても興味深いところである。処理条件に従って次のような表を作 成しなさい。 【処理条件】 ① 列幅、罫線、文字や数値の形式など、表の体裁は見やすいように設定する。 ② F 列に得点を入力し、合計得点（B7）を求める（SUM 関数）。 ③ 合計得点が 20 点以上の場合は「集中力あり」、10～19 点の場合は「まあまあ」、10 点未満の場合は 「集中力欠如」と、診断結果（B9）に表示する（IF 関数）。
3 集中力度チェックテストの実施例 1.5 のぞみの時刻表 新幹線「のぞみ」は、東京～博多間を約 5 時間で走行する超高速列車である。処理条件に従って次の ような表を作成しなさい。 【処理条件】 ① 次表のような新幹線「のぞみ」の駅間の距離と時間を求める表を作成する。 ② 列幅・罫線・文字や数値の形式など、表の体裁は見やすいように設定する。 ③ 発駅コード（B3）と着駅コード（B4）を入力すると、（A6：A9）に駅名と距離・所要時間を表示 する。 ④ 使用する関数は、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 の値」がマイナスの場合はプラスにする。
4 新幹線「のぞみ」の駅間距離と駅間時間を求める表 ⑤ A6 の式＝B3 が未入力なら何も表示せず、そうでなければ VLOOKUP(B3,D4:E11,2,0)を表示する。 すなわち、セル A6=if(B3=””,””,VLOOKUP(B3,D4:E11,2,0)) ⑥ A7 の式＝A6 の式に準じる。 ⑦ A8 の 式 ＝ B4 が 未 入 力な ら 何も 表 示せず 、 そ うで な けれ ば 、 ABS(VLOOKUP(B4,D4:F11,3,0) － VLOOKUP(B3,D4:F11,3,0))を表示する。 ⑧ A9 の式=B4 が未入力なら何も表示せず、そうでなければ、 ABS(VLOOKUP(B4,D4:G11,4,0)-VLOOKUP(B3,D4:F11,4,0))を表示する。 ⑨ A9 の書式：時刻 実施例 1.6 万年カレンダーの作成 （♪） （注）（♪）のある問題は、遅れている人は飛ばして下さい。遅れている人と進んでいる人との調整 を取るための問題です。 以下の説明で、【説明】の部分は理解を深めるための数式等の説明で、操作の説明ではないから、読み 飛ばしても万年カレンダー作成に影響はない。
5 年と月を入力すると日付が自動的に変更されるカレンダーを作成する。 (1)次図のように入力する (2)セルに名前を定義する。 ①A1 をクリックしてアクティブにする。 ②数式バーの左横にある［名前］ボックスをクリックする。 名前ボックス ③ A1 と表示されているところに「年」を入れて［Enter］キーを押す。セル A1 に年という名前がつ いて、名前ボックスの表示が A1 から年に変わる。 ④ 同様に A2 に「月」という名前を、G1 に「月初の曜日」という名前をつける。 (3)指定した年月の 1 日が何曜日かを求める。 ①G1 に「=WEEKDAY(年&”/”&月&”/”&1,1)」を、年月以外は半角で入力する。7 が現れる。 【説明】「&」は文字を結合するので、入力した式は「=WEEKDAY(年/月/1,1)」となる。この式は指定 した年月の 1 日目は何曜日かを求めるもので、曜日が数字（日=1、月=2、･･･、土=7）で示される。 セル G1 には 2011 年 10 月 1 日の曜日「7（＝土曜日）」が現れる。月（A2）を変えるとその月の 1 日 目の曜日が数字で G1 に現れる。 (4)日付の設定 カレンダーの第 1 週目の各セルは「空白」「１」「前日の値＋１」のいずれかである。 ①A5 に「=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 は空白になる。 ②B5 に「=IF(月初の曜日=B3,DAY(DATE(年,月,1)),IF(A5=””,””,DAY(DATE(年,月,A5)+1)))」を入力す る。 【説明】1 日が月曜日(2）から土曜日(7）の場合は、B3(2)～G3(7)のいずれかが月初の曜日（G1）と
6 一致する。一致した数字の曜日が月初の曜日であるから、その曜日の欄に1 が入る。一致しない場合 は 1 以外、即ち空白か 2 以上の値が入る。前日が空白なら空白で、前日が空白でなければ前日に 1 加えた値になる。このような処理をするのが②の式である。 ③B5 の式を G5 までコピーする。G5 に 1 が現れる。 ④第 2 週～第 6 週のセルは前日のセルの値に 1 加えた値である。 A6 に「=G5+1」、B6 に「=A6+1」を入力する。 B6 の数式を G6 までコピーする。 A6:G6 を範囲指定して 10 行目までコピーする。 (5)翌月の日付部分の処理 第5・第 6 週の日付が翌月になるときは、条件付書式でフォントの色を白に設定して見えないようにす る。 ① A9:G10 を範囲指定→ ② ［ホーム］タブの［スタイル］で［条件付書式］をクリックし、［ルールの管理］をクリックする。 「条件付き書式ルールの管理」ダイアログボックスが表示される。 ③ 「新規ルール」をクリックする。「新しい書式ルール」ダイアログボックスで「数式を使用して、 書式設定するセルを決定」をクリックする。 ④ 「次の数式を満たす場合に値を書式設定」のボックスに［=MONTH(DATE(年,月,A9))<>$A$2 ］を入 力する。［書式］をクリックすると、「セルの書式設定」ダイアログボックスが表示される。 ④ のボックスをクリックして「白色」を選択→［OK］→「新しい書式ルール」ダイアログボックスに 戻る→「OK」→「条件付き書式ルールの管理」ダイアログボックスに戻る→［OK］→ 翌月の部分 が白字になり見えなくなる。 (6)万年カレンダー A1 に年、A2 に月を入力すると、その月のカレンダーが表示される。 1.7 年賀はがきの当選番号（♪） 毎年たくさんの年賀はがきをもらっても、１等はなかなか当たらないものである。処理時要件に従っ て次のような表を作成しなさい。 【処理条件】 ① 次表に示す年賀はがき当選番号判定表を作成する。 ② 列幅・罫線・文字や数値の形式など、表の体裁は見やすいように設定する。 ③ 当選番号（B3）を入力し、D 列の番号が当たっている場合は「当たり」と表示し、そうでない場合 は「はずれ」と表示する。 ④ （B5）には、当選枚数を表示する。
