수석과 꼴등 모두 표시하기

이 정도 데이터만 되더라도 눈으로 보고 해결하기란 쉽지가 않습니다. 데이터 정렬 기능을 이용하여 처리할 수 있긴 합니다만 원본 데이터 형태가 흐트러진다는 문제가 있습니다. 만약 데이터 형태를 그대로 둔 상태에서 처리해야 한다면 어떻게 해야 할까요?

100명의 이름과 점수를 무작위로 생성한 예제 데이터(Workplace 시트)를 대상으로, 공동 수석과 공동 꼴등을 모두 찾아 나열한 결과는 다음과 같은 모습이 됩니다(점수가 무작위로 생성되므로 실행할 때마다 동석자 수가 달라집니다).

공동 수석		공동 꼴등
이름	점수	이름	점수
HESDK	79	UHLNC	0
EMNOV	79

(더 이상 동점자가 없는 나머지 칸은 수식 결과가 #VALUE! 오류로 나오는데, '조건부 서식'을 이용해 화면에는 보이지 않도록 처리했습니다.)

오래간만에 배열 수식을 사용해 봅니다.

공동 수석의 이름을 알아내기 위해서 아래와 같은 수식을 사용하였습니다. B40:B49 영역을 범위로 지정한 다음 이 수식을 입력하고 <Ctrl+Shift+Enter>를 함께 눌러 마무리 합니다.

=INDEX(Workplace!$A$2:$A$101,SMALL(IF(MAX(Workplace!$B$2:$B$101)=Workplace!$B$2:$B$101,ROW(Workplace!$B$2:$B$101)-1,""),ROW()-A40))

공동 꼴등의 이름을 알아내기 위해서는 또 이런 수식을 사용했습니다. 다 똑같고 Max를 Min으로 바꿔 주었습니다. 역시나 D40:D49 영역을 범위로 지정한 다음, <Ctrl+Shift+Enter>를 함께 눌러주어야 합니다.

=INDEX(Workplace!$A$2:$A$101,SMALL(IF(MIN(Workplace!$B$2:$B$101)=Workplace!$B$2:$B$101,ROW(Workplace!$B$2:$B$101)-1,""),ROW()-A40))

점수의 경우에는 Vlookup 함수를 사용하였으므로 별도 설명은 생략합니다.

배열 수식에서 해당 값이 없을 때 생기는 오류 메시지를 처리하기 위해서 '조건부 서식'을 적용했습니다.

참, 예제 데이터를 만들기 위해 사용된 코드는 다음과 같습니다.

Sub MakeTable()
    Dim intRow As Integer
    Dim wrkSheet As Worksheet
    Set wrkSheet = Worksheets("Workplace")
    wrkSheet.Select
    
    With wrkSheet
        .Range("A1:B1") = Array("이름", "점수")
        
        For intRow = 1 To 100
            With .Range("A1")
                .Offset(intRow, 0) = RandomName(3)
                .Offset(intRow, 1) = Int(Rnd * 100)
            End With
        Next intRow
    
    End With
    
    MsgBox "예제 테이블을 다 만들었습니다. 순식간에... 그죠?", , "아이엑셀러 닷컴"
    Application.Goto Sheets("Preface").Range("A30"), True
    
End Sub

코드의 내용은 그냥 참고만 하세요. 관심있는 분들만…

다음 시간에…

정리 — 동점자 모두 찾기 배열 수식

목적	수식
공동 수석 이름	=INDEX($A$2:$A$101,SMALL(IF(MAX($B$2:$B$101)=$B$2:$B$101,ROW($B$2:$B$101)-1,""),ROW()-기준행)) (배열 수식)
공동 꼴등 이름	위 수식에서 MAX를 MIN으로 교체 (배열 수식)
해당 점수	VLOOKUP으로 찾은 이름의 점수 조회

자주 묻는 질문 (FAQ)

마치며

VBA에 대한 기초 지식을 공부하실 분은 아이엑셀러 닷컴 사이트 상단 메뉴에서 [Excel 강의] - [Excel 입문]을 먼저 보시면 이해하기 쉽습니다.