Inspekcja Formuł

Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Inspekcja Formul.xlsx tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji

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

Ta lekcja może być obejrzana lub przeczytana poniżej.

Film wygląda najlepiej jeśli będzie odtwarzany w rozdzielczości 720p HD, rozdzielczość można zmienić dopiero po uruchomieniu filmu klikając na ikonie trybika która pojawi się w prawym dolnym rogu poniższego ekranu. Po kilku sekundach od zmiany obraz wyostrzy się.

Jeśli pracujemy na dużych i skomplikowanych plikach, szczególnie jeżeli to nie my byliśmy ich autorem, z pewnością użyteczne okażą się polecenia z grupy ‘Inspekcja Formuł’.

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 ‘Inspekcja Formuł 1’)

W poniższym przykładzie pewne regiony w nieznanych nam miesiącach były objęte promocją. Funkcja obliczająca ‘sumę sprzedaży w regionach objętych promocją’ zwraca błąd. Chcielibyśmy ustalić które regiony w których miesiącach były objęte promocją oraz jak możemy skorygować 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 - ta zaznaczona czerwoną strzałką.

Polecenia śledź poprzedniki i zależności są najczęściej używane 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 ‘Inspekcja Formuł 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 można 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, także 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 ‘Inspekcja Formuł 3’)

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

Oznacza to, ż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.

Informacje o zamierzonych odwołaniach cyklicznych znajdziesz w lekcji ‘Triki dla Ekspertów’.