課 題 1 ピボットテーブル 編 集 ファイル H27 東 京 アビリン 提 供 データ を 開 き シート 課 題 1 受 注 一 覧 を 表 示 し 以 下 の 設 問 にしたがってピボットテーブルを 作 成 後 表 をコピーし 整 えなさい シートは 課 題 1 受 注 一 覧 課 題 1ピボ

Transcription

1 第 14 回 東 京 障 害 者 技 能 競 技 大 会 表 計 算 競 技 事 前 課 題 これは 第 14 回 東 京 障 害 者 技 能 競 技 大 会 における 事 前 課 題 である 参 考 として 公 表 されるものであり 実 際 に 使 われる 競 技 課 題 は 事 前 課 題 の 仕 様 の 一 部 を 変 更 したものである 1 競 技 内 容 作 品 は 課 題 1~5 の 5 種 類 とする (1) 課 題 1:ピボットテーブル 編 集 予 め 準 備 されているデータ( 以 下 提 供 データ)をもとに ピボットテーブル 作 成 図 形 作 成 書 式 設 定 条 件 付 き 書 式 の 設 定 等 を 行 う (2) 課 題 2: 関 数 式 による 表 の 完 成 提 供 データをもとに 名 前 の 定 義 されたリストを 利 用 して 指 示 に 従 い 最 終 的 値 を 求 める (3) 課 題 3:データ 処 理 提 供 データをもとに レコードの 抽 出 並 べ 替 え 等 を 行 う かつ 抽 出 されたレコードがど の 条 件 で 抽 出 されたかが 分 かるようにする (4) 課 題 4:グラフ 作 成 提 供 データをもとに セルの 複 数 範 囲 の 選 択 によるグラフの 作 成 印 刷 設 定 等 を 行 う (5) 課 題 5:マクロ 作 成 シート 目 次 を 除 いた 全 てのシートをもとに それらのシート 名 を 使 って 目 次 を 作 成 する * 提 供 データの 説 明 ファイル 名 : H27 東 京 アビリン 提 供 データ.xlsm 9つのシート 名 : 目 次 課 題 1 受 注 一 覧 課 題 1ピボット 課 題 1 受 注 集 計 課 題 2 料 金 計 算 表 課 題 2 料 金 表 課 題 3 図 書 一 覧 表 課 題 3 図 書 抽 出 課 題 4グラフ 2 競 技 時 間 90 分 なお 作 成 する 課 題 の 順 番 は 任 意 とする 3 課 題 提 出 方 法 競 技 者 は 完 成 した 作 品 ( 未 完 成 作 品 も 含 む)を 大 会 主 催 側 の 用 意 する 外 付 けの USB メモリに 保 存 す るとともに 印 刷 指 示 があったものは 印 刷 物 も 提 出 すること なお 提 出 に 係 る 試 し 印 刷 及 びファイルの 保 存 は 競 技 時 間 内 に 含 まれるが 提 出 作 品 の 印 刷 は 競 技 時 間 には 含 まれない 4 競 技 会 場 に 準 備 してあるもの (1) OS :Microsoft Windows 7 Pro (2) 表 計 算 ソフト :Microsoft Excel2010(Microsoft Office 2010) (3) PC :DOS/V 互 換 機 (4) USB メモリ :32MB 以 上 5 注 意 事 項 (1) 提 供 データは 大 会 当 日 に 配 布 されるファイルを 使 用 すること (2) 競 技 中 に 万 一 機 器 が 故 障 した 場 合 は 審 査 員 の 指 示 に 従 うこと (3) 競 技 が 終 了 したら 審 査 員 に 申 し 出 ること (4) 競 技 場 での 座 席 位 置 は 予 め 審 査 員 が 決 定 しておく (5) 事 前 に 配 布 された 課 題 メモ 参 考 書 その 他 の 資 料 等 の 持 込 は 一 切 不 可 とする - 1 -

2 課 題 1 ピボットテーブル 編 集 ファイル H27 東 京 アビリン 提 供 データ を 開 き シート 課 題 1 受 注 一 覧 を 表 示 し 以 下 の 設 問 にしたがってピボットテーブルを 作 成 後 表 をコピーし 整 えなさい シートは 課 題 1 受 注 一 覧 課 題 1ピボット 課 題 1 受 注 集 計 の3 種 類 である シート 課 題 1 受 注 一 覧 は 伝 票 番 号, 商 品, 単 価, 数 量, 売 上 額, 配 送 地 域, 受 注 日, 発 注 日 か らなるレコードが 530 件 ある シート 課 題 1ピボット は シート 課 題 1 受 注 一 覧 のデータを 使 用 しピボットテー ブル 作 成 を 行 うためのものである シート 課 題 1 受 注 集 計 は シート 課 題 1ピボット の 表 をコピーし 書 式 を 設 定 す るためのものである 問 1 表 のデータをもとに 次 の 設 定 でピボットテーブルを 作 成 しなさい 配 置 する 場 所 :シート 課 題 1ピボット セル B2 行 ラベル : 商 品 列 ラベル : 配 送 地 域 Σ 値 : 売 上 額 集 計 方 法 を 合 計 とする < 途 中 完 成 図 > 問 2 ピボットテーブルのデータ 範 囲 B3:H18 をコピーし シート 課 題 1 受 注 集 計 のセ ル B7 に 元 の 列 幅 を 保 持 して 貼 り 付 けなさい ピボットテーブルの 最 初 の 行 (セル B2:H2)はコピー 範 囲 に 含 めないものとする 問 3 行 ラベル のセル B7 を ギフト 商 品 セット 名 に 変 更 しなさい また セル B7 の 書 式 をセル B22 に 貼 り 付 けなさい - 2 -

3 問 4 以 下 のように 設 定 しなさい 項 目 名 (セル B7:H7)の 文 字 : 前 後 にスペースが 入 った 均 等 割 り 付 け 数 値 データ(セル C8:H22) : 桁 区 切 りスタイル 問 5 ギフト 商 品 セット 名 ごとの 総 計 が 最 大 であるレコードは ギフト 商 品 セット 名 (セ ル B8:B21)の 塗 りつぶしの 色 が 赤 系 になるよう 条 件 付 き 書 式 で 設 定 しなさい また ギフト 商 品 セット 名 ごとの 総 計 が 最 小 であるレコードは ギフト 商 品 セット 名 (セル B8:B21)の 塗 りつぶしの 色 が 緑 系 になるよう 条 件 付 き 書 式 で 設 定 しなさい 問 6 以 下 のように 罫 線 を 設 定 しなさい 表 の 範 囲 (セル B7:H22) : 格 子 線 項 目 名 (セル B7:H7) : 下 太 罫 線 問 7 C~F 列 H 列 の 列 幅 を G 列 と 同 じ 幅 に 設 定 しなさい 問 8 次 の 設 定 で 表 題 を 作 成 しなさい 配 置 する 場 所 :セル B2:H5 ( 概 ねで 良 い) 表 示 する 文 字 :ギフト 商 品 地 域 別 売 上 集 計 図 形 : 対 角 する2つの 角 を 切 り 取 った 三 角 形 塗 りつぶし : 薄 い 青 色 系 の 色 フォント :フォントサイズ:24 フォント :MS 明 朝 フォントの 色 : 黒 色 系 太 字 文 字 配 置 : 縦 横 位 置 ともに 中 央 揃 え < 完 成 図 > - 3 -

