Công thức RANK trong Excel: Hướng dẫn toàn diện kèm ví dụ

Xếp hạng giúp bạn so sánh giá trị và nhận diện mẫu hình trong bộ dữ liệu. Excel cung cấp các công cụ mạnh mẽ như RANK(), RANK.EQ() và RANK.AVG() để việc xếp hạng trở nên dễ dàng hơn. Các hàm này hữu ích trong nhiều tình huống, từ xếp hạng thí sinh trong giải đấu thể thao theo điểm số đến phân tích xu hướng thị trường dựa trên dữ liệu doanh số đã được xếp hạng.

Trong bài viết này, chúng ta sẽ khám phá cả ba hàm xếp hạng trong Excel và giải thích cách sử dụng với các ví dụ thực hành. Chúng ta cũng sẽ tìm hiểu cách kết hợp hàm xếp hạng với các hàm Excel khác và cách tính percentile rank.

Câu trả lời nhanh: Cách tính xếp hạng trong Excel

Để tính xếp hạng trong bộ dữ liệu bạn muốn, hãy dùng công thức sau:

=RANK(number, ref, [order])

Tìm hiểu hàm RANK() trong Excel

Giả sử bạn tổ chức một cuộc thi cho học sinh và giờ muốn xếp hạng hàng nghìn học sinh dựa trên thành tích của họ.

Bạn có định dành hàng giờ để sắp xếp dữ liệu không? Không, vì Excel có thể giúp. Trong số nhiều hàm mạnh mẽ của Excel có hàm RANK(), cho phép bạn xác định thứ hạng của ai đó chỉ với một thao tác.

Để tính thứ hạng của một giá trị trong một phạm vi, nhập công thức:

RANK(number, ref, [order])

Trong công thức trên:

• number: là số bạn muốn xếp hạng.
• ref: là phạm vi số dùng để xếp hạng.
• order: xác định thứ tự xếp hạng. Tăng dần dùng “1”; giảm dần dùng “0” (mặc định).

Cách dùng công thức RANK() trong Excel

Hãy áp dụng hàm RANK() vào các ví dụ thực tế:

Xếp hạng điểm số học sinh

Việc tính điểm sẽ rất khó và tốn thời gian nếu bạn có hàng nghìn học sinh. Giả sử bạn đang chuẩn bị kết quả cuối kỳ và muốn xếp hạng theo điểm số.

Để làm vậy, trước tiên hãy chuẩn bị bộ dữ liệu.

Bộ dữ liệu ví dụ về học sinh trong Excel. Nguồn: Tác giả.

Nhập công thức =RANK(B3,($B$3:$B$7)) vào một ô rồi kéo xuống ô có dữ liệu cuối cùng để nhận thứ hạng.

Dùng hàm RANK() để tính xếp hạng của học sinh. Nguồn: Tác giả.

Giờ bạn đã có thứ hạng của học sinh chỉ với một bước.

Nhưng bạn có để ý rằng hai học sinh có cùng điểm đều ở hạng 4? Học sinh tiếp theo, với 23 điểm, nhảy lên hạng 6, bỏ qua hạng 5. Điều này nghĩa là công thức gán cùng thứ hạng cho điểm trùng lặp.

Điều này có thể làm xáo trộn thứ tự khi điểm sát nhau. Bạn có thể phá vỡ các trường hợp hòa này bằng hàm COUNTIF() . Nhập công thức sau:

=RANK(B3,($B$3:$B$8)) + COUNTIF($B$3:B3, B3)-1

Hàm COUNTIF() điều chỉnh thứ hạng cho giá trị trùng. Nó đếm số lần điểm của học sinh hiện tại xuất hiện phía trên trong danh sách.

Phá vỡ trường hợp hòa bằng hàm COUNTIF() trong Excel. Nguồn: Tác giả.

Giá trị 28 xuất hiện hai lần và COUNTIF() theo dõi số lần xuất hiện. Con số này được dùng để điều chỉnh thứ hạng. Công thức cộng số lần xuất hiện trừ một vào thứ hạng ban đầu. Nhờ vậy, mỗi giá trị trùng nhận một thứ hạng duy nhất.

Xếp hạng doanh số trong một tập dữ liệu

Xem thêm một ví dụ khác để minh họa cách dùng hàm RANK() của Excel theo thứ tự tăng hoặc giảm.

Nếu bạn muốn đánh giá hiệu suất đội ngũ bán hàng, bạn có thể xếp hạng doanh số hàng tháng của họ bằng hàm RANK() trong Excel.

Ở đây, chúng ta có bộ dữ liệu doanh số hàng tháng cho từng nhân viên bán hàng. Chúng ta sẽ dùng hàm RANK() để xếp hạng theo người mang về doanh số cao nhất (từ cao xuống thấp).

