Tại sao Excel 2010 lại thay đổi tham chiếu ô trong công thức định dạng có điều kiện của tôi?

Đây là sự cố không thường xuyên và tôi dường như không tìm ra quy luật nào, nhưng nó xảy ra khoảng 25-50% thời gian. Đây là một ví dụ thực tế sử dụng tệp .xlsx với Excel 2010 (14.0.6117.5003) SP1 MSO (14.0.6123.5001).
Tôi đã chọn phạm vi G6:K18, mở trình quản lý định dạng có điều kiện và tạo một định dạng có điều kiện mới. Không có định dạng nào có sẵn cho phạm vi này. Tôi đã nhập công thức như sau: >=not(isnumber(g6)) >vào trường công thức >Tôi đã nhấp vào nút "Định dạng", chọn "Tối hơn 50%" cho màu chữ và nhấp vào "OK". Công thức vẫn hiển thị như tôi đã nhập. Tôi đã nhấp vào "OK" để vào trình quản lý công thức và nhấp vào "OK" một lần nữa để áp dụng định dạng, nhưng định dạng không chính xác (các số có màu xám). >Tôi đã kiểm tra lại công thức và nó đã được thay đổi thành: >=NOT(ISNUMBER(C1048570)) >
Tại sao điều này lại xảy ra, và làm thế nào để khắc phục? Đây không phải là vấn đề quá nghiêm trọng, vì nếu tôi đổi lại tham chiếu ô thành g6, nó thường sẽ giữ nguyên như vậy. Tuy nhiên, nó RẤT khó chịu.
Cảm ơn trước, >-Jeremy


Trả lời:

Tôi nghi ngờ rằng K18 là ô đang hoạt động trong vùng chọn khi bạn tạo quy tắc. Khi bạn chỉnh sửa quy tắc, công thức sẽ được hiển thị cho ô trên cùng bên trái của phạm vi áp dụng (G6).

Nếu K18 là ô đang hoạt động, bạn nên sử dụng =NOT(ISNUMBER(K18)). Hoặc, hãy đảm bảo rằng G6 là ô đang hoạt động trong vùng chọn khi bạn tạo quy tắc.

Trả lời:

>... Tôi đã kiểm tra công thức và thấy nó đã được thay đổi thành:
=NOT(ISNUMBER(C1048570)) >
Tại sao điều này lại xảy ra, và làm thế nào để tôi khắc phục nó?

Lỗi này xảy ra do cách bạn chọn vùng ban đầu. Có vẻ như bạn đang chọn vùng G6:K18 bằng cách bắt đầu từ K18 và kéo lên trên và sang trái. Mặc dù thao tác này chọn được vùng G6:K18, nhưng vùng được chọn lại có K18 là ô đang hoạt động.



Vậy công thức của bạn đáng lẽ phải là,

=NOT(ISNUMBER(K18))

Khi bạn sử dụng công thức của mình và sau đó quay lại công thức đó với phạm vi G6:K18 được chọn nhưng G6 là ô đang hoạt động , chức năng CF của Excel đã sử dụng tham chiếu tương đối của G6 để bắt đầu từ G6 và di chuyển sang phải 4 cột đến cột C và lên 12 hàng đến hàng 1048570. Thực tế là hàng 1049570 vì Excel sẽ xuống dòng ở cuối và tiếp tục dịch chuyển khi bạn đã đến hàng 1.

Có thể sẽ hữu ích nếu bạn suy nghĩ về công thức theo thuật ngữ R1C1. Với K18 hoạt động, việc sử dụng công thức của bạn sẽ trở thành

=NOT(ISNUMBER(R[-12]C[-4]))

Điều đó khá khó hiểu. Giải pháp là thực hiện lựa chọn phạm vi ban đầu từ trên cùng bên trái xuống dưới cùng bên phải, chứ không phải ngược lại. Nói cách khác, hãy luôn để ô trên cùng bên trái là ô hoạt động nếu bạn muốn có kết quả dự đoán được.