Wykrywanie błędów w formułach

W poniższej tabeli opisano niektóre z najbardziej typowych błędów popełnianych przez użytkownika podczas wprowadzania formuł i wyjaśniono, jak je poprawić.

Upewnij się, że	Więcej informacji
Każda funkcja rozpoczyna się znakiem równości (=)	Jeśli znak równości zostanie pominięty, wpisany tekst może być wyświetlany jako tekst lub data. Jeśli na przykład wpiszesz SUMA(A1:A10), program Excel wyświetli ciąg tekstowy SUMA (A1:A10) i nie wykona obliczeń. Jeśli wpiszesz 11/2, program Excel wyświetli datę 2-lis (zakładając, że format komórek to Ogólne), zamiast podzielić 11 przez 2.
Liczba wszystkich nawiasów otwierających i zamykających jest zgodna	Należy się upewnić, że każdy nawias ma nawias do pary (otwierający lub zamykający). Podczas używania funkcji w formule ważne jest, aby każdy nawias był w odpowiednim położeniu, aby funkcja działała poprawnie. Przykładowa formuła =JEŻELI(B5<0);"Nieprawidłowe";B5*1,05) nie będzie działać, ponieważ ma dwa nawiasy zamykające i tylko jeden otwierający (powinien być tylko jeden nawias otwierający i jeden nawias zamykający). Poprawna formuła ma następującą postać: =JEŻELI(B5<0;"Nieprawidłowe";B5*1,05).
Do oznaczenia zakresu użyto dwukropka	Przy odwoływaniu się do zakresu komórek należy oddzielić odwołanie do pierwszej komórki w zakresie od odwołania do ostatniej komórki w zakresie przy użyciu dwukropka (:). Na przykład =SUMA(A1:A5), a nie =SUMA(A1 A5), co zwróci #NULL! Błąd.
Wprowadzono wszystkie wymagane argumenty	Niektóre funkcje mają wymagane argumenty. Ponadto należy się upewnić, że nie wprowadzono zbyt wielu argumentów.
Wprowadzono argumenty poprawnych typów	Niektóre funkcje, na przykład SUMA, wymagają argumentów liczbowych. Inne funkcje, na przykład ZASTĄP, wymagają wartości tekstowej w co najmniej jednym ze swoich argumentów. Jeśli jako argumentu zostanie użyty nieprawidłowy typ danych, Excel może zwrócić nieoczekiwane wyniki lub wyświetlić błąd.
Liczba zagnieżdżonych funkcji nie przekracza 64	Można wprowadzić lub zagnieździć maksymalnie 64 poziomy funkcji w funkcji.
Nazwy innych arkuszy są ujęte w pojedynczy cudzysłów	Jeśli formuła odnosi się do wartości lub komórek w innych arkuszach albo skoroszytach, a nazwa innego skoroszytu lub arkusza zawiera spację lub znak inny niż litera, należy ująć tę nazwę w pojedynczy cudzysłów, na przykład =Dane kwartalne’!D3, lub =‘123’!A1.
W przypadku odniesienia do arkusza w formule po jego nazwie znajduje się wykrzyknik (!)	Aby na przykład zwrócić wartość komórki D3 z arkusza Dane kwartalne w tym samym skoroszycie, należy użyć formuły ='Dane kwartalne'!D3.
Dołączono ścieżkę dostępu do skoroszytów zewnętrznych	Należy się upewnić, że każde odwołanie zewnętrzne zawiera nazwę skoroszytu i ścieżkę do niego. Odwołanie do skoroszytu zawiera jego nazwę i musi być ujęte w nawiasy kwadratowe (Nazwaskoroszytu.xlsx). Odwołanie musi również zawierać nazwę arkusza w tym skoroszycie. Odwołanie do skoroszytu można dołączyć do formuły nawet wtedy, gdy skoroszyt nie jest otwarty w programie Excel. Należy w tym celu podać pełną ścieżkę do pliku, na przykład =ILE.WIERSZY('C:\Moje dokumenty\[Działania w drugim kwartale.xlsx]Sprzedaż'!A1:A8). Ta formuła zwraca liczbę wierszy w zakresie obejmującym komórki od A1 do A8 w innym skoroszycie (8). : Jeśli pełna ścieżka zawiera znaki spacji (jak w powyższym przykładzie), należy ją ująć w pojedynczy cudzysłów (ten znak należy wstawić na początku ścieżki i po nazwie skoroszytu, przed wykrzyknikiem).
Wprowadzono liczby bez formatowania	Nie wolno formatować liczb wprowadzanych w formułach. Jeśli na przykład chcesz wprowadzić wartość $1.000 (z kropką jako separatorem tysięcy), wprowadź 1000 w formule. Jeśli wprowadzisz kropkę jako część numeru, program Excel będzie ją traktować jako znak kropki. Jeśli chcesz wyświetlać numery z separatorami tysięcy lub milionów albo symbolami walut, sformatuj komórki po wprowadzeniu liczb. Jeśli na przykład chcesz dodać liczbę 3100 do wartości w komórce A3 i wprowadzisz formułę =SUMA(3;100;A3),Excel doda liczby 3 i 100, a następnie doda tę sumę do wartości z komórki A3, zamiast dodawać 3100 do komórki A3, która byłaby =SUMA(3100;A3). Jeśli wprowadzisz formułę =MODUŁ.LICZBY(-2;134), program Excel wyświetli błąd, ponieważ funkcja MODUŁ.LICZBY obsługuje tylko jeden argument: =MODUŁ.LICZBY(-2134).

Można zastosować pewne reguły umożliwiające sprawdzanie błędów w formułach. Te reguły nie gwarantują, że arkusz będzie wolny od błędów, ale są przydatne w odnajdywaniu typowych błędów. Każdą z tych reguł można osobno włączać i wyłączać.

Błędy mogą być oznaczane i poprawiane na dwa sposoby: po kolei (podobnie jak w przypadku modułu sprawdzania pisowni) lub natychmiast po ich wystąpieniu w arkuszu podczas wprowadzania danych.

Błąd można rozwiązać przy użyciu opcji wyświetlanych w programie Excel lub zignorować, wybierając pozycję Ignoruj błąd. Jeśli zignorujesz błąd w określonej komórce, błąd w tej komórce nie będzie wyświetlany podczas następnego sprawdzania błędów. Możesz jednak zresetować wszystkie uprzednio zignorowane błędy, aby były ponownie wyświetlane.

1. W przypadku programu Excel w systemie Windows przejdź do pozycji Opcje > plików > formuł lub
   w programie Excel na komputerze Mac wybierz menu Excel > Preferencje > Sprawdzanie błędów.

2. W obszarze Sprawdzanie błędów zaznacz pole wyboru Włącz sprawdzanie błędów w tle. Każdy znaleziony błąd jest oznaczony trójkątem w lewym górnym rogu komórki.

   

3. Aby zmienić kolor trójkąta oznaczającego wykrycie błędu, wybierz odpowiedni kolor w polu Wskaż błędy przy użyciu koloru.

4. W obszarze Reguły sprawdzania błędów zaznacz lub wyczyść pola wyboru dowolnych z następujących reguł:

   • Komórki zawierające formuły, które powodują błąd: w formule nie jest używana oczekiwana składnia, argumenty ani typy danych. Wartości błędów to: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!i #VALUE!. Każda z tych wartości błędów ma różne przyczyny i jest rozwiązywana na różne sposoby.

     : Jeśli wprowadzasz wartość błędu bezpośrednio w komórce, jest ona przechowywana jako ta wartość błędu, ale nie jest oznaczona jako błąd. Jeśli jednak do danej komórki odwołuje się formuła w innej komórce, formuła ta zwraca wartość błędu z tej komórki.

   • Niespójna formuła kolumny obliczeniowej w tabelach: Kolumna obliczeniowa może zawierać pojedyncze formuły, które są inne niż formuła głównej kolumny, co powoduje wystąpienie wyjątku. Wyjątki kolumny obliczeniowej są tworzone w następujących sytuacjach:

     • Wpisanie danych innych niż formuła w komórce kolumny obliczeniowej.

     • Wpisz formułę w komórce kolumny obliczeniowej, a następnie użyj Ctrl +Z lub wybierz pozycję Cofnij na pasku narzędzi Szybki dostęp.

     • Wpisanie nowej formuły w kolumnie obliczeniowej, która już zawiera co najmniej jeden wyjątek.

     • Skopiowanie do kolumny obliczeniowej danych niezgodnych z formułą kolumny obliczeniowej. Jeśli kopiowane dane zawierają formułę, formuła ta zastępuje dane w kolumnie obliczeniowej.

     • Przeniesienie lub usunięcie w innym arkuszu komórki, do której odwołuje się wiersz kolumny obliczeniowej.

   • Komórki zawierające lata przedstawione w postaci dwucyfrowej: Komórka zawiera datę tekstową, która może zostać niepoprawnie zinterpretowana jako błędny wiek, jeśli zostanie użyta w formułach. Na przykład data w formule =ROK("1/1/31") może dotyczyć roku 1931 lub 2031. Tę regułę należy stosować do sprawdzania niejednoznacznych dat tekstowych.

   • Liczby formatowane jako tekst lub poprzedzane apostrofem: Komórka zawiera liczby przechowywane jako tekst. Dzieje się tak zazwyczaj, gdy dane są importowane z innych źródeł. Liczby przechowywane jako tekst mogą powodować nieoczekiwane wyniki sortowania, dlatego najlepiej jest przekonwertować je na liczby. Formuła ‘=SUMA(A1:A10) jest widoczna jako tekst.

   • Formuły niespójne z innymi formułami w regionie: Formuła nie odpowiada wzorcowi innych formuł znajdujących się w pobliżu. W wielu przypadkach formuły sąsiadujące z innymi formułami różnią się tylko używanymi odwołaniami. W następującym przykładzie czterech sąsiadujących formuł w programie Excel jest wyświetlany błąd obok formuły =SUMA(A10:C10) w komórce D4, ponieważ w sąsiednich formułach występuje przyrost o jeden wiersz, a w tej formule występuje przyrost o 8 wierszy — program Excel oczekuje formuły =SUMA(A4:C4).

     

     Jeśli odwołania użyte w formule nie są spójne z odwołaniami w sąsiednich formułach, w programie Excel zostanie wyświetlony błąd.

   • Formuła, która nie uwzględnia komórek w regionie: Formuła może nie dołączać automatycznie odwołań do danych wstawionych między oryginalny zakres danych a komórkę zawierającą formułę. Ta reguła porównuje odwołanie w formule z rzeczywistym zakresem komórek sąsiadujących z komórką zawierającą formułę. Jeśli komórki sąsiadujące zawierają dodatkowe wartości i nie są puste, program Excel wyświetli obok formuły błąd.

     Na przykład program Excel wstawia błąd obok formuły =SUMA(D2:D4), gdy ta reguła jest stosowana, ponieważ komórki D5, D6 i D7 sąsiadują z komórkami, do których odwołuje się formuła, i komórką zawierającą formułę (D8), a komórki te zawierają dane, do których powinna odwoływać się formuła.

     

   • Odblokowane komórki zawierające formuły: Formuła nie jest blokowana w celu ochrony. Domyślnie wszystkie komórki w arkuszu są zablokowane, aby uniemożliwić ich zmianę, gdy arkusz jest chroniony. Może to ułatwić uniknięcie pomyłek, takich jak przypadkowe usunięcie lub zmiana formuł. Ten błąd wskazuje, że komórka została ustawiona jako do odblokowania, ale arkusz nie jest chroniony. Upewnij się, że komórka nie ma być zablokowana.

   • Formuły odwołujące się do pustych komórek: Formuła zawiera odwołanie do pustej komórki. Jak pokazano w następującym przykładzie, może to spowodować niezamierzone wyniki.

     Załóżmy, że należy obliczyć średnią liczb w następującej kolumnie komórek. Jeśli trzecia komórka jest pusta, nie jest uwzględniana w obliczeniach, a wynik to 22,75. Jeśli trzecia komórka zawiera 0, wynik wynosi 18,2.

     

   • Dane wprowadzone w tabeli są nieprawidłowe: W tabeli jest błąd sprawdzania poprawności. Sprawdź ustawienia sprawdzania poprawności, przechodząc do karty Dane > grupa Narzędzia danych > Sprawdzanie poprawności danych.

1. Wybierz arkusz, który ma zostać sprawdzony pod kątem błędów.

2. Jeśli obliczenia w arkuszu były wykonywane ręcznie, naciśnij klawisz F9, aby wykonać obliczenia ponownie.

   Jeśli okno dialogowe Sprawdzanie błędów nie jest wyświetlane, wybierz pozycję Formuły > Inspekcja formuł > Sprawdzanie błędów.

3. Jeśli wcześniej zignorowano jakiekolwiek błędy, możesz je ponownie sprawdzić, wykonując następujące czynności: przejdź do pozycji Opcje > plików > Formuły. W programie Excel na komputerze Mac wybierz menu Excel > Preferencje > Sprawdzanie błędów.

   W sekcji Sprawdzanie błędów wybierz pozycję Resetuj zignorowane błędy > OK.

   

   : Zresetowanie zignorowanych błędów powoduje zresetowanie wszystkich błędów we wszystkich arkuszach w aktywnym skoroszycie.

   : Warto przenieść okno dialogowe Sprawdzanie błędów tuż poniżej paska formuły.

   

4. Wybierz jeden z przycisków akcji po prawej stronie okna dialogowego. Dostępne akcje różnią się w zależności od typu błędu.

5. Wybierz pozycję Dalej.

1. Obok komórki wybierz pozycję Sprawdzanie błędów , a następnie wybierz odpowiednią opcję. Dostępne polecenia różnią się w zależności od typu błędu, a pierwszy wpis jest opisem błędu.

   Jeśli wybierzesz pozycję Ignoruj błąd, błąd zostanie oznaczony do zignorowania dla każdego kolejnego sprawdzenia.

   

Jeśli formuła nie może poprawnie oszacować wyniku, program Excel wyświetli wartość błędu, taką jak #####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!i #VALUE!. Każdy typ błędu ma inne przyczyny i różne rozwiązania.

Poniższa tabela zawiera linki do artykułów zawierających szczegółowe opisy tych błędów oraz krótki opis ułatwiający rozpoczęcie pracy.

Temat	Opis
Naprawianie błędu ####	Program Excel wyświetla ten błąd, gdy kolumna nie jest wystarczająco szeroka, aby wyświetlić wszystkie znaki zawarte w komórce lub komórka zawiera ujemne wartości dat lub godzin. Na przykład formuła odejmująca datę w przyszłości od daty w przeszłości, taka jak =2008-06-15-2008-07-01, daje w wyniku wartość ujemną daty. : Spróbuj automatycznie dopasować komórkę, klikając dwukrotnie między nagłówkami kolumn. Jeśli jest wyświetlany błąd ###, ponieważ program Excel nie może wyświetlić wszystkich znaków, spowoduje to jego poprawienie.
Naprawianie błędu #DZIEL/0! błąd	Program Excel wyświetla ten błąd, gdy liczba jest dzielona przez zero (0) lub przez komórkę, która nie zawiera wartości. : Dodawanie funkcji obsługi błędów, takiej jak w poniższym przykładzie, czyli =JEŻELI(C2;B2/C2;0)
Naprawianie błędu #N/D!	Program Excel wyświetla ten błąd, gdy wartość dla funkcji lub formuły jest niedostępna. Jeśli korzystasz z funkcji, takiej jak WYSZUKAJ.PIONOWO, czy dla wyszukiwanej zawartości istnieją zgodne elementy w zakresie wyszukiwania? Zwykle nie istnieją. Spróbuj użyć funkcji JEŻELI.BŁĄD, aby pomijać błędy #N/D. W tym przypadku można użyć następującej formuły: =JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(D2;$D$6:$E$8;2;PRAWDA);0)
Naprawianie błędu #NAZWA? błąd	Ten błąd jest wyświetlany, gdy program Excel nie rozpoznaje tekstu w formule. Na przykład nazwa zakresu lub nazwa funkcji może być wpisana niepoprawnie. : Jeśli korzystasz z funkcji, upewnij się, że nazwa funkcji jest wprowadzona prawidłowo. W tym przypadku nazwa funkcji SUMA jest wpisana z błędem. Usuń literę "e", a program Excel poprawi ten błąd.
Naprawianie błędu #NULL! błąd	Program Excel wyświetla ten błąd w przypadku określenia przecięcia dwóch obszarów, które się nie przecinają (nie krzyżują). Operator przecięcia jest znakiem spacji oddzielającym odwołania w formule. : Upewnij się, że zakresy są poprawnie rozdzielone — obszary C2:C3 i E4:E6 nie przecinają się, więc wprowadzenie formuły =SUMA(C2:C3 E4:E6) zwraca #NULL! . Umieszczenie przecinka między zakresami C i E poprawia je =SUMA(C2:C3;E4:E6)
Naprawianie błędu #LICZBA! błąd	Program Excel wyświetla ten błąd, gdy formuła lub funkcja zawiera nieprawidłowe wartości liczbowe. Czy korzystasz z funkcji wykonującej iteracje, na przykład IRR lub RATE? Jeśli tak, błąd #LICZBA! występuje prawdopodobnie dlatego, że funkcja nie może znaleźć wyniku. Informacje na temat procedury rozwiązywania problemu zawiera temat pomocy.
Naprawianie błędu #ADR! błąd	Program Excel wyświetla ten błąd, gdy odwołanie do komórki jest nieprawidłowe. Na przykład możesz usunąć komórki, do których odwoływały się inne formuły, lub wklejać komórki przeniesione na komórki, do których odwoływały się inne formuły. Czy przypadkowo usunięto wiersz lub kolumnę? Usunęliśmy kolumnę B w tej formule, =SUMA(A2;B2;C2), i zobacz, co się stało. Naciśnij przycisk Cofnij (Ctrl+Z), aby anulować usunięcie, odbuduj formułę, lub użyj odwołania do ciągłego zakresu, w następujący sposób: =SUMA(A2:C2), co spowodowałoby automatyczną aktualizację po usunięciu kolumny B.
Naprawianie błędu #ARG! błąd	Program Excel może wyświetlać ten błąd, jeśli w formule uwzględniono komórki, które zawierają różne typy danych. Czy używasz operatorów matematycznych (+, -, *, /, ^) z innymi typami danych? Jeśli tak, spróbuj użyć funkcji. W tym przypadku formuła =SUMA(F2:F5) rozwiązałaby problem.

Jeśli komórki nie są widoczne w arkuszu, możesz watch te komórki i ich formuły na pasku narzędzi Okno czujki. Okno czujki ułatwia przeprowadzanie inspekcji bądź audytu albo potwierdzanie obliczeń formuły i wyników w dużych arkuszach. W przypadku korzystania z okna czujki nie jest konieczne wielokrotne przewijanie arkusza ani przechodzenie do jego różnych części.

Ten pasek narzędzi może być przenoszony lub dokowany jak każdy inny pasek narzędzi. Na przykład można go zadokować u dołu okna. Pasek narzędzi zawiera informacje o następujących właściwościach komórki: 1) skoroszyt, 2) arkusz, 3) nazwa (jeśli komórka ma odpowiadający nazwany zakres), 4) adres komórki, 5) wartość i 6) formuła.

Dodawanie komórek do okna czujki

1. Zaznacz komórki, które chcesz obserwować.

   Aby zaznaczyć wszystkie komórki w arkuszu z formułami, przejdź do pozycji Narzędzia główne > edytowanie > wybierz pozycję Znajdź & Zaznacz (lub możesz użyć Ctrl+G lub Control+G na komputerze Mac)> Przejdź do formuł specjalnych > .

   

2. Przejdź do pozycji Formuły > Inspekcja formuł > wybierz pozycję Okno czujki.

