Funkcje SUMA.JEŻELI i SUMA.WARUNKÓW, jak wskazuje ich nazwa, służą do sumowania wybranych danych. Pierwsza funkcja – niezwykle popularna – pozwala sumować na podstawie jednego założenia, SUMA.WARUNKÓW – na podstawie wielu. Co ciekawe, są sytuacje, w których te funkcje zwracają błędne wyniki.
Obcięcie zer przy odczycie
Kilka lat temu napisałem na blogu tekst dotyczący zaczytywania danych z prefiksem 0 i problemów z jakimi to się wiąże.
Napisałem tam m.in. że firmy często przypisują kody, które zaczynają się od zera np. 03355.
Napisałem, że w praktyce stwarza to spore problemy z raportowaniem, ponieważ te zera są bardzo często ucinane. Przywołałem tam przykład zaczytywania takiego kodu formułą i zmianę jej na wartość.
Błędy w sumowaniu warunkowym
W dzisiejszej notce dorzucam kolejny kamyczek do ogródka… Tym razem dotyczy on wykorzystania funkcji sumujących warunkowo.
Kilka dni temu dostałem w swoje ręce raport XLSX wygenerowany bezpośrednio z SAP. Firma, z którą współpracuję, posiada w swoim asortymencie masę produktów, w tym dwa zupełnie różne, oznaczone odpowiednio kodami 03355 i 3355 (sic!).
Po otwarciu raportu, od razu zobaczyłem ucięte zera w kolumnie z kodem produktu! Kod 3355 został więc przypisany dwóm różnym produktom, co od razu wprowadzało w błąd.
W trakcie wykonywania docelowego zadania zauważyłem bardzo (nie)ciekawą prawidłowość. Otóż funkcje SUMA.JEŻELI i SUMA.WARUNKÓW traktują tak samo kody: 3355 i 03355.
Funkcja SUMA.ILOCZYNÓW jak i SUMA (Ctrl+Shift+Enter) zwracają natomiast prawidłowe wyniki.
Przykład złego sumowania
Poniższy screen prezentuje trzy rożne pozycje, możemy powiedzieć, że są to kody produktów.
- 3355.
- 03355 poprzedzony apostrofem.
- 03355 bez apostrofu, ale z komórką sformatowaną jako tekst.
Aby ułatwić sobie sprawę nadałem nazwy kolumnom Kod i Cena.
- Komórka D2. =SUMA.JEŻELI(Kod;$B2;Cena)
- Komórka E2. =SUMA.WARUNKÓW(Cena;Kod;$B2)
- Komórka F2. =SUMA.ILOCZYNÓW(–(Kod=$B2);Cena)
- Komórka G2. =SUMA((Kod=$B2)*Cena) ► Ctrl+Shift+Enter
- Komórka H2. =SUMA(JEŻELI(PORÓWNAJ($B2;Kod);Cena;””)) ► Ctrl+Shift+Enter
Zakres J1:K4 pokazuje tabelę przestawną, która generuje takie same wyniki jak funkcja SUMA.ILOCZYNÓW i funkcja SUMA w obu kombinacjach.
Podsumowanie i wnioski
- Funkcje SUMA.JEŻELI i SUMA.WARUNKÓW zwróciły błędne wyniki poprzez fakt, że zsumowały ceny wszystkich produktów.
- Excel nie rozróżnił pozycji nr 2 i 3, czyli kod 03355 poprzedzony apostrofem (format ogólny) jest dla Excela tym samym, co wpis 03355 bez apostrofu, ale z komórką uprzednio sformatowaną jako tekst.
- Funkcje SUMA.ILOCZYNÓW i SUMA, w dwóch kombinacjach zatwierdzanych tablicowo, zwróciły ten sam prawidłowy wynik co tabela przestawna.