Hàm SORTBY của Excel – sắp xếp tùy chỉnh với công thức

Hướng dẫn giải thích cú pháp và cách sử dụng điển hình của hàm SORTBY mảng động mới. Xem cách sắp xếp tùy chỉnh trong Excel bằng một công thức, sắp xếp ngẫu nhiên một danh sách, sắp xếp các ô theo độ dài văn bản và hơn thế nữa.

Microsoft Excel cung cấp một số cách để sắp xếp dữ liệu văn bản theo thứ tự abc, ngày theo thời gianvà các số từ nhỏ nhất đến lớn nhất hoặc từ cao nhất đến thấp nhất. Cũng có một cách để tự sắp xếp danh sách tùy chỉnh. Ngoài chức năng Sắp xếp thông thường, Excel 365 giới thiệu một cách hoàn toàn mới để sắp xếp dữ liệu bằng các công thức – rất tiện lợi và cực kỳ đơn giản để sử dụng!

Hàm Excel SORTBY

Hàm SORTBY trong Excel được thiết kế để sắp xếp một phạm vi hoặc mảng dựa trên các giá trị trong phạm vi hoặc mảng khác. Sắp xếp có thể được thực hiện bởi một hoặc nhiều cột.

SORTBY là một trong sáu cái mới chức năng mảng động có sẵn trong Microsoft Excel 365. Kết quả của nó là một mảng động tràn sang các ô lân cận và tự động cập nhật khi dữ liệu nguồn thay đổi.

Hàm SORTBY có số lượng đối số thay đổi – hai đối số đầu tiên là bắt buộc và hai đối số khác là tùy chọn:

SORTBY (mảng, by_array1, [sort_order1], [by_array2, sort_order2], Giáo)

Mảng (bắt buộc) – phạm vi ô hoặc mảng giá trị được sắp xếp.

By_array1 (bắt buộc) – phạm vi hoặc mảng để sắp xếp theo.

Sắp xếp_order1 (tùy chọn) – thứ tự sắp xếp:

  • 1 hoặc bỏ qua (mặc định) – tăng dần
  • -1 – giảm dần

By_array2 / Sắp xếp_order2, Hoài (tùy chọn) – các cặp mảng / thứ tự bổ sung sẽ được sử dụng để sắp xếp.

Lưu ý quan trọng! Hiện tại, chức năng SORTBY chỉ khả dụng với kênh Đăng ký hàng tháng của Microsoft 365. Bắt đầu từ tháng 7 năm 2020, nó được lên kế hoạch đưa vào kênh Bán niên. Trong Excel 2019, Excel 2016 và các phiên bản trước đó, chức năng SORTBY không khả dụng.

Chức năng SORTBY – 4 điều cần nhớ

Để công thức SORTBY của Excel hoạt động chính xác, có một vài điểm quan trọng cần lưu ý:

  • By_array đối số phải cao một hàng hoặc rộng một cột.
  • Các mảng và tất cả by_array đối số phải có kích thước tương thích. Ví dụ: khi sắp xếp theo hai cột, mảng, by_array1by_array2 nên có cùng số lượng hàng; nếu không thì Lỗi #VALUE sẽ xảy ra.
  • Nếu mảng được trả về bởi SORTBY là kết quả cuối cùng (đầu ra trong một ô và không được truyền cho hàm khác), Excel sẽ tạo một động phạm vi tràn và đưa ra kết quả. Vì vậy, hãy chắc chắn rằng bạn có đủ các ô trống và / hoặc bên phải của ô nơi bạn nhập công thức, nếu không thì Lỗi #SPILL sẽ xảy ra.
  • Kết quả của các công thức SORTBY tự động cập nhật bất cứ khi nào dữ liệu nguồn thay đổi. Tuy nhiên, các mục mới được thêm bên ngoài mảng được tham chiếu trong công thức không được bao gồm trong kết quả trừ khi bạn cập nhật mảng tài liệu tham khảo. Để mảng được tham chiếu mở rộng tự động, hãy chuyển đổi phạm vi nguồn thành Bảng Excel hoặc tạo một phạm vi tên động.

Công thức SORTBY cơ bản trong Excel

Đây là một kịch bản điển hình của việc sử dụng công thức SORTBY trong Excel:

Giả sử, bạn có một danh sách các dự án với Giá trị cánh đồng. Bạn muốn sắp xếp các dự án theo giá trị của chúng trên một tờ riêng. Vì những người dùng khác không cần phải xem các số, bạn không muốn bao gồm Giá trị cột trong kết quả.

Có thể dễ dàng thực hiện tác vụ với hàm SORTBY mà bạn cung cấp các đối số sau:

  • Mảng là A2: A10 – vì bạn không muốn Giá trị cột được hiển thị trong kết quả, bạn để nó ra khỏi mảng.
  • By_array1 là B2: B10 – sắp xếp theo Giá trị.
  • Sắp xếp_order1 là -1 – giảm dần, tức là từ cao nhất đến thấp nhất.

Đặt các đối số lại với nhau, chúng ta có được công thức này:

=SORTBY(A2:B10, B2:B10, -1)

Để đơn giản, chúng tôi sử dụng công thức trên cùng một tờ – nhập nó vào D2 và nhấn nút Đi vào Chìa khóa. Kết quả “tự động” tràn đến nhiều ô nếu cần (D2: D10 trong trường hợp của chúng tôi). Nhưng về mặt kỹ thuật, công thức chỉ có trong ô đầu tiên và xóa nó khỏi D2 sẽ xóa tất cả các kết quả.
Hàm Excel SORTBY

Khi được sử dụng trên một trang tính khác, công thức có hình dạng sau:

=SORTBY(Sheet1!A2:A10, Sheet1!B2:B10, -1)

Ở đâu Tờ1 là bảng tính chứa dữ liệu gốc.

Sử dụng hàm SORTBY trong Excel – ví dụ về công thức

Dưới đây bạn sẽ tìm thấy một vài ví dụ khác về việc sử dụng SORTBY trong Excel 365, hy vọng sẽ chứng minh được sự hữu ích và sâu sắc.

Sắp xếp theo nhiều cột

Công thức cơ bản đã thảo luận ở trên sắp xếp dữ liệu theo một cột. Nhưng nếu bạn cần thêm một cấp độ sắp xếp thì sao?

Giả sử bảng mẫu của chúng tôi có hai trường, Trạng thái (cột B) và Giá trị (cột C), chúng tôi muốn sắp xếp trước Trạng thái theo thứ tự abc, và sau đó bởi Giá trị giảm dần.

Để sắp xếp theo hai cột, chúng ta chỉ cần thêm một cặp by_array / thứ tự sắp xếp tranh luận:

  • Mảng là A2: C10 – lần này, chúng tôi muốn bao gồm cả ba cột trong kết quả.
  • By_array1 là B2: B10 – đầu tiên, sắp xếp theo Trạng thái.
  • Sắp xếp_order1 là 1 – sắp xếp theo thứ tự abc từ A đến Z.
  • By_array2 là C2: C10 – sau đó, sắp xếp theo Giá trị.
  • Sắp xếp_order2 là -1 – sắp xếp từ lớn nhất đến nhỏ nhất.

Kết quả là chúng ta có được công thức sau:

=SORTBY(A2:B10, B2:B10, 1, C2:C10, -1)

Sắp xếp lại dữ liệu của chúng tôi chính xác như chúng tôi đã hướng dẫn:
Công thức Excel để sắp xếp theo nhiều cột

Sắp xếp tùy chỉnh trong Excel với công thức

Để sắp xếp dữ liệu theo thứ tự tùy chỉnh, bạn có thể sử dụng Excel Sắp xếp tùy chỉnh tính năng hoặc xây dựng công thức SORTBY MATCH theo cách này:

SORTBY (mảng, MATCH (phạm vi_to_sort, danh sách khách hàng, 0))

Nhìn kỹ hơn vào tập dữ liệu của chúng tôi, có lẽ bạn sẽ thấy thuận tiện hơn khi sắp xếp các dự án theo trạng thái “hợp lý”, ví dụ như theo mức độ quan trọng, thay vì theo thứ tự bảng chữ cái.

Để hoàn thành nó, trước tiên chúng tôi tạo một danh sách tùy chỉnh theo thứ tự sắp xếp mong muốn (Trong tiến trình, Đã hoàn thành, Chờ) nhập từng giá trị trong một ô riêng biệt trong phạm vi E2: E4.

Và sau đó, bằng cách sử dụng công thức chung ở trên, chúng tôi cung cấp phạm vi nguồn cho mảng (A2: C10), Trạng thái cột cho phạm vi_to_sort (B2: B10) và danh sách tùy chỉnh mà chúng tôi đã tạo cho danh sách khách hàng (E 2: E4).

=SORTBY(A2:C10, MATCH(B2:B10, E2:E4, 0))

Kết quả là, chúng tôi đã có các dự án được sắp xếp theo trạng thái của chúng chính xác khi cần:
Công thức Excel để sắp xếp theo danh sách tùy chỉnh

Để sắp xếp theo danh sách tùy chỉnh theo thứ tự ngược lại, đặt -1 cho sort_order1 tranh luận:

=SORTBY(A2:C10, MATCH(B2:B10, E2:E4, 0), -1)

Và bạn sẽ có các dự án được sắp xếp theo hướng ngược lại:
Sắp xếp theo danh sách tùy chỉnh theo thứ tự ngược lại

Bạn muốn sắp xếp bổ sung hồ sơ trong mỗi trạng thái? Không vấn đề gì. Đơn giản, thêm một cấp độ sắp xếp vào công thức, nói Giá trị (C2: C10) và xác định thứ tự sắp xếp mong muốn, tăng dần trong trường hợp của chúng tôi:

=SORTBY(A2:C10, MATCH(B2:B10, E2:E5, 0), 1, C2:C10, 1)

Sắp xếp tùy chỉnh với nhiều cấp độ sắp xếp

Một lợi thế lớn của công thức SORTBY so với tính năng Sắp xếp tùy chỉnh của Excel là công thức tự động cập nhật bất cứ khi nào dữ liệu gốc thay đổi, trong khi tính năng yêu cầu dọn dẹp và sắp xếp lại với mỗi thay đổi.

Cách thức hoạt động của công thức này:

Như đã đề cập, hàm SORTBY của Excel chỉ có thể xử lý các mảng “sắp xếp theo” có kích thước tương thích với mảng nguồn. Vì mảng nguồn của chúng tôi (C2: C10) chứa 9 hàng và danh sách tùy chỉnh (E2: E4) chỉ có 3 hàng, chúng tôi không thể cung cấp trực tiếp cho by_array tranh luận. Thay vào đó, chúng tôi sử dụng TRẬN ĐẤU Hàm tạo mảng 9 hàng:

MATCH(B2:B10, E2:E5, 0)

Ở đây, chúng tôi sử dụng Trạng thái cột (B2: B10) làm giá trị tra cứu và danh sách tùy chỉnh của chúng tôi (E2: E5) dưới dạng mảng tra cứu. Đối số cuối cùng được đặt thành 0 để tìm kiếm các kết quả khớp chính xác. Kết quả là chúng ta có được một mảng gồm 9 số, mỗi số đại diện cho vị trí tương đối của một số đã cho Trạng thái giá trị trong danh sách tùy chỉnh:

{1;3;2;1;3;2;2;1;2}

Mảng này đi trực tiếp đến by_array đối số của hàm SORTBY và buộc nó đặt dữ liệu theo thứ tự tương ứng với các phần tử của mảng, tức là các mục đầu tiên được đại diện bởi 1, sau đó các mục được biểu thị bằng 2, v.v.

Sắp xếp ngẫu nhiên trong Excel với công thức

Trong các phiên bản trước của Excel, bạn có thể sắp xếp ngẫu nhiên với RAND chức năng như được giải thích trong hướng dẫn này: Cách sắp xếp ngẫu nhiên một danh sách trong Excel.

Trong Excel 365, bạn có thể sử dụng mạnh hơn NGÀY LỄ hoạt động cùng với SORTBY:

NGẮNmảng, RANDARRAY (ROWS (mảng)))

Ở đâu mảng là dữ liệu nguồn mà bạn muốn xáo trộn.

Công thức chung này hoạt động cho một danh sách bao gồm một cột đơn cũng như cho một phạm vi nhiều cột.

Ví dụ: để sắp xếp ngẫu nhiên một danh sách trong A2: A10, hãy sử dụng công thức này:

=SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10)))

Công thức sắp xếp ngẫu nhiên một danh sách trong Excel

Để xáo trộn dữ liệu trong A2: C10 giữ các hàng lại với nhau, hãy sử dụng dữ liệu này:

=SORTBY(A2:C10, RANDARRAY(ROWS(A2:C10)))

Công thức sắp xếp ngẫu nhiên dữ liệu trong Excel giữ các hàng lại với nhau

Cách thức hoạt động của công thức này:

Hàm RANDARRAY tạo ra một mảng các số ngẫu nhiên được sử dụng để sắp xếp và bạn chuyển nó vào trong by_array đối số của SORTBY. Để chỉ định số lượng số ngẫu nhiên sẽ tạo, bạn đếm số lượng hàng trong phạm vi nguồn bằng cách sử dụng hàm ROWS và “cung cấp” số đó cho hàng lập luận của RANDARRAY. Đó là nó!

Ghi chú. Giống như người tiền nhiệm của nó, RANDARRAY là một hàm dễ bay hơi và nó tạo ra một mảng các số ngẫu nhiên mới mỗi khi bảng tính được tính toán lại. Do đó, dữ liệu của bạn sẽ được sử dụng với mỗi thay đổi trên trang tính. Để ngăn chặn tự động nghỉ mát, bạn có thể sử dụng Dán đặc biệt > Giá trị tính năng để thay thế công thức bằng các giá trị của chúng.

Sắp xếp các ô theo độ dài chuỗi

Để sắp xếp các ô theo độ dài của chuỗi văn bản mà chúng chứa, hãy sử dụng Chức năng LEN để đếm số lượng ký tự trong mỗi ô và cung cấp độ dài tính toán cho by_array đối số của SORTBY. Các thứ tự sắp xếp đối số có thể được đặt thành 1 hoặc -1, tùy thuộc vào thứ tự ưu tiên sắp xếp.

Để sắp xếp theo chuỗi văn bản từ nhỏ nhất đến lớn nhất:

SORTBY (mảng, LEN (mảng), 1)

Để sắp xếp theo chuỗi văn bản từ lớn nhất đến nhỏ nhất:

SORTBY (mảng, LEN (mảng), -1)

Và đây là một công thức thể hiện cách tiếp cận này trên dữ liệu thực:

=SORTBY(A2:A7, LEN(A2:A7), 1)

Trong đó A2: A7 là các ô ban đầu bạn muốn sắp xếp theo độ dài văn bản theo thứ tự tăng dần:
Công thức sắp xếp các ô theo độ dài chuỗi

SORTBY so với SORT

Trong nhóm mới Hàm mảng động Excel, có hai thiết kế để phân loại. Dưới đây chúng tôi liệt kê những khác biệt và điểm tương đồng cần thiết nhất cũng như khi nào là tốt nhất để sử dụng.

  • Không giống như hàm SORT, SORTBY không yêu cầu mảng “sort by” là một phần của mảng nguồn, cũng không cần phải xuất hiện trong kết quả. Vì vậy, khi nhiệm vụ của bạn là sắp xếp một phạm vi dựa trên một mảng độc lập khác hoặc một danh sách tùy chỉnh, SORTBY là chức năng phù hợp để sử dụng. Nếu bạn đang tìm cách sắp xếp một phạm vi dựa trên các giá trị của chính nó, thì SORT phù hợp hơn.
  • Cả hai chức năng đều hỗ trợ nhiều cấp độ sắp xếp và cả hai có thể được kết nối cùng với các hàm động và các hàm thông thường khác.
  • Cả hai chức năng chỉ có sẵn cho người dùng Excel 365.

Chức năng Excel SORTBY không hoạt động

Trong trường hợp công thức SORTBY của bạn trả về lỗi, rất có thể vì một trong những lý do sau.

Đối số by_array không hợp lệ

Các by_array đối số phải là một hàng đơn hoặc một cột đơn và có kích thước tương thích với mảng tranh luận. Ví dụ, nếu mảng có 10 hàng by_array cũng nên bao gồm 10 hàng. Nếu không thì #VALUE! lỗi xảy ra.

Đối số sort_order không hợp lệ

Các thứ tự sắp xếp đối số chỉ có thể là 1 (tăng dần) hoặc -1 (giảm dần). Nếu không có giá trị nào được đặt, SORTBY mặc định theo thứ tự tăng dần. Nếu bất kỳ giá trị nào khác được đặt, #VALUE! lỗi được trả lại.

Không có đủ không gian cho kết quả

Giống như bất kỳ chức năng mảng động nào khác, SORTBY sẽ đưa các kết quả vào một phạm vi có thể thay đổi kích thước và tự động cập nhật. Nếu không có đủ ô trống để chứa đầy đủ phạm vi tràn, một #SPILL! lỗi được hiển thị.

Sổ làm việc nguồn đã bị đóng

Nếu công thức SORTBY tham chiếu một tệp Excel khác, cả hai sổ làm việc cần phải được mở. Nếu sổ làm việc nguồn bị đóng, #REF! lỗi xảy ra.

Phiên bản Excel của bạn không hỗ trợ mảng động

Khi được sử dụng trong phiên bản tiền động của Excel (trừ Excel 365), hàm SORT trả về #NAME? lỗi.

Đó là cách sử dụng hàm SORTBY trong Excel để thực hiện sắp xếp tùy chỉnh và những thứ khá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!

Bài tập thực hành để tải về

Công thức SORTBY của Excel

Bạn cũng có thể quan tâm:

Source link

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *