Blair có một bảng tính được chia thành hai lĩnh vực: nhập dữ liệu và xác minh dữ liệu. Vùng xác minh dữ liệu bao gồm các công thức kiểm tra các mục nhập sử dụng câu lệnh IF. Nếu có vấn đề, một tin nhắn văn bản được hiển thị trong một ô trong vùng xác minh, nếu không công thức sẽ trả về một ô trống. Sau đây là công thức xác minh điển hình:

=IF(A1<>5,"Value in A1 is not 5")

Vấn đề là vùng xác minh dữ liệu có thể khá lớn, có nghĩa là rất dễ bỏ sót một trong các tin nhắn văn bản. Blair tự hỏi liệu có cách nào để tạo công thức kiểm tra khu vực xác minh dữ liệu và trả về một thông báo nếu có bất kỳ thông báo nào khác trong khu vực hay không.

Có một số cách khác nhau để tiếp cận vấn đề này. Nếu vùng xác minh dữ liệu liền kề, thì một công thức mảng đơn giản sẽ thực hiện thủ thuật. Nhập thông tin sau vào bất kỳ ô trống nào trên trang tính:

=SUM((LEN(ValRange)>0)*1)

Đảm bảo rằng ValRange được thay thế bằng dải ô trong vùng xác thực dữ liệu. Ngoài ra, hãy đảm bảo bạn nhập công thức bằng cách nhấn Shift + Ctrl + Enter (để biểu thị đó là công thức mảng). Công thức trả về một giá trị cho biết có bao nhiêu ô trong phạm vi có độ dài lớn hơn 0. Nói cách khác, nó đếm số ô có thông báo hiển thị.

Nếu bạn không muốn sử dụng công thức mảng, bạn có thể đạt được kết quả tương tự bằng cách sử dụng công thức thông thường sau:

=SUMPRODUCT((LEN(ValRange)>0)*1)

Một lần nữa, kết quả là số ô có độ dài lớn hơn 0. Một cách tiếp cận khác là sử dụng một số hàm COUNT được cung cấp bởi Excel:

=COUNTA(ValRange) - COUNTBLANK(ValRange)

Công thức này đếm số lượng ô trong phạm vi, sau đó trừ số ô trống trong phạm vi. Kết quả là số ô không trống hoặc những ô đang hiển thị thông báo. Một cách tiếp cận công thức khác có thể được sử dụng để xác định phản hồi có / không đơn giản:

=IF(COUNTIF(ValRange,"?*"),"","No ") & "Verification Messages"

Nếu không có tin nhắn nào trong ValRange, công thức trả về “Không có tin nhắn xác minh”. Nếu có tin nhắn, nó sẽ loại bỏ “Không” và chỉ trả lại “Tin nhắn xác minh”.

Bạn cũng nên áp dụng định dạng có điều kiện cho vùng xác minh dữ liệu của mình. Mặc dù các công thức được thảo luận cho đến nay sẽ cho bạn biết nếu có thông báo, nó sẽ không làm nổi bật các thông báo đó ở đâu — định dạng có điều kiện có thể xác định từng thông báo. Chọn tất cả các ô trong vùng chứa công thức, sau đó sử dụng định dạng có điều kiện để kiểm tra độ dài của các ô đó. Nếu độ dài lớn hơn 0, ô có thể được định dạng để hiển thị nền màu đỏ. Điều này sẽ làm cho bất kỳ thông báo nào trong vùng xác minh dữ liệu khó bị bỏ sót hơn nhiều khi cuộn qua trang tính.

Nếu bạn muốn thiết kế lại hoàn toàn trang tính của mình, một cách tiếp cận mạnh mẽ hơn sẽ là loại bỏ khu vực xác minh dữ liệu.

Bạn có thể đạt được kết quả tương tự (kiểm tra những gì có trong vùng nhập dữ liệu) bằng cách sử dụng xác thực dữ liệu cho từng ô nhập. Thiết lập đúng cách, xác thực dữ liệu sẽ đảm bảo rằng người dùng đã nhập các giá trị có thể chấp nhận vào mỗi ô, loại bỏ nhu cầu về nhiều vùng xác thực dữ liệu.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (2772) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003.