Błędy formuł w Excelu i rozwiązaniach

„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.), nazywa się 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, niepoprawne typy 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ć, to sprawdzić wartość wyszukiwania. Sprawdź, czy wartości wyszukiwania są poprawnie napisane. 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 niektóre części tekstu muszą 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 niepoprawnym formatem 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! Od 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. Za każdym razem, gdy 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 formuła daje wynik dzielenia 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.

Jak rozwiązać #DIV/0! Błąd.

Można to łatwo rozwiązać, uważając na dane i sprawdzając, czy formuła da w wyniku #DIV/0! błąd. Oto przykładowa formuła, jak to zrobić.

=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 jest to 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ą.

=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 zwracaj uwagę na kolumnę symboli łączących (:) lub przecinek (,), aby tego 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ł.

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ś tam 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, a które 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.