Các hàm liệt kê trong Excel và cách sử dụng qua một số ví dụ minh họa dễ hiểu

Hàm liệt kê trong Excel bao gồm các hàm nào? Cách sử dụng các loại hàm này như thế nào? Trong bài viết này, FPT Shop sẽ đề cập đến các hàm liệt kê phổ biến, cũng như cách áp dụng chúng vào các tình huống thực tế qua một vài ví dụ chi tiết.

Cách sử dụng các hàm liệt kê danh sách trong Excel

Hàm IFERROR

Hàm IFERROR là một hàm liệt kê trong Excel được dùng để xử lý và kiểm soát các lỗi trong công thức. Khi bạn sử dụng một công thức có thể gây ra lỗi, chẳng hạn như chia cho 0, tham chiếu ô trống hoặc các lỗi tương tự, hàm IFERROR sẽ giúp bạn phát hiện ra lỗi sai thay thế kết quả lỗi bằng một giá trị khác do bạn xác định.

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

Trong đó: 

• Value: Bắt buộc. Là đối số đang cần kiểm tra xem có lỗi không.
• Value_if_error: Bắt buộc. Là giá trị cần trả về nếu công thức cho kết quả lỗi. 

Lưu ý: 

• Hàm IFERROR sẽ xét đối số Value nếu không phải là lỗi thì sẽ trực tiếp trả kết quả là chính nó, nếu ngược lại đối số Value là lỗi thì hàm này sẽ trả về kết quả Value_if_error.
• Đối số Value_if_error, bạn có thể để trống hoặc đặt ký tự phù hợp để dễ dàng ghi nhớ khi bị lỗi.

Ví dụ: Trong dữ liệu sau đây, bài toán yêu cầu bạn thực hiện phép tính chia giữa cột Chỉ số 1 và cột Chỉ số 2 và thực hiện kiểm soát lỗi nếu số chia là 0.

Bước 1: Tại ô C3, bạn nhập công thức hàm: =IFERROR(A3/B3,"Lỗi") và nhấn ENTER.

Bước 2: Bạn thực hiện sao chép công thức để trả kết quả vào các ô còn lại. Lúc này, bạn có thể thấy tại ô C4 kết quả trả về là Lỗi.

Hàm INDEX dạng mảng

Hàm INDEX được coi là một hàm liệt kê trong Excel với dạng mảng với tính năng liệt kê giá trị của một ô trong một bảng hoặc phạm vi dựa trên các chỉ số hàng và cột.

Cú pháp hàm: =INDEX(array, row_num, [column_num])

Trong đó:

• Array: Bắt buộc. Là phạm vi dữ liệu của ô hoặc là một hằng số của mảng.
• Row_num: Bắt buộc. Là chọn vào hàng để làm khởi đầu trả về một giá trị trong nguồn dữ liệu.
• [column_num]: Tùy chọn. Chọn cột trong mảng mà từ đó trả về một giá trị. Nếu Column_num được bỏ qua thì hàm tính bắt buộc phải điền đối số Row_num và ngược lại.

Ví dụ: Bạn muốn hiển thị kết quả của đơn hàng Cam thuộc chi nhánh nào trong dữ liệu bản tính như sau:

Công thức: =INDEX($A$3:$B$6,3,2)

Lúc này, bạn có thể thấy trên màn hình máy tính kết quả trả về thể hiện đơn hàng Cam thuộc chi nhánh Thủ Đức. 

Hàm INDEX dạng tham chiếu

Hàm INDEX dạng tham chiếu sẽ trả về kết quả tham chiếu của một ô thuộc điểm giao nhau giữa 1 hàng và 1 cột cụ thể nào đó. 

Cấu trúc hàm: =INDEX(Reference,Row_num,[Column_num],[Area_num])

Trong đó:

• Reference: Bắt buộc. Là vùng tham chiếu cố định.

Lưu ý: 

Nếu phạm vi không liền kề cho tham chiếu thì bạn nên đặt dữ liệu tham chiếu trong dấu ngoặc đơn để đảm bảo kết quả trả về được chính xác.

Ngoài ra, bạn cần lưu ý nếu vùng tham chiếu chỉ chứa 1 hàng hoặc 1 cột thì đối số Row_num hoặc Column_num tương ứng là tùy chọn.

