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
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"});
{"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
Zobacz także:
