[Excel] SUMIF 함수에 대한 재미있는 사실

지금 내 기분

 

회사에서 엑셀 작업을 검토하다가 SUMIF의 Range와 Sum_range가 잘못 지정되어 있는 것을 발견하였다. 워낙 많은 값이 뒤섞인 상태라 별 생각 없이 수정하고 넘어가려 했는데, 자세히 보니 뭔가 의문이 드는 점이 있어 예제를 만들어서 실험해 보았다.

 

먼저, 문제와 의문점은 아래와 같다.

 

아래는 구분이 D인 데이터의 금액 합계를 구하는 수식이다 (실제 회사 데이터를 정리한 예제).

Range는 7행에서부터 시작한 반면 Sum_range는 9행에서부터 시작하고 있다.

처음에는 D인 데이터의 합계를 구하고 있고 D는 정상적으로 범주에 포함되었으니 결과값이 맞을 거라고 생각하고, 수정하고 넘어가려고 했다.

그런데! 보니까 결과가 맞지 않다. 실제로는 Total이 45,000,000이어야 한다.

첫 오류 화면

그럼 한칸 위로 올리면 어떨까?

음..?

한 칸 위로 올렸더니 122,000,000이 나왔다.

맞게 적용된 수식

정확하게 Range와 Sum_range를 대치시키자 맞는 결과가 나왔다. 그렇다면, 처음에 나온 162,000,000과 122,000,000은 어떻게 해서 나온 것일까?

 

더 간단한 예시를 통해 알아보기로 했다.

 

먼저, 실제 발생했던 상황과 비슷한 예시를 만들어보았다.

간단한 예시

B를 다 더했는데 결과가 8이 나왔다. Range에서 B가 연속된 3행에 기입되어 있기 때문에, Sum_range에서도 연속된 3개 숫자로 8을 만들려면 2, 3, 3이 필요하다는 추측을 해볼 수 있다. 그렇다면 Range에서 4, 5, 6행에 대치되는 값을 Sum_range에서 6, 7, 8행에서 찾았다고 생각되어진다. 결국, Range와 Sum_range가 동일한 행에서부터 출발해서 동일한 행에서 끝난다면 같은 행의 데이터를 찾는 것인데, Sum_range가 2행 느리게 출발했기 때문에 Range에 비해 Sum_range의 행에 +2가 된 상태로 쌍이 만들어지는 것으로 추측할 수 있다. 맞는지 확인해 보자.

A와 C의 경우 맞는데 D는 다르다

추측한 대로 A의 Sum_range는 3, 4, 5행에서, C의 Sum_range는 9, 10, 11에서 찾았기 때문에 각각 5와 11이라는 결과를 출력하는 것을 알 수 있다. 여기까지는 생각이 맞았다. 그러나, D의 경우 4를 출력하고 있다. 왜 그럴까?

 

일단 출력값 4가 나오는 것을 몰랐다고 가정하고 생각을 해 보면, D가 어떻게 출력될 지 아래와 같이 몇가지 경우로 생각해 볼 수 있겠다.

1. D = 7 = 4+1+2

- 10번째 4를 찾고(엑셀의 12행) 11, 12번째에는 다시 Sum_range 배열의 첫번째로 돌아가서 1과 2를 더할 것이다.

2. D = 4 + 4 + 4 (11, 12행의 4가 아니라 10행의 4)

- 10번째에서 4를 찾고 그 다음이 없으니 마지막 값을 2번 더 더할 것이다. 

3. D = 4

- Range는 12개이지만 Sum_range는 9개이므로 반복문은 9까지만 돌고 종료된다. 즉, A11과 A12는 사용자가 범위에 설정했지만 실제로는 찾지 않는 값이다. 그래서 A10의 D를 찾고 그 결과가 B12의 4로 출력된다.

 

앞선 실험에 의해 D가 4를 출력하는 것을 알았기 때문에, 3번의 가설을 선택할 수 있다. 그럼 이 가설이 맞는지 확인하기 위해 몇 가지 실험을 더 해보자.

따란

 

확인을 위해 Range를 A10까지로 줄이고, B10, B11 셀에 100을 입력하였는데 결과값은 4가 나왔다. 이를 통해 A10의 D를 찾았고 그에 대응하는 값이 B12의 4임을 확인할 수 있다 (C는 203이 나오는 것도 확인할 수 있다).

 

이를 통해 SUMIF 함수가 값을 찾는 방식에 대해 이해하게 되었다. 

여기서 끝내도 되지만, 또 한 가지 실험을 더 해보려고 한다.

 

만약, Range보다 Sum_range의 범위가 더 좁다면?

결과는 똑같이 출력된다

첫 행만 지정하였음에도 결과는 동일하게 출력된다. 여기서 한 가지 더 확실하게 하기 위해 Range의 범위를 줄여보자.

B를 찾을 때

A를 찾을 때

 

Range가 Sum_range보다 넓은 범위일 때는 max가 Sum_range였지만, 그 반대일 때는 Range가 max가 되는 것을 확인할 수 있다.

 

즉, SUMIF 함수에서 Range의 범위(갯수)는 Sum_range보다 많을 수 없고(Range<=Sum_range, i=len(Range)), Sum_range는 첫 행만 정확하게 지정해 주면 된다는 것!

 

# SUMIF의 구조를 코드로 확인해 보기 위해 파이썬이나 VBA로 한번 만들어보고 싶은데, 그건 다음 기회에 하는 것으로..

# 나만 몰랐던 것일 수도 있고, 쓸모 없는 지식일 수 있지만 스스로 알아낸 게 너무 뿌듯해서 길가는 사람 아무나 붙잡고 설명해주고 싶은 마음이다.

# 조건이 1개만 있어도 SUMIFS, COUNTIFS를 쓰는 것이 좋은 듯 하다. 실수할 확률이 줄어드는 것 같은 플라시보 효과가 있다.