Jak naprawić błędy #NUM! w programie Microsoft Excel
Radzenie sobie z błędami #NUM w Excelu to prawdziwa udręka, zwłaszcza gdy jesteś pogrążony w obliczeniach i nagle wszystko się zatrzymuje. Błędy te pojawiają się z wielu powodów – nieprawidłowe argumenty, bardzo duże lub małe liczby, niemożliwe działania matematyczne, takie jak pierwiastki kwadratowe z liczb ujemnych, lub formuły iteracyjne, które po prostu nie mogą znaleźć rozwiązania. Wiedza o tym, jak je rozwiązać, może zaoszczędzić Ci frustracji i zapewnić niezawodność arkuszy kalkulacyjnych. Czasami wystarczy po prostu sprawdzić formuły, ale innym razem może być konieczna modyfikacja ustawień lub przetworzenie danych. Ten przewodnik omawia typowe rozwiązania, z których korzystałem, mając nadzieję, że uchroni Cię przed wyrywaniem sobie włosów z głowy, gdy Excel zgłasza błąd #NUM.
Jak naprawić błąd #NUM w programie Excel
Rozwiązanie 1: Sprawdź argumenty formuły i typy danych
Dlaczego to pomaga: Większość błędów #NUM występuje, gdy argumenty wejściowe w formułach są nieprawidłowe lub mają niewłaściwy typ danych. Na przykład, wpisanie tekstu do funkcji liczbowej lub użycie roku spoza dopuszczalnego zakresu może spowodować problemy. Gdy formuły, takie jak funkcje programu Excel, oczekują określonych danych wejściowych, każda niezgodność prowadzi do błędów.
Kiedy ma to zastosowanie: Jeśli formuła nagle zaczyna zwracać #NUM, a masz pewność, że odwołania wyglądają poprawnie, warto sprawdzić te dane wejściowe jeszcze raz.
Czego można się spodziewać: Szybka poprawka w tym przypadku często skutkuje tym, że formuła znów obliczy się poprawnie lub co najmniej wyświetli bardziej zrozumiały komunikat o błędzie, który wskaże właściwy kierunek.
Wskazówka: Na przykład, jeśli używasz tej DATEfunkcji, upewnij się, że rok mieści się między 1 a 9999. Lub, za pomocą DATEDIF, sprawdź, czy data końcowa jest późniejsza niż data początkowa. Ten błąd często wynika z literówki lub błędnego odwołania.
Rozwiązanie 2: Zajmij się zbyt dużymi lub zbyt małymi liczbami
Dlaczego to pomaga: Excel nie obsługuje liczb poza pewnymi limitami – na przykład liczb większych niż około 1, 8E+308 lub mniejszych niż około -1, 8E+308. Przekroczenie tych limitów powoduje błędy #NUM. Czasami formuły zawierające bardzo duże lub małe liczby po prostu nie są przetwarzane w ramach możliwości Excela.
Kiedy ma to zastosowanie: Jeśli Twój wzór obejmuje wykładniki, logarytmy lub duże mnożenia i nagle pojawia się błąd, przyczyną może być to.
Czego się spodziewać: Dostosowanie obliczeń – na przykład poprzez podzielenie ich na mniejsze fragmenty lub użycie komórek pośrednich – może wyeliminować błąd. Na przykład, zamiast obliczać ogromną potęgę w jednej komórce, podziel ją. Pamiętaj też, że w nowszych wersjach programu Excel małe liczby poniżej 2, 2E-308 po prostu stają się zerami, więc możesz zobaczyć nieoczekiwane zera. Dziwne, prawda?
Rozwiązanie 3: Zapobiegaj niemożliwym operacjom
Dlaczego to pomaga: Próba wykonania operacji matematycznych niemożliwych dla Excela, takich jak wyciągnięcie pierwiastka kwadratowego z liczby ujemnej, natychmiast spowoduje błąd #NUM. Oczywiście Excel nie obsługuje skomplikowanych obliczeń matematycznych, chyba że korzysta się z funkcji specjalistycznych.
Kiedy ma to zastosowanie: Jeśli Twoje dane zawierają liczby ujemne w funkcjach, takich jak SQRTlub podnosisz liczby ujemne do potęg ułamkowych, może to spowodować błędy.
Czego się spodziewać: Użycie ABSfunkcji, takiej jak, może rozwiązać ten problem =SQRT(ABS(-25)). Oblicza ona wartość bezwzględną, unikając niemożliwych obliczeń. Jeśli jednak Twój cel obejmuje liczby zespolone, domyślne ustawienia programu Excel nie są w stanie tego obsłużyć i konieczne będzie przejście na specjalistyczne narzędzia.
Poprawka 4: Pomóż formułom iteracyjnym zbiegać się
Dlaczego to pomaga: Niektóre formuły, takie jak IRR, XIRR czy RATE, wykorzystują obliczenia iteracyjne do znalezienia rozwiązania. Jeśli ustawienia iteracji w programie Excel nie są poprawnie skonfigurowane lub nie ma zbieżności — co oznacza, że formuły nie mogą ustalić wartości — pojawią się błędy #NUM.
Kiedy ma to zastosowanie: Jeśli pracujesz z tymi funkcjami i nie dają one żadnych wyników, to prawdopodobnie tu leży problem.
Czego się spodziewać: Dostosowanie ustawień często może rozwiązać problem. Aby to zrobić: Przejdź do Plik > Opcje > Formuły. Zaznacz opcję Włącz obliczenia iteracyjne. Zwiększ maksymalną liczbę iteracji — na przykład ze 100 do 1000 — i zmniejsz maksymalną zmianę, aby zwiększyć precyzję. Następnie kliknij OK. Czasami samo danie Excelowi więcej prób pomaga znaleźć właściwą odpowiedź.
Rozwiązanie 5: Rozwiązywanie problemów z funkcją IRR
Dlaczego to pomaga: IRRFunkcja ma problemy, gdy przepływy pieniężne nie zmieniają znaku lub gdy początkowe oszacowanie jest bardzo dalekie od prawdy. Jeśli wszystkie przepływy pieniężne są dodatnie lub ujemne, funkcja IRR nie może znaleźć rozwiązania i zgłasza błąd #NUM.
Kiedy to ma zastosowanie: Jeśli formuła IRR zwraca #NUM, sprawdź dokładnie znaki przepływów pieniężnych. Pierwszy przepływ pieniężny (Twoja inwestycja) powinien zazwyczaj być ujemny, co oznacza wypływ gotówki. Jeśli przypadkowo wpiszesz same wartości dodatnie, IRR może się zadławić.
Czego się spodziewać: Zmiana przepływów pieniężnych z inwestycji na wartość ujemną (np =IRR(A1:A10, -0.1).) często rozwiązuje problem. Funkcja ma wtedy większą szansę na zbieżność. Zwiększenie liczby iteracji (patrz poprzednie rozwiązanie) może w tym przypadku pomóc, zwłaszcza jeśli dane są złożone lub nieregularne.
Uwaga: pamiętaj, że program Excel może wyświetlać liczby ujemne w nawiasach, ale nie ma to wpływu na formułę — dlatego nie panikuj, jeśli (10, 000)na pasku formuły zobaczysz zamiast -10000.
Z mojego doświadczenia wynika, że tego typu zmiany — korygowanie znaków, zwiększanie limitów iteracji, zapewnianie dobrych początkowych szacunków — często decydują o tym, czy błąd #NUM wystąpi, czy obliczenia zostaną wykonane pomyślnie.
Ostatecznie rozwiązanie błędów #NUM sprowadza się do zrozumienia, co Excel próbuje zrobić i zapewnienia mu tego, czego potrzebuje, aby odnieść sukces – czy to czyste dane, rozsądne dane wejściowe, czy więcej prób obliczeniowych. Oczywiście, Excel czasami musi utrudniać zadanie bardziej, niż jest to konieczne.
Powiązane artykuły
Czy ten artykuł był pomocny?