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

2018年12月30日 15:52

１．出典
２．問題で使用するExcelシート「会議室選定」「予約リスト」のダミーデータの簡単な作り方
３．問題文の要旨
４．問題文（疑似言語）
５．解答群（疑似言語）
６．問題文（VBA翻訳）
７．VBA翻訳の補足説明
８．解答群（VBA翻訳）
９．正解
10．正解を入れたVBA完全版 ※動作確認済み
11．WorksheetFunctionを使わないバージョン ※動作確認済み
12．バックナンバー
13．ExcelVBA講座ご紹介

１．出典

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

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

２．問題で使用する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()を使ってダミーのデータを入力しているので値の貼り付けをして数式を消しておくこと。

３．問題文の要旨

ワークシート「会議室選定」のセル範囲A19:E19に条件を入力して、マクロSelectRoomを実行する（実はD19:E19はマクロと無関係）。

会議室の使用可否は、ワークシート「予約リスト」に格納されている予約の一覧を参照し、会議室ごとに、条件に指定した利用日の開始時刻から終了時刻までの間（開始時刻と終了時刻は含まない）に他の予約が入っていない場合は「可」、そうでない場合は「否」とする。マクロを用いてセルD2～D16を求めたい。ただし、予約リストは常に利用日の昇順になっている。

４．問題文（疑似言語）

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

５．解答群（疑似言語）

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

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

７．VBA翻訳の補足説明

条件付個数CountIf　表引きIndex　照合一致Match　相対Offset

８．解答群（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")

９．正解

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講座ご紹介

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