Hỏi code nào chạy hàm max nhanh hơn ko?

[qtm1987]

Trong excel mình gõ vào ô A2 = max($A$1:A1) + 1 {đây là cột số thứ tự 1 2 3 4 5 ....n số} rồi kéo công thức này cho các ô bên dưới. Nếu khoản chục hàng thì công thức xử lý nhanh nhưng nếu khoản 60.000 dòng thì thấy tốc độ xử lý công thức này rất chậm làm máy treo lun . Cho mình hỏi có code nào trong VBA thực hiện được công việc giống công thức này ko và tốc độ khi xử lý số dòng lớn như vậy có nhanh được không? Mong sự giúp đỡ của các bạn trong diễn đàn.

 

[nghiaphuc]

Trong excel mình gõ vào ô A2 = max($A$1:A1) + 1 {đây là cột số thứ tự 1 2 3 4 5 ....n số} rồi kéo công thức này cho các ô bên dưới. Nếu khoản chục hàng thì công thức xử lý nhanh nhưng nếu khoản 60.000 dòng thì thấy tốc độ xử lý công thức này rất chậm làm máy treo lun . Cho mình hỏi có code nào trong VBA thực hiện được công việc giống công thức này ko và tốc độ khi xử lý số dòng lớn như vậy có nhanh được không? Mong sự giúp đỡ của các bạn trong diễn đàn.

Nếu danh sách liên tục thì thay vì hàm MAX, bạn có thể sử dụng hàm ROW, chẳng hạn: A2=ROW(1:1) --> fill xuống.

 

[qtm1987]

hix. file của mình thì danh sách ko liên tục, hok sài được hàm ROW. Chủ yếu công thức nói trên mình kết hợp với hàm IF để đếm số thứ tự tại những ô của Cột B, ô nào có dữ liệu thì số thứ tự là 1 rồi tiếp theo ô nào ko có dữ liệu thì bỏ trống ko đếm, cứ thế mà đếm lên 2, 3, 4.... n số .v.v...

 

[ndu96081631]

hix. file của mình thì danh sách ko liên tục, hok sài được hàm ROW. Chủ yếu công thức nói trên mình kết hợp với hàm IF để đếm số thứ tự tại những ô của Cột B, ô nào có dữ liệu thì số thứ tự là 1 rồi tiếp theo ô nào ko có dữ liệu thì bỏ trống ko đếm, cứ thế mà đếm lên 2, 3, 4.... n số .v.v...

Nếu là 60,000 dòng dữ liệu thì chẳng có công thức nào "nhanh" được đâu!
- Một là bạn gõ công thức, xong copy và paste values vào dữ liệu
- Hai là dùng code VBA

 

[Quang_Hải]

Bạn xài cái này khá nhanh nè, khoảng 1/4 giây, nếu là dữ liệu liên tục

Sub STT()
[A2:A65536] = [row(A:A)]
End Sub

 

[ndu96081631]

Bạn xài cái này khá nhanh nè, khoảng 1/4 giây, nếu là dữ liệu liên tục

Sub STT()
[A2:A65536] = [row(A:A)]
End Sub

Người ta đã nói không dùng được ROW rồi cơ mà
Ngoài ra, do bạn test trên 1 bảng tính trắng nên thấy nhanh ---> Nếu trong 1 file đang chưa nhiều công thức thì.. code này vẫn chưa nhanh đâu ---> Code cho tốc độ nhanh nhất là dùng mảng
------------
Cũng định làm nhưng chẳng thấy tác giả cho file lên nên chẳng biết giúp thế nào

 

[Quang_Hải]

Oh code của mình không áp dụng cho bạn được rồi.

 

[qtm1987]

Cũng định làm nhưng chẳng thấy tác giả cho file lên nên chẳng biết giúp thế nào

Em bổ sung file đính kèm rồi. Anh giúp em code cho cột số thứ tự (Cột tô màu hồng) anh nhé. file này e lọc ra có mấy chục dòng thôi, nhưng vì số chứng từ thu chi thực tế nhiều lắm nên hàm max trong cột STT em đặt khi làm nhiều dòng nó bị treo máy.

 

[ndu96081631]

Em bổ sung file đính kèm rồi. Anh giúp em code cho cột số thứ tự (Cột tô màu hồng) anh nhé. file này e lọc ra có mấy chục dòng thôi, nhưng vì số chứng từ thu chi thực tế nhiều lắm nên hàm max trong cột STT em đặt khi làm nhiều dòng nó bị treo máy.

Thử code này vào file của bạn xem (Viết theo thuật toán trong công thức của bạn)

Sub STT()
  Dim i As Long, n As Long, tmp1 As String, tmp2 As String, sArray, Arr()
  With Sheets("Phat sinh")
    sArray = .Range(.[P6], .[P65536].End(xlUp))
    tmp1 = CStr(.Range("P5").Value)
    ReDim Arr(1 To UBound(sArray, 1), 1 To 1)
    For i = 1 To UBound(sArray, 1)
      If CStr(sArray(i, 1)) <> "" Then
        tmp2 = CStr(sArray(i, 1))
        If tmp2 <> tmp1 Then
          n = n + 1
          Arr(i, 1) = n
        End If
      End If
      tmp1 = CStr(sArray(i, 1))
    Next
    If n Then .Range("A6").Resize(i - 1).Value = Arr
  End With
End Sub

 

[nmhung49]

Em bổ sung file đính kèm rồi. Anh giúp em code cho cột số thứ tự (Cột tô màu hồng) anh nhé. file này e lọc ra có mấy chục dòng thôi, nhưng vì số chứng từ thu chi thực tế nhiều lắm nên hàm max trong cột STT em đặt khi làm nhiều dòng nó bị treo máy.

Bạn thử code này xem

Sub STT()
Dim rngHD As Variant, kqstt() As Variant, irow As Long, i As Long
With Sheets("Phat sinh")
rngHD = .Range("P5:P" & .Range("p65000").End(xlUp).Row).Value
ReDim kqstt(1 To UBound(rngHD, 1), 1 To 1)

    For irow = 1 To UBound(rngHD, 1) - 1

        If rngHD(irow, 1) <> rngHD(irow + 1, 1) Then
            i = i + 1
            kqstt(irow, 1) = i
        End If
    
    Next irow

.Range("A6").Resize(irow).Value = kqstt
End With
End Sub

 

[Quang_Hải]

Mình góp vui code này. Code cua anh NDU chạy nhanh hơn nhưng cao cấp quá

 

[qtm1987]

Thử code này vào file của bạn xem (Viết theo thuật toán trong công thức của bạn)

Tuyệt vời , 1 click là ra ngay kết quả cho 65536 hàng. Chân thành cám ơn sự giúp đỡ của ndu960818631. Đoạn code này giải thuật là thế nào vậy anh, em đang làm quen với VBA nên muốn học hỏi thêm.

 

[qtm1987]

Mình góp vui code này. Code cua anh NDU chạy nhanh hơn nhưng cao cấp quá

Anh quanghai1969 ơi, sao em test thử code của anh em tăng lên nhìu hàng nữa thì code của anh chạy tới khoảng stt 51 là ko chạy típ nữa vậy anh. Code của anh cũng hay thiệt. Cám ơn sự giúp đỡ của anh.

 

[app]

Tại dòng bắt đầu chạy công thức (row 6), bạn có :

=IF(AND($P6>0)*($P6<>$P5),MAX($A$4:$A5)+1,"")

Bạn chỉ cần sửa lại thành:

=IF(AND(P6>0)*(P6<>P5),MAX(A1:A5)+1,"")

MAX($A$4:$A5) thành MAX(A1:A5)

là có thể đáp ứng với mục tiêu của bạn.

Trường hợp với 1 số thứ tự ra nhiều hơn 4 kết quả liên quan thì bạn có thể chèn thêm dòng trống vào ở "Row 1". Chẳng hạn ước lượng tối đa ra 10 kết quả liên quan bạn thêm vào 6 dòng, tối đa 20 kết quả thì thêm 16 dòng. Bạn ẩn các dòng trống này đi cho đẹp mắt .

Hy vọng đúng như ý bạn.

 

[ndu96081631]

Tuyệt vời , 1 click là ra ngay kết quả cho 65536 hàng. Chân thành cám ơn sự giúp đỡ của ndu960818631. Đoạn code này giải thuật là thế nào vậy anh, em đang làm quen với VBA nên muốn học hỏi thêm.

Vẫn là thuật toán y chang như trong công thức của bạn thôi, tức giá trị tại cột P khác rổng đồng thời "thằng em" phía dưới khác phía trên thì điền STT... vậy thôi
Vấn đề là tôi dùng phương pháp xử lý mảng, toàn bộ xử lý theo kiểu "ảo", xong việc ta gán 1 lần vào bảng tính (chứ không xử lý từng cell)... Vì lẽ đó mà tốc độ rất nhanh
Bạn mới học VBA thì không cần phải vội, cứ từ từ với những điều đơn giản nhất ---> Rồi cũng sẽ hiểu mọi thứ thôi!

 

[Quang_Hải]

Anh quanghai1969 ơi, sao em test thử code của anh em tăng lên nhìu hàng nữa thì code của anh chạy tới khoảng stt 51 là ko chạy típ nữa vậy anh. Code của anh cũng hay thiệt. Cám ơn sự giúp đỡ của anh.

Mình cũng chèn thêm dòng và test thử thấy bình thường mà

 

[qtm1987]

Tại dòng bắt đầu chạy công thức (row 6), bạn có :

=IF(AND($P6>0)*($P6<>$P5),MAX($A$4:$A5)+1,"")

Bạn chỉ cần sửa lại thành:

=IF(AND(P6>0)*(P6<>P5),MAX(A1:A5)+1,"")

MAX($A$4:$A5) thành MAX(A1:A5)

là có thể đáp ứng với mục tiêu của bạn.

uh. mình hiểu rồi, với cách của App là giới hạn lại vùng cho hàm MAX thì tốc độ xử lý mau hơn. Cám ơn bạn nhé.

 

[app]

Để giúp bạn hiệu rõ hiệu quả của phương pháp rút gọn, mình sẽ đưa ra giải thích sau đây:


Tại phương pháp ban đầu của với 60.000 dòng thì khi sử dụng hàm MAX bạn sẽ có tổng cộng: 2+3+4+...+59.999= 59.999x60.000:2-1= 1.799.969.999 ≈ 1.800.000.000 phép so sánh. Đây là lý do khiến phép tính của bạn làm treo máy.


Với phương pháp rút gọn với hàm MAX bạn sẽ có: 10x60.000=600.000 phép so sánh. Ít hơn ...3.000 lần.


Còn với phương án có dùng tới lệnh redim, với mỗi lần ra lệnh redim máy tính sẽ phải thực hiện việc sau:
1. Cấp phát bộ nhớ cho mảng mới. (xác định vùng nhớ có thể đủ sử dụng)
2. Giải phóng bộ nhớ dành cho mảng ban đầu.
Và bạn phải làm thêm một số công việc như gán dữ liệu cho từng ô nhớ, so sánh...
Cho nên lệnh redim không được khuyến khích sử dụng trong giới lập trình. Trong khi đó với hàm MAX, công việc của máy tính đơn giản là so sánh từng ô nhớ với 1 ô nhớ trung gian để tìm ra giá trị lớn nhất.

Tuy nhiên đây chỉ là lý thuyết, thực nghiệm mới cho bạn sự lựa chọn chính xác và phù hợp.

Thân chào.

 

[ndu96081631]

Tại dòng bắt đầu chạy công thức (row 6), bạn có :

=IF(AND($P6>0)*($P6<>$P5),MAX($A$4:$A5)+1,"")

Bạn chỉ cần sửa lại thành:

=IF(AND(P6>0)*(P6<>P5),MAX(A1:A5)+1,"")

MAX($A$4:$A5) thành MAX(A1:A5)

là có thể đáp ứng với mục tiêu của bạn.

Trường hợp với 1 số thứ tự ra nhiều hơn 4 kết quả liên quan thì bạn có thể chèn thêm dòng trống vào ở "Row 1". Chẳng hạn ước lượng tối đa ra 10 kết quả liên quan bạn thêm vào 6 dòng, tối đa 20 kết quả thì thêm 16 dòng. Bạn ẩn các dòng trống này đi cho đẹp mắt .

Hy vọng đúng như ý bạn.

Để giúp bạn hiệu rõ hiệu quả của phương pháp rút gọn, mình sẽ đưa ra giải thích sau đây:

Tại phương pháp ban đầu của với 60.000 dòng thì khi sử dụng hàm MAX bạn sẽ có tổng cộng: 2+3+4+...+59.999= 59.999x60.000:2-1= 1.799.969.999 ≈ 1.800.000.000 phép so sánh. Đây là lý do khiến phép tính của bạn làm treo máy.
Với phương pháp rút gọn với hàm MAX bạn sẽ có: 10x60.000=600.000 phép so sánh. Ít hơn ...3.000 lần.

Nghe thì có vẽ rất có lý, thế nhưng không biết bạn đã thí nghiệm chắc chưa?
Hãy thử trong trường hợp ở cột P có 6 cell liên tiếp trùng nhau
Nói chung, muốn tính MAX thì phải so sánh "từ đầu đến chân"

 

[app]

Như mình đã nói:

Tuy nhiên đây chỉ là lý thuyết, thực nghiệm mới cho bạn sự lựa chọn chính xác và phù hợp.

Trường hợp với 1 số thứ tự ra nhiều hơn 4 kết quả liên quan thì bạn có thể chèn thêm dòng trống vào ở "Row 1". Chẳng hạn ước lượng tối đa ra 10 kết quả liên quan bạn thêm vào 6 dòng, tối đa 20 kết quả thì thêm 16 dòng. Bạn ẩn các dòng trống này đi cho đẹp mắt .

.
Mình thiếu sót khi quên nói cách hiệu chỉnh công thức tương ứng theo số dòng bổ sung. Tuy nhiên đây là công thức rút gọn ra của bạn qtm1987 nên bạn qtm1987 chắc chắn đã hiểu rõ để có thể tự giải quyết.

Một số ý mình dẫn từ lý thuyết về CTDL và HĐH nên có vẻ không ăn nhập lắm với Excel hay VBA.

Thân chào.