Khối ô kiểm tra cho file kế toán: đỏ là chưa được nộp
Đầu bài
File báo cáo qua tay ba người, tháng nào cũng có một chỗ lệch mà tới lúc sếp hỏi mới biết. Cần một khối ô đầu file tự trả lời: file này đã sạch chưa, không sạch thì hỏng ở đâu.
Các bước làm
- 1
Liệt kê đúng những thứ KHÔNG ĐƯỢC PHÉP sai
Cân đối tổng · không có lỗi công thức · không có dòng chưa phân loại · tổng con khớp tổng cha · không có ô trống ở cột bắt buộc. Mỗi thứ một dòng, viết bằng tiếng Việt cho người khác đọc được.
- 2
Mỗi dòng kiểm là một công thức trả về ĐÚNG/SAI
=IF(ABS(tổng_A - tổng_B)<1, "ĐẠT", "LỆCH "&TEXT(chênh,"#,##0")) — báo luôn lệch bao nhiêu, không chỉ báo sai. Biết số lệch là nửa đường tới nguyên nhân.
- 3
Quét lỗi công thức toàn file
=SUMPRODUCT(--ISERROR(vùng)) đếm số ô lỗi. Hoặc F5 → Special → Formulas → Errors để nhảy thẳng tới chúng.
- 4
Bắt ô trống ở cột bắt buộc
=COUNTBLANK(cột_mã) phải bằng 0. Dòng thiếu mã là dòng rơi ra ngoài mọi SUMIFS mà tổng cột vẫn cộng nó — sai kiểu êm nhất.
- 5
Bắt giá trị chưa có trong danh mục
=SUMPRODUCT(--(COUNTIF(danh_mục, cột_giá_trị)=0)) đếm số dòng mang mã lạ. Gõ sai một chữ trong tên phòng ban là dòng đó biến mất khỏi báo cáo phân tổ.
- 6
Một ô tổng kết duy nhất ở trên cùng
=IF(COUNTIF(vùng_kiểm,"ĐẠT")=số_dòng_kiểm, "FILE SẠCH", "CÒN "&COUNTIF(vùng_kiểm,"<>ĐẠT")&" LỖI") — cỡ chữ to, tô màu. Đây là thứ duy nhất người bận nhìn.
- 7
Đóng băng khối này lại
Freeze Panes để nó luôn nằm trên màn hình, và khoá ô để không ai vô tình gõ đè. Ô kiểm bị gõ đè thành số cứng thì file lúc nào cũng xanh.
Phím dùng trong bài
Bẫy hay dính
Bọc IFERROR quanh chính ô kiểm tra là tự bịt mắt: công thức kiểm bị hỏng vùng sẽ trả về "ĐẠT" thay vì báo lỗi. Ô kiểm tra là chỗ duy nhất trong file được phép hiện lỗi to đùng.
Cùng nhóm kiểm tra số liệu & kiểm soát nội bộ
- Truy vết công thức sai: ô nào nuôi ô nào, chạy từng bước xem hỏng ở đâuMột ô ra con số vô lý. Công thức dài ba dòng, lồng bốn tầng hàm, trỏ sang hai sheet khác. Nhìn thẳng vào nó không ai đoán được sai chỗ nào, mà xoá đi viết lại thì mất luôn cái đang đúng.
- File dùng chung: bắt số gõ đè lên công thứcBảng lương có cột tính bằng công thức, nhưng ai đó thấy kết quả không vừa ý nên gõ thẳng số vào. Cột nhìn vẫn bình thường, tháng sau đổi dữ liệu gốc thì ô đó không đổi theo và không ai biết.
- Soi chứng từ bất thường: tròn số, sát ngưỡng duyệt, cuối tuần, trùngCần rà 12.000 chứng từ chi trong năm để tìm dấu hiệu bất thường. Đọc hết là không thể. Phải để Excel khoanh vùng đáng ngờ trước, người chỉ xem phần được khoanh.
- Chọn mẫu kiểm tra ngẫu nhiên mà lần sau mở lại vẫn đúng mẫu đóPhải chọn 60 chứng từ trong 4.000 để kiểm chi tiết. Dùng RAND() thì cứ bấm phím là mẫu đổi, in ra một đằng file một nẻo, và không ai kiểm chứng được cách chọn có khách quan không.