Jak radzić sobie z odwołaniami okrężnymi w Excelu

W Excelu zazwyczaj wpisujesz formuły, aby uzyskać wyniki i dość powszechne jest odwoływanie się do innych komórek w tym procesie. Co się jednak stanie, jeśli odwołasz się do samej komórki, bezpośrednio lub pośrednio? Oznacza to nieskończoną pętlę obliczeń! Na szczęście Excel ma rozwiązania, które radzą sobie z tego typu sytuacjami.

Okrągłe odwołanie oznacza, że Twoja formuła próbuje obliczyć komórkę pochodzenia. Zazwyczaj jest to uważane za błąd. Jednak zdarzają się sytuacje, w których ten błąd może być przydatny, a Ty możesz chcieć utworzyć odwołanie kołowe celowo. Przyjrzyjmy się bliżej temu zjawisku na kilku przykładach. Możesz pobrać przykładowy skoroszyt tutaj.

Bezpośrednie odwołanie

Jeśli na przykład przejdziesz do komórki C3, a następnie wpiszesz „=C3”, zmylisz trochę Excela, ponieważ ta komórka wskazuje teraz na siebie, tworząc w ten sposób odwołanie kołowe. Wykonanie tej samej czynności wewnątrz formuły również przyniesie ten sam efekt (np. =IF(C3,…,…) lub =C3+10).

Innym częstym przykładem jest uwzględnienie oryginalnej komórki podczas wybierania zakresu z sąsiednich komórek. Na przykład uwzględnienie komórki C4 w formule sumy =SUM(C2:C4) przekształci ją w odwołanie kołowe.

Odniesienie pośrednie

Komórka, która jest skierowana na komórkę, która wskazuje na komórkę wyjściową, utworzy odwołanie pośrednie. Innymi słowy, odniesienie kołowe może zostać utworzone przez dwie komórki skierowane na siebie nawzajem. Na przykład, wpisanie „=D3” do komórki B3, podczas gdy komórka D3 zawiera odwołanie z komórki B3 („=B3” w tym przypadku).

Dlaczego to jest problem?

Excel ostrzega użytkownika, gdy występuje odwołanie kołowe, ponieważ odwołanie kołowe zasadniczo oznacza nieskończone pętle. Mówienie Excelowi, aby powtarzał to samo obliczenie w kółko nieskończoną ilość razy nie ma zbyt wiele sensu. Aby zapobiec zamarzaniu komputera, Excel po prostu ignoruje odwołania kołowe.

Przyjrzyjrzyjmy się temu komunikatowi ostrzegawczemu. Po wpisaniu formuły z odwołaniem kołowym otrzymasz następujący komunikat,

Zauważ, że Excel nie wyświetla tego samego błędu w przypadku dodatkowych odwołań do formuł kołowych. Na pasku stanu programu Excel zostanie wyświetlona ostatnia komórka z odwołaniem kołowym. Oznacza to, że na tej karcie może znajdować się więcej niż jedno takie odwołanie. Oto jak to wygląda,

Excel prowadzi również listę wszystkich odwołań kołowych, do której dostęp można uzyskać z opcji Sprawdzanie błędów na karcie Formuły,

Jak się ich pozbyć

Niestety, nie ma prostego sposobu na usunięcie lub naprawienie niechcianych odwołań kołowych. Będziesz musiał użyć jednego z wymienionych wcześniej sposobów i rozwiązywać je jeden po drugim. Aby dowiedzieć się, co jest przyczyną problemu, możesz skorzystać z opcji Audyt formuły w zakładce Formuły. Zobacz nasz poradnik, aby dowiedzieć się jak to zrobić https://www.spreadsheetweb.com/identifying-analyzing-spreadsheets-formula-auditing/

Czy mogą być przydatne?

Tak! Chociaż tylko w pewnych okolicznościach. Możesz celowo chcieć obliczyć okrągły blok formuły więcej niż kilka razy. Excel umożliwia takie „iteracyjne” podejście do obliczeń za pomocą limitów.

Aby aktywować i korzystać z odwołań kołowych, musisz je najpierw uaktywnić, zaznaczając opcję Włącz obliczenia iteracyjne w menu Plik.

Przejdź do Plik > Opcje > Formuły > Opcje obliczeń w programach Excel 2016, Excel 2013 i Excel 2010.

W Excelu 2007, musisz przejść do przycisku Office > Opcje programu Excel > Formuły > Obszar iteracji

W programie Excel 2003 i wcześniejszych, musisz przejść doMenu > Narzędzia > Opcje > Obliczenia

Włączenie obliczeń iteracyjnych spowoduje pojawienie się dwóch dodatkowych wejść w tym samym menu,

• Maksimum Iteracji określa ile razy Excel ma przeliczyć skoroszyt
• Maksymalna Zmiana określa maksymalną różnicę między wartościami formuł iteracyjnych. Należy pamiętać, że mniejsza liczba oznacza dokładne wyniki.

Obliczenia iteracyjne zatrzymują się, gdy jeden z warunków zdefiniowanych powyżej jest spełniony.

Przypadki użycia

Obliczanie przyszłej wartości inwestycji

Załóżmy, że mamy 10 000 $ i chcemy zainwestować te pieniądze w konto CD. Miesięczne oprocentowanie w tej opcji wynosi 1,25%. Pobierz przykładowy skoroszyt dla tego przypadku użycia tutaj. Aby obliczyć całkowitą wartość na koniec 21 miesiąca, będziemy obliczać każdy miesiąc i dodawać odsetki do poprzedniego miesiąca.

Na początku wpisujemy wartość początkową, odsetki i podstawową funkcję wartość całkowita,

=wartość * ( 1 + oprocentowanie)

Następnie zaznaczamy komórkę z początkową wartością pieniężną i dodajemy odwołanie do funkcji wartość całkowita.

Ten ruch spowoduje ostrzeżenie o odwołaniu kołowym, jeśli nie są włączone obliczenia iteracyjne. Jeśli jeszcze tego nie zrobiłeś, włącz tę opcję i ustaw Maximum Iterations na 20, aby znaleźć odsetki w 21. miesiącu.

Naciśnij OK.

Automatyczny znacznik czasu

Okrągłe odwołania można również wykorzystać do dodania znacznika czasu do komórek. Pobierz przykładowy skoroszyt dla tego przypadku użycia tutaj. Załóżmy, że chcemy dodać znaczniki czasu do zamówień w poniższej tabeli,

Możemy użyć odwołań kołowych, aby dodać znacznik czasu, gdy zostanie wprowadzona nowa informacja o zamówieniu.

Zacznij od dodania nowej kolumny do tabeli, w której umieścimy znaczniki czasu. Wpisz formułę, =IF(A2<>””,IF(I2<>””,I2,TERAZ()),””)

Ta formuła sprawdzi, czy Numer Zamówienia (komórka A2) został wpisany. Jeśli jest wpisana jakaś wartość, a komórka ze znacznikiem czasu jest pusta, to formuła zwróci funkcję NOW().

Zauważ, że naciśnięcie przycisku Enter spowoduje wyświetlenie ostrzeżenia o odwołaniu kołowym, jeśli nie są włączone obliczenia iteracyjne. Tym razem liczby Maximum Iterations czy Maximum Change nie mają większego znaczenia, ponieważ potrzebujemy tylko jednej iteracji, więc możesz pozostawić te dwie wartości domyślne.

Teraz, za każdym razem, gdy wprowadzimy nowe zamówienie i utworzymy nowy wiersz, w kolumnie Timestamp będzie automatycznie wypisywany znacznik czasu.