교육기관에서는 학생들의 학습 성취도를 정리한 성적 보고서를 작성해야 할 일이 매우 자주 생깁니다. 엑셀에는 다양한 수식과 기능이 갖춰져 있어 성적통지표를 엑셀로 관리하면 작업이 훨씬 수월해집니다. 표준 양식의 성적통지표를 한 번만 만들어 두면 모든 학생에게 복사해 재사용할 수 있고, 시간도 크게 절약됩니다. 이 글에서는 엑셀로 성적통지표를 만드는 방법을 단계별로 자세히 소개합니다.
무료 성적통지표 템플릿 다운로드
아래에 성적통지표 템플릿 파일을 준비했습니다. 무료로 다운로드하여 바로 활용하실 수 있습니다.
성적일람표(Mark Sheet)와 성적통지표(Report Card)란?
성적일람표는 학생의 과목별 성적을 모두 나열한 문서입니다. 즉, 학교에서는 성적일람표를 활용해 성적, 코멘트 등 학업 평가 결과를 입력하고 기록합니다. 여기에 학생의 나이, 주소 같은 인적 정보를 함께 기록해 두면 성적통지표를 작성할 때 유용한 참고 자료로 삼을 수 있습니다.
성적통지표는 각 학생의 학업 성취도를 요약한 문서입니다. 즉, 특정 평가 시험에서 해당 학생이 거둔 전체적인 성취 수준을 보여주는 개인별 평가 보고서라고 할 수 있습니다.
성적통지표의 필수 구성 요소
학교 성적통지표에는 학생 정보, 과목별 점수, 과목별 등급, 반별 등급이 포함되며, 학생의 전체 백분율, 최종 등급, 석차까지 함께 표시됩니다.
1. 학생 정보
학생 ID, 이름, 학년, 반, 학교 정보, 학기 정보, 평가 체계 등이 이 항목에 포함됩니다.
2. 성적일람표
이 항목에는 과목별·학기별 성적일람표가 담깁니다. 기본적으로 학생들의 ID와 이름, 과목별 취득 점수가 기록됩니다.
3. 학기별 성적통지표
개별 학생의 학기별 성적통지표가 이곳에 들어갑니다. 모든 학생의 학기별 개인 성적통지표가 간결하게 정리되어 있습니다.
4. 누적 성적통지표
개별 학생의 누적 성적통지표입니다. 해당 학생의 모든 학기 성적일람표를 하나로 종합한 요약본이라고 이해하면 됩니다.
엑셀로 성적통지표 만드는 방법
예제 통합문서에서는 9학년 10명의 학생을 대상으로 엑셀 성적통지표를 만들었습니다. 데이터 세트에는 성적통지표의 네 가지 핵심 구성 요소에 해당하는 워크시트 4개를 준비했습니다. 또한 학생 이름과 반을 매번 직접 입력하는 대신 VLOOKUP 함수를 사용해 학생 ID만 입력하면 이름과 반이 자동으로 불러와지도록 구성했습니다. 아울러 SUM 함수로 학생의 총점을 계산하고, IF 함수를 활용하면 학교의 평가 체계에 맞춰 등급도 손쉽게 산출할 수 있습니다.
엑셀 성적통지표 작성 단계
아래 단계를 순서대로 따라 하면 누구나 손쉽게 성적통지표를 완성할 수 있습니다. 👇
📌 1단계: 기본 정보 시트 만들기
성적통지표를 만들기 전에 학생 정보, 학교 정보, 학기 정보, 평가 체계 등을 미리 기록해 두면 작업이 훨씬 편리합니다. 먼저 학생 ID, 이름, 학년, 반, 학교명, 소재지, 교장 선생님, 담임 교사 이름, 평가 체계 등을 담은 '기본 정보' 시트를 작성하세요.
📌 2단계: 학기별 학생 성적일람표 만들기
이제 과목별·학기별·학생별 성적일람표를 준비합니다. 여기에는 학생의 ID와 이름, 그리고 학기마다 과목별로 취득한 점수를 기록합니다.
점수를 기록했다면 각 과목의 등급도 함께 산정해야 합니다. 등급을 일일이 입력하는 일은 번거롭습니다. 이럴 때 중첩 IF(nested IF)를 사용하면 등급 산정을 자동화할 수 있습니다. 등급 셀에 아래 수식을 입력해 보세요. 👇
=IF(E8>=90,"O",IF(E8>=80,"A+",IF(E8>=70,"A",IF(E8>=60,"B+",IF(E8>=50,"B",IF(E8>=40,"C","F"))))))
🔎 수식 동작 원리
- =IF( — 등호(=)는 수식의 시작을 의미합니다. IF를 입력하면 IF 함수가 실행되며, 이 함수는 논리 검증, 참일 때 반환값, 거짓일 때 반환값의 세 가지 인수를 필요로 합니다.
- =IF(E8>=90,"O",IF( — 첫 번째 인수는 논리 검증입니다. E8 셀의 점수가 90점 이상인지 확인합니다. 두 번째 인수는 조건이 참(true)일 때 반환되는 값으로, 학교의 평가 체계상 90점 이상이면 "O" 등급이므로 "O"를 입력합니다. 문자 값이므로 반드시 큰따옴표(" ") 안에 넣어야 합니다. 세 번째 인수는 조건이 거짓(false)일 때 반환되는 값인데, 90점 미만일 수 있는 경우가 여러 개이므로 단순히 하나의 값만 지정할 수 없습니다. 이때 중첩 IF를 사용해 나머지 구간을 계속 판별합니다.
- =IF(E8>=90,"O",IF(E8>=80,"A+",IF( — E8 셀 값이 90 이상이 아니라면 다음 IF 문이 실행됩니다. 값이 80보다 크면 "A+"가 반환되고, 80 이하라면 다음 IF로 넘어갑니다.
이런 식으로 평가 체계의 모든 등급 구간을 만족할 때까지 IF를 이어 붙이면 됩니다.
수식 작성 후 채우기 핸들을 드래그하면 다른 과목 열에도 수식을 복사할 수 있습니다.
완성된 결과는 아래와 같습니다. 👇
📌 3단계: 학기별 성적통지표 만들기
이제 성적통지표 작성의 핵심 단계입니다. 2단계에서 만든 성적일람표를 바탕으로 학생별 학기 성적통지표를 만들어 보겠습니다. 👇
작업 순서:
- 성적통지표 상단에 학생 ID, 이름, 반, 출석률, 학기 번호 등 기본 정보를 기록합니다. 이때 '기본 정보' 시트에서 VLOOKUP 함수로 학생 이름과 반을 자동으로 불러오면 매번 반복 입력할 필요가 없습니다. 학생 ID만 입력하면 엑셀이 알아서 이름과 반을 찾아줍니다.
- 학생 ID에 따라 이름을 자동으로 불러오려면 이름을 입력할 E7 셀을 클릭한 뒤 아래 수식을 입력합니다:
=VLOOKUP(C7,'Basic Information'!B5:D14,2)
🔎 수식 동작 원리
- =VLOOKUP( — 등호(=)로 수식을 시작하고 VLOOKUP 함수를 호출합니다. 이 함수는 4개의 인수를 받습니다.
- =VLOOKUP(C7, — ID에 해당하는 학생 이름을 찾으려면 먼저 조회할 값을 지정해야 합니다. 학생 ID가 있는 C7 셀을 조회 값으로 선택한 뒤 쉼표를 입력합니다.
- =VLOOKUP(C7,'Basic Information'!B5:D14, — 다음은 조회 범위(table array)를 지정합니다. 'Basic Information' 시트에서 학생 정보 전체 범위인 B5:D14를 선택합니다. 셀을 드래그하기만 해도 범위가 고정되며, 입력 후 쉼표를 넣습니다.
- =VLOOKUP(C7,'Basic Information'!B5:D14,2) — 마지막으로 반환할 값이 있는 열 번호(col_index_num)를 지정합니다. 선택한 범위에서 학생 이름은 두 번째 열에 있으므로 2를 입력하면, 조회 값마다 두 번째 열의 값이 반환됩니다.
결과는 아래와 같습니다. 👇
- 이름을 자동화한 것처럼 같은 수식으로 학년과 반도 불러올 수 있습니다. 다만 학년·반이 선택 범위의 세 번째 열에 있으므로 col_index_num 인수에는 3을 입력합니다.
- 성적통지표 상단 부분이 완성되었다면, '학기별 성적일람표'에서 해당 학기의 과목별 점수를 가져옵니다.
- 점수를 다시 일일이 입력하는 것은 비효율적입니다. 점수를 기록할 셀을 클릭한 뒤 등호(=)를 입력하고, 학기별 성적일람표에서 해당 과목 점수가 있는 셀을 클릭한 후 Enter 키를 누르세요. 그러면 셀 참조를 통해 점수가 자동으로 연동됩니다.
이 방식으로 모든 과목 점수 셀을 성적일람표와 연결해 채워 넣으면 됩니다.
- 과목별 등급 역시 2단계에서 설명한 중첩 IF 방식으로 자동 산정할 수 있습니다.
- 등급에 따른 코멘트(remarks)도 VLOOKUP 함수로 자동화할 수 있습니다.
- 이때 조회 값은 학생이 받은 등급입니다. '기본 정보' 시트에는 등급별 코멘트 열이 마련되어 있으므로, 등급 열부터 코멘트 열까지의 범위를 선택하고, 선택 순서에 맞춰 열 번호를 지정합니다. 마지막 인수에는 FALSE(정확히 일치)를 입력합니다. 열 값이 숫자가 아니라 텍스트이기 때문에 정확히 일치 옵션을 지정하는 것이 수식의 신뢰성과 정확성 면에서 훨씬 안전합니다.
참고:
조회 범위(table array)에는 반드시 달러 기호($)를 붙여 절대 참조로 만들어야 합니다. 채우기 핸들을 아래로 드래그하면 수식이 아래 셀들에 모두 적용되는데, 절대 참조가 없으면 조회 범위까지 함께 아래로 밀려나 잘못된 결과가 나옵니다. 셀을 선택한 상태에서 F4 키를 누르면 간편하게 절대 참조로 변환할 수 있습니다.
- 총점은 SUM 함수로 구할 수 있습니다. =SUM()을 입력한 뒤 괄호 안에 과목별 점수가 있는 셀들을 모두 참조하면 됩니다. 셀을 드래그해서 선택하기만 해도 참조가 자동으로 입력됩니다.
- 다음으로 학생의 전체 백분율을 계산합니다. 백분율을 넣을 셀을 클릭하고 등호(=)를 입력한 뒤, 취득 총점 ÷ 과목 만점 합계로 나눕니다. 그러면 0~1 사이의 비율이 나오는데, 여기에 100을 곱하면 백분율 값이 됩니다.
- 마지막으로 이 전체 백분율을 평가 체계에 대입해 최종 등급을 산출합니다. 2단계와 마찬가지로 중첩 IF 조건을 활용하면 됩니다. 백분율 점수에 동일한 방식을 적용해 최종 등급을 구하세요.
📌 4단계: 누적 성적통지표 만들기
이제 학생별 누적 성적통지표를 만듭니다. 이 성적통지표에는 모든 학기의 성적일람표가 하나로 통합됩니다. 상단의 이름, 학년, 반 역시 VLOOKUP 함수로 자동으로 불러올 수 있고, 취득 점수 합산은 SUM 함수, 등급 산출은 중첩 IF로 처리하면 됩니다.
꼭 기억해야 할 사항
- VLOOKUP 함수는 말 그대로 세로(수직) 조회 방식입니다. 값을 찾을 때 열 방향으로만 조회할 수 있으며, 가로(행) 방향 조회는 불가능합니다.
- 조회 범위를 선택할 때는 반드시 조회 값이 있는 열을 첫 번째 열로 포함해야 합니다. 반환 값의 열 번호도 이 순서를 기준으로 지정합니다.
- 조회 값이 숫자라면 range_lookup 인수는 크게 중요하지 않지만, 텍스트 값이라면 항상 정확히 일치시키기 위해 range_lookup 인수를 FALSE로 지정하는 것이 좋습니다.
마무리
지금까지 엑셀로 성적통지표를 만드는 전 과정을 단계별로 살펴봤습니다. 위의 방법대로 따라 하면 학생 수가 많아도 손쉽게 성적통지표를 완성할 수 있습니다. 이 글이 실무에 도움이 되기를 바랍니다. 추가 문의나 제안이 있다면 언제든지 의견을 남겨 주세요. 이와 같은 엑셀 활용 팁은 ExcelDemy에서 더 많이 만나보실 수 있습니다.
함께 읽으면 좋은 글
- 엑셀로 일일 매출 보고서 만드는 방법 (빠르게 따라하기)
- 엑셀로 월간 보고서 만드는 방법 (빠르게 따라하기)
- 엑셀로 분기별 매출 현황 보고서 만들기 (초간단 방법)
- 영업용 MIS 보고서 엑셀로 만들기 (단계별 가이드)
- 엑셀 재고 경과(Aging) 보고서 만들기 (단계별 가이드)
- 엑셀 데이터로 PDF 보고서 생성하는 4가지 방법
- 엑셀 MIS 보고서 작성법 (실전 예제 2가지)