[엑셀] 데이타 베이스에 도전한다. lookup / vlookup /hlookup

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

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

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

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

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

 

-마음가는 길은 곧은 길-