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, informacje wpisane przez użytkownika może być wyświetlane 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 należy pamiętać, że każdy nawias powinien być w odpowiednim miejscu — w przeciwnym razie funkcja nie będzie dział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 #ZERO! 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. W przypadku użycia nieprawidłowego typu danych jako argumentu program 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). Uwaga: 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 komórki A3 i wprowadzisz formułę =SUMA(3;100;A3), program Excel doda liczby 3 i 100, a następnie doda tę sumę do wartości z komórki A3, zamiast dodać liczbę 3100 do zawartości komórki A3, co wyraża formuła =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 naprawić przy użyciu opcji wyświetlanych przez program Excel lub zignorować kliknięciem pozycji 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 obszaru Plik > Opcje > Formuły, lub
   w programie Excel na komputerze Mac wybierz kolejno pozycje 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. W przypadku znalezienia błędu w lewym górnym rogu komórki jest wyświetlany trójkąt.

   

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ą formuły, które powodują błąd: w formule użyto nieoczekiwanej składni, argumentów lub typów danych. Wartości błędów to: #DZIEL/0!, #N/D!, #NAZWA?, #ZERO!, #LICZBA!, #ADR! i #ARG!. Każda wartość błędu ma inną przyczynę i jest naprawiana w odmienny sposób.

     Uwaga: Jeśli wartość błędu zostanie wprowadzona bezpośrednio w komórce, zostanie zapisana jako wartość błędu, ale nie będzie 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.

     • Wpisanie formuły w komórce kolumny obliczeniowej, a następnie naciśnięcie klawiszy Ctrl+Z lub kliknięcie przycisku 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, więc 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, które sąsiadują z innymi formułami, różnią się od nich tylko użytym odwołaniem. 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 tymi w sąsiednich formułach, program Excel wyświetli 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 włączona, 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 te komórki 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 ona uwzględniana w obliczeniach, a wynik wynosi 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 kolejno pozycje Formuły > Inspekcja formuł > Sprawdzanie błędów.

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

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

   

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

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

   

4. Kliknij 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. Wybierz pozycję Sprawdzanie błędów , który pojawia się obok komórki, 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 będzie pomijany przy każdym kolejnym sprawdzaniu.

   

Jeśli formuła nie może poprawnie obliczyć wyniku, program Excel wyświetla wartość błędu, taką jak #####, #DZIEL/0!, #N/D!, #NAZWA?, #ZERO!, #LICZBA!, #ADR! lub #ARG!. 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. Porada: Spróbuj automatycznie dopasować komórkę, klikając dwukrotnie między nagłówkami kolumn. Jeśli ### jest wyświetlany, ponieważ program Excel nie może wyświetlić wszystkich znaków, ta funkcja to koryguje.
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. Porada: 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ć napisana niepoprawnie. Uwaga: 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 naprawi 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. Uwaga: Upewnij się, że zakresy są poprawnie rozdzielone — obszary C2:C3 i E4:E6 nie przecinają się, dlatego po wprowadzeniu formuły =SUMA(C2:C3 E4:E6) jest zwracany błąd #ZERO! #ZABLOKOWANE!. 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 przypadkowo mogły zostać usunięte komórki, do których odwoływały się inne formuły, lub mogły zostać wklejone komórki w miejsce komórek, 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.

Gdy komórki nie są widoczne w arkuszu, możesz obserwować je oraz 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 z formułami w arkuszu, przejdź do obszaru Narzędzia główne > Edytowanie > wybierz pozycję Znajdź i zaznacz (lub naciśnij klawisze Ctrl+G lub Control+G na komputerze Mac)> Przejdź do obszaruSpecjalnie > Formuły.

   

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

3. Wybierz pozycję Dodaj czujkę.

   

4. Upewnij się, że zaznaczono wszystkie komórki, które chcesz obserwować, a następnie 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.

   Uwaga: 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 obszaru Formuły > Inspekcja formuł > wybierz pozycję Okno czujki.

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

   Aby zaznaczyć wiele komórek, naciśnij klawisz CTRL, a następnie kliknij te 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 zostaną wyświetlone 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 obszaru 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 stanowi odwołanie 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. Klikaj przycisk Szacuj, aż każda z części formuły zostanie oszacowana.

5. Aby ponownie wyświetlić oszacowanie, kliknij przycisk Uruchom ponownie.

6. Aby zakończyć oszacowanie, kliknij przycisk Zamknij.