테스트 결과표 정리 매크로

업무설명: 

  - 프로젝트 수행 과정에서 개발이 완료되면 테스트를 수행하게 됩니다. 

테스트를 수행하면 테스트 결과를 정리해야 하는데 보통 여러 군데서 수행한 테스트 결과서를 모아서 하나의 결과서에 취합하는 방식으로 진행합니다. 

이 결과서의 내용을 분석하여 업무별로, 심각도 별로, 형태별로, 대응상태별로 결과표를 작성하는 매크로입니다. 

이 매크로를 사용하는 매크로 파일에는 

진척관리시트, mainData 시트가 있어야 합니다. 

테스트결과표 파일에는 faultData 시트가 있어야 합니다. 

결과는 severe_Level(결함의 심각성 정도), faultType(결함의 형태), presentStatus(현재 조치 상태)를 구분해서 관리해야 합니다. 

관리하는 통합테스트결과서의 양식에 맞춰 아래의 매크로를 적당히 수정해서 사용하면 됩니다. 

Sub getJobFile()
    'get files
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    Dim vrtSelectedItem As Variant
   
    Cells(3, 1) = ""
    With fd
        .InitialFileName = "d:\temp\"
        '만약 취소하면 종료처리한다.
        If .Show = 0 Then
            Cells(3, 1) = "작업을 취소하였습니다."
            Cells(3, 1).Font.Color = RGB(240, 0, 0)
            Cells(3, 1).Font.Bold = True
            Exit Sub
        Else
            sPath = .SelectedItems(1)
            tPath = .InitialFileName
           
        End If
    End With
    Set fd = Nothing
   
    Cells(6, 2) = tPath
    Cells(8, 2) = tPath
   
    Set fs = CreateObject("Scripting.FileSystemObject")
    fPath = "D:\temp\jobFiles\"
    tPath = "D:\temp\jobFiles_back\"
   
    Set dDir = fs.getfile(sPath)
    sFName = fs.getfilename(sPath)
    Cells(8, 3) = sFName
   
    '아래 부분은 파일에서 작업일을 가져오는 부분
'    chkpos = Right(sFName, 11)
'    Cells(4, 9) = CDate("20" & Mid(chkpos, 1, 2) & "-" & Mid(chkpos, 3, 2) & "-" & Mid(chkpos, 5, 2))
    '연습용이므로 오늘에서 하루 뺀걸로 그냥 설정
    Cells(4, 9) = Date - 1

   
End Sub
Sub getAndAnalysis()
    Cells(1, 8) = Time
    extractResult
    severeAnalysis
    typeAnalysis
    statAnalysis
    Cells(1, 9) = Time
End Sub

Sub extractResult()

    Dim i As Integer
    Dim rowCnt As Integer
    Dim shtCnt As Integer
    Dim testCnt As Integer
   
    Dim d_name As String
    Dim f_name As String
    Dim file_name As String
   
    '기본 변수 할당 및 파일명 읽어오기
    i = 8
    orgWorkBookName = ActiveWorkbook.Name
   
    '기존 자료 정리
    '작업을 위한 첫 시트는 진척관리
    '두번째시트는 mainData
    Sheets("mainData").Activate
    Range(Cells(3, 2), Cells(20000, 30)).Clear
   
    '파일 가져오기
    '하나의 파일만 처리하는 형태임
    d_name = Sheets("진척관리").Cells(i, 2).Value
    f_name = Sheets("진척관리").Cells(i, 3).Value
   
    file_name = d_name + f_name
   
    '시작
    Dim fs, f, s
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.getfile(file_name)
   
    '파일열기
    Workbooks.Open Filename:=file_name, UpdateLinks:=0
    Workbooks(f_name).Activate
   
    '시트수를 확인하는 건데 이 프로시져에서는 사용하지 않는다
    shtCnt = Workbooks(f_name).Sheets.Count
   
    Sheets("faultData").Select
    afCheck = Worksheets("faultData").AutoFilterMode
   
    If afCheck = True Then
        Range(Cells(1, 1), Cells(100, 3000)).AutoFilter
    End If
   
    Range(Cells(1, 1), Cells(20000, 30)).Select
    Selection.Copy
   
    Workbooks(orgWorkBookName).Activate
    Sheets("mainData").Select
   
    Cells(5, 4).Select
    ActiveSheet.Paste
   
    Cells(3, 4) = "=counta(E6:E200000)"
   
    '작업후번호
    testCnt = Cells(3, 4)
    Cells(5, 3) = "번호"
    Cells(6, 3) = 1
    Cells(7, 3) = 2
    Range("C6:C7").Select
    Selection.AutoFill Destination:=Range(Cells(6, 3), Cells(testCnt + 5, 3))
   
    Range(Cells(1, 1), Cells(20000, 30)).Select
    Selection.UnMerge
   
    '파일닫기
    Application.DisplayAlerts = False
    Workbooks(f_name).Close SaveChanges:=False
   
    Sheets("mainData").Select
   
