엑셀에서 DMAX함수 이용 최고점과 차하점 그리고 해당되는 학생 찾기

■ 각 반별 최고점수, 차하점수(1등, 2등) 및 해당하는 사람 찾기

 

최고점수와 최고점수를 받은 사람, 두 번째로 상위점수와 이를 받은 사람을 알아보기 좋은 엑셀 함수는 DMAX 함수와 INDEX 함수, MATCH함수를 이용하면 된다.

 

DMAX 함수는 목록이나 데이터베이스의 레코드 필드(열)에서 지정한 조건에 맞는 가장 큰 값을 반환하는 수식이다.

 

DMAX함수 구문

=DMAX(database, field, criteria)

database : 데이터베이스나 목록으로 지정할 셀 범위

데이터베이스 : 레코드(관련 정보 행)와 필드(데이터 열)로 이루어진 관련 데이터 목록

 

field : 함수에 사용되는 열

field 인수는 "총점" 또는 "평균"처럼 열 레이블을 큰따옴표로 묶어 텍스트로 지정하거나 첫째 열을 1, 둘째 열을 2 등 목록 내의 열 위치를 나타내는 숫자로 지정

 

criteria : 지정한 조건이 있는 셀 범위

 

다음 테이블에서 반별 최고점과 차하점을 찾아 봅시다.

 

각 반별로 총점의 최고점을 찾아보자.

수식을 사용하기 전에 각 반별로 표(조건에 해당됨 : criteria)를 하나 만든다.

첫 번째 레이블	반	반	반	반
해당하는 반	1	2	3	4

 

각반셀 밑에 다음과 같은 수식을 입력해 줍니다.

=DMAX($B$1:$K$21,"총점",D24:D25)

행으로 드래그 하면 전체반 학생들 중 각반 학생들의 총점중 최고점 점수가 반환됩니다.

 

이제 최고점을 맞은 학생을 찾아 봅시다.

학생을 찾기 위해서는 INDEX함수와 MATCH함수를 이용하면 쉽게 찾을 수 있습니다. 이것은 배열수식을 이용하듯이 사용하면 좋을 것 같습니다.

 

{=INDEX($B$2:$K$21,MATCH(D25&D26,$B$2:$B$21&$J$2:$J$21,0),3)}

 

INDEX함수를 이용시 B2행부터 시작하고 수식을 입력하고 나서 Shift+Ctrl+Enter를 눌러줘야 됩니다.

 

MATCH함수 이용시 D25&D26은 반&총점, $B$2:$B$21&$J$2:$J$21는 테이블에서 반과 총점이 같은 행의 위치를 찾는 것입니다. 완전히 일치하는 행이 몇 번째 행인지를 알 수 있습니다.

그리고 MATCH함수의 행과 3열(이름)을 찾아서 값을 반환합니다. 이렇게 하면 최고점의 학생을 찾을 수 있습니다.

 

차하점을 찾기 위해서는 다음과 같이 첫 번째 레이블에 “반”과 “총점”을 입력하여 조건에 맞게 수식을 입력해 주고 나서 DMAX함수와 INDEX함수를 사용하면 됩니다.

 

차하점을 찾기위해 다음과 같이 만들어 줍니다.

총점에서 최고점보다 작은 점수를 찾아야 하므로 총점에서

="<"&D26

이렇게 입력하여 최고점보다 작은 범위에서 최고점을 다시 찾는 것입니다.

이번에는 두가지 조건을 만족시키는 “반”과 “총점”중 차하점에 해당되는 값을 선택하도록 하고 다음과 같이 입력해줍니다.

 

=DMAX($B$1:$K$21,"총점",D31:E32)

 

차하점을 맞은 학생을 구하는 방법은 앞에서와 같은 방법으로 적어주시면 됩니다.

 

{=INDEX($B$2:$K$21,MATCH(D32&D33,$B$2:$B$21&$J$2:$J$21,0),3)}

 

반드시 Shift+Ctrl+Enter을 잊지 마시길 바랍니다.

이상 최고점과 차하점을 찾고 이름도 찾는 방법을 알아보았습니다.

엑셀 연습_1,2등찾기.xlsx

0.01MB