Cách Dùng Các Hàm Liệt Kê Có Điều Kiện Trong Excel, Cách Dùng Các

Hiện nay, trong Excel hàm Lookup cung cấp người áp dụng tìm kiếm quý giá theo điều kiện đưa ra. Tuy nhiên, hàm này có nhược điểm là chỉ đưa ra được hiệu quả của một trị giá xuất hiện thêm sớm nhất. Vị vậy, chúng ta không thể thống kê lại được tất cả các cực hiếm cùng đáp ứng nhu cầu điều kiện. Kế bên ra, hàm Lookup không hỗ trợ trích xuất để tập thích hợp thành một bảng tài liệu khác hoặc đưa sang Sheet khác. Như vậy, cần được sử dụng những hàm gián tiếp khác nhằm phục được hai điểm yếu này. Hàm liệt kê list trong Excel được sử dụng dựa vào sự kết hợp của các hàm IFERROR, INDEX, SMALL.

Bạn đang xem: Hàm liệt kê có điều kiện trong excel

Nội dung

Hàm IFERRORHàm INDEXHàm INDEX dạng mảngHàm INDEX dạng tham chiếuHàm SMALL

Hàm IFERROR

Mục đích sử dụng hàm IFERROR để đưa những giá chỉ trị mong muốn khi điều kiện của hàm này cho hiệu quả lỗi.

Hàm IFERROR

Cấu trúc hàm

= IFERROR(value, value_if_error).

Giải thích các thuật ngữ

IFERROR: đó là tên điện thoại tư vấn của hàmValue: lựa chọn vùng giá trị đang cần khẳng định có lộ diện lỗi tốt không.Value_if_error: giá trị sẽ mang về khi quý hiếm value báo lỗi.
Download Giáo trình tự học tập Hàm Excel mới nhất 2021 pdf+word miễn phíTải Ngay

Chú ý

