Bài viết trình bày cách khắc phục lỗi tham chiếu vòng (circular reference) không thể liệt kê trong Excel. Rất đáng sợ nếu gặp lỗi tham chiếu vòng khi làm việc trên Excel. Khi làm việc với tập dữ liệu lớn chứa hàng nghìn ô, rất khó xác định ô chứa lỗi tham chiếu vòng bằng cách kiểm tra từng ô. Vì vậy, hướng dẫn này giải thích cách liệt kê lỗi tham chiếu vòng dễ dàng từ bất kỳ kích thước tập dữ liệu nào.
Bạn có thể tải file thực hành từ đây.
Tham chiếu vòng (Circular Reference) là gì?
Tham chiếu vòng là công thức trả về cùng một ô hoặc ô khác nhiều lần trong chuỗi tính toán, dẫn đến vòng lặp vô hạn làm chậm nghiêm trọng bảng tính.
Để minh họa rõ hơn, dùng tập dữ liệu sau. Tập dữ liệu gồm “Doanh số bán (Sales Amount)” 6 tháng. Giả sử cần tính tổng doanh số ở ô C11.
Giờ chọn phạm vi ô (C6:C10) trong công thức SUM để lấy kết quả. Nếu vô tình chọn phạm vi (C6:C11) có thể không nhận kết quả mong đợi.
Công thức trên ở ô C11 cảnh báo lỗi tham chiếu vòng. Điều này xảy ra vì công thức ô C11 cố gắng tự tính toán chính nó.
Có thể phân loại lỗi tham chiếu vòng thành 2 loại:
1. Tham chiếu vòng trực tiếp (Direct Circular Reference):
Lỗi tham chiếu vòng trực tiếp xuất hiện khi công thức trong ô trực tiếp tham chiếu đến chính ô đó.
2. Tham chiếu vòng gián tiếp (Indirect Circular Reference):
Tham chiếu vòng gián tiếp xảy ra khi công thức trong ô không trực tiếp tham chiếu đến chính ô đó.
4 cách dễ dàng khắc phục tham chiếu vòng Excel không thể liệt kê
Khi gặp lỗi tham chiếu vòng lúc tính toán, phải khắc phục ngay. Để khắc phục, trước tiên cần xác định vị trí. Bài viết dùng 4 phương pháp khác nhau liệt kê lỗi tham chiếu vòng, sau đó sửa lỗi bằng cách sửa công thức.
1. Khắc phục tham chiếu vòng không thể liệt kê bằng công cụ Error Checking (Kiểm tra lỗi) trên Ribbon Excel
Trước hết, dùng công cụ ‘Error Checking’ từ Ribbon Excel xác định lỗi tham chiếu vòng không thể liệt kê. Để minh họa, dùng tập dữ liệu sau chứa lỗi tham chiếu vòng ở ô C11. Tập dữ liệu này chỉ ví dụ để hiểu tốt hơn. Khi làm việc thực tế, phải tìm tham chiếu vòng từ hàng nghìn ô.
Xem các bước liệt kê lỗi tham chiếu vòng dùng công cụ ‘Error Checking’.
CÁC BƯỚC:
- Đầu tiên, vào tab Formulas (Công thức).
- Thứ hai, từ Ribbon Excel dưới tab Formulas chọn menu thả xuống “Error Checking”. Từ menu thả xuống nhấp tùy chọn “Circular References” (Tham chiếu vòng).
- Hành động trên hiển thị ở thanh bên rằng tham chiếu vòng đang xảy ra tại ô C11 trong sheet.
- Tóm gọn: Vào Formulas > Error Checking > Circular References
- Thứ ba, chọn ô C11. Công thức ô đó đang tự tính toán chính nó.
- Sau đó, sửa công thức ô C11 như sau:
=SUM(C5:C10)
- Nhấn Enter.
- Cuối cùng, thấy không còn lỗi tham chiếu vòng ở ô C11. Tổng doanh số ở ô C11 là $17000.
Đọc thêm: Cách khắc phục lỗi tham chiếu vòng trong Excel (Hướng dẫn chi tiết)
2. Dùng Thanh trạng thái (Status Bar) khắc phục tham chiếu vòng trong Excel không thể liệt kê
Tìm lỗi tham chiếu vòng bằng “Thanh trạng thái (Status Bar)” là cách dễ nhất. Để giải thích quy trình liệt kê tham chiếu vòng Excel không thể liệt kê với “Thanh trạng thái”, tiếp tục dùng tập dữ liệu ví dụ trước.
Xem các bước liệt kê và khắc phục tham chiếu vòng với “Thanh trạng thái”.
CÁC BƯỚC:
- Mở worksheet chứa lỗi tham chiếu vòng.
- Nhìn “Thanh trạng thái” dưới tên sheet.
- Từ “Thanh trạng thái”, thấy lỗi tham chiếu vòng ở ô C11.
- Sau đó, sửa công thức ô C11 bằng cách đổi phạm vi từ (C5:C11) thành (C5:C10).
=SUM(C5:C10)
- Nhấn Enter.
- Cuối cùng, các lệnh trên khắc phục lỗi tham chiếu vòng ô C11 và trả về tổng giá trị ô.
LƯU Ý:
Nếu worksheet có 2 hoặc nhiều ô chứa tham chiếu vòng, “Thanh trạng thái” chỉ hiển thị cái mới nhất.
3. Áp dụng Tính toán lặp (Iterative Calculation) khắc phục tham chiếu vòng trong Excel
Cũng có thể khắc phục tham chiếu vòng Excel không thể liệt kê từ worksheet bằng tính toán lặp (iterative calculations). Có thể liệt kê và khắc phục tham chiếu vòng bằng cách bật tính toán lặp trong worksheet. Minh họa phương pháp này dùng tập dữ liệu ví dụ trước.
Xem các bước thực hiện:
CÁC BƯỚC:
- Ban đầu, vào tab File.
- Tiếp, chọn Options (Tùy chọn).
- Hộp thoại “Excel Options” hiện ra.
- Từ hộp đó chọn Formulas (Công thức) và tích tùy chọn “Enable iterative calculation” (Bật tính toán lặp). Đặt giá trị 1 cho “Maximum iterations” (Số lần lặp tối đa). Giá trị 1 chỉ ra công thức chỉ lặp một lần qua ô C5 đến C10.
- Nhấn OK.
- Cuối cùng, không nhận lỗi tham chiếu vòng ở ô C11. Trả về tổng doanh số ở ô C11.
Đọc thêm: Cách bật tính toán lặp trong Excel (với các bước dễ dàng)
4. Tìm & Khắc phục tham chiếu vòng trong Excel dùng phương pháp Truy vết (Tracing)
Không thể tìm và khắc phục tham chiếu vòng bằng một cú nhấp chuột. Để khắc phục tham chiếu vòng Excel không thể liệt kê, sẽ truy vết từng cái. Sau truy vết, sửa công thức ban đầu để khắc phục. Các phương pháp truy vết dùng ở phần này là “Trace Precedents” (Truy vết tiền tố) và “Trace Dependents” (Truy vết hậu tố).
4.1 Tính năng ‘Trace Precedents’ khắc phục tham chiếu vòng
Tính năng “Trace Precedents” truy vết ô phụ thuộc vào ô hiện tại. Tính năng này cho biết ô nào ảnh hưởng ô đang chọn bằng cách vẽ mũi tên. Trong tập dữ liệu sau, trả về tổng ô (C5:C10) ở ô C11. Vì vậy, ô (C5:C10) ảnh hưởng ô C11.
Xem cách dùng “Trace Precedents” từng bước.
BƯỚC:
- Thứ nhất, chọn ô C11.
- Thứ hai, vào tab Formulas.
- Chọn tùy chọn “Trace Precedents”.
- Hành động trên vẽ mũi tên. Cho thấy ô (C5:C11) ảnh hưởng ô C11. Vì ô C11 tự tính toán nên trả về lỗi tham chiếu vòng.
- Thứ ba, sửa công thức ô C11 đổi phạm vi trong công thức thành (C5:C10) từ (C5:C11). Công thức ô C11 sẽ là:
=SUM(C5:C10)
- Sau đó, nhấn Enter. Lệnh trên xóa tham chiếu vòng từ ô đó.
- Cuối cùng, dùng “Trace Precedents” ở ô C11. Sẽ thấy lần này ô (C5:C10) ảnh hưởng ô C11 trong khi bước trước ô ảnh hưởng là (C5:C11).
LƯU Ý:
Phím tắt tìm Trace Precedents: ‘Alt + T U T’
4.2 Tính năng ‘Trace Dependents’ khắc phục tham chiếu vòng
Tính năng “Trace Dependents” dùng tìm ô phụ thuộc vào ô đang chọn. Tính năng cho thấy ô nào phụ thuộc ô đang chọn bằng mũi tên. Trong tập dữ liệu sau, liệt kê lỗi tham chiếu vòng với tùy chọn “Trace Dependents”.
Xem các bước liệt kê tham chiếu vòng dùng “Trace Dependents”.
CÁC BƯỚC:
- Đầu tiên, chọn ô C11.
- Tiếp, vào tab Formulas.
- Chọn tùy chọn “Trace Dependents” từ Ribbon.
- Hành động trên cho thấy ô (C5:C10) phụ thuộc ô C11 bằng mũi tên.
- Sau đó, điều chỉnh công thức ô C11 đổi phạm vi trong công thức thành (C5:C10) từ (C5:C11). Công thức ô C11 sẽ là:
=SUM(C5:C10)
- Nhấn Enter.
- Cuối cùng, thấy không còn tham chiếu vòng ở ô C11.
LƯU Ý:
Phím tắt tìm Trace Precedents: ‘Alt + T U D’ (Lưu ý: gốc ghi Trace Precedents nhưng ý nói Trace Dependents).
Đọc thêm: Cách tìm tham chiếu vòng trong Excel (2 mẹo dễ dàng)
Kết luận
Cuối cùng, hướng dẫn này chỉ cách khắc phục tham chiếu vòng Excel không thể liệt kê. Dùng bảng thực hành đi kèm bài viết để kiểm tra kỹ năng. Nếu có thắc mắc, để lại bình luận dưới. Chúng tôi sẽ cố gắng trả lời nhanh nhất. Theo dõi thêm giải pháp Microsoft Excel sáng tạo trong tương lai.
Bài viết liên quan
- Cách xóa tham chiếu vòng trong Excel (2 cách)
- Cách cho phép tham chiếu vòng trong Excel (Với 2 trường hợp sử dụng phù hợp)