안녕하세요! 여러분의 칼퇴를 도와드리는 엑셀 블로거입니다. 😊
보고서 작업을 하다 보면 '이 방대한 데이터 표에서 딱 저 위치의 값만 쏙 빼오고 싶은데...' 하는 생각, 다들 한 번쯤 해보셨죠? 😥 수많은 데이터 속에서 원하는 값을 정확히 찾아내야 할 때, 엑셀의 숨은 보석 같은 함수 INDEX 가 여러분의 강력한 무기가 되어줄 수 있답니다!
오늘은 저와 함께 INDEX 함수의 기본부터 동적 범위를 만드는 놀라운 응용법까지 완벽하게 정복해 봐요.
📌 [핵심] INDEX 함수는 지정한 범위에서 행과 열 번호를 이용해 원하는 위치의 값을 정확히 찾아주는 '좌표' 함수예요.
🔍 엑셀 INDEX 함수, 대체 뭔가요? (기본 개념)
엑셀 시트를 거대한 아파트라고 생각해 보세요. INDEX 함수는 '몇 동 몇 호' 인지 알려주면 그 집에 누가 사는지 정확히 알려주는 것과 같아요. 즉, 우리가 지정한 데이터 범위(Array) 안에서 특정 행(Row)과 열(Column) 번호에 위치한 값을 반환해 주는 아주 직관적인 함수랍니다.
함수 구문 및 인수 설명
INDEX 함수는 아래와 같은 구조로 이루어져 있어요.
=INDEX(array, row_num, [column_num])- array: 값을 찾을 데이터 범위 전체를 의미해요. (예: A1:D10)
- row_num: array 범위 내에서 몇 번째 '행'에 있는 값을 가져올지 지정하는 숫자예요.
- column_num: array 범위 내에서 몇 번째 '열'에 있는 값을 가져올지 지정하는 숫자예요. (생략 가능)
⚠️ NOTE: row_num 과 column_num 은 엑셀 시트 전체의 행/열 번호가 아니라, 내가 지정한 array 범위 내에서의 상대적인 위치 라는 점! 꼭 기억해 주세요. 예를 들어 C5:E10 범위를 지정했다면, C5 셀이 1행 1열이 되는 거예요.
🚀 INDEX 함수 실전 예제 따라하기
몇가지 적용 예를 살펴봅니다. 직접 예제를 통해 INDEX 함수와 친해져 봐요.
예제 1: 기본 사용법 (좌표로 값 찾기)
가장 기본적인 기능이죠. 아래 분기별 제품 판매량 데이터에서 '노트북' 의 '2분기' 판매량을 직접 좌표를 찍어 찾아보겠습니다.
[데이터]
제품명,1분기,2분기,3분기,4분기
모니터,150,180,220,200
키보드,300,320,350,380
마우스,500,550,600,620
노트북,80,95,110,130
웹캠,200,210,230,240
헤드셋,120,130,140,150
스피커,90,100,110,120'노트북' 은 데이터 범위(A1:E8)에서 5번째 행에 있고, '2분기' 는 3번째 열에 있습니다.
[수식]
=INDEX(A1:E8, 5, 3)
결과: 95
설명: A1:E8 범위에서 5행 3열에 있는 값을 정확히 찾아왔어요. INDEX 함수의 가장 기본적인 원리랍니다.
예제 2: 중급 활용 (동적 범위 만들어 합계 구하기)
INDEX 함수의 진짜 매력은 셀 주소 자체를 반환하는 능력에 있어요. 이를 이용해 유동적인 범위를 만들어 다른 함수와 조합할 수 있습니다. 예를 들어, '키보드' 부터 '웹캠' 까지의 1분기 판매량 합계를 구해볼까요?
[데이터]
제품명,1분기 판매량
모니터,150
키보드,300
마우스,500
노트북,80
웹캠,200
헤드셋,120
스피커,90
[수식]
=SUM(INDEX(B2:B8, 2):INDEX(B2:B8, 5))
결과: 1080 (300 + 500 + 80 + 200)
설명:
- INDEX(B2:B8, 2) 는 B2:B8 범위의 2번째 값, 즉 B3 셀을 가리킵니다.
- INDEX(B2:B8, 5) 는 B2:B8 범위의 5번째 값, 즉 B6 셀을 가리킵니다.
- 이 두 개를 콜론(:) 으로 연결하여 =SUM(B3:B6) 이라는 동적인 범위를 만들어 합계를 구한 거예요. 보고서의 조회 기간이나 품목이 계속 바뀔 때 정말 유용하겠죠?
예제 3: 고급 활용 (특정 행/열 전체 데이터 활용하기)
INDEX 함수에서 row_num 이나 column_num 인수를 0 으로 설정하거나 비워두면, 해당하는 열 또는 행 전체를 배열로 가져오는 아주 멋진 기능이 있어요! '3분기' 의 평균 판매량을 계산해 보겠습니다.
[데이터]
제품명,1분기,2분기,3분기,4분기
모니터,150,180,220,200
키보드,300,320,350,380
마우스,500,550,600,620
노트북,80,95,110,130
웹캠,200,210,230,240
헤드셋,120,130,140,150
스피커,90,100,110,120
[수식] (최신 엑셀 버전에서는 그냥 Enter, 구 버전에서는 Ctrl+Shift+Enter)
=AVERAGE(INDEX(B2:E8, 0, 3))
결과: 251.4 (소수점은 표시에 따라 다를 수 있어요)
설명:
- INDEX(B2:E8, 0, 3) 수식에서 row_num 을 0 으로 지정했어요.
- 이렇게 하면 B2:E8 범위의 3번째 '열' 에 해당하는 D2:D8 범위(220부터 110까지) 전체를 하나의 배열로 가져옵니다.
- 마지막으로 AVERAGE 함수가 이 배열의 평균값을 계산해 주는 원리랍니다. MAX, MIN, SUM 등 다른 배열 함수와도 환상적인 궁합을 자랑해요!
예제 4: 실무 활용 (배송비 조견표에서 교차 값 조회하기)
출발지와 도착지에 따라 달라지는 배송비 조견표에서 원하는 구간의 요금을 쏙쏙 뽑아오는 예제예요. 실무에서 아주 유용하게 쓸 수 있겠죠? '부산' 에서 출발해서 '제주' 로 가는 배송비를 찾아보겠습니다.
[데이터]
,서울,대전,부산,광주,제주
서울,5000,8000,10000,9000,12000
인천,5000,7000,11000,8000,13000
대전,8000,5000,6000,5000,10000
부산,10000,6000,5000,7000,9000
대구,9000,5000,5000,6000,10000
광주,9000,5000,7000,5000,11000- 출발지 '부산' 은 데이터 범위(B2:F7) 내에서 4번째 행입니다.
- 도착지 '제주' 는 데이터 범위(B2:F7) 내에서 5번째 열입니다.
[수식] (수식은 같은 결과, 내려갈수록 동적 수식)
=INDEX(B2:F7, 4, 5) // 예제4-1 직접 부산위치 넘김
=INDEX(B2:F7, MATCH("부산",A2:A7,0), MATCH("제주",B1:F1,0)) // 예제4-2 부산,제주를 검색해 위치반환
=INDEX(B2:F7, MATCH(H5,A2:A7,0), MATCH(I5,B1:F1,0)) // 예제4-3 데이터유효성 검사를 활용한 동적 참조
결과: 9000
설명: 복잡한 함수 조합 없이, 행과 열 번호만 정확히 알면 거대한 매트릭스 표에서도 원하는 값을 정확히 찾아낼 수 있어요. 입력 셀을 따로 만들어 행/열 번호를 넣거나 지역을 넣고 MATCH 함수로 검색하여 위치를 얻을 수 있고, 더 나아가면 데이터유효성 검사를 활용해 동적 활용이 가능한 더 편리한 조회 시트를 만들 수 있답니다.
😥 이런! INDEX 함수 오류, 왜 발생할까요?
- #REF! 오류: row_num 이나 column_num 이 array 범위를 벗어났을 때 발생해요. 예를 들어 10행까지밖에 없는 범위에서 11번째 행을 찾으라고 하면 이 오류가 떠요. 범위를 다시 확인해 보세요!
- #VALUE! 오류: 함수의 인수가 잘못되었을 때 발생해요. row_num 이나 column_num 에 숫자가 아닌 텍스트 등이 들어가진 않았는지 확인해 보세요.
🌞 함께 쓰면 더 강력한 함수들
- SUM, AVERAGE, MAX: 합계,평균,최대값을 구하는 함수로, INDEX 로 가져온 동적 범위나 배열을 최종 계산하는 데 사용됩니다.
- ROW, COLUMN: 특정 셀의 행/열 번호를 알려주는 함수로, INDEX의 row_num, column_num 인수에 동적으로 값을 넣어줄 때 유용합니다.
- MATCH: 특정 값의 상대적 위치(행/열 번호)를 찾아주는 함수입니다. INDEX와 조합하면 VLOOKUP 을 뛰어넘는 최고의 조회 기능을 만들 수 있습니다.
- XLOOKUP: 최신 엑셀(Microsoft 365, 2021)의 끝판왕 조회 함수로, INDEX+MATCH 조합의 편리함을 하나로 합쳤습니다.
🎉 INDEX 함수, 오늘부터 여러분의 최애 함수!
오늘은 엑셀 데이터 추출의 핵심, INDEX 함수에 대해 배워봤어요. 단순히 특정 값을 찾는 것을 넘어, 동적 범위를 만들고, 행이나 열 전체를 배열로 다루는 방법까지! INDEX 함수는 여러분이 생각하는 것보다 훨씬 더 다양하고 강력한 잠재력을 가지고 있답니다.
직접 예제를 따라 해보는 것이 가장 중요해요! 오늘 배운 내용을 여러분의 업무 파일에 꼭 한번 적용해 보세요. 궁금한 점은 언제든 댓글로 남겨주시고, 오늘 내용이 유용했다면 공감과 구독도 잊지 마세요! 여러분의 칼퇴를 응원합니다. 😉
작성자: 마늘빵
📱 모바일에서 빠르게 다시보기
💥 INDEX란?
지정한 범위(Array)에서 행(Row), 열(Column) 번호로 값을 찾는 '좌표' 함수.
✒ 핵심 기능 1: 동적 범위 생성
SUM(INDEX(...):INDEX(...)) 형태로 유동적인 합계 범위를 만들 수 있음.
💦 핵심 기능 2: 배열 반환
AVERAGE(INDEX(범위, 0, 열번호)) 형태로 특정 열 전체의 평균 등을 쉽게 계산 가능.
❗ 주요 오류:
#REF! (범위 초과), #VALUE! (인수 형식 오류).
