Buzz
Hàm Lookup sẵn có trong Excel giúp người dùng 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 bạn muốn liệt kê toàn bộ các giá trị thỏa mãn điều kiện và sao chép vào 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.
Hãy xem ví dụ sau: Bạn có bảng dữ liệu tổng hợp và muốn trích xuất tất 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, Mytour 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, sử dụng các hàm: IFERROR, INDEX, SMALL.
Bí quyết sử dụng hàm IFERROR
Công thức hàm: = IFERROR(value, value_if_error).
Trong đó:
- IFERROR: là tên hàm giúp phát hiện giá trị lỗi và trả về giá trị mà bạn cần.
Giá trị cần kiểm tra với hàm IFERROR là Value.
Kết quả trả về khi giá trị là lỗi sẽ là Value_if_error.
Nếu value là lỗi (#N/A, #REF,…) thì hàm IFERROR sẽ trả về value_if_error, ngược lại sẽ là giá trị value
Bí quyết sử dụng hàm INDEX
Công thức hàm có dạng: =INDEX(array, row_num, [column_num]).
Trong đó:
- INDEX: Là cái tên của hàm giúp bạn định vị giá trị tại dòng thứ n (row_num) trong mảng dữ liệu array.
- Array: Đây là mảng dữ liệu, nhưng ở bài viết này chúng ta chỉ sử dụng hàm INDEX với mảng dữ liệu chỉ chứa một cột.
Dòng số n: Là lựa chọn của bạn trong mảng, từ đó giá trị sẽ được trả về.
Một ví dụ về cách sử dụng hàm INDEX:
Hàm SMALL
Công thức hàm: =SMALL(array,k).
Trong ngữ cảnh này:
- SMALL: là tên hàm giúp xác định giá trị nhỏ thứ k.
- Array: Là mảng hoặc phạm vi dữ liệu số bạn muốn xác định giá trị nhỏ thứ k của nó.
- K: Đây là vị trí (tính từ giá trị nhỏ nhất) trong mảng hoặc phạm vi dữ liệu mà bạn muốn trả về.
Ví dụ áp dụng hàm SMALL:
Làm thế nào để liệt kê danh sách theo điều kiện trong Excel
Để đáp ứng yêu cầu mà Mytour đưa ra, bạn muốn liệt kê 3 mục doanh thu của Chi nhánh Hải Dương vào cột J.
Tại ô J4, 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. Sao chép công thức cho các dòng khác trong cột.
Tại đây:
- $G$1:$G$12: là phạm vi 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à phạm vi dữ liệu cần tra cứu.
Dựa vào công thức trên, bạn chỉ cần thay đổi vị trí của mảng dữ liệu và ô dữ liệu tương ứng với bảng tính của mình.
Lưu ý:
- Đề xuất sử dụng 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 quá dài, hãy sao chép công thức cho toàn bộ cột hoặc ít nhất bằng số dòng của vùng tham chiếu để đảm bảo an toàn. Dù đã sao chép công thức đến ô J9, nhưng vì chỉ có 3 giá trị thỏa mãn điều kiện cho Chi nhánh Hải Dương, nên chỉ có các ô J3-J4 chứa dữ liệu số, các ô khác là dữ liệu trắng và không ảnh hưởng đến thẩm mỹ của báo cáo.
Dưới đây là một kết quả khác khi Mytour thay đổi điều kiện tham chiếu thành Chi nhánh Hồ Chí Minh.
Chúc các bạn đạt được thành công!
