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

tranhungdao12a3

Thành viên mới
Tham gia ngày
29 Tháng mười một 2007
Bài viết
19
Được thích
8
Điểm
665
Tuổi
31
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

HieuCD

Chuyên gia GPE
Tham gia ngày
14 Tháng chín 2010
Bài viết
6,771
Được thích
13,004
Điểm
1,560
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
 

tranhungdao12a3

Thành viên mới
Tham gia ngày
29 Tháng mười một 2007
Bài viết
19
Được thích
8
Điểm
665
Tuổi
31
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?
 

tranhungdao12a3

Thành viên mới
Tham gia ngày
29 Tháng mười một 2007
Bài viết
19
Được thích
8
Điểm
665
Tuổi
31
Công thức cho nhiều ô sẽ làm chậm Excel, ít ô thì được
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ẻ!
 

Hau151978

Thành viên tích cực
Tham gia ngày
19 Tháng mười 2011
Bài viết
1,346
Được thích
1,235
Điểm
560
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

tranhungdao12a3

Thành viên mới
Tham gia ngày
29 Tháng mười một 2007
Bài viết
19
Được thích
8
Điểm
665
Tuổi
31
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
 

Hau151978

Thành viên tích cực
Tham gia ngày
19 Tháng mười 2011
Bài viết
1,346
Được thích
1,235
Điểm
560
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.
 

HieuCD

Chuyên gia GPE
Tham gia ngày
14 Tháng chín 2010
Bài viết
6,771
Được thích
13,004
Điểm
1,560
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

tranhungdao12a3

Thành viên mới
Tham gia ngày
29 Tháng mười một 2007
Bài viết
19
Được thích
8
Điểm
665
Tuổi
31
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:

VetMini

Chuyên gia GPE
Tham gia ngày
21 Tháng mười hai 2012
Bài viết
8,925
Được thích
10,460
Điểm
1,560
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:

tranhungdao12a3

Thành viên mới
Tham gia ngày
29 Tháng mười một 2007
Bài viết
19
Được thích
8
Điểm
665
Tuổi
31
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!
 
Top Bottom