Hướng dẫn cách liệt kê danh sách theo điều kiện trong Excel hiệu quả

Hàm Lookup trong Excel giúp bạn tìm kiếm các giá trị theo điều kiện đã định, tuy nhiên chỉ trả về giá trị đầu tiên đúng nhất. Nếu bạn muốn liệt kê tất cả các giá trị thỏa mãn điều kiện và sao chép chúng sang một bảng dữ liệu khác hoặc một Sheet riêng biệt, hiện tại Excel không cung cấp công cụ trực tiếp để thực hiện điều này.

Giả sử bạn có một bảng dữ liệu tổng hợp và cần trích xuất tất cả những dòng dữ liệu đáp ứng điều kiện ra một bảng mới.

Vậy làm sao để có thể hoàn thành yêu cầu này? Tripi sẽ chỉ cho bạn cách sử dụng các hàm kết hợp trong Excel như: IFERROR, INDEX, SMALL để liệt kê tất cả các giá trị thỏa mãn điều kiện một cách chính xác.

Hàm IFERROR trong Excel

Công thức hàm có dạng: = IFERROR(value, value_if_error)

Giải thích về các thành phần của hàm:

- IFERROR: là hàm giúp phát hiện lỗi trong quá trình tính toán và thay thế nó bằng giá trị mà bạn muốn hiển thị thay vì thông báo lỗi.

- Value: giá trị cần kiểm tra xem có phải là lỗi hay không.

- Value_if_error: giá trị sẽ được trả về nếu Value là một lỗi.

Khi giá trị Value gặp phải lỗi như #N/A, #REF,... hàm IFERROR sẽ thay thế bằng Value_if_error; nếu không có lỗi, kết quả trả về sẽ là chính giá trị Value.

Hàm INDEX trong Excel

Công thức hàm: =INDEX(array, row_num, [column_num])

Các thành phần trong hàm INDEX gồm:

- INDEX: là hàm giúp bạn trích xuất giá trị tại một vị trí nhất định trong mảng dữ liệu, dựa trên số dòng row_num trong mảng array.

- Array: Mảng dữ liệu cần tham chiếu. Trong bài viết này, chúng ta chỉ đề cập đến trường hợp hàm INDEX hoạt động với mảng dữ liệu có một cột duy nhất.

- row_num: Xác định dòng trong mảng mà từ đó hàm sẽ trả về giá trị.

Ví dụ minh họa khi sử dụng hàm INDEX:

Giới thiệu về hàm SMALL

Công thức hàm: =SMALL(array,k)

Giải thích các thành phần của hàm:

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

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

- K: Vị trí thứ k (tính từ giá trị nhỏ nhất) mà bạn muốn hàm trả về trong mảng hoặc phạm vi dữ liệu.

Ví dụ về việc sử dụng hàm SMALL:

Cách liệt kê danh sách theo điều kiện trong Excel một cách hiệu quả

Dựa trên yêu cầu từ Tripi, bạn cần liệt kê 3 mục doanh thu của Chi nhánh Hải Dương trong cột J.

Tại ô 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))),""), nhấn tổ hợp phím Ctrl + Shift + Enter, rồi sao chép công thức này cho các ô tiếp theo trong cột.

Giải thích các thành phần trong công thức:

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

Dựa trên công thức đã đưa ra, bạn chỉ cần thay đổi vị trí của các mảng dữ liệu và ô tham chiếu sao cho phù hợp với bảng tính của bạn.

Lưu ý: Hãy chắc chắn rằng các tham chiếu trong công thức được điều chỉnh chính xác với dữ liệu của bạn.

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

Hình ảnh dưới đây thể hiện kết quả nếu Tripi thay đổi điều kiện tham chiếu sang Chi nhánh Hồ Chí Minh.

Chúc các bạn thành công và áp dụng thành thạo công thức trong công việc!