Sử dụng hàm RANK để so sánh các số với các số khác trong cùng một danh sách. Xem video và nhận sổ làm việc RANK miễn phí
Cách sử dụng chức năng RANK
Sử dụng hàm trang tính RANK để so sánh các số với các số khác trong cùng một danh sách. Xem các bước trong video này và hướng dẫn bằng văn bản ở bên dưới video
Cách sử dụng chức năng RANK
Nếu bạn cung cấp cho hàm RANK một số và một danh sách các số, nó sẽ cho bạn biết thứ hạng của một số trong danh sách, theo thứ tự tăng dần hoặc giảm dần
Ví dụ, trong ảnh chụp màn hình bên dưới, có danh sách điểm 10 bài kiểm tra của học sinh, tại ô B2. B11
Để tìm thứ hạng của điểm của học sinh đầu tiên trong ô B2, hãy nhập công thức này vào ô C2
=RANK[B2,$B$2. $B$11]
Sau đó, sao chép công thức từ ô C2 xuống ô C11 và điểm số sẽ được xếp theo thứ tự giảm dần
Đối số cú pháp hàm RANK
Có 3 đối số cho cú pháp hàm RANK
- con số. trong ví dụ trên, số để xếp hạng nằm trong ô B2
- giới thiệu. Chúng tôi muốn so sánh số với danh sách các số trong ô $B$2. $B$11. Sử dụng tham chiếu tuyệt đối [$B$2. $B11], thay vì tham chiếu tương đối [B2. B11] nên phạm vi được tham chiếu sẽ giữ nguyên khi bạn sao chép công thức xuống các ô bên dưới
- trật tự. [tùy chọn] Đối số thứ ba này cho Microsoft Excel biết xếp hạng danh sách theo thứ tự tăng dần hay giảm dần
- Sử dụng số 0 hoặc để trống đối số này để tìm thứ hạng giá trị số trong danh sách theo thứ tự giảm dần. Trong ví dụ trên, đối số thứ tự được để trống, để tìm thứ hạng theo thứ tự giảm dần.
=RANK[B2,$B$2. $B$11] - Đối với thứ tự tăng dần, hãy nhập 1 hoặc bất kỳ số nào khác trừ số 0.
Nếu bạn đang so sánh điểm số chơi gôn, bạn có thể nhập số 1 để xếp hạng theo thứ tự tăng dần.
=RANK[B2,$B$2. $B$11,1]
- Sử dụng số 0 hoặc để trống đối số này để tìm thứ hạng giá trị số trong danh sách theo thứ tự giảm dần. Trong ví dụ trên, đối số thứ tự được để trống, để tìm thứ hạng theo thứ tự giảm dần.
Thứ tự chức năng RANK
Trong hàm RANK, đối số thứ 3 [thứ tự], là tùy chọn. Đối số thứ tự cho Excel biết xếp hạng danh sách theo thứ tự tăng dần hay giảm dần
Thứ tự giảm dần
Nếu bạn sử dụng số 0 làm cài đặt cho thứ tự hoặc nếu bạn không sử dụng đối số thứ 3, thứ hạng sẽ được đặt theo thứ tự giảm dần
- Số lớn nhất được xếp hạng 1
- Số lớn thứ 5 có số hạng là 5
Thứ tự tăng dần
Nếu bạn sử dụng 1 làm cài đặt cho thứ tự hoặc nếu bạn nhập bất kỳ số nào trừ số 0 làm đối số thứ 3, thứ hạng sẽ được đặt theo thứ tự tăng dần
- Số bé nhất được xếp hạng 1
- Số nhỏ thứ 5 có số hạng là 5
Công thức linh hoạt
Thay vì nhập số đối số thứ tự vào công thức RANK, hãy sử dụng tham chiếu ô để tạo công thức linh hoạt
Ví dụ: nhập 1 vào ô E1 và liên kết đến ô E1 cho đối số thứ tự
GHI CHÚ. Đảm bảo sử dụng tham chiếu tuyệt đối [$E$1], nếu công thức sẽ được sao chép xuống các hàng khác. Nếu bạn sử dụng tham chiếu tương đối [E1], tham chiếu sẽ thay đổi trong mỗi hàng
=RANK[B2,$B$2. $B$6,$E$1]
Bằng cách liên kết đến một ô, bạn có thể nhanh chóng xem các kết quả khác nhau mà không cần thay đổi công thức. Nhập số 0 vào ô E1 hoặc xóa số và thứ hạng sẽ thay đổi thành Thứ tự giảm dần
Sử dụng hộp kiểm
Đối với tùy chọn thứ tự, chỉ có 2 lựa chọn - Tăng dần hoặc Giảm dần. Để giúp mọi người thay đổi thứ tự dễ dàng hơn, hãy sử dụng hộp kiểm để BẬT hoặc TẮT thứ tự tăng dần
- Nếu nó được BẬT, thứ tự RANK sẽ tăng dần
- Nếu TẮT thì thứ tự RANK sẽ giảm dần
Trong tệp mẫu hàm RANK, có một ví dụ về hộp kiểm trên trang tính RankOrderCheck. Để biết hướng dẫn về cách thêm hộp kiểm và sử dụng hộp kiểm đó trong công thức, hãy xem bài đăng trên blog của tôi, Sử dụng kết quả hộp kiểm trong công thức Excel
Thêm ví dụ về công thức linh hoạt
Để biết thêm ví dụ về công thức linh hoạt, hãy xem
Quan hệ chức năng RANK
Điều gì xảy ra với thứ hạng nếu một số điểm bằng nhau?
- Trong ví dụ trên, hai điểm cuối cùng trong danh sách giống nhau -- 38. Hai học sinh Ivy và Joe đều xếp thứ 4
- Điểm cao nhất tiếp theo -- điểm của Ed là 36 -- được xếp hạng thứ 6, không phải thứ 5, vì có 5 học sinh đứng trước anh ấy
Nếu bạn phải phá vỡ các ràng buộc hoặc trao tiền thưởng dựa trên các ràng buộc, có các ví dụ bên dưới, cho biết cách giải quyết các yêu cầu đó
Phá vỡ mối quan hệ với chức năng RANK
Chia tiền thắng cho Xếp hạng bị ràng buộc
Phá vỡ mối quan hệ với chức năng RANK
Trong một số trường hợp không được thắt cà vạt nên bạn phải tìm cách phá cà vạt
Xem video này để biết các bước và hướng dẫn bằng văn bản ở bên dưới video
Phá vỡ mối quan hệ với chức năng RANK
Trong một số trường hợp không được thắt cà vạt nên bạn phải tìm cách phá cà vạt
Trong ví dụ này, bạn có thể theo dõi số phút mà mỗi học sinh làm bài kiểm tra và sử dụng thời gian đó để phá vỡ mọi ràng buộc. Nếu điểm số bằng nhau, học sinh nào có ít thời gian hơn để hoàn thành bài kiểm tra sẽ xếp trước học sinh khác có cùng số điểm
Tính số thập phân cho các điểm bằng nhau
Tôi đã thêm Thời gian kiểm tra vào cột C và công thức TieBreak vào cột E
=IF[COUNTIF[$B$2. $B$11,B2]>1,
RANK[C2,$C$2. $C$11,1]/100,0]
Công thức Tie Break hoạt động như thế nào
Công thức Tie Break sử dụng các hàm COUNTIF và RANK, được bao bọc bởi một hàm IF, để xem liệu có nên thêm một số thập phân phá vỡ tie vào Xếp hạng ban đầu hay không
- Đầu tiên, công thức TieBreak kiểm tra xem NẾU có nhiều hơn một trường hợp của số trong toàn bộ danh sách.
IF[COUNTIF[$B$2. $B$11,B2]>1 - Nếu có nhiều hơn một phiên bản, nó sẽ xếp hạng Thời gian theo thứ tự tăng dần, vì thời gian càng thấp thì càng tốt.
XẾP HẠNG[C2,$C$2. $C$11,1] - Tiếp theo, nó chia số tiền đó cho 100, để có số thập phân. Sau đó, bạn sẽ thêm số thập phân này vào Xếp hạng ban đầu.
Lưu ý. Số chia, 100, có thể được thay đổi thành một số khác, nếu bạn đang làm việc với một danh sách dài hơn.
/100 - Cuối cùng, để hoàn thành chức năng IF, nếu chỉ có một trường hợp a a Xếp hạng, kết quả cho TieBreak bằng 0.
,0]
Tính thứ hạng cuối cùng
Sau khi tính toán số thập phân hòa, bạn có thể thêm kết quả hàm RANK vào kết quả TieBreak để có được thứ hạng cuối cùng
Trong ví dụ này, hai học sinh được xếp ở vị trí thứ 4. Joe mất 27 phút để hoàn thành bài kiểm tra và Thời gian của anh xếp thứ 5. Ivy mất 29 phút để hoàn thành bài thi và Time của cô xếp thứ 9
Công thức Tie Break thêm một số thập phân là 0. 09 đến điểm của Ivy, và 0. 05 đến điểm của Joe. Trong bảng xếp hạng cuối cùng, Joe, với 4. Cao hơn Ivy 05 bậc, với 4. 09
Chia tiền thắng cho Xếp hạng bị ràng buộc
Trong một giải đấu, thay vì phá hòa, bạn có thể muốn chia tiền thắng cược cho bất kỳ người chơi hòa nào, nếu bạn đang trao giải thưởng tiền mặt hoặc điểm. Để xem các bước chia tiền thắng cược, hãy xem video ngắn này. Hướng dẫn bằng văn bản ở bên dưới video
Chia tiền thắng cho Xếp hạng bị ràng buộc
Trong một giải đấu, thay vì phá hòa, bạn có thể muốn chia tiền thắng cược cho bất kỳ người chơi hòa nào, nếu bạn đang trao giải thưởng tiền mặt hoặc điểm. Nếu 2 hoặc nhiều người chơi có cùng thứ hạng, họ sẽ chia số tiền thưởng có sẵn cho thứ hạng đó, xuống thứ hạng tiếp theo
Dưới đây là bảng giải thưởng mẫu, thể hiện số tiền được thưởng cho mỗi thứ hạng. Trong ví dụ này, nếu 3 người chơi ở hạng 1, họ sẽ chia tổng số tiền [10+9+8=27] cho các hạng 1, 2 và 3
Mỗi người trong số 3 người chơi ở hạng 1 kiếm được 9 [27/3 = 9] và người chơi có số điểm cao nhất tiếp theo sẽ được xếp hạng 4 và kiếm được 7
Tính số tiền chia
Để chia số tiền thưởng cho những người chơi bằng điểm, công thức Giải thưởng sử dụng hàm AVERAGE, với hàm OFFSET tìm phạm vi ô lấy trung bình. Công thức này được nhập vào ô D2 và sao chép xuống ô D11
=AVERAGE[OFFSET[$K$1,C2,0, COUNTIF[$C$2. $C$11,C2]]]
Công thức giải thưởng hoạt động như thế nào
Công thức Giải thưởng sử dụng hàm AVERAGE, với hàm OFFSET tìm phạm vi ô tính trung bình
- Hàm AVERAGE sẽ tính toán số tiền cho mỗi người chơi, dựa trên một phạm vi ô cụ thể.
TRUNG BÌNH[ - Hàm OFFSET trả về phạm vi với số tiền sẽ sử dụng cho giá trị trung bình.
OFFSET[ - Trong công thức OFFSET, đối số đầu tiên là ô tham chiếu. Trong ví dụ này, đó là ô K1 -- tiêu đề cho cột Số tiền thưởng.
$K$2, - Trong công thức OFFSET, đối số thứ 2 là số hàng từ ô tham chiếu trở xuống mà các ô tính trung bình bắt đầu. Thứ hạng được liệt kê theo thứ tự tăng dần, vì vậy đối với Xếp hạng là 1, các ô tính trung bình sẽ bắt đầu 1 hàng trở xuống từ ô Tham chiếu của $K$1. Thứ hạng của người chơi đầu tiên nằm trong ô C2, vì vậy hãy tham khảo thứ hạng đó trong công thức
C2, - Trong công thức OFFSET, đối số thứ 3 là số cột ở bên phải của ô tham chiếu, mà các ô được tính trung bình bắt đầu. Bạn muốn tìm số tiền trong cùng một cột, vì vậy số này bằng 0
0, - Trong công thức OFFSET, đối số thứ 4 là số hàng cần đưa vào phạm vi. Đây phải là số lượng người chơi bị ràng buộc ở thứ hạng đó. Hàm COUNTIF sẽ đếm các trường hợp xếp hạng trong cột C, bằng với xếp hạng trong C2
COUNTIF[$C$2. $C$11,C2]
Công thức RANK IF
Thay vì sử dụng hàm RANK để so sánh một số với toàn bộ danh sách các số, bạn có thể cần xếp hạng một giá trị trong một tập con số cụ thể. Ví dụ: xếp hạng doanh số bán hàng của mỗi ngày so với các ngày khác trong cùng một tuần
Trong ảnh chụp màn hình bên dưới, có các bản ghi bán hàng trong hai tuần
- Ngày 2 tháng 1 và ngày 4 tháng 1 có doanh số cao nhất trong tuần 1, vì vậy chúng sẽ có thứ hạng 1
- Trong tuần 2, ngày 10 tháng 1 có doanh số cao nhất, vì vậy nó sẽ có thứ hạng 1 cho tuần đó
Không có chức năng RANKIF
Không có hàm RANKIF nhưng bạn có thể sử dụng hàm COUNTIFS để tính thứ hạng dựa trên các mục có cùng số tuần
Nhập công thức này vào ô D2 và sao chép nó xuống hàng cuối cùng có dữ liệu
=COUNTIFS[[Wk], [@Wk], [Doanh số], ">"&[@Sales]]+1
Làm thế nào nó hoạt động
Tiêu chí đầu tiên trong công thức kiểm tra doanh số bán hàng khác có cùng số tuần
=COUNTIFS[[Wk], [@Wk]
Tiêu chí thứ 2 tìm mặt hàng có số lượng lớn hơn ở cột Doanh số
[Bán hàng],">"&[@Bán hàng]]
Sau đó, 1 được thêm vào số đó, để có được thứ hạng
+1
Ví dụ: trong tuần 1, hãy xem doanh số bán hàng cho ngày 3 tháng 1 -- 237
- Có 2 ngày có doanh số lớn hơn trong tuần 1 -- ngày 2 tháng 1 và ngày 4 tháng 1
- Thêm 1 vào số đó và ngày 3 tháng 1 có thứ hạng là 3
Lấy tệp mẫu
Lấy tệp Hàm Excel RANK mẫu đã nén. Tệp có định dạng xlsx và không chứa macro