Thỉnh thoảng cần tạo danh sách lọc theo tiêu chí trong Excel. Bài viết demo 4 cách (Excel 365 khuyến nghị, các bản khác cũng áp dụng được).
Dữ liệu: Người – Khu vực – Xe cộ.
Mục tiêu: Liệt kê người theo Khu vực (4 vùng).
File Thực Hành
Tải workbook từ link bên dưới.
Tạo Danh Sách Dựa Trên Tiêu Chí
1. Kết Hợp INDEX-SMALL
Công thức mảng (Ctrl+Shift+Enter), kéo xuống:
=IFERROR(INDEX($B$2:$B$12,SMALL(IF($C$2:$C$12=$G$2,ROW($B$2:$B$12)),ROW(1:1))-1,1),"")
Giải thích: IF lọc hàng khớp tiêu chí → SMALL lấy hàng thứ k → INDEX lấy tên → IFERROR xử lý lỗi.
Làm tương tự cho các khu vực khác (đổi tham chiếu ô tiêu chí).
Phiên Bản INDEX-SMALL Thay Thế
Trừ 1 bên trong IF thay vì cuối 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),"")
Cũng cần Ctrl+Shift+Enter.
Đọc thêm: Cách Tạo Danh Sách Trong Một Ô Excel (3 Phương Pháp Nhanh)
2. Dùng Hàm AGGREGATE
AGGREGATE(15,6,...) thay cho SMALL – tự bỏ qua lỗi, không cần CSE trong một số trường hợp.
Bảng function_number: 1=AVERAGE, 2=COUNT, 3=COUNTA, 4=MAX, 5=MIN, 6=PRODUCT, 9=SUM, 14=LARGE, 15=SMALL.
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),"")
Đọc tương tự: Tạo To Do List, Mailing List, Numbered List Trong Excel
3. Tạo Danh Sách Duy Nhất (Unique) Bằng INDEX-MATCH-COUNTIF
Loại bỏ trùng lặp trong kết quả.
=IFERROR(INDEX($B$2:$B$12, MATCH(0, IF(G$2=$C$2:$C$12, COUNTIF($G$2:$G2, $B$2:$B$12), ""), 0)),"")
COUNTIF đếm số lần tên đã xuất hiện trong danh sách kết quả ($G$2:$G2 mở rộng khi kéo xuống) → MATCH tìm vị trí chưa xuất hiện (count=0).
Nhấn Ctrl+Shift+Enter → kéo xuống.
4. Hàm FILTER (Excel 365) – Cách Nhanh Nhất
Hàm đơn lẻ, tự tràn kết quả (spill):
=FILTER($B$2:$B$12,$C$2:$C$12=G$2)
Không cần kéo, không cần CSE.
Đọc thêm: Cách Tạo Danh Sách Theo Thứ Tự Bảng Chữ Cái (3 Cách)
Kết Luận
4 cách tạo danh sách theo tiêu chí: INDEX-SMALL (cổ điển), AGGREGATE (mạnh mẽ), INDEX-MATCH-COUNTIF (unique), FILTER (hiện đại Excel 365). Hy vọng hữu ích. Comment hoặc chia sẻ cách khác.
Đọc Thêm
- Tạo Danh Sách Dấu Chấm (Bulleted List) – 9 Cách
- Tạo Danh Sách Phân Cách Bằng Dấu Phẩy – 5 Cách
- Tạo Bảng Giá Trong Excel (Hướng Dẫn Bước Đi)