基本情報技術者過去問題平成27年秋期午後問13(ソフトウェア開発(表計算))

基本情報技術者過去問題平成27年秋期午後問13

問13　ソフトウェア開発(表計算)

次の表計算のワークシート及びマクロの説明を読んで，設問1，2に答えよ。

〔表計算の説明〕
　あるPC販売店では，セット値引きを導入することにした。セット値引きでは，指定された3種類の商品を同時に購入する場合，各商品を単品で購入する場合に比べて購入金額を安くする。セット値引きとなる商品の組合せ(以下，セットという)と値引きを適用していく順序は，商品の仕入状況と販売状況に基づいて適宜見直しを行う。セット値引きの導入に合わせて，これまで手書きであった購入伝票の作成について，表計算ソフトを使って行うことにした。

〔ワークシート：価格表〕
　商品の単価を記載したワークシート"価格表"の例を，図1に示す。

販売している商品は50品目あり，セルA2～A51には1から始まる連番で商品番号を入力する。
セル B2～B51 には商品名を，セル C2～C51 には商品コードを，セル D2～D51 には単価を入力する。商品名と商品コードは，それぞれ重複するものはない。

〔ワークシート：セット値引き表〕
　セットと値引き額を記載したワークシート"セット値引き表"の例を，図2に示す。

セットは10種類あり，値引きを適用していく順に入力する。
セル A3～A12 には，1から始まる連番でセット番号を入力する。
セル B3～B12 には，セット名を入力する。
セットは3種類の商品の組合せから成り，セル C3～H12 には組み合わせる商品の商品コードと数量を入力する。商品コードは，ワークシート"価格表"に登録されているものを用いる。
セルI3～I12には，セットの値引き額を入力する。
列 K～M は，セット値引き適用処理のための作業領域に用いる。

〔ワークシート：購入伝票〕
　購入する商品の一覧(購入内容)，セット値引き適用状況の一覧(セット値引き内容)，合計金額などを表示するワークシート"購入伝票"の例を，図3に示す。

1回に購入できる商品は20品目以下である。ワークシート"購入伝票"のセル B7～C26 の各行に，商品名と数量を1件ずつ入力すると，各行の列Dと列Eにそれぞれ単価と金額(数量×単価)が，セル E1 に金額の合計が表示される。ワークシート"価格表"に登録されていない商品名を入力することはない。また，同一の商品を2行以上入力することができる。
マクロ Calc_Discount_Price を実行すると，セル C30～C39 に，セット値引きを適用するセットについてだけ数量が設定され，対応する各行の列Eにセット値引き金額(数量×値引き額)が，セルE2にセット値引き金額の合計が表示される。

設問1

ワークシート"購入伝票"に関する次の説明中の　に入れる正しい答えを，解答群の中から選べ。

セル A7～A26 には，1から始まる連番を入力する。
セル E1 には，購入する商品の合計金額を求めるための式を入力する。
セル E2 には，セット値引き金額の合計を求めるための式を入力する。
セル E3 には，購入金額を求めるための式を入力する。
商品ごとの単価を表示するために，セル D7 に次の式を入力し，セル D8～D26 に複写する。
　　IF(B7＝null，null，垂直照合(B7，a，0))
商品ごとの金額を求めるために，セル E7 に次の式を入力し，セル E8～E26 に複写する。
　　IF(D7＝null，null，C7＊D7)
セル B30～B39 には，ワークシート"セット値引き表"のセット名を，同表と同じ順序で表示するための式を入力する。
セル C30～C39 には，マクロ Calc_Discount_Price を用いて，数量を設定する。
ワークシート"セット値引き表"の値引き額を，同表と同じ順序で表示するために，セル D30 に次の式を入力し，セル D31～D39 に複写する。
　　b
セット値引き金額を求めるために，セル E30 に次の式を入力し，セル E31～E39 に複写する。
　　IF(C30＝null，null，C30＊D30)

a に関する解答群

価格表!A$2:D$51,2
価格表!A$2:D$51,3
価格表!A$2:D$51,4
価格表!B$2:D$51,2
価格表!B$2:D$51,3

b に関する解答群

セット値引き表!A3
セット値引き表!A$3
セット値引き表!I3
セット値引き表!I$3

解答選択欄

解答

a=オ
b=ウ

解説

〔aについて〕
商品の単価はワークシート"価格表"のD列に入力されています。

垂直照合には4つの引数を指定します。

垂直照合(式，セル範囲，列の位置，検索の指定)

この関数は、まず第2引数で指定されたセル範囲の左端列を上から下に走査し、第1引数に合致する行を探します。そして、その行に対して左から数えた列位置のセルの値を返します。

設問の式では第1引数の値としてB7（商品名）を指定しているので、第2引数のセル範囲はワークシート"価格表"のB列（商品名）が左端表になっていなければなりません。また"単価"を得たいのでD列までをセル範囲に含める必要があります。したがってセル範囲は「B$2:D$51」となります。

第3引数には値を得たい列位置を指定します。"単価"を保持するD列は、B列（商品名）を1列目とすると3列目なので、列位置には「3」を指定します。

「ア」「イ」「ウ」は商品名をA列（商品番号）から探すので正しく検索できないので誤り、「エ」は左端列から2列目のC列（商品コード）の値が表示されるので誤りです。

∴a＝オ：価格表!B$2:D$51,3

〔bについて〕
セル D30～D39 にはワークシート"値引き表"の値引き額を、同表と同じ順序で表示します。単純に複写すれば良いのでワークシート"値引き表"のセル I3～I12 を参照する式を入力することになります。このときD3に入力された式は縦方向のセルに複写されるので、D3 には I3 の値、D4 には I4 の値というように複写先の行位置に応じて参照先の行位置が変わるようになっていなければなりません。"$"を付けると絶対参照となり常にD3を参照することになるので、行指定は相対参照（何も付けない）にするのが適切です。

「ア」「イ」はA列（セット番号）を表示するので誤り、「エ」は参照先行の固定により複写先で正しい値引き額が表示されないので誤りです。

∴b＝ウ：セット値引き表!I3

設問2

セット値引き適用処理に関する次の説明及びマクロ中の　に入れる正しい答えを，解答群の中から選べ。

〔セット値引き適用処理の説明〕
　"セット値引き表"に記載されているセットに対して，セット番号が小さいものから順にセット化して，値引きを適用していく。

セットを構成する商品の商品番号を表示するために，ワークシート"セット値引き表"のセル K3 に次の式を入力し，セル K4～K12 に複写する。
　　照合検索(C3，c，d)
　同様の方法で，セル L3～L12 とセル M3～M12 にも，セットを構成する商品の商品番号を表示するための式を入力する。
ワークシート"購入伝票"にマクロ Calc_Discount_Price を格納する。

〔マクロの説明〕
　マクロ Calc_Discount_Price は，次の手順で処理を行う。

購入する各商品の数量を求め，配列 Goods_Num に格納する。商品番号が i(i＝1，2，…，50) の商品の数量は，Goods_Num[i－1] に格納される。
セット番号が1，2，…，10の順に，セット値引きの対象となる数量を求めるために(3)～(5)の処理を繰り返す。
配列 Goods_Num について，セットを構成する3種類の商品の数量を調べ，セットにできる最大の数量 Set_Num を求める。
数量 Set_Num が0でない場合は，ワークシート"購入伝票"のセットの数量のセルに数量 Set_Num を格納し，セット値引きの対象とした3種類の商品の数量を，配列 Goods_Num から減らす。
数量 Set_Num が0の場合は，ワークシート"購入伝票"のセットの数量のセルに空値 null を格納する。

c，d に関する解答群

価格表!A$2:A$51
価格表!B$2:B$51
価格表!C$2:C$51
価格表!D$2:D$51

e に関する解答群

価格表!A2
価格表!B2
価格表!C2
相対(価格表!A2，I，0)
相対(価格表!B2，I，0)
相対(価格表!C2，I，0)

f に関する解答群

Num ＜ Set_Num
Num ＞ Set_Num
Num ＝ 0
Num ≠ 0
Num ≠ Set_Num
Set_Num ＝ 0
Set_Num ≠ 0

g に関する解答群

＋ 1
＋ Set_Num
－ 1
－ Set_Num
＊ Set_Num
／ Set_Num

解答選択欄

解答

c=ウ
d=ア
e=オ
f=ア
g=オ

解説

〔c、dについて〕
照合検索は3つの引数を指定します。

照合検索(式，検索のセル範囲，抽出のセル範囲)

この関数は、1行または1列の検索のセル範囲に対して指定条件を満たすセルを探します。そして、抽出のセル範囲の中から、見つかったセルの検索のセル範囲の中での位置と同じ位置にあるセルの値を返します。

