엑셀 함수 점수 구간 별 등급 매기기 계산하기 index match

ㅇ엑셀에서 점수나 구매 금액 별로 구간 등급을 계산하는 함수를 공유합니다. 

사용하는 함수는 INDEX와 MATCH입니다. 

어렵지 않으니 금방 따라하실 수 있을 거에요.

 

엑셀 구간별 등급 계산 함수 작성법

아래와 같이 엑셀에 고객 ID 별로 구매 금액이 있을 때 구매 금액에 따라 고객 등급을 계산해야하는  상황을 가정하겠습니다.

이런 경우 IF 함수를 많이 사용하실 텐데 훨씬 짧고 간단한 엑셀 함수로 계산하겠습니다. 

먼저 함수를 공유하자면 아래와 같습니다. 

=INDEX($F$6:$G$9,MATCH(C6,$F$6:$F$9,1),2)

각 함수의 이해를 하고 나서 최종 함수를 검토하겠습니다. 

 

엑셀 INDEX() 함수의 이해

=INDEX(  값을 찾을 범위  , 지정한 행 번호  ,  지정한 열 번호)

INDEX() 함수는 입력받은 범위에서 마찬가지로 입력 받은 행(row) 번호 & 열(column) 번호에 따라 위치에 있는 셀의 내용을 반환합니다. 

조금 더 풀어서 설명하면 "내가 지정한 표에서 몇번째 행의 몇번째 열 값을 찾아줘" 입니다. 

그림으로 설명하면 아래와 같습니다. 

 위와 같이 지정한 범위 (B8:C11)에서 3번째 행의 2번째 열의 값인 '실버'를 반환하였습니다. 

엑셀 INDEX 함수 이해하셨죠? 그럼 다음 함수인 엑셀 MATCH()함수를 알아보시죠

 

엑셀 MATCH() 함수의 이해

=MATCH(  찾고자하는 값  , 값을 찾을 범위  , 계산 방식)

엑셀 MATCH() 함수는 값을 찾을 범위에서 찾고자 하는 값의 상대적 위치를 찾아 줍니다.

조금 더 풀어서 설명하면, "내가 지정한 범위에서 이 값이 몇번째 행/열에 있는지 알려줘" 입니다.

*MATCH 함수에서 주의할 점은 값을 찾을 범위가 배열이라는 점입니다. 배열은 행x열 형태의 테이블이 아니라 하나의 행 혹은 열이라는 의미입니다. 

*계산 방식의 경우 1 , 0 ,-1을 지정할 수 있습니다. 1: 찾을 값보다 작거나 같은 값을 가진 행or열 0: 정확히 일치하는 값을 가진 행or열, -1: 찾을 값보다 크거나 같은 값을 가진 행or열 입니다. 일반적으로 1을 사용합니다. 

 

 

엑셀 INDEX와 MATCH 함수를 사용한 구간별 등급 함수 

그럼 INDEX와 MATCH를 사용한 함수로 등급을 매겨보겠습니다. 

1. 등급표가 세로 형태일 때

등급표가 세로일때 엑셀 구간 등급을 계산하겠습니다. 

아래표에서 C1셀에 함수를 기록하겠습니다. 

먼저 INDEX 함수로 등급표 범위를 잡아줍니다. *복사할거라 고정값을 넣었습니다. 

=index($E$2:$F$5,

그리고 저희는 세로형 표에서 두번째 열의 값을 반환할 예정이기 때문에, 

MATCH()함수로 행 값을 지정해야합니다. 따라서 INDEX() 함수 두번째 인자에 B1의 값보다 작거나 값은 값의 "행"값을 계산하는 수식을 넣어줍니다.

=index($E$2:$F$5,match(B2,$E$2:$E$5,1)

그리고 마지막으로 호출 시킬 고객 등급이 있는 열 번호를 넣어줍니다.

=index($E$2:$F$5,match(B2,$E$2:$E$5,1),2)

완성된 결과

 

2. 등급표가 가로 형태일 때

반대로 등급표가 가로일 때는 INDEX 내부 인자값 순서와 MATCH 함수의 배열만 조정해줍니다. 

세로 형일때는 MATCH함수가 열(row) 위치에 들어갔지만 지금은 열(column) 위치에 들어갔습니다. 

그리고, MATCH함수의 범위(배열)을 가로로 길게 했고, INDEX도 마찬가지로 조절했습니다. 

 

이렇게 엑셀 INDEX와 MATCH 함수를 사용한 구간별 등급 함수를 알아보았습니다.