平成28年度秋期基本情報技術者_表計算のマクロをExcelVBAで再現する
目次
1.出典
2.問題で使用するExcelシート「作業工程(拡張)」のダミーデータの簡単な作り方
3.問題文の要旨
4.問題文(疑似言語)
5.解答群(疑似言語)
6.問題文(VBA翻訳)
7.解答群(VBA翻訳)
8.正解
9.正解を入れたVBA完全版 ※動作確認済み
10.CalcurateMinimumを実行するためのExcelシート「状態遷移」、「作業工程(拡張)」のダミーデータの簡単な作り方
11.マクロCalculateMinimum(VBA) ※動作確認済み
12.バックナンバー
13.ExcelVBA講座ご紹介
1.出典
経済産業省国家試験(IPA情報処理推進機構)
平成28年度秋期 基本情報技術者試験 午後試験 選択問題 問13表計算設問2[解答欄d~f]
なお、試験問題及び正解の著作権はIPA情報処理推進機構に帰属します。
2.問題で使用するExcelシート「作業工程(拡張)」のダミーデータの簡単な作り方
・数値はすべて直接入力。
・空白のセルはマクロの実行と無関係なので空白で良い。
3.問題文の要旨
20個の状態があり、それぞれ状態ID:1~20の番号が振られている。それぞれ、2つの状態間(小さい番号のIDから大きい番号のIDへの遷移)は直接遷移できる場合とできない場合があって、直接遷移できる場合は遷移するのに必要な作業日数がある。先にマクロCalculateMinimumを実行すると、開始ID(セルF2)からセル範囲H3:H22の各状態へ遷移するための最小日数を計算する。ただし、直接または間接的に遷移できなければ日数=9999、開始IDと一致する場合は日数=0とする。遷移できる場合は、J列に最短日数で遷移した場合の直前の状態IDを表示する。
これをもとに、マクロDisplayMinimumPathで、セルA3以降に、開始ID(セルF2)から終了ID(セルF3)に至るまでの最短経路を表示したい。
4.問題文(疑似言語)
[マクロ:DisplayMinimumPath]
〇マクロ:DisplayMinimumPath
〇数値型:I, NumWork, Current
■ I: 1, I<=20, 1
|・相対(A2, I, 0) ← null
■
▲ 相対(I2, F3, 0) < 9999 /* 状態遷移列が存在する */
|・NumWork ← 0
|・Current ← F3
|/* 作業工程の作業数 NumWork を算出する */
|■ [ 解答欄d ]
||・Current ← [ 解答欄e ]
||・NumWork ← NumWork + 1
|■
|/* 状態遷移列を格納する */
|・Current ← F3
|■ [ 解答欄f ]
||・相対(A3, I, 0) ← Current
||・Current ← [ 解答欄e ]
|■
▼
5.解答群(疑似言語)
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
6.問題文(VBA翻訳)
Sub DisplayMinimumPath()
Dim I As Integer, NumWork As Integer, Current As Integer
For I = 1 To 20
Range("A2").Offset(I, 0) = ""
Next
If Range("I2").Offset(Range("F3"), 0) < 9999 Then '状態遷移列が存在する
NumWork = 0
Current = Range("F3")
'作業工程の作業数 NumWork を算出する
Do While [ 解答欄d ]
Current = [ 解答欄e ]
NumWork = NumWork + 1
Loop
'状態遷移列を格納する
Current = Range("F3")
For [ 解答欄f ]
Range("A3").Offset(I, 0) = Current
Current = [ 解答欄e ]
Next
End If
End Sub
7.解答群(VBA翻訳)
dに関する解答群
ア Current < Range("F2")
イ Current < Range("F3")
ウ Current < Range("F3") - Range("F2")
エ Current = 0
オ Current = Range("F2")
カ Current <> Range("F2")
eに関する解答群
ア WorksheetFunction.Lookup(Current, Range("H3:H22"), Range("J3:J22"))
イ WorksheetFunction.Lookup(Current, Range("J3:J22"), Range("H3:H22"))
ウ WorksheetFunction.Lookup(NumWork, Range("H3:H22"), Range("J3:J22"))
エ WorksheetFunction.Lookup(NumWork, Range("J3:J22"), Range("H3:H22"))
fに関する解答群
ア I = 0 To NumWork - 1, 1
イ I = 0 To NumWork, 1
ウ I = 1 To NumWork, 1
エ I = NumWork - 1 To 0 Step -1
オ I = NumWork To 1 Step -1
カ I = NumWork To 0 Step -1
8.正解
d カ
最初にCurrentにセルF3(終了ID)を初期値として入れているのがポイント。ループのなかでCurrentを上書きしているので、ループでその上書きを何回か繰り返して最終的にCurrentがセルF2(開始ID)と一致すれば終了する(一致していないあいだ繰り返す)。
e ア
終了IDから経路をさかのぼるためには、直前の状態(前ノードへのポインタ)をCurrentに入れる必要がある。照合検索を使う必要はない(相対で良い?)が、あえて使うのであればCurrentをH列で探して、J列の値を返せばよい。
f カ
NumWork算出の時にCurrentを一次元配列に保存しておけばよいが、それをしないのであればもう一度さかのぼることになる。相対の開始セルがA3になっているのがポイント。遷移回数がNumWorkであれば、出力する行数はNumWork+1行になるので、IはNumWork~0となる。
9.正解を入れたVBA完全版 ※動作確認済み
Sub DisplayMinimumPath()
Dim I As Integer, NumWork As Integer, Current As Integer
For I = 1 To 20
Range("A2").Offset(I, 0) = ""
Next
If Range("I2").Offset(Range("F3"), 0) < 9999 Then '状態遷移列が存在する
NumWork = 0
Current = Range("F3")
'作業工程の作業数 NumWork を算出する
Do While Current <> Range("F2")
Current = WorksheetFunction.Lookup(Current, Range("H3:H22"), Range("J3:J22"))
NumWork = NumWork + 1
Loop
'状態遷移列を格納する
Current = Range("F3")
For I = NumWork To 0 Step -1
Range("A3").Offset(I, 0) = Current
Current = WorksheetFunction.Lookup(Current, Range("H3:H22"), Range("J3:J22"))
Next
End If
End Sub
10.CalculateMinimumを実行するためのExcelシート「状態遷移」、「作業工程(拡張)」のダミーデータの簡単な作り方
問題では、マクロCalculateMinimumを実行してから、マクロDisplayMinimumPathを実行して最短経路を出力します。そこで、マクロCalculateMinimumも作りました。
問題文の要旨
シート「状態遷移」は、2つの状態間が直接遷移できる場合は遷移にかかる作業日数を、直接遷移できない場合は0が入力されている。マクロを実行すると、シート「作業工程(拡張)」のセルF2をスタートの状態として、H~J列に状態1~20への最小日数と前状態IDが出力される。
シート「状態遷移」
・数値はすべて直接入力。
シート「作業工程(拡張)」
・セルB3=0、C3=0
・セルB4以降:=INDEX(状態遷移!$C$3:$V$22,A4,A3)
・セルC4以降:=C3+B4
・数式はエラーになっているがマクロ実行に影響はない。
11.マクロCalculateMinimum(VBA)※動作確認済み
最短日数を出すには再帰呼び出しをします。
Sub CalculateMinimum()
a = Sheets("状態遷移").Range("C3:V22")
Dim minday(1 To 20)
Dim prev(1 To 20)
For I = 1 To 20
Cells(I + 2, 8) = I
minday(I) = 9999
Next
current_id = Range("F2")
minday(current_id) = 0
prev(current_id) = 0
current_day = 0
Call CalculateMinimum2(current_id, current_day, a, minday, prev)
For I = 1 To 20
Cells(I + 2, 9) = minday(I)
Cells(I + 2, 10) = prev(I)
Next
Call DisplayMinimumPath
End Sub
Sub CalculateMinimum2(ByVal current_id, ByVal current_day, ByVal a, ByRef minday, ByRef prev)
For I = current_id To 20
If a(I, current_id) <> 0 Then
tmp = current_day
current_day = current_day + a(I, current_id)
If minday(I) > current_day Then
minday(I) = current_day
prev(I) = current_id
End If
Call CalculateMinimum2(I, current_day, a, minday, prev)
current_day = tmp
End If
Next
End Sub
CalculateMinimumのなかで、DisplayMinimumPathを呼び出して実行
12.バックナンバー
13.ExcelVBA講座ご紹介
この記事が気に入ったらサポートをしてみませんか?