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 vòng tròn’. 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, gây 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 ô B1 và B2. Khi công thức = B1 + B2 được nhập vào B2, nó sẽ tạo ra một tham chiếu vòng tròn; công thức trong B2 nhiều lần tự tính toán lại vì mỗi lần tính toán, giá trị B2 đã thay đổi.
Hầu hết các tài liệu tham khảo 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ó các 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 trang tính của bạn có thể khiến công thức của bạn tính toán không chính xác.
Do đó, trong bài viết này, chúng tôi sẽ giải thích 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 trong Excel.
Cách tìm và xử lý tham chiếu vòng 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, nó xảy ra khi công thức của bạn đang cố gắng tính toán chính 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 tham chiếu trực tiếp đến ô của chính nó, điều này là sai. Do đó, bạn sẽ nhận được cảnh báo tham chiếu vòng sau:
Sau khi nhận được thông báo cảnh báo ở trên, bạn có thể nhấp vào nút ‘Trợ giúp’ để biết thêm về lỗi hoặc đóng cửa sổ thông báo lỗi bằng cách nhấp vào nút ‘OK’ hoặc ‘X’ và nhận được kết quả là ‘0’.
Đôi khi, các vòng tham chiếu tròn có thể khiến tính toán của bạn gặp sự cố hoặc làm chậm hiệu suất trang tính của bạn. Tham chiếu vòng cũng có thể dẫn đến một số vấn đề khác, vấn đề này sẽ không hiển nhiên 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
Tài liệu tham khảo Thông tư có thể được phân thành hai loại: Tài liệu tham khảo thông tư trực tiếp và tài liệu tham khảo thông tư gián tiếp.
Tham khảo trực tiếp
Một tham chiếu vòng tròn trực tiếp là khá đơn giản. Thông báo cảnh báo tham chiếu vòng tròn trực tiếp bật lên khi công thức đang tham chiếu trực tiếp trở lại ô của chính nó.
Trong ví dụ dưới đây, công thức trong ô A2 trực tiếp tham chiếu đến ô của chính nó (A2).
Khi thông báo cảnh báo bật lên, bạn có thể nhấp vào ‘OK’, nhưng nó sẽ chỉ dẫn đến kết quả là ‘0’.
Tham chiếu vòng gián tiếp
Tham chiếu vòng tròn gián tiếp trong Excel xảy ra khi một giá trị trong công thức tham chiếu trở lại ô của chính nó, nhưng không trực tiếp. Nói cách khác, tham chiếu vòng tròn có thể được hình thành bởi hai ô tham chiếu lẫn nhau.
Hãy để chúng tôi giải thích bằng ví dụ đơn giản này.
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ờ thay thế giá trị 20 trong ô A1 bằng công thức như hình dưới đây. Mọi ô khác đều phụ thuộc vào ô A1. Khi bạn sử dụng tham chiếu của bất kỳ ô công thức nào khác trước đó trong A1, nó sẽ gây ra cảnh báo tham chiếu vòng. 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ị 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 / xóa các 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 (tắt) trong Excel. Tính toán lặp lại là các phép tính lặp đi lặp lại cho đến khi nó đáp ứng một điều kiện cụ thể. Khi nó bị tắt, Excel sẽ hiển thị thông báo Tham chiếu vòng tròn và trả về kết quả là 0.
Tuy nhiên, đôi khi cần tham chiếu vòng tròn để tính toá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 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úng tôi chỉ cho bạn cách bạn có thể bật hoặc tắt tính toán 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, bấm 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 trước đó, bạn cần chuyển đến tab Menu> Công cụ> Tùy chọn> Tính toán.
Số lần lặp lại tối đa & thông 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 - Con số này 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. Giá trị mặc định là 100. Nếu bạn thay đổi thành '50', Excel sẽ lặp lại các phép tính 50 lần trước khi cung cấp 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. Giá trị này xác định độ 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.
Nếu tùy chọn tính toán lặp lại được bật, 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 phải tìm ra lỗi đã xảy ra ở đâu (trong ô nào) để sửa chữa nó. Để tìm tham chiếu vòng tròn trong Excel, hãy làm theo các bước sau:
Sử dụng công cụ kiểm tra lỗi
Đầu tiên, 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 trong danh sách và nó sẽ đưa bạn đến địa chỉ ô đó để giải quyết vấn đề.
Sử dụng thanh trạng thái
Bạn cũng có thể tìm thấy tham chiếu vòng tròn tại 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 hình 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 xử lý tham chiếu vòng tròn:
- 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 kích hoạt, vì vậy bạn cần phải tắt nó trước khi bắt đầu xem sổ làm việc để tìm tham chiếu vòng.
- Trong trường hợp không tìm thấy tham chiếu vòng tròn trong trang tính hiện hoạt, 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 lời nhắc tham chiếu vòng tròn một lần và sau khi bạn nhấp vào ‘OK’, nó sẽ không hiển thị lại lời nhắc vào lần tiếp theo.
- Nếu sổ làm việc của bạn có tham chiếu vòng tròn, nó sẽ hiển thị 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.
Loại bỏ một tham chiếu vòng trong Excel
Tìm tài liệu tham khảo 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ó tùy chọn nào 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 có thể giúp bạn xóa các tham chiếu vòng tròn bằng cách hiển thị 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.
Theo dõi tiền lệ
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ụ dưới đây, 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 là một phần của công thức, 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ề kết quả là ‘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 theo dõi phụ thuộc theo dõi các ô phụ thuộc vào ô đã chọn. Tính năng này vẽ một đường màu xanh lam cho biết ô nào bị ảnh hưởng bởi ô đã chọn. Có nghĩa là, nó hiển thị những ô nào chứa công thức tham chiếu đến ô đang hoạt động. 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 đó, dấu vết phụ thuộc 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 vòng 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 hiếm hoi mà bạn cần tham chiếu vòng tròn để có thể nhận được kết quả như 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 muốn chia 2% hoa hồng trên tổng chi phí của ngôi nhà 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 đưa ra cảnh báo cho bạn hoặc kết quả là 0. Thay vào đó, kết quả của ô B6 và B4 sẽ được tính như hình trên.
Tùy chọn tính toán 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 đánh dấu sẽ được hiển thị.
Đó là nó. Đây là tất cả những gì bạn cần biết về tham chiếu vòng tròn trong Excel.