Śledzenie Odwołań

Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Śledzenie Odwołań.xlsx tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji

Przykłady dla wszystkich lekcji szkolenia Excel 2007: ExcelSzkolenie.pl Cwiczenia Excel 2007.zip

Jeśli pracujemy na dużych i skomplikowanych plikach, szczególnie jeżeli to nie my byliśmy ich autorem, bardzo użyteczną funkcją Excela jest ‘Śledzenie odwołań’.

Odwołania dzielą się na poprzedniki i zależności.

Poprzedniki to komórki, z których korzysta formuła w interesującej nas komórce.

Zależności to wszystkie te komórki, które korzystają z danych w interesującej nas komórce.

Przykład 1.

(arkusz ‘Śledzenie Odwołań 1’)

W poniższym przykładzie wybrano nie wiemy, które regiony i w których miesiącach były objęte promocją, nie wiemy także dlaczego formuła wyliczająca ‘sumę sprzedaży w regionach objętych promocją’ zwraca błąd.

Ustawiamy aktywną komórkę na formule która nas interesuje (komórka H25) i na karcie ‘Formuły’ odnajdujemy polecenie ‘Śledź poprzedniki’.

Kliknięcie tego polecenia spowoduje wyświetlenie strzałek łączących formułę ze wszystkimi komórkami z których pobiera dane.

Wiemy teraz nie tylko z których danych korzysta formuła ale także, która z danych powoduje błąd formuły (zaznaczona czerwoną strzałką).

Polecenia śledź poprzedniki i zależności są najczęściej używana do:

- zrozumienia formuł stworzonych przez innych użytkowników

- znajdowania powodów błędów w wynikach

- znajdowania przyczyn odwołań cyklicznych

- sprawdzania czy dane, które chcemy skasować, są używane w innym miejscu arkusza.

Strzałki usuwamy klikając na polecenie ‘Usuń strzałki’ (karta Formuły).

Podobny rezultat daje podwójne kliknięcie na komórkę z formułą. Tym razem komórki z których formuła korzysta zostają zaznaczone kolorowymi prostokątami. Sposób ten umożliwia oglądanie tylko poprzedników pierwszego poziomu, czyli komórek bezpośrednio wpływających na wynik formuły.

To rozwiązanie ma tą zaletę że możemy łatwo modyfikować formułę poprzez złapanie odpowiedniego prostokąta za bok i przeciągnięcie go do właściwej komórki.

Przykład 2.

(arkusz ‘Śledzenie Odwołań 2’)

W tym przykładzie proponuje uaktywnić komórkę z sumą sprzedaży dla filii od 1 do 7 (G9) i dwukrotnie wybrać polecenie: Śledź poprzedniki

Ze strzałek które się pokazały należy wywnioskować że suma 1039 jest wynikiem dodania sprzedaży z 4 komórek powyżej oraz nieznanej ilości odwołań do innych arkuszy w tym samym lub innym pliku odwołania te zostały oznaczone przerywaną linią prowadzącą do symbolu zaznaczonego czerwonym prostokątem.

W naszym przykładzie komórka odwołuje się do jednej danej z innego pliku, co widać w pasku formuły, gdyby jednak odwołań było więcej zostałoby to zaznaczone w taki sam sposób tylko jednym symbolem.

Poprzedniki drugiego poziomu to dane sprzedaży produktów w filii 7 znajdujące się w wierszu 3 tego arkusza. Zarówno polecenie Śledź poprzedniki jak i Śledź zależności umożliwia sprawdzanie wielu poziomów połączeń między komórkami.

Proponuje teraz skorzystać z polecenia ‘Usuń strzałki’ i sprawdzić zależności komórki G9 – dwukrotnie klikamy ‘Śledź zależności’.

Dana w komórce G9 wykorzystana jest przez komórkę I14, która z kolei wykorzystywana jest przez komórkę K14 oraz komórkę (lub komórki) z innego arkusza (arkuszy) lub innego aktualnie otwartego pliku Excela co zostało oznaczone symbolem w czerwonym prostokącie.

Polecenie to nie pokaże zależności jeśli będą one w innym zamkniętym pliku Excela.

Zainteresujmy się teraz komórką K14, wydaje się nam że jest to wynik już nieaktualnych i niepotrzebnych obliczeń, który można wykasować dla pewności sprawdzimy, czy dana ta nie jest wykorzystywana przez inne formuły. Usuwamy strzałki. Uaktywniamy komórkę K14 i klikamy ‘Śledź zależności’.

Wyświetlony zostaje komunikat stwierdzający że nie znaleziono odwołań do aktywnej komórki. Możemy ją teraz bez obaw wykasować.

Przykład 3.

(arkusz ‘Śledzenie Odwołań 3’)

W arkuszu ‘Śledzenie Odwołań 3’ w dolnym lewym rogu znajduje się informacja ‘Odwołania Cykliczne G13’.

Oznacza ona że formuła odwołuje się do samej siebie, mówiąc obrazowo to tak jakby powiedzieć że ktoś ma tyle lat ile miał 5 lat temu plus 5. Z tą różnicą że odwołania cykliczne jakie występują w Excelu są przeważnie zdecydowanie bardziej skomplikowane.

Problem taki najprościej rozwiązać stając w komórce zawierającej odwołanie cykliczne i klikając ‘Śledź zależności’ tak długo aż zatoczone zostanie pełne koło i powrócimy do tej samej komórki.

Zysk w 2010 (G11) zależy od Sprzedaży w 2010 (G13) to powiązanie ma sens.

Zmiana wielkości Zysku (H11) musi zależeć od Zysku 2010 (G11).

Kolejne powiązanie jest tym, którego szukaliśmy, nie ma sensu uzależnienie zmiany sprzedaży od zmiany zysku, zależność powinna być odwrotna, formułę w H13 należy poprawić.

Jeżeli podczas wprowadzania formuł w Excelu popełnimy błąd i zapętlimy formuły Excel sam poinformuje nas o odwołaniu cyklicznym i wyświetli strzałki zależności, powyżej opisany sposób znajdowania błędów stosuje się wtedy gdy ktoś zignorował automatyczne ostrzeżenie Excela.