Cách tìm tham chiếu vòng trong Excel

Thường thì nó xảy ra đúng lúc tệ nhất: một bảng tính chạy ổn hôm qua bỗng hiện cảnh báo tham chiếu vòng ngay trước hạn chót.

Tin tốt là những tham chiếu vòng này dễ sửa hơn bạn nghĩ.

Trong hướng dẫn này, tôi sẽ giải thích:

• Cách tìm tham chiếu vòng trong Excel
• Hiểu các cách chúng xuất hiện
• Sửa chúng bằng các phương pháp đã được kiểm chứng

Tham chiếu vòng trong Excel là gì?

Tham chiếu vòng trong Excel xảy ra khi một công thức tham chiếu lại chính ô của mình (trực tiếp hoặc gián tiếp), tạo ra một vòng lặp tính toán mà Excel không thể giải được. Ví dụ, nếu ô A1 chứa =A1+10, Excel sẽ cố tính kết quả mãi mãi.

Ngay cả các vòng gián tiếp cũng gây rắc rối. Nếu A1 phụ thuộc vào B1, B1 phụ thuộc vào C1, và C1 phụ thuộc vào A1, Excel sẽ bị mắc kẹt. Tệp có thể mất nhiều thời gian để tính, hiển thị giá trị sai, hoặc ngừng cập nhật hoàn toàn.

Một số tham chiếu vòng là lỗi công thức đơn giản, số khác lại là lựa chọn thiết kế có chủ đích dùng trong các mô hình nâng cao. Tôi sẽ bàn về sự khác biệt đó sau, nhưng trước tiên hãy xem các loại khác nhau.

Tham chiếu vòng trực tiếp

Tham chiếu vòng trực tiếp xảy ra khi công thức chứa chính địa chỉ ô của nó. Giả sử bạn có hai giá trị ở ô A2 và B2. Nếu tại ô C2 chúng ta nhập A2 + B2 + C2, thì C2 đang tự tham chiếu trực tiếp. 

Khi bạn nhập công thức và nhấn Enter, một cửa sổ cảnh báo tham chiếu vòng sẽ xuất hiện. Và khi bạn bấm OK, công thức trả về một kết quả nào đó ngoài dự kiến.

Tham chiếu vòng trực tiếp. Ảnh: Tác giả.

Nó trả về 0 vì khi cộng A2 và B2 lẽ ra được 6, nhưng 'C2' cứ tự cộng thêm chính nó. Excel không thể tính C2 nếu không biết giá trị của C2. Điều này tạo ra một vòng lặp tức thì không bao giờ kết thúc.

Lưu ý: Các tham chiếu vòng này dễ nhận ra nhất vì phần tự tham chiếu hiển thị trực tiếp trong cú pháp công thức. Thực tế, Excel phát hiện và gắn cờ chúng ngay khi bạn nhập công thức.

Tham chiếu vòng gián tiếp

Tham chiếu vòng gián tiếp xảy ra khi nhiều ô tạo thành một chuỗi phụ thuộc rồi quay lại điểm bắt đầu.

Ví dụ:

• F2 tham chiếu H4

• H4 tham chiếu E4

• E4 tham chiếu F2

Tham chiếu vòng gián tiếp. Ảnh: Tác giả.

Không có công thức nào tự nó trông sai. Vấn đề chỉ lộ ra khi Excel lần theo cả chuỗi và nhận ra phép tính quay về điểm xuất phát.

Những trường hợp này khó phát hiện hơn vì vòng lặp có thể trải qua nhiều ô và, trong sổ làm việc phức tạp, thậm chí nhiều trang tính, trước khi Excel xác định được phụ thuộc vòng.

Tham chiếu vòng ẩn

Tham chiếu vòng ẩn là nơi vòng phụ thuộc không hiển thị rõ trong cú pháp công thức. Chúng thường đến từ:

• Vùng đặt tên (named range)

• Hàm INDIRECT() hoặc OFFSET()

• Logic điều kiện phức tạp

Ví dụ, một vùng đặt tên QuarterlyTotal bao gồm các ô C3 đến C5, chứa giá trị doanh số theo quý.

Tại ô C5, thanh công thức hiển thị:

=QuarterlyTotal*0.1Được hỗ trợ bởi

Thoạt nhìn, có vẻ ổn. Công thức không tham chiếu C5 trực tiếp. Tuy nhiên, vì QuarterlyTotal bao gồm C5, Excel phải tính C5 như một phần của tổng được dùng để tính C5.

Điều này tạo ra một vòng ẩn:

• Excel cố tính QuarterlyTotal

• QuarterlyTotal bao gồm C5

• C5 phụ thuộc vào QuarterlyTotal

Tham chiếu là vòng, dù cú pháp công thức không thể hiện rõ điều đó.

Đây là lý do khiến tham chiếu vòng ẩn rất khó xử lý. Công thức có vẻ đúng, sổ làm việc có thể tính bình thường trong thời gian dài, và chỉ khi Excel đánh giá vùng đặt tên thì tham chiếu vòng mới lộ ra.

Tham chiếu vòng ẩn. Ảnh: Tác giả.

Logic điều kiện còn khiến việc phát hiện khó hơn. Vì sao? Bởi các IF() lồng nhau có thể chỉ tạo tham chiếu vòng trong những điều kiện nhất định. Kết quả là tệp vẫn chạy cho đến khi một số giá trị thay đổi.

Tham chiếu vòng ẩn là khó khắc phục nhất vì vòng phụ thuộc bị che khuất bởi lớp trừu tượng thay vì các tham chiếu ô hiển thị.

Tham chiếu vòng có chủ đích và vô tình

Một số tham chiếu vòng tồn tại theo thiết kế. Số khác là lỗi bạn cần loại bỏ.

Tham chiếu vòng có chủ đích

Tham chiếu vòng có chủ đích được tạo ra nhằm phục vụ các nhu cầu mô hình hóa cụ thể, nơi các giá trị phụ thuộc lẫn nhau.

Lấy mô hình tài chính làm ví dụ: chi phí lãi vay phụ thuộc vào dư nợ, trong khi dư nợ thay đổi theo dòng tiền, vốn bao gồm cả chi phí lãi vay. Điều này tạo ra một vòng phản hồi phản ánh logic kinh doanh thực tế.

Mô hình bắt đầu với cấu trúc đơn giản:

• Số dư nợ thể hiện số đầu kỳ
• Chi phí lãi tính theo tỷ lệ phần trăm của dư nợ
• Dư nợ cập nhật bao gồm cả số dư gốc và tiền lãi

Tham chiếu vòng có chủ đích. Ảnh: Tác giả.

Khi lãi tính dựa trên dư nợ, và dư nợ cập nhật bao gồm tiền lãi đó, phép tính tự nhiên quay lại chính nó. Excel gắn cờ đây là tham chiếu vòng và mặc định trả về 0 vì không thể giải quyết phụ thuộc chỉ trong một lần tính.

Để xử lý, hãy bật tính toán lặp như sau:

1. Vào File > Options > Formulas
2. Trong mục Calculation Option đánh dấu chọn ô Enable iterative calculation

Bật tùy chọn tính toán lặp. Ảnh: Tác giả.

Khi bật tính toán lặp, Excel sẽ liên tục tính lại lãi và dư nợ cập nhật. Mỗi lần, các con số điều chỉnh nhẹ cho đến khi ổn định và ngừng thay đổi. 

Kết quả cuối cùng là một nghiệm hội tụ thay vì phép tính một bước. Cách này hoạt động vì tham chiếu vòng là có chủ đích. Lãi ảnh hưởng dư nợ, và dư nợ ảnh hưởng lãi. Mỗi giá trị tác động lẫn nhau, nên việc tính lặp là mong đợi và cần thiết.

Tính toán lặp. Ảnh: Tác giả.

Lưu ý: Tính toán lặp tự thân không đảm bảo kết quả có ý nghĩa. Nó chỉ lặp lại logic bạn xây dựng trong mô hình, vì vậy tham chiếu vòng có chủ đích cần được theo dõi và kiểm chứng cẩn thận.

Tham chiếu vòng vô tình

Tham chiếu vòng vô tình phát sinh do lỗi công thức, sai sót khi sao chép-dán, hoặc thay đổi ngoài ý muốn. Giả sử bạn nhập các giá trị 2, 4, và 6 tại ô A1:A3.

