Jak znaleźć odwołania cykliczne w programie Excel na 4 sposoby

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.

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ść wynik w komórce B1 samo.

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

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ł.

Będziesz mieć zaufanie do błędnych danych obliczonych 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.

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 wpłynąć na jego algorytm obliczeniowy i wydajność.

Aby uniknąć szkód spowodowanych przez odwołania cykliczne 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.

• Właśnie otworzyłeś skoroszyt, który wcześniej zawierał jedno lub wiele odwołań cyklicznych. Zobaczysz ostrzeżenie.
• Gdy program Excel wyświetli raz okrągły komunikat ostrzegawczy o odwołaniu i klikniesz OK, nie będzie już wyświetlać tego samego wyskakującego okienka w tej samej sesji, nawet jeśli będziesz tworzyć więcej cyklicznych formuł, do których istnieją odwołania.

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.

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

• 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ł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ć SUMA funkcja z komórki A1 Do A9 i pobierz wynik w komórce 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.

• Popularne są również pośrednie odwołania cykliczne, które pojawiają się podczas tworzenia nowej formuły w dowolnej komórce, na przykład w komórce A1, a ta nowa formuła użyłaby innej funkcji w innej komórce, na przykład komórka B1, który już korzysta z komórki A1 jako odniesienie.

Teraz, gdy zrozumiałeś podstawy odwołań cyklicznych w Excelu, dowiedzmy się, jak znaleźć odwołania cykliczne w Excelu poniżej:

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:

• Odnośniki cykliczne: C3 (lub dowolne inne odwołanie do komórki), gdy jesteś w arkuszu zawierającym formułę kołową w skoroszycie.

• Zobaczysz tylko Odnośniki okólne jeśli jesteś w arkuszu, który nie zawiera żadnych, ale skoroszyt zawiera kilka z tych okrągłych formuł.

To najlepszy sposób na sprawdzenie 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 Formuły menu na wstążce programu Excel.

• w Audyt formuły grupę poleceń, kliknij Sprawdzanie błędówLista rozwijana.
• Najedź kursorem na Odnośniki okólne opcję, aby wyświetlić wszystkie dostępne wystąpienia we wszystkich arkuszach.
• Zaznacz każdą instancję, a program Excel automatycznie wskaże te komórki.

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łę.
• Idź do Formuły w menu wstążki programu Excel, a następnie kliknij Śledź precedensy.
• Excel podświetli strzałkami komórki, które mają wpływ na wynik formuły w bieżącej komórce.

• Możesz także kliknąć Ś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.

• Kliknij Plik na wstążce programu Excel, a następnie otwórz Opcje Excela z paska bocznego, który się pojawi.
• Na Opcje Excela oknie dialogowym wybierz opcję Formuły menu z paska bocznego, a następnie usuń zaznaczenie pola wyboru dla Włącz obliczenia iteracyjne.

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

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.

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

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

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.

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

Dalej, najlepsze szablony kalendarzy Excel na dowolny rok i najlepsze szablony budżetu w Excelu.