Hàm Liệt Kê Có Điều Kiện Trong Excel

Hiện nay, vào Excel hàm Lookup cung ứng người thực hiện tìm kiếm cực hiếm theo đk đưa ra. Mặc dù nhiên, hàm này có nhược điểm là chỉ chỉ dẫn được tác dụng của một trị giá mở ra sớm nhất. Bởi vì vậy, chúng ta không thể những thống kê được tất cả các giá trị cùng thỏa mãn nhu cầu điều kiện. Kế bên ra, hàm Lookup không cung cấp trích xuất nhằm tập vừa lòng thành một bảng tài liệu khác hoặc chuyển sang Sheet khác. Như vậy, rất cần được sử dụng những hàm loại gián tiếp khác nhằm phục được hai yếu điểm này. Hàm liệt kê list trong Excel được sử dụng dựa trên sự phối hợp của những 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 áp dụng hàm IFERROR để lấy những giá trị mong muốn khi điều kiện của hàm này cho công dụng 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: đấy là tên call của hàmValue: lựa chọn vùng quý hiếm đang cần xác minh có xuất hiện thêm lỗi hay không.Value_if_error: quý hiếm sẽ mang về khi cực 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 cho rằng lỗi (#N/A, #REF,…) thì hàm IFERROR vẫn trả về là value_if_error, còn nếu như không thì sẽ trả về là quý giá value.

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

Hàm INDEX

Có hai các loại hàm INDEX chính là INDEX dạng mảng với INDEX dạng tham chiếu:

Hàm INDEX dạng mảng

Hàm INDEX sẽ gửi ra tài liệu của một ô trong vùng với căn nguyên là chỉ số row còn 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 thường gọi của hàmArray: là nguồn dữ liệu bao gồm các cột giá chỉ trị.row_num: lựa chọn vào hàng để làm bắt đầu trả về một quý giá 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 thực hiện ít nhất một trong những hai dữ liệu Row_num cùng Column_num. Giả dụ không đáp ứng nhu cầu đúng thì tác dụng sẽ báo lỗi.

Hàm INDEX dạng tham chiếu

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

Xem thêm: Hướng Dẫn Tải Và Cài Đặt Phần Mềm Pdf Miễn Phí, Foxit Pdf Reader

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 ráng định.Row_num: chỉ số hàng từ đó giới thiệu một tham chiếu có đặc thù cố định.Column_num: chỉ số cột tự đó giới thiệu một tham chiếu có đặc điểm tùy chọn.Area_num: số của vùng ô sẽ giới thiệu giá trị tại vùng Reference. Chú ý

Nếu Area_num không phải dùng cho thì hàm INDEX dùng vùng tùy chọn.

Hàm SMALL

Hàm SMALL trả về giá trị nhỏ 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 gọi của hàm.Array: vùng dữ liệu có format số mà bạn phải tìm ra giá trị nhỏ dại thứ k của nó.K: địa điểm (từ giá trị nhỏ nhất) vào vùng tài liệu cần trả về.

Chú ý

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

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

Các hàm trực tiếp bây chừ không đáp ứng nhu cầu được yêu mong liệt kê list và sao chép. Vị vậy, họ sẽ nên sự kết hợp đúng đắn các hàm IFERROR, INDEX, SMALL. Mục tiêu là để tạo thành được Hàm liệt kê list trong Excel.

Ví dụ: đến bảng dữ liệu như mặt dưới. Việc yêu cầu liệt kê danh sách cụ thể doanh thu theo của sản phẩm với điều kiện là trụ sở Hải Dương

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

Bạn đề nghị liệt kê list 3 mục doanh thu của chi nhánh Hải Dương ngơi nghỉ 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 phải lấy cực 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 đk tìm kiếm.

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

Chú ý

Để không xẩy ra lỗi trong quá 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ị hoàn hảo và tuyệt vời nhất “$..$”.Trong lúc viết hàm INDEX nếu như vùng dữ liệu tham chiếu nhiều, thì cách rất tốt là chúng ta nên xào nấu công thức cho tổng thể cột. Hoặc coppy sao cho ít nhất là bằng số dòng của vùng tham chiếu để đạt được công dụng tốt nhất.

Bài viết này giúp bọn họ nắm bắt được cấu tạo của những hàm IFERROR, INDEX, SMALL. Từ bỏ đó rất có thể sử dụng trực tiếp những hàm này để đáp ứng nhu cầu các yêu cầu ứng với từng hàm. Ngoại trừ ra, sự phối kết hợp của chúng sẽ đưa đế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à mang về kết quả đúng đắn và đáp ứng những điều kiện phức tạp. Chúng để giúp đỡ cho việc làm báo cáo của các bạn được dễ dãi và dễ dàng hơn.