„O mój Boże” formuły Excela nie działają w moim raporcie.
Brzmi znajomo prawda?
Jeśli tak, to nie martw się, jesteś jednym z wielu użytkowników Excela, którzy borykają się z tym problemem bardzo często.
Pomyśl tylko o tym.
Zbudowałeś model Excela dla Prognozy Sprzedaży używając różnych logik z dość skomplikowanymi formułami.
A kiedy próbujesz użyć tego modelu, widzisz, że niektóre z Twoich formuł albo nie działają, albo wyglądają na uszkodzone.
Podstawowo, dwie rzeczy mogą się tutaj wydarzyć:
Po pierwsze, możesz chcieć powiedzieć coś głośno .
Po drugie, włożysz dodatkowy wysiłek i naprawisz wszystkie formuły.
Więc, aby uniknąć wszystkich tych kłopotów napisaliśmy ten dogłębny artykuł obejmujący większość powodów, dla których formuły Excela nie działają.
I jak je naprawić.
Formuły Excela nie działają: Why & How to fix them
According to Washington post article on excel errors, „The Australian researchers found that roughly 1 in 5 of these papers included errors in their gene lists that were due to Excel automatically converting gene names to things like calendar dates or random numbers.”
Oryginalne badania przeprowadzone przez Journal Genome Biology wykazały, że z roku na rok obserwuje się tendencję wzrostową, jeśli chodzi o liczbę błędów.
Pełne materiały badawcze można pobrać ze strony SourceForge (Open Source) do dalszej lektury.
Powyższy research paper może nie jest bezpośrednio związany z formułami niedziałającymi w Excelu, ale jest w tym jakiś sens:
Bardzo dobrze zbudowane & przetestowane oprogramowanie jak MS Excel może się mylić w kwestii zrozumienia naszych danych co z Tobą & ja?
Oto nasze 10 najważniejszych powodów
#1 Opcje obliczeń
Zmiana opcji obliczeń na 'ręczne' jest najlepszą praktyką, ale musisz być bardzo ostrożny podczas używania.
Domyślnie, opcja obliczeń będzie ustawiona na automatyczną w Excelu.
Ale są przypadki, w których mogłeś zmienić tę opcję na 'ręczną', aby przyspieszyć proces.
Jeśli włączysz tę opcję, to formuły nie będą aktualizowane automatycznie, co z kolei zwiększy szybkość przetwarzania arkusza kalkulacyjnego.
Jak już skończysz, musisz kliknąć na Zapisz lub Odśwież skoroszyt, aby ponownie dokonać obliczeń. Jeśli tego nie zrobisz, otrzymasz nieprawidłowe wyniki.
Jak rozwiązać ten problem:
Przejdź do zakładki Formuły → Obliczenia → Kliknij na opcję Oblicz teraz/Oblicz arkusz.
Możesz również użyć skrótu klawiszowego F9, aby odświeżyć skoroszyt.
Tutaj znajduje się nasz przewodnik wideo na temat opcji obliczeń w Excelu.
Wskazówki:
- Wyrób sobie nawyk wciskania Ctl+S
- Nie używaj tej opcji dla każdego skoroszytu, chyba, że masz ogromny plik z dużą ilością formuł
- Ta opcja jest specyficzna dla skoroszytu, więc musisz włączyć/wyłączyć ją w każdym skoroszycie, którego chcesz używać
#2 Formuła Excela nie oblicza tylko pokazuje formułę
Pokaż formuły jest opcją w Excelu, aby wyświetlić wszystkie formuły skoroszytu za pomocą jednego kliknięcia.
Opcja ta jest bardzo przydatna, aby znaleźć wszystkie formuły w arkuszu.
Mówiąc to, musimy być trochę ostrożni podczas używania opcji Pokaż formuły, w przeciwnym razie, możesz skończyć z czymś takim jak poniżej.
Czy widzisz, że formuła Dzisiaj jest wyświetlana jako formuła, zamiast pokazywać rzeczywistą datę?
Tak samo jest z formułami Randbetween.
Jak rozwiązać ten problem:
To bardzo proste:
Przejdź do zakładki Formuły → W zakładce Audyt formuły → Kliknij na „Pokaż formuły” (Przy pierwszym kliknięciu formuły się pokażą, a przy drugim ukryją się za komórkami i zaczną działać)
Przeczytaj również: 51 best excel tips and tricks
#3 Extra Space(s) is an extra headache
Wyciągałeś dane używając formatu CSV (comma separated values), wtedy Twoje dane najczęściej narażone są na niechciane spacje.
Czy wiesz, że dodatkowe spacje powodują wiele problemów podczas używania formuły Vlookup?
Czasami dość trudno jest zidentyfikować te spacje.
Na przykład, powiedzmy, że chcesz zrobić Vlookup używając nazwiska pracownika jako bazy, więc Twoje nazwisko pracownika wygląda jak poniżej
Zauważyłeś?
Mimo, że obie nazwy wyglądają podobnie gołym okiem, w drugiej nazwie jest pusta spacja.
To nie spełnia kryteriów dokładnego dopasowania w formule Vlookup, co skutkuje błędem #N/A.
Jak rozwiązać ten problem:
Dodatkowa spacja(y) może zostać naprawiona przy użyciu formuły Trim.
Generalnie, formuła Trim znajdzie więcej niż jedną spację między słowami i usunie ją.
Wskazówki:
- Zawsze dobrym pomysłem jest użycie formuły Trim przed Vlookup w obu tabelach (lookup & tabela danych)
- Funkcja trim będzie m.in. zignoruje puste spacje między literami (będzie działać tylko ze słowami)
#4 Pozbądź się niedrukowalnych/ukrytych znaków
Podobnie do wiodących spacji, niedrukowalne znaki są również jednym z powodów, dla których formuły Excela nie działają.
W przypadku, gdy masz dane zawierające te znaki, lepiej jest je usunąć przed dalszą pracą.
Jak rozwiązać ten problem:
Podobnie jak w przypadku przycinania, możesz użyć formuły CLEAN, aby usunąć te znaki.
#5 Formatowanie w Excelu – nie próbuj porównywać Jabłek z Pomarańczami
Bardzo ważne jest prawidłowe formatowanie przed użyciem formuł takich jak Vlookup, Hlookup i Dopasuj & Indeks.
Liczby sformatowane jako tekst są jednym z najczęstszych powodów niedziałania formuł w Excelu.
Choć wyglądają jak liczby, ale nimi nie są.
Na przykład, po lewej stronie, możesz zobaczyć ID klienta wygląda podobnie do liczb.
Ale, jeśli odniesiesz się do powyższej sekcji formatowania, możesz zobaczyć, że zostały one sformatowane jako tekst.
To z kolei spowoduje wystąpienie błędu #N/A podczas dodawania formuły Vlookup.
Najgorsze jest to, że jest on trudny do zidentyfikowania.
Jak rozwiązać ten problem:
Zapewnij się, że wszystkie Twoje liczby są sformatowane jako liczby, a nie jako tekst. Warto przeformatować kolumnę lookup przed użyciem formuły Vlookup.
Wskazówki:
- Ogólnie, liczby zawsze będą wyrównane do prawej strony komórki (ale w naszym powyższym przykładzie nie są)
- Czasami możesz zobaczyć małą zieloną wskazówkę w lewym górnym rogu, która wskazuje, że coś jest nie tak z tą komórką
#6 Odwołania kołowe (Błędy spowodowane nieskończonym okręgiem)
W uproszczeniu, Twój zakres formuły jest w nieskończonej pętli lub jest w okręgu.
Zgodnie z regułami obliczeń Excela, Twoje formuły nie powinny odwoływać się do komórek wynikowych jako zakresu. Jeśli to zrobisz, pojawią się błędy odwołań kołowych.
Personalnie, wiele razy zmagałem się z tego typu błędami.
Na przykład, w naszej poniższej tabeli Budżet wydatków, musimy dodać pewną wtyczkę (numer korekty), aby dopasować ją do celów.
Więc, obliczyliśmy kwotę różnicową w wierszu 13, a teraz musimy umieścić ją w wierszu 9.
Jednym z prostych sposobów jest połączenie komórek wiersza 13 w wierszu 9.
Jeśli to zrobisz, Twoja formuła łącząca nie zadziała, zamiast tego zobaczysz zero i ewentualnie zobaczysz też niebieskie strzałki wskazujące odwołania do komórek.
Ogólnie powinno to działać.
Ale technicznie rzecz biorąc, będzie to błąd, ponieważ odwołania do komórek formuły są w pętli.
Jak rozwiązać ten problem:
Po pierwsze, musimy zidentyfikować błędne komórki.
Przejdź do zakładki Formuły → kliknij strzałkę rozwijaną Sprawdzanie błędów → ustaw kursor na Odwołania do komórek → teraz zobaczysz zakres(y) błędów
Po drugie, teraz znasz zakresy odwołań kołowych, przejdź do każdej komórki błędu i napraw formuły.
#7 Nieprawidłowe użycie cudzysłowów – błąd ludzki
Często używamy cudzysłowów podczas pisania formuł.
Ale bardzo ważne jest, aby zrozumieć, kiedy ich używać, a kiedy nie.
Na przykład, aby połączyć dwie liczby, możemy użyć formuły jak poniżej.
=1234&5678 = 12345678
Alternatywnie, aby połączyć dwa ciągi tekstowe nie można zrobić tego samego zamiast tego, trzeba określić tekst w podwójnym cudzysłowie.
=John&Smith: Nieprawidłowy sposób użycia
=”John”&”Smith” = To prawidłowy sposób.
To samo dotyczy formuł warunkowych, takich jak JEŻELI, ORAZ, LUB. Bardzo ważne jest, aby tekst był podany w cudzysłowie.
W przeciwnym razie jest to jeden z powodów, dla których formuły Excela nie działają.
#8 BODMAS – podstawowa reguła dla każdego obliczenia
Czy wiesz, że każde obliczenie w Excelu opiera się na regule 'BODMAS'?
Och, chwila… co to jest reguła BODMAS?
To reguła matematyczna określająca kolejność operacji.
Ok, uprośćmy jeszcze bardziej.
Czy potrafisz zgadnąć, jaka jest odpowiedź na poniższe pytanie?
10+10*2 =?
Czy jest to 40, gratuluję, nie masz racji.
Czy możesz spróbować tego samego obliczenia w Excelu?
Prawdopodobnie zobaczysz 30, rzeczywiście jest to poprawna odpowiedź… ale jak?
Zgodnie z regułą BODMAS, obliczenia będą wykonywane w oparciu o sekwencję, tj. → B- nawiasy, O- kolejność, D- dzielenie, M- mnożenie, A- dodawanie & S- odejmowanie.
Więc kolejność powyższej formuły to → 10*2 = 20 → 20+10 =30.
Twoje formuły mogą pójść źle, jeśli nie uwzględnisz BODMAS.
Jak rozwiązać ten problem:
To bardzo proste, wystarczy zmienić kolejność za pomocą nawiasów, tak jak poniżej.
=(30+40)*2
Więc wszystko w nawiasach zostanie obliczone jako pierwsze, a następnie przejdzie do następnej operacji.
#9 Nieprawidłowe użycie odwołań „bezwzględnych”
Każdy, kto używa Excela, wie co to są odwołania bezwzględne.
Jeśli nie, proszę zatrzymać się tutaj i dowiedzieć się więcej na ten temat. Oto krótki poradnik na temat odwołań do komórek.
Z pomocą odwołań bezwzględnych można ustalić zakres formuły tak, aby można było kopiować z jednej komórki do drugiej.
To rzeczywiście bardzo przydatna funkcja. Jeśli jednak nie wiesz, jak z niej korzystać, to Twoje formuły nie będą działać tak, jak powinny.
Jest to z pewnością jeden z głównych powodów, dla których formuły Excela nie działają po skopiowaniu z jednej komórki do drugiej.
Na przykład, w naszej powyższej tabeli, użyliśmy bezwzględnego odwołania do komórki, aby ułatwić sobie kopiowanie formuł.
Ale popełniliśmy błąd.
Zamiast używać częściowego zamrożenia, użyliśmy skrótu klawiszowego F4 i ustaliliśmy cały zakres.
To rzeczywiście spowodowało nieprawidłowe wyniki.
Więc, poprawnym sposobem powinno być użycie bezwzględnego odniesienia tylko dla wierszy, jak to =SUM(D$3:D$6).
Jak rozwiązać ten problem:
Niestety, nie ma szybkich rozwiązań dla tego typu błędów. Musisz to zrobić ręcznie, analizując wszystkie formuły.
Ale najlepszym sposobem, jaki znalazłem do tej pory, jest krzyżowe sprawdzanie formuł & wyników, gdy tylko użyję bezwzględnego & względnego odniesienia.
Proszę dać mi znać swoje obejścia w tym momencie.
Wskazówki:
- Nie używaj klawisza skrótu F4 dla częściowego zamrożenia
- Spróbuj dołączyć znak dolara ($) ręcznie dla częściowego zakresu
#10 Nieprawidłowe argumenty formuły
Nieprawidłowe argumenty formuły mogą mieć negatywny wpływ na wyniki formuły.
Aby dać ci trochę perspektywy, zrozummy poniższą składnię vlookup, zwłaszcza ostatnią.
Argument Range lookup ma dwie opcje do wyboru.
- True – Approximate match
- False – Exact match
Personalnie uważam, że używanie opcji „Approximate match” jest jak popełnienie samobójstwa.
Ponieważ dopasowanie przybliżone nigdy nie zwróci dokładnych wyników.
W naszym powyższym przykładzie, dodaliśmy formułę Vlookup w komórce F3, aby znaleźć odpowiednią kwotę przychodu.
Jeśli przyjrzysz się uważnie, kwota przychodu 25k dla klienta id 6378993 jest nieprawidłowa. Powinno być 65k zamiast tego.
Co więc poszło nie tak?
Jak już wspomniałem, użyłem 'Approximate match' w argumencie range lookup.
„Match” jest kolejną formułą, w której masz możliwość wybrania podobnych argumentów.
Jak rozwiązać ten problem:
Nigdy nie używaj 'Dopasowania przybliżonego' w swoich formułach vlookup.
Teraz Twoja kolej
Czy uważasz, że masz jakieś inne powody, dla których formuły Excela nie działają?
Albo, może masz jakieś pytanie do zadania.