Khi cố gắng nhập công thức vào trang tính Excel, nó không hoạt động vì lý do nào đó. Thay vào đó, nó thông báo tham chiếu vòng (circular reference). Hàng triệu người dùng gặp vấn đề này hàng ngày do yêu cầu hàm Excel tính toán chính ô của nó. Bài học nhanh này đi qua cơ bản của lỗi tham chiếu vòng trong Excel và tại sao nên tránh chúng. Bạn cũng sẽ học cách phân tích, tìm, và sửa lỗi tham chiếu vòng trong bảng tính Excel, cũng như cách bật và dùng công thức vòng nếu các tùy chọn trước không khả thi.
Tham chiếu Vòng (Circular Reference) Là Gì?
Khi kết thúc với một công thức trong ô, bạn tạo tham chiếu vòng – thực hiện tính toán dùng tham chiếu ô (nơi nó được nhập) như con rắn đuổi đuôi mình.
Nói cách khác, tham chiếu vòng xảy ra khi công thức, trực tiếp hoặc gián tiếp, tham chiếu ngược về ô của nó, dẫn đến vòng lặp tính toán không kết thúc. Giá trị ô sẽ thay đổi mỗi khi vòng lặp tham chiếu không dừng.
Ví dụ giúp bạn hiểu.
Bảng sau thể hiện thống kê doanh thu qua thập kỷ của Apple - một trong những thương hiệu lớn nhất thế giới. Ví dụ, dùng Hàm SUM tại trường C16, cố tính tổng doanh thu. Tuy nhiên, đưa phạm vi C5:C16 cho Hàm SUM.
Tạo vòng lặp vô hạn vì phạm vi cung cấp cũng chứa ô C16 (nơi kết quả cần ghi). Do đó, Excel liên tục cộng giá trị mới vào ô C16, giá trị tăng lên theo cấp số nhân.
Điều này kích hoạt Excel hiển thị cảnh báo tham chiếu vòng như hình dưới.
📝 Lưu ý.
- Đây không phải lỗi vì không dừng hoặc nhắc người dùng thay đổi tính toán. Là thông báo cho biết tính toán của người dùng có thể sai.
- Trong nhiều trường hợp, khi nhập nhiều công thức có tham chiếu vòng, Excel không hiển thị cảnh báo lặp lại.
Áp Dụng Tính toán Lặp (Iterative Calculations) để Bật/Tắt Lỗi Tham chiếu Vòng
Tính toán lặp là tính năng thú vị trong Excel. Nếu người dùng tắt tính toán lặp, Excel hiển thị cảnh báo Tham chiếu Vòng và hiển thị 0 thay vì kết quả thực trong ô. Vì là vòng lặp không kết thúc, nên trường hợp này xảy ra.
Nếu làm việc với bộ dữ liệu lớn và muốn Excel ngồi yên để bạn làm việc không phiền cảnh báo tham chiếu vòng, có thể dùng Tính toán Lặp (Iterative Calculations).
Bật/Tắt Tính toán Lặp
Trong ảnh chụp màn hình dưới, thấy tham chiếu vòng ở ô C16 được chỉ ra ở thanh trạng thái (status bar) của trang tính. Do đó, kết quả là không (0) tại ô C16. Bây giờ, nếu muốn công việc làm không phiền toái, cần bật Tính toán lặp.
Xem cách bật/tắt tính toán lặp trong Excel ngay bây giờ. Làm theo các bước dưới.
Các bước:
- Vào thẻ File ⇨ Options (Tùy chọn) ⇨ Formulas (Công thức).
- Đánh dấu ô kiểm Enable iterative calculation (Bật tính toán lặp).
- Nhấn Enter.
Kết quả, thấy hộp thoại tham chiếu vòng sáng lên với calculate (tính toán) như ảnh chụp dưới. Bây giờ ô 16 có giá trị thay vì không (0).
Vì thế, mỗi ô trước đây liên quan đến tham chiếu vòng sẽ được tính toán.
Tham số Maximum Iterations (Số lần lặp tối đa) & Maximum Change (Thay đổi tối đa)
Tính toán Lặp có hai tham số:
Maximum Iterations
Vòng lặp này lặp lại khi Excel tính kết quả cuối cùng. Hoàn toàn do bạn kiểm soát. Hãy nhớ nhiều lần lặp hơn nghĩa là Excel hoạt động nhiều hơn, máy cần nhiều tài nguyên và khả năng xử lý hơn. Cũng mất thời gian hơn.
Maximum Change
Thay đổi tối đa là số phải đạt để lần lặp tiếp tục. Liên quan đến độ chính xác kết quả. Hãy đặt giá trị này càng thấp càng tốt để đạt kết quả chính xác. Mặc định, maximum change là 0.001.
Tại Sao Nên Tránh Bật Tính toán Lặp Có Ý Định?
Không tốt khi dùng Tham chiếu Vòng trong Excel. Khi bật tính toán lặp, việc dùng có chủ đích tham chiếu vòng là cách thủ thuật khiến mọi thứ hoạt động nhưng tốn nhiều tài nguyên và sức máy tính. Khi phải sửa vài cái, thường cho hậu quả không mong muốn hoặc không chính xác, gây bực bội.
Tìm Lỗi Tham chiếu Vòng trong Excel
Khi lỗi tham chiếu vòng xảy ra, bạn nhận cảnh báo nhưng vẫn cần tìm ô nào gặp sự cố. Việc biết vị trí ô cụ thể của lỗi giúp dễ dàng xử lý và sửa.
Các kỹ thuật này rất hữu ích khi xử lý bộ dữ liệu lớn.
1. Dùng Menu Thả xuống Error Checking để Tìm Lỗi Tham chiếu Vòng trong Excel
Cách dùng Ribbon trong Excel tìm tham chiếu vòng.
Bước 1:
- Vào thẻ Formulas (Công thức)
- Nhấp menu thả xuống Error Checking (Kiểm tra lỗi).
Bước 2:
- Chọn Circular References (Tham chiếu vòng) từ tùy chọn.
Excel sẽ hiển thị tất cả tham chiếu vòng của trang tính ở đây.
Như thấy ảnh chụp trên, bảng tính dùng có một tham chiếu vòng ở ô C16.
Đọc thêm: Cách Tìm Tham chiếu Vòng trong Excel (2 Mẹo Dễ dàng)
2. Dùng Thanh Trạng thái (Status Bar) để Tìm Lỗi Tham chiếu Vòng trong Excel
Khá đơn giản tìm tham chiếu vòng dùng thanh trạng thái. Nếu trang tính có tham chiếu vòng, người dùng thấy ở thanh trạng thái dưới tên trang tính.
📝 Lưu ý.
- Tính năng này tắt khi tùy chọn Iterative Calculation (Tính toán lặp) bật, nên cần tắt trước khi kiểm tra workbook tìm tham chiếu vòng.
- Chỉ hiển thị tham chiếu vòng gần nhất và cho người dùng quay ngược từ đó.
Đọc thêm: Cách Cho phép Tham chiếu Vòng trong Excel (Với 2 Cách Sử dụng Phù hợp)
Sửa Lỗi Tham chiếu Vòng trong Excel
Tham chiếu vòng không thể sửa trong Excel bằng một cú nhấp chuột. Để xử lý tham chiếu vòng, phải xóa chúng từng cái một.
Ví dụ, sau khi tìm lỗi ở ô C16, cần viết lại công thức đúng hoặc chọn phạm vi đúng.
Bước 1:
- Chọn phạm vi đúng để sửa lỗi ô C16.
Bước 2:
- Nhấn Enter ở ô C16 để xem kết quả.
Đọc thêm: Cách Xóa Tham chiếu Vòng trong Excel (2 Cách)
Truy vết Mối Quan hệ Giữa Công thức và Ô
Khi Excel tham chiếu vòng không rõ ràng, có thể dùng tính năng Trace Precedents (Truy vết nguồn) và Trace Dependents (Truy vết phụ thuộc) vẽ một hoặc nhiều đường kẻ chứng minh ô nào ảnh hưởng hoặc bị ảnh hưởng bởi ô chọn.
Trong ảnh chụp dưới, thể hiện doanh thu dự kiến của Apple năm 2022. Bây giờ muốn Tổng doanh thu từ năm 2011 đến 2022. Làm vậy, nhập công thức sau ở ô D10.
Nhưng do có lỗi tham chiếu vòng ở ô C16, ô D10 sẽ có giá trị lỗi. Vấn đề này, dùng Trace Precedents và Trace Dependents truy vết mối quan hệ giữa công thức và ô.
Trace Precedents Liên kết Lỗi Tham chiếu Vòng trong Excel
Truy vết các ô cung cấp dữ liệu cho công thức, vẽ đường kẻ hiển thị ô nào ảnh hưởng đến ô chọn. Làm theo các bước.
Các bước:
- Vào thẻ Formulas (Công thức) trong Excel.
- Chọn Trace Precedents.
Do đó, bạn sẽ thấy mũi tên chỉ các ô ảnh hưởng đến ô hoạt động.
📝 Lưu ý. Phím tắt Trace Precedents: Alt+T U T
Trace Dependents Liên kết Lỗi Tham chiếu Vòng trong Excel
Vẽ đường kẻ chỉ ô nào bị ảnh hưởng bởi ô chọn và truy vết ô phụ thuộc vào ô hoạt động. Làm theo các bước.
Các bước:
- Vào thẻ Formulas trong Excel.
- Chọn Trace Decedents.
Kết quả, hiển thị các ô có công thức tham chiếu đến ô hoạt động hiện tại.
📝 Lưu ý. Phím tắt Trace Decedents: Alt+T U D
Kết luận
Tóm lại, hy vọng bài viết cung cấp hướng dẫn chi tiết sửa lỗi tham chiếu vòng trong Excel. Tất cả quy trình nên học và áp dụng cho bộ dữ liệu. Xem workbook thực hành và kiểm tra kỹ năng. Sự hỗ trợ quý báu của bạn thúc đẩy chúng tôi tiếp tục làm hướng dẫn như này.
Nếu có câu hỏi – Hãy thoải mái hỏi chúng tôi. Cũng để lại bình luận ở phần dưới.
Chúng tôi, Đội ngũ Exceldemy, luôn phản hồi nhanh yêu cầu của bạn.
Hãy cùng chúng tôi & tiếp tục học tập.
Bài viết Liên quan
- Sửa Tham chiếu Vòng Không Thể Liệt kê trong Excel (4 Cách Dễ dàng)