ワークシート"セット値引き表"のK列に表示するのは商品1の商品番号なので、セル K3～K12 にはC列（商品コード）から商品番号を得るための式を入れなければなりません。第1引数はC3（商品コード）になっているので、検索のセル範囲は商品コードを保持する1行または1列ということになります。ワークシート"価格表"では、商品コードがC列に入力されているので検索のセル範囲は「C2:C51」が適切です。
そして、取得したい商品番号は見つかった行のA列に入力されているので、抽出のセル範囲を A2:A51 とすれば商品コードに対応する商品番号を取得できます。なお、縦方向に複写するのでセル範囲が固定となるように行位置は絶対参照にします。

∴c＝ウ：価格表!C$2:C$51
　d＝ア：価格表!A$2:A$51

〔eについて〕
マクロ Calc_Discount_Price の説明中の(1)の処理に該当し、カウンタ変数Iを用いて、配列 Goods_Num[I] に各商品の購入数量を格納します。

[e]の値は変数 Name に代入されていますが、この変数 Name は次行の条件付合計の中でセル B7～B26 の中から条件に合致するセルを特定するために使用されています。セル B7～B26 は商品名を保持するセルですので、Name には商品名を入れておかなければなりません。よって、価格表のB列を参照する式が適切となります。カウンタ変数Iが1増えるごとに参照先の行を1つ下にしたいので、価格表のセル B2 を基準に相対とIを使って「相対(価格表!B2，I，0)」と指定することになります。Iを0から49まで増やすことで、価格表のB2からB51の値を Goods_Num[0]～[49] に格納できます。

「ア」「イ」「ウ」は、配列 Goods_Num の全要素に同じ値を代入することになるので誤り、「エ」は商品番号が格納されるので誤り、「カ」は商品コードが格納されるので誤りです。

∴e＝オ：相対(価格表!B2，I，0)

〔fについて〕
マクロの以下の部分は、〔マクロの説明〕(3)の「セットを構成する3種類の商品の数量を調べ，セットにできる最大の数量 Set_Num を求める」処理に該当します。

最初の2行で商品1の購入数量からセットできる最大の数量を求め、内側のループ内では商品2および商品3について同様の処理を行ってセットできる最大の数量を求めています。命令文の要素を分解してみると、

相対(セット値引き表!K3, I, 0): 現在のセットにおける商品1の商品番号
Goods_Num[相対(セット値引き表!K3, I, 0) - 1]: 商品1の購入個数
相対(セット値引き表!D3, I, 0) - 1]: 現在のセットにおける商品1の構成数量

ですので、購入数をセットの構成数量で割っていることになります。例えば、購入数が3で商品1の必要数が1だった場合には「3÷1＝3」で Set_Num は3になります。内側のループでは商品2と商品3について同様の処理を行っています。

ここで、

商品1 … 3セット分
商品2 … 2セット分
商品3 … 1セット分

が購入されていた場合を考えると、作成できるセット数は商品3に合わせて1セットになります。つまり、商品2および商品3で算出された Num が Set_Num よりも小さい場合には、Set_Num を Num に合わせて小さくしなければなりません。[f]の条件式が真になるときには Set_Num を Num で更新しているので、条件式としてはNum が Set_Num よりも小さい場合を示す「Num ＜ Set_Num」が入ります。

∴f＝ア：Num ＜ Set_Num

〔gについて〕
マクロの以下の部分は、〔マクロの説明〕(4)のうち「セット値引きの対象とした3種類の商品の数量を，配列 Goods_Num から減らす」処理に該当します。

上記のプログラムの

Goods_Num[相対(セット値引表!K3，I，J) - 1]

は2つとも同じなので、配列 Goods_Num の値を

相対(セット値引表!D3，I，J ＊ 2) g

を減らした値で更新する処理であることがわかります。Jを0から2まで増やしたとき、上記式はセット値引表の以下のセルを示します。

配列 Goods_Num から減らす数量は「セットの構成数量×作成できたセット数」なので、(3)の処理で求めた Set_Num を各商品の構成数量に乗じた数だけ減らしてやれば良いことになります。

∴g＝オ：＊ Set_Num

平成27年秋期午後問題一覧

問1　問2　問3　問4　問5　問6　問7　問8　問9　問10　問11　問12　問13　採点講評