Loại bỏ dấu vết phụ thuộc Excel

Bạn đã bao giờ mở một tệp trong Excel và thấy lỗi #REF đáng sợ chưa?. nơi một kết quả công thức nên được. Tôi thường thấy mọi người sửa công thức này bằng cách xóa phần có lỗi hoặc đoán phạm vi sẽ được nhập thay vì #REF. Điều này đôi khi có tác dụng, nhưng có thể dễ dàng hơn để tránh #REF. ở nơi đầu tiên

#REF là gì. ?

Excel cho chúng ta biết một cách hữu ích rằng #REF. liên quan đến một "Tham chiếu ô không hợp lệ"

Từ kinh nghiệm cá nhân và giúp đỡ người khác, tôi thấy điều này xảy ra thường xuyên nhất khi các ô đã bị xóa, nhưng một công thức đang tham chiếu đến một phần của phạm vi đã xóa

Trong hình ảnh đầu tiên ở trên, #REF. được gây ra bởi vì công thức đã thêm các ô B4, E5, B11 và cả B15

Nhưng ai đó đã xóa tất cả các hàng bên dưới hàng 11, bao gồm cả ô B15. Điều này gây ra lỗi của tôi vì công thức đang cố thêm một ô đã xóa vào phần bổ sung của nó

Cách tránh #REF

Trước khi tôi xóa bất kỳ hàng, cột, ô hoặc trang tính nào;

Để làm điều này, tôi kiểm tra những người phụ thuộc

Tôi cũng sẽ nói ngắn gọn về các tiền lệ trong bài viết này

Người phụ thuộc và tiền lệ

Tùy thuộc vào phiên bản Excel của bạn, bạn có thể thấy phần “Kiểm tra công thức” trên dải băng/menu Công thức

Chúng ta sẽ giải quyết 3 tùy chọn ở bên trái của phần này

dấu vết tiền lệ

Trong ví dụ của tôi, tổng chính của tôi (tô màu vàng) là tổng của cả 4 tổng phụ

Nếu tôi nhấp vào ô có công thức này (E10) và sau đó nhấp vào “Trace Precedents” thì các mũi tên từ ô này sẽ đi đến tất cả các ô được tham chiếu trong công thức này

Đây là những tiền lệ

Dấu vết phụ thuộc

Khi các tiền lệ theo dõi hiển thị cho bạn ô nào đứng trước (hoặc được bao gồm trong công thức của) ô hiện tại, thì các phụ thuộc theo dõi cho bạn biết liệu ô hiện tại có được tham chiếu hoặc được đưa vào một công thức trong một ô khác hay không

Bên dưới, tôi đã chọn ô B7 và nhấp vào “Trace Dependents” và một mũi tên đi từ ô này đến Tổng phụ 3

Xóa mũi tên

Khi bạn theo dõi những người phụ thuộc và tiền lệ, điều quan trọng cần nhớ là nhấp vào "Xóa mũi tên" hoặc bạn sẽ kết thúc với việc tạo ra các mũi tên trên trang tính của mình

Tiền lệ & phụ thuộc trên một trang tính khác

Cho đến nay chúng ta chỉ xem xét các tiền lệ và phụ thuộc trên cùng một tờ

Điều gì xảy ra nếu các tiền lệ hoặc phụ thuộc nằm trên một trang tính khác?

Bạn vẫn sẽ theo dõi phụ thuộc/tiền lệ như trước, nhưng lần này nếu ô liên quan đến một trang tính khác, bạn sẽ thấy một mũi tên nét đứt màu đen

Nhấp vào mũi tên này sẽ xuất hiện hộp thoại “Go To”

Bạn có thể thấy các trang tính và ô đang được theo dõi và bạn có thể nhấp vào bất kỳ trang tính nào và “OK” để chuyển đến chúng

Tránh #REF

Nếu tôi thấy một ô/phạm vi/hàng/trang tính mà tôi sắp xóa có bất kỳ thành phần phụ thuộc nào, thì tôi có thể xem lại và sửa đổi công thức nếu cần trước khi nhận được lỗi #REF. và không biết những gì gây ra nó

Tôi có xem lại mọi ô mà tôi sắp xóa không…. không là câu trả lời ngắn gọn cho điều đó

Nếu tôi chuẩn bị xóa một trang tính hoặc cột hoặc hàng, số lượng ô cần kiểm tra để tìm người phụ thuộc có thể rất lớn

Thay vào đó, tôi thường kiểm tra những người phụ thuộc cho một trường tổng phụ hoặc trường tương tự có thể được tham chiếu trong bản tóm tắt ở một nơi khác trong tệp chẳng hạn. Tôi không kiểm tra từng cái một

Đây không phải là một cách dễ dàng để ngăn chặn 100% #REF. lỗi, nhưng đó là một khởi đầu tốt. Tôi chưa nhận được gì kể từ khi tôi bắt đầu thêm kiểm tra nhanh (và tôi luôn kiểm tra nhanh) trước khi xóa các ô/dãy/v.v.

Nếu bạn không gặp phải những lỗi này thì có lẽ bạn không thấy điểm của bài đăng này, nhưng dù sao cũng cảm ơn bạn đã đọc đến đây 🙂

Nếu bạn gặp phải những lỗi này và thấy chúng khó chịu như tôi, thì tôi hy vọng bài đăng này có thể giúp bạn tránh hoặc ít nhất là giảm sự xuất hiện của chúng trong tương lai