Jak wykrywać odwołania cykliczne w programie Microsoft Excel
Odwołania cykliczne w Excelu mogą być prawdziwym problemem. Czasami formuły po prostu się plączą i nagle obliczenia zawieszają się lub generują błędy przy każdej próbie przeglądania lub analizy danych. To trochę dziwne, ale problem w tym, że Excel nie lubi formuł, które odwołują się do samych siebie bezpośrednio lub pośrednio – to tzw.odwołanie cykliczne. W takim przypadku Excel wyświetli ostrzeżenie, ale czasami możesz go nawet nie zauważyć od razu, zwłaszcza jeśli komunikat zginie w natłoku informacji.
Zrozumienie, gdzie występują te pętle oraz jak je zlokalizować i naprawić, może zaoszczędzić Ci wiele frustracji i zapobiec problemom z arkuszami. Niezależnie od tego, czy jest to prosty błąd, czy celowe ustawienie obliczeń iteracyjnych, wiedza o tym, jak zlokalizować i usunąć (lub w niektórych przypadkach zezwolić na) te odwołania, znacznie ułatwia życie. Oczywiście Excel musi wszystko komplikować bardziej niż to konieczne, ukrywając niektóre z tych narzędzi za menu i opcjami. Tak czy inaczej, oto, co pomogło w naprawieniu tych irytujących odwołań cyklicznych i przywróceniu poprawnego działania formuł.
Jak naprawić odwołania cykliczne w programie Excel
Sprawdź, czy masz odniesienia cykliczne – to podstępne małe szkodniki
Pierwszą rzeczą, którą należy zrobić, to sprawdzić, czy Excel już zasygnalizował problem. Zazwyczaj pasek stanu w lewym dolnym rogu informuje o obecności odwołania cyklicznego wraz z adresem komórki, takim jak F5. Jeśli to widzisz, wiesz, na czym się skupić. Czasami alert nie jest oczywisty, dopóki się nie przyjrzysz, zwłaszcza jeśli zagłębisz się w złożone formuły.
Na jednej konfiguracji działało idealnie, na innej… nie do końca – więc nie zakładaj, że wszystko jest idealne, bo nie widzisz ostrzeżenia. Wyrób sobie nawyk zerkania od czasu do czasu na dolny pasek, zwłaszcza po dużych edycjach.
Użyj narzędzia do sprawdzania błędów w programie Excel, aby zlokalizować pętlę
To prostsza metoda, jeśli chcesz szybko zidentyfikować odwołania cykliczne bez ręcznego przeszukiwania formuł. Oto jak to zrobić:
- Otwórz skoroszyt programu Excel i przejdź do karty Formuły na Wstążce.
- Kliknij „Sprawdzanie błędów”. To ten mały przycisk pośrodku zakładki.
- Z listy rozwijanej wybierz „Odwołania cykliczne”, a Excel wyświetli listę wszystkich problematycznych adresów komórek. Zazwyczaj od razu przejdzie do pierwszego na liście, co jest bardzo przydatne.
Stamtąd wystarczy przejść do tych komórek i zbadać przyczynę pętli. Oczywiście Excel bywa w tym względzie irytująco niejasny, ale to narzędzie dobrze radzi sobie ze wskazaniem właściwego kierunku.
Jak usunąć odwołanie cykliczne
To dość oczywiste, ale warto o tym wspomnieć: jeśli nie chcesz tworzyć odwołań cyklicznych, po prostu pozbądź się formuł z pętlami zwrotnymi. Prostym rozwiązaniem jest często zastąpienie formuły wartością statyczną. Brzmi brutalnie, ale jest skuteczne — zwłaszcza jeśli pętla nie jest niezbędna do obliczeń.
Oto jak:
- Kliknij komórkę wyświetlającą ostrzeżenie o odwołaniu cyklicznym lub oznaczoną przez narzędzie Sprawdzanie błędów.
- Kliknij prawym przyciskiem myszy i wybierz Kopiuj lub naciśnij Ctrl + C.
- Kliknij ponownie prawym przyciskiem myszy, wybierz Wklej specjalnie, a następnie wybierz Wartości. Możesz też nacisnąć Ctrl + Alt + Vi wybrać Wartości.
Zastępuje to formułę bieżącym wynikiem, przerywając pętlę. Powtórz to dla wszystkich podejrzanych komórek.
Pamiętaj jednak, że jeśli Twoje formuły są od siebie wzajemnie zależne w celu dynamicznej aktualizacji, przekształcenie ich w wartości statyczne może nie być idealnym rozwiązaniem na dłuższą metę. Jednak w przypadku naprawiania błędów jest to szybkie i proste.
Włącz obliczenia iteracyjne, jeśli potrzebujesz odwołań cyklicznych
Czasami odwołania cykliczne nie są błędami. Stanowią raczej część kontrolowanego procesu iteracyjnego, na przykład w przypadku niektórych modeli finansowych lub formuł dążących do osiągnięcia celu. Jeśli wiesz, co robisz, możesz zezwolić na takie odwołania, włączając tzw.*obliczenia iteracyjne*.
Oto gdzie to zrobić:
- Otwórz program Excel. Przejdź do Plik > Opcje (lub Plik > Więcej > Opcje w systemie Windows).
- W menu Opcje programu Excel kliknij pozycję Formuły.
- Przewiń w dół do opcji obliczeń. Zaznacz pole wyboru Włącz obliczenia iteracyjne.
- Dostosuj maksymalną liczbę iteracji (ile razy Excel będzie przeliczał dane) i maksymalną zmianę (precyzję wyniku końcowego).Poeksperymentuj z tymi wartościami, jeśli chcesz uzyskać szybsze lub dokładniejsze wyniki.
Naciśnij OK i zapisz zmiany — teraz Excel będzie tolerował odwołania cykliczne w oparciu o ustawione limity. Należy pamiętać, że w niektórych arkuszach kalkulacyjnych włączenie tej opcji może powodować powolne przeliczanie lub nieoczekiwane wyniki, jeśli nie zostanie to odpowiednio przeprowadzone.
Zarządzanie błędami i zapobieganie przyszłym pętlom
Odwołania cykliczne, choć czasami przydatne, zazwyczaj przynoszą więcej kłopotów niż pożytku – chyba że celowo ustawisz je w celach iteracyjnych. Ich usunięcie sprawia, że formuły są bardziej przewidywalne, ale jeśli polegasz na podejściach iteracyjnych, pamiętaj o włączeniu tego ustawienia i kontroluj limity obliczeń. Warto również dokładnie sprawdzić formuły po większych edycjach – takie błędy często pojawiają się ponownie, gdy arkusze stają się skomplikowane.
Jeśli Twój arkusz kalkulacyjny działa niestabilnie lub wyświetla dziwne komunikaty o błędach, sprawdź dokładnie te komórki pod kątem pętli i rozważ uproszczenie formuł, jeśli to możliwe. Im mniej zależności, tym mniej niespodzianek.
Powiązane artykuły
Czy ten artykuł był pomocny?