Hàm liệt kê danh sách trong Excel ai cũng nên biết

» Microsoft Office » Hàm liệt kê danh sách trong Excel ai cũng nên biết

Hàm liệt kê danh sách trong Excel và cách liệt kê danh sách theo điều kiện trong Excel.

Contents

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

Dưới đây chúng mình xin giới thiệu đến bạn các hàm liệt kê danh sách trong Excel thông dụng nhất, từ dạng mảng đến dạng tham chiếu và dạng có điều kiện. Chúng ta sẽ cùng tìm hiểu và phân tích chi tiết mục đích cũng như là cách sử dụng của các hàm liệt kê danh sách trong Excel này.

Hàm INDEX

Trong Excel, nếu được thực hiện đúng thì hàm INDEX sẽ trả về kết quả là giá trị tại một vị trí nhất định nằm trong một phạm vi hoặc một mảng tìm kiếm. 

Ứng dụng hàm liệt kê danh sách trong Excel này vào thực tế, bạn có thể tận dụng chức năng của hàm INDEX để truy xuất các giá trị riêng lẻ hoặc truy xuất toàn bộ một hàng/một cột. Hàm INDEX thường được sử dụng kết hợp với hàm MATCH. Khi này hàm MATCH thực chất là hỗ trợ định vị rồi cung cấp một vị trí cho hàm INDEX.

Khi sử dụng bạn cần chú ý hàm INDEX có hai dạng là dạng mảng và dạng tham chiếu. Cả hai dạng này về cơ bản đều có cách trả về giá trị giống nhau (đều là một tham chiếu nằm trong một mảng tìm kiếm, dựa trên vị trí của hàng và vị trí của cột nhất định).

Tuy vậy chúng vẫn có sự khác biệt nhất định. Dạng tham chiếu của hàm INDEX có thể dùng để tham chiếu nhiều hơn là dạng mảng. Bên cạnh đó, dạng tham chiếu còn có một đối số tùy chọn để có thể chọn được mảng chính xác nên được sử dụng. 

Chính vì ưu điểm vượt trội này mà trong công việc, nhiều người thường sử dụng công thức hàm  INDEX ở dạng mảng. Tuy nhiên dưới đây chúng mình vẫn trình bày cả hai dạng để bạn có đủ thông tin tham khảo.

Hàm INDEX dạng mảng

Công thức chung của hàm INDEX là:

=INDEX (Array;row_num;[col_num])

Trong đó:

Như vậy sẽ có hai trường một số trường hợp xảy ra:

Nếu cả row_num và col_num đều được cung cấp đầy đủ, lúc này hàm INDEX sẽ trả về giá trị trong ô nằm tại chính giao điểm của row_num và col_num.

Nếu row_num được cung cấp bằng 0, khi này hàm INDEX sẽ trả về một mảng giá trị cho toàn bộ cột. Để có thể sử dụng được các giá trị mảng vừa được trả về này, bạn cần nhập hàm INDEX dưới dạng công thức mảng trong phạm vi ngang (hoặc bạn cũng có lựa chọn khác là cung cấp mảng vào một hàm khác).

Nếu  col_num được cung cấp bằng 0, khi đó hàm INDEX sẽ trả về một mảng giá trị cho toàn bộ một hàng. Để có thể sử dụng các giá trị mảng vừa được trả về này, bạn có thể nhập hàm INDEX dưới dạng công thức mảng trong phạm vi dọc (hoặc bạn cũng có lựa chọn khác là cung cấp mảng vào một hàm khác).

Hàm INDEX dạng tham chiếu

Trong hàm  INDEX dạng tham chiếu, tham số đầu tiên chính là tham chiếu đến một hay nhiều phạm vi và đối số tùy chọn thứ tư, còn area_num được cung cấp nhằm chọn phạm vi thích hợp. 

Công thức của biểu mẫu tham chiếu của hàm INDEX là:

=INDEX(reference;row_num;[col_num];[area_num])

Trong đó:

Tương tự như hàm INDEX dạng mảng, dạng tham chiếu của hàm INDEX cũng trả về tham chiếu của ô tại chính giao điểm của row_num và col_num. Sự khác biệt của hai dạng này là: đối với dạng tham chiếu, đối số tham chiếu có thể chứa nhiều hơn một dải ô và area_num chọn dải ô sẽ được sử dụng. Như vậy mảng đầu tiên bên trong tham chiếu sẽ là 1, mảng thứ hai là 2,…

Hàm SMALL