Tại A4, bạn muốn tính tổng, nên nhập:

=SUM(A1:A4)Được hỗ trợ bởi

Ô A4 giờ đã tự bao gồm chính nó trong phép tính.

Khi nhấn Enter, Excel hiện cảnh báo tham chiếu vòng. Sau khi nhấp OK, Excel trả về 0.

Tham chiếu vòng vô tình. Ảnh: Tác giả.

Tham chiếu vòng này không phản ánh logic thực. Nó tồn tại vì phạm vi công thức sai.

Cách sửa tham chiếu vòng trong Excel

Khi Excel gắn cờ một vòng, bạn cần xác định chính xác điểm bắt đầu và quyết định cách xử lý. 

Hãy xem một vài phương pháp sửa tham chiếu vòng, tùy theo đó là vô tình hay có chủ đích: 

Phát hiện nhanh để xác định vị trí vấn đề

Excel có vài công cụ phát hiện tích hợp để nhanh chóng chỉ ra tham chiếu vòng, mỗi công cụ có ưu và nhược riêng. Hãy cùng đi qua các tùy chọn này. 

Thông báo trên thanh trạng thái

Cách nhanh nhất để phát hiện tham chiếu vòng là nhìn vào thanh trạng thái ở góc dưới bên trái cửa sổ Excel.

Khi tồn tại tham chiếu vòng, Excel hiển thị văn bản như “Circular References: G4.” Điều này cho bạn biết ô được phát hiện gần đây nhất có tham gia vào vòng.

Hiển thị tham chiếu vòng trên thanh trạng thái. Ảnh: Tác giả.

Tuy nhiên, thanh trạng thái chỉ hiển thị một tham chiếu vòng tại một thời điểm. Nếu tồn tại nhiều vòng, Excel sẽ lần lượt tiết lộ từng cái.

Cách xử lý như sau: 

1. Sửa ô hiển thị trên thanh trạng thái
2. Kiểm tra lại thanh trạng thái
3. Lặp lại cho đến khi thông báo biến mất

Mẹo: Ghi lại từng địa chỉ ô, vì sửa một vòng thường làm lộ vòng khác.

Cảnh báo ban đầu

Excel cũng hiển thị cảnh báo khi:

• Bạn tạo một tham chiếu vòng, hoặc
• Bạn mở một sổ làm việc đã có tham chiếu vòng

Cảnh báo thường nêu rằng một hoặc nhiều công thức tự tham chiếu và có thể tính sai.

Các cảnh báo này có thể ngừng xuất hiện nếu:

• Bạn đã bỏ qua thông báo trước đó bằng cách bấm OK
• Đã bật tính toán lặp
• Chế độ tính được đặt thành Manual (Formulas > Calculation Options > Manual)

Nếu kết quả trông không đúng và không có cảnh báo nào, hãy buộc tính lại như sau:

• Nhấn F9 để tính lại trang tính đang hoạt động
• Nhấn Ctrl + Alt + F9 để buộc tính lại trên tất cả các trang

Mẹo: Đóng và mở lại sổ làm việc cũng có thể khiến Excel hiển thị lại cảnh báo.

Công cụ kiểm tra lỗi

Để xem nhiều tham chiếu vòng cùng lúc, hãy dùng công cụ Error Checking của Excel: Vào Formulas > Error Checking > Circular References

Menu này liệt kê tất cả tham chiếu vòng đã phát hiện và cho phép bạn nhảy trực tiếp đến từng ô, giúp chẩn đoán các sổ làm việc phức tạp dễ hơn.

Nếu menu con Circular References trống nhưng Excel vẫn hoạt động bất thường, hãy nhấn F9 để buộc tính lại và làm mới danh sách.

Công cụ kiểm tra lỗi để tìm tham chiếu vòng. Ảnh: Tác giả.

Lưu ý: Trong Excel 2007 trở đi, tính năng này có trong Ribbon. Các phiên bản cũ hơn cung cấp các tùy chọn tương tự qua thanh menu.

Công cụ truy vết trực quan

Sau khi xác định được ô có vấn đề, hãy dùng các công cụ truy vết trực quan để xem tham chiếu vòng hình thành thế nào.

