fbpx

Funkcja SUMA źle liczy!

21.03.2018 | ECP1, Operacje na tekście

… i co zrobić, żeby ją naprawić?

Trochę dziwnie brzmi tytuł tego wpisu, ponieważ oczywiście funkcja SUMA dobrze liczy :). Natomiast nam użytkownikom czasem może się wydawać, że jednak SUMA liczy źle. Nic dziwnego, jak widzimy coś takiego:

Suma liczb w ramce jest zdecydowanie większa niż 61, mimo tego, co twierdzi funkcja SUMA. Co więc jest z nią nie tak? Rozwiązanie tej zagadki jest bardzo proste i ma związek z postawami Excela, a mianowicie z typem danych, jakie przechowujemy w komórce. Te podstawy warto znać 😉

Na początku warto zdać sobie sprawę z tego, że to nie z funkcją SUMA jest coś nie tak, tylko z danymi, które dostała.

Przyjrzyj się dokładnie danym: Twój niepokój powinien wzbudzić fakt, że wszystkie wartości w czarnej ramce (czyli sumowane wartości) są wyrównane do prawej strony (specjalnie tak zrobiłam przykład, żeby było to widać, choć zdaję sobie sprawę z tego, że nie zawsze jest to takie oczywiste). Mamy tam zarówno wartości liczbowe, tekstowe jak i logiczne (PRAWDA). Każdy z tych typów danych powinien być wyrównany inaczej, czyli:

  • teksty do lewej
  • liczby do prawej
  • logiczne do środka

A nie są. To znaczy, że user ręcznie zmienił wyrównanie komórek! Grzech!!! Przez to teraz mamy problem :). Co do zasady nie powinno się zmieniać domyślnego wyrównania komórek, właśnie dlatego, że może to wprowadzić w błąd. Wyjątkiem od tej zasady są nagłówki tabel, które możemy wyrównać zgodnie z wyrównaniem zawartości kolumny, której są nagłówkiem.

To są podstawy, ale niestety często o nich zapominamy, albo – co gorsza – nie znamy ich ;(

Przywracanie wyrównania ogólnego

Zmieńmy zatem wyrównanie tych komórek na ogólne. Możesz to zrobić w Narzędziach głównych klikając na ikonkę wyrównania do prawej strony (zobacz, że jest zaznaczona dla tych danych!) lub poprzez okienko formatowania komórek (skrót klawiszowy, Ctrl + 1).  W obu przypadkach najpierw zaznacz dane, które chcesz formatować (czyli w tym przykładzie czarną ramkę):

Przywracanie wyrównania ogólnego

Przywracanie wyrównania ogólnego

Widać na powyższym rysunku wynik przywróconego wyrównania. Liczby do prawej, teksty do lewej i wartości logiczne na środek.

Ale zaraz! Niektóre liczby: 34, 75 i 3 są do lewej! I tutaj jest pies pogrzebany. Te liczby to dla Excela teksty! A on nie umie sumować tekstów. Należy więc, specjalnie dla Excelka, dokonać konwersji tekstu na liczby, czyli przerobić teksty na liczby :).

Wklejanie specjalne z operacją

Aby dokonać takiej konwersji, należy wykonać na tych „liczbach” dowolną operację matematyczną, która nie zmieni ich wartości (tego akurat nie chcemy ;)). Takimi operacjami są dodanie lub odjęcie zera, pomnożenie lub podzielenie liczby przez 1 lub tzw. podwójna negacja, czyli dwukrotne pomnożenie liczby razy -1 (ostatni sposób jest najszybszy w formułach).

Moją ulubioną i jednocześnie najszybszą metodą na zrobienie tego jest wklejanie specjalne z operacją np. mnożenia. Żeby to zrobić:

  1. wpisz liczbę 1 w dowolną komórkę poza zakresem danych,
  2. skopiuj ją (Ctrl + c)
  3. zaznacz zakres danych, który chcesz przekonwertować (u nas: czarna ramka)
  4. kliknij na tym zakresie prawym przyciskiem myszy i z menu podręcznego wybierz opcję Wklej specjalnie…
  5. w okienku, które się pojawi, zaznacz: Wartości i Przemnóż (może być też Podziel – nie ma to znaczenia, ponieważ jeśli liczbę pomnożysz lub podzielisz przez jeden – otrzymasz dokładnie tę szamą liczbę)
Konwersja tekstu na liczby poprzez wklejanie specjalne

Konwersja tekstu na liczby poprzez wklejanie specjalne

Po naciśnięciu OK robota zrobiona!

Wynik jest taki:

Wynik

Wynik

