2016 年 度 情 報 リテラシー 三 科 目 合 計 の 算 出 関 数 を 用 いて 各 教 科 の 平 均 点 と 最 高 点 を 求 めることにする この2つの 計 算 は [ホーム]タブのコマ ンドにも 用 意 されているが 今 回 は 関 数 として 作 成 する まず 表 に 三 科

( 第 9 回 )2016/06/13 Excel 関 数 の 基 礎 この 回 では Excel での 数 値 処 理 に 役 立 つ 関 数 について 解 説 する 1. 課 題 の 確 認 成 績 の 集 計 について 関 数 を 利 用 して 行 う 利 用 するソフトウェア:Microsoft Excel 1.1. 演 習 の 内 容 関 数 は 表 計 算 ソフトで 数 値 処 理 を 自 動 化 するものである 例 えば 合 計 平 均 条 件 判 断 などがある 関 数 の 記 述 には 基 本 的 な 形 があり この 形 を 覚 えておくことで 様 々な 関 数 に 対 応 することができる ここでは 成 績 の 集 計 を 題 材 にいくつかの 関 数 を 利 用 するが 基 本 形 を 意 識 して 作 業 を 進 めること 2. 例 題 成 績 の 集 計 : 関 数 の 活 用 ここでは 下 記 のデータ 集 計 作 業 を 通 して Excel における 関 数 の 利 用 ついて 解 説 する 設 定 塾 講 師 のバイトをしており 期 末 テストの 集 計 を 行 うことになった 成 績 には 受 験 者 の 採 点 結 果 が 記 録 されており このデータを 用 いて 成 績 一 覧 成 績 の 状 況 を Excel でまとめることにする 2.1. テスト 結 果 のデータ ここでは 下 表 の 成 績 データを 基 に 成 績 を 集 計 する 作 業 について 解 説 する 表 成 績 データ 名 前 数 学 英 語 物 理 数 学 英 語 物 理 浅 田 60 40 43 関 口 84 97 91 石 田 89 86 70 田 中 75 46 79 石 山 94 76 78 轟 72 92 69 稲 葉 91 94 51 服 部 92 96 79 植 山 75 96 70 東 野 31 50 29 坂 井 29 48 16 藤 田 91 78 74 酒 本 72 22 43 藤 原 23 66 62 佐 藤 76 71 71 山 口 98 96 84 鈴 木 50 84 23 山 田 74 80 67 2.2. 成 績 の 集 計 ここでは 成 績 データを 用 いて 受 験 者 の 総 得 点 各 教 科 の 平 均 最 高 得 点 合 否 判 定 を 作 成 する また 各 項 目 の 数 式 は 自 分 で 作 成 するのではなく Excel に 備 えられている 関 数 を 用 いることにする Excel では 一 般 的 に 用 いられることの 多 い 計 算 が 関 数 としてまとめられている 関 数 を 用 いることで 合 計 平 均 などの 簡 単 な 計 算 から 統 計 処 理 数 学 関 数 文 字 列 操 作 など 多 様 な 計 算 データ 操 作 を 行 うことができる 情 リテ 9-1

