Formuły Excela nie działają: możliwe przyczyny i jak to naprawić!

Jeśli pracujesz z formułami w programie Excel, prędzej czy później napotkasz problem polegający na tym, że formuły programu Excel w ogóle nie działają (lub dają zły wynik).

Chociaż byłoby świetnie, gdyby istniało tylko kilka możliwych przyczyn nieprawidłowego działania formuł. Niestety zbyt wiele rzeczy może się nie udać (i często tak się dzieje).

Ale ponieważ żyjemy w świecie zgodnym z zasadą Pareto, jeśli sprawdzisz niektóre typowe problemy, prawdopodobnie rozwiąże to 80% (a może nawet 90% lub 95% problemów, w których formuły nie działają w programie Excel).

W tym artykule przedstawię te typowe problemy, które prawdopodobnie są przyczyną twojego Formuły Excela nie działają.

Więc zacznijmy!

Nieprawidłowa składnia funkcji

Zacznę od zwrócenia uwagi na oczywiste.

Każda funkcja w programie Excel ma określoną składnię - na przykład liczbę argumentów, które może przyjąć lub typ argumentów, które może zaakceptować.

W wielu przypadkach powodem, dla którego formuły programu Excel nie działają lub dają zły wynik, może być niepoprawny argument (lub brakujące argumenty).

Na przykład funkcja WYSZUKAJ.PIONOWO przyjmuje trzy argumenty obowiązkowe i jeden argument opcjonalny.

Jeśli podasz zły argument lub nie określisz argumentu opcjonalnego (gdzie jest on potrzebny do działania formuły), otrzyma zły wynik.

Załóżmy na przykład, że masz zestaw danych, jak pokazano poniżej, w którym musisz znać wynik oceny z egzaminu 2 (w komórce F2).

Jeśli użyję poniższej formuły, otrzymam zły wynik, ponieważ używam niewłaściwej wartości w trzecim argumencie (takim, który pyta o numer indeksu kolumny).

=WYSZUKAJ.PIONOWO(A2;A2:C6;2;FAŁSZ)

W tym przypadku formuła oblicza (ponieważ zwraca wartość), ale wynik jest niepoprawny (zamiast wyniku z egzaminu 2, daje wynik z egzaminu 1).

Innym przykładem, w którym należy zachować ostrożność w odniesieniu do argumentów, jest użycie funkcji WYSZUKAJ.PIONOWO z przybliżonym dopasowaniem.

Ponieważ musisz użyć opcjonalnego argumentu, aby określić, gdzie funkcja WYSZUKAJ.PIONOWO ma wykonać dokładne lub przybliżone dopasowanie, nieokreślenie tego (lub użycie niewłaściwego argumentu) może powodować problemy.

Poniżej znajduje się przykład, w którym mam dane dotyczące ocen dla niektórych uczniów i chcę wyodrębnić oceny z egzaminu 1 dla uczniów w tabeli po prawej stronie.

Kiedy używam poniższej formuły WYSZUKAJ.PIONOWO, pojawia się błąd dla niektórych nazw.

=WYSZUKAJ.PIONOWO(E2,$A$2:$C$6,2)

Dzieje się tak, ponieważ nie określiłem ostatniego argumentu (który jest używany do określenia, czy wykonać dopasowanie dokładne, czy przybliżone). Jeśli nie określisz ostatniego argumentu, domyślnie automatycznie wykonuje on przybliżone dopasowanie.

Ponieważ w tym przypadku musieliśmy wykonać dokładne dopasowanie, formuła zwraca błąd dla niektórych nazw.

Chociaż wziąłem przykład funkcji WYSZUKAJ.PIONOWO, w tym przypadku jest to coś, co może mieć zastosowanie do wielu formuł programu Excel, które mają również opcjonalne argumenty.

Przeczytaj także: Identyfikuj błędy za pomocą debugowania formuł programu Excel

Dodatkowe przestrzenie powodujące nieoczekiwane wyniki

Spacje wiodące i końcowe są trudne do znalezienia i mogą powodować problemy, gdy używasz komórki, która ma je w formułach.

Na przykład w poniższym przykładzie, jeśli spróbuję użyć funkcji WYSZUKAJ.PIONOWO do pobrania wyniku dla Marka, otrzymam błąd #N/A (błąd niedostępny).

Chociaż widzę, że formuła jest poprawna, a nazwa „Mark” wyraźnie znajduje się na liście, to nie widzę, że w komórce jest spacja, która ma nazwę (w komórce D2).

Program Excel nie uważa zawartości tych dwóch komórek za taką samą i dlatego uważa ją za niezgodność podczas pobierania wartości za pomocą funkcji WYSZUKAJ.PIONOWO (lub może to być dowolna inna formuła wyszukiwania).

Aby rozwiązać ten problem, musisz usunąć te dodatkowe znaki spacji.

Możesz to zrobić, korzystając z dowolnej z następujących metod:

1. Wyczyść komórkę i usuń wszelkie spacje wiodące/końcowe przed użyciem jej w formułach
2. Użyj funkcji TRIM w formule, aby upewnić się, że wszystkie spacje wiodące/końcowe/podwójne są ignorowane.

W powyższym przykładzie możesz zamiast tego użyć poniższej formuły, aby upewnić się, że działa.

=WYSZUKAJ.PIONOWO(PRZYC.(D2);$A$2:$B$6,2,0)

Chociaż wziąłem przykład WYSZUKAJ.PIONOWO, jest to również częsty problem podczas pracy z funkcjami TEKST.

Na przykład, jeśli użyję funkcji LEN do zliczenia całkowitej liczby znaków w komórce, jeśli występują spacje wiodące lub końcowe, zostaną one również policzone i dadzą zły wynik.

Zabierz / Jak naprawić: Jeśli to możliwe, wyczyść dane, usuwając wszelkie początkowe/końcowe lub podwójne spacje przed użyciem ich w formułach. Jeśli nie możesz zmienić oryginalnych danych, użyj funkcji TRIM w formułach, aby się tym zająć.

Używanie obliczeń ręcznych zamiast automatycznych

To jedno ustawienie może doprowadzić Cię do szaleństwa (jeśli nie wiesz, co powoduje wszystkie problemy).

Excel ma dwa tryby obliczeń - Automatyczny oraz Podręcznik.

Domyślnie włączony jest tryb automatyczny, co oznacza, że ​​jeśli użyję formuły lub dokonam jakichkolwiek zmian w istniejących formułach, automatycznie (i natychmiast) dokona obliczenia i poda wynik.

To jest ustawienie, do którego wszyscy jesteśmy przyzwyczajeni.

W ustawieniu automatycznym za każdym razem, gdy dokonasz jakiejkolwiek zmiany w arkuszu (takiej jak wprowadzenie nowej formuły do ​​nawet jakiegoś tekstu w komórce), program Excel automatycznie przeliczy wszystko (tak, wszystko).

Ale w niektórych przypadkach ludzie włączają ręczne ustawienie obliczeń.

Odbywa się to najczęściej, gdy masz ciężki plik Excela z dużą ilością danych i formuł. W takich przypadkach możesz nie chcieć, aby program Excel ponownie obliczał wszystko po wprowadzeniu niewielkich zmian (ponieważ może to potrwać kilka sekund lub nawet minut), aby to ponowne obliczenie zostało ukończone.

Jeśli włączysz obliczenia ręczne, program Excel nie wykona obliczeń, chyba że wymusisz to.

A to może sprawić, że pomyślisz, że twoja formuła nie oblicza.

Wszystko, co musisz zrobić w tym przypadku, to albo ustawić obliczenia z powrotem na automatyczne, albo wymusić ponowne obliczenie, naciskając klawisz F9.

Poniżej znajdują się kroki, aby zmienić obliczenia z ręcznych na automatyczne:

1. Kliknij kartę Formuła
2. Kliknij Opcje obliczeń
3. Wybierz Automatycznie

Ważne: Jeśli zmieniasz obliczenia z ręcznego na automatyczne, dobrym pomysłem jest utworzenie kopii zapasowej skoroszytu (na wypadek, gdyby spowodowało to zawieszenie się skoroszytu lub awarię programu Excel)

Zabierz / Jak naprawić: Jeśli zauważysz, że twoje formuły nie dają oczekiwanych wyników, spróbuj czegoś prostego w dowolnej komórce (na przykład dodanie 1 do istniejącej formuły. Po zidentyfikowaniu problemu jako tego, w którym należy zmienić tryb obliczania, wykonaj obliczenie siły za pomocą klawisza F9.

Usuwanie wierszy/kolumn/komórek prowadzących do #REF! Błąd

Jedną z rzeczy, które mogą mieć niszczący wpływ na istniejące formuły w programie Excel, jest usunięcie dowolnego wiersza/kolumny, które zostały użyte w obliczeniach.

W takim przypadku czasami program Excel dostosowuje samo odwołanie i upewnia się, że formuły działają poprawnie.

I czasami… nie może.

Na szczęście jedną wyraźną wskazówką, którą otrzymujesz, gdy formuły przestają działać podczas usuwania komórek/wierszy/kolumn, jest #REF! błąd w komórkach. Jest to błąd odwołania, który informuje o problemie z odwołaniami w formule.

Pokażę ci, co mam na myśli na przykładzie.

Poniżej użyłem formuły SUMA, aby dodać komórki A2:A6.

Teraz, jeśli usunę którąkolwiek z tych komórek/wierszy, formuła SUMA zwróci #REF! błąd. Dzieje się tak, ponieważ po usunięciu wiersza formuła nie wie, do czego się teraz odwołać.

Widać, że trzeci argument w formule zmienił się na #REF! (co wcześniej odnosiło się do komórki, którą usunęliśmy).

Zabierz / Jak naprawić: przed usunięciem jakichkolwiek danych używanych w formułach upewnij się, że po usunięciu nie ma błędów. Zaleca się również regularne tworzenie kopii zapasowej swojej pracy, aby mieć pewność, że zawsze masz na czym polegać.

Nieprawidłowe umieszczenie nawiasów (BODMAS)

Gdy Twoje formuły stają się coraz większe i bardziej złożone, dobrym pomysłem jest użycie nawiasów, aby mieć absolutną jasność, która część należy do siebie.

W niektórych przypadkach możesz umieścić nawias w niewłaściwym miejscu, co może dać zły wynik lub błąd.

W niektórych przypadkach zaleca się użycie nawiasów, aby upewnić się, że formuła rozumie, co należy najpierw pogrupować i obliczyć.

Załóżmy na przykład, że masz następującą formułę:

=5+10*50

W powyższym wzorze wynik to 505, ponieważ Excel najpierw mnoży, a następnie dodaje (ponieważ istnieje kolejność pierwszeństwa, jeśli chodzi o operatory).

Jeśli chcesz, aby najpierw wykonał dodawanie, a następnie mnożenie, musisz skorzystać z poniższej formuły:

=(5+10)*50

W niektórych przypadkach kolejność pierwszeństwa może działać dla Ciebie, ale nadal zaleca się stosowanie nawiasów, aby uniknąć nieporozumień.

Ponadto, jeśli jesteś zainteresowany, poniżej znajduje się kolejność pierwszeństwa różnych operatorów często używanych w formułach:

Operator	Opis	Porządek pierwszeństwa
: (okrężnica)	Zasięg	1
(pojedyncza przestrzeń)	Skrzyżowanie	2
, (przecinek)	unia	3
-	Negacja (jak w -1)	4
%	Odsetek	5
^	Potęgowanie	6
* oraz /	Mnożenie i dzielenie	7
+ i -	Dodawanie odejmowanie	8
&	Łączenie	9
= =	Porównanie	10

Zabierz / Jak naprawić: Zawsze używaj nawiasów, aby uniknąć nieporozumień, nawet jeśli znasz kolejność pierwszeństwa i używasz jej poprawnie. Posiadanie nawiasów ułatwia kontrolę formuł.

Nieprawidłowe użycie bezwzględnych/względnych odniesień do komórek

Gdy kopiujesz i wklejasz formuły w programie Excel, automatycznie dostosowuje on odwołania. Czasami jest to dokładnie to, czego chcesz (głównie podczas kopiowania i wklejania formuł w dół kolumny), a czasami nie chcesz, aby tak się stało.

Odwołanie bezwzględne ma miejsce wtedy, gdy naprawiasz odwołanie do komórki (lub odwołanie do zakresu), tak aby nie zmieniało się podczas kopiowania i wklejania formuł, a odwołanie względne to takie, które się zmienia.

Możesz przeczytać więcej o odniesieniach bezwzględnych, względnych i mieszanych tutaj.

Możesz otrzymać błędny wynik, jeśli zapomnisz zmienić referencję na bezwzględną (lub odwrotnie). To jest coś, co zdarza mi się dość często, gdy używam formuł wyszukiwania.

Pokażę ci przykład.

Poniżej mam zestaw danych, w którym chcę pobrać wynik z egzaminu 1 dla nazw w kolumnie E (prosty przypadek użycia WYSZUKAJ.PIONOWO)

Poniżej znajduje się formuła, której używam w komórce F2, a następnie kopiuję do wszystkich komórek poniżej:

=WYSZUKAJ.PIONOWO(E2,A2:B6,2,0)

Jak widać, ta formuła w niektórych przypadkach daje błąd.

Dzieje się tak, ponieważ nie zablokowałem argumentu tablicy tabeli - jest A2:B6 w komórce F2, podczas gdy powinno być $A$2:$B$6

Mając te znaki dolara przed numerem wiersza i alfabetem kolumny w odwołaniu do komórki, zmuszam program Excel do utrwalenia tych odwołań do komórek. Tak więc, nawet gdy skopiuję tę formułę w dół, tablica tabeli będzie nadal odnosić się do A2:B6

Pro Porada: Aby przekonwertować odwołanie względne na bezwzględne, zaznacz to odwołanie do komórki w komórce i naciśnij klawisz F4. Zauważysz, że zmienia się, dodając znaki dolara. Możesz kontynuować naciskanie F4, aż uzyskasz żądane odniesienie.

Nieprawidłowe odniesienie do nazw arkuszy / skoroszytów

Odwołując się do innych arkuszy lub skoroszytów w formule, musisz przestrzegać określonego formatu. A jeśli format jest niepoprawny, otrzymasz błąd.

Na przykład, jeśli chcę odwołać się do komórki A1 w arkuszu 2, odniesienie będzie =Arkusz2!A1 (gdzie jest wykrzyknik po nazwie arkusza)

A w przypadku, gdy w nazwie arkusza znajduje się wiele słów (powiedzmy, że są to dane przykładowe), odniesienie będzie =’Przykładowe dane’!A1 (gdzie nazwa jest ujęta w pojedyncze cudzysłowy, po których następuje znak wykrzyknika).

Jeśli odwołujesz się do zewnętrznego skoroszytu (załóżmy, że odwołujesz się do komórki A1 w „Przykładowym arkuszu” w skoroszycie o nazwie „Przykładowy skoroszyt”), odniesienie będzie wyglądać tak, jak pokazano poniżej:

='[Przykładowy skoroszyt.xlsx]Przykładowy arkusz'!$A$1

A jeśli zamkniesz skoroszyt, odwołanie zmieni się tak, aby obejmowało całą ścieżkę skoroszytu (jak pokazano poniżej):

='C:\Users\sumit\Desktop\[Przykładowy skoroszyt.xlsx]Przykładowy arkusz'!$A$1

Jeśli zmienisz nazwę skoroszytu lub arkusza roboczego, do którego odnosi się formuła, otrzymasz #REF! błąd.

Przeczytaj także: Jak znaleźć linki zewnętrzne i odniesienia w programie Excel

Referencje okólnikowe

Odwołanie cykliczne ma miejsce wtedy, gdy odwołujesz się (bezpośrednio lub pośrednio) do tej samej komórki, w której obliczana jest formuła.

Poniżej znajduje się prosty przykład, w którym używam formuły SUMA w komórce A4, używając jej w samych obliczeniach.

=SUMA(A1:A4)

Chociaż program Excel wyświetla monit informujący o odwołaniu cyklicznym, nie zrobi tego dla każdego wystąpienia. A to może dać zły wynik (bez ostrzeżenia).

Jeśli podejrzewasz, że w grze występuje odwołanie cykliczne, możesz sprawdzić, które komórki je mają.

Aby to zrobić, kliknij kartę Formuła i w grupie „Kontrola formuł” kliknij rozwijaną ikonę Sprawdzanie błędów (mała strzałka skierowana w dół).

Najedź kursorem na opcję Odwołanie cykliczne, a wyświetli się komórka, w której występuje problem z odwołaniem cyklicznym.

Komórki sformatowane jako tekst

Jeśli znajdziesz się w sytuacji, w której po wpisaniu formuły po naciśnięciu klawisza Enter zobaczysz formułę zamiast wartości, oznacza to, że komórka jest sformatowana jako tekst.

Gdy komórka jest sformatowana jako tekst, formuła jest traktowana jako ciąg tekstowy i jest wyświetlana bez zmian.

Nie zmusza go do obliczenia i podania wyniku.

I ma łatwe rozwiązanie.

1. Zmień format na „Ogólny” z „Tekst” (znajduje się na karcie Strona główna w grupie Liczby)
2. Przejdź do komórki zawierającej formułę, przejdź do trybu edycji (użyj F2 lub kliknij dwukrotnie komórkę) i naciśnij Enter

Jeśli powyższe kroki nie rozwiążą problemu, kolejną rzeczą do sprawdzenia jest to, czy komórka ma na początku apostrof. Wiele osób dodaje apostrof, aby konwertować formuły i liczby na tekst.

Jeśli jest apostrof, możesz go po prostu usunąć.

Tekst automatycznie konwertowany na daty

Excel ma ten zły nawyk konwertowania, który wygląda jak data na rzeczywistą datę. Na przykład, jeśli wprowadzisz 1/1, program Excel przekonwertuje go na 01 stycznia bieżącego roku.

W niektórych przypadkach może to być dokładnie to, czego chcesz, aw niektórych może to działać przeciwko tobie.

A ponieważ Excel przechowuje wartości daty i godziny jako liczby, zaraz po wprowadzeniu 1/1 konwertuje je na liczbę reprezentującą 1 stycznia bieżącego roku. W moim przypadku, kiedy to robię, konwertuje go na numer 43831 (na 01-01-2020).

Może to zepsuć formuły, jeśli używasz tych komórek jako argumentu w formule.

Jak to naprawić?

Ponownie, nie chcemy, aby Excel automatycznie wybierał dla nas format, więc musimy sami jasno określić format.

Poniżej znajdują się kroki, aby zmienić format na tekst, aby nie konwertował automatycznie tekstu na daty:

1. Wybierz komórki/zakres, w którym chcesz zmienić format
2. Kliknij kartę Strona główna
3. W grupie Numer kliknij menu rozwijane Format
4. Kliknij Tekst

Teraz za każdym razem, gdy wprowadzisz cokolwiek w zaznaczonych komórkach, zostanie to uznane za tekst i nie zostanie zmienione automatycznie.

Uwaga: powyższe kroki działają tylko w przypadku danych wprowadzonych po zmianie formatowania. Nie zmieni żadnego tekstu, który został przekonwertowany do tej pory przed wprowadzeniem tej zmiany formatowania.

Innym przykładem, w którym może to być naprawdę frustrujące, jest wprowadzenie tekstu/liczby z wiodącymi zerami. Program Excel automatycznie usuwa te wiodące zera, ponieważ uważa je za bezużyteczne. Na przykład, jeśli wpiszesz 0001 w komórce, program Excel zmieni ją na 1. Jeśli chcesz zachować te wiodące zera, wykonaj powyższe kroki.

Ukryte wiersze/kolumny mogą dawać nieoczekiwane wyniki

Nie jest to przypadek formuły dającej zły wynik, ale użycie niewłaściwej formuły.

Załóżmy na przykład, że masz zestaw danych, jak pokazano poniżej, i chcę uzyskać sumę wszystkich widocznych komórek w kolumnie C.

W komórce C12 użyłem funkcji SUMA, aby uzyskać całkowitą wartość sprzedaży dla wszystkich podanych rekordów.

Na razie w porządku!

Teraz stosuję filtr do kolumny pozycji, aby wyświetlić tylko rekordy dotyczące sprzedaży drukarek.

I tu pojawia się problem - formuła w komórce C12 nadal pokazuje ten sam wynik - czyli sumę wszystkich rekordów.

Jak powiedziałem, formuła nie daje złych wyników. W rzeczywistości funkcja SUMA działa dobrze.

Problem polega na tym, że zastosowaliśmy tutaj złą formułę.

Funkcja SUMA nie może uwzględniać przefiltrowanych danych i podawać wynik dla wszystkich komórek (ukrytych lub widocznych). Jeśli chcesz uzyskać tylko sumę / liczbę / średnią widocznych komórek, użyj funkcji SUMA CZĘŚCIOWA lub AGREGACJA.

Zabrany klucz - Zapoznaj się z poprawnym użyciem i ograniczeniami funkcji w programie Excel.

Oto niektóre z najczęstszych przyczyn, które widziałem, gdzie twój Formuły Excela mogą nie działać lub dawać nieoczekiwane lub błędne wyniki. Jestem pewien, że istnieje wiele innych powodów, dla których formuła programu Excel nie działa lub nie aktualizuje się.

Jeśli znasz jakiś inny powód (może coś, co często Cię irytuje), daj mi znać w sekcji komentarzy.

Mam nadzieję, że ten samouczek okazał się przydatny!