d_cj01

青 森 県 総 合 学 校 教 育 センター 情 報 教 育 長 期 研 究 講 座 報 告 [2014.3] G8-02 高 等 学 校 情 報 活 用 Excel による 入 試 処 理 システムの 作 成 青 森 県 立 五 所 川 原 工 業 高 等 学 校 教 諭 秋 田 春 樹 要 旨 本 研 究 では, 表 計 算 ソフトウェアを 活 用 して, 入 試 業 務 における 各 種 処 理 を 正 確 かつ 効 率 的 に 行 うことのできるシステムの 作 成 を 目 的 とした そのために, 現 行 で 使 用 されている 入 試 処 理 システムの 問 題 点 や 改 善 点, 必 要 な 処 理 内 容 を 分 析 し, 表 計 算 ソフトウェアMicrosoft Excel 2010( 以 下 Excel と 記 す )を 活 用 して,シス テム 作 成 について 研 究 した キーワード: 簡 単 な 処 理 関 数 配 列 数 式 抽 出 マクロ Ⅰ 主 題 設 定 の 理 由 入 試 業 務 においては, 正 確 かつ 効 率 の 良 い 入 試 処 理 システムが 要 求 される 現 行 のシステムは, 入 力 セル とデータの 反 映 されるセルが 各 シートに 点 在 し, 初 めて 使 う 人 には 処 理 の 内 容 が 分 かりづらい また, 手 動 で 処 理 する 部 分 が 多 く, 業 務 に 手 間 がかかる 以 上 の 理 由 から,システムの 作 成 者 以 外 が 処 理 の 修 正 変 更 等 をすると 考 え, 現 行 のシステム 同 様,Excel で 入 試 処 理 システムを 作 成 することにした Ⅱ 研 究 目 標 本 研 究 は, 基 本 的 なExcel の 知 識 をもった 人 ならば 簡 単 に 扱 うことができ,また, 処 理 に 不 具 合 等 があっ た 場 合 でも 修 正 しやすい 入 試 処 理 システムを 作 成 し, 実 際 の 現 場 で 即 活 用 できることを 目 標 とした Ⅲ 研 究 の 実 際 1 入 試 処 理 システムの 流 れ

2 入 試 処 理 システムの 各 種 シート (1) 入 力 シート 入 力 シートは 基 本 情 報 シート 調 査 書 シート 学 力 検 査 シート 面 接 シート の 四 つに 分 類 さ れ, 全 てのデータ 入 力 はこの 四 つのシートで 行 う また,これらのシートに 入 力 されたデータが 他 のシー トに 反 映 されて 各 処 理 を 行 う 基 本 情 報 シート を 図 1に 示 す このシートでは, 受 検 番 号, 学 科, 氏 名,ふりがな, 性 別, 中 学 校 名, 第 2 志 望, 卒 業 年 度, 学 力 検 査 室, 面 接 室, 合 否 を 入 力 する 受 検 番 号 を 入 力 するとそれに 対 応 した 学 科 が 自 動 で 表 示 される また, 性 別 や 中 学 校 名, 第 2 志 望 にお いても, 番 号 のみ 入 力 することで 自 動 表 示 されるように 処 理 している データは 全 てテスト 用 に 作 成 したデータである 図 1 基 本 情 報 シート 調 査 書 シート を 図 2に 示 す このシートは, 調 査 書 に 記 載 されている 欠 席 日 数, 教 科 の 評 定, 特 記 事 項, 部 活 動 得 点 を 入 力 する 欠 席 と 評 定 の 合 計 を 求 める 関 数 式 は,IF 関 数 とSUM 関 数 を 組 み 合 わせることにより, 受 検 番 号 が 入 力 さ れている 場 合 のみ 計 算 表 示 するよう 処 理 している 部 活 動 の 入 力 も 番 号 のみ 入 力 することで 自 動 表 示 されるよう 処 理 している 図 2 調 査 書 シート 学 力 検 査 シート を 図 3に 示 す このシートは, 学 力 検 査 採 点 終 了 後 に, 各 教 科 の 得 点 を 入 力 する 学 力 検 査 得 点 の 合 計 を 求 める 関 数 式 は,IF 関 数 とSUM 関 数 を 組 み 合 わせることにより, 受 検 番 号 が 入 力 されている 場 合 のみ 計 算 表 示 するよう 処 理 している 図 3 学 力 検 査 シート

面 接 シートを 図 4に 示 す このシートは, 面 接 終 了 後 に, 面 接 での 得 点 と 特 記 事 項 を 入 力 する 面 接 得 点 の 合 計 を 求 める 関 数 式 は,IF 関 数 とSUM 関 数 を 組 み 合 わせることにより, 受 検 番 号 が 入 力 され ている 場 合 のみ 計 算 表 示 するよう 処 理 している 図 4 面 接 シート (2) 処 理 シート 処 理 シートでは, 関 数 や 抽 出 を 用 いて 受 検 者 名 簿 面 接 誘 導 名 簿 面 接 名 簿 選 抜 会 議 資 料 中 学 校 訪 問 名 簿 合 格 者 名 簿 得 点 開 示 等 の 作 成 を 自 動 化 した 選 抜 資 料 シート を 図 5に 示 す 調 査 書 や 学 力 検 査, 面 接 のデータはIFERROR 関 数 とVLOOKUP 関 数 を 組 み 合 わせることにより, 受 検 番 号 が 入 力 されている 場 合 のみ 必 要 なデータを 抜 き 出 して 表 示 させている 群 分 けや 順 位 付 けに 関 してはIF 関 数 やAND 関 数,RANK 関 数 等 を 用 いて 処 理 している 図 5 選 抜 資 料 シート (3) 報 告 用 シート 主 にSUMPRODUCT 関 数 を 用 いて, 総 括 表 選 抜 結 果 合 格 者 数 報 告 等 の 県 への 報 告 に 必 要 なシー トの 作 成 を 自 動 化 した 合 格 者 数 報 告 シート を 図 6に 示 す 図 6 合 格 者 数 報 告 シート( 様 式 第 10 号 )

3 入 試 処 理 システムの 各 種 処 理 (1) 使 用 関 数 SUM 関 数,AND 関 数,OR 関 数,IF 関 数,COUNTIF 関 数,RANK 関 数,VLOOKUP 関 数,ISERROR 関 数, IFERROR 関 数,SUMPRODUCT 関 数 (2) 配 列 数 式 配 列 数 式 とは, 表 の 中 の 複 数 のセルを 含 む 範 囲 を 一 つのかたまり( 配 列 )として 式 の 中 で 取 り 扱 うもの である 本 システムでは, 配 列 数 式 を 使 って 条 件 付 きの 順 位 を 求 める 処 理 を 行 った 図 7は, 学 年 全 体 の 集 計 表 の 中 でクラス 順 位 を 求 める 配 列 数 式 の 例 である =SUM(IF($A$4:$A$12=A4,IF($C$ 4:$C$12>C4,1)))+1と 入 力 し, [Ctrl][Shift ][Enter ]を 押 し, 配 列 数 式 として 処 理 させる 順 位 というのは 自 分 より 大 きな 数 値 の 数 をかぞえて,それに1を 足 す 処 理 であるため,この 配 列 数 式 では 同 一 学 科,かつ, 点 数 が 自 分 よ り 大 きいものを1,それ 以 外 を0 として,その 合 計 を 求 め,さらに その 合 計 値 に1を 足 す ことによ って 条 件 付 き 順 位 を 割 り 出 してい る 選 抜 結 果 報 告 シート を 図 8 に 示 す このシートでは 複 数 の 学 科 のデータが 入 力 されているため, 下 記 のような 配 列 数 式 で 処 理 を 行 った 図 7 配 列 数 式 の 例 {=SUM(IF($AD$10:$AD$310=$AD$8,IF($Z$10:$Z$310=Z10,IF($T$10:$T$310>T10,1,""))))+1} 図 8 選 抜 結 果 報 告 シート

(3) データの 抽 出 Excel では,ある 条 件 のデータを 抽 出 するために,VLOOKUP 関 数 やMATCH 関 数 とINDEX 関 数 を 組 み 合 せ た 関 数 式 を 用 いる しかし, 複 数 のデータを 抽 出 するためには 非 常 に 長 い 関 数 式 となることから,フィル ターオプションの 設 定 による 抽 出 方 法 を 用 いた 図 9 フィルターオプションの 設 定 例 図 10 検 索 条 件 範 囲 の 例 図 11 抽 出 されたデータ 図 9はフィルターオプションの 設 定 例 である 検 索 条 件 範 囲 ( 図 10)で 指 定 した 学 科 と 同 じ 項 目 をリス ト 範 囲 から 抽 出 し, 抽 出 されたデータ( 図 11)を 抽 出 範 囲 で 指 定 したセルに 反 映 させる 図 12は 中 学 校 訪 問 名 簿 シート である 検 索 条 件 範 囲 を 中 学 校 名 とすることで, 検 索 条 件 に 合 致 した 中 学 校 の 受 検 生 の 受 検 番 号, 氏 名,ふりがな, 性 別, 合 格 学 科 を 基 本 情 報 シート から 抽 出 している 図 12 中 学 校 訪 問 名 簿 シート( 検 索 条 件 範 囲 : 中 学 校 名 ) 図 13は 合 格 者 名 簿 シート である 検 索 条 件 範 囲 を 合 格 学 科 とすることで, 検 索 条 件 に 合 致 した 学 科 の 受 検 番 号, 氏 名,ふりがな, 性 別, 中 学 校 名 を 基 本 情 報 シート から 抽 出 している 図 13 合 格 者 名 簿 シート( 検 索 条 件 範 囲 : 合 格 学 科 )

(4) 条 件 付 き 書 式 設 定 図 5に 示 した 選 抜 資 料 シート で, 群 を 最 も 優 先 させる 値 としてソートした 場 合 は, 群 が 空 白 の 行 が 上 位 にくるため, 選 抜 対 象 外 のセルの 群 を Ⅳ と 表 示 させることで,ソートしたときに 選 抜 対 象 外 のセ ルが 下 位 にくるよう 処 理 をした しかし, Ⅳ という 表 示 は, 選 抜 資 料 には 不 必 要 な 値 のため, 条 件 付 き 書 式 設 定 で 非 表 示 (フォントの 色 を 白 )にすることで 対 処 した また, 図 8に 示 した 選 抜 結 果 報 告 シート では, 卒 業 年 度 が 過 年 度 の 場 合 自 動 で 処 理 できないため, 空 白 として 表 示 させ, 条 件 付 き 書 式 設 定 で, 塗 りつぶしの 色 を 赤 に 変 わるよう 処 理 した (5) 入 力 規 則 データの 入 力 規 則 を 設 定 することで, 入 力 ミスを 防 ぐようにした 調 査 書 シート の 各 学 年 の 評 定 は 入 力 値 の 種 類 を 整 数 とし,0から45までの 値 のみ 入 力 できるように 設 定 した 学 力 検 査 シート の 各 教 科 の 得 点 と 面 接 シート の 各 面 接 委 員 の 得 点 は 入 力 値 の 種 類 を 整 数 とし,0から 100までの 値 のみ 入 力 できるように 設 定 した その 他 に, 抽 出 の 処 理 を 行 うシートでは, 抽 出 に 用 いる 検 索 条 件 をリストから 選 択 するように 設 定 する ことで, 限 られた 値 のみを 入 力 できるよう 処 理 した (6) マクロの 記 録 データの 並 べ 替 えや 抽 出 といった 簡 単 な 操 作 をマクロとして 記 録 することで, 処 理 システムの 操 作 を 簡 単 にした しかし, 記 録 したマクロ( 図 14)は,モジュール(プログラム 言 語 )として 記 録 され 動 作 する ため 処 理 に 不 具 合 があった 場 合 にプログラム 言 語 を 理 解 していないと 対 処 できない 本 システムでは, 不 具 合 があった 場 合 でも 手 動 で 処 理 できる 内 容 にとどめることでマクロに 頼 らないシステムを 作 成 した 図 14 マクロの 記 録 により 作 成 されたモジュール (7) ブックとシートの 保 護 関 数 やマクロの 変 更 を 制 限 するため,また, 誤 改 変 を 防 止 するため,パスワードで 保 護 した Ⅳ 研 究 の 成 果 と 課 題 1 研 究 の 成 果 従 来 のシステムと 比 較 して, 効 率 よく 処 理 できるシステムを 作 成 することができた 入 力 シートと 処 理 シートを 分 けることで, 操 作 を 簡 単 にすることができた 2 今 後 の 課 題 平 成 27 年 度 入 学 生 から 実 施 される 新 制 度 へも 対 応 できるよう 入 試 処 理 システムの 修 正 をする 実 際 に 使 用 して 得 られた 改 善 点 を 基 に, 随 時, 入 試 処 理 システムの 修 正 変 更 をしていく この 報 告 書 に 表 示 されているデータは 全 てテスト 用 に 作 成 したデータである < 商 標 > Excelは,Microsoft Corporationの 登 録 商 標 である