'업체별 합계'에 해당되는 글 2건

업체별 공급가액 합계 및 정렬 2 (SUMIF 함수 사용)

지난번에는 검사하는 로직이 너무 단순 무식한 방법이라 이번에는 COUNTIF 함수와 SUMIF 함수를 이용하여 가져오는 걸 해봤습니다.

VBA Code 입니다.

Option Explicit
Sub 업체별공급가합계()
    Dim rngC As Range           '// Data 시트 각 셀을 넣을 변수
    Dim rngData As Range        '// Data 시트 전체 영역 변수
    Dim sumData As Range      '// Data 시트 합계를 낼 영역 변수
    Dim rngT As Range           '// 결과 시트 변수
    Dim rngVariable As Range    '// 결과 산출 변수
    Dim i, j As Integer
    Dim oldTime As Single       '// 걸린 시간 구하는 변수 지정
   
    oldTime = Timer     '// 시간 변수 설정
    Application.ScreenUpdating = False        '// 화면 업데이트 (일시) 정지
    Set rngData = Range(Sheets("매출장").[B2], Sheets("매출장").Cells(Rows.Count, "B").End(3))
    Set sumData = Range(Sheets("매출장").[E2], Sheets("매출장").Cells(Rows.Count, "E").End(3))
    Set rngT = Sheets("업체별").[A2]    '// 결과를 도출할 영역 설정
    rngT.CurrentRegion.Offset(1).Clear
   
    For Each rngC In rngData
        Set rngVariable = Range(rngT, rngT.End(4)) 
        '// COUNTIF(범위,조건) : 범위에서 조건에 맞는게 몇개인지 카운트하라

          '// SUMIF(조건범위, 조건, 합계범위)
        If Application.CountIf(rngVariable, rngC) = 0 Then
            rngT.Offset(i) = rngC
            rngT.Offset(i, 1) = Application.SumIf(rngData, rngC, sumData)
            rngT.Offset(i, 2) = Application.SumIf(rngData, rngC, sumData) * 0.1
            rngT.Offset(i, 3) = Application.SumIf(rngData, rngC, sumData) * 1.1
            'rngT.Offset(i, 4) = Application.CountIf(rngData, rngC)     '// 중복횟수
            i = i + 1
        End If
    Next rngC
   
    With Sheets("업체별")   '// 결과를 표시할 Sheet 선택
        '--------------- 가운데 정렬, 선그리기 ----------------------------
        .Range("A1").CurrentRegion.HorizontalAlignment = xlCenter
        .Range(.[B2], .Cells(Rows.Count, "D").End(3)).NumberFormat = "#,###" '// 셀서식 : 3단위 콤마
        .UsedRange.Borders.LineStyle = 1     '// 현재시트 사용영역 선그리기
        .Range("A2").CurrentRegion.Sort key1:=Range("B2"), order1:=xlDescending  '// 내림차순 정렬
    End With
      
    MsgBox "총 " & Format(Timer - oldTime, "#0.00") & " : 초 소요"
End Sub

매출장_2.xlsm

업체별 공급가액 합계 및 정렬 방법1

업체별 공급가액을 합친 금액을 자동 계산하는 VBA 코드입니다.

원본 데이터는 다른 시트에 있고 현재 시트에서 가져오고자 하는 걸 가져오는 것입니다.

이런 결과를 가져오구요.

아래는 VBA Code 입니다. F8키를 눌러서 버그가 있는지 일일이 점검해서 완벽하게 동작합니다.

Option Explicit
Sub 업체별공급가합계()

    Dim rngC As Range    '// 각 셀을 넣을 변수
    Dim rngAll As Range  '// 색상 전체 데이터 영역을 넣을 변수
    Dim cnt, i, j As Long
    Dim Sum_Cnt As Double   '// 상황에 따라 Long, Double 지정
   
    Application.ScreenUpdating = False        '화면 업데이트 (일시) 정지
   
    Cells(1, "A").CurrentRegion.Offset(1).Clear  '// 기존 데이터 삭제
   
    '-------------------- Sheets("매출장") B열을 복사 ----------------------------------
    For i = 2 To Sheets("매출장").Cells(Rows.Count, "B").End(3).Row
        Cells(i, "A").Value = Sheets("매출장").Cells(i, "B").Value
    Next i
  
    '-------------------  중복값 모두 제거 ----------------------------
    For i = 2 To Cells(Rows.Count, "A").End(3).Row          '// A열 각셀을 순환
        For j = i + 1 To Cells(Rows.Count, "A").End(3).Row
            If Cells(i, "A") = Cells(j, "A") Then
               Cells(j, "A").Delete Shift:=xlUp         '// 셀을 위로 밀면서 삭제
               j = j - 1                                '// 셀이 삭제되었으므로 변수 1을 빼줌
            End If
        Next j
    Next i

    '------------------- 같은 이름별로 공급가액 더하기 --------------------------------
    Sum_Cnt = 0
    Set rngAll = Range([A2], Cells(Rows.Count, "A").End(3))
    For Each rngC In rngAll
        For i = 2 To Sheets("매출장").Cells(Rows.Count, "B").End(3).Row
            If rngC.Value = Sheets("매출장").Cells(i, "B").Value Then
                Sum_Cnt = Sum_Cnt + Sheets("매출장").Cells(i, "E").Value   '//공급가액 열
                rngC.Offset(0, 1).Value = Sum_Cnt
            End If
        Next i
        rngC.Offset(0, 2).Value = rngC.Offset(0, 1).Value * 0.1   '// 부가세 10%
        rngC.Offset(0, 3).Value = rngC.Offset(0, 1).Value + rngC.Offset(0, 2).Value
        Sum_Cnt = 0
    Next rngC
   
    '--------------- 셀 서식지정 및 가운데 정렬, 선그리기 ----------------------------
    Range([B2], Cells(Rows.Count, "D").End(3)).NumberFormat = "#,###" '// 셀서식 : 3단위 콤마
    Range("A1").CurrentRegion.HorizontalAlignment = xlCenter
    ActiveSheet.UsedRange.Borders.LineStyle = 1     '// 현재시트 사용영역 선그리기
   
    '-------------------- 내림차순 정렬 (필요한 경우에만 하면 됨) -------------------------------
    With Range("A2").CurrentRegion
        .Sort key1:=Range("B2"), order1:=xlDescending
    End With
   
    '------------------ 개체변수 초기화(메모리 비우기) -----------------------------
    Set rngAll = Nothing
   
    MsgBox "작업완료"
   
End Sub

첨부파일 첨부하니 필요하신 분은 받아가세요.

매출장.xlsm