macro countif dữ liệu hơn 700k dòng.

luckyboy1310 · 10/3/20

Em có 1 data hơn 700k dòng. em làm hàm countif thì chạy lâu quá. sử dụng macro tô màu dữ liệu trùng cũng lâu. Anh chi cho em hỏi co code nào xử lý data này nhanh hơn ko ạ.

Yêu cầu của em là VD vùng cần countif là $C$2:$C$700000, bên cột B2 sẽ la sô lượng trùng của dữ liệu đó. công thức ô B2 e xài là =Countif($C$2:$C$700000,C2), rồi copy công thức này xuống tới ô B2:B70000

VetMini · 10/3/20

gần cả triệu dòng mà còn bày đặt tô màu. File còn mở ra được là may mắn lắm rồi.

Nguyên tắc căn bản: đông con thì phải sắm nhà mới. Dữ liệu tiến tới 100 ngàn thì phải nghĩ đến những công cụ mới của Excel, điển hình là Data Model.
Làm việc ngày càng nhiều mà không chịu tự tiến thân, tự học hỏi thêm.

luckyboy1310 · 10/3/20

VetMini đã viết:
gần cả triệu dòng mà còn bày đặt tô màu. File còn mở ra được là may mắn lắm rồi.

Nguyên tắc căn bản: đông con thì phải sắm nhà mới. Dữ liệu tiến tới 100 ngàn thì phải nghĩ đến những công cụ mới của Excel, điển hình là Data Model.
Làm việc ngày càng nhiều mà không chịu tự tiến thân, tự học hỏi thêm.

ah. cái này em sửa chữa lại file nên buộc phải làm trên data gốc, chứ pivot hay data model thì em đâu cần phải tô màu chi cho mất công, vì cần xem xét lai mây dư liệu trùng đó nữa

VetMini · 10/3/20

Có con lừa già bắt nó kéo mãi cái xe ngày càng chất nặng.
Microsoft nó đã ra cái đầu máy cày để kéo mà không chịu dùng - lừoi học lái máy cày.

Chẳng lẽ ba bữa sau, cần làm thêm cái gì đó lại phải lên GPE: file 700k dòng [sic] nhờ viết giùm macro để...

luckyboy1310 · 10/3/20

VetMini đã viết:
Có con lừa già bắt nó kéo mãi cái xe ngày càng chất nặng.
Microsoft nó đã ra cái đầu máy cày để kéo mà không chịu dùng - lừoi học lái máy cày.

Chẳng lẽ ba bữa sau, cần làm thêm cái gì đó lại phải lên GPE: file 700k dòng [sic] nhờ viết giùm macro để...

cũng chăng biêt nói sao cho anh hiểu, chỉ đơn giản la phai loc ra những du liêu trung đó đẻ xem cai nào con xài dc, cái nao cân xóa đi.

batman1 · 10/3/20

Nếu thế thì may ra code làm được. Nhưng phải có tập tin thực gửi lên. Xóa dữ liệu nhạy cảm. Hoặc chỗ nào đang dùng đồ xuyên thấu thì thay bằng đồ kín đáo. Xóa dữ liệu chỉ để cột C.

bên cột B2 sẽ la sô lượng trùng

Có nghĩa là thế nào? vd. C1 là duy nhất thì B1 = trống. C2 = C200 = C345 = C1234 thì B2 = B200 = B345 = B1234 = 4? Và tô mầu cái gì? C2, C200, C345, C1234?
Phải có tập tin thực để còn thử tốc độ xem có cải thiện được nhiều hay không. Và nhập tay vài kết quả, và tô mầu vài chỗ cần tô mầu. Bằng nước bọt khó mà hiểu chính xác.

luckyboy1310 · 10/3/20

batman1 đã viết:
Nếu thế thì may ra code làm được. Nhưng phải có tập tin thực gửi lên. Xóa dữ liệu nhạy cảm. Hoặc chỗ nào đang dùng đồ xuyên thấu thì thay bằng đồ kín đáo. Xóa dữ liệu chỉ để cột C.

Có nghĩa là thế nào? vd. C1 là duy nhất thì B1 = trống. C2 = C200 = C345 = C1234 thì B2 = B200 = B345 = B1234 = 4? Và tô mầu cái gì? C2, C200, C345, C1234?
Phải có tập tin thực để còn thử tốc độ xem có cải thiện được nhiều hay không. Và nhập tay vài kết quả, và tô mầu vài chỗ cần tô mầu. Bằng nước bọt khó mà hiểu chính xác.

em chi càn macro cho hàm countif đó thôi ạ, dư liêu lớn qa hàm no chay ko nổi

phulien1902 · 10/3/20

luckyboy1310 đã viết:
em chi càn macro cho hàm countif đó thôi ạ, dư liêu lớn qa hàm no chay ko nổi

Bạn thử:

PHP:

Sub Test()
    Dim i&, LR&, Rng As Range
    Application.ScreenUpdating = 0
    LR = Cells(Rows.Count, "A").End(3).Row
    Set Rng = Range("A2:A" & LR)
    For i = 2 To LR
        Cells(i, "C").Value = WorksheetFunction.CountIf(Rng, Cells(i, "A"))
    Next i
    Application.ScreenUpdating = 1
End Sub

chisinhvnn · 10/3/20

phulien1902 đã viết:

Bạn thử:

PHP:

Sub Test()
    Dim i&, LR&, Rng As Range
    Application.ScreenUpdating = 0
    LR = Cells(Rows.Count, "A").End(3).Row
    Set Rng = Range("A2:A" & LR)
    For i = 2 To LR
        Cells(i, "C").Value = WorksheetFunction.CountIf(Rng, Cells(i, "A"))
    Next i
    Application.ScreenUpdating = 1
End Sub

bác cho cái màu vào nữa luôn

luckyboy1310 · 10/3/20

phulien1902 đã viết:

Bạn thử:

PHP:

Sub Test()
    Dim i&, LR&, Rng As Range
    Application.ScreenUpdating = 0
    LR = Cells(Rows.Count, "A").End(3).Row
    Set Rng = Range("A2:A" & LR)
    For i = 2 To LR
        Cells(i, "C").Value = WorksheetFunction.CountIf(Rng, Cells(i, "A"))
    Next i
    Application.ScreenUpdating = 1
End Sub

Treo luôn....kkkkkkk

HieuCD · 10/3/20

luckyboy1310 đã viết:
Treo luôn....kkkkkkk

Thử code

Mã:

Sub ABC()
  Dim i&, k&, iKey$, sRow&, sArr(), Res() As Long
 
  i = Cells(Rows.Count, "A").End(xlUp).Row
  If i < 2 Then Exit Sub
  Application.ScreenUpdating = False
  sArr = Range("A2:A" & i).Value
  sRow = UBound(sArr)
  ReDim Res(1 To sRow, 1 To 1)
  With CreateObject("scripting.dictionary")
    For i = 1 To sRow
      iKey = sArr(i, 1)
      If .exists(iKey) = False Then
        .Add iKey, 1
      Else
        .Item(iKey) = .Item(iKey) + 1
      End If
    Next i
    For i = 1 To sRow
      iKey = sArr(i, 1)
      Res(i, 1) = .Item(iKey)
    Next i
  End With
  Range("C2").Resize(sRow) = Res
  Application.ScreenUpdating = True
End Sub

Phuocam · 10/3/20

Haha, hiểu lầm .....

HieuCD · 10/3/20

phuocam đã viết:
2 for dài quá, 1 for thôi, Z...ọ...t _ l...ẹ

1 for sợ chạy quá chậm nên phải dùng 2 for

VetMini · 10/3/20

Để đối phó với dữ liệu hằng trăm ngàn dòng, Microsoft đã dùng giải pháp Data Model, đưa dữ liệu vào bộ xử lý của SQL Server Express.
Nếu dữ liệu này nằm trong Data Model thì một vài câu query là ra kết quả.

Tương tự vậy, nếu muốn dùng ADO thì code cũng không khó.

Nhưng nếu tôi xét trùng thì tôi sort bảng, và dùng hàm so sánh IF($A2=$A1, "X")
Nhìn chỗ có hai ba X liên tiếp thì biết nó trùng.

ppc0312 · 10/3/20

VetMini đã viết:
Để đối phó với dữ liệu hằng trăm ngàn dòng, Microsoft đã dùng giải pháp Data Model, đưa dữ liệu vào bộ xử lý của SQL Server Express.
Nếu dữ liệu này nằm trong Data Model thì một vài câu query là ra kết quả.

Tương tự vậy, nếu muốn dùng ADO thì code cũng không khó.

Nhưng nếu tôi xét trùng thì tôi sort bảng, và dùng hàm so sánh IF($A2=$A1, "X")
Nhìn chỗ có hai ba X liên tiếp thì biết nó trùng.

Một X cũng là có trùng rồi, mọi thứ thật đơn giản mà sao lên GPE cứ thấy phức tạp rồi VBA, mảng dic lên vậy(?)

batman1 · 10/3/20

Với dữ liệu kiểu này

