엑셀 일정하게 떨어져있는 셀들의 합계 구하기
- 정보/생활정보
- 2020. 2. 20. 00:22
엑셀(EXCEL)로 규칙적으로 떨어져있는 셀들의 합계 구하는 법
엑셀로 업무를 하다보면 합계를 구해야하는 경우가 굉장히 많습니다.
저의 경우 각 사원들의 급여 합계를 자주 구하곤 합니다.
사실 사원마다 급여의 총계만 구한다면 쭉 긁어서 쉽게 합계를 구할 수 있겠지만, 급여의 항목을 디테일 하게 나눠서 각각 합계를 구해야되는 경우는 인원수가 늘어날수록 골머리를 썩게 됩니다.
그래서 오늘은 그런 상황에서 쉽게 합계를 구하는 방법을 알아보도록 하겠습니다.
바로 SUMPRODUCT함수를 이용하는 방법입니다.
SUMPRODUCT 함수 이용하기
아래처럼 급여의 항목이 기본급, 인센티브, 식대, 보너스로 쪼개져 규칙적으로 배열되어 있을 때 전체 합계와 함께 각각의 합계도 구해야 되는 상황을 가정해보겠습니다.(예제라 많은 인원을 적지는 않았지만 실제로는 훨씬 더 많은 인원을 계산하게 되겠죠?)
이 때 사용할 수 있는 것이 SUMPRODUCT 함수로 배열 또는 범위의 대응되는 값끼리 곱해서 그 합을 구하는 함수입니다.
SUMPRODUCT 함수
구문: SUMPRODUCT(array1, [array2], [array3], ...)
support.office.com
함수의 설명을 쓰고있었는데 글이 너무 길어져서 삭제하고 바로 대입하여 계산할 수 있는 식을 알려드리겠습니다.
=SUMPRODUCT((MOD(ROW(범위,항목수)=나머지)*범위)
범위: 말 그대로 계산하려고 하는 값들이 있는 범위 입니다. 위 표에서는 B6부터 B29까지 이므로 B6:B29가 되겠습니다.
항목수: 다른 숫자를 넣고 나머지 값으로 함수를 조절해도 되겠지만 가장 편한 방법은 쪼개진 항목 숫자를 넣는 것입니다. 위에 표에서는 기본급, 인센티브, 식대, 보너스 라는 4가지 항목으로 나눠져 있기 때문에 4를 넣어주면 되겠습니다.
나머지: 내가 구하고자 하는 행의 값을 항목수로 나눴을 때 남는 나머지 입니다. 기본급의 합을 구한다고 가정했을 때 위 표상 기본급은 B6, B10, B14··· 에 위치해 있으므로 항목수인 4로 나누면 나머지가 2가 남습니다. 그러므로 2를 입력합니다.
그렇게 위 표를 기준으로 기본급을 구하는 함수를 만들어보면 아래와 같이 나오게 됩니다.
기본급의 합=SUMPRODUCT((MOD(ROW(B6:B29),4)=2)*B6:B29)
여기서 기본급 말고 식대의 합을 구하고싶다면 식대는 표의 B8, B12, B16···에 위치해 있으므로 4로 나누면 나머지가 0이기 때문에 나머지 자리에 0을 넣어주면 되고 아래와 같은 식이 됩니다.
식대의 합=SUMPRODUCT((MOD(ROW(B6:B29),4)=0)*B6:B29)
다른 값들도 아래처럼 동일한 방법으로 구할 수 있습니다.
이와 같은 방법을 통해 굉장히 범위가 넓은 케이스에서도 값들이 규칙적으로 배치되어 있다면 쉽게 합계를 계산할 수 있습니다.
도움이 되셨길 바라겠습니다 :)
