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

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ểu	Chức năng
1​	Cells	Vùng cần tổng	Nhận vùng cần sắp xếp
2​	Indexs	Số cột hoặc Mảng các cột	Nế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​	MatchCase	Có/Không	Sắp xếp không phân biệt hoa thường
4​	color	Số	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​	DefaultFontColor	Số	Màu phông chữ mặc định
6​	DefaultBackColor	Số	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:

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:

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

Hàm Tự động sắp xếp bổ trợ Chế độ Sort linh hoạt cho Excel

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 tự động thay vì phải điều chỉnh bằng tay ở chế độ sắp xếp. Có phải các bạn rất khó khăn trong việc thực hiện sắp xếp với chế độ sort của Excel, hay bạn phải dùng tay thực hiện sắp xếp quá nhiều lần...

giaiphapexcel.com

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:

VBA_FastCode v2.22 - Công cụ học và viết mã VBA một cách nhanh nhất (*27/10/2023)

***** CẬP NHẬT MỚI v2.2 ***** -------------------------------------------------------------------------- Add-in được chia sẻ dưới đây, đó là những gì tôi hiểu biết về VBA và đã viết ra nó. Một Add-in Tools chỉnh sửa code VBA rất đơn giản nhưng có thể giúp các bạn học VBA và viết code nhanh...

giaiphapexcel.com

Bạn có thể đọc thêm các bài biết của tôi tại tag #sanbi udf

 

Của tôi thì không biết là tiếng gì :

Gần 9 ̣ năm trước siwtom đã viết

 

*** 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.

 

*** 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

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ủ đề

Function Sort mảng 2 chiều

Function Sort mảng 2 chiều Trước đây có viết hàm sort mảng 2 chiều bằng cách chuyển dạng số sang dạng chuỗi, nay viết mới toàn bộ và tách dữ liệu thành 4 dạng: Error, Blank, Số và Chuổi, hàm có thể dùng trong code VBA hoặc trực tiếp trên Sheet Function SortArray2D(ByVal sArr, ByVal aCol...

giaiphapexcel.com

 

Code

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ủ đề

Function Sort mảng 2 chiều

Function Sort mảng 2 chiều Trước đây có viết hàm sort mảng 2 chiều bằng cách chuyển dạng số sang dạng chuỗi, nay viết mới toàn bộ và tách dữ liệu thành 4 dạng: Error, Blank, Số và Chuổi, hàm có thể dùng trong code VBA hoặc trực tiếp trên Sheet Function SortArray2D(ByVal sArr, ByVal aCol...

giaiphapexcel.com

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ả

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

 

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.

 

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.

Hàm trên tôi đã viết đầy đủ tính năng. Bạn đọc bài chính để có hướng dẫn.

 

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.

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

 

Cập nhật sửa lỗi khi sắp xếp thứ tự giảm dần

@batman1 Đã sửa Bác kiểm tra lại.​

 

Cập nhật sửa lỗi khi sắp xếp thứ tự giảm dần

@batman1 Đã sửa Bác kiểm tra lại.​

Có vẻ đã hết lỗ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.

Thôi được, hôm nay thứ 7 mà cũng ngại ra ngoài quá nên làm giải trí. Bạn tự kiểm tra.

 

Thôi được, hôm nay thứ 7 mà cũng ngại ra ngoài quá nên làm giải trí. Bạn tự kiểm tra.

Cảm ơn bạn nhiều, kết quả rất tuyệt, thật hữu ích cho tôi.

 

Cập nhật sửa lỗi khi sắp xếp thứ tự giảm dần

@batman1 Đã sửa Bác kiểm tra lại.​

Khi chạy code sẽ có lỗi. Code có lỗi khi sắp xếp cột có ngày tháng (cột C)

 

Khi chạy code sẽ có lỗi. Code có lỗi khi sắp xếp cột có ngày tháng (cột C)

Bác sửa đoạn CDec(CDate(Text1)) < CDec(CDate(Text2))

 

Khi chạy code sẽ có lỗi. Code có lỗi khi sắp xếp cột có ngày tháng (cột C)

View attachment 258718

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.

 

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.

 

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

 

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ờ.

 

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é.

 

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.

 

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.