表計算競技 事前課題
※これは、第１４回東京障害者技能競技大会における事前課題である。参考として公表されるものであり、 実際に使われる競技課題は、事前課題の仕様の一部を変更したものである。 １ 競技内容 作品は、課題 1～5 の 5 種類とする。 （１）課題１：ピボットテーブル・編集 予め準備されているデータ（以下、提供データ）をもとに、ピボットテーブル作成、図形作 成、書式設定、条件付き書式の設定等を行う。 （２）課題２：関数式による表の完成 提供データをもとに、名前の定義されたリストを利用して、指示に従い最終的値を求める。 （３）課題３：データ処理 提供データをもとに、レコードの抽出、並べ替え等を行う。かつ抽出されたレコードがど の条件で抽出されたかが分かるようにする。 （４）課題４：グラフ作成 提供データをもとに、セルの複数範囲の選択によるグラフの作成、印刷設定等を行う。 （５）課題５：マクロ作成 シート「目次」を除いた全てのシートをもとに、それらのシート名を使って目次を作成する。 *提供データの説明 ・ファイル名：｢H27 東京アビリン 提供データ.xlsm｣ ・９つのシート名：「目次」｢課題１受注一覧｣｢課題１ピボット｣｢課題１受注集計｣｢課題２料金計 算表｣｢課題２料金表｣｢課題３図書一覧表｣｢課題３図書抽出｣｢課題４グラフ｣ ２ 競技時間 ９０分 なお、作成する課題の順番は任意とする。 ３ 課題提出方法 競技者は、完成した作品（未完成作品も含む）を大会主催側の用意する外付けの USB メモリに保存す るとともに、印刷指示があったものは印刷物も提出すること。 なお、提出に係る試し印刷及びファイルの保存は競技時間内に含まれるが、提出作品の印刷は競技時 間には含まれない。 ４ 競技会場に準備してあるもの（１） OS ：Microsoft Windows ７ Pro
（２） 表計算ソフト ：Microsoft Excel2010（Microsoft Office 2010） （３） PC ：DOS/V 互換機 （４） USB メモリ ：32MB 以上 ５ 注意事項 （１） 提供データは大会当日に配布されるファイルを使用すること。 （２） 競技中に万一機器が故障した場合は、審査員の指示に従うこと。 （３） 競技が終了したら審査員に申し出ること。 （４） 競技場での座席位置は、予め審査員が決定しておく。 （５） 事前に配布された課題、メモ、参考書、その他の資料等の持込は一切不可とする。
【課題１】 ピボットテーブル・編集 ファイル｢H27 東京アビリン 提供データ｣を開き、シート｢課題１受注一覧｣を表示し、以下 の設問にしたがってピボットテーブルを作成後、表をコピーし整えなさい。 シートは、｢課題１受注一覧｣｢課題１ピボット｣｢課題１受注集計｣の３種類である。 シート｢課題１受注一覧｣は、伝票番号,商品,単価,数量,売上額,配送地域,受注日,発注日か らなるレコードが530 件ある。 シート｢課題１ピボット｣は、シート｢課題１受注一覧｣のデータを使用しピボットテー ブル作成を行うためのものである。 シート｢課題１受注集計｣は、シート｢課題１ピボット｣の表をコピーし、書式を設定す るためのものである。 問１ 表のデータをもとに、次の設定でピボットテーブルを作成しなさい。 配置する場所：シート｢課題１ピボット｣ セル B2 行ラベル ：商品 列ラベル ：配送地域 Σ 値 ：売上額 集計方法を｢合計｣とする ＜途中完成図＞ 問２ ピボットテーブルのデータ範囲 B3:H18 をコピーし、シート｢課題１受注集計｣のセ ル B7 に、元の列幅を保持して貼り付けなさい。 ピボットテーブルの最初の行（セル B2:H2）はコピー範囲に含めないものとする。 問３ ｢行ラベル｣のセル B7 を｢ギフト商品セット名｣に変更しなさい。 また、セル B7 の書式をセル B22 に貼り付けなさい。
問４ 以下のように設定しなさい。 項目名（セル B7:H7）の文字 ：前後にスペースが入った均等割り付け 数値データ（セル C8:H22） ：桁区切りスタイル 問５ ギフト商品セット名ごとの総計が最大であるレコードは、ギフト商品セット名（セ ル B8:B21）の塗りつぶしの色が赤系になるよう条件付き書式で設定しなさい。 また、ギフト商品セット名ごとの総計が最小であるレコードは、ギフト商品セット名 （セル B8:B21）の塗りつぶしの色が緑系になるよう条件付き書式で設定しなさい。 問６ 以下のように罫線を設定しなさい。 表の範囲（セル B7:H22） ：格子線 項目名（セル B7:H7） ：下太罫線 問７ C～F 列・H 列の列幅を G 列と同じ幅に設定しなさい。 問８ 次の設定で表題を作成しなさい。 配置する場所：セル B2:H5 （概ねで良い） 表示する文字：ギフト商品地域別売上集計 図形 ：対角する２つの角を切り取った三角形 塗りつぶし ：薄い青色系の色 フォント ：フォントサイズ：24 フォント ：MS 明朝 フォントの色 ：黒色系 太字 文字配置 ：縦、横位置ともに中央揃え ＜完成図＞
【課題２】 関数式による表の完成 OA オフィス器具備品レンタル会社において、器具備品別レンタル期間を指定して利用料 金を算出する問題である。 シート｢課題２料金計算表｣を開き、次に示す各シートの概要をもとに、以下の指示に従 って利用料金を算出しなさい。 課題２で利用するシートは、｢課題２料金計算表｣及び｢課題２料金表｣の２種類である。 シート｢課題２料金表｣には、｢種類｣、｢デスク｣、｢椅子｣、「テーブル・応接セット」、「ロ ッカー・保管庫」、「OA 機器・季節物」の６つのリストがある。 シート｢課題２料金表｣には、利用しやすいように特定セル範囲に名前をすでに以下の ように定義されている。 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 オフィス器具備品の利用料金計算ための事前設定 問１ No 欄を指定しない行(レコード)は非表示にする。（特に、金額とレンタル料欄） 問２ 種類を入力するのにプルダウンリストをセルC6:C20 に一括して設定する。 元の値は、セル名である「種類」を使用すること。 問３ 品名を入力するのにプルダウンリストをセルD6:D20 に一括して設定しなさい。 ただし、種類を選んだ時、対象になる品名のみが表示されるようにすること。
問４ 個数を入力するのにプルダウンリストをセルE6:E20 に一括して設定しなさい。 元の値は”１～10”までとする。 問５ 期間を入力するのにプルダウンリストをセルF6:F20 に一括して設定しなさい。 元の値はセル名である「期間」を使用すること。 Ⅱ．レンタル OA オフィス器具備品の利用料金計算 問１ 金額は、品名と期間からシート｢課題２料金表｣のリストから導き出す。 具体的には、対応する各種類別リスト（マトリックス）を使って、品名と期間をキ ーに該当する金額を求める。 問２ レンタル料は、金額 * 個数とする。 問３ 総合計金額を算出する。 ①合計金額は、レンタル料(セル H6:H15)の合計である。 ②消費税 ＝ 合計金額 * 0.08(8%) ③総合計金額 ＝ 合計金額 + 消費税 【参考例】 シート「課題２料金計算表」
【料金表】
【課題３】 データ処理 各シートの説明をもとに、以下の設問に従って データ処理を行いなさい。 シートは、｢課題３図書一覧表｣及び｢課題３図書抽出｣の２種類である。 シート｢課題３図書一覧表｣は、図書 CD,図書名,発行年月日,著者名,出版社名,内容紹介, 貸出件数の７項目からなるレコードが100 件ある。 シート｢課題３図書抽出｣は、シート｢課題３図書一覧表｣からデータの抽出を行うため のものである。 問１ 次の指示に従い、シート｢課題３図書一覧表｣からシート｢課題３図書抽出｣へ抽出を 行いなさい。 ＜抽出 1＞ ・抽出先セル セル A3 以降に ・抽出条件 一度も貸し出されていない本 ・並び順 発行年月日が古いもの順 ＜抽出 2＞ ・抽出条件セル 抽出１処理で表示されたセル以降に ・抽出先セル 抽出条件セル以降に ・抽出条件 ①図書内容に｢日本｣を含み、かつ発行日が2010 年以降 ②図書名に｢散歩｣を含み、かつ貸出件数が100 件以上 ③著者名が｢小栗 景子｣ 問２ 条件付き書式を使って、問１の抽出２の抽出条件にあった抽出されたレコードの対 象セルに以下の背景色を設定しなさい。 ＜背景色＞ ① 黄色系 ② 水色系 ③ 橙色系 ＜完成図＞
【課題４】 グラフ作成 シート｢課題４グラフ｣に以下の設問にしたがって、データをもとにグラフを作成しなさい。 問１ セル B11:N14 を使用し、以下の設定でグラフを作成しなさい。 グラフの種類：集合縦棒 グラフの配置：セル B16:N38（概ねで良い） 次に、東京都の2014 年平均気温（B4:N14）を、以下の設定でグラフに追加しなさ い。 グラフの種類：折れ線 縦（値）軸：第２軸を使用 問２ グラフエリアを次の設定で編集しなさい。 枠線の色 ：濃いオレンジ系の色 枠線のスタイル：角を丸くする 影 ：オフセット（斜め右下） 塗りつぶし ：薄いオレンジ系の色 問３ グラフタイトルをグラフの上に表示し、次の設定で編集しなさい。 タイトル ：セル B10 とリンクして表示 枠線の色 ：濃い赤系の色 問４ 凡例を次の設定で編集しなさい。 位置 ：上 問５ 横（項目）軸の軸ラベルを横書きで表示し、次の設定で編集しなさい。 横（項目）軸 ：（2014 年） （ ）は全角とすること。 位置 ：下側右部 問６ 第2 軸 縦（値）軸を次の設定で編集しなさい。 最小値 ：－３０ 最大値 ：３０ 問７ ｢平均気温｣の系列に次の設定でデータラベルを表示しなさい。 ラベルの内容 ：値 ラベルの位置 ：上
問９ 次のページ設定をし、印刷しなさい。 印刷の向き ：横 印刷範囲 ：セル B2:N38 A4 一枚に納めて印刷できるようにする 余白 ：上下、左右とも3cm ページの中央（水平／垂直とも）に配置 ヘッダー ：右側 ｢本日の日付｣（試験日）を自動挿入で表示 フッター ：中央 ｢シート名｣を自動挿入で表示 ＜完成図＞
【課題５】 マクロ作成
シート｢目次｣を開いて、以下の問にしたがって表を完成させなさい。 なお、VBA（Visual Basic for Applications）を利用すること。
問１ シート｢目次｣を除いた既存シートのシート名一覧をハイパーリンク付きで作成しな さい。かつ適正な場所にボタン｢目次作成｣を追加・配置して、リンクさせなさい。さ らにシート名欄の対象セルを選択して、クリックすればそのシートが開くようにする こと。 参考 Hyperlinks オブジェクトの Add メソッドの書式 Object.Hyperlinks.Add _
Anchor:= リンクセル,Address:=飛び先 1, SubAddress:=飛び先 2,TextToDisplay:= リンクセルに表示内容
※今回の Object はアクティブシートである。
問２ 問１で作成したシート名一覧（セル C8：D22）を削除して、元の状態に戻しなさい。 さらに、適正な場所にボタン｢目次クリア｣を追加・配置して、リンクさせなさい。 ＜完成図＞
