Hàm Excel SORT – tự động sắp xếp theo công thức

Hàm Excel SORT – tự động sắp xếp theo công thức

Hướng dẫn cho thấy cách sử dụng hàm SORT để sắp xếp các mảng dữ liệu một cách linh hoạt. Bạn sẽ học một công thức để sắp xếp theo thứ tự abc trong Excel, sắp xếp các số theo thứ tự tăng dần hoặc giảm dần, sắp xếp theo nhiều cột và hơn thế nữa.

Các chức năng Sắp xếp đã có từ lâu. Nhưng với việc giới thiệu các mảng động trong Excel 365, đã xuất hiện một cách đơn giản đáng kinh ngạc để sắp xếp trong Excel bằng các công thức. Cái hay của phương pháp này là kết quả tự động cập nhật khi dữ liệu nguồn thay đổi.

Hàm Excel SORT
Công thức SORT Excel cơ bản
Cách sắp xếp tài liệu trong Excel bằng công thức – ví dụ
Excel SORT theo cột
Sắp xếp theo nhiều cột theo thứ tự khác nhau
Sắp xếp và lọc trong Excel
Sắp xếp và nhận N giá trị trên cùng hoặc dưới cùng
Làm thế nào để có được mảng sắp xếp để lan rộng ra tự động hóa
Hàm Excel SORT không hoạt động giải trí
Hàm Excel SORT
Hàm SORT trong Excel sắp xếp nội dung của một mảng hoặc khoanh vùng phạm vi theo cột hoặc hàng, theo thứ tự tăng dần hoặc giảm dần .

SORT thuộc nhóm chức năng Mảng động. Kết quả là một mảng động tự động tràn sang các ô lân cận theo chiều dọc hoặc chiều ngang, tùy thuộc vào hình dạng của mảng nguồn.

Cú pháp của hàm SORT như sau:

SORT ( array, [ sort_index ], [ sort_order ], [ by_col ] )

Ở đây:

Mảng ( bắt buộc ) – là một mảng những giá trị hoặc một khoanh vùng phạm vi ô để sắp xếp. Đây hoàn toàn có thể là bất kể giá trị nào gồm có văn bản, số, ngày, thời hạn, v.v.

Sort_index (tùy chọn) – một số nguyên cho biết cột hoặc hàng nào được sắp xếp theo. Nếu bỏ qua, chỉ mục mặc định 1 được sử dụng.

Sort_order (tùy chọn) – xác định thứ tự sắp xếp:

1 hoặc bỏ lỡ ( mặc định ) – thứ tự tăng dần, tức là từ nhỏ nhất đến lớn nhất
– 1 – thứ tự giảm dần, tức là từ lớn nhất đến nhỏ nhất
by_col ( tùy chọn ) – một giá trị logic cho biết hướng sắp xếp :

SAI hoặc bỏ qua (mặc định) – sắp xếp theo hàng. Bạn sẽ sử dụng tùy chọn này hầu hết thời gian.

TRUE – sắp xếp theo cột. Sử dụng tùy chọn này nếu dữ liệu của bạn được sắp xếp theo chiều ngang trong các cột như trong ví dụ này .

Hàm Excel SORT – mẹo và ghi chú
SORT là một hàm mảng động mới và do đó, nó có một vài đặc thù mà bạn cần quan tâm :

Hiện tại, chức năng SORT chỉ khả dụng trong kênh Đăng ký hàng tháng của Office 365. Bắt đầu từ tháng 7 năm 2020, nó sẽ được đưa vào kênh Bán niên. Excel 2019, Excel 2016 không hỗ trợ các công thức mảng động, do đó, hàm SORT không có sẵn trong các phiên bản này.

Nếu mảng được trả về bởi công thức SORT là kết quả cuối cùng (nghĩa là không được chuyển sang hàm khác), Excel sẽ tự động tạo một phạm vi có kích thước phù hợp và điền vào đó các giá trị được sắp xếp. Vì vậy, hãy chắc chắn rằng bạn luôn có đủ các ô trống hoặc / và bên phải của ô nơi bạn nhập công thức, nếu không sẽ xảy ra lỗi #SPILL.

Các hiệu quả cập nhật động khi tài liệu nguồn đổi khác. Tuy nhiên, mảng được cung ứng cho công thức không tự động hóa lan rộng ra để gồm có những mục mới được thêm bên ngoài mảng được tham chiếu. Để gồm có những mục như vậy, bạn cần update tham chiếu mảng trong công thức của mình hoặc quy đổi khoanh vùng phạm vi nguồn thành bảng Excel như trong ví dụ này hoặc tạo một khoanh vùng phạm vi có tên động .

Công thức SORT Excel cơ bản

Ví dụ này cho thấy một công thức cơ bản để sắp xếp tài liệu trong Excel theo thứ tự tăng dần và giảm dần .

