Hướng dẫn dùng hàm liệt kê trong Excel dễ dàng, chi tiết

Hàm liệt kê trong Excel có thể hiểu như những công cụ giúp bạn “gom” dữ liệu theo tiêu chí nhất định để bảng tính trở nên có trật tự và dễ đọc hơn. Khi biết cách vận hành chúng, bạn sẽ biến những danh sách rối thành thông tin rõ ràng chỉ với vài cú chạm. Hãy điểm qua phần hướng dẫn ngay sau đây để khéo léo tận dụng chúng vào tác vụ hằng ngày nhé.

Những hàm liệt kê trong Excel phổ biến

Việc xử lý danh sách dài trong bảng tính sẽ nhẹ nhàng hơn nhiều khi bạn tận dụng đúng hàm liệt kê trong Excel. Nhờ khả năng lọc, gom và sắp xếp tự động, những hàm này hỗ trợ bạn kiểm soát thông tin rõ ràng hơn.

Hàm IFERROR

Hàm IFERROR là công cụ dùng để “đệm” kết quả thay thế mỗi khi một phép tính phát sinh lỗi. Nó giúp chuỗi xử lý, đặc biệt trong các hàm liệt kê có điều kiện - không bị ngắt quãng.

Cú pháp:

Trong đó:

• giá_trị_cần_kiểm_tra: công thức hoặc biểu thức có khả năng tạo lỗi.
• giá_trị_khi_lỗi: nội dung bạn muốn trả về nếu biểu thức phía trước gặp lỗi (như #N/A, #DIV/0!, #VALUE!...).

Ví dụ: Bạn có một bảng gồm mã số và tên, nếu muốn truy xuất một mã bất kỳ hãy dùng công thức kết hợp =IFERROR(VLOOKUP("179", A1:B22, 2, FALSE), "Không tìm thấy")

→ Nếu VLOOKUP trả về lỗi, ô sẽ hiện “Không tìm thấy” thay vì mã lỗi khó đọc.

Ngoài hàm liệt kê, bạn có thể linh hoạt vận dụng hàm khác để xử lý tác vụ chỉ với một chiếc laptop tiêu chuẩn. Đây là lúc bạn nâng cấp thiết bị của mình, hãy tham khảo ngay.

[Product_Listing categoryid="380" propertyid="" customlink="https://cellphones.com.vn/laptop.html" title="Danh sách Laptop đang được quan tâm nhiều tại CellphoneS"]

Hàm INDEX dạng mảng

Hàm INDEX dạng mảng đóng vai trò là công cụ trích xuất chính xác trong môi trường bảng tính. Hơn nữa, có thể coi INDEX như một dạng hàm liệt kê theo điều kiện trong Excel vì nó cho phép trích xuất các yếu tố dựa trên điều kiện vị trí được quy định.

Cú pháp:

Trong đó:

• mảng_dữ_liệu: tập giá trị bạn muốn truy xuất.
• vị_trí_hàng: số thứ tự dòng trong mảng.
• vị_trí_cột (tùy chọn): chỉ định cột nếu mảng có nhiều cột.

Ví dụ: Bạn có một bảng gồm mã số và tên, nếu muốn truy xuất thông tin ở 1 dòng bất kỳ (ví dụ giá trị thứ ba từ danh sách) hãy dùng công =INDEX(UNIQUE(A2:B22), 3). Trong đó hàm UNIQUE(A2:B22) giúp liệt kê các giá trị khác nhau trong Excel, tránh các giá trị lặp lại.

Hàm INDEX dạng tham chiếu

Hàm INDEX dạng tham chiếu cho phép truy xuất giá trị từ một hoặc nhiều phạm vi dữ liệu rời rạc dựa trên chỉ số hàng, cột và vùng được chỉ định. Nó cũng được xem là hàm liệt kê trong Excel thông qua lọc và sắp xếp các yếu tố từ các nguồn đa dạng để tạo ra kết quả chính xác.

Cú pháp:

Trong đó:

• vùng_1, vùng_2…: các dải dữ liệu mà bạn muốn cho INDEX lựa chọn.
• số_chỉ_mục_vùng: xác định đang lấy dữ liệu từ vùng thứ mấy trong danh sách các vùng.
• số_hàng: số thứ tự dòng trong vùng đã chọn.
• số_cột (tùy chọn): chỉ định cột nếu vùng có nhiều cột.

Ví dụ: Giả sử tham_chiếu là (A1:B2,D1:E2) với các giá trị A1=Alpha, A2=Beta, B1=Gamma, B2=Delta, D1=Epsilon, D2=Zeta, E1=Eta, E2=Theta; khi áp dụng công thức 

• =INDEX((A1:B2,D1:E2), 2, 1, 2), kết quả thu được là Zeta (tương ứng hàng 2, cột 1 từ vùng thứ 2);
• =INDEX((A1:B2,D1:E2), 1, 2) sẽ trả về Gamma (từ vùng đầu tiên, hàng 1, cột 2), thể hiện khả năng điều hướng hiệu quả giữa các khối dữ liệu riêng lẻ.

Hàm SMALL

Hàm SMALL dạng tham chiếu được dùng để truy xuất phần tử có giá trị nhỏ thứ k trong một tập hợp. Hàm thường được lồng vào các hàm liệt kê có điều kiện để rút ra thứ tự giá trị theo tiêu chí mong muốn. 

Cú pháp:

Trong đó:

• mảng_dữ_liệu: nhóm giá trị bạn muốn xét thứ hạng.
• k: vị trí nhỏ thứ mấy cần lấy (1 là nhỏ nhất, 2 là nhỏ thứ hai…).

Ví dụ: Giả sử phạm vi A1:A5 chứa các giá trị 8, 3, 7, 1, 5; khi áp dụng công thức 

• =SMALL(A1:A5, 3), kết quả thu được là 5 (giá trị nhỏ thứ ba sau khi sắp xếp: 1, 3, 5, 7, 8); tương tự, 
• Nếu kết hợp với FILTER như =SMALL(FILTER(A1:A5, A1:A5>4), 2) thì sẽ trả về 7 (xếp hạng thứ hai trong tập đã lọc: 5, 7, 8).

Các lỗi thường gặp khi dùng hàm liệt kê

Ngoài cách áp dụng khéo léo hàm liệt kê trong Excel, bạn cũng nên điểm qua một số lỗi dễ gặp sau:

• Sai sót về không gian đổ dữ liệu (#SPILL!): Người dùng thường bỏ qua việc kiểm tra vùng ô trống xung quanh, dẫn đến lỗi khi mảng kết quả không có đủ chỗ để mở rộng tự động.
• Lỗi khớp kích thước mảng (#VALUE!): Thường xảy ra khi các đối số trong hàm không đồng nhất về chiều dài hoặc loại dữ liệu. Ví dụ kết hợp một mảng hàng ngang với mảng dọc mà không sử dụng TRANSPOSE, khiến hàm không thể xử lý đồng bộ.
• Điều kiện lọc không chính xác: Nhiều trường hợp người dùng viết sai biểu thức logic trong FILTER, như sử dụng dấu so sánh không phù hợp hoặc quên ngoặc đơn, dẫn đến kết quả trống.
• Bỏ qua xử lý giá trị rỗng hoặc lỗi: Khi nguồn dữ liệu chứa ô trống hoặc lỗi như #N/A, hàm liệt kê có thể trả về lỗi nếu không kết hợp với IFERROR hoặc các hàm lọc bổ sung.
• Sử dụng sai phiên bản Excel: Một số hàm liệt kê chỉ hoạt động đầy đủ trên Excel 365 hoặc phiên bản mới, dẫn đến lỗi #NAME! trên các bản cũ hơn.

Để tránh lỗi hàm liệt kê theo điều kiện trong Excel, việc thực hành thường xuyên và kiểm tra từng bước sẽ giúp nâng cao kỹ năng sử dụng hàm liệt kê một cách hiệu quả. 

Mẹo khi sử dụng hàm liệt kê trong Excel

Hàm liệt kê trong Excel mang lại sức mạnh xử lý dữ liệu động. Để khai thác tối ưu, cần áp dụng một số chiến lược thông minh nhằm tránh sai lầm và nâng cao hiệu suất.

• Chuẩn bị không gian đổ dữ liệu rộng rãi: Luôn đảm bảo vùng ô xung quanh công thức trống đủ để mảng kết quả tự động mở rộng, tránh lỗi #SPILL! bằng cách di chuyển công thức đến vị trí thoáng đãng.
• Kết hợp linh hoạt với các hàm hỗ trợ: Hãy ghép nối hàm liệt kê với SORT hoặc TRANSPOSE để sắp xếp hoặc chuyển hướng mảng.
• Tận dụng tham chiếu động với bảng hoặc tên định nghĩa: Chuyển dữ liệu nguồn thành bảng Excel (Ctrl+T) rồi tham chiếu bằng tên cột như Table1[Column1].
• Kiểm tra tính tương thích phiên bản: Trước khi triển khai, xác nhận Excel đang dùng hỗ trợ mảng động (như Microsoft 365), để tránh lỗi #NAME! bằng cách cung cấp công thức thay thế đơn giản hơn.

Bằng cách áp dụng những mẹo này một cách sáng tạo, bạn sẽ biến hàm liệt kê thành công cụ mạnh mẽ, tiết kiệm thời gian đáng kể trong công việc hàng ngày. Cuối cùng, thực hành thường xuyên trên các tập dữ liệu thực tế sẽ giúp bạn nắm vững và phát huy hết tiềm năng của chúng trong môi trường Excel.

Hàm liệt kê trong Excel giúp bảng tính tự động chọn và hiển thị những phần dữ liệu bạn đang tìm. Khi đã nắm được cách vận hành các hàm này, việc xử lý danh sách dài sẽ trở nên nhẹ nhàng và chính xác hơn rất nhiều. Sforum sẽ đồng hành cùng bạn trong các mẹo tối ưu tác vụ văn phòng tiếp theo trong cùng chuyên mục, hãy tiếp tục theo dõi nhé!

Xem thêm các bài viết tại chuyên mục: Tin học văn phòng/Thủ thuật excel