Nội dung
Nếu bạn cần sắp xếp danh sách, thì có rất nhiều cách tại dịch vụ của bạn, trong đó dễ nhất là các nút sắp xếp trên tab hoặc trong menu Ngày (Dữ liệu - Sắp xếp). Tuy nhiên, có những tình huống khi việc sắp xếp danh sách cần được thực hiện tự động, tức là các công thức. Điều này có thể được yêu cầu, ví dụ, khi tạo dữ liệu cho danh sách thả xuống, khi tính toán dữ liệu cho biểu đồ, v.v. Làm thế nào để sắp xếp danh sách với công thức một cách nhanh chóng?
Phương pháp 1. Dữ liệu số
Nếu danh sách chỉ chứa thông tin số, thì việc sắp xếp nó có thể dễ dàng thực hiện bằng cách sử dụng các hàm ÍT NHẤT (NHỎ BÉ) и ĐƯỜNG DÂY (HÀNG NGANG):
Chức năng ÍT NHẤT (NHỎ BÉ) kéo ra khỏi mảng (cột A) phần tử nhỏ nhất thứ n trong một hàng. Những thứ kia. SMALL (A: A; 1) là số nhỏ nhất trong cột, SMALL (A: A; 2) là số nhỏ thứ hai, v.v.
Chức năng ĐƯỜNG DÂY (HÀNG NGANG) trả về số hàng cho ô được chỉ định, tức là ROW (A1) = 1, ROW (A2) = 2, v.v. Trong trường hợp này, nó được sử dụng đơn giản như một bộ tạo chuỗi số n = 1,2,3… cho danh sách được sắp xếp của chúng tôi. Với thành công tương tự, có thể tạo thêm một cột, điền vào nó theo cách thủ công với dãy số 1,2,3… và tham chiếu đến nó thay vì hàm ROW.
Phương pháp 2. Danh sách văn bản và công thức thông thường
Nếu danh sách không chứa số mà là văn bản, thì chức năng NHỎ sẽ không hoạt động nữa, vì vậy bạn phải đi một con đường khác, dài hơn một chút,.
Đầu tiên, hãy thêm một cột dịch vụ với một công thức trong đó số sê-ri của mỗi tên trong danh sách được sắp xếp trong tương lai sẽ được tính bằng cách sử dụng hàm COUNTIF (ĐẾM):
Trong phiên bản tiếng Anh, nó sẽ là:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Số hạng đầu tiên là một hàm để đếm số lượng ô nhỏ hơn số hiện tại. Thứ hai là mạng lưới an toàn trong trường hợp bất kỳ tên nào xảy ra nhiều hơn một lần. Sau đó, chúng sẽ không có cùng một số lượng, nhưng tăng liên tục.
Bây giờ các số nhận được phải được sắp xếp tuần tự theo thứ tự tăng dần. Đối với điều này, bạn có thể sử dụng chức năng ÍT NHẤT (NHỎ BÉ) từ cách đầu tiên:
Cuối cùng, nó vẫn chỉ là rút ra các tên khỏi danh sách theo số của chúng. Để làm điều này, bạn có thể sử dụng công thức sau:
Chức năng THÊM ĐƯỢC TIẾP XÚC (CUỘC THI ĐẤU) tìm kiếm trong cột B cho số sê-ri mong muốn (1, 2, 3, v.v.) và trên thực tế, trả về số của dòng chứa số này. Hàm số INDEX (MỤC LỤC) kéo ra từ cột A tên ở dòng này.
Phương pháp 3: Công thức mảng
Trên thực tế, phương pháp này là cùng một thuật toán vị trí như trong Phương pháp-2, nhưng được thực hiện bởi một công thức mảng. Để đơn giản hóa công thức, phạm vi ô C1: C10 đã được đặt tên Danh sách (chọn ô, nhấn Ctrl + F3 và nút Tạo):
Trong ô E1, sao chép công thức của chúng tôi:
=INDEX(Danh sách; MATCH(SMALL(COUNTIF(Danh sách; “<"&Danh sách); ROW(1:1)); COUNTIF(Danh sách; "<"&Danh sách); 0))
Hoặc trong phiên bản tiếng Anh:
=INDEX(Danh sách, MATCH(SMALL(COUNTIF(Danh sách, «<"&Danh sách), ROW(1:1)), COUNTIF(Danh sách, "<"&Danh sách), 0))
và đẩy Ctrl + Shift + Enterđể nhập nó dưới dạng công thức mảng. Sau đó, công thức kết quả có thể được sao chép toàn bộ chiều dài của danh sách.
Nếu bạn muốn công thức không tính đến một phạm vi cố định, nhưng có thể điều chỉnh khi thêm các phần tử mới vào danh sách, thì bạn sẽ cần phải thay đổi một chút chiến lược.
Đầu tiên, phạm vi Danh sách sẽ cần được đặt động. Để làm điều này, khi tạo, bạn không cần chỉ định một phạm vi cố định C3: C10, mà là một công thức đặc biệt sẽ tham chiếu đến tất cả các giá trị có sẵn, bất kể số lượng của chúng. Nhấp chuột Alt + F3 hoặc mở tab Công thức - Trình quản lý Tên (Công thức - Trình quản lý tên), tạo một tên mới và trong trường liên kết (Tài liệu tham khảo) nhập công thức sau (tôi giả sử rằng phạm vi dữ liệu được sắp xếp bắt đầu từ ô C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
= OFFSET (C1,0,0, SCHÖTZ (C1: C1000), 1)
Thứ hai, công thức mảng ở trên sẽ cần được kéo dài ra với một lề - với kỳ vọng sẽ có thêm dữ liệu được nhập trong tương lai. Trong trường hợp này, công thức mảng sẽ bắt đầu đưa ra lỗi #NUMBER trên các ô chưa được điền. Để chặn nó, bạn có thể sử dụng hàm SỐ PHIẾU, cần được thêm vào "xung quanh" công thức mảng của chúng ta:
=IFERROR (INDEX(Danh sách; MATCH(SMALL(COUNTIF(Danh sách; “<"&Danh sách); ROW(1:1)); COUNTIF(Danh sách; "<"&Danh sách); 0)); »»)
=IFERROR(NDEX(Danh sách, MATCH(SMALL(COUNTIF(Danh sách, «<"&Danh sách), ROW(1:1)), COUNTIF(Danh sách, "<"&Danh sách), 0));"")
Nó bắt lỗi #NUMBER và thay vào đó xuất ra một khoảng trống (dấu ngoặc kép trống).
:
- Sắp xếp phạm vi theo màu sắc
- Công thức mảng là gì và tại sao chúng lại cần
- Sắp xếp SORT và các mảng động trong Office 365 mới