Giả sử tài liệu của bạn được sắp xếp theo thứ tự abc như trong ảnh chụp màn hình hiển thị bên dưới. Bạn đang tìm cách sắp xếp những số trong cột B mà không phá vỡ hoặc trộn tài liệu .

Công thức sắp xếp theo thứ tự tăng dần
Để sắp xếp những giá trị trong cột B từ nhỏ nhất đến lớn nhất, đây là công thức sử dụng :

= SORT ( A2 : B8, 2, 1 )

Ở đâu:

A2 : B8 là mảng nguồn
2 là số cột để sắp xếp theo
1 là thứ tự sắp xếp tăng dần
Vì tài liệu của chúng tôi được sắp xếp theo hàng, nên đối số sau cuối hoàn toàn có thể được bỏ lỡ để mặc định thành FALSE – sắp xếp theo hàng .

Chỉ cần nhập công thức vào bất kể ô trống nào ( D2 trong trường hợp của chúng tôi ), nhấn Đi vàovà tác dụng sẽ tự động hóa tràn sang D2 : E8 .

Công thức sắp xếp theo thứ tự giảm dần

Để sắp xếp tài liệu giảm dần, tức là từ lớn nhất đến nhỏ nhất, hãy đặt đối số sort_order thành – 1 như thế này :

=SORT(A2:B8, 2, -1)

Nhập công thức vào ô trên cùng bên trái của khoanh vùng phạm vi đích và bạn sẽ nhận được hiệu quả này :

Theo cách tựa như, bạn hoàn toàn có thể sắp xếp những giá trị văn bản theo thứ tự bảng vần âm từ A đến Z hoặc từ Z đến A .

Cách sắp xếp dữ liệu trong Excel bằng công thức

Các ví dụ dưới đây cho thấy một vài cách sử dụng nổi bật của hàm SORT trong Excel và một vài cách không tầm thường .

Excel SORT theo cột

Khi bạn sắp xếp dữ liệu trong Excel, phần lớn bạn thay đổi thứ tự các hàng. Nhưng khi dữ liệu của bạn được sắp xếp theo chiều ngang với các hàng chứa nhãn và cột chứa các bản ghi, bạn có thể cần sắp xếp từ trái sang phải, thay vì từ trên xuống dưới.

Để sắp xếp theo cột trong Excel, hãy đặt đối số by_col thành TRUE. Trong trường hợp này, sort_index sẽ đại diện thay mặt cho một hàng, không phải là một cột .

Ví dụ : để sắp xếp tài liệu dưới đây theo Qty. từ cao nhất đến thấp nhất, sử dụng công thức này :

=SORT(B1:H2, 2, 1, TRUE)

Ở đâu :

B1 : H2 là tài liệu nguồn để sắp xếp
2 là chỉ số sắp xếp, vì chúng tôi đang sắp xếp những số ở hàng thứ hai
– 1 chỉ ra thứ tự sắp xếp giảm dần
TRUE có nghĩa là sắp xếp những cột, không phải hàng

Sắp xếp theo nhiều cột theo thứ tự khác nhau (sắp xếp nhiều cấp)

Khi thao tác với những quy mô tài liệu phức tạp, bạn hoàn toàn có thể thường cần một loại sắp xếp đa cấp. Điều đó hoàn toàn có thể được triển khai với một công thức ? Đúng, thuận tiện ! Những gì bạn làm là cung ứng hằng mảng cho sort_index và SORT_ORDER đối số .

Ví dụ: để sắp xếp dữ liệu bên dưới trước theo Vùng (cột A) từ A đến Z, sau đó theo Qty. (cột C) từ nhỏ nhất đến lớn nhất, đặt các đối số sau:

Mảng là tài liệu trong A2 : C13 .
Sort_index là hằng số mảng { 1,3 }, vì thứ nhất tất cả chúng ta sắp xếp theo Vùng ( cột thứ 1 ), sau đó theo Qty. ( Cột thứ 3 ) .
Sort_order là hằng số mảng { 1, – 1 }, vì cột thứ 1 sẽ được sắp xếp theo thứ tự tăng dần và cột thứ 3 theo thứ tự giảm dần .
By_col bị bỏ lỡ vì chúng tôi sắp xếp những hàng, mặc định .
Đặt những đối số lại với nhau, tất cả chúng ta có được công thức này :

=SORT(A2:C13, {1,3}, {1,-1})

Và nó hoạt động giải trí tuyệt vời ! Các giá trị văn bản trong cột tiên phong được sắp xếp theo thứ tự abc và những số trong cột thứ ba từ lớn nhất đến nhỏ nhất :

Sắp xếp và lọc trong Excel

