전체 명단에 없는 항목 나타내기

안녕하세요.

 

데이터 파일 없이 글로 올려진 질문에 답을 할 때는 고급 필터에 COUNTIF 함수로 조건을 설정하면 되는 작업으로 보였었습니다.

막상 메일로 받은 데이터 파일을 보니 생각했던 것과는 다른 모습이었습니다.

 

6시 근무자와 14시 근무자를 D열부터 R열에 적었고, S열부터 근무하지 않은 명단을 나타내는 것입니다.

전체 명단은 AA열과 AB열에 기록되어 있습니다.

한참 생각해 봤지만 함수나 엑셀 기능으로 나타낼 방법은 없어 보입니다.

 

방법은 VBA로 작성하는 것입니다.

 

사용자 지정 함수로 만들고 싶었지만 결과값이 한 셀에만 나타나는 것이 아니라 여러 셀에 나타나는 것이라 프로시저로 처리했습니다.

 

커서는 결과를 나타낼 셀 [S7]에 둡니다.

매크로를 실행하야 하는데, [보기]-[매크로]-[매크로 보기]를 선택하거나 단축키 <Alt + F8>을 누릅니다.

[매크로] 창에서 '없는항목나타내기' 매크로를 선택하고 [실행] 단추를 누릅니다.

이 매크로가 실행되기 위해선 세가지 정보가 필요한데, 첫번째는 결과를 나타낼 셀 위치입니다.

이것은 처음 커서를 둔 곳으로 동작하게 작성했습니다.

두번째는 근무자 명단입니다.

Application 개체 Inputbox 메서드를 이용해 셀 범위를 입력 받습니다.

 

'근무자 범위 입력' 대화상자에서 데이터 범위를 선택합니다.

마지막 세번째는 전체 직원 명단 범위입니다.

 

예제 파일에서는 고정되어 있지만 실제 사용할 파일에서는 셀 범위가 달라질 것이니 '전체명단 범위 입력' 대화상자에서 데이터 범위를 선택합니다.

결과가 나타납니다.

 

반복문 안에 반복문으로 생각나는 첫번째 방법으로 구현했습니다.

언제나처럼 입력이 제대로 되었는지 확인하지도 않고, 오류가 날 때를 대비한 코드는 하나도 없이 꼭 필요한 코딩만 해서 깔끔합니다.

물론 조금만 문제가 생겨도 동작하지 않는다면 문제가 있습니다.

대상 인원이 많지 않기 때문에 문제되지 않습니다.

 

전체명단에서 근무자 명단 이름을 하나하나 비교해서 있는 사람은 배열 두번째에 1을 입력해 둡니다.

전체명단에서 배열 두번째 값이 기본값인 0인 항목만 출력합니다.

Sub 없는항목나타내기()
    Dim 전체()
    
    Dim 근무자 As Range
    Dim 전체명단 As Range
    
    Set 근무자 = Application.InputBox(prompt:="근무자 범위를 선택하세요.", _
        Title:="근무자 범위 입력", Type:=8)
    Set 전체명단 = Application.InputBox(prompt:="전체명단 범위를 선택하세요.", _
        Title:="전체명단 범위 입력", Type:=8)
    
    결과행 = ActiveCell.Row
    결과열 = ActiveCell.Column
    
    총원수 = 전체명단.Count
    
    ReDim 전체(총원수, 2)
    i = 1
    
    For Each a In 전체명단
        전체(i, 1) = a.Value
        전체(i, 2) = 0
        i = i + 1
    Next a
    
    For Each a In 근무자
        For i = 1 To 총원수
            If a.Value = 전체(i, 1) Then
                전체(i, 2) = 1
                Exit For
            End If
        Next i
    Next a
    
    For i = 1 To 총원수
        If 전체(i, 2) = 0 Then
            Cells(결과행, 결과열).Value = 전체(i, 1)
            결과열 = 결과열 + 1
        End If
    Next i
End Sub

완성 파일 다운로드

없는항목나타내기.xls

0.01MB

 

사용자 지정 함수로 구현하려고 할 때 순환참조 오류가 나타났습니다.

수식을 작성하는 [S7] 셀 값을 바꾸기 때문입니다.

함수가 배열로 반환하면 문제없이 동작할 듯 한데 다음번에 기회가 되면 구현해볼 예정입니다.

 

도움되길 바랍니다.