Nguyễn Ngọc Duy Ái
Guest
- 4/11/16
- 1
- 1
- 3
- 40
Nguồn: Bkav Forum - http://forum.bkav.com.vn/forum/bkav...rong-excel-de-kiem-soat-nhap-lieu-theo-y-muon
Chào các bạn,
Khi thiết kế một biểu mẫu trong Excel để phân phối cho người khác nhập dữ liệu, có nhiều khi ta chỉ muốn cho người nhập dữ liệu vào (các) ô / hàng / cột theo các định dạng hoặc kiểu dữ liệu nhất định (VD như chỉ có thể nhập hoặc văn bản, hoặc số, hoặc số thập phân, ngày tháng, hoặc danh sách chọn sẵn...) cho đỡ sai sót. Chúng ta có thể làm được điều đó thông qua chức năng Data Validation có sẵn trong Excel từ 2003 trở lên.
Giả sử ta có bảng danh sách cần nhập liệu sau đây:
Trong đó:
- Cột Mã số chỉ được nhập 4 ký tự bất kỳ, nhiều hơn hay ít hơn đều không được.
- Cột Họ tên được nhập tự do.
- Cột Nữ chỉ chấp nhận hoặc ký tự "x" hoặc để trống.
- Cột Ngày sinh chỉ chấp nhận định dạng ngày tháng năm trong khoảng từ 01/01/1970 đến 31/12/1990
- Cột Ưu tiên sẽ được chọn trong danh sách gồm "Mức 1", "Mức 2", "Mức 3".
- Cột Điểm ưu tiên chỉ được chấp nhận các số tự nhiên từ 1 đến 5
- Cột Điểm thi chỉ chấp nhận số (cả bao gồm số thập phân) từ 0 đến 10.
- Cột Tổng cộng được lập công thức SUM(Điểm ƯT + Điểm thi)
Bây giờ ta tiến hành:
A. Đối với cột Mã số:
1. Quét khối chọn các ô cần áp dụng. Ở tab Data chọn Data Validation => Data Validation....
2. Ở cửa sổ Data Validation, tab Settings, mục Allow (cho phép) chọn kiểu Text lenght (độ dài chuỗi); mục Data (kiểu dữ liệu) chọnequal to (bằng); mục Lenght (độ dài) ta nhập vào 4.
Ở mục Allow, tuỳ yêu cầu ta có thể chọn between (nằm giữa khoảng), not between (không nằm giữa <=> khác khoảng), not equal to (khác số), greater than (lớn hơn), less than (ít hơn), greater than or equal to (lớn hơn hoặc bằng), less than or equal to (bé hơn hoặc bằng)...
3. Sang tab Input Message sẽ cho ta tạo tooltip để hướng dẫn người nhập khi con trỏ nằm trong ô có điều kiện. Ở mục Title nhập tiêu đề, mục Input Message nhập hướng dẫn như ví dụ dưới đây.
Sau khi chọn ô, ta được kết quả kiểu này:
4. Tab Error Alert sẽ tuỳ chỉnh hộp thoại thông báo khi nhập sai. Mục Style ta chọn kiểu thông báo. Ở đây tôi chọn kiểu Stop để yêu cầu người dùng phải nhập lại dữ liệu. Nhập tiêu đề vào ô Title và nội dung lỗi vào khung Error message như hình minh hoạ:
5. Nhấn OK để hoàn tất. Kể từ giờ, các dữ liệu nhập vào ô Mã số nếu ko đủ 4 ký tự sẽ thông báo lỗi như ta đã thiết lập:
B. Đối với các cột khác: Các bước tương tự như cột Mã số, chỉ khác ở điều kiện cho phép. Cụ thể như sau:
B.1. Cột Nữ: Chỉ cho phép nhập hoặc "x" hoặc để trống: Ta chọn mục Allow là List (danh sách) và mục Source ta nhập thẳng là "x" (không có dấu ngoặc kép). Lưu ý bỏ tích ở In cell dropdown để không hiển thị mũi tên chọn ở trong ô, và chọnIgnore blank để bỏ (không kiểm tra) các ô trống ko có dữ liệu.
(Từ các bước này, thẻ Input Message và Error Alert các bạn tuỳ chỉnh nội dung theo ý mình. Lưu ý ở thẻ Alert, mục Style vẫn chọn làStop nhé).
B.2. Cột Ngày sinh, các bạn chọn như hình:
B.3. Cột Ưu tiên, trước tiên các bạn gõ danh sách cần cho người dùng chọn ở 1 nơi nào đó, ví dụ phía dưới bảng, hoặc có thể sang Sheet khác cho gọn. Để minh hoạ, mình làm như hình. Các bạn có thể chọn In cell dropdown để người dùng lựa chọn cho dễ.
B.4. Cột Điểm Ưu tiên (cái này có thể dùng hàm, nhưng mình đang giới thiệu nên mình dùng Data Validation luôn). Lưu ý: Whole number là số nguyên.
B.5. Cuối cùng là cột Điểm thi: Chấp nhận số thập phân nên ta chọn là Decimal.
Nắm cơ bản về Data Validation tôi vừa giới thiệu, các bạn sẽ sáng tạo ra thêm nhiều điều kiện trong khi nhập liệu. Như vậy, dùng Data Validation sẽ giúp việc kiểm soát nhập liệu của chính bản thân mình hoặc của cộng sự trong Excel một cách dễ dàng và chính xác, giúp hạn chế tối đa sai sót trong khâu nhập liệu.
Chúc các bạn thành công.
N.Ngọc Duy Ái,
Bkav Forum.
Chào các bạn,
Khi thiết kế một biểu mẫu trong Excel để phân phối cho người khác nhập dữ liệu, có nhiều khi ta chỉ muốn cho người nhập dữ liệu vào (các) ô / hàng / cột theo các định dạng hoặc kiểu dữ liệu nhất định (VD như chỉ có thể nhập hoặc văn bản, hoặc số, hoặc số thập phân, ngày tháng, hoặc danh sách chọn sẵn...) cho đỡ sai sót. Chúng ta có thể làm được điều đó thông qua chức năng Data Validation có sẵn trong Excel từ 2003 trở lên.
Giả sử ta có bảng danh sách cần nhập liệu sau đây:
Trong đó:
- Cột Mã số chỉ được nhập 4 ký tự bất kỳ, nhiều hơn hay ít hơn đều không được.
- Cột Họ tên được nhập tự do.
- Cột Nữ chỉ chấp nhận hoặc ký tự "x" hoặc để trống.
- Cột Ngày sinh chỉ chấp nhận định dạng ngày tháng năm trong khoảng từ 01/01/1970 đến 31/12/1990
- Cột Ưu tiên sẽ được chọn trong danh sách gồm "Mức 1", "Mức 2", "Mức 3".
- Cột Điểm ưu tiên chỉ được chấp nhận các số tự nhiên từ 1 đến 5
- Cột Điểm thi chỉ chấp nhận số (cả bao gồm số thập phân) từ 0 đến 10.
- Cột Tổng cộng được lập công thức SUM(Điểm ƯT + Điểm thi)
Bây giờ ta tiến hành:
A. Đối với cột Mã số:
1. Quét khối chọn các ô cần áp dụng. Ở tab Data chọn Data Validation => Data Validation....
2. Ở cửa sổ Data Validation, tab Settings, mục Allow (cho phép) chọn kiểu Text lenght (độ dài chuỗi); mục Data (kiểu dữ liệu) chọnequal to (bằng); mục Lenght (độ dài) ta nhập vào 4.
Ở mục Allow, tuỳ yêu cầu ta có thể chọn between (nằm giữa khoảng), not between (không nằm giữa <=> khác khoảng), not equal to (khác số), greater than (lớn hơn), less than (ít hơn), greater than or equal to (lớn hơn hoặc bằng), less than or equal to (bé hơn hoặc bằng)...
3. Sang tab Input Message sẽ cho ta tạo tooltip để hướng dẫn người nhập khi con trỏ nằm trong ô có điều kiện. Ở mục Title nhập tiêu đề, mục Input Message nhập hướng dẫn như ví dụ dưới đây.
Sau khi chọn ô, ta được kết quả kiểu này:
4. Tab Error Alert sẽ tuỳ chỉnh hộp thoại thông báo khi nhập sai. Mục Style ta chọn kiểu thông báo. Ở đây tôi chọn kiểu Stop để yêu cầu người dùng phải nhập lại dữ liệu. Nhập tiêu đề vào ô Title và nội dung lỗi vào khung Error message như hình minh hoạ:
5. Nhấn OK để hoàn tất. Kể từ giờ, các dữ liệu nhập vào ô Mã số nếu ko đủ 4 ký tự sẽ thông báo lỗi như ta đã thiết lập:
B. Đối với các cột khác: Các bước tương tự như cột Mã số, chỉ khác ở điều kiện cho phép. Cụ thể như sau:
B.1. Cột Nữ: Chỉ cho phép nhập hoặc "x" hoặc để trống: Ta chọn mục Allow là List (danh sách) và mục Source ta nhập thẳng là "x" (không có dấu ngoặc kép). Lưu ý bỏ tích ở In cell dropdown để không hiển thị mũi tên chọn ở trong ô, và chọnIgnore blank để bỏ (không kiểm tra) các ô trống ko có dữ liệu.
(Từ các bước này, thẻ Input Message và Error Alert các bạn tuỳ chỉnh nội dung theo ý mình. Lưu ý ở thẻ Alert, mục Style vẫn chọn làStop nhé).
B.2. Cột Ngày sinh, các bạn chọn như hình:
B.3. Cột Ưu tiên, trước tiên các bạn gõ danh sách cần cho người dùng chọn ở 1 nơi nào đó, ví dụ phía dưới bảng, hoặc có thể sang Sheet khác cho gọn. Để minh hoạ, mình làm như hình. Các bạn có thể chọn In cell dropdown để người dùng lựa chọn cho dễ.
B.4. Cột Điểm Ưu tiên (cái này có thể dùng hàm, nhưng mình đang giới thiệu nên mình dùng Data Validation luôn). Lưu ý: Whole number là số nguyên.
B.5. Cuối cùng là cột Điểm thi: Chấp nhận số thập phân nên ta chọn là Decimal.
Nắm cơ bản về Data Validation tôi vừa giới thiệu, các bạn sẽ sáng tạo ra thêm nhiều điều kiện trong khi nhập liệu. Như vậy, dùng Data Validation sẽ giúp việc kiểm soát nhập liệu của chính bản thân mình hoặc của cộng sự trong Excel một cách dễ dàng và chính xác, giúp hạn chế tối đa sai sót trong khâu nhập liệu.
Chúc các bạn thành công.
N.Ngọc Duy Ái,
Bkav Forum.
