• Zapisz się do newslettera, aby otrzymywać powiadomienia o nowościach na blogu
    Zapisując się, wyrażasz zgodę na przesyłanie Ci informacji o nowościach na tym blogu. Zgodę możesz w każdej chwili wycofać (szczegóły).

Funkcja SUMA źle liczy!

… 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…

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

Malinowy Excel Funkcja SUMA źle liczy dw.xlsx

I wersja wideo przedstawionego rozwiązania:

 

 

Tagi , , , , , , , , , , , , , , , , .Dodaj do zakładek Link.

4 odpowiedzi na „Funkcja SUMA źle liczy!

  1. Bea mówi:

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

    • Malina mówi:

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

      • Spam mówi:

        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.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *