Microsoft Excel to potężna i wszechstronna aplikacja do obsługi arkuszy kalkulacyjnych, która doskonale nadaje się do śledzenia i zarządzania wszystkim, od zasobów firmowych, przez budżety małych firm, aż po osobistą kondycję. Jedną z zalet programu Excel jest to, że możesz wcześniej skonfigurować formuły, które będą automatycznie aktualizowane po wprowadzeniu nowych danych. Niestety niektóre formuły są matematycznie niemożliwe bez wymaganych danych, co powoduje błędy w tabeli, takie jak # DIV / 0 !, #VALUE !, #REF !, i #NAME ?. Błędy te niekoniecznie są szkodliwe, ale będą wyświetlane w arkuszu kalkulacyjnym do czasu ich poprawienia lub wprowadzenia wymaganych danych, co może sprawić, że ogólna tabela będzie mniej atrakcyjna i trudniejsza do zrozumienia. Na szczęście, przynajmniej w przypadku brakujących danych, możesz ukryć błędy Excela przy pomocy niektórych funkcji IF i ISERROR. Oto jak to zrobić.
Używamy małego arkusza kalkulacyjnego śledzenia utraty wagi jako przykładu tabeli, która spowodowałaby błąd obliczeniowy (obliczenie procentu utraty masy) podczas oczekiwania na nowe dane (kolejne ważenia).
Nasz przykładowy arkusz kalkulacyjny czeka na dane wejściowe w kolumnie Waga, a następnie automatycznie aktualizuje wszystkie inne kolumny na podstawie nowych danych. Problem polega na tym, że kolumna Procent utraconych danych opiera się na wartości Zmiana, która nie została zaktualizowana w tygodniach, w których waga nie została jeszcze wprowadzona, co daje # DIV / 0! błąd, który wskazuje, że formuła próbuje podzielić przez zero. Ten błąd możemy rozwiązać na trzy sposoby:
- Możemy usunąć formułę z tygodni, w których nie wprowadzono wagi, a następnie ręcznie dodać ją z powrotem w każdym tygodniu. To działałoby w naszym przykładzie, ponieważ arkusz kalkulacyjny jest stosunkowo mały, ale nie byłby idealny w większych i bardziej skomplikowanych arkuszach kalkulacyjnych.
- Możemy obliczyć procent utraconych przy użyciu innej formuły, która nie dzieli się przez zero. Ponownie jest to możliwe w naszym przykładzie, ale nie zawsze może być zależne od arkusza kalkulacyjnego i zestawu danych.
- Możemy użyć funkcji ISERROR, która w połączeniu z instrukcją IF pozwala nam zdefiniować alternatywną wartość lub obliczenie, jeśli wynik początkowy zwróci błąd. To rozwiązanie pokażemy Ci dzisiaj.
Funkcja ISERROR
ISERROR sam testuje wyznaczoną komórkę lub formułę i zwraca „prawda”, jeśli wynikiem obliczenia lub wartości komórki jest błąd, a „fałsz”, jeśli nie jest. Aby użyć ISERROR, wystarczy wprowadzić obliczenia lub komórkę w nawiasach następujących po funkcji. Na przykład:
ISERROR ((B5-B4) / C5)
Jeśli obliczenie (B5-B4) / C5 zwróci błąd, wówczas ISERROR zwróci „prawda” po sparowaniu ze wzorem warunkowym. Chociaż można to wykorzystać na wiele różnych sposobów, jego prawdopodobnie najbardziej użyteczna rola występuje w połączeniu z funkcją IF.
Funkcja IF
Funkcja JEŻELI używana jest przez umieszczenie trzech testów lub wartości w nawiasach oddzielonych przecinkami: JEŻELI (wartość do przetestowania, wartość jeśli prawda, wartość jeśli fałsz). Na przykład:
JEŻELI (B5> 100, 0, B5)
W powyższym przykładzie, jeśli wartość w komórce B5 jest większa niż 100 (co oznacza, że test jest prawdziwy), wówczas zero zostanie pokazane jako wartość komórki. Ale jeśli B5 jest mniejsze lub równe 100 (co oznacza, że test jest fałszywy), zostanie wyświetlona rzeczywista wartość B5.
IF i ISERROR w połączeniu
Sposób, w jaki łączymy funkcje IF i ISERROR, polega na użyciu ISERROR jako testu instrukcji IF. Jako przykład przyjrzyjmy się naszemu arkuszowi kalkulacyjnemu odchudzania. Powód, dla którego komórka E6 zwraca # DIV / 0! błąd wynika z tego, że jego formuła próbuje podzielić całkowitą wagę utraconą przez wagę z poprzedniego tygodnia, która nie jest jeszcze dostępna dla wszystkich tygodni i która skutecznie działa jak próba podzielenia przez zero.
Ale jeśli użyjemy kombinacji IF i ISERROR, możemy powiedzieć Excelowi, aby zignorował błędy i po prostu wprowadził 0% (lub dowolną żądaną wartość), lub po prostu ukończył obliczenia, jeśli nie ma żadnych błędów. W naszym przykładzie można to osiągnąć za pomocą następującej formuły:
JEŻELI (CZY.BŁĄD (D6 / B5), 0, (D6 / D5))
Dzięki tej funkcji możesz skopiować ją do pozostałych komórek, a wszelkie błędy zostaną zastąpione zerami. Jednak w miarę wprowadzania nowych danych w przyszłości zmienione komórki zostaną automatycznie zaktualizowane do poprawnych wartości, ponieważ warunek błędu nie będzie już spełniony.
Pamiętaj, że próbując ukryć błędy programu Excel, możesz użyć dowolnej wartości lub formuły dla wszystkich trzech zmiennych w instrukcji IF; nie musi to być zero ani liczba całkowita, jak w naszym przykładzie. Alternatywne rozwiązania obejmują odwołanie się do zupełnie oddzielnej formuły lub wstawienie spacji przy użyciu dwóch znaków cudzysłowu („”) jako „prawdziwej” wartości. Aby to zilustrować, następująca formuła wyświetli puste miejsce w przypadku błędu zamiast zera:
JEŻELI (CZY.BŁĄD (D6 / B5), „”, (D6 / D5))
Pamiętaj tylko, że instrukcje JEŻELI mogą szybko stać się długie i skomplikowane, szczególnie w połączeniu z ISERROR, i w takich sytuacjach łatwo jest zgubić nawiasy lub przecinek. Najnowsze wersje formuł kodów kolorów Excel po ich wprowadzeniu w celu śledzenia wartości komórek i nawiasów.
