平成28年度秋期基本情報技術者_表計算のマクロをExcelVBAで再現する

2018年12月31日 15:24

１．出典
２．問題で使用するExcelシート「作業工程(拡張)」のダミーデータの簡単な作り方
３．問題文の要旨
４．問題文（疑似言語）
５．解答群（疑似言語）
６．問題文（VBA翻訳）
７．解答群（VBA翻訳）
８．正解
９．正解を入れたVBA完全版 ※動作確認済み
10．CalcurateMinimumを実行するためのExcelシート「状態遷移」、「作業工程(拡張)」のダミーデータの簡単な作り方
11．マクロCalculateMinimum（VBA） ※動作確認済み
12．バックナンバー
13．ExcelVBA講座ご紹介

１．出典

経済産業省国家試験（IPA情報処理推進機構）
平成28年度秋期基本情報技術者試験午後試験選択問題問13表計算設問２[解答欄d～f]

なお、試験問題及び正解の著作権はIPA情報処理推進機構に帰属します。

２．問題で使用するExcelシート「作業工程(拡張)」のダミーデータの簡単な作り方

・数値はすべて直接入力。
・空白のセルはマクロの実行と無関係なので空白で良い。

３．問題文の要旨

20個の状態があり、それぞれ状態ID：1～20の番号が振られている。それぞれ、２つの状態間（小さい番号のIDから大きい番号のIDへの遷移）は直接遷移できる場合とできない場合があって、直接遷移できる場合は遷移するのに必要な作業日数がある。先にマクロCalculateMinimumを実行すると、開始ID（セルF2）からセル範囲H3:H22の各状態へ遷移するための最小日数を計算する。ただし、直接または間接的に遷移できなければ日数＝9999、開始IDと一致する場合は日数＝0とする。遷移できる場合は、J列に最短日数で遷移した場合の直前の状態IDを表示する。

これをもとに、マクロDisplayMinimumPathで、セルA3以降に、開始ID（セルF2）から終了ID（セルF3）に至るまでの最短経路を表示したい。

４．問題文（疑似言語）

[マクロ：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 ]
｜■
▼

５．解答群（疑似言語）

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

６．問題文（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

７．解答群（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

８．正解

d　カ
最初にCurrentにセルF3（終了ID）を初期値として入れているのがポイント。ループのなかでCurrentを上書きしているので、ループでその上書きを何回か繰り返して最終的にCurrentがセルF2（開始ID）と一致すれば終了する（一致していないあいだ繰り返す）。
e　ア
終了IDから経路をさかのぼるためには、直前の状態（前ノードへのポインタ）をCurrentに入れる必要がある。照合検索を使う必要はない（相対で良い？）が、あえて使うのであればCurrentをH列で探して、J列の値を返せばよい。
f　カ
NumWork算出の時にCurrentを一次元配列に保存しておけばよいが、それをしないのであればもう一度さかのぼることになる。相対の開始セルがA3になっているのがポイント。遷移回数がNumWorkであれば、出力する行数はNumWork+1行になるので、IはNumWork～０となる。

９．正解を入れた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も作りました。

問題文の要旨

シート「状態遷移」は、２つの状態間が直接遷移できる場合は遷移にかかる作業日数を、直接遷移できない場合は０が入力されている。マクロを実行すると、シート「作業工程(拡張)」のセル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講座ご紹介

この記事が気に入ったらサポートをしてみませんか？