Jak znaleźć odwołania cykliczne w programie Excel

Podczas pracy z programem Microsoft Excel od czasu do czasu można napotkać tak zwane odwołanie cykliczne. Dzieje się tak, gdy formuła w komórce odnosi się do samej siebie, powodując nieskończoną pętlę w procesie obliczeń.

Aby znaleźć odwołania cykliczne w programie Excel, użyj narzędzia „Sprawdzanie błędów” na karcie „Formuły”, aby zidentyfikować komórki z zależnością cykliczną.

W tym artykule pokazano, jak znaleźć odwołania cykliczne w programie Excel i naprawić je. Dowiesz się również, jak im zapobiegać podczas pracy z dużymi lub złożonymi arkuszami.

Spis treści

• Czym są odwołania cykliczne
• Okno komunikatu o błędzie odwołania cyklicznego
• Jak znaleźć odwołania cykliczne w programie Excel
• Jak znaleźć odwołania cykliczne za pomocą paska stanu programu Excel
• Jak znaleźć ukryte odwołania cykliczne w programie Excel
  • Jak korzystać z narzędzia zależnego od śledzenia
  • Narzędzie Śledź precedensy
• 2 sposoby usuwania odwołań cyklicznych w programie Excel
  • 2. Użyj alternatywnych formuł
• 5 wskazówek, jak zapobiegać odsyłaczom cyklicznym
  • 1. Zachowaj ostrożność podczas wybierania zakresów
  • 2. Starannie planuj swoje receptury
  • 3. Użyj alternatywnych formuł
  • 4. Podziel złożone formuły na mniejsze części
  • 5. Na bieżąco rozwiązuj problemy
• Jak włączyć obliczenia iteracyjne i po co zawracać sobie głowę?

Czym są odwołania cykliczne

Odwołania cykliczne występują w programie Excel, gdy obliczenie formuły w komórce zależy od własnej wartości komórki. Tworzy to pętlę i prowadzi do nieprawidłowych lub nieprzewidywalnych wyników.

Łatwiej to zrozumieć na prostym przykładzie.

Załóżmy, że masz dwie liczby w komórkach A1 i B1. Chcesz użyć formuły w komórce A3, która dodaje do siebie dwie liczby. Formuła w komórce A3 powinna wyglądać tak:

=A1 + B1

Jeśli jednak popełnisz błąd i uwzględnisz C1 w obliczeniach, masz teraz odniesienie cykliczne:

=A1 + B1 + C1

Może to być oczywisty problem, którego należy unikać w przypadku dwóch komórek. Jednak podczas pracy z dużymi zakresami komórek prawdopodobieństwo wystąpienia błędów jest większe.

Na szczęście program Excel został zaprojektowany do obsługi takich sytuacji, wyświetlając komunikat ostrzegawczy po wykryciu odwołania cyklicznego.

Jednak w niektórych przypadkach możesz napotkać odwołanie cykliczne bez otrzymania żadnego alertu. Dlatego ważne jest, aby wiedzieć, jak ręcznie znaleźć i rozwiązać te problemy.

Okno komunikatu o błędzie odwołania cyklicznego

Domyślnie program Excel monitoruje odwołania cykliczne podczas wprowadzania formuł do komórek. Po naciśnięciu klawisza Enter w komórce, w której występuje problem, wyskakujące okienko wyświetli komunikat o błędzie odwołania cyklicznego.

To jest tekst wyjaśniający:

Istnieje co najmniej jedno odwołanie cykliczne, w którym formuła odnosi się bezpośrednio lub pośrednio do własnej komórki. Może to spowodować nieprawidłowe obliczenia. Spróbuj usunąć lub zmienić te odwołania albo przenieść formuły do ​​innych komórek.

Kiedy zobaczysz to okno dialogowe, powinieneś kliknąć „OK” i użyć metod opisanych w tym artykule, aby rozwiązać problem.

Jednak nie musisz natychmiast naprawiać błędu. Możesz kontynuować pracę z arkuszem kalkulacyjnym z zamiarem późniejszego rozwiązania problemu.

W takim przypadku musisz wiedzieć, jak znaleźć odwołania cykliczne w programie Excel po odrzuceniu pola ostrzeżenia.

Jak znaleźć odwołania cykliczne w programie Excel

Program Excel ma wbudowane narzędzie do sprawdzania błędów, które znajduje kilka rodzajów błędów, w tym odwołania cykliczne. Dostęp do narzędzia uzyskujesz na karcie Formuły.

Ten obraz przedstawia lokalizację w grupie Inspekcja formuł.

Teraz wiesz, jak znaleźć narzędzie, oto szczegółowe kroki, aby go użyć:

1. Przejdź do zakładki Formuły.

1. Kliknij listę rozwijaną Sprawdzanie błędów w grupie Kontrola formuł.

2. Wybierz „Referencje cykliczne” z listy rozwijanej.

3. Kliknij łącze, aby przeskoczyć kursor do pierwszej komórki z problemem.

Poniższy rysunek pokazuje, że narzędzie znalazło błąd cykliczny w komórce G1. Wyświetlana komórka jest hiperłączem.

Po kliknięciu łącza kursor przesuwa się do komórki. Jest to bardzo pomocne w przypadku dużej ilości danych.

Po rozwiązaniu pierwszego odwołania cyklicznego możesz wrócić do narzędzia do sprawdzania błędów, aby znaleźć następne odwołanie na arkuszu.

Wykonaj czynności opisane w dalszych sekcjach tego artykułu, aby rozwiązać problem, i użyj narzędzia, aby przejść do następnego problemu.

Jeśli nie ma więcej okrągłych wydań, pozycja menu „Odwołania cykliczne” w menu rozwijanym będzie wyszarzona. Nie masz już nic do roboty.

Jak znaleźć odwołania cykliczne za pomocą paska stanu programu Excel

Innym sposobem na wykrycie odwołania cyklicznego jest spojrzenie na pasek stanu u dołu arkusza programu Excel.

Gdy w arkuszu znajduje się co najmniej jeden problem, na pasku stanu wyświetlana jest lokalizacja jednego odwołania do komórki problemu.

Na przykład, jeśli w komórce C1 znajduje się odwołanie cykliczne i nie ma innych problemów z arkuszem kalkulacyjnym, na pasku stanu pojawi się informacja: „Odwołania cykliczne: C1”.

To zdjęcie pokazuje, gdzie zobaczyć wyświetlacz.

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

Ukryte odwołania cykliczne w programie Excel to odwołania cykliczne, które nie są natychmiast wykrywane przez wbudowane funkcje błędów. Innymi słowy, program Excel nie wyświetla standardowego ostrzeżenia.

Dzieje się tak, gdy komórka odwołuje się do innej komórki pośrednio, poprzez serię komórek pośrednich, i ostatecznie odnosi się z powrotem do pierwotnej komórki.

Program Excel ma dwa narzędzia ułatwiające śledzenie relacji i badanie tych problemów:

1. Narzędzie śledzenia zależności

2. Narzędzie Śledź precedensy

Jak korzystać z narzędzia zależnego od śledzenia

Doświadczeni użytkownicy programu Excel korzystają z narzędzia Śledzenie zależności, aby zbadać te problemy i naprawić odwołania cykliczne. Wybierając jedną lub więcej komórek i korzystając z tego narzędzia, program Excel wyświetli zależne komórki.

Narzędzie jest dostępne w sekcji Audyt formuł na zakładce Formuły.

Aby użyć narzędzia, wykonaj następujące kroki:

1. Kliknij komórkę lub komórki, dla których chcesz śledzić osoby na utrzymaniu.

2. Przejdź do zakładki „Formuły” na wstążce programu Excel.

3. Kliknij przycisk „Śledź zależności” w grupie „Kontrola formuł”.

