trueonot.com

혹시 어제 내준 숙제를 하신분 계신가요?

같은 이름의 홍길순은 3명이나 됩니다. 그럼 그중에서 누구를 찾아줄까요?

lookup은 단순히 가장 마지막 값을 알려주는 군요.

그럼 index와 match로 만든 lookup은 어떨까요? 진짜 lookup함수랑 결과가 같습니다.

근데 위의 예제에서 보면 222-2222-2222인 3번째 홍길순의 데이타가 나왔습니다. 이건 어떻게 한 걸까요?

여기에 match의 비밀이 숨어 있습니다.

match함수부분만 보면

E4 는 MATCH(E1,사원자료!B:B) 인데 반해MATCH(E1,사원자료!B:B,0)으로 되어 있습니다.

0은 생략가능한 선택 인자로 가장 첫번째로 일치하는 것을 리턴하게 됩니다. 그래서 첫번째 match자료인 사원번호 2번의 홍길순 자료를 보여주는 것 입니다.

1은 디폴트 값이죠 즉 마지막 자료입니다.

-1도 사용가능하구요 어떤 차이가 있는지 궁금하신분은 도움말을 참조하세요^^*

숙제입니다 숙제. F1키 한번만 눌러보시면 되요. ^^*

lookup은 무조건 한가지 결과만을 알 수 있으며 우리가 어떻게 할 수 가 없지만 index와 match로 쪼개놓으니 우리가 원하는데로 결과를 만들어 낼 수 있군요. ^^* 뭐 간단하지만 이것도 하나의 장점이 될 수 있겠죠?

여기서 오늘 강의를 마치면 여러분들이 매우 섭섭해 할 꺼 같은데.... 안그런가요? ㅎㅎ

지금까지 배운 함수 index와 match로 할 수 있는 실용예제를 들어볼게요.

저는 엑셀로 가계부를 씁니다. 가계부를 쓰다 보면 항상 하는 일이 계좌 정리죠 이리저리 자금이체.. 그러다보면 매번 보안카드를 봐야 하고 귀찮기가 여간 ..-_-;; 게다가 요샌 앞2자리 뒷2자리 이런식으로 되어있고

관리하는 은행만해도 3개가 넘으니..-_-;;;; (돈이 많아서가 아닙니다. -_-;;;)

암튼 그렇다 보니 보안카드를 일일이 보는 일도 곤욕이지요. 그래서 오늘 배운 index/match로 보안카드를 검색할 수 있게 만들었답니다.

실제 제 보안카드를 들어 예제를 보여줄수는 없구 ^^* ㅎㅎㅎㅎ

간단한 예제를 만들어서 보여드리죠.

E2와 E3은 사용자가 입력하는 란 입니다. 즉 은행에서 묻는 보안번호를 입력하는 란이지요.  예제는 심플하게 작성했습니다.

G3은 보안카드라는 특성상 매우 간단하게 함수를 구성해본 것입니다. 제가 쓰던 예제를 넣으려고 했는데 생각해보니 이 방법이 더 간단할 듯 해서요. 보안카드 특성상 1번부터 일련번호로 증가하니 그냥 가장 단순하게 index함수만으로도 만들수가 있겠더군요... ㅎㅎㅎ 그리고 수식을 편하게 하기 위해서 B:B로 해서 행이 한줄 더 추가되었으므로 E2+1 을 해주어야 합니다.

이건 같은 기능을 제가 쓰는 것과 비슷하게 만들어본것입니다. (아직 설명안드린 함수가 있어서 ㅎㅎㅎ 그대로는 못 쓰겠더라구요.. 간단히 설명을 드리면 보통 보안카드가 여러개 이다보니 한번에 여러개를 등록해놓구 쓰기 위해서 만들다 보니 ... 좀 더 복잡해진 경우입니다. ㅎㅎ 그냥 이대로만 쓰셔도 충분하구요^^?*)

index와 match로 구성을 했군요 match의 결과값을 그대로 index에서 쓰이기 때문에 따로 +!을 해주지 않아도 됩니다.

index와 match로 하였으니 이것을 lookup/vlookup으로도 만들 수 있겠지요?

lookup/vlookup으로의 변환은 숙제입니다. (정말 쉽고 10분이면 되니깐 꼭 좀 해보세요.. )

아 그리고 투루노는 여기에다가 한가지를 더 해서 좀 더 편하게 함수를 만들었습니다.

문자열 합치는 함수임 concatenate함수와 문자열 자르는 함수인 left/right 함수를 이용하여 .. concatenate는 이미 알려드렸고 left/right는 직접 도움말을 참조하세요. 결과를 보여드리게습니다.

최종 결과 입니다.  보시는 바와 같이 앞 두자리 / 뒷 두자리 해서 두개의 문자씩을 잘라낸담에 함쳐서 은행 홈페이지에 바로 입력할 수 있게 만들었습니다.

도움이 되셨나요? ^^*

-마음가는 길은 곧은 길-

휴가를 다녀왔더니 정신이 없었나 보네요.-_-;

lookup을 올리려다가 match를 먼저 올려버렸습니다.

원래는 lookup->match->index 순서로 진행되어야 하는데... -_-;;; 쩝. 암튼 index를 진행하겠습니다.

match는 해당하는 자료의 위치를 알려주는 값입니다.

프로그래밍을 조금이라도 아시는 분이라면 배열 이라는 단어를 아실껍니다.

배열을 설명을 위해 간략히 얘기를 하자면. 값들의 집합 이라고 볼 수 있지요.