Các công cụ này vẽ mũi tên giữa các ô và làm lộ các phụ thuộc thay vì ẩn trong công thức. Cả hai có trong nhóm Formula Auditing trên thẻ Formulas:

• Trace Precedents hiển thị các ô cấp dữ liệu cho công thức đã chọn. Excel vẽ mũi tên từ các ô đầu vào về phía ô công thức. Phím tắt: Alt + M, P
• Trace Dependents hiển thị các ô sử dụng giá trị của ô đã chọn. Excel vẽ mũi tên từ ô đã chọn tới mọi công thức tham chiếu nó. Phím tắt: Alt + M, D

Nếu lần theo precedents và dependents mà cuối cùng quay lại ô ban đầu, các mũi tên tạo thành vòng lặp và xác nhận tham chiếu vòng.

Trace Precedents và Trace Dependents. Ảnh: Tác giả.

Sau khi sửa công thức, các mũi tên sẽ không còn quay về và tham chiếu vòng biến mất. Bạn có thể xóa mũi tên bằng Remove Arrows trong cùng menu.

Tái cấu trúc công thức và phép tính thay thế

Đa số tham chiếu vòng xuất hiện vì thứ tự tính toán sai, không phải vì Excel trục trặc. Với các tham chiếu vòng vô tình, tái cấu trúc công thức là giải pháp hiệu quả nhất. 

Dưới đây là vài cách khắc phục:  

Tái cấu trúc công thức để bỏ tự phụ thuộc

Tham chiếu vòng thường xuất hiện khi công thức cố tính một giá trị bằng chính kết quả của nó.

Ví dụ, công thức như =A2+1 ở ô A2 tạo vòng lặp ngay vì ô tự phụ thuộc vào mình. Để sửa, hãy chuyển đầu vào cần thiết sang ô khác và tham chiếu tới giá trị đó.

Khi phụ thuộc tự thân được loại bỏ, Excel có thể tính công thức bình thường.

Dùng cột/ô phụ để tính trung gian

Tham chiếu vòng cũng xảy ra khi một công thức cố gắng xử lý nhiều bước cùng lúc.

Ví dụ, nếu hai ô phụ thuộc lẫn nhau để tính một giá trị dùng chung, Excel không có điểm bắt đầu rõ ràng. Để sửa, hãy thêm một ô phụ thực hiện tính trung gian trước.

Bằng cách tính các giá trị dùng chung ở một ô phụ riêng, Excel có thể theo một trật tự rõ ràng:

1. Ô phụ tính trước
2. Các công thức phụ thuộc tính sau

Cách tiếp cận này đơn giản hóa công thức và loại bỏ phụ thuộc vòng.

Tách phần tổng hợp khỏi phần tính toán

Tổng và công thức tổng hợp không nên đặt trong cùng các ô mà chúng phụ thuộc vào. Khi một tổng lấy số từ một vùng bao gồm cả chính nó, Excel tạo tham chiếu vòng. 

Ví dụ, nếu A2 chứa số gốc 1000, B2 chứa tỷ lệ 10%, C2 hiển thị tổng, và D2 tính phí.

Nếu C2 dùng =A2+D2 và D2 dùng =C2*$B$2, sẽ xuất hiện tham chiếu vòng vì tổng phụ thuộc vào phí, còn phí phụ thuộc vào tổng. 

Excel không thể tính cái nào trước.

Để sửa, phí nên được tính từ giá trị gốc, không phải từ tổng. Hãy đổi D2 thành =A2*$B$2 và giữ C2 là =A2+D2.

Dùng các ô riêng để tránh tham chiếu vòng. Ảnh: Tác giả.

Giờ Excel có thể tính phí trước rồi cộng vào số gốc. Tham chiếu vòng biến mất vì mỗi công thức có điểm bắt đầu rõ ràng.

Bật tính toán lặp cho logic vòng có chủ đích

Chỉ nên dùng tính toán lặp khi tham chiếu vòng là yếu tố thiết kế có chủ đích, không phải lỗi vô tình. Phương pháp này phù hợp với các mô hình tài chính và kịch bản khác nơi có vòng phản hồi hợp lệ, và logic vòng phản ánh hành vi thực tế.