Trong trường hợp khi bạn đang tìm cách lọc tài liệu với 1 số ít tiêu chuẩn và sắp xếp đầu ra theo thứ tự, hãy sử dụng những hàm SORT và LỌC với nhau :

SORT ( FILTER ( array, criteria_range = criteria ), [ sort_index ], [ sort_order ], [ by_col ] )

Hàm LỌC có một mảng những giá trị dựa trên những tiêu chuẩn bạn xác lập và chuyển mảng đó sang đối số tiên phong của SORT .

Điều tốt nhất về công thức này là nó cũng cho ra hiệu quả dưới dạng khoanh vùng phạm vi tràn động mà không cần bạn phải nhấnCtrl + Shift + Enterhoặc đoán xem có bao nhiêu ô để sao chép nó vào. Như thường lệ, bạn nhập một công thức ở ô trên cùng và nhấn phím Enter .

Ví dụ, chúng tôi sẽ trích xuất những mục có số lượng bằng hoặc lớn hơn 30 ( > = 30 ) từ tài liệu nguồn trong A2 : B9 và sắp xếp những tác dụng theo thứ tự tăng dần .

Đối với điều này, thứ nhất tất cả chúng ta thiết lập điều kiện kèm theo, giả sử, trong ô E2 như trong hình bên dưới. Và sau đó, kiến thiết xây dựng công thức Excel SORT của chúng tôi theo cách này :

=SORT(FILTER(A2:B9, B2:B9>=E2), 2)

Ngoài mảng được tạo bởi hàm FILTER, chúng tôi chỉ xác lập đối số sort_index ( cột 2 ). Hai đối số còn lại được bỏ lỡ vì mặc định hoạt động giải trí đúng chuẩn như tất cả chúng ta cần ( sắp xếp tăng dần, theo hàng ) .

Nhận N giá trị lớn nhất hoặc nhỏ nhất và sắp xếp hiệu quả
Khi nghiên cứu và phân tích số lượng lớn nếu thông tin, thường cần phải trích xuất một số ít giá trị số 1 nhất định. Có thể không chỉ giải nén, mà còn sắp xếp chúng theo thứ tự mong ước. Và lý tưởng nhất, chọn những cột để đưa vào hiệu quả. Nghe có vẻ như khó khăn vất vả ? Không phải với những tính năng mảng động mới !

Đây là một công thức chung:

INDEX(SORT(…), SEQUENCE(n), {column1_to_return, column2_to_return, …})

Trong đó n là số lượng giá trị bạn muốn trả về .

Từ tập dữ liệu bên dưới, giả sử bạn muốn có được list top 3 dựa trên những số trong cột C .

Để triển khai xong nó, thứ nhất bạn sắp xếp mảng A2 : C13 theo cột thứ 3 theo thứ tự giảm dần :

SORT ( A2 : C13, 3, – 1 )

Và sau đó, lồng công thức trên trong đối số ( mảng ) tiên phong của hàm INDEX để có mảng được sắp xếp từ cao nhất đến nhỏ nhất .

Đối với đối số thứ hai ( row_num ), cho biết có bao nhiêu hàng trả về, tạo những số thứ tự thiết yếu bằng cách sử dụng hàm SEQUENCE. Vì tất cả chúng ta cần 3 giá trị số 1, chúng tôi sử dụng SEQUENCE ( 3 ), tựa như như việc cung ứng hằng số mảng dọc { 1 ; 2 ; 3 } trực tiếp trong công thức .

Đối với đối số thứ ba ( col_num ), xác lập có bao nhiêu cột sẽ trả về, cung cấp số cột dưới dạng hằng số mảng ngang. Chúng tôi muốn trả về cột B và C, thế cho nên chúng tôi sử dụng mảng { 2,3 } .

Cuối cùng, chúng tôi nhận được công thức sau đây:

=INDEX(SORT(A2:C13, 3, -1), SEQUENCE(3), {2,3})

Và nó tạo ra tác dụng đúng mực mà tất cả chúng ta muốn :

Để trả về 3 giá trị dưới cùng, chỉ cần sắp xếp tài liệu gốc từ nhỏ nhất đến lớn nhất. Đối với điều này, đổi khác đối số sort_order từ – 1 thành 1 :

=INDEX(SORT(A2:C13, 3, 1), SEQUENCE(3), {2,3})

Trả về một giá trị được sắp xếp ở một vị trí cụ thể

Nhìn từ một góc nhìn khác, nếu bạn chỉ muốn trả về một vị trí sắp xếp đơn cử thì sao ? Nói, chỉ có bản ghi thứ 1, duy nhất thứ 2 hay chỉ bản ghi thứ 3 trong list được sắp xếp ? Để triển khai xong, hãy sử dụng phiên bản đơn giản hóa của công thức INDEX SORT đã đàm đạo ở trên :

