Chức năng Tra cứu có sẵn trong Excel giúp người dùng có thể tìm kiếm theo điều kiện nhưng chỉ trả về giá trị chính xác đầu tiên. Nếu bạn cần liệt kê tất cả các giá trị thỏa mãn điều kiện và sao chép nó sang một bảng dữ liệu khác hoặc một Trang tính khác… thì không có công cụ trực tiếp nào có thể đáp ứng yêu cầu này.
Bạn xem xét ví dụ dưới đây: Bạn có một bảng tổng hợp dữ liệu và cần trích xuất tất cả dữ liệu theo các điều kiện vào 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 các bạn cách kết hợp các hàm có sẵn để liệt kê tất cả các giá trị thỏa mãn điều kiện trong Excel, đó là các hàm: IFERROR, INDEX, SMALL.
IFERROR. hàm số
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ị bạn cần.
– Giá trị: giá trị mà ta cần xác định có lỗi hay không.
– Value_if_error: trả về giá trị nếu giá trị là lỗi.
Nếu giá trị của value là lỗi (# N / A, #REF, …) thì hàm IFERROR sẽ trả về giá trị_if_error, ngược lại nó sẽ trả về giá trị.
MỤC LỤC . hàm số
Cú pháp hàm: = INDEX (array, row_num, [column_num]).
Trong đó:
– INDEX: là tên hàm dùng để lấy giá trị ở dòng n (row_num) trong mảng dữ liệu mảng.
– Array: Mảng dữ liệu. Trong bài viết này, Thủ thuật Phần mềm chỉ hướng dẫn hàm INDEX với 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:
NHỎ BÉ . hàm số
Cú pháp hàm: = SMALL (array, k).
Trong đó:
– NHỎ: là tên hàm dùng để xác định giá trị nhỏ nhất thứ k.
– Array: Mảng hoặc dải dữ liệu số mà bạn muốn xác định giá trị nhỏ nhất thứ k của nó.
– K: Vị trí (từ giá trị nhỏ nhất) trong mảng hoặc dải dữ liệu 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 các yêu cầu Thủ thuật phần mềm đưa ra ở trên, bạn cần liệt kê 3 khoản mục doanh thu của Chi nhánh Hải Dương vào cột J.
Trong cột J4, bạn nhập công thức: = IFERROR (INDEX ($ G $ 1: $ G $ 12, NHỎ (IF ($ J $ 2 = $ B $ 3: $ B $ 12, ROW ($ G $ 3: $ G $ 12)), ROW (A1))), “”) và nhấn Ctrl + Shif + Enter. Và sao chép công thức vào các hàng khác của cột.
Nơi đây:
$ G $ 1: $ G $ 12: là một mảng dữ liệu tham chiếu cần được trả về một giá trị.
$ J $ 2 là vị trí của ô chứa giá trị mong muốn.
$ B $ 3: $ B $ 12 là mảng dữ liệu cần tra cứu.
Vì vậy, dựa trên 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í trang tính của bạn.
Ghi chú:
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 phạm vi 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 nhiều hàng như phạm vi tham chiếu, để an toàn. Ad đã copy công thức cho đến ô J9, nhưng do chỉ có 3 giá trị tìm kiếm thỏa mãn điều kiện Chi nhánh Hải Dương nên chỉ có ô J3-J4 mới có dữ liệu số, các ô khác sẽ là dữ liệu trống và không ảnh hưởng đến thẩm mỹ của báo cáo .
Hình ảnh dưới đây là một kết quả khác nếu Thủ thuật Phần mềm thay đổi điều kiện tham chiếu thành: Chi nhánh Hồ Chí Minh.
Chúc may mắn!
Bài viết này giúp bạn nắm bắt được thông tin về các thủ thuật khi sử dụng các ứng dụng văn phòng. Để từ đó có thể sử dụng trực tiếp những thủ thuật này để đáp ứng các yêu cầu của bạn. Việc hiểu biết các thông tin trên sẽ giúp cho công việc của bạn được thực hiện dễ dàng và thuận tiện hơn. Ngoài ra, nếu bạn còn điều gì thắc mắc, hãy truy cập website: https://www.amorstay.com.vn/ để biết thêm các thông tin chi tiết hơn nhé.