Pracuj z cyklicznymi odniesieniach w programie Excel

Uważa się, że cykliczne odniesienia w Excele są błędnym wyrazem. Rzeczywiście, dość często jest to właśnie tak, ale wciąż nie zawsze. Czasami stosują się dość świadomie. Dowiedzmy się, jakie linki cykliczne są jak tworzyć je, jak znaleźć już istniejące w dokumencie, jak z nimi współpracować lub jak go usunąć.

Wykorzystanie cyklicznych odniesień

Przede wszystkim dowiedz się, co to jest link cykliczny. W istocie, wyrażenie, które przez wzory w innych komórkach, odnosi się do siebie. Ponadto może być link umieszczony w elemencie liści, do którego się odnosi.

Należy zauważyć, że domyślnie nowoczesne wersje Excel automatycznie blokują proces wykonania cyklicznej operacji. Wynika to z faktu, że takie wyrażenia w przytłaczającej większości są błędne, a zapętlenie wytwarza stały proces przeliczania i obliczania, który tworzy dodatkowe obciążenie w systemie.

Tworzenie linku cyklicznego

Teraz zobaczmy, jak stworzyć najprostsze wyrażenie cykliczne. Będzie to związek zlokalizowany w tej samej komórce, na której odnosi się.

1. Podświetlamy element arkusza A1 i napisz w nim następujące wyrażenie:

   = A1.

   Następnie kliknij przycisk Enter na klawiaturze.



2. Po tym pojawia się okno dialogowe Cyclic Expression Ostrzeżenie. Kliknij go na przycisk "OK".



3. W ten sposób otrzymaliśmy cykliczną pracę na arkuszu, w którym komórka odnosi się do siebie.

Niewielkie zadanie komplikujące i tworzy cykliczną ekspresję z kilku komórek.

1. W dowolnym elemencie arkusza, napisz numer. Niech będzie komórką A1, a numer 5.



2. W innej komórce (B1) napisz wyrażenie:

   = C1.



3. W następnym elemencie (C1) będziemy nagrywać taką formułę:

   = A1.



4. Następnie wracamy do komórki A1, w której ustawiono numer 5. Odnieś się do niego do elementu B1:

   = B1.

   Kliknij przycisk Enter.



5. W ten sposób cykl zamknięty i otrzymaliśmy klasyczny link cykliczny. Po zamknięciu okna ostrzeżenia widzimy, że program oznaczony cykliczną wiązanie z niebieskimi strzałkami na arkuszu, które nazywane są strzałkami śledzenia.

Teraz wraca do tworzenia cyklicznej ekspresji na przykładzie tabeli. Mamy tabelę implementacji tabeli. Składa się z czterech kolumn, które wskazują nazwę towarów, liczba sprzedanych produktów, cenę i kwotę przychodów ze sprzedaży całej objętości. Stół w ostatniej kolumnie ma już formuły. Obliczają przychody, mnożąc kwotę ceny.

1. Aby poluzować formułę w pierwszej linii, zaznaczamy element arkuszy z liczbą pierwszego produktu (B2). Zamiast wartości statycznej (6), wprowadź tam wzór, który rozważy ilość towarów poprzez podzielenie całkowitej kwoty (D2) za cenę (C2):

   = D2 / C2

   Kliknij przycisk Enter.



2. Okazało się pierwsze cykliczne łącze, związek, w którym jest zaznajomiony ze strzałką śledzenia. Ale jak widzimy, wynik jest błędny i równy zero, jak już wcześniej powiedziano, Excel blokuje wykonanie operacji cyklicznych.



3. Skopiuj wyrażenie do wszystkich innych komórek kolumny z ilością produktów. Aby to zrobić, ustaw kursor do dolnego kąta prawego tego elementu, który już zawiera formułę. Kursor jest konwertowany na krzyż, który jest wywołany, aby zadzwonić do markera napełniającego. Wyczyść lewy przycisk myszy i wyciągnij ten krzyż na końcu tabeli w dół.



4. Jak widać, wyrażenie zostało skopiowane do wszystkich elementów kolumny. Ale tylko jeden związek jest oznaczony strzałką śladową. Zwróć uwagę na przyszłość.

Wyszukaj cykliczne linki

Jak już widzieliśmy wyższe, nie we wszystkich przypadkach program oznacza relację cyklicznego odniesienia do obiektów, nawet jeśli jest na arkuszu. Biorąc pod uwagę fakt, że w przytłaczających większości operacje cykliczne są szkodliwe, powinny zostać usunięte. Ale dla tego muszą najpierw znaleźć. Jak to zrobić, jeśli wyrażenia nie są oznaczone linią strzałką? Zajmijmy się tym zadaniem.

