Hàm Liệt Kê Danh Sách Trong Excel Là Gì? Liệt Kê Danh Sách Theo Điều Kiện

Làm việc với excel đôi lúc bạn sẽ cần liệt kê các giá trị có điều kiện trọng excel, vậy làm sao để làm được cùng tìm hiểu nhé. Hôm nay, lingocard.vn viết về hàm liệt kê có điều kiện trong excel.

Đang xem: Hàm liệt kê danh sách trong excel

Nếu như bạn cần lên danh sách tất cả 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. Vậy làm cách nào có khả năng làm được yêu cầu này? cùng với mình xem qua nội dung sau đây nhé.

Các hàm dùng để liệt kê trong excel

Hàm IFERROR

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

Trong đó:

– IFERROR: là tên hàm sử dụng để phát hiện giá trị lỗi và trả về thành quả mà bạn cần.

– Value: thành quả mà con người cần chọn lựa có phải lỗi hay không.

– Value_if_error: giá trị trả về nếu thành quả 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, ).

Trong đó:

– INDEX: là tên hàm sử 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 đăng này mánh 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ị.

Chẳng hạn như sử dụng hàm INDEX:

Hàm SMALL

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

Trong đó:

– SMALL: là tên hàm sử dụng để lựa chọn giá trị nhỏ thứ k.

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

Xem thêm: Hãy Bỏ Túi Ngay Kinh Nghiệm Thiết Kế Giếng Trời Cho Nhà Diện Tích Nhỏ

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

Ví dụ dùng hàm SMALL:

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

Với đòi hỏi mà thủ thuật ứng dụng đưa rõ ra ở trên, bạn phải 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 phương pháp 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 thành quả để trả về.$J$2 là vị trí của ô chứa thành quả cần tìm.$B$3:$B$12 là mảng dữ liệu cần tra cứu.

Vậy phụ thuộc 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.

Điều bạn cần lưu ý:

Bạn nên để thành quả 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 như vùng dữ liệu tham chiếu của bạn dài, bạn cần phải sao chép phương pháp cho tất cả cột hoặc tối thiểu là bằng số dòng của vùng tham chiếu cho không gây hại. 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ư mánh ứng dụng điều chỉnh điều kiện tham chiếu là : Chi nhánh Hồ Chí Minh.

Cách lọc ra các thành quả độc nhất trong excel

Để hạn chế nhầm lẫn, trước tiên, chúng ta cần hiểu những giá trị nào được gọi là là các giá trị duy nhất trong Excel. Thành quả duy nhất là các giá trị chỉ hiện hữu độc nhất một lần trong tất cả danh sách.

Để trích xuất một danh sách các thành quả duy nhất trong Excel, bạn có thể sử dụng một trong những công thức sau đây.

Phương pháp mảng để tìm ra các giá trị duy nhất

Hoàn thành công thức ta nhấn CTRL + SHIFT + ENTER

= IFERROR (INDEX ($ A $ 2: $ A $ 10, MATCH (0, COUNTIF ($ B $ 1: B1, $ A $ 2: $ A $ 10) + (COUNTIF ($ A $ 2: $ A $ 10, $ A $ 2: $ A $ 10) 1), 0)), “”)

Phương pháp thành quả duy nhất thường xuyên

Hoàn thành công thức xong ta nhấn ENTER

= IFERROR (INDEX ($ A $ 2: $ A $ 10, MATCH (0, INDEX (COUNTIF ($ B $ 1: B1, $ A $ 2: $ A $ 10) + (COUNTIF ($ A $ 2: $ A $ 10, $ A $ 2 : $ A $ 10) 1), 0,0), 0)), “”)

TRONG ĐÓ:

A2: A10 – danh sách nguồnB1 – ô đầu của danh sách độc nhất trừ đi 1. Trong ví dụ này, chúng ta tiếp tục danh sách các giá trị duy nhất ở B2, và thế nên chúng ta đưa B1 vào phương pháp (B2-1 = B1). nếu danh sách các thành quả độc nhất của bạn tiếp tục ở ô C3, ta sẽ thay đổi $B$1:B1 thành $C$2:C2.

Chú thích. Bởi vì công thức tham chiếu ô bên trên ô trước tiên của danh sách các thành quả duy nhất, thường là tiêu đề cột (như trong ví dụ này là B1), có thể bạn phải cần cam kết tiêu đề của bạn là một tên độc nhất không hiện diện ở bất cứ đâu trong cột.

Những hàm cơ bản trong Excel

Ở chẳng hạn như này đang trích xuất các tên độc nhất từ cột A (chính xác hơn là trong phạm vi A2: A20), và ảnh chụp màn hình sau sẽ minh họa cho công thức sẽ dùng:

Dưới đây là giải thích một cách chi tiết về việc sử dụng hàm

– Chọn lựa một trong các công thức dựa theo dữ liệu của bạn.– Nhập công thức vào ô trước tiên của danh sách các giá trị duy nhất (trong ví dụ sẽ là ô B2).– Nếu như bạn đang dùng công thức mảng, nhấn Ctrl + Shift + Enter. nếu như bạn chọn công thức bình thường, hãy nhấn phím Enter như thường lệ.– Sao chép phương pháp xuống càng xa càng tốt bằng việc kéo chốt xử lý. Vì cả hai công thức giá trị độc nhất được gói gọn trong hàm IFERROR, bạn có khả năng sao chép phương pháp đến cuối bảng mà không làm lộn xộn dữ liệu vì bất kỳ lỗi nào dù các giá trị duy nhất đã được trích xuất ra sao.

Xem thêm: giáo án trò chuyện về đồ dùng đồ chơi của bé

Bài viết trên đã gửi đến các bạn các hàm liệt kê có điều kiện mình mong nó sẽ giúp ích được cho các bạn. Cảm ơn các bạn đã xem qua bài viết của mình nhé.

Lộc Đạt-tổng hợp

Tham khato ( thuthuatphanmem, blog.hocexcel.online, … )