4. Przejrzyj strzałkę lub strzałki śledzące pokazujące zależności.

Program Excel pokazuje strzałki od wartości aktywnej komórki do wszystkich komórek, które są od niej zależne. Niebieska strzałka na poniższym obrazku pokazuje, że komórka D6 jest zależna od komórki B2.

Narzędzie Śledź precedensy

Precedensy są przeciwieństwem zależności. Narzędzie Śledź poprzedniki pozwala wybrać komórkę zawierającą formułę i zobaczyć wszystkie komórki, od których ona zależy, tj. precedensy.

Narzędzie jest dostępne w sekcji Audyt formuł na zakładce Formuły.

Aby użyć narzędzia, wykonaj następujące kroki:

1. Kliknij komórkę zawierającą formułę, którą chcesz zbadać.

2. Przejdź do karty Formuła na wstążce programu Excel.

3. Kliknij przycisk „Śledzenie precedensów” w grupie „Kontrola formuł”.

4. Przejrzyj strzałki śledzenia wyświetlane w programie Excel.

Niebieskie strzałki na poniższym obrazku pokazują, że komórka D6 jest zależna od zakresu komórek A2:B5.

Korzystanie z obu narzędzi pomaga znaleźć odwołania cykliczne programu Excel, które nie są od razu widoczne. Następnym krokiem jest naprawienie problemu w wybranej komórce. Następnie możesz kontynuować wyszukiwanie tego samego problemu w innych komórkach.

2 sposoby usuwania odwołań cyklicznych w programie Excel

Istnieją dwie główne metody rozwiązywania odwołań cyklicznych:

• przerwanie cyklicznego odniesienia

• przy użyciu alternatywnych formuł

Wyjaśnijmy oba sposoby.

1. Złamanie odnośnika okrężnego

Przerwanie cyklicznego odwołania w programie Excel to skuteczny sposób rozwiązania problemu. Wymaga to zidentyfikowania komórek zaangażowanych w odwołanie cykliczne i odpowiedniego zmodyfikowania ich formuł.

Aby to zrobić, wykonaj czynności opisane wcześniej, aby znaleźć adres komórki.

Następnie powinieneś sprawdzić formułę na pasku formuły. Poszukaj odniesienia do samej komórki.

W naszym pierwszym przykładzie odwołania cyklicznego formuła dodawania w komórce C1 zawierała odwołanie do samej komórki:

=A1 + B1 + C1

Można to naprawić, usuwając ostatni dodatek. Po rozwiązaniu tego problemu przejdź do znalezienia innych odwołań cyklicznych i zajmij się nimi.

2. Użyj alternatywnych formuł

Niektóre z najczęstszych przyczyn odwołań cyklicznych występują podczas sumowania lub uśredniania komórek. Błędem jest uwzględnienie komórki wyników w formule.

W naszym poprzednim przykładzie formuła używała operatorów dodawania do obliczania sumy zakresu komórek. Alternatywną metodą jest użycie funkcji SUMA programu Excel.

Aby naprawić przykład, zamieniasz formułę na:

=SUMA(A1:B1)

Podobnie możesz uśrednić zakres za pomocą wzoru takiego jak =(A1+B1)/2. Jednak prawdopodobieństwo popełnienia błędu przez funkcję ŚREDNIA jest mniejsze.

=ŚREDNIA(A1:B1)

5 wskazówek, jak zapobiegać odsyłaczom cyklicznym

Aby zapobiec odwołaniom cyklicznym w programie Microsoft Excel, konieczne jest dokładne zrozumienie interakcji formuł w arkuszu programu Excel.

Identyfikując relacje między komórkami i mając świadomość, jak te połączenia ze sobą współpracują, można uniknąć tworzenia formuł cyklicznych.

Oto pięć naszych najlepszych wskazówek, które pomogą Ci uniknąć odwołań cyklicznych.

1. Zachowaj ostrożność podczas wybierania zakresów

