잔액 구하기 chatgpt

아래와 같은 엑셀데이터의 계좌별  잔액을 구하는 vba

계좌번호 날짜                잔액
a              2023/02/02  300
b              2023/02/03  100
b              2023/02/04  500
a              2023/02/05  100
c              2023/02/05  100
b              2023/02/06  200

#1
Sub CalculateBalances()

    ' 입력 데이터 범위 설정
    Dim dataRange As Range
    Set dataRange = Worksheets("Sheet1").Range("A2:C7") ' 예시 데이터 범위
    
    ' 계좌번호, 날짜, 잔액 열 설정
    Dim accountCol, dateCol, balanceCol As Integer
    accountCol = 1 ' 계좌번호 열
    dateCol = 2 ' 날짜 열
    balanceCol = 3 ' 잔액 열
    
    ' 결과 출력 범위 설정
    Dim resultRange As Range
    Set resultRange = Worksheets("Sheet1").Range("E2:F7") ' 결과 범위
    
    ' 입력 데이터를 계좌별로 정렬
    dataRange.Sort key1:=dataRange.Columns(accountCol), key2:=dataRange.Columns(dateCol), order1:=xlAscending, order2:=xlAscending, Header:=xlNo
    
    ' 결과 범위에 계좌번호 및 날짜 입력
    Dim currentAccount As String
    currentAccount = dataRange.Cells(1, accountCol).Value
    resultRange.Cells(1, 1).Value = currentAccount
    resultRange.Cells(1, 2).Value = dataRange.Cells(1, dateCol).Value
    
    ' 결과 범위에 잔액 입력
    Dim currentBalance, rowCounter As Long
    currentBalance = 0 ' 초기 잔액 설정
    rowCounter = 1 ' 결과 범위 행 카운터
    For i = 1 To dataRange.Rows.Count
        ' 계좌번호가 변경되면 결과 범위에 계좌번호 및 날짜 입력
        If dataRange.Cells(i, accountCol).Value <> currentAccount Then
            currentAccount = dataRange.Cells(i, accountCol).Value
            rowCounter = rowCounter + 1
            resultRange.Cells(rowCounter, 1).Value = currentAccount
            resultRange.Cells(rowCounter, 2).Value = dataRange.Cells(i, dateCol).Value
            currentBalance = 0 ' 새로운 계좌의 잔액 초기화
        End If
        
        ' 현재 계좌의 잔액 업데이트
        currentBalance = currentBalance + dataRange.Cells(i, balanceCol).Value
        
        ' 결과 범위에 현재 계좌의 잔액 입력
        resultRange.Cells(rowCounter, 3).Value = currentBalance
    Next i
    
End Sub

#2
Sub CalculateAccountBalance()

    Dim dict As New Scripting.Dictionary ' 딕셔너리 객체 생성
    Dim lastRow As Long, i As Long
    Dim account As String, balance As Long
    
    ' 마지막 행 찾기
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    ' 계좌번호를 기준으로 정렬
    Range("A2:C" & lastRow).Sort Key1:=Range("A2"), Order1:=xlAscending, _
                                 Key2:=Range("B2"), Order2:=xlAscending, _
                                 Header:=xlYes
    
    ' 각 계좌별로 마지막 잔액 구하기
    For i = 2 To lastRow
        account = Cells(i, "A").Value
        balance = Cells(i, "C").Value
        
        ' 이전 계좌와 같으면 딕셔너리 값을 업데이트하고, 다르면 새로운 항목을 추가
        If dict.Exists(account) Then
            dict(account) = balance
        Else
            dict.Add account, balance
        End If
    Next i
    
    ' 결과 출력하기
    Range("E1:F1").Value = Array("계좌번호", "잔액")
    Range("E2").Resize(dict.Count, 1).Value = Application.Transpose(dict.Keys)
    Range("F2").Resize(dict.Count, 1).Value = Application.Transpose(dict.Items)
    
End Sub

#3
Sub CalculateBalances()
    Dim lastRow As Long
    Dim dataRange As Range
    Dim accountBalances As New Scripting.Dictionary
    
    ' 데이터 시트에서 계좌번호, 날짜, 잔액 데이터 범위 가져오기
    lastRow = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
    Set dataRange = Worksheets("Data").Range("A2:C" & lastRow)
    
    ' 데이터를 계좌번호별로 루프하면서 최종 잔액 계산
    For Each row In dataRange.Rows
        ' 계좌번호가 새로운 경우 딕셔너리에 계좌 추가
        If Not accountBalances.Exists(row.Cells(1).Value) Then
            accountBalances.Add row.Cells(1).Value, row.Cells(3).Value
        Else ' 계좌번호가 이미 있는 경우 최종 잔액으로 갱신
            accountBalances(row.Cells(1).Value) = row.Cells(3).Value
        End If
    Next row
    
    ' 결과 출력
    With Worksheets("ans")
        ' 이전 결과 삭제
        .Cells.ClearContents
        
        ' 헤더 추가
        .Range("A1:B1").Value = Array("계좌번호", "최종잔액")
        
        ' 계좌별 최종잔액 출력
        Dim i As Integer: i = 2
        For Each account In accountBalances.Keys
            .Cells(i, 1).Value = account
            .Cells(i, 2).Value = accountBalances(account)
            i = i + 1
        Next account
    End With
