Sắp xếp thứ tự của các giá trị tương ứng (1 người xem)

[AndOrNot]

Chào các bác!
Em có 1 bài toán được diễn giải như sau:

Dữ liệu đầu vào gồm 2 cột "Ma hang", "So LOT". Kết quả (cột "Thu tu") cần hiển thị theo yêu cầu như sau:

+ Mã hàng A có các số lot: M1526, M4728, M9672, M1320. Như vậy nếu như trong cùng mã hàng A thứ tự sắp xếp tăng dần theo số LOT là M1320, M1526, M4728, M9672

+ Mã hàng B có các số lot: G1341, G4379, G3419. Như vậy nếu như trong cùng mã hàng B thứ tự sắp xếp tăng dần theo số LOT là G1341, G3419, G4379

+ Mã hàng C có các số lot: X3414, X1345, X5654, X2341, X3445. Như vậy nếu như trong cùng mã hàng C thứ tự sắp xếp tăng dần theo số LOT là X1345, X2341, X3414, X3445, X5654

+ Mã hàng D có các số lot: R8475, R3427, R9877, R4524. Như vậy nếu như trong cùng mã hàng D thứ tự sắp xếp tăng dần theo số LOT là R3427, R4524, R8475, R9877

(chi tiết trong file đính kèm)

Mong được các bác chỉ giáo!

AndOrNot

 

[hoamattroicoi]

Thử thế này xem :

Public Function RankHMT(vSearch, inGroup, listGroups As Range, listValues As Range) As Long
    Dim Dic, gR, vA
    Dim i As Long, d As Long, vAss As Long
    gR = listGroups.Value
    vA = listValues.Value
    vSearch = Val(Right(vSearch, Len(vSearch) - 1))
    Set Dic = CreateObject("Scripting.Dictionary")
    d = 0
    For i = 1 To UBound(gR, 1)
        If gR(i, 1) = inGroup Then
        vAss = Val(Right(vA(i, 1), Len(vA(i, 1)) - 1))
            If vAss < vSearch And (Not Dic.Exists(vAss)) Then
                Dic.Add vAss, ""
                d = d + 1
            End If
        End If
    Next i
    Set Dic = Nothing
    
    RankHMT = d + 1
End Function

Xem thêm trong file đính kèm nhé!

 

[AndOrNot]

Thử thế này xem :

Public Function RankHMT(vSearch, inGroup, listGroups As Range, listValues As Range) As Long
    Dim Dic, gR, vA
    Dim i As Long, d As Long, vAss As Long
    gR = listGroups.Value
    vA = listValues.Value
    vSearch = Val(Right(vSearch, Len(vSearch) - 1))
    Set Dic = CreateObject("Scripting.Dictionary")
    d = 0
    For i = 1 To UBound(gR, 1)
        If gR(i, 1) = inGroup Then
        vAss = Val(Right(vA(i, 1), Len(vA(i, 1)) - 1))
            If vAss < vSearch And (Not Dic.Exists(vAss)) Then
                Dic.Add vAss, ""
                d = d + 1
            End If
        End If
    Next i
    Set Dic = Nothing
    
    RankHMT = d + 1
End Function

Xem thêm trong file đính kèm nhé!

Bác ơi. Kết quả khi em mở file của bác lên thì đúng rồi, nhưng khi em thay đổi dữ liệu ở 2 cột A,B thì công thức bị biến thành "#NAME?"

 

[Ba Tê]

Bác ơi. Kết quả khi em mở file của bác lên thì đúng rồi, nhưng khi em thay đổi dữ liệu ở 2 cột A,B thì công thức bị biến thành "#NAME?"

Sao không Sort nó về một cục cho dễ nhìn, dễ quản lý nhỉ?
Có thay đổi gì đó thì bấm nút 1 phát là xong

 

[siwtom]

Chào các bác!
Em có 1 bài toán được diễn giải như sau:

Dữ liệu đầu vào gồm 2 cột "Ma hang", "So LOT". Kết quả (cột "Thu tu") cần hiển thị theo yêu cầu như sau:

+ Mã hàng A có các số lot: M1526, M4728, M9672, M1320. Như vậy nếu như trong cùng mã hàng A thứ tự sắp xếp tăng dần theo số LOT là M1320, M1526, M4728, M9672

+ Mã hàng B có các số lot: G1341, G4379, G3419. Như vậy nếu như trong cùng mã hàng B thứ tự sắp xếp tăng dần theo số LOT là G1341, G3419, G4379

+ Mã hàng C có các số lot: X3414, X1345, X5654, X2341, X3445. Như vậy nếu như trong cùng mã hàng C thứ tự sắp xếp tăng dần theo số LOT là X1345, X2341, X3414, X3445, X5654

+ Mã hàng D có các số lot: R8475, R3427, R9877, R4524. Như vậy nếu như trong cùng mã hàng D thứ tự sắp xếp tăng dần theo số LOT là R3427, R4524, R8475, R9877

(chi tiết trong file đính kèm)

Mong được các bác chỉ giáo!

AndOrNot

Bây giờ mới để ý thấy là mục "hàm tự tạo"
Nhờ BQT xóa hộ bài này.
----------------------















Trong khi chờ các bạn khác tôi mạo muội góp vui.

Cách 1: Công thức cho D2 kết thúc bằng CTRL + SHIFT + ENTER. Kéo xuống dưới

=INDEX(IF($A$2:$A$17=A2;COUNTIF($B$2:$B$17;"<="&$B$2:$B$17);"");
ROWS($1:1))-MIN(IF($A$2:$A$17=A2;COUNTIF($B$2:$B$17;"<="&$B$2:$B$17);""))+1

Cách 2: Chọn ô E2 (hoặc ô ở dòng 2) --> đặt Name là Ma

=IF($A$2:$A$17=$A2;COUNTIF($B$2:$B$17;"<="&$B$2:$B$17);"")

Công thức cho E2, kết thúc bằng ENTER:

=INDEX(Ma;ROWS($1:1))-MIN(Ma)+1

Kéo xuống dưới

 

[hoamattroicoi]

Bác ơi. Kết quả khi em mở file của bác lên thì đúng rồi, nhưng khi em thay đổi dữ liệu ở 2 cột A,B thì công thức bị biến thành "#NAME?"

Chưa rõ bạn thay thế dữ liệu thực tế ra sao, lỗi #NAME nghiã là hàm bạn gõ bị sai tên. Chú ý hàm của tôi k có sẵn trong excel, bạn phải copy cả đoạn code trên vào module gõ đúng tên hàm và tham số nhé. Phải chăng dữ liệu thực tế của bạn có phát sinh thêm? Xin vui lòng kiểm tra lại giúp mình nha. Thanks!!!!

 

[AndOrNot]

Chưa rõ bạn thay thế dữ liệu thực tế ra sao, lỗi #NAME
? nghiã là hàm bạn gõ bị sai tên. Chú ý hàm của tôi k có sẵn trong excel, bạn phải copy cả đoạn code trên vào module gõ đúng tên hàm và tham số nhé. Phải chăng dữ liệu thực tế của bạn có phát sinh thêm? Xin vui lòng kiểm tra lại giúp mình nha. Thanks!!!!

Em không thay đổi gì cả, chỉ F2 sau đó Enter thì báo lỗi như vậy.

 

[siwtom]

Mới viết sơ qua, bạn kiểm tra xem
------------
Công thức cho ô bất kỳ

=hichic(A$2:A$17;B$2:B$17;ROWS($1:1))

Kéo xuống dưới cho tới khi gặp #VALUE!

[GPECODE=vb]
Function hichic(ByVal List, ByVal Values, ByVal index As Long)
Dim arrList, arrValues, item, value, k As Long, count
arrList = List
arrValues = Values
item = arrList(index + LBound(arrList) - 1, 1)
value = arrValues(index + LBound(arrValues) - 1, 1)
For k = LBound(arrList) To UBound(arrList)
If arrList(k, 1) = item Then
If arrValues(k, 1) <= value Then count = count + 1
End If
Next k
hichic = count
End Function
[/GPECODE]

 

