Sắp xếp công thức trong Excel sử dụng các hàm SORT và SORTBY

2/11/2020

Ý chính: Tìm hiểu cách sử dụng hàm SORT và SORTBY để trả về phạm vi được sắp xếp dựa trên một hoặc nhiều tiêu chuẩn.

Trình độ kỹ năng: Trung bình

Tương thích: File sử dụng hàm Dynamic Array mới chỉ duy nhất có trên phiên bản mới nhất của Office 365. Bao gồm cả desktop và web app phiên bản Excel.

Sắp xếp và Xếp loại

Trong bài viết này chúng ta hãy nhìn vào 2 hàm Dynamic Array, SORT và SORTBY. Hai hàm này thích hợp trong việc hiển thị danh sách được xếp loại theo thứ tư hiệu suất hoặc thứ tự thời gian (ngày, giờ).

Trong trường hợp ví dụ của chúng ta, chúng ta sẽ sắp xếp nhân viên dựa theo thời gian họ dành thời gian tại văn phòng (thời lượng) trong tuần. Hàm sẽ xếp loại nhân viên những ai dành nhiều thời gian nhất và ai dành ít thời gian nhất tai văn phòng.

Tuy nhiên, trước khi sắp xếp, tôi sẽ giới thiệu nhanh cho bạn biết làm cách nào để thêm tất cả thời lượng với nhau với mỗi nhân viên bằng cách sử dụng hàm SUMIF.

Hàm SUMIF

Hàm SUMIF thêm các ô được chỉ định bằng một tiêu chí được nêu ra. Có 3 đối số. Đối số thứ nhất là phạm vi của dữ liệu mà hàm sẽ tìm kiếm. Trong ví dụ, đó là danh sách tên nhân viên.

Đối số thứ hai là tiêu chí mà hàm sẽ tìm. Đối với đối số này, chúng ta có thể xác định phạm vi tràn chúng ta đã tạo chỉ liệt kê các nhân viên đối với phòng ban chúng ta đã xác định. Trong ví dụ, tham chiếu phạm vi tràn sẽ là A2#.

Cuối cùng, đối số phạm vi tổng hợp của chúng ta là cột Thời lượng. Đối số này báo Excel thêm tất cả thời lượng cho nhân viên mà chúng ta vừa xác định. Công thức cuối cùng sẽ như sau:

=SUMIF(tblData[Employee],A2#, tblData[Duration])

Hàm SORTBY

Bây giờ chúng ta vừa tổng hợp tất cả thời gian của mỗi nhân viên, chúng ta có thể sắp xếp nhân viên bằng số giờ mà họ đã làm việc.

Để sử dụng hàm SORTBY, chúng ta chỉ cần xác định dữ liệu cho 3 đối số. Đối số thứ nhất là Array, đây là set giá trị mà chúng ta muốn hàm trả về. Trong trường hợp của chúng ta đó là danh sách nhân viên, được xác định bời phạm vi tràn A2#.

Đối số thứ 2 là By Array, và đó là set dữ liệu chúng ta muốn sắp xếp. Chúng ta muốn sắp xếp bằng tổng thời gian mỗi người làm việc tại văn phòng, do đó chúng ta sử dụng dữ liệu Sum of Time, được xác định bởi phạm vi tràn B2#.

Đối số thứ 3 và cũng là đối số cuối cùng, Sort Order, chỉ định cách sắp xếp dữ liệu. Chúng ta có thể lựa chọn tăng dần hoặc giảm dần. Chúng ta muốn sắp xấp theo thứ tự giảm dần (lớn nhất hoặc nhỏ nhất), được thể hiện bằng -1.

Do đó hàm SORTBY sẽ được thể hiện như sau:

=SORTBY(A2#,B2#,-1)

Hàm SORT

Ngoài ra, để xếp loại nhân viên từ những người có số thời gian làm việc nhiều nhất đến ít nhất, nó cũng sẽ rất hữu ích để liệt kê những giờ tương ứng cạnh mỗi tên. Chúng ta có thể thực hiện theo nhiều cách, nhưng chúng ta sẽ sử dụng hàm SORT. Đây là một hàm Dynamic Array khác trả về nhiều giá trị trong một phạm vi tràn.

Để sử dụng SORT, bạn chỉ cần xác định phạm vi mà bạn muốn sắp xếp, và thứ tự bạn muốn sắp xếp. (Đối số thứ 2 trong hàm SORT, sắp xếp chỉ số, không cần thiết cho ví dụ của chúng ta.)

Công thức đơn giản sẽ là:

=SORT (D2#,,-1)

Các metric khác trên sheet thường đơn giản và được liệt kê bên dưới.

• Sum: =SUM(D2#)
• Avg: =AVERAGE(D2#)
• Good Label: =IF($I$3>=$I$5,$I$3,””)
• Bad Label: =IF($I$3<$I$5,$I$3,””)

Nguồn: Excel Campus