End Sub

#4
Sub CalculateBalances()

    'Dim balanceSheet As Worksheet
    On Error Resume Next
    Set balanceSheet = ActiveWorkbook.Sheets("계좌잔액")
    'sheetCount = WorksheetFunction.CountIf(ActiveWorkbook.Sheets, "계좌잔액")
 
    
    If balanceSheet Is Nothing Then
        '새로운 시트 추가
        ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)).Name = "계좌잔액"
        'Set balanceSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        'balanceSheet.Name = "계좌잔액"
    Else
        Dim userInput As Variant
        userInput = MsgBox("계좌잔액 시트 내용을 지우시겠습니까?", vbYesNo, "계좌잔액 시트 내용 지우기")
        If userInput = vbYes Then
            ActiveWorkbook.Worksheets("계좌잔액").Cells.ClearContents
        Else
            Exit Sub
        End If
    End If
    
       On Error GoTo 0
    
    Worksheets("filter").Activate
    
    ' 입력 데이터 범위 설정
    Dim dataRange As Range
        ' 마지막 행 찾기
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Set dataRange = Worksheets("filter").Range("A4:q" & lastRow) ' 예시 데이터 범위
    
    ' 계좌번호, 날짜, 잔액 열 설정
    Dim accountCol, dateCol, balanceCol As Integer
    accountCol = 2 ' 계좌번호 열
    dateCol = 4 ' 날짜 열
    balanceCol = 9 ' 잔액 열
    timecol = 14 '시간 열
    idx = 17 'idx col
    
    ' 결과 출력 범위 설정
    Dim resultRange As Range
    Set resultRange = Worksheets("계좌잔액").Range("a2:e900") ' 결과 범위
    
    ' 입력 데이터를 계좌별로 정렬
    dataRange.sort key1:=dataRange.Columns(accountCol), key2:=dataRange.Columns(dateCol), key3:=dataRange.Columns(timecol), order1:=xlAscending, order2:=xlAscending, order3:=xlAscending, Header:=xlYes
    
    ' 결과 범위에 계좌번호 및 날짜 입력
    Dim currentAccount As String
    currentAccount = dataRange.Cells(1, accountCol).Value
    resultRange.Cells(1, 1).Value = currentAccount
    resultRange.Cells(1, 2).Value = dataRange.Cells(1, dateCol).Value
    
    ' 결과 범위에 잔액 입력
    Dim currentBalance, rowCounter As Long
    currentBalance = 0 ' 초기 잔액 설정
    rowCounter = 1 ' 결과 범위 행 카운터
    For i = 1 To dataRange.Rows.Count
        ' 계좌번호가 변경되면 결과 범위에 계좌번호 및 날짜 입력
        If dataRange.Cells(i, accountCol).Value <> currentAccount Then
            currentAccount = dataRange.Cells(i, accountCol).Value
            rowCounter = rowCounter + 1
            resultRange.Cells(rowCounter, 1).Value = currentAccount
            resultRange.Cells(rowCounter, 2).Value = dataRange.Cells(i, dateCol).Value
            currentBalance = 0 ' 새로운 계좌의 잔액 초기화
        End If
        
        ' 현재 계좌의 잔액 업데이트
        If IsNumeric(dataRange.Cells(i, balanceCol).Value) Then
            currentBalance = dataRange.Cells(i, balanceCol).Value
        Else
            currentBalance = 0
        End If
        
        ' 결과 범위에 현재 계좌의 잔액 입력
        resultRange.Cells(rowCounter, 3).Value = currentBalance
        
        resultRange.Cells(rowCounter, 2).Value = dataRange.Cells(i, dateCol).Value
        resultRange.Cells(rowCounter, 4).Value = dataRange.Cells(i, idx).Value
        
        '거래 시간이 없는 경우 다시확인
        If Trim(dataRange.Cells(i, timecol).Value) = "" Then
            
            resultRange.Cells(rowCounter, 5).Value = "시간열이 없으므로 정렬이 부정확, 수동 잔액확인"
        End If
        
    Next i
    
        lastRow = Worksheets("계좌잔액").Cells(Rows.Count, "A").End(xlUp).Row ' A열에서 마지막 행 번호를 가져옴
    
        Worksheets("계좌잔액").Range("A1:A" & lastRow).NumberFormat = "General"
    
    MsgBox "완료"
    