2016 年 度 情 報 リテラシー 三 科 目 合 計 の 算 出 関 数 を 用 いて 各 教 科 の 平 均 点 と 最 高 点 を 求 めることにする この2つの 計 算 は [ホーム]タブのコマ ンドにも 用 意 されているが 今 回 は 関 数 として 作 成 する まず 表 に 三 科 目 合 計 平 均 と 最 高 点 の 項 目 を 用 意 する 項 目 を 入 力 する 際 適 宜 罫 線 など を 設 定 し 分 かりやすい 表 作 成 を 心 がけること 三 科 目 合 計 平 均 最 高 点 図 項 目 の 準 備 合 計 を 算 出 する 関 数 は 図 のとおりである メニューから 入 力 する 場 合 は [ホーム]タブの[Σ] ま たは[ 数 式 ]タブの[Σ]をクリックして[ 合 計 ]をクリックする 今 回 はこのメニューを 用 いず 直 接 関 数 を 入 力 する 方 法 で 解 説 する = SUM ( 合 計 する 範 囲 ) =SUM (B2:E2) 図 SUM 関 数 の 例 まず 作 成 した 場 所 に SUM 関 数 を 入 力 する 関 数 の 入 力 は 数 式 と 同 様 で セルに = を 入 力 した 後 に 下 図 の 手 順 通 りに 作 業 を 行 えばよい = を 入 力 する SUM( と 入 力 情 リテ 9-2

( 第 9 回 )2016/06/13 合 計 の 対 象 となるセルを 範 囲 選 択 最 後 に ) を 入 力 して Enter 図 関 数 の 入 力 図 では 関 数 名 を 直 接 入 力 する 方 法 を 示 した メニューから 実 行 することもできるが ある 程 度 慣 れ てくると 直 接 入 力 した 方 が 効 率 良 い また 関 数 も 数 式 と 同 様 に 数 式 コピーを 用 いて 同 じ 計 算 を 繰 り 返 し 設 定 することが 可 能 である ここでは 一 人 分 の 計 算 が 完 了 した 後 数 式 コピーで 残 りの 計 算 も 実 施 する 平 均 点 最 高 点 の 算 出 次 に 関 数 を 用 いて 科 目 ごとの 平 均 点 を 算 出 する 平 均 最 大 値 を 求 める 関 数 は 下 記 のとおりであり 入 力 方 法 は SUM 関 数 と 同 様 の 方 法 で 行 う 平 均 を 求 める 関 数 は 下 記 のとおり = AVERAGE ( 範 囲 ) =AVERAGE (B2:E2) 図 AVERAGE 関 数 選 択 範 囲 の 最 大 値 最 小 値 を 求 める 関 数 は 下 記 のとおり = MAX ( 範 囲 ) = MIN ( 範 囲 ) =MIN (B2:E2) =MAX (B2:E2) 図 MAX MIN 関 数 上 記 関 数 を 用 いることで 下 記 のように 結 果 が 得 られる 平 均 最 高 点 ともに 横 へ 数 式 コピーを 行 い 各 教 科 の 結 果 を 算 出 しておく 情 リテ 9-3

2016 年 度 情 報 リテラシー 図 平 均 点 の 算 出 図 最 高 点 の 算 出 平 均 点 の 四 捨 五 入 ( 関 数 の 組 み 合 わせ) ここでは 平 均 点 の 小 数 点 を 丸 めるために 関 数 を 利 用 する 四 捨 五 入 を 行 う 関 数 として ROUND 関 数 があるので この 関 数 を 使 って AVERAGE で 算 出 した 平 均 点 を 小 数 点 第 一 位 で 四 捨 五 入 する =ROUND( 参 照 セル 桁 数 ) =ROUND (B2, 2) 図 ROUND 関 数 このような 場 合 関 数 は 単 一 で 利 用 するだけでなく 組 み 合 わせて 利 用 する 例 えば 平 均 値 の 値 を 四 捨 五 入 する 場 合 計 算 式 は 下 記 のように 設 定 することで 1つのセルで2つ 以 上 の 関 数 を 組 み 合 わせ て 利 用 できる 図 ROUND 関 数 内 に AVERAGE 関 数 を 入 れ 子 にした 例 情 リテ 9-4

( 第 9 回 )2016/06/13 ここでは 平 均 点 を 小 数 点 第 一 位 で 丸 めるので ROUND 関 数 の 桁 数 の 設 定 は 1 となる 結 果 は 右 にコピーして 後 の 科 目 についても 同 じ 計 算 を 行 う ROUND の 中 に AVERAGE を 入 れる 図 平 均 点 を ROUND 関 数 で 丸 めた 結 果 2.3. 評 価 の 算 出 ここでは 点 数 によって 合 格 不 合 格 の 評 価 を 表 示 するための 関 数 を 作 成 する まず 三 科 目 合 計 の 隣 に 合 否 と 見 出 しのついた 項 目 を 用 意 する ここに ある 一 定 の 点 数 以 上 の 場 合 合 格 それ 以 外 の 場 合 不 合 図 項 目 の 用 意 格 と 表 示 する 関 数 を 作 成 する この 作 業 を 実 現 する 方 法 として IF という 関 数 を 用 いることで 実 現 できる IF 関 数 は 条 件 に 従 って 出 力 する 数 値 文 字 列 を 指 定 できる 関 数 である ここで 論 理 式 とは 条 件 判 断 の 基 準 となる 式 を 言 う あるセルが と 同 じならば というよう な 判 断 をするときに 作 成 する 式 のことである =IF( 論 理 式 真 の 場 合 偽 の 場 合 ) =IF( 参 照 先 >=100,1,0) 図 IF 関 数 の 例 ここで 評 価 の 基 準 は 別 途 セルに 入 力 し 関 数 内 で 参 照 することで 合 否 の 判 断 を 行 いたい ここで 注 意 しなくてはならないのは 評 価 の 基 準 を 参 照 するセルは 固 定 されなくてはならないところである 多 数 の 成 績 を 評 価 するため 一 度 作 成 した 関 数 は 数 式 コピーを 利 用 したい しかし 数 式 コピーは 相 対 的 に 参 照 がずれる このような 場 合 参 照 を 固 定 する 必 要 がある このような 場 合 絶 対 参 照 という 方 法 を 利 用 する 情 リテ 9-5

2016 年 度 情 報 リテラシー 絶 対 参 照 相 対 参 照 について Excel では セルの 参 照 が 行 われるが 数 式 や 関 数 のコピーを 行 うと 参 照 は 相 対 的 に 移 動 するように 基 本 設 定 されている 場 合 によっては 参 照 の 移 動 を 固 定 したい 場 合 がある( 例 えば 税 率 を 用 いた 計 算 な ど ) 固 定 したい 参 照 ( 数 式 コピー 時 に 動 かしたくない) 移 動 したい 参 照 ( 数 式 コピー 時 に 動 かしたい) 図 参 照 が 問 題 になる 計 算 この 場 合 絶 対 参 照 という 機 能 を 使 えばよい 絶 対 参 照 は 参 照 セル 番 号 に $ を 付 記 することで 設 定 される また 絶 対 参 照 は[F4]キーでも 設 定 できる =$A$1 =A1 ( 通 常 の 参 照 ) ( 行 列 の 参 照 を 固 定 ) =A$1 ( 行 の 参 照 を 固 定 ) =$A1 ( 列 の 参 照 を 固 定 ) [F4]キーで 絶 対 参 照 の 設 定 が 切 り 替 わる 相 対 参 照 絶 対 参 照 =A1+B21 =A1+B21 =A$1+B21 数 式 を 下 方 にコピ =A2+B22 =A$1+B22 ーした 場 合 =A3+B23 =A$1+B23 =A4+B24 =A$1+B24 図 絶 対 参 照 の 設 定 $のついた 箇 所 が 固 定 される 情 リテ 9-6

