Jak znaleźć odwołania cykliczne w programie Microsoft Excel

Odwołanie cykliczne w programie Excel występuje, gdy komórka zawierająca formułę zależy w pewien sposób od własnego wyniku, tworząc cykl, którego nie można rozwiązać. Jeśli chcesz zatrzymać ten błąd, musisz znaleźć i usunąć te łącza, aby umożliwić programowi Excel dokończenie obliczeń. Oto jak.

Co to są odwołania cykliczne w programie Excel?

Aby bardziej szczegółowo wyjaśnić odwołania cykliczne programu Excel, wyobraźmy sobie scenariusz. W przykładowym arkuszu kalkulacyjnym Excel znajdują się trzy komórki z wartościami – A2, A3 i A4.

Każda z tych komórek ma wartość utworzoną za pomocą prostego obliczenia sumy. A2 sumuje wartości z A3 i A4, natomiast A3 to suma A2 i A4 a A4 to suma A2 i A3. Niestety, te trzy obliczenia nie mogą współistnieć bez odniesienia kołowego.

A2 nie może znaleźć sumy A3 i A4, ponieważ zarówno A3, jak i A4 zawierają obliczenia obejmujące A2.

Dzieje się tak, ponieważ komórka zawierająca formułę lub obliczenie nie może odwoływać się do siebie (bezpośrednio lub pośrednio) bez powodowania błędu odwołania cyklicznego. Powstaje niekończąca się pętla obliczeniowa, której nie można przetworzyć.

Oznacza to, że nie widzisz szukanej wartości — program Excel zawiesza się i nie może kontynuować.

Dla większości użytkowników programu Excel jest to niezamierzony wynik, ponieważ chcesz, aby obliczenia zostały ukończone. Aby rozwiązać ten problem, musisz znaleźć linki i je naprawić.

Znajdowanie odwołań cyklicznych w programie Excel

Program Excel ostrzeże Cię, jeśli odwołanie cykliczne powoduje problem w skoroszycie. Jeśli nie możesz samodzielnie znaleźć błędu, możesz użyć menu Sprawdzanie błędów, aby znaleźć wszystkie cykliczne odniesienia w swojej książce.

Korzystanie z menu sprawdzania błędów

Aby znaleźć istniejące odwołania cykliczne, otwórz skoroszyt i wybierz kartę „Formuły” na pasku wstążki. Następnie kliknij przycisk „Sprawdź błędy”.

W menu rozwijanym umieść wskaźnik myszy nad opcją Odwołania cykliczne, aby wyświetlić listę wszystkich odwołań cyklicznych w skoroszycie. Aby przejść do komórki, która go zawiera, wybierz odwołanie do komórki w menu.

Excel przesunie się, aby podświetlić komórkę zawierającą twoje okrągłe odwołanie, co pozwoli ci rozwiązać go ręcznie.

Śledzenie odwołania cyklicznego

Jeśli masz do czynienia ze złożonymi formułami, określenie źródła odwołania cyklicznego w programie Excel może być trudne ze względu na dużą liczbę poprzedników lub komórek zależnych. Komórki poprzedzające to komórki, które zmieniają wartość zaznaczonej komórki, natomiast komórki zależne to komórki, które wymagają wartości wybranej komórki do ukończenia obliczenia.

Aby pomóc sobie z nimi poradzić, możesz prześledzić cykliczne odwołanie z powrotem do źródła za pomocą narzędzi do śledzenia przypadków użycia i śledzenia zależności. Aby śledzić odwołanie cykliczne w programie Excel, otwórz skoroszyt i zaznacz komórkę zawierającą odwołanie cykliczne.

Następnie wybierz Formuły > Pokaż formuły. Spowoduje to przełączenie skoroszytu do widoku formuły, umożliwiając wyświetlanie wszystkich używanych formuł bez wybierania poszczególnych komórek.

Nie jest to konieczne, ponieważ program Excel wizualnie pokaże okrągłą pętlę łączy za pomocą strzałek, ale może pomóc szybko zidentyfikować łącza do problematycznych komórek w dużym skoroszycie.

Mając aktywny widok formuł i zaznaczoną komórkę zawierającą odwołanie cykliczne, kliknij przycisk Śledź obserwacje.

Pojawi się strzałka wskazująca inne komórki – zwróć uwagę na ślad strzałki, aby określić źródło problemu.

Kiedy skończysz, kliknij „Śledź zależności” i sprawdź każdą komórkę, na którą wskazuje.

Tak jak poprzednio, zwróć uwagę na komórki, które powodują problem. Excel wizualnie identyfikuje okrągłą pętlę wsparcia ze strzałkami i okrągłymi ikonami.

W przypadku każdego łącza kołowego, które masz, może być konieczne powtórzenie tych kroków, aby uzyskać pełną odpowiedź i rozwiązać problem.

Naprawianie odwołań cyklicznych w Excelu

Jedynym sposobem, aby naprawdę rozwiązać odwołanie cykliczne w programie Excel, jest edycja obliczeń, które go spowodowały. Jeśli pętla zostanie przerwana, program Excel może dokończyć obliczenia. Najszybszym (i najlepszym) sposobem na to jest zastąpienie formuł zawierających odwołania do komórek odpowiednimi wartościami.

Oto przykład. Załóżmy ponownie, że wszystkie trzy komórki w skoroszycie (A2, A3 i A4) zawierają proste obliczenia, które odwołują się do siebie. Aby wyeliminować odwołanie kołowe, musisz zastąpić obliczenia w A2 i A4 wartością o równej wartości.

Warunki obliczeń w A3 zostaną spełnione, co pozwoli Excelowi obliczyć wartość bez tworzenia pętli. Jeśli nie chcesz używać wartości bezpośredniej, musisz odwołać się do innego odwołania do komórki — takiego, które nie ma istniejącego odwołania do wybranej komórki.

To prosty przykład, ale ta sama zasada dotyczy dużych i złożonych skoroszytów programu Excel.