trueonot.com

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

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을 홍길순 으로 하면 어떻게 되는지 함 확인해보세요. ^^*

-마음가는 길은 곧은 길-

제목이 너무 거창한듯 하네요. ^^*

어쩌면 이번 주제는 좀 어려울 수 있습니다. 일단 하나씩 분석하고 차이점을 이해해 보는 방향으로 진행해보려고 합니다.

lookup계열 함수는 다른 함수로 분해해서 이해할 수 도 있으며 이 렇게 분해하는 것이 제 강의의 1차 목적지가 될 거 같습니다.

그럼 lookup이 무언지 부터 살펴보겠습니다.

도움말을 참조하면 아래와 같이 설명하고 있습니다.

벡터는 행이나 열을 한 개만 포함하는 배열입니다. LOOKUP의 벡터형은 한 개의 행이나 한 개의 열로 이루어진 범위(벡터)에서 값을 찾고, 한 개의 행이나 한 개의 열로 이루어진 두 번째 범위의 같은 위치에서 값을 반환합니다. 찾으려는 값을 포함하는 범위를 지정하려면 LOOKUP 함수의 벡터형을 사용합니다. 범위를 지정하지 않고 첫 번째 행이나 열에서 값을 찾으려면 배열형을 사용합니다.

LOOKUP(lookup_value,lookup_vector,result_vector)

이 외에도 추가적인 설명이 있습니다. (배열형이니 뭐니 하는) 그러나 엑셀을 쓰면서 배열형같은 경우를 쓰는 경우는 그리 없으리라 예상됩니다.

그럼 lookup함수란 무엇일까요? 위의 설명만으로는 이해하기가 쉽지 않군요.

쉽게 설명하자면 많은 자료가 있고 그 자료중에서 원하는 자료를 찾아내기 위한 함수입니다. (더 어려운가요?)

실제예를 들어보겠습니다.

예상되는 상황은 사원번호 성명 전화번호 (간단히 해서..) 의 자료가 있는 엑셀 파일이 있습니다.

사원번호를 치면 그 사람의 이름과 전화번호를 알려주는 예제를 한번 만들어보겠습니다.

사원자료입니다. 투루노 주식회사로 사원은 4명입니다. ㅎㅎ (물론 다 가라 입니다. 사장혼자 다 말아먹구 있지요)

사원자료 시트를 저런식으로 구성해 놓았다고 예를 들겠습니다. 실제 자료에선 더 많은 데이타가 있을 수 있지요.

위와 같은 사원검색용 페이지를 만듭니다. 같은 페이지에 만들어도 좋지만 일반적으로 DB자료는 따로 분리해두시는 편이 관리하시기 편리합니다.)

이렇게 하고 위에다가 사원번호를 넣으면 이름과 전화번호가 검색되도록 하고 싶습니다.

=LOOKUP(B1,사원자료!A:A,사원자료!B:B)

함수는 위와 같습니다.

첫번째 인자는 lookup_value로서 검색하고자 하는 값이 됩니다. 두번째 인자는 레인지로서 검색할 대상이 되지요, 세번째는 결과값을 보여주는 인자입니다.

첫번째 자료 페이지와 함께 비교해보시기 바랍니다.

여기서 결과값은 생략가능하며 생략하면 두번째 인자에서 매칭되는 값을 결과값으로 보여줍니다.

어떤 결과가 나올까요? 궁금하네요.

헐. 아무런 의미가 없는것 같지요?... 그냥 =B1 하고 말지... 그러나 주어진 데이타(lookup_value)와 매칭되는 것이 없을 경우는 에러를 리턴해서 정해진 값이 있는지 확인하는데 도움을 줄 수 있습니다.

세번째 전화번호 입니다. 검색 대상은 똑같으며 결과값(result_vector)만 바꾸었습니다.

아 검색대상(lookup_vector)와 결과값(result_vector)가 좀 이상하다구요?

"사원자료!A:A" 첨보는 주소 형식인가요?

사원자료! 는 현재 시트가 아닌 다른 시트를 의미합니다. A:A는 A열 전체를 의미하지요. 실제데이타는 꼴랑 4개뿐이면서 왜 전체를 했을까요?

나중에 데이타가 추가되거나 할때 수식을 수정할 필요가 없으므로 편리하답니다.

그럼 좀 더 실용적인 예제를 들어볼까요?

검색을 할 때 사원번호를 알고 누구를 찾는 경우는 극히 드물지요. 이름은 아는데 전화번호가 궁금할 때가 있습니다.

그럼 어떻게 하면 될까요?

이번에도 스스로 생각해보기입니다.

생각좀 하시라구요.!!!

ㅎㅎㅎ

쉽게 가르쳐 주지 않을 껍니다.

ㅎㅎ 이제 길게 내려 쓰는것도 식상하지요? 뭔가 다른 방법을 찾아봐야 할듯^^*

먼저 결과 페이지 입니다.

그림 처럼 이름 칸에 이름을 입력하면 전화번호가 튀어나와야 합니다.

ㅎㅎㅎㅎ

수식은 위와 같습니다. 즉 lookup_vector가 사원의 이름이 되는 것이죠.

비슷한 응용을 통해 주소라던지 하는 정보등도 계산해 낼 수 있겠지요?

그럼 강의가 조금 길어지는 것 같은데. 서두에 선 보였던 vlookup/hlookup은 무엇일까요?

이 페이지를 보시면 앞의 페이지와 결과가 동일합니다.

그럼 정말 같은 것 일까요? 수식을 보시죠. 수식이 틀려졌습니다.

첫번째 인자 B1은 절대 참조로 바꾸었습니다. (별 의미는 없습니다. 다만 수식을 복사할때 편할 뿐이죠)

그리고 두번째인자가 아까와는 달리 일정범위의 형태로 바뀌었습니다. 즉 아까는 열 이었다면 이번엔 테이블 이네요. 도우말에도 table_array라고 되어 있습니다.

세번째 인자는 그냥 숫자값이군요.

vlookup은 vertical look_up으로 lookup을 조금 더 쓰기 편하게 만든 함수입니다. 어떤게 편한지 한번 볼까요?

첫번째 인자는 동일하므로 그냥 넘어갑니다. 두번째 인자가 특이하지요 열과 행으로 이루어진 테이블 형태입니다.

vlookup에선 이 테이블의 첫번째 열을 lookup에서의 lookup_vector로 활용합니다. 테이블로 이루어진 데이타에서 테이블만 한번 넣으면 되니 더 편하지요

세번째 인자는 index라는 것으로서 해당 테이블에서 몇 번째 값을 결과값으로 취할 것인지를 가르킵니다.

다시 말하면 테이블의 몇번째 열인가 입니다. 검색 기준열인 lookup_vector가 되는 첫번째 열을 '1'로 해서 하나씩 증가합니다.

이름은 2번째 열이니 2이고 전화번호는 3입니다.

실제로 전화번호를 검색하는 함수를 보지요.

B2의 함수와 완전히 같은데 index만 바뀌었습니다.

hlookup은 vlookup하고는 반대로 동작합니다. (반대로는 좀 어색하고 뒤집어서 라고 할까요? 전문용어로 하면 오소고날인데... ㅎㅎ)

즉 행과 열이 뒤바뀐 상태로 검색을 하게 됩니다. 실제로 해보니 그런 식으로 만드는 표는 익숙하지 않기 때문에 잘 쓰지는 않게 되더군요..

과연 어떤것이 더 편한지는 쓰는 사람 맘이고 용도에 따라서 다르겠지요... ㅎㅎ

-마음가는 길은 곧은 길-