• Row_num: Bắt buộc. Là số hàng trong tham chiếu từ đó đưa ra một tham chiếu có tính chất cố định.
• [Column_num]: Tùy chọn. Là số cột từ trong tham chiếu đó đưa ra một tham chiếu có tính chất tùy chọn.
• [Area_num]: Không bắt buộc. Là số của một phạm vi trong tham chiếu để trả về ô giao điểm giữa Row_num và Column_num.

Ví dụ: Cùng ví dụ với hàm INDEX dạng mảng, bài toán yêu cầu bạn tìm và hiển thị giá trị của đơn hàng thứ 3 trong vùng dữ liệu.

Công thức hàm: =INDEX($A$3:$B$6,3,1,1)

Kết quả trả về là Cam.

Hàm SMALL

Hàm SMALL được xem như là một hàm liệt kê trong Excel giúp trả về giá trị nhỏ thứ K của nguồn dữ liệu tham chiếu. Bạn có thể áp dụng tính năng này để trả về các giá trị thứ hạng tương đối cụ thể trong phạm vị tham chiếu.

Cấu trúc hàm: =SMALL(array,k)

Trong đó:

• Array: Bắt buộc. Là mảng dữ liệu dạng dạng số mà bạn muốn tìm ra giá trị nhỏ thứ K của nó.
• K: Bắt buộc. Là vị trí (thứ tự nhỏ nhất) trong mảng dữ liệu cần trả về.

Lưu ý: Đối số K > 0 và nhỏ hơn số điểm dữ liệu trong đối số Array, nếu không thì hàm tính sẽ trả về giá trị lỗi #NUM!.

Ví dụ: Bài toán yêu cầu bạn tính giá trị nhỏ nhất thứ K dựa theo dữ liệu tham chiếu trong bảng tính như sau:

Bước 1: Tại ô E8, bạn nhập cú pháp hàm =SMALL(B3:B6,3).

Bước 2: Bạn nhấn Enter. Lúc này, kết quả hiển thị là 50 là số nhỏ thứ 3 trong vùng tham chiếu.

Bài toán minh họa về cách sử dụng hàm 

Đến đây thì có lẽ bạn đã hiểu rõ hơn về mục đích sử dụng cũng như cách thực hiện từng hàm liệt kê. Vậy trong phần sau đây, FPT Shop sẽ hướng dẫn bạn thực hiện sử dụng kết hợp tất cả các hàm để tạo ra một công thức hàm liệt kê danh sách trong Excel qua ví dụ minh họa nhé!

Ví dụ: Bài toán yêu cầu bạn liệt kê các hàng chứa giá trị A từ cột B và trả về các giá trị tương ứng từ cột A trong bảng tính sau:

Bước 1: Tại ô E1, bạn nhập cú pháp =IFERROR(INDEX($A$1:$A$6, SMALL(IF($B$1:$B$6="A", ROW($B$1:$B$6)-ROW($B$1)+1, ""), ROW(A1))), "").

Giải thích hàm:

• IFERROR: Để tránh lỗi nếu không tìm thấy giá trị phù hợp.
• INDEX: Trả về giá trị từ cột A dựa trên chỉ số hàng.
• SMALL: Trả về giá trị nhỏ nhất thứ N từ mảng được tạo bởi hàm IF.
• IF($B$1:$B$6="A",ROW($B$1:$B$6)-ROW($B$1)+1,""): Tạo một mảng chứa các chỉ số hàng nơi giá trị của cột B là A.
• ROW(A1): Trả về giá trị hàng hiện tại, sử dụng để tăng dần chỉ số trong hàm SMALL.

Bước 2: Nhấn tổ hợp phím Ctrl + Shift + Enter và thực hiện sao chép để trả kết quả cho các ô còn lại.

Lời kết

Trên đây là tổng hợp các hàm liệt kê trong Excel giúp người dùng thực hiện các tác vụ xử lý và phân tích dữ liệu một cách hiệu quả và chính xác, từ đó đưa ra các quyết định thông minh.

Xem thêm:

• Hàm IFERROR trong Excel: Cú pháp cơ bản và 4 cách kết hợp được sử dụng phổ biến nhất
• Mẹo vặt Hướng dẫn kết hợp SUM và VLOOKUP trong Google Sheet đơn giản, dễ hiểu nhất 2024

Để tối ưu hóa hiệu suất và xử lý dữ liệu một cách nhanh chóng, hiệu quả, bạn nên trang bị cho mình một chiếc máy tính chất lượng cùng công cụ mạnh mẽ từ Microsoft Office là một điều rất cần thiết cho công việc.

Tham khảo ngay các mẫu máy tính tại đây FPT Shop:

• Máy tính Lenovo