Hàm sắp xếp mảng ngang dọc với Thuật toán QuickSort (sắp xếp Tiếng Việt) cho Excel

Liên hệ QC

Hôm nay tôi lại chia sẻ cho các bạn các Hàm UDF được viết bằng VBA, nhằm mục đích để sắp xếp mảng một cách tối ưu nhất.


HÀM UDF SẮP XẾP PHÂN TẦNG (ĐA HÀNG CỘT)
  1. Hàm VSORT và Thủ tục VSORTING - Sắp xếp dọc
  2. Hàm HSORT và Thủ tục HSORTING - Sắp xếp ngang

Mục tiêu đạt được:
  1. Sắp xếp cả mảng ngang và mảng dọc.
  2. Sắp xếp phân tầng (nhiều hàng nhiều cột).
  3. Sắp xếp Tiếng Việt và Tên tiếng Việt.
  4. Giao diện tô màu mảng đẹp mắt.


HƯỚNG DẪN:
  • Hàm viết trong bảng tính:
    • =VSORT(Cells,Indexs,matchCase,color,DefaultFontColor,DefaultBackColor)
    • =HSORT(Cells,Indexs,matchCase,color,DefaultFontColor,DefaultBackColor)
  • Thủ tục viết trong mã để gọi từ nút ngoài bảng tính:
    • Call VSORTING(Cells,Indexs,matchCase,color,DefaultFontColor,DefaultBackColor)
    • Call HSORTING(Cells,Indexs,matchCase,color,DefaultFontColor,DefaultBackColor)
Cách gõ nhanh: =VSORT( và ấn tổ hợp phím tắt Ctrl+Shift+A

Vị tríTham sốKiểuChức năng
1​
CellsVùng cần tổngNhận vùng cần sắp xếp
2​
IndexsSố cột hoặc Mảng các cộtNếu nhập số âm thì sắp xếp giảm dần, bằng 0 sx tất cả, -99999 xs từ cột cuối
Nếu nhập mảng {3,1,2} thì sắp xếp phân tầng từ cột 3, đến 1, đến 2
Nếu nhập số lớn hơn 1000 hoặc nhỏ hơn -1000 thì sắp xếp tên tiếng Việt.
Nếu nhập mảng {1003,1,2} thì sắp xếp tên tiếng Việt cột 3
3​
MatchCaseCó/KhôngSắp xếp không phân biệt hoa thường
4​
colorSốMàu, Nếu đặt màu nhỏ hơn 0 thì không tô màu, và sẽ sắp xếp nhanh hơn
5​
DefaultFontColorSốMàu phông chữ mặc định
6​
DefaultBackColorSốMàu nền mặc đinh


Ví dụ viết hàm vào bảng tính (sắp xếp dọc):
1. Sắp xếp mảng A2:Z10000 với các phân tầng là tất cả cột, lớn dần
=VSORT(A2:Z10000, 0, FALSE)
2. Sắp xếp mảng A2:Z10000 với cột thứ 3, lớn dần gõ 3, nhỏ dần gõ -3
=VSORT(A2:Z10000, 3, FALSE)
3. Sắp xếp mảng A2:Z10000 với các phân tầng là 1, 3, 5, 7 (thứ tự cột), lớn dần
=VSORT(A2:Z10000, {1, 3, 5, 7}, FALSE)
4. Sắp xếp mảng A2:Z10000 với các phân tầng là 8, 3, 5, 4 (thứ tự cột), lớn dần
=VSORT(A2:Z10000, {8, 3, 5, 4}, FALSE)
5. Sắp xếp mảng A2:Z10000 với các phân tầng là -8, -3, -5, -4 (thứ tự cột), giảm dần
=VSORT(A2:Z10000, {-8, -3, -5, -4}, FALSE)
***Nếu dấu phân cách tham số là dấu ; thì mảng sẽ đổi dấu phẩy (,) thành dấu gạch chéo (\) là {-8\-3\-5\-4}
Ví dụ sắp xếp ngang tương tự chỉ đổi tên Hàm V thành H


Ví dụ gọi thủ tục trong code VBA:

Mã:
Call VSORTING([A2:Z1000], 0, FALSE)
Call HSORTING([A2:Z1000], 0, FALSE)
Call VSORTING([A2:Z1000], Array(3,1,2), FALSE)
Call HSORTING([A2:Z1000], Array(3,1,-2), FALSE)
Call HSORTING([A2:Z1000], Array(1003,1,2), FALSE)

Hãy chép toàn bộ mã trong Module zzzSort_UDF vào ứng dụng của bạn để thực thi các hàm.


Hình ảnh tham khảo:

QuickSort Highlight Color - Excel UDF


Các bạn có thể tham khảo thêm hàm sắp xếp mảng khác:

Các bạn muốn học VBA nhanh nhất có thể hãy tham khảo tool hỗ trợ lập trình VBA:
Bạn có thể đọc thêm các bài biết của tôi tại tag #sanbi udf
 

File đính kèm

  • S_QuickSort_Color.xlsm
    447.8 KB · Đọc: 36
Lần chỉnh sửa cuối:
*** Cập nhật: Tăng tốc, thay đổi giải thuật sắp xếp tiếng Việt, sắp xếp số và chuỗi.
Thêm tham số sắp xếp không phân biệt Hoa thường.
 
Upvote 0
*** Cập nhật: Tăng tốc, thay đổi giải thuật sắp xếp tiếng Việt, sắp xếp số và chuỗi.
Thêm tham số sắp xếp không phân biệt Hoa thường.
Code
Mã:
Sub test()
Dim Arr
    Arr = Sheet5.Range("A1:C10").Value
    Sheet5.Range("E1").Resize(10, 3).Value = S_SortV(Arr, [{1,3,2}])
End Sub
Kết quả

sapxep.jpg

Tại sao 2 không nhỏ nhất? Cột C là số mà.
-------------
Dữ liệu dùng để test giống như trong Sheet5 của tập tin đính kèm ở bài 15 trong chủ đề

 
Lần chỉnh sửa cuối:
Upvote 0
Code
Mã:
Sub test()
Dim Arr
    Arr = Sheet5.Range("A1:C10").Value
    Sheet5.Range("E1").Resize(10, 3).Value = S_SortV(Arr, [{1,3,2}])
End Sub
Kết quả



Tại sao 2 không nhỏ nhất? Cột C là số mà.
-------------
Dữ liệu dùng để test giống như trong Sheet5 của tập tin đính kèm ở bài 15 trong chủ đề

Hàm CompText lỗi ở bước so sánh hai số, nhưng hai số đó vẫn ở dạng chuỗi nên lỗi. Lần này ổn cả

PHP:
Function CompText(ByVal Text1$, ByVal Text2$, Optional ByVal MatchCase As Boolean, Optional ByVal SortDescending As Boolean) As Long
  If Text1 = Text2 Then
    CompText = 0
  ElseIf Text1 = vbNullString Then
    CompText = IIf(SortDescending, -1, 1)
  ElseIf Text2 = vbNullString Then
    CompText = IIf(SortDescending, 1, -1)
  Else
    Dim n1 As Boolean, n2 As Boolean
    n1 = IsNumeric(Text1) Or IsDate(Text1)
    n2 = IsNumeric(Text2) Or IsDate(Text2)
    If (n1 And n2) Then
      If Text1 = Text2 Then
        CompText = 0
      ElseIf CDec(Text1) < CDec(Text2) Then
        CompText = -1
      Else
        CompText = 1
      End If
    ElseIf (n1 And Not n2) Then
      CompText = -1:
    ElseIf (Not n1 And n2) Then
      CompText = 1:
    Else
      Dim l1&, l2&, l&, m1$, m2$, b As Integer, i&
      l1 = Len(Text1)
      l2 = Len(Text2)
      l = IIf(l1 > l2, l2, l1)
      For i = 1 To l
        m1 = mid(Text1, i, 1)
        m2 = mid(Text2, i, 1)
        b = StrComp(m1, m2, 1)
        Select Case True
        Case b = 0
          If m1 <> m2 And Not MatchCase Then
            CompText = IIf(m1 < m2, 1, -1): Exit Function
          End If
        Case Else
          CompText = b
          Exit Function
        End Select
        If i = l Then
          If l1 < l2 Then
            CompText = -1
          ElseIf l1 = l2 Then
            CompText = 0
          Else
            CompText = 1
          End If
        End If
      Next
    End If
  End If
End Function
 
Lần chỉnh sửa cuối:
Upvote 0
1. Hồi xưa tôi định gộp 3 đoạn code dùng để sắp xếp 3 cột để làm gọn và có thể sắp xếp với số cột tùy ý. Tuy nhiên do không có nhu cầu nên thôi không làm nữa. Bây giờ càng không có hứng. Bây giờ là nghỉ ngơi thôi.
Bạn ơi, khi nào có thời gian bạn phát triển thêm giúp mình theo hướng này với.
 
Upvote 0
Hàm CompText lỗi ở bước so sánh hai số, nhưng hai số đó vẫn ở dạng chuỗi nên lỗi. Lần này ổn cả
Tôi tải tập tin S_QSort_Color.xlsm ở bài #1 -> chạy code bên dưới và tôi nhận được kết quả "lộn tùng phèo".
Dụng ý là sắp xếp theo 3 cột theo thứ tự: cột 2 tăng, cột 1 giảm, và cột 3 tăng.

Mã:
Sub test()
Dim Arr
    Arr = Sheet5.Range("A1:C10").Value
    Sheet5.Range("E1").Resize(10, 3).Value = S_SortV(Arr, [{2,-1,3}])
End Sub

sapxep.jpg
 
Upvote 0
Càng lúc tôi càng cảm thấy yên tâm khi sử dụng khi sử dụng file bài 30, của bạn.
Khi tôi viết code thì một trong những tiêu chí của tôi là kết quả không được phụ thuộc vào dạng unicode của dữ liệu gốc. Đã có rất nhiều người kêu ca trên GPE là dữ liệu gốc của họ không chuẩn vì được lấy từ nhiều nguồn, hoặc được nhập bởi nhiều người mà mỗi người nhập theo một kiểu. Nếu anh A khi nhập dùng unicode dựng sẵn còn cô B dùng unicode tổ hợp thì chắc chắn code của HeSanbi không thể sắp xếp đúng được. Vd. dữ liệu A1:B6, sau khi sắp xếp tăng dần theo cột A có D1:E6. Rõ ràng kết quả không đúng.

sapxep.jpg
 

File đính kèm

  • S_QSort_Color_HeSanbi1.xlsm
    449.6 KB · Đọc: 15
Upvote 0
Khi tôi viết code thì một trong những tiêu chí của tôi là kết quả không được phụ thuộc vào dạng unicode của dữ liệu gốc. Đã có rất nhiều người kêu ca trên GPE là dữ liệu gốc của họ không chuẩn vì được lấy từ nhiều nguồn, hoặc được nhập bởi nhiều người mà mỗi người nhập theo một kiểu. Nếu anh A khi nhập dùng unicode dựng sẵn còn cô B dùng unicode tổ hợp thì chắc chắn code của HeSanbi không thể sắp xếp đúng được. Vd. dữ liệu A1:B6, sau khi sắp xếp tăng dần theo cột A có D1:E6. Rõ ràng kết quả không đúng.

View attachment 258742
Bác nói như vậy khác nào code của bác, A nhập unicode, B nhập VPS, cũng lỗi vậy. Muốn thì ta chuyển hết về Unicode quá đơn giản vậy mà, có gì căng
 
Upvote 0
Bác nói như vậy khác nào code của bác, A nhập unicode, B nhập VPS, cũng lỗi vậy.
Tôi không nói suông. Khi tôi chỉ ra chỗ sai thì tôi cũng đính kèm tập tin đàng hoàng. Nếu bạn cho là với dữ liệu gốc có cả unicode tổ hợp và dựng sẵn thì code của tôi cũng sai thì xin mời đính kèm tập tin. Nếu bạn chưa kiểm tra code của tôi thì đừng viết: "Bác nói như vậy khác nào code của bác, A nhập unicode, B nhập VPS, cũng lỗi vậy". Nếu bạn đã kiểm tra và cho rằng code của tôi sai thì hãy đính kèm tập tin để tôi xem.
Muốn thì ta chuyển hết về Unicode quá đơn giản vậy mà, có gì căng
Căng ở chỗ là bạn không nói, và người dùng code của bạn không ý thức được những chỗ có thể có "bẫy". Bạn phải nói rõ ra để những người dùng code của bạn biết để họ chuyển hết về unicode dựng sẵn trước khi chạy code của bạn. Bạn không nói thì người ta không chuyển và kết quả sai. Nếu có 6 dòng như tôi đính kèm thì người ta sẽ nhận ra ngay, chứ nếu có hàng ngàn dòng thì họ làm sao biết?

Tôi tin là code của tôi xử lý dữ liệu lẫn lộn cả unicode dựng sẵn và unicode tổ hợp. Xử lý để sắp xếp nhưng KHÔNG THAY ĐỔI DỮ LIỆU GỐC. Tức sắp xếp vẫn đúng mà dữ liệu gốc có dựng sẵn và tổ hợp thì mãi mãi, muôn đời, vĩnh viễn nó vẫn có dựng sẵn và tổ hợp, vì code của tôi không chuyển chúng. Code của tôi không chỉnh sửa DỮ LIỆU GỐC của người dùng.

Nói A nhập unicode dựng sẵn còn B nhập unicode tổ hợp là có lý bởi thực tế đã có vậy. Nhiều người còn hỏi GPE là sao 2 đoạn text nhìn như nhau mà chúng không bằng nhau, hàm Excel không tìm thấy. Nói A nhập unicode còn B nhập VPS là nói cùn rồi.

Mà thôi, nói nhiều làm gì. Code của tôi xử lý tốt khi dữ liệu nguồn có cả unicode dựng sẵn và unicode tổ hợp. Code của bạn không xử lý được khi dữ liệu nguồn có cả unicode dựng sẵn và unicode tổ hợp. Nói rõ thế để người dùng tiềm năng khỏi bị bất ngờ.
 
Upvote 0
Càng lúc tôi càng cảm thấy yên tâm khi sử dụng khi sử dụng file bài 30, của bạn.
Nói vậy không hay rồi.
Các bạn post bài trả lời nhớ chú ý code phải chuẩn 100%, không sai mọi trường hợp, đáp ứng đúng yêu cầu chủ thớt chứ không phải chỗ thảo luận code, chỗ để trao đổi sai sửa , hoc hỏi nhé.
 
Lần chỉnh sửa cuối:
Upvote 0
Nói vậy không hay rồi.
Các bạn post bài trả lời nhớ chú ý code phải chuẩn 100%, không sai mọi trường hợp, đáp ứng đúng yêu cầu chủ thớt chứ không phải chỗ thảo luận code, chỗ để trao đổi sai sửa , hoc hỏi nhé.
Tôi nói tôi yên tâm là vì người viết đã chỉ ra các vấn đề lỗi có thể sảy ra.. đó là tôi thôi còn người khác nói hay hoặc không hay cũng được tôi không bận tâm, nhu cầu của tôi sort mảng thì chỉ cần tốc độ và các lựa chọn cột và kiểu chứ còn các tính năng khác màu tôi không dùng đến.
 
Lần chỉnh sửa cuối:
Upvote 0
Tôi nói tôi yên tâm là vì người viết đã chỉ ra các vấn đề lỗi có thể sảy ra.. đó là tôi thôi còn người khác tôi biết hay hay không hay cũng được, nhu cầu của tôi sort mảng thì chỉ cần tốc độ và các lựa chọn cột và kiểu chứ còn các tính năng khác màu tôi không dùng đến.
Càng trả lời càng tầm bậy,
Microsoft chỉ nên xây dựng hàm SUM, phát triển SUMIF làm gì, tôi chả cần, phát triển chi để Excel thêm nặng nề, các hàm lung tumg, "tôi" chả bao giờ xài tới. Người khác có xài hay không, tôi không quan tâm. Nếu không đúng yêu cầu của tôi, tôi coi nó là rác thôi. Bởi vậy nhắc nhở là các bạn trả lời đừng vung vãi chất xám lung tung nhé. :)
Thôi ngưng vậy.
 
Lần chỉnh sửa cuối:
Upvote 0
Web KT
Back
Top Bottom