trueonot.com

http://www.trueonot.com/99 에서 설명하였던 제가 즐겨 사용하는 엑셀 작성법입니다.

누구나 자기만의 노하우가 있는 법이고 이보다 더 유용한 방법도 있겠지요.

진행하기에 앞서 일단 위의 페이지 하단에서 명시한 방법을 한번만 더 읽어주세요.

요약하여 설명하면 두 줄 정도되겠군요.

1. 수식을 나누어서 작성해라.
2. 보기좋게 모양을 꾸밀때는 Ctrl+X를 이용하여 수식을 옮겨라.

이렇게 두줄입니다. ^^*

1번에 대해서는 이미 지난 강의 시간에 http://www.trueonot.com/102 했던 것 처럼 수식을 나누어서 쓰시면 됩니다.

즉 복잡한 수식을 여러단계의 중간과정 셀들을 통해 차근차근 진행하는 것이죠 장점은 중간 과정을 직접 보면서 할 수 있으므로 오류를 찾기 쉽고 필요한 경우 수식을 고치기 쉽다는 점을 들 수 있겠네요.

예를 들어 위의 그림에선 총무팀장이라는 G8의 결과 수식을 도출해 내기 위해서 G7/G6/I7과 같은 중간과정 셀들을 사용한 것이지요.

직접 시각적으로 볼 수 있으니 바로 바로 문제가 발생했을때 처리를 할 수 가 있습니다.

그럼 두번째 항목인 Ctrl+X를 이용하는 것에 대해 검토해보지요.

현재 시트는 "사원자료" 시트입니다. 1차적으로 함수를 개발(?) 하는 단계에서는 원 자료를 직접 볼 수 있는 페이지 즉, 자료페이지에서 작업하시는 것이 유용합니다. 참조를 위해 시트를 이리저리 왔다갔다 하지 않아도 되고 원하는 결과를 바로 바로 볼 수 있으니깐요.

당연한 얘기겠죠. 보면서 하는게 쉬운건. 그러나 최종 결과물을 자료와 함께 두는것은 결코 바람직하지 않지요. 결과물을 보게 될 사람이 자기 자신이라고 해도 수많은 데이타와 함께 보는 것은 결코 유쾌하지 않습니다. 만약 상급자라면? 더더욱 그럴테고 이것이 프린트를 위한 자료라면 너무나도 당연한 얘기겠지요.

그럼 지금 검색하는 부분을 "사원검색" 시트로 옮겨보겠습니다. F1:I8까지의 셀을 전부 선택한 후 Ctrl+X로 옮기도록 합니다.

중요한 점은 반드시 Ctrl+X를 하고 Ctrl+V를 하셔야 한다는 겁니다. Ctrl+C로도 비슷한 결과를 얻을 수는 있습니다만. 최종적으로는 틀려지게 되고 오류가 발생합니다. 그점은 뒤에서 설명드리죠.

기존 사원검색에 있던 내용은 전부 삭제하고 위의 복사한 내용을 옮겨왔습니다.

현재 주어진 과제는 사원 이름을 넣으면 사원의 전화번호와 직책을 알려주는 형태로 가공하는 것이라고 가정하겠습니다.

먼저 검색에 필요한 셀들이(중간과정셀) 모두 다른 페이지로 이동하였음에도 결과값에는 변함이 없음을 확인해보시죠.

(아 다른 셀들은 전부 이상없이 변경되었으나 "직책" 결과는 "0" 이라고 나오는 군요 이부분도 수정해보겠습니다.)

그림과 같이 A열을 추가하고 2/3행을 추가하여 내용을 입력하여 줍니다.

전화번호는 B6이군요 B6을 선택한 후 잘라내어 붙여넣기를 이용하여 B2에 붙여넣기를 합니다.

수식을 보면 우리가 수정한 것이 아님에도 참조열이 원래의 C:C에서 "사원자료!C:C"로 자동으로 바뀌었네요. 이러한 식으로 잘라내어 붙여넣기를 하면 대상이 되는 참조 셀들의 수식은 필요에 따라 자동으로 변경됩니다.

그럼 다음 결과물에 해당하는 C10의 직책 함수를 마찬가지 방법으로 B3으로 옮겨보겠습니다.

보시는 것처럼 수식은 옮겨졌습니다. 참조값들도 변경이 되었군요 근데 왜 결과값이 0 일까요?

원인은 Indirect에 있습니다. indirect가 참조하는 셀은 c9로 c9에는 D:D라고 되어 있습니다. 그럴 경우 엑셀에선 당연히 현재 시트인 "사원검색!D:D"라고 인식하는 것이죠 그러니 결과값이 제대로 나오지 않는 것입니다.

그럼 어떻게 해야 할까요?

이렇게 해주면 됩니다. 문자열 함수에 해당 시트를 합쳐주는 것이지요.

그럼 이제 필요없는 셀들을 지워보겠습니다.

B5는 전혀 필요없는 셀이므로 지워버리고 나머지는 전부 필요하겠군요.

이런식으로 구성을 바꾸어보겠습니다.

그림 처럼 지금까진 그냥 단순한 예제이므로 단계별로 이름없이 바로 바로 진행하였으나 나중에 시간이 지나면 헤깔릴 수 있으므로 각 중간과정별로 설명을 적어두는 것이 좋습니다.

그리고 나서 E/F열을 숨기기를 한다던지 흰색 글씨로 바꾼다던지 하면 깨끗한 결과물을 얻을 수 있겟지요.

본인은 수식을 수정할때 어디에 숨겨뒀는지 찾기 힘들 수가 있으므로 이렇게 회색으로 처리해버리는 편입니다.

도움이 되실려는지요? ^^*

-마음가는 길은 곧은 길-

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

물론 이 함수 말고도 비슷한 역활을 할 수 있는 함수들도 많습니다. 여러 조합으로 다양합니다. 그러나 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함수에 대해서는 도움말을 확인해보시면 됩니다.

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

-마음가는 길은 곧은 길-