10 możliwych błędów w funkcjach Excela i ich rozwiązaniach

Oto jak automatycznie wykryć te błędy w programie Excel

Ze względu na dużą różnorodność opcji obliczeniowych typowe błędy w funkcjach Excela pojawiają się wielokrotnie w arkuszach kalkulacyjnych. Nie ma problemu - w większości przypadków można je poprawić za pomocą kilku kliknięć i możesz dalej korzystać z tabeli. Tutaj znajdziesz przegląd najczęstszych błędów i praktyczne wskazówki, jak je naprawić. Poznasz również możliwe strategie unikania.

Oto 10 typowych błędów w funkcjach Excela

Poniżej przedstawiono dziesięć najczęstszych kodów błędów dotyczących możliwych problemów i rozwiązań programu Excel.

1. #NAZWA?

Występują błędy pisowni - jeśli tak jest w formule, zamiast wyniku pojawia się komunikat o błędzie #NAZWA?. Nie ma sensu ukrywać błędu za pomocą funkcji takiej jak JEŻELI.BŁĄD. Musisz to poprawić. Wszystko, co musisz zrobić, to dokładnie przyjrzeć się nazwie, którą nadałeś formule. Czy istnieje przekręcony list? Zapomniałeś listu lub wpisałeś go dwa razy? Na przykład, popraw = SUMA (A3: A16) do = SUMA (A3: A16), a otrzymasz poprawny wynik.

Użyj kreatora formuł

Możesz uniknąć tego typu literówek, korzystając z Asystenta formuł. Działa to tak:

Gdy zaczniesz wpisywać nazwę formuły, otworzy się menu rozwijane z nazwami odpowiadającymi wprowadzonym wartościom.

Jeśli wprowadzisz nazwę i nawias otwierający, poprawna pisownia zostanie podana w tekście najechania.

Kreator funkcji pomoże przy wejściu do funkcji. Jeśli zaznaczysz komórkę formułą i wybierzesz „Wstaw funkcję” w zakładce „Formuła”, Excel wywoła kreatora. Tutaj wyświetlane są poszczególne argumenty - a także czy wystąpił błąd.

2. #NULL!

Ten komunikat o błędzie może wskazywać na dwie rzeczy:

• W formule określono nieprawidłowy operator zakresu.
• Używasz operatora skrzyżowania na dwóch nienakładających się obszarach.

W pierwszym przypadku odnosisz się do ciągłego zakresu komórek w formule - na przykład komórki od B4 do B12. Używasz dwukropka jako operatora zakresu. Na przykład, jeśli chcesz obliczyć sumę, poprawna formuła to = SUMA (B4: B12). Jeśli odwołujesz się do dwóch obszarów bez nakładania się, średnik jest odpowiednim operatorem. Jeśli chcesz obliczyć sumę z obszarów B4 do B12 i C9 do C23, ten wzór jest poprawny: = SUMA (B4: B12; C9: C23).

W drugim przypadku chcesz pracować z nakładającymi się zakresami komórek, ale dwa określone zakresy nie nakładają się. Na przykład otrzymujesz błąd dla formuły = KOMÓRKA („Adres” (B4: B12 D4: D6)). Te dwa obszary nie nakładają się na siebie. Jeśli zmienisz obszary tak, aby się nakładały - na przykład jako = CELL ("Adres" (B4: B12 B5: D5)) - jako wynik zostanie wyświetlony punkt przecięcia obu obszarów. W tym przypadku jest to komórka B5.

3. #REFERENCJA!

Komunikat o błędzie #REZUG! wskazuje, że obszar, do którego się odnosisz, nie istnieje. Program nie może odwoływać się do podanego zakresu do obliczeń. Dzieje się tak na przykład po usunięciu arkusza, wiersza, kolumny lub komórki, do której odwołujesz się w formule.

Na przykład masz arkusz kalkulacyjny programu Excel z wierszami 1, 2 i 3 oraz kolumnami A, B i C. Formuła to = SUMA (A2; B2; C2). Teraz usuń wiersz 2. Zamiast wyniku zobaczysz wartość błędu #REFER, ponieważ jedna z wartości do obliczenia nie istnieje.

Jeśli przypadkowo usunąłeś część, której brakuje do obliczeń, możesz poprawić swój błąd bezpośrednio za pomocą polecenia „Cofnij”. Jeśli podobszar został słusznie usunięty, przeformułuj formułę. Jeśli wpiszesz = SUMA (A2: C2), zostanie wyświetlony poprawny wynik, ponieważ Excel po prostu usunie usuniętą drugą kolumnę.

4. #WARTOŚĆ!

Ten komunikat o błędzie może wskazywać na wiele różnych problemów z programem Excel. Jedna z wartości w tabeli nie zgadza się z informacją we wzorze, więc program nie może wykonać obliczeń. Dzieje się tak na przykład wtedy, gdy jedna z komórek używanych do obliczeń zawiera słowo zamiast liczby.

Ponieważ istnieje wiele potencjalnych źródeł błędów dla #ARG! istnieje wiele sposobów, aby to naprawić. Pomóc mogą następujące rozwiązania:

• Możesz używać funkcji zamiast operatorów matematycznych. Więc zamiast = B4 + B5 + B6 wprowadź funkcję = SUMA (B4: B6).
• Sprawdź komórki, o których mowa, pod kątem znaków specjalnych. W razie potrzeby użyj do tego funkcji IST.TEKST w osobnej kolumnie. To pokazuje, w której komórce znajduje się błąd.
• Być może wartość błędu jest spowodowana spacją w komórce, która pojawia się w funkcji. Aby się dowiedzieć, zaznacz odpowiednie komórki. Następnie przejdź do „Wyszukaj i wybierz” w „Start” i kliknij „Zamień”. Wpisz spację dla „Szukaj” i nic dla „Zamień na”. Teraz możesz zastąpić niepotrzebne przestrzenie.
• Ukryte znaki mogą również prowadzić do tego powszechnego błędu w funkcjach programu Excel. Aby je wyeliminować, przejdź do „Start” i „Sortuj i filtruj” w „Filtruj”. Pod strzałką filtra dezaktywuj „Zaznacz wszystko” i zaznacz „Puste komórki” i wszystkie pozycje, w których nic nie ma. Jeśli naciśniesz OK, Excel pokaże ci wszystkie rzekomo puste komórki zawierające ukryte symbole. Zaznacz go i naciśnij "Usuń". Usuń filtr ponownie.

5. #####

Kiedy cała komórka Excela wydaje się pełna diamentów, wygląda gorzej niż na pierwszy rzut oka. Oznacza to po prostu, że kolumna Excela jest zbyt wąska, aby pokazać całą zawartość komórki. Po prostu przeciągnij myszą kolumnę szerzej lub wybierz mniejszy rozmiar czcionki, aby można było wyświetlić całą zawartość.

6. #DIV/0!

Załóżmy na przykład, że wprowadzasz funkcję, która ma podzielić wiele wartości przez inne wartości. Wtedy może się zdarzyć, że w jednej z komórek, których wartości chcesz podzielić, jest zero – albo w ogóle nic. W takim przypadku pojawia się wartość #DIV/0!Błąd wskazująca, że dzielenie przez zero jest niedozwolone.

Rozwiązanie: Upewnij się, że w odpowiednich komórkach programu Excel nie ma zera ani nic lub zmień odwołanie. Alternatywnie możesz wyłączyć wyświetlanie błędu - a mianowicie, gdy nadal czekasz na wartości, które chcesz wstawić do odpowiednich komórek. W tym celu można użyć na przykład formuły Excel IFERROR, która jest przedstawiona bardziej szczegółowo poniżej.

7. #NV!

Dzięki tej wartości błędu program Excel wskazuje, że nie można znaleźć tego, czego szukasz. Na przykład, jeśli przypisałeś wartości do określonych terminów i chcesz je wyświetlić, ale brakuje jednego z terminów, zamiast wyniku pojawi się ten komunikat o błędzie. Często odbywa się to w połączeniu z formułami WYSZUKAJ, WYSZUKAJ.POZIOMO, PODAJ.POZYCJĘ lub WYSZUKAJ.PIONOWO. Na przykład możesz wyświetlać błędy za pomocą funkcji WYSZUKAJ.PIONOWO, ponieważ pomaga ona porównywać ze sobą tabele lub je podsumowywać.

Przykład: Utworzyłeś tabelę z odpowiednimi cenami śrub, haczyków, kołków, śrub, nakrętek itp. i zapomniałeś jednego z warunków. Program go nie znajduje. W związku z tym nie może być ceny, tylko #NV! wskazać. Rozwiązujesz ten problem, dodając termin. Alternatywnie można pominąć wyświetlanie błędu za pomocą formuły JEŻELI BŁĄD (patrz poniżej).

8. #NUMER

Jeśli funkcja zawiera nieprawidłową wartość liczbową, otrzymasz błąd #NUMBER. Może się to zdarzyć na przykład, gdy piszesz liczby takie jak 1000 z kropką po jedynce. Wprowadź liczby bez formatowania. Być może wyciągasz gdzieś pierwiastek kwadratowy z liczby ujemnej lub wykonujesz inne obliczenia, które nie działają matematycznie?

Inną sytuacją, która może prowadzić do wartości błędu #LICZBA, jest użycie funkcji iteracyjnych, takich jak odsetki. Aby uzyskać wynik w tym przypadku, po prostu zmień liczbę iteracji, które Excel pozwala na obliczenie - czyli powtórne użycie tej samej metody obliczeniowej. Jest to konieczne przy obliczaniu odsetek, na przykład w przypadku ciągłego doliczania 3%.

Jak to zrobić:

1. W "Plik" i "Opcje" wybierz "Formuły". Tutaj, w sekcji „Opcje obliczeń”, zaznacz pole obok „Włącz obliczanie iteracyjne”.

2. W sekcji „Maksymalna liczba iteracji” możesz określić liczbę obliczeń, które program Excel powinien wykonać. Im wyższa ta liczba, tym dłużej zajmie obliczenie.

3. W polu „Maksymalna zmiana” możesz określić, jaka może być kwota zmiany między dwoma wynikami faktury.

W rzadkich przypadkach może się również zdarzyć, że wynik formuły jest za duży lub za mały do obliczenia w programie Excel. W tym celu jednak wynik musi być poniżej -1x10(wysoki) 307 lub powyżej 1x10(wysoki) 307. W takim przypadku musisz edytować formuły, aby wynik mieścił się w zakresie, który może obliczyć Excel.

9. Okólnik odniesienia

Komunikat o błędzie Okólnik odniesienia? Następnie wprowadziłeś formułę, która odwołuje się bezpośrednio lub pośrednio do komórki, w której znajduje się formuła. Okrągły błąd odniesienia wskazuje, że formuła nie może sama obliczyć i dlatego nie jest możliwy żaden wynik. Jeśli funkcje odwołują się do siebie, jest to pośrednie, nieprawidłowe odwołanie.

Jak rozwiązać błąd:

1. Jeśli istnieje odwołanie cykliczne, w odpowiednim miejscu pojawia się komunikat o błędzie. W takim przypadku możesz bezpośrednio zmienić komórkę. Nie wcinaj bieżącej komórki podczas wprowadzania formuły.

2. Jeśli chcesz znaleźć nieodkryte referencje cykliczne, przejdź do "Formuły", "Monitorowanie formuł" i "Odwołania cykliczne". Wszystkie odwołania cykliczne w dokumencie są teraz wyświetlane i można je rozwiązać tak, jak w pierwszym przypadku.

10. E +

E+ nie jest typowym błędem w funkcjach Excela, ale od czasu do czasu powoduje zamieszanie wśród użytkowników. Jeśli wprowadzisz bardzo dużą liczbę w jednej z komórek, program Excel może ją skrócić. Wtedy do gry wchodzi E +. Na przykład 265000000000000 w komórce Excela staje się 2,65E + 14. Jest to format wykładniczy. Excel używa go, aby móc wyświetlać nawet duże liczby, aby były całkowicie widoczne w komórkach.

W ten sposób możesz wyświetlić liczby w całości

Jeśli chcesz, aby liczby były wyświetlane w całości, konieczne jest wykonanie kilku prostych kroków:

