엑셀 INDEX & MATCH 함수 정의
INDEX 함수는 셀 범위에서 행 순번, 열 순번을 입력하면 그 위치의 값을 가져오고,
MATCH 함수는 셀 범위에서 내가찾는 값이 어디 위치에 있는지 알려준다.
이거만 보면 이걸 어디다써? 하는데
KPI 실적같이 판이 무지무지 커지게 된다면 INDEX와 MATCH 함수가 빛을 발한다.
각 월별 실적, 계획 등을 한시트에 때려넣고 INDEX와 MATCH 함수로 하나씩 가져오면 됨.
엑셀 INDEX & MATCH함수 문법
INDEX = ( 셀 범위, 행 위치, 열 위치 )
MATCH = ( 찾을 값, 셀 범위, 찾을 조건 )
※ MATCH 함수는 찾을 값이 범위 내 여러개 있는 경우 제일 위 (OR 왼쪽) 에 있는 값을 가지고 온다.
엑셀 INDEX & MATCH함수 실제사례
순서상 MATCH 함수 먼저 이해하는게 좋다.
MATCH 함수는 행, 열 모두 위치찾기에 사용 가능하다. 한번만 활용법을 이해하면 행방향 열방향 다 똑같이 사용가능하다.
MATCH 함수 사용법
MATCH = ( 찾을 값, 셀 범위, 찾을 조건 )여기서 찾을 조건은 0이 정확하게 찾기, 1이 비슷해도 찾기 인데보통 정확하게 찾기 위해 0을 입력한다.
1) 행 (가로로 길쭉) 기준 위치찾기
찾을 값 = "생활쓰레기"셀 범위 = 행 범위 (가로로 길쭉, 꼭 1줄로 넣어야 한다)** 1줄로 안 넣으면 에러가 난다. 에러는 아래에서 자세히!찾을 조건 = 0
-> 결과로는 5가 나온다.
"생활쓰레기" 가 왼쪽에서부터 5번째에 위치하고 있기 때문임.
((에러주의)) 만약 저렇게 한줄이 아니고 두줄, 세줄로 넣는다면 #N/A 에러가 난다.
2) 열 (세로로 길쭉) 기준 위치찾기
찾을 값 = "생활쓰레기"
셀 범위 = 행 범위 (세로로 길쭉, 여기도 꼭 1줄로 넣어야 한다)
찾을 조건 = 0
-> 결과로는 1이 나온다.
찾을 값인 "생활쓰레기" 가 중복되는 경우, 제일 위에 있는 값 1개만 도출함.
"생활쓰레기" 제일 위에 있기 때문에 1이 결과로 나온다.
INDEX 함수 사용법
INDEX 함수는 행렬을 생각하면 이해가 쉽다.
셀 범위에서 행 위치, 열 위치를 각각 지정하면
그 위치에 있는 값을 알려주는 함수이다.
INDEX = ( 셀 범위, 행 위치, 열 위치 )
* INDEX 함수의 셀 범위는 MATCH 함수와 다르게, 꼭 한줄이 아니어도 된다.
주어진 셀 범위에서 행 위치는 첫번째 (=1), 열 위치는 5번째 (=위 MATCH 함수에서 도출) 로 넣었을 경우
아래처럼 "생활쓰레기" 값이 나온다.
이 예시도 동일하다.
왜 굳이 위의 MATCH 함수에 있는 값을 사용했냐면..
MATCH와 INDEX는 같이 쓰는 경우가 정말 많기 때문이다.
INDEX & MATCH 함수로 KPI 실적 관리하기
드디어 이 두 함수를 섞어서 실적관리하기
↓ 이런 표를 채운다고 생각해보자.
그리고 마감을 할때마다 5월, 6월, 7월... 매번 월을 바꿔서 실적, 목표비 등등을 바꿔줘야 한다.
할수 있는 방법은 정말 많겠지만, 여기에서는
INDEX & MATCH 함수로 자동 셋팅해 보기로 한다.
자동 셋팅을 한다면, 매월 월의 숫자만 바꿔주면 된다.
1. MATCH 함수로 값을 가져오기
우선 5월 실적을 가져와보자.
MATCH = ( 찾을 값, 셀 범위, 찾을 조건 )
찾을값 = 5월 (셀 클릭)
셀 범위 = 1월~12월 선택
찾을 조건 = 0 (정확하게 찾기)
---> 함수 결과 : 5
5월 셀이 왼쪽부터 5번째에 있기 때문임.
2. INDEX 함수로 실적 가져오기
INDEX = ( 셀 범위, 행 위치, 열 위치 )
셀 범위 = 실적, 목표, 목표비가 들어가 있는 표 전체
행 위치 = 실적은 파란색 셀 범위에서 제일 위 줄이므로 1 입력
(**만약 목표를 가져와야 한다면 두번째 줄이므로 2 입력)
열 위치 = 1번의 MATCH 함수 삽입(=5)
---> 함수 결과 : 18
셀 범위에서 첫번째 행, 다섯번째 열 (= 셀위치(1,5) ) 의 값 18이 도출
3. 동일한 방법으로 모두 채우기
- 당월 목표비
- 당월까지 누적실적
- 당월까지 누적목표비
- (자동셋팅결과) 5월 -> 6월로 수정시 실적, 목표비 등 자동수정
INDEX 랑 MATCH 함수는 표가 어마어마하게 커졋을 때 빛을 발하는 함수이다
다양하게 활용되니 잘 알아두고 쓰면 정말 좋다
※ 데이터예시 출처 : 공공데이터포털 | 전국종량제봉투가격표준데이터
https://www.data.go.kr/index.do