Tôi đã thay thế các bảng tính kiểm tra lỗi của mình bằng xác thực dữ liệu trong Excel và phát hiện lỗi trước khi chúng xảy ra

Trước đây, tôi thường dành thời gian mỗi tuần để dọn dẹp các bảng tính của mình bằng cách chạy các công thức trợ giúp, quét các ô màu đỏ từ định dạng có điều kiện và sửa các lỗi đánh máy đã lọt vào trong vài ngày vừa qua. Việc dọn dẹp chưa bao giờ trở nên ngắn hơn, vì vậy tôi đã nhìn kỹ hơn vào hộp thoại Xác thực Dữ liệu của Excel. Thay vì phát hiện lỗi sau khi chúng đã làm hỏng các bảng tổng hợp của tôi, giờ đây tôi chặn chúng ngay khi ai đó cố gắng nhập một giá trị không hợp lệ.

Xác thực dữ liệu ngăn chặn việc nhập sai thay vì tìm ra sau này

Toàn bộ thiết lập mất khoảng 30 giây cho mỗi quy tắc

Cấu hình cũ của tôi không hẳn là bị hỏng. Tôi có một cột các công thức IF và COUNTIF chạy âm thầm bên cạnh dữ liệu của mình, cộng với các quy tắc định dạng có điều kiện mà sẽ biến các ô thành màu đỏ khi có điều gì đó không ổn. Mỗi thứ Sáu, tôi sẽ cuộn qua và sửa những gì sáng lên. Vấn đề là phương pháp này mang tính phản ứng — đến khi tôi phát hiện ra một lỗi chính tả, nó đã phá hỏng một công thức SUMIFS hoặc chia nhỏ một danh mục trong bảng tổng hợp của tôi.

Xác thực dữ liệu đảo ngược thứ tự đó. Nó nằm dưới tab Dữ liệu, và ý tưởng cơ bản rất đơn giản: bạn nói với Excel chính xác những gì được phép trong một ô hoặc phạm vi, và Excel từ chối bất cứ thứ gì khác. Để thiết lập một quy tắc:

1. Chọn phạm vi bạn muốn bảo vệ.
2. Đi tới Dữ liệu > Xác thực dữ liệu.
3. Chọn một loại quy tắc từ menu thả xuống Cho phép, ví dụ: số nguyên, số thập phân, ngày tháng, giờ, độ dài văn bản, danh sách hoặc tùy chỉnh.
4. Đặt các tham số (một giá trị tối thiểu và tối đa, một danh sách nguồn, hoặc một công thức).
5. Nhấp OK.

Trong bảng doanh số của tôi, tôi đã sử dụng một quy tắc số nguyên cho cột Số Lượng Bán để áp dụng phạm vi từ 1 đến 100. Các giá trị thực trong cột đó dao động từ 7 đến 44, vì vậy 100 để lại khoảng trống mà không cho phép một giá trị phi lý như 1.500 do nhập sai. Chỉ riêng quy tắc đó cũng phát hiện loại lỗi mà nếu không tôi sẽ cần một công thức hỗ trợ và đánh dấu đỏ để tìm ra.

Các danh sách thả xuống đã loại bỏ nguồn gốc gây lỗi chính của tôi

Một danh sách nguồn, không có lỗi chính tả nào trên toàn bộ bảng

Đây là phần Xác thực Dữ liệu mà tôi dựa vào nhiều nhất, và đó là phần mà tôi ước gì mình đã tích hợp vào quy trình làm việc sớm hơn. Bảng tính doanh số của tôi có một cột Danh mục Sản phẩm, và trước khi tôi thêm quy tắc danh sách, cùng một cột đó chứa "Electronics," "ELECTRONICS," và "electronics" như ba giá trị riêng biệt. "Home & Garden" và "Home and Garden" cũng đang chia cùng một danh mục thành hai.

Khi tôi tạo một bảng tổng hợp từ dữ liệu đó, tôi nhận được các hàng trùng lặp cho những gì lẽ ra chỉ là một dòng sản phẩm. Đây là cách tôi đã sửa nó:

1. Thiết lập một phạm vi nhỏ ở nơi khác trên bảng tính với bốn giá trị được phép: Điện tử, Quần áo, Nhà & Vườn, và Thể thao.
2. Chọn cột Danh mục Sản phẩm.
3. Đi đến Dữ liệu > Xác thực Dữ liệu > Cho phép > Danh sách.
4. Đặt Nguồn thành phạm vi đó (hoặc chuyển nó thành một bảng Excel và tham chiếu nó như một phạm vi được đặt tên để danh sách thả xuống tự mở rộng khi tôi thêm một danh mục mới).
5. Nhấp OK.

