Cykliczne odniesienia w Microsoft Excel

Uważa się, że odniesienia cykliczne w Excelu są błędnym wyrażeniem. Rzeczywiście, dość często tak jest, ale nadal nie zawsze. Czasem są używane dość świadomie. Dowiedzmy się, jakie są odwołania cykliczne, jak je tworzyć, jak znaleźć istniejące w dokumencie, jak z nimi pracować lub jak je usunąć, jeśli to konieczne.

Korzystanie z odwołań cyklicznych

Przede wszystkim dowiedzmy się, co to jest odniesienie cykliczne. W rzeczywistości jest to wyrażenie, które za pomocą formuł w innych komórkach odnosi się do samego siebie. Może to być także link znajdujący się w elemencie arkusza, do którego się odnosi.

Należy zauważyć, że domyślnie nowoczesne wersje programu Excel automatycznie blokują proces wykonywania operacji cyklicznej. Wynika to z faktu, że takie wyrażenia są w przeważającej mierze błędne, a pętla tworzy ciągły proces ponownego obliczania i obliczania, który powoduje dodatkowe obciążenie systemu.

Tworzenie odwołania cyklicznego

Zobaczmy teraz, jak utworzyć najprostsze wyrażenie cykliczne. Będzie to link znajdujący się w tej samej komórce, do której się odnosi.

1. Wybierz element arkusza A1 i wpisz w nim następujące wyrażenie:

   =A1

   Następnie kliknij przycisk Enter na klawiaturze.

2. Następnie pojawia się okno dialogowe ostrzeżenia o wyrażeniu cyklicznym. Kliknij w przycisk "OK" .
3. W ten sposób uzyskaliśmy cykliczną operację na arkuszu, w którym komórka odnosi się do siebie.

Nieznacznie skomplikujmy zadanie i utwórz cykliczną ekspresję z kilku komórek.

1. Napisz numer do dowolnego elementu arkusza. Niech to będzie komórka A1 i numer 5 .
2. W innej komórce ( B1 ) wpisujemy wyrażenie:

   =C1

3. Do następnego elementu ( C1 ) wpisujemy następującą formułę:

   =A1

4. Następnie wracamy do komórki A1 , w której ustawiona jest liczba 5 . Odnosimy się do niego w elemencie B1 :

   =B1

   Naciśnij przycisk Enter .

5. Tak więc cykl jest zamknięty, a my uzyskaliśmy klasyczne odniesienie cykliczne. Po zamknięciu okna ostrzegawczego widzimy, że program oznaczony jest cyklicznym łączem przez niebieskie strzałki na arkuszu, które nazywane są strzałkami śledzenia.

Teraz przystąpmy do tworzenia cyklicznego wyrażenia za pomocą przykładowej tabeli. Mamy stół do sprzedaży żywności. Składa się z czterech kolumn, które wskazują nazwę produktu, liczbę sprzedanych produktów, cenę i kwotę przychodów ze sprzedaży całego wolumenu. W tabeli w ostatniej kolumnie znajdują się już formuły. W nich dochód jest obliczany poprzez pomnożenie ilości przez cenę.

1. Aby naprawić formułę w pierwszym wierszu, wybierz element arkusza z kwotą pierwszego produktu ( B2 ). Zamiast wartości statycznej ( 6 ) wprowadzamy tam formułę, która pobiera ilość towarów dzieląc całkowitą kwotę ( D2 ) przez cenę ( C2 ):

   =D2/C2

   Klikamy przycisk Enter .

2. Mamy pierwszy cykliczny link, w którym relacja zazwyczaj jest oznaczona strzałką śladu. Ale jak widać, wynik jest błędny i równy zeru, ponieważ został już wcześniej powiedziany, Excel blokuje wykonywanie cyklicznych operacji.
3. Skopiuj wyrażenie do wszystkich innych komórek w kolumnie z liczbą produktów. Aby to zrobić, ustaw kursor w prawym dolnym rogu elementu, który już zawiera formułę. Kursor jest konwertowany na krzyżyk, który jest zwykle nazywany znacznikiem wypełnienia. Zacisnąć lewy przycisk myszy i przeciągnąć ten krzyż na koniec stołu w dół.
4. Jak widać, wyrażenie zostało skopiowane do wszystkich elementów kolumny. Ale istnieje tylko jedna relacja oznaczona strzałką śledzenia. Zauważmy to na przyszłość.

Znajdowanie odniesień kołowych

Jak widzieliśmy powyżej, nie zawsze program wyznacza relację okrągłego odniesienia do obiektów, nawet jeśli istnieje na arkuszu. Biorąc pod uwagę, że przeważająca większość cyklicznych operacji jest szkodliwa, należy je usunąć. Ale do tego muszą najpierw zostać znalezieni. Jak to zrobić, jeśli wyrażenia nie są oznaczone linią strzałki? Zajmijmy się tym zadaniem.

1. Tak więc, jeśli otworzysz okno informacyjne po uruchomieniu pliku Excel zawierającego link kołowy, powinieneś go znaleźć. W tym celu przechodzimy do zakładki "Formuły" . Kliknij na wstążkę w trójkącie, który znajduje się po prawej stronie przycisku "Sprawdź błędy" , znajdującego się w oknie narzędziowym "Formula Dependencies" . Otwiera się menu, w którym można przenieść kursor do pozycji "Łącza cykliczne" . Następnie następne menu otwiera listę adresów elementów arkusza, w których program znalazł wyrażenia cykliczne.
2. Po kliknięciu konkretnego adresu wybierana jest odpowiednia komórka na arkuszu.

Jest inny sposób, aby dowiedzieć się, gdzie znajduje się link kołowy. Komunikat o tym problemie i adres elementu zawierającego takie wyrażenie znajduje się po lewej stronie paska stanu, który znajduje się na dole okna Excela. Jednak, w przeciwieństwie do poprzedniego wariantu, na pasku stanu będą wyświetlane adresy nie wszystkich elementów zawierających linki kołowe, jeśli jest ich dużo, ale tylko jeden, który pojawił się przed innymi.

Ponadto, jeśli znajdujesz się w książce zawierającej wyrażenie cykliczne, a nie na arkuszu, w którym się znajduje, ale z drugiej strony, w tym przypadku na pasku stanu zostanie wyświetlony tylko komunikat o błędzie bez adresu.

Lekcja: Jak znaleźć odwołania cykliczne w programie Excel

Korygowanie połączeń cyklicznych

Jak już wspomniano powyżej, w zdecydowanej większości przypadków cykliczne operacje są złem, z którego należy się pozbyć. Dlatego naturalne jest, że po odkryciu komunikacji cyklicznej konieczne jest jej skorygowanie w celu dostosowania formuły do ​​normalnej formy.

Aby skorygować zależność cykliczną, konieczne jest prześledzenie całego połączenia komórek. Nawet jeśli check określił konkretną komórkę, błąd może nie być sam w sobie, ale w innym elemencie łańcucha zależności.

1. W naszym przypadku, mimo że program poprawnie wskazywał na jedną z komórek pętli ( D6 ), prawdziwy błąd leży w innej komórce. Wybierz element D6, aby dowiedzieć się, z których komórek pobiera wartość. Spójrz na wyrażenie na pasku formuły. Jak widać, wartość w tym elemencie arkusza tworzy się przez pomnożenie zawartości komórek B6 i C6 .
2. Idź do komórki C6 . Wybierz i spójrz na wiersz formuły. Jak widać, jest to zwykła wartość statyczna ( 1000 ), która nie jest produktem obliczania formuły. Dlatego możemy z całą pewnością stwierdzić, że określony element nie zawiera błędu powodującego tworzenie cyklicznych operacji.
3. Przejdź do następnej komórki ( B6 ). Po zaznaczeniu na pasku formuły widzimy, że zawiera wyliczone wyrażenie ( = D6 / C6 ), które pobiera dane z innych elementów tabeli, w szczególności z komórki D6 . Zatem komórka D6 odnosi się do danych elementu B6 i na odwrót, co powoduje utrwalenie.

   Tutaj dość szybko obliczyliśmy związek, ale w rzeczywistości zdarzają się sytuacje, w których w procesie obliczeniowym zaangażowanych jest wiele komórek, a nie trzy elementy, tak jak my. Wtedy wyszukiwanie może trwać dość długo, ponieważ musisz przestudiować każdy element cykliczności.

4. Teraz musimy zrozumieć, która komórka ( B6 lub D6 ) zawiera błąd. Chociaż formalnie nie jest to nawet pomyłka, ale po prostu nadmierne używanie odniesień, co prowadzi do pętli. Podczas procesu decydowania o komórce do edycji musisz zastosować logikę. Nie ma jasnego algorytmu działania. W każdym przypadku ta logika będzie jego własna.

   Na przykład, jeśli w naszej tabeli całkowita kwota powinna być obliczona poprzez pomnożenie ilości faktycznie sprzedanych towarów przez ich cenę, to możemy powiedzieć, że link, który oblicza kwotę z całkowitej kwoty sprzedaży jest wyraźnie zbyteczny. Dlatego usuwamy go i zastępujemy go wartością statyczną.

