Podczas pracy z formułami programu Excel czasami możesz zobaczyć następujący monit ostrzegawczy.
Ten monit informuje, że w arkuszu znajduje się odwołanie cykliczne, które może prowadzić do nieprawidłowego obliczenia przez formuły. Prosi również o odniesienie się do tego okrągłego problemu referencyjnego i uporządkowanie go.
W tym samouczku omówię wszystko, co musisz wiedzieć o odwołaniu kołowym, a także jak znaleźć i usunąć cykliczne odniesienia w programie Excel.
Więc zacznijmy!
Co to jest odwołanie cykliczne w programie Excel?
Mówiąc prościej, odwołanie cykliczne ma miejsce, gdy w komórce znajduje się formuła – która sama w sobie wykorzystuje komórkę (w której została wprowadzona) do obliczeń.
Spróbuję to wyjaśnić na prostym przykładzie.
Załóżmy, że masz zestaw danych w komórce A1:A5 i używasz poniższej formuły w komórce A6:
=SUMA(A1:A6)
To da ci okrągłe ostrzeżenie o odwołaniu.
Dzieje się tak, ponieważ chcesz zsumować wartości w komórce A1:A6, a wynik powinien znajdować się w komórce A6.
Tworzy to pętlę, ponieważ program Excel po prostu dodaje nową wartość w komórce A6, która ciągle się zmienia (stąd pętla cykliczna).
Jak znaleźć odwołania cykliczne w programie Excel?
Chociaż monit ostrzeżenia o odwołaniu cyklicznym jest na tyle miły, że informuje, że istnieje w arkuszu, nie informuje, gdzie to się dzieje i jakie odwołania do komórek go powodują.
Jeśli więc próbujesz znaleźć i obsłużyć odwołania cykliczne w arkuszu, musisz znać sposób, aby je jakoś znaleźć.
Poniżej znajdują się kroki, aby znaleźć cykliczne odwołanie w programie Excel:
- Aktywuj arkusz, który zawiera odwołanie cykliczne
- Kliknij kartę Formuły
- W grupie Edycja formuł kliknij ikonę rozwijaną Sprawdzanie błędów (mała strzałka skierowana w dół po prawej stronie)
- Najedź kursorem na opcję Okrągłe odniesienia. Pokaże ci komórkę, która ma cykliczne odwołanie w arkuszu
- Kliknij adres komórki (który jest wyświetlany), aby przejść do tej komórki w arkuszu.
Po rozwiązaniu problemu możesz ponownie wykonać te same kroki powyżej i wyświetli więcej odwołań do komórek, które mają odwołanie cykliczne. Jeśli nie ma, nie zobaczysz żadnego odwołania do komórki,
Innym szybkim i łatwym sposobem na znalezienie odniesienia okrężnego jest spojrzenie na pasek stanu. Po lewej stronie pokaże tekst Circular Reference wraz z adresem komórki.
Jest kilka rzeczy, które musisz wiedzieć podczas pracy z odwołaniami cyklicznymi:
- W przypadku włączenia obliczania iteracyjnego (omówionego w dalszej części tego samouczka), pasek stanu nie będzie pokazywał adresu komórki odwołania cyklicznego
- Jeśli odwołanie cykliczne nie znajduje się w aktywnym arkuszu (ale w innych arkuszach w tym samym skoroszycie), wyświetli tylko odwołanie cykliczne, a nie adres komórki
- Jeśli raz otrzymasz ostrzeżenie o odwołaniu cyklicznym i je odrzucisz, nie wyświetli się on ponownie następnym razem.
- Jeśli otworzysz skoroszyt zawierający odwołanie cykliczne, wyświetli się monit zaraz po otwarciu skoroszytu.
Jak usunąć odwołanie cykliczne w programie Excel?
Po zidentyfikowaniu, że w arkuszu znajdują się odwołania cykliczne, nadszedł czas, aby je usunąć (chyba że chcesz, aby były tam z jakiegoś powodu).
Niestety nie jest to tak proste, jak naciśnięcie klawisza usuwania. Ponieważ są one zależne od formuł, a każda formuła jest inna, należy to przeanalizować indywidualnie dla każdego przypadku.
Jeśli chodzi tylko o to, że przez pomyłkę odwołanie do komórki powoduje problem, możesz po prostu poprawić, dostosowując odwołanie.
Ale czasami to nie jest takie proste.
Odwołanie cykliczne może być również spowodowane wieloma komórkami, które łączą się ze sobą na wielu poziomach.
Pokażę ci przykład.
Poniżej znajduje się odwołanie cykliczne w komórce C6, ale nie jest to tylko prosty przypadek samoodniesienia. Jest wielopoziomowy, w którym komórki używane w obliczeniach również odwołują się do siebie.
- Formuły w komórce A6 to =SUMA(A1:A5)+C6
- Formuła to komórka C1 = 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órkach A6 i C1, które z kolei są zależne od komórki C6 (co powoduje błąd odwołania cyklicznego)
I znowu wybrałem naprawdę prosty przykład tylko do celów demonstracyjnych. W rzeczywistości mogą być one dość trudne do rozszyfrowania i być może odległe w tym samym arkuszu, a nawet rozproszone w wielu arkuszach.
W takim przypadku istnieje jeden sposób zidentyfikowania komórek, które powodują odwołanie cykliczne, a następnie ich leczenia.
Dzieje się tak za pomocą opcji Trace Precedents.
Poniżej znajdują się kroki, aby użyć precedensów śledzenia w celu znalezienia komórek, które zasilają komórkę, która ma odwołanie cykliczne:
- Wybierz komórkę, która ma odwołanie cykliczne
- Kliknij kartę Formuły
- Kliknij Śledź poprzedniki
Powyższe kroki pokażą ci niebieskie strzałki, które powiedzą ci, jakie komórki zasilają formułę w wybranej komórce. W ten sposób możesz sprawdzić formuły i komórki oraz pozbyć się odwołania cyklicznego.
Jeśli pracujesz ze złożonymi modelami finansowymi, możliwe, że te precedensy również sięgają wielu poziomów.
Działa to dobrze, jeśli masz wszystkie formuły odwołujące się do komórek w tym samym arkuszu. Jeśli jest w wielu arkuszach, ta metoda nie jest skuteczna.
Jak włączyć / wyłączyć obliczenia iteracyjne w programie Excel?
Gdy w komórce znajduje się odwołanie cykliczne, najpierw pojawi się monit ostrzegawczy, jak pokazano poniżej, a jeśli zamkniesz to okno dialogowe, w komórce zostanie wyświetlony wynik 0.
Dzieje się tak, ponieważ gdy istnieje odwołanie kołowe, jest to nieskończona pętla, a Excel nie chce się w nią wplątać. Więc zwraca 0.
Ale w niektórych przypadkach możesz chcieć, aby odwołanie cykliczne było aktywne i wykonać kilka iteracji. W takim przypadku zamiast nieskończonej pętli i możesz zdecydować, ile razy pętla ma być uruchomiona.
To się nazywa obliczenia iteracyjne w Excelu.
Poniżej znajdują się kroki, aby włączyć i skonfigurować obliczenia iteracyjne w programie Excel:
- Kliknij kartę Plik
- Kliknij Opcje. Spowoduje to otwarcie okna dialogowego Opcje programu Excel
- Wybierz Formuła w lewym okienku
- W sekcji Opcje obliczeń zaznacz pole „Włącz obliczanie iteracyjne”. Tutaj możesz określić maksymalną liczbę iteracji i maksymalną wartość zmiany
Otóż to! Powyższe kroki umożliwiłyby obliczenie iteracyjne w programie Excel.
Pozwolę sobie również szybko wyjaśnić dwie opcje w obliczeniach iteracyjnych:
- Maksymalna liczba iteracji: Jest to maksymalna liczba obliczeń, które program Excel ma obliczyć przed podaniem końcowego wyniku. Więc jeśli określisz to jako 100, Excel uruchomi pętlę 100 razy, zanim poda ostateczny wynik.
- Maksymalna zmiana: Jest to maksymalna zmiana, która, jeśli nie zostanie osiągnięta między iteracjami, obliczenia zostaną zatrzymane. Domyślna wartość to 0,001. Im niższa jest ta wartość, tym dokładniejszy byłby wynik.
Pamiętaj, że im więcej iteracji jest wykonywanych, tym więcej czasu i zasobów zajmuje programowi Excel. Jeśli utrzymasz wysokie maksymalne iteracje, może to spowodować spowolnienie lub awarię programu Excel.
Uwaga: Gdy obliczenia iteracyjne są włączone, program Excel nie wyświetli monitu ostrzegawczego o odwołaniu cyklicznym, a także wyświetli go teraz na pasku stanu.Celowe korzystanie z cyrkularnych odniesień
W większości przypadków obecność odwołania cyklicznego w arkuszu byłaby błędem. I dlatego program Excel wyświetla monit o treści: „Spróbuj usunąć lub zmienić te odwołania lub przenieść formuły do różnych komórek”.
Ale mogą istnieć pewne szczególne przypadki, w których potrzebujesz odwołania cyklicznego, aby uzyskać pożądany wynik.
Jeden z takich konkretnych przypadków, o których już pisałem, uzyskiwał znacznik czasu w komórce w komórce w Excelu.
Załóżmy na przykład, że chcesz utworzyć formułę, tak aby każdy wpis w komórce w kolumnie A był widoczny w kolumnie B (jak pokazano poniżej):
Chociaż możesz łatwo wstawić znacznik czasu, korzystając z poniższej formuły:
=JEŻELI(A2"",JEŻELI(B2"",B2,TERAZ()),"")
Problem z powyższą formułą polega na tym, że zaktualizuje wszystkie znaczniki czasu, gdy tylko w arkuszu zostanie wprowadzona jakakolwiek zmiana lub jeśli arkusz zostanie ponownie otwarty (ponieważ formuła TERAZ jest niestabilna)
Aby obejść ten problem, możesz użyć metody odwołań cyklicznych. Użyj tej samej formuły, ale włącz obliczanie iteracyjne.
Istnieje również kilka innych przypadków, w których pożądana jest możliwość korzystania z odwołań cyklicznych (możesz znaleźć jeden przykład tutaj).
Uwaga: Chociaż mogą wystąpić sytuacje, w których można użyć odwołania cyklicznego, uważam, że najlepiej go unikać. Odwołania cykliczne mogą również mieć negatywny wpływ na wydajność skoroszytu i mogą go spowolnić. W rzadkich przypadkach, gdy tego potrzebujesz, zawsze wolę używać kodów VBA, aby wykonać pracę.Mam nadzieję, że ten samouczek okazał się przydatny!
Inne samouczki programu Excel, które mogą Ci się przydać:
- #REF! Błąd w Excelu; Jak naprawić błąd referencyjny!
- Obsługa błędów Excel VBA
- Użyj funkcji IFERROR z funkcją WYSZUKAJ.PIONOWO, aby pozbyć się błędów #N/A
- Jak odwoływać się do innego arkusza lub skoroszytu w programie Excel (z przykładami)
- Bezwzględne, względne i mieszane odniesienia do komórek w programie Excel
