情 報 科 学 総 合 課 題 集 V2012-1 総 合 課 題 1 美 容 室 の 経 営 効 率 分 析 資 料 の 作 成 この 課 題 のポイント Excle のさまざまな 機 能 を 利 用 して 効 率 よく 作 業 する セル 参 照 での 相 対 指 定 と 絶 対 指 定 を 上

情 報 科 学 総 合 課 題 集 V2012-1 情 報 科 学 総 合 課 題 集 2012 年 度 第 1 版 H.Ohtsuka,H.Takatsuji,K.Nakamoto,K.Yoshida. 総 合 課 題 について 総 合 課 題 1 から 総 合 課 題 21 までの 各 課 題 から 10 問 以 上 を 選 択 して 行 うこと それぞれ 指 定 の 方 法 で 全 て A4 版 に 印 刷 すること 基 本 的 に 用 紙 は 横 置 きに 使 い 各 課 題 番 号 順 にならべて 左 側 を 綴 じる 表 紙 は 指 定 のも のを 編 集 して 必 ず 付 けること 不 正 コピーによる 課 題 提 出 を 防 止 するため 全 ての 課 題 のワークシートをファイルで 提 出 する 作 成 した 課 題 ファ イルは 課 題 毎 にブック 形 式 で 保 存 すること( 同 一 課 題 の 拡 張 問 題 上 級 問 題 応 用 問 題 自 主 問 題 などはタブを 使 って1ブックに 複 数 のシートを 作 成 してよい) ファイルの 提 出 方 法 は 提 出 日 に 具 体 的 に 指 示 する なお 提 出 ファ イルは 重 複 チェックされる コピーによる 作 成 は 簡 単 に 検 出 される コピーによる 提 出 が 発 覚 した 場 合 コピー 元 を 提 供 した 者 コピーした 者 の 両 方 の 全 ての 課 題 が0 点 となるので 注 意 すること ヘッダ フッタについて 以 下 のように 各 課 題 にヘッダ フッタを 付 ける( 通 常 ページ 番 号 が 入 る 位 置 に 課 題 番 号 が 入 る) ヘッダ 左 : 氏 名 (ふりがな) ヘッダ 中 央 :クラス 名 ヘッダ 右 : 学 籍 番 号 フッタ 左 : 印 刷 日 付 フッタ 中 央 : 印 刷 時 刻 フッタ 右 : 課 題 番 号 課 題 -1 形 式 表 紙 ( 表 紙 と 目 次 となる 表 は 必 須 ) kadaiフォルダに 表 紙 用 のExcelファイルが 用 意 されている 課 題 は 課 題 番 号 を 昇 順 に 並 べること kadai Info-Sci 情 報 科 学 総 合 課 題 - 表 紙.xlsx を 各 自 のフォルダにコピーした 後 に 編 集 して 利 用 すること 必 要 事 項 が 書 き 込 まれていれば その 他 の 部 分 は 自 由 に 編 集 してもよい 表 紙 をカラー 印 刷 してもよい 電 子 メールでの 質 問 疑 問 点 が 十 分 整 理 されているようなものについては 電 子 メールで 質 問 できる なお すでに 配 布 したプリントに 記 載 されているようなことを 何 度 も 質 問 するのは 禁 止 です ヘルプデスクおよびTAさんには 課 題 について 一 切 の 質 問 に 答 えないよう 指 示 してありますので 質 問 は 必 ず 担 当 教 員 へ 電 子 メールで 行 うこと 表 題 は 以 下 のようにク ラス 名 氏 名 を 入 れ 情 報 科 学 Bの 総 合 課 題 についてであることが 分 かるようにすると 返 答 を 得 られやすい 表 題 (Subject): ( 情 報 科 学 B)B4クラス- 麗 澤 花 子 - 総 合 課 題 集 ついて メモ クラス: 質 問 送 付 先 アドレス( 担 当 教 員 アドレス): @reitaku-u.ac.jp 提 出 日 K1,K2 クラス,B4,IM クラス 12 月 17 日 ( 月 ) K3 クラス 12 月 19 日 ( 木 ) 中 本 B3 クラス( 水 5-1308) に 提 出 K4 クラス 12 月 19 日 ( 金 ) 中 本 B3 クラス( 水 5-1308) に 提 出 B1,B2,B3,KR,BR クラス 12 月 19 日 ( 水 ) 通 常 授 業 で 提 出 課 題 評 価 の 目 安 10 問 提 出 の 場 合 の 課 題 の 標 準 点 はBです(これはミスが 許 容 範 囲 内 の 場 合 です ミスが 多 い 場 合 はB-やC がつくので 注 意 しましょう) なお 提 出 課 題 数 の 制 限 は 無 いので 何 問 取 り 組 んでもかまいません( 当 然 加 点 される) 評 価 の 目 安 としては12 問 でB+,14 問 でA,16 問 でA++,18 問 以 上 がSとなります 全 ての 問 題 を 解 答 し た 場 合 には 欠 席 1/25 2 日 分 を 補 うことができます

情 報 科 学 総 合 課 題 集 V2012-1 総 合 課 題 1 美 容 室 の 経 営 効 率 分 析 資 料 の 作 成 この 課 題 のポイント Excle のさまざまな 機 能 を 利 用 して 効 率 よく 作 業 する セル 参 照 での 相 対 指 定 と 絶 対 指 定 を 上 手 に 使 い 分 ける 坪 あたり 単 価 や 売 上 単 価 などのビジネス 用 語 を 理 解 する ファイルの 置 き 場 所 Y: Info-Sci 情 報 科 学 総 合 課 題 sougou1.xlsx 基 本 問 題 あなたは ある 美 容 室 の 事 務 を 担 当 している この 度 店 長 から 各 支 店 の 経 営 効 率 の 分 析 を 指 示 され 各 支 店 の 経 営 効 率 分 析 資 料 を 作 成 することになった 次 の 指 示 に 従 い 経 営 効 率 分 析 資 料 を 作 成 しなさい なお 利 用 するワークシートは 前 期 売 上 表 シート 業 界 平 均 シート 単 価 計 算 と 比 較 シートの 3 つで ある 指 示 1 前 期 売 上 表 シートをもとに 各 店 舗 のつぼ 単 価 ( 一 坪 当 たり 売 上 )と 売 上 単 価 ( 一 人 当 たり 売 上 )を 集 計 す る その 際 次 の 指 示 に 従 うこと 1 単 価 計 算 と 比 較 シートの 指 示 1 の 領 域 に 集 計 を 行 うこと その 際 小 数 点 第 1 位 を 四 捨 五 入 し 整 数 で 表 示 すること 2 売 上 単 価 の 計 算 にあたっては 見 習 い 2 人 で 1 人 と 計 算 しスタッフと 合 わせた 数 を 真 のスタッフ 数 として 人 数 の 列 に 計 算 し 売 上 単 価 の 計 算 の 利 用 すること な お 端 数 が 出 る 場 合 は 切 り 捨 てる(Rounddown 関 数 か Int 関 数 を 利 用 し 1.5 人 は 1 人 として 扱 うこと) 指 示 2 指 示 1で 完 成 させた 表 と 業 界 平 均 シートのデータを もとに 単 価 計 算 と 比 較 シートの 指 示 2 の 領 域 にあ る 表 を 完 成 させない その 際 次 の 指 示 に 従 うこと 完 成 例 1 業 界 平 均 の 数 値 を 1 とした 場 合 の 各 月 の 単 価 を 指 数 として 表 しなさい 2 計 算 結 果 は 小 数 点 第 3 位 を 四 捨 五 入 し 小 数 点 第 2 位 まで 表 示 させること(Round 関 数 は 利 用 せず 書 式 設 定 にて 対 応 すること) 指 示 3 指 示 2で 完 成 させた 表 をもとに 各 支 店 の 売 上 単 価 を 比 較 するレーダーチャートを 作 成 し グラフ 配 置 領 域 に 配 置 しなさい その 際 次 の 指 示 に 従 うこと 1グラフのタイトルは 対 業 界 平 均 比 較 チャート とする こと 2グラフには 凡 例 を 表 示 すること 3 数 値 軸 の 最 大 値 は 1.3 とすること 4 数 値 軸 の 最 小 値 は 0.4 とすること シートの 印 刷 単 価 計 算 と 比 較 シートを 必 ず 横 向 き1 枚 に 縮 小 し て 印 刷 し 提 出 する ファイルの 提 出 ( 課 題 フォルダへのコピー) Excel ブック 形 式 (.xlsx)で 保 存 したファイルを 課 題 ドライブ(Y ドライブ)の 提 出 用 フォルダ 内 の 自 分 のクラ スの 自 分 のフォルダへコピーする 業 界 平 均 より 業 績 の 悪 い 店 舗 は だ! 2/25

情 報 科 学 総 合 課 題 集 V2012-1 総 合 課 題 2 家 電 卸 販 売 会 社 の 販 売 状 況 資 料 の 作 成 この 課 題 のポイント 関 数 を 組 み 合 わせて 目 的 の 結 果 を 得 る ピボットテーブル 機 能 を 利 用 して 数 値 を 集 計 する 利 益 率 の 概 念 を 理 解 する ファイルの 置 き 場 所 Y: Info-Sci 情 報 科 学 総 合 課 題 sougou2.xlsx 基 本 問 題 あなたは ある 家 電 卸 販 売 会 社 に 勤 務 している この 度 上 司 から 販 売 会 議 の 資 料 として 上 半 期 の 集 計 表 を 作 成 するよう 指 示 を 受 けた 次 の 指 示 に 従 い 販 売 状 況 の 資 料 を 作 成 しなさい なお 利 用 するワークシートは 売 上 実 績 シート 商 品 単 価 表 シート 集 計 シートの 3 つである 指 示 1 売 上 実 績 シートに 売 上 金 額 と 売 上 原 価 を 追 加 しな さい その 際 次 の 指 示 に 従 うこと 1E 列 に 販 売 単 価 を F 列 には 原 価 を Vlookup 関 数 を 用 いて 商 品 単 価 表 シートから 転 記 しなさい * Vlookup( 商 品 名 商 品 単 価 表 列 の 指 定 0) 検 索 範 囲 である 商 品 単 価 表 は 絶 対 参 照 とすること 2G 列 には 販 売 台 数 に 応 じて 次 のような 割 引 率 を 設 定 すること 販 売 台 数 割 引 率 20 台 以 上 10% 10 台 以 上 20 台 未 満 5% 10 台 未 満 0% * If 関 数 を 利 用 して 20 台 以 上 の 場 合 10 台 未 満 の 場 合 それ 以 外 として 条 件 分 けをすれば 論 理 関 数 を 利 用 せずに 求 めることができる 3H 列 に 売 上 金 額 I 列 に 売 上 原 価 の 計 算 をしなさい * 売 上 金 額 = 販 売 台 数 販 売 単 価 (100%- 割 引 率 ) * 売 上 原 価 = 販 売 台 数 原 価 3 粗 利 益 率 の 高 い 順 に 並 び 変 えること 4 表 のタイトルは 上 半 期 プリンタ 売 上 実 績 集 計 表 と すること ピボットテーブルによる 簡 単 集 計 同 じ 商 品 名 の 行 の 売 上 金 額 や 売 上 原 価 の 数 値 をす べて 合 計 したいとき どのようにすればよいだろうか? ピボットテーブルは こうした 集 計 を 簡 単 に 実 現 できる 機 能 である 1 売 上 実 績 シートを 完 成 させ セル A2 を 選 択 する 2[ 挿 入 ]タブから ピボットテーブルを 挿 入 して 出 てき たダイアログボックスにおいて OK ボタンを 押 す 3ピボットテーブルフィールドリストより 売 上 金 額 と 売 上 原 価 を 値 ボックスに 商 品 名 を 行 ラベル ボックスにドラッグアンドドロップする( 画 像 では 売 上 原 価 しか 設 定 していない) 指 示 2 指 示 1で 完 成 させた 表 をもとに プリンタ(3 種 類 )のみ に 集 計 した 表 を 作 成 しなさい 次 の 指 示 に 従 うこと 1 集 計 シートに 集 計 を 行 うこと なお やり 方 は ピボ ットテーブルによる 簡 単 集 計 を 参 照 のこと 2 粗 利 益 率 は 小 数 点 第 1 位 までのパーセント 表 示 に すること * 粗 利 益 率 = 粗 利 益 ( 売 上 金 額 - 売 上 原 価 ) 売 上 金 額 4 必 要 なデータをコピーし 集 計 シートに 貼 り 付 ける この 課 題 の 印 刷 方 法 この 課 題 では 用 紙 を 縦 置 きにする 売 上 実 績 シートは ページレイアウト 印 刷 タイトルで 行 タイトルに 1 行 目 を 指 定 する さらに ページ 設 定 の 拡 大 縮 小 印 刷 で 縦 2 ページにする Sheet1 には 完 成 した 集 計 シートの 表 を 余 白 部 分 に 貼 り 付 けて 印 刷 する 3/25

