Analiza danych programu Excel - inspekcja formuł

Możesz chcieć sprawdzić dokładność formuł lub znaleźć źródło błędu. Polecenia inspekcji formuł programu Excel zapewniają łatwy sposób wyszukiwania

Które komórki biorą udział w obliczaniu formuły w aktywnej komórce.
Które formuły odnoszą się do aktywnej komórki.

Te ustalenia są przedstawione graficznie za pomocą linii strzałek, co ułatwia wizualizację. Możesz wyświetlić wszystkie formuły w aktywnym arkuszu za pomocą jednego polecenia. Jeśli formuły odnoszą się do komórek w innym skoroszycie, otwórz również ten skoroszyt. Program Excel nie może przejść do komórki w skoroszycie, która nie jest otwarta.

Ustawianie opcji wyświetlania

Musisz sprawdzić, czy opcje wyświetlania dla używanych skoroszytów są poprawnie ustawione.

Kliknij FILE > Options.
W oknie dialogowym Opcje programu Excel kliknij przycisk Zaawansowane.
W opcjach wyświetlania skoroszytu -
- Wybierz skoroszyt.
- Sprawdź, czy w obszarze W przypadku obiektów pokaż, zaznaczono opcję Wszystko.
Powtórz ten krok dla wszystkich kontrolowanych skoroszytów.

Śledzenie precedensów

Komórki poprzedzające to te komórki, do których odwołuje się formuła w aktywnej komórce.

W poniższym przykładzie aktywną komórką jest C2. W C2 masz wzór=B2*C4.

B2 i C4 są poprzednimi komórkami dla C2.

Aby prześledzić precedensy komórki C2,

Kliknij komórkę C2.
Kliknij kartę Formuły.
Kliknij opcję Śledź poprzedniki w grupie Inspekcja formuł.

Zostaną wyświetlone dwie strzałki, jedna od B2 do C2, a druga od C4 do C2, śledząc poprzednie.

Zwróć uwagę, że w celu śledzenia precedensów komórki komórka powinna mieć formułę z prawidłowymi odwołaniami. W przeciwnym razie zostanie wyświetlony komunikat o błędzie.

Kliknij komórkę, która nie zawiera formuły, lub kliknij pustą komórkę.
Kliknij opcję Śledź poprzedniki w grupie Inspekcja formuł.

Otrzymasz wiadomość.

Usuwanie strzał

Kliknij opcję Usuń strzałki w grupie Inspekcja formuł.

Wszystkie strzałki w arkuszu znikną.

Śledzenie zależności

Zależne komórki zawierają formuły, które odwołują się do innych komórek. Oznacza to, że jeśli aktywna komórka wnosi wkład do formuły w innej komórce, druga komórka jest komórką zależną od aktywnej komórki.

W poniższym przykładzie C2 ma wzór =B2*C4. Dlatego C2 jest komórką zależną od komórek B2 i C4

Aby prześledzić zależności komórki B2,

Kliknij komórkę B2.
Kliknij kartę Formuły.
Kliknij opcję Śledź zależności w grupie Inspekcja formuł.

Strzałka pojawia się od B2 do C2, pokazując, że C2 jest zależne od B2.

Aby prześledzić zależności komórki C4 -

Kliknij komórkę C4.
Kliknij kartę Formuła> Śledź zależności w grupie Inspekcja formuł.

Kolejna strzałka pojawia się od C4 do C2, pokazując, że C2 jest również zależne od C4.

Kliknij Remove Arrowsw grupie Formula Auditing. Wszystkie strzałki w arkuszu znikną.

Note- Aby śledzić zależności komórki, do komórki powinno się odwoływać formuła w innej komórce. W przeciwnym razie zostanie wyświetlony komunikat o błędzie.

Kliknij komórkę, do której B6 nie odwołuje się żadna formuła, ani kliknij pustą komórkę.
Kliknij opcję Śledź zależności w grupie Inspekcja formuł. Otrzymasz wiadomość.

Praca z formułami

Zrozumieliście pojęcie precedensów i osób na utrzymaniu. Rozważmy teraz arkusz z kilkoma formułami.

Kliknij komórkę w sekcji Kategoria zaliczenia w tabeli Wyniki egzaminów.
Kliknij opcję Śledź poprzedniki. Komórka po jej lewej stronie (Oznaczenia) i zakres E4: F8 zostaną zamapowane jako precedensy.
Powtórz te czynności dla wszystkich komórek w sekcji Kategoria zaliczenia w tabeli Wyniki egzaminu.

Kliknij komórkę w sekcji Kategoria zaliczenia w tabeli Oceny uczniów.
Kliknij opcję Śledź zależności. Wszystkie komórki w sekcji Kategoria zaliczenia w tabeli wyników egzaminów zostaną zamapowane jako zależne.

