엑셀로 평가 데이터 관리 4 - 점수에 따라 인정점(등급) 부여하기

ICT 활용

엑셀로 평가 데이터 관리 4 - 점수에 따라 인정점(등급) 부여하기

정사열 0 394 1 0

여러 온라인 수시 평가 결과를 수행평가 등에 반영하려면 온라인 평가에서 자동 부여된 점수를 수행평가 집계 형식에 맞는 인정점으로 바꿔야 하는데요,

이걸 수작업으로 일일이 하나씩 하려고 하면 시간이 너무 많이 걸리고, 특히 교사가 인정점 부여할 때 실수로 인정점을 잘못 입력할 수도 있습니다.

엑셀의 VLOOKUP 함수를 활용하면 매우 쉽게 이 작업을 처리할 수 있습니다.

 

categ001.jpg

 

온라인 평가도구에서 내려받은 csv 평가 데이터 파일을 엑셀로 열고 불필요한 부분을 삭제한 다음, 인정점 기준표를 빈 곳에 만듭니다.

위 예를 보면 내려받은 데이터 파일에는 자동 채점된 점수가 %로 기록되어 있습니다. 

원 점수가 0% 이상이면 인정점 0점, 40% 이상이면 인정점 1점, 70% 이상이면 인정점 2점, 90% 이상이면 인정점 3점을 부여하고 싶다면 위와 같이 원점수와 인정점 표를 만듭니다.

원 점수는 항상 낮은 점수부터 높은 점수로 범위를 정해야 합니다. 

인정점 대신 등급 A, B, C나 수, 우, 미, 양 등으로 표시되도록 하려면 인정점 대신 등급을 각 란에 입력하면 됩니다.  

 

categ002.jpg

 

이제 맨 첫 학생의 원 점수 오른쪽 인정점 란(C2)을 선택한 후 '=VLOOKUP(B2,$E$3:$F$7,2,TRUE)'과 같이 입력합니다.

VLOOKUP 함수의 괄호 안에 입력할 내용은 아래와 같은 순서로 하고 쉼표로 구분합니다.

1. 원점수가 있는 셀(여기서는 B2)

2. 인정점 표의 범위(제목 셀까지 포함. 여기서는 E3:F7인데, 나중에 일괄 복사할 때 자동으로 셀의 행열 값이 변경되지 않도록 셀을 고정하기 위해 '$E$3:$F$7'과 같이 각각의 행과 열 값 앞에 '$'를 붙여야 함. 즉 참조할 인정점 표는 항상 같은 위치에서 참조하라는 의미) 

3. 부여할 인정점(등급)이 있는 열의 번호(여기서는 점수 열이 1이고 인정점 열이 2이므로 '2'를 입력) 

4. 논리값(정확한 값을 찾아 인정점이나 등급을 부여할 때는 0(또는 FALSE), 대략적 범위를 찾아 인정점이나 등급을 부여할 때는 1(또는 TRUE). 여기서는 TRUE로 지정)

 

categ003.jpg

 

위와 같이 정확히 입력하고 엔터키를 누르면 C2 셀에 새로 인정점이 기준에 맞게 입력된 것을 볼 수 있습니다.(여기서는 원점수가 100%라 90% 이상에 해당하여 인정점 3점이 자동 입력됨) 

 

categ004.jpg

 

이제 맨 위 인정점이 나타난 셀을 선택한 후 마우스로 그 셀 하단 오른쪽 가장자리를 끌어 아래로 죽 내려서 마지막 학생의 인정점 셀에서 마우스 버튼을 놓으면 모든 학생의 인정점이 자동 입력되는 것을 볼 수 있습니다.

인정점이 부여된 각 셀을 누르고 함수 입력창을 보면 '=VLOOKUP(G2,$E$3:$F$7,2,TRUE)'와 같이 맨 앞의 원점수 셀만 바뀌었을 뿐 인정점 표의 셀은 고정시켜 놓아 모두 똑같다는 것을 확인할 수 있지요. 

 

이와 같은 방식으로 하면 여러 온라인평가의 결과를 아래와 같이 다양한 인정점이나 등급으로 쉽게 수정하는 것이 가능합니다.

- 50% 이상은 1점, 그 미만이나 불참은 0점 : 0% - 0, 50% - 1 로 입력

- 다양한 피드백 주기 가능 : 0% - Do your best!, 30% - You can do it!, 50% - Good job!, 70% - Excellent!

 

또한 일단 위와 같이 인정점 부여를 마친 경우, 점수-인정점(등급) 기준표의 두 번째 열의 내용을 수정하면 모든 인정점 부여 셀이 자동으로 기준표에 입력한 값으로 바뀌는 것을 볼 수 있습니다.

인정점의 일괄 수정, 인정점을 등급이나 피드백으로 수정하고자 하는 경우 즉석에서 정말 쉽게 수정할 수 있는 것입니다.

 

0 Comments