Nhờ đếm giá trị dương không trùng theo nhiều điều kiện với dữ liệu lớn

Liên hệ QC

tranhungdao12a3

Thành viên thường trực
Tham gia
29/11/07
Bài viết
358
Được thích
325
Chào các anh/chị/em GPE!
Em có việc xin nhờ các mọi người giúp đỡ!
Em muốn đếm các giá trị điền vào các ô F2 đến J2 với các điều kiện:
-Đếm giá không trùng ở cột C sheet"Data" với điều kiện giá trị cột E sheet"Data" >0, D3 và E3 lấy từ cột A,B bên sheet"Data", F2:V2 Lấy từ cột D sheet"Data"​
-Có công thức nào nhanh hoặc VBA nhanh không ah? Em dùng Sumproduct nhưng nó quá nặng vì mấy trăm ngàn dòng chạy không nổi.​
-Hỗ trợ em dùng công thức hoặc VBA, không dùng Pivot​
Em muốn dùng công thức hoặc VBA, em đặt công thức đếm bằng sumproduct nhưng vì dữ liệu nó quá lớn nên công thức không thể chạy được, em biết cách đếm nó bằng Pivot distinct count nhưng không muốn dùng cách này.
Anh chị có thể viết công thức chạy nhanh hoặc VBA dùm em với!
Em cảm ơn nhiều!

1585712701913.png
 

File đính kèm

  • Đếm giá trị dương không trùng dữ liệu lớn.xlsb
    3.2 MB · Đọc: 18
Chào các anh/chị/em GPE!
Em có việc xin nhờ các mọi người giúp đỡ!
Em muốn đếm các giá trị điền vào các ô F2 đến J2 với các điều kiện:
-Đếm giá không trùng ở cột C sheet"Data" với điều kiện giá trị cột E sheet"Data" >0, D3 và E3 lấy từ cột A,B bên sheet"Data", F2:V2 Lấy từ cột D sheet"Data"​
-Có công thức nào nhanh hoặc VBA nhanh không ah? Em dùng Sumproduct nhưng nó quá nặng vì mấy trăm ngàn dòng chạy không nổi.​
-Hỗ trợ em dùng công thức hoặc VBA, không dùng Pivot​
Em muốn dùng công thức hoặc VBA, em đặt công thức đếm bằng sumproduct nhưng vì dữ liệu nó quá lớn nên công thức không thể chạy được, em biết cách đếm nó bằng Pivot distinct count nhưng không muốn dùng cách này.
Anh chị có thể viết công thức chạy nhanh hoặc VBA dùm em với!
Em cảm ơn nhiều!

View attachment 234465
Dùng Sub VBA chạy toàn bộ dữ liệu
Mã:
Sub DemKhongTung()
  Dim sArr(), tArr(), Res(), Dic As Object
  Dim eRow&, sRow&, sCol&, i&, iR&, jC&, iKey$
 
  With Sheet2 'Sheet ket qua
    eRow = .Range("D" & Rows.Count).End(xlUp).Row
    If eRow > 2 Then .Range("A3:J" & eRow).ClearContents
    tArr = .Range("D2:J2").Value
  End With
  sCol = UBound(tArr, 2)
  Set Dic = CreateObject("scripting.dictionary")
  For j = 3 To sCol
    Dic.Add tArr(1, j), j
  Next j
 
  With Sheet1 'Sheet Data
    eRow = .Range("A" & Rows.Count).End(xlUp).Row
    If eRow < 2 Then MsgBox ("Khong co du lieu"): Exit Sub
    sArr = .Range("A2:E" & eRow).Value
  End With
  sRow = UBound(sArr)
  ReDim Res(1 To sRow, 1 To sCol)
 
  For i = 1 To sRow
    If sArr(i, 5) > 0 Then
      iKey = sArr(i, 1)
      If Dic.exists(iKey) = False Then
        k = k + 1
        Res(k, 1) = iKey: Res(k, 2) = sArr(i, 2)
        Dic.Add iKey, k
      End If
      iR = Dic.Item(iKey)
      
      iKey = iKey & sArr(i, 3) & sArr(i, 4)
      If Dic.exists(iKey) = False Then
        jC = Dic.Item(sArr(i, 4))
        If jC > 0 Then Res(iR, jC) = Res(iR, jC) + 1
        Dic.Add iKey, ""
      End If
    End If
  Next i
 
  With Sheet2 'Sheet ket qua
    Range("D3:J3").Resize(k) = Res
  End With
