안녕하세요!
엑셀 VBA로 하나의 파일을 여러 개로 분할하기 | 지피터스 GPTers지난번에 계획했던대로 VBA를 이용하여 학생 성적을 분석해보도록 하겠습니다. 매번 성적 분석할 때마다 2~3시간이 훌쩍 지나가 꼭 한번은 해보고 싶었던 작업이기 때문입니다.
1. 어려운 점
하나의 열에 하나의 데이터가 깔끔하게 있지 않다.
과목명이 너무 길다.
학생들의 과목별 성적만 있으면 되는데 군더더기가 너무 많다.
2. 계획 세우기
1) 중간, 기말고사 데이터 전 처리
2) 성적 향상도 나타낸 시트 만들고 향상도에 따른 셀 속성 바꾸기
3) 성적 향상도를 여러 구간으로 나누어 학생 데이터 모으기
3. 데이터 전 처리
LLM을 어느 정도 이해하는지 궁금하여 대충 물어 보았습니다.
결과는 역시나 엉망이었습니다.
이때, 엑셀의 매크로이므로 내가 직접 기능들을 순서대로 하는대로 명령을 내려야겠다고 생각했습니다. 그래서 수정한 프롬 프트는 다음과 같습니다.
이 프롬프트만으로 한번에 성공하진 못했지만 몇 번의 대화를 주고 받은 끝에 아래와 같은 결과를 얻을 수 있었습니다.
Sub ProcessWorksheet()
Dim ws As Worksheet
Set ws = ActiveSheet
' 1. 모든 셀의 "병합하고 가운데 맞춤"을 해제한다.
ws.Cells.UnMerge
' 2. 특정 열 삭제
Dim colsToDelete As String
colsToDelete = "A,C,H,J,L,M,O,Q,R,S,T,U,V,W"
Dim colArr As Variant
colArr = Split(colsToDelete, ",")
Dim i As Integer
For i = UBound(colArr) To LBound(colArr) Step -1
ws.Columns(colArr(i) & ":" & colArr(i)).Delete
Next i
' 3. 특정 행 삭제
ws.Rows("34:" & ws.Rows.Count).Delete
ws.Rows("10:10").Delete
ws.Rows("1:8").Delete
' 4. 용어 수정
Dim replacements As Variant
replacements = Array( _
Array("사회(역사/도덕포함):한국사(3)", "한국사"), _
Array("국어:국어(4)", "국어"), _
Array("수학:수학(4)", "수학"), _
Array("영어:영어(4)", "영어"), _
Array("사회(역사/도덕포함):통합사회(3)", "사회"), _
Array("과학:통합과학(3)", "과학"), _
Array("기술가정/제2외국어/한문/교양:기술·가정(2)", "기술") _
)
Dim cell As Range
For Each cell In ws.UsedRange
For i = LBound(replacements) To UBound(replacements)
cell.Replace What:=replacements(i)(0), Replacement:=replacements(i)(1), LookAt:=xlPart
Next i
Next cell
' 7. A열의 왼쪽에 새로운 열 추가
ws.Columns("A:A").Insert Shift:=xlToRight
' 8. 1행에 "반" 내용 삽입
ws.Cells(1, 1).Value = "반"
' 9. A2:A24 에 "1" 기입
ws.Range("A2:A24").Value = 1
' 5. A~J열 너비를 텍스트 길이에 맞추어 정렬
Dim col As Range
For Each col In ws.Range("A:J").Columns
col.AutoFit
Next col
' 6. A~J열의 모든 데이터의 가운데 정렬
ws.Range("A:J").HorizontalAlignment = xlCenter
End Sub결과는 놀라웠죠. 최소 10~20분은 걸릴 것 같은 작업을 순식간에 끝낸거에요.
4. 성적 향상도 나타내기
전 처리가 완료된 파일을 다시 주고, GPT에게 다음과 같이 요청해 보았습니다.
역시 몇 번의 대화를 좀더 거친 후에 아래와 같은 코드를 작성해 주었습니다.
Sub CompareGradeChanges()
Dim wb As Workbook
Set wb = ActiveWorkbook
' 중간고사 및 기말고사 시트의 참조
Dim wsMidterm As Worksheet, wsFinal As Worksheet
Set wsMidterm = wb.Sheets("중간")
Set wsFinal = wb.Sheets("기말")
' 결과를 저장할 새 시트 생성
Dim wsResults As Worksheet
Set wsResults = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count))
wsResults.Name = "성적 변화"
' 결과 시트에 헤더 설정
wsResults.Cells(1, 1).Value = "반"
wsResults.Cells(1, 2).Value = "학생 번호"
wsResults.Cells(1, 3).Value = "학생 이름"
' 과목 이름 헤더 추가
Dim col As Integer
For col = 4 To 10 ' D열부터 J열
wsResults.Cells(1, col).Value = wsMidterm.Cells(1, col).Value
Next col
' 중간고사와 기말고사에서 데이터 읽기
Dim lastRow As Long
lastRow = wsMidterm.Cells(wsMidterm.Rows.Count, 2).End(xlUp).Row
Dim i As Long, j As Long
Dim studentClass As String, studentID As String, studentName As String
Dim midtermScore As Variant, finalScore As Variant
Dim scoreChange As Double
' 데이터를 행별로 반복 처리
For i = 2 To lastRow
studentClass = wsMidterm.Cells(i, 1).Value
studentID = wsMidterm.Cells(i, 2).Value
studentName = wsMidterm.Cells(i, 3).Value
' 각 학생의 정보 입력
wsResults.Cells(i + 1, 1).Value = studentClass
wsResults.Cells(i + 1, 2).Value = studentID
wsResults.Cells(i + 1, 3).Value = studentName
' D열부터 J열까지 반복
For j = 4 To 10 ' 과목별 성적 변화 계산
midtermScore = wsMidterm.Cells(i, j).Value
finalScore = wsFinal.Cells(i, j).Value
' 성적 변화 계산
scoreChange = finalScore - midtermScore
' 결과 기록
wsResults.Cells(i + 1, j).Value = scoreChange
' 성적 변화에 따른 서식 조정
If Abs(scoreChange) >= 30 Then
With wsResults.Cells(i + 1, j)
.Font.Bold = True
.Font.Color = vbWhite
If scoreChange > 0 Then
.Interior.Color = vbRed ' 양수일 때 붉은색
Else
.Interior.Color = vbBlue ' 음수일 때 파란색
End If
End With
ElseIf Abs(scoreChange) >= 20 And Abs(scoreChange) < 30 Then
With wsResults.Cells(i + 1, j)
.Font.Bold = True
If scoreChange > 0 Then
.Interior.Color = vbYellow ' 양수일 때 노란색
Else
.Interior.Color = vbCyan ' 음수일 때 하늘색
End If
End With
End If
Next j
Next i
' 결과 시트의 열 너비 자동 조절
wsResults.Columns.AutoFit
End Sub결과는 너무 멋졌습니다. 제가 신이 나서 색을 제 마음대로 했지만, 사실 양수와 음수는 각각 유사한 계열의 색상으로, 점수 차이에 따라 색의 진하기를 다르게 해야 하지만 우선은 아래와 같이 나왔답니다^^
5. 학생 데이터 별도로 모아보기
좀더 구분해 보고 싶었습니다. 학생들을 향상도 점수와 함께 하나의 시트에요. GPT에게 다시 요청했습니다.
결과는 C열에 있는 학생의 이름 대신 자꾸 B열에 있는 번호를 가져왔습니다. 그래서 해당 부분을 찾아 “2”를 “3”으로 수정했더니 결과가 잘 나왔습니다. 코드는 아래와 같습니다.
Sub AnalyzeScores()
Dim srcSheet As Worksheet, destSheet As Worksheet
Dim i As Integer, j As Integer
Dim scoreCell As Range, nameCell As Range
Dim lastColumn As Long, colLetter As String
Dim categories As Variant
Dim categoryString As String
Dim rowIndex As Variant
Application.ScreenUpdating = False
' 성적 변화 시트 설정
Set srcSheet = ThisWorkbook.Sheets("성적 변화")
lastColumn = srcSheet.Cells(1, Columns.Count).End(xlToLeft).Column
' 분석 시트 추가
Set destSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
destSheet.Name = "분석"
' 분석 시트 맞춤 설정
Range("A1:H7").VerticalAlignment = xlTop
' 카테고리 설정
categories = Array("30.0 이상", "20.0_29.99", "10.0_19.99", "-10.0_-19.99", "-20.0_-29.99", "-30.0 이하")
For i = 0 To UBound(categories)
destSheet.Cells(i + 2, 1).Value = categories(i)
Next i
' 과목명 처리 및 데이터 입력
For i = 4 To lastColumn
colLetter = Split(srcSheet.Cells(1, i).Address(True, False), "$")(0)
destSheet.Cells(1, i - 2).Value = srcSheet.Cells(1, i).Value
' 데이터 처리
For Each scoreCell In srcSheet.Range(colLetter & "3:" & colLetter & "25")
categoryString = DetermineCategory(scoreCell.Value)
If categoryString <> "" Then
Set nameCell = srcSheet.Cells(scoreCell.Row, 3) ' 이름은 C열
rowIndex = Application.Match(categoryString, categories, 0)
If Not IsError(rowIndex) Then
destSheet.Cells(rowIndex + 1, i - 2).Value = destSheet.Cells(rowIndex + 1, i - 2).Value & nameCell.Value & " (" & scoreCell.Value & ")" & Chr(10)
End If
End If
Next scoreCell
' 열 너비 자동 조정
destSheet.Columns(i - 2).AutoFit
Next i
' 전체 시트의 열 너비 조정
destSheet.Cells.EntireColumn.AutoFit
' 셀 높이 자동 조정
For i = 2 To UBound(categories) + 2
destSheet.Rows(i).EntireRow.AutoFit
Next i
Application.ScreenUpdating = True
End Sub
Function DetermineCategory(score As Double) As String
Select Case score
Case Is >= 30
DetermineCategory = "30.0 이상"
Case 20 To 29.99
DetermineCategory = "20.0_29.99"
Case 10 To 19.99
DetermineCategory = "10.0_19.99"
Case -19.99 To -10
DetermineCategory = "-10.0_-19.99"
Case -29.99 To -20
DetermineCategory = "-20.0_-29.99"
Case Is <= -30
DetermineCategory = "-30.0 이하"
Case Else
DetermineCategory = ""
End Select
End Function결과는 아래와 같습니다.
6. 마무리
사실 처음에는 30점 이상과 -30점 이하에만 학생들 이름이 반환되었습니다. 그래서 알아보니 “~”이라는 문자 때문에 값을 반환해오지 못한다는 것을 알게 되었습니다. 그래서 “~”을 “_”로 수정했더니 결과가 잘 나올 수 있었습니다.
VBA 코드를 짤 때 오류가 뜨면 계속해서 다시 질문하는 방법밖에는 없었습니다. 만약 코드 볼 줄을 조금이라도 배운다면 GPT의 활용이 좀더 수월하지 않을까 생각해 보았습니다.
이제 코드를 공부할 때입니다^^
끝!
#10기로우코드