엑셀함수 INDEX – 특정 범위에서 행과 열을 이용하여 값 찾기 – XLWorks

공개됨 글쓴이 Admin댓글 6개
      

엑셀의 INDEX함수는 테이블이나 범위에서 값 또는 값에 대한 참조를 반환한다. INDEX 함수에는 배열형과 참조형이라는 두 가지 형식이 있다.
이 함수는 단독으로 쓰이기 보다는 MATCH함수 등 다른 함수와 같이 자주 사용된다. INDEX와 MATCH함수를 같이 사용하면 VLOOKUP으로 해결할 수 없는 다중 조건으로 값찾기 등의 문제를 해결할 수 있다. INDEX,MATCH를 이용한 다중조건 값찾기는 “INDEX, MATCH를 이용한 다중조건으로 값찾기” 를 참고하자.

구문(Syntax)

 

1. 배열형 사용예 :

특정 영역에서 행과 열에 해당하는 값을 가져오기

아래의 판매실적 자료에서 5행, 3열에 해당하는 값을 가져오기 위해서 C2셀에 =INDEX(B5:D11,5,3)을  입력해보자.

  • 첫번째 인수 B5:D11은 값을 찾을 영역이다.
  • 두번째 인수 5는 이 영역에서 5번째 행을 말하고
  • 세번째 인수 3은 3번째 열을 말한다.

수식이 정상적으로 입력되었으면 C2셀에 5월 자영대리점의 판매실적인 67,843,000원이 표시될 것이다.

 

행이나 열 전체를 배열형태로 가져오기

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만 입력하면 아래와 같이 오류가 난다.
Ctrl + Shift + Enter를 입력하지 않으면 오류가 발생한다

 

그런데 배열수식을 입력했는데 보이는 값은 배열이 아니고 배열의 첫번째 값인 53,789,000원만 보인다. 배열값을 보기 위해서는 좀 특별한 방법을 이용해야 한다.

배열이 입력된 수식을 마우스로 선택하고 F9(펑션키)를 입력하면 아래와 같이 수식입력줄에 ={53789000,67843000}의 형태로 배열이 표시된다.

배열수식의 배열확인하기
배열수식의 배열확인하기
배열값 확인하기
배열값 확인하기

 

배열수식은 그 자체로 이용되기 보다는 SUM, COUNT등의 함수로 연산하기 위해서 많이 사용된다. 아래와 같이 =SUM(INDEX(C5:D11,5,0))을 입력하면 반환된 배열을 더해준다.
SUM으로 배열수식을 더하기

 

SUM함수로 배열수식을 더한 결과

 

그런데, 여기서 드는 의문 하나… 배열수식을 입력하고 배열수식에 SUM까지 입력했는데 그냥 5행을 선택하고 SUM함수 입력한 거랑 똑 같지 않은가?

그냥 SUM함수를 입력하면 될 걸… 왜 이렇게 복잡한 것을 할까하는 의문이 들것이다.

예시와 같이 단순한 경우에는 SUM함수를 쓰는 것과 동일하겠지만 좀 더 복잡한 경우에는 SUM함수만으로 해결이 안되는 경우가 있다. 예를 들면 SUM을 하는 행과 열이 동적으로 바뀌는 경우에 SUM함수만으로는 안되고 INDEX함수에서 행과 열번호만 바뀌서 SUM하면 쉽게 해결할 수 있다.

말로는 설명이 어렵지만 이 부분은 INDEX함수 응용편에서 자세히 다루기로 한다.

 

2. 참조형 사용예 :

여러 영역에서 행과 열에 해당하는 값을 가져오기

참조형 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함수에 대한 설명을 마친다. 첨부의 실습자료를 다운로드 받아서 실습해 보자.

[ 엑셀 찾기 및 참조영역 함수 목록 ]

엑셀함수 INDEX – 특정 범위에서 행과 열을 이용하여 값 찾기 – XLWorks에 1개의 응답

  1. 엑셀강의 잘보고있습니다. 혹시 제 문제좀 해결해주실수있나요?

    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
      감사합니다.

  2. 고수님들 질문이 있습니다.
    저는 건설일에 뛰어든 병아리 입니다.
    요즘 내역서와 씨름중인데요..ㅠㅠ
    거래처에서 보낸서류를 변경하여 협의된 금액으로 변경해야하는데 넘 힘들어서요..
    예시)수식이 포함된 전체값(견적금액의 81%값)합계가
    123,456,789입니다.
    거래처 계약금은 123,000,000 입니다.이금액을 마춰야합니다.
    전체 문서를 손대서 이금액을 마추려니 일일이 이윤(%)에서 소수점작업해서 마추고 있습니다.
    한번에 값을 계산할수 없을까요?
    부끄럽지만 부탁드립니다.

    • 안녕하세요. 작업하고 계신 파일을 보아야 도움을 드릴 수 있을 것 같습니다. 괜찮으시다면 admin@xlworks.net으로 보내주시면 확인해보겠습니다.
      감사합니다.

댓글 남기기

이메일은 공개되지 않습니다.