1. Zaznacz odpowiednie pozycje na swojej liście.

2. Naciśnij Ctrl i 1, aby móc sformatować komórki.

3. W otwartym oknie dialogowym wybierz zakładkę „Liczby” i kategorię „Liczba”.

4. Wprowadź dużą liczbę i wybierz, ile miejsc dziesiętnych chcesz wyświetlić i czy chcesz wyświetlać separator tysięcy.

5. Po kliknięciu OK, Excel pokaże ci pełny numer w tabeli.

Automatycznie wykrywaj typowe błędy w funkcjach programu Excel

Masz możliwość aktywowania automatycznego sprawdzania programu Excel. Jeśli nie jest jeszcze dla Ciebie włączony, wystarczy kilka kliknięć:

W sekcji „Plik” przejdź do „Opcje”.

Wybierz "Formuły".

Zaznacz pole obok „Włącz sprawdzanie błędów w tle”.

Jeśli klikniesz jedną z komórek w skoroszycie, która zawiera komunikat o błędzie, obok niej pojawi się mały wykrzyknik. Jeśli ją wybierzesz, pojawi się lista. Wyjaśnia, jaki to komunikat o błędzie, i oferuje opcje rozwiązania, kroki obliczeniowe lub dalszą pomoc w rozwiązaniu problemu. Alternatywnie błąd można również zignorować.

Te funkcje identyfikują błędy w funkcjach programu Excel

Za pomocą tych funkcji można szybko sprawdzić typowe błędy w funkcjach programu Excel.

JEŻELIBŁĄD

Niektóre formuły mogą nie być kompletne w obecnym stanie rzeczy. Czy chcesz zapobiec wyświetlaniu błędów? Do tego nadaje się funkcja JEŻELI.BŁĄD. Ocenia typy błędów #NV, #VALUE !, #REFERENCE !, #DIV/0!, #NUMBER !, #NAME? lub #NULL! koniec. Ma prostą strukturę, ponieważ zawiera tylko wartość i wartość_jeżeli_błąd. Pierwsza wartość sprawdza poprawność funkcji. Program powinien wyświetlić ostatnią wartość w wyniku, jeśli wystąpi błąd. Funkcja jest bardzo przydatna, gdy edytujesz tabelę przez długi czas.

Przykład:

Wprowadzasz = JEŻELI.BŁĄD (B3 * C3; "Wprowadź wartości") lub = JEŻELI.BŁĄD (B3 * C3; ""). W pierwszym przypadku otrzymasz przypomnienie o tym, co jeszcze musisz zrobić (jeśli wystąpi błąd) z tekstem „Dodaj wartości”. W drugim przypadku komórka, która normalnie zawierałaby wynik, po prostu pozostaje pusta.

Uwaga: Wprowadzając tekst do formuły, należy go zawsze umieszczać w cudzysłowie. W przeciwnym razie to nie zadziała. Ponieważ bez cudzysłowów program Excel rozumie słowa jako część formuły.

CZY.BŁĄD

W połączeniu z funkcją JEŻELI.BŁĄD funkcja CZY.BŁĄD może również wskazywać istniejące błędy. W ten sposób nie utrudniają dalszych obliczeń na liście. W tym celu wpisz np.: = JEŻELI (ISERROR (B3); "proszę sprawdzić"; B3 / 6). Jeśli obliczenia można przeprowadzić, otrzymasz wynik. Jeśli Excel wykryje błąd podczas sprawdzania B3, w odpowiedniej komórce pojawią się słowa „proszę sprawdzić”.

Wniosek: szybkie i łatwe naprawianie błędów w funkcjach programu Excel

Niezależnie od tego, czy jest to odwrócona litera, wzór, którego nie można obliczyć matematycznie, błędne odniesienie czy spacja, ponieważ wciąż brakuje wartości - błędy zdarzają się w codziennej pracy. Excel też to wie. Automatyczne sprawdzanie wskazuje typowe błędy w funkcjach Excela i wyjaśnia, o co w nich chodzi. Istnieją również funkcje, które sprawdzają lub ukrywają błędy, dzięki czemu możesz przejść do reszty arkusza kalkulacyjnego.