End Sub
 
 
#5 수정중
Sub CalculateBalances()

    'Dim balanceSheet As Worksheet
    On Error Resume Next
    Dim myarray(500, 5)
    Dim rng As Variant
    
    Set balanceSheet = ActiveWorkbook.Sheets("계좌잔액")
    'sheetCount = WorksheetFunction.CountIf(ActiveWorkbook.Sheets, "계좌잔액")
 
    
    If balanceSheet Is Nothing Then
        '새로운 시트 추가
        ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)).Name = "계좌잔액"
        'Set balanceSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        'balanceSheet.Name = "계좌잔액"
    Else
        Dim userInput As Variant
        userInput = MsgBox("계좌잔액 시트 내용을 지우시겠습니까?", vbYesNo, "계좌잔액 시트 내용 지우기")
        If userInput = vbYes Then
            ActiveWorkbook.Worksheets("계좌잔액").Cells.ClearContents
        Else
            Exit Sub
        End If
    End If
    
       On Error GoTo 0
    
    Worksheets("filter").Activate
    
    ' 입력 데이터 범위 설정
    Dim dataRange As Range
        ' 마지막 행 찾기
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row +3 '배열 마지막값을 못찾을것 같아서 여유를 더줌
    
    Set dataRange = Worksheets("filter").Range("A4:q" & lastRow) ' 예시 데이터 범위
    rng = Worksheets("filter").Range("A4:q" & lastRow).Value
    
    ' 계좌번호, 날짜, 잔액 열 설정
    Dim accountCol, dateCol, balanceCol As Integer
    accountCol = 2 ' 계좌번호 열
    dateCol = 4 ' 날짜 열
    balanceCol = 9 ' 잔액 열
    timecol = 14 '시간 열
    idx = 17 'idx col
    
    ' 결과 출력 범위 설정
    Dim resultRange As Range
    Set resultRange = Worksheets("계좌잔액").Range("a2:e900") ' 결과 범위
    
    ' 입력 데이터를 계좌별로 정렬
    dataRange.sort key1:=dataRange.Columns(accountCol), key2:=dataRange.Columns(dateCol), key3:=dataRange.Columns(timecol), order1:=xlAscending, order2:=xlAscending, order3:=xlAscending, Header:=xlYes
    
    ' 결과 범위에 계좌번호 및 날짜 입력
    Dim currentAccount As String
    currentAccount = dataRange.Cells(1, accountCol).Value
    resultRange.Cells(1, 1).Value = currentAccount
    resultRange.Cells(1, 2).Value = dataRange.Cells(1, dateCol).Value
    
    ' 결과 범위에 잔액 입력
    Dim currentBalance, rowCounter As Long
    currentBalance = 0 ' 초기 잔액 설정
    rowCounter = 1 ' 결과 범위 행 카운터
    'For i = 1 To dataRange.Rows.Count
    For i = 1 To UBound(rng) - 1
        ' 계좌번호가 변경되면 결과 범위에 계좌번호 및 날짜 입력
        'If dataRange.Cells(i, accountCol).Value <> currentAccount Then
        If Trim(rng(i, accountCol)) <> Trim(rng(i+1, accountCol)) then ' currentAccount Then
            
            currentAccount = Trim(rng(i, accountCol)) 'dataRange.Cells(i, accountCol).Value
            rowCounter = rowCounter + 1
            'resultRange.Cells(rowCounter, 1).Value = Trim(currentAccount)
            myarray(rowCounter, 1) = Trim(currentAccount)
            'resultRange.Cells(rowCounter, 2).Value = rng(i, dateCol) 'dataRange.Cells(i, dateCol).Value
            myarray(rowCounter, 2) = rng(i, dateCol)
            currentBalance = 0 ' 새로운 계좌의 잔액 초기화
        'End If
        
            ' 현재 계좌의 잔액 업데이트
            'If IsNumeric(dataRange.Cells(i, balanceCol).Value) Then
            If IsNumeric(rng(i, balanceCol)) Then '숫자이면 잔액으로 입력 숫자가 아니면 0으로 입력
                currentBalance = rng(i, balanceCol) ' dataRange.Cells(i, balanceCol).Value
            Else
                currentBalance = 0
            End If
            
            ' 결과 범위에 현재 계좌의 잔액 입력
            'resultRange.Cells(rowCounter, 3).Value = currentBalance
             myarray(rowCounter, 3) = currentBalance
            
            'resultRange.Cells(rowCounter, 2).Value = rng(i, dateCol) 'dataRange.Cells(i, dateCol).Value
            myarray(rowCounter, 2) = rng(i, dateCol)
            'resultRange.Cells(rowCounter, 4).Value = rng(i, idx) 'dataRange.Cells(i, idx).Value
            myarray(rowCounter, 4) = rng(i, idx)
            '거래 시간이 없는 경우 다시확인
            'If Trim(dataRange.Cells(i, timecol).Value) = "" Then
            If Trim(rng(i, timecol)) = "" Then
                'resultRange.Cells(rowCounter, 5).Value = "시간열이 없으므로 정렬이 부정확, 수동 잔액확인"
                myarray(rowCounter, 5) = "시간열이 없으므로 정렬이 부정확, 수동 잔액확인"
            End If
        
        End If
        
    Next i
    
        Worksheets("계좌잔액").Range("a2:e502") = myarray
    
        lastRow = Worksheets("계좌잔액").Cells(Rows.Count, "A").End(xlUp).Row ' A열에서 마지막 행 번호를 가져옴
    
        Worksheets("계좌잔액").Range("A1:A" & lastRow).NumberFormat = "General"
    
    MsgBox "완료"
    
End Sub