„Błędy są okazją do poprawy”. W każdym zadaniu lub systemie występują błędy. Excel nie jest wyjątkiem. Próbując zrobić coś z formułą, napotkasz różnego rodzaju błędy Excela. Błędy te mogą sprawić, że formuła / pulpit nawigacyjny / raporty będą absolutnym marnotrawstwem. A jeśli nie wiesz, dlaczego wystąpił określony rodzaj błędu, być może będziesz musiał poświęcić wiele godzin na ich rozwiązanie.
Podczas pracy nad Excelem napotkałem wiele błędów Excela. Z pewnymi problemami i wyszukiwaniami w Google udało mi się obejść te błędy. W tym artykule wyjaśnię niektóre typowe i irytujące błędy programu Excel, które występują w programie Excel. Omówimy, dlaczego te błędy występują i jak je rozwiązać.
Co to są błędy formuł programu Excel
Podczas stosowania formuły, która powoduje błędy zdefiniowane w programie Excel (#NA, #WARTOŚĆ, #NAZWA itp.) Są nazywane błędami formuły programu Excel. Błędy te są wyłapywane przez program Excel i drukowane na arkuszach. Przyczyną tych błędów mogą być niedostępne wartości, niepoprawny typ argumentów, dzielenie przez 0 itp. Są łatwe do wychwycenia i naprawienia.
Błędy logiczne nie są wychwytywane przez program Excel i są najtrudniejsze do naprawienia. Niespójność danych, błędne wprowadzanie danych, błędy ludzkie itp. są częstymi przyczynami tych błędów. Można je również naprawić, ale wymagają czasu i wysiłku. Lepiej dobrze przygotować swoje dane przed wykonaniem na nich operacji.
Wyłapywanie błędów formuły Excela:
Istnieje kilka dedykowanych funkcji w programie Excel do przechwytywania i obsługi określonego typu błędów (takich jak funkcja ISNA). Ale funkcja CZY.BŁĄD i funkcja CZY.BŁĄD to dwie funkcje, które mogą przechwycić dowolny rodzaj błędu programu Excel (z wyjątkiem logicznego).
Błąd Excela #NA
Błąd #NA występuje w programie Excel, gdy wartość nie zostanie znaleziona. Oznacza to po prostu NIEDOSTĘPNE. Błąd #NA jest często spotykany w funkcji WYSZUKAJ.PIONOWO programu Excel.
Na powyższym obrazku otrzymujemy błąd #NA, gdy szukamy "Divya" w kolumnie A. Dzieje się tak, ponieważ „Divya nie znajduje się na liście.
Rozwiązywanie błędu #NA:
Jeśli masz pewność, że wartość wyszukiwania musi istnieć na liście wyszukiwania, pierwszą rzeczą, którą powinieneś zrobić, jest sprawdzenie wartości wyszukiwania. Sprawdź, czy odnośnik do wartości jest poprawnie napisany. Jeśli nie, popraw to.
Po drugie, możesz wykonać częściowe dopasowanie za pomocą funkcji WYSZUKAJ.PIONOWO lub dowolnej funkcji wyszukiwania. Jeśli masz pewność, że jakaś część tekstu musi się zgadzać, użyj tego.
Jeśli nie masz pewności, czy wartość istnieje, czy nie, można jej użyć do sprawdzenia, czy wartość istnieje na liście, czy nie. Na powyższym obrazku możemy powiedzieć, że Divya nie ma na liście.
Jeśli chcesz złapać błąd #NA i wydrukować lub zrobić coś innego zamiast drukowania błędu #NA, możesz użyć funkcji ISNA programu Excel. Funkcja ISNA zwraca TRUE, jeśli funkcja zwraca błąd #NA. Korzystając z tego, możemy uniknąć błędu #NA. ISNA doskonale współpracuje z funkcją WYSZUKAJ.PIONOWO. Sprawdź to tutaj.
Błąd Excela #VALUE
#VALUE występuje, gdy podany argument nie jest obsługiwanego typu. Na przykład, jeśli spróbujesz dodać dwa teksty za pomocą operatora arytmetycznego plus (+), otrzymasz błąd #VALUE. To samo stanie się, jeśli spróbujesz uzyskać rok z nieprawidłowego formatu daty za pomocą funkcji ROK.
Jak naprawić błąd #VALUE?
Najpierw potwierdź typ danych, do którego się odwołujesz. Jeśli twoja funkcja wymaga numeru, upewnij się, że odwołujesz się do numeru. Jeśli liczba jest sformatowana jako tekst, użyj funkcji WARTOŚĆ, aby przekonwertować ją na liczbę. Jeśli funkcja wymaga tekstu (np. funkcja DATA.WARTOŚĆ) i odwołujesz się do liczby lub typu daty, przekonwertuj je na tekst.
Jeśli spodziewasz się, że może wystąpić błąd #VALUE i chcesz je złapać, możesz użyć ISERR, ISERROR lub IFERROR, aby złapać i zrobić coś innego.
Excel #REF Błąd
„ref” w #REF oznacza odniesienie. Ten błąd występuje, gdy formuła odwołuje się do lokalizacji, która nie istnieje. Dzieje się tak, gdy usuwamy komórki z zakresów, do których również odnosi się formuła.
W poniższym gifie formuła sumy odnosi się do A2 i B2. Kiedy usuwam A2, formuła zmienia się w błąd #REF.
Rozwiąż błąd #REF programu Excel:
Najlepiej zachować ostrożność przed usunięciem komórek w danych. Upewnij się, że żadna formuła nie odwołuje się do tej komórki.
Jeśli masz już błąd #REF, prześledź go, a następnie usuń go z formuły.
Na przykład, gdy pojawi się błąd #REF, Twoja formuła będzie wyglądać tak.
| =A2+#REF! |
Możesz po prostu usunąć #REF! Z formuły, aby uzyskać formułę bezbłędną. Jeśli chcesz zrobić to zbiorczo, użyj funkcji Znajdź i zamień. Naciśnij klawisze CTRL+H, aby otworzyć funkcję Znajdź i zamień. W polu Znajdź wpisz #REF. Pozostaw puste pole wymiany. Naciśnij przycisk Zamień wszystko.
Jeśli chcesz ponownie dostosować odwołanie do nowej komórki, zrób to ręcznie i zastąp #REF! Z tym ważnym odniesieniem.
Błąd programu Excel #NAZWA
#NAME występuje w programie Excel, gdy nie może zidentyfikować tekstu w formule. Na przykład, jeśli błędnie wpiszesz nazwę funkcji, program Excel wyświetli błąd #NAZWA. Jeśli formuła odwołuje się do nazwy, która nie istnieje w arkuszu, wyświetli błąd #NAZWA.
Na powyższym obrazku komórka B2 ma formułę =POWERS(A2,2). POWERS nie jest prawidłową funkcją w programie Excel, dlatego zwraca błąd #NAZWA.
W komórce B3 mamy =SUMA(liczby). SUMA jest prawidłową funkcją programu Excel, ale nazwany zakres „liczby” nie istnieje w arkuszu. Dlatego program Excel zwraca #NAZWA? Błąd.
Jak uniknąć błędu #NAME w programie Excel?
Aby uniknąć błędu #NAZWA w programie Excel, zawsze poprawnie przeliteruj nazwy funkcji. Możesz użyć sugestii programu Excel, aby mieć pewność, że używasz prawidłowej funkcji. Ilekroć wpisujemy znaki po znaku równości, Excel pokazuje funkcje i nazwane zakresy na arkuszu, zaczynając od tego znaku/ów. Przewiń w dół do nazwy funkcji lub nazwy zakresu na liście sugestii, naciśnij klawisz Tab, aby użyć tej funkcji.
Excel #DIV/0! Błąd
Jak sama nazwa wskazuje, błąd ten pojawia się, gdy ze wzoru wynika dzielenie przez zero. Ten błąd może również wystąpić, gdy usuniesz jakąś wartość z komórki, od której zależy formuła podziału.
| =JEŻELI(B2=0,A2,A2/B2) |
Pojawi się błąd DIV/0 tylko wtedy, gdy dzielnik będzie równy 0 lub pusty. Dlatego sprawdzamy dzielnik (B2), jeśli wynosi zero, to wypisujemy A2, w przeciwnym razie dzielimy A2 przez B2. Będzie to działać również w przypadku pustych komórek.
Błąd programu Excel #NUM
Ten błąd występuje, gdy nie można wyświetlić liczby na ekranie. Przyczyną może być to, że liczba jest za mała lub za duża do wyświetlenia. Innym powodem może być to, że obliczenia nie można wykonać z podaną liczbą.
Na powyższym obrazku w komórce C2 próbujemy uzyskać wartość -16309. Wartość jest tak mała, że nie można jej wyświetlić.
W komórce C3 próbujemy uzyskać pierwiastek kwadratowy z wartości -16. Ponieważ nie ma takiej wartości, jak pierwiastek kwadratowy z wartości ujemnej (z wyjątkiem liczb urojonych), dlatego program Excel pokazuje #LICZBA! Błąd.
Jak rozwiązać #NUM! Błąd?
Aby rozwiązać błąd #NUM, pierwszą rzeczą, którą możesz zrobić, jest sprawdzenie każdej wartości, do której się odnosisz. Sprawdź, czy są to prawidłowe liczby, z którymi może działać Twoja formuła.
Użyj funkcji licznika, aby rozwiązać format liczb. Na przykład w powyższym przykładzie, jeśli chcesz uzyskać pierwiastek kwadratowy z -16, ale nie chcesz zmieniać wartości w komórce, możemy użyć funkcji ABS.
| =PIERWIASTEK(ABS(A3)) |
To zwróci 4. Jeśli chcesz uzyskać wartość ujemną, użyj znaku minus przed funkcją. Wtedy oczywiście możesz oczywiście skorzystać z funkcji obsługi błędów.
Na rozwiązanie błędu #NUM mamy dedykowany artykuł. Możesz to sprawdzić tutaj.
#NULL Błąd w Excelu
To rzadki rodzaj błędu. #NULL błąd spowodowany nieprawidłowym odwoływaniem się do komórki. Na przykład, jeśli chcesz podać odniesienie do zakresu A2:A5 w funkcji SUMA, ale przez pomyłkę wpisujesz A2 A5. Spowoduje to wygenerowanie błędu #NULL. Jak widać na poniższym obrazku, formuła zwraca błąd #NULL.
Jeśli zastąpisz spację kolumną (:), błąd #NULL zniknie i otrzymasz sumę A2:A5. Jeśli zastąpisz spację przecinkiem (,) , otrzymasz sumę A2 i A5.
Jak rozwiązać błąd #NULL?
Jak wiemy, błąd #NULL jest spowodowany literówką. Aby tego uniknąć, spróbuj wybrać zakresy za pomocą curser zamiast wpisywać je ręcznie.
Będą chwile, kiedy będziesz musiał wpisać adres zakresu z klawiatury. Zawsze dbaj o kolumnę symboli łączących (:) lub przecinek (,), aby uniknąć.
Jeśli masz istniejący błąd #NULL, sprawdź odwołania. Najprawdopodobniej przegapiłeś kolumnę (:) lub przecinek (,) między dwoma odwołaniami do komórek. Zastąp je odpowiednim symbolem i gotowe.
###### Błąd w Excelu
Czasami wydaje nam się, że ten błąd jest spowodowany brakiem miejsca na pokazanie wartości, ale tak nie jest. W takim przypadku możesz po prostu rozszerzyć szerokość komórki, aby wyświetlić wartość w komórce. Nie nazwałbym tego błędem.
W rzeczywistości ten błąd powstał, gdy próbujemy pokazać ujemną wartość czasu (nie ma czegoś takiego jak czas ujemny). Na przykład, jeśli spróbujemy odjąć 1 od 12:21:00, zwróci ######. W Excelu pierwsza data to 1/1/1900 00:00. Jeśli spróbujesz odjąć czas, który upłynął wcześniej, Excel wyświetli błąd ######. Im bardziej rozszerzysz szerokość, tym więcej # dostaniesz. Omówiłem to w tym artykule.
Jak uniknąć błędu ###### Excela?
Przed wykonaniem obliczeń arytmetycznych w programie Excel z wartością czasu pamiętaj o tych rzeczach.
- Minimalna wartość czasu to 1/1/1900 00:00. Nie możesz mieć prawidłowej daty przed tym w programie Excel
- 1 to 24 godziny (1 dzień) w programie Excel. Odejmując godziny, minuty i sekundy, przekształć je na równoważne wartości. Na przykład, aby odjąć 1 godzinę od 12:21, musisz od tego odjąć 1/24.
Śledzenie błędów w programie Excel
Teraz wiemy, jakie są popularne formuły Excela i dlaczego się pojawiają. Omówiliśmy również, jakie możliwe rozwiązanie może być dla każdego rodzaju błędów programu Excel.
Czasami w raportach programu Excel otrzymujemy błędy i nie mogliśmy wiedzieć, skąd właściwie występuje błąd. Trudno jest rozwiązać tego rodzaju błędy. Aby śledzić te błędy, program Excel udostępnia funkcję śledzenia błędów na karcie formuły.
Szczegółowo omówiłem śledzenie błędów w programie Excel.
Rozwiązywanie błędów logicznych w formule
Błędy logiczne są trudne do znalezienia i rozwiązania. Brak komunikatu wyświetlanego przez program Excel, gdy masz błąd logiczny w swojej funkcji. Wszystko wygląda dobrze. Ale tylko ty wiesz, że coś jest nie tak. Na przykład, otrzymując procent części całości, należy podzielić część przez sumę (=(część/suma)*100). Powinna być zawsze równa lub mniejsza niż 100%. Kiedy uzyskasz więcej niż 100%, wiesz, że coś jest nie tak.
To był prosty błąd logiczny. Ale czasami twoje dane pochodzą z kilku źródeł, w takim przypadku trudno jest rozwiązać logiczne problemy. Jednym ze sposobów jest ocena formuły.
W zakładce Formuła dostępna jest opcja oceny formuły. Wybierz swoją formułę i kliknij na nią. Każdy krok twojego obliczenia zostanie ci pokazany, co prowadzi do twojego końcowego wyniku. Tutaj możesz sprawdzić, gdzie wystąpił problem lub gdzie obliczenia się nie powiodły.
Możesz także prześledzić zależności i precedensy, aby zobaczyć, od których odwołań zależy Twoja formuła i jakie formuły zależą od konkretnej komórki.
Więc tak, to były niektóre typowe typy błędów, z którymi boryka się każdy użytkownik programu Excel. Dowiedzieliśmy się, dlaczego pojawia się każdy rodzaj błędu i jak możemy ich uniknąć. Dowiedzieliśmy się również o dedykowanej funkcji Excel do obsługi błędów. W tym artykule mamy linki do powiązanych stron, które szczegółowo omawiają problem. Możesz je sprawdzić. Jeśli masz jakiś konkretny rodzaj błędu, który Cię denerwuje, wspomnij o nim w sekcji komentarzy poniżej. Rozwiązanie otrzymasz w rozsądny sposób.
Jak poprawić #NUM! Błąd
Twórz niestandardowe słupki błędów w programie Excel 2016
Błąd #VALUE i jak go naprawić w programie Excel
Jak śledzić i naprawiać błędy formuł w programie Excel?
Popularne artykuły:
Funkcja WYSZUKAJ.PIONOWO w programie Excel
LICZ.JEŻELI w Excelu 2016
Jak korzystać z funkcji SUMIF w programie Excel?