엑셀(EXCEL) 함수 TIP 20 - (LARGE,SMALL,LMATCH,INDEX함수)
엑셀(excel 2007)에서 하단과 같은 데이터시트가 있을 때 오른쪽 처럼 상위 1,2,3,4 또는 하위 1,2,3,4위를 large함수 및 small함수를 이용하여 구할 수 있습니다. 또한 1,2,3,4위에 해당하는 학점을 match함수를 이용하여 추출할 수 있고 그에 해당되는 성명를 index함수를 이용하여 불러올 수 있습니다.
이처럼 함수의 사용용도를 잘 알고 있다면 원하고자 하는 값을 여러가지 함수를 사용하여 구할 수 있습니다. 따라서 함수는 지속적으로 사용하여서 그 사용용도와 방법을 완벽하게 익혀야 할 것입니다.
▶원본 데이터 값
▶ large 및 small함수의 사용 용도
=LARGE(array,D) : 데이터 내에서(array) D번째로 큰값을 구하라
=SMALL((array,K) : 데이터 내에서(array) K번째로 작은값을 구하라
● large 및 small 함수를 사용한 석차 구하기
▶ large함수
[F3:F6 블럭설정 - 수식탭 - 함수추가 - 통계 -largre- large함수인수창 - Array($D$17) - K(F3)- 확인]
Array는 셀참조 범위입니다. 평균학점이 있는 [D3:D15]입니다. 이 값은 나중에 채우기 핸들을 이용해야 하기때문에 기능키 F4를 눌어서 절대주소($D$15)로 변경을 해 주어야 합니다.
▶ small 함수
[F3:F6 블럭설정 - 수식탭 - 함수추가 - 통계 -small - small 함수인수창 - Array($D$17) - K(F3)- 확인]
Array는 셀참조 범위입니다. 평균학점이 있는 [D3:D15]입니다. 이 값은 나중에 채우기 핸들을 이용해야 하기때문에 기능키 F4를 눌어서 절대주소($D$15)로 변경을 해 주어야 합니다.
▶ large함수 및 small 함수를 이용한 결과값
하단과 같이 large함수를 이용한 평균학점 상위 1~4위까지의 결과와 small 함수를 이용한 하위 1위~4위까지의 결과값이 나왔습니다.
● MATCH함수 사용
=MATCH(Look_value, look_array, match_type) (값, 범위, 방법)
Lookup_value(구하고자 하는 값)
look_array(참조 범위)
match_type(방법)
MATCH함수는 데이터의 위치번호를 구하는 함수이다. '구하고자 하는 값을 매치(연결)시켜라'라는 의미입니다. 즉 평균학점과 매치를 시켜서, 원본데이터의 평균학점이 몇번째 있느냐의 뜻입니다.
[G3:G6 블럭설정 - 수식탭 -찾기/참조영역 -match - match함수인수창 - Lookup_value(G3) look_array($D$3:$D$17) - match_type(0) - 확인]
▶MATCH를 이용한 결과값
사용한 함수[=MATCH(G3,$D$3:$D$17,0)]이며, 하단으로 채우기 핸들을 사용하면 됩니다. 수식의 의미는 '찾고자 하는 평균학점[4.9]이 원본데이터의 몇번째 있느냐'의 의미입니다. 이 MATCH함수는 INDEX함수를 구하기 위하여 필요한 결과값입니다.
● INDEX함수 사용
= INDEX(array,row_num, columm_num) : 원본데이터의 범위(array)내에서 행번호와 열번호를 지정해서 특정셀값을 구라하는 의미이다.
하단에서 [I3]의 값을 구할 때 원본데이터(개인별 평균학점의 성명범위)에서 구하고자 하는 범위의 행, 열 번호에 있는 값을 구하라는 뜻이다.
▶[ 구하고자 하는 셀 범위 선택(I3:I6) - INDEX함수 선택 - 인수선택 창 - 상단클릭 - 확인]
▶[INDEX함수인추창 - array($D$3:$D$17),row_num(H3) - (ctrl + enter)]
하단과 같은 결과값이 나타난다. 즉 (홍수아)를 예를 들면 개인별 평균학점의 성명범위(좌측 데이터베이스)내에서 H3에 있는 값인 [6번]쩨 열번호에 있는 성명으로 반환하라는 뜻입니다.
▶ 사용된 함수
값을 구할 때 [구하고자 하는 범위 한셀(I3)을 선택하여서 값을 구하고 하단으로 드래그하여 채워넣을 수도 있지만 구하고자 하는 전범위(I3:I6)를 선택한 후 마지막 값을 구할 때 (Ctrl + enter)를 하면 전범위의 값이 동시에 구해진다. 위의 index함수 값은 (구하고자 하는 전범위 선택)과 (Ctrl + enter)를 해서 한번에 구한 값이다.
