“Cho phép tham chiếu vòng trong Excel” là cách dễ dàng để chèn tự tham chiếu một ô. Theo định nghĩa của Microsoft, Tham chiếu vòng (Circular Reference) xảy ra khi công thức Excel tham chiếu ngược lại ô của chính nó, trực tiếp hoặc gián tiếp. Ví dụ, nếu muốn cộng hai ô (B2 và C2) nhưng hiển thị kết quả ở một trong hai ô đó (ví dụ B2), thì ta tạo ra tham chiếu vòng. Nhấn ENTER lần đầu, Excel cảnh báo có một hoặc nhiều tham chiếu vòng trong công thức.
Tham chiếu vòng tính toán kết quả công thức theo lặp. Do đó, nó có thể tạo vòng lặp nếu số lần lặp tối đa được đặt quá lớn.
Bài viết thảo luận Tham chiếu vòng, các vấn đề và cách sử dụng.
Tải file Excel thực hành
Cách kiểm tra Tham chiếu vòng đã được bật hay chưa?
Từ các phần trước, chúng ta biết tham chiếu vòng là gì và cách xử lý. Câu hỏi quan trọng: làm sao biết một sheet Excel có tham chiếu vòng đang Bật hay Tắt?
Cách tự nhiên nhất: nếu tự nhập tham chiếu, Excel cảnh báo lần đầu như hình. Nếu hiện cảnh báo, chắc chắn sheet đang TẮT tham chiếu vòng. Ngược lại, công thức trả về kết quả đúng cho số lần lặp tối đa 1.
Sau khi nhập tham chiếu trong công thức, bạn thấy Excel trả về 0 không rõ lý do. Kiểm tra văn bản bên phải trạng thái Ready (Sẵn sàng) ở dưới cùng sheet. Nếu hiển thị Circular Reference: cell, nghĩa là tùy chọn tham chiếu vòng đang TẮT cho sheet đó.
Có thể vào tab Formulas (Công thức) > Chọn Error Checking (Kiểm tra lỗi) (trong phần Formula Auditing) > Chọn Circular References (Tham chiếu vòng). Nó chỉ vị trí lỗi.
Đọc thêm: Cách tìm tham chiếu vòng trong Excel (2 mẹo dễ dàng)
Cho phép Tham chiếu vòng trong Excel dùng Ribbon File
Cho phép tham chiếu vòng là một cách bỏ qua lỗi tham chiếu trong công thức. Tuy nhiên, bật tùy chọn này giúp công thức vượt qua lỗi và trả về giá trị kết quả đúng.
Để cho phép tham chiếu vòng, làm theo trình tự:
➤ Vào Ribbon File của sheet bất kỳ.
➤ Chọn Options từ menu File (cửa sổ Excel Options hiện ra) > Chọn Formulas (bên phải cửa sổ) > Tích Enable iterative calculation (Bật tính toán lặp) (nhập Maximum iterations và Maximum change cần thiết).
Nhấn OK.
Thực hiện xong, công thức có thể trả về kết quả với tham chiếu vòng hiện hữu.
Vấn đề với Số lần lặp tối đa (Maximum Iterations)
Khi tham chiếu vòng xảy ra, Excel cảnh báo lần đầu như ảnh dưới:
Tuy nhiên, sau đó Excel không còn cảnh báo, người dùng băn khoan vì kết quả trả về 0 bất kể loại công thức.
Để khắc phục, phải kiểm tra Enable iterative calculation. Vào tab File như hướng dẫn:
Sau khi nhấn File, chọn Options (cửa sổ Excel Options hiện ra) > Chọn Formulas (bên trái cửa sổ) > Tích Enable iterative calculation. Nhấn OK.
Giờ có thể dùng tham chiếu vòng trong công thức. Nhưng nếu nhập 1 (như ví dụ) vào ô Maximum iterations, công thức (ví dụ =B2+C2) trả về 20 (đúng vì B2 trống, C2 = 20).
Tuy nhiên, nếu nhập 5 vào Maximum iterations như ảnh:
Công thức trả về 100 vì lặp lại tính toán 5 lần. Đây là sai sót tính toán nghiêm trọng trong Excel.
Ứng dụng của Tham chiếu vòng trong Excel
Tham chiếu vòng khá hữu ích trong một số trường hợp. Một số tình huống cần nó để biểu diễn giá trị hiệu quả hơn. Bài viết trình bày 2 trường hợp phổ biến nhất.
Ví dụ 1: Cho phép tham chiếu vòng để chèn dấu thời gian tĩnh (Static Timestamp)
Giả sử có Sản phẩm với Ngày đặt hàng và Trạng thái giao hàng. Muốn chèn Dấu thời gian (Timestamp) vào ô khi sản phẩm Đã giao. Hiển thị trạng thái bằng Yes hoặc No.
Nếu Trạng thái giao hàng là Yes, Timestamp hiển thị Giờ và Ngày lúc đó.
➤ Dán công thức sau vào ô trống (ví dụ E5) nơi muốn hiển thị Timestamp:
=IF(D5="Yes",IF(E5="",NOW(),E5),"")Công thức chạy logical_test khớp "Yes" với cột D. Hiển thị giờ hiện tại cho ô trả về TRUE, để trống nếu không.
➤ Nhấn ENTER rồi kéo Fill Handle (Taylor) để chèn Timestamp cho tất cả sản phẩm đã giao.
Vì Tham chiếu vòng gây ra vấn đề tự tham chiếu ô, đôi khi công thức似乎 không hoạt động đúng. Lúc đó, viết lại Trạng thái giao hàng hoặc điều kiện rồi áp dụng lại công thức. Có thể cần đổi định dạng ô để có định dạng ngày-giờ.
Ví dụ 2: Cho phép tham chiếu vòng cho tính toán lặp (Iterative Calculation)
Đôi khi cần tính toán lặp. Trong tính toán lặp, ta lấy giá trị lặp trực tiếp mà không cần tính toàn bộ.
Giả sử đầu tư số tiền ban đầu (ví dụ $1500) và muốn kỳ trả hàng tháng từ tài sản. Tính số tiền kỳ trả tháng thứ nhất bằng công thức:
=$C$4*(1+$C$5)Sau đó tham chiếu kỳ trả trước để tính kỳ tiếp theo như hình:
➤ Tuy nhiên, dùng tham chiếu vòng, ta có thể lấy số tiền kỳ trả nth chỉ bằng cách nhập số kỳ trả làm số Maximum iterations. Để kết quả đúng, nhập 5 làm Maximum iterations và 1th của 1000 thay đổi cho phép như ảnh:
Nhập maximum iterations = 5 vì tính kỳ trả thứ 6. Cho phép lượng thay đổi biến đổi 1th của 1000 phần.
➤ Sau khi cho phép Tham chiếu vòng, gõ =F6 ở ô F4 để lấy giá trị sau 5 lần lặp ở ô F6 (tạo Tham chiếu vòng) áp dụng công thức =F4*(1+F5).
Công thức ở ô F6 tính kỳ trả thứ 6 ngay lần thứ hai và ít phiền toái hơn.
Kết luận
Bài viết thảo luận một trong những sự cố khó chịu nhất của Excel: Tham chiếu vòng. Cũng xem xét vấn đề do tùy chọn Maximum iterations gây ra. Minh họa cách dùng phổ biến của Tham chiếu vòng và mẹo sử dụng trong tính toán phù hợp. Hy vọng bài viết làm rõ khái niệm và giúp bạn xử lý hiệu quả hơn. Bình luận nếu có thắc mắc. Hẹn gặp lại bài sau.
Bài viết liên quan
- Cách xóa tham chiếu vòng trong Excel (2 cách)
- Khắc phục tham chiếu vòng không thể liệt kê trong Excel (4 cách dễ dàng)