엑셀 코드를 모르는 상태로 원하는 값 취합하기

아마 다룰 줄 아는 사람들이 보면 

참 쉬운 결과일지도 모른다 

그냥 단순히 피벗해서 나온 값을 원하는 위치에 

알맞게 나열하는 코드를 짜는 것 뿐이니까 

내가 할 수 있는건 오로지 gpt(업무할 때 주로 사용하는건 Copilot 이지만)한테 계속 요구하는 것뿐 

 

처음에 코드를 만들어 달라고 한건

피벗에 나온 값들을 그대로 불러오는 거였다 

5행 부터 순차적으로 나열하는 것 뿐이었다 

간단하게도 너무 잘 완성되어서 버튼 하나 만들어서 

딸깍 누르면 피벗값을 나열해주게 해놨다 

문제는 취합해야 할 피벗이 점점 늘어나면서 일어났다 

시트가 늘어나서 추가해 달라는건 별로 문제가 되지않았다

1번시트만 해주던걸 2번시트도 추가해달라는 요청을 잘 들어주었고 

그대로 나열하면 구분이 되지않으니 1번시트와 2번시트 사이 2칸을 띄워 달라는 요청도 

잘 이해해서 만들어 주었다 

 

피벗 표에서 마지막 합계를 빼달라는 요청도 척척 완수하고 있는데 

3,4번 시트를 추가하면서 문제가 생겼다 

피벗의 시트에 4행의 구분이 되어있던 '사과', '배', '토마토', '오이' 라는 열이 있다고 치자 

그 아래는 열로 숫자들이 적혀있었고 

집계하는 시트에도 똑같이 '사과', '배', '토마토', '오이' 열을 찾아서 맞는 열에 값을 집어넣어서 

나열을 하는 방식이었는데 3,4번 시트에는 예를들어 '사과' 가 아니라 'apple' 이라고 적혀있엇다 

물론 간단하게 데이터값을 사과로 바꿔주면 간단하게 해결 될 일이었다

하지만 원본 데이터는 최대한 건드리고 싶지 않았고 

예외로 'apple' 이라는 열의 값도 '사과' 열에 넣어달라고 요청했고 

코드를 그에 맞게 수정해 주었다 

 

처음엔 그 코드가 잘 실행되지 않았고 

원인이 뭔지 몰랐다 

내가 할 수 있는건 그저 안나오니 코드를 수정해달라 

계속해서 요청하니 어느순간 바꿔서 나왔다 

값이 제대로 나오긴 하는데 

누락이 있었다 

알고보니 'りんご'(일본어로 사과,링고) 라는것도 있었다 

그래서 'apple'과 'りんご'를 예외로 추가하여 이것도 '사과'에 넣어달라고 했는데 

'apple'값은 나온다 'りんご'의 값은 나열되지 않는다 

 

다시 무한굴레에 빠진다 

다시 만들어줘, 안나온다, 다시 등등 같은 질문을 계속해서 다시 해봤는데 

어느순간 코드가 크게 바뀌지 않는다 

그냥 오류를 무시하는 코드를 추가해 줄 뿐

원인이 뭔지 모르겠어서 

그냥 원본값을 바꿔야하나 하며 

피벗값의 열 이름을 '사과'로 바꿔서 해봤는데 

그래도 값이 나오지 않는다 

뭔가 문제가 생겼다 

기존에 만들어서 잘 됐던 코드를 다시 가져와서 

열값을 바꿔서 사용해야하나 고민하고 있었는데 

 

저 피벗값 나열 버튼을 누르고 

값을 다 지워주는 비우기버튼을 누르고 

두개를 왔다갔다 하면서 눌러보다보니 

값이 아예 안나오는게 아니었다 

나왔다가 사라진다

???????

 

곧바로 현재 상황에 대해서 다시 질문을 한다 

이게 나왔다가 바로 사라진다 라고 얘기를 해보니 

이미 나온 값에대해서 덮어쓰기를 하지 않는 조건을 추가해 준다고 한다

그래서 뽑아진 코드를 다시 덮어쓰고 실행을 해보니 

처음에 간단하게 시작했던 코드에서 

뭐가 많이 추가돼서 그런지 속도도 많이 느려졌다 

 

영겁의 시간이 지나고 나열이 완료됐다 

총 합계를 수작업으로 나열한 값과 매치시켜보니 

일치한다 

드디어 완성됐다 

 

기본적인 접수된 데이터를 크게 건드리지 않고 

복붙만 하면 집계를 해주게끔 만들었다 

애초에 원본에서 작업하게끔 만드는게 제일 편하겠지만 

그럼 더 많은 질문과 구분값의 중복데이터가 많아 더 많은 질문과 막힘을 반복해야했을 것이다

차라리 간단하게 원하는 값을 나오게끔만 해주고 

지식이 었었더라면 조금 수정하는 수준으로 하고 싶었는데 

너무 복잡하다 

 

최종적으로 예시의 글과 처럼 수정하게 된 코드는 이렇게 되었다 

----------------------------------------------- ----------------------------------------------- -----------------------------------------------

Sub ArrangeData()

