3 hàm liệt kê trong Excel kèm ví dụ chi tiết

Hôm nay, Blogphanmem chia sẻ đến bạn 3 hàm liệt kê trong Excel kèm ví dụ chi tiết. 3 hàm liệt kê trong Excel bài viết đề cập là hàm SMALL, hàm IFERROR và hàm INDEX. Cùng theo dõi bài viết để biết cách sử dụng 3 hàm này thôi bạn ơi.

Bạn đang xem bài viết: Hàm liệt kê trong Excel 

Hàm liệt kê trong Excel – Hàm SMALL

Định nghĩa

Hàm SMALL là hàm liệt kê trong Excel đầu tiên Blogphanmem chia sẻ đến bạn. Hàm SMALL sẽ trả về các giá trị số dựa theo vị trí đã được xếp hạng từ nhỏ đến lớn trong danh sách. Hay nói đơn giản là, hàm SMALL có thể trả về giá trị nhỏ nhất đầu tiên, nhỏ nhất thứ n,…. trong mảng.

Xem thêm: Tổng hợp 5 hàm Excel nâng cao bạn nên biết

Ngược lại, nếu bạn muốn tìm giá trị lớn nhất thứ n thì có thể sử dụng hàm LARGE.

Công thức

Cú pháp của Hàm SMALL như sau : =SMALL(Array; n)

Trong đó:

• Array: là một mảng gồm nhiều ô chứa giá trị số để trích xuất các giá trị nhỏ nhất.
• n: là một số nguyên. n là thứ tự của giá trị nhỏ nhất cần truy xuất.

Lưu ý:

• Khi không có giá trị Array hoặc nhập nhầm thì hàm sẽ không trả về kết quả mà báo lỗi #NUM!
• Nếu n nhỏ hơn hoặc bằng 0 hay n lớn hơn số dữ liệu trong array thì hàm cũng báo lỗi #NUM!

Ví dụ

Cho bảng tính Excel như sau. Yêu cầu tìm giá trị nhỏ nhất thứ 4 của bảng giá trị 1.

Vì yêu cầu tìm giá trị nhỏ nhất thứ 4 của bảng nên ta biết ngay phải sử dụng hàm SMALL- hàm liệt kê trong Excel. ÁP dụng công thức hàm SMALL: =SMALL(C5:C9,4).

Trong đó: array là từ C5 đến C9, GTNN thứ 4 nên n=4.

Sau đó nhấn phím Enter để hiển thị kết quả đúng là 44.

Hàm liệt kê danh sách trong Excel – Hàm IFERROR

Hàm IFERROR – hàm liệt kê trong Excel giúp bẫy và xử lý lỗi cho các giá trị không xác định bằng cách trả về giá trị bạn chỉ định cho các điều kiện lỗi. Bạn không cần sử dụng các câu lệnh IF lồng nhau phức tạp khi đã có hàm IFERROR.

Công thức

Cú pháp hàm IFERROR là: =IFERROR(value; value_if_error)

Trong đó:

• Value: Value là giá trị số, công thức hoặc tham chiếu bạn muốn kiểm tra lỗi.
• Value_if_error: là giá trị trả về nếu tìm thấy lỗi. Giá trị trả về này do bạn chỉ định hay tùy thuộc vào yêu cầu của đề bài.

Lưu ý:

• Nếu vế value bị sai (#N/A, #REF,…) thì hàm IFERROR sẽ trả về giá trị.
• Ở vế Value_if_error bạn có thể chọn là: để trống (“”), điền số 0, hoặc đoạn văn bản trong dấu ngoặc kép.

Hàm IFERROR có thể kiểm tra các lỗi sau: # N / A, #VALUE !, # DIV / 0 !, #REF !, #NAME ?, #NUM ! hoặc #NULL !. Với lỗi ###### thì hàm IFERROR sẽ không bắt lỗi. Vì lỗi này không phải là lỗi dữ liệu mà là lỗi hiển thị trong Excel bạn cũng nên lưu ý kẻo nhầm.

Ví dụ

Cho bảng tính Excel sau. Yêu cầu tính điểm trung bình cộng 3 môn toán, lý, hóa của từng học sinh, mà không dùng hàm AVERAGE.

Đầu tiên nhìn lướt qua bảng ta thấy có những dữ liệu không phải là số. Nếu sử dụng công thức tính trung bình cộng AVERAGE để tính thì sẽ báo lỗi #VALUE!

Vì để như vậy không được đẹp và chuyên nghiệp. Ta có thể dùng hàm IFERROR để thay lỗi #VALUE bằng chữ “sai phép tính”.

Nhập công thức hàm IFERROR =IFERROR((C2+D2+E2)/F2,”Sai phép tính”)  Vế value là công thức tính trung bình cộng, nếu đúng thì sẽ trả ra số TB, còn nếu vế value sai thì trả về chữ “sai phép tính”

Sau đó bạn nhấn Enter.

Lúc này bảng tính sẽ được như sau:

Hàm liệt kê trong Excel – Hàm INDEX dạng mảng

Định nghĩa

Hàm INDEX dạng mảng là một hàm liệt kê danh sách trong Excel. Hàm INDEX dùng để liệt kê, trả ra giá trị của một ô trong vùng biết vị trí hàng và cột của nó.

Công thức

Cú pháp và thủ thuật excel hàm INDEX dạng mảng: =INDEX(array, row_num, [column_num])

Trong đó:

• Array: Phạm vi ô hoặc mảng. Yếu tố bắt buộc.
• Row_num: hàng được chọn để bắt đầu và trả về một giá trị trong nguồn dữ liệu.
• Column_num: cột được chọn trong mảng mà từ đó trả về một giá trị.

Ví dụ

Cho bảng tính Excel là danh sách một nhóm học sinh. Trả ra giá trị ở hàng 2 cột 2

Áp dụng công thức hàm INDEX ta có  :=INDEX(B4:C7,2,2)