Jak ukryć # DIV / 0! błąd, gdy komórka, do której istnieje odwołanie, jest pusta?

28

W kolumnie CI mają produkcję. W kolumnie DI masz cel. W kolumnie EI mam wariancję%. Moja formuła to=(D11-C11)/D11

Jednak, jak można ukryć komórek arkusza w dół, dopóki nie umieścić coś w D11& C11się ukryć #DIV/0!. Próbowałem użyć tej IFformuły, ale wydaje się, że to źle?

microsoft-excel worksheet-function

— Jackie Reid
źródło

45

Funkcja IFERROR

Istnieje „specjalny” IFtest przeznaczony do obsługi błędów:

=IFERROR( (D11-C11)/D11, "")

Daje to obliczoną wartość (D11-C11) / D11, chyba że wynikiem jest błąd, w którym to przypadku zwraca wartość pustą.

Wyjaśnienie

Ostatnią wartością parametru „if error” może być cokolwiek; nie ogranicza się do pustych podwójnych cudzysłowów. JEŻELI BŁĄD działa dla każdego warunku, który zwraca wartość błędu (rzeczy zaczynające się od a #), na przykład:

#NULL!  -   reference to an intersection of two ranges that don't intersect
#DIV/0! -   attempt to divide by zero
#VALUE! -   variable is the wrong type 
#REF!   -   invalid cell reference
#NAME?  -   formula name, or text within a formula, isn't recognized
#NUM!   -   invalid number
#N/A    -   value is not available

Jest to przydatne do debugowania; funkcję można tymczasowo owinąć wokół formuły, aby zwrócić tekst wiadomości, gdy formuła generuje błąd. Jest to również usprawniona forma testu IF; nie wymaga dołączenia wyrażenia w celu przetestowania go, a następnie włączenia go ponownie, aby użyć jego wyniku.

Inne arkusze kalkulacyjne

Ta funkcja jest również dostępna dla użytkowników innych programów do obsługi arkuszy kalkulacyjnych. Został on dodany do LibreOffice Calc w wersji 4.0 (jeszcze nie wersja rozproszona w niektórych dystrybucjach Linuksa). Jak zauważa @Kroltan, jest jeszcze bardziej usprawniony w Arkuszach Google, gdzie wartość „jeśli błąd” jest opcjonalna; domyślnie jest puste, jeśli go brakuje. W takim przypadku, w którym chcesz po prostu ukryć potencjalne wartości błędów, Arkusze Google mogą to zrobić IFERROR(expression).

— fixer1234
źródło

Wiem, że dotyczy to programu Excel, ale w Dokumentach Google i Open / LibreOffice istnieje odmiana bez konieczności stosowania „wartości, jeśli prawda”, co upraszcza ją =IFERROR(yourformula).

— Kroltan

1

@Kroltan: Właśnie wtedy, gdy myślałem, że nie mogą już usprawnić tej funkcji. Wygląda na to, że masz rację co do Arkuszy Google. JEŻELI BŁĄD domyślnie jest pusty w przypadku błędu, jeśli nie określono innej wartości, więc jest opcjonalny. LibreOffice Calc zaimplementował funkcję w wersji 4.0. Najnowsza wersja, do której mam dostęp, to wersja 4.2, w związku z czym wartość if-error jest nadal wymagana.

— fixer1234

Rzeczywiście testowałem tylko w arkuszach kalkulacyjnych Google, ale zgodnie z dokumentami jest on identyczny z tym z LibreOffice. Przepraszam za wprowadzanie w błąd.

— Kroltan

11

Wewnątrz IFinstrukcji znajduje się kontrola logiczna (pierwsza część).

IF(logical_test, value_if_true, [value_if_false])

Aby uniknąć błędów spowodowanych, gdy dane produkcyjne i / lub dane celu są puste, użyj funkcji ORwith ISBLANKw ramach kontroli logicznej.

=IF(OR(ISBLANK(C11),ISBLANK(D11)), "", (D11-C11)/D11)

To sprawdza, czy wskazana komórka jest pusta. Jeśli jedno lub oba są puste (test logiczny PRAWDA), przetworzy IF TRUEczęść IFinstrukcji. W takim przypadku ""formuła ta nic nie robi. W przeciwnym razie przetworzy IF FALSEczęść formuły, którą masz.

— CharlieRB
źródło

Zaktualizowałem to, aby obejmowało sprawdzanie zarówno C11& D11jak pierwotnie napisałeś . Przepraszam, że przeoczyłem to w mojej oryginalnej odpowiedzi.

— CharlieRB

Dobry chwyt Jeśli cele zostaną wstępnie wypełnione, a następnie poczekaj na wpisy produkcyjne, wyniki będą wykazywać 100% wariancję, dopóki nie zostaną wprowadzone. Twoje rozwiązanie dba o to, co jest znacznie mniejszą presją, jeśli wydajność jest mierzona na podstawie tego arkusza. :-)

— fixer1234

1

Spróbuj tego:

= JEŻELI (D11 <> 0, (D11-C11) / D11, „”)

lub

= JEŻELI (D11 = 0, „”, (D11-C11) / D11)

— BillDOe
źródło

1

Wybierz cały arkusz kalkulacyjny, a następnie w menu Strona główna - Formatowanie warunkowe - Nowa reguła ... - Wybierz Formatuj tylko komórki zawierające - W obszarze Formatuj tylko komórki z zaznaczonymi błędami - Kliknij przycisk Formatuj przycisk - Przejdź do zakładki Czcionka - w obszarze Wybierz kolor ten sam kolor czcionki co tło (np. biały).

— Vladimir Reshetnikov
źródło