처리 조건이 여러 가지로 나뉘는 경우 대처법

방법 1: 다중 IF 문 사용

엑셀 초보 시절에 Sum이나 Count 함수에 이어서 접하게 되는 함수가 If 함수일 겁니다. If 함수를 여러 번 중첩해서 사용하면 되니까 이 정도는 문제도 아니지요. G5 셀에는 이런 수식이 입력되어 있습니다.

=IF(F5>=$C$5,1,IF(F5>=$C$6,2,IF(F5>=$C$7,3,IF(F5>=$C$8,4,
IF(F5>=$C$9,5,IF(F5>=$C$10,6,IF(F5>=$C$11,7,
IF(F5>=$C$12,8,9))))))))

엑셀 2007 이전 버전이라면 이 수식을 사용할 수 없습니다. 다중 If 문에서 If는 7번까지만 중첩해서 쓸 수 있기 때문입니다. 엑셀 2007 이후 버전부터는 64개까지 중첩해서 사용할 수 있게 되었습니다.

방법 2: INDEX와 MATCH 함수 조합

Index와 Match 함수의 조합을 통해서도 해결할 수 있습니다. 아래 표와 같이 수능 예상등급컷 표를 '점수' 항목을 오름차순으로 정리해 둔 다음 이것을 참조하여 수식을 작성합니다.

H5 셀에는 이런 수식이 입력되어 있습니다.

=INDEX($M$5:$M$13,MATCH(F5,$N$5:$N$13,1))

Match 함수의 match_type 인수를 1로 지정하면 "작거나 같은 값 중에서 최대값"을 찾아줍니다. 그러기 위해서는 참조 대상 테이블이 반드시 "오름차순 정렬"되어 있어야 합니다.

표에서 자신이 원하는 값을 찾고자 할 때 초보 사용자들은 일단 Vlookup 함수를 떠올립니다. 하지만 Vlookup 함수는 '찾을 값'(이 경우에는 '점수')의 왼쪽 영역에 있는 값('등급')은 가져올 수 없습니다.

방법 3: 수식에 이름을 정의하여 해결

이것을 뭐라고 부를까 생각하다가 '수식에 이름 정의를 통한 해결'이라고 해봅니다. 예제 파일의 I5 셀을 보면 '=IF(기준,조건1,조건2)'라고 하는 희안한 수식이 들어 있습니다.

'기준', '조건1', '조건2'라는 건 어디서 나타난 것일까요? 이것이 바로 '수식에 이름 정의하기'를 이용한 겁니다.

① [수식] 탭 - [정의된 이름] 그룹에서 [이름 정의]를 클릭합니다.

② [새 이름] 대화상자에서 '이름' 항목에는 '기준'이라고 입력하고, '참조 대상'에는 아래와 같은 수식을 입력한 다음 '확인' 버튼을 클릭합니다. 수식은 이해하시겠지요? 7번째 조건이 되는 C11 셀을 기준으로 조건을 둘로 나누기 위한 수식입니다.

=IF(연습!E5>=연습!$C$11,TRUE,FALSE)

③ 다시 한 번 [삽입] - [이름 정의]를 클릭하고 '조건1'이라는 이름을 입력합니다. 7번째 조건에 해당되는 부분을 기준으로 둘로 나누기 위한 수식입니다.

=IF(연습!E5>=연습!$C$5,1,IF(연습!E5>=연습!$C$6,2,IF(연습!E5>=연습!$C$7,3,
IF(연습!E5>=연습!$C$8,4,IF(연습!E5>=연습!$C$9,5,IF(연습!E5>=연습!$C$10,6,
IF(연습!E5>=연습!$C$11,7,FALSE)))))))

④ 마찬가지 방법으로 '조건2'라는 이름을 작성합니다.

=IF(연습!F14>=연습!$C$20,8,9)

⑤ I5 셀에 '=IF(기준,조건1,조건2)'라고 입력하고 수식을 아래로 복사하여 완성합니다.

방법 4: IFS 함수 이용

설치된 엑셀이 2019나 Office 365 버전이라면 Ifs 함수를 사용할 수 있습니다. 예제 파일의 J5 셀에는 Ifs 함수를 이용한 수식이 입력되어 있습니다.

=IFS(F5>=$C$5,$B$5,F5>=$C$6,$B$6,F5>=$C$7,$B$7,F5>=$C$8,$B$8,F5>=$C$9,$B$9,
F5>=$C$10,$B$10,F5>=$C$11,$B$11,F5>=$C$12,$B$12,TRUE,9)

Ifs 함수는 각각의 조건을 기준으로 수식을 나눠서 살펴보면 이해하기 쉽습니다.

=IFS(F5>=$C$5,$B$5, F5>=$C$6,$B$6, F5>=$C$7,$B$7,
F5>=$C$8,$B$8, F5>=$C$9,$B$9, F5>=$C$10,$B$10,
F5>=$C$11,$B$11, F5>=$C$12,$B$12, TRUE,9)

수식 마지막의 TRUE, 9라는 것은 앞에서 지정한 모든 조건을 충족하지 않는 값에는 '9'(9등급)를 표시하라는 의미이며, True 대신 1을 써도 됩니다. 만약 이 부분을 생략하면 조건을 충족하지 않는 값일 때 '#N/A' 오류 메시지가 나타납니다. 참고로, Ifs 함수에서 조건은 127개까지 지정할 수 있습니다.

방법 5: VBA 활용

① 워크시트에서 <Alt> + <F11> 키를 눌러서 Visual Basic Editor로 갑니다.

② [삽입] - [모듈] 메뉴를 클릭하여 모듈을 삽입하고 다음 코드를 붙여넣습니다.

Function GetGrade(iScore) As Integer
    Select Case iScore
        Case Is >= 92: GetGrade = 1
        Case Is >= 85: GetGrade = 2
        Case Is >= 76: GetGrade = 3
        Case Is >= 66: GetGrade = 4
        Case Is >= 55: GetGrade = 5
        Case Is >= 44: GetGrade = 6
        Case Is >= 30: GetGrade = 7
        Case Is >= 20: GetGrade = 8
        Case Else: GetGrade = 9
    End Select
End Function

③ K5 셀에 '=getgrade(f5)'라고 입력하고 수식을 아래로 복사합니다.

정리 — 다섯 가지 방법 비교

방법	특징	제약
1. 다중 IF	가장 직관적	2007 이전 7중첩 한도, 수식 길어짐
2. INDEX+MATCH	짧고 유지보수 쉬움	참조표 오름차순 정렬 필수
3. 이름 정의	셀 수식이 짧고 읽기 쉬움	이름 관리자에서 별도 확인 필요
4. IFS	중첩 없이 조건 나열, 최대 127개	엑셀 2019 이상 필요
5. VBA	일반 함수처럼 재사용 가능	매크로 사용 설정 필요

자주 묻는 질문 (FAQ)

마치며

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