Sumowanie danych o niestandardowym układzie

Autor: Piotr Gromulski

Nie napotkasz większych problemów z obliczeniami warunkowymi, gdy wszystkie dane są zgromadzone w zestawieniu zachowującym układ listy. Excel oferuje bowiem wiele funkcji warunkowych, a także narzędzia umożliwiające niestandardowe sumowanie danych. Niestety możesz otrzymać arkusz, który zawiera wiele niedużych tabel cząstkowych umieszczonych obok siebie.

Przy takim układzie danych będziesz mieć kłopoty z przeprowadzeniem warunkowego podsumowania. Pokażemy Ci zatem, jaką formułą się posłużyć, aby takie obliczenia z łatwością wykonywać.

Przykładowy arkusz przedstawia rysunek 1. Przyjmijmy, że Twoim zadaniem jest wyznaczenie sumy wszystkich liczb odnoszących się do nazwiska Kowalik.

Rys. 1. Przykładowy arkusz z zestawieniami cząstkowymi

W tym celu:

1. W dowolnej pustej komórce arkusza (np. H3) wprowadź nazwisko stanowiące kryterium obliczeń warunkowych.

2. W komórce poniżej wpisz następującą formułę:

=SUMA(SUMA.JEŻELI(ADR.POŚR(

{"A3:A8";"A12:A17";"D3:D8";"D12:D17"});

H3;ADR.POŚR(

{"B3:B8";"B12:B17";"E3:E8";"E12:E17"})))

3. Zatwierdź ją kombinacją klawiszy Ctrl + Shift + Enter, ponieważ jest to formuła tablicowa. Poprawnie wprowadzona zostanie ujęta w nawiasy klamrowe widoczne na pasku formuły.

Uzyskasz oczekiwany efekt, jak jest to przedstawione na rysunku 2.

Rys. 2. Suma warunkowa z zestawień cząstkowych

Wyjaśnienie działania formuły:

- Pierwszy argumentem funkcji SUMA.JEŻELI jest zakres komórek zawierających kryteria podsumowania. W naszym przykładzie potrzebny jest zestaw takich zakresów, ponieważ formuła operuje na większej liczbie zestawień cząstkowych.

- Do połączenia zakresów zastosowaliśmy funkcję ADR.POŚR. Wymaga ona argumentów tekstowych, a zatem każde odwołanie do zakresu komórek zostało ujęte w cudzysłów. Zestaw wszystkich odwołań ujęto w tablice zawartą między nawiasami klamrowymi.

- Dzięki takiemu zapisowi funkcja SUMA.JEŻELI sprawdzi każdą komórkę wszystkich zestawień cząstkowych pod kątem występowania kryterium. Jest ono określone w komórce H3, dlatego jej adres został podany w drugim argumencie funkcji SUMA.JEŻELI.

- W trzecim argumencie tej funkcji za pomocą drugiej funkcji ADR.POŚR podajesz tablicę zakresów komórek, w których znajdują się liczby do zsumowania.

- W obrębie każdego zestawienia cząstkowego dokonywane jest osobne podsumowanie i powstaje tablica wyników.

- Jest ona przekazywana funkcji SUMA, która dodaje otrzymane wcześniej sumy cząstkowe i zwraca ostateczny wynik działania formuły.

Piotr Gromulski