반응형
학교생활기록부 점검을 할때 거쳐야 하는 작업 중, 과목별 세부능력 및 특기사항이 중복되는 학생이 있는지 확인하는 것은 굉장히 귀찮습니다. 물론, 교과 선생님이 잘 작성해주셨겠지만 사람은 누구나 실수를 할 수 있죠. 이러한 중복검사를 VBA를 이용해 자동화 해보겠습니다.
이러한 작업이 필요없는 분들께도 Excel VBA를 통해 뭔가를 만들고 싶을 때 어떻게 해야 하는가에 대한 예시로서 도움이 되었으면 합니다.
목표
다음은 학교생활기록부 세부능력 및 특기사항(이하 세특)을 Excel로 내려받은 파일의 예시입니다.
과목별, 학생별로 셀마다 정리해서 중복여부를 확인하고 싶습니다.
1. 데이터 분석
위의 데이터는 중복여부를 검사하기 힘들게 되어있습니다.
제한점은 다음과 같습니다.
(1) 과목에 상관없이 한 셀에 작성되어있음.
(2) 잘려서 두 셀에 걸쳐 기록되어있는 과목, 학생이 존재함
2. 알고리즘 생각하기
B열의 각 행을 확인해서 숫자라면 그 행은 해당 번호의 학생의 세특이 있는 행입니다.
그 행의 E열의 내용을 번호별로 구분해서 다른 시트에 합쳐서 적어줍니다.
이렇게 모든 학생의 세특이 학생별로 모아졌다면,
split 함수를 이용해서 과목별로 분류합니다.
마지막으로 조건부서식의 중복값을 이용해서 중복여부를 판단합니다.
3. 코드 작성하기
아래 내용을 모듈에 작성합니다.
Sub Setting() 'Setting 함수의 시작입니다.
Dim volume, num As Integer 'volume, num 라는 변수를 정수로 선언합니다.
Dim MyArray() As String 'MyArray라는 배열을 문자열로 선언합니다.
For i = 1 To 400 '1행부터 400행까지(여유있게 잡습니다.)
If IsNumeric(Sheet1.Cells(i, 2)) Then '만약 (i, 2) 셀이 숫자라면
num = Sheet1.Cells(i, 2) 'num에 해당 숫자를 저장합니다.
If num <> 0 Then '만약 num이 0이 아니라면(빈칸 제외)
'Worksheets(2)의 (num, 1) 셀에 이름을 작성합니다.
Worksheets(2).Cells(num, 1) = Sheet1.Cells(i, 3)
'Worksheets(2)의 (num, 2) 셀에 기존의 내용과 새로운 세특내용을 합쳐줍니다.
Worksheets(2).Cells(num, 2) = Worksheets(2).Cells(num, 2) & Sheet1.Cells(i, 5)
End If
End If
Next
End Sub
'----------------------------------------------------------------------------------
Sub Div() 'Div 함수의 시작입니다.
For i = 1 To 30 '1행부터 30행까지(여유있게 잡습니다.)
'MyArray 배열에 앞에서 (i, 2) 셀에 합쳐준 세특내용을
''Enter(줄바꿈)'을 기준으로 잘라서 저장합니다.
MyArray = Split(Worksheets(2).Cells(i, 2), Chr(10))
flag = 3 '3열부터 작성하기 위해 flag에 3을 저장합니다.
For N = 0 To UBound(MyArray) 'MyArray 배열의 처음부터 끝까지
If MyArray(N) <> "" Then '만약 내용이 ""이 아니라면(존재한다면)
'Worksheets(2)의 (i, flag) 셀에 해당 내용을 입력합니다.
Worksheets(2).Cells(i, flag).Value = MyArray(N)
flag = flag + 1 'flag 1 증가
End If
Next N
Next i
Worksheets(2).Columns("B").Delete 'B열을 삭제합니다.
End Sub
'----------------------------------------------------------------------------------
Sub main() '메인 함수입니다.
Worksheets.Add after:=Worksheets(1) ' 첫번째 시트 다음에 새로운 시트를 만듭니다.
Setting 'Setting 함수를 실행합니다.
Div 'Div 함수를 실행합니다.
Worksheets(2).Activate 'Worksheets(2)를 활성화합니다.
'A1부터 K26까지 조건부서식 -> 중복값을 적용합니다.
Range("A1:K26").Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
4. 매크로 연결하기
이전 포스트에서 다루었던 '개발 도구 > 삽입 > 단추'를 이용해서 버튼을 생성하고 main 함수를 연결합니다.
이제 버튼을 누르면 과목별, 학생별로 세특이 정리되고 중복값이 있다면 빨간색으로 경고해줍니다.
5. 파일
반응형
