Excel: Suma niepustych komórek – formuła tablicowa

1-cze-2013

W sumie to dość typowy problem. Masz w excelu tabelkę z danymi w dwóch kolumnach:

kolumna A zawiera jakiś opis, np numer faktury
kolumna B zawiera pewne kryterium, które wpływa na to czy wartość ma być sumowana czy nie, np datę płatności
kolumna C zawiera wartość do podsumowania

No i wreszczie zadanie. Zsumować należy tylko te faktury, które zostały już zapłacone, czyli zsumować należy te komórki z wartością, gdzie komórka obok z datą jest pusta lub niepusta.

Zadanie można rozwiązać na wiele sposobów, ale spodobał mi się szczególnie jeden z tzw. formułami tablicowymi. Chciałoby się napisać tak:

=SUMA(JEŻELI(CZY.PUSTA(B5:B11);C5:C11;0))

czyli sumuj liczby w taki sposób, że jeśli w komórkach B5:B11 coś jest to dodawaj wartości z C5:C11. Problem jest tylko taki, że formuła czy.pusta może na raz sprawdzić tylko jedną komórkę, a my chcielibyśmy sprawdzić zakres komórek. Drugi problem to formuła jeżeli. Jeżeli może zwrócić jedną liczbę, a w naszym zapisie zwraca zakres komórek C5:C11.

Z pomocą przychodzi formuła tablicowa. Uroda takiej funkcji polega na tym, że można ją zdefiniować tak, jak my to powyżej napisaliśmy (ona to zrozumie!!!), ale podczas wykonywania obliczeń nie będzie sięgać do blok komórek B5:B11 i C5:C11, ale wykona szereg obliczeń dla każdej komórki z tego zakresu oddzielnie. Czyli policzy wartości formuł:

JEŻELI(CZY.PUSTA(B5);C5;0)

JEŻELI(CZY.PUSTA(B6);C6;0)

JEŻELI(CZY.PUSTA(B7);C7;0)

…

JEŻELI(CZY.PUSTA(B11);C11;0)

A potem formuła SUMA zsumuje wartości wyliczone przez JEŻELI.

Do dzieła! Aby wprowadzić formułę tablicową ustawiasz się we właściwej komórce (tam gdzie ma się pojawić wynik) i wpisujesz formułę. Nie kończysz jej jednak zwyczajnie naciskając ENTER. Na koniec należy nacisnąć ENTER przytrzymując jednocześnie CONTROL i SHIFT:

Formuła powinna w tym momencie zadziałać i wyliczyć poprawną wartość. Jeśli podświetlisz komórkę, to w pasku formuł zobaczysz, jak wygląda teraz nasza formuła. Została przez Excela opakowana w nawiasy klamrowe. Tak własnie wygląda formuła tablicowa.

W naszym zadaniu trzeba jeszcze było wyznaczyć sumę wartości dla faktur zapłaconych. Formuła będzie więc wyglądać:

=SUMA(JEŻELI(NIE(CZY.PUSTA(B5:B11));C5:C11;0))

I tylko pamiętaj o kończeniku wprowadzania formuły: CONTROL + SHIFT + ENTER.

Jedno z podstawowych pytań – skąd ja to wszystko wiem. Odpowiedź jest prosta. Czytam książki, nie Mickiewicza, nie Sienkiewicza, tylko życiową fachową literaturę informatyczną.


Excel 2010 Biblia	100 Sposobów na Excel	Excel 2007 w firmie. Controlling, finansei nie tylko	Więcej niż Excel 2007. 166 gotowychrozwiązań i trików w języku VBA