基本情報技術者 2017年 春期 午後 問13
次の表計算のワークシート及びマクロの説明を読んで、設問1〜3に答えよ。
〔表計算の説明〕
T社のUさんは、3種類の商品X、Y、Zを納品書に基づいて梱包し、宅配便業者に集荷を依頼して発送する業務を担当している。従来、発送する商品をまとめて荷物として梱包した後に、個別に重量を量って料金計算をしていたが、表計算ソフトを用いて作業の効率化を図ることにした。
〔ワークシート：料金表〕
商品の発送に利用する宅配便サービスの発送料金は、料金計算上同一地域とする都道府県の区分である着地域区分と、荷物の重量で区分された重量区分に基づいて決定される。ワークシート“料金表”には、T社の所在地からの発送料金を検索するために利用する“料金表”が格納されている。ワークシート“料金表”の例を、図1に示す。
(1) セルC2〜N2には着地域区分の名称が、セルC3〜N10には、それぞれの列の行2の着地域区分に属する都道府県名が、上のセルから順に入力されている。全ての都道府県はいずれか一つの着地域区分に必ず属する。
(2) セルB11〜B15には、重量区分（1〜5）が入力されている。
(3) セルC11〜N15には、着地域区分と重量区分に対応する発送料金が入力されている。
(4) セルC16〜N16は、料金計算で着地域区分を検索するための作業領域である。
〔ワークシート：商品重量〕
ワークシート“商品重量”には、料金計算で商品の重量を検索するときに利用する“商品重量表”が格納されている。セルA3〜A5には商品名が、セルB3〜B5には各商品1個の重量が入力されている。ワークシート“商品重量”を、図2に示す。
〔ワークシート：重量区分〕
ワークシート“重量区分”には、荷物の重量から重量区分を検索するときに利用する“重量区分表”が格納されている。ワークシート“重量区分”を、図3に示す。
(1) セルA4〜A8には重量区分が、セルB4〜B8には各重量区分で発送できる荷物の重量の上限が入力されている。この宅配便サービスで発送できる荷物の重量は30,000gまでである。
(2) 商品の梱包には、重量区分に応じた梱包用の箱を用いる。セルC4〜C8には、各重量区分で用いる梱包用の箱の重量が入力されている。
(3) セル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：イ
解説
解答の導き方
この設問では、発送先の都道府県名が、料金表のどの着地域区分に属するかを調べる式を選びます。
-
まず、式全体の外側にあるIFを確認します。発送先の入力欄が空なら、結果も空にする仕組みです。式の 料金計算!$A4 = null は、「料金計算」シートのセルA4が空かどうかを調べています。空なら null を表示し、空でなければ a の判定に進みます。
-
a では、都道府県名が現在の列にあるかを調べます。条件付個数は、指定した範囲のうち条件に合うセルの個数を数える機能です。たとえばセルC16では、セルC3〜C10の中に「料金計算」シートのセルA4と一致する名前が何個あるかを数えます。
-
問題文には「全ての都道府県はいずれか一つの着地域区分に必ず属する」とあります。そのため、調べている列に発送先の都道府県名があれば一致数は1、なければ0です。一致数が1なら 1 を表示し、0なら null を表示する式が必要です。
-
この条件に合う式は、条件付個数が1のときに1を返し、それ以外のときに空値を返すものです。IF(条件付個数(C3:C10,= 料金計算!$A4) = 1,1,null)
-
セルC16の式はセルD16〜N16に複写されます。範囲の列を固定していないため、複写するとC3:C10はD3:D10、E3:E10のように変わります。これにより、それぞれの列に対応する着地域区分を調べられます。一方、料金計算!$A4 は列Aが固定されているため、複写しても発送先の入力欄を参照し続けます。
以上から、aに入るのはイの式です。
誤りやすいポイント
- アは、一致数が0のときに1を表示し、一致があるときに空値を表示します。必要な表示と逆です。
- ウは、一致数が2以上の場合だけ空値にします。一致数が0のときも1を表示してしまうため、発送先がその列にない場合の表示が誤りです。
- エは、C列を固定しています。式をD16〜N16に複写してもC3:C10を調べ続けるため、各列の着地域区分を判定できません。
- オは、一致数が1のときに空値を返し、それ以外のときに1を返します。必要な表示と逆です。
- カは、一致数が2以上のときに1を返します。問題文の条件では一致数は0か1なので、正しく判定できません。
FAQ
Q: 一致数が0より大きいかではなく、1と等しいかを調べるのはなぜですか？
A: 都道府県は一つの着地域区分にだけ属するため、一致数が1ならその列に発送先があります。一致数が2以上になることはありません。
A: 都道府県は一つの着地域区分にだけ属するため、一致数が1ならその列に発送先があります。一致数が2以上になることはありません。
Q: 料金計算!$A4 の列Aを固定する必要があるのはなぜですか？
A: 式を右方向に複写しても、発送先の入力欄である列Aを参照し続けるためです。列Aを固定しないと、複写先に応じて参照列がB、Cのようにずれてしまいます。
A: 式を右方向に複写しても、発送先の入力欄である列Aを参照し続けるためです。列Aを固定しないと、複写先に応じて参照列がB、Cのようにずれてしまいます。
Q: null は何を表していますか？
A: この設問では、セルに何も表示しないことを表します。発送先が空の場合や、その列に発送先の都道府県名がない場合に、余計な値を表示しないために使います。
A: この設問では、セルに何も表示しないことを表します。発送先が空の場合や、その列に発送先の都道府県名がない場合に、余計な値を表示しないために使います。
関連キーワード: IF関数、条件付き個数、相対参照、複合参照
設問2：
問題文を見る ワークシート“料金計算”に関する次の記述中のに入れる正しい答えを、解答群の中から選べ。
(1) セルE4には、発送する商品の総重量を表示する次の式を入力する。
B4 * 商品重量!B3 + C4 * 商品重量!B4 + D4 * 商品重量!B5
(2) セルF4には、セルE4の値が0のときは“−”を、セルE4の値が発送可能な商品の総重量の上限を超えるときは“×”を表示し、それ以外のときは、セルE4の商品総重量の商品を一つの荷物として発送できる最も小さい重量区分を、ワークシート“重量区分”から検索して表示する次の式を入力する。
IF(E4 = 0,'−',b)
(3) セルG4には、セルF4の値が“−”又は“×”のときは“−”を表示し、それ以外のときは、セルE4の商品総重量にセルF4の重量区分で利用する箱の重量を加えた値を梱包後重量として表示する式を入力する。
(4) セル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の値は、発送する商品の総重量です。ワークシート「重量区分」では、セルD4〜D8に各重量区分で発送できる商品の総重量の下限が、セルE4〜E8に上限が入力されています。
たとえば、セルD4〜D8の下限は、重量区分ごとに1、1,866、4,741、9,586、19,131です。商品総重量がこのどの下限以上かを調べれば、発送できる最も小さい重量区分を選べます。
照合一致の末尾に指定する1は、完全一致だけでなく、検索値以下の値のうち最も大きい値を探す指定です。そこで、昇順に並んだ下限のセルD4:D8を検索範囲にすれば、商品総重量に合う重量区分を見つけられます。
また、商品総重量がセルE8の上限を超える場合は、どの重量区分でも発送できないため、「×」を表示します。商品総重量が0の場合は、その前にあるIFで「−」を表示します。
したがって、bに入るのは、セルE8の上限を確認してから、セルD4:D8を検索する ア です。セルE4:E8は各重量区分の上限なので、下限を使って重量区分を探す目的には合いません。
c：料金を検索できない場合を判定する
IFは、最初の条件が真なら「−」を表示し、偽なら料金を検索する関数です。条件には、料金検索ができない場合をまとめて指定します。
料金検索ができないのは、次のいずれかの場合です。
- セルG4が「−」である
重量区分が「−」または「×」なら、梱包後重量を計算できないためです。 - 料金表の作業領域であるセルC16:N16の合計が0である
発送先の都道府県名に該当する着地域区分が見つからなかったことを表します。図1では、青森県に対応する列であるD16に1が入力されています。
この二つを「または」でつなぐ必要があります。したがって、cに入るのは、セルG4が「−」である場合、またはセルC16:N16の合計が0である場合を表す ウ です。
d：重量区分と着地域区分から料金を取り出す
料金表の料金が入力されている範囲は、セルC11:N15です。この範囲では、行が重量区分、列が着地域区分に対応しています。
表引きで料金を取り出すには、次の二つの位置を指定します。
- 行の位置：セルF4の重量区分
- 列の位置：セルC16:N16で「1」が見つかった位置
セルC16:N16の中から1を完全一致で探すと、該当する着地域区分が何列目か分かります。完全一致で探す指定が、照合一致の末尾にある0です。
たとえば、図4の例では、セルF4の重量区分は4です。また、青森県に対応する列はD列で、セルC16:N16の中では2番目です。そのため、料金表のセルC11:N15から、4行目・2列目にある料金を取り出します。この値は1,674円です。
よって、行の位置にセルF4、列の位置に着地域区分の検索結果を指定する ウ が適切です。
垂直照合を使う場合、検索範囲をセルB11:N15とすると、列番号はB列を1として数えます。一方、着地域区分の検索結果はセルC16:N16を基準に数えるため、列番号が1列ずれてしまいます。また、表引きの行位置と列位置を逆にした式も、正しい料金を取り出せません。
誤りやすいポイント
- 商品総重量を、重量区分の上限と照合しようとすること
下限の範囲で照合一致を使えば、「その重量以上を発送できる区分」のうち最も小さい区分を選べます。 - 発送できる荷物の上限を、梱包後重量の上限と混同すること
セルE8は、箱の重量を加える前の商品総重量の上限です。重量区分5の箱の重量は1,320gで、荷物全体の上限である30,000gから差し引くと、商品総重量の上限は28,680gになります。 - 表引きの行番号と列番号を逆にすること
料金表では、重量区分が行、着地域区分が列です。 - 検索結果の列番号が、どの範囲を基準にした番号かを見落とすこと
セルC16:N16の位置と、セルB11:N15の列番号は基準位置が異なります。
FAQ
Q: なぜ重量区分の上限である30,000gと商品総重量を直接比べないのですか？
A: 30,000gは箱を含む荷物全体の上限です。商品を入れる箱にも重量があるため、箱を加える前の商品総重量の上限は、重量区分ごとに異なります。重量区分5では、30,000gから箱の重量1,320gを引いた28,680gが上限です。
A: 30,000gは箱を含む荷物全体の上限です。商品を入れる箱にも重量があるため、箱を加える前の商品総重量の上限は、重量区分ごとに異なります。重量区分5では、30,000gから箱の重量1,320gを引いた28,680gが上限です。
Q: 照合一致の末尾に指定する1と0は、どう違いますか？
A: 1は、検索値以下の値のうち最も大きい値を探す近似照合に使います。0は、検索値と完全に一致する値を探すときに使います。この問題では、重量区分の下限を探すときは1、作業領域にある1を探すときは0を使います。
A: 1は、検索値以下の値のうち最も大きい値を探す近似照合に使います。0は、検索値と完全に一致する値を探すときに使います。この問題では、重量区分の下限を探すときは1、作業領域にある1を探すときは0を使います。
Q: 図4の例では、商品総重量と梱包後重量はどのように計算されますか？
A: 商品総重量は、gです。この重量に合う重量区分は4で、重量区分4の箱の重量870gを加えるため、梱包後重量はgです。
A: 商品総重量は、gです。この重量に合う重量区分は4で、重量区分4の箱の重量870gを加えるため、梱包後重量はgです。
関連キーワード: 表計算関数、近似照合、完全一致検索、二次元表検索、条件分岐
設問3：
問題文を見る ワークシート“料金計算”で、商品総重量が発送可能な商品の総重量の上限を超え、重量区分欄に“×”が表示されると、Uさんは、商品を発送可能な重量に収まるような組合せに分割して梱包し、発送しなければならない。この商品の組合せを求めるために、ワークシート“料金計算”に新たな表を追加するとともに、マクロPackage_countを作成して格納した。マクロPackage_count中のに入れる正しい答えを、解答群の中から選べ。
〔“梱包作業表”及び“梱包指示表”の追加〕
マクロPackage_countを実行すると、商品を分割して発送するときの商品の組合せを“梱包作業表”を利用して求め、その結果が“梱包指示表”に格納される。
“梱包作業表”及び“梱包指示表”を追加したワークシート“料金計算”の例を、図5に示す。
(1) セルB9〜D9には、まだ梱包の組合せが決定していない商品の数量（以下、残数量という）がマクロPackage_countによって格納される。
(2) セルE9には、まだ梱包の組合せが決定していない商品の総重量（以下、残重量という）を表示する式を入力する。
(3) セルF9には、セルE9の値が0のときは“−”を、セルE9の値が発送可能な商品の総重量の上限を超えるときは5を、それ以外のときは、セルE9の商品総重量の商品を一つの荷物として発送できる最も小さい重量区分を表示する式を入力する。
(4) セルG9には、セルE9の値が0のときは“−”を表示し、それ以外のときは、セルA4の発送先都道府県名とセルF9の重量区分に該当する発送料金を表示する式を入力する。
(5) セルB10〜D10は、マクロPackage_countが作業領域として利用し、それぞれ商品X、Y及びZの数量を格納する。また、セルE10には、セルB10〜D10に格納された数量の各商品の重量の合計（以下、作業重量という）を表示する式を入力する。
(6) 1枚の納品書で発送する荷物の数は10個を超えないものとし、セルA16〜G25には、発送可能な組合せに分割した結果を表示する。また、セルB26〜E26及びセルG26には、各列の行16〜25の値の合計を表示する式を入力する。
〔マクロ：Package_countの説明〕
マクロPackage_countでは、残重量が、発送可能な商品の総重量の上限を超える間は、その上限以下で最大の重量になるように荷物を作成し、残りを次の荷物に割り振る作業を繰り返すことによって組合せを求める。
(1) セルB4〜D4に入力されている数量を、セルB9〜D9に格納して残数量とする。また、荷物番号を数える変数package_noに初期値として1を設定する。
(2) セルE9の残重量が0になるまで、(3)〜(5)の処理を繰り返す。
(3) 行15から数えてpackage_noの値だけ下の行を、該当する荷物番号の荷物を表示する行（以下、表示行という）とし、表示行の列Aにpackage_no、列FにセルF9の重量区分、列GにセルG9の発送料金を複写する。また、梱包する商品総重量の最大値を保存する変数work_weightに、初期値として0を設定する。
(4) セルB10〜D10の数量を、それぞれ0から対応する商品の残数量まで順に変化させて、それぞれの数量の組合せで求めた作業重量の中で、値が一つの荷物として発送可能な商品の総重量の上限以下で最大となるものを表示行の列Eに、そのときの商品X、Y及びZの数量を表示行の列B〜Dに格納する。
(5) 商品の全ての組合せの比較が終了したとき、表示行の列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：エ
解説
解答の導き方
このマクロでは、まだ梱包していない商品の数量から、1個の荷物に入れる組合せを決めます。設問では、商品の組合せを選ぶ条件と、選んだ数量を残数量から差し引く処理を考えます。
1. 荷物に入れる組合せは、発送可能な範囲で最も重いものを選びます
マクロは、セルB10〜D10の数量を変えながら、商品の組合せを一つずつ調べます。商品重量表によると、商品Xは460g、商品Yは720g、商品Zは1,200gです。セルE10には、調べている組合せの商品の総重量が表示されます。
このとき、work_weightには、これまでに見つけた「発送可能な組合せの中で最も重い商品の総重量」を保存します。新しい組合せを採用するには、次の2つの条件を両方満たす必要があります。
- 新しい組合せの総重量E10が、これまでの最大値work_weightより大きいこと
- 新しい組合せの総重量E10が、発送可能な商品の総重量の上限以下であること
図3では、重量区分5で発送可能な商品の総重量の上限がセルE8に示されています。上限は28,680gです。梱包用の箱の重量1,320gを加えると、荷物の重量は30,000gになります。
したがって、条件は「work_weight < E10」かつ「E10 ≦ 重量区分!E8」です。これに当てはまるイは、組合せを更新する条件として適切です。条件を満たしたとき、その数量と総重量を記録します。
図5の例では、最初の荷物に入れる数量は商品Xが6、商品Yが1、商品Zが21です。商品の総重量は、次のとおり上限の28,680gになります。
このように、商品の全組合せを調べ、上限を超えない範囲で最も重い組合せを選びます。
2. 選んだ数量を残数量から差し引きます
組合せの比較が終わると、表示行の列B〜Dには、その荷物に梱包する商品X、Y及びZの数量が入っています。次の荷物を作るためには、セルB9〜D9の残数量から、今回の荷物に入れた数量を差し引く必要があります。
たとえば、iが0のときは商品Xの残数量を更新します。相対(B9,0,i)は残数量のセルを、相対(B15,package_no,i)は今回の荷物の数量を記録したセルを表します。したがって、更新する式は「残数量 − 今回の荷物に入れた数量」です。
ここで差し引くのは、商品をすべて組み合わせて調べ終えた後に記録された数量です。調査中の組合せを入れるセルB10〜D10ではありません。そのため、正しい選択肢はエです。
図5では、最初の荷物に商品Xを6、商品Yを1、商品Zを21入れた後、残数量は商品Xが4、商品Yが35、商品Zが9になります。そこから次の荷物の組合せを決める処理を繰り返します。
誤りやすいポイント
-
上限を30,000gと考えてしまうこと
30,000gは、箱を含む荷物全体の上限です。E10で比べるのは商品の総重量なので、箱の重量1,320gを差し引いた28,680gが上限になります。 -
work_weightとE9を比べてしまうこと
E9は、まだ梱包していない商品の総重量です。組合せを選ぶときに比較するのは、これまでに見つけた最大値work_weightと、調査中の組合せの重量E10です。 -
残数量からB10〜D10の値を差し引くこと
B10〜D10には、組合せを調べている途中の値が入ります。最終的に採用した数量は、表示行の列B〜Dに記録されています。残数量から差し引くのは、その記録済みの数量です。
FAQ
Q: なぜ商品の数量を0から順に変えて、すべての組合せを調べるのですか？
A: 商品ごとに重量が異なるため、単に数量の多い商品を優先するだけでは、上限以下で最も重い組合せになるとは限りません。組合せを一つずつ比較することで、条件に合う最大の重量を選べます。
A: 商品ごとに重量が異なるため、単に数量の多い商品を優先するだけでは、上限以下で最も重い組合せになるとは限りません。組合せを一つずつ比較することで、条件に合う最大の重量を選べます。
Q: 「work_weight < E10」は、なぜ「work_weight ≦ E10」ではないのですか？
A: 同じ重量の組合せが見つかっても、すでに記録した最大重量は変わりません。より重い場合だけ記録を更新する条件なので、「<」を使います。
A: 同じ重量の組合せが見つかっても、すでに記録した最大重量は変わりません。より重い場合だけ記録を更新する条件なので、「<」を使います。
Q: 荷物の料金は、なぜ残数量から決めずに荷物ごとに記録するのですか？
A: 荷物ごとに重量区分が異なる場合があるためです。選んだ荷物の重量区分と発送先から料金を求め、それぞれの荷物の料金を記録します。
A: 荷物ごとに重量区分が異なる場合があるためです。選んだ荷物の重量区分と発送先から料金を求め、それぞれの荷物の料金を記録します。
関連キーワード: 全探索、組合せ最適化、条件判定、最大値探索、残数量管理
