Jak znaleźć odwołania cykliczne w programie Excel, aby uniknąć błędnych danych

Czy zauważyłeś błąd odwołania cyklicznego w arkuszu programu Excel i zastanawiasz się, czy arkusz jest uszkodzony? Zaufaj mi! Nie jesteś pierwszą osobą, która napotyka ten błąd programu Excel. Setki i tysiące użytkowników programu Excel każdego dnia napotyka błędy w odwołaniach cyklicznych. Nie martwić się! Oto jak znaleźć odwołania cykliczne w programie Excel i rozwiązać je.

Będąc zapalonym użytkownikiem programu Excel, rzadko wpisujesz te same dane w wielu komórkach. Zamiast tego importujesz zestaw danych ze źródła i używasz odwołań do komórek do tworzenia formuł. W ten sposób tworzysz dynamiczny arkusz kalkulacyjny.

Gdy ktoś zmieni wartości komórek w odwołaniach do komórek, arkusz aktualizuje się automatycznie. Nazywa się to automatyzacją analizy danych, a analitycy danych uwielbiają bawić się odwołaniami do komórek.

Jednak problem pojawia się, gdy celowo lub przypadkowo wpiszesz adres komórki, w której aktualnie wykonujesz obliczenia. Lub, gdy tworzysz wiele formuł w całym arkuszu, komórka zawierająca formułę pojawia się pośrednio w innej formule.

W takich i wielu innych podobnych sytuacjach pojawia się błąd odwołania cyklicznego w Excelu. Czytaj dalej, aby dowiedzieć się, czym są odwołania cykliczne, jakie są ich przyczyny, dlaczego ich unikać, a przede wszystkim, jak znaleźć odwołania cykliczne w Excelu, aby na dobre pozbyć się tych anomalii.

Co to jest odwołanie cykliczne w programie Excel?

Program Excel napotyka błąd odwołania cyklicznego, gdy widzi, że jedna lub wiele funkcji arkusza odwołuje się z powrotem do adresu komórki, w której zostały utworzone ich funkcje.

Może to wydawać się mylące, więc zastanów się, jak poważne może to być dla aplikacji Excel. Krótko mówiąc, nigdy nie chcesz umieszczać adresu komórki w formule, w której ją tworzysz. Spójrz na poniższy przykład, aby uzyskać wizualne wyjaśnienie:

Próbujesz pomnożyć 10 z komórki B1 i umieścić wynik w samej komórce B1 .

Teraz, w powyższym scenariuszu, jeśli po prostu naciśniesz OK , Excel zamknie błąd i wyświetli zero jako obliczony wynik. To tylko prosty przykład.

Co to jest odwołanie cykliczne w programie Excel

Teraz wyobraź sobie, że w arkuszu księgowym, arkuszu planowania finansów, liście płac pracowników, arkuszu płatności dostawców i tak dalej znajdują się setki formuł.

Zaufasz błędnym danym obliczonym przez program Excel z powodu błędu odniesienia cyklicznego, co z kolei wpłynie na podejmowanie decyzji biznesowych lub zawodowych.

Ponadto odwołania cykliczne mogą znacznie zmniejszyć szybkość obliczeń w aplikacji Excel. W poważnych przypadkach sam błąd może powodować bardzo powolne działanie komputera, jeśli nie zostanie natychmiast rozwiązany.

Jeśli jednak pracujesz nad zaawansowanymi formułami programu Excel, w których model danych wymaga pośrednich danych wejściowych z odwołania cyklicznego w celu kontynuacji długiej funkcji, może być konieczne jego użycie. W rzeczywistości program Excel umożliwia korzystanie z tej metody obliczeń w razie potrzeby.

Na przykład w jednostkach produkcji energii, takich jak wiatraki, musisz obliczyć odzyskaną energię wyjściową z ułamka energii wejściowej. Alternatywnie, w zakładzie chemicznym inżynierowie wykorzystują trzpień wyjściowy jako wkład do tego samego lub innego kotła lub zbiornika procesowego. Takie obliczenia inżynierskie wysokiego poziomu rzeczywiście wymagają cyklicznych odniesień do obliczeń iteracyjnych.

Kiedy widzisz błąd odwołania cyklicznego w programie Excel?

Wyraźnie mówiąc, okrągłe wyskakujące okienko odniesienia nie jest komunikatem o błędzie. Jest to sposób, w jaki program Excel informuje, że napotkał odwołanie cykliczne w jednej lub wielu formułach, co może mieć wpływ na jego algorytm obliczeniowy i wydajność.

