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.

Sumowanie komórek niepustych

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:

Suma komórek pustych

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.

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

Suma niepustych komórek

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, finanse
i nie tylko
Więcej niż Excel 2007.
166 gotowych
rozwiązań i trików
w języku VBA

Komentarze:

  1. radek napisał,

    Dobre

  2. radek napisał,

    podobne rzeczy można robić za pomocą funkcji SUMPRODUCT

Dodaj komentarz:

Autor: Rafał Kraik