INDEX(SORT(…), n, {column1_to_return, column2_to_return, …})

Trong đó n là vị trí chăm sóc .

Ví dụ: để có được một vị trí cụ thể từ trên xuống (tức là từ dữ liệu được sắp xếp giảm dần), hãy sử dụng công thức này:

=INDEX(SORT(A2:C13, 3, -1), F1, {2,3})

Để có được một vị trí đơn cử từ dưới lên ( tức là từ tài liệu được sắp xếp tăng dần ), hãy sử dụng vị trí này :

=INDEX(SORT(A2:C13, 3, 1), I1, {2,3})

Trong đó A2 : C13 là tài liệu nguồn, F1 là vị trí từ trên xuống, I1 là vị trí từ dưới lên và { 2,3 } là những cột được trả về .

Sử dụng bảng Excel để lấy mảng sắp xếp để tự động mở rộng

Như bạn đã biết, mảng được sắp xếp sẽ tự động hóa update khi bạn thực thi bất kể đổi khác nào so với tài liệu gốc. Đây là hành vi tiêu chuẩn của tổng thể những hàm mảng động Excel, gồm có cả SORT. Tuy nhiên, khi bạn thêm những mục mới bên ngoài mảng được tham chiếu, chúng không được tự động hóa đưa vào một công thức. Nếu bạn muốn công thức của mình cung ứng với những biến hóa đó, hãy quy đổi khoanh vùng phạm vi nguồn thành bảng Excel rất đầy đủ công dụng và sử dụng những tham chiếu có cấu trúc trong công thức của bạn .

Để xem nó hoạt động giải trí như thế nào trong trong thực tiễn, hãy xem xét ví dụ sau .

Giả sử bạn sử dụng công thức SORT Excel bên dưới để sắp xếp những giá trị trong khoanh vùng phạm vi A2 : B8 theo thứ tự bảng vần âm :

=SORT(A2:B8, 1, 1)

Sau đó, bạn nhập một mục mới trong hàng 9 Nhận và tuyệt vọng khi thấy mục nhập mới được thêm vào nằm ngoài khoanh vùng phạm vi tràn :

Bây giờ, quy đổi khoanh vùng phạm vi nguồn thành một bảng. Đối với điều này, chỉ cần chọn khoanh vùng phạm vi của bạn gồm có những tiêu đề cột ( A1 : B8 ) và nhấnCtrl + T. Khi kiến thiết xây dựng công thức của bạn, hãy chọn khoanh vùng phạm vi nguồn bằng chuột và tên bảng sẽ được tự động hóa chèn vào công thức ( đây được gọi là tham chiếu có cấu trúc ) :

=SORT(Table1, 1, 1)

Khi bạn nhập một mục mới ngay bên dưới hàng ở đầu cuối, bảng sẽ tự động hóa lan rộng ra và tài liệu mới sẽ được gồm có trong khoanh vùng phạm vi tràn của công thức SORT :

Hàm Excel SORT không hoạt động

Nếu công thức Excel SORT của bạn dẫn đến lỗi, rất hoàn toàn có thể vì những nguyên do sau .

Lỗi #NAME: phiên bản Excel cũ hơn

SORT là một tính năng mới, hiện chỉ khả dụng trong kênh ĐK Office 365 hàng tháng. Trong phiên bản Excel cũ hơn mà công dụng này không được tương hỗ, # NAME ? lỗi xảy ra .

Lỗi #SPILL: thứ gì đó chặn phạm vi tràn

Nếu một hoặc nhiều ô trong khoanh vùng phạm vi tràn không trọn vẹn trống, # SPILL ! lỗi được hiển thị. Để khắc phục, chỉ cần xóa hoặc xóa những ô không trống. Để biết thêm thông tin, vui mắt xem lỗi # SPILL với mảng động .

Lỗi #VALUE: đối số không hợp lệ

Bất cứ khi nào bạn gặp phải một # VALUE ! lỗi, kiểm tra sort_index và SORT_ORDER đối số. Sort_index không được vượt quá số lượng cột là mảng và sort_order phải là 1 ( tăng dần ) hoặc – 1 ( giảm dần ) .

Lỗi #REF: sổ làm việc nguồn đã bị đóng

Vì các mảng động có hỗ trợ hạn chế cho các tham chiếu giữa các sổ làm việc, nên hàm SORT yêu cầu cả hai tệp phải được mở. Nếu sổ làm việc nguồn bị đóng, một công thức sẽ ném #REF! lỗi. Để sửa nó, chỉ cần mở tệp được tham chiếu.

Đó là cách sắp xếp tài liệu trong Excel bằng công thức. Tôi cảm ơn bạn đã đọc và kỳ vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới !

Source: https://bem2.vn
Category: TỔNG HỢP