Pokazywanie formuł

Poniższy arkusz zawiera podsumowanie sprzedaży przez sprzedawców w regionach Wschód, Północ, Południe i Zachód.

Kliknij kartę FORMUŁY na Wstążce.
Kliknij opcję Pokaż formuły w grupie Inspekcja formuł. Pojawią się formuły w arkuszu, dzięki czemu będziesz wiedzieć, które komórki zawierają formuły i jakie są formuły.

Kliknij komórkę pod TotalSales.
Kliknij opcję Śledź poprzedniki. Na końcu strzałki pojawi się ikona arkusza. Ikona arkusza wskazuje, że precedensy znajdują się w innym arkuszu.

Kliknij dwukrotnie strzałkę. ZAGo TO pojawi się okno dialogowe, pokazujące precedensy.

Jak zauważyłeś, istnieją cztery precedensy w czterech różnych arkuszach roboczych.

Kliknij odniesienie do jednego z precedensów.
Odniesienie pojawi się w polu Odniesienie.
Kliknij OK. Pojawi się arkusz roboczy zawierający ten precedens.

Ocena formuły

Aby sprawdzić krok po kroku, jak działa złożona formuła w komórce, możesz użyć polecenia Oblicz formułę.

Rozważ wzór NPV (środkowy rok) w komórce C14. Formuła jest taka

=SQRT (1 + C2)*C10

Kliknij komórkę C14.
Kliknij kartę FORMUŁY na Wstążce.
Kliknij opcję Oceń formułę w grupie Inspekcja formuł. Zostanie wyświetlone okno dialogowe Oceń wzór.

w Evaluate FormulaW oknie dialogowym formuła zostanie wyświetlona w polu w obszarze Ocena. KlikającEvaluateprzycisk kilka razy, formuła zostanie oceniona krokowo. Wyrażenie z podkreśleniem będzie zawsze wykonywane jako następne.

Tutaj C2 jest podkreślone we wzorze. Tak więc jest oceniany w następnym kroku. KliknijEvaluate.

Komórka C2 ma wartość 0,2. W związku z tym C2 zostanie ocenione jako 0,2.1+0.2jest podkreślona, pokazując to jako następny krok. KliknijEvaluate.

1 + 0,2 zostanie ocenione jako 1,2. SQRT(1.2)jest podkreślona, pokazując to jako następny krok. KliknijEvaluate.

SQRT (1.2) zostanie oceniony jako 1.09544511501033. C10jest podkreślona, pokazując to jako następny krok. KliknijEvaluate.

C10 zostanie oszacowany jako 4976,8518518515.

1.09544511501033 * 4976.8518518515 jest podkreślona, co oznacza, że jest to następny krok. KliknijEvaluate.

1,09544511501033 * 4976,8518518515 zostanie oceniony jako 5 451,87.

Nie ma już wyrażeń do oceny i oto jest odpowiedź. PlikEvaluate zostanie zmieniony na Restart przycisk informujący o zakończeniu oceny.

Sprawdzanie błędów

Dobrą praktyką jest sprawdzenie błędów, gdy arkusz i / lub skoroszyt są gotowe z obliczeniami.

Rozważ następujące proste obliczenia.

Obliczenie w komórce spowodowało błąd # DIV / 0 !.

Kliknij komórkę C5.
Kliknij kartę FORMUŁY na Wstążce.
Kliknij strzałkę obok pozycji Sprawdzanie błędów w grupie Inspekcja formuł. Na liście rozwijanej znajdziesz toCircular References jest dezaktywowana, co oznacza, że arkusz nie zawiera odwołań cyklicznych.
Wybierz Trace Error z listy rozwijanej.

Komórki potrzebne do obliczenia aktywnej komórki są oznaczone niebieskimi strzałkami.

Kliknij Usuń strzałki.
Kliknij strzałkę obok pozycji Sprawdzanie błędów.
Wybierz Sprawdzanie błędów z listy rozwijanej.

Plik Error Checking pojawi się okno dialogowe.

Przestrzegaj następujących zasad -

Jeśli klikniesz Help on this error, Zostanie wyświetlona pomoc programu Excel dotycząca błędu.
Jeśli klikniesz Show Calculation Steps, Pojawi się okno dialogowe Oceń wzór.
Jeśli klikniesz Ignore Error, okno dialogowe Sprawdzanie błędów zostanie zamknięte i jeśli klikniesz Error Checking polecenie ponownie, ignoruje ten błąd.
Jeśli klikniesz Edit in Formula Bar, zostaniesz przeniesiony do formuły na pasku formuły, aby móc edytować formułę w komórce.