Xin trợ giúp code VBA đếm và tính tổng nhiều điều kiện (1 người xem)

Liên hệ QC

Người dùng đang xem chủ đề này

Đang trực tuyến: 1 (Thành viên: 0, Khách: 1)
H

Hugo Nguyen

Thành viên mới
Tham gia
15/8/20
Bài viết
38
Được thích
4
Xin chào mọi người!
Mình có 1 file gồm 2 sheet:
Sheet 1 chứa dữ liệu nguồn có 8 cột nhưng số hàng lên tới 1 triệu hàng, chứa dữ liệu giao dịch khách hàng
Sheet 2 là sheet tổng hợp, số hàng cũng lên tới 1000 hàng, mình hiện tại đang dùng hàm COUNTIFS và SUMIFS để tính số lần giao dịch và số tiền giao dịch của mỗi khách hàng (nhiều điều kiện)
Vấn đề là dữ liệu nguồn quá nhiều nên công thức không thể chạy được, mong các bác chỉ giáo chuyển công thức sang VBA giúp
Xin cảm ơn nhiều!
 

File đính kèm

Hugo Nguyen đã viết:
Xin chào mọi người!
Mình có 1 file gồm 2 sheet:
Sheet 1 chứa dữ liệu nguồn có 8 cột nhưng số hàng lên tới 1 triệu hàng, chứa dữ liệu giao dịch khách hàng
Sheet 2 là sheet tổng hợp, số hàng cũng lên tới 1000 hàng, mình hiện tại đang dùng hàm COUNTIFS và SUMIFS để tính số lần giao dịch và số tiền giao dịch của mỗi khách hàng (nhiều điều kiện)
Vấn đề là dữ liệu nguồn quá nhiều nên công thức không thể chạy được, mong các bác chỉ giáo chuyển công thức sang VBA giúp
Xin cảm ơn nhiều!
Nhấp chuột vào đây để mở rộng...
Up cái file lên xem thử.
 
Trời. Công ty quản lý hàng triệu phát sinh giao dịch khách hàng bằng Excel. Hà tiện dữ.
Quăng cái bảng phát sinh vào một Data Model rồi từ đó mà tổng hợp này nọ.
Nếu bạn không biết Data Model trong Excel là gì thì tìm học là vừa. Dữ liệu khủng kiểu này thì không thể mỗi công việc lại nhờ người ta viết code VBA.
 
VetMini đã viết:
Trời. Công ty quản lý hàng triệu phát sinh giao dịch khách hàng bằng Excel. Hà tiện dữ.
Quăng cái bảng phát sinh vào một Data Model rồi từ đó mà tổng hợp này nọ.
Nếu bạn không biết Data Model trong Excel là gì thì tìm học là vừa. Dữ liệu khủng kiểu này thì không thể mỗi công việc lại nhờ người ta viết code VBA.
Nhấp chuột vào đây để mở rộng...
Do em cũng mới làm cái này thời gian ngắn đây thôi, hiện tại vẫn chỉ dùng hàm excel và VBA xử lý, cảm ơn lời khuyên và gợi ý của bác :)
 
