Bạn đã cố gắng nhập một số công thức vào trang tính Excel của mình, nhưng vì lý do nào đó, nó không hoạt động. Thay vào đó, nó cho bạn biết điều gì đó về tham chiếu vòng tròn.
Hàng nghìn người dùng đang phải đối mặt với cùng một vấn đề hàng ngày chỉ vì buộc một công thức Excel tính toán ô của chính nó. Khi bạn cố gắng thực hiện điều này, Excel sẽ đưa ra thông báo lỗi sau:
"Cẩn thận, chúng tôi đã tìm thấy một hoặc nhiều tham chiếu vòng tròn trong sổ làm việc của bạn, điều này có thể khiến công thức của bạn tính toán không chính xác."
Như bạn có thể hiểu, các tham chiếu vòng tròn trong Excel rất rắc rối và theo lẽ thường, hãy tránh chúng bất cứ khi nào có thể. Tuy nhiên, có thể có một số trường hợp hiếm gặp khi tham chiếu vòng tròn Excel là giải pháp khả thi duy nhất cho tác vụ mà bạn đang gặp phải.
Tham chiếu vòng xảy ra khi bạn có một công thức trong một ô – bản thân ô này sử dụng tham chiếu ô (trong đó nó được nhập) để tính toán. Nói một cách đơn giản, một tham chiếu vòng xảy ra khi một công thức tham chiếu trực tiếp hoặc gián tiếp trở lại ô của chính nó, tạo ra một vòng lặp vô tận của các phép tính. Vòng lặp tham chiếu vô tận này, nếu không dừng lại, sẽ liên tục thay đổi giá trị của ô.
Các tham chiếu vòng tròn có thể gây ra nhiều vấn đề (và rất nhiều nhầm lẫn) vì chúng có thể khiến các công thức khác trả về số 0 hoặc một kết quả khác không chính xác.
Giả sử bạn muốn cộng các giá trị trong cột C bằng cách sử dụng hàm SUM thông thường và khi làm điều này, bạn vô tình bao gồm cả ô tổng cộng (trong ví dụ này là ô C11).
Nếu tham chiếu vòng tròn không được phép trong Excel của bạn (và chúng bị tắt theo mặc định), bạn sẽ thấy thông báo lỗi mà chúng ta đã thảo luận lúc trước.
Nếu tính toán lặp được bật, thì công thức vòng tròn của bạn sẽ trả về 0 như trong ảnh chụp màn hình sau:
Tính toán kết quả của một công thức, khi ô chứa công thức đó là một phần của phép tính, dẫn đến một vòng lặp vô tận mà Excel không thể hoàn thành.
Hãy sử dụng một ví dụ rất cơ bản. Giả sử bạn có hai ô: C3 và D3. Cả hai đều chứa tham chiếu đến ô khác, với C3 chứa =D3 và D3 chứa =C3. Đây là một tham chiếu vòng tròn, vì không thể hoàn thành “công thức”.
Không có câu trả lời nào để Excel xuất ra, vì vậy nó sẽ hiển thị lỗi. Một mũi tên màu xanh kết nối hai ô cung cấp một biểu diễn đồ họa của vấn đề.
Điều này cũng xảy ra nếu, ví dụ, bạn tham chiếu dữ liệu trong một ô dựa trên kết quả của ô đầu tiên của bạn. Chẳng hạn, C3 chứa =D3, trong khi D3 chứa =E3. Tuy nhiên, E3 chứa một phép tính đơn giản =C3+1. Vì kết quả tổng thể phụ thuộc vào đầu ra của ô ban đầu ( C3 ), điều này vẫn dẫn đến lỗi.
Các vòng lặp như thế này không lý tưởng cho người dùng làm việc trong sổ làm việc Excel, phần lớn là do các phép tính dư thừa dẫn đến sử dụng bộ nhớ quá mức (mặc dù ví dụ trên, theo định nghĩa, sẽ không dẫn đến bất kỳ kết quả hữu ích nào).
Excel sẽ không cố gắng đưa ra kết quả cho bạn—cảnh báo là đủ để ngăn chặn lỗi. Rất ít người dùng muốn tắt nó, nhưng có một lợi thế khi làm như vậy. Nếu bạn quyết định muốn tạo một kết quả tĩnh bằng cách sử dụng các hàm thay đổi thường xuyên, chẳng hạn như dấu thời gian, thì bạn có thể tắt tham chiếu vòng tròn để làm như vậy.
Điều này được thực hiện bằng cách cho phép tính toán lặp lại, là các phép tính được lặp lại cho đến khi đáp ứng các điều kiện nhất định. Kết hợp với một số hàm logic nhất định, chẳng hạn như hàm IF, bạn có thể biến các công thức không ổn định thành công thức tĩnh không cập nhật. Tuy nhiên, điều này yêu cầu một chút điều chỉnh đối với cài đặt mặc định của Excel.
Tìm và sửa các tham chiếu vòng trong Excel
Hầu hết người dùng sẽ muốn tránh các tham chiếu vòng xuất hiện trong sổ làm việc Excel của họ. Điều này thường là do nó phá vỡ công thức, ngăn công thức xuất kết quả (mặc dù trong nhiều trường hợp, tham chiếu vòng là rào cản đối với kết quả ngay từ đầu).
Có một số cách bạn có thể thực hiện việc này, từ phát hiện biểu tượng lỗi trên một số ô nhất định đến sử dụng hệ thống kiểm tra lỗi của Excel để xác định vị trí chúng cho bạn.
Khi bạn chèn một công thức có chứa tham chiếu vòng trong Excel, nó thường sẽ cảnh báo bạn. Một cửa sổ bật lên sẽ xuất hiện cùng với biểu tượng cảnh báo bên cạnh ô đó. Tuy nhiên, biểu tượng cảnh báo và cửa sổ bật lên này sẽ không luôn xuất hiện nếu bạn đã chèn nhiều công thức tham chiếu vòng vào sổ làm việc của mình.
Điều này có thể khiến việc tìm kiếm từng công thức sai trở nên khó khăn, đặc biệt đối với các tập dữ liệu lớn hơn. Để giải quyết các tham chiếu vòng, bạn sẽ cần tìm (các) ô có tham chiếu ô không chính xác và điều chỉnh nếu cần. Tuy nhiên, không giống như các lỗi khác (#N/A, #VALUE!, v.v.), tham chiếu vòng tròn không xuất hiện trực tiếp trong ô. Để khắc phục sự cố, nhấn tab Formulas (Công thức) trên thanh ribbon (dải băng), sau đó nhấn biểu tượng mũi tên hướng xuống bên cạnh tùy chọn Error Checking (Kiểm tra Lỗi). Từ trình đơn, hãy di chuột qua tùy chọn Circular References (Tham chiếu vòng).
Trong menu bật lên, bạn sẽ thấy danh sách các tham chiếu ô tuyệt đối đến các ô chứa công thức gây ra tham chiếu vòng. Nhấp vào bất kỳ ô nào trong số này sẽ khiến Excel tìm và chọn ô đó trong sổ làm việc của bạn, thanh trạng thái sẽ thông báo cho bạn rằng các tham chiếu vòng được tìm thấy trong sổ làm việc của bạn và hiển thị địa chỉ của một trong các ô đó.
Khi bạn đã định vị được ô, bạn sẽ cần loại bỏ chúng, bạn sẽ phải kiểm tra từng tham chiếu vòng riêng lẻ bằng cách thực hiện thay đổi ô đó để xóa tham chiếu vòng; loại bỏ hoàn toàn một công thức vòng đã cho hoặc thay thế nó bằng một hoặc nhiều công thức đơn giản. Loại bỏ tham chiếu vòng khỏi công thức sẽ cho phép Excel trả về kết quả chính xác.
Cách bật/tắt tham chiếu vòng trong Excel
Tham chiếu vòng thường là dấu hiệu cho thấy Excel đang hoạt động như dự định. Nếu Excel cố gắng trả về kết quả có tồn tại tham chiếu vòng, điều đó sẽ gây ra việc sử dụng bộ nhớ quá mức và tùy thuộc vào công thức, trả về kết quả không chính xác.
Tuy nhiên, có một ngoại lệ hạn chế đối với quy tắc này khi các tham chiếu vòng tròn được mong muốn. Trong một số trường hợp bạn cần sử dụng vòng lặp để tính toán và có thể thiết lập số vòng lặp cho phép tính.
Ví dụ bạn cần lập cột dữ liệu ghi nhận thời gian cố định và sau đó thời gian sẽ không cập nhật nữa.
Thay vì tạo một hàm lặp vô tận (có khả năng không thành công) để tạo kết quả, các phép tính lặp lại trong một khoảng thời gian hữu hạn. Nếu kết quả được đáp ứng, điều này sẽ tạo ra một kết quả tĩnh không cập nhật.
Phép tính lặp thường được tắt trong Excel theo mặc định (trong ngữ cảnh này, phép lặp là phép tính lặp lại cho đến khi đáp ứng một điều kiện số cụ thể). Để các công thức vòng hoạt động, bạn phải kích hoạt phép tính lặp trong sổ làm việc Excel của mình.
Trong Excel 2003 trở về trước, tùy chọn Tính toán Lặp lại nằm trong Menu > Tools (Công cụ) > Options (Tùy chọn) > tab Calculation (Tính toán).
Trong Excel 2007, nhấp vào nút Office > chọn Microsoft Excel Options (Tùy chọn Excel) > Formulas (Công thức) > rồi chọn Enable Iterative Calculation (Bật phép tính lặp lại) trong nhóm Calculation Options (Tùy chọn tính toán).
Trong Excel 2010 trở lên, bấm vào File (Tệp) > Options (Tùy chọn), > sau đó chọn Formulas (Công thức) > từ menu bên trái và chọn hộp kiểm Enable Iterative Calculation (Bật phép tính lặp) trong phần Calculation Options (Tùy chọn tính toán).
Khi bật phép tính lặp, bạn phải chỉ định hai tùy chọn sau:
Số lần lặp lại tối đa (Maximum Iterations): Chỉ định số lần công thức sẽ tính toán lại. Số lần lặp càng nhiều thì thời gian tính toán càng nhiều.
Thay đổi tối đa (Maximum Change): Chỉ định sự thay đổi tối đa giữa các kết quả tính toán. Con số càng nhỏ, kết quả bạn nhận được càng chính xác và Excel càng mất nhiều thời gian để tính toán trang tính. Giá trị thay đổi tối đa xác định độ chính xác của kết quả đầu ra, cho phép bạn quyết định mức độ đầu ra của công thức có thể thay đổi trước khi ngừng tính toán lại. Chẳng hạn, nếu giá trị ban đầu là 10 và giá trị thay đổi tối đa là 0,1 và sau đó giá trị này thay đổi thành 10,1 thì Excel sẽ ngừng tính toán lại, ngay cả khi không đạt đến giá trị số lần lặp lại tối đa.
Cài đặt mặc định là 100 cho Số lần lặp tối đa và 0,001 cho Thay đổi tối đa. Điều đó có nghĩa là Microsoft Excel sẽ ngừng tính toán công thức vòng tròn của bạn sau 100 lần lặp hoặc sau khi thay đổi nhỏ hơn 0,001 giữa các lần lặp, tùy điều kiện nào đến trước.
Tóm lược:
Nếu bạn đang nhìn thấy các cảnh báo tham chiếu vòng tròn trong Excel, thì có lẽ tốt nhất bạn nên xem qua và khắc phục chúng. Tuy nhiên, nếu bạn muốn sử dụng các tham chiếu vòng tròn, bạn có thể kết hợp các hàm thời gian hoặc hàm ngày với các kiểm tra logic để tạo kết quả tĩnh bằng cách sử dụng các phép tính lặp. Chỉ cần nhớ rằng đây không phải là cách Excel hoạt động và có thể dẫn đến lỗi.
Để ý các tham chiếu vòng trong dữ liệu của bạn chỉ là một trong một số kỹ năng cần thiết mà các nhà phân tích dữ liệu mới nên chú ý trong Excel. Nếu bạn đã thành thạo các công thức Excel cơ bản, bạn có thể tham gia khóa học ngắn hạn để xác định các kỹ năng và kinh nghiệm khác mà bạn cần cho sự nghiệp thành công trong lĩnh vực phân tích dữ liệu.