Nếu quý hiếm của value chỉ ra rằng lỗi (#N/A, #REF,…) thì hàm IFERROR đã trả về là value_if_error, còn nếu không thì đang trả về là giá trị value.

Loại quý giá Value_if_error bạn cũng có thể chọn là: để trống (“”); điền số 0; ví dụ hơn là dòng chữ để các bạn ghi nhớ dễ dãi khi lỗi.

Hàm INDEX

Có hai nhiều loại hàm INDEX sẽ là INDEX dạng mảng cùng INDEX dạng tham chiếu:

Hàm INDEX dạng mảng

Hàm INDEX sẽ gửi ra dữ liệu của một ô vào vùng với gốc rễ là chỉ số row và chỉ số array của vùng đó.

Cấu trúc hàm INDEXCấu trúc hàm

=INDEX(array, row_num, ).

Trong đó:

INDEX: là tên gọi của hàmArray: là nguồn dữ liệu bao gồm các cột giá bán trị.row_num: chọn vào hàng nhằm làm khởi đầu trả về một quý hiếm trong mối cung cấp dữ liệu.Chú ý

Trong hàm INDEX điều kiện cần thiết là phải sử dụng ít nhất 1 trong những hai tài liệu Row_num và Column_num. Nếu không đáp ứng đúng thì kết quả sẽ báo lỗi.

Hàm INDEX dạng tham chiếu

Hàm Index dạng tham chiếu đưa ra tham chiếu của ô nằm ở giao của một row cùng một column thắt chặt và cố định được chuyển ra.

Xem thêm: Tải Car Simulator Vietnam Miễn Phí, Car Simulator Vietnam For Android

Hàm INDEX thường xuyên được kết hợp với hàm khácCấu trúc hàm

=INDEX(Reference,Row_num,,)

Trong đó:

Reference: là vùng tham chiếu núm định.Row_num: chỉ số mặt hàng từ đó chỉ dẫn một tham chiếu có tính chất cố định.Column_num: chỉ số cột tự đó chỉ dẫn một tham chiếu có đặc thù tùy chọn.Area_num: số của vùng ô sẽ đưa ra giá trị trên vùng Reference. Chú ý

Nếu Area_num không bắt buộc dùng mang lại thì hàm INDEX sử dụng vùng tùy chọn.

Hàm SMALL

Hàm SMALL trả về giá chỉ trị bé dại thứ k của nguồn tài liệu đưa ra. Dùng hàm này để trả về giá trị với thứ hạng tương đối cụ thể trong tập dữ liệu.

Cấu trúc hàm

=SMALL(array,k).

Trong đó:

SMALL: là tên thường gọi của hàm.Array: vùng dữ liệu có định hình số mà bạn phải tìm trả giá trị nhỏ thứ k của nó.K: vị trí (từ giá chỉ trị bé dại nhất) vào vùng tài liệu cần trả về.

Chú ý

Khi nhằm trống quý hiếm Array thì hàm gửi ra tác dụng là cực hiếm lỗi (#NUM!)Nếu K ≤ 0 hoặc K nhiều hơn thế số điểm dữ liệu trong Array thì hàm cũng đưa ra giá trị lỗi #NUM!

Cách thực hiện hàm liệt kê list trong Excel

Các hàm trực tiếp hiện thời không đáp ứng nhu cầu được yêu mong liệt kê list và sao chép. Bởi vậy, chúng ta sẽ đề xuất sự kết hợp đúng mực các hàm IFERROR, INDEX, SMALL. Mục đích là để tạo ra được Hàm liệt kê list trong Excel.

Ví dụ: mang lại bảng tài liệu như bên dưới. Vấn đề yêu ước liệt kê danh sách chi tiết doanh thu theo của mặt hàng với điều kiện là chi nhánh Hải Dương

Công thức hàm liệt kê danh sách trong excel

Bạn nên liệt kê list 3 mục lợi nhuận của chi nhánh Hải Dương sinh sống vào cột J.

Cấu trúc hàm:

=IFERROR(INDEX($G$1:$G$12,SMALL(IF($J$2=$B$3:$B$12,ROW($G$3:$G$12)),ROW(A1))),””)

Trong đó:

$G$1:$G$12: là cột dữ liệu tham chiếu nên lấy quý hiếm để trả về.$J$2 là ô chứa đk cần tìm.$B$3:$B$12 là cột dữ liệu chứa điều kiện tìm kiếm.

Để sao chép công thức sống cột J3 cho các dòng khác bạn phải sử dụng tổng hợp phím Ctrl + Shift + Enter. Như vậy, là chúng ta đã kết thúc yêu ước của vấn đề rồi. Hiện tại nay, chưa xuất hiện Hàm liệt kê list trong Excel trực tiếp nào nên dù hơi phức hợp nhưng các bạn vẫn có thể làm được đúng không nào nào.

Chú ý

Để không xẩy ra lỗi trong vượt trình xào nấu công thức, thì các nguồn dữ liệu được sử dụng đến cần được đặt giá trị tuyệt vời và hoàn hảo nhất “$..$”.Trong khi viết hàm INDEX nếu như vùng dữ liệu tham chiếu nhiều, thì cách tốt nhất có thể là chúng ta nên sao chép công thức cho toàn thể cột. Hoặc sao chép sao cho tối thiểu là ngay số dòng của vùng tham chiếu nhằm đạt được công dụng tốt nhất.

Bài viết này giúp chúng ta nắm bắt được kết cấu của các hàm IFERROR, INDEX, SMALL. Tự đó hoàn toàn có thể sử dụng trực tiếp những hàm này để thỏa mãn nhu cầu các yêu cầu ứng với từng hàm. Ngoài ra, sự kết hợp của bọn chúng sẽ mang đến cho bọn họ hàm liệt kê danh sách trong Excel. Ưu điểm của hàm này là đem về kết quả đúng chuẩn và đáp ứng nhu cầu những điều kiện phức tạp. Chúng sẽ giúp đỡ cho việc làm báo cáo của chúng ta được dễ dãi và dễ dãi hơn.