日本情報処理検定協会後援文部科学省主催の検定問題を解くために！ 本練習問題を使用し、処理手順の解説を行います。 PC サークル 編集オリジナル 情報処理技能検定試験（表計算）１級練習問題 ＜問 題＞ 1.下記の＜入力データ＞を基に、<処理条件>に従って表およびグラフを完成し、印刷しなさい。 表題は表の中央、見出しは中央揃え、文字は左揃え、数字は右揃えとし、3 桁ごとにコンマをつけること。 2.使用したテーブルを印刷しなさい。(表の形式は問わない) 3.データ保存またはすべての表の数式印刷をしなさい。 4.試験時間は 30 分とする。ただし、印刷は試験時間外とする。 ＜処理条件＞ 1.<出力形式１>のような販売手数料一覧表を作成しなさい。（― の部分は空白とする） 2.商品名は<商品テーブル>の商品を検索し、"商品"の文字を関数または演算子を使用して結合しなさい。 (例 A 商品) 3.等級は、CO の右から 1 文字目とし、関数を使用し求めなさい。 4.売価＝定価☓0.93(整数未満切捨て) ※定価は<商品テーブル>を参照する。 増減絶対値＝前記数量-後期数量 または 増減絶対値＝後記数量-前期数量 売上額＝売価☓(前記数量+後期数量) 5.手数料率は<商品テーブル>を表検索しなさい。(％の小数第一位までの表示とする) 手数量＝売上額☓手数料率(整数未満切上げ) 6.乗率は<乗率テーブル>を表検索しなさい。 (％の小数第一位までの表示とする) 7.合計を求めなさい。 8.<出力形式１>と同じ形式で、後期数量が 600 以下で手数料が 60，000 以上を抽出しなさい。 表題は”販売手数料一覧表(後期数量 600 以下・手数料 60，000 円以上)”とし、売上額の降順にソートしなさい。 9.販売手数料一覧表を基に<出力形式 2>のような処理をしなさい。 10.販売手数料一覧表を基に<出力形式 3>のような処理をしなさい。(平均は整数未満四捨五入の表示とする) 11.罫線の太線と細線を区別する。(外枠は太線とする） 12.<出力形式 2>の手数料・報奨金を商品別の積上げ棒グラフにしなさい。 タイトルは”手数料・報奨金の比較"とし、凡例は”手数料”、”報奨金”とする。 ＜出力形式 1＞ ＜出力形式 2＞ ＜出力形式３＞ 商品名 売上額 手数料 報奨金 A商品 B商品 C商品 D商品 等級がXまたはZの手数料の合計 商品別集計表 後期数料が500より多く700未満での売上額の平均 手数料が60,000円以上で報奨金が25,000円未満の件数 CO 商品名 等級 区分 売価 前期数量 後期数量 増減絶対値 売上額 手数料率 手数料 乗率 報奨金 101X 11 694 566 （ 途中省略 ） ― 合計 ― ― ― ― ― ― 販 売 手 数 料 一 覧 表 ＜入力データ＞ CO 区分 前期数量 後期数量 <商品テーブル> 101X 11 694 566 CO 商品 定価 手数料率 202Z 21 400 356 101X A 1240 7.3% 303Y 32 500 450 202Z B 980 6.9% 404X 15 650 489 303Y C 1130 6.5% 101X 31 345 486 404X D 780 6.1% 202Z 26 541 489 303Y 11 760 668 <乗率テーブル> 404X 15 618 754 101X 28 800 700 10～19 20～29 30～39 202Z 29 543 362 Ｘ 1.7% 2.6% 3.5% 303Y 35 372 468 Ｙ 1.5% 2.4% 3.3% 404X 38 486 658 Ｚ 1.3% 2.2% 3.1% 101X 33 612 536 202Z 22 729 689 303Y 15 851 710 404X 24 597 612 等級 区分
1.＜出力形式 1＞のような販売手数料一覧表を作成しなさい。（ ― の部分は空白とする） 回答として作成する必要があるのは＜出力形式＞のとおりの表となります。＜出力形式 1＞を見ながら表を作成します。 A1 セルに「販売手数料一覧表」と入力し、各見出しを入力します。 A2 セルに移動し「CO」から順に右へ「報奨金」までの見出しを入力します。 A3 セルに移動し、＜出力形式１＞と同じ見出しの列に＜入力データ＞をすべて入力します。
2.商品名は<商品テーブル>を検索し、”商品”の文字を関数または演算子を使用して結合しなさい。
(例 A 商品)
複数の文字列(別々のセルにあるデータや文字列など)を結合して１つの文字列にします。 この場合「“＆”」演算子を使用します。<商品テーブル>を参照し、テーブルを作成します。 ここでは最下行前行で指定すると以下のようになる。※セル番号は作成した表の位置によって変わります。 このように、テーブル(ｾﾙの値)から項目を検索する場合は 「VLOOKUP 関数」を使用します。 また、別解として以下の CONCATENATE 関数を使用しても 同じ結果が得られます。 今回の処理の場合、データ行の最下行で文字列１は =CONCATENATE(VLOOKUP(A19,$O$3:$R$6,2),"商品")の中の緑字で示したようになる。3.等級は CO の右から 1 文字目とし、関数で求めなさい。
文字列の右端から、指定した数の文字を求める場合 RIGHT 関数を用いる。
ここでは =RIGHT(A4,1) ……右から１文字を抽出する。その後、最下行までコピーする。 また、 設定例 左(先頭)から、指定した数の文字列を抽出する場合は LEFT 関数を用いる。 この他文字列の途中文字を抽出する場合は、MID 関数を用いる。4.売価＝定価☓0.93(整数未満切捨て) ※定価は<商品テーブル>を参照する。
①参照する場合は、VLOOKUP 関数を想起して考えましょう。 E4 セルを選択して数式バーの [関数の挿入]をクリックします。 VLOOKUP 関数を選択します。 検索値＝表内最左列のセルを選択する｡ 範囲＝<商品テーブル>のデータ範囲を選 択する。テーブルの位置は固定なので、 絶対セルを指定する。 列番号＝検索値を含めて何番目に定価が あるかで設定する。ここでは 3 番目です。 検索方法＝通常”0”で指定します。 この関数は、テーブルの最左列に引数がある場合に限られます。 =VLOOKUP(A4,$O$4:$R$7,3,0) …S4：R7 のセル範囲にテ－ブルデータがあることにします。 ②四則演算で与式を完成します。 =E25*0.93 …ここで、E25＝VLOOKUP(A4,$O$4:$R$7,3,0) ③整数未満切捨ての場合は、ROUNDDOWN 関数を想起して考えましょう。 =ROUNDDOWN(E42,0) …ここで、E42＝E25*0.93 という設定になります。 従って、最終的に E4 セルの与式は以下のように設定します。 CO 解 101X 10 101X 101 101X 1X =LEFT(B27,2) =MID(B28,1,3) =RIGHT(B29,2) 数式数値＝VLOOKUP(A4,$O$4:$R$7,3,0)*0.93 桁数＝0(整数未満切り捨て) ここで、端数処理を行います。 ⅰ)数値の設定は与式部を切取り（ ）、 ⅱ)ROUNDDOWN 関数(ウインドウ)を開く。 ⅲ)数値枠にカーソルを置いて貼り付け( )を行う。 ⅳ) をクリックして完了です。 4.増減絶対値＝前期数量-後期数量 （絶対値なので、前期と後期が反対でも可） 四則演算式を入力し、その式を以下の ABS 関数を読み込み、数値枠に入力する。 で設定終了。 4.売上額＝売価☓(前記数量+後期数量) 当該セルに四則演算式を入力する。 ①増減絶対値＝前記数量-後期数量 または 増減絶対値＝後記数量-前期数量 四則演算式を入力し、その式を以下の ABS 関数、数値枠に入力する。 ②売上額＝売価☓(前記数量+後期数量) 四則演算式を入力する。 5.手数料率は<商品テーブル>を表検索しなさい。(％の小数第一位までの表示とする) 売価と同様に VLOOKUP 関数で手数料率を設定する。 ①手数料率を求める。 0.073 =VLOOKUP(A4,$O$4:$R$7,4,0) ②小数で表示された場合は、1)ホームタブリビンの中から数値グループにある％をクリックする。 2)更に桁移行を行い「%の小数第一位までの表示」にします。 ※この部分はセルを選択して、書式設定ウインドウで設定しても良い。 ポイント：数値グループのコマンドを使いこなそう！ 手数量＝売上額☓手数料率(整数未満切上げ) 与式に従って、売上額☓手数料率の四則演算式を設定する。 ここで、条件により(整数未満切上げ)端数処理を行う。 整数未満切り上げ場合は、ROUNDUP 関数を想起して考えましょう。
6.乗率は<乗率テーブル>を表検索しなさい。 (％の小数第一位までの表示とする) この場合は特殊な設定が必要になる。 ここでは、VLOOKUP 関数の応用です、列番号の考え方を学びます。 このように区分が数値範囲で指定されている場合は、 列番号を設定するに当って以下のように考える。 列番号の求め方 ※端数処理は、ここでは ROUNDDOWN 関数でも同じ結果が得られる。が、その必要はない。 ※列番設定の方法として２例を示した。設定例１を使用して乗率の求め方を示した。 列番設定１ =INT((D26/10)+1) 列番設定２ =ROUNDDOWN(((D26/10)+1),0) 乗率設定=VLOOKUP(RIGHT(B26,1),$O$13:$R$15,INT(D26/10)+1,0) <乗率テーブル> 10～19 20～29 30～39 Ｘ 1.7% 2.6% 3.5% Ｙ 1.5% 2.4% 3.3% Ｚ 1.3% 2.2% 3.1% 区分 等級 CO 等級 区分 列番 列番 乗率
101X
X
11
2
2
1.7%
202Z
Z
21
3
3
2.2%
303Y
Y
32
4
4
3.3%
404X
X
15
2
2
1.7%
101X
X
31
4
4
3.5%
202Z
Z
26
3
3
2.2%
303Y
Y
11
2
2
1.5%
404X
X
15
2
2
1.7%
101X
X
28
3
3
2.6%
202Z
Z
29
3
3
2.2%
303Y
Y
35
4
4
3.3%
404X
X
38
4
4
3.5%
101X
X
33
4
4
3.5%
202Z
Z
22
3
3
2.2%
303Y
Y
15
2
2
1.5%
404X
X
24
3
3
2.6%
6.乗率は<乗率テーブル>を表検索しなさい。 (％の小数第一位までの表示とする) 報奨金＝売上額☓乗率(10 位未満切捨て) …条件により ROUNDDOWN 関数を用いる。 ①四則演算式を入力する。 ⇒数式をコピーする。 ②ROUNDDOWN 関数を設定する。 ⇒数値枠に貼り付ける。 ③桁区切り位置を設定する。…桁数＝-1(10 位未満) 7.合計を求めなさい。 ①入力セルを選択する。 ②リボンのΣオート SUM をクリックする。 ③合計対象セル範囲を選択する。 ④ で入力完了。 ⑤コピーして、他の合計セルに数式貼り付けを行う。 8.<出力形式１>と同じ形式で、後期数量が 600 以下で手数料が 60，000 以上を抽出しなさい。 表題は”販売手数料一覧表(後期数量 600 以下・手数料 60，000 円以上)”とし、売上額の降順にソートしなさい。 ①フィルター抽出を行う。 条件；後期数量が 600 以下 ＆ 手数料 60，000 円以上 方法１ 項目行を選択して、ホームタブ→並べ替えとフィルターグループ→フィルターをクリックする。 この場合は、販売手数料一覧表そのものを使用して設定す るため同行に表示されている表示は隠れてしまう可能性 があるため注意が必要です。 また、設定されている集計行は SUM 関数では設定できませ ん。 抽出データをコピーして、別の任意の場所に貼り付けてか ら SUM 関数を再設定する。
方法 2 データタブ→並べ替えとフィルターグループ→フィルター【詳細設定】ボタンをクリックする。 ダイアログボックスの設定 指定した範囲をクリックする。 リスト範囲…データベースの明細欄を選択する。 検索条件範囲…条件表を作成し、その範囲を選択する。 抽出範囲…抽出データを表示するセルを指定する。 OK をクリックする。 ※詳細はエクセル作成回答を参照 9.販売手数料一覧表を基に<出力形式 2>のような処理をしなさい。<出力形式 1>の販売手数料一覧表から、商品 名ごとに売上額・手数料・報奨金のレコードを集計(合計)した商品別集計表を作成します。 例)A 商品の売上額を合計する。 データベース中の条件を満たすレコードの合計を求める場合、DSUM 関数を使用します。 ※この他、同様にして平均,件数,最大値, 最小値が求められます。 詳細は、関数マニュアル参照 この場合は、販売手数料一覧表から抽出したデータを表示する場所を任 意に指定(ダイアログボックス_抽出範囲)する。 また、設定されている集計行は SUM 関数で再設定します。 このデータは数値データで抽出され、数式は貼り付けできません。
DSUM 関数を使用します。 ①条件表を作成します。 ②DSUM 関数を設定します。 例えば、A 商品の売上額の設定は、 ここで、 データベース,条件枠に関しては絶対セルで指定する。 また、フィールド枠については、 商品別集計表の売上額・手数料・報奨金は同行なので行固定で指定する。 10.販売手数料一覧表を基に<出力形式 3>のような処理をしなさい。(平均は整数未満四捨五入の表示とする) データベース関数を使用して設定します。 AND 条件 条件を横に並べて作成し、条件の全てを 含む範囲を指定します。 OR 条件 条件を縦に並べて作成し、条件の全てを 含む範囲を指定します。 ﾃﾞｰﾀﾍﾞｰｽ枠＝対象セル範囲を選択する。 ﾌｨｰﾙﾄﾞ枠＝商品別集計表の売上額セルを選択する。 条件枠＝商品名・A 商品セルを選択する。
ここまでの処理が完了したら、問題に指示されている以下の設定を行います。 表題は表の中央、見出しは中央揃え、文字は左揃え、数字は右揃えとし、3 桁ごとにコンマをつけること。 11.罫線の太線と細線を区別する。（外枠は太線とする） 4 級問題で実施。 4 級問題で実施。 12.<出力形式 2>の手数料・報奨金を商品別の積上げ棒グラフにしなさい。 タイトルは”手数料・報奨金の比較"とし、凡例は”手数料”、”報奨金”とする。 別途作成 回答の印刷は試験時間後に行います。 印刷が２ページになっても良いが、１つの表が２ページになっていると減点対象です。
準１級・１級・初段は、保存媒体への結果データの保存または数式印刷を行います。試験監督の指示に従って ください。