情 報 科 学 B 総 合 課 題 集 V2012-1 総 合 課 題 3 飲 料 水 卸 売 店 の 売 上 予 測 を 行 う この 課 題 のポイント Excle のさまざまな 機 能 を 利 用 して 効 率 よく 作 業 する セル 参 照 での 相 対 指 定 と 絶 対 指 定 を 上 手 に 使 い 分 ける 季 節 指 数 や 売 上 予 測 などのビジネス 用 語 を 理 解 する ファイルの 置 き 場 所 Y: Info-Sci 情 報 科 学 総 合 課 題 sougou3.xlsx 基 本 問 題 あなたは ある 飲 料 水 卸 売 店 に 勤 務 している これま では 年 間 の 販 売 計 画 を 全 年 度 の 売 上 金 額 から 算 出 し ていたが 季 節 指 数 を 算 出 して 月 別 の 予 算 を 立 てる よう 指 示 を 受 けした 次 の 指 示 に 従 い 2013 年 の 各 月 の 売 上 予 測 金 額 を 求 めなさい なお 利 用 するワークシートは 季 節 指 数 シートの みである 指 示 1 過 去 4 年 間 の 売 上 金 額 をもとに 当 店 の 季 節 指 数 を 算 出 すること その 際 次 の 指 示 に 従 うこと 14 年 間 の 平 均 は 小 数 点 以 下 を 切 り 捨 てること *Rounddown 関 数 を 利 用 すること(この 後 の 計 算 に 利 用 するため 表 示 だけの 設 定 では ) 2 季 節 指 数 は 各 月 の 4 年 分 の 平 均 4 年 分 の 平 均 の 平 均 で 求 めること * 4 年 分 の 平 均 の 平 均 =4 年 分 の 平 均 の 合 計 12 3 季 節 指 数 は 表 示 形 式 を 小 数 点 第 1 位 までのパー セント 表 示 にすること 指 示 3 2009 年 から 2012 年 までの 各 月 の 売 上 および 2013 年 の 売 上 予 測 を 比 較 する 折 れ 線 グラフを 作 成 し なさい その 際 次 の 指 示 に 従 うこと 1グラフのタイトルは 売 上 比 較 とすること 2グラフには 凡 例 を 表 示 すること 3グラフの 数 値 軸 には 適 切 な 単 位 を 表 示 すること 4 作 成 したグラフは 表 の 下 に 配 置 すること シートの 印 刷 季 節 指 数 シートを 必 ず 用 紙 横 置 き 1 枚 に 縮 小 し て 印 刷 し 提 出 する グラフの 配 置 位 置 に 気 を 付 けて できるだけグラフが 見 やくなるような 印 刷 を 行 うこと 完 成 例 指 示 2 指 示 1で 求 めた 季 節 指 数 を 利 用 して 2013 年 度 の 各 月 の 売 上 予 測 を 求 めなさい その 際 次 の 指 示 に 従 う こと 12013 年 の 年 間 の 売 上 計 画 を 240,000 千 円 として 各 月 の 売 上 予 測 を 求 めなさい * 季 節 指 数 は 年 間 売 上 の 平 均 値 との 比 較 であること から どのような 計 算 するのかを 考 えてみる ヒント 月 当 たりの 単 純 な 売 上 計 画 ( 年 間 の 売 上 計 画 12) に 季 節 指 数 ( 季 節 変 動 の 程 度 )を 掛 けて 月 別 の 売 上 予 測 を 算 出 できる 2 各 月 の 売 上 予 測 は 小 数 点 以 下 を 四 捨 五 入 して 整 数 表 示 にすること 4/25