[hoamattroicoi]

Em không thay đổi gì cả, chỉ F2 sau đó Enter thì báo lỗi như vậy.

AndOrNot gửi cho mình xem cái file mà bạn chạy code của mình bị lỗi được không? Cứ kêu code của mình sai mà mình k nhìn thấy nguyên nhân nó sai là do mình viết code sai hay dữ liệu sai hay là gì gì đó....là mình rất khó chịu nha, thanks bạn!

Nếu không muốn share dữ liệu thật trên GPE thì có thể gửi qua email của mình : hoamattroicoi@giaiphapexcel.com

 

[AndOrNot]

AndOrNot gửi cho mình xem cái file mà bạn chạy code của mình bị lỗi được không? Cứ kêu code của mình sai mà mình k nhìn thấy nguyên nhân nó sai là do mình viết code sai hay dữ liệu sai hay là gì gì đó....là mình rất khó chịu nha, thanks bạn!

Nếu không muốn share dữ liệu thật trên GPE thì có thể gửi qua email của mình : hoamattroicoi@giaiphapexcel.com

bác hoamattroicoi ơi, em mở lại file của bác gửi bằng Excel 2k3 thì được (máy ở cty). Đợi trưa em về nhà em gửi cho bacs lỗi (chụp màn hình) khi mở bằng Excel 2k7. Em cũng không biết sao nữa.

 

[AndOrNot]

AndOrNot gửi cho mình xem cái file mà bạn chạy code của mình bị lỗi được không? Cứ kêu code của mình sai mà mình k nhìn thấy nguyên nhân nó sai là do mình viết code sai hay dữ liệu sai hay là gì gì đó....là mình rất khó chịu nha, thanks bạn!

Nếu không muốn share dữ liệu thật trên GPE thì có thể gửi qua email của mình : hoamattroicoi@giaiphapexcel.com

Bác hoamattroicoi ơi!

1. Lần này em dùng 2k3 thì không báo lỗi nhưng mà nó cho kết quả không chính xác. Bác kiểm tra lại giúp em với

2. Tiện thể em muốn sắp xếp theo thứ tự (Rank) bằng 1 hàm tự tạo khác mà không phụ thuộc vào Group (như trong ví dụ sheet2 "RankHMT2")


Em cảm ơn bác nhiều nhé!
Bác ơi, bác cho em xin số điện thoại của bác đi.

AndOrNot

 

[hoamattroicoi]

Dữ liệu của bạn có phát sinh thảo nào code mình nó báo lỗi là phải, không thấy cái lỗi đó chắc mình áy náy mãi hỏng biết sao code viết bị sai. Code mình viết trên chỉ áp dụng với những dữ liệu mà VAL của nó chứa 1 ký tự TEXT đứng đầu theo dữ liệu mẫu bạn cho còn dữ liệu thật của bạn thì text nằm lung tung, thêm cái hàm tách số ra và xếp hạng thôi. Bạn xem file này nhé, đã chỉnh sửa và thêm rankHMT2 cho bạn.

Thanks vì đã share lỗi!!!!

 

[AndOrNot]

Dữ liệu của bạn có phát sinh thảo nào code mình nó báo lỗi là phải, không thấy cái lỗi đó chắc mình áy náy mãi hỏng biết sao code viết bị sai. Code mình viết trên chỉ áp dụng với những dữ liệu mà VAL của nó chứa 1 ký tự TEXT đứng đầu theo dữ liệu mẫu bạn cho còn dữ liệu thật của bạn thì text nằm lung tung, thêm cái hàm tách số ra và xếp hạng thôi. Bạn xem file này nhé, đã chỉnh sửa và thêm rankHMT2 cho bạn.

Thanks vì đã share lỗi!!!!

Em cảm ơn bác hoamattroicoi ! và bác cũng phải cảm ơn em nữa rồi (hihi) vì em thấy kết quả vẫn có chỗ chưa đúng. Bác kiểm tra giúp em với nhé!

