Jeśli korzystasz z programu Microsoft Excel przez dłuższy czas, najprawdopodobniej zdarzyło Ci się utworzyć formułę, która powoduje powstanie odwołania kołowego. Oznacza to, że utworzona formuła obejmuje komórkę, w której znajduje się formuła. W większości przypadków w programie Excel podczas tworzenia formuł odwołujemy się do komórek innych niż ta, w której znajduje się nasza formuła. Istnieją jednak sytuacje, w których możesz celowo utworzyć odwołanie kołowe.
Najprawdopodobniej przypadkowo utworzyłeś odwołanie okrężne, tworząc funkcję SUMA, która obejmuje sam wiersz sumy, jak pokazano na rysunku 1. Wyniki, które zobaczysz, będą się różnić w zależności od wersji programu Excel:
- Excel 2013 - zostanie wyświetlony prosty monit informujący o utworzeniu odwołania kołowego i dający możliwość wyboru opcji OK lub Anuluj. Jeśli klikniesz przycisk OK, formuła pojawi się w komórce, ale może wyświetlać zero, jak pokazano na rysunku 1.
- Excel 2010 i wcześniejsze - Możesz mieć wrażenie, że Twój komputer zaraz się zawiesi. Excel nie tylko wyświetla monit ostrzegawczy, ale także rysuje na ekranie jedną lub więcej strzałek i wyświetla ekran pomocy.
Rysunek 1: W formule wyświetlane jest zero, ponieważ utworzono odwołanie kołowe.
W każdej wersji programu Excel na pasku stanu pojawi się napis Odwołanie kołowe wraz z odwołaniem do komórki. Wszystkie te informacje znikną po poprawieniu formuły.
Mogą jednak wystąpić sytuacje, w których użytkownik będzie chciał celowo utworzyć odwołanie okrężne. Mój ulubiony przykład podczas nauki odwołań okrężnych pochodzi z czasów, gdy pracowałem w branży nieruchomości komercyjnych. Załóżmy, że deweloper ma zapewnione finansowanie na budowę budynku o wartości 100 mln USD. Za swoje działania deweloper otrzyma prowizję deweloperską, w tym przypadku powiedzmy 3%. Jednak w umowie zapisano, że nie będzie to 3% kosztów budynku, lecz 3% całkowitych kosztów projektu. Całkowity koszt projektu to 100 milionów dolarów plus opłata deweloperska. W tym tkwi błąd, ponieważ opłata jest częścią obliczeń. Rysunek 2 pokazuje zastosowane formuły.
Rysunek 2: Komórka H3 zawiera formułę SUM(H1:H2), więc opłata deweloperska początkowo nie jest obliczana.
Zauważ, że początkowo opłata deweloperska nie jest obliczana. W tej sytuacji musimy nakazać programowi Excel, aby obliczył za nas wysokość opłaty deweloperskiej, ustawiając opcję programu Excel, jak pokazano na rysunku 3:
- Wybierz polecenie Plik, Opcje, a następnie Formuły.
- Kliknij pole wyboru Włącz obliczenia iteracyjne, a następnie kliknij przycisk OK.
Rysunek 3: Włącz opcję Obliczenia iteracyjne, aby obliczyć opłatę deweloperską.
Należy pamiętać, że jest to ustawienie specyficzne dla danego skoroszytu, więc trzeba je włączyć dla każdego skoroszytu, który będzie zawierał odwołania kołowe. Po zamknięciu okna dialogowego Opcje zobaczysz, że Excel oblicza opłatę deweloperską w wysokości 3 092 784 USD. Zapisanie w umowie, że opłata deweloperska jest ustalana na podstawie całkowitego kosztu, daje deweloperowi dodatkowe 92 784 USD, ponieważ 3% kosztu budynku wynosiłoby 3 000 000 USD.
Jeśli wrócimy do rysunku 3, zauważymy, że istnieją dwa dodatkowe ustawienia związane z obliczeniami iteracyjnymi:
- Maksimum Iteracji: Domyślną wartością jest 100, co oznacza, że Excel będzie próbował 100 razy rozwiązać obliczenie, zanim się podda. To ustawienie chroni użytkownika przed wprowadzeniem Excela w stan ciągłych obliczeń.
- Maksymalna zmiana: Excel próbuje do 100 razy rozwiązać obliczenia, a zatrzymuje się, gdy różnica między iteracjami jest nie większa niż 0,001.
W mojej karierze tylko kilka razy zdarzyło mi się, że musiałem zmienić którąś z tych opcji. Z mojego doświadczenia wynika, że zmiana wartości maksymalnej zmiany na 0,01 powinna wystarczyć w większości przypadków. Upewnij się, że zapisałeś swój skoroszyt przed zmianą ustawień opcji Maksymalna liczba iteracji lub Maksymalna zmiana, aby przypadkowo nie wprowadzić Excela w stan, z którego nie będzie można wyjść bez awarii programu.
W jednym z kolejnych artykułów opiszę ukrytą funkcję Excela, która ułatwia znajdowanie wszystkich odwołań kołowych w skoroszycie. Należy pamiętać, że z odwołań kołowych w Excelu należy korzystać ostrożnie. Jeśli po prostu włączysz obliczenia iteracyjne, aby zniknęły podpowiedzi i strzałki, możesz uzyskać mylące wyniki. Na przykład utworzona przeze mnie prosta funkcja sumy zwraca 60 000 zamiast 600, jeśli włączona jest opcja Włącz obliczenia iteracyjne, jak pokazano na rysunku 4.
Rysunek 4: Należy uważać z włączaniem opcji Obliczenia iteracyjne, ponieważ może to prowadzić do mylących wyników.