Trong các hàm liệt kê danh sách trong Excel, hàm SMALL giúp trả về các giá trị là số dựa trên vị trí xuất hiện của chúng trong danh sách được xếp hạng theo giá trị. Nói rõ hơn thì hàm này có thể truy xuất ra giá trị nhỏ nhất đầu tiên, giá trị nhỏ nhất thứ 2, giá trị nhỏ nhất thứ 3, hoặc giá trị “nhỏ nhất thứ n”.

Dưới đây là công thức hàm SMALL sẽ trả về giá trị nhỏ nhất thứ n trong mảng tìm kiếm.

=SMALL(Array;n)

Trong đó:

Như vậy trong thực tế để ứng dụng các hàm liệt kê danh sách trong Excel, khi muốn truy xuất giá trị thấp nhất thứ n từ một tập dữ liệu có trước, bạn nên sử dụng hàm SMALL, chủ yếu là tìm thời gian nhanh nhất, thứ hai hoặc thứ ba trong một cuộc thi, như được mô tả trong ảnh dưới đây.

Chú ý: để nhận về giá trị lớn thứ n, bên cạnh hàm SMALL bạn cũng có thể sử dụng hàm LARGE. Hàm này cũng thực hiện điều tương tự nếu bạn cần tìm kiếm giá trị lớn nhất thứ n với công thức như sau =LARGE(array;n).

Hàm IFERROR

Trong các hàm liệt kê danh sách trong Excel, hàm IFERROR sẽ trả về một kết quả tùy chỉnh nếu có một công thức tạo ra lỗi, hoặc trả về một kết quả chuẩn khi không có bất cứ lỗi nào được phát hiện. 

Trong ứng dụng thực tế, IFERROR là một cách để quản lý lỗi đơn giản và nhanh chóng hơn, thay vì sử dụng các câu lệnh IF lồng nhau một cách dài dòng và phức tạp.

Hàm IFERROR giúp “bẫy” và xử lý lỗi cho các dữ liệu không xác định hoặc bị lỗi, sau đó trả về các giá trị bạn chỉ định cho các điều kiện lỗi này.

Công thức hàm IFERROR là: 

=IFERROR(value;value_if_error)

Trong đó:

Điều này có nghĩa là hàm IFERROR sẽ tìm kiếm và phát hiện ra lỗi trong công thức rồi trả về kết quả hoặc công thức thay thế mỗi khi lỗi được phát hiện.

Có thể gọi IFERROR là hàm đi sửa lỗi do các công thức hoặc hàm khác gây ra. Cụ thể thì IFERROR có thể kiểm tra các lỗi sau đây: #NULL !, # N / A, # DIV / 0 !, #VALUE !, #NAME ?, #REF !, #NUM !. 

Chú ý rằng nếu bạn nhìn thấy lỗi ######, đừng vội xử lý. Đây thực ra không phải là một lỗi dữ liệu mà chỉ là lỗi hiển thị dữ liệu trong Excel, do đó hàm IFERROR sẽ không bắt và xử lý lỗi này.

Dưới đây là một ví dụ để bạn dễ dàng hình dung về chức năng của hàm liệt kê danh sách trong Excel này:

Ví dụ: nếu A1 chứa 10, B1 trống và C1 chứa công thức =A1/B1, thì công thức sau sẽ bắt lỗi # DIV / 0! lỗi do chia A1 cho B1 như trong hình bên dưới.

Cách liệt kê danh sách theo điều kiện trong Excel

Thực tế thì khi sử dụng hàm VLOOKUP nhiều người thường hay gặp lỗi #N/A khi hàm không tìm thấy giá trị tra cứu. Khi đó, bạn có thể sử dụng hàm IFERROR để bắt lỗi này theo công thức như sau:

=IFERROR(VLOOKUP(value;data;column;0);”Không tìm thấy”)

Trong ví dụ này, khi hàm VLOOKUP trả về kết quả như thế nào thì hàm IFERROR sẽ trả về chính kết quả đó. Còn nếu hàm VLOOKUP trả về lỗi #N/A (khi không tìm được giá trị cần tra cứu), thì hàm IFERROR sẽ trả về kết quả là “Không tìm thấy”.

Một số lưu ý:

Như vậy trên đây chúng mình đã chia sẻ đến bạn những hàm liệt kê danh sách trong Excel thông dụng nhất. Hy vọng rằng những kiến thức này sẽ giúp quá trình làm việc trên Excel của bạn trở nên dễ dàng hơn. Cuối cùng, để liên tục cập nhật thêm các thủ thuật tin học văn phòng hay ho khác, đừng quên bấm theo dõi trang nhé. 

Microsoft Office -