AndOrNot

 

[hoamattroicoi]

Em cảm ơn bác hoamattroicoi ! và bác cũng phải cảm ơn em nữa rồi (hihi) vì em thấy kết quả vẫn có chỗ chưa đúng. Bác kiểm tra giúp em với nhé!

AndOrNot

Không phải kết quả chưa đúng mà chưa hiểu ý nhau thôi, không hiểu chỗ bôi màu hồng của AndOrNot là sao, 0704 và 740 không cùng hạng? - 0740 xếp thứ 56 còn 740 xếp thứ 4?????? Thuật toán của mình là lọc lấy số và đem đi xếp hạng giống như mình dùng hàm ngoài excel đó, HMT có để hàm kiểm tra bên cạnh để bạn đối chiếu mà.

Chắc bạn phải mô tả lại yêu cầu rồi, càng ngày càng thấy cái yêu cầu của bạn chả giống quái gì với cái file ban đầu bạn đưa ra, hix hix hix

 

[AndOrNot]

Không phải kết quả chưa đúng mà chưa hiểu ý nhau thôi, không hiểu chỗ bôi màu hồng của AndOrNot là sao, 0704 và 740 không cùng hạng? - 0740 xếp thứ 56 còn 740 xếp thứ 4?????? Thuật toán của mình là lọc lấy số và đem đi xếp hạng giống như mình dùng hàm ngoài excel đó, HMT có để hàm kiểm tra bên cạnh để bạn đối chiếu mà.

Chắc bạn phải mô tả lại yêu cầu rồi, càng ngày càng thấy cái yêu cầu của bạn chả giống quái gì với cái file ban đầu bạn đưa ra, hix hix hix

Bác hoamattroicoi ơi!
Có thẻ là ví dụ em đưa ra ban đầu chỉ mang tính minh hoạ chứ chưa bao quát được các trường hợp tổng quát. Vì vậy em xin giải thích lại yêu cầu như sau: sử dụng hàm tự tạo (RankHTM và RankHTM2) để sắp xếp theo thứ tự tăng dần, thứ tự này đảm bảo trùng khớp với việc sắp xếp của Excel, như vậy có nghĩa là nếu như em sắp xếp (Sort tăng dần) bằng thao tác thì kết quả của cột hàm tự tạo sẽ phải tăng từ 1--> n (trong đó n là số ô giá trị), như vậy giá trị kết quả phải đảm bảo tồn tại và duy nhất (các giá trị từ 1-n, vì dữ liệu cần sắp xếp không trùng nhau)

Hy vọng là bác đã hiểu cái yêu cầu quái gở của em

AndOrNot

 

[vodoi2x]

Bác hoamattroicoi ơi!
Có thẻ là ví dụ em đưa ra ban đầu chỉ mang tính minh hoạ chứ chưa bao quát được các trường hợp tổng quát. Vì vậy em xin giải thích lại yêu cầu như sau: sử dụng hàm tự tạo (RankHTM và RankHTM2) để sắp xếp theo thứ tự tăng dần, thứ tự này đảm bảo trùng khớp với việc sắp xếp của Excel, như vậy có nghĩa là nếu như em sắp xếp (Sort tăng dần) bằng thao tác thì kết quả của cột hàm tự tạo sẽ phải tăng từ 1--> n (trong đó n là số ô giá trị), như vậy giá trị kết quả phải đảm bảo tồn tại và duy nhất (các giá trị từ 1-n, vì dữ liệu cần sắp xếp không trùng nhau)

Hy vọng là bác đã hiểu cái yêu cầu quái gở của em

AndOrNot

Vậy là bạn sắp xếp để nguyên ký tự vậy thì dùng thử hàm giản đơn này vậy

Public Function RankV(ByVal V, ByVal listArr) As Long
    Dim i As Long, k As Long, Arr
    Arr = listArr
    k = 0
    For i = LBound(Arr) To UBound(Arr)
        If V > Arr(i, 1) Then k = k + 1
    Next i
    RankV = k
