Aby podsumować kwoty w obrębie kolumny, które spełniają warunek oparty na wartości w innej kolumnie, najwygodniej skorzystać z funkcji SUMA.JEŻELI. Przykładowa formuła: =SUMA.JEŻELI(A2:A10;C1;B2:B10) zsumuje wszystkie liczby zawarte w zakresie B2:B10, dla których odpowiadające im wartości w kolumnie A są takie same jak kryterium zawarte w komórce C1.
Wykonanie takiego podsumowania nie jest rzeczą trudną. Problem pojawi się wówczas, gdy chcesz podsumować w ten sposób liczby (kwoty) rozrzucone po różnych arkuszach.
Poniższa ilustracja przedstawia proste zestawienie z danymi zawarte w arkuszu Sty. Podobne znajdują się w arkuszach Lut i Mar.
Twoim zadaniem jest obliczenie w arkuszu Podsumowanie sumy wszystkich kwot, które odnoszą się do określonego miasta (np. Warszawy).
- Przejdź do arkusza Podsumowanie i w dowolnej pustej komórce (np. A2) wpisz kryterium, czyli nazwę Warszawa.
Następnie z menu Wstaw wybierz polecenie Nazwa (w Excelu 2007: polecenie Definiuj nazwę znajdziesz na karcie Formuły).
Zostanie wyświetlone okno dialogowe Definiowanie nazwy. W polu Nazwy w skoroszycie wpisz Arkusze.
W polu Odwołuje się do wprowadź natomiast takie odwołanie:
={"Sty";"Lut";"Mar"}- Kliknij przycisk Dodaj, a następnie Zamknij.
Definiowanie nazwy
Dzięki zastosowaniu nawiasów klamrowych odwołałeś się do tablicy stałych, której składnikami są nazwy arkuszy z danymi cząstkowymi. Takie odwołanie będzie Ci dalej potrzebne przy budowaniu formuły.
Aby obliczyć sumę kwot spełniających kryterium w komórce A2 arkusza Podsumowanie:
- Wprowadź do komórki B2 tego arkusza następującą formułę:
=SUMA.ILOCZYNÓW(SUMA.JEŻELI(ADR.POŚR
("'"&Arkusze&"'!A2:A10");A2;ADR.POŚR("'"&Arkusze&"'!B2:B10")))
Jeżeli nazwy Twoich arkuszy nie zawierają spacji, to możesz nieco uprościć formułę, pomijając w niej dodawanie znaku apostrofu do adresu obliczanych obszarów:
=SUMA.ILOCZYNÓW(SUMA.JEŻELI(ADR.POŚR
(Arkusze&"!A2:A10");A2;ADR.POŚR(Arkusze&"!B2:B10")))
Formuła zwróciła poprawny wynik
Wyjaśnienie działania formuły:
Dzięki temu, że zdefiniowałeś odwołanie do tablicy zawierającej nazwy arkuszy, pierwsza funkcja ADR.POŚR przyjęła następujące argumenty:
{"'Sty'!A2:A10";"'Lut'!A2:A10";"'Mar'!A2:A10"}.
Analogicznie druga funkcja przyjęła podobne argumenty różniące się jedynie odwołaniem do kolumny B
{"'Sty'!B2:B10";"'Lut'!B2:B10";"'Mar'!B2:B10"}.
A zatem można przyjąć, że wewnątrz jednej formuły mamy 3-krotnie zastosowaną funkcję SUMA.JEŻELI, która za każdym razem odwołuje się do innego arkusza. W naszym przykładzie zwróci ona tablicę wyników podsumowania kwot odwołujących się do miasta Warszawa ze wszystkich arkuszy {737;1763;404}. Nominalnym zadaniem funkcji SUMA.ILOCZYNÓW jest przemnożenie odpowiadających sobie elementów kilku tablic, a następnie zsumowanie tych iloczynów. Ze względu na to, że w wyniku działania przedstawionej formuły otrzymujemy tylko jedną tablicę składającą się z 3 elementów, etap mnożenia jest pomijany i od razu zwrócona jest suma wszystkich elementów.
Równie dobrze zamiast funkcji SUMA.ILOCZYNÓW możesz zastosować funkcję SUMA. Pamiętaj jednak, że tak zmienioną formułę musisz zatwierdzić tablicowo, czyli za pomocą kombinacji klawiszy Ctrl + Shift + Enter.
Piotr Gromulski
Zobacz także:
