Một bảng có thể vượt qua mọi quy tắc nhập liệu mà vẫn chứa những con số không thể đúng: một đơn hàng số lượng âm, một phiếu nhập kho ngày năm sau, một mặt hàng bán với đơn giá gấp mười lần danh mục. Đây là những giá trị bất thường, và chúng thường là dấu vết của lỗi gõ, lỗi dán hoặc lỗi trong lượt xử lý tự động.
Bài này chỉ cách dựng các cột cờ kiểm và quy tắc tô màu để bắt nhóm lỗi đó trong Excel và Google Sheets, cách nhờ AI đề xuất quy tắc phù hợp với bảng của bạn, và cách xử lý một dòng bị gắn cờ. Bài dừng ở việc phát hiện và sửa lỗi; phần dùng thống kê để tìm điểm ngoại lai cho mục đích phân tích được trang AI Phân Tích bàn riêng. Ví dụ dùng một bảng đơn hàng giả định.
Bất thường không đồng nghĩa với sai
Trước khi dựng quy tắc, cần thống nhất một điều: giá trị bất thường là giá trị đáng xem lại, không phải giá trị chắc chắn sai. Một đơn hàng hai trăm thùng ở cửa hàng thường chỉ bán vài thùng mỗi đơn có thể là lỗi gõ thêm số không, cũng có thể là một khách sỉ thật. Một đơn giá thấp hơn danh mục có thể là gõ nhầm, cũng có thể là đơn khuyến mãi đã được duyệt.
Vì vậy cách làm đúng là gắn cờ chứ không tự sửa. Mỗi quy tắc tạo ra một danh sách dòng cần người có hiểu biết nghiệp vụ xem lại. Người đó quyết định sửa, giữ nguyên kèm ghi chú lý do, hay hỏi lại người nhập. Nếu bạn để công thức hay AI tự sửa giá trị bất thường về mức "hợp lý", bạn đang thay lỗi gõ bằng lỗi bịa.
Có hai nhóm quy tắc. Nhóm thứ nhất là quy tắc cứng, vi phạm thì gần như chắc chắn sai: số lượng âm trong bảng bán hàng, ngày giao trước ngày đặt, thành tiền khác số lượng nhân đơn giá. Nhóm thứ hai là quy tắc mềm, vi phạm thì đáng nghi: số lượng lớn hơn nhiều lần mức thường gặp, đơn giá lệch xa danh mục. Phần dưới đi qua cả hai nhóm.
Các quy tắc cứng hay dùng cho bảng đơn hàng
Giả định bảng đơn hàng có các cột: A mã đơn, B ngày đặt, C ngày giao, D mã hàng, E số lượng, F đơn giá, G thành tiền, H mã khách. Mỗi quy tắc là một công thức trả về TRUE khi dòng có vấn đề.
| Quy tắc | Công thức ở dòng 2 | Lỗi thường gặp đằng sau |
|---|---|---|
| Số lượng không dương | =E2<=0 | Gõ dấu trừ nhầm, đơn trả hàng ghi lẫn vào bảng bán |
| Ngày đặt ở tương lai | =B2>TODAY() | Gõ nhầm năm, đảo ngày với tháng |
| Ngày giao trước ngày đặt | =AND(C2<>"",C2| Gõ nhầm, dán lệch cột | |
| Thành tiền lệch số lượng nhân đơn giá | =ABS(G2-E2*F2)>1 | Sửa tay một ô, công thức bị ghi đè bằng số |
| Mã hàng không có trong danh mục | =COUNTIF(DanhMuc!A:A,D2)=0 | Gõ sai mã, mã cũ đã ngừng dùng |
| Thiếu mã khách | =H2="" | Bỏ sót khi nhập, đơn khách lẻ chưa có quy ước |
Phép so thành tiền dùng độ lệch lớn hơn một thay vì so bằng, để tránh báo lỗi oan vì sai số làm tròn của số thập phân. Các công thức viết với dấu phẩy theo Excel tiếng Anh; máy định dạng vùng Việt Nam có thể cần dấu chấm phẩy. Google Sheets dùng được nguyên các công thức trên.
Quy tắc mềm: so với mức thường gặp của chính bảng đó
Số lượng lớn bất thường theo từng mặt hàng. Mỗi mặt hàng có mức bán khác nhau, nên so với mức chung của cả bảng là vô nghĩa. Một cách đơn giản: thêm cột tính mức trung vị số lượng của cùng mã hàng, rồi gắn cờ những dòng lớn hơn, ví dụ, mười lần mức đó. Excel bản mới và Google Sheets có thể dùng MEDIAN kết hợp FILTER; bản cũ có thể dùng AVERAGEIFS làm mốc thay thế, dù trung bình dễ bị chính giá trị bất thường kéo lệch.
Đơn giá lệch danh mục. Kéo đơn giá niêm yết từ trang danh mục bằng XLOOKUP hoặc VLOOKUP theo mã hàng, rồi gắn cờ khi đơn giá trong đơn thấp hơn hoặc cao hơn một khoảng bạn đặt, ví dụ lệch quá 30%. Ngưỡng tuỳ chính sách chiết khấu của cửa hàng; đây là chỗ cần người làm bán hàng quyết định, không phải AI.
Khách hàng mua bất thường so với chính họ. Khách quen thường mua vài thùng mỗi lần bỗng có đơn rất lớn có thể là lỗi chọn nhầm khách khi nhập. Cách gắn cờ tương tự số lượng, nhưng theo mã khách thay vì mã hàng.
Đặt ngưỡng rồi điều chỉnh. Lần đầu áp quy tắc mềm, danh sách cờ có thể rất dài. Xem qua, nếu phần lớn là đơn bình thường thì nới ngưỡng; nếu cờ quá ít mà bạn biết có lỗi lọt, thắt ngưỡng lại. Sau vài vòng, quy tắc sẽ khớp với thực tế cửa hàng.
Dựng cột cờ kiểm và tô màu, từng bước
Cách làm dưới đây giữ nguyên dữ liệu gốc, chỉ thêm cột kiểm ở bên phải.
- Làm trên bản sao của bảng, hoặc ít nhất đặt tên phiên bản trước khi thêm cột.
- Bên phải bảng, thêm mỗi quy tắc một cột, tiêu đề ghi ngắn nội dung quy tắc, ví dụ "SL không dương", "Ngày tương lai". Nhập công thức ở dòng 2 và kéo xuống hết bảng.
- Thêm một cột "Số cờ" đếm số quy tắc bị vi phạm trên dòng, ví dụ =COUNTIF(I2:N2,TRUE). Dòng có số cờ lớn hơn không là dòng cần xem.
- Chọn toàn bộ vùng dữ liệu, tạo định dạng có điều kiện dùng công thức =$O2>0 với O là cột Số cờ, chọn màu nền nhạt. Dòng có vấn đề sẽ nổi lên khi cuộn.
- Thêm một cột "Xử lý" để người xem ghi quyết định: đã sửa, đúng – giữ nguyên, chờ hỏi lại. Đừng xoá cờ khi dòng được xác nhận là đúng; ghi chú lý do để lần sau không phải xem lại.
- Ở đầu bảng hoặc ở trang Kiểm tra riêng, đếm tổng số dòng có cờ và số dòng chưa xử lý. Con số này cho biết bảng đã sẵn sàng dùng hay chưa.
Với bảng nhập liệu liên tục, giữ các cột cờ như một phần cố định của bảng. Mỗi dòng mới nhập vào sẽ được kiểm ngay, thay vì dồn đến cuối tháng.
Lối tắt: sắp xếp, lọc và MIN, MAX
Không phải lúc nào cũng cần dựng cột cờ. Với một bảng mới nhận, cách nhìn nhanh nhất là tính MIN và MAX cho từng cột số và cột ngày. Nếu cột số lượng có MIN âm, cột ngày có MAX ở năm sau, cột đơn giá có MAX lớn hơn mọi giá trong danh mục, bạn biết ngay phải tìm ở đâu.
Bộ lọc tự động cũng cho thông tin nhanh. Mở danh sách lọc của một cột chữ như tỉnh hay nhóm khách, đọc danh sách giá trị không trùng: những biến thể viết sai, viết tắt, giá trị lạ hiện ra ngay. Với cột số, sắp xếp tăng dần rồi giảm dần, nhìn mười dòng đầu và mười dòng cuối. Nhớ chọn cả bảng khi sắp xếp để các cột không bị lệch với nhau.
Excel bản mới có tính năng phân tích dữ liệu tự động và Copilot có thể gợi ý điểm bất thường; Google Sheets có Gemini với vai trò tương tự. Các gợi ý này hữu ích như một danh sách để xem lại, nhưng tính năng thay đổi theo phiên bản và gói tài khoản, và mọi gợi ý vẫn cần bạn xác nhận bằng chính dữ liệu gốc.
Nhờ AI đề xuất quy tắc kiểm tra
AI hữu ích nhất ở việc gợi ý những quy tắc bạn chưa nghĩ tới, dựa trên mô tả nghiệp vụ. Không cần đưa dữ liệu thật. Một số câu lệnh mẫu:
- "Bảng đơn hàng của cửa hàng vật liệu xây dựng có các cột: mã đơn, ngày đặt, ngày giao, mã hàng, số lượng, đơn vị tính, đơn giá, thành tiền, mã khách, nhân viên bán. Liệt kê các quy tắc kiểm tra giá trị bất thường nên áp dụng, chia thành quy tắc chắc chắn sai và quy tắc đáng nghi, kèm công thức Excel cho dòng 2."
- "Với quy tắc đơn giá lệch danh mục, gợi ý cách đặt ngưỡng khi cửa hàng có ba mức giá: lẻ, sỉ và đại lý. Viết công thức gắn cờ theo mức giá áp dụng cho từng khách."
- "Giải thích vì sao so thành tiền bằng phép bằng có thể báo lỗi oan, và cách viết lại."
- "Đây là danh sách mười giá trị giả định của cột đơn vị tính, viết không thống nhất. Đề xuất danh mục chuẩn và quy tắc gắn cờ cho giá trị ngoài danh mục."
Thử mỗi quy tắc AI đề xuất trên vài dòng giả định có lỗi cài sẵn trước khi áp vào bảng thật. Quy tắc gắn cờ quá nhiều dòng bình thường cũng tệ như quy tắc bỏ sót, vì người xem sẽ quen bỏ qua cờ.
Từ cờ kiểm đến sửa tận gốc
Sau vài tuần dùng cột cờ, hãy nhìn lại xem cờ nào xuất hiện nhiều nhất. Nếu "ngày tương lai" lặp lại, có thể máy nhập liệu đang đặt sai định dạng ngày, hoặc nhân viên quen gõ tháng trước ngày. Nếu "mã hàng không có trong danh mục" lặp lại, có thể danh mục chưa được cập nhật mặt hàng mới, hoặc cần chuyển cột mã sang danh sách thả xuống bằng Data Validation.
Mỗi loại lỗi lặp lại là một gợi ý sửa ở khâu trước: đổi biểu mẫu nhập, thêm danh sách thả xuống, khoá cột công thức để không bị ghi đè, hướng dẫn lại ca làm. Cột cờ kiểm là lưới bắt lỗi; mục tiêu lâu dài là để lưới đó ngày càng ít việc.
Khi bảng đã qua hết các cờ, mỗi dòng có cờ đều đã được xử lý hoặc giải thích, bảng mới thật sự sẵn sàng cho bước tiếp theo như tổng hợp báo cáo hay phân tích xu hướng.
Nếu bảng do nhiều người cùng nhập, chia sẻ định kỳ danh sách các loại cờ hay gặp với cả nhóm, kèm ví dụ giả định cho từng loại. Người nhập biết lỗi của mình thường rơi vào đâu sẽ tự cẩn thận hơn ở đúng chỗ đó, và số cờ mỗi tuần giảm dần mà không cần thêm quy tắc nào.
Câu hỏi thường gặp
Có nên xoá ngay những dòng bị gắn cờ không?
Không. Cờ chỉ báo dòng đáng xem lại. Người hiểu nghiệp vụ nên kiểm từng dòng, sửa nếu sai, giữ nguyên kèm ghi chú nếu đúng. Xoá vội có thể làm mất đơn hàng thật và làm lệch mọi tổng số sau đó.
Ngưỡng "lớn bất thường" nên đặt bao nhiêu?
Không có con số chung. Bắt đầu với một ngưỡng rộng, xem danh sách cờ có hợp lý không, rồi điều chỉnh. Ngưỡng nên đặt theo từng mặt hàng hoặc từng khách thay vì một con số cho cả bảng, vì mức bán của mỗi loại rất khác nhau.
Vì sao công thức so thành tiền báo lỗi dù số trông giống nhau?
Số thập phân trong máy tính có sai số làm tròn rất nhỏ, nên phép so bằng tuyệt đối có thể báo khác. So độ lệch tuyệt đối với một ngưỡng nhỏ, hoặc làm tròn cả hai bên bằng ROUND trước khi so.
Có thể nhờ chatbot đọc bảng và chỉ ra dòng bất thường không?
Có thể thử với bảng giả định nhỏ, nhưng với bảng thật thì không nên. Chatbot dễ bỏ sót dòng trong bảng dài và bạn phải đưa dữ liệu ra ngoài. Nhờ AI viết quy tắc rồi tự chạy công thức sẽ đáng tin hơn.
Cột cờ kiểm có làm bảng rối không?
Có thể nhóm các cột cờ lại ở bên phải và thu gọn bằng chức năng nhóm cột, chỉ để hiện cột Số cờ. Người nhập liệu vẫn thấy dòng được tô màu, còn người kiểm mở rộng nhóm khi cần xem quy tắc nào bị vi phạm.
Quy tắc kiểm tra giá trị bất thường khác Data Validation thế nào?
Data Validation chặn giá trị sai ngay lúc nhập, áp cho từng ô. Cột cờ kiểm xem xét cả dòng và có thể so với dữ liệu khác như danh mục hay lịch sử mua. Hai cách bổ sung cho nhau: chặn được thì chặn, không chặn được thì gắn cờ.