수학시간 맨 첨에 배우는 게 집합 이지요. {1,2,3....} 이런식으로 되던거요.. 부분집합도 있고 등등등..

그럼 엑셀에서 배열은 무엇일까요? 값들의 집합 혹은 cell들의 집합 입니다. 이 셀들의 집합은 Range가 되지요.

이해가 잘 안되시나요?

예를 들면,  "C1:C50" / "A:A" / "10:10" 이런 형태가 배열의 예가 될 수 있겠네요.

정확히는 매트릭스(행렬) 형태도 배열로 계산이 되나 그렇게까지 하면 머리가 아프므로 하나의 행/열로 구성된 값혹은 셀들의 집합 이라고 생각하겠습니다.

match라는 함수는 가 배열에서 내가 찾고자 하는 값이 몇 번째 있는지를 알려주는 함수 입니다.

학생이 50명인 반에서 "투루노" 라는 학생은 몇번인지를 알려주는 함수 라고 하시면 이해가 될런지요?

이 경우 학생 50명은 한개의 배열이 되는 것이고 "투루노"는 lookup_value가 됩니다.

프로그래밍을 하신분이라면 1번이 아닌 0번부터 시작에 대해 익숙하시겠지만 이 엑셀은 개발자용 프로그램이 아니라 1번부터 시작합니다.

즉 1번 부터 50번까지 50명의 학생이 있습니다.

그중 투루노가 10번 이라면 match의 결과는 10이 됩니다.

여기까지가 전 시간의 복습입니다.

그럼 index라는 함수는 무엇일까요?

다시 우리가 작성하던 예제로 돌아가보겠습니다.

E3의 행에 index함수가 사용되고 있습니다.

index는 주어진 배열에서 index에 해당 하는 값을 리턴합니다.

첫번째 인자는 array 혹은 reference가 됩니다. 그냥 편하게 같은거라고 생각하십시오. (복잡해지면 어려우니...)

즉 위의 에서는 B:B B열 전부가 해당 인덱스 입니다. 그리고 참조값은 E2 = 3 입니다.

B:B 열은 이름/홍길동/홍길순..... 이런식으로 진행됨으로 이름(1)/홍길동(2)/홍길순(3) 의 형태로 됩니다.

즉 해당 배열에서 3번째의 값은 홍길순 이 되는 것이죠.

그럼 지난 시간엔 mathc함수로 홍길순이 3번째 인자라는 것을 알았고 이번엔 그 3이라는 값을 가지고 다시 홍길순이라는 걸 알았네요.

그게 도데체 무슨 소용이 있을까요?

왜 이런 캐삽질을 하는 걸까요?

미친건가요?

다시 아까 예를 들었던 50명짜리 반으로 돌아가겠습니다. 출석부에서 10번을 찾으면 "투루노"가 나오겠죠? 

이름을 몰라도 투루노를 찾을 수 있습니다. ^^*

(그냥 원래 데이타를 보고 말겠다구요? ㅎㅎㅎ)

저도 그렇게 생각합니다. ^^*

그럼 위의 함수를 유용하게 바꾸어 보지요.

reference가 B:B 에서 C:C로 바뀌었습니다. 단지 그것 하나만 바꾸었는데 이번엔 이름이 아닌 전화번호가 나옵니다.

우리가 이해하는 대부분의 데이타는 2차원 배열 (행렬)의 형태를 띄고 있습니다. (물론 수학이나 과학영역에선 보다 심도 깊은 차원도 많이 나오지만 여기 엑셀에선 일단 2차원까지만으로 이해하겠습니다.)

아까 예를 들은 출석부/월별 수입,지출/사원명부/거래장부/수업시간표/배차시간표/근무시간표/가계부/일자별주가 등등등 셀수도 없지요.

위의 예에서도 2차원을 넘어가는 녀석들도 있네요. 엑셀에서도 다차원을 다룰수는 있습니다. 시트가 그걸 지원하는 것이고 몇가지 꽁수로 다차원인것처럼 쓰면 되구요. ^^*

암튼 우리는 1차원 배열(array)에서 2차원(matrix)로 넘어왔습니다. 그리고 이런것들을 다루는 함수가 index/match/lookup/vlookup/hlookup이 있습니다.

1차원은 index/match로 2차원은 lookup계열 함수로 다룬다라고 이해하시면 조금 편하게 이해하시는 것일지도...

(물론 쓰다보면 구분은 없습니다. 제 강의 목표도 구분을 없애고 편한걸 쓰겠다니깐요)

자 다시 본론으로 들어가서 위의 index와 match의 조합으로 lookup함수와 비슷한 결과를 내었습니다.

=LOOKUP(E1,사원자료!B:B,사원자료!C:C)

에서 쓰인 lookup 과 같은 일을 index와 match만으로 구현해본것이죠. 한 개의 복합 함수로 정리하면 이렇게 되겠네요.

match의 결과값을 바로 index에 넣어주었습니다.

lookup이 더 편한것 같지요? 사실 lookup이 더 편합니다. ^^* 그럼 왜 lookup을 쓰지 어렵게 index/match같은걸 강의 했냐구요?

미리 말씀드리면 lookup은 정해진 역활 뿐이 못하지만 index/match로 분리해두면 뭔가 좀 더 일을 많이 할 수 있는 가능성이 생긴다고 할까요?

어떻게 그런일이 발생하는 지는 조금 더 진행해서 알아보도록 하지요..

아 그리고 숙제입니다. 위의 예제에서 E1을 홍길순 으로 하면 어떻게 되는지 함 확인해보세요. ^^*

-마음가는 길은 곧은 길-