엑셀 | sumif, subtotal, sumifs, sumproduct, product

엑셀의 함수라는 명칭을 이해하는데 있어서 영어를 잘해서가 아니라 “함수"라는 용어보다는 영어로 function이라는 단어에서 더 느낌이 살아 있는 것 같다.

function이라고 하면 ‘어떠한 기능을 하는 것’이라는 느낌으로 다가오는데 함수라고 하면 한문에서 온 표현이라 그런지 오히려 더 거부감이 든다. 지금 와서 생각 해 보면 수학을 배우던 어린이 시절에 수학은 그 자체로도 충분히 어렵지만 용어가 일본어나 중국어를 인용하여 더 어렵게 느껴졌던 것 같다.

‘function은 어떠한 기능을 나 대신 빠르게 반복해 주는 고마운 도구’ 라고 생각하면 function에 대하여 거부하기보다 좋아할 수도 있을 것 같다.

엑셀 | vlookup 하지 마세요. (?)에서 index와 match 함수를 다루었고 엑셀 | if 함수에서 if함수를 다루고 엑셀 | sum 함수 - 한번 눌러서 실행하기에서 sum 함수를 빠르게 실행하기 위한 방법을 생각해 보았다.

오늘은 sum에서 조금 더 추가적인 기능을 하는 함수들에 대하여 기록해 보고자 한다.

sumif

