엑셀에서 INDIRECT 와 INDEX 함수를 함께 사용하는 좋은 예제를 준비해 드리겠습니다. 이 조합이 어떻게 동적 참조를 가능하게 하는지 실제 데이터로 보여드리겠습니다. 실습하면서 INDIRECT 함수와 INDEX 함수 조합의 편리함을 느끼고 활용해 보세요.
이전 "INDIRECT 함수의 동적참조" 게시물의 고급예제 부분을 좀 더 보강한 1번째 예제 입니다.
이 예제는 여러 시트에 있는 판매 데이터를 동적으로 참조하여 특정 제품의 판매 정보를 찾는 상황입니다.
예제 데이터
시트 1: "전자제품"
| 제품코드 | 제품명 | 가격 | 재고량 |
|---|---|---|---|
| E001 | 스마트폰 | 850000 | 25 |
| E002 | 노트북 | 1200000 | 15 |
| E003 | 태블릿 | 550000 | 30 |
| E004 | 스마트워치 | 320000 | 40 |
| E005 | 블루투스 이어폰 | 180000 | 50 |
시트 2: "가전제품"
| 제품코드 | 제품명 | 가격 | 재고량 |
|---|---|---|---|
| H001 | 냉장고 | 1500000 | 10 |
| H002 | 세탁기 | 900000 | 12 |
| H003 | 에어컨 | 1300000 | 8 |
| H004 | 전자레인지 | 150000 | 20 |
| H005 | 청소기 | 350000 | 25 |
시트 3: "주방용품"
| 제품코드 | 제품명 | 가격 | 재고량 |
|---|---|---|---|
| K001 | 프라이팬 | 45000 | 30 |
| K002 | 전기밥솥 | 120000 | 15 |
| K003 | 믹서기 | 85000 | 20 |
| K004 | 토스터 | 50000 | 25 |
| K005 | 커피메이커 | 75000 | 18 |
시트 4: "메인" (사용자 입력 및 결과 표시)
| 카테고리 | 제품코드 | 결과 |
|---|---|---|
| 전자제품 | E002 | 수식 결과가 표시됨 |
| 가전제품 | H004 | 수식 결과가 표시됨 |
| 주방용품 | K003 | 수식 결과가 표시됨 |
🔥수식 설명
"메인" 시트의 C2, C3, C4 셀에 아래와 같은 수식을 사용해요.
=INDEX(INDIRECT(A2&"!A1:D6"), MATCH(B2, INDIRECT(A2&"!A1:A6"), 0), 3)이 수식은:
ㄱ. A2 에 입력된 카테고리명(예: "전자제품")을 시트 이름으로 사용합니다. ( "전자제품!A1:D6" )
ㄴ. B2 에 입력된 제품코드(예: "E002")를 해당 시트에서 찾습니다. ( MATCH 함수의 결과로 3, INDEX 함수의 행으로 사용 )
ㄷ. 찾은 제품의 가격(3번째 열)을 반환합니다. ( 전자제품 시트 A1:D6 범위의 3행 3열의 값이 결과)
작동 원리
ㄱ. INDIRECT(A2 & "!A1:D6")는 A2 셀에 있는 텍스트를 시트 이름으로 사용하여 해당 시트의 A1:D6 범위를 참조합니다.
ㄴ. MATCH(B2, INDIRECT(A2 &" !A1:A6"), 0) 는 B2 셀의 제품코드가 해당 시트의 A열에서 몇 번째 행에 있는지 찾습니다.
ㄷ. INDEX 함수는 찾은 행 번호와 3번째 열(가격)을 이용해 정확한 가격 값을 반환합니다.
결과
- C2 셀: 노트북(E002)의 가격인 1,200,000원이 표시됩니다.
- C3 셀: 전자레인지(H004)의 가격인 150,000원이 표시됩니다.
- C4 셀: 믹서기(K003)의 가격인 85,000원이 표시됩니다.
이런 방식으로 사용자는 카테고리와 제품코드만 입력하면 여러 시트에 분산된 데이터를 자동으로 찾아올 수 있습니다. 카테고리명을 변경하면 다른 시트의 데이터를 참조하게 되고, 제품코드를 변경하면 다른 제품의 정보를 찾아오게 됩니다.
이 방법은 대규모 데이터를 여러 시트에 분산 저장하고 있을 때 특히 유용합니다.
작성자: 마늘빵
