Trong Excel, đôi khi bạn có thể cần tạo danh sách dựa trên tiêu chí. Hôm nay chúng tôi sẽ hướng dẫn bạn cách tạo danh sách dựa trên các tiêu chí. Đối với phiên này, chúng tôi đang sử dụng Excel 365, mặc dù chúng tôi khuyên bạn nên sử dụng phiên bản này, hãy thoải mái sử dụng phiên bản của bạn.
Điều đầu tiên, hãy tìm hiểu về tập dữ liệu là cơ sở cho các ví dụ của chúng tôi.
Ở đây chúng tôi có một tập dữ liệu về một số người từ các địa điểm khác nhau cùng với phương tiện của họ. Sử dụng dữ liệu này, chúng tôi sẽ hình thành một danh sách dựa trên các tiêu chí.
Lưu ý rằng đây là một bảng cơ bản với dữ liệu giả để giữ cho mọi thứ đơn giản. Trong một tình huống thực tế, bạn có thể gặp phải một tập dữ liệu lớn hơn và phức tạp hơn nhiều.
Sách bài tập Thực hành
Bạn có thể tải xuống sách bài tập thực hành từ liên kết sau.
Tạo danh sách dựa trên tiêu chí
Ví dụ:chúng tôi sẽ tạo danh sách những người dựa trên khu vực của họ.
Vì đó là một tập dữ liệu nhỏ nên chúng tôi biết có 4 khu vực. Chúng tôi đã lưu trữ tên của các khu vực và sẽ tìm danh sách dựa trên khu vực đó.
1. Sử dụng kết hợp INDEX-SMALL để tạo danh sách
Ở đây chúng ta cần một danh sách, vì vậy công thức của chúng ta phải là một công thức sẽ truy xuất nhiều giá trị từ bảng. Đối với tác vụ đó, chúng tôi có thể sử dụng kết hợp INDEX và NHỎ chức năng.
Để biết các chức năng này, hãy xem các bài viết sau: INDEX, SMALL.
Cùng với hai hàm này, chúng ta sẽ cần một vài hàm trợ giúp, IF , ROW và IFERROR . Kiểm tra các bài viết để biết thêm thông tin: IF, ROW, IFERROR.
Hãy cùng khám phá công thức
=IFERROR(INDEX($B$2:$B$12,SMALL(IF($C$2:$C$12=$G$2,ROW($B$2:$B$12)),ROW(1:1))-1,1),"")
Ở đây mọi chức năng đều có mục đích của nó. INDEX hàm trả về giá trị từ mảng B2:B12 (Cột tên) và NHỎ lớn phần cung cấp số hàng sẽ được tìm nạp.
NẾU, trong NHỎ, kiểm tra xem các tiêu chí có phù hợp hay không và ROW hàm lặp qua các ô của cột.
Sau đó, ROW bên ngoài biểu thị giá trị thứ k cho giá trị NHỎ hàm số. Các hàm này kết hợp với nhau trả về số hàng và INDEX trả về kết quả.
IFERROR để đối phó với bất kỳ lỗi nào có thể phát sinh từ công thức.
Kéo xuống, bạn sẽ có tất cả những người từ khu vực nhất định.
Tương tự, hãy viết công thức cho các vùng khác (công thức giống nhau, chỉ dịch chuyển ô).
Kết hợp INDEX-NHỎ thay thế
Chúng ta có thể viết công thức theo một cách khác. Các hàm được sử dụng cho công thức sẽ giống như trước đó. Chỉ có bản trình bày sẽ khác.
Hãy xem công thức
=IFERROR(INDEX($B$2:$B$12,SMALL(IF($C$2:$C$12=G$2,ROW($B$2:$B$12)-1),ROW(1:1)),1),"") Một lần nữa, bạn cần nhấn CTRL + SHIFT + ENTER để thực thi công thức.
Có một chút khác biệt giữa hai công thức này, bạn có thể phân biệt chúng không?
Có, trong công thức trước đó của chúng tôi, chúng tôi đã trừ đi 1 ở cuối SMALL nhưng ở đây chúng tôi đã trừ đi 1 trong IF phần.
Mục đích của phép trừ 1 là chuyển kênh đến số hàng thích hợp. Trước đó, chúng tôi đã làm điều đó cuối cùng, tại đây đã làm điều đó sớm hơn và tiếp tục hoạt động tiếp theo.
Viết công thức cho các tiêu chí khác để hoàn thành danh sách.
Đọc thêm:Cách tạo danh sách trong ô trong Excel (3 phương pháp nhanh)
2. Sử dụng hàm AGGREGATE để tạo danh sách
Excel cung cấp cho bạn một hàm có tên là AGGREGATE mà bạn có thể sử dụng để thực hiện các tác vụ khác nhau. Tại đây, chúng ta có thể sử dụng hàm để tạo danh sách dựa trên các tiêu chí.
ĐỒNG Ý hàm trả về một phép tính tổng hợp như AVERAGE, COUNT, MAX, v.v.
Cú pháp cho AGGREGATE chức năng như sau:
AGGREGATE(function_number,behavior_options, range) function_number: Con số này chỉ định phép tính nào nên được thực hiện.
behavior_options: Đặt số này bằng cách sử dụng. Con số này biểu thị cách hàm sẽ hoạt động.
phạm vi: Phạm vi bạn muốn tổng hợp.
ĐỒNG Ý hàm thực hiện một số tác vụ vì vậy số lượng hàm được xác định trước bên trong nó. Chúng tôi liệt kê một vài số hàm thường được sử dụng
| Hàm | Hàm_số |
|---|---|
| AVERAGE | 1 |
| COUNT | 2 |
| COUNTA | 3 |
| MAX | 4 |
| MIN | 5 |
| SẢN PHẨM | 6 |
| SUM | 9 |
| LARGE | 14 |
| NHỎ | 15 |
Để biết thêm về chức năng, hãy truy cập Hỗ trợ của Microsoft trang web.
Bây giờ chúng ta hãy xem công thức,
=IFERROR(INDEX($B$2:$B$12,AGGREGATE(15,6,IF($C$2:$C$12=G$2,ROW($B$2:$B$12)-1),ROW(1:1)),1),"")
Đây cùng với AGGREGATE , chúng tôi đã sử dụng INDEX . INDEX giữ mảng trả về các giá trị dựa trên các kết quả phù hợp được tìm thấy ở phần sau của công thức.
Bạn có thể thấy rằng chúng tôi đã sử dụng 15 dưới dạng function_number trong AGGREGATE . Từ bảng trên, bạn có thể thấy 15 cuộc gọi cho NHỎ hoạt động chức năng. Bây giờ bạn có thể liên hệ?
Có, chúng tôi đã thực thi INDEX-SMALL công thức theo cách của AGGREGATE chức năng.
6 đối với tùy chọn hành vi, biểu thị bỏ qua các giá trị lỗi .
Viết công thức cho các giá trị còn lại.
Bài đọc tương tự
- Cách tạo danh sách việc cần làm trong Excel (3 phương pháp dễ dàng)
- Tạo Danh sách Gửi thư trong Excel (2 Phương pháp)
- Cách tạo danh sách được đánh số trong Excel (8 phương pháp)
3. Tạo danh sách duy nhất bằng INDEX-MATCH-COUNTIF
Chúng tôi có thể tạo một danh sách duy nhất dựa trên các tiêu chí. Để làm được điều đó, chúng tôi có thể sử dụng kết hợp INDEX , TRẬN ĐẤU và COUNTIF .
COUNTIF đếm các ô trong một phạm vi đáp ứng một điều kiện duy nhất. Và TRẬN ĐẤU xác định vị trí của giá trị tra cứu trong một phạm vi. Để biết thêm về các chức năng này, hãy truy cập các bài viết sau: TRẬN ĐẤU, COUNTIF.
Hãy cùng khám phá công thức
=IFERROR(INDEX($B$2:$B$12, MATCH(0, IF(G$2=$C$2:$C$12, COUNTIF($G$2:$G2, $B$2:$B$12), ""), 0)),"")
Trong công thức này: B2:B12 là phạm vi cột chứa các giá trị duy nhất mà bạn muốn trích xuất, C2:C12 là cột chứa tiêu chí mà bạn dựa trên G2 chỉ ra tiêu chí.
Trong TRẬN ĐẤU , chúng tôi đã cung cấp 0 dưới dạng lookup_array, và cho lookup_range chúng tôi đã sử dụng IF phần chứa COUNTIF . Vì vậy, phần này trả về giá trị miễn là 0 được tìm thấy. Giá trị ở đây hoạt động như số hàng cho INDEX .
Kéo nó xuống và bạn sẽ tìm thấy tất cả các giá trị duy nhất.
Đừng quên sử dụng CTRL + SHIFT + ENTER để thực thi công thức.
Đây là một đề cập danh giá về các phương pháp tiếp cận để tạo ra một danh sách duy nhất. Theo dõi bài viết này để biết về cách tạo danh sách duy nhất dựa trên tiêu chí .
4. Sử dụng hàm FILTER để tạo danh sách dựa trên tiêu chí
Nếu bạn đang sử dụng Excel 365, thì bạn có thể thực hiện tác vụ với một hàm tích hợp duy nhất có tên là FILTER .
BỘ LỌC hàm lọc một loạt dữ liệu dựa trên các tiêu chí đã cho và trích xuất các bản ghi phù hợp. Để biết về chức năng, hãy truy cập bài viết này: FILTER .
Bây giờ, công thức của chúng ta sẽ là công thức sau đây,
=FILTER($B$2:$B$12,$C$2:$C$12=G$2)
B2:B12 là mảng sẽ được lọc. Sau đó, chúng tôi đã cung cấp điều kiện, dựa trên những gì chúng tôi sẽ tạo danh sách.
Tại đây, bạn sẽ không cần phải kéo công thức xuống, ngay lập tức, công thức này sẽ cung cấp tất cả các giá trị và hoàn thành danh sách.
Đọc thêm: Cách tạo danh sách theo thứ tự bảng chữ cái trong Excel (3 cách)
Kết luận
Đó là tất cả cho ngày hôm nay. Chúng tôi đã liệt kê một số cách để tạo danh sách dựa trên các tiêu chí. Hy vọng bạn sẽ thấy điều này hữu ích. Vui lòng bình luận nếu bất cứ điều gì có vẻ khó hiểu. Hãy cho chúng tôi biết bất kỳ phương pháp nào khác mà chúng tôi đã bỏ qua tại đây.
Bài đọc thêm
- Cách Tạo Danh sách Dấu đầu dòng trong Excel (9 Phương pháp)
- Tạo Danh sách được Phân tách bằng Dấu phẩy trong Excel (5 Phương pháp)
- Cách tạo bảng giá trong Excel (Hướng dẫn từng bước)