엑셀로 평가 데이터 관리 5 - 중복 데이터 자동 삭제 및 불필요한 문자열 일괄 삭제

ICT 활용

엑셀로 평가 데이터 관리 5 - 중복 데이터 자동 삭제 및 불필요한 문자열 일괄 삭제

정사열 0 307 1 0

온라인 수업 도구 사이트나 온라인 설문 등을 활용하여 퀴즈나 학습지를 자동 채점한 결과를 내려받아 평소 수행평가에 누적 반영하는 경우가 많습니다.

그런데 학생 접속 편의를 위해 로그인 없이 운영하거나 학생 명단을 미리 입력하지 않은 경우, 또는 완전 학습을 위해 교사가 중복 응답이 가능하도록 설정한 경우에는 학생 응답 결과 데이터를 내려받아보면 반복 응답한 학생이 매우 많게 됩니다. 게다가 이런 경우 학생이 응답할 때 자신의 이름을 직접 입력하고 응답을 시작하게 되어서 학교에서 정한 '반번이름' 규칙을 어기고 제각각으로 입력한 경우도 많지요. 이걸 일일이 대조하여 중복학생 없애고 학생 이름 규칙을 통일하려면 많은 시간이 걸립니다. 오늘은 이것을 간단히 엑셀로 처리하는 과정을 살펴보겠습니다.

 

exel001.jpg

 

Quizizz를 예로 들면, Reports에서 자신이 데이터를 내려받을 퀴즈를 선택한 다음 Download를 눌러 엑셀 호환 csv 데이터 파일을 내려받게 됩니다. 

 

exel002.jpg

 

내려받은 파일을 열어보면 위와 같이 이름이 엉망이죠. 우선 이름과 점수 부분을 제외하고 선택한 후 오른쪽 마우스 버튼을 눌러 '삭제'합니다.

 

exel003.jpg

 

그리고 엑셀의 '데이터' 메뉴에서 '정렬'을 눌러 기준을 추가하고 점수 열을 선택하여 내림차순으로 정렬합니다. 

 

exel004.jpg

 

70% 이상인 경우만 1점을 부여하기로 했다면 70% 미만인 부분을 마우스로 긁어 선택하여 삭제합니다. 

 

exel005.jpg

 

이제 남은 70% 이상은 모두 동일한 1점이므로 점수 열이 의미가 없으므로 점수 열을 선택하여 삭제한 후, 이름을 전체 선택하고 '데이터' 메뉴에서 '중복된 항목 제거'를 눌러 적용합니다. 

 

exel006.jpg

 

위와 같이 중복값이 모두 삭제된 속이 시원해지는 결과를 보여줍니다. 이 많은 것을 일일이 수작업으로 처리하려면 무척 힘들고 눈알이 빠지겠지요... 

 

exel007.jpg

 

이제 이름을 정돈해야겠지요. 불필요하게 들어 있는 '*'를 모두 삭제하려면 '홈' 메뉴에서 '찾기 및 선택' - '바꾸기'를 선택하거나, Ctrl+H키를 누릅니다. 

찾을 내용에 그냥 '*'나 '?'를 넣으면 와일드카드 기호로 인식하므로 그 앞에 '~'을 붙여 와일드카드 기호가 아닌 순수 문자열임을 표시하도록 합니다.

찾을 내용에는 삭제할 문자열을, 바꿀 내용에는 공란으로 두고 '모두 바꾸기' 버튼을 누르면 해당 문자열이 모두 사라지게 됩니다.

이와 같은 방법으로 찾을 내용에 '스페이스'(문자열의 모든 빈칸 삭제), '~?'(모든 물음표 삭제), '(*)'(괄호로 묶인 문자열 삭제) 등 삭제할 내용을 각각 넣어 '모두 바꾸기'를 적용합니다.

 

참고로 찾을 내용에 '(*)'와 같이 입력한다면, 여기에서 *는 와일드카드 기호로 적용되므로 괄호 안에 어떤 내용이 있건 괄호로 묶인 모든 문자열(예를 들어 '(김수환수환)', '(421최고)' 등)을 찾아 괄호와 함께 삭제할 수 있습니다.  

찾아바꾸기를 통해 수많은 데이터를 일거에 정리하는 것은 말할 수 없이 통쾌하지요.

 

** 찾아바꾸기를 모두바꾸기로 일괄적용하면 자칫 삭제되어서는 안될 문자열까지 삭제할 수 있으니 찾아바꿀 내용을 신중하게 입력하여 적용하도록 합니다. 만약 삭제할 경우도 있고 삭제해선 안될 경우도 있는 문자열이라면 '다음 찾기'와 '바꾸기를 통해 확인하면서 하나씩 적용하는 방법이 있습니다. '모두 바꾸기' 만큼은 아니지만 그래도 일일이 직접 찾아 바꾸는 것보다는 훨씬 빠른 작업을 할 수 있지요.

 

exel008.jpg

 

찾아바꾸기를 통해 불필요한 문자열을 모두 삭제했으면 다시 데이터 - 정렬을 통해 재정렬을 한 후, 수작업으로 이름을 알 수 없는 데이터를 삭제하거나 이름 규칙에 어긋나는 것을 그에 맞도록 수정하는 등의 작업을 합니다. 앞에서 한 자동화 작업들 덕분에 수작업으로 처리할 것은 금방 마칠 수 있지요.

이제 이름을 전체 선택하고 '데이터' 메뉴에서 '중복된 항목 제거'를 눌러 다시 한 번 중복된 이름을 삭제합니다. 

 

exel009.jpg

 

위와 같이 깔끔하게 정리된 결과를 볼 수 있습니다. 행을 맨 위에 추가하여 '이름'과 점수 항목명을 넣은 후 다른 데이터와 통합하기 좋은 일관성 있는 방식으로 만듭니다.(여기서는 모두 1을 입력했습니다. 맨 위 두 셀에 1을 입력한 후 그 두 셀을 선택한 다음 두셀 아래 오른쪽 끝을 마우스로 잡아 명단 끝까지 끌면 자동으로 모든 칸에 1이 채워집니다.)

이제 이 게시글의 이전 게시글 엑셀 팁들을 적용하여 이 데이터들을 통합하고 합산하여 수행평가에 반영하면 될 것입니다. 

0 Comments