Z mojego doświadczenia wynika, że ​​jedną z najczęstszych przyczyn odwołań cyklicznych jest szybkie wybieranie przez użytkowników zakresów, które mają zostać uwzględnione w ich formułach.

Podczas przesuwania w pionie i/lub poziomie w celu pogrupowania komórek łatwo jest wybrać jeszcze jedną komórkę, która sąsiaduje z odpowiednim zakresem. Jeśli ta komórka jest również komórką wyników, przypadkowo utworzyłeś odwołanie cykliczne.

Ten błąd jest zwykle popełniany podczas pracy z dużymi zbiorami danych, na przykład przy przeprowadzaniu testów chi-kwadrat. Rozwiązaniem jest ostrożność przy wyborze zakresu.

2. Starannie planuj swoje receptury

Przed wprowadzeniem formuł zastanów się nad logiką swoich obliczeń i nad tym, jak powinny one przepływać przez komórki.

Możesz z góry nakreślić obliczenia, korzystając z narzędzi do tworzenia diagramów lub tablicy.

Pomoże to zidentyfikować wszelkie możliwe pętle, które mogą prowadzić do odwołań cyklicznych.

3. Użyj alternatywnych formuł

Niektóre funkcje, takie jak POŚR lub PRZESUNIĘCIE , mogą przypadkowo utworzyć odwołania cykliczne, jeśli są używane niewłaściwie.

Rozważ użycie alternatywnych funkcji, które zapewniają podobną funkcjonalność bez ryzyka odwołań cyklicznych.

4. Podziel złożone formuły na mniejsze części

Zamiast tworzyć jedną złożoną formułę z wieloma zagnieżdżonymi funkcjami, rozważ podzielenie obliczeń na mniejsze, łatwiejsze w zarządzaniu części.

To nie tylko ułatwia zrozumienie i utrzymanie skoroszytu, ale także zmniejsza szanse na wprowadzenie odwołania cyklicznego.

5. Na bieżąco rozwiązuj problemy

Podczas pracy z dużymi lub skomplikowanymi skoroszytami konieczne jest również monitorowanie odwołań cyklicznych. Zamiast ignorować problemy, dopóki nie zostanie zebrany duży zestaw danych i formuł, lepiej jest naprawiać każdy błąd, gdy się pojawi.

Możesz na chwilę zignorować komunikat ostrzegawczy dotyczący odwołania cyklicznego. Należy jednak skorzystać z funkcji sprawdzania błędów, aby szybko nadrobić zaległości w istniejących odwołaniach cyklicznych.

Dzięki temu łatwiej je naprawić i zapobiec ich wpływowi na skoroszyt.

Jak włączyć obliczenia iteracyjne i po co zawracać sobie głowę?

Zwykle chcesz uniknąć odwołań cyklicznych w arkuszu. Istnieją jednak scenariusze, w których są one konieczne.

Na przykład niektóre obliczenia inżynierskie lub naukowe wymagają wykorzystania wyniku poprzedniego obliczenia. Jest to znane jako rozwiązanie iteracyjne.

Każda pętla udoskonala wyniki obliczeń, aż do osiągnięcia określonego progu lub celu.

W takich przypadkach konieczne jest włączenie obliczeń iteracyjnych w ustawieniach programu Excel, aby umożliwić zamierzone odwołania cykliczne. Pozwala to oprogramowaniu powtórzyć obliczenia określoną liczbę razy, zbiegając się do rozwiązania.

Wykonaj następujące kroki, aby odwołanie cykliczne działało w pliku Excel:

1. Kliknij kartę Plik.

2. Wybierz opcje.

3. Wybierz kategorię Formuły.

4. W sekcji „Opcje obliczeń” zaznacz pole wyboru „Włącz obliczenia iteracyjne”.

5. Zmień liczbę w polu maksymalnej liczby iteracji lub pozostaw domyślną wartość 100.

6. Użyj pola maksymalnej zmiany, aby zmniejszyć liczbę iteracji zgodnie z wymaganiami.