Trong bài viết này, blog.hocexcel.online sẽ hướng dẫn các bạn tìm hiểu sâu hơn về hàm RANK trong các phiên bản Office từ 2010 trở lên, và việc làm thế nào để xếp hạng theo một hoặc nhiều điều kiện.
Bạn đang xem: Hàm rank có điều kiện trong excel
Những nội dung chính:
Phần 1. Hàm xếp hạng trong các phiên bản office từ 2010 trở lên được thay đổi từ hàm RANK sang hàm RANK.AVG và hàm RANK.EQ
Phần 2. Hàm RANK / RANK.AVG / RANK.EQ đều không hỗ trợ xếp hạng theo điều kiện. Muốn xếp hạng theo điều kiện cần kết hợp thêm những bước tính toán khác.
Phần 3. Cách xây dựng công thức xếp hạng theo một điều kiện, hoặc theo nhiều điều kiện (theo thứ tự ưu tiên)
Để dễ hình dung, chúng ta sẽ áp dụng bài tập sau để làm ví dụ minh họa:
Dù đã có phần mềm, nhưng kỹ năng Excel vẫn cực kỳ quan trọng với kế toán, bạn đã vững Excel chưa? Hãy để tôi giúp bạn, đăng ký khoá học Excel:
Sau đây chúng ta bắt đầu tìm hiểu các nội dung:
Phần 1: Phân biệt các hàm RANK / RANK.AVG / RANK.EQ
Bắt đầu từ phiên bản 2010, trong excel xuất hiện 2 hàm thay thế cho hàm RANK là hàm RANK.AVG và hàm RANK.EQ. Các phiên bản trước đó (2003, 2007) chỉ có hàm RANK mà thôi.
Về cú pháp, 3 hàm này đều có cú pháp giống nhau
CÚ PHÁP=RANK( number, ref,
=RANK.AVG( number, ref,
=RANK.EQ( number, ref,
Trong đó:
– Number: Đối số mà bạn muốn tìm thứ hạng cho nó.– Ref: Một mảng hoặc tham chiếu đến danh sách các số.– Order: Số chỉ rõ cách xếp hạng. (Order có 2 giá trị là 0 và 1).
*Lưu ý: Nếu Order=0 thì xếp hạng theo thứ tự giảm dần, order =1 thì xếp hàng theo thứ tự tăng dần.
Chúng ta xét việc sử dụng 3 hàm này vào bài tập và xem kết quả thu được thế nào nhé: (Xếp theo thứ tự giảm dần)
Hàm RANK và RANK.AVG cho kết quả giống nhau. Ở đây hàm RANK.AVG nghĩa là thứ hạng trung bình, tức là nếu có sự đồng hạng (thứ hạng ngang nhau) thì sẽ xếp trung bình cho số hạng đó (2 người đồ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à viết tắt của Average)
Hàm RANK.EQ sẽ xếp đồng hạng mà không tính trung bình hạng, tức là nếu 2 người đồng hạng thì sẽ 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)
Để có thể ứng dụng việc xếp hạng của các hàm này vào việc xếp hạng theo một hoặc nhiều điều kiện, mời các bạn đón xem ở những phần sau của bài viết này. Mục tiêu của chúng ta là bảng kết quả sau:
Phần 2: Cách phân biệt những người đồng hạng theo 1 tiêu chuẩn ưu tiên (xếp hạng theo một điều kiện)
Theo bài tập ví dụ ở trên, chúng ta có thể xác định yêu cầu ở đây như sau:
Xếp hạng theo điểm trung bình, nếu đồng hạng thì ưu tiên những ai có điểm toán cao hơn sẽ xếp hạng cao hơn.
Chúng ta cùng phân tích đề bài để hiểu cách làm nhé:
Bước 1: Xét ưu tiên môn toán khi điểm trung bình bằng nhau.
Công thức sử dụng:
=IF(COUNTIF($E$4:$E$13,E4)>1,RANK.EQ(B4,$B$4:$B$13,1),0)
Phân tích công thức:
COUNTIF($E$4:$E$13,E4)>1 Đếm trong vùng E4:E13 theo từng giá trị điểm, xét điểm nào có kết quả đếm lớn hơn 1 (tức là có điểm trùng)
RANK.EQ(B4,$B$4:$B$13,1) Khi điểm trung bình bị trùng, thì ta sẽ xếp hạng theo điểm toán, thứ tự tăng dần (ai điểm thấp nhất sẽ xếp trước)
=> Công thức được hiểu là: nếu điểm trung bình mà trùng, thì tại những người bị trùng đó sẽ xếp hạng họ theo điểm môn toán, theo thứ tự tăng dần.
* Lưu ý ở đây: Tại sao thứ tự ưu tiên lại xếp tăng dần mà không phải giảm dần.
Bởi vì như vậy ai điểm càng cao thì thứ tự càng là số lớn => Số thứ hạng này cũng chính là số sẽ được cộng thêm vào điểm gốc để làm căn cứ xếp hạng với điều kiện ưu tiên => Số điểm càng cao thì điểm cộng càng nhiều
Bước 2: Tính lại điểm trung bình khi có điểm ưu tiên
Vì điểm trung bình có 2 số sau dấu phẩy nên điểm ưu tiên sẽ tính sau dấu phẩy 3 chữ số (chia thứ hạng ở cột ưu tiên cho 1000)
Khi đó ta sẽ được điểm trung bình mới bao gồm điểm trung bình cũ + điểm xếp hạng với những điểm trùng mà xét ưu tiên với điểm toán
Điểm xếp hạng ưu tiên này nên đặt phía sau điểm gốc để việc sắp xếp thứ hạng tốt hơn và dễ phân biệt hơn.
Bước 3: Xếp hạng dựa trên điểm trung bình mới
Hàm xếp hạng ở đây chúng ta sử dụng hàm RANK.EQ để xếp hạng cho điểm trung bình mới.
Phần 3. Cách xây dựng công thức xếp hạng theo một điều kiện, hoặc theo nhiều điều kiện (theo thứ tự ưu tiên)
Sau khi hiểu cách xếp hạng theo một điều kiện, chúng ta có thể áp dụng cách đó cho nhiều điều kiện.
Với trường hợp nhiều điều kiện, chúng ta cần quan tâm tới 2 tiêu chí:
Tiêu chí 1: Thứ tự ưu tiên của các điều kiện: điều kiện nào xét trước, điều kiện nào xét sauTiêu chí 2: Công thức xét hạng của các điều kiện tiếp theo sẽ phụ thuộc vào thứ hạng của điều kiện trước đó
Yêu cầu của bài tập ví dụ cho phần xếp hạng theo nhiều điều kiện như sau:
Xếp hạng theo điểm trung bình, nếu đồng hạng thì ưu tiên xét theo thứ tự các môn: Toán > Lý > Hóa
Hướng dẫn học Excel cơ bản
Chúng ta có thể thấy với trường hợp dòng 7 (Dương Quốc Đạt) và dòng 10 (Lê Bá Long) khi xét theo 1 điều kiện thì vẫn đồng xếp hạng 6. Do đó cần xét thêm điều kiện ưu tiên khác.
Chúng ta cùng phân tích cụ thể cách làm nhé:
Bước 1: Xét ưu tiên môn toán khi điểm trung bình bằng nhau (giống ở Phần 2)
Công thức sử dụng:
=IF(COUNTIF($E$4:$E$13,E4)>1,RANK.EQ(B4,$B$4:$B$13,1),0)
Nội dung công thức này đã phân tích trong phần 2, mời các bạn xem lại để hiểu.
Bước 2: Xét ưu tiên môn Lý khi xếp hạng điểm môn Toán bằng nhau (Đồng điểm môn Toán ->theo thứ tự ưu tiên, có tính phụ thuộc vào thứ tự trước đó)
Công thức sử dụng:
=IF(AND(L4>0,COUNTIF(L$4:L$13,L4)>1),RANK.EQ(C4,C$4:C$13,1),0)
Phân tích công thức:
L4>0: Chỉ xét khi xếp hạng ưu tiên môn Toán khác 0 (tức là điểm trung bình ban đầu phải trùng nhau)COUNTIF(L$4:L$13,L4)>1 : Chỉ xét khi xếp hạng ưu tiên môn Toán đồng hạng (đếm kết quả xếp hạng ưu tiên môn Toán nếu xuất hiện nhiều hơn 1 thì tức là có đồng hạng)AND(L4>0,COUNTIF(L$4:L$13,L4)>1): Xét thỏa mãn đồng thời cả 2 điều kiện trên thì mới được tính ưu tiên môn LýRANK.EQ(C4,C$4:C$13,1): Xếp hạng điểm môn Lý theo thứ tự tăng dần để tính điểm ưu tiênBước 3: Xét ưu tiên môn Hóa khi xếp hạng điểm môn Toán và môn Lý bằng nhau (đồng điểm cả môn Toán và môn Lý)
Công thức sử dụng:
=IF(AND(M4>0,COUNTIF(M$4:M$13,M4)>1),RANK.EQ(D4,D$4:D$13,1),0)
Phân tích công thức:
M4>0: Chỉ xét khi xếp hạng ưu tiên môn Lý khác 0 (tức là ãã bao gồm trùng của môn Toán rồi, giờ xét thêm môn Lý có thứ hạng trùng)COUNTIF(M$4:M$13,M4)>1: Chỉ xét khi xếp hạng ưu tiên môn Lý đồng hạng (đếm kết quả xếp hạng ưu tiên môn Lý nếu xuất hiện nhiều hơn 1 thì tức là có đồng hạng)AND(M4>0,COUNTIF(M$4:M$13,M4)>1): Xét thỏa mãn đồng thời cả 2 điều kiện trên thì mới được tính ưu tiên môn HóaRANK.EQ(D4,D$4:D$13,1),0): Xếp hạng điểm môn Hóa theo thứ tự tăng dần để tính điểm ưu tiên
Bước 4: Tính điểm trung bình mới dựa trên điểm ưu tiên của các điều kiện
Ở bước này, các bạn lưu ý là thứ tự ưu tiên của các môn sẽ được cộng vào điểm trung bình, do đó mỗi cấp bậc ưu tiên cũng đồng thời phải xét giảm đi 10 lần để nhìn vào kết quả chúng ta có thể phân biệt ngay bằng mắt thường được => Khi đó kiểm tra lại cũng dễ hơn.
Bước 5: Xếp hạng dựa trên điểm trung bình mới
Ở bước này, chúng ta xếp hạng theo thứ tự giảm dần ( Order = 0)
Như vậy bằng việc sử dụng các cột phụ và xếp hạng thứ tự ưu tiên, chúng ta có thể hoàn thành được việc xếp hạng theo nhiều điều kiện một cách dễ dàng và dễ hiểu.
Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…
Hàm RANK là một trong những hàm cơ bản được sử dụng phổ biến trong Excel để thống kê số liệu theo thứ hạng. Tuy nhiên không phải ai cũng biết cách sử dụng hàm này, nhất là đối với các bạn mới tìm hiểu và sử dụng công cụ tin học Excel. Bài viết dưới đây Dân Tài Chính sẽ hướng dẫn các bạn cách sử dụng hàm RANK một cách cụ thể và chi tiết nhất.
1.Cách sử dụng hàm RANK
Hàm RANK là hàm trả về thứ hạng của một ô dữ liệu trong danh sách, được dùng để xếp hạng dữ liệu theo thứ tự theo thứ tự từ cao đến thấp hoặc thấp đến cao tùy vào mục đích sử dụng. Hàm này sẽ giúp người sử dụng tiết kiệm được thời gian và công sức trong việc sắp xếp dữ liệu.
Mỗi hàm trong Excel đều được sử dụng theo một cú pháp nhất định và cú pháp của hàm Rank như sau
=RANK(number, ref,
Giải thích
Rank: tên hàm, trong tiếng anh có nghĩa là thứ hạng
number: giá trị, dữ liệu cần được xếp hạng
ref: vùng dữ liệu, danh sách các giá trị để xếp hạng
order: đây là một chỉ số chỉ định thứ tự sắp xếp, kiểu sắp xếp: nếu thứ tự là 0 hoặc không điền, hàm RANK sẽ trả về danh sách được sắp xếp theo thứ tự giảm dần. Nếu thứ tự là 1, hàm RANK sẽ trả về một danh sách được sắp xếp theo thứ tự tăng dần.
2. Các ví dụ về hàm RANK
Để có thể hiểu rõ hơn về hàm này, hãy cùng thực hành cùng Dân Tài Chính nhé
Sử dụng hàm RANK xếp hạng từ thấp đến cao nhất
Để sắp xếp thứ tự điểm trung bình của các bạn trong lớp theo thứ tự từ thấp đến cao
Bước 1: Ở ô đầu tiên của cột xếp hạng ta điền công thức như sau
=RANK(B2; $B$2:$B$10; 1)
Giải thích
B2: số liệu trong ô cần được xếp hạng$B$2:$B$10: vùng dữ liệu chưa giá trị B2, ta có thể cố định ô bằng cách chọn vùng từ B2 đến B10 rồi sau đó nhấn F4 hoặc Fn + F41: lệnh xếp hạng theo thứ tự từ thấp đến caoBước 2: Nhấn Enter
Bước 3: Xếp hạng các ô còn lại bằng cách kéo chuột từ ô C2 xuống đến ô C10. Theo bảng dướp đây, DTC 4 là học sinh có điểm trung bình xếp đầu tiên và DTC 9 là học sinh có điểm trung bình xếp hạng cuối.
Sử dụng hàm RANK xếp hạng từ cao xuống thấp
Để sắp xếp thứ tự điểm trung bình của các bạn trong lớp theo thứ tự từ cao xuống thấp
Bước 1: Ở ô đầu tiên của cột xếp hạng ta điền công thức như sau
=RANK(B2; $B$2:$B$10; 0)
Giải thích
B2: số liệu trong ô cần được xếp hạng$B$2:$B$10: vùng dữ liệu chưa giá trị B2, ta có thể cố định ô bằng cách chọn vùng từ B2 đến B10 rồi sau đó nhấn F4 hoặc Fn + F40: lệnh xếp hạng theo thứ tự từ cao đến thấpBước 2: Nhấn Enter
Bước 3: Xếp hạng các ô còn lại bằng cách kéo chuột từ ô C2 xuống đến ô C10. Theo bảng dướp đây, DTC 4 là học sinh có điểm trung bình xếp cuối cùng và DTC 9 là học sinh có điểm trung bình xếp đầu tiên.
Hàm RANK có thể sắp xếp các số trùng lặp sẽ cùng một thứ hạng với nhau. Tuy nhiên điều này sẽ ảnh hưởng đến thứ hạng của các số phía sau. Trong ví dụ ở trên, khi sắp xếp theo thứ tự tăng giần, số 7 xuất hiện 2 lần và có thứ hạng là 4 thì số 8 sẽ có thứ hạng là 6 (không có số nào có thứ hạng 5)
Vậy làm thế nào để sắp xếp các số liệu theo thứ tự mà không bị ngắt quãng bởi các số liệu giống nhau?
Đối với cách sắp xếp theo thứ tự tăng dần, ta có thể dùng hàm như sau
=SUMPRODUCT((B2>=$B$2:$B$10)/COUNTIF($B$2:$B$10; $B$2:$B$10))
Vị trí điểm 7 đã có số hạng là 4, vị trí điểm 8 có số hạng là 5
Ngược lại, đối với cách sắp xếp theo thứ tự giảm dần, ta có thể dùng hàm như sau
=SUMPRODUCT((B2Cách sử dụng hàm Sumproduct
3. Hàm RANK.AVG và hàm RANK.EQ
Hiện giờ hàm RANK vẫn được sử dụng tuy nhiên đã dần được thay thế bởi một số hàm mới có độ chính xác cao và có tên gọi phản ánh rõ các công dụng của chúng là hàm RANK.AVG và RANK.EQ (từ phiên bản Excel 2010 trở đi). Các hàm này đều có công thức tương tự như nhau nhưng kết quả thu được có sự khác biệt
Bảng trên đây thể hiện kết quả, hàm RANK và RANK.EQ cho kết quả giống nhau
Hàm RANK.AVG (Average) có nghĩa là thứ hạng trung bình, nghĩa là nếu có số hạng ngang nhau thì sẽ xếp hạng trung bình cho 2 người đó.
Hàm RANK.EQ (Equal) sẽ xếp đồng hạng mà không tính trung bình hạng, nghĩa là nếu có 2 người cùng thứ hạng thì sẽ xếp vào thứ hạng làm tròn, chứ không phải thứ hạng lẻ.
4. Cách sử dụng hàm RANK sắp xếp có nhiều điều kiện
Dữ liệu sẽ được sắp xếp theo điều kiện trước, sau. Công thức xếp hạng của các điều kiện sẽ phụ thuộc vào thứ hạng của điều kiện trước đó.
Đối với ví dụ dưới đây xếp hạng các học sinh theo điểm trung bình tổng các môn, nếu đồng hạng thì xét ưu tiên theo thứ tự Toán => Lý => Hóa.
Bước 1: Xét ưu tiên môn Toán khi điểm trung bình bằng nhau. Ta sử dụng công thức
L4=IF(COUNTIF($E$4:$E$13,E4)>1, RANK.EQ(B4,$B$4:$B$13,1), 0)
Kéo chuột từ ô L4 xuống L13
Bước 2: Khi điểm môn Toán bằng nhau, ta tiếp tục xếp hạng môn Lý (xếp hạng theo thứ tự ưu tiên, có phụ thuộc vào thứ tự ở trước đó). Ta sử dụng công thức sau
M4 =IF(AND(L4>0,COUNTIF(L$4:L$13,L4)>1), RANK.EQ(C4,C$4:C$13,1), 0)
Kéo chuột từ ô M4 xuống M13
Giải thích
L4>0: Xét các điểm trung bình môn Toán có giá trị trùng với nhauCOUNTIF(L$4:L$13,L4)>1: Xét với trường hợp xếp hạng ưu tiên môn Toán có xếp hạng giống nhau (đếm số kết quả xếp hạng môn Toán, nếu lớn hơn 1 tức là có sự đồng hạng với nhau).AND(L4>0,COUNTIF(L$4:L$13,L4)>1): Khi thỏa mãn đồng thời cả 2 điều kiện trên thì mới xét đến trường hợp ưu tiên môn LýRANK.EQ(C4,C$4:C$13,1): Xếp hạng điểm môn Lý theo thứ tự tăng dần.
Bước 3: Khi xếp hạng môn Toán và môn Lý bằng nhau, ta xét đến ưu tiên môn Hóa
N4=IF(AND(M4>0,COUNTIF(M$4:M$13,M4)>1),RANK.EQ(D4,D$4:D$13,1),0)
AND(M4>0,COUNTIF(M$4:M$13,M4)>1): xếp hạng điểm môn Hóa theo thứ tự từ thấp đến cao.
Bước 4: Tính điểm trung bình dựa trên điểm ưu tiên của các điều kiện
)4= E4+L4/1000 + M4/10000 + N4/100000
Bước 5: Dựa trên điểm trung bình mới, sử dụng hàm RANK để sắp xếp lại theo thứ tự giảm dần
=RANK.EQ(O4, $O$4:$O$13, 0)
Ta có thể thấy thứ tự xếp hạng của các học sinh đã được thay đổi theo thứ tự ưu tiên.
Xem thêm: Cách Khắc Phục Lỗi Không Chơi Candy Crush Saga Không Bị Chặn
Trên đây là một số kiến thức về hàm RANK trong Excel. Mong rằng bài viết này sẽ giúp ích cho các bạn trong việc sử dụng các hàm và tiết kiệm thời gian cho mình.