Để bật tính toán lặp trong Excel cho Windows:

1. Vào File > Options > Formulas
2. Trong phần tùy chọn Calculation, chọn Enable iterative calculation
3. Nhấn OK

Khi bật, Excel sẽ lặp lại việc tính các công thức có tham chiếu vòng cho đến khi chúng ổn định hoặc đạt điều kiện dừng.

Thiết lập lặp chính

Hai thiết lập kiểm soát cách tính toán lặp hoạt động:

• Maximum Iterations đặt số lần Excel tính lại trước khi dừng. Giá trị 100 phù hợp với hầu hết các mô hình kinh doanh và tài chính.
• Maximum Change xác định độ chênh lệch nhỏ nhất giữa các lần tính để Excel dừng lặp. Giá trị 0.001 là điểm khởi đầu phổ biến, cân bằng giữa độ chính xác và hiệu năng.

Tăng một trong hai có thể cải thiện độ chính xác nhưng có thể làm chậm tính toán trong sổ lớn.

Khi nào dùng phương pháp này

Chỉ bật tính toán lặp khi:

• Tham chiếu vòng thể hiện logic kinh doanh hợp lệ
• Mô hình có các vòng phản hồi chính đáng (ví dụ, lãi phụ thuộc dư nợ, còn dư nợ thay đổi theo lãi)
• Bạn kỳ vọng các giá trị sẽ hội tụ thay vì dao động vô tận

Phương pháp này không phải là cách sửa cho các tham chiếu vòng vô tình do lỗi công thức.

Cảnh báo quan trọng cần lưu ý

Trước khi bật tính toán lặp, hãy nhớ các rủi ro sau:

• Tham chiếu vòng vô tình có thể không còn kích hoạt cảnh báo, khiến lỗi khó phát hiện hơn.
• Các mô hình lớn hoặc phức tạp có thể tính chậm hơn.
• Nếu giá trị dao động thay vì ổn định, kết quả có thể không đáng tin.

Vì các rủi ro này, việc bật tính toán lặp luôn cần được ghi chú rõ ràng.

Triển khai “cầu dao” cho mô hình phức tạp

“Cầu dao” là kỹ thuật nâng cao giúp bạn kiểm soát khi nào logic vòng hoạt động. Thay vì loại bỏ tham chiếu vòng, bạn cố ý tạm dừng hoặc cho phép nó bằng một giá trị điều khiển trong công thức.

Cách tiếp cận này phù hợp nhất với người lập mô hình tài chính và người dùng nâng cao cần logic vòng trong một số tình huống nhưng không phải lúc nào cũng cần.

Một “cầu dao” thường dùng:

• Một ô điều khiển (thường đặt 0 hoặc 1)

• Một câu lệnh IF chuyển đổi giữa logic vòng và phương án không vòng

Mẫu tổng quát như sau:

=IF($A$1=1, circular_logic, non_circular_alternative)Được hỗ trợ bởi

• Khi ô điều khiển bằng 0, công thức tránh tham chiếu vòng
• Khi ô điều khiển bằng 1, logic vòng được phép chạy

Giả sử bạn nhập các giá trị sau: 

• A1: Cầu dao (0 hoặc 1)

• A2: Số dư ban đầu = 1000

• B2: Tiền lãi

• C2: Số dư cập nhật

Tại B2, dùng =IF($A$1=1, C2*10%, A2*10%) và tại C2 nhập =A2+B2

Bây giờ:

• Khi A1 = 0, lãi được tính từ số dư ban đầu. Tham chiếu vòng bị vô hiệu, và Excel tính bình thường.

• Khi A1 = 1, lãi được tính từ số dư cập nhật. Logic vòng hoạt động và tạo vòng phản hồi có chủ đích.

Sử dụng “cầu dao”. Ảnh: Tác giả.

Khi nào dùng “cầu dao”

“Cầu dao” hữu ích để:

• Giữ logic vòng tắt trong khi xây dựng và kiểm chứng công thức
• Bật/tắt logic vòng để cô lập vấn đề và xác minh kết quả
• Trình bày mô hình với logic vòng tắt để giả định và cơ chế dễ giải thích hơn