Bây giờ mỗi ô trong cột đó đều hiển thị một mũi tên nhỏ, và các mục nhập chỉ giới hạn trong bốn lựa chọn đó. Bảng tổng hợp đã tự làm sạch lần tiếp theo khi tôi làm mới nó.

Công thức tùy chỉnh thực thi các quy tắc mà các tùy chọn tích hợp không thể

Một công thức duy nhất có thể ngăn trùng lặp, thực thi định dạng hoặc phát hiện lỗi logic

Tùy chọn Tùy chỉnh trong Xác thực Dữ liệu là nơi tính năng trở nên thực sự hữu ích cho các quy tắc phức tạp hơn. Bạn viết một công thức trả về TRUE cho các mục hợp lệ và FALSE cho tất cả những thứ khác, và Excel sẽ làm phần còn lại.

Ba quy tắc mà tôi thấy mình thường sử dụng:

• Chặn các mục trùng lặp. Đối với cột ID đơn hàng, =COUNTIF($E$2:$E$1000,E2)=1 ngăn không cho ai nhập cùng một ID hai lần. Điều đó sạch sẽ và gọn gàng hơn nhiều so với việc kiểm tra trùng lặp sau đó.
• Thực thi một định dạng. Nếu mã sản phẩm trong bảng của bạn luôn phải bắt đầu bằng "PRD-," =LEFT(A2,4)="PRD-" sẽ từ chối bất cứ gì không đúng. Hữu ích để giữ mã thống nhất giữa các cộng tác viên.
• Bắt lỗi logic. Nếu cột B chứa ngày kết thúc và cột A chứa ngày bắt đầu, =B2>=A2 sẽ chặn các mục nhập mà ai đó nhập ngày kết thúc dự án sớm hơn ngày bắt đầu.

Điều cân nhắc này đáng để lưu ý. Các công thức tùy chỉnh chỉ trả về kết quả đạt hay không đạt — Excel sẽ không cho người dùng biết lý do tại sao mục nhập của họ bị từ chối, và đây chính là phần mà phần tiếp theo sẽ đề cập.

Các thông điệp nhập liệu và cảnh báo lỗi làm cho các quy tắc trở nên rõ ràng

Một lời nhắc rõ ràng luôn tốt hơn một thất bại im lặng trong mọi trường hợp

Hộp thoại Xác thực Dữ liệu có hai tab quan trọng khác: Thông báo Nhập liệu và Cảnh báo Lỗi. Chúng là những gì biến một quy tắc im lặng thành thứ mà cộng tác viên thực sự có thể sử dụng.

Tab Tin Nhắn Nhập thêm một mẹo nhỏ xuất hiện khi ai đó chọn ô. Tôi dùng nó để giải thích những gì được mong đợi trước khi ai đó bắt đầu gõ — một cái gì đó như "Nhập một giá trị từ 1 đến 100" hoặc "Chọn một danh mục từ menu thả xuống." Nó không chặn gì cả; nó chỉ trả lời câu hỏi mà ai đó vốn phải hỏi tôi.

Tab Cảnh Báo Lỗi thú vị hơn vì nó cung cấp cho bạn ba kiểu để lựa chọn:

• Stop hoàn toàn từ chối nhập liệu. Tốt nhất cho các quy tắc nghiêm ngặt, như ID duy nhất hoặc nhãn danh mục.
• Cảnh báo yêu cầu người dùng xác nhận trước khi chấp nhận giá trị. Tốt cho các quy tắc mềm, ví dụ, một khoản chiết khấu trên 30% được phép nhưng không phổ biến.
• Thông tin chỉ thông báo cho người dùng và vẫn chấp nhận mục nhập.

Điều khác đáng làm ở đây là viết văn bản cảnh báo hữu ích. Thông báo mặc định của Excel là "Giá trị này không khớp với các hạn chế xác thực dữ liệu được xác định cho ô này," điều này chẳng nói cho ai biết gì cả. Tuy nhiên, "Vui lòng nhập ngày sau ngày bắt đầu dự án" lại rõ ràng hơn và thực sự giúp ích.

Nơi tôi sẽ đưa các quy tắc xác thực của mình tiếp theo

Đặt lớp xác thực lên trên các mảng động là bước tiếp theo

Xác thực dữ liệu không phải là sự thay thế hoàn toàn cho định dạng có điều kiện — vẫn còn chỗ cho việc làm nổi bật các giá trị ngoại lai nằm trong các quy tắc nhưng trông không bình thường. Điều nó thay đổi là thứ tự các thao tác. Điều tiếp theo tôi muốn thử là kết hợp các quy tắc xác thực với công thức mảng động, để danh sách nguồn cho các danh sách thả xuống tự mở rộng khi tôi thêm các danh mục hoặc nhân viên bán hàng mới.