Jednym z najczęstszych ostrzeżeń o błędach, jakie użytkownicy napotykają w programie Excel, jest „Okólnik”. Tysiące użytkowników ma ten sam problem i pojawia się, gdy formuła odwołuje się bezpośrednio lub pośrednio do własnej komórki, powodując niekończącą się pętlę obliczeń.
Na przykład masz dwie wartości w komórkach B1 i B2. Gdy formuła =B1+B2 zostanie wprowadzona do B2, tworzy odwołanie cykliczne; formuła w B2 wielokrotnie przelicza się, ponieważ za każdym razem, gdy oblicza, zmienia się wartość B2.
Większość odniesień cyrkularnych to niezamierzone błędy; Excel ostrzeże Cię o tym. Istnieją jednak również zamierzone odwołania cykliczne, które są wykorzystywane do wykonywania obliczeń iteracyjnych. Niezamierzone odwołania cykliczne w arkuszu mogą powodować nieprawidłowe obliczanie formuły.
Dlatego w tym artykule wyjaśnimy wszystko, co musisz wiedzieć o odwołaniach cyklicznych, a także jak znajdować, naprawiać, usuwać i używać odwołań cyklicznych w programie Excel.
Jak znaleźć i obsłużyć odwołanie cykliczne w programie Excel?
Podczas pracy z programem Excel czasami napotykamy błędy odwołań cyklicznych, które występują po wprowadzeniu formuły zawierającej komórkę, w której znajduje się formuła. Zasadniczo dzieje się tak, gdy formuła próbuje sama obliczyć.
Na przykład masz kolumnę liczb w komórce A1:A4 i używasz funkcji SUMA (=SUMA(A1:A5)) w komórce A5. Komórka A5 bezpośrednio odnosi się do swojej własnej komórki, co jest błędne. W związku z tym otrzymasz następujące ostrzeżenie o odwołaniu cyklicznym:
Po otrzymaniu powyższego komunikatu ostrzegawczego możesz kliknąć przycisk „Pomoc”, aby dowiedzieć się więcej o błędzie, lub zamknąć okno komunikatu o błędzie, klikając przycisk „OK” lub „X” i otrzymać jako wynik „0”.
Czasami cykliczne pętle odwołań mogą spowodować awarię obliczeń lub spowolnić działanie arkusza roboczego. Okólnik może również prowadzić do wielu innych problemów, które nie będą od razu widoczne. Dlatego najlepiej ich unikać.
Bezpośrednie i pośrednie odniesienia do okólnika
Odwołania kołowe można podzielić na dwa typy: bezpośrednie odwołania kołowe i pośrednie odwołania kołowe.
Bezpośrednie odniesienie
Bezpośrednie odwołanie cykliczne jest dość proste. Komunikat ostrzegawczy o bezpośrednim odwołaniu cyklicznym pojawia się, gdy formuła odwołuje się bezpośrednio do własnej komórki.
W poniższym przykładzie formuła w komórce A2 bezpośrednio odwołuje się do własnej komórki (A2).
Gdy pojawi się komunikat ostrzegawczy, możesz kliknąć „OK”, ale spowoduje to tylko „0”.
Pośrednie odniesienie do okólnika
Pośrednie odwołanie cykliczne w programie Excel występuje, gdy wartość w formule odwołuje się do własnej komórki, ale nie bezpośrednio. Innymi słowy, odwołanie cykliczne może być utworzone przez dwie komórki odwołujące się do siebie.
Wyjaśnijmy na tym prostym przykładzie.
Teraz wartość zaczyna się od A1, która ma wartość 20.
Następnie komórka C3 odwołuje się do komórki A1.
Następnie komórka A5 odwołuje się do komórki C3.
Teraz zastąp wartość 20 w komórce A1 formułą, jak pokazano poniżej. Każda inna komórka jest zależna od komórki A1. Użycie odwołania do dowolnej innej komórki z poprzednią formułą w komórce A1 spowoduje wyświetlenie ostrzeżenia o odwołaniu cyklicznym. Ponieważ formuła w A1 odwołuje się do komórki A5, która odwołuje się do C3, a komórka C3 odwołuje się do komórki A1, stąd odwołanie cykliczne.
Po kliknięciu przycisku „OK” w komórce A1 zostanie wyświetlona wartość 0, a program Excel utworzy połączoną linię pokazującą śledzenie poprzedników i śledzenie zależności, jak pokazano poniżej. Możemy użyć tej funkcji, aby łatwo znajdować i naprawiać/usuwać odwołania cykliczne.
Jak włączyć / wyłączyć odwołania cykliczne w programie Excel?
Domyślnie obliczenia iteracyjne są wyłączone (wyłączone) w programie Excel. Obliczenia iteracyjne są obliczeniami powtarzalnymi, dopóki nie spełnią określonego warunku. Gdy jest wyłączona, program Excel wyświetla komunikat Circular Reference i zwraca jako wynik 0.
Czasami jednak do obliczenia pętli potrzebne są odwołania cykliczne. Aby korzystać z odwołań cyklicznych, musisz włączyć obliczenia iteracyjne w programie Excel, co pozwoli ci wykonać obliczenia. Teraz pokażemy Ci, jak włączyć lub wyłączyć obliczenia iteracyjne.
W Excel 2010, Excel 2013, Excel 2016, Excel 2019 i Microsoft 365 przejdź do zakładki „Plik” w lewym górnym rogu programu Excel, a następnie kliknij „Opcje” w lewym okienku.
W oknie Opcje programu Excel przejdź do zakładki „Formuła” i zaznacz pole „Włącz obliczanie iteracyjne” w sekcji „Opcje obliczeń”. Następnie kliknij „OK”, aby zapisać zmiany.
Umożliwi to obliczenia iteracyjne, a tym samym umożliwi odniesienie cykliczne.
Aby to osiągnąć w poprzednich wersjach programu Excel, wykonaj następujące kroki:
- W programie Excel 2007 kliknij przycisk pakietu Office > Opcje programu Excel > Formuły > obszar iteracji.
- W programie Excel 2003 i wcześniejszych wersjach musisz przejść do Menu > Narzędzia > Opcje > karta Obliczenia.
Maksymalna liczba iteracji i maksymalna zmiana parametrów
Po włączeniu obliczeń iteracyjnych można kontrolować obliczenia iteracyjne, określając dwie opcje dostępne w sekcji Włącz obliczenia iteracyjne, jak pokazano na zrzucie ekranu poniżej.
- Maksymalna liczba iteracji – Ta liczba określa, ile razy formuła powinna zostać przeliczona przed podaniem końcowego wyniku. Domyślna wartość to 100. Jeśli zmienisz ją na „50”, Excel powtórzy obliczenia 50 razy, zanim poda ostateczny wynik. Pamiętaj, że im większa liczba iteracji, tym więcej zasobów i czasu zajmuje obliczenie.
- Maksymalna zmiana – Określa maksymalną zmianę między wynikami obliczeń. Ta wartość określa dokładność wyniku. Im mniejsza liczba, tym dokładniejszy byłby wynik i tym dłużej trwałoby obliczenie arkusza roboczego.
Jeśli opcja obliczeń iteracyjnych jest włączona, nie otrzymasz żadnego ostrzeżenia za każdym razem, gdy w arkuszu znajduje się odwołanie cykliczne. Włączaj obliczenia interaktywne tylko wtedy, gdy jest to absolutnie konieczne.
Znajdź okólnik w programie Excel
Załóżmy, że masz duży zestaw danych i otrzymałeś ostrzeżenie o odwołaniu cyklicznym, nadal musisz dowiedzieć się, gdzie (w której komórce) wystąpił błąd, aby go naprawić. Aby znaleźć odwołania cykliczne w programie Excel, wykonaj następujące kroki:
Korzystanie z narzędzia do sprawdzania błędów
Najpierw otwórz arkusz roboczy, w którym nastąpiło odwołanie cykliczne. Przejdź do zakładki „Formuła”, kliknij strzałkę obok narzędzia „Sprawdzanie błędów”. Następnie po prostu najedź kursorem na opcję „Odwołania cykliczne”, Excel wyświetli listę wszystkich komórek zaangażowanych w odwołanie cykliczne, jak pokazano poniżej.
Kliknij dowolny adres komórki na liście, a zostaniesz przeniesiony do tego adresu komórki, aby rozwiązać problem.
Korzystanie z paska stanu
Odwołanie cykliczne można również znaleźć na pasku stanu. Na pasku stanu programu Excel zostanie wyświetlony najnowszy adres komórki z odwołaniem cyklicznym, takim jak „Odwołania cykliczne: B6” (patrz zrzut ekranu poniżej).
Są pewne rzeczy, o których powinieneś wiedzieć podczas obsługi odwołań cyklicznych:
- Na pasku stanu adres komórki odwołania cyklicznego nie będzie wyświetlany, gdy włączona jest opcja obliczania iteracyjnego, dlatego należy ją wyłączyć, zanim zaczniesz przeglądać skoroszyt w poszukiwaniu odwołań cyklicznych.
- W przypadku, gdy w aktywnym arkuszu nie zostanie znalezione odwołanie cykliczne, pasek stanu wyświetla tylko „Odwołania cykliczne” bez adresu komórki.
- Monit o odwołanie okrężny zostanie wyświetlony tylko raz, a po kliknięciu „OK” monit nie zostanie ponownie wyświetlony następnym razem.
- Jeśli skoroszyt zawiera odwołania cykliczne, będzie wyświetlany monit za każdym razem, gdy go otworzysz, dopóki nie rozwiążesz odwołania cyklicznego lub nie włączysz obliczeń iteracyjnych.
Usuń odwołanie cykliczne w programie Excel
Znalezienie referencji Circular jest łatwe, ale naprawienie tego nie jest takie proste. Niestety, w programie Excel nie ma opcji, która pozwoliłaby na jednoczesne usunięcie wszystkich odwołań cyklicznych.
Aby naprawić odwołania cykliczne, musisz znaleźć każde odwołanie cykliczne indywidualnie i spróbować je zmodyfikować, całkowicie usunąć formułę cykliczną lub zastąpić ją inną.
Niekiedy w prostych formułach wystarczy dostosować parametry formuły, aby nie odwoływała się do siebie. Na przykład zmień formułę w B6 na =SUMA(B1:B5)*A5 (zmieniając B6 na B5).
Zwróci wynik obliczenia jako „756”.
W przypadkach, gdy trudno jest znaleźć odwołanie cykliczne programu Excel, można użyć funkcji Trace Precedents i Trace Dependents, aby prześledzić je z powrotem do źródła i rozwiązać je jeden po drugim. Strzałka pokazuje, na które komórki wpływa aktywna komórka.
Istnieją dwie metody śledzenia, które mogą pomóc w usuwaniu odwołań cyklicznych, pokazując relacje między formułami i komórkami.
Aby uzyskać dostęp do metod śledzenia, przejdź do zakładki „Formuły”, a następnie kliknij „Śledź poprzedniki” lub „Śledź zależności” w grupie Formuła audytu.
Śledź precedensy
Po wybraniu tej opcji śledzi komórki, które wpływają na wartość aktywnej komórki. Rysuje niebieską linię wskazującą, które komórki wpływają na bieżącą komórkę. Skrót do korzystania z precedensów śledzenia to Alt + T U T.
W poniższym przykładzie niebieska strzałka pokazuje komórki, które wpływają na wartość B6, to B1:B6 i A5. Jak widać poniżej, komórka B6 jest również częścią formuły, co czyni ją odwołaniem cyklicznym i powoduje, że formuła zwraca jako wynik „0”.
Można to łatwo naprawić, zamieniając B6 na B5 w argumencie SUMA: =SUMA(B1:B5).
Śledzenie zależności
Funkcja śledzenia zależności śledzi komórki, które są zależne od wybranej komórki. Ta funkcja rysuje niebieską linię wskazującą, na które komórki wpływa wybrana komórka. Oznacza to, że wyświetla, które komórki zawierają formuły odwołujące się do aktywnej komórki. Klawisz skrótu do korzystania z osób na utrzymaniu to Alt + T U D.
W poniższym przykładzie komórka D3 ma wpływ na komórkę B4. Jego wartość jest zależna od B4 w celu uzyskania wyników. Stąd zależny od śladu rysuje niebieską linię od B4 do D3, wskazując, że D3 jest zależny od B4.
Celowe używanie odwołań cyklicznych w programie Excel
Celowe używanie odwołań cyklicznych nie jest zalecane, ale mogą wystąpić rzadkie przypadki, w których potrzebne jest odwołanie cykliczne, aby uzyskać żądane dane wyjściowe.
Wyjaśnijmy to na przykładzie.
Na początek włącz „Obliczenia iteracyjne” w skoroszycie programu Excel. Po włączeniu obliczeń iteracyjnych możesz zacząć używać odwołań cyklicznych na swoją korzyść.
Załóżmy, że kupujesz dom i chcesz dać agentowi 2% prowizji od całkowitego kosztu domu. Całkowity koszt zostanie obliczony w komórce B6, a procent prowizji (opłata agenta) zostanie obliczona w B4. Prowizja naliczana jest od całkowitego kosztu, a całkowity koszt zawiera prowizję. Ponieważ komórki B4 i B6 są od siebie zależne, tworzy odwołanie cykliczne.
Wprowadź formułę, aby obliczyć całkowity koszt w komórce B6:
=SUMA(B1:B4)
Ponieważ całkowity koszt obejmuje opłatę agenta, w powyższym wzorze uwzględniliśmy B4.
Aby obliczyć opłatę agenta w wysokości 2%, wprowadź ten wzór w B4:
=B6*2%Teraz formuła w komórce B4 zależy od wartości B6, aby obliczyć 2% całkowitej opłaty, a formuła w B6 zależy od B4 do obliczenia całkowitego kosztu (w tym opłaty agenta), stąd odwołanie cykliczne.
Jeśli obliczenia iteracyjne są włączone, program Excel nie wyświetli ostrzeżenia ani 0 w wyniku. Zamiast tego wynik komórek B6 i B4 zostanie obliczony, jak pokazano powyżej.
Opcja obliczeń iteracyjnych jest zwykle domyślnie wyłączona. Jeśli go nie włączyłeś i kiedy wpiszesz formułę w B4, która utworzy odwołanie cykliczne. Excel wyświetli ostrzeżenie, a po kliknięciu „OK” pojawi się strzałka śledząca.
Otóż to. To wszystko, co musisz wiedzieć o odwołaniach cyklicznych w programie Excel.