Jak unikać niepoprawnych formuł

Jak unikać niepoprawnych formuł

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 próbujesz zrobić, więc możesz po prostu zacząć od początku.

Zacznij od wybrania przycisku OK lub naciśnij klawisz ESC, aby zamknąć komunikat o błędzie.

Powrócisz do komórki z przerwaną 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, co spowoduje rezygnację z trybu edycji.

Obraz przycisku Anuluj na pasku formuły

Jeśli chcesz przejść do przodu, na poniższej liście kontrolnej przedstawiono kroki rozwiązywania problemów, które ułatwiają zorientowanie się, co może wystąpić źle.

Uwaga: Jeśli korzystasz z programuOffice dla sieci Web, mogą nie zostać wyświetlony te same błędy lub rozwiązania mogą nie zostać zastosować.

Program Excel zgłasza różne błędy funta (#), takie jak #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? i #NULL!, aby wskazać, że coś w formule nie działa poprawnie. Na przykład #VALUE! spowodowany nieprawidłowym formatowaniem lub nieobsługiwanymi typami danych w argumentach. Zostanie też wyświetlony #REF! jeśli formuła odwołuje się do komórek, które zostały usunięte lub zamienione na inne dane. Wskazówki dotyczące rozwiązywania problemów będą się różnić w przypadku 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 następujących tematów związanych z wyświetlonym błędem w funtie:

Przy każdym otwarciu 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 pozostawienie ich bez opuszczania.

Okno dialogowe przerwanych odwołań w programie Excel

Program Excel wyświetli powyższe okno dialogowe, aby upewnić się, że formuły w bieżącym arkuszu kalkulacyjnym zawsze wskazują na wartość najbardziej aktualną na wypadek zmiany wartości odwołania. 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. W tym celu przejdź do menu Opcje plików > > Zaawansowane> Ogólne i wyczyść pole wyboru Pytaj przy automatycznym aktualizowaniu łączy.

Obraz opcji Pytaj przy automatycznym aktualizowaniu łączy

Ważne: Jeśli pracujesz z przerwanym linkiem w formułach po raz pierwszy, chcesz odświeżyć informacje na temat rozwiązywania problemów z przerwanym linkiem lub nie wiesz, czy zaktualizować odwołania, zobacz "Sterowanie" podczas aktualizowania odwołań zewnętrznych (linków).

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

  • Upewnij się, że program Excel jest ustawiony tak, aby wyświetlać formuły w arkuszu kalkulacyjnym. W tym celu wybierz kartę Formuły, a następnie w grupie Inspekcja formuł wybierz pozycję Pokaż formuły.

    Porada: Możesz również użyć skrótu klawiaturowego Ctrl + ' (klawisz powyżej klawisza Tab). Gdy to zrobisz, kolumny zostaną automatycznie poszerzane w celu wyświetlenia formuł, ale nie martw się, gdy przełączysz się z powrotem do widoku normalnego, rozmiar kolumn zostanie zmieniony.

  • Jeśli powyższy krok nadal nie rozwiąże problemu, możliwe, że komórka jest sformatowana jako tekst. Kliknij prawym przyciskiem myszy komórkę, a następnie wybierz pozycję Formatuj komórki > Ogólne (lub naciśnij klawisze Ctrl + 1), a następnie naciśnij klawisze F2 > Enter, aby zmienić formatowanie.

  • Jeśli masz kolumnę z dużym zakresem komórek sformatowanych jako tekst, możesz zaznaczyć zakres, zastosować format liczbowy i przejść do pola Tekst > Tekst jako kolumny, a następnie > Zakończ. W ten sposób zostaną sformatowane wszystkie zaznaczone komórki.

    Obraz okna dialogowego Dane > Tekst jako kolumny

Jeśli 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 nie ma pola Obliczanie automatyczne.

  1. Wybierz kartę Plik,wybierz pozycję Opcje,a następnie wybierz 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 ponownego obliczania,iteracji lub dokładności formuły.

Odwołanie cykliczne występuje, gdy formuła odwołuje się do komórki, w których się znajduje. Poprawka ta pozwala przenieść formułę do innej komórki lub zmienić składnię formuły na inną, 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ć funkcję Usuń lub zezwolić na odwołanie cykliczne.

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

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. Alternatywnie, jeśli wpiszemy 2/11,program Excel pokaże datę, taką jak 2-lis lub 11/02/2009, zamiast podzielić 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. 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 nawias 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: Podczas wpisywania argumentów funkcji są wyświetlane na przestawnych paskach narzędzi funkcji poniżej formuły.

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

Ponadto niektóre funkcje, na przykład SUMA, wymagają tylko argumentów liczbowych, natomiast inne funkcje, na przykład ZAMIEŃ, 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 #VALUE!.

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 ($) ani separatorów dziesiętnych (,), ponieważ znaki dolara wskazują odwołania bezwzględne, a przecinki są separatorami argumentów. Zamiast wprowadzać $1,000 w formule, wpisz 1000.

Jeśli w argumentach używasz liczb sformatowanych, zostaną uzyskane nieoczekiwane wyniki obliczeń, ale może również zostać wyświetlony #NUM!. Na przykład po wprowadzeniu formuły =ABS(-2;134) w celu znalezienia wartości bezwzględnej -2134 program Excel #NUM! ponieważ funkcja ABS przyjmuje tylko jeden argument i widzi wartości -2 i 134 jako osobne argumenty.

Uwaga: Wynik formuły można sformatować przy użyciu separatorów dziesiętnych i symboli walut po wprowadzeniu formuły przy użyciu liczb niesformatowanych (stałych). Zwykle nie warto umieszczać stałych w formułach, ponieważ w razie konieczności ich późniejszej aktualizacji mogą być trudne do znalezienia i są one bardziej podatne na błędy wpisywania. Znacznie lepiej jest umieścić stałe w komórkach, gdzie są one otwarte i można do nich łatwo się odwoływać.

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 domowe i wybierz strzałkę, aby rozwinąć grupęFormat liczby lub liczby (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, 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 jako operatora mnożenia w formule używa się znaku x, ale w programie Excel do mnożenia jest akceptowana tylko gwiazdka (*). Jeśli w formule używasz stałych, program Excel wyświetla komunikat o błędzie i może naprawić formułę, zamieniając znak x na gwiazdkę (*).

Okno komunikatu z monitem o zamianę znaku x na znak * na potrzeby mnożenia

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

Błąd #NAZWA? w wyniku użycia znaku x zamiast znaku * jako symbolu mnożenia 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 słowo "Dzisiaj jest" zawiera spację przed cudzysłowem kończącym w celu zapewnienia odstępu między wyrazami "Dzisiaj jest" i "Poniedziałek, 30 maja". Formuła bez cudzysłowów wokół tekstu może wyświetlać #NAME?.

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

Na przykład formuła =JEŻELI(PIERWIASTY(PI())<2;"Mniej niż dwa!";"Więcej niż dwa!") ma 3 poziomy funkcji; Funkcja PI jest zagnieżdżona w funkcji PIERWIASTY, 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ść z komórki D3 w arkuszu Dane kwartalne w skoroszycie, wpisz: ='Dane kwartalne'! D3. Formuła bez cudzysłowów wokół nazwy arkusza wyświetla #NAME?.

Możesz również zaznaczyć wartości lub komórki w innym arkuszu, aby odwołać się do nich w formule. 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.

Aby na przykład odwoływać się do komórek od A1 do A8 w arkuszu Sprzedaż w skoroszycie Operacje w drugim kwartale otwartym w programie Excel, wpisz: =[K2 Operations.xlsx]Sprzedaż! A1:A8. Bez nawiasów kwadratowych formuła wyświetla 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).

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: Najłatwiejszym sposobem uzyskania ścieżki do innego skoroszytu jest otwarcie innego skoroszytu, a następnie wpisanie =i przejście do innego skoroszytu za pomocą klawiszy Alt+Tab. Zaznacz dowolną komórkę w arkuszu, a następnie zamknij skoroszyt źródłowy. Formuła zostanie automatycznie zaktualizowana, aby była widoczna pełna ścieżka i nazwa arkusza wraz z wymaganą składnią. Możesz nawet kopiować i wklejać ścieżkę oraz używać jej, gdy tylko chcesz.

Podzielenie komórki przez inną komórkę, która ma wartość zero (0) lub nie powoduje żadnej wartości, powoduje #DIV/0!.

Aby uniknąć tego błędu, możesz odwołać się do niego bezpośrednio i sprawdzić, czy istnieje mianownik. Możesz uż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).

Zanim cokolwiek usuniesz, zawsze 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, a jeśli zwraca #REF!zaznacz komórkę z #REF! . Na pasku formuły wybierz pozycję #REF! i usuń ją. Następnie ponownie wprowadź zakres formuły.

  • Jeśli brakuje zdefiniowanej nazwy, a formuła, która się do tej nazwy odwołuje, zwraca #NAME?zdefiniuj nową nazwę odwołującą się do tego 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, a formuła, która się do niego odwołuje, zwraca #REF! 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 przyciskAnuluj, 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 formuły.

Czasami podczas kopiowania zawartości komórki chcesz wkleić tylko wartość, a nie formułę, która jest wyświetlana w pasek formuły.

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

Ten błąd można uniknąć, wklejając w komórkach docelowych wartości wynikowe formuł bez formuły.

  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 pozycję 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, wybierz 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 Obraz przycisku , a następnie wybierz pozycję Wklej wartości lub naciśnij klawisze Alt > E > S > V > Enter dla systemu Windows lub Command > Opcja > 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. Wybierz pozycję Formuły >Szacowanie formuły.

    Grupa Inspekcja formuł na karcie Formuły

  3. Wybierz pozycję Szacowanie, 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 jest odwołaniem do innej formuły, wybierz pozycję Krok w celu pokazania drugiej formuły w polu Oceny. Wybierz pozycję Wychodz, aby wrócić do poprzedniej komórki i formuły.

    Przycisk Krok w nie jest dostępny po raz drugi, gdy odwołanie występuje w formule — lub jeśli 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 Szacowanie formuły może nie wskazać przyczyny, dla której formuła jest uszkodzona, ale może ułatwić wskazanie miejsca, w którym się znajduje. To bardzo przydatne narzędzie w przypadku większych formuł, w których inaczej trudno byłoby znaleźć problem.

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

    • Niektóre funkcje są ponownie obliczanie za każdym razem, gdy zmienia się arkusz. 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ści witryny Answers.

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)

Potrzebna dalsza pomoc?

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?

×