Jak unikać niepoprawnych formuł w programie Excel

Jeśli program Excel nie może rozwiązać formuły, którą próbujesz utworzyć, może zostać wyświetlony komunikat o błędzie, taki jak przedstawiony poniżej:

Niestety oznacza to, że program Excel nie może zrozumieć, co próbujesz zrobić, dlatego musisz zaktualizować formułę lub upewnić się, że używasz funkcji w sposób poprawny.

Powróć do komórki z niepoprawną formułą, która będzie w trybie edycji, a program Excel wyróżni miejsce, w którym występuje problem. Jeśli nadal nie wiesz, co zrobić, i chcesz zacząć od nowa, możesz ponownie nacisnąć klawisz ESC lub wybrać przycisk Anuluj na pasku formuły, aby opuścić tryb edycji.

Jeśli chcesz przejść dalej, poniższa lista kontrolna zawiera kroki rozwiązywania problemów, które pomogą Ci ustalić przyczynę błędu. Wybierz nagłówki, aby dowiedzieć się więcej.

Czy w formule używane są prawidłowe separatory list?

W formułach z więcej niż jednym argumentem do oddzielenia argumentów używane są separatory list. Używany separator może się różnić w zależności od ustawień regionalnych systemu operacyjnego i programu Excel. Najczęściej używane separatory list to przecinek „,” i średnik „;”.

Formuła zostanie przerwana, jeśli w którejkolwiek z jej funkcji zostaną użyte nieprawidłowe separatory.

Aby uzyskać więcej informacji, zobacz: Błędy formuł, gdy separator list nie jest prawidłowo ustawiony

