trueonot.com

오늘은 제가 쓰는 함수들 중에서 가장 중요한 함수입니다. 사실 이 함수를 알고 나서야 엑셀이 내 맘에 들기 시작했다고 할까요? ^^*

물론 이 함수 말고도 비슷한 역활을 할 수 있는 함수들도 많습니다. 여러 조합으로 다양합니다. 그러나 indirect는 비교적 직관적이라. 주로 사용하는 편이지요

그럼 indirect란 어떤 함수 일까요?

도움말에서는 이렇게 설명하고 있습니다.

ref_text를 참조하여 반환 한다는 군요. 도데체 무신 귀신 시나라 까먹는 소리랍니까? ㅎㅎ

E7을 보시면 그 사용법을 보실수 있습니다. 즉 텍스트로 된 주소를 참조로 반환한다고 할까요?

이걸 왜 이렇게 어렵게 할까요? 그냥 E7에다가 =E1 이라고 입력하면 간단할텐데요?

엑셀을 쓰면서 수식을 만들다 보면 해당 수식을 동적으로 필요에 따라 변경하고 싶은 경우가 있습니다. 그럴 경우 유용하게 사용할 수 있는 가능성을 제공하는 것이 바로 Indirect이지요

위의 사원 예제를 조금 확장해서 직책을 추가해보겠습니다.

F4에는 현재 전화번호를 보여주고 있지요 만약 직책을 검색하고 싶으면 어떻게 해야 할까요?

수식을 index(D:D,F2)로 바꾸면 됩니다. 그럼 바로 직책으로 변경되겠지요.

근데 매번 같은 일을 해야한다고 생각해보지요... 어떻게 하면 될까요?
(저라면 밑에다가 수식을 하나 더 만들고 맙니다. ^^* )

ㅎㅎㅎ 이 경우엔 예제가 너무 단순해서 그냥 수식을 추가하는 것으로 해결이 되지만 indirect를 사용하는 예제로 바꾸어보겠습니다.

F7열의 수식을 보시죠. B:B 라는 주소대신에 Indirect(F6) 으로 되어있스비나다. F6에는 B:B라고 그냥 텍스트 문자열이 입력되어 있습니다.

그럼 여기서 직책을 검색할려면 어떻게 해야 할까요? F7의 수식을 변경하지 않고 가능할까요?

F6의 문자열 내용만 바꾸는 것으로 간단하게 수정이 되었습니다.

조금더 확장을 하면 D:D 라고 치는 것도 귀찮지요 간단히 D열이라는 의미에서 D만 입력해도 되도록 해보겠습니다.

F6이 문자열이 아닌 수식으로 바뀌었습니다. F5에 입력된 D라는 문자열을 가지고 indirect에 넣어줄 인자를 만들었네요.

그럼 조금만 더 머리를 써 볼까요? 지금은 자료랑 같은 페이지라 D열이 직책열임을 쉽게 알 수 있지만 다른 페이지였다면 번거롭겠지요?

D대신에 "직책" 이라고 입력하면 검색이 되도록 하고 싶어요.

먼저 match함수를 사용하여 직책이 몇번째 행인지를 알아냅니다. a, b, c, d 4번째 열이군요 그래서 결과값은 4 입니다.

그럼 이 4라는 숫자로부터 어떻게 D라는 값을 얻어낼 수 있을까요? 다양한 방법이 있지만 쉬운 방법으로 char 함수를 쓰겠습니다.

컴퓨터 내부에는 ascii라는 문자열을 쓰고 거기에 정의되기를 'A'는 65라고 정의 되어 있습니다. B는 66, C는 77 이런식이죠.

너무 길어지면 짜증낼테니....

그림과 같이 수식을 만들면 됩니다. match에서 계산된 결과 + 64를 해줌으로 64+4 = 68 , char(68)은 "D"가 됩니다.

char함수에 대해서는 도움말을 확인해보시면 됩니다.

다른 방법도 있으나 그것까지 설명하면 또 너무 길어지니 여기서 줄이기로 하지요.

-마음가는 길은 곧은 길-

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)

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

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

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

-마음가는 길은 곧은 길-