1.< 出力形式 1>のような販売手数料一覧表を作成しなさい ( の部分は空白とする ) 回答として作成する必要があるのは< 出力形式 >のとおりの表となります < 出力形式 1>を見ながら表を作成します A1 セルに 販売手数料一覧表 と入力し 各見出しを入力します A2 セルに移動し CO から

Transcription

1 日本情報処理検定協会後援文部科学省主催の検定問題を解くために! 本練習問題を使用し 処理手順の解説を行います PC サークル編集オリジナル < 問 題 > 情報処理技能検定試験 ( 表計算 )1 級練習問題 1. 下記の < 入力データ > を基に < 処理条件 > に従って表およびグラフを完成し 印刷しなさい 表題は表の中央 見出しは中央揃え 文字は左揃え 数字は右揃えとし 3 桁ごとにコンマをつけること 2. 使用したテーブルを印刷しなさい ( 表の形式は問わない ) 3. データ保存またはすべての表の数式印刷をしなさい 4. 試験時間は 30 分とする ただし 印刷は試験時間外とする < 入力データ> CO 区分前期数量後期数量 < 商品テーブル > 101X CO 商品 定価 手数料率 202Z X A % 303Y Z B % 404X Y C % 101X X D % 202Z Y < 乗率テーブル > 404X 区分等級 101X ~19 20~29 30~39 202Z X 1.7% 2.6% 3.5% 303Y Y 1.5% 2.4% 3.3% 404X Z 1.3% 2.2% 3.1% 101X Z Y X < 処理条件 > 1.< 出力形式 1> のような販売手数料一覧表を作成しなさい ( の部分は空白とする ) 2. 商品名は < 商品テーブル > の商品を検索し " 商品 " の文字を関数または演算子を使用して結合しなさい ( 例 A 商品 ) 3. 等級は CO の右から 1 文字目とし 関数を使用し求めなさい 4. 売価 = 定価 0.93( 整数未満切捨て ) 定価は < 商品テーブル > を参照する 増減絶対値 = 前記数量 - 後期数量または増減絶対値 = 後記数量 - 前期数量売上額 = 売価 ( 前記数量 + 後期数量 ) 5. 手数料率は < 商品テーブル > を表検索しなさい (% の小数第一位までの表示とする ) 手数量 = 売上額 手数料率 ( 整数未満切上げ ) 6. 乗率は < 乗率テーブル > を表検索しなさい (% の小数第一位までの表示とする ) 7. 合計を求めなさい 8.< 出力形式 1> と同じ形式で 後期数量が 600 以下で手数料が 60,000 以上を抽出しなさい 表題は 販売手数料一覧表 ( 後期数量 600 以下 手数料 60,000 円以上 ) とし 売上額の降順にソートしなさい 9. 販売手数料一覧表を基に < 出力形式 2> のような処理をしなさい 10. 販売手数料一覧表を基に < 出力形式 3> のような処理をしなさい ( 平均は整数未満四捨五入の表示とする ) 11. 罫線の太線と細線を区別する ( 外枠は太線とする ) 12.< 出力形式 2> の手数料 報奨金を商品別の積上げ棒グラフにしなさい タイトルは 手数料 報奨金の比較 " とし 凡例は 手数料 報奨金 とする < 出力形式 1> 販売手数料一覧表 CO 商品名等級区分売価前期数量後期数量増減絶対値売上額手数料率手数料乗率報奨金 101X ( 途中省略 ) 合計 < 出力形式 2> 商品別集計表 商品名 売上額 手数料 報奨金 A 商品 B 商品 C 商品 D 商品 < 出力形式 3> 後期数料が500より多く700 未満での売上額の平均手数料が60,000 円以上で報奨金が25,000 円未満の件数等級がXまたはZの手数料の合計

2 1.< 出力形式 1>のような販売手数料一覧表を作成しなさい ( の部分は空白とする ) 回答として作成する必要があるのは< 出力形式 >のとおりの表となります < 出力形式 1>を見ながら表を作成します A1 セルに 販売手数料一覧表 と入力し 各見出しを入力します A2 セルに移動し CO から順に右へ 報奨金 までの見出しを入力します A3 セルに移動し < 出力形式 1>と同じ見出しの列に< 入力データ>をすべて入力します 2. 商品名は < 商品テーブル > を検索し 商品 の文字を関数または演算子を使用して結合しなさい ( 例 A 商品 ) 複数の文字列 ( 別々のセルにあるデータや文字列など ) を結合して1つの文字列にします この場合 & 演算子を使用します < 商品テーブル > を参照し テーブルを作成します ここでは最下行前行で指定すると以下のようになる セル番号は作成した表の位置によって変わります このように テーブル ( セルの値 ) から項目を検索する場合は VLOOKUP 関数 を使用します また 別解として以下の CONCATENATE 関数を使用しても同じ結果が得られます 今回の処理の場合 データ行の最下行で文字列 1 は =CONCATENATE(VLOOKUP(A19,$O$3:$R$6,2)," 商品 ") の中の緑字で示したようになる

3 3. 等級は CO の右から 1 文字目とし 関数で求めなさい 文字列の右端から 指定した数の文字を求める場合 RIGHT 関数を用いる ここでは =RIGHT(A4,1) 右から 1 文字を抽出する その後 最下行までコピーする また 左 ( 先頭 ) から 指定した数の文字列を抽出する場合は LEFT 関数を用いる この他文字列の途中文字を抽出する場合は MID 関数を用いる 設定例 CO 解 101X X X 1X 数式 =LEFT(B27,2) =MID(B28,1,3) =RIGHT(B29,2) 4. 売価 = 定価 0.93( 整数未満切捨て ) 定価は < 商品テーブル > を参照する 1 参照する場合は VLOOKUP 関数を想起して考えましょう E4 セルを選択して数式バーの [ 関数の挿入 ] をクリックします VLOOKUP 関数を選択します 検索値 = 表内最左列のセルを選択する 範囲 =< 商品テーブル > のデータ範囲を選択する テーブルの位置は固定なので 絶対セルを指定する 列番号 = 検索値を含めて何番目に定価があるかで設定する ここでは 3 番目です 検索方法 = 通常 0 で指定します この関数は テーブルの最左列に引数がある場合に限られます =VLOOKUP(A4,$O$4:$R$7,3,0) S4:R7 のセル範囲にテ-ブルデータがあることにします 2 四則演算で与式を完成します =E25*0.93 ここで E25=VLOOKUP(A4,$O$4:$R$7,3,0) 3 整数未満切捨ての場合は ROUNDDOWN 関数を想起して考えましょう =ROUNDDOWN(E42,0) ここで E42=E25*0.93 という設定になります 従って 最終的に E4 セルの与式は以下のように設定します

4 数値 =VLOOKUP(A4,$O$4:$R$7,3,0)*0.93 桁数 =0( 整数未満切り捨て ) ここで 端数処理を行います ⅰ) 数値の設定は与式部を切取り ( ) ⅱ)ROUNDDOWN 関数 ( ウインドウ ) を開く ⅲ) 数値枠にカーソルを置いて貼り付け ( ) を行う ⅳ) をクリックして完了です 4. 増減絶対値 = 前期数量 - 後期数量 ( 絶対値なので 前期と後期が反対でも可 ) 四則演算式を入力し その式を以下の ABS 関数を読み込み 数値枠に入力する で設定終了 4. 売上額 = 売価 ( 前記数量 + 後期数量 ) 当該セルに四則演算式を入力する 1 増減絶対値 = 前記数量 - 後期数量または増減絶対値 = 後記数量 - 前期数量四則演算式を入力し その式を以下の ABS 関数 数値枠に入力する 2 売上額 = 売価 ( 前記数量 + 後期数量 ) 四則演算式を入力する 5. 手数料率は < 商品テーブル > を表検索しなさい (% の小数第一位までの表示とする ) 売価と同様に VLOOKUP 関数で手数料率を設定する 1 手数料率を求める =VLOOKUP(A4,$O$4:$R$7,4,0) 2 小数で表示された場合は 1) ホームタブリビンの中から数値グループにある % をクリックする 2) 更に桁移行を行い % の小数第一位までの表示 にします この部分はセルを選択して 書式設定ウインドウで設定しても良い ポイント : 数値グループのコマンドを使いこなそう! 手数量 = 売上額 手数料率 ( 整数未満切上げ ) 与式に従って 売上額 手数料率の四則演算式を設定する ここで 条件により ( 整数未満切上げ ) 端数処理を行う 整数未満切り上げ場合は ROUNDUP 関数を想起して考えましょう

5 6. 乗率は < 乗率テーブル > を表検索しなさい (% の小数第一位までの表示とする ) この場合は特殊な設定が必要になる ここでは VLOOKUP 関数の応用です 列番号の考え方を学びます このように区分が数値範囲で指定されている場合は 列番号を設定するに当って以下のように考える 列番号の求め方 < 乗率テーブル > 等級 区分 10~19 20~29 30~39 X 1.7% 2.6% 3.5% Y 1.5% 2.4% 3.3% Z 1.3% 2.2% 3.1% 端数処理は ここでは ROUNDDOWN 関数でも同じ結果が得られる が その必要はない 列番設定の方法として 2 例を示した 設定例 1 を使用して乗率の求め方を示した CO 等級区分列番列番乗率 101X X % 202Z Z % 303Y Y % 404X X % 101X X % 202Z Z % 303Y Y % 404X X % 101X X % 202Z Z % 303Y Y % 404X X % 101X X % 202Z Z % 303Y Y % 404X X % 列番設定 1 =INT((D26/10)+1) 列番設定 2 =ROUNDDOWN(((D26/10)+1),0) 乗率設定 =VLOOKUP(RIGHT(B26,1),$O$13:$R$15,INT(D26/10)+1,0)

