Excel źle sumuje

Często można usłyszeć stwierdzenie, iż:

Excel coś źle sumuje, Excel czegoś nie liczy, albo Excel nie liczy tak jak ja tego chcę

Na potrzeby niniejszego wpisu przyjmijmy ostatecznie: 

Excel się nie myli i liczy dokładnie to co mu każemy. 

Oczywiście znajdą się sytuacje, gdy Excel naprawdę coś liczy błędnie i wynika to tylko z jego ułomności bądź błędów, jednak w 99,9% przypadków wyniki inne od oczekiwanych wynikają z błędu użytkownika.

Poniżej załączam subiektywne zestawienie najczęstszych błędów:

1. Wartość a formatowanie komórki
2. Liczba sformatowana jako tekst
3. Data w błędnym formacie daty
4. „Tekst” a „Tekst ”
5. Błędne odwołanie do komórki

Wartość a formatowanie komórki 

Bardzo ważną kwestią jest odróżnienie formatowania komórki od jej faktycznej wartości. Zmieniając format komórki nie zmieniamy wartości, a jedynie sposób jej wyświetlania.

Przykładowo dla komórki o wartości 0,005 sformatowanej jako wartość liczbowa z dwoma miejscami po przecinku otrzymamy 0,01. Teraz dodają trzy takie same komórki niektórzy oczekują wyniku 0,3 (0,1+0,1+0,1). Excel z kolei nie zwraca uwagi na formatowanie komórki, a jedynie na jej wartość w związku z powyższym policzy on w sposób (0,005 + 0,005 + 0,005) czyli 0,015, a dopiero tą wartość wyświetli zgodnie z ustawieniami formatowania komórki w której tą sumę wyliczyliśmy, czyli trzymając się przykładu dwóch miejsc po przecinku, otrzymamy 0,02. 

Podobne sytuacje występują bardzo często w przypadku liczenia udziałów bądź ceny powiększonej o podatek VAT. Na przykład mając 33,3% + 33,3% + 33,3% otrzymujemy w sumie 100%, jednak gdy takie wartości sformatujemy jako liczby całkowite otrzymamy 33% + 33% + 33% = 100%, co już może powodować wątpliwości co do prawidłowości obliczeń.

Gdy mamy wątpliwość, czy wszystko zostało policzone prawidłowo polecam:

• Na chwilę zwiększyć ilość miejsc zerowych w analizowanych komórkach, aby zobaczyć czy właśnie tam coś się nie kryje
• Sprawdzić zakresy formuły, czy na pewno obejmują wszystkie komórki które powinny zostać wzięte pod uwagę

Z kolei gdy zależy nam na sytuacji odwrotnej, czyli liczba ma kilka miejsc po przecinku, ale chcemy aby przy dalszych kalkulacjach Excel uwzględniał już ją jako liczbę jedynie z dwoma miejscami, należy wartość tej komórki zaokrąglić przy użyciu funkcji ZAOKR.

Liczba sformatowana jako tekst

Aby móc dokonywać obliczenia Excel musi widzieć, że wartość komórek są liczbami. Gdybyśmy próbowali dodawać do siebie dwie komórki w których jedna była by liczbą, a druga tekstem, w zależności od zastosowanej funkcji możemy otrzymać błąd #ADR! (w przypadku funkcji w postaci =D4+D5), bądź wartość tekstowa zostanie pominięta (np. w funkcji SUMA).

Kiedy więc dokonujemy kalkulacji, formuły są dobrze napisane, a otrzymujemy błędne wyniki bądź błędy, wtedy warto sprawdzić czy naszych wartości Excel nie traktuje jako tekst. Sytuacja taka może wynikać z:

• Wpisania liczby z innymi separatorami aniżeli ustawione w naszym programie. Standardowo w Polsce są to spacja jako separator tysięcy i przecinek jako separator dziesiętny. Przykłady błędnych wpisów:
• 1.000 (kropka jako separator tysięcy, powinna być spacja bądź puste),
• 1'000 (apostrof jako separator tysięcy, powinna być spacja bądź puste),
• 1 000.00 (kropka jako separator dziesiątek, powinien być przecinek).
• Wpisania wartości w komórkę wcześniej sformatowaną jako Tekstową
• Wymuszenia traktowania wpisywanej wartości jako wartości tekstowej, co jest dokonywane przez wpisanie wartości poprzedzonej apostrofem, np. ‘1000

