Podczas pracy z formułami programu Excel możesz czasem zobaczyć następujące ostrzeżenie.
Wskazówka ta informuje, że w arkuszu znajduje się odwołanie kołowe, które może prowadzić do nieprawidłowego obliczenia przez formuły. W tym samouczku omówię wszystko, co musisz wiedzieć o odwołaniu kołowym, a także jak znaleźć i usunąć odwołanie kołowe w Excelu.
Zacznijmy więc!
Tutorial obejmuje:
Co to jest odwołanie kołowe w Excelu?
W prostych słowach, odwołanie kołowe ma miejsce, gdy kończysz posiadanie formuły w komórce – która sama w sobie używa komórki (w której została wprowadzona) do obliczeń.
Postaram się to wyjaśnić na prostym przykładzie.
Załóżmy, że masz zbiór danych w komórce A1:A5 i używasz poniższej formuły w komórce A6:
=SUM(A1:A6)
To da ci ostrzeżenie o odwołaniu kołowym.
Powodem tego jest fakt, że chcesz zsumować wartości w komórce A1:A6, a wynik powinien znaleźć się w komórce A6.
Tworzy to pętlę, ponieważ Excel po prostu ciągle dodaje nową wartość w komórce A6, która ciągle się zmienia (stąd pętla odwołań kołowych).
Jak znaleźć odwołania kołowe w Excelu?
Pomimo, że monit ostrzegawczy dotyczący odwołań kołowych jest na tyle uprzejmy, aby powiedzieć ci, że istnieje w twoim arkuszu, nie mówi ci, gdzie to się dzieje i jakie odwołania do komórek są przyczyną tego zjawiska.
Jeśli więc próbujesz znaleźć i obsłużyć odwołania kołowe w arkuszu, musisz znać sposób, aby jakoś je znaleźć.
Poniżej znajdują się kroki, aby znaleźć okrągłe odniesienie w programie Excel:
- Uaktywnij arkusz, w którym znajduje się odwołanie kołowe
- Kliknij zakładkę Formuły
- W grupie Edycja formuły kliknij ikonę rozwijaną Sprawdzanie błędów (mała strzałka skierowana w dół po prawej stronie)
- Najedź kursorem na opcję Odwołania kołowe. Pokaże ci komórkę, która ma okrągłe odniesienie w arkuszu
- Kliknij na adres komórki (który jest wyświetlany) i przeniesie cię do tej komórki w arkuszu.
Po rozwiązaniu problemu możesz ponownie wykonać te same kroki powyżej i pokaże więcej odniesień do komórek, które mają okrągłe odniesienie. Jeśli nie ma żadnego, nie zobaczysz żadnego odniesienia do komórki,
Innym szybkim i łatwym sposobem na znalezienie okrągłego odniesienia jest spojrzenie na pasek stanu. W jego lewej części znajduje się tekst Odwołanie kołowe wraz z adresem komórki.
Przy pracy z odwołaniami kołowymi należy wiedzieć o kilku rzeczach:
- W przypadku, gdy włączone jest obliczanie iteracyjne (omówione w dalszej części tego poradnika), pasek stanu nie będzie pokazywał adresu komórki odwołania kołowego
- W przypadku, gdy odwołanie kołowe nie znajduje się w aktywnym arkuszu (ale w innych arkuszach w tym samym skoroszycie), będzie pokazywał tylko Odwołanie kołowe, a nie adres komórki
- W przypadku, gdy raz pojawi się monit ostrzegający o odwołaniu kołowym i zostanie on odrzucony, następnym razem monit nie pojawi się ponownie.
- Jeśli otworzysz skoroszyt, który ma okrągłe odniesienie, pokaże ci monit, gdy tylko otworzy się skoroszyt.
Jak usunąć okrągłe odniesienie w Excelu?
Po zidentyfikowaniu, że w Twoim arkuszu znajdują się odwołania kołowe, nadszedł czas, aby je usunąć (chyba że chcesz, aby znajdowały się tam z jakiegoś powodu).
Niestety nie jest to tak proste, jak naciśnięcie klawisza delete. Ponieważ są one zależne od formuł, a każda formuła jest inna, musisz przeanalizować to indywidualnie dla każdego przypadku.
W przypadku, gdy jest to tylko kwestia tego, że odwołanie do komórki powoduje problem przez pomyłkę, możesz po prostu poprawić, dostosowując odwołanie.
Ale czasami nie jest to takie proste.
Okrągłe odniesienie może być również spowodowane w oparciu o wiele komórek, które zasilają się nawzajem na wielu poziomach.
Pokażę ci przykład.
Poniżej znajduje się okrągłe odniesienie w komórce C6, ale nie jest to tylko prosty przypadek samoodniesienia. Jest ono wielopoziomowe, gdzie komórki, z których korzysta w obliczeniach, również odwołują się do siebie nawzajem.
- Formuła w komórce A6 to =SUM(A1:A5)+C6
- Formuła w komórce C1 to =A6*0.1
- Formuła w komórce C6 to =A6+C1
W powyższym przykładzie wynik w komórce C6 jest zależny od wartości w komórce A6 i C1, które z kolei są zależne od komórki C6 (powodując tym samym błąd odwołania kołowego)
I znowu, wybrałem naprawdę prosty przykład tylko dla celów demonstracyjnych. W rzeczywistości mogą one być dość trudne do rozgryzienia i mogą znajdować się daleko w tym samym arkuszu lub nawet być rozproszone po wielu arkuszach.
W takim przypadku istnieje jeden sposób na zidentyfikowanie komórek, które powodują odwołanie kołowe, a następnie ich leczenie.
Jest nim użycie opcji Trace Precedents.
Poniżej opisano kroki, które należy wykonać, aby znaleźć komórki, które zasilają komórkę z odwołaniem kołowym:
- Zaznaczyć komórkę, która ma odwołanie kołowe
- Kliknąć zakładkę Formuły
- Kliknąć na Trace Precedents
Powyższe kroki pokażą Ci niebieskie strzałki, które powiedzą Ci, jakie komórki zasilają formułę w zaznaczonej komórce. W ten sposób możesz sprawdzić formuły i komórki i pozbyć się okrągłego odniesienia.
W przypadku, gdy pracujesz ze złożonymi modelami finansowymi, może być możliwe, że te precedensy również sięgają wielu poziomów w głąb.
To działa dobrze, jeśli masz wszystkie formuły odnoszące się do komórek w tym samym arkuszu. Jeśli jest to w wielu arkuszach, ta metoda nie jest skuteczna.
Jak włączyć/wyłączyć obliczenia iteracyjne w Excelu
Gdy masz okrągłe odniesienie w komórce, najpierw otrzymasz monit ostrzegawczy, jak pokazano poniżej, a jeśli zamkniesz to okno dialogowe, da ci 0 jako wynik w komórce.
Jest to spowodowane tym, że gdy istnieje okrągłe odniesienie, jest to nieskończona pętla i Excel nie chce się w nią zaplątać. Zwraca więc 0.
Ale w niektórych przypadkach możesz faktycznie chcieć, aby odwołanie kołowe było aktywne i wykonało kilka iteracji. W takim przypadku, zamiast nieskończonej pętli, możesz zdecydować, ile razy pętla powinna zostać uruchomiona.
To się nazywa obliczenie iteracyjne w Excelu.
Poniżej przedstawiono kroki, które należy wykonać, aby włączyć i skonfigurować obliczenia iteracyjne w Excelu:
- Kliknij kartę Plik
- Kliknij Opcje. Spowoduje to otwarcie okna dialogowego Opcje programu Excel
- Wybierz opcję Formuły w lewym panelu
- W sekcji Opcje obliczeń zaznacz pole wyboru „Włącz obliczenia iteracyjne”. Tutaj możesz określić maksymalną liczbę iteracji i maksymalną wartość zmiany
To wszystko! Powyższe kroki pozwolą na włączenie obliczeń iteracyjnych w Excelu.
Pozwól mi również szybko wyjaśnić dwie opcje w obliczeniach iteracyjnych:
- Maksymalna liczba iteracji: Jest to maksymalna liczba razy, którą chcesz, aby Excel obliczył przed podaniem ostatecznego wyniku. Jeśli określisz tę wartość jako 100, Excel wykona pętlę 100 razy, zanim poda wynik końcowy.
- Maksymalna zmiana: Jest to maksymalna zmiana, która jeśli nie zostanie osiągnięta pomiędzy iteracjami, obliczenia zostaną zatrzymane. Domyślnie wartość ta wynosi .001. Im niższa jest ta wartość, tym dokładniejszy będzie wynik.
Pamiętaj, że im więcej razy iteracje zostaną wykonane, tym więcej czasu i zasobów potrzebuje Excel, aby to zrobić. W przypadku, gdy utrzymasz maksymalną liczbę iteracji na wysokim poziomie, może to doprowadzić do spowolnienia lub awarii programu Excel.
Umyślne używanie odwołań kołowych
W większości przypadków obecność odwołań kołowych w arkuszu jest błędem. Dlatego Excel wyświetla monit, który mówi – „Spróbuj usunąć lub zmienić te odwołania lub przenieść formuły do innych komórek.”
Ale mogą istnieć pewne szczególne przypadki, w których potrzebujesz odwołania kołowego, aby uzyskać pożądany wynik.
Jeden z takich szczególnych przypadków, o których już pisałem, to uzyskanie znacznika czasu w komórce w Excelu.
Na przykład, załóżmy, że chcesz stworzyć formułę, aby po każdym wpisie w komórce w kolumnie A, znacznik czasu pojawił się w kolumnie B (jak pokazano poniżej):
W prosty sposób możesz wstawić znacznik czasu używając poniższej formuły:
=IF(A2<>"",IF(B2<>"",B2,NOW()),"")
Problem z powyższą formułą polega na tym, że zaktualizowałaby ona wszystkie znaczniki czasu, gdy tylko w arkuszu zostanie dokonana jakakolwiek zmiana lub gdy arkusz zostanie ponownie otwarty (ponieważ formuła NOW jest zmienna)
Aby obejść ten problem, możesz użyć metody odniesienia kołowego. Użyj tej samej formuły, ale włącz iteracyjne obliczanie.
Istnieją również inne przypadki, w których możliwość użycia odwołania kołowego jest pożądana (jeden z przykładów można znaleźć tutaj).
Mam nadzieję, że ten poradnik okazał się przydatny!
Inne poradniki programu Excel, które mogą Ci się przydać: