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

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

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

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

〔表計算の説明〕
　T社のUさんは，3種類の商品X，Y，Zを納品書に基づいて梱包し，宅配便業者に集荷を依頼して発送する業務を担当している。従来，発送する商品をまとめて荷物として梱包した後に，個別に重量を量って料金計算をしていたが，表計算ソフトを用いて作業の効率化を図ることにした。

〔ワークシート：料金表〕
　商品の発送に利用する宅配便サービスの発送料金は，料金計算上同一地域とする都道府県の区分である着地域区分と，荷物の重量で区分された重量区分に基づいて決定される。ワークシート"料金表"には，T社の所在地からの発送料金を検索するために利用する"料金表"が格納されている。ワークシート"料金表"の例を，図1に示す。

セル C2～N2 には着地域区分の名称が，セル C3～N10 には，それぞれの列の行2の着地域区分に属する都道府県名が，上のセルから順に入力されている。全ての都道府県はいずれか一つの着地域区分に必ず属する。
セル B11～B15 には，重量区分(1～5)が入力されている。
セル C11～N15 には，着地域区分と重量区分に対応する発送料金が入力されている。
セル C16～N16 は，料金計算で着地域区分を検索するための作業領域である。

〔ワークシート：商品重量〕
　ワークシート"商品重量"には，料金計算で商品の重量を検索するときに利用する"商品重量表"が格納されている。セル A3～A5 には商品名が，セル B3～B5 には各商品1個の重量が入力されている。ワークシート"商品重量"を，図2に示す。

〔ワークシート：重量区分〕
　ワークシート"重量区分"には，荷物の重量から重量区分を検索するときに利用する"重量区分表"が格納されている。ワークシート"重量区分"を，図3に示す。

セル A4～A8 には重量区分が，セル B4～B8 には各重量区分で発送できる荷物の重量の上限が入力されている。この宅配便サービスで発送できる荷物の重量は30,000gまでである。
商品の梱包には，重量区分に応じた梱包用の箱を用いる。セル C4～C8 には，各重量区分で用いる梱包用の箱の重量が入力されている。
セル D4～E8 には，箱の重量を加えたときに，当該重量区分で発送可能な商品の総重量の範囲が入力されている。

〔ワークシート：料金計算〕
　ワークシート"料金計算"には，"料金計算表"を作成して格納する。セル A4 に発送先の都道府県名，セル B4～D4 に発送する商品X，Y及びZの数量を入力すると，商品総重量，重量区分，梱包後重量及び発送料金を表示する。ワークシート"料金計算"の例を，図4に示す。

設問1

ワークシート"料金表"に関する次の記述中の　に入れる正しい答えを，解答群の中から選べ。

　セル C16 には，セル C3～C10 の中に，ワークシート"料金計算"のセル A4 に入力された発送先都道府県名と一致するものがあるときには1を，そうでなければ空値を表示する次の式を入力し，セル D16～N16 に複写する。

　　IF(料金計算!$A4＝null，null,a)

a に関する解答群

IF(条件付個数(C3:C10,＝料金計算!$A4)＝0,1,null)
IF(条件付個数(C3:C10,＝料金計算!$A4)＝1,1,null)
IF(条件付個数(C3:C10,＝料金計算!$A4)＞1,null,1)
IF(条件付個数($C3:$C10,＝料金計算!$A4)＝0,null,1)
IF(条件付個数($C3:$C10,＝料金計算!$A4)＝1,null,1)
IF(条件付個数($C3:$C10,＝料金計算!$A4)＞1,1,null)

解答選択欄

解答

a=イ

解説

〔aについて〕
ワークシート"料金計算"のA4(発送先都道府県名)が空値(NULL)の場合には、結果値を空値にするようにIF文が構成されています。

IF 料金計算!$A4が空値
　真の場合：何も表示しない(空値を表示)。
　偽の場合：処理aを行う。

選択肢の中では"条件付個数"が使われており、各列の都道府県名の中にA4の都道府県名が含まれているか否かによって、1またはnullの結果を返すようになっています。このためIF文での分岐処理が必要です。条件付個数の結果が0であれば、その列には該当の都道府県は存在しないのでnull、0以外であればその列に該当の都道府県が存在するので1を表示することとなります。これを実現するIF文は以下の2通りです。

IF 条件付個数(…) = 0
　真の場合：null
　偽の場合：1

または、

IF 条件付個数(…) = 1
　真の場合：1
　偽の場合：null

