基本情報技術者 2016年 秋期 午後 問13
次の表計算のワークシート及びマクロの説明を読んで、設問1，2に答えよ。
〔表計算の説明〕
A社では、建物の改築作業を効率的に遂行するスケジュールを決定するために表計算ソフトを用いている。
〔ワークシート：状態遷移〕
作業とは、建物の改築状態をある状態から別の状態にするための行為とする。20種類の状態があり、各状態には、1～20の番号（以下、状態IDという）が振られている。
改築作業の作業工程は、一つ以上の作業を順に施すことをいう。一連の作業による改築状態の遷移を、状態IDの列で表現する。これを、状態遷移列と呼ぶ。
はじめに、作業による改築状態の遷移の関係、及び各作業の作業日数を入力したワークシート“状態遷移”を作成した。ワークシート“状態遷移”を、図1に示す。
(1) セルC2～V2には、作業開始時の状態IDを1から順に入力する。同様にセルB3～B22には、作業終了時の状態IDを1から順に入力する。
(2) セルC3～V22には、対応する状態間を直接遷移する作業が存在すれば作業に掛かる日数（以下、作業日数という）を、存在しなければ0を入力する。作業日数は、100日以下の整数値である。例えば、図1のセルC4の値は、作業開始時の状態IDが1であって、作業終了時の状態IDが2となる作業が存在し、その作業は5日掛かることを表している。また、セルC6の値は、作業開始時の状態IDが1であって、作業終了時の状態IDが4となる作業が存在しないことを表している。
(3) 全ての作業について、作業開始時の状態IDよりも作業終了時の状態IDの方が大きい。
〔ワークシート：作業工程〕
状態遷移列を入力すると、その状態遷移列が存在するかどうかを判定し、存在する場合は作業工程の総日数を算出するワークシート“作業工程”を作成した。ワークシート“作業工程”において、状態遷移列が存在する場合の例と状態遷移列が存在しない場合の例を、図2に示す。
(1) セルA3～A22には、状態遷移列を構成する状態IDをセルA3から順に入力する。最後の状態IDが入力されているセルよりも下のセルには空値が入力されている。同じ状態IDが2回以上入力されることはない。
(2) セルB3には、0を入力する。セルB4～B22には、同じ行の列Aの状態IDへ1行上の列Aの状態IDから直接遷移するのに必要な作業日数が表示される。直接遷移する作業が存在しない場合は、“×”が表示される。同じ行の列Aのセルの値が空値の場合は0が表示される。
(3) セルC3には、0を入力する。セルC4～C22には、セルA4～A22の各状態IDへ、セルA3に入力されている状態IDから遷移するのに必要な作業の総日数が表示される。同じ行の列Aの状態IDへ1行上の列Aの状態IDから直接遷移する作業が存在しない場合は、“×”が表示される。一つ上のセルの値が“×”の場合は、“×”が表示される。同じ行の列Aのセルの値が空値の場合は、一つ上のセルと同じ値が表示される。
(4) セルC23には、状態遷移列が存在する場合は、作業工程の総日数が表示され、存在しない場合は“×”が表示される。
設問1：
問題文を見る ワークシート“作業工程”の作成手順(1)〜(4)中のに入れる正しい答えを、解答群の中から選べ。
(1) セルB3及びセルC3に0を入力する。
(2) 次の式をセルB4に入力し、セルB5〜B22に複写する。
IF(A4 = null, 0, IF(a > 0, a, '×'))
(3) 次の式をセルC4に入力し、セルC5〜C22に複写する。
IF(b, '×', c)
(4) 次の式をセルC23に入力する。
C22
aに関する解答群
ア：表引き(状態遷移!C$3:V$22, A$4, A3)
イ：表引き(状態遷移!C$3:V$22, A4, A$3)
ウ：表引き(状態遷移!C$3:V$22, A4, A3)
エ：表引き(状態遷移!C3:V$22, A$4, A3)
オ：表引き(状態遷移!C3:V$22, A4, A$3)
カ：表引き(状態遷移!C3:V$22, A4, A3)
bに関する解答群
ア：B4 = '×'
イ：C3 = '×'
ウ：論理積(B4 = '×', C3 = '×')
エ：論理積(B4 ≠ '×', C3 ≠ '×')
オ：論理和(B4 = '×', C3 = '×')
カ：論理和(B4 ≠ '×', C3 ≠ '×')
cに関する解答群
ア：B3 + B4
イ：C3 + B4
ウ：C5 - B4
エ：C5 - B5
模範解答
a：ウ、b：オ、c：イ
解説
解答の導き方
この設問では、セルB列に「直前の状態から移る作業の日数」を表示し、セルC列に「最初の状態から現在の状態までの総日数」を表示する式を考えます。
まず、ワークシート“状態遷移”では、行が作業終了時の状態ID、列が作業開始時の状態IDを表します。したがって、状態遷移列の直前の状態から次の状態へ移る作業日数を調べるには、終了時の状態IDと開始時の状態IDを使って表を引きます。
a：次の状態への作業日数を探す
セルB4では、セルA3の状態からセルA4の状態へ直接遷移する作業があるかを調べます。
- 終了時の状態IDは、セルA4の値です。これは“状態遷移”の表の行を指定します。
- 開始時の状態IDは、セルA3の値です。これは表の列を指定します。
そのため、表引きの行の指定にA4、列の指定にA3を使います。また、セルB4の式を下の行へ複写しても、検索範囲は変わらないようにします。行番号を固定する「絶対参照」を使うと、検索範囲を固定できます。絶対参照とは、式を複写しても参照する行や列を動かさない指定です。
したがって、aには次の式が入ります。
表引き(状態遷移!C$3:V$22, A4, A3)
この式を下へ複写すると、A4とA3はそれぞれA5とA4に変わります。つまり、毎回「直前の状態から現在の状態へ」の作業日数を調べられます。一方、検索範囲の行は固定されます。
なお、表引きの行と列を逆にすると、別の状態間の作業日数を調べてしまいます。また、A3を固定すると、複写後も最初の状態からの作業日数を調べることになり、「直前の状態から」の作業日数にはなりません。
b：総日数が“×”になる条件を考える
セルC4以降の総日数は、直前の総日数に、今回の作業日数を加えて求めます。ただし、次のどちらかに当てはまる場合、状態遷移列は成立しません。
- 今回の作業が存在せず、セルB4が“×”である。
- それまでの状態遷移が成立しておらず、セルC3が“×”である。
どちらか一方でも当てはまれば“×”にする必要があります。そのため、条件には「論理和」を使います。論理和は、複数の条件のうち少なくとも一つが成り立つときに真になる条件です。
よって、bには次の条件が入ります。
論理和(B4 = '×', C3 = '×')
「論理積」では、B4とC3の両方が“×”の場合にしか条件が成立しません。しかし、作業が一つでも存在しない場合や、直前までに“×”が出ている場合は、その時点で状態遷移列が成立しないため、論理和が必要です。
c：直前までの総日数に今回の日数を加える
“×”になる条件に当てはまらない場合、総日数は「一つ上のセルC3の総日数」と「今回の作業日数B4」の合計です。したがって、cには次の式が入ります。
C3 + B4
状態IDが空値の行では、セルB列には0が表示されます。そのため、総日数に0を加えれば、それまでの総日数がそのまま引き継がれます。反対に、途中で“×”になった場合は、bの条件によって後続のセルも“×”になります。
誤りやすいポイント
-
表の行と列を逆にする
“状態遷移”では、行が終了時の状態ID、列が開始時の状態IDです。セルA4は行、セルA3は列を指定します。 -
最初の状態IDを固定してしまう
セルB4を下へ複写すると、参照する状態IDも次の行に合わせて変わる必要があります。A3を固定すると、毎回最初の状態からの作業日数を調べてしまいます。 -
検索範囲を複写でずらしてしまう
セルB4の式を下へ複写しても、“状態遷移”の表全体を検索し続ける必要があります。そこで、検索範囲の行を固定します。 -
“×”になる条件に論理積を使う
作業が存在しない場合と、直前までに“×”がある場合は、どちらか一方だけでも状態遷移列が成立しません。したがって、論理積ではなく論理和を使います。 -
今回の作業日数だけを総日数にする
総日数は今回の作業日数だけではありません。直前までの総日数に、今回の作業日数を加えます。
FAQ
Q: 状態IDが空値の行では、総日数がなぜ変わらないのですか？
A: 空値の行では、セルB列に0が表示されます。直前の総日数に0を加えるため、総日数はそのまま引き継がれます。
A: 空値の行では、セルB列に0が表示されます。直前の総日数に0を加えるため、総日数はそのまま引き継がれます。
Q: 途中のセルが“×”になった後も、後続のセルを“×”にするのはなぜですか？
A: 状態遷移列は、並んだ状態IDのすべての間に直接遷移する作業が必要だからです。途中の遷移が成立しなければ、その後の状態までの総日数も求められません。
A: 状態遷移列は、並んだ状態IDのすべての間に直接遷移する作業が必要だからです。途中の遷移が成立しなければ、その後の状態までの総日数も求められません。
Q: セルC23では、なぜセルC22の値を使うのですか？
A: 状態遷移列の最後の状態までの総日数がセルC22に表示されるためです。成立しない場合は、セルC22に“×”が表示され、その値がセルC23にも引き継がれます。
A: 状態遷移列の最後の状態までの総日数がセルC22に表示されるためです。成立しない場合は、セルC22に“×”が表示され、その値がセルC23にも引き継がれます。
関連キーワード: 表引き、相対参照、絶対参照、論理和、累積計算
設問2：
問題文を見る マクロDisplayMinimumPath中のに入れる正しい答えを、解答群の中から選べ。
〔ワークシート：作業工程（拡張）〕
状態遷移列の先頭になる状態ID（以下、開始状態IDという）と状態遷移列の最後になる状態ID（以下、終了状態IDという）を入力すると、作業工程の総日数が最小になる状態遷移列を求める機能を追加したワークシート“作業工程（拡張）”を作成し、マクロCalculateMinimumとマクロDisplayMinimumPathを格納した。作成したワークシート“作業工程（拡張）”の例を、図3に示す。
なお、ワークシート“作業工程”を拡張することによって、関数“表引き”の引数で与えられた行又は列の位置の値が空値になることがある。その場合は、関数“表引き”は空値を返す。
(1) セルF2に開始状態IDを、セルF3に終了状態IDを入力し、マクロCalculateMinimumを実行した後、マクロDisplayMinimumPathを実行する。
(2) マクロCalculateMinimumの仕様は、次のとおりである。
① セルH3～H22には、1から順に状態IDを格納する。
② セルI3～I22には、開始状態IDから各行の列Hで示す状態IDまで遷移するのに必要な作業の総日数の最小値を格納する。状態遷移列が存在しない場合は9999を格納する。
③ セルJ3～J22には、開始状態IDから各行の列Hで示す状態IDまで遷移するのに必要な作業の総日数が最小となる状態遷移列における、その状態IDの直前の状態IDを格納する。開始状態IDに対応するセルの場合は0を格納する。また、状態遷移列が存在しない場合は空値を格納する。
(3) マクロDisplayMinimumPathは、セルA3～A22に作業工程の総日数が最小となる状態遷移列を格納する。終了状態IDを格納したセルよりも下のセルには空値を格納する。開始状態IDから終了状態IDまでの状態遷移列が存在しない場合は、セルA3～A22には全て空値を格納する。
dに関する解答群
ア：Current < F2
イ：Current < F3
ウ：Current < F3 - F2
エ：Current = 0
オ：Current = F2
カ：Current ≠ F2
eに関する解答群
ア：照合検索(Current, H3:H22, J3:J22)
イ：照合検索(Current, J3:J22, H3:H22)
ウ：照合検索(NumWork, H3:H22, J3:J22)
エ：照合検索(NumWork, J3:J22, H3:H22)
fに関する解答群
ア：I: 0, I < NumWork, 1
イ：I: 0, I ≦ NumWork, 1
ウ：I: 1, I ≦ NumWork, 1
エ：I: NumWork − 1, I ≧ 0, −1
オ：I: NumWork, I > 0, −1
カ：I: NumWork, I ≧ 0, −1
模範解答
d：カ、e：ア、f：カ
解説
解答の導き方
このマクロは、最小日数の状態遷移列を、まず終了状態IDから開始状態IDへさかのぼって作業数を数え、その後、セルA3～A22に格納します。
1. 状態遷移列が存在するかを確認します
マクロの最初のループでは、セルA3～A22を空値にしています。その後、「相対(I2, F3, 0) < 9999」を調べます。
セルI3～I22には、開始状態IDから各状態IDまでの最小日数が格納されています。また、状態遷移列が存在しない場合は9999です。したがって、この条件は「終了状態IDまでの状態遷移列が存在するか」を確認しています。存在しない場合は条件を満たさず、空値のまま終了します。
2. 終了状態IDから開始状態IDまでさかのぼります
状態遷移列が存在する場合、Currentには終了状態IDが入ります。セルJ3～J22には、最小日数となる状態遷移列での「直前の状態ID」が格納されています。
つまり、Currentが現在の状態IDなら、Currentに対応するセルJの値を調べると、その一つ前の状態IDが分かります。これを開始状態IDに着くまで繰り返すことで、必要な作業数を数えます。
開始状態IDに着いたら、さかのぼる処理を終える必要があります。そのため、繰り返しの条件は「Current ≠ F2」となり、dはカです。
たとえば開始状態IDが3で終了状態IDが13なら、Currentを13から始めて、13の直前の状態、さらにその直前の状態という順にたどります。開始状態IDの3に着いたところで終了します。
3. Currentの直前の状態IDを調べます
Currentに入っている状態IDを、セルH3～H22から探します。対応するセルJ3～J22の値を取り出せば、その状態の直前の状態IDが得られます。
この処理に合うのは、キーのCurrentを検索範囲H3:H22で探し、対応する値をJ3:J22から取り出す「照合検索(Current, H3:H22, J3:J22)」です。したがって、eはアです。
この検索を一回行うたびに、直前の状態へ一つ戻り、作業数を1増やします。開始状態IDまで戻った時点で、NumWorkには状態間の遷移回数が入っています。
4. 終了状態IDから逆順にセルへ格納します
作業数がNumWorkなら、状態IDの個数はNumWorkに1を加えた数です。作業数が0の場合でも、開始状態IDと終了状態IDが同じ状態IDとして1つ必要だからです。
そこで、IをNumWorkから0まで1ずつ減らしながら、セルA3からI行分下のセルにCurrentを格納します。最初は終了状態IDを格納し、その後、直前の状態IDへ戻って格納します。最後にIが0のとき、開始状態IDがセルA3に入ります。
このように、終了状態IDから始めてIを0まで含めて減らすため、fはカです。
誤りやすいポイント
- dで「Current < F2」を選ばないようにします。状態IDの大小を比べるのではなく、直前の状態を順にたどり、開始状態IDに着いたかどうかを判定します。
- eでは、検索範囲と取り出す範囲の順番に注意します。Currentを状態IDの一覧H3:H22から探し、その行に対応する直前の状態IDをJ3:J22から取り出します。
- NumWorkは状態IDの個数ではなく、状態間の遷移回数です。そのため、格納する状態IDはNumWork個ではなく、NumWorkに1を加えた個数になります。
- fの終了値を1にすると、Iが0のときにセルA3へ格納できず、開始状態IDが抜けてしまいます。Iは0まで含めて処理します。
FAQ
Q: 開始状態IDと終了状態IDが同じ場合、どうなりますか？
A: 終了状態IDから開始状態IDへさかのぼる処理は行われないため、NumWorkは0です。格納処理はIが0のときだけ行われ、状態IDがセルA3に入ります。
A: 終了状態IDから開始状態IDへさかのぼる処理は行われないため、NumWorkは0です。格納処理はIが0のときだけ行われ、状態IDがセルA3に入ります。
Q: 状態遷移列が存在しない場合、なぜセルA3～A22は空値のままなのですか？
A: マクロの最初にセルA3～A22を空値にし、その後、セルIの値が9999未満の場合にだけ状態IDを格納する処理へ進むからです。状態遷移列がない場合は、この格納処理を行いません。
A: マクロの最初にセルA3～A22を空値にし、その後、セルIの値が9999未満の場合にだけ状態IDを格納する処理へ進むからです。状態遷移列がない場合は、この格納処理を行いません。
Q: 「直前の状態ID」は何を表していますか？
A: 最小日数となる状態遷移列の中で、現在の状態へ直接遷移する一つ前の状態を表します。これを終了状態IDから順にたどると、最小日数の状態遷移列を逆向きに確認できます。
A: 最小日数となる状態遷移列の中で、現在の状態へ直接遷移する一つ前の状態を表します。これを終了状態IDから順にたどると、最小日数の状態遷移列を逆向きに確認できます。
関連キーワード: 最短経路、探索、表引き、ループ制御
