Obliczenia warunkowe nie powinny sprawiać większych problemów, jeśli wszystkie dane są zgromadzone w zestawieniu zachowującym układ listy. Arkusz kalkulacyjny oferuje bowiem wiele funkcji warunkowych, a także narzędzia dedykowane do tego typu operacji. Niestety, można napotkać arkusz, który zawiera wiele niedużych tabel cząstkowych umieszczonych obok siebie. Przy takim układzie danych przeprowadzenie warunkowego podsumowania wymaga dodatkowych zabiegów.
Przykładowy arkusz przedstawiamy na filmie. Przyjmijmy, że zadaniem jest wyznaczenie sumy wszystkich liczb odnoszących się do piątku.
W tym celu:
W dowolnej pustej komórce arkusza (np. H3) wprowadźmy nazwę dnia tygodnia stanowiącą kryterium obliczeń warunkowych.
Zatwierdźmy 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.
Uzyskamy oczekiwany efekt, jak jest to przedstawione na filmie.
Wyjaśnienie działania formuły:
Pierwszym 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, która wymaga 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 tablicę zawartą między nawiasami klamrowymi.
Dzięki takiemu zapisowi funkcja SUMA.JEŻELI sprawdzi każdą komórkę 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 podajemy 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. Tablica jest przekazywana funkcji SUMA, która dodaje otrzymane wcześniej sumy cząstkowe i zwraca ostateczny wynik działania formuły.
RYvFq83a0V7fR
Film nawiązujący do treści materiału: Sumowanie warunkowe z zestawień cząstkowych.
Film nawiązujący do treści materiału: Sumowanie warunkowe z zestawień cząstkowych.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Razem z przyjaciółmi postanowiliście zagrać w kalambury. Podzieliliście się na dwie drużyny. Każda drużyna otrzymywała tyle punktów, ile haseł udało się jej odgadnąć w ciągu minuty. Rozegraliście trzy gry. Na każdą z nich składały się trzy rundy, podczas których obie drużyny próbowały zdobyć punkty. Wyniki z każdej gry zapisywaliście w oddzielnej tabelce arkusza. Oblicz liczbę punktów zdobytych przez każdą drużynę w poszczególnych rundach.
Zrzut ekranu przedstawia tabelę w arkuszu bazy danych. W kolumnie kolumnie B, w komórce B1 znajduje się napis: 1 gra. Poniżej w kolumnach A, B, C wpisano opisy: w komórce A3 Runda, w komórce B3 Drużyna A, w komórce C3 Drużyna B. W kolumnie kolumnie F, w komórce F1 znajduje się napis: 2 gra. Poniżej w kolumnach E, F, G wpisano opisy: w komórce E3 Runda, w komórce F3 Drużyna A, w komórce G3 Drużyna B. W komórkach od E4 do G6 znajdują się wartości liczbowe. W kolumnie kolumnie B, w komórce B8 znajduje się napis: 3 gra. Poniżej w kolumnach A, B, C wpisano opisy: w komórce A10 Runda, w komórce B10 Drużyna A, w komórce C10 Drużyna B. W komórkach od A11 do C13 znajdują się wartości liczbowe. W kolumnie kolumnie F i G, w scalonych komórkach F8 i G8 znajduje się napis: Podsumowanie. Poniżej w kolumnach E, F, G wpisano opisy: w komórce E10 Runda, w komórce F10 Drużyna A, w komórce G10 Drużyna B. W komórkach od F11 do G13 znajdują się wartości liczbowe.
Polecenie 2
Masz jakieś hobby, na które w tygodniu poświęcasz pewną ilość czasu. Czas ten zapisujesz w arkuszu kalkulacyjnym obok nazwy odpowiedniego dnia tygodnia. Każda tabelka to inny tydzień. Udało ci się zebrać dane z trzech ostatnich tygodni. Podsumuj, ile czasu udało ci się przeznaczyć na twoje hobby w podziale na poszczególne dni tygodnia.
Przykładowe rozwiązanie zadania:
RF5aoDmdWgtRo
Zrzut ekranu przedstawia tabelę w arkuszu bazy danych. W kolumnie kolumnie A, w komórce A1 znajduje się napis: 1 tydzień. Poniżej w kolumnach A, B wpisano opisy: w komórce A2 Dzień tygodnia, w komórce B2 Ilość czasu. W komórkach od A3 do A9 wypisano dni tygodnia. W komórkach od B3 do B9 wypisano wartości liczbowe. W kolumnie kolumnie D, w komórce D1 znajduje się napis: 1 tydzień. Poniżej w kolumnach D, E wpisano opisy: w komórce D2 Dzień tygodnia, w komórce E2 Ilość czasu. W komórkach od D3 do D9 wypisano dni tygodnia. W komórkach od E3 do E9 wypisano wartości liczbowe. W kolumnie kolumnie G, w komórce G1 znajduje się napis: 1 tydzień. Poniżej w kolumnach G, H wpisano opisy: w komórce G2 Dzień tygodnia, w komórce H2 Ilość czasu. W komórkach od G3 do G9 wypisano dni tygodnia. W komórkach od H3 do H9 wypisano wartości liczbowe. W kolumnie kolumnie A, w komórce A11 znajduje się napis: Podsumowanie. Poniżej w kolumnach A, B wpisano opisy: w komórce A12 Dzień tygodnia, w komórce B12 Ilość czasu. W komórkach od A13 do A19 wypisano dni tygodnia. W komórce B13 znajduje się formuła: =SUMA(SUMA.JEŻELI(ADR.POŚR({"A3:A9";"D3:D9";"G3:G9"});A13;ADR.POŚR({"B3:B9";"E3"E9";"H3:H9"}))) W komórkach od B14 do B19 wypisano wartości liczbowe.