また、C16の式はD16～N16に複写されるので、複写先セルごとに各列を検索範囲とするには"条件付個数"の第1引数を C3:C10 と相対参照で指定しなくてはなりません。

"条件付個数"の結果が0のときにnullを返し、0以外のときに1を返す
"条件付個数"の検索範囲が相対参照なっている

以上の2点をともに満たしている式は「イ」です。

「エ」「オ」「カ」は、"条件付個数"の検索範囲が絶対参照になっているので複写先セルで上手く動作しません。
「ア」は、一致する都道府県名がないときに1を、存在すれば0を表示するので誤りです。
「ウ」は、"条件付個数"の結果は0又は1に限られ、1より大きくなることはありません。全列に1が表示されることとなるので誤りです。

設問2

ワークシート"料金計算"に関する次の記述中の　に入れる正しい答えを，解答群の中から選べ。

セル E4 には，発送する商品の総重量を表示する次の式を入力する。
　　B4＊商品重量!B3＋C4＊商品重量!B4＋D4＊商品重量!B5
セル F4 には，セル E4 の値が0のときは"－"を，セル E4 の値が発送可能な商品の総重量の上限を超えるときは"×"を表示し，それ以外のときは，セル E4 の商品総重量の商品を一つの荷物として発送できる最も小さい重量区分を，ワークシート"重量区分"から検索して表示する次の式を入力する。
　　IF(E4＝0,'－',b)
セル G4 には，セル F4 の値が"－"又は"×"のときは"－"を表示し，それ以外のときは，セル E4 の商品総重量にセル F4 の重量区分で利用する箱の重量を加えた値を梱包後重量として表示する式を入力する。
セル H4 には，セル A4～D4 に入力した条件でワークシート"料金表"から発送料金の検索ができないときは"－"を表示し，それ以外のときは，セル A4 の発送先都道府県名とセル F4 の重量区分に該当する発送料金をワークシート"料金表"から検索して表示する次の式を入力する。
　　IF(c,'－',d)

b に関する解答群

IF(E4＞重量区分!E8,'×',照合一致(E4,重量区分!D4:D8,1))
IF(E4＞重量区分!E8,'×',照合一致(E4,重量区分!D4:D8,－1))
IF(E4＞重量区分!E8,'×',照合一致(E4,重量区分!E4:E8,1))
IF(E4＞重量区分!E8,'×',照合一致(E4,重量区分!E4:E8,－1))

c に関する解答群

論理積(G4≠'－',A4≠null)
論理積(論理和(F4＝'－',F4＝'×'),A4＝null)
論理和(G4＝'－',合計(料金表!C16:N16)＝0)
論理和(論理積(F4≠'－',F4≠'×'),合計(料金表!C16:N16)＝1)

d に関する解答群

垂直照合(F4,料金表!B11:N15,照合一致(1,料金表!C16:N16,0),0)
垂直照合(照合一致(1,料金表!C16:N16,0),料金表!B11:N15,F4,0)
表引き(料金表!C11:N15,F4,照合一致(1,料金表!C16:N16,0))
表引き(料金表!C11:N15,照合一致(1,料金表!C16:N16,0),F4)

解答選択欄

解答

b=ア
c=ウ
d=ウ

解説

〔bについて〕
セルE4の値が0のときは"－"を表示する式があらかじめ用意されているので、ここではE4の値が0以外だった場合に実行する式bを選択します。

IF(E4＝0,'－',b)

bには、セルE4の値が発送可能な商品の総重量の上限を超えるときは"×"を表示、それ以外のときは、E4の商品総重量の商品を一つの荷物として発送できる最も小さい重量区分を、ワークシート"重量区分"から検索して表示する式を入力する必要があります。

各選択肢で異なっているのは、"照合一致"の第2引数(セル範囲)と第3引数(検索の指定)です。"照合一致"の第3引数は指定する数により検索方法が変わります。

0の場合: 式(第1引数)と一致する値を検索する
1の場合: 式の値以下の最大値を検索する
セル範囲の値は昇順に整列されている必要がある
-1の場合: 式の値以上の最小値を検索する
セル範囲の値は降順に整列されている必要がある

ワークシート"重量区分"の総重量の範囲が上端から昇順に整列していることを考えれば、第3引数に指定するのは1となります。「イ」「エ」は、セル範囲のデータが降順になっていないのに第3引数に-1を指定しているので期待通りに動作しません。

残った「ア」と「ウ」で動作を比較すると、「ア」はD列で商品総重量の値以下の最大値であるD7にマッチするのに対し、「ウ」ではE6にマッチすることになります。"照合検索"はセル範囲の左上端から数えた位置を返すため、上端から4セル目であるD7にマッチする「ア」では4を、「ウ」では3を返すことになります。表示されるべき重量区分は4であるため「ア」の式の動作が適切です。

〔cについて〕
IF文の条件式が入ります。真のときに"－"を表示するので、「発送料金の検索ができないとき」に真となるものを選択する必要があります。発送料金の検索ができないときとは、次のような場合です。

G4の値が"－"の場合（F4の値が"－"又は"×"の場合）
→ G4='－'（又は 論理和(F4='－', F4='×')）の場合
A4の値が入力されていない
→ A4=null の場合
A4の値に誤りがあり、ワークシート"料金表"上に合致する都道府県名がない
→ ワークシート"料金表"C16:N16に1となっているセルがない

したがって、G4の値が"－"、または、ワークシート:料金表のC16～N16の値が全て0の場合に結果が真となる「ウ」の式が正解です。

G4が"－"であっても、A4に発送先都道府県名が表示されていれば偽となってしまうため誤りです。
発送料金の検索ができない場合を論理積で囲んでいるため誤りです。
正しい。
「論理積(F4≠'－', F4≠'×')」と「合計(料金表!C16:N16)=1」はともに検索可能な状況を示す式です。どちらか一方が真であれば論理和の結果も真となってしまうため誤りです。

〔dについて〕
セルA4(発送先都道府県名)とセルF4(重量区分)に該当する発送料金をワークシート"料金表"から検索して表示する式を選択します。

各式の動作を確認してみましょう。

"料金表"B11～B15を上端から走査し、F4の値が最初に現れる行を探します。その行に対して、セル範囲の左端から数えて第3引数で与えられた数値分だけ右に位置するセルの値を返します。
第3引数は"照合一致"の結果であり、C16～N16の中で1が表示されているセルの相対な列位置を返します。
これを実行すると、B列から数えたセル位置を指定すべきところにC列から数えた列位置(照合一致の結果)を指定することとなるため、参照するセルが1列分左にずれます。したがって誤りです。
検索値として、C16～N16の中で1が表示されているセルの相対な列位置を指定しています。着地域区分の列位置と重量区分の行位置は無関係なので誤りです。
正しい。C11～N15をセル範囲として、「F4」の値で行位置を、「照合一致(1, 料金表!C16:N16, 0)」で列位置を指定しています。
表引きは、第2引数に行位置を、第3引数に列位置を指定します。この式では行位置と列位置の指定が逆です。

設問3

ワークシート"料金計算"で，商品総重量が発送可能な商品の総重量の上限を超え，重量区分欄に"×"が表示されると，Uさんは，商品を発送可能な重量に収まるような組合せに分割して梱包し，発送しなければならない。この商品の組合せを求めるために，ワークシート"料金計算"に新たな表を追加するとともに，マクロ Package_count を作成して格納した。マクロ Package_count 中の　に入れる正しい答えを，解答群の中から選べ。

〔"梱包作業表"及び"梱包指示表"の追加〕
　マクロ Package_count を実行すると，商品を分割して発送するときの商品の組合せを"梱包作業表"を利用して求め，その結果が"梱包指示表"に格納される。"相包作業表"及び"相包指示表"を追加したワークシート"料金計算"の例を，図5に示す。

セル B9～D9 には，まだ梱包の組合せが決定していない商品の数量(以下，残数量という)がマクロ Package_count によって格納される。
セル E9 には，まだ梱包の組合せが決定していない商品の総重量(以下，残重量という)を表示する式を入力する。
セル F9 には，セル E9 の値が0のときは"－"を，セル E9 の値が発送可能な商品の総重量の上限を超えるときは5を，それ以外のときは，セル E9 の商品総重量の商品を一つの荷物として発送できる最も小さい重量区分を表示する式を入力する。
セル G9 には，セル E9 の値が0のときは"－"を表示し，それ以外のときは，セル A4 の発送先都道府県名とセル F9 の重量区分に該当する発送料金を表示する式を入力する。
セル B10～D10 は，マクロ Package_count が作業領域として利用し，それぞれ商品X，Y及びZの数量を格納する。また，セル E10 には，セル B10～D10 に格納された数量の各商品の重量の合計(以下，作業重量という)を表示する式を入力する。
1枚の納品書で発送する荷物の数は10個を超えないものとし，セル A16～G25 には，発送可能な組合せに分割した結果を表示する。また，セル B26～E26 及びセル G26 には，各列の行16～25の値の合計を表示する式を入力する。