5. Podobną operację wykonujemy dla wszystkich innych wyrażeń cyklicznych, jeśli istnieją na arkuszu. Po tym, jak absolutnie wszystkie odwołania cykliczne zostały usunięte z książki, wiadomość o istnieniu tego problemu powinna zniknąć z paska stanu.

   Ponadto, jeśli wyrażeń cyklicznych zostały całkowicie usunięte, można dowiedzieć się za pomocą narzędzia do sprawdzania błędów. Przejdź do zakładki "Formuły" i kliknij na trójkąt, który już znamy, po prawej stronie przycisku "Sprawdź błędy" na pasku narzędzi "Formula Dependencies" . Jeśli pozycja "Linki cykliczne" nie jest aktywna w menu, to usunęliśmy wszystkie takie obiekty z dokumentu. W przeciwnym razie będziesz musiał zastosować procedurę usuwania do pozycji, które są na liście w taki sam sposób jak poprzednio.

Włączanie operacji cyklicznych

W poprzedniej części lekcji mówiliśmy głównie o tym, jak radzić sobie z referencjami cyklicznymi lub jak je znaleźć. Ale wcześniej rozmowa dotyczyła również tego, że w niektórych przypadkach mogą one być użyteczne i świadomie używane przez użytkownika. Na przykład ta metoda jest często używana do iteracyjnych obliczeń przy konstruowaniu modeli ekonomicznych. Problem polega jednak na tym, że niezależnie od tego, czy świadomie lub nieświadomie używasz wyrażenia cyklicznego, program Excel domyślnie nadal blokuje na nich operację, aby nie spowodować nadmiernego przeciążenia systemu. W takim przypadku kwestia wymuszonego wyłączenia takiego zamka staje się aktualna. Zobaczmy, jak to zrobić.

1. Przede wszystkim przechodzimy do zakładki "Plik" aplikacji Excel.
2. Następnie kliknij element "Opcje" znajdujący się po lewej stronie otwieranego okna.
3. Otworzy się okno opcji programu Excel. Musimy przejść do zakładki "Formuły" .
4. To w oknie, które się otworzy, będziesz mógł wykonywać cykliczne operacje. Przechodzimy do właściwego bloku tego okna, w którym znajdują się same ustawienia Excela. Będziemy pracować z blokiem konfiguracji "Opcje obliczeń" , który znajduje się na samej górze.

   Aby zezwolić na użycie wyrażeń cyklicznych, zaznacz pole wyboru Włącz obliczenia iteracyjne . Ponadto w tym samym bloku można ustawić maksymalną liczbę iteracji i względny błąd. Domyślnie ich wartości wynoszą odpowiednio 100 i 0,001. W większości przypadków nie trzeba zmieniać tych parametrów, chociaż w razie potrzeby można wprowadzić zmiany w określonych polach. Ale tutaj należy wziąć pod uwagę, że zbyt wiele iteracji może prowadzić do poważnego obciążenia programu i systemu jako całości, szczególnie jeśli pracujesz z plikiem zawierającym wiele wyrażeń cyklicznych.

   Tak więc zaznacz opcję "Włącz obliczenia iteracyjne" , a następnie kliknij przycisk "OK" znajdujący się w dolnej części okna opcji programu Excel, aby nowe ustawienia zaczęły obowiązywać.

5. Potem automatycznie przejdziemy do arkusza bieżącej książki. Jak widać, w komórkach, w których znajdują się formuły cykliczne, teraz wartości są obliczane poprawnie. Program nie blokuje w nich obliczeń.

Ale nadal warto zauważyć, że włączenie cyklicznych operacji nie powinno być nadużywane. Użyj tej funkcji tylko wtedy, gdy użytkownik jest w pełni przekonany o swoich potrzebach. Nieuzasadnione włączenie operacji cyklicznych może nie tylko doprowadzić do nadmiernego obciążenia systemu i spowolnić obliczenia podczas pracy z dokumentem, ale użytkownik może niechcący wprowadzić błędne wyrażenie cykliczne, które domyślnie zostanie natychmiast zablokowane przez program.

Jak widzimy, w przytłaczającej większości przypadków odniesienia cykliczne są zjawiskiem, które należy rozwiązać. Aby to zrobić, najpierw powinieneś znaleźć najbardziej cykliczną relację, następnie obliczyć komórkę, w której znajduje się błąd, i ostatecznie wyeliminować ją, dokonując odpowiednich korekt. Ale w niektórych przypadkach operacje cykliczne mogą być przydatne w obliczeniach i świadomie wykonane przez użytkownika. Ale nawet wtedy warto ich używać ostrożnie, poprawnie konfigurując program Excel i znając zakres dodawania takich linków, które używane w dużych ilościach mogą spowolnić system.

• ← Formy wprowadzania danych w Microsoft Excel
• Odzyskiwanie brakujących arkuszy w programie Microsoft Excel →