Cách liệt kê danh sách theo điều kiện trong Excel

Hàm Lookup có sẵn trong Excel giúp cho người dùng có thể tìm kiếm theo điều kiện nhưng chỉ trả về một giá trị đầu tiên đúng nhất. Nếu như bạn cần liệt kê toàn bộ các giá trị thỏa mãn điều kiện và sao chép ra một bảng dữ liệu khác hoặc một Sheet khác… thì chưa có một công cụ trực tiếp nào thỏa mãn yêu cầu này.

Bạn xét ví dụ dưới: Bạn bảng dữ liệu tổng hợp và cần trích xuất tất cả các dữ liệu theo điều kiện ra một bảng khác.

Vậy làm thế nào có thể thực hiện được yêu cầu này? Hôm nay, Thủ Thuật Phần Mềm sẽ hướng dẫn bạn cách kết hợp các hàm có sẵn để liệt kê toàn bộ các giá trị thỏa mãn điều kiện trong Excel là các hàm: IFERROR, INDEX, SMALL.

Hàm IFERROR

Cấu trúc hàm: = IFERROR(value, value_if_error).

Trong đó:

- IFERROR: là tên hàm dùng để phát hiện giá trị lỗi và trả về giá trị mà bạn cần.

- Value: giá trị mà chúng ta cần xác định có phải lỗi hay không.

- Value_if_error: giá trị trả về nếu giá trị value là lỗi.

Nếu giá trị của value là lỗi (#N/A, #REF,…) thì hàm IFERROR sẽ trả về là value_if_error, nếu không thì sẽ trả về là giá trị value.

Hàm INDEX

Cú pháp hàm: =INDEX(array, row_num, [column_num]).

Trong đó:

- INDEX: là tên hàm dùng để lấy lại giá trị ở dòng n (row_num) trong mảng dữ liệu array.

- Array: Mảng dữ liệu. Ở bài viết này Thủ Thuật Phần Mềm chỉ hướng dẫn hàm INDEX với array là mảng dữ liệu chỉ chứa một cột.

- row_num: Chọn hàng trong mảng mà từ đó trả về một giá trị.

Ví dụ sử dụng hàm INDEX:

Hàm SMALL

Cú pháp hàm: =SMALL(array,k).

Trong đó:

- SMALL: là tên hàm dùng để xác định giá trị nhỏ thứ k.

- Array: Mảng hoặc phạm vi dữ liệu dạng số mà bạn muốn xác định giá trị nhỏ thứ k của nó.

- K: Vị trí (từ giá trị nhỏ nhất) trong mảng hoặc phạm vi dữ liệu cần trả về.

Ví dụ sử dụng hàm SMALL:

Cách liệt kê danh sách theo điều kiện trong Excel

Với yêu cầu mà Thủ Thuật Phần Mềm đưa ra ở trên, bạn cần liệt kê 3 mục doanh thu của Chi nhánh Hải Dương ở vào cột J.

Ở cột J4 bạn nhập công thức: =IFERROR(INDEX($G$1:$G$12,SMALL(IF($J$2=$B$3:$B$12,ROW($G$3:$G$12)),ROW(A1))),"") và ấn tổ hợp phím Ctrl + Shif + Enter. Và sao chép công thức cho các dòng khác của cột.

Ở đây:

• $G$1:$G$12: là mảng dữ liệu tham chiếu cần lấy giá trị để trả về.
• $J$2 là vị trí của ô chứa giá trị cần tìm.
• $B$3:$B$12 là mảng dữ liệu cần tra cứu.

Vậy dựa vào công thức trên, bạn thay đổi các mục vị trí của mảng dữ liệu và ô dữ liệu tương ứng với vị trí bảng tính của mình.

Lưu ý:

• Bạn nên để giá trị tuyệt đối của các mảng dữ liệu để tránh sai sót khi sao chép công thức.
• Nếu vùng dữ liệu tham chiếu của bạn dài, bạn nên sao chép công thức cho toàn bộ cột hoặc ít nhất là bằng số dòng của vùng tham chiếu cho an toàn. Ad có sao chép công thức cho đến ô J9, tuy nhiên do chỉ có 3 giá trị tìm kiếm được thỏa mãn điều kiện Chi nhánh Hải Dương nên chỉ các ô J3-J4 có dữ liệu số, các ô khác sẽ là dữ liệu trắng và sẽ không ảnh hưởng đến thẩm mỹ của báo cáo.

Hình ảnh dưới là một kết quả khác nếu như Thủ Thuật Phần Mềm thay đổi điều kiện tham chiếu là : Chi nhánh Hồ Chí Minh.

Chúc các bạn thành công!