4 課 題 2 関 数 式 による 表 の 完 成 OA オフィス 器 具 備 品 レンタル 会 社 において 器 具 備 品 別 レンタル 期 間 を 指 定 して 利 用 料 金 を 算 出 する 問 題 である シート 課 題 2 料 金 計 算 表 を 開 き 次 に 示 す 各 シートの 概 要 をもとに 以 下 の 指 示 に 従 って 利 用 料 金 を 算 出 しなさい 課 題 2で 利 用 するシートは 課 題 2 料 金 計 算 表 及 び 課 題 2 料 金 表 の2 種 類 である シート 課 題 2 料 金 表 には 種 類 デスク 椅 子 テーブル 応 接 セット ロ ッカー 保 管 庫 OA 機 器 季 節 物 の6つのリストがある シート 課 題 2 料 金 表 には 利 用 しやすいように 特 定 セル 範 囲 に 名 前 をすでに 以 下 の ように 定 義 されている No 名 前 セル 範 囲 1 種 類 K5:K9 2 デスク B6:B12 3 デスク 表 C6:H12 4 椅 子 B17:B23 5 椅 子 表 C17:H23 6 テーブル 応 接 セット B28:B32 7 テーブル 応 接 セット 表 C28:H32 8 ロッカー 保 管 庫 B37:B40 9 ロッカー 保 管 庫 表 C37:H40 10 OA 機 器 季 節 物 B45:B51 11 OA 機 器 季 節 物 表 C45:H51 12 期 間 C27:H27 Ⅰ.レンタル OA オフィス 器 具 備 品 の 利 用 料 金 計 算 ための 事 前 設 定 問 1 No 欄 を 指 定 しない 行 (レコード)は 非 表 示 にする ( 特 に 金 額 とレンタル 料 欄 ) 問 2 種 類 を 入 力 するのにプルダウンリストをセル C6:C20 に 一 括 して 設 定 する 元 の 値 は セル 名 である 種 類 を 使 用 すること 問 3 品 名 を 入 力 するのにプルダウンリストをセル D6:D20 に 一 括 して 設 定 しなさい ただし 種 類 を 選 んだ 時 対 象 になる 品 名 のみが 表 示 されるようにすること - 4 -

5 問 4 個 数 を 入 力 するのにプルダウンリストをセル E6:E20 に 一 括 して 設 定 しなさい 元 の 値 は 1~10 までとする 問 5 期 間 を 入 力 するのにプルダウンリストをセル F6:F20 に 一 括 して 設 定 しなさい 元 の 値 はセル 名 である 期 間 を 使 用 すること Ⅱ.レンタル OA オフィス 器 具 備 品 の 利 用 料 金 計 算 問 1 金 額 は 品 名 と 期 間 からシート 課 題 2 料 金 表 のリストから 導 き 出 す 具 体 的 には 対 応 する 各 種 類 別 リスト(マトリックス)を 使 って 品 名 と 期 間 をキ ーに 該 当 する 金 額 を 求 める 問 2 レンタル 料 は 金 額 * 個 数 とする 問 3 総 合 計 金 額 を 算 出 する 1 合 計 金 額 は レンタル 料 (セル H6:H15)の 合 計 である 2 消 費 税 = 合 計 金 額 * 0.08(8%) 3 総 合 計 金 額 = 合 計 金 額 + 消 費 税 参 考 例 シート 課 題 2 料 金 計 算 表 - 5 -

6 料 金 表 シート 課 題 2 料 金 表 - 6 -

7 課 題 3 データ 処 理 各 シートの 説 明 をもとに 以 下 の 設 問 に 従 って データ 処 理 を 行 いなさい シートは 課 題 3 図 書 一 覧 表 及 び 課 題 3 図 書 抽 出 の2 種 類 である シート 課 題 3 図 書 一 覧 表 は 図 書 CD, 図 書 名, 発 行 年 月 日, 著 者 名, 出 版 社 名, 内 容 紹 介, 貸 出 件 数 の7 項 目 からなるレコードが 100 件 ある シート 課 題 3 図 書 抽 出 は シート 課 題 3 図 書 一 覧 表 からデータの 抽 出 を 行 うため のものである 問 1 次 の 指 示 に 従 い シート 課 題 3 図 書 一 覧 表 からシート 課 題 3 図 書 抽 出 へ 抽 出 を 行 いなさい < 抽 出 1> 抽 出 先 セル セル A3 以 降 に 抽 出 条 件 一 度 も 貸 し 出 されていない 本 並 び 順 発 行 年 月 日 が 古 いもの 順 < 抽 出 2> 抽 出 条 件 セル 抽 出 1 処 理 で 表 示 されたセル 以 降 に 抽 出 先 セル 抽 出 条 件 セル 以 降 に 抽 出 条 件 1 図 書 内 容 に 日 本 を 含 み かつ 発 行 日 が 2010 年 以 降 2 図 書 名 に 散 歩 を 含 み かつ 貸 出 件 数 が 100 件 以 上 3 著 者 名 が 小 栗 景 子 問 2 条 件 付 き 書 式 を 使 って 問 1の 抽 出 2の 抽 出 条 件 にあった 抽 出 されたレコードの 対 象 セルに 以 下 の 背 景 色 を 設 定 しなさい < 背 景 色 > 1 黄 色 系 2 水 色 系 3 橙 色 系 < 完 成 図 > - 7 -

8 課 題 4 グラフ 作 成 シート 課 題 4グラフ に 以 下 の 設 問 にしたがって データをもとにグラフを 作 成 しなさい 問 1 セル B11:N14 を 使 用 し 以 下 の 設 定 でグラフを 作 成 しなさい グラフの 種 類 : 集 合 縦 棒 グラフの 配 置 :セル B16:N38( 概 ねで 良 い) 次 に 東 京 都 の 2014 年 平 均 気 温 (B4:N14)を 以 下 の 設 定 でグラフに 追 加 しなさ い グラフの 種 類 : 折 れ 線 縦 ( 値 ) 軸 : 第 2 軸 を 使 用 問 2 グラフエリアを 次 の 設 定 で 編 集 しなさい 枠 線 の 色 : 濃 いオレンジ 系 の 色 枠 線 のスタイル: 角 を 丸 くする 影 :オフセット( 斜 め 右 下 ) 塗 りつぶし : 薄 いオレンジ 系 の 色 問 3 グラフタイトルをグラフの 上 に 表 示 し 次 の 設 定 で 編 集 しなさい タイトル :セル B10 とリンクして 表 示 枠 線 の 色 : 濃 い 赤 系 の 色 問 4 凡 例 を 次 の 設 定 で 編 集 しなさい 位 置 : 上 問 5 横 ( 項 目 ) 軸 の 軸 ラベルを 横 書 きで 表 示 し 次 の 設 定 で 編 集 しなさい 横 ( 項 目 ) 軸 :( 2014 年 ) ( )は 全 角 とすること 位 置 : 下 側 右 部 問 6 第 2 軸 縦 ( 値 ) 軸 を 次 の 設 定 で 編 集 しなさい 最 小 値 :-30 最 大 値 :30 問 7 平 均 気 温 の 系 列 に 次 の 設 定 でデータラベルを 表 示 しなさい ラベルの 内 容 : 値 ラベルの 位 置 : 上 問 8 平 均 気 温 の 系 列 を 次 の 設 定 で 編 集 しなさい 線 の 色 :オレンジ 系 の 色 - 8 -

9 問 9 次 のページ 設 定 をし 印 刷 しなさい 印 刷 の 向 き : 横 印 刷 範 囲 :セル B2:N38 A4 一 枚 に 納 めて 印 刷 できるようにする 余 白 : 上 下 左 右 とも 3cm ページの 中 央 ( 水 平 / 垂 直 とも)に 配 置 ヘッダー : 右 側 本 日 の 日 付 ( 試 験 日 )を 自 動 挿 入 で 表 示 フッター : 中 央 シート 名 を 自 動 挿 入 で 表 示 < 完 成 図 > - 9 -

10 課 題 5 マクロ 作 成 シート 目 次 を 開 いて 以 下 の 問 にしたがって 表 を 完 成 させなさい なお VBA(Visual Basic for Applications)を 利 用 すること 問 1 シート 目 次 を 除 いた 既 存 シートのシート 名 一 覧 をハイパーリンク 付 きで 作 成 しな さい かつ 適 正 な 場 所 にボタン 目 次 作 成 を 追 加 配 置 して リンクさせなさい さ らにシート 名 欄 の 対 象 セルを 選 択 して クリックすればそのシートが 開 くようにする こと 参 考 Hyperlinks オブジェクトの Add メソッドの 書 式 Object.Hyperlinks.Add _ Anchor:= リンクセル,Address:= 飛 び 先 1, SubAddress:= 飛 び 先 2,TextToDisplay:= リンクセルに 表 示 内 容 今 回 の Object はアクティブシートである 問 2 問 1で 作 成 したシート 名 一 覧 (セル C8:D22)を 削 除 して 元 の 状 態 に 戻 しなさい さらに 適 正 な 場 所 にボタン 目 次 クリア を 追 加 配 置 して リンクさせなさい < 完 成 図 >