End Sub

Sub severeAnalysis()

    Sheets("진척관리").Select
   
    stdDate = Cells(4, 8)
       
    Range(Cells(6, 7), Cells(20000, 30)).Clear
    Cells(4, 9) = "심각도"
    
    Cells(4, 7) = "=counta(h6:h20000)"
   
    Sheets("mainData").Select
    '자동필터를 사용해야 하는데 이미 있으면 오류가 나니 체크해서 있으면 없앤다
    afCheck = Worksheets("mainData").AutoFilterMode
    If afCheck = True Then
        Range(Cells(1, 1), Cells(100, 3000)).AutoFilter
    End If
   
    '다시 자동필터 설정
    Range(Cells(5, 3), Cells(5, 23)).Select
    Selection.AutoFilter
   
   
    ActiveWorkbook.Worksheets("mainData").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("mainData").AutoFilter.Sort.SortFields.Add Key:=Range("E5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("mainData").AutoFilter.Sort .Apply
   
    ActiveWorkbook.Worksheets("mainData").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("mainData").AutoFilter.Sort.SortFields.Add Key:=Range("D5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("mainData").AutoFilter.Sort .Apply
   
    Range(Cells(6, 7), Cells(20000, 7)).Select
    Selection.Copy
    Sheets("진척관리").Select
    Cells(6, 8).Select
    ActiveSheet.Paste
   
    Sheets("mainData").Select
    Range(Cells(6, 9), Cells(20000, 9)).Select
    Selection.Copy
    Sheets("진척관리").Select
    Cells(6, 9).Select
    ActiveSheet.Paste
    '중복값 제거
    ActiveSheet.Range("$H$6:$I$20000").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
   
    Sheets("mainData").Select
    Range(Cells(6, 12), Cells(20000, 12)).Select
    Selection.Copy
    Sheets("진척관리").Select
    Cells(6, 10).Select
    ActiveSheet.Paste
   
    '중복값 제거
    ActiveSheet.Range("$J$6:$J$20000").RemoveDuplicates Columns:=Array(1), Header:=xlYes
       
    Cells(4, 10) = "=counta(j6:j30)"
   
    faultCnt = Cells(4, 10)
   
    Range(Cells(6, 10), Cells(6 + faultCnt, 10)).Select
    Selection.Copy
   
    Cells(5, 10).Select
   
    Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, SkipBlanks:=False, Transpose:=True
   
    Cells(4, 8) = "=counta(H6:H20000)"
    rowCnt = Cells(4, 8)
    Cells(4, 10) = "=cells(j5:az5)"
    faultCnt = Cells(4, 10)
    Cells(5, faultCnt + 10) = "합계"
   
   
    i = 6
    Do While i < rowCnt + 15
   
        Sheets("진척관리").Select
        sysName = Cells(i, 8)
        If sysName = "" Then
            Exit Do
        End If
        dtlName = Cells(i, 9)
        allPtFaultCnt = 0
       
        j = 10
        '심각도 하나씩
        '좌에서 우로 하나씩 처리
        Do While j < falutCnt + 10
            faultName = Cells(5, j)
           
            Sheets("mainData").Select
            recordCnt = Cells(3, 4)
            ptFaultCnt = 0
            k = 6
            Do While k < recordCnt + 6
                cmpSysName = Cells(k, 7)
                cmpDtlName = Cells(k, 9)
                cmpFaultName = Cells(k, 12)
               
                If cmpSysName = "" Then
                    Exit Do
                End If
               
                If sysName = cmpSysName And dtlName = cmpDtlName And faultName = cmpFaultName Then
                    '테스트 결과
                    ptFaultCnt = ptFaultCnt + 1
                    allPtFaultCnt = allPtFaultCnt + 1
                End If
               
                k = k + 1
            Loop
            Sheets("진척관리").Select
            Cells(i, j) = ptFaultCnt
           
            j = j + 1
           
        Loop
            Cells(i, j).Select
            Cells(i, j) = allPtFaultCnt
            i = i + 1
    Loop
           
    sumRowPsn = Cells(4, 8) + 6
    typeCnt = Cells(4, 10) + 10
   
    Cells(sumRowPsn, 8) = "합계"
    i = 10
    '옆으로 가면서 합계 넣기
    Do While i < typeCnt + 1
        j = 6
        faultSum = 0
        Do While j < sumRowPsn
            faultVal = Cells(i, j)
            faultSum = faultSum + faultVal
           
            j = j + 1
        Loop
        Cells(i, j) = faultSum
        i = i + 1
       
    Loop
       
    Cells(3, 4).Select
    
End Sub
 
Sub faultAnalysis()
    Sheets("진척관리").Select
       
    '작업위치를 잡는다.
    '위에서 7번째 줄에서부터 데이터가 있다.
    delPnt = Cells(4, 8) + 7
       
    Range(Cells(delPnt, 7), Cells(20000, 30)).Select
    Range(Cells(delPnt, 7), Cells(20000, 30)).Clear
   
    Cells(delPnt + 2, 8).Select
    '상대참조를 해야 할 경우 사용한다.
    ActiveCell.FormulaR1C1 = "=Counta(R[2]c:r[20000]c)"
    Cells(delPnt + 2, 9) = "fault유형"
   
    Sheets("mainData").Select
   
    Range(Cells(6, 7), Cells(20000, 7)).Select
   
    Selection.Copy
   
    Sheets("진척관리").Select
    Cells(delPnt + 3, 8).Select
    ActiveSheet.Paste
   
    Sheets("mainData").Select
    Range(Cells(5, 9), Cells(20000, 9)).Select
   
    Selection.Copy
   
    Sheets("진척관리").Select
    Cells(delPnt + 3, 9).Select
    ActiveSheet.Paste
   
    delPnt0 = delPnt
   
    Cells(delPnt + 2, 8).Select
    ActiveCell.FormulaR1C1 = "=Counta(R[2]c:r[20000]c)"
    delPnt = Cells(delPnt + 2, 8)
   
    '중복값 제거
    ActiveSheet.Range(Cells(delPnt0 + 4, 8), Cells(delPnt + delPnt0 + 4, 9)).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
   
    Cells(delPnt0 + 2, 8).Select
    ActiveCell.FormulaR1C1 = "=Counta(R[2]c:r[20000]c)"
       
    'fault타입 처리
    Sheets("mainData").Select
    Range(Cells(6, 13), Cells(20000, 13)).Select
   
    Selection.Copy
   
    Sheets("진척관리").Select
    Cells(delPnt0 + 4, 10).Select
    ActiveSheet.Paste
       
   
    '중복값 제거
    Range(Cells(delPnt0 + 4, 10), Cells(delPnt + delPnt0 + 3, 10)).Select
    '1
    ActiveSheet.Range(Cells(delPnt0 + 4, 10), Cells(delPnt + delPnt0 + 3, 10)).RemoveDuplicates Columns:=Array(1), Header:=xlNo
    Cells(delPnt0 + 2, 10).Select
    ActiveCell.FormulaR1C1 = "=Counta(R[2]c:r[40]c)"
    '2
    faultCnt = Cells(delPnt0 + 3, 10)
    Range(Cells(delPnt0 + 4, 10), Cells(delPnt + 40, 10)).Select
    Selection.Copy
    '3
    Cells(delPnt0 + 3, 10).Select
    Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, SkipBlanks:=False, Transpose:=True
   
    '4
    Cells(delPnt0 + 2, 10).Select
    ActiveCell.FormulaR1C1 = "=Counta(R[1]c:r[40]c)"
    faultCnt = Cells(delPnt0 + 2, 10)
   
    Cells(delPnt0 + 3, faultCnt + 10) = "합격"
   
    rowCnt = Cells(delPnt0 + 2, 8)
   
    '5
    i = delPnt0 + 4
    Do While i < rowCnt + delPnt0 + 15
   
        Sheets("진척관리").Select
        sysName = Cells(i, 8)
        If sysName = "" Then
            Exit Do
        End If
        dtlName = Cells(i, 9)
        allPtFaultCnt = 0
       
        j = 10
        '좌에서 우로 하나씩 처리
        Do While j < falutCnt + 10
            faultName = Cells(delPnt0 + 3, j)
            If faultName = "" Then
                Exit Do
            End If
           
            Sheets("mainData").Select
            recordCnt = Cells(3, 4)
            ptFaultCnt = 0
            k = 6
            Do While k < recordCnt + 6
                cmpSysName = Cells(k, 7)
                cmpDtlName = Cells(k, 9)
                cmpFaultName = Cells(k, 13)
               
                If cmpSysName = "" Then
                    Exit Do
                End If
               
                If sysName = cmpSysName And dtlName = cmpDtlName And faultName = cmpFaultName Then
                    '테스트 결과
                    ptFaultCnt = ptFaultCnt + 1
                    allPtFaultCnt = allPtFaultCnt + 1
                End If
               
                k = k + 1
            Loop
            Sheets("진척관리").Select
            Cells(i, j) = ptFaultCnt
           
            j = j + 1
           
        Loop
            Cells(i, j).Select
            Cells(i, j) = allPtFaultCnt
            i = i + 1
    Loop
           
    sysItemCnt = Cells(delPnt0 + 2, 8)
    sumRowPsn = delPnt0 + sysItemCnt + 4
   
    typeCnt = Cells(delPnt0 + 2, 10) + 10
   
    Cells(sumRowPsn, 8) = "합계"
    i = 10
    '옆으로 가면서 합계 넣기
    Do While i < typeCnt + 1
        j = delPnt0 + 4
        faultSum = 0
        Do While j < sumRowPsn
            faultVal = Cells(j, i)
            faultSum = faultSum + faultVal
           
            j = j + 1
        Loop
        Cells(j, i) = faultSum
        i = i + 1
       
    Loop
       
    Cells(3, 4).Select
    
End Sub
 

 

Sub statAnalysis()
    Sheets("진척관리").Select
       
    '작업위치를 잡는다.
    '위에서 7번째 줄에서부터 데이터가 있다.
    delPnt = Cells(4, 8) + 7
       
    Range(Cells(delPnt, 7), Cells(20000, 30)).Select
    Range(Cells(delPnt, 7), Cells(20000, 30)).Clear
   
    Cells(delPnt + 2, 8).Select
    '상대참조를 해야 할 경우 사용한다.
    ActiveCell.FormulaR1C1 = "=Counta(R[2]c:r[20000]c)"
    Cells(delPnt + 2, 9) = "현재상태"
   
    Sheets("mainData").Select
   
    Range(Cells(6, 7), Cells(20000, 7)).Select
   
    Selection.Copy
   
    Sheets("진척관리").Select
    Cells(delPnt + 3, 8).Select
    ActiveSheet.Paste
   
    Sheets("mainData").Select
    Range(Cells(5, 9), Cells(20000, 9)).Select
   
    Selection.Copy
   
    Sheets("진척관리").Select
    Cells(delPnt + 3, 9).Select
    ActiveSheet.Paste
   
    delPnt0 = delPnt
   
    Cells(delPnt + 2, 8).Select
    ActiveCell.FormulaR1C1 = "=Counta(R[2]c:r[20000]c)"
    delPnt = Cells(delPnt + 2, 8)
   
    '중복값 제거
    ActiveSheet.Range(Cells(delPnt0 + 4, 8), Cells(delPnt + delPnt0 + 4, 9)).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
   
    Cells(delPnt0 + 2, 8).Select
    ActiveCell.FormulaR1C1 = "=Counta(R[2]c:r[20000]c)"
       
    'fault타입 처리
    Sheets("mainData").Select
    Range(Cells(6, 4), Cells(20000, 4)).Select
   
    Selection.Copy
   
    Sheets("진척관리").Select
    Cells(delPnt0 + 4, 10).Select
    ActiveSheet.Paste
       
   
    '중복값 제거
    Range(Cells(delPnt0 + 4, 10), Cells(delPnt + delPnt0 + 3, 10)).Select
    '1
    ActiveSheet.Range(Cells(delPnt0 + 4, 10), Cells(delPnt + delPnt0 + 3, 10)).RemoveDuplicates Columns:=Array(1), Header:=xlNo
    Cells(delPnt0 + 2, 10).Select
    ActiveCell.FormulaR1C1 = "=Counta(R[2]c:r[40]c)"
    '2
    faultCnt = Cells(delPnt0 + 3, 10)
    Range(Cells(delPnt0 + 4, 10), Cells(delPnt + 40, 10)).Select
    Selection.Copy
    '3
    Cells(delPnt0 + 3, 10).Select
    Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, SkipBlanks:=False, Transpose:=True
   
    '4
    Cells(delPnt0 + 2, 10).Select
    ActiveCell.FormulaR1C1 = "=Counta(R[1]c:r[40]c)"
    faultCnt = Cells(delPnt0 + 2, 10)
   
    Cells(delPnt0 + 3, faultCnt + 10) = "합격"
   
    rowCnt = Cells(delPnt0 + 2, 8)
   
    '5
    i = delPnt0 + 4
    Do While i < rowCnt + delPnt0 + 15
   
        Sheets("진척관리").Select
        sysName = Cells(i, 8)
        If sysName = "" Then
            Exit Do
        End If
        dtlName = Cells(i, 9)
        allPtFaultCnt = 0
       
        j = 10
        '좌에서 우로 하나씩 처리
        Do While j < falutCnt + 10
            faultName = Cells(delPnt0 + 3, j)
            If faultName = "" Then
                Exit Do
            End If
           
            Sheets("mainData").Select
            recordCnt = Cells(3, 4)
            ptFaultCnt = 0
            k = 6
            Do While k < recordCnt + 6
                cmpSysName = Cells(k, 7)
                cmpDtlName = Cells(k, 9)
                cmpFaultName = Cells(k, 4)
               
                If cmpSysName = "" Then
                    Exit Do
                End If
               
                If sysName = cmpSysName And dtlName = cmpDtlName And faultName = cmpFaultName Then
                    '테스트 결과
                    ptFaultCnt = ptFaultCnt + 1
                    allPtFaultCnt = allPtFaultCnt + 1
                End If
               
                k = k + 1
            Loop
            Sheets("진척관리").Select
            Cells(i, j) = ptFaultCnt
           
            j = j + 1
           
        Loop
            Cells(i, j).Select
            Cells(i, j) = allPtFaultCnt
            i = i + 1
    Loop
           
    sysItemCnt = Cells(delPnt0 + 2, 8)
    sumRowPsn = delPnt0 + sysItemCnt + 4
   
    typeCnt = Cells(delPnt0 + 2, 10) + 10
   
    Cells(sumRowPsn, 8) = "합계"
    i = 10
    '옆으로 가면서 합계 넣기
    Do While i < typeCnt + 1
        j = delPnt0 + 4
        faultSum = 0
        Do While j < sumRowPsn
            faultVal = Cells(j, i)
            faultSum = faultSum + faultVal
           
            j = j + 1
        Loop
        Cells(j, i) = faultSum
        i = i + 1
       
    Loop
       
    Cells(3, 4).Select
    
End Sub