Narzędzia do inspekcji formuł w programie Excel
Jak wszyscy wiemy, MS Excel jest używany i popularny głównie ze względu na jego funkcje, formuły i makra. Ale co, jeśli napotykamy jakiś problem podczas pisania formuły lub nie jesteśmy w stanie uzyskać pożądanego wyniku w komórce, ponieważ nie sformułowaliśmy poprawnie funkcji. Właśnie dlatego MS Excel zapewnia wiele wbudowanych narzędzi do kontroli formuł i rozwiązywania problemów z formułami.
Narzędzia, których możemy użyć do audytu i rozwiązywania problemów z formułami w programie Excel to:
- Śledzenie precedensów
- Zależne od śledzenia
- Usuń strzałki
- Pokaż formuły
- Sprawdzanie błędów
- Oceń formułę
Przykłady narzędzi audytowych w programie Excel
Dowiemy się o każdym z powyższych narzędzi audytowych po kolei na kilku przykładach w programie Excel.
Możesz pobrać ten szablon narzędzia audytorskiego programu Excel tutaj - szablon programu narzędzi audytowych programu Excel# 1 - Śledzenie precedensów
Załóżmy, że w komórce D2 mamy następującą formułę do obliczania odsetek dla konta FD w banku.
Teraz, jeśli chcemy sprawdzić precedensy dla formuły, możemy nacisnąć F2, aby przejść do trybu edycji po wybraniu wymaganej komórki, tak aby komórki poprzedzające zostały otoczone różnymi kolorami i w tym samym kolorze, zapisane jest odwołanie do komórki.
Widzimy, że komórka A2 jest zapisana w komórce formuły kolorem niebieskim, a komórka A2 jest obramowana tym samym kolorem.
W ten sam sposób,
Komórka B2 ma kolor czerwony.
Komórka C2 ma kolor fioletowy.
Ten sposób jest dobry, ale mamy wygodniejszy sposób sprawdzania precedensów dla komórki formuły.
Aby śledzić precedensy, możemy użyć polecenia „Trace Precedents” w grupie „Formula Auditing” na karcie „Formulas” .
Musimy tylko zaznaczyć komórkę formuły, a następnie kliknąć polecenie „Trace Precedents” . Następnie możesz zobaczyć strzałkę, jak pokazano poniżej.
Widzimy, że poprzednie komórki są wyróżnione niebieskimi kropkami.
# 2 - Usuń strzały
Aby usunąć te strzały, możemy użyć „Usuń strzałki” polecenie w „” Formula Rewizyjnej grupy w ramach „formuły” karcie.
# 3 - Śledzenie zależności
To polecenie służy do śledzenia komórki zależnej od wybranej komórki.
Użyjmy tego polecenia na przykładzie.
Załóżmy, że mamy 4 kwoty, które możemy zainwestować. Chcemy wiedzieć, ile odsetek możemy zarobić, jeśli zainwestujemy.
Widzimy, że na powyższym obrazku zastosowaliśmy formułę do obliczania odsetek z kwotą 1 i określonym procentem procentowym i czasem trwania w roku.
Skopiujemy formułę i wkleimy ją w sąsiednich komórkach dla kwoty 2, kwoty 3 i kwoty 4. Można zauważyć, że użyliśmy bezwzględnego odwołania do komórki dla komórek G2 i I2, ponieważ nie chcemy zmieniać tych odwołań podczas kopiowanie i wklejanie.
Teraz, jeśli chcemy sprawdzić, które komórki są zależne od komórki G2. Następnie użyjemy 'ślad utrzymaniu polecenia dostępne w „” Formula Rewizyjnej grupy pod „” formuły karcie.
Wybierz komórkę G2 i kliknij polecenie „Śledź zależności” .
Na powyższym obrazku widzimy linie strzałek, w których strzałki wskazują, które komórki są zależne od komórek.
Teraz usuniemy linie strzałek za pomocą polecenia „Usuń strzałki” .
# 4 - Pokaż formuły
Możemy użyć tego polecenia, aby wyświetlić formuły zapisane w arkuszu Excela. Skrót klawiszowy dla tego polecenia to „Ctrl + ~” .
Zobacz poniższy obraz, na którym możemy zobaczyć formuły w komórce.
Widzimy, że zamiast wyników formuły, możemy zobaczyć formułę. W przypadku kwot format waluty nie jest widoczny.
Aby dezaktywować ten tryb, naciśnij ponownie 'Ctrl + ~' lub możemy kliknąć polecenie 'Pokaż formuły' .
# 5 - Sprawdzanie błędów
To polecenie służy do sprawdzania błędu w określonej formule lub funkcji.
Weźmy przykład, aby to zrozumieć.
Zobacz poniższy obrazek, na którym mamy błąd w funkcji zastosowanej do wyniku.
Teraz, aby rozwiązać ten błąd, użyjemy polecenia „Sprawdzanie błędów” .
Kroki byłyby następujące:
Wybierz komórkę, w której zapisana jest formuła lub funkcja, a następnie kliknij „Sprawdzanie błędów”.
Po kliknięciu polecenia otrzymujemy następujące okno dialogowe z podpisem „Sprawdzanie błędów” .
W powyższym oknie dialogowym widać, że wystąpił błąd nieprawidłowej nazwy. Formuła zawiera nierozpoznany tekst.
Jeśli korzystamy z funkcji lub konstruujemy formułę po raz pierwszy, możemy kliknąć przycisk ` ` Pomoc dla tego błędu '' , który otworzy stronę pomocy dla funkcji w przeglądarce, na której możemy zobaczyć wszystkie powiązane informacje online i zrozumieć przyczynę i znajdź wszystkie możliwe rozwiązania.
Klikając teraz ten przycisk, znajdziemy następującą stronę.
Na tej stronie dowiadujemy się o błędzie, kiedy ten błąd występuje
- Formuła odnosi się do nazwy, która nie została zdefiniowana. Oznacza to, że nazwa funkcji lub nazwany zakres nie zostały wcześniej zdefiniowane.
- Formuła zawiera literówkę w zdefiniowanej nazwie. Oznacza to, że wystąpił błąd podczas pisania.
Jeśli korzystaliśmy z tej funkcji wcześniej i wiemy o niej, możemy kliknąć przycisk „Pokaż kroki obliczeniowe” , aby sprawdzić, w jaki sposób ocena funkcji powoduje błąd.
Jeśli klikniemy ten przycisk, zostaną wyświetlone następujące kroki:
- Po kliknięciu przycisku „Pokaż kroki obliczeń” pojawia się następujące okno dialogowe .
- Po kliknięciu przycisku „Oceń” podkreślone wyrażenie, tj. „IIF”, zostanie ocenione i poda następujące informacje, tak jak są wyświetlane w oknie dialogowym.
Jak widać na powyższym obrazku, wyrażenie „IIF” zostało ocenione jako błąd, czyli „#NAZWA?”. Teraz kolejne wyrażenie lub odniesienie, tj. B2, zostało podkreślone. Jeśli klikniemy przycisk „Step In” , możemy również sprawdzić wewnętrzne szczegóły kroku i wyjść, naciskając przycisk „Step Out” .
- Teraz klikniemy przycisk „Oceń” , aby sprawdzić wynik podkreślonego wyrażenia. Po kliknięciu otrzymujemy następujący wynik.
- Po kliknięciu przycisku „Oceń” otrzymujemy wynik zastosowanej funkcji.
- W rezultacie otrzymaliśmy błąd i kiedy analizowaliśmy funkcję krok po kroku, dowiedzieliśmy się, że jest jakiś błąd w 'IIF'. W tym celu możemy użyć polecenia 'Wstaw funkcję' w grupie 'Biblioteka funkcji' w ' Karta Formuły.
Gdy wpisaliśmy „if” , otrzymaliśmy podobną funkcję na liście, musimy wybrać odpowiednią funkcję.
Po wybraniu funkcji 'If' otrzymujemy następujące okno dialogowe z polami tekstowymi dla argumentu i wypełnimy wszystkie szczegóły.
Po kliknięciu „Ok” otrzymujemy wynik w komórce. Przepiszemy funkcję dla wszystkich uczniów.
Rzeczy do zapamiętania
- Jeśli aktywujemy polecenie „Pokaż formuły”, daty są również wyświetlane w formacie liczbowym.
- Oceniając formułę, możemy również użyć F9 jako skrótu w programie Excel.