Danh sách doanh số cần xếp hạng. Nguồn: Tác giả.

Để xếp hạng doanh số theo thứ tự tăng dần, chúng tôi áp dụng công thức =RANK(B2, ($B$2:$B$6), 1). Ở đây, giá trị “1” cho tham số order chỉ định xếp hạng theo thứ tự tăng dần.

Xếp hạng các giá trị theo thứ tự tăng dần. Nguồn: Tác giả.

Sau khi nhập công thức, chỉ cần giữ chuột, kéo góc ô xuống dưới và áp dụng cho phần còn lại của dữ liệu doanh số.

Tương tự, nếu muốn xếp hạng theo thứ tự giảm dần, đặt “0” làm giá trị tham số order trong công thức như sau: =RANK(B2, ($B$2:$B$6), 0)

Bạn có thể thấy sự khác biệt trong kết quả:

Xếp hạng các giá trị theo thứ tự giảm dần. Nguồn: Tác giả.

Bạn có thể nhận thấy dấu đô la trong tham chiếu ($B$2:$B$6). Đây gọi là tham chiếu tuyệt đối. Chúng đảm bảo công thức luôn tham chiếu cùng một phạm vi dữ liệu doanh số, ngay cả khi bạn sao chép xuống, để tránh lỗi.

Nếu bạn không dùng tham chiếu tuyệt đối, như =RANK(A2, A2:A10), phạm vi sẽ thay đổi khi bạn kéo xuống:

• Công thức ở G3: =RANK(G3,(G3:G7), 1)
• Công thức ở G4: =RANK(G4,(G4:G8), 1)

Để tránh vấn đề này, hãy dùng tham chiếu tuyệt đối bằng cách nhấn “F4”. Khi đó công thức sẽ là: =RANK(G3,($G$3:$G$7), 1)

Khi bạn kéo công thức trên xuống ô cuối cùng có dữ liệu, tham chiếu tương đối sẽ thay đổi theo vị trí từng ô. Bạn có thể thấy sự khác biệt trong kết quả khi dùng tham chiếu tuyệt đối:

Khác biệt giữa xếp hạng có và không có tham chiếu tuyệt đối. Nguồn: Tác giả.

Các hàm xếp hạng nâng cao trong Excel: RANK.EQ() và RANK.AVG()

Khi gặp giá trị trùng trong Excel, mọi thứ có thể trở nên rắc rối. Đó là lúc tôi ưu tiên dùng các hàm RANK.EQ() và RANK.AVG(). Cả hai xử lý hơi khác nhau. Hãy xem chúng hoạt động thế nào.

Hàm RANK.EQ() trong Excel

RANK.EQ() hoạt động tương tự như hàm RANK() nhưng có tên gọi chính xác hơn. Khi có giá trị trùng, RANK.EQ() gán cùng thứ hạng cho tất cả và bỏ qua (nhảy qua) thứ hạng tiếp theo.

Cú pháp: =RANK.EQ(number, ref, [order])

Trong công thức trên:

• number: là số bạn muốn xếp hạng.
• ref: là phạm vi số dùng để xếp hạng.
• order: xác định thứ tự xếp hạng. Tăng dần dùng “1”; giảm dần dùng “0” (mặc định).

Hãy hiểu công thức này qua ví dụ: Tôi có bộ dữ liệu dân số của một số quốc gia và muốn xếp hạng các quốc gia dựa trên dân số.

Đây là cách tôi thực hiện bằng hàm RANK.EQ():

Sử dụng hàm RANK.EQ(). Nguồn: Tác giả.

Trong hình trên, quốc gia có dân số cao nhất được xếp hạng 1, v.v. Giờ bạn thấy cách RANK.EQ() xử lý trường hợp hòa.

Vì Mỹ và Indonesia có dân số bằng nhau, họ cùng ở hạng 2. Điều này cho thấy RANK.EQ() gán cùng thứ hạng cho giá trị trùng và bỏ qua thứ hạng kế tiếp trong chuỗi.

Hàm RANK.AVG() trong Excel

Hàm RANK.AVG() tiếp cận khác khi xử lý hòa trong xếp hạng. Thay vì gán cùng thứ hạng cho tất cả giá trị trùng, nó tính trung bình các thứ hạng mà từng giá trị sẽ nhận được nếu xét riêng lẻ.

Cú pháp: RANK.AVG(number, ref, [order])

Trong công thức trên:

• number: là số bạn muốn xếp hạng.
• ref: là phạm vi số dùng để xếp hạng.
• order: xác định thứ tự xếp hạng. Tăng dần dùng “1”; giảm dần dùng “0” (mặc định).

Hiểu bằng ví dụ: Tôi có bộ dữ liệu điểm số, nên áp dụng hàm RANK.AVG() để xem nó xử lý điểm hòa thế nào.

