Một trong những cảnh báo lỗi phổ biến nhất mà người dùng gặp phải trong Excel là’Tham chiếu Thông tư’. Hàng nghìn người dùng có cùng một vấn đề và nó xảy ra khi một công thức tham chiếu trở lại ô của chính nó một cách trực tiếp hoặc gián tiếp, tạo ra một vòng lặp vô tận của các phép tính.
Ví dụ: bạn có hai giá trị trong ô A1 và A2. Khi công thức=A1 + A2 được nhập vào A2, nó sẽ tạo ra một tham chiếu vòng tròn; công thức trong A2 liên tục tự tính toán lại vì mỗi lần tính toán, giá trị A2 đã thay đổi.
Hầu hết các tham chiếu vòng tròn là sai lầm ngoài ý muốn; Excel sẽ cảnh báo bạn về những điều này. Tuy nhiên, cũng có những tham chiếu vòng tròn dự kiến, được sử dụng để thực hiện các phép tính lặp lại. Các tham chiếu vòng tròn ngoài ý muốn trong sổ làm việc của bạn có thể khiến công thức của bạn tính không chính xác.
Do đó, trong bài viết này, chúng tôi sẽ trình bày tất cả những gì bạn cần biết về tham chiếu vòng tròn cũng như cách tìm, sửa, xóa và sử dụng tham chiếu vòng tròn trong Excel.
Cách tìm và xử lý tham chiếu hình tròn trong Excel
Khi làm việc với Excel, đôi khi chúng tôi gặp phải lỗi tham chiếu vòng tròn xảy ra khi bạn nhập công thức bao gồm ô chứa công thức của bạn. Về cơ bản, điều này xảy ra khi công thức của bạn đang cố gắng tự tính toán.
Ví dụ: bạn có một cột số trong ô A1: A4 và bạn đang sử dụng hàm SUM (=SUM (A1: A5)) trong ô A5. Ô A5 đề cập trực tiếp đến ô của chính nó, điều này không thể thực hiện được. Do đó, bạn sẽ nhận được cảnh báo tham chiếu vòng sau:
Khi bạn nhận được thông báo cảnh báo ở trên, bạn có thể nhấp vào”Trợ giúp”để biết thêm thông tin hoặc đóng cửa sổ thông báo bằng cách nhấp vào nút”OK”hoặc”X”và kết quả là nhận được”0″.
Các vòng tham chiếu vòng này có thể làm hỏng việc tính toán của bạn hoặc làm chậm hiệu suất trang tính của bạn. Chúng cũng có thể dẫn đến một số vấn đề khác, không rõ ràng ngay lập tức. Vì vậy, tốt nhất là bạn nên tránh những điều này.
Tham chiếu Thông tư Trực tiếp và Gián tiếp
Các tham chiếu Thông tư có thể được phân thành hai loại: Tham chiếu Thông tư Trực tiếp và Tham chiếu Thông tư gián tiếp.
Tham khảo trực tiếp
Tham chiếu vòng tròn trực tiếp khá đơn giản. Thông báo cảnh báo tham chiếu vòng tròn trực tiếp hiển thị khi công thức trong ô tham chiếu trực tiếp đến ô của chính nó.
Ví dụ: công thức trong ô A2 bên dưới trực tiếp tham chiếu đến ô của chính nó (A2).
Sau khi thông báo cảnh báo hiển thị, người dùng có thể nhấp vào’OK’, nhưng nó sẽ chỉ dẫn đến kết quả là’0′.
Tham chiếu Thông tư gián tiếp
Tham chiếu vòng tròn gián tiếp trong Excel đánh thuế đặt khi một giá trị trong công thức tham chiếu đến ô của chính nó, không phải trực tiếp mà ở một mức độ nào đó. Nói cách khác, tham chiếu vòng tròn có thể được thiết lập bởi hai ô nhắm mục tiêu nhau.
Hãy xem một ví dụ đơn giản.
Bây giờ giá trị bắt đầu từ A1 có giá trị 20.
Tiếp theo, ô C3 tham chiếu đến ô A1.
Sau đó, ô A5 tham chiếu đến ô C3.
Bây giờ, hãy thay thế giá trị 20 trong ô A1 bằng công thức như được hiển thị bên dưới. Mọi ô khác đều phụ thuộc vào ô A1. Khi bạn sử dụng một tham chiếu của bất kỳ ô nào khác trong A1, nó sẽ tạo ra một cảnh báo tham chiếu vòng tròn. Bởi vì, công thức trong A1 tham chiếu đến ô A5, tham chiếu đến C3 và ô C3 tham chiếu trở lại A1, do đó tham chiếu vòng tròn.
Khi bạn nhấp vào ‘OK’, nó dẫn đến giá trị 0 trong ô A1 và Excel sẽ tạo một dòng được liên kết hiển thị Tiền lệ theo dõi và Người phụ thuộc theo dấu vết như được hiển thị trong ảnh chụp màn hình bên dưới. Chúng tôi có thể sử dụng tính năng này để dễ dàng tìm và sửa tham chiếu vòng tròn.
Cách Bật/Tắt Tham chiếu Hình tròn trong Excel
Theo mặc định, tính toán lặp lại bị tắt trong Excel. Các phép tính lặp lại là các phép tính lặp lại cho đến khi một điều kiện cụ thể được đáp ứng. Khi nó bị vô hiệu hóa, Excel sẽ trả về lời nhắc Tham chiếu vòng tròn và trả về kết quả là 0.
Tuy nhiên, có một số trường hợp hiếm hoi cần tham chiếu vòng tròn để thực hiện một vòng lặp. Để sử dụng tham chiếu vòng tròn, bạn phải bật tính toán lặp lại trong sổ làm việc Excel của mình và nó sẽ cho phép bạn thực hiện các phép tính của mình. Bây giờ, hãy chỉ cho bạn cách bạn có thể bật hoặc tắt các phép tính lặp lại.
Trong Excel 2010, Excel 2013, Excel 2016, Excel 2019 và Microsoft 365, hãy chuyển đến tab”Tệp”ở góc trên bên trái của Excel, sau đó nhấp vào”Tùy chọn”trong ngăn bên trái.
Trên cửa sổ Tùy chọn Excel, hãy chuyển đến tab’Công thức’và đánh dấu vào hộp kiểm’Bật tính toán lặp lại’trong phần’Tùy chọn tính toán’. Sau đó, nhấp vào’OK’để lưu các thay đổi.
Điều này sẽ cho phép tính toán lặp lại và do đó cho phép tham chiếu vòng tròn.
Để đạt được điều này trong các phiên bản Excel dễ hiểu, hãy làm theo các bước sau:
- Trong Excel 2007, hãy nhấp vào nút Office> Tùy chọn Excel> Công thức> Vùng lặp lại.
- Trong Excel 2003 và các phiên bản cũ hơn, bạn cần đi tới Trình đơn> Công cụ> Tùy chọn> Tính toán tab.
Lặp lại tối đa & Tham số thay đổi tối đa
Khi bạn bật tính toán lặp lại, bạn có thể kiểm soát các tính toán lặp lại, bằng cách chỉ định hai tùy chọn có sẵn trong phần Bật tính toán lặp lại như được hiển thị trong ảnh chụp màn hình bên dưới.
- <
- Lặp lại tối đa -Nó chỉ định số lần công thức sẽ được tính toán lại trước khi cho bạn kết quả cuối cùng. Như chúng ta thấy ở đây, giá trị mặc định là 100. Điều này có nghĩa là Excel sẽ lặp lại các phép tính 100 lần trước khi cho bạn kết quả cuối cùng. Hãy nhớ rằng số lần lặp càng cao, thì càng tốn nhiều tài nguyên và thời gian để tính toán.
- Thay đổi tối đa -Nó xác định sự thay đổi lớn nhất giữa các kết quả tính toán. Đó là về độ chính xác của kết quả. Số càng nhỏ thì kết quả càng chính xác và thời gian tính toán trên trang tính càng lâu.
- Thanh trạng thái sẽ không hiển thị địa chỉ ô tham chiếu vòng tròn khi tùy chọn Tính toán Lặp lại được bật, vì vậy bạn cần tắt nó đi trước khi bắt đầu kiểm tra sổ làm việc để tìm tham chiếu vòng.
- Trong trường hợp tham chiếu vòng tròn không được tìm thấy trong trang tính đang hoạt động, thanh trạng thái chỉ hiển thị’Tham chiếu vòng tròn’mà không có địa chỉ ô.
- Bạn sẽ chỉ nhận được cảnh báo tham chiếu vòng tròn một lần và sau khi bạn loại bỏ nó, nó sẽ không hiển thị lại lời nhắc vào lần sau.
- Nếu sổ làm việc của bạn có các tham chiếu vòng tròn, nó sẽ hiển thị cho bạn lời nhắc mỗi khi bạn mở nó cho đến khi bạn giải quyết tham chiếu vòng tròn hoặc cho đến khi bạn bật tính toán lặp lại.
Sau đó, bạn sẽ không nhận được bất kỳ cảnh báo nào bất cứ khi nào có tham chiếu vòng tròn trong trang tính của bạn. Chỉ bật tính toán tương tác khi thực sự cần thiết.
Tìm tham chiếu vòng trong Excel
Giả sử bạn có một tập dữ liệu lớn và bạn nhận được cảnh báo tham chiếu vòng tròn, bạn vẫn cần tìm hiểu lỗi đã xảy ra ở ô nào để sửa lỗi. Để tìm tham chiếu vòng tròn trong Excel, hãy tiến hành theo các bước sau:
Sử dụng Công cụ Kiểm tra Lỗi
Trước tiên, hãy mở trang tính nơi tham chiếu vòng tròn đã xảy ra. Chuyển đến tab ‘Công thức’, nhấp vào mũi tên bên cạnh công cụ ‘Kiểm tra lỗi’. Sau đó, chỉ cần di con trỏ qua tùy chọn ‘Tham chiếu hình tròn’, Excel sẽ hiển thị cho bạn danh sách tất cả các ô có liên quan đến tham chiếu hình tròn như hình dưới đây.
Nhấp vào bất kỳ địa chỉ ô nào bạn muốn và nó sẽ đưa bạn đến ô cụ thể đó để giải quyết vấn đề.
Sử dụng thanh trạng thái
Một cách dễ dàng khác để tìm tham chiếu vòng tròn là nhìn vào thanh Trạng thái. Trên thanh trạng thái của Excel, nó sẽ hiển thị cho bạn địa chỉ ô mới nhất có tham chiếu vòng tròn, chẳng hạn như’Tham chiếu hình tròn: B6′(xem ảnh chụp màn hình bên dưới).
Có một số điều bạn nên biết khi làm việc với tham chiếu vòng tròn:
Xóa tham chiếu vòng trong Excel
Việc tìm kiếm các tham chiếu của Thông tư rất dễ dàng nhưng việc sửa chữa nó không hề đơn giản. Rất tiếc, không có gì trong Excel cho phép bạn xóa tất cả các tham chiếu vòng tròn cùng một lúc.
Để sửa các tham chiếu vòng tròn, bạn phải tìm từng tham chiếu vòng riêng lẻ và cố gắng sửa đổi nó, xóa hoàn toàn công thức vòng tròn hoặc thay thế nó bằng một công thức khác.
Đôi khi, trong các công thức đơn giản, tất cả những gì bạn cần làm là điều chỉnh lại các tham số của công thức để nó không quay trở lại chính nó. Ví dụ: thay đổi công thức trong B6 thành=SUM (B1: B5) * A5 (thay đổi B6 thành B5).
Nó sẽ trả về kết quả của phép tính là”756″.
Trong trường hợp khó tìm thấy một tham chiếu vòng tròn Excel, bạn có thể sử dụng các tính năng Trace Precedents và Trace Dependents để theo dõi nó trở lại nguồn và giải quyết từng việc một. Mũi tên cho biết ô nào bị ảnh hưởng bởi ô đang hoạt động.
Có hai phương pháp theo dõi sẽ cho phép bạn xóa các tham chiếu vòng tròn bằng cách truy tìm mối quan hệ giữa các công thức và ô.
Để truy cập các phương pháp theo dõi, hãy chuyển đến tab”Công thức”, sau đó nhấp vào”Tiền lệ theo dõi”hoặc”Người phụ thuộc theo dấu vết”trong nhóm Kiểm tra công thức.
Tiền lệ theo dõi
Khi bạn chọn tùy chọn này, nó sẽ theo dõi lại các ô ảnh hưởng đến giá trị của ô hiện hoạt. Nó vẽ một đường màu xanh lam cho biết ô nào ảnh hưởng đến ô hiện tại. Phím tắt để sử dụng tiền lệ theo dõi là Alt + T U T .
Trong ví dụ bên dưới, mũi tên màu xanh lam hiển thị các ô ảnh hưởng đến giá trị B6 là B1: B6 và A5. Như bạn có thể thấy bên dưới, ô B6 cũng được bao gồm, làm cho nó trở thành một tham chiếu vòng tròn và khiến công thức trả về 0.
Điều này có thể dễ dàng khắc phục bằng cách thay B6 bằng B5 trong đối số của SUM:=SUM (B1: B5).
Theo dõi người phụ thuộc
Tính năng phụ thuộc dấu vết theo dõi các ô phụ thuộc vào ô đang hoạt động. Tính năng này vẽ các đường cho biết ô nào bị ảnh hưởng bởi ô đã chọn. Nói cách khác, nó cho biết ô nào chứa công thức tham chiếu đến ô hiện hoạt. Phím tắt để sử dụng người phụ thuộc là Alt + T U D .
Trong ví dụ sau, ô D3 bị ảnh hưởng bởi B4. Nó phụ thuộc vào B4 cho giá trị của nó để tạo ra kết quả. Do đó, phụ thuộc dấu vết vẽ một đường màu xanh lam từ B4 đến D3, cho biết D3 phụ thuộc vào B4.
Cố ý Sử dụng Tham chiếu Thông tư trong Excel
Việc cố ý sử dụng tham chiếu vòng tròn không được khuyến khích, nhưng có thể có một số trường hợp cụ thể mà bạn cần tham chiếu vòng tròn để có thể có được kết quả mong muốn.
Hãy để chúng tôi giải thích điều đó bằng cách sử dụng một ví dụ.
Để bắt đầu, hãy bật’Tính toán Lặp lại’trong sổ làm việc Excel của bạn. Khi bạn đã bật Tính toán lặp lại, bạn có thể bắt đầu sử dụng các tham chiếu vòng tròn có lợi cho mình.
Giả sử bạn đang mua một ngôi nhà và bạn đã quyết định chia 2% hoa hồng trên tổng chi phí cho đại lý của mình. Tổng chi phí sẽ được tính trong ô B6 và phần trăm hoa hồng (phí đại lý) được tính trong ô B4. Hoa hồng được tính từ tổng chi phí và tổng chi phí bao gồm cả hoa hồng. Vì các ô B4 và B6 phụ thuộc vào nhau, nó tạo ra một tham chiếu vòng tròn.
Nhập công thức để tính tổng chi phí vào ô B6:
=SUM (B1: B4)
Vì tổng chi phí bao gồm phí đại lý, chúng tôi đã bao gồm B4 trong công thức trên.
Để tính Phí đại lý là 2%, hãy chèn công thức này vào B4:
=B6 * 2%
Bây giờ công thức trong ô B4 phụ thuộc vào giá trị của B6 để tính 2% tổng phí và công thức trong B6 phụ thuộc vào B4 để tính tổng chi phí (bao gồm phí đại lý), do đó tham chiếu vòng tròn.
Nếu tính toán lặp lại được bật, Excel sẽ không cung cấp cho bạn cảnh báo hoặc số 0 trong kết quả. Thay vào đó, kết quả của ô B6 và B4 sẽ được tính như hình trên.
Tính toán lặp đi lặp lại thường bị tắt theo mặc định. Nếu bạn không bật nó và khi bạn nhập công thức vào B4, công thức này sẽ tạo ra một tham chiếu vòng tròn. Excel sẽ đưa ra cảnh báo và khi bạn nhấp vào’OK’, mũi tên theo dõi sẽ được hiển thị.
Chỉ vậy thôi. Đây là tất cả những gì bạn cần biết về tham chiếu vòng tròn trong Excel.
