[엑셀 팁] 내가원하는 항목들만의 합을 계산한다 : sum / sumif

SUM은 엑셀에서 가장 많이 쓰이는 함수 일것입니다.

인자는 값 혹은 셀 혹은 영역이 될 수 있구요. 그러나 의외로 SUMIF는 잘 모르시는 것 같네요.

SUM은 잘 아신다고 생각하고 SUMIF에 대해서 조금 적어볼까 합니다.

SUMIF(range, criteria, [sum_range])

SUMIF 함수 구문에는 다음과 같은 인수 (인수: 동작, 이벤트, 메서드, 속성, 함수 또는 프로시저에 정보를 제공하는 값입니다.)가 사용됩니다.

• range  조건을 적용할 셀 범위로서 필수 항목입니다. 각 범위의 셀은 숫자나 이름, 배열 또는 숫자가 들어 있는 참조여야 합니다. 공백과 텍스트 값은 무시됩니다.
• criteria  추가할 셀을 정의하는 숫자, 식, 셀 참조, 텍스트 또는 함수 형식의 조건으로서 필수 항목입니다. 예를 들어 32, ">32", B5, 32, "32", "사과", 오늘() 등으로 criteria를 표시할 수 있습니다.

  중요   텍스트 조건이나 논리 기호 또는 수학 기호가 포함된 조건은 큰따옴표(")로 묶어야 합니다. 조건이 숫자인 경우에는 큰따옴표가 필요 없습니다.

• sum_range  range 인수에 지정된 것과 다른 셀을 더하려는 경우 실제로 더할 셀로서 선택 항목입니다. sum_range 인수를 지정하지 않으면 range 인수에 지정된 셀(조건이 적용되는 셀)이 더해집니다.

excel 2007의 도움말.

구문은 위와 같습니다.

설명에 잘 나와있지만 추가적으로 설명을 드리자면

sum_range는 실제 더하고자 하는 값이 들어가게 됩니다. 필요한 경우 생략이 가능하며 생략할 경우 range의 값이 더해지죠. 즉 range에 값은 비교 조건의 용도로만 쓰고자 할 경우 활용됩니다.

예제를 들겠습니다.

 

위의 그림에서 보이듯이 sum은 단순히 일정 구간의 합을 구해 줄 수 있습니다..

sumif를 쓰면 옆에 예제처럼 다양한 조건을 걸 수 가 있지요.

sumif의 활용의 기존 데이타에서 일정한 조건에 만족하는 값의 합을 얻고자 할 때 활용됩니다.

주의하실점은 "range"와 "sum_range"의 차이로 위의 첫번째 항목과 두번째 항목은 같은 범위이기 때문에 같은 결과가 나왔습니다.

그러나 아래의 잘못된 경우는 sum_range를 생략하였으므로 range에서 2007인 값을 찾아서 더하게 됩니다.(12번 이겠죠?)

그럼 제가 더 잘 사용하는 건 바로 마지막 샘플입니다. 임의의 조건.

수많은 데이타중 어떻게 수식같은 특정한 방법으로 분류하기가 애매할 경우 활용합니다. 사용자가 임의로 지정해버리는거죠.

그러면 그 셀의 조건값을 비교하여 합을 도출해냅니다.

이해가 되실려는지요?

 

그럼 제가 실제로 사용하는 예제를 함 보겠습니다. 저는 가계부에서 이 SUMIF를 기반으로 사용합니다.

초기엔 PIVOT을 하고 그 PIVOT에서 필요한 데이타를 가져오는 방식으로 했는데 파일크기도 커지고 느리고 내 맘대로 안되서 SUMIF로 대체해버렸습니다. ^^*

일단 가계부다 보니 민감한(?) 자료가 있을수도 있어 부분부분 발췌하도록 하지요.

아래쪽에 보시면 여러개의 수많은 시트들이 보일껍니다. 중간정리 시간에 말씀드렸다시피 가계부가 제 생활의 중심(?) 이라고 할까요? ㅎㅎㅎ

암튼 또 삼천포로 빠지면 안되니 간단히 정리합니다.

노랑색부분이 실제 사용자가 입력하는 부분이지요 날자 구분 항목 대분류 소분류 금액 내용 거래처 등입니다.

이중 분류 항목에 따라 계를 내는 형태를 취하고 싶은데.

예를 들자면 월별 식료품비-술값 은 얼마나 나올지 등등이 되겠지요. 그런데 이걸 그렇게 분류하자니 쉽지 않습니다.

그래서 처음엔 피봇을 썼지요 (알아서 해주니깐요.) 근데 이게 또 제입맛대로 안되더군요. ^^*

그래서 J열에 보이는 조건식을 추가했습니다.

수식은 : =CONCATENATE(YEAR($B217),"년",TEXT(MONTH($B217),"00"),"월",E217,"",F217) 입니다.

결과물은 2007년12월식료품비술값 이라는 문자열이 되겠네요.

물론 각 입력항목별로 다르게 나타나겠지만 12월 술값 이라면 같은 타이틀을 가진 형태로 분류가 되겠지요?

그럼 여기서 SUMIF가 빛을 발휘합니다.

sumif(j:j,"2007년12월식료품비술값",G;G)

이렇게 하면 2007년 12월에서 대분류 식료품비 소분류 술값의 합이 자동으로 계산됩니다.

그렇게 하면 지출분석이라는 시트에 그림과 같은 형태로 분류를 만들어서 월별 지출내역을 통계냅니다.

=SUMIF(입력자료!$J:$J,CONCATENATE(지출분석!H$1,지출분석!$B62,지출분석!$C62),입력자료!$G:$G)

즉 조건절을 이 표에서도 새로 생성을 하도록 만드는 것이죠 그럼 항목별로 지출내역이 표시가 됩니다.
1행의 월에 해당하는 문자열과 대분류 소분류를 결합하여 조건으로 입력해서 아까 입력자료의 J열과 비교하여 합을 구해내는 방식입니다.

이 방식으로 매월 지출 흐름을 계산 할 수 가 있습니다.

 

또한 입력자료에 보면 계정명이라는 항목이 있습니다. 비슷한 방식으로 해당 계정의 잔액을 계산해낼수도 있겠군요.

다시 위의 그림에서 계정명 자금관리의 현재잔액이 얼마인지 알기 위해서는

SUMIF(D:D,"자금관리",G:G)

이러한 함수로서 자금관리의 잔액을 알 수 가 있겠네요. 그럼 마찬가지 방식으로 이것도 단순히 사용자가 직접 입력하는게 아니라 모든 계정을 정렬하여 표시하면 되겠지요. 해당 하는 항목은 자산현황인데.. ㅎㅎㅎ

보여드릴수는 없겠군요. ^^*

 

나머지는 직접 응용해보세요..

-마음가는 길은 곧은 길-