Tham chiếu vòng (circular reference) là công thức tham chiếu tới chính ô của nó, trực tiếp hoặc thông qua các công thức khác. Gõ =SUM(B2:B7) vào B7 là tạo ra một tham chiếu vòng: ô tổng tự cộng cả chính nó. Excel hiện cảnh báo, đặt 0 vào ô đó và ghi tên ô trong Formulas > Error Checking > Circular References. Cách sửa là đổi vùng để nó dừng trước ô chứa công thức, ở đây là =SUM(B2:B6).
B7: =SUM(B2:B7) circular: B7 is inside its own range, Excel shows 0
B7: =SUM(B2:B6) fixed: the range stops above the total
| A | B | |
|---|---|---|
| 1 | Month | Sales |
| 2 | Jan | 120 |
| 3 | Feb | 95 |
| 4 | Mar | 140 |
| 5 | Apr | 110 |
| 6 | May | 130 |
| 7 | Total | 595 |
Bấm vào B7: khung màu bao quanh B2:B6 và dừng ngay phía trên ô tổng. Đây là kiểu tham chiếu vòng phổ biến nhất. Nó thường xuất hiện khi bạn chèn một hàng ngay phía trên ô tổng rồi tự tay mở rộng vùng SUM thừa một hàng, hoặc khi kéo chuột chọn vùng lấn qua cả ô tổng.
Excel xử lý tham chiếu vòng thế nào
Khi bạn nhập công thức, Excel hiện thông báo: "There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly." Nghĩa là có công thức tham chiếu tới chính ô của nó, trực tiếp hoặc gián tiếp, nên kết quả có thể bị tính sai. Bấm OK thì công thức vẫn được giữ lại, hiện 0 (hoặc giá trị cuối cùng nó có). Sau đó:
- Thanh trạng thái ở cuối cửa sổ hiện Circular References: B7 (địa chỉ của một ô trong vòng lặp) khi trang tính đó đang mở.
- Excel không lặp lại thông báo khi bạn tiếp tục làm việc, nên một tham chiếu vòng có thể nằm trong workbook mà không ai để ý, và thanh trạng thái là thứ duy nhất nhắc bạn.
- Các công thức khác phụ thuộc vào ô đó sẽ dùng giá trị 0, nên các ô tổng phía sau bị sai mà không báo lỗi gì.
Cùng workbook đó trong Google Sheets sẽ hiện #REF! kèm ghi chú "Circular dependency detected".
Cách tìm tham chiếu vòng trong Excel
- Đọc thanh trạng thái. Nó ghi tên một ô trên trang tính đang mở. Nếu nó chỉ ghi Circular References mà không có địa chỉ, vòng lặp nằm ở một trang tính khác.
- Vào Formulas > Error Checking, bấm vào mũi tên nhỏ bên cạnh rồi trỏ tới Circular References. Menu con liệt kê các ô nằm trong vòng lặp. Bấm vào một ô để chọn nó.
- Khi đã chọn ô, dùng Formulas > Trace Precedents để vẽ mũi tên từ các ô mà nó đọc. Lần theo các mũi tên cho đến khi có một mũi tên dẫn ngược về điểm xuất phát. Remove Arrows sẽ xóa các mũi tên.
- Trên Mac, các lệnh nằm ở cùng chỗ: thẻ Formulas, Error Checking, rồi Circular References.
Sửa ô mà danh sách chỉ ra, rồi kiểm tra lại danh sách: một workbook có thể có nhiều vòng lặp, và Excel sẽ liệt kê vòng tiếp theo khi vòng đầu tiên đã được sửa.
Tham chiếu vòng gián tiếp
Vòng lặp đi qua hai ô trở lên khó nhận ra hơn, vì không có công thức nào nhắc tới chính ô của nó.
C2: =B2*10% tax on the net price in B2
B2: =D2-C2 net price = total minus tax
D2: =B2+C2 total = net plus tax
Công thức nào trông cũng hợp lý, nhưng B2 cần C2, C2 cần B2, còn D2 cần cả hai. Một trong ba giá trị phải là dữ liệu nhập vào. Hãy xác định con số nào bạn thực sự biết, gõ nó vào, rồi tính các giá trị còn lại từ nó:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Net price | Tax (10%) | Total |
| 2 | Desk | $240.00 | $24.00 | $264.00 |
| 3 | Chair | $85.00 | $8.50 | $93.50 |
Giá chưa thuế được gõ vào, còn thuế và tổng được tính từ đó: chiếc bàn có giá 24,00 là thuế. Nếu con số bạn biết lại là tổng, thì giá chưa thuế là =D2/(1+10%): công thức đã được giải theo ẩn số, nên không có gì tham chiếu ngược về chính nó.
Phần trăm trên một tổng có chứa chính nó
Một cột tỷ trọng trên tổng sẽ thành tham chiếu vòng khi ô tổng cũng cộng cả cột tỷ trọng, hoặc khi hàng tổng nằm trong vùng mà các tỷ trọng chia cho.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Sales | Share |
| 2 | North | 420 | 42% |
| 3 | South | 310 | 31% |
| 4 | East | 180 | 18% |
| 5 | West | 90 | 9% |
| 6 | Online | 0 | 0% |
| 7 | Total | 1000 | 100% |
Mỗi tỷ trọng đều chia cho B7, và B7 chỉ cộng B2:B6. Online không bán được gì, nên tỷ trọng của nó là 0%, và C7 cộng các tỷ trọng lại được 100%. Nếu B7 là =SUM(B2:B7) hoặc =SUM(B2:C6), mọi tỷ trọng đều sẽ phụ thuộc vào chính nó. Dấu $ trong $B$7 giữ cố định ô tổng khi công thức được điền xuống; xem phần trăm.
Số dư lũy kế trỏ vào chính hàng của nó
Tổng lũy kế cộng mỗi khoản mới vào số dư ở hàng phía trên. Trỏ vào số dư ngay trong cùng hàng là tạo ra vòng lặp.
C3: =C3+B3 circular
C3: =C2+B3 previous balance plus this row's amount
| A | B | C | |
|---|---|---|---|
| 1 | Date | Amount | Balance |
| 2 | 2026-03-01 | 500 | 500 |
| 3 | 2026-03-04 | -120 | 380 |
| 4 | 2026-03-09 | -80 | 300 |
| 5 | 2026-03-15 | 250 | 550 |
| 6 | 2026-03-22 | -60 | 490 |
Số dư đầu tiên chính là khoản đầu tiên; mỗi hàng sau đó cộng khoản của mình vào hàng phía trên. Số dư cuối cùng là 490. Bấm vào C4 thì các khung màu hiện C3 và B4, không bao giờ là chính C4.
Hoa hồng tính trên lợi nhuận sau hoa hồng
Có những tham chiếu vòng không phải do gõ nhầm mà là một phép tính thật sự phụ thuộc vào chính kết quả của nó: hoa hồng bằng 10% lợi nhuận, trong khi lợi nhuận là phần còn lại sau khi trả hoa hồng.
B5 (commission): =B6*B4 10% of profit
B6 (profit): =B2-B3-B5 revenue minus cost minus commission
Bật tính toán lặp (File > Options > Formulas > Enable iterative calculation, hoặc Excel > Preferences > Calculation trên Mac) cho phép Excel lặp lại vòng tính cho đến khi các con số ổn định. Ở đây cách này hiệu quả, nhưng nó cũng che giấu mọi vòng lặp vô tình trong workbook, và có những vòng lặp không bao giờ ổn định. Giải phương trình sẽ tốt hơn. Nếu hoa hồng bằng tỷ lệ nhân với (doanh thu trừ chi phí trừ hoa hồng), thì hoa hồng bằng (doanh thu trừ chi phí) nhân tỷ lệ, chia cho (1 + tỷ lệ).
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Revenue | $50,000.00 |
| 3 | Cost | $30,000.00 |
| 4 | Rate | 10% |
| 5 | Commission | |
| 6 | Profit | $20,000.00 |
| 7 | Rate of profit | $2,000.00 |
Đến lượt bạn: Viết công thức hoa hồng trong B5 mà không tham chiếu tới B5 hay B6: hoa hồng bằng 10% lợi nhuận sau hoa hồng, tức (doanh thu trừ chi phí) nhân tỷ lệ, chia cho 1 cộng tỷ lệ.
Khi B5 đúng, B7 (10% lợi nhuận) bằng đúng khoản hoa hồng trong B5: cả hai đều hiện $1.818,18. Sự bằng nhau đó chính là điều kiện mà phiên bản có tham chiếu vòng cố gắng đạt tới.
Câu hỏi thường gặp
Tham chiếu vòng trong Excel là gì?
Là công thức cần chính kết quả của nó để tính toán. Nó có thể tham chiếu tới chính ô của mình, như =SUM(B2:B7) trong B7, hoặc vòng qua các ô khác, như A1 chứa =B1+1 còn B1 chứa =A1*2. Excel không thể tính xong, nên sẽ cảnh báo bạn và hiện 0 hoặc giá trị cuối cùng.
Làm sao để tìm tham chiếu vòng trong Excel?
Nhìn vào thanh trạng thái ở cuối cửa sổ: nó ghi Circular References kèm theo một địa chỉ ô. Hoặc vào Formulas > Error Checking (mũi tên bên cạnh) > Circular References, nơi liệt kê các ô bị lỗi; bấm vào một ô để nhảy tới đó.
Vì sao Excel báo có tham chiếu vòng nhưng tôi không tìm thấy?
Thanh trạng thái chỉ hiện tham chiếu vòng của trang tính đang mở, nên hãy lần lượt chuyển qua từng trang tính và kiểm tra Formulas > Error Checking > Circular References ở mỗi trang. Vòng lặp cũng có thể đi qua một tên đã định nghĩa hoặc một ô ở trang tính khác, nên hãy dùng Trace Precedents từ ô được liệt kê để lần theo.
Có nên bật tính toán lặp để sửa tham chiếu vòng không?
Chỉ khi vòng lặp là có chủ đích, chẳng hạn một mô hình hội tụ về một giá trị. File > Options > Formulas > Enable iterative calculation khiến Excel lặp lại phép tính tối đa 100 lần thay vì cảnh báo. Với vòng lặp vô tình, nó chỉ che giấu lỗi, và kết quả có thể sai.
