trueonot.com

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

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

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하고는 반대로 동작합니다. (반대로는 좀 어색하고 뒤집어서 라고 할까요? 전문용어로 하면 오소고날인데... ㅎㅎ)

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

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

-마음가는 길은 곧은 길-

오늘 배울 함수는 match와 index입니다.

이것이 오늘 배우고자 하는 내용입니다. 지난 시간에 공부한 lookup 예제를 다시 풀어보는 것이죠. 이번에는 참조를 쉽게 하기 위해서 같은 시트에서 작업합니다. 그래야 직접 눈으로 보면서 이해하기 쉬우니깐요.

그리고 위의 그림은 결과 페이지입니다.

먼저 Match 함수가 무엇인지에 대해 알아보지요.

도움말에 설명이 잘 되어 있군요. lookup_value/lookup_array 등 많이 보던 녀석도 보이네요. match_type이란것도 보이구요.

익숙하지 않다면 지난 강의를 다시 한번 보세요.

lookup_value는 lookup()에서와 같습니다. 배열상에서 찾고자 하는 값이죠.

lookup_array는 검색할려는 대상입니다. lookup에서의 lookup_vector와 같습니다.

match_type은 조금 있다 설명하지요.

array라는 용어가 등장했는데 우리말로 번역하면 배열입니다. 그러나 여러분들은 그냥 단순히 "레인지" 하고 같다 라고 이해하시면 됩니다. (깊게 들어가면 차이가 있지만 굳이 그 차이까진 모르셔도 됩니다.)

즉 위의 예제에선 "홍길동,홍길순,투루노,라라루" 가 lookup_vector/lookup_array 가 되는 것입니다. 그리고 검색어 lookup_value가 있는지 있다면 몇번째 위치에 있는 지를 알려주는 것이 바로 match 펑션이 하는 일입니다.

match_type은 찾고자 하는 검색어를 좀 더 명확히 해주는 인자값입니다. 값은 -1 , 0, 1 이 될 수 있으며 디폴트 값은 1입니다. 자세한 건 직접 도움말을 참조하시고 여기서 쓸 옵션은 '0' 입니다.

0 은 해당 하는 자료중 중복되는 자료가 있어도 상관하지 않고 그 첫번째 자료를 리턴해줍니다.

만약 투루노 주식회사에 홍길순 이라는 같은 이름의 직원이 3명이 있다고 해도 그 중 첫번째 값만을 리턴해줍니다. 실제로 한번 볼까요?

홍길순 사원이 여러명입니다. 그러나 결과값은 2입니다.

B:B 라고 해서 나중에 사원이 많이 늘어라더라도 수식을 수정하지 않도록 했습니다.  그러다 보니 1행의 사원/이름/전화번호 까지 카운트가 되기때문에 결과값은 2 가 아니라 3이 되었습니다.

"이름/홍길동/홍길순/투루노/라라루....." 이렇게 배열이 되고 홍길순은 3번째 항목이 되는군요

만약 0이 아닌 값을 match_type으로 쓰기 위해선 정렬을 해주어야 합니다. 그리고 본인이 계속 진행하게 될 최종 목표에서도 정렬을 하는 것이 더욱 좋은 결과를 낼 수 있습니다.

정렬이 안되어 있을 경우에는 원하는 값이 정확히 나오지 않을 수 있으니 가능하면 정렬을 해주시기 바랍니다.

여기서 잠깐 DataBase에 대해서 맛만 보고 지나가도록 하겠습니다.  엑셀은 어떤면에서 데이타베이스를 많이 닮아 있습니다. 데이타베이스란 자료를 체계화하여 저장하는 것이라고 할까요? (물론 db를 정의하기 위해선 이 한문장으로는 매우 부족합니다. ^^*)  일반적으로 생각할때 위의 사원 자료도 훌륭한 데이타 베이스입니다. 쉽게 이해한다면 표는 데이타베이스다 라고 이해하셔도 될껍니다.

여기서 필드/레코드 하는 db용어가 등장하는 데 레코드는 사원 한명 한명을 의미합니다. 즉 한 행 한행의 데이타를 가르킨다고 할까요? 필드란 각 레코드의 구성요소로서 "사원번호, 이름, 전화번호" 등이 필드가 됩니다. 이와는 다른 말로도 불리기는 하는데 엑셀에서는 이렇게 쓰이고 있습니다. (향후 db로도 확장됩니다.)

그리고 키 필드리는 것이 있습니다. 각각의 레코드를 고유하게 하기 위한 값이 저장된 필드라고 할까요? 여기선 사원번호가 그에 해당합니다. 우리가 항상 쓰는 주민번호도 대한민국 국민 모두에 대해 유일하지요? 훌륭한 키 필드가 됩니다.

지금 굳이 db용어에 대해서 설명드린 이유는 위와 같은 자료를 만들때 이러한 키 필드가 있어야 관리하기가 편리합니다. 만약 위의 자료에서 match함수를 쓰기 위해 이름순으로 정렬하였다면 나중에 다시 원래 대로 어떻게 정렬 할 수 있을까요? (물론 이게 키 필드의 목적은 아닙니다.)

키 필드가 있으면 유용하겠지요? 키 필드는 꼭 필요한 것은 아닙니다. 그리고 여러개의 필드가 모여서 키를 구성할 수 도 있지요.

너무 깊게 들어가면 창 닫아 버릴지도 모르니 이쯤에서 접겠습니다. 그냥 이런게 있다 라고만 알고 넘어가면 될 듯 하네요. ^^*

이상으로 match가 무언지 알아봤습니다. 그럼 index를 알아볼까요?

아핫 내릴 시간이 되었습니다. ^^*

투루노는 퇴근하면서 버스안에서 이 글을 작성하다보니. ㅎㅎㅎ

지금은 컴퓨터를 꺼야 합니다. ^^*

그래서 index는 다음 시간에.. 절대 욹어먹기가 아닙니다. ^^* ㅎㅎㅎ

-마음가는 길은 곧은 길-