엑셀 배우기, 이름정의와 INDIRECT/ VLOOKUP 함수 활용, 목록과 드롭다운 표시!

지난 포스팅, 엑셀배우기, DATE/EDATE/RANDBETWEEN/VLOOKUP 함수 활용 및 셀서식! 에서 기초 참조 함수인 VLOOKUP 함수를 다룬 차에, 이번 포스팅에서는 VLOOKUP 함수와 함께 사용하면 굉장히 유용한 INDIRECT 함수를 이야기해 하겠습니다. 같은 참조 함수입니다만, 둘의 차이를 간단히 말하자면,  VLOOKUP 함수가 특정 셀값을 참조한다면, INDIRECT 함수는 텍스트로 지정된 특정 범위를 참조합니다.

그럼 어떠한 경우에, INDIRECT 함수를 사용하는게 좋을까요? 왜 특정 범위를 참조할 필요가 있을까요? 이 범위를 때때로 바꿔야할 필요가 있기 때문입니다. 이른바 가변 범위 참조입니다.

VLOOKUP 함수는 항상 고정된 지정 범위 내에서 특정 셀값을 참조하는 함수인데, 이 지정 범위를 계속 바꿀 필요가 있다면, VLOOKUP 함수를 구성할때, 참조할 범위에 INDIRECT 함수를 넣어서, 가변 범위에 대응할 수 있다는 의미가 됩니다. 예를들어, 인보이스를 만들어야 하는데 참조할 가격표가 두가지 이상이라고 한다면, 참조할 범위도 두가지 이상이란 이야기고 참조할 범위를 계속 바꾸어 주어야 한다는 이야기입니다.

아니면, 두 가격표 각각 인보이스 양식을 별도로 만들어도 되겠지요. 하지만 VLOOKUP 함수 내에 INDIRECT 함수를 사용하여 참조할 범위를 구성하면, 하나의 통합 인보이스만 사용해도 된답니다! 

일단 아래 작업 파일을 보면서 천천히 다시 설명해 보도록 하겠습니다. 본론으로 들어가기전에, INDIRECT 함수는 앞서, '텍스트로 지정된' 특정 범위를 참조하는 함수라 말씀드렸습니다. 텍스트로 지정되었다는 것은 즉, 특정 범위에 '이름'을 부여했다는 이야기입니다. INDIRECT 함수 사용 전에 '이름 정의'로  특정 범위에 이름을 부여하는 작업을 미리 해두어야 합니다.

예를 들어, 아래에서 빨간 점선 사각형으로 표시된 '가형 가격표' 범위에 '가'라는 이름을 부여하였습니다. 다시말하면, '=INDIRECT(가)'는 결국 B3:L6의 범위가 되는 것입니다. 마찬가지로, '나형 가격표'의 범위, B9:L12에 '나' 라는 이름을 부여해 봅시다. 

상단 메뉴, 수식의 '이름 관리자' 메뉴를 눌러보면, 아래와 같이 이름 정의가 완료되어 있음을 확인할 수 있습니다. 이제 필요한 두가지 범위에 각각의 이름 정의, '가'와 '나'를 마쳤으니, VLOOKUP 함수를 통해 특정 셀값을 찾아보도록 하겠습니다.

 

 

익히 아시겠지만, VLOOKUP 함수 구성은 몹시 간단합니다. '=VLOOKUP(찾을 값, 참조할 범위, 지정한 범위에서 참조할 값의 열 번호, FALSE), 'FALSE'는 정확히 일치하는 값을 불러온다는 의미입니다. 그리고 VLOOKUP 함수 내에서 INDIRECT 함수가 들어갈 곳은 '참조할 범위' 입니다. 이제 아래의 H17셀의 수식을 풀이해 보도록 하겠습니다. 우리가 필요한 것은, 가형의 가격표에서 A 조건의 2번째 가격입니다.

그렇다면, VLOOKUP 함수를 사용해서,  '가'라는 범위 (가형 가격표 범위 / '가'로 이름 정의된 범위) 내에서, '기준 값'은 'A'로 하고 (A행의 데이터 중에서 / D17셀), '2' 조건의 열 값 (지정 범위 '가'의 2번째 열은 C열이고, 지정 범위 내에서 '2' 조건은 3열인 D열이므로, F17+1 / B열이 열 번호 1번, C열이 열번호 2번, D열이 열번호 3번)을 참조해야합니다. 

 

INDIRECT 함수는 굉장히 유용한 함수인데, 아무래도 설명이 좀 부족하지 않았나 싶네요. 이해되지 않는 부분이 있다면, 아래에 댓글 남겨주세요. 나름 성실하게 답변 드리도록 하겠습니다! 마지막으로, 보너스! 작업 파일 만들면서 셀값을 특정 값들로 지정하여 목록을 만들고 해당 목록에서만 값을 선택할 수 있는 '드롭다운'기능도 사용해 봤는데요. 상단 메뉴의 '데이터 - 데이터 유효성 검사'로 아래의 팝업 창을 활성화하면 된답니다.

그리고, '제한 대상'을 '목록'으로 선택하고, '드롭다운 표시'에 체크 마크후, '원본'에 사용할 특정 값들을 콤마 사용해서 순차적으로 입력해 주시면 됩니다! 그러면 아래와 같이 간단히 드롭 다운이 완성됩니다!