Program Excel ma wbudowane narzędzia dedykowane do obliczeń warunkowych. Wykonywanie ich nie powinno więc sprawiać nam większych kłopotów, szczególnie w przypadku, gdy wszystkie dane zgromadzone są w zestawieniu zachowującym układ listy. Może się jednak zdarzyć tak, że nasz arkusz będzie zawierał wiele niedużych tabel cząstkowych umieszczonych obok siebie. Przy takim układzie danych przeprowadzenie warunkowego podsumowania będzie wymagało dodatkowych zabiegów.
Na filmie widzimy przykładowy arkusz. Naszym zadaniem będzie wyznaczenie sumy wszystkich liczb odnoszących się do piątku. W tym celu:
W dowolnej pustej komórce arkusza (np. H3) wprowadź nazwę dnia tygodnia stanowiącą kryterium obliczeń warunkowych.
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 filmie.
W jaki sposób działa ta formuła? 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 tablicę 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 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 podaj 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 przekazywana funkcji SUMA, która dodaje otrzymane wcześniej sumy cząstkowe i zwraca ostateczny wynik działania formuły.
R16EHWEcCKD28
Nagranie filmowe dotyczące podsumowania warunkowego z zestawień cząstkowych
Nagranie filmowe dotyczące podsumowania warunkowego z zestawień cząstkowych
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Razem ze swoją rodziną grasz w grę polegającą na tym, że za wykonywanie obowiązków domowych każdy z domowników jest nagradzany punktami. Na koniec każdego miesiąca podliczacie zdobyte punkty i ustalacie, kto jest zwycięzcą, tzn. uzyskał najwięcej punktów. Stwórz cztery tabele (jedną na każdy tydzień miesiąca) zawierającą kolejno dwie kolumny: imię oraz liczbę punktów. Napisz formułę, która po wpisaniu w odpowiednią komórkę imienia podliczy liczbę zdobytych przez tę osobę punktów.
Przykładowe rozwiązanie zadania:
RLbjiYVLNDdJa
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A, B, D oraz E wprowadzono dane. W ARKUSZU kolejno dodano opisy. W kolumnie A, w komórce A2 wpisano tytuł IMIĘ. W komórkach od A3 do A8 wpisano imiona. W kolumnie B, w komórce B2 wpisano tytuł LICZBA PUNKTÓW. W komórkach od B3 do B8 wprowadzono wartości liczbowe. W kolumnie A11 wprowadzono tytuł IMIĘ. W komórkach A12 do A17 wprowadzono imiona. W kolumnie B, w komórkach B11 wpisano tytuł LICZBA PUNKTÓW. W komórkach od B12 do B17 wprowadzono wartości liczbowe. W kolumnie D, w komórce D2 wprowadzono tytuł IMIĘ. W komórkach D3 do D8 wpisano imiona. W kolumnie E2 wpisano tytuł LICZBA PUNKTÓW. W komórkach E3 do E8 wprowadzono wartości liczbowe. W kolumnie D, w komórce D11 wprowadzono tytuł IMIĘ. W komórkach D12 do D17 wpisano imiona. W kolumnie E11 wpisano tytuł LICZBA PUNKTÓW. W komórkach E12 do E17 wprowadzono wartości liczbowe. W kolumnie G, w komórce G2 wpisano tytuł IMIĘ. W kolumnie H, w komórce H2 wpisano tytuł SEBASTIAN. W komórce H3 wpisano formułę, która brzmi następująco: =SUMA(SUMA.JEŻELI(ADR.POŚR({”A3:A8”’;”A12:A17”’;”D3:D8”’;”D12:D17”});H2;ADR.POŚR({”B3:B8”’;”B12:B17”’;”E3:E8”’;”E12:E17”})))
3
Polecenie 2
Zmodyfikuj arkusz z poprzedniego zadania. Policz, ile każdy z domowników zdobył punktów. Wyznacz największy wynik i wypisz, kto go otrzymał.
Przykładowe rozwiązanie zadania:
R16rrGNdIiDE2
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A, B, D oraz E wprowadzono dane. W ARKUSZU kolejno dodano opisy. W kolumnie A, w komórce A2 wpisano tytuł IMIĘ. W komórkach od A3 do A8 wpisano imiona. W kolumnie B, w komórce B2 wpisano tytuł LICZBA PUNKTÓW. W komórkach od B3 do B8 wprowadzono wartości liczbowe. W kolumnie A11 wprowadzono tytuł IMIĘ. W komórkach A12 do A17 wprowadzono imiona. W kolumnie B, w komórkach B11 wpisano tytuł LICZBA PUNKTÓW. W komórkach od B12 do B17 wprowadzono wartości liczbowe. W kolumnie D, w komórce D2 wprowadzono tytuł IMIĘ. W komórkach D3 do D8 wpisano imiona. W kolumnie E2 wpisano tytuł LICZBA PUNKTÓW. W komórkach E3 do E8 wprowadzono wartości liczbowe. W kolumnie D, w komórce D11 wprowadzono tytuł IMIĘ. W komórkach D12 do D17 wpisano imiona. W kolumnie E11 wpisano tytuł LICZBA PUNKTÓW. W komórkach E12 do E17 wprowadzono wartości liczbowe. W kolumnie G, w komórce G1 wpisano tytuł IMIĘ. W komórkach od G2 do G8 wpisano imiona. W kolumnie H, w komórce H1 wpisano tytuł ZDOBYTE PUNKTY. W komórkach od H2 do H8 wpisano wartości liczbowe. W kolumnie G, w komórce G11 wpisano tytuł ZWYCIĘZCA. W kolumnie H, w komórce H11 wpisano imię IZABELA. W kolumnie I, w komórce I11 wpisano formułę, która brzmi następująco: =MAX(H2:H7)
R4DNa9shcBvvR
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A, B, D oraz E wprowadzono dane. W ARKUSZU kolejno dodano opisy. W kolumnie A, w komórce A2 wpisano tytuł IMIĘ. W komórkach od A3 do A8 wpisano imiona. W kolumnie B, w komórce B2 wpisano tytuł LICZBA PUNKTÓW. W komórkach od B3 do B8 wprowadzono wartości liczbowe. W kolumnie A11 wprowadzono tytuł IMIĘ. W komórkach A12 do A17 wprowadzono imiona. W kolumnie B, w komórkach B11 wpisano tytuł LICZBA PUNKTÓW. W komórkach od B12 do B17 wprowadzono wartości liczbowe. W kolumnie D, w komórce D2 wprowadzono tytuł IMIĘ. W komórkach D3 do D8 wpisano imiona. W kolumnie E2 wpisano tytuł LICZBA PUNKTÓW. W komórkach E3 do E8 wprowadzono wartości liczbowe. W kolumnie D, w komórce D11 wprowadzono tytuł IMIĘ. W komórkach D12 do D17 wpisano imiona. W kolumnie E11 wpisano tytuł LICZBA PUNKTÓW. W komórkach E12 do E17 wprowadzono wartości liczbowe. W kolumnie G, w komórce G1 wpisano tytuł IMIĘ. W komórkach od G2 do G8 wpisano imiona. W kolumnie H, w komórce H1 wpisano tytuł ZDOBYTE PUNKTY. W komórkach od H3 do H8 wpisano wartości liczbowe. W komórce H2 wpisano formułę, która brzmi następująco: =SUMA(SUMA.JEŻELI(ADR.POŚR({”A3:A8”’;”A12:A17”’;”D3:D8”’;”D12:D17”});G2;ADR.POŚR({”B3:B8”’;”B12:B17”’;”E3:E8”’;”E12:E17”}))) W kolumnie G, w komórce G11 wpisano tytuł ZWYCIĘZCA. W kolumnie H, w komórce H11 wpisano imię IZABELA. W kolumnie I, w komórce I11 wpisano wartość liczbową.
R1Vicvf61fHVq
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A, B, D oraz E wprowadzono dane. W ARKUSZU kolejno dodano opisy. W kolumnie A, w komórce A2 wpisano tytuł IMIĘ. W komórkach od A3 do A8 wpisano imiona. W kolumnie B, w komórce B2 wpisano tytuł LICZBA PUNKTÓW. W komórkach od B3 do B8 wprowadzono wartości liczbowe. W kolumnie A11 wprowadzono tytuł IMIĘ. W komórkach A12 do A17 wprowadzono imiona. W kolumnie B, w komórkach B11 wpisano tytuł LICZBA PUNKTÓW. W komórkach od B12 do B17 wprowadzono wartości liczbowe. W kolumnie D, w komórce D2 wprowadzono tytuł IMIĘ. W komórkach D3 do D8 wpisano imiona. W kolumnie E2 wpisano tytuł LICZBA PUNKTÓW. W komórkach E3 do E8 wprowadzono wartości liczbowe. W kolumnie D, w komórce D11 wprowadzono tytuł IMIĘ. W komórkach D12 do D17 wpisano imiona. W kolumnie E11 wpisano tytuł LICZBA PUNKTÓW. W komórkach E12 do E17 wprowadzono wartości liczbowe. W kolumnie G, w komórce G1 wpisano tytuł IMIĘ. W komórkach od G2 do G8 wpisano imiona. W kolumnie H, w komórce H1 wpisano tytuł ZDOBYTE PUNKTY. W komórkach od H2 do H8 wpisano wartości liczbowe. W kolumnie G, w komórce G11 wpisano tytuł ZWYCIĘZCA. W kolumnie H, w komórce H11 wpisano formułę, która brzmi następująco: =INDEKS(G2:G7;PODAJ.POZYCJĘ(I11;H2:H7;0))