End Function

nhập công thức kết quả cho ô B2

=RankV(A2,$A$2:$A$60)

copy tiếp tục cho B3, B4, ....

 

[AndOrNot]

Vậy là bạn sắp xếp để nguyên ký tự vậy thì dùng thử hàm giản đơn này vậy

Public Function RankV(ByVal V, ByVal listArr) As Long
    Dim i As Long, k As Long, Arr
    Arr = listArr
    k = 0
    For i = LBound(Arr) To UBound(Arr)
        If V > Arr(i, 1) Then k = k + 1
    Next i
    RankV = k
End Function

nhập công thức kết quả cho ô B2

=RankV(A2,$A$2:$A$60)

copy tiếp tục cho B3, B4, ....

Bác vodoi2x hiểu đúng ý em rồi --> mừng quá

Em cảm ơn bác, công thức của bác đúng với ý của em (em chỉ cần thay giá trị ban đầu k=1 là OK luôn.

Phần 2 muốn nhờ bác giúp cho công thức tự tạo để sắp xếp thứ tự theo nhóm. Bác xem trong file attach giúp em. Lần này cũng rất hồi hộp và hy vọng bác hiểu ý diễn đạt rất tồi của em.

Cảm ơn bác vodoi2x, bác hoamattroicoi, bác ndu.

AndOrNot

 

[vodoi2x]

Bác vodoi2x hiểu đúng ý em rồi --> mừng quá

tại bạn phức tạp hóa vấn đề lên đó thui, cứ nói muốn có hàm giống hàm RANK nhưng áp dụng cho cả ký tự là nhanh rui

Phần 2 muốn nhờ bác giúp cho công thức tự tạo để sắp xếp thứ tự theo nhóm. Bác xem trong file attach giúp em. Lần này cũng rất hồi hộp và hy vọng bác hiểu ý diễn đạt rất tồi của em.
AndOrNot

thử hàm củ mài giản đơn này xem sao:

 Public Function RankV2(ByVal Vsearch, ByVal listVsearch, ByVal vGroup, ByVal listGroup) As Long
    Dim i As Long, k As Long, Arr, ArrG
    Arr = listVsearch: ArrG = listGroup
    k = 1
    For i = LBound(Arr) To UBound(Arr)
        If vGroup = ArrG(i, 1) Then
            If Vsearch > Arr(i, 1) Then k = k + 1
        End If
    Next i
    RankV2 = k
End Function

công thức cho D2
=RankV2(B2,$B$2:$B$72,A2,$A$2:$A$72)
copy xuống cho D3,D4,...

 

[AndOrNot]

tại bạn phức tạp hóa vấn đề lên đó thui, cứ nói muốn có hàm giống hàm RANK nhưng áp dụng cho cả ký tự là nhanh rui



thử hàm củ mài giản đơn này xem sao:

 Public Function RankV2(ByVal Vsearch, ByVal listVsearch, ByVal vGroup, ByVal listGroup) As Long
    Dim i As Long, k As Long, Arr, ArrG
    Arr = listVsearch: ArrG = listGroup
    k = 1
    For i = LBound(Arr) To UBound(Arr)
        If vGroup = ArrG(i, 1) Then
            If Vsearch > Arr(i, 1) Then k = k + 1
        End If
    Next i
    RankV2 = k
End Function

công thức cho D2
=RankV2(B2,$B$2:$B$72,A2,$A$2:$A$72)
copy xuống cho D3,D4,...

Em không biết nói gì hơn chỉ biết nói "Cảm ơn" và "Khâm phục" bác quá bác vodoi2x ơi. (Em cũng thấy mình thật là kém trong việc diễn đạt ý làm các bác khác đã mất công).

Cảm ơn tất cả các bác đã xem chủ đề và đặc biết cảm ơn các bác đã có những chỉ giáo cho em. EM NGU QUÁ, em vui vì điều này, vì qua đây em biết thêm được nhiều cái nữa, và biết được các bác thật là giỏi.

AndOrNot