Trev có một bảng dự báo doanh số bán hàng theo sản phẩm mà một số người dùng xem xét và cập nhật. Dự báo ban đầu được đặt bằng các công thức khác nhau, nhưng người dùng được phép ghi đè các công thức bằng cách nhập giá trị vào bất kỳ ô nào có chứa một trong các công thức. Nếu người dùng làm điều này, sẽ rất hữu ích cho Trev để Excel làm nổi bật ô đó bằng cách nào đó.

Có một số cách tiếp cận bạn có thể thực hiện. Đầu tiên, bạn có thể sử dụng định dạng có điều kiện để đánh dấu. Đặt loại quy tắc định dạng có điều kiện thành Chỉ định dạng ô chứa, “Giá trị ô”

“Không bằng To”, rồi nhập công thức làm phép so sánh. Điều này sẽ cho bạn biết khi nào giá trị trong ô không bằng bất kể công thức là gì, nhưng “gottcha” tiềm năng là nếu người đó ghi đè công thức bằng kết quả của công thức đó. Ví dụ: nếu công thức sẽ tạo ra kết quả là “27” và người dùng nhập “27” vào ô. Một khả năng khác là xác định một công thức trong một hằng số được đặt tên và sau đó sử dụng hằng số được đặt tên đó ở định dạng có điều kiện. Làm theo các bước sau:

  1. Hiển thị tab Công thức của ruy-băng.

  2. Nhấp vào Xác định tên trong nhóm Tên đã xác định. Excel sẽ hiển thị hộp thoại Tên mới. (Xem Hình 1.)

  3. Trong hộp Tên, hãy nhập tên bạn muốn gán cho công thức này. Đối với ví dụ này, hãy sử dụng CellHasNoFormula.

  4. Chọn bất kỳ thứ gì có trong hộp Tham chiếu đến, ở cuối hộp thoại và nhấn Del. Thao tác này sẽ loại bỏ bất kỳ thứ gì Excel có ở đó trước đó.

  5. Nhập công thức sau vào hộp Tham khảo:

  6. Bấm OK.

Bây giờ bạn có thể thiết lập một số định dạng có điều kiện và sử dụng công thức được đặt tên này trong định dạng. Chỉ cần đặt loại quy tắc định dạng có điều kiện thành Sử dụng công thức để xác định ô cần định dạng và nhập công thức sau vào điều kiện:

=CellHasNoFormula

Công thức trả về True hoặc False, tùy thuộc vào việc có công thức trong ô hay không. Nếu không có công thức, thì True được trả về và bất kỳ định dạng nào bạn chỉ định sẽ được áp dụng cho ô. Một cách tiếp cận khác là sử dụng một hàm do người dùng xác định để trả về True hoặc False, sau đó thiết lập định dạng có điều kiện. Bạn có thể sử dụng một macro rất đơn giản, chẳng hạn như sau:

Function IsFormula(Check_Cell As Range) As Boolean     Application.Volatile     IsFormula = Check_Cell.HasFormula End Function

Sau đó, bạn có thể chỉ định loại quy tắc định dạng có điều kiện là Sử dụng công thức để xác định ô nào cần định dạng và nhập công thức sau vào điều kiện, ví dụ: nếu bạn đang định dạng có điều kiện ô C1:

=NOT(IsFormula(C1))

Công thức trả về True nếu không có công thức trong ô, do đó, định dạng có điều kiện được áp dụng. Nhược điểm duy nhất của việc sử dụng bất kỳ công thức nào trong số này để xác định xem một công thức có trong ô hay không là nó không thể xác định xem công thức trong ô đã được thay thế bằng một công thức khác hay chưa. Điều này áp dụng cho cả cách tiếp cận vĩ mô và cách tiếp cận công thức đã xác định. Một cách tiếp cận hoàn toàn khác là xem xét lại bảng tính của bạn một chút. Bạn có thể tách các ô để người dùng nhập từ các ô sử dụng công thức. Công thức có thể sử dụng hàm IF để xem liệu người dùng có nhập nội dung nào đó vào ô người dùng nhập hay không. Nếu không, công thức của bạn sẽ được sử dụng để xác định một giá trị; nếu vậy, thì đầu vào của người dùng được sử dụng tùy theo công thức của bạn. Cách tiếp cận này cho phép bạn giữ các công thức bạn cần mà không bị người dùng ghi đè. Điều này dẫn đến tính toàn vẹn tuyệt vời của các công thức và kết quả trang tính.

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

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

Mẹo này (9270) áp dụng cho Microsoft Excel 2007, 2010 và 2013. Bạn có thể tìm phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: