ChatGPT X 엑셀 업무자동화(4) - VBA는 더 이상 어렵지 않다.

 

ChatGPT X 엑셀 업무자동화(4) - VBA는 더 이상 어렵지 않다.

지금까지 우리는 ChatGPT를 통해서 함수 문제를 해결하고 데이터정규화가 되어있지 않은 엑셀들을 정규화 시키는 방법들을 알아봤습니다. 여기까지만 해도 많은 시간을 절약했습니다. 하지만 진정한 자동화는 지금부터입니다.
엑셀 VBA, 이름만 들어도 묵직한 답답함이 느껴지시나요? 할 줄 아는 사람들에겐 이것보다 좋은 자동화 도구가 없지만 모르는 사람들은 매크로 녹화 정도만 살짝 해보다가 포기하는 마의 장벽을 한번 뛰어넘어 봅시다!

ChatGPT X 엑셀 업무자동화

목차

데이터 준비

프롬프트 작성

ChatGPT 분석

결과 검증

 

데이터 준비

우리는 지난 시간에 ChatGPT를 통한 데이터 정규화 작업으로 잘 정리된 엑셀 파일을 하나 얻어냈습니다.
 

ChatGPT X 엑셀 업무자동화(3) - 데이터정규화(Tool 사용법 포함)

 

ChatGPT X 엑셀 업무자동화

 
지난 포스팅에서 얻어낸 엑셀파일입니다.
 
이 엑셀파일에서 G열은 원래 실적을 계획으로 나눈 수식이 들어가고 백분율 서식으로 입력되어야 합니다.
또한 해당 데이터를 표로 만들고 피벗테이블로 작성하고 싶어요.
 
할 줄 안다면 금방하는 작업입니다만 이런걸 자주 하게 된다면? 미리 만들어두는게 장땡입니다.
 

프롬프트 작성

ChatGPT 프롬프트를 한번 작성해보죠
 
상황, 목적, 형태 잊지 않으셨죠?

■ 프롬프트

#상황
VBA를 사용하여 엑셀 파일에서 특정 데이터를 처리하고자 해.

#목적
1. 'Sheet1'에 A1:G217 범위의 데이터가 있고, G열에는 F열을 E열로 나눈 결과를 백분율로 표시하고 싶어.
2. 그리고 이 데이터를 이용하여 새 시트에 '매출정리'라는 이름의 표와 피벗 테이블을 생성하고 싶어.
3. 피벗 테이블에서는 '품명', '시/구', '월'을 행 필드로 사용하고, '실적', '계획', '달성률'을 값 필드로 사용하고 싶어.

#형태
가능한 엑셀 VBA 코드를 알려줘.
오류가 발생하지 않도록 미리 검토를 해줘.

 
이렇게 작성해보았습니다.

ChatGPT 분석

ChatGPT에게 물어보면 어떻게 되는지 한번 살펴봅시다.
 

ChatGPT X 엑셀 업무자동화

 
먼저 ChatGPT에게 버프를 주고 엑셀파일을 업로드 한 후 프롬프트를 입력했습니다.
첫 시도는 아래와 같은 오류가 났네요.
 

ChatGPT X 엑셀 업무자동화

 
한번에 안되면 오류가 났다고 다시 물어보면 되는데 여기서 꿀팁!

ChatGPT에게 오류가 났다고 질문할 때 저렇게 오류메시지 창을 캡쳐해서 그냥 붙여넣기 하면 됩니다.
엑셀 오류창은 텍스트가 복사가 안된다구요 ㅠㅠ 얼마나 편해졌습니까! 저거 하나하나 다 입력 안해도 되니깐!
 
오류가 났다고 물어보고나서 2번째 시도에 성공했습니다!

Sub ManageSalesData()
    Dim ws As Worksheet, newWs As Worksheet, pvtCache As PivotCache, pvtTable As PivotTable
    Dim lastRow As Long
    Dim i As Integer
    
    ' 기존 데이터 시트 설정
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' 데이터 범위 내의 마지막 행 찾기
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' G열에 달성률 계산 (백분율)
    For i = 2 To lastRow
        If ws.Cells(i, "E").Value <> 0 Then
            ws.Cells(i, "G").Value = ws.Cells(i, "F").Value / ws.Cells(i, "E").Value
            ws.Cells(i, "G").NumberFormat = "0.00%"
        Else
            ws.Cells(i, "G").Value = 0
            ws.Cells(i, "G").NumberFormat = "0.00%"
        End If
    Next i
    
    ' 새 시트 생성 및 데이터 복사
    Set newWs = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    newWs.Name = "매출정리"
    ws.Range("A1:G" & lastRow).Copy Destination:=newWs.Range("A1")
    
    ' 피벗 테이블 생성
    Set pvtCache = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=newWs.Range("A1:G" & lastRow))
        
    Set pvtTable = pvtCache.CreatePivotTable( _
        TableDestination:=newWs.Cells(1, 9), _
        TableName:="피벗테이블")
        
    ' 피벗 테이블 구성
    With pvtTable
        With .PivotFields("품명")
            .Orientation = xlRowField
            .Position = 1
        End With
        With .PivotFields("시/구")
            .Orientation = xlRowField
            .Position = 2
        End With
        With .PivotFields("월")
            .Orientation = xlRowField
            .Position = 3
        End With
        With .PivotFields("실적")
            .Orientation = xlDataField
            .Function = xlSum
        End With
        With .PivotFields("계획")
            .Orientation = xlDataField
            .Function = xlSum
        End With
        With .PivotFields("달성률")
            .Orientation = xlDataField
            .Function = xlSum
            .NumberFormat = "0.00%"
        End With
    End With
    
    ' 모든 행의 자동 크기 조정
    newWs.Cells.EntireRow.AutoFit
End Sub

 
 

결과 검증

결과를 확인해봅시다!

ChatGPT X 엑셀 업무자동화

 
G열의 달성률이 백분율로 잘 바뀌었고 피벗테이블도 우리가 계획한대로 잘 만들어졌습니다!
표가 좀 애매하긴 한데 여기서는 매출정리라는 시트를 만들어서 그 시트안에 달성률이 변경된 데이터를 넣었습니다.(ChatGPT가 그걸 표라고 생각한거 같음) 그 옆에는 피벗테이블이 만들어져있죠.
 
이정도면 음 합격점이라고 생각합니다. 
프롬프트를 간결하게 작성하려고 하다보니 제 설명이 살짝 부족했다는 느낌도 드네요.
프롬프트가 너무 길어져 버리면 배보다 배꼽이 더 큰 상황이 되는거라 가끔은 딜레마를 느끼기도 하지만 최대한 간결하면서도 정확한 지시가 담겨있는 프롬프트를 작성하는게 우리의 목적입니다.
 
VBA를 어떻게 실행 시켜야 하는지 모르겠다 하시는 분들은 찾아보면 너무 잘 정리되있는 자료가 많습니다. 그래서 굳이 여기에 따로 적지는 않습니다. 제 블로그에 정리되있는 글로 대신하겠습니다.
 

VBA 코드로 업무 자동화 시작하기

 
포스팅이 도움이 되셨다면 구독, 공감, 댓글 부탁드려요!
행복한 하루 되세요!