W jaki sposób można rozpoznać, czy liczba jest wpisana jako wartość tekstowa:

• Błędnie wpisane wartości oznaczone są zielonym trójkątem w lewym górnym rogu komórki. W tym przypadku wystarczy kliknąć na komórkę, LPM na pojawiającym się znaku z wykrzyknikiem i zaznaczyć opcję „Konwertuj na liczbę”
  

• Przy standardowym formatowaniu komórek tekst  jest sformatowany do lewej w odróżnieniu od liczb, które sformatowane są do prawej. W związku z tym liczby przez nas poszukiwane powinny się wyróżniać z danych
• W związku z faktem, iż komórek tekstowych nie można dodawać przy ich zaznaczeniu Excel w dolnym prawym rogu ekranu nie pojawi się suma. Normalnie powinno to wyglądać następująco:
  
  Jednak gdy zaznaczamy komórki tekstowe zaznaczone pole będzie wyglądało trochę inaczej
  
  Co już daje nam informację, iż komórki naszego zakresu nie są liczbami. 

Sposoby naprawy:

Aby umożliwić Excelowi prowadzenie poprawnych obliczeń, wszystkie nieprawidłowe komórki należy edytować i wpisać je w poprawnym formacie liczbowym akceptowalnym przez program. W przypadku gdy Excel sam zauważył, iż liczba jest sformatowana jako tekst możliwe jest również skorzystanie z wspomnianej opcji „Konwertuj na liczbę”

Data w błędnym formacie daty

Zakładany przez Excel format wpisywania daty to rok-miesiąc-dzień, bądź rok/miesiąc/dzień, dlatego też wpisanie ich w inny sposób może spowodować rozpoznawanie ich jako tekst i powodować problemy analogiczne do wyżej opisanych w kontekście liczb sformatowanych jako tekst. 

Rozpoznawanie przyczyny oraz sposoby naprawy są analogiczne, dlatego też odsyłam do punktu wyżej.

4.      „Tekst” a „Tekst ” 

Często w funkcjach wyszukiwania (np. PODAJ.POZYCJĘ), czy porównywania (A1=B1) przychodzi użytkownikowi do zweryfikowania czy komórki są sobie równe. Na tym etapie można się spotkać z opinią, iż Excel nie widzi, że komórki są identyczne i zwraca błędne wartości.

Najczęściej powodem zwracania błędnej wg. nas wartości jest fakt, iż komórki w rzeczywistości nie są sobie równe, a różnią się czymś dla nas na pierwszy rzut oka niezauważalnym. Najczęściej wystarczy sprawdzić, czy komórki nie zawierają na początku bądź końcu spacji, w sytuacji takiej Excel porównuje dwa ciągi tekstowe „Tekst” oraz „Tekst ”, które rzeczywiście są inne.

Najłatwiejszym sposobem sprawdzenia czy na końcu komórki znajduje się tekst, jest naciśnięcie klawisza F2 i przejście do trybu edycji. W sytuacji gdy za naszym tekstem znajduje się spacja kursor nie będzie migał bezpośrednio za ostatnią literą, a w pewnym odstępie od niej. Nadprogramowe znaki proponuję usunąć co powinno spowodować, iż dane będą do siebie pasować.

Błędne odwołanie do komórki

Błąd powstający najczęściej w przypadku kopiowania formuł, które już mieliśmy wpisane w arkuszu.

W zależności od odwołań , które były zastosowane w kopiowanej komórce (względne/bezwzględne) przy jej kopiowaniu w inne miejsce wszystkie zawarte w niej odwołania zostaną zmienione/pozostaną bez zmian. Doświadczeni użytkownicy świadomie korzystają z różnego typu odwołań i znają skutki ich zastosowania. Czasami jednak i oni przy kopiowaniu formuł nie zwrócą uwagi na typ odwołania i funkcja w nowym miejscu nie będzie się już odwoływała do danych które nas interesują.

Na szczęście łatwo sprawdzić komórki do których odwołuje się funkcja. Wystarczy zaznaczyć interesującą nas komórkę i spojrzeć na pasek formuły gdzie wpisane są wszystkie odwołania. Dodatkowym uproszczeniem może być wejście w tryb edycji analizowanej formuły (przez kliknięcie LPM na pasku formuły, bądź naciśnięcie klawisza F2), wtedy bowiem na kolorowo zostaną zaznaczone wszystkie znajdujące się w arkuszu komórki, do których odwołuje się formuła.