1. Jeśli więc uruchomisz plik Excel, masz okno informacji, które zawiera link cykliczny, pożądane jest go znaleźć. Aby to zrobić, przejdź do zakładki "Formuły". Kliknij na wstążkę na trójkącie, który znajduje się po prawej stronie przycisku "Sprawdzanie błędów", znajduje się w bloku narzędzi "zależność zależności zależnego". Otwiera się menu, w którym kursor powinien być hostowany na "Cyclic Links". Następnie następujące menu otwiera listę adresów elementów arkuszy, w których program odkrył wyrażenia cykliczne.



2. Po kliknięciu określonego adresu, odpowiednia komórka jest wybrana na arkuszu.

Istnieje inny sposób, aby dowiedzieć się, gdzie znajduje się link cykliczny. Wiadomość o tym problemie i adres elementu zawierającego podobną ekspresję znajduje się po lewej stronie ciągu statusu, który znajduje się na dole okna Excel. Prawda, w przeciwieństwie do poprzedniej wersji, adresy nie wszystkich elementów zawierających cykliczne odniesienia będą wyświetlane na pasku stanu, jeśli istnieje wiele z nich, ale tylko jeden z nich, który pojawił się przed innymi.

Ponadto, jeśli jesteś w książce zawierającej cykliczną ekspresję, a nie na arkuszu, gdzie znajduje się, a na drugim, w tym przypadku, w tym przypadku tylko wiadomość o obecności błędu zostanie wyświetlona na pasku stanu.

Lekcja: Jak znaleźć cykliczne linki do programu Excel

Korekta cyklicznych odniesień

Jak wspomniano powyżej, w przytłaczającej większości przypadków cykliczne operacje są zły, z których należy łatwo wyeliminować. Dlatego też jest naturalne, że po wykryciu cyklicznego połączenia konieczne jest skorygowanie go do wprowadzenia wzoru do normalnego formularza.

Aby poprawić zależność cykliczną, musisz śledzić całe połączenia komórek. Nawet jeśli sprawdzenie wskazywała na konkretną komórkę, błąd może nie być objęty w sobie, ale w innym elemencie łańcucha zależności.

1. W naszym przypadku, pomimo faktu, że program poprawnie wskazywał na jedną z komórek cyklu (D6), rzeczywisty błąd leży w innej komórce. Wybierz element D6, aby dowiedzieć się, które komórki wyciąga wartość. Patrzymy na wyrażenie w ciągu wzoru. Jak widzimy, wartość w tym elemencie arkusza jest utworzona przez pomnożenie zawartości komórek B6 i C6.



2. Idź do komórki C6. Podświetlamy go i spojrzeć na ciąg formuły. Jak widzimy, jest to zwykła wartość statyczna (1000), która nie jest produktem obliczającym formułę. Dlatego można powiedzieć, że określony element nie zawiera błędów powodujących cykliczne operacje.



3. Idź do następnej komórki (B6). Po wybraniu w Wierszu Wiersz widzimy, że zawiera ona obliczoną ekspresję (= D6 / C6), która ciągnie dane z innych elementów tabeli, w szczególności z komórki D6. W ten sposób komórka D6 odnosi się do danych elementu B6 i odwrotnie, co powoduje pętlę.

   

   Tutaj relacja, którą obliczyliśmy całkiem szybko, ale w rzeczywistości istnieją przypadki, gdy wiele komórek jest zaangażowanych w proces obliczeniowy, a nie trzy elementy, jak mamy. Następnie wyszukiwanie może trwać sporo czasu, ponieważ będzie musiał studiować każdy cykliczny element.

4. Teraz musimy zrozumieć, która komórka (B6 lub D6) zawiera błąd. Chociaż formalnie nie jest nawet błędem, ale po prostu nadmiernym wykorzystaniem odniesień, które prowadzi do zapętlenia. Podczas procesu rozwiązywania tej komórki należy edytować, musisz zastosować logikę. Nie ma jasnego algorytmu do działania. W każdym przypadku ta logika będzie własna.

   Na przykład, jeśli nasza tabela podzieliła się całkowitą kwotę, należy obliczyć, pomnożenie liczby sprzedanych towarów po jego cenie, możemy powiedzieć, że link licząc kwotę całkowitej ilości sprzedaży jest wyraźnie zbędne. Dlatego usuwamy go i zastępujemy znaczenie statyczne.



5. Taka operacja jest przeprowadzana przez wszystkie inne cykliczne wyrażenia, jeśli są na arkuszu. Po absolutnie wszystkie połączenia cykliczne zostały usunięte z książki, wiadomość o obecności tego problemu powinna zniknąć z ciągu statusu.

   Ponadto wyrażenia cykliczne zostały całkowicie usunięte, możesz dowiedzieć się za pomocą narzędzia do sprawdzania błędów. Przejdź do zakładki "Formuły" i kliknij trójkąt już znany nam na prawo od przycisku "Sprawdzanie błędów" w grupie przyrządów "W zależności od formuł" . Jeśli element "cykliczne linki" w menu Uruchamiające nie jest aktywne, oznacza to, że usunąliśmy wszystkie takie przedmioty z dokumentu. W przeciwnym przypadku musisz zastosować procedurę usuwania do elementów wymienionych, taki sam w ten sam sposób.

Zezwolenie na wykonanie operacji cyklicznych

W poprzedniej części lekcji powiedzieliśmy, głównie jak radzić sobie z cyklicznymi odniesieniach lub jak je znaleźć. Ale wcześniej rozmowa była również o tym, że w niektórych przypadkach, wręcz przeciwnie, mogą być przydatne i świadomie używane przez użytkownika. Na przykład, dość często ta metoda jest używana do obliczeń iteracyjnych podczas budowy modeli ekonomicznych. Ale kłopotami jest to, że niezależnie od tego, czy jesteś świadomie lub nieświadomie używasz cyklicznej ekspresji, program Excel nadal będzie zablokować operację w nich domyślnie, aby domyślnie prowadzić do nadmiernego przeciążenia systemu. W tym przypadku ważne jest kwestia wymuszonego wyłączania taka blokującego. Zobaczmy, jak to zrobić.

1. Przede wszystkim przenosimy się do zakładki "Plik" aplikacji Excel.



2. Następnie kliknij element "Parametry" znajdujący się po lewej stronie okna, które się otworzyło.



3. Okno parametrów wygnania zaczyna działać. Musimy przejść do zakładki "Formuły".



4. W oknie otwiera się, że zostanie otwarte wykonywanie wykonania operacji cyklicznych. Przejdź do prawego bloku tego okna, gdzie ustawienia Excel są bezpośrednio. Będziemy pracować z blokiem ustawień "Parametry obliczeniowe", który znajduje się na górze.

   Aby umożliwić korzystanie z cyklicznych wyrażeń, musisz zainstalować kleszcz o parametrze "Włącz obliczenia iteracyjne". Ponadto w tym samym bloku można skonfigurować limit liczbę iteracji i błędu względnego. Domyślnie ich wartości są odpowiednio 100 i 0,001. W większości przypadków parametry te nie muszą być zmieniane, chociaż w razie potrzeby możliwe jest wprowadzenie zmian w określonych polach. Ale tutaj konieczne jest rozważenie, że zbyt wiele iteracji może prowadzić do poważnego obciążenia programu i systemu jako całości, zwłaszcza jeśli pracujesz z plikiem, w którym umieszcza się wiele cyklicznych wyrażeń.

   Instalujemy więc kleszcz o parametrze "Włącz obliczenia Iteracyjne", a następnie wprowadzone nowe ustawienia, kliknij przycisk "OK", znajduje się na dole okna parametrów Excel.



5. Następnie automatycznie udamy się do arkusza bieżącej książki. Jak widzimy, w komórkach, w których znajdują się cykliczne wzory, teraz wartości są poprawnie obliczane. Program nie blokuje się w nich obliczeń.

Ale warto zauważyć, że włączenie cyklicznych operacji nie powinno być wykorzystywane. Zastosuj tę funkcję, następuje tylko wtedy, gdy użytkownik jest całkowicie pewny siebie w swojej potrzebie. Nieuzasadnione włączenie operacji cyklicznych może nie tylko prowadzić do nadmiernego obciążenia w systemie i spowolnić obliczenia podczas pracy z dokumentem, ale użytkownik może nieumyślnie dokonać błędnego wyrażenia cyklicznego, co domyślnie byłoby zablokowane przez program.

Jak widzimy, w przytłaczającej większości przypadków cykliczne odniesienia są zjawiskiem, z którym trzeba walczyć. W tym celu, przede wszystkim, powinieneś wykryć samą samą relację cykliczną, a następnie obliczyć komórkę, w której błąd jest zawarty i ostatecznie wyeliminować, dokonując odpowiednie korekty. Ale w niektórych przypadkach operacje cykliczne mogą być przydatne przy obliczaniu i używaniu przez użytkownika świadomie. Ale nawet wtedy warto używać ich stosowania z ostrożnością, prawidłowo skonfigurowanie programu Excel i poznawanie środka w dodaniu takich odniesień, które, w stosowaniu do stosowania w ilościach, może spowolnić działanie systemu.