Sử dụng hàm RANK.AVG(). Nguồn: Tác giả.

Với =RANK.AVG(D3,$D$3:$D$6), điểm 88 nhận thứ hạng 2,5. Tức là hàm lấy trung bình các thứ hạng sẽ được gán nếu xét riêng.

Cách này giúp phân bố thứ hạng cân bằng hơn, đặc biệt với dữ liệu có nhiều trường hợp hòa.

Kết hợp RANK() với các hàm Excel khác

Kết hợp hàm RANK() với các hàm khác của Excel hữu ích trong một số kịch bản. Hãy xem vài trường hợp phổ biến.

Kết hợp RANK() và ROUNDUP() để phân loại dữ liệu

Đây là cách bạn có thể kết hợp hàm ROUNDUP() với hàm RANK():

Kết hợp các hàm RANK() và ROUNDUP() trong Excel. Nguồn: Tác giả.

Tôi dùng công thức =ROUNDUP(RANK(B2, $B$2:$B$7, 1)/3, 0), công thức này tính thứ hạng của giá trị ở ô B2 trong phạm vi B2 theo thứ tự tăng dần. Sau đó chia thứ hạng này cho ba và làm tròn lên đến số nguyên gần nhất.

Với công thức trên, bạn phân loại dữ liệu thành các nhóm/tầng riêng biệt, giúp nhận diện và phân tích các mức hiệu suất khác nhau hoặc phục vụ các phân tích tiếp theo.

Kết hợp RANK.EQ() và COUNTIFS() để phá vỡ thế hòa

Chúng ta đã bàn về vài cách xử lý hòa bằng hàm RANK() của Excel. Tuy nhiên, giả sử bạn muốn xếp hạng sản phẩm chỉ dựa trên giá. Điều này có thể thiếu khách quan khi nhiều mặt hàng cùng giá.

Vì vậy, hãy đưa vào tiêu chí phụ để phá vỡ thế hòa và tạo hệ thống xếp hạng khách quan hơn.

Xét danh sách khách hàng mua tai nghe. Ta muốn xếp hạng theo giá (tiêu chí chính), rồi dùng đánh giá của khách hàng (tiêu chí phụ) để phân biệt khi hòa.

Danh sách khách hàng ví dụ. Nguồn: Tác giả 

Để làm điều này, ta sẽ kết hợp hàm RANK.EQ() với hàm COUNTIFS(). Cách làm như sau:

=RANK.EQ($B2,$B$2:$B$9) + COUNTIFS($B$2:$B$9, $B2, $C$2:$C$9, ">&"& $C2)

Tính xếp hạng bằng RANK.EQ() và COUNTIFS(). Nguồn: Tác giả.

Phần COUNTIFS($B$2:$B$9,$B2) đếm số lần giá hiện tại xuất hiện trong danh sách. Ta dùng tham chiếu tuyệt đối cho phạm vi $B$2:$B$9 để nhất quán, nhưng giữ ô tiêu chí $B2 là tương đối để công thức kiểm tra từng hàng riêng biệt.

Phần thứ hai, $C$2:$C$9,">"&$C2, tìm số lượng đánh giá của khách hàng cao hơn đánh giá hiện tại ở C2.

COUNTIFS() tuân theo logic “AND”: chỉ đếm các ô thỏa tất cả điều kiện. Nếu không có tai nghe nào cùng giá có đánh giá cao hơn, COUNTIFS() trả về 0. Khi đó thứ hạng do RANK.EQ() xác định không thay đổi.

Tính percentile rank trong Excel

Vì hàm RANK() xếp hạng một giá trị số, bạn có thể dùng công thức percentile rank trong Excel để tính thứ hạng của giá trị trong bộ dữ liệu dưới dạng phần trăm của toàn bộ tập dữ liệu.

Excel cung cấp hai hàm để tính percentile rank. Hãy cùng tìm hiểu cả hai.

Hàm PERCENTRANK.INC() trong Excel

PERCENTRANK.INC() tính percentile rank của một giá trị, bao gồm cả giá trị nhỏ nhất và lớn nhất trong tập dữ liệu.

Hiểu hàm này qua ví dụ: Tôi có bộ dữ liệu điểm thi của 10 học sinh và muốn tính percentile rank cho từng điểm, bao gồm cả giá trị nhỏ nhất và lớn nhất.

Vì vậy, tôi dùng công thức =PERCENTRANK.INC($A$2:$A$11, A2):

Tính xếp hạng bằng hàm PERCENTRANK.INC(). Nguồn: Tác giả

Trong ví dụ trên, bạn có thể thấy hàm PERCENTRANK.INC() bao gồm cả giá trị nhỏ nhất và lớn nhất trong tập. Điểm 45 (nhỏ nhất) có percentile rank là 0, và điểm 90 (lớn nhất) có percentile rank là 1.