3. Wybierz pozycję Dodaj czujkę.

   

4. Upewnij się, że zaznaczono wszystkie komórki, które chcesz watch, i wybierz pozycję Dodaj.

   

5. Aby zmienić szerokość kolumny w oknie czujki, przeciągnij obramowanie z prawej strony nagłówka kolumny.

6. Aby wyświetlić komórkę, której dotyczy wpis w oknie czujki, kliknij dwukrotnie wybrany wpis.

   : Formuły z odwołaniami zewnętrznymi do innych skoroszytów są wyświetlane na pasku narzędzi Okno czujki tylko wtedy, gdy te skoroszyty są otwarte.

Usuwanie komórek z okna czujki

1. Jeśli pasek narzędzi Okno czujki nie jest wyświetlany, przejdź do pozycji Formuły > Inspekcja formuł > wybierz pozycję Okno czujki.

2. Zaznacz komórki, które chcesz usunąć.

   Aby zaznaczyć wiele komórek, naciśnij CTRL, a następnie zaznacz komórki.

3. Wybierz pozycję Usuń czujkę.

   

Czasami trudno jest zrozumieć, jak formuła zagnieżdżona oblicza ostateczny wynik, ponieważ istnieje kilka obliczeń pośrednich i testów logicznych. Jednak za pomocą okna dialogowego Szacowanie formuły można wyświetlić różne części formuły zagnieżdżonej, szacowane w kolejności zgodnej z obliczaniem formuły. Na przykład formuła =JEŻELI(ŚREDNIA(D2:D5)>50;SUMA(E2:E5);0) jest łatwiejsza do zrozumienia, gdy są widoczne następujące wyniki pośrednie:

W oknie dialogowym Szacowanie formuły	Opis
=JEŻELI(ŚREDNIA(D2:D5) > 50;SUMA(E2:E5);0)	Początkowo jest wyświetlana formuła zagnieżdżona. Funkcje ŚREDNIA i SUMA są zagnieżdżone w funkcji JEŻELI. Zakres komórek D2:D5 zawiera wartości 55, 35, 45 i 25, dlatego wynikiem funkcji ŚREDNIA(D2:D5) jest 40.
= JEŻELI(40>50;SUMA(E2:E5);0)	Zakres komórek D2:D5 zawiera wartości 55, 35, 45 i 25, dlatego wynikiem funkcji ŚREDNIA(D2:D5) jest 40.
=JEŻELI(FAŁSZ;SUMA(E2:E5);0)	Ponieważ 40 nie jest większe niż 50, wyrażenie w pierwszym argumencie funkcji JEŻELI (test_logiczny) ma wartość Fałsz. Funkcja JEŻELI zwraca wartość trzeciego argumentu (wartość_jeżeli_fałsz). Funkcja SUMA nie jest szacowana, ponieważ jest drugim argumentem funkcji JEŻELI (wartość_jeżeli_prawda) i jest zwracana tylko wtedy, gdy wyrażenie ma wartość Prawda.

1. Zaznacz komórkę, którą chcesz oszacować. Nie można szacować kilku komórek równocześnie.

2. Przejdź do pozycji Formuły > Inspekcja formuł > Szacuj formułę.

3. Kliknij przycisk Szacuj, aby zbadać wartość podkreślonego odwołania. Wynik szacowania zostanie wyświetlony kursywą.

   Jeśli podkreślona część formuły jest odwołaniem do innej formuły, wybierz pozycję Wkrocz , aby wyświetlić drugą formułę w polu Oszacowanie . Kliknij przycisk Wyjdź, aby powrócić do poprzedniej komórki i formuły.

   Przycisk Wkrocz jest niedostępny dla odwołania w przypadku drugiego wystąpienia tego odwołania w formule lub gdy formuła odwołuje się do komórki w innym skoroszycie.

4. Kontynuuj wybieranie opcji Szacuj , aż każda część formuły zostanie oceniona.

5. Aby ponownie wyświetlić ocenę, wybierz pozycję Uruchom ponownie.

6. Aby zakończyć oszacowanie, wybierz pozycję Zamknij.