[엑셀] 예상 등급 환산표 만들기 - IF, LOOKUP 함수

모의고사 예상 등급을 산출해 봅시다.

오늘은 학급 학생들 전체의 모의고사용 예상 등급 환산표를 만드는 방법을 소개하고자 합니다. 개인의 모의고사 성적은 가채점 후 인터넷에 업데이트되는 예상 컷이랑 비교하여 충분히 확인 가능합니다. 하지만 오늘 소개하는 것은 교사용으로, 학급 학생들 전체의 예상 등급을 일괄로 산출하는 방법입니다. 물론 요즘에는 대입상담용 프로그램들에 학생들 점수를 입력하면 예상 등급이 자동으로 산출됩니다. 하지만 오늘은 LOOKUP 함수를 활용해서 직접 만들어보는 시간을 가져보고자 합니다.

더불어 글 마지막에 서식을 탑재하여, 누구나 다운로드해서 활용 가능하도록 공유하고자 합니다.

목차
가. LOOKUP 함수로 예상 등급 환산
나. IF, LOOKUP 함수로 선택과목 예상 등급 환산
다. 예상 등급 산출표 다운로드

 

예상 등급 환산표 만들기(모의고사용)

---

가. LOOKUP 함수로 예상 등급 환산하기

LOOKUP(찾는 값, 찾는 범위, 출력 범위)  

위 방법으로 함수를 적용하면 예상 등급이 산출됩니다. (이때, '찾는 범위'는 오름차순으로 정렬되어야 합니다.)

 

예를 들어 살펴보겠습니다. 한 과목의 예상 등급을 산출할 때에는 '찾는 범위'와 '출력 범위'를 따로 할 필요 없이, 아래 화면처럼 예상 등급 표로 범위를 한 번에 설정하면 첫 번째 열에서 값을 찾아서 두 번째 열의 값을 출력합니다.

찾는 범위와 출력 범위 구분 없이 LOOK 함수 사용하는 법

 

여러 과목의 예상 등급을 산출하고자 하기 때문에 저는 등급과 점수의 위치를 교체하였고, 그래서 '찾는 범위'와 '출력 범위'를 따로 지정하여 아래처럼 함수식을 작성하였습니다.

찾는 범위와 출력 범위를 구분하여 LOOK 함수 사용하는 법

위의 두 예시 모두 함수를 쓴 결과 3등급이라는 값을 확인할 수 있습니다.

 

 

나. IF, LOOKUP 함수로 선택 과목의 예상 등급 산출하기

국어, 수학, 사회, 과학(직업탐구까지) 과목들은 모두 과목을 선택하여 응시하고 있습니다. 따라서 학생이 선택한 과목에 따라 일치하는 예상 등급 표를 찾는 함수가 하나 추가되어야 합니다. 바로 IF 함수를 사용하여 적용할 수 있습니다.

 

아무래도 선택 과목은 종류가 많다 보니 IF 함수가 상당히 길어질 수밖에 없습니다. 그래서 저는 먼저 과목별 예상 등급을 이름으로 정의하였습니다.(함수가 길어져서 그렇지, 이름으로 정의하지 않고도 할 수 있습니다.)

범위를 이름으로 지정하는 법 (좌측 상단의 '이름 상자'에 입력 후 엔터)

이제 IF 함수와 LOOKUP 함수를 적용하여 본격적으로 예상 등급을 산출할 차례입니다.

IF(조건, 조건이 참이라면, 조건이 거짓이라면)
=IF(과목이 확통인지, 참이면 LOOKUP 함수 실행, 거짓이면 다시 IF 함수 실행)

IF 함수는 위와 같은 구조입니다. 이에 따라 '과목을 조사'하는 조건을 넣고 과목이 일치하면 LOOKUP 함수를 적용하여 예상 등급 표에서 등급을 산출하고, 일치하지 않으면 또다시 IF 함수를 적용하여 다른 과목인지를 확인하는 방식으로 구성합니다. IF 함수 안에 또 IF 함수를 넣고, 또 안에 IF 함수를 넣은 구조입니다. 함수식은 아래 그림과 같습니다. 

IF, LOOKUP 함수를 사용하여 선택과목에 따른  등급 산출식을 작성하는 법

조건 3(기하 과목인지 확인)이 거짓일 때는 별도로 정의하지 않았습니다. 세 과목 중에서 반드시 나와야 하니까요.

그리고 아무래도 과목수가 훨씬 많은 탐구 과목인 경우에는 함수가 이보다도 훨씬 길어지겠지요?

 

 

다. 모의고사 예상 등급 산출표 다운로드하기

위와 같은 방식으로 구성된 예상 등급 산출표를 탑재하였습니다. 필요한 분들은 다운로드해서 사용하시면 될 것 같습니다. 사용 방법은 아래와 같습니다.

모의고사 예상 등급 산출표

1. 파일을 다운로드해서 학급 학생들의 가채점 점수를 입력합니다.

2. 인터넷 검색창에 모의고사 등급이라고 검색 후 나오는 예상 등급 표를 복사해서 붙여 넣습니다.

3. 단, 오름차순으로 되어야 하니까 마우스 우 클릭 > [정렬] > [텍스트 오름차순 정렬]

예상 등급 산출표(모의고사).xlsx

0.01MB

노란색 셀에는 함수식이 작성되어 있어 시트 보호가 적용되어 있습니다. 검토 > 시트 보호 해제를 클릭하면 암호 없이 해제할 수 있습니다.

 

 

마무리하기

 

아무래도 이 모의고사용 예상 등급 환산표는 학생들의 가채점 점수도 필요하고 예상 등급 컷도 복사해서 넣어야 하는 만큼 귀찮은 과정이 동반됩니다. 그래서 선택과목 수가 많아진 후부터는 저 또한 사용도가 급격히 낮아졌습니다.

이를 통해 LOOKUP 함수의 이해에 도움이 되시길 바라며, 수행평가 후 급간 점수 부여에도 IF 함수와 LOOKUP 함수를 변형하여 적용 가능합니다.

 

또한 모의고사 예상 등급 산출표는 학생 지도용으로 교사가 살펴보는 자료입니다. 학생들 입장에서는 아무래도 인터넷에 예상 등급컷이 실시간으로 업데이트되니까, 누구나 예상 등급을 확인 가능한 모의고사보다는 내신 성적에 따른 예상 등급 컷에 더욱 관심이 많습니다. 중간고사 종료 후 자신의 학기말 예상 등급이 궁금하여 교과 선생님에게 문의하는 학생들이 많지요.(기말고사와 수행평가가 모두 신행된 학기말에야 정확하게 알 수 있는 데이터이지만, 대략적으로라도 궁금해하는 학생들이 많습니다.)

 

다음 시간에는 모의고사 보다 활용도가 높은, 내신 예상 등급 산출표로 찾아뵙겠습니다. 오늘도 읽어주셔서 감사합니다.