Zrozumienie i naprawienie #NUM błędów w programie Excel to coś więcej niż tylko zadanie rozwiązywania problemów; jest to niezbędna umiejętność dla każdego, kto pracuje z Excelem. Niezależnie od tego, czy przeglądasz dane finansowe, prowadzisz badania naukowe czy zarządzasz zapasami, te błędy formuł mogą ujawnić się i zagrozić Twojej analizie. W tym artykule przeprowadzimy Cię przez różne sposoby naprawienia błędu #NUM.
Zapoznaj się też z naszymi przewodnikami dotyczącymi jak naprawić błędy #N/A w formułach Excela i często spotykanych błąd naruszenia zasad udostępniania.
Co to jest błąd #NUM i dlaczego występuje?
Komunikat o błędzie Excel #NUM jest jednym z najczęstszych błędów. Zostanie uruchomione z następujących powodów:
Napraw błąd #NUM spowodowany nieprawidłowym argumentem funkcji
Najczęstszą przyczyną błędu #NUM jest nieprawidłowy argument lub nieprawidłowe typy danych. Jeśli podejrzewasz, że jest to przyczyną błędu #NUM w Twojej funkcji, sprawdź typy danych i składnię formuły pod kątem błędów.
Jeśli na przykład używasz funkcji DATA, Excel oczekuje, że jako argument roku użyjesz tylko liczb z zakresu od 1 do 9999. Jeśli podasz wartość roku spoza tego zakresu, spowoduje to błąd #NUM.
Podobnie, jeśli używasz funkcji DATEDIF, podana data końcowa musi być późniejsza niż data początkowa. Można także mieć wpisy z jednakową datą. Jeśli jednak jest odwrotnie, wynikiem będzie błąd #NUM.
Zobaczmy to na przykładzie:
=DATEDIF(A2,B2,”d”) oblicza różnicę w liczbie dni pomiędzy dwiema datami (w komórkach A2 i B2). Wynik będzie liczbowy, jeśli data w komórce A2 będzie mniejsza niż data w komórce B2. Jeśli tak nie jest, jak w naszym przykładzie poniżej, wynikiem będzie błąd #NUM..
Napraw błąd #NUM spowodowany zbyt dużymi lub zbyt małymi liczbami
Jeśli formuła zawiera argument przekraczający limit liczbowy programu Excel, spowoduje to błąd #NUM. Tak, program Microsoft Excel ma ograniczenie rozmiaru liczb, które może obliczyć. Aby sobie z tym poradzić, musisz dostosować wartość wejściową, tak aby wynik mieścił się w dozwolonym zakresie.
Jeśli musisz pracować z tak dużymi liczbami, rozważ podzielenie obliczeń na mniejsze części. Umożliwi to użycie wielu komórek, aby osiągnąć końcowy wynik.
Oto limity obliczeń w programie Microsoft Excel:
Jeśli wynik używanej formuły wykracza poza te zakresy, otrzymany wynik będzie błędem #NUM.
Popatrzmy na przykład.
Uwaga: w nowym Wersje Microsoft Excela tylko duże liczby spowodują błąd #NUM. Liczby, które są zbyt małe, dadzą wynik 0 (format ogólny) lub 0,00E+00 (format naukowy).
Jeśli nie znasz formatu naukowego, pamiętaj, że na przykład część „E-20” tłumaczy się jako „razy 10 do potęgi -20”.
Napraw błąd #NUM spowodowany niemożliwymi obliczeniami
Innym powodem wystąpienia błędu #NUM jest sytuacja, gdy Excel uzna obliczenia za niemożliwe. W takim przypadku musisz zidentyfikować funkcję lub część funkcji powodującą problem i odpowiednio dostosować formułę lub dane wejściowe.
Najbardziej typowym przykładem niemożliwego obliczenia jest próba znalezienia pierwiastka kwadratowego z ujemnej wartości liczbowej. Zobaczmy to na przykładzie z funkcją SQRT:
Jeśli użyjesz =SQRT(25), wynikiem będzie 5.
Jeśli użyjesz =SQRT(-25), wynikiem będzie #NUM.
Możesz rozwiązać ten problem, jeśli zastosujesz funkcję ABS, a otrzymasz wartość bezwzględną tej liczby.
=KR(ABS(-25))
Lub
=SQERT(ABS(odniesienie_komórki))
Podobnie Excel nie obsługuje liczb zespolonych, więc wszelkie obliczenia, które wykonasz w wyniku liczby zespolonej, spowodują błąd #NUM. W takim przypadku obliczenia nie da się wykonać w ramach ograniczeń programu Excel..
Przykładem może być próba podniesienia liczby ujemnej do potęgi niecałkowitej.
Napraw błąd #NUM, gdy formuła iteracji nie może się zbiegać
Może pojawić się błąd #NUM, ponieważ formuła, której używasz, nie może znaleźć wyniku. W takim przypadku musisz zmodyfikować wartości wejściowe i pomóc formule w wykonaniu obliczeń.
Iteracja to funkcja programu Excel umożliwiająca wielokrotne obliczanie formuł do momentu spełnienia odpowiednich warunków. Oznacza to, że formuła będzie próbowała znaleźć wynik metodą prób i błędów. Istnieje kilka funkcji programu Excel korzystających z funkcji iteracji: IRR, XIRR lub RATE. Formuła iteracyjna, która nie może znaleźć prawidłowego wyniku w ramach podanych parametrów, zwróci błąd #NUM.
Na przykład:
Wspomóż swoją formułę, podając wstępne przypuszczenie:
Być może konieczne będzie dostosowanie ustawień iteracji w programie Excel, aby formuła była zbieżna. Oto jak to zrobić:
Napraw # NUM Błąd w funkcji IRR programu Excel
Jeśli występuje problem braku zbieżności, formuła IRR nie znajdzie rozwiązania. Wynikiem będzie błąd #NUM. Aby sobie z tym poradzić, musisz dostosować ustawienia iteracji programu Excel i podać wstępne szacunki.
Podobnie jak w przypadku innych funkcji iteracji, funkcja IRR może spowodować błąd #NUM, ponieważ formuła nie może znaleźć wyniku po określonej liczbie iteracji. Możesz łatwo rozwiązać ten problem, jeśli zwiększysz liczbę iteracji i podasz wstępne przypuszczenie. Przykłady znajdziesz w poprzedniej sekcji..
Jeśli masz do czynienia z niespójnymi znakami, jak w naszym przykładzie poniżej, pamiętaj o wprowadzeniu wszystkich początkowych wychodzących przepływów pieniężnych jako liczby ujemne. Funkcja zakłada, że istnieją zarówno dodatnie, jak i ujemne przepływy pieniężne.
Na przykład, jeśli Twoja inwestycja jest ustawiona na 10 000 USD, funkcja IRR zwróci błąd #NUM.
Jeśli zmienisz Inwestycję na -10 000 USD, jako wartość ujemną w następujący sposób:
Funkcja IRR będzie działać.
Pamiętaj, że nowoczesne wersje programu Microsoft Excel zamienią Twoje ujemne dane wejściowe na nawiasy (nasze -10 000 USD zostało zamienione na (10 000 USD)), więc nie martw się, jeśli zobaczysz, że tak się dzieje. Formuła będzie nadal działać.
Postępując zgodnie z krokami i najlepszymi praktykami opisanymi w tym artykule, jesteś teraz przygotowany, aby stawić czoła nieznośnym #NUM! problemy, zapewniając, że arkusze kalkulacyjne Excel pozostaną niezawodne i wolne od błędów. Udanego tworzenia arkusza kalkulacyjnego!
.