基本情報技術者 2019年 秋期 午後 問13
次の表計算のワークシート及びマクロの説明を読んで、設問1，2に答えよ。
Z組合では、収穫したメロンのうち1kg以上かつ3kg未満のものだけを組合で決めた3等級に分類して直接小売店に出荷している。出荷するメロンの等級と価格を決定する処理に、表計算ソフトを用いている。
〔メロンの等級〕
出荷するメロンは、形状及び表皮色をそれぞれ評価して“優”、“良”、“並”のいずれかに分類する。評価は“優”が最も高く、“並”が最も低い。形状と表皮色の評価のうち低い方が、そのメロンの等級となる。メロンの等級ごとに“キログラム当たり単価”が設定されている。
〔ワークシート：単価表〕
キログラム当たり単価が格納されたワークシート“単価表”を、図1に示す。
(1) 列Aは、見出し列である。
(2) セルB1〜D1には、等級として、順に“優”、“良”、“並”を入力する。
(3) セルB2〜D2には、各等級のキログラム当たり単価を入力する。
〔ワークシート：集計表〕
1回の出荷ごとに、出荷する個々のメロンに対して一意となるIDを付与する。1回の出荷数は1,000個以内である。各メロンの等級と価格（価格は、等級が“優”と“良”のものだけ）を決定するワークシート“集計表”の例を、図2に示す。
(1) 行1は見出し行である。出荷するメロンのデータは、行2以降にそれぞれ1行で入力する。
(2) データの最終行よりも下の行の列A〜Dの各セルには空値が入力されている。
(3) 列Aには、付与したIDを入力する。
(4) 列Bには、メロンの重量をkg単位で小数第3位まで入力する。
(5) 列Cと列Dには、メロンの形状と表皮色の評価をそれぞれ入力する。
(6) セルE2には、セルC2及びD2の評価に基づいて、メロンの等級を表示する次の式を入力し、セルE3〜E1001に複写する。
IF(A2=null, null, a)
(7) セルF2には、次の式を入力し、セルF3〜F1001に複写する。この式は、等級が“並”のときは“－”を表示し、それ以外のときは、メロンの算出価格を表示する。算出価格は、ワークシート“単価表”を参照して、メロンのキログラム当たり単価に重量を掛けた値である。
IF(A2=null, null, IF(E2='並', '－', b * B2))
(8) セルG2には、次の式を入力し、セルG3～G1001に複写する。この式は、等級が“並”のときは“－”を表示し、それ以外のときは、メロンの販売価格を表示する。販売価格は、算出価格を50円単位で切り上げた値である。
IF(A2=null, null, IF(E2='並', '－', c))
設問1：
問題文を見る ワークシート“集計表”の説明文中のに入れる正しい答えを、解答群の中から選べ。
aに関する解答群
ア：IF(論理積(C2='並', D2='並'), '並', IF(論理積(C2='良', D2='良'), '良', '優'))
イ：IF(論理積(C2='優', D2='優'), '優', IF(論理積(C2='並', D2='並'), '並', '良'))
ウ：IF(論理積(C2='優', D2='優'), '優', IF(論理積(C2='良', D2='良'), '良', '並'))
エ：IF(論理和(C2='並', D2='並'), '並', IF(論理和(C2='良', D2='良'), '良', '優'))
オ：IF(論理和(C2='優', D2='優'), '優', IF(論理和(C2='並', D2='並'), '並', '良'))
カ：IF(論理和(C2='優', D2='優'), '優', IF(論理和(C2='良', D2='良'), '良', '並'))
bに関する解答群
ア：照合一致(E2, 単価表!$B2:$D2, 0)
イ：照合一致(E2, 単価表!B$2:D$2, 0)
ウ：水平照合(E2, 単価表!$B1:$D2, 2, 0)
エ：水平照合(E2, 単価表!B$1:D$2, 2, 0)
オ：表引き(単価表!$B1:$D2, 2, 1)
カ：表引き(単価表!B$1:D$2, 2, 1)
cに関する解答群
ア：切上げ(F2 * 2, 0) ／ 2
イ：切上げ(F2 ／ 2, 0) * 2
ウ：切上げ(F2 ／ 50, 0) * 50
エ：四捨五入(F2, -2)
オ：四捨五入(F2 + 50, -2) - 50
カ：四捨五入(F2 ／ 50, 0) * 50
模範解答
a：エ、b：エ、c：ウ
解説
解答の導き方
この問題では、メロンの等級を決める式、等級から単価を調べる式、販売価格を50円単位で切り上げる式を考えます。
a：形状と表皮色から等級を決める
本文には、形状と表皮色の評価のうち「低い方が、そのメロンの等級」とあります。評価の順は「優」が最も高く、「並」が最も低いので、次の順で判定すればよいです。
- どちらか一方でも「並」なら、等級は「並」です。
- 「並」がなく、どちらか一方でも「良」なら、等級は「良」です。
- それ以外は、両方とも「優」なので、等級は「優」です。
「どちらか一方でも」に当たるのは、論理和です。論理和は、複数の条件のうち一つでも成り立てば真になります。
したがって、まず「C2='並'」または「D2='並'」を調べ、次に「C2='良'」または「D2='良'」を調べるエが適切です。例えば、形状が「良」で表皮色が「優」の場合は、最初の条件には当てはまらず、次の条件に当てはまるため、等級は「良」になります。
b：等級に対応するキログラム当たり単価を調べる
図1では、等級が横方向に並び、その下の行に各等級の単価が並んでいます。つまり、等級を横に探し、その等級と同じ列にある2行目の単価を取り出します。
水平照合は、表の先頭行を横方向に探し、指定した行から値を取り出す関数です。ここでは、検索する値はE2、検索範囲は単価表のB1からD2、取り出す行は範囲内の2行目、検索方法は完全一致です。そのため、水平照合(E2, 単価表!B$1:D$2, 2, 0)を使うエが当てはまります。
最後の0は、等級が完全に一致するものを探す指定です。また、B$1:D$2の「$」は、行番号を固定する記号です。この式を下の行へ複写しても、単価表の見出しと単価の行がずれません。E2は複写先に応じてE3、E4と変わり、それぞれのメロンの等級を検索します。
例えば等級が「優」なら、図1の単価表から1,500を取り出します。その単価に重量B2を掛けることで、算出価格になります。
c：算出価格を50円単位で切り上げる
50円単位で切り上げるには、まず算出価格を50で割り、その結果を整数に切り上げてから、再び50を掛けます。したがって、切上げ(F2 ／ 50, 0) * 50とするウが適切です。切上げの「0」は、小数点以下を残さず整数に切り上げる指定です。
図2の例では、F2の算出価格は2,431.5です。これを50で割ると48.63です。これを整数に切り上げると49になり、50を掛けると2,450になります。よって、販売価格は2,450です。
誤りやすいポイント
- 形状と表皮色の評価が一致したときだけ等級を決めるわけではありません。本文のルールは「低い方」なので、片方だけでも「並」なら等級は「並」です。
- 論理積は、複数の条件がすべて成り立つときに真になります。片方だけでも「並」や「良」なら該当する、という判定には論理和を使います。
- 単価表は等級が横に並んでいます。横方向に検索する表に対して、縦方向に検索する関数を選ばないようにします。
- 50円単位で切り上げる処理は、価格を直接50で割って整数に切り上げてから、50を掛けます。四捨五入では、切り上げにならない場合があります。
FAQ
Q: 等級が「並」の場合も、単価を調べて算出価格を出しますか？
A: いいえ。問題文の式では、等級が「並」のときは「－」を表示します。単価を調べて算出価格を出すのは、それ以外の等級の場合です。
A: いいえ。問題文の式では、等級が「並」のときは「－」を表示します。単価を調べて算出価格を出すのは、それ以外の等級の場合です。
Q: 水平照合の最後の0は何を指定していますか？
A: 等級が完全に一致するものを探す指定です。「優」に対して「良」の単価を返すような、近い値での検索を避けられます。
A: 等級が完全に一致するものを探す指定です。「優」に対して「良」の単価を返すような、近い値での検索を避けられます。
Q: 切上げ(F2 ／ 50, 0)のあとに50を掛けるのはなぜですか？
A: 50で割った値を整数に切り上げることで、次の50円単位の位置を求めます。その整数に50を掛けると、販売価格が50円単位になります。
A: 50で割った値を整数に切り上げることで、次の50円単位の位置を求めます。その整数に50を掛けると、販売価格が50円単位になります。
関連キーワード: 論理和、水平照合、絶対参照、切上げ、表計算関数
設問2：
問題文を見る 等級が“優”及び“良”のメロンは、1個ずつ箱に入れて梱包する。等級が“並”のメロンは、複数個を大箱に入れて梱包する。大箱の販売価格は、梱包したメロンの合計重量に、等級が“並”のキログラム当たり単価を掛けて、50円単位で切り上げた値である。等級が“並”のメロンを大箱に割り振るために、ワークシート“重量計算表”を作成し、マクロPackingを格納した。マクロPacking中のに入れる正しい答えを、解答群の中から選べ。
〔ワークシート：重量計算表〕
ワークシート“集計表”の行2以降のデータを上から順に参照し、メロンを大箱に割り振っていく。大箱には箱連番を付与する。一つの大箱には、出荷条件である“メロンの合計重量が5kg以上”又は“メロンの個数が4個”のどちらかを満たすまでメロンを割り振る。ワークシート“重量計算表”の例を、図3に示す。
行1は見出し行である。マクロPackingの実行前に、セルA2〜F1001には、空値が格納されている。セルG2〜G1001には、箱連番が示す大箱に割り振られたメロンの合計重量から販売価格を算出する式を入力しておく。この式は、合計重量を格納するセルが空値の場合は、空値を表示する。
マクロPackingは、処理(1)〜(4)を実行する。
(1) 列Aの行2以降には、1から順に箱連番を格納する。
(2) 列B〜Eの行2以降には、箱連番が示す大箱に割り振られたメロンのIDを格納する。割り振られたメロンの個数が4未満だった場合、IDを格納しなかったセルは空値のままとなる。
(3) 列Fの行2以降には、箱連番が示す大箱に割り振られたメロンの合計重量を格納する。
(4) 割り振った結果、出荷条件を満たさない大箱に関する情報は、表示しないようにする。
dに関する解答群
ア：相対(F1, i, 0) ← 相対(F1, i, 0) + 相対(集計表!B1, 1, 0)
イ：相対(F1, i, 0) ← 相対(F1, i, 0) + 相対(集計表!B1, i, 0)
ウ：相対(F1, i, 0) ← 相対(F1, i, 0) + 相対(集計表!B1, j, 0)
エ：相対(F1, i, 0) ← 相対(F1, j, 0) + 相対(集計表!B1, j, 0)
オ：相対(F1, j, 0) ← 相対(F1, i, 0) + 相対(集計表!B1, 0, 0)
カ：相対(F1, j, 0) ← 相対(F1, j, 0) + 相対(集計表!B1, 0, 0)
キ：相対(F1, j, 0) ← 相対(F1, j, 0) + 相対(集計表!B1, i, 0)
ク：相対(F1, j, 0) ← 相対(F1, j, 0) + 相対(集計表!B1, j, 0)
eに関する解答群
ア：論理積(相対(F1, j, 0) = 5, CurrentColumn = 4)
イ：論理積(相対(F1, j, 0) = 5, CurrentColumn ≧ 4)
ウ：論理積(相対(F1, j, 0) < 5, CurrentColumn = 4)
エ：論理積(相対(F1, j, 0) ≧ 5, CurrentColumn < 4)
オ：論理和(相対(F1, j, 0) = 5, CurrentColumn ≧ 4)
カ：論理和(相対(F1, j, 0) < 5, CurrentColumn = 4)
キ：論理和(相対(F1, j, 0) ≧ 5, CurrentColumn = 4)
ク：論理和(相対(F1, j, 0) ≧ 5, CurrentColumn < 4)
fに関する解答群
ア：相対(A1, j, 1) ← null
イ：相対(A1, j, k) ← null
ウ：相対(A1, j, k) ← 相対(A1, j, k) + 1
エ：相対(A1, k, 1) ← null
オ：相対(A1, k, j) ← null
カ：相対(A1, k, j) ← 相対(A1, k, j) + 1
模範解答
d：キ、e：キ、f：イ
解説
解答の導き方
このマクロでは、ワークシート“集計表”のデータを上から調べ、等級が“並”のメロンを大箱に割り振ります。確認するのは、空欄にするセルの範囲、箱を完成させる条件、重量の加算先の3点です。
まず、変数の役割を整理します。iは“集計表”で現在見ている行を表し、jは“重量計算表”の箱の行を表します。CurrentColumnは、現在の箱に何個のメロンを割り振ったかを表します。
このマクロの相対指定は、基準となるセルから行と列のずれを指定します。例えば、iが1のとき、相対(集計表!A1, i, 0)は“集計表”のA2です。同様に、jが1のとき、相対(A1, j, 0)は“重量計算表”のA2です。入力側の行を示すiと、出力側の行を示すjを区別することが大切です。
-
dでは、箱の合計重量を加算します。メロンのIDは相対(B1, j, CurrentColumn)に格納されます。割り振ったメロンの重量は、“集計表”の列Bから読み取ります。例えば、iが1なら、相対(集計表!B1, i, 0)は“集計表”のB2です。箱の合計重量を格納するセルは、出力側の行jにあるF列、つまり相対(F1, j, 0)です。したがって、箱の合計重量に、現在のメロンの重量を加える処理にします。相対(F1, j, 0) ← 相対(F1, j, 0) + 相対(集計表!B1, i, 0)これは解答群のキです。jは箱の行、iはメロンを読み取る行なので、足し合わせるセルの行を取り違えないようにします。
-
eでは、箱を完成させる条件を表します。問題文では、「メロンの合計重量が5kg以上」又は「メロンの個数が4個」のどちらかを満たすと、次の箱に進みます。CurrentColumnは、メロンを割り振るたびに1増えます。したがって、割り振り後のCurrentColumnは、その箱に入れた個数を表します。4個入ったときは、CurrentColumn = 4です。重量については、合計重量が5kgを超えることもあります。例えば図3では、3個のメロンを入れた箱の合計重量が5.728kgです。そのため、重量の条件は「5kgと等しい」ではなく「5kg以上」とします。条件は、重量が5kg以上又は個数が4個です。したがって、使うのは論理和であり、条件は「相対(F1, j, 0) ≧ 5」又は「CurrentColumn = 4」です。解答群ではキに当たります。条件を満たしたら、jを1増やして次の箱の行に移り、CurrentColumnを0に戻します。どちらの条件も満たさない間は、次の“並”のメロンを同じ箱に入れます。
-
fでは、出荷条件を満たさない箱の情報を消します。すべてのメロンを調べ終えたとき、最後の箱が5kg未満で、かつ4個未満のことがあります。この箱は出荷条件を満たさないので、問題文の「出荷条件を満たさない大箱に関する情報は、表示しないようにする」に従い、情報を消します。kは0から5まで1ずつ増えます。相対(A1, j, k)は、行j+1の列Aから、kの値に応じて右へ移動します。つまり、kが0から5までの間に、A列からF列までのセルを順に指定します。箱連番、メロンのID、合計重量が入るセルをまとめて空値にする処理が必要です。そのため、fは「相対(A1, j, k) ← null」です。解答群ではイに当たります。列Gには、F列の合計重量が空値なら空値を表示する式があらかじめ入力されています。F列も空値にすることで、販売価格も表示されなくなります。
誤りやすいポイント
-
入力行iと箱の行jを混同すること
“集計表”から重量を読む行はi、箱の合計重量を書き込む行はjです。重量を加算するときは、入力側にi、出力側にjを使います。 -
重量条件を「5kgと等しい」と考えること
メロンを加えた結果、合計重量が5kgを超える場合もあります。「5kg以上」なので、条件は「≧ 5」です。 -
個数の条件を「4個以上」とすること
メロンを1個ずつ追加し、4個になった時点で箱を閉じます。その後は次の箱に移るため、CurrentColumnが4より大きくなることはありません。「CurrentColumn = 4」で判定できます。 -
最後の箱で列Gまで消すと考えること
列Gの式は、列Fが空値なら空値を表示します。そのため、fでは列AからFまでを空値にすれば足ります。
FAQ
Q: 合計重量が5kg以上になった時点で、箱に4個入っていなくても次の箱に進むのですか？
A: はい。条件は「重量が5kg以上」又は「個数が4個」です。どちらか一方を満たせば、その箱への割当てを終えます。
A: はい。条件は「重量が5kg以上」又は「個数が4個」です。どちらか一方を満たせば、その箱への割当てを終えます。
Q: 最後の箱が出荷条件を満たさないかどうかは、どのように判断しますか？
A: 箱の合計重量を格納する列Fが空値でないかを確認します。未完成の箱がある場合は、列AからFまでを空値にして情報を消します。列Fが空値になれば、列Gの式も販売価格を表示しません。
A: 箱の合計重量を格納する列Fが空値でないかを確認します。未完成の箱がある場合は、列AからFまでを空値にして情報を消します。列Fが空値になれば、列Gの式も販売価格を表示しません。
Q: どうしてfは列AからFまでを消すのですか？
A: 列Aには箱連番、列BからEにはメロンのID、列Fには合計重量が入るためです。これらを残すと、出荷条件を満たさない箱の情報が表示されてしまいます。
A: 列Aには箱連番、列BからEにはメロンのID、列Fには合計重量が入るためです。これらを残すと、出荷条件を満たさない箱の情報が表示されてしまいます。
関連キーワード: 相対参照、論理和、条件分岐、繰返し処理、表計算編集
