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
Nội dung bài viết
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)
Lưu ý:
- Trong hàm INDEX dạng mảng bắt buộc phải có ít nhất một trong hai dữ liệu Row_num và Column_num. Nếu không hàm INDEX sẽ báo lỗi.
Cách liệt kê trong Excel – Hàm INDEX dạng tham chiếu
Định nghĩa
Hàm INDEX dạng tham chiếu dùng để đưa ra giá trị của ô nằm ở một row và một column cố định được cho sẵn.
Công thức
Cú pháp hàm INDEX dạng tham chiếu: =INDEX(Reference,Row_num,[Column_num],[Area_num])
Trong đó:
- Reference: Là vùng tham chiếu cố định cần truy xuất.
- Row_num: Là chỉ số hàng (Row). Từ chỉ số này đưa ra một tham chiếu chỉ ra vị trí cần tìm
- Column_num: Là chỉ số cột (Column). Từ chỉ số này đưa ra một tham chiếu chỉ ra vị trí cần tìm.
- Area_num: Là số của khu vực ô sẽ đưa ra giá trị tại vùng Reference.
Lưu ý:
- Nếu Area_num KHÔNG được khai báo thì hàm INDEX sẽ tự động dùng vùng tùy chọn.
Cách dùng hàm liệt kê trong Excel
Phía trên Blogphanmem đã giới thiệu từng hàm bao gồm định nghĩa, công thức, ví dụ để bạn hiểu rõ hơn về mỗi hàm. Ở phần này, Blogphanmem sẽ hướng dẫn bạn kết hợp tất cả các hàm trên lại với nhau để tạo ra hàm liệt kê trong Excel.
Xem thêm: Công thức hàm MIN, hàm MAX có điều kiện trong Excel
Ví dụ: Cho bảng tính Excel sau. Hãy liệt kê danh sách Chi nhánh Hồ Chí Minh theo thứ tự như trong bảng. Có sử dụng kết hợp các hàm liệt kê trong Excel.
Bạn nhập công thức =IFERROR(INDEX($D$5:$D$9,SMALL(IF($G$4=$B$5:$B$9,ROW($D$5:$D$9)),ROW(A7))),””)
Giải thích:
- IFERROR, INDEX, SMALL, ROW: Là những hàm liệt kê.
- $D$5:$D$9: Là cột códữ liệu tham chiếu cần lấy giá trị để trả về.
- $G$4: Là ô có chứa điều kiện cần tìm.
- $B$5:$B$9: Là cột dữ liệu chứa điều kiện cần tìm kiếm.
- ROW(A7),””: Là điều kiện hiển thị khi không tìm được dữ liệu cần tìm.
Sau đó bạn nhấn tổ hợp phím Ctrl + Shift + Enter. Và được kết quả như hình.
Tổng kết
Bài viết 3 hàm liệt kê trong Excel kèm ví dụ chi tiết mong rằng đã đem đến cho các bạn các thông tin hữu ích. Chúc các bạn thành công. Cảm ơn các bạn đã theo dõi bài viết của blogphanmem.vn