엑셀(excel)함수 tip 25 - (indirect 및 vlookup함수로 원하는 값 추출하기)
엑셀(excel)에서 indirect함수는 텍스트 형태의 셀을 참조하여 실제 셀 참조로 바꾸는데 사용합니다. 즉 셀에 입력되어 있는 이름을 셀참조로 하여 사용할 때 사용합니다. 예를 들어 indirect(A6)에서 A6셀에 값이 있으면 그 값을 참조하고 셀범위(D2:D6)가 있으면 셀범위 참조, "판매현황"이라는 이름정의가 있으면 이름정의를 참조합니다.
▶ indirect함수 사용 예
하단에서 =INDIRECT(G5)는 G5에 있는 셀 B5를 참조하라는 의미입니다. 따라서 B5에 있는 값이 에어컨이므로 결과값이 에어컨으로 표시가 됩니다.
▶ 원본엑셀 함수
하단에서 2011년도와 2012년도의 데이터시트가 있습니다. 이 두 데이터를 참조로 하단부위의 [제품판매현황]에서 [해당연도]와 [품목]을 입력시에 수량. 단가, 금액이 자동으로 생성되도록 하는 법을 기술하겠습니다. 이때 이용하는 함수가 INDIRECT함수와 VLOOKUP함수입니다.
● 이름정의하기
하단과 같이 2011년도와 2012년도의 셀 값[품목,수량,단가,금액]에 대한 부분을 이름정의를 해 보도록 하겠습니다. (관련글 바로가기 ( ☞이름정의를 통한 빠른 수식계산) 이름정의를 하는 것은 하나하나의 셀을 선택할 필요 없이 정의된 이름을 입력하여 수식을 계산할 수 있기 때문입니다. 하단에서 (B4:E13)을 (_2011년도)로 (G4:J13)을 (_2012년도)로 정의하였습니다. 연도 앞에 _을 한 이유는 이름정의 규칙때문에 숫자로 시작할 수 없기 때문입니다.
☞ 이름정의 규칙
- 이름은 한글, 영어, 밑줄 중 하나로 시작한다
- 영어의 대/소문자를 구분하지 않는다
- 이름에는 공백을 사용할 수 없다
- '셀 참조주소'와 동일한 이름을 사용할수 없다
- 이름은 최대 255자까지 사용할 수 있다
● 데이터 유효성 설정하기
데이터의 유효성[관련글바로가기 : 데이터유효성검사/유효성설정은? ] 을 설정해 놓은 셀을 클릭시에 역삼각형이 생성이 되면서 해당목록을 선택할 수 있는 값들이 생성이 됩니다. 하단에서처럼 [해당 셀 선택 -엑셀편집메뉴의 데이터탭 - 데이터유효성 검사]를 클릭하면 데이터유효성 창이 생성이 됩니다. [제한대상]은 [목록]으로, [원본]은 [2011년도,2012년도]로 하고 [확인]을 합니다.
하단은 품목에 해단되는 셀에 대한 값을 데이터유효성으로 설정하는 과정입니다.[해당셀 선택 - 데이터유효성 창 - 제한대상(목록) - 원본($B$4:$B$13) - 확인]을 하면 됩니다.
데이터유효성을 설정한 결과를 보시면 하단의 품목 옆셀을 클릭시 우측의 역삼각형이 생성이 되면서 목록들이 생성이 됩니다. 이렇듯 유효성설정으로 하나하나의 값을 입력하지 않아도 역삼각형을 선택해서 해당값을 클릭하면 됩니다.
● INDIRECT, VLOOKUP함수 중첩하여 값 구하기
[C19셀 선택 - =VLOOKUP(C18,INDIRECT("_"&C16),2,0) - ENTER]를 하면 해당값(95)이 생성이 됩니다. 이 함수에서 보시는 것처럼 두개의 함수가 중첩되어 사용되었습니다. 의 의미를 해석해 보면 INDIRECT("_"&C16)의 의미는 INDIRECT(_2012년도)의 의미로서 이름정으로 되어 있는 [_2012년도]의 전체범위를 참조하라는 의미입니다.
=VLOOKUP(C18,INDIRECT("_"&C16),2,0)은 찾으려는 값은 C18이고, 이름정의된 [_2012년도]를 참조하여, 두번째열(수량)과 정확히 일치하는 값을 찾으시오 라는 뜻입니다. 따라서 C18값을 품목(팩스)로 선택하면 2012년도 2번째 열의 (95)가 결과값으로 나옵니다.
▶ 연도와 품목선택시 자동으로 값 생성
위와 같이 [데이터유효성 설정]과 [INDIRECT함수와 VLOOKUP]함수두가지를 이용하면 하단과 같이 간단한 클릭만으로 해당값들이 추출이 되어서 나타납니다. [연도]와[품목]의 원하는 값들을 선택을 하면 하단의 값들은 자동으로 생성(추출)이 됩니다.
