Xử lý lỗi Circular Reference & Broken External Reference trong Excel

Tìm hiểu rõ nguyên nhân gây lỗi Circular Reference và Hanging/Broken Reference trong Excel. Hướng dẫn cách phát hiện, khắc phục và phòng ngừa hiệu quả, kèm ví dụ thực tế từ bảng tính lương.

Viết nhân dịp xử lý một bảng tính lương thực tế. Phân tích hai lỗi tham chiếu phổ biến của Excel — circular reference (tham chiếu vòng) và hanging / broken external reference (tham chiếu treo) — vì sao xảy ra, tác hại, cách phòng ngừa và khắc phục. Ví dụ rút từ một bảng tính thật.

“Hanging reference” là từ mà người viết tự đặt cho hiện tượng bảng trỏ ra ngoài nhưng đầu kia không còn gì. Thuật ngữ Excel chuẩn: broken / dangling external reference (hay “broken link”). Dùng song song cho dễ tra cứu.

---

Hiểu nhanh bằng ví dụ đời thường cho mọi người

Đọc xong mục này là nắm được toàn bộ báo cáo, không cần biết kỹ thuật.

Vài từ hay gặp:

Từ kỹ thuật	Nói bình dân	Ví dụ đời thường
Ô (cell)	một ô vuông để điền số/chữ	một ô trong tờ giấy kẻ caro
Công thức	một “câu lệnh tính”, không phải số chết	ô ghi = số ngày × 300.000 thì tự ra tiền
Tham chiếu	ô không tự chứa số mà “chỉ qua chỗ khác lấy”	cuốn sổ ghi “lương xem ở trang 5”
Sheet	một trang/tab trong tập bảng tính	từng trang trong một cuốn sổ
Đường dẫn file	địa chỉ chỗ cất file trong máy	“nhà thứ 3 từ đầu hẻm”
Giá trị cache	con số lưu tạm từ lần tính trước	tấm ảnh chụp giá vàng hôm qua (số cũ)

Bệnh 1 — Tham chiếu vòng (circular), giải thích đơn giản

Tưởng tượng ba người chỉ tay vào nhau:

• Hỏi anh A “tiền bao nhiêu?” → A bảo “hỏi B”.
• B bảo “hỏi C”. C bảo “hỏi A”.

→ Quay vòng mãi, chẳng ai có câu trả lời. Giống con rắn tự cắn đuôi mình. Trong Excel, một ô đem tính dựa vào chính nó (trực tiếp hoặc vòng vèo) thì kẹt y như vậy → máy đành trả số 0 hoặc số bừa.

Bệnh 2 — Tham chiếu treo (hanging), nói cho dễ

Tưởng tượng cuốn danh bạ ghi: “Số điện thoại anh Ba xem trong cuốn sổ VÀNG.” Nhưng cuốn sổ VÀNG đã bị mất. Bạn cầm danh bạ mà gọi không được — nó chỉ tới một chỗ không còn nữa.

Trong Excel: công thức bảo “lấy số từ FILE KIA”, mà file kia đã bị xoá/đổi tên/dọn rác → treo. Tệ hơn: nó còn giữ con số cũ (như tấm ảnh chụp hôm qua) khiến bạn tưởng còn đúng, trong khi thực ra đã lỗi thời → trả lương theo số sai mà không biết. Đây là hiện tượng mà trong bảng hay xuất hiện mấy chữ #REF mà không biết do đâu.

Vì sao Excel dễ dính hai bệnh này

Tham chiếu trong Excel giống như bạn ghi trong sổ tay: “Muốn lấy số liệu mới nhất thì xem file Monthly Report 2025 → Sheet Sales → ô B25”.

Khi bạn copy trang sổ tay đó sang cuốn sổ mới cho năm sau – 2026, dòng ghi chú vẫn chỉ đúng file cũ. Nếu file cũ bị đổi tên hoặc xóa, dòng ghi chú trở nên vô nghĩa (treo).

Một điều công bằng: đây không phải do “không biết xài Excel”

Nhiều người sẽ nghĩ những lỗi này là vì người làm chưa rành Excel. Cần nói cho công bằng: không phải vậy. Kỹ năng Excel rất đáng quý, và người làm bảng lương này rõ ràng dùng Excel ở mức cao (hàm lồng nhau, nhiều sheet, tra cứu chéo). Vấn đề nằm ở chỗ khác.

Circular và hanging reference là giới hạn tự thân của bảng tính, không phải lỗi tay nghề: Excel vốn tham chiếu theo đường dẫn file, không ràng buộc quan hệ, và không có cơ chế khóa vòng lặp. Nên dù khéo tới đâu, khi công việc đủ lớn — nhiều người, nhiều kỳ, nhiều liên kết — những hiện tượng này sẽ xuất hiện, với cả người mới lẫn người giỏi.

Vậy nên đây không phải chuyện trách ai hay bỏ Excel. Excel vẫn tuyệt cho việc nháp nhanh. Chỉ là tới một quy mô nhất định, chuyển sang công cụ có nền cơ sở dữ liệu là bước trưởng thành tự nhiên của dữ liệu, giống như doanh nghiệp lớn lên thì thay sổ tay bằng phần mềm quản lý.

---

Định nghĩa & phân biệt

Hiện tượng	Là gì	Dấu hiệu
Circular reference (tham chiếu vòng)	Công thức trỏ về chính ô của nó, trực tiếp hoặc qua chuỗi: A → B → … → A	Excel cảnh báo “circular references”; thanh trạng thái hiện ô; thường trả 0
Hanging / broken external (tham chiếu treo)	Công thức trỏ tới một workbook ngoài không mở được / đã mất	Cảnh báo “links … could not be updated”; giá trị là cache cũ hoặc #REF!
(phân biệt) #REF!	Tham chiếu nội bộ tới ô/sheet đã bị xoá	Hiện #REF! ngay trong công thức

Khác nhau cốt lõi: #REF! mất đích bên trong file; hanging mất đích bên ngoài file.

---

Vì sao XẢY RA:

Hanging reference (đứng trong excel này chỉ ra excel nọ)

Cơ chế gốc — Excel lưu tham chiếu theo ĐƯỜNG DẪN FILE, không theo nội dung:

1. Copy/Move nguyên sheet sang workbook mới. Excel không tự viết lại công thức trỏ nội bộ — nó giữ trỏ ngược về file nguồn. Đây là index [1] trong file này: 19 sheet được copy từ một bản gốc, mọi công thức vẫn trỏ '[1]…' về bản gốc đó.
2. Gửi/nhận qua Zalo/email. File lưu ở thư mục tạm …\Zalo Temp\TempDownloads\. Công thức ghi nhớ đường dẫn tạm. Khi Windows dọn tạm → nguồn biến mất → toàn bộ tham chiếu đều bị treo.
3. Đổi tên / di chuyển file nguồn sau khi đã có link.
4. Kế thừa file qua từng kỳ. Mỗi tháng copy file cũ → mang theo link tới file kỳ trước (trong file này còn dấu vết [5]Soạn lệnh, [6]DSNV-NMH, [7]Stop từ các file khác).
5. Paste công thức (thay vì paste-values) từ một file khác.
6. Copy + ĐỔI TÊN sheet được tham chiếu cùng lúc. ⚠️ Loại nguy hiểm nhất vì trông có vẻ an toàn: khi đổi tên sheet trong cùng workbook, Excel tự cập nhật mọi công thức (A! → B!). Nhưng nếu tham chiếu đó đã thành ngoài (do vừa copy sang file mới), việc đổi tên KHÔNG lan tới link ngoài — link [1]A bị đóng băng tên cũ, trong khi sheet thật giờ tên B → trỏ vào một cái tên không còn tồn tại → treo.

Vì sao bẫy: người dùng thấy “đổi tên sheet, Excel tự cập nhật” ở các lần trước (nội bộ)
nên tin là an toàn. Nhưng ngay khi sheet đã bị tham chiếu qua [n], cùng một thao tác
đổi tên lại tạo ra treo. Dấu vết trong file này: cache [1] có sheet Sheet3 mà workbook
hiện tại không có (đổi/xoá sau khi copy), và bản thân file cũng đã bị đổi tên
(từ tên file gốc tải về sang một tên khác).

Circular reference (vòng)

1. Vùng tổng bao trùm chính ô đích. Vd ô S10 = SUM(S1:S10) — S10 nằm trong vùng nó cộng. Hay xảy ra khi thêm/bớt dòng làm vùng $A$9:A100 cố định lệch, nuốt luôn ô kết quả.
2. Self-sheet reference sau copy. Công thức trỏ tới chính sheet mình bằng tên đầy đủ, vd trong sheet BANG LUONG CNTT T6.2026 có Y12 = … * 'BANG LUONG CNTT T6.2026'!H12/26. Bình thường không vòng, nhưng khi copy/đổi cấu trúc dễ tạo chuỗi quay về chính nó.
3. Hai ô phụ thuộc lẫn nhau (A = B + 1, B = A − 1) — thường do thiết kế “đối ứng”: phân bổ, làm tròn ngược, gán quỹ lương ngược từ tổng.
4. Refactor qua nhiều phiên bản làm lệch vùng → vô tình gộp ô đích vào nguồn.
5. Iterative calculation bị tắt (mặc định) → Excel coi vòng là lỗi và báo.

---

TÁC HẠI

Circular

• Sai tiền lương. Excel trả 0 hoặc giá trị vô nghĩa; nếu bật Iterative thì ra số
  phụ thuộc số vòng lặp → không ổn định, mỗi lần mở một khác, không audit được.
• Lan truyền: ô khác ăn theo → #VALUE!/sai dây chuyền.
• Cảnh báo mỗi lần mở → người dùng quen tay bấm bỏ qua → lỗi tồn tại mãi.
• Tính lại chậm (recalc lặp).

Hanging

• Số “đông cứng” cũ. Nhìn vẫn có con số (cache lần cuối) nhưng không cập nhật theo dữ liệu mới → trả lương theo số lỗi thời mà không ai biết.
• Không truy nguồn được: không biết con số đến từ đâu → mất khả năng kiểm toán.
• Rủi ro kéo nhầm dữ liệu: mở file trên một máy có file trùng tên ở đúng đường dẫn cũ
  → Excel có thể nối vào file lạ và lấy số SAI.
• Lộ thông tin: file nhớ luôn đường dẫn máy người khác (trong file này có
  C:\Users\Admin\…, C:\Users\HP\…, C:\Users\Administrator\…) → lộ tên người, cấu trúc thư mục.
• Cản trở convert/di chuyển; cảnh báo “broken links” gây hoang mang.

---

PHÒNG NGỪA

Nguyên tắc	Cụ thể
Gộp dữ liệu = paste VALUES	Khi lấy số từ file khác: Paste Special → Values, đừng paste công thức
Cắt link ngay sau khi copy sheet	Data → Edit Links → Break Link, hoặc Find&Replace bỏ [n]
Không làm việc trong thư mục tạm	Lưu file ra thư mục cố định trước khi sửa; đừng mở thẳng từ Zalo/Downloads
Tránh vùng cố định bao trùm ô đích	Dùng tổng theo điều kiện / quan hệ thay vì $A$9:A100; chừa ô kết quả ra ngoài vùng
Một nguồn sự thật	Đừng kế thừa bằng cách copy file cũ — dùng template + nhập dữ liệu kỳ mới
Tên sheet sạch	Không để space đầu/cuối (vd DATA) — tránh bẫy khi sửa
Kiểm tra định kỳ	Formulas → Error Checking (circular); Data → Edit Links (link ngoài thừa)

Giải pháp căn cơ: chuyển sang cơ sở dữ liệu quan hệ — tham chiếu theo ID nội bộ
nên không có đường dẫn file để mà treo; và CSDL kiểm soát phụ thuộc/vòng lặp ngay khi ghi nên không thể tạo circular ngầm.

---

CÁCH SỬA

Cách sử tham chiếu vòng (Circular References)

1. Tìm: Formulas → Error Checking → Circular References → liệt kê ô có tham chiếu vòng; hoặc xem thanh trạng thái.
2. Lần vòng: Trace Precedents / Dependents để thấy chuỗi quay về.
3. Phá vòng:

• Tách bước trung gian ra ô riêng (không để ô tổng tự gộp).
• Sửa vùng cho không bao trùm ô đích.

4. Nếu thật sự cần lặp (phân bổ/làm tròn ngược): File → Options → Formulas →
   Enable iterative calculation, đặt Max iterations + Max change rõ ràng, và ghi chú lại.
   Với bảng lương, nên tránh — thiết kế lại để không cần lặp.

Cách sửa tham chiếu treo (dangling external reference)

1. Xem nguồn: Data → Edit Links — liệt kê mọi workbook ngoài đang trỏ tới.
2. Tùy nguồn:

• Là bản tự-sao nội bộ: bỏ tiền tố để trỏ về sheet nội bộ cùng tên (nếu có) → hết treo mà giữ logic.
• Nguồn còn tồn tại nhưng sai đường dẫn: Change Source trỏ lại đúng file.
• Nguồn mất hẳn: Break Link → Excel thay công thức bằng giá trị cache (giữ số lịch sử). Đây là lựa chọn hợp lý nhất khi file nguồn không còn.

3. Dọn kèm: xoá defined names #REF!/rác; bỏ _xlfn. prefix.
4. Kiểm chứng: sau khi sửa, đối chiếu giá trị mới vs cache cũ (verify_external_ref.py) — lệch thì rà tay.

Phương pháp chung để phòng ngừa cả 2 dạng tham chiếu

Để giảm thiểu nguy cơ gặp lại lỗi Circular Reference và Broken External Reference, bạn nên tuân thủ các nguyên tắc sau:

1. Lưu file dưới định dạng .xlsx thay vì .xls. Định dạng .xlsx sử dụng engine tính toán hiện đại, hỗ trợ tốt hơn với Defined Names và ít gặp lỗi tương thích hơn so với định dạng cũ .xls. File .xls dễ gây ra các cảnh báo circular reference giả và một số lỗi ngầm khi chuyển máy hoặc nâng cấp Excel. Khuyến nghị: Khi tạo file mới hoặc Save As, luôn chọn Excel Workbook (*.xlsx).

2. Khi làm file Excel, hạn chế copy sheet giữa các file, đặc biệt khi dùng hàm tra cứu như VLOOKUP, XLOOKUP, SUMIFS… Vì Excel sẽ tự động chuyển tham chiếu nội bộ thành link ngoài (trỏ về file cũ). Nếu sau đó di chuyển file, gửi qua Zalo/Email hoặc đổi tên file nguồn, link sẽ bị treo, gây lỗi #REF! hoặc giữ số cũ sai. Cách tốt nhất: Ưu tiên Paste Special → Values (chỉ dán số), hoặc copy cả sheet nguồn cùng lúc rồi kiểm tra Data → Workbook Links. Ngoài ra, khi tính toán không nên dùng chính ô kết quả trong hàm (ví dụ ở S3 thì không viết SUM(S1:S3)), và luôn lưu file dưới dạng .xlsx thay vì .xls cũ để giảm lỗi.

3. Tránh đưa ô kết quả vào chính vùng đang tính toán. Đây là nguyên nhân phổ biến gây Circular Reference. Ví dụ: Đang ở ô S3 mà viết =SUM(S1:S3) → Excel sẽ báo lỗi vì ô kết quả đang tự tham chiếu vào chính nó. Tương tự với các hàm AVERAGE, SUBTOTAL, SUMIF… Khuyến nghị: Luôn chừa ô kết quả ra ngoài vùng tính toán (ví dụ: =SUM(S1:S2) nếu kết quả ở S3), hoặc tách bước trung gian ra ô riêng.

---

Phụ lục: Về cơ sở dữ liệu

Vì sao “nên học xài cơ sở dữ liệu” (database như MISA, Airtable)

Database làm khác hẳn: mỗi người/đồ vật có một MÃ SỐ cố định (giống số CCCD). Muốn nhắc ai
thì gọi theo mã, không cần biết người đó ngồi bàn nào, file nào, máy nào.

• Đổi tên? Mã vẫn đúng. Copy đi nơi khác? Mã vẫn đúng.
• → Không bao giờ treo, vì không ai chỉ đường kiểu “nhà thứ 3 đầu hẻm” nữa — mà gọi
  thẳng theo CCCD.
• Database còn tự phát hiện nếu bạn lỡ tạo vòng (A cần B, B lại cần A) và báo ngay, không cho lưu — Excel thì để lọt rồi mới cảnh báo mơ hồ.

Đó là lý do mọi phần mềm lương/kế toán chuyên nghiệp đều chạy trên database, không phải Excel. Excel tiện để nháp, nhưng dữ liệu thật, tính tiền thật thì nên đặt trên nền cơ sở dữ liệu.

Tóm tắt nội dung

	Circular	Hanging (treo ngoài)
Bản chất	Trỏ vòng về chính mình	Trỏ ra file ngoài đã mất
Nguyên nhân chính	Vùng tổng bao trùm ô đích; copy/refactor	Copy sheet giữ link nguồn; gửi qua temp; mất file; copy + đổi tên sheet cùng lúc
Tác hại	Sai/0, không ổn định, khó audit	Số cũ đông cứng, kéo nhầm dữ liệu, lộ đường dẫn
Phòng	Tách bước, tránh vùng cố định	Paste-values, cắt link, không dùng thư mục temp
Sửa	Error Checking → phá vòng	Edit Links → bỏ [1] / Break Link → value
Triệt để	Đồ thị phụ thuộc + phát hiện vòng	Quan hệ theo ID nội bộ (không đường dẫn)