平成30年度春期基本情報技術者_表計算のマクロをExcelVBAで再現する
目次
1.出典
2.問題で使用するExcelシート「会議室選定」「予約リスト」のダミーデータの簡単な作り方
3.問題文の要旨
4.問題文(疑似言語)
5.解答群(疑似言語)
6.問題文(VBA翻訳)
7.VBA翻訳の補足説明
8.解答群(VBA翻訳)
9.正解
10.正解を入れたVBA完全版 ※動作確認済み
11.WorksheetFunctionを使わないバージョン ※動作確認済み
12.バックナンバー
13.ExcelVBA講座ご紹介
1.出典
経済産業省国家試験(IPA情報処理推進機構)
平成30年度春期 基本情報技術者試験 午後試験 選択問題 問13表計算設問2[解答欄d~f]
なお、試験問題及び正解の著作権はIPA情報処理推進機構に帰属します。
2.問題で使用するExcelシート「会議室選定」「予約リスト」のダミーデータの簡単な作り方
シート「会議室選定」
・入力は上記の通り。B~E列はマクロとは無関係なので空白でも良い。
・シート「会議室選定」のほうを先に作っておく。
シート「予約リスト」
・セルB2:20180415
・セルB3以降:=B2+INT(RAND()*1.2)
・セルC2以降:=INT(RAND()*8+9)*100
・セルD2以降:=C3+INT(RAND()+2)*100+INT(RAND()*1.3)*30
・セルG2以降:=INDEX(会議室選定!$A$2:$A$16,INT(RAND()*15)+1)
・A、E、F列はマクロとは無関係なので空白でも良い。また予約の時間帯が被る可能性があるが、マクロの実行に影響はない。
・ただし、B、C、D、G列はRand()を使ってダミーのデータを入力しているので値の貼り付けをして数式を消しておくこと。
3.問題文の要旨
ワークシート「会議室選定」のセル範囲A19:E19に条件を入力して、マクロSelectRoomを実行する(実はD19:E19はマクロと無関係)。
会議室の使用可否は、ワークシート「予約リスト」に格納されている予約の一覧を参照し、会議室ごとに、条件に指定した利用日の開始時刻から終了時刻までの間(開始時刻と終了時刻は含まない)に他の予約が入っていない場合は「可」、そうでない場合は「否」とする。マクロを用いてセルD2~D16を求めたい。ただし、予約リストは常に利用日の昇順になっている。
4.問題文(疑似言語)
[マクロ:Select_Room]
〇マクロ:Select_Room
〇数値型:I, J, NumRoom
・NumRoom ← 15
■ I: 1, I <= NumRoom, 1
|・ 相対(D1, I, 0) ← '可'
■
▲ 条件付個数(予約リスト!B2:B10000, = A19) > 0
|・J ← 照合一致(A19, 予約リスト!B2:B10000, 0)
|■
||▲ 否定([ 解答欄d ])
|||・ [ 解答欄e ] ← '否'
||▼
||・J ← J + 1
|■ [ 解答欄f ]
▼
5.解答群(疑似言語)
dに関する解答群
ア 論理積(表引き(予約リスト!C2:C10000, J, 1) >= C19, 表引き(予約リスト!D2:D10000, J, 1) <= B19)
イ 論理積(表引き(予約リスト!C2:C10000, J, 1) <= C19, 表引き(予約リスト!D2:D10000, J, 1) >= B19)
ウ 論理積(表引き(予約リスト!D2:D10000, J, 1) >= C19, 表引き(予約リスト!C2:C10000, J, 1) <= B19)
エ 論理積(表引き(予約リスト!D2:D10000, J, 1) <= C19, 表引き(予約リスト!C2:C10000, J, 1) >= B19)
オ 論理和(表引き(予約リスト!C2:C10000, J, 1) >= C19, 表引き(予約リスト!D2:D10000, J, 1) <= B19)
カ 論理和(表引き(予約リスト!C2:C10000, J, 1) <= C19, 表引き(予約リスト!D2:D10000, J, 1) >= B19)
キ 論理和(表引き(予約リスト!D2:D10000, J, 1) >= C19, 表引き(予約リスト!C2:C10000, J, 1) <= B19)
ク 論理和(表引き(予約リスト!D2:D10000, J, 1) <= C19, 表引き(予約リスト!C2:C10000, J, 1) >= B19)
eに関する解答群
ア 相対(D2, J - 1, 0)
イ 相対(D2, J, 0)
ウ 相対(D2, 照合一致(表引き(予約リスト!G2:G10000, J, 1), A2:A16, 0) - 1, 0)
エ 相対(D2, 照合一致(表引き(予約リスト!G2:G10000, J, 1), 予約リスト!G2:G10000, 0) - 1, 0)
オ 相対(D2, 照合一致(予約リスト!G2, A2:A16, 0) - 1, 0)
カ 相対(D2, 照合一致(予約リスト!G2, A2:A16, 1) - 1, 0)
fに関する解答群
ア 表引き(予約リスト!B2:B10000, J, 1) = A19
イ 表引き(予約リスト!B2:B10000, J, 1) = null
ウ 表引き(予約リスト!B2:B10000, J, 1) <> A19
エ 表引き(予約リスト!B2:B10000, J, 1) <> null
オ 表引き(予約リスト!C2:C10000, J, 1) < B19
カ 表引き(予約リスト!D2:D10000, J, 1) > C19
6.問題文(VBA翻訳)
Sub Select_Room()
Dim I As Integer, J As Integer, NumRoom As Integer
NumRoom = 15
For I = 1 To NumRoom
Range("D1").Offset(I, 0) = "可"
Next
If WorksheetFunction.CountIf(Sheets("予約リスト").Range("B2:B10000"), "=" & Range("A19")) > 0 Then
J = WorksheetFunction.Match(Range("A19"), Sheets("予約リスト").Range("B2:B10000"), 0)
Do
If Not ([ 解答欄d ]) Then
[ 解答欄e ] = "否"
End If
J = J + 1
Loop While [ 解答欄f ]
End If
End Sub
7.VBA翻訳の補足説明
条件付個数CountIf 表引きIndex 照合一致Match 相対Offset
8.解答群(VBA翻訳)
dに関する解答群
ア WorksheetFunction.Index(Sheets("予約リスト").Range("C2:C10000"), J, 1) >= Range("C19") And WorksheetFunction.Index(Sheets("予約リスト").Range("D2:D10000"), J, 1) <= Range("B19")
イ WorksheetFunction.Index(Sheets("予約リスト").Range("C2:C10000"), J, 1) <= Range("C19") And WorksheetFunction.Index(Sheets("予約リスト").Range("D2:D10000"), J, 1) >= Range("B19")
ウ WorksheetFunction.Index(Sheets("予約リスト").Range("D2:D10000"), J, 1) >= Range("C19") And WorksheetFunction.Index(Sheets("予約リスト").Range("C2:C10000"), J, 1) <= Range("B19")
エ WorksheetFunction.Index(Sheets("予約リスト").Range("D2:D10000"), J, 1) <= Range("C19") And WorksheetFunction.Index(Sheets("予約リスト").Range("C2:C10000"), J, 1) >= Range("B19")
オ WorksheetFunction.Index(Sheets("予約リスト").Range("C2:C10000"), J, 1) >= Range("C19") Or WorksheetFunction.Index(Sheets("予約リスト").Range("D2:D10000"), J, 1) <= Range("B19")
カ WorksheetFunction.Index(Sheets("予約リスト").Range("C2:C10000"), J, 1) <= Range("C19") Or WorksheetFunction.Index(Sheets("予約リスト").Range("D2:D10000"), J, 1) >= Range("B19")
キ WorksheetFunction.Index(Sheets("予約リスト").Range("D2:D10000"), J, 1) >= Range("C19") Or WorksheetFunction.Index(Sheets("予約リスト").Range("C2:C10000"), J, 1) <= Range("B19")
ク WorksheetFunction.Index(Sheets("予約リスト").Range("D2:D10000"), J, 1) <= Range("C19") Or WorksheetFunction.Index(Sheets("予約リスト").Range("C2:C10000"), J, 1) >= Range("B19")
eに関する解答群
ア Range("D2").Offset(J - 1, 0)
イ Range("D2").Offset(J, 0)
ウ Range("D2").Offset(WorksheetFunction.Match(WorksheetFunction.Index(Sheets("予約リスト").Range("G2:G10000"), J, 1), Range("A2:A16"), 0) - 1, 0)
エ Range("D2").Offset(WorksheetFunction.Match(WorksheetFunction.Index(Sheets("予約リスト").Range("G2:G10000"), J, 1), Sheets("予約リスト").Range("G2:G10000"), 0) - 1, 0)
オ Range("D2").Offset(WorksheetFunction.Match(Sheets("予約リスト").Range("G2"), Range("A2:A16"), 0) - 1, 0)
カ Range("D2").Offset(WorksheetFunction.Match(Sheets("予約リスト").Range("G2"), Range("A2:A16"), 1) - 1, 0)
fに関する解答群
ア WorksheetFunction.Index(Sheets("予約リスト").Range("B2:B10000"), J, 1) = Range("A19")
イ WorksheetFunction.Index(Sheets("予約リスト").Range("B2:B10000"), J, 1) = ""
ウ WorksheetFunction.Index(Sheets("予約リスト").Range("B2:B10000"), J, 1) <> Range("A19")
エ WorksheetFunction.Index(Sheets("予約リスト").Range("B2:B10000"), J, 1) <> ""
オ WorksheetFunction.Index(Sheets("予約リスト").Range("C2:C10000"), J, 1) < Range("B19")
カ WorksheetFunction.Index(Sheets("予約リスト").Range("D2:D10000"), J, 1) > Range("C19")
9.正解
d オ
否定(C19より後から始まっている or B19より前に終了している)
e ウ
G列の会議室の番号から、会議室選定のA列の番号を探して行番号を求める
f ア
B列の利用日がA19である間ループする
10.正解を入れたVBA完全版 ※動作確認済み
Sub Select_Room()
Dim I As Integer, J As Integer, NumRoom As Integer
NumRoom = 15
For I = 1 To NumRoom
Range("D1").Offset(I, 0) = "可"
Next
If WorksheetFunction.CountIf(Sheets("予約リスト").Range("B2:B10000"), "=" & Range("A19")) > 0 Then
J = WorksheetFunction.Match(Range("A19"), Sheets("予約リスト").Range("B2:B10000"), 0)
Do
If Not (WorksheetFunction.Index(Sheets("予約リスト").Range("C2:C10000"), J, 1) >= Range("C19") Or WorksheetFunction.Index(Sheets("予約リスト").Range("D2:D10000"), J, 1) <= Range("B19")) Then
Range("D2").Offset(WorksheetFunction.Match(WorksheetFunction.Index(Sheets("予約リスト").Range("G2:G10000"), J, 1), Range("A2:A16"), 0) - 1, 0) = "否"
End If
J = J + 1
Loop While WorksheetFunction.Index(Sheets("予約リスト").Range("B2:B10000"), J, 1) = Range("A19")
End If
End Sub
11.WorksheetFunctionを使わないバージョン ※動作確認済み
問題の趣旨(出題意図)を変えないまま、関数を使わない形に置き換えてみました。IndexはCellsに変えることができます。
Sub Select_Room()
Dim ws As Worksheet
Set ws = Sheets("予約リスト")
a19 = Range("A19")
b19 = Range("B19")
c19 = Range("C19")
NumRoom = 15
For I = 1 To NumRoom
Range("D1").Offset(I, 0) = "可"
Next
J = 0
For I = 2 To 10000
If ws.Cells(I, 2) = a19 Then
J = I
Exit For
End If
Next
If J > 0 Then
Do
If Not (ws.Cells(J, 3) >= c19 Or ws.Cells(J, 4) <= b19) Then
For I = 2 To 16
If Cells(I, 1) = ws.Cells(J, 7) Then
Cells(I, 4) = "否"
End If
Next
End If
J = J + 1
Loop While ws.Cells(J, 2) = a19
End If
End Sub
12.バックナンバー
13.ExcelVBA講座ご紹介
この記事が気に入ったらサポートをしてみませんか?