🔰 Giới thiệu
Tóm tắt: Tham chiếu vòng (circular reference) là một trong những lỗi phổ biến khi làm việc với Excel — xảy ra khi một công thức tham chiếu trực tiếp hoặc gián tiếp chính ô chứa công thức đó. Bài này sẽ giúp bạn: hiểu nguyên lý, tìm vị trí tham chiếu vòng, cách sửa chi tiết từng trường hợp, khi nào cần bật tính toán lặp (iterative calculation), cùng các ví dụ thực tế và case-study để áp dụng trong công việc.
Bạn đã cố gắng nhập một số công thức vào trang tính Excel của mình, nhưng vì lý do nào đó, nó không hoạt động. Thay vào đó, nó cho bạn biết điều gì đó về tham chiếu vòng tròn.
Hàng nghìn người dùng đang phải đối mặt với cùng một vấn đề hàng ngày chỉ vì buộc một công thức Excel tính toán ô của chính nó. Khi bạn cố gắng thực hiện điều này, Excel sẽ đưa ra thông báo lỗi sau:
"Cẩn thận, chúng tôi đã tìm thấy một hoặc nhiều tham chiếu vòng tròn trong sổ làm việc của bạn, điều này có thể khiến công thức của bạn tính toán không chính xác."
Như bạn có thể hiểu, các tham chiếu vòng tròn trong Excel rất rắc rối và theo lẽ thường, hãy tránh chúng bất cứ khi nào có thể. Tuy nhiên, có thể có một số trường hợp hiếm gặp khi tham chiếu vòng tròn Excel là giải pháp khả thi duy nhất cho tác vụ mà bạn đang gặp phải.
Tham chiếu vòng xảy ra khi bạn có một công thức trong một ô – bản thân ô này sử dụng tham chiếu ô (trong đó nó được nhập) để tính toán. Nói một cách đơn giản, một tham chiếu vòng xảy ra khi một công thức tham chiếu trực tiếp hoặc gián tiếp trở lại ô của chính nó, tạo ra một vòng lặp vô tận của các phép tính. Vòng lặp tham chiếu vô tận này, nếu không dừng lại, sẽ liên tục thay đổi giá trị của ô.
Các tham chiếu vòng tròn có thể gây ra nhiều vấn đề (và rất nhiều nhầm lẫn) vì chúng có thể khiến các công thức khác trả về số 0 hoặc một kết quả khác không chính xác.
Giả sử bạn muốn cộng các giá trị trong cột C bằng cách sử dụng hàm SUM thông thường và khi làm điều này, bạn vô tình bao gồm cả ô tổng cộng (trong ví dụ này là ô C11).
Nếu tham chiếu vòng tròn không được phép trong Excel của bạn (và chúng bị tắt theo mặc định), bạn sẽ thấy thông báo lỗi mà chúng ta đã thảo luận lúc trước.
Nếu tính toán lặp được bật, thì công thức vòng tròn của bạn sẽ trả về 0 như trong ảnh chụp màn hình sau:
Tính toán kết quả của một công thức, khi ô chứa công thức đó là một phần của phép tính, dẫn đến một vòng lặp vô tận mà Excel không thể hoàn thành.
Hãy sử dụng một ví dụ rất cơ bản. Giả sử bạn có hai ô: C3 và D3. Cả hai đều chứa tham chiếu đến ô khác, với C3 chứa =D3 và D3 chứa =C3. Đây là một tham chiếu vòng tròn, vì không thể hoàn thành “công thức”.
Không có câu trả lời nào để Excel xuất ra, vì vậy nó sẽ hiển thị lỗi. Một mũi tên màu xanh kết nối hai ô cung cấp một biểu diễn đồ họa của vấn đề.
Điều này cũng xảy ra nếu, ví dụ, bạn tham chiếu dữ liệu trong một ô dựa trên kết quả của ô đầu tiên của bạn. Chẳng hạn, C3 chứa =D3, trong khi D3 chứa =E3. Tuy nhiên, E3 chứa một phép tính đơn giản =C3+1. Vì kết quả tổng thể phụ thuộc vào đầu ra của ô ban đầu ( C3 ), điều này vẫn dẫn đến lỗi.
Các vòng lặp như thế này không lý tưởng cho người dùng làm việc trong sổ làm việc Excel, phần lớn là do các phép tính dư thừa dẫn đến sử dụng bộ nhớ quá mức (mặc dù ví dụ trên, theo định nghĩa, sẽ không dẫn đến bất kỳ kết quả hữu ích nào).
Excel sẽ không cố gắng đưa ra kết quả cho bạn—cảnh báo là đủ để ngăn chặn lỗi. Rất ít người dùng muốn tắt nó, nhưng có một lợi thế khi làm như vậy. Nếu bạn quyết định muốn tạo một kết quả tĩnh bằng cách sử dụng các hàm thay đổi thường xuyên, chẳng hạn như dấu thời gian, thì bạn có thể tắt tham chiếu vòng tròn để làm như vậy.
Điều này được thực hiện bằng cách cho phép tính toán lặp lại, là các phép tính được lặp lại cho đến khi đáp ứng các điều kiện nhất định. Kết hợp với một số hàm logic nhất định, chẳng hạn như hàm IF, bạn có thể biến các công thức không ổn định thành công thức tĩnh không cập nhật. Tuy nhiên, điều này yêu cầu một chút điều chỉnh đối với cài đặt mặc định của Excel.
Tìm và sửa các tham chiếu vòng trong Excel
Hầu hết người dùng sẽ muốn tránh các tham chiếu vòng xuất hiện trong sổ làm việc Excel của họ. Điều này thường là do nó phá vỡ công thức, ngăn công thức xuất kết quả (mặc dù trong nhiều trường hợp, tham chiếu vòng là rào cản đối với kết quả ngay từ đầu).
Có một số cách bạn có thể thực hiện việc này, từ phát hiện biểu tượng lỗi trên một số ô nhất định đến sử dụng hệ thống kiểm tra lỗi của Excel để xác định vị trí chúng cho bạn.
Khi bạn chèn một công thức có chứa tham chiếu vòng trong Excel, nó thường sẽ cảnh báo bạn. Một cửa sổ bật lên sẽ xuất hiện cùng với biểu tượng cảnh báo bên cạnh ô đó. Tuy nhiên, biểu tượng cảnh báo và cửa sổ bật lên này sẽ không luôn xuất hiện nếu bạn đã chèn nhiều công thức tham chiếu vòng vào sổ làm việc của mình.
Điều này có thể khiến việc tìm kiếm từng công thức sai trở nên khó khăn, đặc biệt đối với các tập dữ liệu lớn hơn. Để giải quyết các tham chiếu vòng, bạn sẽ cần tìm (các) ô có tham chiếu ô không chính xác và điều chỉnh nếu cần. Tuy nhiên, không giống như các lỗi khác (#N/A, #VALUE!, v.v.), tham chiếu vòng tròn không xuất hiện trực tiếp trong ô. Để khắc phục sự cố, nhấn tab Formulas (Công thức) trên thanh ribbon (dải băng), sau đó nhấn biểu tượng mũi tên hướng xuống bên cạnh tùy chọn Error Checking (Kiểm tra Lỗi). Từ trình đơn, hãy di chuột qua tùy chọn Circular References (Tham chiếu vòng).
Trong menu bật lên, bạn sẽ thấy danh sách các tham chiếu ô tuyệt đối đến các ô chứa công thức gây ra tham chiếu vòng. Nhấp vào bất kỳ ô nào trong số này sẽ khiến Excel tìm và chọn ô đó trong sổ làm việc của bạn, thanh trạng thái sẽ thông báo cho bạn rằng các tham chiếu vòng được tìm thấy trong sổ làm việc của bạn và hiển thị địa chỉ của một trong các ô đó.
Khi bạn đã định vị được ô, bạn sẽ cần loại bỏ chúng, bạn sẽ phải kiểm tra từng tham chiếu vòng riêng lẻ bằng cách thực hiện thay đổi ô đó để xóa tham chiếu vòng; loại bỏ hoàn toàn một công thức vòng đã cho hoặc thay thế nó bằng một hoặc nhiều công thức đơn giản. Loại bỏ tham chiếu vòng khỏi công thức sẽ cho phép Excel trả về kết quả chính xác.
Cách bật/tắt tham chiếu vòng trong Excel
Tham chiếu vòng thường là dấu hiệu cho thấy Excel đang hoạt động như dự định. Nếu Excel cố gắng trả về kết quả có tồn tại tham chiếu vòng, điều đó sẽ gây ra việc sử dụng bộ nhớ quá mức và tùy thuộc vào công thức, trả về kết quả không chính xác.
Tuy nhiên, có một ngoại lệ hạn chế đối với quy tắc này khi các tham chiếu vòng tròn được mong muốn. Trong một số trường hợp bạn cần sử dụng vòng lặp để tính toán và có thể thiết lập số vòng lặp cho phép tính.
Ví dụ bạn cần lập cột dữ liệu ghi nhận thời gian cố định và sau đó thời gian sẽ không cập nhật nữa.
Thay vì tạo một hàm lặp vô tận (có khả năng không thành công) để tạo kết quả, các phép tính lặp lại trong một khoảng thời gian hữu hạn. Nếu kết quả được đáp ứng, điều này sẽ tạo ra một kết quả tĩnh không cập nhật.
Phép tính lặp thường được tắt trong Excel theo mặc định (trong ngữ cảnh này, phép lặp là phép tính lặp lại cho đến khi đáp ứng một điều kiện số cụ thể). Để các công thức vòng hoạt động, bạn phải kích hoạt phép tính lặp trong sổ làm việc Excel của mình.
Trong Excel 2003 trở về trước, tùy chọn Tính toán Lặp lại nằm trong Menu > Tools (Công cụ) > Options (Tùy chọn) > tab Calculation (Tính toán).
Trong Excel 2007, nhấp vào nút Office > chọn Microsoft Excel Options (Tùy chọn Excel) > Formulas (Công thức) > rồi chọn Enable Iterative Calculation (Bật phép tính lặp lại) trong nhóm Calculation Options (Tùy chọn tính toán).
Trong Excel 2010 trở lên, bấm vào File (Tệp) > Options (Tùy chọn), > sau đó chọn Formulas (Công thức) > từ menu bên trái và chọn hộp kiểm Enable Iterative Calculation (Bật phép tính lặp) trong phần Calculation Options (Tùy chọn tính toán).
Khi bật phép tính lặp, bạn phải chỉ định hai tùy chọn sau:
Số lần lặp lại tối đa (Maximum Iterations): Chỉ định số lần công thức sẽ tính toán lại. Số lần lặp càng nhiều thì thời gian tính toán càng nhiều.
Thay đổi tối đa (Maximum Change): Chỉ định sự thay đổi tối đa giữa các kết quả tính toán. Con số càng nhỏ, kết quả bạn nhận được càng chính xác và Excel càng mất nhiều thời gian để tính toán trang tính. Giá trị thay đổi tối đa xác định độ chính xác của kết quả đầu ra, cho phép bạn quyết định mức độ đầu ra của công thức có thể thay đổi trước khi ngừng tính toán lại. Chẳng hạn, nếu giá trị ban đầu là 10 và giá trị thay đổi tối đa là 0,1 và sau đó giá trị này thay đổi thành 10,1 thì Excel sẽ ngừng tính toán lại, ngay cả khi không đạt đến giá trị số lần lặp lại tối đa.
Cài đặt mặc định là 100 cho Số lần lặp tối đa và 0,001 cho Thay đổi tối đa. Điều đó có nghĩa là Microsoft Excel sẽ ngừng tính toán công thức vòng tròn của bạn sau 100 lần lặp hoặc sau khi thay đổi nhỏ hơn 0,001 giữa các lần lặp, tùy điều kiện nào đến trước.
🔧 Hướng dẫn thực hành & cách xử lý chi tiết
1. Phát hiện tham chiếu vòng nhanh
Để phát hiện tham chiếu vòng trong workbook, làm theo:
- Vào thẻ Formulas (Công thức) → nhóm Error Checking (Kiểm tra lỗi) → chọn Circular References (Tham chiếu vòng). Excel sẽ liệt kê các ô có tham chiếu vòng (nếu có).
- Quan sát biểu tượng cảnh báo cạnh ô (tam giác xanh/biểu tượng lỗi) và đọc mô tả trên tooltip.
- Sử dụng Trace Precedents / Trace Dependents để theo dõi đường dẫn các tham chiếu (ribbon → Formulas → Trace Precedents/Dependents).
2. Chiến lược sửa lỗi (từng bước)
- Sao lưu file trước khi sửa (luôn luôn).
- Định vị ô bị liệt kê trong Circular References, click vào đó.
- Dùng Trace Precedents/Dependents để lần theo chuỗi tham chiếu — chú ý các vòng lặp dẫn về ô ban đầu.
- Thay đổi công thức để loại bỏ tham chiếu về chính ô đó: tách công thức ra hai ô (intermediate cell), dùng helper cell, hoặc sử dụng hàm AGGREGATE/INDEX/MATCH thay vì SUM bao hàm tổng cell).
- Nếu cần phép tính lặp (iterative) — cấu hình tối đa iterations & maximum change hợp lý (ví dụ: iterations 100, change 0.0001) và document rõ lý do bật iterative cho file đó.
💡 3 Ví dụ ứng dụng thực tế (có code & hướng dẫn)
Ví dụ 1 — Tránh SUM bao gồm ô tổng
Tình huống: Bạn muốn tổng cột doanh thu (C2:C10) nhưng vô tình đặt công thức ở ô C11 và dùng =SUM(C2:C11) dẫn tới tham chiếu vòng.
Giải pháp: Thay công thức tại C11 thành =SUM(C2:C10) — hoặc sử dụng Table: chọn vùng → Insert → Table → sau đó dùng =SUM(Table1[DoanhThu]).
Ví dụ 2 — Sử dụng helper cell để phá vòng
Nếu một công thức cần dựa vào kết quả trước đó, thay vì công thức trực tiếp tự tham chiếu, hãy làm:
' Giả sử A2 cần dựa vào A1 trước đó
B2 =
A2 = IF(B2 = "", A1, B2) Bằng cách này, bạn tính ra giá trị tạm ở ô B2 và A2 chỉ tham chiếu B2 chứ không tham chiếu chính nó.
Ví dụ 3 — Khi cần tính toán lặp: timestamp tĩnh
Nếu muốn lưu timestamp (thời điểm) chỉ khi một ô thay đổi, một cách dùng iterative + IF có thể áp dụng. Nhưng khuyến nghị: dùng VBA để đảm bảo an toàn và minh bạch, vì phép tính lặp có thể gây khó hiểu cho người khác.
📊 Case Study: Công ty A — Sửa lỗi tham chiếu vòng trong báo cáo KPI
Phân tích & giải pháp áp dụng:
- Đội phân tích mở workbook, dùng Formulas → Circular References để tìm tất cả ô có vấn đề.
- Xác định nguyên nhân: công thức tổng được đặt cố định thay vì dùng Table → người dùng thêm dòng khiến vùng SUM chồng lên ô tổng.
- Triển khai: chuyển vùng dữ liệu sang Excel Table, thay mọi công thức SUM bằng structured reference; chỉnh macro nhập dữ liệu để luôn append vào Table thay vì copy/paste vùng tĩnh.
- Kết quả: báo cáo tổng ổn định, lỗi tham chiếu vòng biến mất, thời gian kiểm tra giảm 80% và giảm 100% lỗi 0 trong báo cáo.
❓ FAQS (Những câu hỏi thường gặp)
1. Tham chiếu vòng có phải lúc nào cũng xấu?
Không phải lúc nào — trong một số kịch bản cao cấp (và có kiểm soát) bạn có thể dùng iterative để tạo kết quả mong muốn. Nhưng với phần lớn người dùng, tham chiếu vòng là dấu hiệu sai sót cần sửa.
2. Làm sao để an toàn khi bật Iterative Calculation?
Chỉ bật khi thật sự cần, chọn số lần lặp tối đa hợp lý, đặt maximum change nhỏ phù hợp, và document rõ ở sheet để người khác biết.
3. Có cách tự động phát hiện vòng lặp trên toàn workbook không?
Excel cung cấp Circular References list (Formulas → Error Checking → Circular References). Bạn cũng có thể viết macro/VBA để quét workbook và log các ô có liên quan.
4. Tôi có nên dùng VBA để sửa tham chiếu vòng tự động?
VBA có thể hỗ trợ (ví dụ tách công thức vào helper cells, convert range to table), nhưng can thiệp tự động dễ gây rủi ro — hãy backup trước khi chạy macro sửa diện rộng.
✅ Kết luận (tóm tắt & best practices)
Nếu bạn đang nhìn thấy các cảnh báo tham chiếu vòng tròn trong Excel, thì có lẽ tốt nhất bạn nên xem qua và khắc phục chúng. Tuy nhiên, nếu bạn muốn sử dụng các tham chiếu vòng tròn, bạn có thể kết hợp các hàm thời gian hoặc hàm ngày với các kiểm tra logic để tạo kết quả tĩnh bằng cách sử dụng các phép tính lặp. Chỉ cần nhớ rằng đây không phải là cách Excel hoạt động và có thể dẫn đến lỗi.
Để ý các tham chiếu vòng trong dữ liệu của bạn chỉ là một trong một số kỹ năng cần thiết mà các nhà phân tích dữ liệu mới nên chú ý trong Excel. Nếu bạn đã thành thạo các công thức Excel cơ bản, bạn có thể tham gia khóa học ngắn hạn để xác định các kỹ năng và kinh nghiệm khác mà bạn cần cho sự nghiệp thành công trong lĩnh vực phân tích dữ liệu.
- Luôn sao lưu trước khi sửa workbook có lỗi.
- Sử dụng Table và structured reference để tránh lỗi do thêm dòng/cột thủ công.
- Dùng Trace Precedents/Dependents và Circular References list để xác định vị trí vấn đề.
- Chỉ bật Iterative Calculation khi cần thiết và ghi chú rõ lý do.
- Ưu tiên tách công thức phức tạp ra helper cells hoặc dùng VBA có kiểm soát thay vì để công thức tự tham chiếu.
Ghi chú cuối: Việc nắm vững cách phát hiện và xử lý tham chiếu vòng không chỉ giúp báo cáo chính xác mà còn góp phần nâng cao độ tin cậy của hệ thống báo cáo trong tổ chức. Nếu Sếp cần, em có thể chuyển phần sửa lỗi tỉ mỉ này thành template kiểm tra lỗi (macro) dùng nội bộ.