情 報 科 学 B 総 合 課 題 集 V2012-1 総 合 課 題 4 降 水 確 率 と 売 上 高 の 関 連 性 を 見 る この 課 題 のポイント Excle のさまざまな 機 能 を 利 用 して 効 率 よく 作 業 する 集 計 するだけでなく 他 のデータと 適 切 に 組 み 合 わせて 分 析 を 行 う ファイルの 置 き 場 所 Y: Info-Sci 情 報 科 学 総 合 課 題 sougou4.xlsx 基 本 問 題 あなたは あるホームセンターにおいて 製 品 の 売 上 に 関 係 するファイルを 作 成 しており この 度 上 司 より 2012 年 度 第 4 四 半 期 (10 月 ~12 月 )のデ ータ 集 計 するよう 指 示 を 受 けました 以 下 の 各 指 示 に 従 い 2012 年 度 第 4 四 半 期 の 販 売 状 況 に 関 す る 資 料 を 作 成 しなさい なお 利 用 するワークシートは 売 上 シート 感 謝 デー シートの 2 つである 指 示 1 売 上 シートに 売 上 高 と 売 上 原 価 を 求 めなさい その 際 次 の 指 示 に 従 うこと 売 上 高 は 売 上 数 量 販 売 単 価 として 求 めること 売 上 原 価 は 売 上 高 原 価 率 100 として 求 めるこ と 指 示 2 この 店 では 毎 月 5 日 15 日 25 日 を お 客 様 感 謝 デー としており 通 常 日 の 2 倍 のポイントを 与 え ている 感 謝 デーは 通 常 日 より 多 くの 売 上 が 期 待 さ れており 売 上 額 の 把 握 をする 必 要 がある 指 示 1 で 作 成 した 表 を 利 用 して 2012 年 度 第 4 四 半 期 の 感 謝 デーの 売 上 データを 抽 出 し 日 にちごとに 売 上 高 を 集 計 シートの 該 当 する 所 に 集 計 しなさい なお 集 計 する 際 にはピボットテーブルを 用 いると 良 い 手 順 * 売 上 シートのセル A1 を 選 択 し 挿 入 リボンから ピボ ットテーブルを 選 ぶ * 作 成 したピボットテーブルの 行 フィールドに 売 上 日 値 フィールドに 売 上 高 を 配 置 する * ピボットテーブルの A4( 売 上 日 )の をクリックし すべ て 選 択 のチェックを 外 し 該 当 する 日 付 (5 日 15 日 25 日 )のみをチェックする( もしくは 感 謝 デー シートから 日 付 を 検 索 値 として 集 計 値 を Vlookup すると 簡 単 かも) 指 示 3 この 店 では 感 謝 デー 当 日 の 朝 に 天 気 予 報 で 発 表 される 降 水 確 率 を 記 録 している( 感 謝 デー シー ト) 感 謝 デーにおける 降 水 確 率 のデータと 指 示 2 の 結 果 をもとに 降 水 確 率 と 売 上 高 の 関 係 を 示 す 散 布 図 を 描 きなさい なお 散 布 図 に 以 下 の 設 定 をす ること 左 値 軸 には 売 上 高 を 設 定 すること 各 軸 に 単 位 を 明 示 すること 凡 例 値 ラベルは 表 示 しないこと 線 形 の 近 似 曲 線 を 追 加 すること グラフの 左 上 部 に 図 1 降 水 確 率 と 売 上 高 の 関 係 と 表 示 すること 指 示 4 次 の 文 章 の( ) 内 から 適 切 な 語 句 を 選 んで 文 章 を 完 成 させ 感 謝 デー シートに 挿 入 しなさい 降 水 確 率 と 売 上 高 の 関 係 を 分 析 し 散 布 図 を 描 い たところ 図 1 に 示 すように 各 点 がほぼ 直 線 的 に 分 布 する( 線 形 / 平 行 ) 関 係 がみられた また ( 右 下 がり / 右 上 がり )のグラフとなったことか ら 降 水 確 率 と 売 上 高 には( 正 の 相 関 / 負 の 相 関 )があるといえる この 課 題 の 提 出 方 法 ここまでで 説 明 した 作 業 内 容 に 抜 けがある 場 合 は 大 幅 に 減 点 されるので 注 意 すること シートの 印 刷 感 謝 デー シートを 必 ず 用 紙 横 置 き 1 枚 に 収 ま るように 印 刷 する 売 上 シートは 印 刷 しなくてもよい 5/25

総 合 課 題 5 都 道 府 県 別 産 業 別 就 業 者 数 の 作 表 この 課 題 のポイント Excle のさまざまな 機 能 を 利 用 して 効 率 よく 作 業 する セル 参 照 での 相 対 指 定 と 絶 対 指 定 を 上 手 に 使 い 分 ける 背 景 色 や 罫 線 などうまく 使 って 表 を 見 やすくする ファイルの 置 き 場 所 Y: Info-Sci 情 報 科 学 総 合 課 題 sougou5.xlsx 基 本 問 題 国 勢 調 査 (2005 年 )で 集 計 された 都 道 府 県 別 産 業 別 就 労 者 数 のデータをもとに 第 一 次 産 業 ~ 第 三 次 産 業 別 の 就 労 者 数 と 構 成 比 率 を 算 出 する ただし 短 時 間 で 作 業 を 完 了 させるため Excel のさまざまな 基 本 機 能 を 活 用 して 作 業 する 具 体 的 な 作 業 内 容 は 以 下 のようになる ワークシートに ある 説 明 も 参 照 すること 1 総 数 ( 合 計 )の 算 出 都 道 府 県 ごとの 総 数 および 産 業 ごとの 総 数 をワーク シートに 記 入 する 2 産 業 分 類 ごとの 総 数 などの 算 出 第 一 次 産 業 ~ 第 三 次 産 業 ごとの 総 数 を 算 出 し 次 に 全 産 業 に 対 する 構 成 比 率 をワークシート 上 で 算 出 する 数 式 は 必 ず 他 のセルへコピーできるように 記 述 し 短 時 間 で 作 業 を 完 了 させる このとき 構 成 比 率 は 百 分 率 (%)の 表 記 にし 小 数 以 下 は2 桁 で 表 示 させる ( 注 : 本 来 ならば 分 類 できない 項 目 を 産 業 分 類 には 含 めない が この 課 題 では 第 三 次 産 業 に 含 める) 3 条 件 付 き 書 式 の 設 定 を2で 算 出 した 構 成 比 率 の 全 セルに 設 定 し 0.5% 以 上 のセルの 背 景 色 を 変 更 する 設 定 する 数 式 には 元 の 数 値 を 指 定 する 必 要 がある 4 都 道 府 県 について 就 業 者 総 数 の 多 い 順 に 並 べ 替 えを 行 なう もちろん 総 数 だけでなく その 都 道 府 県 の すべてのデータも 一 緒 に 並 べ 替 えるようにする 5ワークシートを 見 やすくするために 次 のような 作 業 を 行 なう 数 値 は 3 桁 ごとのカンマ 区 切 りにする 無 用 なエラーチェックの 表 示 を 取 り 除 く セルの 背 景 色 を 設 定 し 表 全 体 を 確 認 しやすくす る 作 業 内 容 は 任 意 とする ( 簡 素 なものでよい) 罫 線 を 印 刷 時 に 見 やすいものに 変 更 追 加 する 6ウィンドウ 枠 を 固 定 し シートをスクロールさせても 産 業 分 野 名 と 都 道 府 県 名 が 常 に 表 示 されるようにする この 課 題 の 提 出 方 法 ( 拡 張 問 題 はありません) ここまでで 説 明 した 作 業 内 容 に 抜 けがある 場 合 は 大 幅 に 減 点 されるので 注 意 すること シートの 印 刷 作 業 を 行 ったセル 範 囲 を 用 紙 横 置 き 1 枚 に 縮 小 し て 印 刷 する なお ワークシートにある 説 明 のためのテ キストボックスは 印 刷 範 囲 に 含 めないよう 設 定 すること 完 成 例 ( 内 側 部 分 は 省 略 している) 6/25

総 合 課 題 6 乱 数 を 用 いた7セグメント 数 値 表 示 この 課 題 のポイント 論 理 演 算 や 関 数 を 組 み 合 わせて 目 的 の 結 果 を 得 る 条 件 付 き 書 式 を 使 ってセルや 文 字 の 色 を 変 化 させる 0 ファイルの 置 き 場 所 Y: Info-Sci 情 報 科 学 総 合 課 題 sougou6.xlsx 概 要 と 基 本 機 能 電 卓 などに 使 用 されている 数 字 (7 セグメント LED と いうの 数 字 表 示 を Excle のシート 上 に 作 成 する この ような 電 子 機 器 では 7 つの 部 分 (セグメント)から 構 成 さ れる 液 晶 パネルや LED などによって 1 桁 の 数 字 を 表 示 する この 課 題 では それぞれのセグメントの 点 灯 と 消 灯 を 条 件 付 き 書 式 で 背 景 色 等 を 切 り 替 えてこれを 実 現 させている 表 示 に 使 用 する 乱 数 は シート 左 上 のセルに 記 入 さ れている 式 で 発 生 させ 計 算 式 で 桁 ごとの 値 を 取 り 出 し ている 基 本 問 題 では 2 桁 の 値 を 使 用 する 各 セグメント(7 本 )ごとの 表 示 条 件 は 下 図 の 通 りで ある いろいろな 方 法 があるが たとえば 各 セグメントを 表 示 するセル( 右 の 図 の 灰 色 の 棒 状 の 部 分 )の 値 を 表 示 したいとき 1 に 表 示 しないとき 0 にする 各 セル にはこの 1 と 0 の 値 を 算 出 する IF 関 数 や OR 関 数 な どを 組 み 合 わせた 論 理 式 を 置 くことになる 次 に 条 件 つ き 書 式 の 機 能 を 使 用 して セル 内 容 が 1 なら 文 字 色 と 背 景 色 を 黒 に 0 なら 文 字 色 と 背 景 色 を 薄 緑 にする 最 後 に 発 生 させた 乱 数 の 1 桁 目 が 0~9 になったと き それぞれの 表 示 セグメントがうまく 機 能 して 0~9 の 数 字 が 大 きく 表 示 されることを 確 認 する なお キーボ ードのF9 キーを 押 すと 新 たな 乱 数 が 算 出 される 機 能 の 拡 張 2 桁 の 表 示 を 行 なう 基 本 的 には 追 加 する 桁 の 表 示 条 件 ( 論 理 式 )に 10 の 桁 用 の 乱 数 を 使 用 するだけの 2 桁 の 乱 数 を 発 生 させる 式 と 各 桁 に 分 け る 式 が 記 入 されている 灰 色 の 棒 状 の 部 分 (セ グ メ ン ト ) に 1 ( 点 灯 )か0( 消 灯 )の 値 を 計 算 する 式 を 記 入 す る この 値 をもとに 条 件 付 き 書 式 で 数 字 と 背 景 色 を 変 化 させる 作 業 となる ただし 10 の 桁 が 0 のときは これを 表 示 しないようにしたい つまり 10 の 桁 が 0,1 の 桁 が 5 の ときは 05 ではなく 5 と 1 桁 だけ 表 示 する この ような 表 示 方 法 をゼロサプレスという ゼロサプレスを 実 現 するためには 10 の 桁 の 各 セグ メントの 表 示 条 件 ( 論 理 式 )を 変 更 して 値 が 0 のときに は 何 も 表 示 しないようにする つまり 左 下 の 表 の 点 灯 させるときの 値 の 中 にある 0 の 部 分 はすべて 消 灯 す るよう 式 を 書 き 換 えればよいことになる 1 の 桁 値 が 0 そのまま 0 を 表 示 する 10 の 桁 値 が 0 全 セグメントを 薄 緑 色 にする シートの 印 刷 方 法 1 桁 表 示 用 のセル ( 基 本 機 能 で 使 用 ) 機 能 の 拡 張 まで 完 成 しなければならない 2 桁 が 動 作 して ゼロサプレスしなければならない 作 業 を 行 ったセル 範 囲 を 用 紙 縦 置 き 1 枚 に 印 刷 して 提 出 する 動 作 は 提 出 されたファイルで 確 認 するので 印 刷 の 際 に 表 示 される 数 字 は 任 意 でよい 7/25

総 合 課 題 7 ピザハウスの 注 文 票 の 作 成 この 課 題 のねらい 基 礎 的 なワークシートの 作 成 と 編 集 ができること VLOOKUP によるデータベースからの 検 索 ができること 操 作 マクロを 定 義 してコマンドボタンを 作 成 できること この 課 題 にはファイルはありません 基 本 問 題 授 業 内 で 解 説 された 宅 配 ピザの 注 文 票 を 作 成 す る 注 文 を 受 ける 商 品 のコードとサイズ 数 量 を 入 力 するだけで 金 額 と 合 計 金 額 を 表 示 するようにし たい この 資 料 では 外 税 方 式 で 作 成 例 が 示 されて いるが 金 額 は 授 業 時 に 解 説 したように 全 て 内 税 方 式 ( 税 込 価 格 で 表 示 されているものとする) ピザの 種 類 は 自 由 でよい 基 本 問 題 ではサイズは1 種 類 でも 構 わない SMLがあるようなものはメニュ ーデータ 上 で 別 メニューにすればよい ピザの 他 に も 飲 み 物 や サイドオーダもある また プレーンピ ザに 特 定 のトッピングを 組 み 合 わせての 注 文 も 想 定 する 必 要 がある メニューデータの 内 容 は 例 に 準 ずるが 各 自 好 みの メニューを 追 加 してよい 下 の 画 面 例 にはないが 1 万 円 札 で 支 払 われた 場 合 5 千 円 札 で 支 払 われた 場 合 の おつり をあらかじめ 計 算 しておくようにする その おつり を 支 払 うのに 必 要 な 金 種 毎 の 枚 数 を 表 示 するようにするとさらによい 1 つの 処 理 を 終 わって 次 の 処 理 を 行 う 場 合 コー ド サイズ 数 量 の 各 部 分 をクリアしなければならな い このクリアの 作 業 を 1 度 の 操 作 で 行 う マクロ を 記 録 してそれをボタンに 割 り 当 てる 印 刷 の 方 法 (1) 課 題 の 印 刷 物 はWordにExcelの 画 面 コピーを 張 り 付 けて 作 成 する マクロボタンが 分 かるようにす ること その 際 処 理 可 能 な 最 大 のデータ 数 分 の 項 目 を 入 れた 注 文 表 が 印 刷 されるようにする 内 容 は 自 由 用 紙 は 横 置 きに 使 用 する (2) 作 成 した 問 題 はいくつかのセルに 入 る 式 が 重 要 となる (1)で 作 成 するWord 文 書 に 重 要 と 思 われ る 式 を 入 力 (Excelから 式 をWordにコピーするとよ いでしょう)しておくこと 別 ページに 式 をまとめても よい この 課 題 は 第 11 回 目 の 授 業 で 解 説 されます 8/25

総 合 課 題 8 ABC 分 析 この 課 題 にはファイルはありません この 課 題 のねらい データを 集 計 して 並 べ 換 えができること 複 合 グラフによるパレート 図 が 作 成 できること 問 題 下 のデータはT 市 のある 年 の 火 災 発 生 件 数 を 建 物 の 種 別 と 火 災 原 因 別 にまとめたものである この 表 を 元 にし て パレート 図 を 作 成 したい パレート 図 からどのような 原 因 を 減 らすことに 注 力 することが 効 果 的 かを 判 断 したい 出 火 した 原 因 集 共 合 同 住 宅 住 宅 飲 食 店 工 場 等 事 務 所 等 百 貨 店 等 学 校 倉 庫 病 院 等 合 計 電 気 ストーブ 50 38 0 1 1 0 0 0 0 90 ガステーブル 等 289 237 55 5 2 1 2 0 0 591 たばこ 279 138 25 9 33 10 5 4 7 510 放 火 374 110 14 12 25 41 28 18 16 638 ロウソク 47 27 2 0 0 0 0 1 0 77 電 気 コンロ 57 11 2 0 6 0 0 0 0 76 コード 17 20 4 0 4 3 0 1 0 49 石 油 ストーブ 12 27 1 6 2 0 0 0 0 48 火 遊 び 33 12 0 0 0 0 1 1 0 47 大 型 ガスコンロ 0 0 47 3 0 3 1 0 0 54 その 他 238 263 85 103 40 49 18 21 9 826 合 計 1,396 883 235 139 113 107 55 46 32 3,006 上 の 表 を 入 力 後 に 集 合 住 宅 について 以 下 のような 分 析 を 行 う 計 算 対 象 の デ ー タ 範 囲 に つ い て 1 原 因 と 集 合 住 宅 のデータをワークシートの 別 の 場 所 へコピーする VLOOKUP 関 数 を 用 いて 下 の 表 を 作 成 す 2 それぞれの 原 因 の 出 火 件 数 をRANK 関 数 で 順 位 付 ける る これをもとにしてグラフを 作 成 する 3 VLOOKUP 関 数 を 使 って 順 位 をキーにデータを 並 べ 変 える 4 作 成 した 表 から 構 成 比 率 と 累 積 比 率 を 求 める 5 度 数 と 構 成 比 率 累 積 比 率 を 用 いて 折 れ 線 グラフを 作 成 する 6 度 数 の 目 盛 り 軸 を 右 側 にする 7 度 数 のグラフを 棒 グラフに 変 更 する 8 目 盛 りを 調 整 して 見 やすくする 9 タイトルや 単 位 を 入 れる 10 度 数 が 上 位 3 位 までの 棒 グラフの 棒 の 色 を 変 える 11 折 れ 線 グラフのデータ 点 を 丸 にし 少 し 大 きめにする 12 棒 グラフの 上 に 値 をデータラベルとしていれる 13 作 成 したグラフと 別 に 計 算 したワークシートの 表 を1ページに 配 置 して 印 刷 する ( 用 紙 横 置 きでヘッダフッタ 付 き) 順 位 集 合 住 宅 出 火 原 因 構 成 比 率 累 積 1 放 火 374 27% 27% 2 ガステーブル 等 289 21% 47% 3 たばこ 279 20% 67% 4 その 他 238 17% 85% 5 電 気 コンロ 57 4% 89% 6 電 気 ストーブ 50 4% 92% 7 ロウソク 47 3% 96% 8 火 遊 び 33 2% 98% 9 コード 17 1% 99% 10 石 油 ストーブ 12 1% 100% 合 計 1396 100% 集 合 住 宅 について 出 火 原 因 についてABC 分 析 を 行 い パレート 図 を 作 成 する 作 成 したパレート 図 は 元 のデー タと 一 緒 に1ページにまとめて 印 刷 できるようにする 用 紙 は 横 置 き この 課 題 はカラーで 見 やすく 印 刷 する 100% 90% 80% 70% 60% 50% 40% 30% 20% 10% 0% 374 27% 放 火 289 279 47% ガ ス テー ブ ル 等 67% た ば こ 集 合 住 宅 の 出 火 原 因 85% 238 そ の 他 89% 92% 96% 57 50 47 電 気 コ ン ロ 電 気 ス トー ブ ロ ウ ソ ク 98% 99% 100% 33 火 遊 び 構 成 比 率 累 積 17 12 コー ド 石 油 ス トー ブ [ 件 ] 400 350 300 250 200 150 100 50 0 9/25

総 合 課 題 9 プロジェクトの 選 択 問 題 - 現 在 価 値 で 評 価 する- この 課 題 のねらい 現 在 価 値 の 考 え 方 がわかる 数 式 をもとに 計 算 式 を 組 み 立 てられる 労 少 なく 益 多 し を 尊 ぶ ファイルの 置 き 場 所 Y: Info-Sci 情 報 科 学 総 合 課 題 sougou9.xlsx 解 説 3 年 後 の 100 万 円 は 現 在 の 100 万 円 と 同 じ 価 値 だろうか 実 はそうではない ちょっと 考 えてみよう いま 100 万 円 持 っているとする それを 年 利 率 6%の 銀 行 預 金 に 3 年 間 あずけるとする 1 年 後 には 1.06 倍, 2 年 後 には 1.06 1.06 倍,3 年 後 には 1.06 1.06 1.06 倍 になる つまり 3 年 後 には 100 万 円 1.06 3 = 約 119.1 万 円 になる ということは,3 年 後 の 119.1 万 円 が 現 在 の 100 万 円 と 同 じ 価 値 だということである つまり, 現 在 の 100 万 円 =3 年 後 の 119.1 万 円 /1.063... (1) という 関 係 が 成 り 立 っている すると,はじめにあげた 3 年 後 の 100 万 円 の 現 在 の 価 値 は, 現 在 の x 万 円 =3 年 後 の 100 万 円 /1.063... (2) という 式 から x=84.0 万 円 と 求 めることができる さてこれを 一 般 化 しよう 現 在 価 値 (Present Value: PV という)を x 円 とする 年 利 率 を 期 待 利 率 と 言 い 換 えて r%とする n 年 後 の 価 値 を z 円 とする すると, z x...(3) n という 式 が 成 立 する 難 しそうだが,MS-Excel の 計 算 式 で 書 くと, x = z / ( 1 + r / 100 ) ^ n... (4) である ( 注 ) もちろん = 右 辺 だけを 入 力 する 課 題 プロジェクト A,B,C がある 1 年 後,2 年 後,3 年 後 の 収 益 が 表 1 のように 予 想 されている 期 待 利 率 が r= 6%のとき,それぞれのプロジェクトの 現 在 価 値 を 求 めなさい なお プロジェクトの 現 在 価 値 は 次 の 意 味 である プロジェクトの 現 在 価 値 = 1 年 後 の 収 益 の 現 在 価 値 + 2 年 後 の 収 益 の 現 在 価 値 + 3 年 後 の 収 益 の 現 在 価 値 1年 後 の 収 益 2年 後 の 収 益 3年 後 の 収 益 + + 1 2 3... (5) 作 業 (1) プロジェクト A,B,C について 1 年 後 の 収 益 の 現 在 価 値 を 求 めなさい 四 捨 五 入 して 小 数 1 桁 表 示 作 業 (2) 同 様 に 2 年 後,3 年 後 を 求 めなさい 作 業 (3) プロジェクトの 現 在 価 値 を 求 めなさい 四 捨 五 入 して 小 数 1 桁 表 示 作 業 (4) 現 在 価 値 の 最 も 大 きいプロジェクトに をつけなさい 作 業 (5) G 列 3 年 後 の 収 益 の 現 在 価 値 の 下 の 3 つのセルに 入 力 した 計 算 式 を,すべて 表 の 下 に 記 しなさい 10/25

総 合 課 題 10 プロジェクトの 選 択 問 題 - 内 部 収 益 率 で 評 価 する- この 課 題 のねらい 内 部 収 益 率 の 考 え 方 がわかる IRR 関 数 の 使 い 方 がわかる 労 少 なく 益 多 し を 尊 ぶ ファイルの 置 き 場 所 Y: Info-Sci 情 報 科 学 総 合 課 題 sougou10.xlsx 解 説 いまプロジェクトに 投 資 を 行 うと,1,2,3 年 後 に 収 益 が 生 まれるとする このとき, 将 来 の 収 益 は 現 在 の 投 資 額 に 対 してどれだけの 期 待 利 率 に 相 当 するか,という 問 題 を 考 えてみよう これは,(6) 式 が 成 立 するような r%を 求 める 問 題 である なお, 期 待 利 率 のことを 内 部 収 益 率 (Internal Rate of Return: IRR)という プロジェクトへの 現 在 の 投 資 額 1年 後 の 収 益 2年 後 の 収 益 3年 後 の 収 益 + + 1 2 3... (6) これは r についての 3 次 方 程 式 である コムズカシイ しかし MS-Excel には, 内 部 収 益 率 を 求 めるための IRR 関 数 があるのでそれを 使 えばよい 課 題 表 1 のようにプロジェクト A,B,C がある 現 在 の 投 資 額 を 700 とする ただしここだけマイナスにして 入 力 す る 1 年 後,2 年 後,3 年 後 の 収 益 が 表 のように 得 られるとき, 内 部 収 益 率 (IRR)を 求 めなさい 作 業 (1) プロジェクト A,B,C のそれぞれについて 内 部 収 益 率 のセルに 次 のように IRR 関 数 を 入 力 しなさい - IRR 関 数 の 第 1 引 数 は, 現 在 の 投 資 額 から 3 年 後 の 収 益 までの 範 囲 である - IRR 関 数 の 第 2 引 数 は,0 と 指 定 すればよい 作 業 (2) IRR 関 数 が 返 す 値 は% 単 位 ではない そこで,これを 100 倍 して% 単 位 にしなさい 四 捨 五 入 して 小 数 1 桁 表 示 作 業 (3) 内 部 収 益 率 の 最 も 大 きいプロジェクトに をつけなさい 作 業 (4) F 列 内 部 収 益 率 の 下 の 3 つのセルに 入 力 した 計 算 式 を,すべて 表 の 下 に 記 しなさい 11/25

総 合 課 題 11 比 較 演 算 と 論 理 演 算 の 応 用 この 課 題 のねらい 比 較 演 算 を 正 しく 表 現 できる 論 理 演 算 関 数 (IF,AND,OR)を 正 しく 使 うことができる 問 題 文 から 適 切 な 式 を 導 くことができる ファイルの 置 き 場 所 Y: Info-Sci 情 報 科 学 総 合 課 題 sougou11.xlsx 解 説 麗 太 と 麗 子 が 住 宅 を 探 している 候 補 として 表 1 のような 6 件 の 住 宅 を 選 んだ この 中 から 好 ましい 住 宅 を 選 択 するために, 住 宅 の 特 徴 ごとに 得 点 を 与 えて,その 合 計 得 点 の 大 きいもの を 選 ぶことにした 得 点 化 のルールは 次 のとおりである 作 業 (1) 作 業 (2) 作 業 (3) 1 価 額 <4000 万 円 なら 10 点,それ 以 外 は 0 点 2 60 m2 広 さ<70 m2 なら 10 点, 70 m2 広 さ なら 20 点,それ 以 外 は 0 点 3 通 勤 時 間 が 50 分 未 満 なら 10 点,それ 以 外 は 0 点 4 向 きが 南 なら 20 点, 向 きが 南 東 または 南 西 なら 10 点,それ 以 外 は 0 点 表 2 に 得 点 を 求 めるための 計 算 式 を 入 力 しなさい IF 関 数,AND 関 数,OR 関 数 を 用 いなさい 合 計 得 点 を 計 算 しなさい C15,D15,E15,F15 のセルに 入 力 する 計 算 式 を 所 定 の 欄 に 記 しなさい 12/25

総 合 課 題 12 関 数 を 使 わない 論 理 演 算 この 課 題 のねらい 比 較 演 算 を 正 しく 表 現 できる 問 題 文 から 適 切 な 式 を 導 くことができる ファイルの 置 き 場 所 Y: Info-Sci 情 報 科 学 総 合 課 題 sougou12.xlsx 解 説 計 算 式 の 中 で 比 較 演 算 を( ) 付 きで 表 すと, 比 較 演 算 結 果 が 真 のとき( ) 内 が 1, 偽 のとき 0 の 値 をと る これを 使 うと IF 関 数 を 使 わなくても 論 理 演 算 ができる 基 本 は 次 のような 計 算 式 の 応 用 である (1) 単 一 の 判 定 の 例 : A1<100 なら 10 点,それ 以 外 は 0 点 とするとき, 次 のように 計 算 式 を 入 力 すればよい すると,(A1<100)が TRUE( 真 )なら( ) 内 が 1,FALSE( 偽 )なら 0 となる =(A1<100)*10 (2) 否 定 の 例 : A1<100 でないなら 10 点,それ 以 外 は 0 点 とする A1<100 でない という 箇 所 を 単 に A1 100 とやったのでは 論 理 演 算 っぽくない 次 のようにするのが 否 定 の 表 現 である =( 1-(A1<100) )*10 (3) 二 つの 判 定 ( 論 理 積 )の 例 : 50 A1<100 なら 10 点,それ 以 外 は 0 点 とする =(50<=A1)*(A1<100)*10 50<=A1 かつ A1<100 ととらえなおす かつ(AND) には 積 (*)の 演 算 を 用 いる (4) 二 つの 判 定 ( 論 理 和 )の 例 : A1= 南 または B1 6 なら 20 点,それ 以 外 は 0 点 とする これは 意 外 と 難 しい まず, A1= 南 または B1 6 の 否 定 (NOT)を 考 えてみると, A1 南 かつ B1>6 である これを 計 算 式 にすると, (A1<> 南 )*(B1>6) と 表 わされる さらにこれの 否 定 を 作 ればもとの 論 理 式 と 同 値 になる =( 1- (A1<> 南 )*(B1>6) )*20 (5) 応 用 例 : A1<100 なら 10 点 とする さらに, B1= 南 または C1 6 なら 20 点 とする この 場 合, 個 々の 判 定 の 結 果 の 得 点 を 加 算 すればよい =(A1<100)*10 + ( 1- (B1<> 南 )*(C1>6) )*20 表 1 に 6 件 の 住 宅 のデータがある 表 の 下 に 書 かれた 条 件 にもとづいて,それぞれの 住 宅 の 価 額, 広 さ, 通 勤 時 間, 部 屋 の 向 きを 得 点 化 して, 合 計 得 点 を 求 めるとする 作 業 (1) 表 1 の 合 計 得 点 の 欄 に, 関 数 を 使 わない 論 理 演 算 の 計 算 式 を 記 入 して 合 計 得 点 を 求 めなさい 作 業 (2) G6 セルに 入 力 した 計 算 式 を 表 1 の 下 の 所 定 の 欄 に 記 しなさい 13/25