5 Cách Dùng ISERROR Biến Bảng Excel Lỗi Lổn Nhổn Thành Báo Cáo Chuyên Nghiệp

Bảng tính Excel của tôi từng đầy các thông báo lỗi đỏ chói như #DIV/0!, #N/A, #VALUE! khắp nơi. Mỗi lần gửi báo cáo cho sếp, tôi phải dành 20 phút để tìm và xóa thủ công từng ô lỗi, sau đó nhập lại giá trị 0 hoặc để trống. Cho đến khi tôi phát hiện hàm ISERROR có thể tự động hóa toàn bộ quá trình này trong vài giây. Dưới đây là 5 cách cụ thể giúp bảng tính trông chuyên nghiệp mà không cần can thiệp thủ công.

Mục Lục

Kết hợp ISERROR với IF để thay thế lỗi tự động

Cách phổ biến nhất là lồng ISERROR vào hàm IF để kiểm tra lỗi và trả về giá trị thay thế. Hàm ISERROR trả về TRUE nếu phát hiện bất kỳ lỗi nào trong 7 loại lỗi Excel phổ biến, bao gồm #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, và #NULL!.

Cú pháp cơ bản:

=IF(ISERROR(công_thức_của_bạn), giá_trị_thay_thế, công_thức_của_bạn)

Giả sử bạn có bảng tính doanh số với cột Tổng Doanh Thu (cột B) và Số Đơn Hàng (cột C). Để tính doanh thu trung bình mỗi đơn, công thức =B2/C2 sẽ gây lỗi #DIV/0! khi cột C có giá trị 0.

Giải pháp: Tại ô D2, nhập công thức:

=IF(ISERROR(B2/C2), 0, B2/C2)

Công thức này hoạt động như sau: nếu B2 chia C2 gây lỗi, trả về số 0. Nếu không có lỗi, trả về kết quả phép chia bình thường. Kéo công thức xuống toàn bộ cột D, mọi ô lỗi sẽ tự động hiển thị 0 thay vì thông báo đỏ chói.

Tôi áp dụng kỹ thuật này cho bảng báo cáo 200 dòng và tiết kiệm 15 phút mỗi lần cập nhật dữ liệu. Thay vì thay thế từng giá trị lỗi thủ công, công thức tự động xử lý ngay khi có dữ liệu mới.

XEM THÊM: Hàm ISLOGICAL Trong Excel: 5 Sai Lầm Tôi Từng Mắc Phải Và Cách Khắc Phục

Mẹo nâng cao: Thay số 0 bằng chuỗi văn bản rỗng "" nếu muốn ô trống hoàn toàn. Hoặc thay bằng thông báo tùy chỉnh như “Chưa có dữ liệu” để dễ phân biệt.

Xử lý lỗi VLOOKUP với ISERROR

Hàm VLOOKUP thường gây lỗi #N/A khi không tìm thấy giá trị tra cứu. Điều này khiến bảng tính trông lộn xộn và khó đọc, đặc biệt khi làm báo cáo tổng hợp từ nhiều nguồn dữ liệu.

Giả sử bạn có bảng danh sách sản phẩm (cột A) và muốn tra cứu đơn giá từ bảng giá riêng. Công thức VLOOKUP chuẩn:

=VLOOKUP(A2, Bảng_Giá!$A$2:$B$50, 2, FALSE)

Khi sản phẩm trong cột A không tồn tại ở Bảng Giá, công thức trả về #N/A. Để xử lý tình huống này chuyên nghiệp:

Công thức hoàn chỉnh:

=IF(ISERROR(VLOOKUP(A2, Bảng_Giá!$A$2:$B$50, 2, FALSE)), "Chưa có giá", VLOOKUP(A2, Bảng_Giá!$A$2:$B$50, 2, FALSE))

Công thức kiểm tra xem VLOOKUP có gây lỗi không. Nếu có lỗi (sản phẩm không tìm thấy), hiển thị văn bản “Chưa có giá”. Nếu tìm thấy, hiển thị đơn giá như bình thường.

Lưu ý quan trọng: công thức trên phải gọi VLOOKUP hai lần, một lần để kiểm tra lỗi và một lần để lấy kết quả. Điều này có thể làm chậm file nếu bảng dữ liệu lớn. Với Excel 2013 trở lên, nên dùng hàm IFERROR hoặc IFNA để tối ưu hiệu suất hơn, nhưng ISERROR vẫn hoạt động tốt với Excel 2007 trở xuống.

Biến thể hữu ích: Thay “Chưa có giá” bằng số 0 nếu bạn cần tính tổng hoặc trung bình cho cột đơn giá. Điều này đảm bảo các hàm tổng hợp như SUM hoặc AVERAGE vẫn chạy được mà không bị lỗi.

Đếm số lượng lỗi trong vùng dữ liệu

Khi làm việc với bảng tính lớn chứa hàng trăm công thức, việc biết có bao nhiêu ô đang bị lỗi giúp kiểm soát chất lượng dữ liệu tốt hơn. Kết hợp SUMPRODUCT với ISERROR cho phép đếm tổng số lỗi trong một phạm vi cụ thể.

XEM THÊM: Tôi Mất 3 Giờ Kiểm Tra Dữ Liệu Mỗi Tuần Cho Đến Khi Phát Hiện Hàm ISNONTEXT

Công thức đếm lỗi:

=SUMPRODUCT(--ISERROR(A2:A100))

Công thức này hoạt động như sau: ISERROR kiểm tra từng ô trong vùng A2:A100 và trả về TRUE nếu có lỗi, FALSE nếu không. Dấu hai trừ -- chuyển đổi TRUE thành 1 và FALSE thành 0. SUMPRODUCT cộng tất cả các giá trị 1 lại, cho biết tổng số ô lỗi.

Ví dụ, nếu vùng A2:A100 chứa 5 ô có lỗi #DIV/0!, 3 ô có lỗi #N/A, và các ô còn lại không lỗi, công thức trả về kết quả là 8.

Tôi đặt công thức này ở góc trên bên phải mỗi bảng tính lớn để theo dõi chất lượng dữ liệu. Khi kết quả khác 0, tôi biết cần kiểm tra lại nguồn dữ liệu hoặc công thức trước khi gửi báo cáo.

Mở rộng: Kết hợp với định dạng có điều kiện để tô màu đỏ ô đếm lỗi khi giá trị lớn hơn 0. Điều này tạo cảnh báo trực quan ngay trên bảng tính.

Sử dụng công thức mảng để làm sạch dữ liệu hàng loạt

Với các phiên bản Excel cũ (trước 2019), bạn có thể dùng công thức mảng kết hợp COUNT, IF và ISERROR để xử lý nhiều ô cùng lúc. Đây là kỹ thuật nâng cao nhưng rất hiệu quả cho việc kiểm tra lỗi hàng loạt.

Công thức đếm lỗi theo loại:

=COUNT(IF(ISERROR(A2:A100), 1, ""))

Sau khi nhập công thức, nhấn tổ hợp phím Ctrl + Shift + Enter thay vì Enter thông thường. Excel sẽ tự động thêm dấu ngoặc nhọn {} xung quanh công thức, biến nó thành công thức mảng.

Công thức mảng này kiểm tra từng ô trong vùng A2:A100. Nếu ô có lỗi, đánh dấu là 1. Nếu không lỗi, đánh dấu là chuỗi rỗng. Hàm COUNT đếm tổng số giá trị số (các số 1), bỏ qua chuỗi rỗng.

Lưu ý quan trọng: Công thức mảng có thể làm chậm file Excel nếu áp dụng cho vùng dữ liệu quá lớn (trên 10.000 ô). Với Excel 365 hoặc Excel 2021, không cần nhấn Ctrl + Shift + Enter vì các phiên bản này hỗ trợ dynamic arrays tự động.

XEM THÊM: Tìm Và Xử Lý Ô Trống Trong Excel Chỉ Trong 30 Giây Với Hàm ISBLANK

Ứng dụng thực tế: Khi nhận file Excel từ hệ thống khác có nhiều lỗi định dạng, dùng công thức này để đánh giá mức độ nghiêm trọng trước khi quyết định làm sạch thủ công hay tự động.

Thay thế lỗi trong công thức phức tạp bằng giá trị mặc định

Đối với các công thức phức tạp có nhiều hàm lồng nhau, ISERROR giúp đảm bảo công thức không bị gián đoạn bởi lỗi từ bất kỳ bước nào. Kỹ thuật này đặc biệt hữu ích khi xây dựng dashboard hoặc báo cáo tự động.

Giả sử bạn có công thức tính tỷ lệ tăng trưởng:

=(B2-A2)/A2*100

Công thức này gây lỗi #DIV/0! khi A2 bằng 0. Thay vì để lỗi hiển thị, bạn có thể trả về giá trị mặc định như “N/A” hoặc số 0:

Công thức an toàn:

=IF(ISERROR((B2-A2)/A2*100), "N/A", (B2-A2)/A2*100)

Hoặc nếu muốn hiển thị 0 thay vì văn bản:

=IF(ISERROR((B2-A2)/A2*100), 0, (B2-A2)/A2*100)

Kỹ thuật này đảm bảo các biểu đồ hoặc bảng tổng hợp phía sau vẫn hoạt động bình thường dù dữ liệu nguồn có lỗi. Tôi áp dụng phương pháp này cho tất cả các công thức tính toán quan trọng trong dashboard quản lý để tránh báo cáo bị gián đoạn.

Mẹo tối ưu: Với Excel 2013 trở lên, hàm IFERROR ngắn gọn và hiệu quả hơn ISERROR trong trường hợp này. Tuy nhiên, ISERROR vẫn cần thiết nếu bạn muốn phân biệt giữa lỗi và không lỗi để thực hiện các xử lý khác nhau.

Kết quả sau khi áp dụng

Hàm ISERROR hoạt động trên mọi phiên bản Excel từ 2007 trở đi, bao gồm cả Excel 2024 và Excel 365. Các công thức trên tương thích với cả phiên bản Windows và Mac. Bắt đầu với kỹ thuật kết hợp ISERROR và IF cho các bảng tính nhỏ trước khi áp dụng công thức mảng phức tạp hơn. Nhớ rằng với Excel 2013 trở lên, hàm IFERROR thường là lựa chọn tốt hơn cho hiệu suất, nhưng ISERROR vẫn quan trọng khi cần kiểm tra lỗi mà không thay đổi giá trị gốc.