82_엑셀 index match 함수 – 함수 중급 (9)
MATCH 함수는 찾고 싶은 값의 위치 즉 INDEX값을 반환하고 INDEX함수는 INDEX를 가지고 찾고 싶은 데이터를 찾아주죠. 그래서 두 함수가 같이 쓰이면 찰떡 궁합 이라는 것 입니다. MATCH함수로 행, 열 번호를 찾고 그 행 열 번호를 INDEX에 넣어서 원하는 값을 찾으면 됩니다. 그리고 두 함수를 각각 쓸 수 있으면 응용은 식은 죽 먹기 입니다.
82연습파일.xlsx문제 파악하기
1월부터 12월까지의 각 제품의 판매량이 큰 테이블로 있습니다. 이 테이블에서 월과 제품명을 입력하면 판매량을 찾아주는 그런 자동화 시트를 만들고 싶습니다.
지금 3월 노트북의 판매량을 목표로 잡고 3358의 판매량을 구해보겠습니다.
각각 단일 함수 사용하기
단일 함수를 적용해서 일단 INDEX를 구하고 그 INDEX를 가지고 MATCH 함수를 써서 판매량을 구해 보겠습니다. 물론 경험이 쌓이면 한번에 하면 되지만 중첩 함수 경험이 많이 없으면 이런식으로 하시면 실수를 피할 수 있습니다.
달 인덱스에 MATCH함수를 적용합니다. =M 만처도 MATCH 가 선택되죠?
여기서 바로 탭 키를 누리면 괄호까지 자동 입력 됩니다.
B1셀을 클릭하고 쉼표를 찍고 달의 범위를 지정해 주세요. 그리고 정확히 일치 옵션을 선택하고 엔터
행의 위치 인덱스를 구했습니다.
같은 방법으로 이번에는 제품명을 범위로 지정하고 0을 선택하고 탭, 엔터를 누릅니다.
행과 열의 인덱스가 각각 구해 졌습니다.
이제 그 인덱스를 가지고 판매량을 구해 보겠습니다. =INDEX를 치시고 다음과 같이 범위를 선택해 주세요.
제가 이번 포스팅에서 테이블 지정을 하지 않는 이유는 이 경우에는 절대 참조를 하지 않아도 되기 때문입니다. 채우기 핸들을 사용하기 않기 때문인데 테이블 지정을 하면 물론 장기적으론 좋습니다.
구해진 달과 제품 행 열 번호로 INDEX함수를 적용 합니다.
잠깐 설명을 드리면 F2:I13 의 범위에서 행 번호 B4와 열 번호 B5를 가지고 판매량을 찾아라 입니다.
판매량이 맞게 구해졌습니다.
사실 여기서 끝내도 됩니다. B1과 B2의 달과 제품명이 업데이트 되면 판매량도 자동 업데이트 되지만 하나의 함수로 넣는게 간지나자나요.
INDEX-MATCH 중첩하기
INDEX함수의 행 열 인수에 MATCH 함수를 갖다 붙이기만 하면 됩니다. 물론 경험이 있으면 이렇게 이중 작업 안하고 한번에 하시면 되죠
일단 INDEX함수만 써서 판매량을 구합시다.
3은 달 MATCH를 써서 구한 것이므로 수식을 통째로 복사합니다.
INDEX함수의 3자리에 갖다 붙입니다. =는 안 딸려오게 조심하세요.
마찬가지로 제품 MATCH 함수를 =를 제외하고 복사 합니다.
2자리에 그대로 갖다 붙입니다.
INDEX-MATCH 중첩 함수가 완성 되었습니다.
보너스 팁 >> 제이킴의 방법
이 방법은 제가 이전에 소개 한 적이 있죠? 바로 알트 + 엔터키를 이용한 중첩함수 쓰는 법 입니다. 물론 이정도는 한줄에 적지만 중첩이 10개가 넘어가면 매우 유용합니다.
각각 쉼표 뒤에 ALT+엔터로 행 구분을 하여 함수를 보기 좋게 구분하면 한번에 함수를 입력하면서 실수를 줄일 수 있습니다.
이처럼 INDEX함수와 MATCH함수는 단일로 쓰이기 보다는 다른 함수와 중첩하여 최대값이나 순위를 구할 때도 많이 쓰입니다.
'엑셀 2016 > 8_함수중급' 카테고리의 다른 글
| 84 엑셀 countif 함수의 재발견 (5) | 2016.03.09 |
|---|---|
| 83 엑셀 이름찾기 INDEX MATCH (1) | 2016.03.09 |
| 81 엑셀 INDEX 함수의 재발견 (0) | 2016.03.09 |
| 80 엑셀 MATCH 함수의 재발견 (4) | 2016.03.09 |
| 79 엑셀 날짜계산 CHOOSE 함수로 분기 구하기 (7) | 2016.03.09 |