I to tyle – takie proste i jednocześnie zbawienne :). Cała trudność w wykryciu takich baboli. Na szczęście wystarczy sięgnąć do podstaw 🙂

BTW Ten temat już poruszałam na blogu, natomiast kontekst był nieco inny, więc postanowiłam jeszcze raz o tym napisać, omawiając ten nowy kontekst. A raczej inny kontekst…

Powiązane produkty:

  • WEBINAR: Konwersja tekstu na liczbę odpowiadający na pytanie dlaczego w ogóle Excel czasem traktuje liczby/ daty jako tekst oraz prezentujący szereg metod na radzenie sobie z takimi sytuacjami. Dodatkowo pokazana jest też metoda na sytuację odwrotną: gdy chcemy z liczby stworzyć tekst.

Poniżej plik z błędnymi danymi do pobrania:

Malinowy Excel Funkcja SUMA źle liczy dw.xlsx

I wersja wideo przedstawionego rozwiązania:

 

 

Jeżeli chcesz lepiej poznać Excela na poziomie podstawowym zapisz się na listę zainteresowanych kursem Excel w codziennej pracy cz. 1! Podczas oczekiwania na kurs będę Ci wysyłała informacje, ciekawostki i excelowe triki. Bezpłatnie! 

Pomogłam Ci? Postaw mi wirtualną kawę! →

Przeczytaj podobne wpisy

Kategorie

15 komentarzy

  1. Jak rozumiem formuła nie wyrzucała błędu? Czyli jakbym nie zauważyła, że coś jest nie tak z tą sumą to mogłabym ten błąd przeoczyć (np. jak mam podsumowanie 500 pozycji).

    Reply
    • Nie, nie wyrzuca. Po prostu sumuje tylko liczby. Niestety to jest problem – można to przeoczyć ;(

      Reply
      • Teoretycznie można by przy każdej wartości postawić „strażnika” w postaci funkcji CZY.LICZBA. Ale wpierw trzeba w ogóle podejrzewać, że coś z tymi wartościami może być nie tak.

        Reply
        • Tak, jak najbardziej. Najtrudniejsze jest właśnie to podejrzewanie…

          Reply
  2. Ten artykuł pomógł nam w rozliczeniu, nad którym głowiliśmy się od kilku dni po parę godzin! DZIĘKI!

    Reply
    • Oooo, jak cudownie!!!! Bardzo się cieszę! Uskrzydlają mnie takie informacje! Bardzo dziękuję i MEGA się cieszę!!!! 🙂 ♥

      Reply
      • Niestety wszystkie kolumny mają format tekst, ponieważ są wyrównane do lewej. Kopiowanie każdej z ponad 40 kolumn i tworzenie oddzielnych tabel gdy nie działa wklej specjalnie i formatowanie kolumn/ tabeli to ogromna strata czasu przy wyliczeniach. Jest jakaś inna, szybsza metoda?

        Reply
  3. Po kliknięciu ok nic się nie dzieje…‍♂️

    Reply
  4. Ja mam pytanie w kontekście sumowania. Mam kłopot – w pliku excel sumując wartości w kolumnie wychodzi mi wartość 100,07. Natomiast korzystając modelu danych (najpierw pobranie z folderu, scalenie) i tworząc tabele przestawną z tego modelu danych mam natomiast wartość 200,14 za ten sam okres (dokładnie zdublowaną do miejsc po przecinku). Nie wiem w czym jest problem

    Reply
    • Hmm…. a może na etapie scalania wychodzi jakiś dubel?

      Reply
  5. Wpisuję w kolumnę z palca kilka liczb, które zawsze powinny sumować się do zera. Liczby mają 3-7 znaków po przecinku. Problem polega na tym, że czasem nie sumują się do zera…. Np. 4 liczby, z których dwie są całkowite, a dwie mają 4 znaki po przecinku (893,1778, -589, -300, -4,1778) i co ciekawe suma pierwszych trzech wychodzi 4,177800000000050000000, a suma wszystkich czterech 0,000000000000007105427.
    Gdzie tu logika?

    Reply
    • Przepraszam, suma wszystkich czterech to 0,000000000000047073456 🙂

      Reply
      • Niestety po zaznaczeniu kolumny opcja wklej specjalnie jest wyszarzona. U mnie nie zadziałała ta metoda, podobnie jak formatowanie 🙁

        Reply
        • Hej, być może masz arkusz zablokowany do edycji?

          Reply
    • Niestety tak się zdarza naszym komputerom=maszynom. Wiem, kiepskie to wytłumaczenie… jedyna opcja, to zaokrąglanie funkcją ZAOKR do odpowiedniej dla Ciebie liczby miejsc po rpzecinku…

      Reply

Submit a Comment

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Pin It on Pinterest