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 dữ 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 để mở rộng tự động
Hàm Excel SORT không hoạt động
Hàm Excel SORT
Hàm SORT trong Excel sắp xếp nội dung của một mảng hoặc 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 các giá trị hoặc một phạm vi ô để sắp xếp. Đây có thể là bất kỳ giá trị nào bao gồm văn bản, số, ngày, thời gian, 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ỏ qua (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 điểm mà bạn cần lưu ý:
 
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 kết quả cập nhật động khi dữ liệu nguồn thay đổi. Tuy nhiên, mảng được cung cấp cho công thức không tự động mở rộng để bao gồm các mục mới được thêm bên ngoài mảng được tham chiếu . Để bao gồm các mục như vậy, bạn cần cập nhật tham chiếu mảng trong công thức của mình hoặc chuyển đổi phạm vi nguồn thành bảng Excel như trong ví dụ này hoặc tạo một 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 dữ liệu trong Excel theo thứ tự tăng dần và giảm dần.
 
Giả sử dữ liệu của bạn được sắp xếp theo thứ tự abc như trong ảnh chụp màn hình bên dưới. Bạn đang tìm cách sắp xếp các số trong cột B mà không phá vỡ hoặc trộn dữ liệu.
 
Công thức sắp xếp theo thứ tự tăng dần
Để sắp xếp các 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ì dữ liệu của chúng tôi được sắp xếp theo hàng, nên đối số cuối cùng có thể được bỏ qua để 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à kết quả sẽ tự động tràn sang D2: E8.
 

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

Để sắp xếp dữ 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 phạm vi đích và bạn sẽ nhận được kết quả này:

Theo cách tương tự, bạn có thể sắp xếp các giá trị văn bản theo thứ tự bảng chữ cái 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 điển hình 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 cho một hàng, không phải là một cột.
 
Ví dụ: để sắp xếp dữ 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à dữ 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 các 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 các 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 làm việc với các mô hình dữ liệu phức tạp, bạn có thể thường cần một loại sắp xếp đa cấp. Điều đó có thể được thực hiện với một công thức? Đúng, dễ dàng! Những gì bạn làm là cung cấp 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à dữ liệu trong A2: C13.
Sort_index là hằng số mảng {1,3}, vì trước tiên 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ỏ qua vì chúng tôi sắp xếp các hàng, mặc định.
Đặt các đối số lại với nhau, chúng ta có được công thức này:
 
=SORT(A2:C13, {1,3}, {1,-1})
 
Và nó hoạt động hoàn hảo! Các giá trị văn bản trong cột đầu tiên được sắp xếp theo thứ tự abc và các 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 dữ liệu với một số tiêu chí và sắp xếp đầu ra theo thứ tự, hãy sử dụng các 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 các giá trị dựa trên các tiêu chí bạn xác định và chuyển mảng đó sang đối số đầu tiên của SORT.
 
Điều tốt nhất về công thức này là nó cũng cho ra kết quả dưới dạ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 các mục có số lượng bằng hoặc lớn hơn 30 (> = 30) từ dữ liệu nguồn trong A2: B9 và sắp xếp các kết quả theo thứ tự tăng dần.
 
Đối với điều này, trước tiên chúng ta thiết lập điều kiện, giả sử, trong ô E2 như trong hình bên dưới. Và sau đó, 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 định đối số sort_index (cột 2). Hai đối số còn lại được bỏ qua vì mặc định hoạt động chính xác như 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 kết quả
Khi 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ố giá trị hàng đầu 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 muốn. Và lý tưởng nhất, chọn các cột để đưa vào kết quả. Nghe có vẻ khó khăn? Không phải với các chức 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 danh sách top 3 dựa trên các số trong cột C.
 
Để hoàn thành nó, trước tiên 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 ) đầu tiên 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 các số thứ tự cần thiết bằng cách sử dụng hàm SEQUENCE. Vì chúng ta cần 3 giá trị hàng đầu, chúng tôi sử dụng SEQUENCE (3), tương tự như việc cung cấp 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 định 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, vì vậy 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 kết quả chính xác mà chúng ta muốn:

Để trả về 3 giá trị dưới cùng , chỉ cần sắp xếp dữ liệu gốc từ nhỏ nhất đến lớn nhất. Đối với điều này, thay đổi đố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 độ khác, nếu bạn chỉ muốn trả về một vị trí sắp xếp cụ thể thì sao? Nói, chỉ có bản ghi thứ 1, duy nhất thứ 2 hay chỉ bản ghi thứ 3 trong danh sách được sắp xếp? Để hoàn thành, hãy sử dụng phiên bản đơn giản hóa của công thức INDEX SORT đã thảo luận ở trên:
 
INDEX(SORT(…), n, {column1_to_return, column2_to_return, …})
Trong đó n là vị trí quan tâm.
 
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í cụ thể từ dưới lên (tức là từ dữ 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à dữ 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à các 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 cập nhật khi bạn thực hiện bất kỳ thay đổi nào đối với dữ liệu gốc. Đây là hành vi tiêu chuẩn của tất cả các hàm mảng động Excel, bao gồm cả SORT. Tuy nhiên, khi bạn thêm các mục mới bên ngoài mảng được tham chiếu, chúng không được tự động đưa vào một công thức. Nếu bạn muốn công thức của mình đáp ứng với những thay đổi đó, hãy chuyển đổi phạm vi nguồn thành bảng Excel đầy đủ chức năng và sử dụng các tham chiếu có cấu trúc trong công thức của bạn.
 
Để xem nó hoạt động như thế nào trong thực tế, 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 các giá trị trong phạm vi A2: B8 theo thứ tự bảng chữ cái:
 
=SORT(A2:B8, 1, 1)
 
Sau đó, bạn nhập một mục mới trong hàng 9 Nhận và thất vọng khi thấy mục nhập mới được thêm vào nằm ngoài phạm vi tràn:

 

Bây giờ, chuyển đổi phạm vi nguồn thành một bảng. Đối với điều này, chỉ cần chọn phạm vi của bạn bao gồm các tiêu đề cột (A1: B8) và nhấnCtrl + T. Khi xây dựng công thức của bạn, hãy chọn phạm vi nguồn bằng chuột và tên bảng sẽ được tự động 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 cuối cùng, bảng sẽ tự động mở rộng và dữ liệu mới sẽ được bao gồm trong 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 có thể vì những lý do sau.
 
Lỗi #NAME: phiên bản Excel cũ hơn
SORT là một chức năng mới, hiện chỉ khả dụng trong kênh đăng ký Office 365 hàng tháng. Trong phiên bản Excel cũ hơn mà chức năng này không được hỗ trợ, #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 phạm vi tràn không hoàn toàn trống, #SPILL! lỗi được hiển thị. Để khắc phục, chỉ cần xóa hoặc xóa các ô không trống. Để biết thêm thông tin, vui lòng 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 dữ liệu trong Excel bằng công thức. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!

 

Các tin cũ hơn