trueonot.com

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)

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

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

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

-마음가는 길은 곧은 길-

지금까지 살펴본 함수를 되집어 볼까요?

datevalue/text/match/index/vlookup/hlookup/lookup/mid/len/left/right/concatenate

정도 되는 듯 하군요. 별로 대단한 함수들은 없습니다. 하나 하나 살펴보면 쉬운 함수들이지요..

강좌를 계속 쓰려고 보니 사실 저도 아는게 별로 없는지라 하나하나의 함수를 더 배우게 되더군요. 그리고 이제는 슬슬 밑천도 다 떨어져 가서 엑셀 책을 하나 구해서 살펴보았습니다.

아실분이 계실지 모르겠지만 Uno라는 분이 있으셨죠. 97시절에 그분 사이트에서 정말 많은 도움을 받고 정말 VBA하나만 알면 모든게 다 되겠구나 라는 생각을 하게 만들어주신 분이죠. ㅎㅎㅎ

그분 책을 우연히 구해서 살펴봤는데 당시에 쓰인거라 97기준이죠. 함수도 지금에 비해서 매우 부족하구요. 근데도 정말 모르는 함수들이 잔뜩이더군요. ㅎㅎㅎ

엑셀에는 정말 많은 수 많은 함수들이 가득합니다. 같은 결과를 얻기 위해 수 없이 많은 방법이 존재하구요.

제가 쓰는 강좌의 방법이 최선이 아니며 이것보다 나은 방법이 분명히 존재합니다.

전 OS개발자인데 Platformbuilder라는 것을 사용하지요. 개발중에 막히는게 있으면 PB의 도움말에 답이 있습니다. 물론 영어로 되어 있어서 보기가 좀 짜증납니다. 쉽게 설명해주는 것도 아니구 여기 봐라 저기 봐라. .-_-;; 그래도 정말 최고의 해법은 도움말이라는거.

MS제품은 거짐 다 그런거 같아요. ㅎㅎ (동의 하기 힘들지만 PB는 그렇네요^^*) 그리고 엑셀의 도움말은 MS의 도움말 중 최고 라고 해도 될듯합니다.

예제도 충실히 있으며 분류도 잘 되어 있구요. 함수만 알면 그 함수를 어떻게 쓰는지 몰라서 쓰는 경우는 없을 껍니다. 도움말 덕분에...

다만 함수를 모를 뿐이고 이 함수를 어떻게 활용해야 할지 모를 뿐이지요.

정보화시대에 함정. 너무 많은 정보속에 정작 원하는 정보를 찾지 못하는 그런 함정에 빠지고 마는 것이죠.

제가 주제넘게 강좌를 하겠다고 하는 건 lookup을 이렇게 저렇게 다르게 구현할 수 있다는 예를 가지고 다양한 활용법을 연구해 봤으면 하는 차원에서 시작했습니다.

저는 엑셀을 통해서 가계부를 쓰고 있으며 주식관리등을 하지요.

가계부에서는 얼마전 설명해드린 보안카드 기능을 비롯하여 기념일 보기등도 쓰고 있으며 월별 지출내역등을 분류 항목별로 표시해주며 등록된 은행계좌별로 잔액이 얼마인지 현재 자산/부채 등은 얼마인지도 보고 있습니다. 매월 현금 흐름도 한눈에 파악하도록 해놨으며 월간 일정표도 만들어두었지요. 일정에서는 할일관리도 하구요. 요새 덧 붙이고 있는걸로는 차계부도 있네요

가계부가 제 생활의 종합 관리자(?) 역활을 합니다.

주식관리는 현재 주가 등을 웹에서 가져와서 현재 손익을 자동으로 계산 해주는 형태로 만들어 둔 것도 있구요.

물론 더 좋게 만들어 쓰시는 분들도 있을테고 더 좋은 가계부 프로그램도 많습니다만 무엇보다 중요한건 내 입맛에 맛는 건 내가 만들어 쓰는 것이 아닌가 싶네요.

가계부에서 보면 하는일이 무지 많은 것 같지만 실제 사용되는 함수는 거의 제한적입니다.

다음 강좌에 사용될 sumif 가 제일 주력 함수 군요 ㅎㅎ. concatenate도 있고 ... 기념일 예제에서 사용된 함수들도 있고 index/match 등도 있으며. indirect라는 함수도 있습니다. (indirect를 했는지는 가물 가물 하네요 ^^*)  별로 대단한 함수는 없습니다만 누구보다 편한 저만의 가계부입니다.

몇분이나 이글을 보시는 지는 모르겠지만 여러분에게 전하고 싶은건 내손으로 가계부 정도는 만들어 쓰는 것을 전하고 싶어서 입니다. 제가 만든 가계부를 그냥 줘도 어떻게 되는지 모르면 쓰지도 못할게 뻔하거든요 ^^*

주저리 주저리 말이 길었습니다. (쓸내용이 떨어져 가니.. ㅎㅎㅎ)

더 나아가서 VBA까지 했으면 좋겠으나... 모르겠네요 사실 전 VBA를 쓰는것을 좋아하지 않습니다. 그 이유는

첫째로는 VBA를 쓰면 2003부터는 성가신 보안 경고창이 뜨며
둘째로는 보안 문제의 심각성을 야기 할 수 있으며(VBA로 시스템의 중요한 파일들을 싹 날릴 수도 컴터를 먹통 시킬수도 있습니다.)
셋째로는 좀 더 실직적인 문제로  VBA 스크립트를 수행하고 나면 UnDo가 안됩니다. 간단한 작업을 하더라도 마찬가지죠. -_-; 무지 불편해요-_-;
마지막으로는 함수형태로 간단한 함수를 짜더라도 그게 여러셀에 넣어지면 수행속도가 워크시트함수에 비해 매우 느리다는것이죠.

그래서 가능하면 워크시트함수의 조합으로 해결하고자 합니다. 그러다 보니 어쩔수 없는 경우도 많긴 하지만 ㅎㅎ 어쩔수 없지요. 어차피 제가 만드는 문서는 저 혼자 보기 위한 경우가 많고 출력용은 VBA스크립트를 보는건 아니고... ^^*

결정적으로 VBA는 어렵잖아요? ㅎㅎㅎ 안 그런가요? ?^^* 프로그래밍을 하는 건 어려운 겁니다. ^^*
(어려운 만큼 막강하긴 합니다.)

잡설은 이만 줄이죠..

다음에는 진짜 강의로~~ 휘리릭~~

-마음가는 길은 곧은 길-