[엑셀VBA] 크로스테이블, IF 다중조건 VBA로 해결하기

간단한 VBA를 통해서 아주 복잡한 다중 IF문을 해결해보겠습니다.

 

[예시상황] 9월 영업실적에 따른 시상금 예시 

영업에 대한 인센티브를 다음과 같은 표 형태로 주고자 합니다.

큰 조건이 3개인 것이 보이나요?

① 실적구분 8월 실적 : 500만원 이상 / 300만원 이상 ~ 500만원 미만 / 그 외

② 제품구분 A : 5건 이상 / 3건 이상 ~ 5건 미만 / 그 외 

                       : 300만원 이상인 경우, 5건 이상 판매한 경우, 3건 이상 ~ 5건 미만 판매한 경우

③ 제품구분 B : 10건 이상 / 5건 이상 ~ 10건 미만 / 그 외

 

간단한 조건 3가지처럼 보이지만 결합할 경우 상당히 복잡합니다.

예를 들어, 500만원을 받기 위해서는 

 - 8월 실적이 500만원 이상인데, 전월 비 제품A는 +3건, 제품B는 +10건 인 경우입니다.

300만원 시상금을 예를 들어보면

- 8월 실적이 300만원 이상이고 500만원 미만인데, 전월 비 제품A는 +5건, 제품B는 + 10건 해야합니다. 

 

현재 만들고자 하는 엑셀형태는 다음과 같습니다. 

각 조건에 따라서 인센티브 지급금액을 넣어주는 것입니다.

뭔가 대단히 간단한 듯하면서도 VLOOKUP으로 하기는 쉽지 않아 보입니다.

(혹시 쉽게 할 수 있는 방법을 알고 계신 분은 댓글로 알려주시면 감사하겠습니다) 

 

함수 IF조건문으로 하면 어찌저찌 풀 수 있을 것 같지만 양이 방대해질 경우 굉장히 느리게 연산될 것 같습니다.

 

이럴 경우 VBA 반복문과 조건문을 통해 쉽게 해결할 수 있습니다. 

 

Sub Promotion()

Dim i As Long, cnt As Long

'cnt = Range("B2").CurrentRegion.Rows.Count '행의 수 세는 방법①
cnt = Range("B4").End(xlDown).Row '행의 수 세는 방법②

For i = 4 To cnt
    If Cells(i, "D") >= 5000000 Then '전월 비 500만원 이상인 경우
        
        If Cells(i, "H") >= 3 Then '제품A가 +3건 이상인 경우
        
            If Cells(i, "K") >= 10 Then '제품B가 +10건 이상인 경우
                Cells(i, "L") = 5000000
                
            ElseIf Cells(i, "K") >= 5 Then '제품B가 +5건 이상인 경우
                Cells(i, "L") = 4000000
            
            End If
        
        End If
        
    ElseIf Cells(i, "D") >= 3000000 Then '전월 비 300만원 이상 ~ 500만원 미만인 경우
    
        If Cells(i, "H") >= 5 Then '제품A가 +5건 이상인 경우
        
            If Cells(i, "K") >= 10 Then '제품B가 +10건 이상인 경우
                Cells(i, "L") = 3000000
            ElseIf Cells(i, "K") >= 5 Then '제품B가 +10건 이상인 경우
                Cells(i, "L") = 2000000
            End If
            
        ElseIf Cells(i, "H") >= 3 Then '제품A가 +3건 이상인 경우
            If Cells(i, "K") >= 10 Then '제품B가 +10건 이상인 경우
                Cells(i, "L") = 1500000
            ElseIf Cells(i, "K") >= 5 Then '제품B가 +10건 이상인 경우
                Cells(i, "L") = 1000000
            End If
        
        End If
    End If
Next

'빈 값은 0으로 만든다.
For i = 4 To cnt

    If Cells(i, "L") = vbNullString Then
        Cells(i, "L") = 0
    End If
    
Next​

 

전체적인 구조는 

1. 8월 실적은 500만원 이상인가? 

그렇지 않으면 300만원 ~ 500만원 미만인가?

그렇지 않은가

 

2. 제품 A는 5건 이상인가

그렇지 않으면 3건 이상 ~ 5건 이하인가

그렇지 않은가

 

3. 제품B는 10건 이상인가

그렇지 않으면 5건 이상 ~ 10건 미만인가

그렇지 않은가

 

의 순서로 정하였습니다. 

 

위의 코드가 복잡해보이는 것은 조건이 많아서입니다.

그래서 쉽게 조건문과 반복문을 배워보겠습니다.

 

함수로 쓴다면

=IF($E2>= 90, "A", IF($E2>= 80, "B", IF($E2>=70, "C", "F")))

으로 쓸 수 있을 것 같습니다.

 

이를 VBA로 구현하면 

Sub Test_result()


Dim i As Long, cnt As Long

cnt = Range("a1").End(xlDown).Row

For i = 2 To cnt
    If Cells(i, "E") >= 90 Then
        Cells(i, "F") = "A"
    ElseIf Cells(i, "E") >= 80 Then
        Cells(i, "F") = "B"
    ElseIf Cells(i, "E") >= 70 Then
        Cells(i, "F") = "C"
    Else
        Cells(i, "F") = "F"
    End If

Next

End Sub

 

으로  할 수 있습니다. 

 

함수와 동일한 구문은 VBA도 역시 IF문입니다.

    If Cells(i, "E") >= 90 Then
        Cells(i, "F") = "A"
    ElseIf Cells(i, "E") >= 80 Then
        Cells(i, "F") = "B"
    ElseIf Cells(i, "E") >= 70 Then
        Cells(i, "F") = "C"
    Else
        Cells(i, "F") = "F"
    End If​

IF문은 If ~ Then / Elseif ~then / Else / 마지막은 Endif를 써줘야 합니다

If ~ Then / End if는 필수이며, Elseif ~then / Else는 선택적으로 쓸 수 있습니다. 

 

위의 경우에 한 셀의 값만 구하는 게 아닌 F2 ~ F6까지 값을 구해야 하므로 For문을 적용하였습니다.

 

For 문의 경우

For i = Start to End

Next

로 사용하며 분홍색이 필수 구문입니다.

 

마지막으로 행을 구하는 방법인데,

Range("a1").End(xlDown).Row 을 해석하면

Range("a1") : a1셀을 클릭해서

End(xlDown) : Ctrl + 방향키 ↓ (맨 끝 방향으로 이동해)

Row : 행을 구해줘

라는 뜻입니다.

 

위의 경우에는 6행이 마지막이므로 6이 나옵니다.

 

그래서 결과적으로 시행하면

함수와 동일하게 값이 잘 나타납니다.

평균점수에 등급 매기는 것만 잘 공부하면 실무에서 쓰는 복잡한 매트릭스 표도 쉽게 구할 수 있을 것입니다

 

엑셀 자동화를 통해 시간을 아껴서 더 값진 곳에 쓰시길 바랍니다.

■ 예시 파일

다중if문_vba.xlsm

0.04MB