Jak usunąć odwołania cykliczne w programie Excel

W arkuszu kalkulacyjnym programu Microsoft Excel w jednej komórce mogą znajdować się formuły, które w celu obliczenia wymagają informacji z innej komórki. Te relacje nazywane są odniesieniami. Gdy komórka odwołuje się do siebie lub innej komórki, która odwołuje się do oryginalnej komórki, formuły nie mogą zostać zakończone, ponieważ wymagają wyniku zależnego od oryginalnej komórki. To zdarzenie nazywane jest odwołaniem cyklicznym, a gdy masz duży arkusz kalkulacyjny z tysiącami formuł, jedno odwołanie cykliczne może spowodować nieprawidłowe obliczenie całego arkusza.

Zrozumienie odwołań w programie Excel

Podczas korzystania z programu Microsoft Excel często formuła w jednej komórce odwołuje się do innej komórki w celu wykorzystania danych tej komórki w obliczeniach. Ogólnie do komórki odwołuje się się za pomocą litery wiersza i numeru kolumny, na przykład A5 lub B10.

Po zaktualizowaniu danych w komórce, do której następuje odniesienie, zaktualizowane zostaną również formuły, które się do nich odwołują. Często zdarza się, że w złożonym arkuszu kalkulacyjnym występuje wiele warstw odniesień, a wyniki obliczeń jednej formuły są wprowadzane do innej.

Eliminacja okólników w programie Excel

Jeśli formuła odwołuje się bezpośrednio do własnej komórki lub poprzez łańcuch odniesień do innych komórek ostatecznie odwołuje się z powrotem do własnej komórki, otrzymasz z programu Excel cykliczne ostrzeżenie o odwołaniu. Dzieje się tak, ponieważ formuły nie można obliczyć w zwykły sposób, ponieważ aktualizowana może wpływać na jej własną wartość.

Często jest to oznaka literówki lub błędu logicznego i będziesz chciał dowiedzieć się, co tworzy odwołanie cykliczne, i go wyeliminować.

  1. Otwórz arkusz kalkulacyjny

  2. Otwórz arkusz kalkulacyjny Microsoft Excel zawierający cykliczne odwołanie.

  3. Skorzystaj z menu Formuły

  4. Kliknij kartę „Formuły” w menu wstążki u góry okna programu Excel. Kliknij małą strzałkę obok przycisku „Sprawdzanie błędów” w tym obszarze. Najedź kursorem myszy na „Odnośniki cykliczne”, a pojawi się ostatnio wprowadzone odwołanie cykliczne. Kliknij to odniesienie, aby przejść do tej komórki w arkuszu kalkulacyjnym.

  5. Poszukaj okrągłego odniesienia

  6. Sprawdź formułę, która pojawia się na pasku formuły tuż nad arkuszem kalkulacyjnym, pod kątem jakichkolwiek oczywistych odwołań cyklicznych, na przykład jeśli formuła próbuje odwołać się do komórki, w której aktualnie się znajduje. W mniejszych arkuszach kalkulacyjnych możesz nawet ręcznie sprawdzić odwołanie do każdej komórki, aby zobacz, czy potrafisz dostrzec okrągłe odniesienie. Jeśli nie możesz tego zauważyć, będziesz musiał prześledzić odniesienia w arkuszu kalkulacyjnym.

  7. Śledzenie precedensów za pomocą programu Excel

  8. Kliknij przycisk „Śledź poprzedniki” w obszarze „Inspekcja formuł” na karcie Formuła menu wstążki. Spowoduje to utworzenie strzałki w arkuszu kalkulacyjnym z każdej komórki wymienionej w określonej komórce do określonej komórki. Kliknij przycisk „Śledź zależności”, co spowoduje utworzenie strzałek z określonej komórki do każdej komórki, która odwołuje się do określonej komórki.

  9. Postępuj zgodnie z łańcuchem odniesienia

  10. Kliknij dwukrotnie każdą linię wychodzącą z komórki z odwołaniem cyklicznym, aby przeskoczyć do komórki na drugim końcu linii. Jeśli komórka, do której przeskakujesz, ma zarówno początek strzałki, która wygląda jak okrąg, jak i koniec strzałki, która wygląda jak grot strzałki, znalazłeś okrągłe odniesienie. Zmień tę komórkę lub odwołanie do tej komórki w oryginalnej komórce, aby poprawić odwołanie cykliczne.

  11. Znajdź więcej kręgów według potrzeb

  12. Kliknij ponownie strzałkę w dół obok pozycji „Sprawdzanie błędów”, aby zobaczyć, czy pojawi się kolejne odwołanie cykliczne. Jeśli tak, powtórz ten proces, aby go wyczyścić i kontynuuj, aż nie pojawią się już żadne cykliczne odniesienia.

Obliczenia iteracyjne w programie Excel

W niektórych przypadkach możesz chcieć użyć odwołań cyklicznych, aby wprowadzić wynik obliczenia z powrotem do obliczenia. Nazywa się to obliczeniem iteracyjnym w programie Excel. Jest to przydatne w przypadku niektórych formuł i algorytmów, w których chcesz iterować, aż zostanie znaleziony określony wynik.

Aby włączyć obliczenia iteracyjne w programie Excel, kliknij kartę „Plik”, a następnie „Opcje” i „Formuły”. W sekcji „Opcje obliczeń” zaznacz pole „Włącz obliczenia iteracyjne”. Wprowadź maksymalną liczbę ponownych obliczeń formuły przez program Excel w polu „Maksymalna liczba iteracji” oraz maksymalną dopuszczalną ilość zmian, zanim program Excel będzie musiał ponownie obliczyć w polu „Maksymalna zmiana”.

Im więcej iteracji pozwolisz i im mniejszą ilość zmian pozwolisz przed wymaganiem ponownego obliczenia, tym dokładniejsze będą wyniki. Upewnij się, że rzeczywiście chcesz użyć iterowanej formuły, zamiast po prostu eliminować odwołania cykliczne przed przejściem tą ścieżką.