Xóa công thức có vấn đề và làm lại từ đầu

Đây là biện pháp “mạnh tay”: dùng khi các cách khác thất bại. Phù hợp với sổ làm việc có phụ thuộc rối rắm vô vọng, tệp thừa kế có logic không rõ, hoặc khi áp lực thời gian đòi hỏi giải pháp nhanh nhưng đáng tin.

Thay vì cố gỡ rối các công thức hỏng, bạn đặt lại mô hình và xây lại một cách có phương pháp. 

Hãy xem cách làm.

Bước 1: Tạo bản sao lưu

Trước khi thay đổi, hãy tạo bản sao lưu đầy đủ của sổ làm việc. Thực hiện như sau:

• Mở sổ làm việc trong Excel

• Vào File > Save As > Browse và chọn vị trí khác hoặc giữ cùng thư mục để lưu tệp

• Đổi tên tệp, ví dụ, Workbook_Backup.xlsx

• Nhấp Save

Tạo bản sao lưu sổ làm việc. Ảnh: Tác giả.

Điều này giữ nguyên công thức, logic và kết quả gốc để bạn có thể tham chiếu lại sau hoặc hoàn tác khi cần.

Không bao giờ thử cách này trên bản duy nhất của tệp.

Bước 2: Ghi nhận cấu trúc hiện tại

Tiếp theo, nắm bắt cách sổ làm việc đang vận hành:

• Chụp màn hình các phần chính
• Ghi chú mỗi trang tính đang tính gì
• Xác định đầu vào, đầu ra và phần tổng hợp

Tài liệu này giúp bạn hiểu cần xây lại những gì và tránh để mất các logic quan trọng khi dọn dẹp.

Bước 3: Thay công thức bằng giá trị

Để phá vỡ mọi phụ thuộc vòng cùng lúc, hãy sao chép các vùng bị ảnh hưởng và dùng Paste Special. Nhấp chuột phải > Paste Special > Values để thay công thức bằng kết quả tính của chúng.

Dùng tùy chọn Paste Special. Ảnh: Tác giả.

Cách này loại bỏ mọi tham chiếu vòng ngay lập tức trong khi vẫn giữ nguyên các con số hiển thị làm mốc tạm thời.

Bước 4: Xây lại công thức theo từng phần

Xây lại sổ làm việc theo từng khối logic thay vì làm một lần:

1. Thêm công thức cho một khối logic
2. Nhấn F9 để buộc tính
3. Xác nhận không xuất hiện tham chiếu vòng
4. Chuyển sang phần tiếp theo

Bước 5: Đối chiếu kết quả với bản sao lưu

Khi xây lại từng phần, hãy so sánh kết quả với tệp sao lưu. Bạn có thể nhận thấy khác biệt cho thấy:

• Thiếu đầu vào
• Logic công thức sai
• Lỗi tính toán từng bị ẩn

Phát hiện nâng cao cho các tham chiếu khó lộ

Một số tham chiếu vòng không lộ ra khi dùng công cụ tiêu chuẩn của Excel. Khi đó, hãy thử các kỹ thuật nâng cao: 

Macro VBA để quét toàn diện

Khi truy vết thủ công quá chậm, VBA có thể quét hàng nghìn ô trong vài giây và xác định tham chiếu vòng trên toàn sổ làm việc.

Cách tiếp cận cơ bản như sau:

• Nhấn Alt + F11 để mở Visual Basic Editor.
• Insert > Module.
• Dán một macro phát hiện tham chiếu vòng.
• F5 để chạy.

Giao diện VBA. Ảnh: Tác giả.

Mẹo: Bạn không cần viết VBA từ đầu. Hãy mô tả điều bạn muốn bằng ngôn ngữ tự nhiên và để ChatGPT tạo một macro phát hiện tùy chỉnh.

Đây là ví dụ mã VBA: 

Option Explicit
Public Sub HighlightCircularReferences_ActiveSheet()
    Dim ws As Worksheet, c As Range
    Dim hasAny As Boolean

    Set ws = ActiveSheet

    Application.ScreenUpdating = False

    ' Optional: clear previous highlighting (only on used range)
    On Error Resume Next
    ws.UsedRange.Interior.Pattern = xlNone
    On Error GoTo 0

    ' Scan formula cells only
    On Error Resume Next
    For Each c In ws.UsedRange.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0

        If IsCircularFormula(c) Then
            c.Interior.Color = RGB(255, 230, 153) ' light yellow
            hasAny = True
        End If
    Next c

    Application.ScreenUpdating = True

    If hasAny Then
        MsgBox "Circular reference cells highlighted on: " & ws.Name, vbInformation
    Else
        MsgBox "No circular references found on: " & ws.Name, vbInformation
    End If
End Sub

Private Function IsCircularFormula(ByVal startCell As Range) As Boolean
    Dim inPath As Object
    Set inPath = CreateObject("Scripting.Dictionary")

    IsCircularFormula = TraceForCycle(startCell, startCell, inPath, 0)
End Function

Private Function TraceForCycle(ByVal startCell As Range, ByVal curCell As Range, _
                               ByVal inPath As Object, ByVal depth As Long) As Boolean
    Dim prec As Range, a As Range, x As Range
    Dim key As String

    ' Safety limit to avoid deep/huge graphs freezing Excel
    If depth > 200 Then Exit Function

    key = curCell.Parent.Name & "!" & curCell.Address(False, False)

    If inPath.Exists(key) Then
        ' We hit a loop in the current path
        TraceForCycle = True
        Exit Function
    End If

    inPath.Add key, True

    ' Direct precedents (cells referenced by curCell)
    On Error Resume Next
    Set prec = curCell.DirectPrecedents
    On Error GoTo 0

    If Not prec Is Nothing Then
        For Each a In prec.Areas
            For Each x In a.Cells
                ' If any precedent is the start cell -> circular
                If x.Address(External:=True) = startCell.Address(External:=True) Then
                    TraceForCycle = True
                    Exit Function
                End If

                ' Recurse only within worksheets (avoid names/constants)
                If x.Parent Is curCell.Parent Or True Then
                    If TraceForCycle(startCell, x, inPath, depth + 1) Then
                        TraceForCycle = True
                        Exit Function
                    End If
                End If
            Next x
        Next a
    End If

    inPath.Remove key
End FunctionĐược hỗ trợ bởi

Dùng macro VBA để xác định các ô có tham chiếu vòng. Ảnh: Tác giả.

Macro này kiểm tra trang tính hiện hoạt để tìm tham chiếu vòng trong công thức. Nếu một công thức tham chiếu lại chính nó, trực tiếp hoặc qua ô khác, macro sẽ tô sáng các ô đó để bạn dễ thấy vấn đề ở đâu. Cách này vẫn hoạt động ngay cả khi bật tính toán lặp.

VBA đã tô sáng các ô có tham chiếu vòng. Ảnh: Tác giả.

Điều tra xuyên trang tính

Tham chiếu vòng trải qua nhiều trang tính khó phát hiện hơn vì mũi tên truy vết không băng qua tab.

Nó có thể trông như sau:

• Sheet A tham chiếu Sheet B
• Sheet B tham chiếu Sheet C
• Sheet C tham chiếu Sheet A

Một cách phát hiện là lập bản đồ phụ thuộc giữa các trang. Hãy ghi lại trang nào tham chiếu trang nào và tìm vòng lặp trong danh sách đó.

Bạn cũng có thể dùng Find & Replace để tìm tham chiếu xuyên trang:

• Nhấn Ctrl + H

• Nhập tên trang kèm theo ! (ví dụ, Sheet1!)

• Nhấn Find All

Excel sẽ liệt kê mọi công thức tham chiếu trang đó, giúp bạn lần theo phụ thuộc giữa các tab.

Tìm các trang liên kết trong sổ làm việc. Ảnh: Tác giả.

Lưu ý: Find & Replace không tự động phát hiện tham chiếu vòng. Nó chỉ tìm các phụ thuộc xuyên trang để bạn phân tích vòng lặp.

Xử lý sự cố với vùng đặt tên

Vùng đặt tên có thể che giấu tham chiếu vòng vì công thức không còn hiển thị địa chỉ ô trực tiếp. Để xem lại chúng:

• Mở “Name Manager” bằng Ctrl + F3
• Kiểm tra định nghĩa từng vùng đặt tên
• Tìm các vùng bao gồm những ô tham chiếu lại cùng tên vùng trong công thức

Kiểm tra các vùng đặt tên. Ảnh: Tác giả.

Vấn đề với logic điều kiện

Một số tham chiếu vòng chỉ xuất hiện trong những điều kiện cụ thể.

Ví dụ:

=IF(A1>100, A2+10, 100)Được hỗ trợ bởi

Khi A1 dưới 100, Excel dùng nhánh thứ hai và không có tham chiếu vòng. Khi A1 vượt 100, Excel đánh giá nhánh đầu và cố tính A2 bằng chính A2, kích hoạt tham chiếu vòng.

Để phát hiện các vấn đề này:

• Thử các giá trị đầu vào khác nhau để buộc đi qua mọi nhánh điều kiện
• Theo dõi cảnh báo tham chiếu vòng chỉ xuất hiện trong một số điều kiện

Để sửa, hãy chuyển logic vòng sang các ô phụ riêng và đơn giản hóa các IF lồng nhau thành các phép tính rõ ràng, theo từng bước.

Cách phòng tránh tham chiếu vòng trong Excel

Một vài thói quen từ sớm có thể tiết kiệm hàng giờ gỡ lỗi về sau khi sổ làm việc phình to hoặc được chia sẻ với người khác. 

Vì vậy, đây là một số thực hành tốt bạn nên tuân theo: 

Lập kế hoạch sổ làm việc và thiết kế công thức

Cấu trúc tốt ngăn hầu hết tham chiếu vòng ngay từ đầu. Hãy đảm bảo thực hiện:

• Tách đầu vào, tính toán và đầu ra để công thức đi theo một hướng rõ ràng.

• Dùng ô/cột phụ thay vì làm mọi thứ trong một công thức.

• Các phép tính nên phụ thuộc vào giá trị trước đó, không phải kết quả của chính chúng hoặc các tổng ở phía sau.

• Giữ ô nhập liệu cách xa các tổng và phần tổng hợp để tránh vùng tự tham chiếu.

Sao chép-dán và quản lý tham chiếu

Nhiều tham chiếu vòng được đưa vào trong quá trình sao chép-dán. Vì vậy, hãy cân nhắc: 

• Dán công thức vào một ô trước và kiểm tra Formula Bar trước khi sao chép trên phạm vi rộng.

• Dùng tham chiếu tuyệt đối (ví dụ, $A$1) cho các tỷ lệ và giả định không nên thay đổi vị trí.

• Không đặt công thức tổng hợp bên trong phạm vi mà chúng tính.

• Tên rõ ràng giúp dễ hiểu công thức đang tham chiếu gì và giảm lỗi khi phạm vi mở rộng.

Tài liệu hóa và kiểm tra định kỳ

Tài liệu nhẹ nhàng cũng rất hiệu quả. Vì thế bạn cần ghi nhớ khi làm việc:

• Thêm chú thích ngắn tại ô hoặc duy trì một trang tài liệu đơn giản.

• Rà soát công thức bằng công cụ Formula Auditing trước các buổi trình bày quan trọng hoặc khi bàn giao.

• Lưu phiên bản các tệp quan trọng để lần ra thời điểm phát sinh vấn đề.

• Trong tệp cộng tác, hãy kiểm tra thay đổi thường xuyên để lỗi nhỏ không lan rộng.

Lời kết

Nếu bạn đang gặp tham chiếu vòng, hãy xác nhận đó là vô tình hay có chủ đích. Quyết định này sẽ dẫn đến việc bạn nên tái cấu trúc công thức, bật tính lặp, hay thiết kế lại một phần mô hình.

Đừng vội điều chỉnh cài đặt hay dùng mẹo tạm thời trước khi chắc chắn logic bản thân có ý nghĩa.

Khi đã xử lý xong, hãy dành chút thời gian để tệp dễ dùng hơn về sau. Thêm ghi chú ngắn cho các công thức quan trọng, lưu một bản sạch của sổ làm việc, và đảm bảo mọi người khác sử dụng tệp đều hiểu cách các phép tính được thiết kế hoạt động.