티스토리 뷰


어제 포스팅했던 피벗 테이블은 대용량 데이터에서 필요한 자료를 원하는 양식으로 추출하는 함수였습니다.

오늘은 VLOOKUP() 함수를 이용하여 데이터 중 필요한 '항목' 을 추출하는 방법에 대해 알아보도록 하겠습니다.

VLOOKUP() 함수는 엑셀에서 모르면 바보;; 라는 말도 있을 정도로 정말 다양하게 사용되고 있습니다.

일일이 찾아서 복사해서 붙여넣기를 하는 작업을 단순화 하여 업무 시간의 단축에 엄청난 역할을 하고 있죠~

다른 곳에서 원본 데이터 목록 내용 중 동일한 어떠한 값을 찾아내거나, 아니면 그 행의 다른 값을 찾아낼 때 주로 사용을 합니다.

우선 기본 문법에 대해 알아보도록 하겠습니다.

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])


실제 엑셀 빈 통합문서에서 "=vlookup(" 이라고 치면 친절하게도 함수의 인수값과 인자값을 넣을 수 있도록 인수에 대한 설명을 보여주게 됩니다.


이렇게 간단하게 설명을 해주지만 우리는 잘 모르는 거죠잉~^^;

그래서 아래에서 예를 들어 설명해 드리도록 하겠습니다.

.



일단 아래의 설명을 잠깐 읽어보시고 넘어가도록 하겠습니다.

 

lookup_value : 찾고자 하는 가장 첫번째의 칼럼 값을 의미합니다. (기준이 되는 값을 의미합니다)

table_array : 찾고자 하는 값이 포함되어 있는 원본 테이블의 범위 (보통 범위는 한정되어 있기 때문에 절대값 $ 로 입력합니다)

col_indes_num : 찾고자 하는 칼럼을 의미합니다. (table_array 에서 지정한 범위 중 값을 불러올 열에 해당하는 열 순번을 입력합니다. 1번째 열에서 추출하려면 1을, 3번째 열에서 추출하려면 3을 입력합니다)

[range_lookup] : 0(FALSE)을 넣으시면 정확히 일치하는 값을, 1(TRUE)을 넣으시면 비슷한 값을 찾습니다.


먼저 아래 엑셀 데이터 시트에서 '볼펜' 항목의 '가격'을 찾아보도록 하겠습니다.

물론 함수 마법사를 이용하여 팝업창에서 가시적으로 구현을 할 수도 있지만, 간단한 원리와 인수들의 순서만 알고 있다면 직접 함수를 입력함으로써 빠른 업무 처리가 가능하게 됩니다.

VLOOKUP() 함수를 설명해 드리기 위해 간단한 자료로 보여드리겠습니다.


하나의 화면에서 알려드릴 수 있도록 동일한 시트에 설명을 하도록 하겠습니다.
(보통은 대용량의 다른 시트나 다른 파일에서 추출하는 방법을 많이 사용합니다)

오른쪽 빈 칸에 =vlookup( 함수로 시작을 합니다.


찾을 항목인 '볼펜' 항목이 lookup_value 인수에 해당하며,

table_array 인수에는 모든 항목의 영역이 선택되게 됩니다.

그리고 col_index_num 인수에는 찾을 행에서 칼럼에 해당하는 2 (두번재 칼럼)를 입력하게 됩니다.

[range_lookup] 인수에는 정확히 일치하는 값을 찾기 위해 FALSE(0) 을 입력합니다.

아래는 모든 인수를 입력한 후의 화면입니다.


인수를 모두 입력한 후 엔터를 누르게 되면 VLOOKUP() 함수는 해당 행의 볼펜 값에 해당하는 2번째 칼럼의 700 이라는 값을 반환합니다.

최종 완료된 화면입니다.


이렇게 VLOOKUP() 함수는 가로 (행) 의 기준으로 몇번째에 위치하는 칼럼의 값을 찾아내는 기능을 수행합니다.

그리고 잠시 설명해드릴 함수는 바로 HLOOKUP() 함수인데요, 이 함수 역시 동일한 기능을 수행하지만 가로(행)의 기준이 아닌 세로(열)의 기준으로 값을 추출해 내게 됩니다.


HLOOKUP 함수의 사용법은 위와 같으며 VLOOKUP 함수와 동일한 인수값이지만 세로(열)의 순번에 따른 칼럼의 값을 추출해 내게 됩니다.

간단히 설명을 드렸지만, 시간이 흐르면 금방 잊어버리게 되는 함수입니다.

특히 함수 인자의 순서를 헷갈려하시는 분들이 많은데요, 그러할 때 나타날 수 있는 에러들과 기본적으로 유의해야 하실 부분을 알아보도록 하겠습니다.

1. 범위를 지정한 원본 데이터는 현재 VLOOKUP 함수가 위치한 곳보다 좌측에 위치해야 합니다.
(보통 다른 워크시트, 다른 엑셀파일에서 진행되지만, 동일한 시트에서 수행할 때에는 조심하셔야 합니다)

2. 입력하시는 부분의 lookup_value 를 추출할 때 공백이나 특수문다 등이 포함되지 않도록 해야 합니다 (TRIM 함수를 이용하여 쉽게 공백 제거 가능)

3. #N/A (Not avaible values)가 출력될 때 : 보통은 table_array 부분의 영역 지정에 오류가 있을 경우입니다.

4. #VALUE! : 다른 모든 함수를 사용할 때 자주 나타나는 오류메시지로, col_index_num 칼럼의 지정 오류나, 인수들의 순서가 뒤바뀔 때 나타나는 여러가지 이유가 있을 수 있습니다.

5. #REF! : col_index_num 칼럼의 순번을 지정할 때 범위로 지정된 2번째 인수인 table_array 영역에 해당되지 않을 때 나타나는 함수입니다. 다른 함수들을 사용하실 때에도 범위 지정 또는 한계 등의 오류가 있을 때 나타나게 됩니다.

이 외에도 여러가지 에러 발생의 경우가 있겠지만, 모두 다 설명해 드릴 수는 없겠네요;;

이해해 주시기 바랍니다~^^

-----

들러주셔서 감사합니다 (__)



댓글