Bài toán tìm số nhỏ (hoặc lớn) thứ n và xếp hạng (1 người xem)

[anhtuan1066]

1> Có nhiều bạn hỏi về việc muốn tìm số nhỏ (hoặc lớn) thứ n...
Ví dụ: trong dãy số: 1;4;4;4;7;1;9 thì số nhỏ nhất = 1, nhỏ thứ 2 = 4 (dùng hàm SMALL ko thể giải quyết được)
2> Và cũng có nhiều bạn muốn tìm 1 giãi pháp về việc xếp hạng...
Ví dụ: Cũng với dãy số trên, số 9 được xếp hạng nhất, số 7 hạng nhì... số 4 hạng 3... Và theo cách xếp hạng của hàm RANK, số 1 sẽ dc xếp hạng 6... Tuy nhiên vẫn có nhiều người muốn số 1 này phải xếp hạng 4 cơ (tức xếp tiếp theo, không nhảy bậc)
Trên GPE đã có nhiều giãi pháp cho vấn đề này nhưng chưa tổng quát lắm (có khi vẫn phải dùng đến cột phụ)
Tôi tình cờ nghĩ ra dc 1 cách: Quy chúng về bài toán trích lọc duy nhất... Các bạn theo dỏi nhé:
- Giả sử tôi có dử liệu số nằm từ A2:A31 (tưởng tượng nó là điểm thi cũng dc)
- Tôi đặt name cho vùng dử liệu này là:

DL =OFFSET($A$2,,,MATCH(9.99999999999999E+307,OFFSET($A$2,,,1000,)),)

(đặt name thế để phòng trường hợp dử liệu có dòng rổng)
- Thêm 1 name nữa:

VT =IF(DL="","",IF(MATCH(DL,DL,0)=ROW(INDIRECT("1:"&ROWS(DL))),DL,""))

(name này trích ra các phần tử duy nhất)
- Tôi có công thức tính số nhỏ (hoặc lớn) thứ n như sau:

SMALL(VT,Thứ)

và

LARGE(VT,Thứ)

(xem ví dụ tại cột E và F)
- Tôi có công thức xếp hạng cho từng phần tử trong dãy trên như sau:

=MATCH($A2,LARGE(VT,ROW(INDIRECT("1:"&COUNT(VT)))),0)

(công thức mãng)
Vậy là xong! Chẳng có cột phụ nào, bất chấp dử liệu có dòng rổng
Các bạn tham khảo và góp ý nhé
Mến
ANH TUẤN

 

[cadafi]

Em nghĩ chỉ cần đặt 1 name:

mArray:=IF($A$2:$A$31="","",IF(MATCH($A$2:$A$31,$A$2:$A$31,0)=ROW($A$2:$A$31)-MIN(ROW($A$2:$A$31))+1,$A$2:$A$31,""))

Sau đó:

+ Tìm số nhỏ thứ n:

=Small(mArray, n)

+ Tìm số lớn thứ n:

=Large(mArray, n)

+ Liệt kê danh sách các số duy nhất theo thứ tự tăng dần:

=IF(ISERROR(SMALL(mArray,ROW(1:1))),"",SMALL(mArray,ROW(1:1)))

+ Tạo một mảng mới với các phần tử duy nhất theo thứ tự tăng dần:

LIST=SMALL(mArray,ROW(INDIRECT("1:"&COUNT(mArray))))

+ Liệt kê danh sách các số duy nhất theo thứ tự giảm dần:

=IF(ISERROR(LARGE(mArray,ROW(1:1))),"",LARGE(mArray,ROW(1:1)))

+ Tạo một mảng mới với các phần tử duy nhất theo thứ tự giảm dần:

LIST:=LARGE(mArray,ROW(INDIRECT("1:"&COUNT(mArray))))

 

[anhtuan1066]

Em nghĩ chỉ cần đặt 1 name:

mArray:=IF($A$2:$A$31="","",IF(MATCH($A$2:$A$31,$A$2:$A$ 31,0)=ROW($A$2:$A$31)-MIN(ROW($A$2:$A$31))+1,$A$2:$A$31,""))

Cái này ko quan trọng lắm, chẳng qua tôi thêm name DL là để xác định vùng này theo kiểu "động"... đở mất công chỉnh sửa khi thêm dử liệu vào thôi!
Thêm nữa: nói thật là khi dùng hàm, tôi ko thích các hàm dạng ISNA, ISERROR lắm
(tính kỹ thế chứ ko biết người ta mang về áp dụng có phải hỏi lại ko đây? hi.. hi..)

 

[thieukien]

Cám ơn nhé! Nhờ bạn mình hiểu biết nhiều hơn!

 

[anhtuan1066]

Với cách dùng công thức như ở trên, sẽ khó khăn cho những bạn mới học!
Tôi xin gữi các bạn bộ hàm XẾP HẠNG và SẮP XẾP dùng code VBA, các bạn chỉ cần gõ hàm là xài được ngay (không cần phải rắc rối với các name)
Bộ hàm của tôi gồm:
1> 2 hàm hổ trợ:

Private Function UniqueList(SrcRng As Range)
  Dim Clls As Range
  With CreateObject("Scripting.Dictionary")
    For Each Clls In SrcRng
      If Not .Exists(Clls.Value) And Clls <> "" Then
        .Add Clls.Value, ""
      End If
    Next
    UniqueList = .Keys
  End With
End Function

Private Function SortArr(Arr, Optional Order As Boolean = True)
  Dim Temp, i As Long
  ReDim Temp(0 To UBound(Arr))
  With WorksheetFunction
    For i = 0 To UBound(Arr)
      Temp(i) = IIf(Order, .Large(Arr, i + 1), .Small(Arr, i + 1))
    Next i
  End With
  SortArr = Temp
End Function

2> 2 hàm chính

Function Ranking(SrcRng As Range, Point As Double, Optional Order As Boolean = True) As Long
  Dim Clls As Range, Temp, i As Long
  On Error Resume Next
  Temp = SortArr(UniqueList(SrcRng), Order)
  For i = 0 To UBound(Temp)
    If Temp(i) = Point Then
      Ranking = i + 1: Exit Function
     End If
  Next i
End Function

Function LSFilter(SrcRng As Range, Pos As Long, Optional Order As Boolean = True)
  Dim Temp
  Temp = UniqueList(SrcRng)
  With WorksheetFunction
    If Pos > UBound(Temp) + 1 Then
      LSFilter = ""
    Else
      LSFilter = .Large(Temp, IIf(Order, Pos, UBound(Temp) + 2 - Pos))
    End If
  End With
End Function

Các bạn có thể lưu thành AddIn để dùng lâu dài
Điểm nổi bậc ở đây là hàm sort mãng...
Vì chúng ta làm việc với Excel nên tôi tận dụng tối đa những gì có sẳn... Cụ thể là dùng hàm LARGE và SMALL
Với cách sort mãng thông thường, ta phải qua 2 vòng lập theo kiểu VÒNG LẬP TRONG VÒNG LẬP, như vậy mất rất nhiều thời gian... Với cách dùng LARGE và SMALL, chỉ cần qua 1 vòng lập duy nhất, tăng tốc độ lên rất nhiều
Xin mời tham khảo file đính kèm

 

[tbtduc]

[A1 : A10] : Là các dãy số ngẫu nhiên cần xếp hạng.

=SUMPRODUCT(--($A$1:$A$10 < A1),1/COUNTIF($A$1:$A$10,$A$1:$A$10))+1 ' Dấu < Xếp hạng từ nhỏ đến lớn

 

[ndu96081631]

[A1 : A10] : Là các dãy số ngẫu nhiên cần xếp hạng.

=SUMPRODUCT(--($A$1:$A$10 < A1),1/COUNTIF($A$1:$A$10,$A$1:$A$10))+1 ' Dấu < Xếp hạng từ nhỏ đến lớn

Công thức này còn phải chỉnh sửa thêm nữa mới xong!
Bạn hãy để ý trường hợp vùng dữ liệu có cell rổng nhé

 

[hoamattroicoi]

=SUMPRODUCT(--($A$1:$A$10 < A1),1/COUNTIF($A$1:$A$10,$A$1:$A$10))+1 ' Dấu < Xếp hạng từ nhỏ đến lớn
Mình thấy công thức náy rất hay nhưng mình chưa hiểu lắm, bạn giúp mình giải thích nó được không? Thanks nhiều!

 

[majoson]

anh Dũng ơi (@ndu96081631), anh chỉnh thêm công thức này giúp em để loại trừ các cell rỗng nhé. Cám ơn anh nha.

 

[Phuocam]

Công thức này còn phải chỉnh sửa thêm nữa mới xong!
Bạn hãy để ý trường hợp vùng dữ liệu có cell rổng nhé

Góp vui thêm một cách xếp hạng trong trường hợp vùng dữ liệu có cell rỗng.

 

[sumisumo]

Mình có bảng tính gồm nhiều mã hàng, mỗi mã có 12 giá, mình muốn tìm giá lớn nhất của mỗi mã hàng thì phải làm sao?
mã hàng tháng 1 tháng 2 tháng 3 tháng 4 tháng 5 tháng 6, .....
A 100 200 80 40 60 150
B
C ......
...
...
...

 

[chimaiyeunguoi]

Thầy ơi, cho em hỏi hai dấu -- đằng trước có ý nghĩa gì
Thầy có thể giải thích rõ công thức này không?
Em cảm ơn thầy nhiều.

 

[Ba Tê]

Thầy ơi, cho em hỏi hai dấu -- đằng trước có ý nghĩa gì
Thầy có thể giải thích rõ công thức này không?
Em cảm ơn thầy nhiều.

Tự "ngâm kiếu" như vầy sẽ nhớ dai hơn:
- A1 nhập ABC123, Copy xuống đến A5
- B1 nhập =Right(A1,3) - Copy xuống đến B5
- B6 =SUM(B1:B5), Kết quả ra cái gì, tại sao?
-----------------------------------------
C1 nhập =Right(A1,3)+0, hoặc =Right(A1,3)*1, hoặc --Right(A1,3), hoặc =VALUE(RIGHT(A1;3)). Copy xuống đến C5
C6=SUM(C1:C5), Kết quả ra cái gì, "vỗ trán" là biết.