基本情報技術者 2014年 秋期 午後 問13
次の表計算のワークシート及びマクロの説明を読んで、設問1，2に答えよ。
〔表計算の説明〕
鉄道会社Y社では、表計算ソフトを使って乗車駅と降車駅を入力すると鉄道路線における駅間の距離（以下、乗車距離という）と運賃を表示するプログラムを作成した。
(1) 鉄道路線は、隣接駅間の距離（以下、隣接駅間距離という）が5kmを超えず、分岐をもたない全長94.2kmの単一路線であり、起点駅から終点駅まで32の駅から成る。
(2) 各駅には“001”～“032”の駅コードが割り付けられている。
(3) ワークシート“鉄道運賃”に乗車駅の駅コード（セルB1）と降車駅の駅コード（セルB2）を入力すると、乗車距離（セルB3）と運賃（セルB4）が表示される。
乗車駅と降車駅の駅コードは、鉄道路線の各駅に割り付けられた駅コードのリストから選択して入力するようになっている。ワークシート“鉄道運賃”の例を図1に示す。
(4) 駅コードと隣接駅間距離は、起点駅から終点駅に向かってワークシート“路線情報”の列Aと列Bに順に格納されている。ワークシート“路線情報”の例を図2に示す。
① ワークシートの各行の隣接駅間距離には、その行の駅コードに対応する駅から次行の駅コードに対応する駅までの距離（単位はkm）が格納されている。例えば、駅コード“002”と駅コード“003”の駅間の距離は、セルB3に格納されている3.0である。終点駅の駅コードに対応する行の隣接駅間距離（セルB33）には0.0が格納されている。
② 列Cは乗車距離計算のための作業領域に用いる。
(5) 運賃は、乗車距離に関係なく一律に定められている基本運賃100円と、乗車距離に応じて決まる距離運賃とを合計した額である。
(6) 距離運賃は、決められた四つの距離の範囲で乗車距離を区分し、決められた計算方法で区分ごとの運賃（以下、区分運賃という）を計算し、それらを合計した額である。
① 各区分には1～4の番号（以下、区分番号という）が割り付けられている。各区分の距離の範囲は表1のとおりである。
② 乗車距離を、決められた四つの距離の範囲で区分し、区分ごとの距離（以下、区分距離という）に分ける。例えば、乗車距離が11.6kmの場合、区分番号1の区分距離10kmと、区分番号2の区分距離1.6km（11.6km－10km）に分ける。
③ 区分ごとに、運賃計算の基準となる単位距離が決められている。区分、単位距離と単位運賃との関係を表2に示す。区分距離の端数は単位距離に切り上げ、単位距離ごとに単位運賃を加算する。
(7) 例えば、乗車距離が11.6kmである駅コード“004”から駅コード“001”までの運賃は、次のように求める。ここで、計算式中の“切上げ(X, 0)”は、Xの小数点以下を切り上げた値を表す。
① 区分番号1の区分運賃は、1kmまでごとに10円を加算し、次の式で求める。
10円×切上げ(10km ÷ 1km, 0) = 100円
② 区分番号2の区分運賃は、3kmまでごとに20円を加算し、次の式で求める。
20円×切上げ((11.6km－10km) ÷ 3km, 0) = 20円
③ 運賃は、基本運賃100円と距離運賃120円（①、②で求めた区分運賃の合計額）を合計した220円になる。
(8) 基本運賃と各区分の区分番号、距離の範囲の上限（以下、上限距離という）、単位距離及び単位運賃を格納したワークシート“運賃情報”の例を図3に示す。
① セルD1には、基本運賃が格納されている。
② 区分ごとの運賃体系表（セルA4～D8）のセルA5～D8には、各区分の区分番号、上限距離、単位距離及び単位運賃が区分番号の昇順に格納されている。
③ 区分番号4の上限距離には、999999が格納されている。
④ 列E、Fには、各区分の区分距離と区分運賃が表示される。
〔乗車距離の計算〕
乗車駅から降車駅までの間（以下、乗車区間という）にある隣接駅間距離を選び出し、それらを合計したものを乗車距離とする。乗車距離（ワークシート“鉄道運賃”のセルB3）を求めるために、ワークシート“路線情報”と“鉄道運賃”に(1)～(3)の手順で式の入力を行う。
(1) 乗車区間の判別に当たり、ワークシート“路線情報”のセルC34とセルC35を使用する。乗車駅及び降車駅の駅コードを利用するために、ワークシート“路線情報”のセルC34に次の式を入力し、セルC35に複写する。
照合一致(鉄道運賃!B1, A$2～A$33, 0)
(2) ワークシート“路線情報”のセルC2に次の式を入力し、セルC3～C32に複写する。
IF(a(b, c), '○', null)
(3) 乗車距離を求めるために、ワークシート“鉄道運賃”のセルB3に次の式を入力する。
条件付合計(路線情報!C2～C32, = '○', 路線情報!B2～B32)
〔運賃の計算〕
区分距離と区分運賃を計算し、基本運賃と全ての区分運賃を合計したものを運賃とする。運賃（ワークシート“鉄道運賃”のセルB4）を求めるために、ワークシート“運賃情報”と“鉄道運賃”に(1)～(4)の手順で式の入力を行う。
(1) 区分番号1の区分距離を求めるために、ワークシート“運賃情報”のセルE5に次の式を入力する。
IF(鉄道運賃!B3 ≦ B5, 鉄道運賃!B3, B5)
(2) 区分番号2～4の区分距離を求めるために、ワークシート“運賃情報”のセルE6に次の式を入力し、セルE7、E8に複写する。
IF(鉄道運賃!B$3 ≦ B6, 鉄道運賃!B$3, B6) − d
(3) 区分運賃を求めるために、ワークシート“運賃情報”のセルF5に次の式を入力し、セルF6～F8に複写する。
e
(4) 乗車区間の運賃を求めるために、ワークシート“鉄道運賃”のセルB4に次の式を入力する。
運賃情報!D1 + 合計(運賃情報!F5～F8)
設問1：
問題文を見る 乗車距離の計算、運賃の計算に関する説明文中のに入れる正しい答えを、解答群の中から選べ。
aに関する解答群
ア：論理積
イ：論理和
bに関する解答群
ア：照合一致(A2, A$2～A$33, 0)＜最小(C$34～C$35)
イ：照合一致(A2, A$2～A$33, 0)≦最小(C$34～C$35)
ウ：照合一致(A2, A$2～A$33, 0)＞最小(C$34～C$35)
エ：照合一致(A2, A$2～A$33, 0)≧最小(C$34～C$35)
cに関する解答群
ア：照合一致(A2, A$2～A$33, 0)＜最大(C$34～C$35)
イ：照合一致(A2, A$2～A$33, 0)≦最大(C$34～C$35)
ウ：照合一致(A2, A$2～A$33, 0)＞最大(C$34～C$35)
エ：照合一致(A2, A$2～A$33, 0)≧最大(C$34～C$35)
dに関する解答群
ア：B5
イ：E5
ウ：合計(B$5～B5)
エ：合計(B$5～B$8)
オ：合計(E5～E$8)
カ：合計(E$5～E5)
eに関する解答群
ア：切上げ(B5, 0)／C5 * D5
イ：切上げ(B5／C5, 0) * D5
ウ：切上げ(E5, 0)／C5 * D5
エ：切上げ(E5／C5, 0) * D5
オ：切捨て(B5／C5 + 1, 0) * D5
カ：切捨て(E5／C5 + 1, 0) * D5
模範解答
a：ア、b：エ、c：ア、d：カ、e：エ
解説
解答の導き方
1. 駅の位置から、乗車区間に含める隣接駅間を判定します。
問題文では、駅コードは起点駅から終点駅に向かって順に並び、各行の隣接駅間距離は「その行の駅コードに対応する駅から次行の駅コードに対応する駅までの距離」とされています。つまり、駅コードの位置が の行の距離は、位置 の駅から次の駅までの距離です。
セルC34とセルC35の照合一致は、それぞれ乗車駅と降車駅が駅コード一覧の何番目にあるかを求めます。図1の例では、乗車駅“004”の位置は 、降車駅“001”の位置は です。乗車区間の両端の位置を比べるため、セルC34～C35の最小値は 、最大値は になります。
駅の位置が最小値以上で、最大値より小さい行を選べば、その行の距離は乗車区間に含まれます。位置が最小値以上である条件は、解答群bの「≧最小(C$34～C$35)」なので bはエ です。位置が最大値より小さい条件は、解答群cの「＜最大(C$34～C$35)」なので cはア です。
この2つの条件を両方満たす必要があるため、使うのは「論理積」です。したがって aはア です。セルC2に入れる式の判定部分は、次のようになります。
IF(論理積(照合一致(A2, A$2～A$33, 0)≧最小(C$34～C$35), 照合一致(A2, A$2～A$33, 0)＜最大(C$34～C$35)), '○', null)
図1の例では、駅の位置が 、、 の行に「○」が付きます。駅コード“004”の行は乗車駅の行ですが、位置が大きい側の駅です。その行の距離は“004”から次の駅までの距離なので、乗車区間の外となり、「○」は付きません。
問題文の条件付合計は、「○」が付いた行の隣接駅間距離だけを合計します。図2の例では、駅コード“001”から“004”までの距離として、kmが合計されます。
2. 区分距離は、乗車距離から前の区分までに割り当てた距離を引いて求めます。
区分番号1の区分距離は、乗車距離と上限距離の小さい方です。問題文に示されたセルE5の式は、乗車距離が上限距離を超えるときは上限距離まで、超えないときは乗車距離までを区分番号1に割り当てます。
区分番号2以降では、その区分の上限距離までに当たる距離から、前の区分までに割り当てた距離を引きます。セルE6では、区分番号2の上限距離までの距離から、前の区分の区分距離であるE5を引きます。セルE6の式を下へ複写すると、引く範囲もE5から現在の行の一つ上まで広がる必要があります。
そのため、引く距離は「合計(E$5～E5)」です。先頭の行番号を固定し、末尾の行番号を複写に合わせて変える形なので、dはカ です。
図1の乗車距離 kmで計算すると、区分距離は次のようになります。
3. 区分運賃は、区分距離を単位距離で割って切り上げます。
問題文では、区分距離の端数は単位距離に切り上げ、単位距離ごとに単位運賃を加算するとされています。そのため、まず区分距離を単位距離で割り、必要な単位数を求めます。次に、その単位数に単位運賃を掛けます。
セルE5～E8が区分距離、セルC5～C8が単位距離、セルD5～D8が単位運賃なので、区分運賃の式は「切上げ(E5／C5, 0) * D5」となります。したがって eはエ です。
図1の例では、区分番号1は kmをkm単位で計算するため、単位です。区分運賃は 円です。区分番号2は kmをkm単位で計算するため、を切り上げて 単位です。区分運賃は 円です。区分番号3と4は区分距離が kmなので、区分運賃も 円です。
最後に、基本運賃 円に区分運賃の合計 円を加えると、運賃は 円になります。
誤りやすいポイント
- 乗車区間の両端のうち、位置が大きい駅の行を含めない点に注意してください。その行の距離は、その駅からさらに次の駅までの距離です。図1では、駅コード“004”は乗車駅ですが、位置が大きい側の駅なので、その行の距離は乗車区間に含まれません。
- 判定条件は、最小の位置以上かつ最大の位置未満です。最大の位置以下にすると、位置が大きい側の駅から次の駅までの距離も含めてしまいます。
- 区分番号2以降の区分距離は、乗車距離から上限距離を単純に引くのではありません。前の区分までに割り当てた区分距離の合計を引きます。
- 区分運賃の切上げは、区分距離そのものではなく、区分距離を単位距離で割った値に対して行います。割り切れない端数があると、単位数が一つ増えます。
FAQ
Q: 乗車駅と降車駅の順番が逆でも、同じ方法で乗車距離を求められますか？
A: 求められます。乗車駅と降車駅の位置の小さい方と大きい方を使って、その間の隣接駅間距離を選ぶためです。路線に分岐がないので、どちら向きに乗る場合も同じ区間の距離を合計します。
A: 求められます。乗車駅と降車駅の位置の小さい方と大きい方を使って、その間の隣接駅間距離を選ぶためです。路線に分岐がないので、どちら向きに乗る場合も同じ区間の距離を合計します。
Q: 乗車距離が上限距離とちょうど同じ場合、次の区分にも距離が割り当てられますか？
A: 割り当てられません。例えば乗車距離が kmなら、区分番号1に kmが割り当てられ、区分番号2の区分距離は kmになります。
A: 割り当てられません。例えば乗車距離が kmなら、区分番号1に kmが割り当てられ、区分番号2の区分距離は kmになります。
Q: なぜ区分距離を単位距離で割った値を切り上げるのですか？
A: 単位距離に満たない端数も、次の単位分として運賃を加算するためです。例えば区分距離が km、単位距離が kmなら、必要な単位数は を切り上げた 単位です。
A: 単位距離に満たない端数も、次の単位分として運賃を加算するためです。例えば区分距離が km、単位距離が kmなら、必要な単位数は を切り上げた 単位です。
関連キーワード: 表計算、相対参照、絶対参照、論理積、条件付き合計、切上げ
設問2：
問題文を見る Y社では、区分運賃を見直すことによる運賃改定を行うことにした。ただし、区分ごとの単位距離と単位運賃は変更せず、距離の範囲だけを見直す。これに伴い、ワークシート“運賃情報”の区分番号1～3の上限距離（セルB5～B7）の値を変更するためのマクロを作成し、ワークシート“運賃情報”に格納した。マクロChange_SectionInfoに関する説明を読んで、マクロ中のに入れる正しい答えを、解答群の中から選べ。ここで、g1とg2に入れる答えは、gに関する解答群の中から組合せとして正しいものを選ぶものとする。
〔距離の範囲見直しの説明〕
(1) 少なくとも隣接駅間距離は、区分番号1の距離の範囲に入るようにする。
① 隣接駅間距離の中で最長の距離を求める。
② 求めた距離の小数点以下を切り上げた値を、区分番号1の新たな上限距離とする。
(2) 五つ離れた駅までの距離は、区分番号2の距離の範囲に入るようにする。
① 鉄道路線上のそれぞれの駅から五つ離れた駅までの距離を計算し、その中で最長の距離を求める。
② 区分番号1の新たな上限距離に区分番号2の単位距離の倍数を加えた値で、かつ、①で求めた距離以上となる最小値を、区分番号2の新たな上限距離とする。
(3) 鉄道路線の全区間（起点駅から終点駅まで）の距離の50%以下の距離は、区分番号3の距離の範囲に入るようにする。
① 鉄道路線の全区間の50%の距離を求める。
② 区分番号2の新たな上限距離に区分番号3の単位距離の倍数を加えた値で、かつ、①で求めた距離以上となる最小値を、区分番号3の新たな上限距離とする。
fに関する解答群
ア：J: 0, J ≦ 4
イ：J: 1, J ≦ 4
ウ：J: 0, J ≦ 5
エ：J: 1, J ≦ 5
オ：J: 0, J ≦ 31
カ：J: 1, J ≦ 31
gに関する解答群
模範解答
f：イ、g：イ
解説
解答の導き方
このマクロでは、まず距離を調べてから、区分番号1～3の新しい上限距離を決めます。変数は、計算途中の値を一時的に入れておく箱のようなものです。
1. fでは、五つ離れた駅までの距離を調べます
問題文には「五つ離れた駅までの距離は、区分番号2の距離の範囲に入るようにする」とあります。五つ離れた駅までの距離は、隣接駅間距離を五つ足した値です。
相対(路線情報!B2, I, 0)は、セルB2から下にI行移動したセルの値を取り出します。したがって、Iが0のとき、最初のSum2にはB2の値が入ります。B2は、駅コード“001”の駅から駅コード“002”の駅までの距離です。
その後、Jを1から4まで変えて、後に続く四つの隣接駅間距離をSum2に加えます。最初の一つはSum2にすでに入っているので、Jは1から始まります。
Iは0から26まで変化します。32の駅から五つ離れた駅までの距離を調べるには、起点にできる駅は駅コード“001”から駅コード“027”までだからです。駅コード“027”を起点にした場合は、B28からB32までの五つの隣接駅間距離を合計します。
各合計値Sum2をDist2と比べ、Sum2のほうが大きいときはDist2を更新します。こうして、五つ離れた駅までの距離の中で最長の距離がDist2になります。
Jを0から始める選択肢では、Sum2に初めから入っている距離をもう一度足してしまいます。Jを5まで進める選択肢では、隣接駅間距離を六つ足してしまいます。したがって、fは J: 1, J ≦ 4 です。
2. g1では、Dist2から区分番号1の上限距離を引きます
区分番号1の新たな上限距離B5は、隣接駅間距離の最長値Dist1を、単位距離C5の倍数になるように切り上げた値です。C5は単位距離なので、切り上げた結果が新しい上限距離になります。
区分番号2の新たな上限距離B6は、「Dist2以上」であり、かつ「B5に区分番号2の単位距離C6の倍数を加えた値」です。つまり、B5からDist2までに、C6が何単位分必要かを求めます。
この式の切上げ対象は、Dist2そのものではなく、Dist2とB5との差をC6で割った値です。そのため、g1には (Dist2 - B5) が入ります。
3. g2では、Dist3から区分番号2の上限距離を引きます
Dist3は、鉄道路線全体の距離の50%です。区分番号3の新たな上限距離B7は、B6から区分番号3の単位距離C7を必要な回数だけ加え、Dist3以上となる最小の値にします。
ここでも、必要な単位距離の回数を求めるため、Dist3から現在の上限距離B6を引きます。したがって、g2には (Dist3 - B6) が入ります。
誤りやすいポイント
- 五つ離れた駅までの距離は、隣接駅間距離を五つ足します。駅を五つ数えるのではなく、駅と駅の間の距離を数える点に注意してください。
- Sum2には、繰返しの前に一つ目の距離が入っています。そのため、Jは1から始めます。
- B6はDist2を基準に単位距離の倍数へ切り上げるのではありません。B5を起点にして、Dist2以上になる最小の値を求めます。
- B7も同様に、B6を起点にしてDist3以上になる最小の値を求めます。g2を求めるときに引くのはDist2ではなくB6です。
FAQ
Q: なぜJを1から4までにするのですか？
A: Sum2には、すでに一つ目の隣接駅間距離が入っています。五つ分の距離にするには、残り四つを追加するため、Jを1から4まで変えます。
A: Sum2には、すでに一つ目の隣接駅間距離が入っています。五つ分の距離にするには、残り四つを追加するため、Jを1から4まで変えます。
Q: なぜg1とg2で、それぞれ上限距離を引くのですか？
A: 区分番号2ではB5から、区分番号3ではB6から単位距離を加えるためです。新しい上限距離までに必要な増分を計算しています。
A: 区分番号2ではB5から、区分番号3ではB6から単位距離を加えるためです。新しい上限距離までに必要な増分を計算しています。
Q: 切上げの対象がちょうど整数なら、さらに大きくなりますか？
A: いいえ。切上げは、小数部分があるときに次の整数へ進める処理です。割り算の結果がちょうど整数なら、その値のままです。
A: いいえ。切上げは、小数部分があるときに次の整数へ進める処理です。割り算の結果がちょうど整数なら、その値のままです。
関連キーワード: 最大値、繰返し処理、相対参照、切上げ、表計算
