David H. Ringstrom, CPA
Od czasu do czasu można stworzyć formułę, która w niektórych przypadkach zwróci błąd, taki jak #DIV/0! lub #N/A. Wielu użytkowników radzi sobie z tą sytuacją, stosując kombinacje funkcji arkusza JEŻELI, ISERROR i ISNA. Jednak wspólne użycie tych funkcji może czasem prowadzić do niepotrzebnie skomplikowanych formuł. W tym artykule omówię alternatywę dostępną dla każdego użytkownika programu Excel 2007 lub nowszego.
Zacznijmy od typowego scenariusza, w którym obliczenie zmiany procentowej między dwiema liczbami skutkuje błędem #DIV/0!, jak pokazano na rysunku 1. Obecnie formuła w komórce D2 ma następującą postać:
=(B2-C2)/B2
Rysunek 1: Excel zwraca błąd #DIV/0! przy próbie dzielenia przez zero.
Jak widać, po skopiowaniu tej formuły w dół kolumny komórka D4 zwraca #DIV/0! ponieważ komórka B4 jest równa zero. Aby wyeliminować błąd #DIV/0!, możemy zmienić formułę tak, aby miała następującą postać:
=IF(ISERROR((B2-C2)/B2)",-",(B2-C2)/B2)
Jak widać na rysunku 2, formuła w komórce D4 zwraca teraz myślnik zamiast #DIV/0!
Rysunek 2: Połączenie funkcji JEŻELI i ISERROR umożliwia wyświetlenie alternatywnych wyników, gdy obliczenia zakończą się błędem.
Dla niewtajemniczonych funkcja JEŻELI ma trzy argumenty lub wejścia, którymi są:
- logiczny_test - w tym przypadku funkcja ISERROR przeprowadza test i zwraca TRUE lub FALSE.
- wartość_jeśli_true - Jeśli funkcja ISERROR zwróci TRUE, Excel ma zwrócić myślnik, który należy ująć w cudzysłów. Cudzysłów jest wymagany tylko wtedy, gdy instrukcja JEŻELI ma zwracać tekst, dlatego nie ma potrzeby ujmowania w cudzysłów wartości liczbowych, takich jak zero.
- wartość_jeżeli_fałsz - Jeżeli instrukcja ISERROR zwróci wartość FAŁSZ, to Excel ma wykonać nasze pierwotne obliczenia.
Chociaż jest to słuszne podejście, wprowadza ono do formuły złożoność, ponieważ musimy dwukrotnie powtórzyć nasze pierwotne obliczenia. Jednak, jak pokazano na rysunku 3, jeśli nasz arkusz będzie używany tylko w programie Excel 2007 lub nowszym, możemy uprościć formułę do następującej postaci:
=IFERROR((B2-C2)/B2",-")
Rysunek 3: Funkcja IFERROR usprawnia złożone formuły wyłapujące błędy.
Funkcja IFERROR, wprowadzona po raz pierwszy w Excelu 2007, ma dwa argumenty:
- wartość - Jest to obliczenie, które chcemy sprawdzić pod kątem błędów.
- wartość_jeśli_błąd - jest to wynik, który chcemy wyświetlić, jeśli nasze obliczenia zakończą się błędem.
Jak widać, IFERROR eliminuje konieczność powtarzania jakiejkolwiek części obliczeń. Nie jest on jednak kompatybilny wstecz z wcześniejszymi wersjami Excela. Jak pokazano na rysunku 4, funkcja IFERROR zwróci wartość #NAME? w programie Excel 2003 i wcześniejszych. Jeśli wymagana jest zgodność z wcześniejszymi wersjami programu Excel, należy użyć wspomnianej wcześniej metody IF/ISERROR.
Rysunek 4: Funkcja IFERROR jest niekompatybilna z programem Excel 2003 i wcześniejszymi, ale może być używana w programie Excel 2007 i późniejszych.
Ponadto wadą funkcji IFERROR i ISERROR jest to, że maskują one błędy związane ze znakiem #, które mogą wystąpić w formule. Może się zdarzyć, że użytkownik będzie chciał zamaskować błąd #N/A, ale zostanie powiadomiony o innych błędach, takich jak #DIV/0!, #REF! lub #NAME? Jak pokazano na rysunku 5, można utworzyć formułę, która będzie wychwytywać tylko błędy #N/A, ale wyświetlać inne błędy #znaków, takie jak #DIV/0!
=IF(ISNA(VLOOKUP(A8,$A$1:$D$5,4,0)),0,VLOOKUP(A8,$A$1:$D$5,4,0))
Rysunek 5: ISNA testuje szczególnie błędy typu #N/A, ale pozwala na wyświetlenie innych błędów.
Uważni czytelnicy zauważą, że użyłem zera zamiast typowego FALSE w VLOOKUP, aby wskazać dokładne dopasowanie. Jeśli nie znasz VLOOKUP, jest to funkcja, która służy do zwracania wartości z listy, która ma cztery wejścia:
- lookup_value - reprezentuje element, którego szukasz na liście. Na przykład w powyższym wzorze A8 oznacza, że chcemy znaleźć słowo "Jabłka".
- table_array - reprezentuje współrzędne komórek listy, którą chcesz przeszukać. W powyższej formule nasza lista składa się z komórek od A1 do D5. VLOOKUP wyszukuje wartość lookup_value w pierwszej kolumnie table_array.
- col_index_num - Ten argument mówi VLOOKUP, z której kolumny ma zwrócić wartość, gdy w pierwszej kolumnie zostanie znalezione dopasowanie. W tym przypadku chcemy zwrócić wartość % zmiany z czwartej kolumny naszej tablicy.
- range_lookup - to kryptyczne ustawienie określa, czy chcemy uzyskać dokładne czy przybliżone dopasowanie. W tym przypadku chcemy uzyskać dokładne dopasowanie do słowa "Jabłka", więc w tym ostatnim argumencie należy wpisać słowo FALSE lub zero. Pomiń ten argument lub użyj słowa TRUE lub jedynki w tej pozycji, aby określić dopasowanie przybliżone, np. w przypadku szukania przedziału podatkowego na podstawie dochodu brutto.
Przeczytaj więcej artykułów Davida Ringstroma.
O autorze:
David H. Ringstrom, CPA kieruje firmą Accounting Advisors, Inc. z siedzibą w Atlancie, zajmującą się doradztwem w zakresie oprogramowania i baz danych, świadczącą usługi szkoleniowe i konsultingowe na terenie całego kraju. Skontaktuj się z Davidem pod adresem [email protected] lub śledź go na Twitterze. David jest prelegentem na konferencjach poświęconych Microsoft Excel, a także prowadzi webcasty dla kilku dostawców usług CPE, w tym dla partnera CPE Link w AccountingWEB.