( 第 9 回 )2016/06/13 IF 関 数 と 絶 対 参 照 の 手 法 を 用 いて 合 否 を 判 定 する 数 式 を 作 成 す る 適 当 な 場 所 に 合 格 点 を 示 す 数 値 を 入 力 しておく この 数 値 が 合 否 判 定 の 基 準 となる 図 合 格 点 の 設 定 合 否 判 定 を 出 力 する IF 関 数 では 下 記 の 設 定 が 必 要 となる 三 科 目 合 計 を 合 格 点 以 上 であれば 合 格 と 判 定 するが 判 定 基 準 は 別 のセルを 参 照 する 以 上 を 数 式 で 表 現 する 場 合 >= と 入 力 する 判 定 の 記 載 方 法 は 次 表 を 参 照 表 判 定 の 際 に 用 いる 演 算 子 以 上 以 下 より 上 より 下 >= <= > < 判 定 が 成 り 立 った 場 合 ( 真 ) 出 力 する 値 は 合 格 成 り 立 たなかった 場 合 ( 偽 ) 出 力 する 値 は 不 合 格 で ある 数 式 で 文 字 列 を 扱 う 場 合 は (ダブルクォーテーション)を 用 いる 合 否 判 定 の 箇 所 に IF 関 数 を 入 力 する まず =if( までを 入 力 する 次 に 参 照 先 である 三 科 目 合 計 をクリックし 続 いて >= を 入 力 する 判 定 基 準 である 合 格 点 を 参 照 する これで 三 科 目 合 計 が 合 格 点 以 上 だった 場 合 という 論 理 式 となる 論 理 式 の 後 を, で 区 切 り ダブルクォーテーションで 囲 む 形 で 合 格 不 合 格 を 入 力 する 前 者 が 真 の 場 合 後 者 が 偽 の 場 合 である 情 リテ 9-7

2016 年 度 情 報 リテラシー Enter を 押 して 数 式 の 結 果 を 確 認 する 次 に 数 式 コピーを 行 うが 合 格 点 への 参 照 がずれないように 合 格 点 の 参 照 を 絶 対 参 照 で 固 定 する 数 式 が 入 力 されたセルをダブルクリックし 合 格 点 を 参 照 している 箇 所 をクリックしてカーソルを 合 わせ る カーソルが 参 照 セルにある 状 態 で キーボードの F4 キーを 押 す すると 参 照 セルに $ がつい た 状 態 になる これで 参 照 を 固 定 することができる 図 絶 対 参 照 の 設 定 以 上 の 設 定 を 終 えた 後 必 要 な 箇 所 まで 数 式 をコピーする ここで 確 認 すべきことは 三 科 目 合 計 への 参 照 は 相 対 的 に 移 動 しているが 合 格 点 への 参 照 が 常 に 固 定 されていることであ る 2.4. 関 数 の 活 用 Excel には 多 数 の 関 数 が 用 意 されているが どのような 関 数 があるのかなかなか 把 握 しにくい この 場 合 [ 数 式 ]タブ 内 のメニューを 開 き 目 的 に 近 いと 思 われる 関 数 にマウスをポイントして 表 示 される 説 明 を 参 照 する 情 リテ 9-8

( 第 9 回 )2016/06/13 数 式 メニューでマウスをポイント すると 説 明 が 表 示 される 図 数 式 タブ また 目 的 に 合 致 した 関 数 を 入 力 する 過 程 で 何 を 設 定 する 必 要 があるのか 分 かりにくいことがあ る その 場 合 関 数 を 入 力 する 際 に 表 示 されるヒントを 確 認 すればよい 数 式 メニューでマウスをポイント すると 説 明 が 表 示 される 図 関 数 入 力 時 に 表 示 されるヒント 情 リテ 9-9

2016 年 度 情 報 リテラシー 課 題 講 義 で 作 成 したファイルを(1)~(2)の 指 示 のとおり 変 更 せよ 目 的 例 題 で 作 成 した 成 績 表 の 集 計 に 三 科 目 平 均 点 総 合 得 点 の 20 点 換 算 を 算 出 せよ (1) 三 科 目 平 均 点 の 算 出 受 験 者 ごとの 三 科 目 平 均 点 を 合 計 の 左 に 挿 入 せよ 計 算 結 果 は 関 数 を 用 いて 小 数 点 第 一 位 を 残 して 四 捨 五 入 ( 第 二 位 で 四 捨 五 入 )せよ (2)20 点 換 算 の 算 出 受 験 者 ごとに 総 合 得 点 を 20 点 満 点 に 換 算 せよ ここで 利 用 する 20 点 は 別 のセルに 記 載 し 絶 対 参 照 を 用 いて 参 照 すること なお ここでも 関 数 を 用 いて 小 数 点 第 一 位 を 残 して 四 捨 五 入 ( 第 二 位 で 四 捨 五 入 )せよ 20 点 換 算 の 算 出 方 法 は ( 総 合 得 点 ) ( 総 合 点 満 点 ) 20 である 20 点 換 算 では 換 算 する 値 を 絶 対 参 照 する 平 均 を 求 め 少 数 第 一 位 で 四 捨 五 入 する 総 合 得 点 を 20 点 換 算 し 少 数 第 二 位 で 四 捨 五 入 ( 小 数 点 第 一 位 を 残 す) 図 計 算 のイメージ 必 ず 件 名 に 組 - 学 籍 番 号 情 報 リテラシー 第 9 回 課 題 を 記 載 すること 例 )3 組 学 籍 番 号 p16991 の 場 合 3-991 情 報 リテラシー 第 9 回 課 題 情 リテ 9-10