Hugo Nguyen đã viết:
Xin chào mọi người!
Mình có 1 file gồm 2 sheet:
Sheet 1 chứa dữ liệu nguồn có 8 cột nhưng số hàng lên tới 1 triệu hàng, chứa dữ liệu giao dịch khách hàng
Sheet 2 là sheet tổng hợp, số hàng cũng lên tới 1000 hàng, mình hiện tại đang dùng hàm COUNTIFS và SUMIFS để tính số lần giao dịch và số tiền giao dịch của mỗi khách hàng (nhiều điều kiện)
Vấn đề là dữ liệu nguồn quá nhiều nên công thức không thể chạy được, mong các bác chỉ giáo chuyển công thức sang VBA giúp
Xin cảm ơn nhiều!
Nhấp chuột vào đây để mở rộng...
Thử code
Mã: 
Sub XYZ()
  Dim sArr(), tArr, Arr, kRes(), tRes(), Res, Dic As Object
  Dim sRow&, sCol&, n&, i&, iR&, k&, jC&, iKey
 
  Application.ScreenUpdating = False
  tArr = Array("D2:AJ2", "AL2:BR2", "BT2:CS2", "CU2:DT2")
  ReDim tRes(0 To 3)
  Set Dic = CreateObject("scripting.dictionary")
  With Sheets("DATA")
    sArr = .Range("A2", .Range("I" & Rows.Count).End(xlUp)).Value
  End With
  sRow = UBound(sArr)
  ReDim kRes(1 To sRow / 5, 1 To 1)
  For i = 1 To sRow
    iKey = sArr(i, 1)
    If Dic.exists(iKey) = False Then
      k = k + 1
      kRes(k, 1) = iKey
      Dic.Add iKey, k
    End If
  Next i
  With Sheets("FINAL")
    For n = 0 To 3
      Arr = .Range(tArr(n)).Value
      sCol = UBound(Arr, 2)
      ReDim Res(1 To k, 1 To sCol)
      tRes(n) = Res
      For j = 1 To sCol - 2
        Dic.Item(n & "#" & Arr(1, j)) = j
      Next j
    Next n
    
    For i = 1 To sRow
      iR = Dic.Item(sArr(i, 1))
      n = 0
      If Dic.exists(n & "#" & sArr(i, 2)) Then
        jC = Dic.Item(n & "#" & sArr(i, 2))
        tRes(n)(iR, jC) = tRes(n)(iR, jC) + 1
      End If
      n = 1
      If Dic.exists(n & "#" & sArr(i, 2)) Then
        jC = Dic.Item(n & "#" & sArr(i, 2))
        tRes(n)(iR, jC) = tRes(n)(iR, jC) + sArr(i, 9)
      End If
      n = 2
      If Dic.exists(n & "#" & sArr(i, 4)) Then
        jC = Dic.Item(n & "#" & sArr(i, 4))
        tRes(n)(iR, jC) = tRes(n)(iR, jC) + 1
      End If
      n = 3
      If Dic.exists(n & "#" & sArr(i, 4)) Then
        jC = Dic.Item(n & "#" & sArr(i, 4))
        tRes(n)(iR, jC) = tRes(n)(iR, jC) + sArr(i, 9)
      End If
    Next i
    
    i = .Range("C" & Rows.Count).End(xlUp).Row
    If i > 3 Then .Range("C4:DT" & i).ClearContents
    .Range("C4").Resize(k, sCol) = kRes
    
    For n = 0 To 3
      Res = tRes(n)
      sCol = UBound(tRes(n), 2)
      For i = 1 To k
        For j = 1 To sCol - 2
          tRes(n)(i, sCol - 1) = tRes(n)(i, sCol - 1) + tRes(n)(i, j)
        Next j
        tRes(n)(i, sCol) = tRes(n)(i, sCol - 1) / (sCol - 2)
      Next i
      .Cells(4, .Range(tArr(n)).Column).Resize(k, sCol) = tRes(n)
    Next n
  End With
  Application.ScreenUpdating = True
End Sub
 
HieuCD đã viết:
Thử code
Mã: 
Sub XYZ()
  Dim sArr(), tArr, Arr, kRes(), tRes(), Res, Dic As Object
  Dim sRow&, sCol&, n&, i&, iR&, k&, jC&, iKey

  Application.ScreenUpdating = False
  tArr = Array("D2:AJ2", "AL2:BR2", "BT2:CS2", "CU2:DT2")
  ReDim tRes(0 To 3)
  Set Dic = CreateObject("scripting.dictionary")
  With Sheets("DATA")
    sArr = .Range("A2", .Range("I" & Rows.Count).End(xlUp)).Value
  End With
  sRow = UBound(sArr)
  ReDim kRes(1 To sRow / 5, 1 To 1)
  For i = 1 To sRow
    iKey = sArr(i, 1)
    If Dic.exists(iKey) = False Then
      k = k + 1
      kRes(k, 1) = iKey
      Dic.Add iKey, k
    End If
  Next i
  With Sheets("FINAL")
    For n = 0 To 3
      Arr = .Range(tArr(n)).Value
      sCol = UBound(Arr, 2)
      ReDim Res(1 To k, 1 To sCol)
      tRes(n) = Res
      For j = 1 To sCol - 2
        Dic.Item(n & "#" & Arr(1, j)) = j
      Next j
    Next n
   
    For i = 1 To sRow
      iR = Dic.Item(sArr(i, 1))
      n = 0
      If Dic.exists(n & "#" & sArr(i, 2)) Then
        jC = Dic.Item(n & "#" & sArr(i, 2))
        tRes(n)(iR, jC) = tRes(n)(iR, jC) + 1
      End If
      n = 1
      If Dic.exists(n & "#" & sArr(i, 2)) Then
        jC = Dic.Item(n & "#" & sArr(i, 2))
        tRes(n)(iR, jC) = tRes(n)(iR, jC) + sArr(i, 9)
      End If
      n = 2
      If Dic.exists(n & "#" & sArr(i, 4)) Then
        jC = Dic.Item(n & "#" & sArr(i, 4))
        tRes(n)(iR, jC) = tRes(n)(iR, jC) + 1
      End If
      n = 3
      If Dic.exists(n & "#" & sArr(i, 4)) Then
        jC = Dic.Item(n & "#" & sArr(i, 4))
        tRes(n)(iR, jC) = tRes(n)(iR, jC) + sArr(i, 9)
      End If
    Next i
   
    i = .Range("C" & Rows.Count).End(xlUp).Row
    If i > 3 Then .Range("C4:DT" & i).ClearContents
    .Range("C4").Resize(k, sCol) = kRes
   
    For n = 0 To 3
      Res = tRes(n)
      sCol = UBound(tRes(n), 2)
      For i = 1 To k
        For j = 1 To sCol - 2
          tRes(n)(i, sCol - 1) = tRes(n)(i, sCol - 1) + tRes(n)(i, j)
        Next j
        tRes(n)(i, sCol) = tRes(n)(i, sCol - 1) / (sCol - 2)
      Next i
      .Cells(4, .Range(tArr(n)).Column).Resize(k, sCol) = tRes(n)
    Next n
  End With
  Application.ScreenUpdating = True
End Sub
Nhấp chuột vào đây để mở rộng...
Cảm ơn bác, file ví dụ đã chạy OK, nhưng file thực tế của em số cột nhiều hơn cũng như hạng mục tính nhiều hơn, em đang sửa code của bác cho phù hợp, trong quá trình sửa gặp vấn đề mong bác chỉ giáo, cảm ơn bác.
 
Hugo Nguyen đã viết:
Xin chào mọi người!
Mình có 1 file gồm 2 sheet:
Sheet 1 chứa dữ liệu nguồn có 8 cột nhưng số hàng lên tới 1 triệu hàng, chứa dữ liệu giao dịch khách hàng
Sheet 2 là sheet tổng hợp, số hàng cũng lên tới 1000 hàng, mình hiện tại đang dùng hàm COUNTIFS và SUMIFS để tính số lần giao dịch và số tiền giao dịch của mỗi khách hàng (nhiều điều kiện)
Vấn đề là dữ liệu nguồn quá nhiều nên công thức không thể chạy được, mong các bác chỉ giáo chuyển công thức sang VBA giúp
Xin cảm ơn nhiều!
Nhấp chuột vào đây để mở rộng...
Sao không thử với PivotTable.
 

File đính kèm

HieuCD đã viết:
Thử code
Mã: 
Sub XYZ()
  Dim sArr(), tArr, Arr, kRes(), tRes(), Res, Dic As Object
  Dim sRow&, sCol&, n&, i&, iR&, k&, jC&, iKey

  Application.ScreenUpdating = False
  tArr = Array("D2:AJ2", "AL2:BR2", "BT2:CS2", "CU2:DT2")
  ReDim tRes(0 To 3)
  Set Dic = CreateObject("scripting.dictionary")
  With Sheets("DATA")
    sArr = .Range("A2", .Range("I" & Rows.Count).End(xlUp)).Value
  End With
  sRow = UBound(sArr)
  ReDim kRes(1 To sRow / 5, 1 To 1)
  For i = 1 To sRow
    iKey = sArr(i, 1)
    If Dic.exists(iKey) = False Then
      k = k + 1
      kRes(k, 1) = iKey
      Dic.Add iKey, k
    End If
  Next i
  With Sheets("FINAL")
    For n = 0 To 3
      Arr = .Range(tArr(n)).Value
      sCol = UBound(Arr, 2)
      ReDim Res(1 To k, 1 To sCol)
      tRes(n) = Res
      For j = 1 To sCol - 2
        Dic.Item(n & "#" & Arr(1, j)) = j
      Next j
    Next n
   
    For i = 1 To sRow
      iR = Dic.Item(sArr(i, 1))
      n = 0
      If Dic.exists(n & "#" & sArr(i, 2)) Then
        jC = Dic.Item(n & "#" & sArr(i, 2))
        tRes(n)(iR, jC) = tRes(n)(iR, jC) + 1
      End If
      n = 1
      If Dic.exists(n & "#" & sArr(i, 2)) Then
        jC = Dic.Item(n & "#" & sArr(i, 2))
        tRes(n)(iR, jC) = tRes(n)(iR, jC) + sArr(i, 9)
      End If
      n = 2
      If Dic.exists(n & "#" & sArr(i, 4)) Then
        jC = Dic.Item(n & "#" & sArr(i, 4))
        tRes(n)(iR, jC) = tRes(n)(iR, jC) + 1
      End If
      n = 3
      If Dic.exists(n & "#" & sArr(i, 4)) Then
        jC = Dic.Item(n & "#" & sArr(i, 4))
        tRes(n)(iR, jC) = tRes(n)(iR, jC) + sArr(i, 9)
      End If
    Next i
   
    i = .Range("C" & Rows.Count).End(xlUp).Row
    If i > 3 Then .Range("C4:DT" & i).ClearContents
    .Range("C4").Resize(k, sCol) = kRes
   
    For n = 0 To 3
      Res = tRes(n)
      sCol = UBound(tRes(n), 2)
      For i = 1 To k
        For j = 1 To sCol - 2
          tRes(n)(i, sCol - 1) = tRes(n)(i, sCol - 1) + tRes(n)(i, j)
        Next j
        tRes(n)(i, sCol) = tRes(n)(i, sCol - 1) / (sCol - 2)
      Next i
      .Cells(4, .Range(tArr(n)).Column).Resize(k, sCol) = tRes(n)
    Next n
  End With
  Application.ScreenUpdating = True
