엑셀의 INDEX함수는 테이블이나 범위에서 값 또는 값에 대한 참조를 반환한다. INDEX 함수에는 배열형과 참조형이라는 두 가지 형식이 있다.
이 함수는 단독으로 쓰이기 보다는 MATCH함수 등 다른 함수와 같이 자주 사용된다. INDEX와 MATCH함수를 같이 사용하면 VLOOKUP으로 해결할 수 없는 다중 조건으로 값찾기 등의 문제를 해결할 수 있다. INDEX,MATCH를 이용한 다중조건 값찾기는 "INDEX, MATCH를 이용한 다중조건으로 값찾기" 를 참고하자.
(배열형) INDEX(array, row_num, [column_num])
테이블 또는 배열에서 행과 열에 해당하는 값을 가져온다.
인수 :
- array : 값을 찾을 테이블 또는 배열
- row_num : 값이 위치한 행번호
- column_num : (생략가능)값이 위치한 열번호
(참조형) INDEX(reference, row_num, [column_num], [area_num])
참조영역에서 행과 열에 해당하는 셀 참조를 가져온다.
인수 :
- reference : 값을 찾을 참조영역
- row_num : 값이 위치한 행번호
- column_num : (생략가능)값이 위치한 열번호
- area_num : (생략가능)값이 위치한 영역의 번호, 생략시 1이 기본값으로 사용됨
1. 배열형 사용예 :
1) 특정 영역에서 행과 열에 해당하는 값을 가져오기
아래의 판매실적 자료에서 5행, 3열에 해당하는 값을 가져오기 위해서 C2셀에 =INDEX(B5:D11,5,3)을 입력해보자.
- 첫번째 인수 B5:D11은 값을 찾을 영역이다.
- 두번째 인수 5는 이 영역에서 5번째 행을 말하고
- 세번째 인수 3은 3번째 열을 말한다.
수식이 정상적으로 입력되었으면 C2셀에 5월 자영대리점의 판매실적인 67,843,000원이 표시될 것이다.
2) 행이나 열 전체를 배열형태로 가져오기
Index함수에서 두번째/세번째 인수인 행 또는 열을 생략하거나 0을 입력하면 배열을 반환한다. 아래의 판매실적 자료에서 5행전체를 배열형태로 가져오기 위해서 F5셀에 =INDEX(C5:D11,5,0)을 입력후 바로 Enter를 누르지 말고 Ctrl + Shift + Enter를 입력해보자.
- 첫번째 인수 B5:D11은 값을 찾을 영역이다.
- 두번째 인수 5는 이 영역에서 5번째 행을 말하고
- 세번째 인수 0은 특정열을 지정하지 않는 것을 의미한다. 즉 전체열을 의미한다. 0을 입력하지 않고
=INDEX(C5:D11,5,)처럼 인수값을 비워도 동일한 수식이다.
배열수식을 입력하면 수식 앞뒤에 중괄호{}가 붙는다. 중괄호{}는 입력한 값이 아니다. 수식을 입력하고 Ctrl +Shift + Enter를 입력하면 자동으로 생긴다.
배열수식을 입력시 Ctrl +Shift + Enter를 입력하지 않고 Enter만 입력하면 아래와 같이 오류가 난다.
그런데 배열수식을 입력했는데 보이는 값은 배열이 아니고 배열의 첫번째 값인 53,789,000원만 보인다. 배열값을 보기 위해서는 좀 특별한 방법을 이용해야 한다.
배열이 입력된 수식을 마우스로 선택하고 F9(펑션키)를 입력하면 아래와 같이 수식입력줄에 ={53789000,67843000}의 형태로 배열이 표시된다.
배열수식은 그 자체로 이용되기 보다는 SUM, COUNT등의 함수로 연산하기 위해서 많이 사용된다. 아래와 같이 =SUM(INDEX(C5:D11,5,0))을 입력하면 반환된 배열을 더해준다.
그런데, 여기서 드는 의문 하나... 배열수식을 입력하고 배열수식에 SUM까지 입력했는데 그냥 5행을 선택하고 SUM함수 입력한 거랑 똑 같지 않은가?
그냥 SUM함수를 입력하면 될 걸... 왜 이렇게 복잡한 것을 할까하는 의문이 들것이다.
예시와 같이 단순한 경우에는 SUM함수를 쓰는 것과 동일하겠지만 좀 더 복잡한 경우에는 SUM함수만으로 해결이 안되는 경우가 있다. 예를 들면 SUM을 하는 행과 열이 동적으로 바뀌는 경우에 SUM함수만으로는 안되고 INDEX함수에서 행과 열번호만 바뀌서 SUM하면 쉽게 해결할 수 있다.
말로는 설명이 어렵지만 이 부분은 INDEX함수 응용편에서 자세히 다루기로 한다.
2. 참조형 사용예 :
1) 여러 영역에서 행과 열에 해당하는 값을 가져오기
참조형 INDEX함수는 지역별 판매실적처럼 여러 영역이 있고 특정 영역에서 행과 열에 해당하는 값을 가져올 때 사용할 수 있다.
아래의 판매실적 자료에서
- 동대문구에 있는 판매 대리점 중
- 직영대리점의
- 5월 판매실적을 가져오기 위해
c2셀에 =INDEX((B6:D12,F6:H12,B16:D22,F16:H22),5,2,4)을 입력해보자.
- 첫번째 인수 (B6:D12,F6:H12,B16:D22,F16:H22)는 값을 찾을 영역이다. 아래 4개의 지역을 말한다. B6:D12는 첫번째 지역인 종로구를 말하고, ,F6:H12는 두번째 지역인 강남구를 의미한다. 세번째, 네번째도 같은 방식으로 입력되어 있다.
- 두번째 인수 5는 영역에서 5번째 행을 말하고
- 세번째 인수 2는 2번째 열을 말한다.
- 네번째 인수 4는 첫번째 인수로 입력한 영역에서 4번째를 말한다. 즉 동대문구를 의미한다.
수식이 정상적으로 입력되었으면 C2셀에 판매실적인 64,549,000원이 표시된다.
이것으로 INDEX함수에 대한 설명을 마친다. 첨부의 실습자료를 다운로드 받아서 실습해 보자.
[ 엑셀 찾기 및 참조영역 함수 목록 ]
- 엑셀함수 SORT - 범위 또는 배열의 내용을 정렬하기
- 엑셀함수 SORTBY - 범위의 값을 기준으로 범위의 내용을 정렬하기
- 엑셀함수 FILTER - 원하는 조건으로 데이터 조회(필터링)하기
- VLOOKUP의 단점을 해결한 XLOOKUP함수로 표에서 값찾기
- 엑셀함수 HLOOKUP - 표를 수평(가로)으로 따라가면서 값찾기
- 엑셀함수 HYPERLINK - 하이퍼링크 만들기
- 엑셀함수 ROWS - 범위나 배열의 행의 개수 구하기
- 엑셀함수 COLUMNS - 범위나 배열의 열의 개수 구하기
- 엑셀함수 AREAS - 참조영역내의 영역의 개수 구하기
- 엑셀함수 ADDRESS - 행,열 번호로 셀주소 표시하기
- 엑셀함수 TRANSPOSE - 행과 열을 바꾸기
- 엑셀함수 INDIRECT – 참조를 동적으로 바꾸기
- 엑셀함수 LOOKUP - 행 또는 열의 같은 위치에 있는 값 찾기
- 엑셀함수 OFFSET - 행열이동 후 참조구하기
- 엑셀함수 COLUMN - 열번호를 자동으로 매기기
- 엑셀함수 ROW - 행번호를 자동으로 매기기
- 엑셀함수 CHOOSE - 값목록에서 원하는 값 선택하기
- 엑셀함수 MATCH - 특정 범위에서 값의 위치 찾기
- 엑셀함수 INDEX - 특정 범위에서 행과 열을 이용하여 값 찾기
- 엑셀 VLOOKUP 함수로 표에서 값 찾기(간단 버전)
- 엑셀에서 값을 찾을 때 VLOOKUP, HLOOKUP 함수 사용하기
- VLOOKUP으로 안될때 INDEX,MATCH 사용하기
- INDEX, MATCH를 이용한 다중조건으로 값찾기
엑셀강의 잘보고있습니다. 혹시 제 문제좀 해결해주실수있나요?
LEFT RIGHT MID로 그 품목에관한 번호를 추출하여 다시 합칠때 그 품복에 관한 이름을 다시 표현하고싶은데 힘드네용 ㅠㅠ 예를들어 123456이라는 번호가있으면 12는 품목명이고 34가 색깔 56이 품목사이즈면 12 34 56을 추출하여 그 이름에 맞는 결과값을 문자로 볼수있으면한데 ㅠㅠ 예를들어 구두(12)파랑색 (34)LARGE(56)면 바코드로 찍으면 숫자만 123456이 나와서 그걸 바로 문자로 보게끔 구두파랑색LARGE이렇게 보고싶게만들고싶어요
간단히말해서 바코드를 이용한 엑셀활용인데 바코드수는 6자리이고 그 6자리안에 물건에대한 정보를 엑셀에 숫자로 입력하여 그 숫자를 다시 VLOOKUP이나 INDEX를 활용하여 물건이름이 나오게끔 하고싶어용
안녕하세요. 각각의 자리수 마다 물건의 정보를 가지고 오려면 별도의 시트에 코드값과 코드값에 해당하는 이름정보를 관리하고 VLOOKUP이나 INDEX/MATCH함수를 이용해서 보여주면 되지 않을까합니다.
별도의 시트는 다음과 같이 구성하면 될듯합니다.
품목코드,품목명,색깔코드,색깔명,품목사이즈코드,사이즈
------------------------------------------------------------------------------
12,구두,34,파랑,56,LARGE
13,숄더백,35,빨강,57,MIDDLE
14,서류가방,36,갈색,58,SMALL
감사합니다.
고수님들 질문이 있습니다.
저는 건설일에 뛰어든 병아리 입니다.
요즘 내역서와 씨름중인데요..ㅠㅠ
거래처에서 보낸서류를 변경하여 협의된 금액으로 변경해야하는데 넘 힘들어서요..
예시)수식이 포함된 전체값(견적금액의 81%값)합계가
123,456,789입니다.
거래처 계약금은 123,000,000 입니다.이금액을 마춰야합니다.
전체 문서를 손대서 이금액을 마추려니 일일이 이윤(%)에서 소수점작업해서 마추고 있습니다.
한번에 값을 계산할수 없을까요?
부끄럽지만 부탁드립니다.
안녕하세요. 작업하고 계신 파일을 보아야 도움을 드릴 수 있을 것 같습니다. 괜찮으시다면 admin@xlworks.net으로 보내주시면 확인해보겠습니다.
감사합니다.
핑백: 엑셀함수 MATCH - 특정 범위에서 값의 위치 찾기 - XLWorks - XLWorks
핑백: VLOOKUP으로 안될때 INDEX,MATCH 사용하기 - XLWorks