09.32008425
0903.350603
09696.07464
037575.3164
0102660909

nếu có thêm vd.

09.03350603
09033.50603
090335.0603
01.02660909
0102.660909
01026.60909
010266.0909

thì theo tôi phải là có trùng. Nhưng COUNTIF thì bó tay. IF(...) cũng bó tay.

huyyeu99 · 10/3/20

batman1 đã viết:
Với dữ liệu kiểu này

09.32008425
0903.350603
09696.07464
037575.3164
0102660909

nếu có thêm vd.

09.03350603
09033.50603
090335.0603
01.02660909
0102.660909
01026.60909
010266.0909

thì theo tôi phải là có trùng. Nhưng COUNTIF thì bó tay. IF(...) cũng bó tay.

cái này replace all dấu chấm đi rồi tìm anh

batman1 · 10/3/20

huyyeu99 đã viết:
cái này replace all dấu chấm đi rồi tìm anh

Tôi có ngu đến thế đâu? Tôi chỉ nói là người ta dùng COUNTIF khi mà dữ liệu của người ta chưa bỏ hết các ký tự khác chữ số. Ít nhất là trong tập tin đính kèm họ chưa bỏ. Mà biết đâu người ta chưa nghĩ tới chuyện đó, chưa lường tới điều *** này, nên tôi lưu ý thôi. Mà ngoài dấu chấm biết đâu trong 700 000 dòng kia còn có "-".

***: nhìn tập tin đính kèm có COUNTIF và dữ liệu chứa dấu chấm thì tôi tin là trong tập tin thực cũng đã có COUNTIF và dấu chấm. Điều đó chứng tỏ chủ thớt không ý thức được điều tôi lưu ý. Nếu chưa ý thức được vấn đề mà dán code của bạn HieuCD thì chạy sẽ ra kết quả sai. Vì thế mà tôi ý thức cho người ta. Thế thôi.

VetMini · 10/3/20

ppc0312 đã viết:
Một X cũng là có trùng rồi, mọi thứ thật đơn giản mà sao lên GPE cứ thấy phức tạp rồi VBA, mảng dic lên vậy(?)

Tôi quên, hồi xưa công thức của tôi là B2=IF(A2=A1, "x", IF(A2=A3,"X", "")). X luôn luôn sẽ là cụm 2 trở lên.
(thực ra loại bài toán này ngày xưa, trước khi có Excel 2013 thì tôi vẫn thường làm)

batman1 đã viết:
...thì theo tôi phải là có trùng. Nhưng COUNTIF thì bó tay. IF(...) cũng bó tay.

COUNTIF phải đếm cả mảng. IF chỉ phải so sánh 1 vài ô.
Trên cái macbook của tôi:
Dựng 800000 dòng dữ liệu (số random) mất 12 giây.
Sort: vì chỉ một cột cho nên thoáng cái (nếu nhiều cột thì lâu hơn)
Hàm =IF(A2=A1, "x", IF(A2=A3,"X", "")) fill xuống, 36 giây.
Tôi nhớ là nếu dữ liệu chuỗi thì lâu hơn. Nhưng không đến nổi lâu lắm.

batman1 · 10/3/20

VetMini đã viết:
COUNTIF phải đếm cả mảng. IF chỉ phải so sánh 1 vài ô.
Trên cái macbook của tôi:
Dựng 800000 dòng dữ liệu (số random) mất 12 giây.
Sort: vì chỉ một cột cho nên thoáng cái (nếu nhiều cột thì lâu hơn)
Hàm =IF(A2=A1, "x", IF(A2=A3,"X", "")) fill xuống, 36 giây.
Tôi nhớ là nếu dữ liệu chuỗi thì lâu hơn. Nhưng không đến nổi lâu lắm.

Tôi không bàn về nhanh chậm. Tôi chỉ muốn ý thức cho người ta về vấn đề. Nếu người ta chưa ý thức được vấn đề mà dán code của bác vào thì sẽ có kết quả sai.

macro countif dữ liệu hơn 700k dòng.

Thành viên mới

Ăn cùng góc phố

Thành viên mới

Ăn cùng góc phố

Thành viên mới

Thành viên gạo cội

Thành viên mới

File đính kèm

GPE - My love

Thành viên tiêu biểu

Thành viên mới

Chuyên gia GPE

Thành viên mới

Chuyên gia GPE

Ăn cùng góc phố

whom?

Thành viên gạo cội

Thành viên chính thức

Thành viên gạo cội

Ăn cùng góc phố

Thành viên gạo cội

Đếm ngược thời gian