表計算公開課題（2019）２

以下は、アビリンピックおおさか２０１９大会における表計算競

技課題の様子を知るために参考として公開するものです。実際

の課題ではありませんのでご注意ください。

提供データ（Excel）を添付していませんので、本文のみで課題を

実施することはできません。

アビリンピックおおさか２０１９

表計算 競技模擬課題

１． 競技問題 「装飾・編集」、「データ処理」、「グラフ作成」、「関数式による表の完成」の4 問題 とする。 ２． 競技時間 競技時間は、4 問題で 90 分とする。 なお、作成する課題の順番は任意とする。 ３． 作品提出方法 競技者は、完成した作品（未完成作品も含む）を、各問題の指示に従ってデスクト ップ上の「提出」フォルダに保存し提出すること。 ４． 作品の採点 作品の採点は、提出されたデスクトップ上の「提出」フォルダに保存されているフ ァイルに対して行う。 同点の場合には、作成時間の短かった者を上位とする。 ５． 使用ソフト環境 大会主催者が用意したソフト環境で行う。OS は Windows10、表計算ソフトは MS Office2016 の Excel2016 とする。 ６． 終了合図 競技時間内に早く終了した場合は、手を上げて合図する。 声を出さないように注意すること。

【課題１ 装飾・編集】

「提供データ」フォルダからファイル「2019H-K1.xlsx」を開き、シート「Sheet1」に、 以下の設問に従って表を完成させなさい。 設問1 セル A1 にゼッケン番号を、セル A2 に競技者氏名を入力しなさい。ただし、 ゼッケン番号は全角で、競技者氏名については全角ひらがなで姓と名とを続け て入力しなさい。 設問2 以下の指示に従い、セルの設定を行いなさい。 区分 対象範囲 設定内容 文字配置 C9:N15 右詰め、上下中央揃え 設問3 次の指定セル範囲に、次表に示すとおりに数値データを追加入力しなさい。 区分 対象範囲 設定内容 数値の入 力 M9:N15 下表のとおり、数値データを入力する。 フォントサイズは「MS ゴシック 11 ポイント」とす る。 設問4 以下指示に従い、罫線の設定を行いなさい。 区分 対象範囲 設定内容 罫線の設定 B7:P15 罫線を設定する。 外枠および8 行目の下線、B 列右線 黒色、太い実線 内側の格子 黒色、実線

設問5 次の指示に従い、以下の表題を作成しなさい。なお、図形内の文字は全角とす る。 設問6 次の指示に従い、セルの設定を行いなさい。 区分 対象範囲 設定内容 条件式書式 C9:N9 大阪の降水量が上位 3 位である月のセルを太字・赤字 にする C10:N10 神戸の降水量が上位 3 位である月のセルを太字・赤字 にする C11:N11 京都の降水量が上位 3 位である月のセルを太字・赤字 にする 設問7 次の指示に従い、セルの設定を行いなさい。 区分 対象範囲 設定内容 列・行の調整 C 列～N 列 列の幅を6 にする 9 行～15 行 行の高さを20 にする 設問8 次の指示に従い、セルの設定を行いなさい。 区分 対象範囲 設定内容 数式の設定 O9:O15 1 月～12 月までの合計の降水量を計算する 数式の設定 書式の設定 P9:P15 年間降水量÷12 を計算する 書式を小数点以下2 桁まで表示する 設問9 デスクトップ上の「提出」フォルダの中に「課題１.xlsx」（全角）で保 存しなさい。 区分 概ねのセルの範囲 B4:J5 図形 種類 六角形 枠線 青 フォント MS 明朝、16 ポイント、文字色 白、太字 塗りつぶし 青 文字配置 縦・横位置ともに中央揃え

【課題２（データ処理）】

「提供データ」フォルダからファイル「HO-K2.xlsx」を開き、次に示す＜各シートの 概要＞をもとに、以下の設問に従ってデータ処理を行いなさい。 ＜各シートの概要＞ ・シートは「一覧表」及び「分析」の2 種類である。 ・シート「一覧表」は、JEED㈱の社員評価表を示すものである。 ・シート「分析」は、「一覧表」からデータの抽出を行うためのものである。 設問1 シート「分析」のセル A1 にゼッケン番号を、セル A2 に競技者氏名を入力し なさい。ただし、ゼッケン番号は全角で、競技者氏名については全角ひらがな で姓と名を続けて入力しなさい。 設問2 次の指示に従い、シート「一覧表」からシート「分析」へ抽出を行いなさい。 ＜抽出1＞ 抽出先 抽出条件 並び順 優先順位 対象項目 順序 セルA6： H10 「勤続年数」の上位5 位のもの （「勤続年数」の多いほうから5 つ） 第1 勤続年数 降順 第2 年齢 降順 ＜抽出2＞ 抽出先 抽出条件 並び順 優先順位 対象項目 順序 セル A16：H25 「部署」が「生産本部」または「研究開発部」かつ「評 価」が「A」であるもの 第1 年齢 降順 ＜抽出3＞ 抽出先 抽出条件 並び順 優先順位 対象項目 順序 セル A31：H45 「年齢」が50 歳未満でかつ「異動歴」が「大阪本社」 または「東京営業所」のもの 第1 年齢 降順 設問3 次の指示に従い、集計を行いなさい。 ＜集計作業用の新規シートの作成＞ 区分 設定内容 シート「一覧表」のコピー シートの末尾に挿入 シート名 集計

＜集計作業＞ 並び順 集計 優先順位 対象項目 順序 基準項目 集計項目 条件 第1 性別 昇順 性別 年齢 平均 小 数 点 第 一 位まで表示 第2 役職 降順 役職 表示先 シート「分析」セル範囲K5:M12 の該当欄 備考 ・シート「集計」における作業後の状態については不問 ・集計結果における表の表示形式及び罫線については不問 設問4 評価ごとの人数をカウントしなさい。「分析」シートの P7:P9 の表に評価 （A～C）の人数をカウントする関数を設定しなさい。また、Q7:Q9 の表に 人数の割合（人数÷４０×１００）を入力しなさい。 設問5 デスクトップ上の「提出」フォルダの中に「課題２.xlsx」（全角）で保 存しなさい。 【

課題３（グラフ作成）】

「提供データ」フォルダからファイル「H-K3.xlsx」を開き、以下の設問に従ってグラ フを完成させなさい。 設問1 セル A1 にゼッケン番号を、セル A2 に競技者氏名を入力しなさい。ただしゼ ッケン番号は全角で、競技者氏名については全角ひらがなで姓と名を続けて入 力しなさい。

設問2 ファイルのデータから、下の＜完成イメージ＞に示すグラフを、次の指示に従 い作成しなさい。 区分 設定内容 作成範囲 概ねセル範囲G5：P31 グラフの種類 2D 折れ線 積み上げ縦棒グラフ 提供データのタイトル 四国地方の年間降水量(mm) 表示内容 四国4 県の月別降水量 設問3 次の指示に従い、グラフの設定をしなさい。 区分 設定内容 ①グラフエリア ・枠線：黒 0.75pt、角丸 ・領域：塗りつぶし（白色） ②グラフタイトル ・タイトル：セルA4 とリンクして表示 ・枠線：黒色 領域：塗りつぶし（オレンジ色） ・フォント：黒色、MS ゴシックで 18 ポイント、太字 ③凡例 ・枠線：赤色 0.75pt、破線 領域：塗りつぶし（白色） ・位置：上 ・フォント：黒色、MS ゴシック 12 ポイント ④縦軸ラベル ・ラベル：「降水量（mm）」 ・領域：塗りつぶし（白色） ・フォント：黒色、MS ゴシック 12 ポイント 太字 ・位置：横書きで縦軸の左側中央付近 ⑤プロットエリア ・枠線：黒色 0.75pt・領域：塗りつぶし（オレンジ、アク セント2、白＋基本色 80%） ⑥縦軸 ・最大値：1000 目盛間隔：100 ・フォント：黒色、MS ゴシック 12 ポイント ⑦横軸 ・フォント：黒色、MS ゴシック 12 ポイント ・軸の書式設定（軸のオプション）： 線（単色）（黒色） ⑧データラベル ・すべてのデータ系列表示 ・枠線：なし ・領域：塗りつぶしなし ・ラベルの内容：値 ラベルの位置：中央 ・フォント：黒色、MS ゴシック 10 ポイント ⑨データ系列 ・系列の重なり：100％ 要素の感覚：60％ ・「愛媛」→色：青、アクセント1 ・「高知」→色：オレンジ、アクセント2 ・「徳島」→色：緑、アクセント6 ・「香川」→色：ゴールド、アクセント4