Application.ScreenUpdating = False

    Application.Calculation = xlCalculationManual

    Dim wsSource1 As Worksheet
    Dim wsSource2 As Worksheet
    Dim wsSource3 As Worksheet
    Dim wsSource4 As Worksheet
    Dim wsTarget As Worksheet
    Dim sourceRow As Long
    Dim targetRow As Long
    Dim col As Long
    Dim lastRowSource1 As Long
    Dim lastRowSource2 As Long
    Dim lastRowSource3 As Long
    Dim lastRowSource4 As Long
    
    Set wsSource1 = Sheets("1번 시트")
    Set wsSource2 = Sheets("2번 시트")
    Set wsSource3 = Sheets("3번 시트")
    Set wsSource4 = Sheets("4번 시트")
    Set wsTarget = Sheets("집계 할 시트") ' 데이터를 배치할 시트 이름으로 변경하세요
    
    targetRow = 5 ' 데이터를 배치할 시작 행
    
    ' 마지막 행을 계산 (맨 아래줄 합계를 제외)
    lastRowSource1 = wsSource1.Cells(wsSource1.Rows.Count, "A").End(xlUp).Row - 1
    lastRowSource2 = wsSource2.Cells(wsSource2.Rows.Count, "A").End(xlUp).Row - 1
    lastRowSource3 = wsSource3.Cells(wsSource3.Rows.Count, "A").End(xlUp).Row - 1
    lastRowSource4 = wsSource4.Cells(wsSource4.Rows.Count, "A").End(xlUp).Row - 1
    
    ' 데이터를 나열하는 함수
    Call ListData(wsSource1, wsTarget, "1번 시트", lastRowSource1, targetRow)
    targetRow = targetRow + 2 ' 두 행을 비움
    Call ListData(wsSource2, wsTarget, "2번 시트", lastRowSource2, targetRow)
    targetRow = targetRow + 2 ' 두 행을 비움
    Call ListData(wsSource3, wsTarget, "3번 시트", lastRowSource3, targetRow)
    targetRow = targetRow + 2 ' 두 행을 비움
    Call ListData(wsSource4, wsTarget, "4번 시트", lastRowSource4, targetRow)
    
        Calculate
    
    Application.Calculation = xlAutomatic
    
Application.ScreenUpdating = True
    
End Sub

Sub ListData(wsSource As Worksheet, wsTarget As Worksheet, sheetName As String, lastRowSource As Long, ByRef targetRow As Long)

Application.ScreenUpdating = False

    Application.Calculation = xlCalculationManual

    Dim sourceRow As Long
    Dim col As Long
    Dim 사과Col As Long
    Dim 배Col As Long
    Dim targetCol As Long
    
    ' 사과와 배 열의 위치를 찾음
    사과Col = wsTarget.Cells(4, 1).EntireRow.Find("사과").Column
    배Col = wsTarget.Cells(4, 1).EntireRow.Find("배").Column
    
    For sourceRow = 5 To lastRowSource
        ' A열에 시트 이름을 추가
        wsTarget.Cells(targetRow, 1).Value = sheetName
        ' B~E열 값을 나열 (원래 A~D열)
        For col = 1 To 4
            wsTarget.Cells(targetRow, col + 1).Value = wsSource.Cells(sourceRow, col).Value
        Next col
        ' E~L열 값을 복사
        For col = 5 To 12 ' E~L열
            ' 특정 조건에 따라 값을 변경
            On Error Resume Next ' 오류 무시
            If Trim(UCase(wsSource.Cells(4, col).Value)) = "apple" Or Trim(UCase(wsSource.Cells(4, col).Value)) = "りんご" Then
                If wsTarget.Cells(targetRow, 사과Col).Value = "" Then
                    wsTarget.Cells(targetRow, 사과Col).Value = wsSource.Cells(sourceRow, col).Value
                End If
            ElseIf Trim(UCase(wsSource.Cells(4, col).Value)) = "peer" Then
                If wsTarget.Cells(targetRow, 배Col).Value = "" Then
                    wsTarget.Cells(targetRow, 배Col).Value = wsSource.Cells(sourceRow, col).Value
                End If
            Else
                ' 다른 열의 값도 나열
                For targetCol = 6 To 25 ' F~Y열
                    If Trim(UCase(wsSource.Cells(4, col).Value)) = Trim(UCase(wsTarget.Cells(4, targetCol).Value)) Then
                        If wsTarget.Cells(targetRow, targetCol).Value = "" Then
                            wsTarget.Cells(targetRow, targetCol).Value = wsSource.Cells(sourceRow, col).Value
                        End If
                    End If
                Next targetCol
            End If
            On Error GoTo 0 ' 오류 무시 해제
        Next col
        targetRow = targetRow + 1
    Next sourceRow
    
        Calculate
    
    Application.Calculation = xlAutomatic
    
Application.ScreenUpdating = True
    
End Sub

----------------------------------------------- ----------------------------------------------- -----------------------------------------------

코드 시작과 끝에 넣은 구문은 넣지 않았을 땐

코드가 실행되면서 하나씩 나열되는걸 보여주면서 꽤 오랜시간이 걸렸다 

저 코드를 추가해 줌으로써 

나열되는 속도가 빨라지고 한 5분 걸렸던 나열을 

10초 이내로 빠르게 해줬다 (시간은 좀 오버해서 적긴 했지만)

그만큼 비약적으로 빠르게 나열되게 되었다 

아마 데이터가 많은 탓이겠지 

 

여기서 일을 하는 동안은 이런 코드들을 만지고 있을 것 같은 느낌이 든다 

설계도 해야하는데...