Bí quyết xếp loại dữ liệu dựa vào điều kiện trên Excel – Phần một

Khi nói đến việc sắp xếp thứ hạng trên Excel, người ta thường nghĩ ngay đến hàm RANK.

Bài viết này từ sieumarketing.com sẽ giúp bạn hiểu rõ hơn về chức năng của hàm RANK trong các bản Office từ năm 2010 trở đi, cùng cách để sắp xếp thứ hạng dựa theo một hay nhiều tiêu chí.

Các phần chính bài viết bao gồm:

Phần 1: Sự thay đổi của hàm xếp hạng trong phiên bản Office từ 2010 trở lên, từ hàm RANK thành hàm RANK.AVG và RANK.EQ

Phần 2: Ba hàm RANK / RANK.AVG / RANK.EQ không hỗ trợ xếp hạng dựa theo điều kiện. Cần áp dụng thêm các phép toán khác để thực hiện việc này.

Phần 3: Phương pháp tạo công thức để xếp hạng dựa theo một hoặc nhiều tiêu chí (theo độ ưu tiên cụ thể)

Để minh họa, chúng ta sẽ sử dụng bài tập sau đây như một ví dụ:

Hãy bắt đầu tìm hiểu từng phần nội dung:

Phần 1: Sự khác biệt giữa các hàm RANK / RANK.AVG / RANK.EQ

Từ bản 2010 trở đi của Excel, hàm RANK được chia thành hai hàm mới là RANK.AVG và RANK.EQ, thay vì chỉ có hàm RANK như các phiên bản 2003 và 2007.

Cả ba hàm đều sử dụng cú pháp tương tự nhau:

CÚ PHÁP

=RANK(number, ref, [order])

=RANK.AVG(number, ref, [order])

=RANK.EQ(number, ref, [order])

Về thành phần:

Number: Dữ liệu bạn muốn áp đặt thứ hạng.
Ref: Mảng hoặc tham chiếu đến danh sách số liệu cần xếp hạng.
Order: Giá trị xác định phương pháp xếp hạng (với 0 là giảm dần và 1 là tăng dần).

*Lưu ý: Order bằng 0 sẽ xếp từ cao xuống thấp, và order bằng 1 sẽ xếp từ thấp lên cao.

Xét sự áp dụng ba hàm này vào bài tập và kết quả nhận được như sau: (Sắp xếp giảm dần)

Hàm RANK và RANK.AVG mang lại kết quả giống hệt nhau. RANK.AVG có nghĩa là chia sẻ vị trí nếu cùng hạng, tức là sẽ chia thứ hạng cho các hạng mục cùng hạng (ví dụ, nếu có hai người cùng vị trí thứ hạng, thì thứ hạng trung bình sẽ cộng thêm 0,5; nếu có mười người cùng vị trí, thì sẽ cộng thêm 0,1) (AVG là từ viết tắt của Average, tức trung bình)

Hàm RANK.EQ sẽ xếp chung hạng không tính toán thứ hạng trung bình, nghĩa là, nếu hai người cùng hạng thì sẽ được xếp vào một vị trí hạng cố định, không phải chia thứ hạng xuống. (EQ là viết tắt của Equal, tức là bằng nhau)

Để hiểu rõ cách áp dụng việc xếp hạng của các hàm này dựa theo điều kiện cụ thể, mời quý độc giả theo dõi trong những phần tiếp theo của bài viết. Mục tiêu là đạt được bảng kết quả như sau:

Phần 2: Bạn làm thế nào để phân biệt các học sinh cùng hạng theo một tiêu chuẩn ưu tiên (xếp hạng dựa vào một điều kiện)

Theo bài tập mẫu đã nêu, yêu cầu có thể được hiểu như sau:

Xếp hạng dựa trên điểm trung bình, và nếu xuất hiện việc cùng hạng thì sẽ xếp cao hơn cho người có điểm môn toán lớn hơn.

Hãy cùng phân tích yêu cầu này để rõ cách tiếp cận:

Bước 1: Xem xét ưu tiên điểm toán khi có điểm trung bình giống nhau.

Hướng dẫn sử dụng công thức:

Giải thích về công thức:

COUNTIF($E$4:$E$13,E4)>1      Thực hiện tính toán số lần xuất hiện của mỗi giá trị điểm từ E4:E13, nếu giá trị nào có số lần lớn hơn 1 thì có điểm giống nhau (điểm bị trùng lặp)

RANK.EQ(B4,$B$4:$B$13,1)   Khi có các điểm trung bình giống nhau, chúng ta sẽ sắp thứ tự dựa vào điểm môn Toán, theo chiều tăng dần (người có điểm thấp hơn sẽ được xếp đầu tiên)

=> Kết luận từ công thức là: khi điểm trung bình bị trùng nhau, những học sinh có điểm trùng nhau sẽ được sắp xếp thứ tự dựa vào điểm môn Toán, theo chiều điểm tăng dần.

* Chú ý: Lý do vì sao lại chọn cách sắp xếp theo thứ tự tăng dần chứ không phải giảm dần là:

Bởi như vậy, người có điểm cao hơn sẽ có số thứ tự lớn hơn => Số thứ tự này sẽ là con số được cộng thêm vào điểm gốc để làm cơ sở xếp loại ưu tiên => Điểm số càng cao thì số điểm cộng thêm càng lớn

Bước 2: Tính lại điểm trung bình tính cả điểm ưu tiên

Do điểm trung bình được quy định có 2 chữ số phần thập phân, nên điểm ưu tiên sẽ được tính 3 chữ số sau dấu phẩy (chia số thứ tự ưu tiên ở cột tương ứng cho 1000)

Điểm trung bình mới sẽ được tính bằng cách cộng điểm trung bình cũ với điểm xếp hạng ưu tiên cho những học sinh có điểm bằng nhau và xét theo ưu tiên điểm môn Toán

Điểm xếp hạng ưu tiên cần được đặt sau điểm gốc để giúp việc xếp thứ tự được chính xác và rõ ràng hơn.

Bước 3: Rangking dựa trên điểm trung bình mới thu được

Hàm để xếp loại mà chúng ta dùng ở đây là RANK.EQ, áp dụng cho điểm trung bình mới đã tính toán.

Phần 3. Hướng dẫn thiết kế công thức xếp hạng dựa theo một hoặc nhiều tiêu chí ưu tiên

Sau khi đã nắm được cách xếp hạng dựa trên một tiêu chí, chúng ta có thể mở rộng áp dụng cho nhiều tiêu chí khác nhau.

Khi xét nhiều tiêu chí, chúng ta cần chú ý đến hai yếu tố:

  • Yếu tố 1: Thứ tự ưu tiên giữa các tiêu chí: tiêu chí nào quan trọng hơn và nên được xét đầu tiên
  • Yếu tố 2: Cách xác định thứ hạng cho tiêu chí tiếp theo phụ thuộc vào thứ hạng được quyết định bởi tiêu chí đã xét trước đó

Đề cương yêu cầu cho ví dụ về việc xếp hạng dựa vào nhiều tiêu chí như sau:

Xếp loại dựa trên điểm trung bình, trong trường hợp xếp hạng bằng nhau, tiếp theo sẽ ưu tiên dựa vào thứ tự các môn học: Toán > Lý > Hóa

Có thể thấy rằng, trong trường hợp của dòng thứ 7 (Dương Quốc Đạt) và dòng thứ 10 (Lê Bá Long), khi chỉ xét duy nhất một tiêu chí họ vẫn xếp chung vị trí thứ 6. Do đó, cần xem xét thêm tiêu chí ưu tiên khác.

Chúng ta cùng xem xét chi tiết cách tính như sau:

Bước 1: Xác định ưu tiên môn Toán nếu như điểm trung bình giống nhau (tương tự như Phần 2)

Công thức được dùng:

Nội dung của công thức này đã được giải thích trong Phần 2, xin mời đọc lại để hiểu rõ.

Bước 2: Xét ưu tiên môn Lý khi điểm môn Toán ngang bằng nhau (Nếu điểm môn Toán giống nhau – theo thứ tự ưu tiên, phụ thuộc vào thứ tự trước đó)

Công thức được sử dụng:

Bài viết giải thích về công thức:

  • L4>0: Công th-
    • Khi điểm môn Toán có xếp hạng ưu tiên khác không mới xét (điểm trung bình môn Toán phải giống nhau ban đầu)
    • Khi số lần xuất hiện của xếp hạng ưu tiên môn Toán lớn hơn 1 mới xét (COUNTIF(L$4:L$13,L4)>1 để kiểm tra sự trùng lặp trong xếp hạng ưu tiên môn Toán)
    • Chỉ xem xét khi cả hai điều kiện trên đều thỏa mãn mới tiến hành tính điểm ưu tiên cho môn Lý (AND(L4>0,COUNTIF(L$4:L$13,L4)>1))
    • Xếp hạng môn Lý theo chiều tăng dần để đưa ra điểm ưu tiên bằng cách sử dụng RANK.EQ(C4,C$4:C$13,1)

    Bước 3: Trong trường hợp xếp hạng ưu tiên môn Toán và môn Lý như nhau (cả hai môn có điểm đồng hạng)

    Công thức được dùng:

    Giải thích công thức:

    • M4>0: Chỉ xét khi môn Lý có xếp hạng ưu tiên không bằng không (đã tính đến sự trùng của môn Toán và bây giờ là môn Lý)
    • Để đếm xếp hạng ưu tiên môn Lý chỉ xét khi có sự trùng lặp (COUNTIF(M$4:M$13,M4)>1 chỉ kiểm tra khi có hơn một sự xuất hiện trùng hạng)
    • Chỉ khi cả hai điều kiện nói trên được đồng ý chúng ta mới xem xét tính ưu tiên cho môn Hóa (AND(M4>0,COUNTIF(M$4:M$13,M4)>1))
    • Dùng RANK.EQ(D4,D$4:D$13,1),0) để xếp hạng môn Hóa theo chiều tăng dần khi tính điểm ưu tiên

    Bước 4: Tính toán lại điểm trung bình dựa vào điểm ưu tiên từ các điều kiện đã xét

    Ở giai đoạn này cần chú ý rằng, điểm trung bình sẽ được cộng thêm với ưu tiên ở các môn và mỗi bậc ưu tiên cần giảm mười lần để phân biệt dễ dàng bằng mắt thường => Việc kiểm tra sau đó cũng sẽ thuận tiện hơn.

    Bước 5: Xếp hạng dựa trên điểm trung bình mới vừa tính toán

    Bước này đòi hỏi xếp hạng Theo thứ tự giảm dần (Order = 0)

    Sử dụng các cột phụ và xếp hạng theo thứ tự ưu tiên, chúng ta có thể hoàn tất việc xếp hạng dưới nhiều tiêu chí một cách dễ dàng và trực quan.

    Để linh hoạt áp dụng Excel vào công việc, không chỉ việc nắm vững các hàm là quan trọng mà còn cần biết vận dụng các công cụ của Excel. Một số hàm tiên tiến giúp việc này thực hiện tốt như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… và các công cụ thường dùng bao gồm Data validation, Conditional formatting, Pivot table…

    Các bạn có khả năng học hỏi tất cả những kiến thức này trong các khóa học đào tạo.

    Trương Thành Tài

    [submission_id id-lien-he]

    0
      0
      Đơn hàng
      Đơn hàng trốngQuay lại Shop