設問4 次の指示に従い、印刷に関するページ設定を行いなさい。 区分 設定内容 印刷範囲 ・セル範囲A4：P32 ・A4 横 1 枚に納めて印刷できるようにする 余白 ・上下、左右：2cm ・ページの中央（水平／垂直とも）に配置 ヘッダー 右側：「本日の日付」（試験日）を自動挿入で表示 フッター 中央：「ページ番号」を表示 ＜完成イメージ＞ 設問5 デスクトップ上の「提出」フォルダの中に「課題３.xlsx」（全角）で保存しな さい。

【課題４（関数式による表の作成）】

「提供データ」フォルダからファイル「HO-K4.xlsx」を開き、次に示す＜各シートの 概要＞をもとに、以下の設問に従ってデータ処理を行いなさい。 ＜各シートの概要＞ ・シートは「とりまとめ表」及び「リスト一覧」の2 種類である。 ・シート「リスト一覧」には、「部署情報一覧表」「役職情報一覧表」「勤務先区分一覧 表」の3 つの リストがある。 ・シート「とりまとめ表」の「㈱JEED 社員一覧」における社員 ID コードの年齢・性 別コードについては、次の内容を意味するものとする。 48 － 2 設問1 シート「とりまとめ表」のセル A1 にゼッケン番号を、セル A2 に競技者氏名 を入力しなさい。ただし、ゼッケン番号は全角で、競技者氏名については全角 ひらがなで姓と名を続けて入力しなさい。 設問2 次に示す箇所に、名前を定義しなさい。 対象シート 対象範囲 名前 リスト一覧 B5：C7 部署 E5：F7 役職 B11：C14 勤務先 設問3 シート「とりまとめ表」の次に示す箇所を、以下の指示及び順序に従い、関数 を用いて求めなさい。 順序 対象範囲 設定内容 （1） F8：F47 「部署情報一覧表」リストを参照して「部署名」を求める （2） G8：G47 「役職情報一覧表」リストを参照して「役職名」を求める （3） H8：H47 「勤務先区分一覧表」リストを参照して「勤務先」を求める （4） I8：I47 「年齢・性別コード」から「性別」（「男性」または「女性」を表示）を 求める （5） J8：J47 「年齢・性別コード」から「年齢」を求める（文字列ではなく数値とし て求める） （6） Q8：Q11 「勤務先」ごとの人数の合計を求める （7） R8：R11 「勤務先」ごとの平均年齢を求める 左1～2 桁：年齢 右1 桁：「1」が「男性」 「2」が「女性」

（8） Q17：Q19 「男性」「女性」「合計」の人数を求める （9） R17：R18 構成比率を求める数式を入力する（「人数」÷「合計」） （10） P23：P25 年齢が若い上位3 名の「年齢」を求める 設問4 シート「とりまとめ表」の次に示す箇所に，以下の指示に従い表示形式を設定 しなさい 対象範囲 設定内容 R17：R18 「○○%」となるように設定 設問 6 デスクトップ上の「提出」フォルダの中に「課題４.xlsx」（全角）で保存しな さい。