〔マクロ：Package_countの説明〕
　マクロ Package_count では，残重量が，発送可能な商品の総重量の上限を超える間は，その上限以下で最大の重量になるように荷物を作成し，残りを次の荷物に割り振る作業を繰り返すことによって組合せを求める。

セル B4～D4 に入力されている数量を，セル B9～D9 に格納して残数量とする。また，荷物番号を数える変数 package_no に初期値として1を設定する。
セル E9 の残重量が0になるまで，(3)～(5)の処理を繰り返す。
行15から数えて package_no の値だけ下の行を，該当する荷物番号の荷物を表示する行(以下，表示行という)とし，表示行の列Aに package_no，列Fにセル F9 の重量区分，列Gにセル G9 の発送料金を複写する。また，梱包する商品総重量の最大値を保存する変数 work_weight に，初期値として0を設定する。
セル B10～D10 の数量を，それぞれ0から対応する商品の残数量まで順に変化させて，それぞれの数量の組合せで求めた作業重量の中で，値が一つの荷物として発送可能な商品の総重量の上限以下で最大となるものを表示行の列Eに，そのときの商品X，Y及びZの数量を表示行の列B～Dに格納する。
商品の全ての組合せの比較が終了したとき，表示行の列B～Dに格納されている数量を，当該荷物番号の荷物に梱包する商品の数量として確定し，各商品の残数量から差し引き，package_no に1を加え，(2)に戻る。

e に関する解答群

論理積(work_weight＜E9，E10＞重量区分!E8)
論理積(work_weight＜E10，E10≦重量区分!E8)
論理積(work_weight＞E9，E10＞重量区分!E8)
論理積(work_weight＞E10，E10≦重量区分!E8)

f に関する解答群

相対(B9,0,i) ← 相対(B4,0,i)－相対(B10,0,i)
相対(B9,0,i) ← 相対(B4,0,i)－相対(B15,package_no,i)
相対(B9,0,i) ← 相対(B9,0,i)－相対(B10,0,i)
相対(B9,0,i) ← 相対(B9,0,i)－相対(B15,package_no,i)

解答選択欄

解答

e=イ
f=エ

解説

マクロ Package_count は、説明の(1)から(5)に対応した4つの部分で構成されています。

〔eについて〕
(4)の記述のうち、「値が一つの荷物として発送可能な商品の総重量の上限以下で最大となるものを…(略)に格納する」の部分が、ここでの処理の内容になります。プログラムを見ると、eが真の場合に B10～E10 の値を表示行（行15から数えてpackage_noの値だけ下の行）に代入し、変数 work_weight を E10 の値で更新しています。B10～D10 には各商品の数量が、E10にはその組合せの総重量が格納されています。work_weight は、商品総重量の最大値を保存する変数ですから、これを更新するのは以下の2つをともに満たすときとなります。

現在の work_weight の値よりも、E10の値が大きい
→ work_weight ＜ E10
E10の値が発送重量の上限値以下
→ E10 ≦ 重量区分!E8

したがって「イ」の式が適切です。

〔fについて〕
(5)の記述のうち、各商品の残数量から当該荷物番号の荷物に梱包する商品の数量を差し引く処理が入ります。ループ処理は、変数iを3回反復させながら実行するので、"相対"の第三引数(列位置)にiを指定すれば、i=0でB9を、i=1でC9を、i=2でD9への代入をそれぞれ処理できます。
現在の表示列の値は package_no に格納されているので、相対(B15, package_no, i)で参照できます。商品の残数量はB9～D9に格納されているので、相対(B9, 0, i)の値から相対(B15, package_no, i)の値を引いた数で残数量(B9～D9)を更新する処理が適切です。したがって「エ」が正解です。

「ア」「イ」は、残数量ではなく発送商品数量から差し引くので誤りです。これではいつまでたってもE10(残数量)が上限値以下になりません。
「ウ」は、B10～D10の値を差し引くので誤りです。B10～D10は作業領域であり、(4)のループ終了後にはX・Y・Zがすべて最大値となっているからです。

平成29年春期午後問題一覧

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