Hàm xếp hạng có điều kiện
Trong nội dung bài viết này, thosanhuyenthoai.vn đã hướng dẫn chúng ta tìm gọi sâu hơn về hàm RANK trong những phiên phiên bản Office tự 2010 trở lên, và vấn đề làm cố nào nhằm xếp hạng theo một hoặc những điều kiện.
Bạn đang xem: Hàm xếp hạng có điều kiện
Những ngôn từ chính:
Phần 1. Hàm xếp hạng trong số phiên bản office từ bỏ 2010 trở lên được thay đổi từ bỏ hàm RANK thanh lịch hàm RANK.AVG cùng hàm RANK.EQ
Phần 2. Hàm RANK / RANK.AVG / RANK.EQ đều không cung cấp xếp hạng theo điều kiện. Hy vọng xếp hạng theo điều kiện cần phối kết hợp thêm phần lớn bước đo lường và tính toán khác.
Phần 3. Bí quyết 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 thiết bị tự ưu tiên)
Để dễ dàng hình dung, họ sẽ áp dụng bài xích tập sau để có tác dụng ví dụ minh họa:
Dù đã gồm phần mềm, nhưng năng lực Excel vẫn cực kì quan trọng cùng với kế toán, các bạn đã vững vàng Excel chưa? Hãy nhằm tôi góp bạn, đăng ký khoá học tập Excel:
Sau đây bọn họ 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 thêm 2 hàm sửa chữa cho hàm RANK là hàm RANK.AVG và hàm RANK.EQ. Những phiên phiên bản trước đó (2003, 2007) chỉ có hàm RANK mà lại thôi.
Về cú pháp, 3 hàm này đều có cú pháp như thể 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 vật dụng hạng mang đến nó.– Ref: Một mảng hoặc tham chiếu mang đến danh sách các số.– Order: Số chỉ rõ phương pháp xếp hạng. (Order bao gồm 2 cực hiếm là 0 và 1).
*Lưu ý: Nếu Order=0 thì xếp thứ hạng theo vật dụng tự giảm dần, order =1 thì xếp sản phẩm theo lắp thêm tự tăng dần.
Chúng ta xét việc sử dụng 3 hàm này vào bài xích tập cùng xem hiệu quả thu được nạm nào nhé: (Xếp theo thiết bị tự giảm dần)
Hàm RANK với RANK.AVG cho kết quả giống nhau. Ở đây hàm RANK.AVG tức thị thứ bình dân bình, tức là nếu gồm sự đồng hạng (thứ hạng ngang nhau) thì đã xếp trung bình cho số hạng đó (2 người đồng hạng thì thứ hạng tầm trung bình là + 0,5 ; 10 người đồng hạng thì thứ hạng tầm trung bình là + 0,1) (AVG là viết tắt của Average)
Hàm RANK.EQ đang xếp đồng hạng mà ngoại trừ trung bình hạng, có nghĩa là nếu 2 người đồng hạng thì đang xếp cùng vào 1 thiết bị hạng được làm tròn, chứ không hẳn thứ hạng lẻ. (EQ là viết tắt của Equal)
Để tất cả thể ứng dụng việc xếp hạng của các hàm này vào vấn đề xếp hạng theo một hoặc nhiều điều kiện, mời những bạn đón xem ở số đông phần sau của bài viết này. Phương châm của bọn họ là bảng kết quả sau:
Phần 2: cách phân biệt phần nhiều 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 xích 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 đa số ai có điểm toán cao hơn nữa sẽ xếp thứ hạng cao hơn.
Chúng ta thuộc 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.
Xem thêm: Cute Theme Cực Độc Cho Nokia, Bộ Theme Chủ Đề Tình Yêu Dành Cho Điện Thoại
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 gồm 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 đã xếp hạng theo điểm toán, sản phẩm công nghệ tự tăng dần (ai điểm tốt nhất đã xếp trước)
=> Công thức được phát âm là: nếu điểm trung bình nhưng mà trùng, thì tại những người bị trùng đó sẽ xếp hạng bọn họ theo điểm môn toán, theo trang bị tự tăng dần.
* Lưu ý ở đây: tại sao thứ tự ưu tiên lại xếp tăng ngày một nhiều mà không hẳn giảm dần.
Bởi vì bởi vậy ai điểm càng cao thì trang bị tự càng là số lớn => Số mẫu mã này cũng chính là số sẽ được cộng thêm vào điểm gốc để làm địa thế 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 lúc có điểm ưu tiên
Vì điểm trung bình bao gồm 2 số sau lốt phẩy nên điểm ưu tiên và tính sau lốt phẩy 3 chữ số (chia sản phẩm công nghệ hạng ở cột ưu tiên mang lại 1000)
Khi đó ta sẽ được điểm trung bình mới bao gồm điểm vừa đủ cũ + điểm xếp thứ hạng với phần đa điểm trùng như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 bố trí thứ hạng giỏi hơn cùng dễ sáng tỏ hơn.
Bước 3: xếp hạng dựa trên điểm mức độ vừa phải mới
Hàm xếp hạng ở đây họ sử dụng hàm RANK.EQ để xếp hạng cho điểm vừa phải mới.
Phần 3. Giải pháp xây dựng bí quyết xếp hạng theo một điều kiện, hoặc theo nhiều điều kiện (theo đồ vật tự ưu tiên)
Sau lúc hiểu cách xếp hạng theo một điều kiện, bọn họ có thể áp dụng cách đó mang đến nhiều điều kiện.
Với trường hòa hợp nhiều điều kiện, bọn họ cần thân thiết tới 2 tiêu chí:
Tiêu chí 1: máy tự ưu tiên của các điều kiện: điều kiện nào xét trước, điều khiếu nại nào xét sauTiêu chí 2: cách làm xét hạng của các điều kiện tiếp sau sẽ phụ thuộc vào vào dạng hình của điều khiếu nại 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 khiếu nại như sau:
Xếp hạng theo điểm trung bình, nếu đồng hạng thì ưu tiên xét theo lắp thêm tự các môn: Toán > Lý > Hóa
Hướng dẫn học tập Excel cơ bản
Chúng ta rất có thể thấy với ngôi trường hợp chiếc 7 (Dương Quốc Đạt) và dòng 10 (Lê Bá Long) lúc xét theo 1 điều khiếu nại 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 thuộc phân tích ví dụ cách có tác dụng nhé:
Bước 1: Xét ưu tiên môn toán khi điểm trung bình cân 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ách làm này đã đối chiếu trong phần 2, mời chúng ta 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ởi nhau (Đồng điểm môn Toán ->theo sản phẩm tự ưu tiên, có tính phụ thuộc vào vào đồ vật 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 cần trùng nhau)COUNTIF(L$4:L$13,L4)>1 : Chỉ xét lúc 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 mở ra nhiều hơn 1 thì có nghĩa 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 sản phẩm công nghệ tự tăng vọt để 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 cùng môn Lý đều 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 lúc 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, tiếng xét thêm môn Lý bao gồm 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 công dụng xếp hạng ưu tiên môn Lý nếu mở ra 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 khiếu nại 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 máy tự tăng dần để tính điểm ưu tiênBước 4: Tính điểm trung bình bắt đầu dựa trên điểm ưu tiên của các điều kiện
Ở bước này, chúng ta lưu ý là trang bị 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 cho bậc ưu tiên cũng đồng thời buộc phải xét giảm đi 10 lần để quan sát vào kết quả bạn cũng có thể phân biệt ngay bằng mắt thường được => Khi đó kiểm tra lại cũng dễ dàng hơn.
Bước 5: xếp hạng dựa trên điểm mức độ vừa phải mới
Ở bước này, bọn họ xếp hạng theo thứ tự bớt dần ( Order = 0)
Như vậy bằng việc sử dụng những cột phụ và xếp hạng thứ tự ưu tiên, bạn có thể hoàn thành được việc xếp hạng theo nhiều điều khiếu nại một cách dễ dãi và dễ hiểu.
Để hoàn toàn có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm rõ được những hàm mà còn nên sử dụng giỏi cả các công thế của Excel. Hầu như hàm nâng cấp giúp áp dụng tốt vào quá trình như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những cách thức thường sử dụng là Data validation, Conditional formatting, Pivot table…