End Sub
 
Dùng Sub VBA chạy toàn bộ dữ liệu
Mã:
Sub DemKhongTung()
  Dim sArr(), tArr(), Res(), Dic As Object
  Dim eRow&, sRow&, sCol&, i&, iR&, jC&, iKey$

  With Sheet2 'Sheet ket qua
    eRow = .Range("D" & Rows.Count).End(xlUp).Row
    If eRow > 2 Then .Range("A3:J" & eRow).ClearContents
    tArr = .Range("D2:J2").Value
  End With
  sCol = UBound(tArr, 2)
  Set Dic = CreateObject("scripting.dictionary")
  For j = 3 To sCol
    Dic.Add tArr(1, j), j
  Next j

  With Sheet1 'Sheet Data
    eRow = .Range("A" & Rows.Count).End(xlUp).Row
    If eRow < 2 Then MsgBox ("Khong co du lieu"): Exit Sub
    sArr = .Range("A2:E" & eRow).Value
  End With
  sRow = UBound(sArr)
  ReDim Res(1 To sRow, 1 To sCol)

  For i = 1 To sRow
    If sArr(i, 5) > 0 Then
      iKey = sArr(i, 1)
      If Dic.exists(iKey) = False Then
        k = k + 1
        Res(k, 1) = iKey: Res(k, 2) = sArr(i, 2)
        Dic.Add iKey, k
      End If
      iR = Dic.Item(iKey)
     
      iKey = iKey & sArr(i, 3) & sArr(i, 4)
      If Dic.exists(iKey) = False Then
        jC = Dic.Item(sArr(i, 4))
        If jC > 0 Then Res(iR, jC) = Res(iR, jC) + 1
        Dic.Add iKey, ""
      End If
    End If
  Next i

  With Sheet2 'Sheet ket qua
    Range("D3:J3").Resize(k) = Res
  End With
End Sub
Em cảm ơn anh HieuCD rất nhiều! Giá trị chạy ra đúng ý em rồi ah!
Có cách nào để làm bằng công thức mà nó chạy được không anh?
 
Vâng do dữ liệu của em nếu đến cuối tháng bình quân nó khoảng 500000 dòng đến 1000000 dòng nên em cũng nghĩ khả năng không công thức nào chịu nổi.
Cảm ơn anh đã chia sẻ!
Bạn chỉ đếm không trùng đối với 1 mã EMPID hay toàn bộ mã, nếu chỉ 1 mã thì dùng các bảng phụ cũng khá nhanh. Các công thức mình sử dụng của office 365, giá không đắt nếu bạn thường xuyên làm với dữ liệu lớn.
R3=FILTER(Data!A1:E299449,('Đếm giá trị dương không trùng'!D3=Data!A1:A299449)*(Data!E1:E299449>0))
W3=UNIQUE(R3#)
AB3=OFFSET(W3#,,,,1)
Kết quả K3=COUNTA(AB3#)
 

File đính kèm

  • Đếm giá trị dương không trùng dữ liệu lớn.xlsb
    3.2 MB · Đọc: 4
Bạn chỉ đếm không trùng đối với 1 mã EMPID hay toàn bộ mã, nếu chỉ 1 mã thì dùng các bảng phụ cũng khá nhanh. Các công thức mình sử dụng của office 365, giá không đắt nếu bạn thường xuyên làm với dữ liệu lớn.
R3=FILTER(Data!A1:E299449,('Đếm giá trị dương không trùng'!D3=Data!A1:A299449)*(Data!E1:E299449>0))
W3=UNIQUE(R3#)
AB3=OFFSET(W3#,,,,1)
Kết quả K3=COUNTA(AB3#)
Em có mở file của anh nhưng em không hiểu lắm, chắc nó áp dụng trên office 365!
Em muốn đếm không trùng cột C bên sheet Data với các điều kiện: theo EMPID cột A, EMPNM cột B, nhãn hàng Brand cột D, giá trị cột E>0.
Bình thường em dùng Pivot cũng khá nhanh, tuy nhiên em muốn cho nhân viên bán hàng xem và sử dụng thì em không thể để Pivot được, các bạn ấy không biết cái đó, cái nào càng đơn giản thì các bạn ý mới dễ hiểu.
Anh có thể chia sẻ thêm chút về cái anh đang nói không ah!
Cảm ơn anh!

1585730024677.png
 
Em có mở file của anh nhưng em không hiểu lắm, chắc nó áp dụng trên office 365!
Em muốn đếm không trùng cột C bên sheet Data với các điều kiện: theo EMPID cột A, EMPNM cột B, nhãn hàng Brand cột D, giá trị cột E>0.
Bình thường em dùng Pivot cũng khá nhanh, tuy nhiên em muốn cho nhân viên bán hàng xem và sử dụng thì em không thể để Pivot được, các bạn ấy không biết cái đó, cái nào càng đơn giản thì các bạn ý mới dễ hiểu.
Các công thức UNIQUE, FILTER mới được bổ sung vào excel 365 hoặc có thể 2019. Các bản này nếu không mua bản quyền thì có thể active dễ dàng mà bạn. Bản home giá khoảng 900 nghìn/năm dùng được cho 6 tài khoản. Nếu không thì dùng filter cũng ra nhưng không tự động được.
 
Em cảm ơn anh HieuCD rất nhiều! Giá trị chạy ra đúng ý em rồi ah!
Có cách nào để làm bằng công thức mà nó chạy được không anh?
Dùng hàm tự tạo, nhập công thức ô đầu tiên chạy hơi lâu, các ô sau sẽ chạy khá nhanh
Cấu trúc dữ liệu sheet Data phải theo thứ tự cột
Mã:
Dim rngAddRess$, Dic As Object

Function CountIfS_Uni(ByVal Rng As Range, ByVal Empid$, ByVal Brand$)
  If Rng.Address(0, 0) <> rngAddRess Then
    Call CreateDic(Rng.Value)
    rngAddRess = Rng.Address(0, 0)
  End If
  CountIfS_Uni = Dic.Item(Empid & Brand)
End Function

Sub CreateDic(ByVal sArr As Variant)
  Dim sRow&, i&, iKey$, iKey2$
  Set Dic = CreateObject("scripting.dictionary")
  sRow = UBound(sArr)
  For i = 1 To sRow
    If sArr(i, 5) > 0 Then
      iKey = sArr(i, 1) & sArr(i, 4)
      iKey2 = iKey & sArr(i, 3)
      If Dic.exists(iKey2) = False Then
        Dic.Add iKey2, ""
        Dic.Item(iKey) = Dic.Item(iKey) + 1
      End If
    End If
  Next i
End Sub
 

File đính kèm

  • Đếm giá trị dương không trùng dữ liệu lớn.xlsb
    3.1 MB · Đọc: 7
Các công thức UNIQUE, FILTER mới được bổ sung vào excel 365 hoặc có thể 2019. Các bản này nếu không mua bản quyền thì có thể active dễ dàng mà bạn. Bản home giá khoảng 900 nghìn/năm dùng được cho 6 tài khoản. Nếu không thì dùng filter cũng ra nhưng không tự động được.
Cảm ơn anh, em sẽ tìm hiểu những kiến thức anh đã chia sẻ!
Bài đã được tự động gộp:

Dùng hàm tự tạo, nhập công thức ô đầu tiên chạy hơi lâu, các ô sau sẽ chạy khá nhanh
Cấu trúc dữ liệu sheet Data phải theo thứ tự cột
Mã:
Dim rngAddRess$, Dic As Object

Function CountIfS_Uni(ByVal Rng As Range, ByVal Empid$, ByVal Brand$)
  If Rng.Address(0, 0) <> rngAddRess Then
    Call CreateDic(Rng.Value)
    rngAddRess = Rng.Address(0, 0)
  End If
  CountIfS_Uni = Dic.Item(Empid & Brand)
End Function

Sub CreateDic(ByVal sArr As Variant)
  Dim sRow&, i&, iKey$, iKey2$
  Set Dic = CreateObject("scripting.dictionary")
  sRow = UBound(sArr)
  For i = 1 To sRow
    If sArr(i, 5) > 0 Then
      iKey = sArr(i, 1) & sArr(i, 4)
      iKey2 = iKey & sArr(i, 3)
      If Dic.exists(iKey2) = False Then
        Dic.Add iKey2, ""
        Dic.Item(iKey) = Dic.Item(iKey) + 1
      End If
    End If
  Next i
End Sub
Cảm ơn anh đã chia sẻ, cái này thiệt đúng ý em, em sẽ tuỳ biến để tạo các hàm theo mục đích của mình! Em nghĩ không chỉ riêng em, những bạn làm ngành FMCG mà không thạo VBA chắc chắn sẽ thấy hàm tự tạo này thiệt hữu ích.
 
Lần chỉnh sửa cuối:
Excel chính bản thân của nó sẽ bị áp lực khi dùng đến trên 60000 dòng. Kỹ thuật vector mapping các cell chỉ đến đó.
Để khắc phục điều này, MS bắt buộc phải nới rộng thêm các công cụ, như Table.
Nhưng để quản lý cỡ nửa triệu dòng trở lên thì ngày xưa nên dùng Access. Ngày nay MS đã đưa thêm công cụ Data Model để quản lý hiệu quả.

Kết luận: nếu làm việc thường xuyên với tầm cỡ 10000 dòng trở lên mà không biết Data Model thì là đi sau thời đại. Cần tu bổ kiến thức gấp, trễ còn hơn không.
 
Lần chỉnh sửa cuối:
Excel chính bản thân của nó sẽ bị áp lực khi dùng đến trên 60000 dòng. Kỹ thuật vector mapping các cell chỉ đến đó.
Để khắc phục điều này, MS bắt buộc phải nới rộng thêm các công cụ, như Table.
Nhưng để quản lý cỡ nửa triệu dòng trở lên thì ngày xưa nên dùng Access. Ngày nay MS đã đưa thêm công cụ Data Model để quản lý hiệu quả.

Kết luận: nếu làm việc thường xuyên với tầm cỡ 10000 dòng trở lên mà không biết Data Model thì là đi sau thời đại. Cần tu bổ kiến thức gấp, trễ còn hơn không.
Em cũng biết bản thân excel không giải quyết được dữ liệu lớn, bình thường nếu em nạp nguyên dữ liệu của một tháng của 8 vùng trên cả nước nó cũng lên tới 5 triệu dòng, đấy là chưa kể của các tháng khác. Cơ bản em vẫn dùng Power Bi để truy xuất báo cáo, tuy nhiên em chưa chuyên sâu lắm nên nhiều thứ vẫn chưa làm được. Chuyển qua excel thì cái món VBA em chỉ biết dùng hoặc sơ sơ thôi nên không thạo lắm.
Cảm ơn anh đã chia sẻ! Em sẽ đầu tư thời gian cho mấy cái quản lý dữ liệu!
 
Web KT
Back
Top Bottom