Hàm PERCENTRANK.EXC() trong Excel

PERCENTRANK.EXC() tính percentile rank của một giá trị, loại trừ giá trị nhỏ nhất và lớn nhất trong tập dữ liệu.

Hiểu hàm này qua ví dụ: Tôi áp dụng hàm PERCENTRANK.EXC() trên cùng bộ dữ liệu học sinh từ phần trước:

Tính xếp hạng bằng hàm PERCENTRANK.EXC(). Nguồn: Tác giả

Trong ví dụ trên, hàm PERCENTRANK.EXC() loại trừ giá trị nhỏ nhất và lớn nhất trong tập. Nghĩa là điểm 45 (nhỏ nhất) không có percentile rank là 0, và điểm 90 (lớn nhất) không có percentile rank là 1.

Các lỗi thường gặp với hàm RANK() trong Excel và cách tránh

Các hàm Excel rất tiện và dễ dùng cho công việc. Nhưng đôi khi, ngay cả người dùng thành thạo cũng có thể mắc vài lỗi phổ biến. Vì vậy, tôi chia sẻ một số lỗi thường gặp và cách tránh:

Sao chép-dán công thức

Khi bạn sao chép công thức xếp hạng giữa các ô, hãy đảm bảo không vô tình dùng tham chiếu tuyệt đối không đúng chỗ. Mỗi ô có thể tham chiếu đến phần dữ liệu khác nhau và làm sai thứ hạng.

Để tránh điều này, hãy dùng tham chiếu tuyệt đối, như $A$1:$A$10, khi thiết lập công thức để cố định đúng phạm vi cho mọi phép xếp hạng.

Không xử lý giá trị trùng lặp

Giả sử có hai nhân viên bán hàng hòa nhau ở mức doanh số cao nhất. Cách bạn xử lý hòa có thể tạo khác biệt lớn: các hàm RANK() và RANK.EQ() xử lý hòa tương tự nhau.

Vì vậy, bạn nên quyết định liệu giá trị hòa nên có cùng thứ hạng hay thứ hạng khác, hoặc thứ hạng nên được lấy trung bình bằng RANK.AVG(). Sau đó dùng đúng hàm tương ứng.

Sai thứ tự sắp xếp

Nghe có vẻ đơn giản, nhưng quên thiết lập đối số order trong hàm có thể khiến xếp hạng đi sai hướng (tăng hoặc giảm).

Vì vậy, hãy luôn kiểm tra kỹ đối số này: dùng “0” (hoặc để trống) cho thứ tự giảm dần và “1” cho thứ tự tăng dần. Đảm bảo thứ tự phù hợp với cách bạn muốn xếp hạng dữ liệu.

Dùng sai hàm

Khi làm việc với các bộ dữ liệu khác nhau, việc chọn đúng hàm xếp hạng rất quan trọng vì dùng sai hàm sẽ cho kết quả không như mong muốn.

Để tránh vấn đề này, hãy dùng RANK.EQ() cho xếp hạng tiêu chuẩn và RANK.AVG() cho các tình huống mà cách xử lý hòa ảnh hưởng đến kết quả, như phân tích thống kê.

Dùng phạm vi lỗi thời

Khi dữ liệu tăng lên, bạn phải cập nhật phạm vi trong công thức. Nếu không, các điểm dữ liệu mới sẽ không được đưa vào xếp hạng và bạn có thể bối rối vì sao kết quả không đúng.

Vì vậy, hãy thường xuyên cập nhật và kiểm tra phạm vi trong công thức. Điều này đảm bảo tất cả dữ liệu quan trọng đều được xem xét khi xếp hạng.

Kết luận

Giờ bạn đã biết cách dùng các hàm như RANK(), RANK.EQ() và RANK.AVG() để xử lý trường hợp hòa và giải quyết nhiều bài toán xếp hạng. Các hàm này hữu ích trong nhiều kịch bản, như phân tích điểm số học sinh hoặc sắp xếp doanh số.

Nếu bạn muốn nâng cao kỹ năng Excel hơn nữa, tôi rất khuyến khích tham khảo một số khóa học và tài nguyên tuyệt vời trên Datacamp:

• Bắt đầu với khóa Introduction to Excel để xây nền tảng, sau đó học tiếp Data Analysis in Excel và Excel Fundamentals.
• Cân nhắc khóa Data Visualization in Excel để nâng cao khả năng trực quan hóa dữ liệu.
• Khóa Introduction to PowerQuery in Excel rất hữu ích cho thao tác dữ liệu nâng cao.
• Ngoài ra, để chính thức hóa chuyên môn Excel, hãy xem hướng dẫn toàn diện về Cách đạt chứng chỉ Microsoft Excel.