Khi làm báo cáo, bạn thường cần lọc danh sách khách hàng theo nhiều tiêu chí cùng lúc. Tuy nhiên, Excel mặc định không có sẵn hàm để xử lý việc này. Bài viết dưới đây của ketoan.vn sẽ hướng dẫn bạn cách kết hợp các hàm liệt kê danh sách có điều kiện trong excel để liệt kê danh sách thỏa mãn nhiều điều kiện, áp dụng ngay cho công việc.
1. Các hàm thường dùng để liệt kê dữ liệu theo nhiều điều kiện trong Excel
Trong Excel, để liệt kê và trích xuất dữ liệu theo nhiều điều kiện, bạn không thể chỉ dựa vào các hàm cơ bản như VLOOKUP hay FILTER. Thay vào đó, bạn cần kết hợp những hàm chuyên dụng vừa giúp phát hiện lỗi, vừa hỗ trợ lấy dữ liệu theo vị trí và sắp xếp thứ hạng. Dưới đây là bốn hàm quan trọng thường dùng trong công việc liệt kê dữ liệu:
1.1 Hàm IFERROR
Hàm IFERROR được sử dụng để trả về một giá trị thay thế khi công thức trong Excel gặp lỗi. Nhờ đó, bạn có thể xử lý và hiển thị dữ liệu mong muốn thay vì các thông báo lỗi (#N/A, #REF,…).
Cú pháp:
=IFERROR(value, value_if_error)
- Value: Giá trị hoặc công thức cần kiểm tra lỗi.
- Value_if_error: Giá trị thay thế sẽ trả về khi Value gặp lỗi.
Lưu ý:
- Nếu không có lỗi, kết quả sẽ là Value.
- Nếu có lỗi, kết quả sẽ là Value_if_error, có thể là chuỗi rỗng (“”), số 0 hoặc một thông báo tùy chọn.
1.2 Hàm INDEX (dạng mảng)
Hàm INDEX dạng mảng giúp lấy giá trị từ một ô trong vùng dữ liệu dựa trên số hàng (row_num) và số cột (column_num).
Cú pháp:
=INDEX(array, row_num, [column_num])
- Array: Vùng dữ liệu chứa giá trị.
- Row_num: Số hàng cần lấy.
- Column_num: Số cột cần lấy (tùy chọn).
Lưu ý:
Cần có ít nhất Row_num hoặc Column_num, nếu không sẽ báo lỗi.
1.3 Hàm INDEX (dạng tham chiếu)
Hàm INDEX dạng tham chiếu trả về ô nằm tại giao điểm giữa một hàng (row) và một cột (column) trong một vùng tham chiếu.
Cú pháp:
=INDEX(reference, row_num, [column_num], [area_num])
- Reference: Vùng dữ liệu cố định.
- Row_num: Vị trí hàng.
- Column_num: Vị trí cột (tùy chọn).
- Area_num: Số thứ tự của vùng dữ liệu trong Reference (nếu có nhiều vùng).
Lưu ý:
Nếu không cần chỉ định Area_num, Excel sẽ mặc định chọn vùng đầu tiên.
1.4 Hàm SMALL
Hàm SMALL trả về giá trị nhỏ thứ k trong một tập dữ liệu số. Đây là cách nhanh để tìm ra giá trị theo thứ hạng cụ thể.
Cú pháp:
=SMALL(array, k)
- Array: Vùng dữ liệu số.
- K: Thứ tự giá trị cần tìm (ví dụ: nhỏ thứ 2, nhỏ thứ 3…).
Lưu ý:
- Nếu Array trống, hàm trả về lỗi #NUM!.
- Nếu k ≤ 0 hoặc k lớn hơn số phần tử trong Array, cũng trả về #NUM!.
Ví dụ:
Với vùng dữ liệu C5:C9, nhập công thức:
=SMALL(C5:C9,4)
Nhấn Enter → Excel trả về giá trị nhỏ thứ 4 trong vùng.
2. Cách kết hợp INDEX, SMALL, IFERROR để lọc dữ liệu nhiều điều kiện
Mục tiêu của việc kết hợp hàm liệt kê danh sách có điều kiện trong excel là giúp bạn biết cách kết hợp các hàm đã giới thiệu ở trên để tạo công thức liệt kê danh sách.
Ví dụ minh họa: Giả sử bạn có bảng dữ liệu doanh thu và muốn liệt kê chi tiết doanh thu theo cửa hàng tại chi nhánh Hồ Chí Minh.
Bước 1: Tại một ô bất kỳ trong bảng tính, nhập công thức sau:
=IFERROR(INDEX($D$5:$D$9,SMALL(IF($G$4=$B$5:$B$9,ROW($D$5:$D$9)),ROW(A7))),””)
Trong đó:
- IFERROR, INDEX, SMALL, ROW: Các hàm được kết hợp để xử lý dữ liệu.
- $D$5:$D$9: Vùng dữ liệu trả về (cột doanh thu).
- $G$4: Ô chứa điều kiện tìm kiếm (ví dụ: Hồ Chí Minh).
- $B$5:$B$9: Cột chứa thông tin điều kiện (chi nhánh).
- ROW(A7), “”: Xác định vị trí hiển thị và trả về giá trị rỗng nếu không tìm thấy kết quả.
Bước 2: Nhấn tổ hợp phím Ctrl + Shift + Enter để công thức hoạt động và hiển thị danh sách kết quả.
Như vậy, với sự kết hợp giữa IFERROR, INDEX và SMALL, bạn hoàn toàn có thể lọc và liệt kê dữ liệu theo nhiều điều kiện một cách chính xác và nhanh chóng. Đây là kỹ năng quan trọng giúp bạn tối ưu công việc báo cáo và phân tích dữ liệu trong Excel. Nếu muốn tìm hiểu thêm các thủ thuật hữu ích khác, bạn có thể tham khảo các bài viết liên quan trên ketoan.vn để nâng cao hiệu quả làm việc.