End Sub
Nhấp chuột vào đây để mở rộng...
Em dùng Code bác sửa theo yêu cầu thực tế nhưng lúc chạy nó báo lỗi, VBA em lại mù mờ quá, em gửi bản em sửa lên đây nhờ bác xem sửa lại giúp em với, cảm ơn bác rất nhiều!
Bài đã được tự động gộp:

be09 đã viết:
Sao không thử với PivotTable.
Nhấp chuột vào đây để mở rộng...
Vâng em cũng đang thử với Pivot Table, có vấn đề gì mong bác giúp đỡ, cảm ơn bác
 

File đính kèm

Hugo Nguyen đã viết:
Em dùng Code bác sửa theo yêu cầu thực tế nhưng lúc chạy nó báo lỗi, VBA em lại mù mờ quá, em gửi bản em sửa lên đây nhờ bác xem sửa lại giúp em với, cảm ơn bác rất nhiều!
Bài đã được tự động gộp:


Vâng em cũng đang thử với Pivot Table, có vấn đề gì mong bác giúp đỡ, cảm ơn bác
Nhấp chuột vào đây để mở rộng...
Các cột mới, xét dữ liệu ở dòng 3
Mình không khuyến khích cờ bạc, bạn nhờ người khác
 
VetMini đã viết:
Thớt nói bên nguồn có hàng triệu dòng, bên tổng hợp cũng có hàng ngàn dòng. E rằng chỉ có Data Model mới đưa cho Power Pivot làm được.
Nhấp chuột vào đây để mở rộng...
Vâng dữ liệu nguồn mỗi tháng có khoảng 1 triệu dòng bác ạ, mà e muốn tổng hợp nhiều tháng một lần, nếu chỉ đơn thuần Pivot Tabel thì không thể làm đúng không bác?
Còn Data Model e chưa làm qua bao giờ? Cho e hỏi cái đó khó không bác?
 
Cũng giống như học Excel.
Muốn học thiết kế file trước thì lúc đầu khó lúc sau càng dễ, bởi vì thiết kế file thuận lợi thì công thức khá đơn giản.
Muốn học công thức trước thì lúc đầu dễ lúc sau càng lúc càng khó, bởi vì nếu file thiết kế không thuận lợi thì phải dùng công thức khủng.
 
Trường hợp của bạn giống của tôi, dữ liệu tổng hợp theo một số tháng nó lên tới hàng chục triệu dòng. Tôi nghĩ bạn học Power Bi hoặc Tableau sẽ dễ dàng xử lý hơn. Dùng excel tôi nghĩ không chịu nổi nhiệt đâu!
 
thật ra với dữ liệu lớn như vậy nếu lựa chọn mình sẽ chọn Pivot Table, với VBA chạy vòng lập xử lý đống dữ liệu đó lâu hơn nhiều, còn sumifs thì thôi thua luôn.
 
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

Bài viết mới nhất

K
Tạo mã thiết bị
Trả lời
8
Đọc
160
thuytrangbp28
T
H
Em chào anh chị ạ.Em cần xếp lịch nghỉ randum cho 1 số nhóm làm việc trong 1 công ty.
Trả lời
3
Đọc
79
Hoan6868
H
1
Em hỏi chút về hàm điều kiện với liệt kê (1 người xem)
Trả lời
2
Đọc
108
123456it
1
M
File excel ứng dụng trong nhập xuất kho, thu chi, bán hàng và công nợ (1 người xem)
    • Thích
    • Yêu thích
    • Cảm ơn
7 8 9
Trả lời
165
Đọc
31K
quick87
quick87
chickenexcel
xin hướng dẫn code chọn hình ảnh trong file (2 người xem)
Trả lời
2
Đọc
69
chickenexcel
chickenexcel
tramcannt
data validation
Trả lời
2
Đọc
81
ptm0412
ptm0412
giaiphap
Add-Ins cho Excel 2007 -2016 32bit và 64bit (2 người xem)
    • Thích
    • Yêu thích
    • Ngạc nhiên
24 25 26
Trả lời
514
Đọc
204K
hoangtuan.net
hoangtuan.net
Nguyễn Duy Tuân
  • Dán lên cao
Phiên bản mới Add-in A-Tools AI v10 (2024) (1 người xem)
    • Thích
2 3
Trả lời
56
Đọc
7K
Nguyễn Duy Tuân
Nguyễn Duy Tuân
Nguyễn Duy Tuân
  • Dán lên cao
Sử dụng AI trong Excel với Add-in A-Tools AI v10 (2 người xem)
    • Thích
    • Cảm ơn
Trả lời
6
Đọc
790
Nguyễn Duy Tuân
Nguyễn Duy Tuân
H
Lỗi syntax error (1 người xem)
Trả lời
2
Đọc
101
ptm0412
ptm0412
GPE-Trợ Lý
ĐĂNG KÍ THAM DỰ SINH NHẬT GPE TẠI HN - SỰ KIỆN TALKSHOW ĐẶC BIỆT (2 người xem)
    • Thích
2
Trả lời
22
Đọc
1K
Nguyễn Khả Nam
N
H
Xin các bác giúp đỡ cách đổi thời gian thành số thập phân
Trả lời
8
Đọc
148
huyphanthieu
H
T
Cách cộng tổng chính xác nhất
Trả lời
4
Đọc
109
ngocbaovu
ngocbaovu
ongke0711
  • Question Question
Tải hóa đơn điện tử (https://hoadondientu.gdt.gov.vn/) Excel Vba (1 người xem)
    • Thích
    • Yêu thích
    • Cảm ơn
17 18 19
Trả lời
361
Đọc
40K
NGUYEN THOA 123
NGUYEN THOA 123
PhanTuHuong
Hà Nội hẹn hò gặp gỡ đầu năm 2025 thôi :)
    • Thích
    • Yêu thích
Trả lời
14
Đọc
1K
tungnguyen_kt
tungnguyen_kt
Maithy46
Giúp mình về việc lập bảng theo dõi chi tiết, báo cáo sản lượng công đoạn trong ngành may mặc ạ
Trả lời
4
Đọc
519
Maithy46
Maithy46
MrAnt
Dùng excel add-in lấy báo cáo từ SQL
Trả lời
5
Đọc
500
Nhattanktnn
Nhattanktnn
Bùi Thúy Thúy
Tắt thông báo "Sign in" khi mở file excel (1 người xem)
Trả lời
1
Đọc
110
vanle33
vanle33
nghiank09
Xin file Excel Danh sách các từ trong Tiếng Việt
    • Ngạc nhiên
Trả lời
14
Đọc
3K
hungtq1016
H
mrjun80
XIin giúp hàm tìm kiếm theo 1 đoạn số thứ tự của bảng (1 người xem)
    • Cười
2
Trả lời
20
Đọc
429
VetMini
V
Xem thêm…

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Sinh nhật lần 19 GPE

000
Ngày
00
Giờ
00
phút
00
giây
Thân mời tham dự sự kiện sinh nhật GPE 2025
Back
Top Bottom