Aby uniknąć szkód spowodowanych odwołaniami cyklicznymi w formułach programu Excel, musisz wiedzieć, kiedy się pojawiają, a kiedy nie. Znajdź poniżej instancje:

• W dowolnym otwartym arkuszu lub skoroszycie tworzysz formułę i po raz pierwszy wprowadzasz odwołanie cykliczne.
• Ty lub ktoś inny usunął wszystkie odwołania cykliczne z pliku Excel i zapisał go. Teraz ponownie ty lub inny użytkownik wprowadź odwołanie cykliczne w tym samym pliku.
• Zamknąłeś wszystkie otwarte skoroszyty. Teraz otwórz inny skoroszyt, a następnie wpisz formułę zawierającą odwołanie cykliczne.

Kiedy widzisz błąd odwołania cyklicznego w programie Excel

• Właśnie otworzyłeś skoroszyt, który wcześniej zawierał jedno lub wiele odwołań cyklicznych. Zobaczysz ostrzeżenie.
• Gdy program Excel raz wyświetli komunikat ostrzegawczy dotyczący odwołań cyklicznych i klikniesz OK , nie będzie już wyświetlał tego samego wyskakującego okienka w tej samej sesji, nawet jeśli nadal będziesz tworzyć formuły z odwołaniami cyklicznymi.

W związku z tym musisz uważnie szukać powyższych instancji, aby wychwycić ostrzeżenia o odwołaniach cyklicznych i rozwiązać je, jeśli nie szukasz obliczeń iteracyjnych.

Rodzaje odwołań cyklicznych w programie Excel

Jeśli chcesz dowiedzieć się, jak znaleźć odwołania cykliczne w Excelu, musisz zrozumieć ich rodzaje. Ponieważ znajdowanie i eliminowanie odwołań cyklicznych w programie Excel nie jest zadaniem automatycznym. Znajdź poniżej typowe typy tego błędu:

Przykład użycia odwołania cyklicznego

• Zamierzone odwołania cykliczne występują, gdy używasz ich celowo do celów obliczeń iteracyjnych. Na przykład musisz wprowadzić znacznik czasu w innej komórce, gdy ktoś wprowadzi wartość w komórce, do której następuje odwołanie, za pomocą funkcji nietrwałych i logicznych, takich jak JEŻELI, LUB, SUMA.JEŻELI itp.

Niezamierzone odwołanie cykliczne w programie Excel

• Niezamierzone odwołania cykliczne podczas próby szybszej pracy z formułą programu Excel i błędnego odwołania się do komórki zawierającej formułę. Na przykład musisz zastosować funkcję SUMA z komórki A1 do A9 i pobrać wynik z komórki A10 . Ale przez pomyłkę wpisujesz taką formułę w komórce A10:

=SUMA(A2:A10)

• Ukryte odwołania cykliczne to te, które nie wyświetlają żadnych komunikatów ostrzegawczych podczas tworzenia formuły lub otwierania arkusza po raz pierwszy. Dzieje się tak, ponieważ ktoś zezwolił na iteracyjne obliczenia w pliku Excel.

Przykład pośredniego odwołania cyklicznego

• Popularne są również pośrednie odwołania cykliczne, które występują, gdy tworzysz nową formułę w dowolnej komórce, na przykład komórce A1 , a ta nowa formuła używa innej funkcji w innej komórce, na przykład komórce B1 , która już używa komórki A1 jako odwołania .

Teraz, gdy znasz podstawy odwołań cyklicznych w programie Excel, dowiedzmy się, jak znaleźć odwołania cykliczne w programie Excel poniżej:

Jak znaleźć odwołania cykliczne w programie Excel: z paska stanu

Pod warunkiem, że funkcja obliczeń iteracyjnych jest wyłączona, na pasku stanu programu Excel można zobaczyć następujące powiadomienia dotyczące odwołań cyklicznych:

• Odwołania cykliczne: C3  (lub dowolne inne odwołanie do komórki), gdy jesteś w skoroszycie zawierającym formułę cykliczną.

Wyświetlanie odwołań cyklicznych na pasku stanu programu Excel

• Odwołania cykliczne będą widoczne tylko wtedy, gdy pracujesz w arkuszu, który ich nie zawiera, ale skoroszyt zawiera kilka takich formuł cyklicznych.

Jak znaleźć odwołania cykliczne w programie Excel: korzystanie ze sprawdzania błędów

Jest to najlepszy sposób sprawdzania odwołań cyklicznych w skoroszycie lub arkuszu. Oto kroki, które musisz znać:

