Jak unikać niepoprawnych formuł

Jak unikać niepoprawnych formuł

Uwaga: Staramy się udostępniać najnowszą zawartość Pomocy w Twoim języku tak szybko, jak to możliwe. Ta strona została przetłumaczona automatycznie i może zawierać błędy gramatyczne lub nieścisłości. Chcemy, aby ta zawartość była dla Ciebie przydatna. Prosimy o powiadomienie nas, czy te informacje były pomocne, u dołu tej strony. Oto angielskojęzyczny artykuł do wglądu.

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

Obraz okna dialogowego „Wystąpił problem z tą formułą” w programie Excel

Niestety oznacza to, że program Excel nie rozumie, co chcesz zrobić, dlatego dobrym rozwiązaniem może być opuszczenie tego miejsca i rozpoczęcie od nowa.

Zacznij od kliknięcia przycisku OK lub naciśnięcia klawisza ESC, aby zamknąć komunikat o błędzie.

Powrócisz do komórki z niepoprawną formułą, która ma być w trybie edycji, a program Excel wyróżni miejsce, w którym jest występuje problem. Jeśli nadal nie wiesz, co należy zrobić z tego miejsca i chcesz zacząć od początku, możesz ponownie naciśnij klawisz ESC lub kliknij przycisk Anuluj na pasku formuły zostanie przerwana poza trybem edycji.

Obraz przycisku Anuluj na pasku formuły

Jeśli nie masz pewności, co należy zrobić w tym momencie lub jakiej pomocy potrzebujesz, możesz wyszukać podobne pytania na forum społeczności programu Excel lub zadać własne.

Link do forum społeczności programu Excel

Jeśli chcesz ruszyć z miejsca, poniższa lista kontrolna zawiera kroki rozwiązywania problemów umożliwiające zidentyfikowanie prawdopodobnego błędu w formułach.

Program Excel wyświetla różne skrótu błędów (#), takich jak #VALUE!, #REF!, #NUM, # n/d!, #DIV/0!, #NAME? i #NULL!, aby wskazać coś w formule nie działa w prawo. Na przykład #VALUE! błąd spowodowany niepoprawne formatowanie lub nieobsługiwanych typów danych argumentów. Lub pojawi się #REF! błąd, gdy formuła odwołuje się do komórki, które zostały usunięte lub zastąpione innych danych. Rozwiązywanie problemów z orientacji będą różne dla każdego błędu.

Uwaga: #### nie jest błędem związanym z formułą. Oznacza po prostu, że kolumna nie jest wystarczająco szeroka, aby wyświetlić zawartość komórki. Wystarczy przeciągnąć kolumnę, aby ją poszerzyć, lub przejść do pozycji Narzędzia główne > Formatuj > Autodopasowanie szerokości kolumn.

Obraz pozycji Narzędzia główne > Formatowanie > Autodopasowanie szerokości kolumn

Zapoznaj się z dowolnym z poniższych tematów odpowiadającym wyświetlanemu błędowi z hasztagiem:

Zawsze podczas otwierania arkusza kalkulacyjnego, który zawiera formuły odwołujące się do wartości w innych arkuszach kalkulacyjnych, możesz zostanie wyświetlony monit o aktualizowanie odwołań lub pozostawianie jako-jest.

Okno dialogowe przerwanych odwołań w programie Excel

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 Plik > Opcje > Zaawansowane > Ogólne i wyczyść pole wyboru Pytaj przy automatycznym aktualizowaniu łączy. W programie Excel 2007 kliknij przycisk pakietu Office > Opcje programu Excel. Przycisk pakietu Office 2007

Obraz opcji Pytaj przy automatycznym aktualizowaniu łączy

Ważne: Jeśli pracujesz z przerwanymi linkami w formułach po raz pierwszy, chcesz przypomnieć sobie informacje dotyczące rozwiązywania problemów z przerwanymi linkami lub nie wiesz, czy należy zaktualizować odwołania, zobacz Określanie, kiedy mają być aktualizowane odwołania zewnętrzne (linki).

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 kliknij kartę Formuły i w grupie Inspekcja formuł kliknij pozycję Pokaż formuły.

    Porada: Możesz również użyć skrótu klawiaturowego klawisze Ctrl + ' (klawisz powyżej klawisza Tab). Po wykonaniu tej kolumny zostanie automatycznie poszerzanie do wyświetlenia formuły, ale nie martw się, w podczas przełączania wrócić do widoku normalnego, który będzie zmienić rozmiar kolumn.

  • Jeśli nadal powyższych czynności nie rozwiążą problemu, jest to możliwe, komórka jest sformatowana jako tekst. Możesz kliknij prawym przyciskiem myszy komórkę i wybierz pozycję Formatowanie komórek > Ogólne (lub klawisze Ctrl + 1), naciśnij klawisz F2 > Enter Aby zmienić format.

  • Jeśli masz duży zakres komórek w kolumnie, które zostały sformatowane jako tekst, a następnie można zaznaczyć zakres, stosowanie formatu liczb w wybranych przez Ciebie i przejdź do danych > Tekst do kolumny > Zakończ. Zastosuje formatowanie do wszystkich zaznaczonych komórek.

    Obraz okna dialogowego Dane > Tekst jako kolumny

Jeśli formuła nie są obliczane, musisz Sprawdź, czy automatyczne obliczanie jest włączona w programie Excel. Formuły nie będzie obliczać, jeśli jest włączone obliczania ręcznego. Wykonaj poniższe czynności, aby sprawdzić, czy Automatyczne obliczanie:

  1. Kliknij kartę Plik, kliknij pozycję Opcje, a następnie kliknij kategorię Formuły.

  2. W sekcji Opcje obliczania w obszarze Obliczanie skoroszytu upewnij się, że jest zaznaczona opcja Automatycznie.

    Obraz opcji obliczania automatycznego i ręcznego

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

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 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 takim przypadku należy włączyć opcję Obliczanie iteracyjne.

Aby uzyskać więcej informacji na temat odwołań cyklicznych, zobacz Znajdowanie i naprawianie odwołania cyklicznego.

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. Natomiast wpisanie ciągu 11/2 spowoduje, że 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

Podczas używania funkcji w formule pamiętaj, że każdy nawias otwierający wymaga nawiasu zamykającego, aby funkcja działała poprawnie, więc 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).

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)

Uwaga: W trakcie wpisywania funkcji zostaną wyświetlone jej argumenty widoczne na pływającym pasku narzędzi z opisem funkcji poniżej formuły.

Zrzut ekranu przedstawiający pasek narzędzi z opisem funkcji
Pasek narzędzi z opisem funkcji

Również niektóre funkcje, takie jak Suma, wymagają tylko argumenty liczbowe, natomiast innych funkcji, takich jak ZAMIENIĆ, wymagają wartość tekstową dla co najmniej jeden z jej argumentów. Użycie nieodpowiedniego typu funkcji może zwrócić nieoczekiwane wyniki lub Pokaż #VALUE! błędu.

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

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 (w przypadku używania angielskich formatów; w formułach utworzonych przy użyciu polskich formatów oczywiście można używać przecinków jako separatorów dziesiętnych). Zamiast wprowadzać $1,000 w formule, wpisz 1000.

Jeśli zostały użyte liczby sformatowane w argumentach, zostanie wyświetlony obliczeń nieoczekiwane wyniki, ale może także zostać wyświetlony #NUM! błędu. Na przykład po wprowadzeniu formuły =ABS(-2,134) , aby znaleźć wartość bezwzględną-2134, program Excel wyświetla #NUM! błąd, ponieważ Funkcja ABS akceptuje tylko jeden argument.

Uwaga: Możesz sformatować wynik formuły z separatorów tysięcy i waluty symbole po wprowadzeniu formuły przy użyciu niesformatowanych liczb (stałe). Ogólnie nie jest dobrym pomysłem jest umieszczenie stałych w formułach, ponieważ może być trudne do znalezienia Jeśli musisz zaktualizować później, i są one bardziej podatne na niepoprawnie wpisana. To dużo lepiej umieścić stałych w komórkach, gdzie się oni znajdują się w polu Otwórz i łatwo odwołania.

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. Kliknij pozycję Narzędzia główne > strzałka obok pozycji Format liczb (lub naciśnij klawisze Ctrl + 1) i kliknij 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, może być wyświetlana nie jako data, ale jako liczbowa wartość daty. Aby wyświetlić tę liczbę jako datę, w galerii Format liczb wybierz pozycję Data.

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

Okno komunikatu z monitem o zamianę znaku x na znak * na potrzeby mnożenia
Komunikat o błędzie w wyniku użycia do mnożenia stałych znaku x zamiast znaku * jako symbolu działania

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

Błąd #NAZWA? w wyniku użycia znaku x zamiast znaku * jako symbolu mnożenia w odwołaniach do komórek
Błąd #NAZWA? w wyniku użycia znaku x zamiast znaku * w odwołaniach do komórek

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?.

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!") zawiera 3 poziomy funkcji: funkcję PI zagnieżdżoną w funkcji PIERWIASTEK, która z kolei jest zagnieżdżona w funkcji JEŻELI.

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ść komórki D3 z arkusza Dane kwartalne w skoroszycie, wpisz ciąg ='Dane kwartalne'!D3. Ta formuła bez cudzysłowów wokół nazwy arkusza zwróci błąd #NAZWA?.

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

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 ciąg: =[Działalność w 2 kwartale.xlsx]Sprzedaż!A1:A8. Bez nawiasów kwadratowych formuła zwróci błąd #ADR!.

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).

Uwaga:  Jeśli pełna ścieżka zawiera znaki spacji, ujmij ją w pojedynczy cudzysłów (ten znak wstaw na początku ścieżki i po nazwie skoroszytu, przed wykrzyknikiem).

Porada: Najprostszym sposobem na uzyskiwanie ścieżkę do innego skoroszytu jest otwarcie innego skoroszytu, następnie z oryginalnego skoroszytu, wpisz =, a następnie przesuń do innego skoroszytu i zaznacz dowolną komórkę w arkuszu, który ma być za pomocą Klawiszy Alt + Tab . Zamknij skoroszyt źródłowy. Formuła zostanie automatycznie zaktualizowany, aby wyświetlić pełną ścieżkę i arkusza nazwę pliku wraz z wymaganej składni. Można nawet kopiować i wklej ścieżkę i używać w miejsce, w którym będzie potrzebny.

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.

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

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

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 zastąpione innymi danymi, i zwraca błąd #ADR!, zaznacz komórkę z błędem #ADR!. Na pasku formuły zaznacz 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. Kliknij przycisk Anuluj, a następnie upewnij się, że te dane nie zostaną utracone, zastępując formuły odwołujące się do brakującego skoroszytu wynikami tych formuł.

Czasami podczas kopiowania zawartości komórki chcesz wkleić tylko wartości, a nie zawarte w nich formuły wyświetlane 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 (#ADR!), 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 kliknij przycisk Kopiuj Obraz przycisku .

    Obraz Wstążki programu Excel

    Skrót klawiaturowy: naciśnij klawisze CTRL+C.

  3. Zaznacz lewą górną komórkę obszar 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 kliknij pozycję Wklej Obraz przycisku , a następnie kliknij pozycję Wklej wartości, lub naciśnij klawisze Alt > H > V > W > W > Enter w systemie Windows albo Opcja > Command > V > V > Enter na komputerze Mac.

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. Kliknij pozycję Formuły > Szacuj formułę.

    Grupa Inspekcja formuł na karcie Formuły

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

    Okno dialogowe Szacowanie formuły

  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łę nie będzie konieczne informujący o formuły zostało przerwane, ale może ułatwić wskazać miejsce. Może to być bardzo przydatne narzędzie w formułach większe gdy w przeciwnym razie jest trudny do znalezienia problemu.

    Uwagi: 

    • Niektóre części funkcji JEŻELI i WYBIERZ nie są obliczane i 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).

    • 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, uzyskać pomoc techniczną w społeczności witryny Answers bądź zasugerować nową funkcję lub ulepszenie w witrynie UserVoice dotyczącej programu Excel.

Zobacz też

Omówienie formuł w programie Excel

Wykrywanie błędów w formułach

Funkcje programu Excel (lista alfabetyczna)

Funkcje programu Excel (według kategorii)

Rozwijaj umiejętności związane z pakietem Office
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

Dziękujemy za opinię!

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×