sum과 if를 연속해서 쓰면 sumif 함수가 된다. sumif는 조건을 만족하는 범위의 합계를 구해주는 기능을 한다. 구문(Syntax)은 엑셀에 =sumif(를 쓰면 하단에 박스로 SUMIF(range, criteria, [sum_range]) 라고 나타난다.

이 것을 보면 sumif는 3개의 매개변수(인수)가 필요하다는 것을 알 수 있고 range라고 되어 있는 첫번째와 세번째의 인수는 범위를, 두번째 인수는 구분자를 뜻하는 것으로 짐작할 수 있다.

A, B, C의 요인(factors)이 있는데 3 가지에 대하여 각각의 수치 데이터가 주어졌다고 한다면 이 중 A의 값에 대하여만 합계를 내고 싶을 때 어떻게 하면 될까?

1. 참조를 통해 =C3+C6+C7+C10를 하나씩 참조하여 더하기로 합산해도 된다. 하지만 이럴 때 우리는 극단적인 상황을 상상해야 함수의 파워에 대하여 이해 할 수 있다. A가 단지 4개 있는 상황을 가정했지만 만약에 4개가 아니라 400,000개의 셀에 입력되어 있고 다른 400,000개의 셀에는 다른 요인(factor)의 Data가 뒤 섞여 있다고 한다면 400,000번의 참조로 A 요인이 가지는 값을 합산하는 것은 속도와 정확도 모두에서 어려움이 많을 것이다. (상상만으로도 괴롭고 눈과 손이 아픈 것 같다.)

2. 필터 기능을 사용할 수 있다.

열 인덱스에 해당하는 가장 상단의 열 중 하나의 셀에 커서를 위치하고 엑셀 | 단순하지만 잘 모르는 단축키 | 초보 의 Shift + Space 단축키를 눌러서 행을 잡고 엑셀 | 셀 서식 (Ctrl + 1)과 더 빠른 단축키 의 Ctrl + Shift + L을 누르면 테이블에 필터가 단숨에 장착된다. A, B, C 요인(factors)이 있는 열 인덱스에서 A를 선택하여 A만 나타나게 필더링 하고 subtotal 함수를 사용하면 데이터의 부분합 등 여러가지 기능을 구현하도록 할 수 있다. subtotal은 여러가지의 기능을 가지고 있으며 =subtotal( 을 타이핑하면 여러가지 기능을 하는 함수 번호(function_num)가 나타난다. 이 중에서 고르면 되고 지금 하고 있는 sum은 9번에 해당한다. =subtotal(은 모두 타이핑 할 필요 없고 =subt 정도까지 치고 Tab키를 누르면 ( 까지 자동완성 된다.

3. sumif 함수를 사용한다.

필터 기능과 subtotal을 활용하기에 편한 경우가 있고 sumif 함수를 사용해야 하는 경우가 있을 수 있다. 필터를 이렇게 저렇게 바꿔가면서 추가할 것은 체크로 넣고 뺄것은 체크를 해제해서 빼고 하면서 넣고 빼고 해가면서 합계나 이 외 여러가지 다른 연산을 연속적으로 수행하고 싶은 경우에는 필터가 더 유용하다. 그러나 데이터가 확정되어 있고 데이터에는 Key 값과 관측값이 각각 존재하며 혹은 관측값이 다른 값을 참조하여 추가적으로 변경될 소지가 있다고 한다면 sumif 함수를 사용하는 것이 적합하다. 대개의 경우에는 필터와 subtotal 두 개의 기능을 혼합하여 사용하는 것보다 sumif 함수 하나를 사용하는 것을 우선 고려하는 것 같다.

sumifs

sumif는 하나의 기준(criteria)에 대하여 판별하고 합산을 한다. 과거의 엑셀은 여기까지만 가능했었다. 점점 더 많은 함수가 개발되면서 sumifs 기능이 등장했던 기억이 난다.

sumifs는 여러개의 기준에 대하여 판별하고 일치하는 경우에만 합산을 하게 구현할 수 있다.

sumif에서 했던 바와 같은 방식으로 sumifs가 입력 받아야 하는 값 등에 대하여 알아보기 위해 간단하게 엑셀에 =sumifs( 를 타이핑 해 보면 작은 박스에 함수가 입력 받고자 하는 매개변수에 대하여 구문(Syntax)이 나타난다.

구문의 순서가 sumif와 다소 다르게 되어 있다. 이러한 차이가 왜 발생했을지 추론해 보면 sumif는 조건을 하나만 판단하도록 하고 있어 조건을 가운데 두고 양 옆에 조건을 찾을 범위와 합산할 범위를 두는 것이 직관적으로 보일 수 있으므로 그렇게 만들어졌었으나 시차를 두고 나중에 sumifs 함수를 만들다 보니 sumif와 같은 순서를 가져가는 것은 비효율적이라고 생각 했던게 아닐까 한다. sumifs는 몇개가 될지 제한하지 않고 조건을 여러 개 부여해 줄 수 있기 때문이다. 따라서 sumifs는 더하는 값들은 하나의 범위에 있을 것이므로 (sumif에서 가장 뒤에 있는) sum_range를 가장 앞에 두고 조건을 비교하는 범위(criteria_range)와 조건은 몇 개가 될지 알 수 없이 여러 개를 나열 할 수 있도록 하기 위하여 맨 뒤로 두게 된 것이 아닐까 생각한다. sumif 함수를 만들 때 sumifs가 필요해 질 줄 알았겠는가? (순전히 개인적인 상상에 불과하다.)

그러므로 sumifs는 (더할 값 범위, 조건범위1, 조건1, 조건범위2, 조건2) 이런 식으로 써 주면 된다.

sumproduct

sumproduct는 product함수와 sum이 한번에 수행되는 기능을 가지고 있다. 따라서 product와 sum함수를 각각 구현해서 혼합하여 사용할 수도 있으나 엑셀을 프로그램처럼 구현하고 변화하는 수치들을 따라서 변동하는 형태로 시트를 작성할 때 주로 함수가 사용된다는 관점에서 볼 때 sumproduct가 반드시 필요한 경우도 있을 수 있겠다. 굳이 sumproduct에 조건을 걸어서 sumif 함수를 대체하여 사용하는 사람도 본 적이 있다. 기본적인 기능은 반복이나 다른 기능을 여러가지로 엮어서 진보적인 기능을 구현할 수 있기도 하고 반대로 진보적인 기능은 기본적인 기능을 포괄하여 사용하는 것도 가능해 지는듯 하다.

sumif, sumifs와 같은 방식으로 엑셀에서 =sumproduct( 를 눌러서 구문을 보고 어떻게 써 주면 함수가 작동할 지에 대하여 짐작 해 보자.

배열(array)을 여러개 나열하여 선택하면 곱하고 합하는 것을 하는 것 같다.
수치 배열을 두개로 만들어 드래그 쉼표 , 드래그로 구현해 보니 잘 동작하는 것 같다.

잘 동작하였는지 다른 방법으로 검증해 보기 위하여 product 함수와 sum 함수를 혼합하여 구현해 보면 다음과 같다.

sumproduct를 사용한 결과값과 결과가 같은 것을 확인 할 수 있다.
엑셀은 당연히 잘 동작한다고 믿으므로 =참조 * 참조 와 + 더하기로 검증하는 것은 생략하고자 한다.

사례는 두 개의 배열(array)을 곱하고 합하는 것으로 하였으나 여러 개의 배열을 곱하고 합하는 것도 가능하다.

이렇듯 여러개의 기본적인 기능을 혼합해서 만들어져 있는 함수를 적재 적소에 사용할 수 있다면 업무 처리 속도는 함수를 사용하지 않는 경우와 대비하여 비교할 수 없을 만큼 빠르게 될 것이다.