• Otwórz arkusz, co do którego podejrzewasz, że zawiera formuły cykliczne.
• Kliknij menu Formuły na wstążce programu Excel.

Użyj sprawdzania błędów, aby znaleźć odwołania cykliczne w programie Excel

• W grupie poleceń Kontrola formuł kliknij listę rozwijaną Sprawdzanie błędów  .
• Najedź kursorem na opcję Odwołania cykliczne , aby zobaczyć wszystkie dostępne wystąpienia we wszystkich arkuszach.
• Zaznacz każdą instancję, a program Excel automatycznie wskaże te komórki.

Jak znaleźć odwołania cykliczne w programie Excel: na podstawie śledzenia formuł

Jeśli uważasz, że metoda sprawdzania błędów nie jest wystarczająco wydajna do wykrywania dużej liczby formuł z odwołaniami cyklicznymi, możesz spróbować wykonać następujące czynności, aby użyć metody śledzenia formuł:

• Zaznacz dowolną komórkę, do której istnieje odwołanie, lub komórkę zawierającą formułę.
• Przejdź do karty Formuły w menu wstążki programu Excel, a następnie kliknij opcję Śledź poprzedniki .
• Excel podświetli strzałkami komórki, które mają wpływ na wynik formuły w bieżącej komórce.

Użyj śledzenia formuły, aby znaleźć odwołania cykliczne w programie Excel

• Możesz także kliknąć opcję Śledź zależności , aby wykryć komórki, na które ma wpływ wartość wybranego odwołania do komórki.
• Jeśli program Excel nie wyświetla żadnych błędów po kliknięciu powyższych poleceń, oznacza to, że w arkuszu nie ma odwołań cyklicznych.
• Powtórz kroki na wszystkich arkuszach, aby sprawdzić cały skoroszyt.

Ta metoda jest szczególnie ważna, gdy funkcje obliczeń iteracyjnych są aktywne w arkuszu programu Excel.

Jak znaleźć odwołania cykliczne w programie Excel: korzystanie z opcji programu Excel

• Kliknij kartę Plik na wstążce programu Excel, a następnie otwórz Opcje programu Excel na wyświetlonym pasku bocznym.
• W oknie dialogowym Opcje programu Excel wybierz menu Formuły na pasku bocznym, a następnie usuń zaznaczenie pola wyboru Włącz obliczenia iteracyjne .

Jak dezaktywować obliczenia iteracyjne w programie Excel

• Kliknij OK, aby zapisać zmiany.
• Teraz skoroszyt lub arkusz roboczy automatycznie wyświetli ostrzeżenie o odwołaniu cyklicznym.
• Jeśli nie pojawi się natychmiast, zapisz skoroszyt, zamknij go i otwórz ponownie, aby zobaczyć ostrzeżenie.
• Po wyświetleniu ostrzeżenia sprawdź pasek stanu programu Excel, aby dowiedzieć się, która komórka znajduje się w odwołaniu cyklicznym.

Jak rozwiązać odwołania cykliczne w programie Excel

Najpewniejszym sposobem poprawienia jednego lub wielu błędów odwołania cyklicznego w programie Excel jest ręczna edycja każdej formuły, która go zawiera. W ten sposób można rozwiązać zarówno przypadkowe, jak i pośrednie odwołania cykliczne w programie Excel.

Jak rozwiązać odwołania cykliczne w programie Excel

Innym łatwym sposobem jest usunięcie formuły z bieżącej komórki i umieszczenie jej w innym miejscu w tym samym arkuszu.

Jak znaleźć referencje cykliczne w Arkuszach Google (metoda bonusowa)

Podobnie jak Excel, Arkusze Google nie zezwalają na żadne odwołania cykliczne w jednej lub kilku formułach, jeśli nie włączono funkcji obliczeń iteracyjnych. Dlatego Arkusze Google natychmiast pokażą #REF! błąd w komórce zawierającej formułę kołową.

Arkusze Google Jak znaleźć referencje cykliczne

Dlatego możesz po prostu zaznaczyć wszystkie komórki zawierające #REF! ostrzeżenie, aby znaleźć i rozwiązać odwołania cykliczne w Arkuszach Google.

Okólnik Excela: ostatnie słowa

Teraz znasz wszystkie sekrety znajdowania odwołań cyklicznych w programie Excel. Użyj metody, którą lubisz i porzuć wszystkie odwołania cykliczne, aby upewnić się, że nie pomylisz się z niewiarygodnymi danymi z raportu programu Excel lub pulpitu matematycznego.

Następnie  najlepsze szablony kalendarzy programu Excel na dowolny rok  i  najlepsze szablony budżetu programu Excel .