Video hướng dẫn sử dụng hàm Rank trong Excel
Công thức của hàm Rank
Hàm Rank được viết theo công thức như sau:
=RANK(number, ref, [order])
Trong đó:
Number: số cần xếp hạng trong danh sách
Ref: danh sách các số
Order: thứ tự xếp hạng. Gồm 2 cách thực hiện sắp xếp theo thứ tự tăng dần (số 0 = mặc định nếu không nhập) hay thứ tự giảm dần (số 1)
Hàm RANK được dùng trong mọi phiên bản của Excel như Excel phiên bản 2003, Excel 2007, Excel 2010, Excel 2013, Excel 2024, Office 365…
Tuy nhiên trong các phiên bản mới của Excel (từ 2010 trở đi) hoàn toàn có thể sử dụng các hàm chúng tôi và chúng tôi để thực hiện việc sắp xếp được cụ thể hơn, chính xác hơn. Tuy nhiên bạn hoàn toàn có thể sử dụng hàm RANK trong mọi phiên bản một cách bình thường.
Xếp hạng theo thứ tự tăng dần với hàm RANK
Để xác định điểm của mỗi người xếp hạng thứ bao nhiêu trong danh sách điểm trung bình B2:B8, bạn hoàn toàn sử dụng hàm Rank:
C2=RANK(B2,$B$2:$B$8)
Xét điểm trung bình tại ô B2, trong danh sách điểm từ B2:B8
Không sử dụng tham số tại [order] tức là thực hiện sắp xếp theo thứ tự tăng dần.
Như vậy bạn thấy:
Người có điểm trung bình cao nhất là 9.5 được xếp thứ hạng 1
Người có điểm TB là 8.1 được xếp thứ hạng 2, trong đó có 2 người cùng điểm, do đó có 2 vị trí xếp hạng 2 là dòng thứ 3 và dòng thứ 6
Do 2 người đồng hạng 2 nên sẽ không có vị trí xếp hạng 3. Hạng tiếp theo sẽ là hạng 4, ứng với số điểm trung bình là 7.6
Xếp hạng theo thứ tự giảm dần bằng hàm RANK
Xếp hạng theo thứ tự giảm dần khi bạn muốn đánh giá trên những tiêu chí “không tốt”, căn cứ vào đó để bạn đánh giá xem ai có giá trị thấp nhất thì tốt nhất.
Trong ví dụ trên, bạn muốn xếp hạng theo số lỗi mà mỗi người mắc phải. Ai ít lỗi nhất thì xếp thứ 1. Thứ hạng sẽ tăng dần theo số lỗi mắc phải.
Khi đó bạn sẽ dùng hàm RANK với tham số Order = 1
C2=RANK(B2,$B$2:$B$8,1)
Giá trị xét xếp hạng: ô B2
Vùng danh sách các số tham chiếu sẽ là: B2:B8. Vùng này cần cố định để không thay đổi trong các công thức
Thứ tự xếp hạng: giảm dần, thì bạn sử dụng số 1
Như vậy bạn sẽ 2 người có lỗi ít nhất (là 1 lỗi) thì đều xếp thứ 1. Người mắc nhiều lỗi nhất (là 8 lỗi) sẽ xếp hạng thứ 7
Tuy nhiên trong 2 cách sắp xếp trên, bạn thấy hàm RANK cho phép những giá trị giống nhau thì chúng sẽ cùng chung 1 thứ hạng. Nhưng thực tế đôi khi bạn cần xếp hạng cụ thể cho từng bậc, nếu giống nhau thì bạn cần có thêm những tiêu chí phụ để làm căn cứ đánh giá xếp hạng. Khi đó bạn sẽ có thêm 2 kiểu xếp hạng như sau:
Kiểu thứ 1: Xếp hạng liên tục không nhảy bậc (không đồng hạng)
Cách sếp hạng theo thứ tự tăng dần liên tục không ngắt quảng
Trong ví dụ về xếp hạng theo thứ tự tăng dần với hàm RANK, bạn thấy:
Trường hợp những người có cùng điểm trung bình là 8.1 đều xếp thứ 2. Tiếp theo là hạng thứ 4, bị ngắt quãng ở hạng thứ 3. Lý do là có 2 người xếp thứ 2, sẽ không còn ai xếp hạng ở vị trí thứ 3 nữa.
Nếu như không quy định tiêu chí cụ thể nào cho việc này, bạn hoàn toàn có thể quy ước thứ hạng được thực hiện sắp xếp theo thứ tự từ trên xuống, tức là ai có điểm giống mà xếp ở trên thì sẽ được sắp xếp thứ hạng nhỏ hơn.
Như vậy nếu lấy giá trị Xếp hạng bởi hàm RANK + COUNTIF – 1 bạn sẽ có những thứ hạng liên tục không bị ngắt quãng như sau:
Cách viết trực tiếp hàm RANK và hàm COUNTIF trong cùng 1 công thức sẽ là:
=RANK(B2,$B$2:$B$8)+COUNTIF($B$2:B2,B2)-1
Cách sếp hạng theo thứ tự giảm dần liên tục không ngắt quảng
Tương tự với cách xếp hạng theo thứ tự giảm dần, bạn hoàn toàn có thể gặp phải trường hợp hàm RANK xếp theo thứ tự đồng hạng với những giá trị bằng nhau. Nhưng trong cách làm này, logic bạn cần thay đổi như sau:
Bước 1: Đếm tổng số hạng cần được xếp với hàm COUNT (đếm giá trị dạng số)
Bước 2: Sử dụng công thức của hàm Rank để xếp hạng theo thứ tự tăng dần
Bước 3: Sử dụng hàm COUNTIF giống như ở cách trên để phát hiện các vị trí có giá trị bị trùng
Bước 4: Kết quả xếp hạng được tính như sau = Bước 1 – (Bước 2 + Bước 3) + 2
Kết quả như sau:
Công thức sử dụng là:
Bước 1: D2=COUNT($B$2:$B$8)
Bước 2 và 3: E2=RANK(B2,$B$2:$B$8)+COUNTIF($B$2:B2,B2)
Bước 4: F2=D2-E2+2
Nếu lồng các hàm vào nhau bạn hoàn toàn có thể sử dụng 1 công thức duy nhất như sau:
Kết quả xếp hạng =COUNT($B$2:$B$8) – (RANK(B2,$B$2:$B$8)+COUNTIF($B$2:B2,B2)) + 2
Kiểu thứ 2: Xếp hạng theo điều kiện
Bắt đầu từ phiên bản Excel 2010, trong excel xuất hiện 2 hàm thay thế cho hàm RANK là hàm chúng tôi và hàm chúng tôi Các phiên bản trước đó của Excel (2003, 2007) chỉ có hàm RANK mà thôi.
Về công thức, 3 hàm này đều có cú pháp giống nhau
Công thức:
= RANK( number, ref, [order])
= RANK.AVG( number, ref, [order])
= RANK.EQ( number, ref, [order])
: Tham số mà bạn muốn tìm thứ hạng cho nó. : Có nghĩa là một mảng hoặc tham chiếu đến danh sách các số. : Có nghĩa là số chỉ rõ cách xếp hạng. (Order có 2 giá trị là giá trị 0 và giá trị 1).
Hàm RANK và chúng tôi sẽ cho kết quả giống nhau. Ở đây hàm chúng tôi được hiểu theo nghĩa là thứ hạng trung bình, tức là nếu trong trường hợp có sự đồng hạng (thứ hạng ngang nhau) thì sẽ xếp vị trí trung bình cho số hạng đó (2 người cùng đồng hạng thì thứ hạng trung bình là + 0,5 ; 10 người đồng hạng thì thứ hạng trung bình là + 0,1) (AVG là kí hiệu viết tắt của Average)
Hàm chúng tôi sẽ xếp đồng hạng mà bạn không tính trung bình hạng, tức là nếu 2 người đồng hạng thì sẽ được sắp xếp cùng vào 1 thứ hạng được làm tròn, chứ không phải thứ hạng lẻ. (EQ là viết tắt của Equal)