Czy widzisz błąd z kratką (#) w formułach programu Excel?

Program Excel wyświetla różne błędy z kratką (#), takie jak #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? i #NULL! aby wskazać, że coś w formule nie działa prawidłowo. Na przykład przyczyną błędu #ARG! jest niepoprawne formatowanie lub obecność nieobsługiwanych typów danych w argumentach. Możesz też zobaczyć błąd #ADR!, jeśli formuła zawiera odwołanie do komórek, które zostały usunięte lub zastąpione innymi danymi. Wskazówki dotyczące rozwiązywania problemów będą różne dla poszczególnych błędów.

Zapoznaj się z dowolnym z następujących tematów odpowiadającym wyświetlanemu błędowi z krzyżykiem:

• Naprawianie błędu #LICZBA! błąd
• Naprawianie błędu #ARG! błąd
• Naprawianie błędu #N/D!
• Naprawianie błędu #DZIEL/0! błąd
• Naprawianie błędu #ADR! błąd
• Naprawianie błędu #NAZWA?
• Naprawianie błędu #NULL! błąd

Naprawianie przerwanych linków w formułach programu Excel

Zawsze podczas otwierania arkusza kalkulacyjnego zawierającego formuły odwołujące się do wartości w innych arkuszach kalkulacyjnych zostanie wyświetlony monit o zaktualizowanie odwołań lub pozostawianie ich bez zmian.

Program Excel wyświetli powyższe okno dialogowe w celu zapewnienia, że formuły w bieżącym arkuszu kalkulacyjnym zawsze wskazują na wartość najbardziej aktualną, na wypadek, gdyby wartość odwołania została zmieniona. Możesz zaktualizować odwołania lub pominąć operację, jeśli nie chcesz przeprowadzać aktualizacji. Nawet jeśli nie zdecydujesz się na zaktualizowanie odwołań, możesz w dowolnym momencie ręcznie zaktualizować linki w arkuszu kalkulacyjnym.

Zawsze możesz wyłączyć wyświetlanie okna dialogowego po uruchomieniu programu. Aby to zrobić, przejdź do pozycji Zaawansowane >> opcje plików > Ogólne i wyczyść pole Pytaj przy automatycznym aktualizowaniu łączy.

Formuła pokazuje składnię zamiast wartości w programie Excel

Jeśli formuła nie wyświetla wartości, wykonaj następujące czynności:

• Upewnij się, że program Excel jest skonfigurowany w celu pokazywania formuł w arkuszu kalkulacyjnym. W tym celu wybierz kartę Formuły i w grupie Inspekcja formuł kliknij pozycję Pokaż formuły.

  Porada

  Możesz także użyć skrótu klawiaturowego Ctrl + ` (klawisz znajdujący się nad klawiszem Tab). Gdy to zrobisz, kolumny zostaną automatycznie poszerzone tak, aby formuły były widoczne. Ale bez obaw — po przełączeniu do widoku normalnego zostanie przywrócony poprzedni rozmiar kolumn.

• Jeśli wykonanie powyższego kroku nie rozwiązało problemu, oznacza to, że prawdopodobnie komórka jest sformatowana jako tekst. Możesz kliknąć prawym przyciskiem myszy komórkę i wybrać polecenie Formatuj komórki ogólnie > (lub Ctrl + 1), a następnie nacisnąć klawisz F2 > Enter, aby zmienić format.

• Jeśli w kolumnie znajduje się duży zakres komórek sformatowanych jako tekst, zaznacz ten zakres, wybierz format liczbowy i przejdź do pozycji Tekst danych > jako Zakończenie kolumny>. W ten sposób zostaną sformatowane wszystkie zaznaczone komórki.

  

Włącz automatyczne obliczanie skoroszytu, jeśli formuła nie jest obliczana w programie Excel

Gdy formuła nie jest obliczana, należy sprawdzić, czy w programie Excel jest włączone obliczanie automatyczne. Formuły nie będą obliczane, jeśli włączono obliczanie ręczne. Wykonaj poniższe czynności, aby sprawdzić, czy jest włączona opcja Obliczanie automatyczne.

1. Wybierz kartę Plik , wybierz pozycję Opcje, a następnie wybierz kategorię Formuły.

2. Upewnij się, że w sekcji Opcje obliczania w obszarze Obliczanie skoroszytu jest wybrana opcja Automatycznie .

   

Aby uzyskać więcej informacji na temat obliczeń, zobacz temat Zmienianie sposobu ponownego obliczania, iteracji lub dokładności formuły.

W formule istnieje co najmniej jedno odwołanie cykliczne

Odwołanie cykliczne występuje wtedy, gdy formuła odwołuje się do komórki, w której sama się znajduje. Rozwiązanie problemu polega na przeniesieniu formuły do innej komórki lub na zmianie składni formuły w taki sposób, aby uniknąć odwołań cyklicznych. Jednak w niektórych sytuacjach odwołania cykliczne mogą być niezbędne, ponieważ umożliwiają iteracyjne wykonywanie funkcji — powtarzanie do momentu spełnienia określonego warunku numerycznego. W takich przypadkach należy włączyć opcję Usuń lub zezwól na odwołanie cykliczne.

Aby uzyskać więcej informacji na temat odwołań cyklicznych, zobacz temat Usuwanie i zezwalanie na odwołania cykliczne.

Czy funkcja zaczyna się znakiem równości (=)?

Wpis, który nie rozpoczyna się znakiem równości, nie jest formułą i nie będzie obliczany — jest to częsty błąd.

W przypadku wpisania ciągu SUMA(A1:A10) program Excel wyświetli ciąg tekstowy SUMA(A1:A10), a nie wynik formuły. Ewentualnie, jeśli wpiszesz wartość 11/2, program Excel wyświetli datę, na przykład 2-lis lub 11/02/2009, zamiast wyniku dzielenia 11 przez 2.

Aby uniknąć tych nieoczekiwanych wyników, zawsze rozpoczynaj funkcję od znaku równości. Na przykład wpisz: =SUMA(A1:A10) i =11/2.

Czy liczba nawiasów otwierających i zamykających jest zgodna?

Podczas używania funkcji w formule pamiętaj, że każdy nawias otwierający wymaga nawiasu zamykającego, aby funkcja działała poprawnie. Upewnij się, że każdy nawias ma nawias do pary. Na przykład formuła =JEŻELI(B5<0);"Nieprawidłowe";B5*1,05) nie będzie działać, ponieważ ma dwa nawiasy zamykające, ale tylko jeden otwierający. Poprawna formuła będzie miała następującą postać: =JEŻELI(B5<0;"Nieprawidłowe";B5*1,05).

Czy w składni występują wszystkie wymagane argumenty?

Funkcje programu Excel mają argumenty — wartości, które muszą zostać podane, aby funkcja działała. Tylko kilka funkcji (na przykład PI lub DZIŚ) nie ma żadnych argumentów. Sprawdź składnię formuły wyświetlaną po rozpoczęciu wpisywania nazwy funkcji, aby upewnić się, że wprowadzono wszystkie wymagane argumenty.

Na przykład funkcja LITERY.WIELKIE przyjmuje jako argument tylko jeden ciąg tekstowy lub odwołanie do komórki: =LITERY.WIELKIE("halo") lub =LITERY.WIELKIE(C2)

Ponadto niektóre funkcje, na przykład SUMA, wymagają tylko argumentów liczbowych, natomiast inne funkcje, na przykład ZASTĄP, wymagają wartości tekstowej dla co najmniej jednego ze swoich argumentów. W przypadku użycia nieprawidłowego typu danych funkcje mogą zwrócić nieoczekiwane wyniki lub wyświetlają błąd #ARG!.

Jeśli chcesz szybko wyszukać składnię konkretnej funkcji, zobacz listę Funkcje programu Excel (według kategorii).

Obsługa liczb niesformatowanych w formułach programu Excel

Nie wprowadzaj w formułach liczb sformatowanych za pomocą znaków dolara ($) lub separatorów tysięcy (,), ponieważ znaki dolara oznaczają odwołania bezwzględne , a przecinki są separatorami argumentów. Zamiast wprowadzać $1,000 w formule, wpisz 1000.

W przypadku użycia liczb sformatowanych w argumentach zostaną zwrócone nieoczekiwane wyniki obliczeń, ale może też zostać wyświetlony błąd #NUM! . Jeśli na przykład wprowadzisz formułę =MODUŁ.LICZBY(-2,134),, aby znaleźć wartość bezwzględną -2134, program Excel wyświetli błąd #LICZBA!, ponieważ funkcja MODUŁ.LICZBY akceptuje tylko jeden argument i widzi argumenty -2 i 134 jako oddzielne argumenty.

Czy komórki, do których się odwołujesz, mają poprawny typ danych?

Czasem formuła nie zwraca oczekiwanych wyników, jeśli typ danych komórki nie może zostać użyty w obliczeniach. Jeśli na przykład prosta formuła =2+3 zostanie wprowadzona w komórce sformatowanej jako tekst, program Excel nie będzie mógł obliczyć wprowadzonych danych. W komórce będzie widoczna wartość =2+3. Aby rozwiązać ten problem, zmień typ danych komórki z Tekst na Ogólne w poniższy sposób:

1. Zaznacz komórkę.
2. Wybierz pozycję Narzędzia główne i wybierz strzałkę, aby rozwinąć grupę Liczby lub Format liczb (lub naciśnij klawisze Ctrl + 1). Następnie wybierz pozycję Ogólne.
3. Naciśnij klawisz F2, aby przełączyć komórkę w tryb edycji, a następnie naciśnij klawisz Enter, aby zaakceptować formułę.

Data wprowadzona w komórce, która ma typ danych Liczbowe , będzie wyświetlana nie jako data, ale jako liczbowa wartość daty. Aby wyświetlić tę liczbę jako datę, w galerii Format liczb wybierz pozycję Data.

Czy próbujesz wykonywać mnożenie bez użycia symbolu *?

Często w formule odruchowo używa się znaku x jako operatora mnożenia. Jednak zamiast niego w programie Excel należy używać symbolu gwiazdki (*). Jeśli w formule używasz stałych, w programie Excel zostanie wyświetlony komunikat o błędzie z propozycją naprawienia formuły przez zastąpienie znaku x symbolem gwiazdki (*).

Jeśli jednak używasz odwołań do komórek, program Excel zwróci #NAME? błąd.

Czy wokół tekstu w formułach brakuje cudzysłowów?

W formule zawierającej tekst umieść ten tekst w cudzysłowie.

Na przykład formuła ="Dzisiaj jest " & TEKST(DZIŚ(),"dddd, dd mmmm") łączy tekst „Dzisiaj jest ” z wynikami funkcji TEKST i DZIŚ i zwraca wynik Dzisiaj jest poniedziałek, 30 maj.

W formule tekst „Dzisiaj jest ” zawiera spację przed cudzysłowem zamykającym w celu umieszczenia odstępu między wyrazami „Dzisiaj jest” i „poniedziałek, 30 maj”. Ta formuła bez cudzysłowów wokół tekstu zwróci błąd #NAZWA?.

Czy w formule użyto więcej niż 64 funkcji?

W obrębie formuły można połączyć (zagnieździć) maksymalnie 64 poziomy funkcji.

Na przykład formuła =JEŻELI(PIERWIASTEK(PI())<2;"Mniej niż dwa!";"Więcej niż dwa!") ma 3 poziomy funkcji; funkcja PI jest zagnieżdżona w funkcji PIERWIASTEK, która z kolei jest zagnieżdżona w funkcji JEŻELI.

Czy nazwy arkuszy są ujęte w pojedynczy cudzysłów?

Gdy wpisujesz odwołanie do wartości lub komórek znajdujących się w innym arkuszu, którego nazwa zawiera znaki niealfabetyczne (na przykład spację), nazwę należy zamknąć w cudzysłowie pojedynczym (').

Aby na przykład zwrócić wartość z komórki D3 w arkuszu o nazwie Dane kwartalne w skoroszycie, wpisz ciąg ='Dane kwartalne'!D3. Bez cudzysłowów wokół nazwy arkusza formuła zwróci błąd #NAME?.

Aby odwołać się w formule do wartości lub komórek z innego arkusza, możesz też wybrać te wartości lub komórki. Gdy to zrobisz, program Excel automatycznie doda cudzysłowy wokół nazw arkuszy.

Poprawianie ścieżek skoroszytów zewnętrznych w formułach programu Excel

Gdy wpiszesz odwołanie do wartości lub komórek z innego skoroszytu, dołącz nazwę tego skoroszytu, umieszczając ją w nawiasach kwadratowych ([]), a po niej dołącz nazwę arkusza zawierającego te wartości lub komórki.

Na przykład, aby odwołać się do komórek z zakresu od A1 do A8 z arkusza Sprzedaż znajdującego się w otwartym w programie Excel skoroszycie Działalność w 2 kwartale, wpisz: =[Działalność w 2 Operations.xlsx]Sprzedaż! A1:A8. Bez nawiasów kwadratowych formuła zwróci błąd #REF!.

Jeśli skoroszyt nie jest otwarty w programie Excel, wpisz pełną ścieżkę do pliku.

Na przykład =ILE.WIERSZY('C:\Moje dokumenty\[Działalność w 2 kwartale.xlsx]Sprzedaż'!A1:A8).

Czy dzielisz wartości liczbowe przez zero?

Podzielenie komórki przez inną komórkę zawierającą wartość zero (0) lub niezawierającą żadnej wartości powoduje zwrócenie błędu #DZIEL/0!.

Aby uniknąć tego błędu, możesz odwołać się do niego bezpośrednio i sprawdzić, czy istnieje mianownik. Możesz użyć formuły:

=JEŻELI(B1;A1/B1;0)

Co oznacza, że JEŻELI(B1 istnieje, podziel A1 przez B1, a w przeciwnym razie zwróć wartość 0).

Czy formuła odwołuje się do usuniętych danych?

Zanim cokolwiek usuniesz, zawsze najpierw sprawdź, czy masz jakieś formuły odwołujące się do danych w komórkach, zakresach, nazwach zdefiniowanych, arkuszach lub skoroszytach. Następnie możesz zastąpić te formuły ich wynikami, zanim usuniesz dane, do których występują odwołania.

Jeśli nie możesz zastąpić formuł ich wynikami, przejrzyj poniższe informacje dotyczące błędów i możliwych rozwiązań:

• Jeśli formuła odwołuje się do komórek, które zostały usunięte lub zamienione na inne dane, i zwraca błąd #REF!, zaznacz komórkę z błędem #REF! . Na pasku formuły wybierz ciąg #ADR! i usuń go. Następnie ponownie wprowadź zakres dla formuły.
• Jeśli brakuje nazwy zdefiniowanej i formuła odwołująca się do tej nazwy zwróci błąd #NAZWA?, zdefiniuj nową nazwę odwołującą się do odpowiedniego zakresu lub zmień formułę tak, aby odwoływała się bezpośrednio do zakresu komórek (na przykład A2:D8).
• Jeśli brakuje arkusza i odwołująca się do niego formuła zwróci błąd #ADR! , niestety nie można rozwiązać tego problemu — nie można odzyskać usuniętego arkusza.
• Jeśli brakuje skoroszytu, odwołująca się do niego formuła pozostanie niezmieniona, dopóki nie zostanie zaktualizowana.
  Jeśli na przykład formuła ma postać =[Skoroszyt1.xlsx]Arkusz1'!A1 i nie masz już pliku Skoroszyt1.xlsx, to wartości z tego skoroszytu, do których w formule występują odwołania, pozostaną dostępne. W przypadku edytowania i próby zapisania formuły odwołującej się do tego skoroszytu w programie Excel zostanie jednak wyświetlone okno dialogowe Aktualizowanie wartości i monit o wprowadzenie nazwy pliku. Wybierz przycisk Anuluj, a następnie upewnij się, że te dane nie zostały utracone, zastępując formuły odwołujące się do brakującego skoroszytu wynikami tych formuł.

Czy skopiowano i wklejono komórki skojarzone z formułą znajdującą się w arkuszu kalkulacyjnym?

Czasami podczas kopiowania zawartości komórki chcesz wkleić tylko wartość, a nie zawartą w niej formułę wyświetlaną na pasku formuły.

Możesz na przykład chcieć skopiować wartość wynikową formuły do komórki w innym arkuszu. Po skopiowaniu wartości wynikowej do innej komórki w arkuszu możesz też usunąć wartości, których użyto w formule. Obie te czynności powodują wyświetlenie w komórce docelowej błędu nieprawidłowego odwołania do komórki (#REF!), ponieważ nie można już odwołać się do komórek zawierających wartości, których użyto w formule.

Można uniknąć tego błędu, wklejając wartości wynikowe formuł bez formuły w komórkach docelowych.

1. W arkuszu zaznacz komórki zawierające wartości wynikowe formuły, które chcesz skopiować.

2. Na karcie Narzędzia główne w grupie Schowek wybierz Kopiuj .
   
   Skrót klawiaturowy: naciśnij klawisze CTRL+C.

3. Zaznacz lewą górną komórkę obszaru wklejania.

   Porada

   Aby przenieść lub skopiować zaznaczenie do innego arkusza lub skoroszytu, kliknij kartę innego arkusza lub przełącz się do innego skoroszytu, a następnie zaznacz lewą górną komórkę obszaru wklejania.

4. Na karcie Narzędzia główne w grupie Schowek wybierz pozycję Wklej, a następnie wybierz pozycję Wklej wartości lub naciśnij klawisz Alt > E > S > V > Enter w systemie Windows lub Option > Command > V >> V Enter na komputerze Mac.

Jeśli masz formułę zagnieżdżoną, oszacuj ją krok po kroku

Aby dowiedzieć się, jak formuła złożona lub zagnieżdżona oblicza wynik końcowy, możesz oszacować tę formułę.

1. Zaznacz formułę, którą chcesz oszacować.

2. Wybieranie formuł Szacuj>formułę.
   

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

4. Jeśli podkreślona część formuły to odwołanie do innej formuły, kliknij przycisk 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 w przypadku drugiego wystąpienia tego odwołania w formule lub gdy formuła odwołuje się do komórki w innym skoroszycie.

5. Kontynuuj, aż każda z części formuły zostanie oszacowana.
   Narzędzie Szacuj formułę może nie podać przyczyny, dla której formuła jest niepoprawna, jednak może Cię naprowadzić na miejsce, w którym występuje błąd. To bardzo przydatne narzędzie w przypadku większych formuł, w których inaczej trudno byłoby znaleźć problem.

   Uwaga

   • Niektóre części funkcji JEŻELI i WYBIERZ nie będą oceniane, a w polu Oszacowanie może zostać wyświetlony błąd #N/D!.
   • Puste odwołania są wyświetlane w polu Oszacowanie jako wartości zerowe (0).
   • Niektóre funkcje, które są ponownie obliczane po każdej zmianie w arkuszu. Te funkcje, w tym funkcje LOS, OBSZARY, INDEKS, PRZESUNIĘCIE, KOMÓRKA, ADR.POŚR, ILE.WIERSZY, LICZBA.KOLUMN, TERAZ, DZIŚ i LOS.ZAKR, mogą powodować wyświetlanie w oknie dialogowym Szacowanie formuły wyników różniących się od rzeczywistych wyników w komórce w arkuszu.

Potrzebujesz dodatkowej pomocy?

Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel lub uzyskać pomoc techniczną w społecznościach.

Zobacz również

Omówienie formuł w programie Excel

Excel — pomoc & nauce