Khi bảng tính hiển thị dòng chữ #DIV/0! hoặc #REF!, người dùng thường chỉ biết có lỗi nhưng không hiểu nguyên nhân cụ thể. Excel có hàm ERROR.TYPE để phân loại từng loại lỗi thành mã số từ 1 đến 7, giúp tạo thông báo rõ ràng thay vì những ký hiệu khó hiểu.
Hàm ERROR.TYPE hoạt động như thế nào
Hàm này trả về một số nguyên tương ứng với loại lỗi trong ô được kiểm tra. Cú pháp đơn giản: =ERROR.TYPE(error_val) trong đó error_val là ô hoặc công thức cần kiểm tra lỗi.
Nếu ô không có lỗi, hàm trả về giá trị #N/A. Điều này giúp phân biệt giữa ô có lỗi thực sự và ô tính toán bình thường.
Cách sử dụng cơ bản:
- Nhập công thức
=ERROR.TYPE(A1)vào ô bất kỳ - Nếu A1 chứa lỗi #DIV/0!, kết quả là số 2
- Nếu A1 tính toán bình thường, kết quả là #N/A
Hàm này có sẵn từ Excel 2007 trở đi, bao gồm Excel 365, Excel 2019, và Excel 2021.
Bảng phân loại 7 mã lỗi
Mỗi loại lỗi trong Excel được gán một mã số duy nhất. Hiểu được hệ thống này giúp xây dựng thông báo lỗi có ý nghĩa.
Danh sách đầy đủ:
- Mã 1: #NULL! – Vùng giao không hợp lệ
- Mã 2: #DIV/0! – Chia cho số không
- Mã 3: #VALUE! – Kiểu dữ liệu sai
- Mã 4: #REF! – Tham chiếu ô không tồn tại
- Mã 5: #NAME? – Tên hàm không được nhận dạng
- Mã 6: #NUM! – Số không hợp lệ
- Mã 7: #N/A – Giá trị không khả dụng
Trong Excel 365, Microsoft bổ sung thêm các mã lỗi cho mảng động như #SPILL! và #CALC!, nhưng ERROR.TYPE chỉ xử lý 7 lỗi cơ bản này.
Lỗi chia cho không – Mã 2
Đây là lỗi phổ biến nhất khi tính toán tỷ lệ hoặc tỷ suất. Công thức =A1/B1 hiển thị #DIV/0! nếu B1 chứa số 0 hoặc ô trống.
Sử dụng ERROR.TYPE để tạo thông báo rõ ràng:
=IF(ERROR.TYPE(A1/B1)=2, "Không thể chia cho 0", A1/B1)
Công thức này kiểm tra mã lỗi. Nếu bằng 2, hiển thị thông báo thân thiện. Nếu không có lỗi, hiển thị kết quả chia.
Một cách tiếp cận khác là thay bằng 0:
=IF(ERROR.TYPE(A1/B1)=2, 0, A1/B1)
Điều này hữu ích khi tính tổng nhiều cột, tránh lỗi lan truyền sang các công thức khác.
Lỗi tham chiếu – Mã 4
Lỗi #REF! xuất hiện khi xóa hàng hoặc cột mà công thức đang tham chiếu đến. Công thức =A1+B1 trở thành =#REF!+B1 sau khi xóa cột A.
Thay vì hiển thị #REF!, tạo cảnh báo cụ thể:
=IF(ERROR.TYPE(SUM(A1:A10))=4, "Vùng dữ liệu đã bị xóa", SUM(A1:A10))
Điều này đặc biệt quan trọng trong các bảng tính có nhiều người chỉnh sửa. Người dùng biết ngay vấn đề là do xóa ô, không phải lỗi công thức.
Đối với các bảng lớn, kết hợp với tham chiếu cấu trúc:
=IF(ERROR.TYPE(SUM(Table1[Doanh thu]))=4, "Cột Doanh thu không tồn tại", SUM(Table1[Doanh thu]))
Lỗi giá trị không khả dụng – Mã 7
Hàm tra cứu như VLOOKUP hoặc MATCH thường trả về #N/A khi không tìm thấy giá trị. Thay vì hiển thị lỗi, tạo thông báo hướng dẫn:
=IF(ERROR.TYPE(VLOOKUP(E1,A1:B100,2,0))=7, "Không tìm thấy mã sản phẩm", VLOOKUP(E1,A1:B100,2,0))
Công thức này kiểm tra mã lỗi 7. Nếu VLOOKUP không tìm thấy kết quả, hiển thị thông báo thân thiện thay vì #N/A.
Trong các báo cáo chuyên nghiệp, điều này giúp người xem hiểu nguyên nhân thay vì nhìn vào ký hiệu lỗi khó hiểu.
Kết hợp nhiều loại lỗi
Một ô có thể gặp nhiều loại lỗi khác nhau tùy thuộc vào dữ liệu đầu vào. Sử dụng nhiều điều kiện IF để xử lý từng trường hợp:
=IF(ERROR.TYPE(A1/B1)=2, "Lỗi chia 0",
IF(ERROR.TYPE(A1/B1)=3, "Dữ liệu không hợp lệ",
IF(ERROR.TYPE(A1/B1)=4, "Ô tham chiếu bị xóa",
A1/B1)))
Công thức này kiểm tra 3 loại lỗi phổ biến và hiển thị thông báo tương ứng. Nếu không có lỗi, thực hiện phép chia bình thường.
Với Excel 365, sử dụng hàm IFS ngắn gọn hơn:
=IFS(
ERROR.TYPE(A1/B1)=2, "Lỗi chia 0",
ERROR.TYPE(A1/B1)=3, "Dữ liệu không hợp lệ",
ERROR.TYPE(A1/B1)=4, "Ô bị xóa",
TRUE, A1/B1
)
Xây dựng bảng phân loại lỗi tự động
Trong các file Excel lớn với hàng trăm công thức, tạo bảng phân loại lỗi giúp debug nhanh hơn. Thiết lập một sheet riêng với các cột:
Cấu trúc bảng:
- Cột A: Địa chỉ ô cần kiểm tra
- Cột B: Công thức
=ERROR.TYPE(INDIRECT(A2)) - Cột C: Công thức phân loại loại lỗi
Công thức cột C:
=IF(B2=1,"Vùng giao NULL",IF(B2=2,"Chia 0",IF(B2=3,"Giá trị sai",IF(B2=4,"Tham chiếu lỗi",IF(B2=5,"Tên hàm sai",IF(B2=6,"Số không hợp lệ",IF(B2=7,"Không tìm thấy","Không có lỗi")))))))
Bảng này hiển thị tất cả các lỗi trong file cùng loại lỗi cụ thể. Quét qua một lần thay vì kiểm tra từng ô thủ công tiết kiệm hàng giờ.
ERROR.TYPE so với IFERROR
Hàm IFERROR đơn giản hơn khi chỉ cần thay thế mọi lỗi bằng một giá trị:
=IFERROR(A1/B1, "Lỗi")
Tuy nhiên IFERROR không phân biệt loại lỗi. Nó xử lý #DIV/0!, #REF!, #VALUE! giống nhau. ERROR.TYPE cho phép tạo thông báo riêng cho từng loại.
Khi nào dùng IFERROR:
- Cần thay thế tất cả lỗi bằng cùng một giá trị
- Công thức đơn giản không cần phân loại chi tiết
- Ưu tiên tốc độ viết công thức
Khi nào dùng ERROR.TYPE:
- Cần thông báo khác nhau cho từng loại lỗi
- Xây dựng hệ thống kiểm tra lỗi chuyên nghiệp
- Tạo bảng phân loại lỗi tự động
Trong các dashboard hoặc báo cáo quan trọng, ERROR.TYPE cung cấp thông tin chi tiết hơn giúp người dùng tự khắc phục thay vì chỉ biết “có lỗi”.
Lưu ý khi sử dụng trong thực tế
Hàm ERROR.TYPE chỉ hoạt động khi ô thực sự chứa lỗi. Nếu công thức trong ô tính toán bình thường, ERROR.TYPE trả về #N/A chứ không phải FALSE.
Điều này có nghĩa bạn không thể dùng:
=IF(ERROR.TYPE(A1)=2, "Có lỗi", "Không lỗi")
Bởi vì khi A1 không có lỗi, ERROR.TYPE(A1) trả về #N/A, khiến toàn bộ công thức IF cũng hiển thị #N/A.
Cách đúng là lồng công thức cần kiểm tra vào trong ERROR.TYPE:
=IF(ERROR.TYPE(A1/B1)=2, "Lỗi chia 0", A1/B1)
Khi tạo các công thức phức tạp với nhiều bước tính toán, đặt ERROR.TYPE ở bước cuối cùng thay vì kiểm tra từng bước riêng lẻ.
Tương thích và giới hạn
ERROR.TYPE có sẵn từ Excel 2007 trở đi, bao gồm tất cả phiên bản Excel 365. Hàm này hoạt động giống nhau trên Windows, Mac, và Excel Online.
Các lỗi mới trong Excel 365 như #SPILL! và #CALC! không được ERROR.TYPE nhận dạng. Microsoft chưa mở rộng hàm này cho các lỗi mảng động.
Đối với file cần chia sẻ với người dùng Excel cũ hơn 2007, tránh sử dụng ERROR.TYPE vì sẽ hiển thị lỗi #NAME? khi mở.
Hàm này xử lý lỗi hiệu quả trong các bảng có dữ liệu thay đổi thường xuyên hoặc nhiều người cùng chỉnh sửa. Thay vì để lỗi lan rộng, các thông báo rõ ràng giúp phát hiện và sửa vấn đề nhanh hơn.
