Odwołania cykliczne (pętle) w formułach

Odwołanie cykliczne występuje, jeśli w formule jest podane niepoprawne odwołanie do komórek z danymi. Taka sytuacja najczęściej jest niepożądana i traktowana jako błąd w obliczeniach. Okazuje się jednak, że wystąpienie odwołania cyklicznego można wykorzystać do własnych celów przy analizowaniu danych czy wykonywaniu obliczeń.

W trakcie budowania formuły może się zdarzyć, że będzie odwoływała się ona do wartości zależnej od wartości zwracanej przez nią samą. Jest to typowy błąd powodujący wystąpienie odwołania cyklicznego.

Jeśli pracujesz w arkuszu kalkulacyjnym z domyślnymi ustawieniami, program nie może sobie poradzić z taką zapętloną formułą i wyświetla komunikat widoczny na filmie.

Jeśli przez pomyłkę podałeś w formule niepoprawne odwołanie, kliknij przycisk OK, a zostanie wyświetlony pasek narzędziowy Odwołanie cykliczne.

Nawet jeżeli odwołanie cykliczne zostanie zignorowane poprzez zamknięcie paska narzędziowego, arkusz będzie o nim stale przypominał. Na pasku stanu (u dołu okna) zobaczysz napis „Cykliczne” ze wskazanym adresem komórki zawierającej niepoprawne obliczenia. Czasem stosowanie odwołań cyklicznych jest jednak przydatne. Przyjmijmy, że chcesz naliczyć premię wypłacaną uczniom, którzy biorą udział w akcji świątecznej, polegającej na sprzedawaniu własnoręcznie zrobionych kartek i zbieraniu pieniędzy na wyposażenie sali chemicznej. Premia  jest obliczana dla każdego ucznia proporcjonalnie do wypracowanego przychodu ze sprzedaży. Fundusz, z którego wypłacane są premie, stanowi 2% wyniku nettonettonetto ogółem.

Obliczysz go, wpisując do komórki D10 formułę =E10*G1, jak to zostało pokazane na filmie. Ogólne środki na wypłacenie premii zostaną zwrócone w komórce D10 Formuła zwróci jednak wartość zerową, gdyż według przyjętych założeń, przy obliczaniu funduszu premiowego, konieczna jest znajomość wartości wyniku netto ogółem. Aby przeprowadzić potrzebne obliczenia, w komórce E10 wpisz następującą formułę: =B10‑C10‑D10. Jak widać, powstało odwołanie cykliczne i żadna wartość nie zostaje zwrócona. Zostało to spowodowane tym, że formuły w komórkach D10 i E10 odwołują się do siebie nawzajem i w związku z tym przeprowadzenie obliczeń w normalnym trybie nie jest możliwe. Żeby wyliczyć premie i wynik netto, wykonaj następujące czynności:

1. Kliknij okrągły Przycisk pakietu Office lub rozwiń menu Plik i przejdź do pozycji Opcje programu Excel (Opcje).

2. W oknie, które się pojawi, przejdź do zakładki Formuły.

3. Włącz opcję Włącz obliczanie iteracyjne. Wartości pól Maksymalna liczba iteracji i Maksymalna zmiana zmodyfikuj, jeśli wynik obliczeń nie jest poprawny (zwiększasz liczbę iteracji) lub jeśli potrzebujesz większej dokładności obliczeń (zmniejszasz wartość w polu Maksymalna zmiana). Możesz teraz przystąpić do obliczenia wysokości premii dla każdego ucznia (czyli uzyskaną wartość w komórce D10 rozdzielić proporcjonalnie między uczniów).

Pamiętaj, aby po zakończeniu pracy z arkuszem wyłączyć iterację w oknie opcji. Pozostawienie tej opcji uaktywnionej spowoduje, że arkusz nie będzie informował o odwołaniach cyklicznych wynikających z błędów w formułach, co może skutkować niepoprawnym wykonywaniem obliczeń. W razie potrzeby użycia iteracji możesz ją ponownie włączyć.

R9PjZPmONVIJZ

Nagranie filmowe dotyczące odwołań cyklicznych w formułach.

Nagranie filmowe dotyczące odwołań cyklicznych w formułach.

Film dostępny pod adresem /preview/resource/R9PjZPmONVIJZ

Nagranie filmowe dotyczące odwołań cyklicznych w formułach.

Pobierz przykładowe dane:

Ćwiczenie 1

Wyobraź sobie, że chcesz zorganizować akcję charytatywną – międzyszkolny turniej sportowy. Każda osoba, która będzie chciała wziąć w nim udział będzie musiała wpłacić 10 zł. Każdy z uczestników otrzyma dyplom uczestnictwa, którego przygotowanie kosztuje 1 zł. Załóżmy, że 15% wpisowego przeznaczymy na przygotowanie dyplomów oraz dodatkowe atrakcje w trakcie turnieju. W konkursie weźmie udział pięć szkół, z których każda wystawi około 20 uczestników. Wylicz, ile zbierzesz pieniędzy, ile przeznaczysz na dyplomy, a ile pozostanie w budżecie na dodatkowe atrakcje.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

R18LM2VSdmKbw

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A i B wprowadzono dane ankiety. W kolumnie A, komórkę A1 zatytułowano ID UCZNIA, w komórkach od A2 do A18 wpisano kolejne numer przypisane uczniom od 1 do 17. W kolumnie B, komórkę B1 zatytułowano WPŁATA, w komórkach od B2 do B18 wpisano wartości wpłat. Dodatkowo zaprezentowano sposób obliczania zebranych pieniędzy, kosztów dyplomów oraz sumy, która pozostanie w budżecie po wydatkach. Formułę obliczania należy wpisać w odpowiedniej komórce. W tym przypadku w komórce E2, E3 oraz E4. W komórce D2 wpisano tekst SUMA, w komórce E2 wpisano formułę: =SUMA(B2 :B101). W komórce D3 wpisano tekst KOSZT DYPLOMÓW, w komórce E3 wpisano formułę: =100*1+E2*0,15. W komórce D4 wpisano tekst POZOSTAŁO, w komórce E4 wpisano formułę: =E2-E3.

3

Ćwiczenie 2

Używając arkusza z poprzedniego zadania oszacuj, ile szkół z twojego regionu musiałoby wziąć udział w turnieju, aby uzbierać fundusze na wybraną przez ciebie akcję charytatywną.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

RsRPFMeJWiZxD

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A i B wprowadzono dane ankiety. W kolumnie A, komórkę A1 zatytułowano ID UCZNIA, w komórkach od A2 do A18 wpisano kolejne numer przypisane uczniom od 1 do 17. W kolumnie B, komórkę B1 zatytułowano WPŁATA, w komórkach od B2 do B18 wpisano wartości wpłat. Dodatkowo zaprezentowano sposób obliczania. W komórce E2 wpisano tekst LICZBA SZKÓŁ. W komórce F2 wpisano wartość 5. W komórce E3 wpisano LICZBA UCZNIÓW Z KAŻDEJ SZKOŁY. W komórce F3 wpisano wartość 20. W komórce E4 wpisano tekst CENA UDZIAŁU. W komórce F4 wpisano wartość 10. W komórce E5 wpisano tekst KOSZT DYPLOMÓW. W komórce F5 wpisano formułę: =F2*F3*1+F2*F3*F4*0,15. W komórce E6 wpisano tekst: POZOSTAŁO. W komórce F6 wpisano formułę =F2*F3*1+F2*F3*F4*0,15. W komórce E8 wpisano tekst CEL CHARYTATYWNY. W komórce F8 wpisano wartość 1000. W komórce E9 wpisano tekst CZY CEL OSIĄGNIĘTY?. W komórce F9 wpisano formułę =JEŻELI(F6>=F8;"TAK";"NIE").Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A i B wprowadzono dane ankiety. W kolumnie A, komórkę A1 zatytułowano ID UCZNIA, w komórkach od A2 do A18 wpisano kolejne numer przypisane uczniom od 1 do 17. W kolumnie B, komórkę B1 zatytułowano WPŁATA, w komórkach od B2 do B18 wpisano wartości wpłat. Dodatkowo zaprezentowano sposób obliczania. W komórce E2 wpisano tekst LICZBA SZKÓŁ. W komórce F2 wpisano wartość 5. W komórce E3 wpisano LICZBA UCZNIÓW Z KAŻDEJ SZKOŁY. W komórce F3 wpisano wartość 20. W komórce E4 wpisano tekst CENA UDZIAŁU. W komórce F4 wpisano wartość 10. W komórce E5 wpisano tekst KOSZT DYPLOMÓW. W komórce F5 wpisano formułę: =F2*F3*1+F2*F3*F4*0,15. W komórce E6 wpisano tekst: POZOSTAŁO. W komórce F6 wpisano formułę =F2*F3*1+F2*F3*F4*0,15. W komórce E8 wpisano tekst CEL CHARYTATYWNY. W komórce F8 wpisano wartość 1000. W komórce E9 wpisano tekst CZY CEL OSIĄGNIĘTY?. W komórce F9 wpisano formułę =JEŻELI(F6>=F8;"TAK";"NIE").

Słownik