Bỏ túi 3 hàm công thức xếp loại trong Excel hữu ích nhất hiện nay

Ứng dụng của việc tính xếp loại trong Excel

Mặc dù sử dụng Excel lâu năm nhưng nhiều bạn vẫn chưa biết được công thức xếp loại trong Excel mà thường làm thủ công rất là tốn thời gian và dễ gây nhầm lẫn.

Trước hết chúng ta hãy cùng xem việc tính xếp loại này có những ứng dụng gì? Về cơ bản thì cách xếp loại trong Excel sẽ giúp bạn phân loại được nhiều đối tượng thành từng nhóm khác nhau. Ví dụ như: 

• Phân loại học lực học sinh vào cuối năm 

• Phân loại nhân viên để tính thưởng 

• Phân loại hàng hóa dựa theo các tiêu chí như màu sắc, kích thước, trọng lượng,...

Tùy theo mục đích sử dụng mà các hàm trong Excel sẽ phân loại đối tượng để bạn dễ dàng tổng hợp thông tin, có cách đánh giá khách quan. Bên cạnh đó, khi sử dụng những hàm này thì bạn sẽ tiết kiệm được thời gian và công sức so với việc thực hiện một cách thủ công.

Công thức xếp loại trong Excel

Tiếp theo chúng ta sẽ tìm hiểu về các hàm xếp loại trong Excel khác nhau. Tùy theo nhu cầu và mục phân loại mà bạn có thể chọn một trong ba cách sau đây:

Sử dụng hàm If

Hàm if là một trong những hàm được nhiều người sử dụng hiện nay. Đây là lệnh xếp loại trong Excel giúp bạn so sánh giữa một giá trị và một giá trị dự kiến thông qua việc kiểm tra điều kiện, sau đó trả về kết quả True hoặc False.

Công thức của hàm If như sau: If(logical_test, value_if_true, [value_if_false])

Trong đó:

• logical_test (bắt buộc): Điều kiện bạn muốn kiểm tra

• value_if_true (bắt buộc): Giá trị bạn muốn trả về nếu như điều kiện đúng 

• value_if_false (không bắt buộc): Giá trị bạn muốn trả về nếu như điều kiện kiểm tra sai

Ví dụ về cách tính điểm xếp loại trong Excel khi sử dụng hàm If như sau: 

Hàm IF: IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))
Mặc dù bạn thấy hàm được lồng nhiều công thức phức tạp, nhưng xét về mặt logic thì tương đối dễ hiểu. Ý nghĩa của hàm IF bên trên như sau:

• Nếu như điểm kiểm tra cao hơn 89 thì học viên sẽ nhận điểm A

• Nếu như điểm kiểm tra lớn hơn 79 thì học viên sẽ nhận điểm B 

• Nếu như điểm kiểm tra lớn hơn 69 thì học viên sẽ nhận điểm C 

• Nếu như điểm kiểm tra lớn hơn 59 thì học viên sẽ nhận điểm D 

• Những trường hợp còn lại sẽ nhận điểm F 

Sử dụng hàm Vlookup

Một trong những công thức xếp loại trong Excel phổ biến khác chính là công thức hàm Vlookup. Hàm được sử dụng để tìm kiếm thông tin theo yêu cầu của người dùng. Hàm này sẽ hỗ trợ bạn dò và trả về dữ liệu ở số hàng, số cột mà bạn muốn tìm kiếm.

Cấu trúc của hàm Vlookup như sau:

    =VLOOKUP(Lookup_value, Table_array, Col_index_ num, [Range_lookup])

Trong đó:

• Lookup_value: Giá trị cần tìm, có thể là giá trị cụ thể hoặc công thức tham chiếu.

• Table_array: Vùng chứa điều kiện, thường sử dụng ở dạng địa chỉ tuyệt đối.

• Col_index_num: Là thứ tự của cột cần lấy giá trị tìm kiếm, cột đầu tiên được tính là 1, tính từ trái sang.

• [Range_lookup]: Giá trị logic ảnh hưởng đến phạm vi tìm kiếm, gồm 2 kiểu giá trị: 

• [Range_lookup] = 1 (TRUE): So sánh tương đối.

• [Range_lookup] = 0 (FALSE): So sánh tuyệt đối.

Để hiểu rõ hơn về việc sử dụng hàm Vlookup, chúng ta cùng xem xét ví dụ sau đây:

Để tính xếp loại trong Excel dựa trên điểm trung bình thì Range_lookup= 1. Vì để xếp loại theo bảng tham chiếu thì bạn cần phải lấy giá trị tương đối gần đúng, 8.1 gần với 8, 6.6 gần với 6.5… Hiểu một cách cụ thể thì tiêu chí xếp loại sẽ như sau:

• Từ 8.0 trở lên: Xếp vào loại giỏi (Điểm trung bình phải lớn hơn hoặc bằng 8, tức là 8 <= x)

• Từ 6.5 đến dưới 8: Xếp vào loại khá (6.5 <= x < 8)

• Từ 5 đến dưới 6.5: Xếp vào loại trung bình (5 <= x < 6.5)

• Dưới 5.5: Xếp vào loại yếu (0<= x <5)

Giá trị cần tìm sẽ ở cột B, bắt đầu từ hàng thứ 2 nên bảng điều kiện dò tìm là  E3:F6  địa chỉ tuyệt đối của bảng là $E$3:$F$6), thứ tự cột cần lấy dữ liệu là cột 2 (mong muốn hàm trả về loại học lực của các học sinh).

Vậy, ta có công thức của hàm Vlookup như sau:

           =VLOOKUP(B2,$E$3:$F$6,2,1)

Lưu ý: Bảng chứa điều kiện để dò tìm cần phải được sắp xếp theo thứ tự từ bé đến lớn. Nếu như thứ tự không được sắp xếp theo quy định này thì kết quả sẽ trả về sai. 

Bạn nhấn Enter, kết quả sẽ thu được như sau:

Lúc này bạn sẽ thấy được xếp lại của Trần Văn An là giỏi. Bạn tiếp tục nhấp chuột vào ô B2 sẽ xuất hiện ô vuông nhỏ ở góc dưới bên phải, di chuyển chuột đến ô này, nếu thấy con trỏ đã chuyển sang dấu “+” thì kéo dọc xuống để sao chép công thức đến học sinh khác. 

Bạn có được bảng học sinh đã được xếp loại như sau:

Sử dụng hàm If giúp xếp loại nâng cao

Trong một vài trường hợp xếp loại phức tạp hơn, bạn sẽ không thể áp dụng được các hàm bên trên nữa. Vì vậy chúng tôi sẽ hướng dẫn các bạn cách sử dụng công thức xếp loại trong Excel nâng cao với hàm IF. Đối với lệnh nâng cao này bạn có thể tính thưởng theo xếp loại trong Excel trong trường hợp có nhiều điều kiện ràng buộc hơn.

Dựa vào bảng quản lý sinh viên bên trên, sau đây là xếp loại học bổng theo tiêu chí sau:

• Sinh viên loại giỏi sẽ không có môn nào dưới 7 điểm thì nhận được học bổng 240.000 đồng.

• Sinh viên loại giỏi hay xóa Tuy nhiên có môn dưới 7 điểm thì sẽ nhận được học bổng 180.000 đồng.

• Sinh viên trung bình sẽ không nhận được học bổng của nhà trường.

• Sinh viên xếp loại yếu sẽ phải nộp thêm 180.000 tiền học lại.

Lúc này, bên cạnh hàm IF, bạn sẽ cần lồng ghép thêm hàm MIN, AND, OR. Từ đó ta có công thức như sau:

Hàm IF thứ nhất: Xét học bổng loại giỏi:

IF(AND(J3=”Giỏi”,MIN(D3:G3)>=7),240000, hàm if thứ 2): Khi sinh viên xếp loại Giỏi (J3 là địa chỉ ô xếp loại) và điểm các môn >=7 (giá trị nhỏ nhất dãy D3:G3 >=7) thì nhận học bổng 240000, còn lại xét các kết quả khác

Hàm IF thứ 2: Xét học bổng loại khá

IF(OR(AND(J3=”Giỏi”,MIN(D3:G3)<7),J3=”Khá”),180000, hàm if thứ 3): Sẽ có 2 trường hợp như sau:

• Xếp loại giỏi nhưng lại có điểm nhỏ hơn 7: Chị nhận học bổng loại khá

• Xếp loại khá

Vì điều này nên bạn phải dùng hàm OR để xét 2 trường hợp. Trong trường hợp xếp loại giỏi nhưng lại có điểm nhỏ hơn 7 thì phải đặt trong hàm AND.

Hàm IF thứ 3: Những trường hợp còn lại

IF(J3=”Trung bình”,0,-180000) : Sinh viên xếp loại trung bình sẽ không được nhận  học bổng, trường hợp Yếu phải nộp 180000

Khi nối các hàm lại ta có:

IF(AND(J3=”Giỏi”,MIN(D3:G3)>=7),240000,IF(OR(AND(J3=”Giỏi”,MIN(D3:G3)<7),J3=”Khá”),180000,IF(J3=”Trung bình”,0,-180000)))

Sau khi thực hiện lệnh trên, bạn sẽ thu về kết quả sau:

Kết luận

Bài viết trên đã chia sẻ tới các bạn về những công thức xếp loại trong Excel được nhiều người sử dụng nhất hiện nay. Thủ Thuật Số hy vọng rằng với các thông tin này sẽ giúp cho công việc của bạn phần nào dễ dàng hơn trong việc tìm kiếm và phân loại đối tượng theo nhu cầu.