6 6. 乗率は < 乗率テーブル > を表検索しなさい (% の小数第一位までの表示とする ) 報奨金 = 売上額 乗率 (10 位未満切捨て ) 条件により ROUNDDOWN 関数を用いる 1 四則演算式を入力する 数式をコピーする 2ROUNDDOWN 関数を設定する 数値枠に貼り付ける 3 桁区切り位置を設定する 桁数 =-1(10 位未満 ) 7. 合計を求めなさい 1 入力セルを選択する 2リボンのΣオート SUM をクリックする 3 合計対象セル範囲を選択する 4 で入力完了 5コピーして 他の合計セルに数式貼り付けを行う 8.< 出力形式 1> と同じ形式で 後期数量が 600 以下で手数料が 60,000 以上を抽出しなさい 表題は 販売手数料一覧表 ( 後期数量 600 以下 手数料 60,000 円以上 ) とし 売上額の降順にソートしなさい 1フィルター抽出を行う 条件 ; 後期数量が 600 以下 & 手数料 60,000 円以上方法 1 項目行を選択して ホームタブ 並べ替えとフィルターグループ フィルターをクリックする この場合は 販売手数料一覧表そのものを使用して設定するため同行に表示されている表示は隠れてしまう可能性があるため注意が必要です また 設定されている集計行は SUM 関数では設定できません 抽出データをコピーして 別の任意の場所に貼り付けてから SUM 関数を再設定する

7 方法 2 データタブ 並べ替えとフィルターグループ フィルター 詳細設定 ボタンをクリックする この場合は 販売手数料一覧表から抽出したデータを表示する場所を任意に指定 ( ダイアログボックス _ 抽出範囲 ) する また 設定されている集計行は SUM 関数で再設定します このデータは数値データで抽出され 数式は貼り付けできません ダイアログボックスの設定指定した範囲をクリックする リスト範囲 データベースの明細欄を選択する 検索条件範囲 条件表を作成し その範囲を選択する 抽出範囲 抽出データを表示するセルを指定する OK をクリックする 詳細はエクセル作成回答を参照 9. 販売手数料一覧表を基に < 出力形式 2> のような処理をしなさい < 出力形式 1> の販売手数料一覧表から 商品 名ごとに売上額 手数料 報奨金のレコードを集計 ( 合計 ) した商品別集計表を作成します 例 )A 商品の売上額を合計する データベース中の条件を満たすレコードの合計を求める場合 DSUM 関数を使用します この他 同様にして平均, 件数, 最大値, 最小値が求められます 詳細は 関数マニュアル参照

8 DSUM 関数を使用します 1 条件表を作成します 2DSUM 関数を設定します 例えば A 商品の売上額の設定は テ ータヘ ース枠 = 対象セル範囲を選択する ここで フィールト 枠 = 商品別集計表の売上額セルを選択する データベース, 条件枠に関しては絶対セルで指定する 条件枠 = 商品名 A 商品セルを選択する また フィールド枠については 商品別集計表の売上額 手数料 報奨金は同行なので行固定で指定する 10. 販売手数料一覧表を基に < 出力形式 3> のような処理をしなさい ( 平均は整数未満四捨五入の表示とする ) データベース関数を使用して設定します AND 条件条件を横に並べて作成し 条件の全てを含む範囲を指定します OR 条件 条件を縦に並べて作成し 条件の全てを 含む範囲を指定します

9 ここまでの処理が完了したら 問題に指示されている以下の設定を行います 表題は表の中央 見出しは中央揃え 文字は左揃え 数字は右揃えとし 3 桁ごとにコンマをつけること 11. 罫線の太線と細線を区別する ( 外枠は太線とする ) 4 級問題で実施 4 級問題で実施 12.< 出力形式 2> の手数料 報奨金を商品別の積上げ棒グラフにしなさい タイトルは 手数料 報奨金の比較 " とし 凡例は 手数料 報奨金 とする 別途作成 回答の印刷は試験時間後に行います 印刷が 2 ページになっても良いが 1 つの表が 2 ページになっていると減点対象です

10 準 1 級 1 級 初段は 保存媒体への結果データの保存または数式印刷を行います 試験監督の指示に従って ください