To zagadnienie chodzi za mną już od jakiegoś czasu. Wiem, że za wami też, bo pytacie mnie o nie. 😉

O czym mowa? Otóż o tym, co zrobić, aby funkcja SUMA wyświetlała poprawną wartość, jeśli w sumowanym zakresie są błędy.

Często zdarza się tak, że zakres, który chcemy zsumować, zawiera już inne formuły. Bywa, że formuły te zwracają błędy. Niestety, jeśli w sumowanym zakresie funkcja SUMA napotka błąd, również zwróci błąd. Taką sytuację ilustruje poniższy obrazek:

Suma wyświetlająca błąd – działanie niepożądane

Funkcja SUMA zwróciła błąd (#N/D), ponieważ jedna z sumowanych komórek zwróciła tenże błąd. Zauważcie, że w tabeli z kursami walut na obrazku nie ma dolara australijskiego (AUD). W tym przykładzie SUMA ma policzyć wszystko to, co się da (czyli ekwiwalent CHF, USD, EUR), i wyświetlić wynik – czyli wszystko oprócz tej błędnej wartości (ekwiwalent AUD). Ponieważ zwykła SUMA nie radzi sobie z tym zadaniem, należy użyć formuły tablicowej.

W idealnym scenariuszu (a taki właśnie piszemy 🙂 ) chcemy, aby formuła sprawdziła, czy każda komórka w zakresie zawiera błąd. Jeśli tak – ma dodawać zero, jeśli nie – ma dodawać wartość z komórki. Przedstawię to rozumowanie na schemacie:

Schemat rozumowania

Ponieważ nasze obliczenie ma być jedną funkcją, posłużymy się formułą tablicową. Na szczęście jest ona łatwa. 🙂

Kilka słów o formułach tablicowych

No właśnie… Co to są te formuły tablicowe? W uproszczeniu można powiedzieć, że robią one w jednej formule coś, co normalnie trzeba byłoby rozpisywać na kilka kolumn z formułami.

Zauważcie, że w naszym przykładzie moglibyśmy w dodatkowej kolumnie sprawdzić, czy funkcja przeliczająca kwotę w walucie na złotówki (kolumna C) zwraca błąd. Jeśli tak – zwrócilibyśmy zero, jeśli nie – wartość komórki. Na koniec oczywiście podsumowalibyśmy tę kolumnę już zwykłą funkcją SUMA. I zrobione. Z praktycznego punktu widzenia, ta kolumna pośrednicząca nic nie wnosi do tabeli. Dobrze by było ją więc pominąć.

Arcyważne

Formuły tablicowe zatwierdzamy kombinacją klawiszy Ctrl + Shift + Enter.

(Nie mylić z Ctrl + Enter!)

Dzięki temu Excel wie, że wpisaną formułę ma traktować specjalnie. 🙂 Na dowód tego ujmuje ją w nawiasy klamrowe: { = … }. Nawiasy te pojawią się TYLKO wtedy, gdy zatwierdzimy formułę przez Ctrl + Shift + Enter. Próba wstawienia ich ręcznie nie zadziała!

Magiczna formuła

No właśnie. Jaka więc będzie nasza formuła? Ano taka:

=SUMA(JEŻELI.BŁĄD(C8:C12;0))

Oczywiście zatwierdźcie ją przez Ctrl + Shift + Enter. Wtedy pojawią się nawiasy klamrowe:

Formuła z nawiasami klamrowymi

Funkcja zaczyna liczenie od środka (to akurat normalne działanie 🙂 ). Najpierw sprawdza, czy wartość w każdej kolejnej komórce zakresu C8:C12 jest błędem (funkcje JEŻELI.BŁĄD). Jeśli jest, to zapamiętuje, że ma dodać (funkcja SUMA) zero, a jeśli błędu nie ma, że ma dodać wartość komórki. Na koniec sumuje wyniki funkcją SUMA, która sama z siebie operuje na tablicach.

Używam określenia „zapamiętuje”, ponieważ Excel tworzy sobie w pamięci tablicę, do której wpisuje wyniki funkcji JEŻELI.BŁĄD dla każdej komórki zakresu. Robi w pamięci to, co my moglibyśmy zrobić korzystając z kolumny pomocniczej.

Po wprowadzeniu tej formuły suma zostanie policzona już prawidłowo. Jak na obrazku:

Wynik formuły tablicowej

I tyle! Łatwe? Mam nadzieję, że tak! 🙂

Myślę sobie, że możecie zapytać jeszcze, czemu formuła licząca cenę w PLN nie zabezpiecza się przed błędem? Można przecież tę formułę objąć funkcją JEŻELI.BŁĄD, która zadziała tak samo, jak w formule tablicowej – zwróci dobrą wartość lub zero w przypadku błędu. Czyli może to wyglądać następująco:

=JEŻELI.BŁĄD(ZAOKR(B8*WYSZUKAJ.PIONOWO(A8;$B$2:$C$4;2;0);2);0)

Wynik wyszedłby ten sam. Jednak znaczenie byłoby – moim zdaniem – inne. Nie jest bowiem prawdą, że cena 58 AUD jest równe zero PLN. W takim razie wolę już widzieć błąd (#N/D) niż złą wartość. Ale to ja. Oczywiście ostateczna decyzja należy do was.

Co o tym myślicie? Przyda się? Dajcie znać w komentarzach.

Redakcja językowa: Aleksandra Wasiak