Học Excel -
Thủ Thuật Excel
Thiết lập các công thức nâng cao (phần 2)
Hướng dẫn thiết lập các công thức nâng cao (phần 2) :
4.4. Áp dụng các quy tắc Data-Validation cho các ô
Có một điều không may khi làm việc với các bảng tính là các công thức của
bạn chỉ hoạt động theo những dữ liệu mà chúng nhận được. Đó là hiệu ứng
GIGO, như các nhà lập trình thường nói,garbage in, garbage out (nếu ta nạp
dữ liệu vào máy sai thì ta cũng thu được kết quả sai). Đối với những lỗi dữ
liệu cơ bản (ví dụ như nhập sai ngày tháng, nhập sai số), bạn không thể làm
gì khác ngoài việc tự cổ vũ chính mình và những người sử dụng các bảng tính
của bạn là phải nhập dữ liệu cách cẩn thận! Nhưng cũng còn chút may mắn là
bạn có một số khả năng kiểm soát để ngăn chận việc nhập dữ liệu không
đúng. Không đúng ở đây có nghĩa là dữ liệu rơi vào một trong hai loại sau:
· Dữ liệu có kiểu nhập không đúng — ví dụ, nhập một chuỗi dạng văn bản
vào trong một ô đòi hỏi phải nhập vào một con số.
· Dữ liệu nằm ngoài một giới hạn cho phép — ví dụ, nhập 200 vào một ô mà
ô này chỉ cho phép nhập trong khoảng từ 1 đến 100.
Bạn có ngăn chận những loại dữ liệu nhập không đúng này trong một phạm
vi nhất định bằng cách thêm các chú thích nhằm cung cấp chi tiết về những gì
có thể được phép nhập vào trong một ô cụ thể. Tuy nhiên điều này đòi hỏi
những người khác phải vừa đọc vừa làm theo những chú thích của bạn.
Một cách khác là sử dụng một số kiểu định dạng số để “định dạng” một ô với
một thông báo lỗi sẽ xuất hiện nếu như dữ liệu nhập vào không đúng. Điều
này thì hữu dụng, nhưng chỉ có tác dụng với một số lỗi nhập liệu nhất định
nào đó thôi.
Giải pháp tốt nhất để ngăn ngừa các lỗi nhập liệu là sử dụng tính năng data-
validation của Excel. Với data-validation, bạn có thể tạo ra những quy tắc
nhằm xác định chính xác loại dữ liệu nào có thể được nhập và những giới hạn
cho loại dữ liệu đó. Bạn cũng có thể đưa ra những thông báo sẽ xuất hiện khi
có một ô được chọn, hay khi nhập liệu không đúng (với những quy tắc bạn đã
đưa ra).
Để thiết lập các quy tắc data-validation, bạn theo các bước sau:
1. Chọn ô hay dãy ô mà bạn muốn áp dụng quy tắc data-validation.
2. Chọn Data, Data Validation. Excel mở ra hộp thoại Data Validation như
minh họa ở hình 4.14.
Figure 4.14 (Hình 4.14)
3. Trong tab Settings, sử dụng một trong các quy tắc dưới đây của danh
sách Allow:
o Chấp nhận bất kỳ loại giá trị nào nhập vào trong dãy. (Điều này cũng có
nghĩa là gỡ bỏ quy tắc đã được thiết lập trước đây. Khi bạn gỡ bỏ một quy tắc
hiện có, bạn hãy nhớ xóa luôn cả thông báo lỗi, nếu bạn đã tạo ra nó như cách
làm ở bước 7 dưới đây.)
o Chỉ cho phép nhập số nguyên vào dãy. Sử dụng thêm danh sách Data để
chọn một loại toán tử so sánh (nằm trong khoảng, bằng, tối thiểu, v.v…), và
nhập một tiêu chuẩn cụ thể. (Ví dụ, nếu bạn chọn Between (nằm trong
khoảng), bạn phải nhập một giá trị Minimum và một giá trị Maximum).
o Chỉ cho phép nhập giá trị là số (số thập phân hoặc số nguyên). Sử dụng
thêm danh sách Datađể chọn một loại toán tử so sánh, và nhập một tiêu
chuẩn cụ thể.
o Chỉ cho phép nhập các giá trị được xác định trong danh sách kèm theo.
Dùng hộp Source để xác định một dãy trong cùng một sheet hoặc một tên
dãy trong bất kỳ sheet nào có chứa danh sách các giá trị được phép nhập.
(Đặt một dấu bằng trước dãy hoặc tên dãy). Hoặc, bạn có thể nhập trực tiếp
các giá trị này vào trong hộp Source (cách nhau bằng dấu phẩy). Nếu bạn
muốn người dùng có thể chọn những giá trị này từ một danh sách xổ xuống
(drop-down list), bạn kích hoạt tùy chọn In-Cell Drop-Down.
o Chỉ cho phép nhập các giá trị ngày tháng. (Nếu người sử dụng nhập vào các
giá trị thời gian, thì các giá trị này sẽ không hợp lệ). Sử dụng thêm danh
sách Data để chọn một loại toán tử so sánh, và nhập một tiêu chuẩn ngày
tháng cụ thể (chẳng hạn như ngày bắt đầu và ngày kết thúc).
o Chỉ cho phép nhập các giá trị thời gian. (Nếu người sử dụng nhập vào các
giá trị ngày tháng, thì các giá trị này sẽ không hợp lệ). Sử dụng thêm danh
sách Data để chọn một loại toán tử so sánh, và nhập một tiêu chuẩn thời gian
cụ thể (chẳng hạn như thời gian bắt đầu và thời gian kết thúc).
o Chỉ cho phép nhập các chuỗi text với chiều dài được chỉ định. Sử dụng
thêm danh sách Datađể chọn một loại toán tử so sánh, và nhập một tiêu
chuẩn độ dài chuỗi cụ thể (chẳng hạn như độ dài tối thiểu và tối đa của chuỗi
text).
o Dùng tùy chọn này để nhập một công thức xác định một điều kiện nào đó.
Bạn có thể nhập trực tiếp công thức vào hộp Formula (phải chắc chắn rằng
có dấu bằng ở trước công thức), hoặc nhập một tham chiếu dẫn tới một ô
chứa công thức. Ví dụ, nếu bạn giới hạn ô A2 và bạn muốn chắc chắn rằng
giá trị nhập vào sẽ luôn khác giá trị trong ô A1, bạn gõ công thức: =
A2<>A1.
4. Để bao gồm cả những giá trị rỗng, trong chính ô đó hoặc trong những ô là
một phần của những thiết lập validation, bạn kích hoạt tùy chọn Ignore
Blank. Nếu bạn không kích hoạt tùy chọn này, Excel sẽ xem các ô rỗng có
giá trị là zero và áp dụng những quy tắc validation phù hợp với chúng.
5. Nếu dãy đã có một quy tắc validation đang áp dụng cho những ô khác, bạn
có thể áp dụng luôn quy tắc mới vào các ô khác đó bằng cách kích hoạt tùy
chọn Apply These Changes to All Other Cells with the Same Settings.
6. Chọn tab Input Message.
7. Nếu bạn muốn có một thông báo xuất hiện khi người dùng chọn ô được
giới hạn hay bất kỳ ô nào trong dãy được giới hạn, bạn kích hoạt tùy
chọn Show Input Message When Cell Is Selected. Xác định nội dung của