[엑셀-팁]SUMPRODUCT함수를 사용한 부서별 비용지출현황

[링크]엑셀 함수 총정리 및 사용 예제

[링크]메모리스트 엑셀 함수 정리 목록 및 엑셀 팁 정리 목록

[링크]메모리스트 엑셀 카테고리 목록

 

엑셀의 이름 기능과 SUMPRODUCT 함수를 사용하여 부서별 비용지출현황을 작성해 볼까요.

SUMPRODUCT 함수는 배열 또는 범위의 대응되는 값끼리 곱해서 그 합을 구하는 함수입니다.

예를 들어 단가와 수량을 곱해서 금액을 구하는데 이 경우 SUMPRODUCT 함수를 사용해서 구하면 된다.

하지만 위의 예처럼 단가와 수량만 곱하는 경우는 =A1*B1 과 같이 간단하게 처리하면 되며, 식이 복잡해 질 경우 SUMPRODUCT함수를 사용하면 된다. 아래에 그 실례를 보이고자 한다.

 

아래의 예처럼 부서별, 기간별, 비용과목별, 결제 수단별 금액의 합계를 구해보자.

 

 

 

 

그러기 위해서는 일자별, 부서별, 비용과목별, 결제 수단별 금액을 사전에 입력해 두어야 한다.

부서는 정의 시트에 부서명을 입력해 두었으며, 비용과목 또한 정의 시트에 입력해 두었다.

 

 

 

 

부서별 비용지출현황을 산출하기 용이하도록 하기 위해 사전에 이름을 정의해 두었다.

이름을 지정하는 방법은 이름지정하고자 하는 영역을 선택하여 삽입-이름-만들기 기능을 이용해도 되고, 이름 항목 하나 하나를 각각 등록하고자 하는 경우는 해당 영역을 범위 지정후 이름상자에 해당 이름을 입력하면 된다. 예를 들어 아래의 그림처럼 A2 부터 A33 까지에 일자가 입력되어 있는 경우 A2 부터 A33 영역까지를 범위 지정 후 이름상자에 "일자"라고 입력하고 엔터를 치면 된다. 이름상자의 위치는 아래 그림의 경우 "결제"라고 입력되어 있는 위치이다.

 

이름 기능을 이용하여 "일자", "부서", "비용과목', "금액", "결제"라고 이름을 정의한다.

 

 

 

 

지출현황 시트에 부서별 비용지출현황의 폼을 작성한다. 작성은 맨위의 그림을 참조하여 작성한다.

부서와 기간은 사용자가 선택할 수 있도록 하기 위해 유효성 검사 기능을 사용하여 사용자의 선택 편의를 제공한다.

D3 셀에는 숫자 1부터 12까지의 기간이 입력이 되며, 셀서식을 사용자정의에서 "0월"로 지정해 두었다.

화면에는 "1월"로 보이지만 실제로는 "1"만 입력이 되어 있는 것이다.

 

 

 

 

B3 셀을 선택하여 데이터- 데이터 유효성검사 기능을 선택한다.

제한대상 항목에서 목록을 선택한 후, 원본 항목은 부서명을 선택한다. 아래 그림처럼 직접 입력해도 되고, 원본 입력 위치에서 이름선택상자 단축키인 F3키를 눌러서 선택해도 된다. F3키를 누르면 정의되어 있는 이름의 목록 중 선택할 수 있다.

 

 

입력 시 "부서명"으로 입력하면 안되며, 반드시 "=부서명"으로 입력이 되어야 한다. 앞에 = 표시가 없을 경우 이름으로 지정된 부서명의 목록이 나오는 것이 아니라 항목에 부서명이라는 텍스트가 나오게 된다.

이와 같은 방식으로 D3 셀에는 유효성검사에 기간을 입력한다. 원본 항목에 "=기간"이라고 입력되면 된다.

 

 

 

 

B7 셀에는 SUMPRODUCT 함수를 사용해서 수식을 입력한다.

수식은 "=SUMPRODUCT((부서=$B$3)*(비용과목=$A7)*(결제=B$6)*(MONTH(일자)=$D$3),금액)" 이다.

 

위의 수식에는 입력 인수가 두개이다.

첫 번째 인수에는 (부서=$B$3)*(비용과목=$A7)*(결제=B$6)*(MONTH(일자)=$D$3) 이며,

두 번재 인수에는 금액 이 입력되어 있다.

첫 번째 인수와 두 번째 인수의 값을 곱해서 결과로 나타내 주는 것이다.

 

첫 번째 인수의 수식을 분리해서 보면

(부서=$B$3)

(비용과목=$A7)

(결제=B$6)

(MONTH(일자)=$D$3)

으로 되어 있으며, 이 항목을 서로 곱하도록 수식이 되어 있다.

 

(부서=$B$3) 내용은 만약 부서가 $B$3의 내용과 같으면 참(1), 그렇지 않으면 거짓(0)으로 계산하라는 의미가 들어있다. 곱셈에서는 아무리 큰 수에도 0을 곱하면 최종 결과가 0 이 되므로 중간에 해당되지 않는 내용이 있으면 합에 포함시키지 말라는 의미를 가지고 있다.

비용과목과 결제 또한 같은 의미를 지니고 있다.

 

(MONTH(일자)=$D$3) 는 비슷한 의미이긴 하지만 일자에 들어있는 데이터 중 월에 해당하는 값만 가져와서 해당 월이면 참(1), 그렇지 않으면 거짓(0)으로 계산하라는 의미이다.

MONTH 함수 대신

년도는 YEAR 함수를, 월은 MONTH 함수를, 일은 DAY 함수를 사용하면 된다.

 

 

첫 번째 인수의 내용이 모두 참인 항목을 찾아 금액을 더하라는 의미이다.

예를 들어 부서는 기획부, 기간은 1월, 비용과목은 식대, 결제수단은 현금인 항목들을 찾아 모두의 합을 구하라는 의미이다.

 

수식 중 절대참조 및 혼합참조는 해당 셀의 위치가 변경되지 않도록 하기 위해 설정한 것이다.

$B$3의 경우 부서명이 입력되는 항목은 항상 B3 셀이므로 절대참조 형식인 $B$3 으로 지정했으며,

비용과목은 상하로는 이동되지만 좌우로는 이동되지 않으므로 혼합참조 형식인 $A7 을 지정한 것이다.

혼합참조가 이해가 잘 되지 않으신 분은 비용과목처럼 열은 항상 A열이지만 행은 7행에서 8행, 9행으로 변동될 경우 A열을 고정하기 위해 A앞에만 $를 붙여 주면 된다.

 

결제는 값이 현금과 카드로 B6셀과 C6셀에 존재하므로 숫자 6을 고정하기 위해 6 앞에만 $를 붙여주는 혼합참조 형식을 지정하면 된다. 절대참조, 혼합참조, 상대참조는 수식을 입력하는 상황에서 F4키를 누르면 4가지 방식을 계속해서 보여주며 바뀌게 된다.

 

이해를 돕기 위해 아래에 동영상을 첨부했으니 실행예를 보기 바라며,

위의 예제 파일은 부서별 비용 지출현황.xlsx에 첨부하니 실제 사용할 수 있도록 수정해서 사용하기 바란다.

 

엑셀의 다양한 기능을 보고 싶으신 분은 아래의 엑셀 카테고리 혹은 우측에 있는 카테고리의 다른 글을 참조하길 바란다.