Zliczanie za pomocą formuły tablicowej

Przyjmijmy, że masz zestawienie dat zakończonych partii w jednej z gier i chcesz sprawdzić, ile z nich wypada w danym miesiącu. Daty są z ostatnich kilku lat, a więc niestety takie zliczenie wydaje się skomplikowane.

Wyciągnięcie oznaczenia miesiąca za pomocą funkcji MIESIĄC() i zliczenie tych wartości może się okazać czasochłonne. Aby to zrobić szybciej, zastosuj formułę tablicową.

W tym celu:

1. Pod każdą z kolumn w wierszach 10. i 11. wprowadź odpowiednio: oznaczenie roku oraz numer miesiąca.

2. W komórce poniżej wprowadź formułę tablicową:

=SUMA((ROK(B3:B8)=B10)*(MIESIĄC(B3:B8)=B11))
1
 
1
=SUMA((ROK(B3:B8)=B10)*(MIESIĄC(B3:B8)=B11))

3. Zatwierdź ją za pomocą kombinacji klawiaturowej [Ctrl]+[Shift]+[Enter].

4. Na koniec skopiuj formułę w prawo.

Wyjaśnienie działania formuły:

Funkcja ROK() sprawdza, czy każda komórka zakresu B3:B8 zawiera datę z 2014 r. Jeżeli tak, wówczas wynikiem jest wartość logiczna PRAWDA, jeżeli nie – FAŁSZ. W ten sposób tworzy się tablicę wartości logicznych. W analogiczny sposób działa funkcja MIESIĄC(), ale sprawdza zgodność miesięcy.

W arkuszu kalkulacyjnym wartość logiczna PRAWDA jest równa liczbie 1, a FAŁSZ to 0. Poszczególne elementy obu tablic są przez siebie przemnażane i powstaje tablica złożona z samych zer i jedynek. Wszystkie elementy są dodawane za pomocą funkcji SUMA() i zwracany jest wynik końcowy.

RgO8kFJRlUD0b

Nagranie filmowe dotyczące zliczania za pomocą formuły tablicowej.

Nagranie filmowe dotyczące zliczania za pomocą formuły tablicowej.

play_circle_outlinepause_circle_outlineOdtwórz/Zatrzymaj

record_voice_overaccessibilityAlternatywna ścieżkasubtitlesNapisy

Napisy

• Napisy
• wyłączone

1x Prędkość odtwarzania

Prędkość

• Po klatce
• 0.25
• 0.5
• 0.75
• Normalna
• 1.25
• 1.5
• 1.75
• 2

fullscreenfullscreen_exitPełny ekran

Trwa wczytywanie danych...

Film dostępny pod adresem /preview/resource/RgO8kFJRlUD0b

Nagranie filmowe dotyczące zliczania za pomocą formuły tablicowej.

Pobierz plik z danymi:

Ćwiczenie 1

Przygotuj arkusz zestawiający rozegrane gry lub mecze sportowe wraz z ich wynikami. Używając poznanych funkcji oblicz, ile partii w każdym miesiącu zakończyło się wygraną, ile przegraną, a ile remisem.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

R1O5QYUKSRgOG

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W arkuszu kolejno dodano opisy: w komórce A1 data meczu, w komórce B1 zdobyte punkty, w komórce C1 stracone punkty. W kolumnie A, w komórkach od A2 do A15 wpisano daty meczów. W kolumnie B, w komórkach od B2 do B15 wpisano liczbę zdobytych punktów. W kolumnie C, w komórkach od C2 do C15 wpisano liczbę straconych punktów. W komórce E6 wpisano tytuł Miesiąc, pod nim w komórkach od E7 do E10 wpisano numery od 1 do 4. W komórce F6 wpisano tytuł Wygrane. W komórkach od F8 do F10 wpisano liczby. W komórce F7 wpisano formułę =SUMA(MIESIĄC($A&2:$A$15)=$E7)*(&B&2:$B$15>$C$2:$C$15)). W komórce G6 wpisano tytuł Przegrane, pod nim w komórkach od G7 do G10 liczby, są to same 1. W komórce H6 wpisano tytuł Remisy, pod nim w komórkach wpisano liczby.

Kliknij, aby uruchomić podgląd

RKLzVOAKuGcGs

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W arkuszu kolejno dodano opisy: w komórce A1 data meczu, w komórce B1 zdobyte punkty, w komórce C1 stracone punkty. W kolumnie A, w komórkach od A2 do A15 wpisano daty meczów. W kolumnie B, w komórkach od B2 do B15 wpisano liczbę zdobytych punktów. W kolumnie C, w komórkach od C2 do C15 wpisano liczbę straconych punktów. W komórce E6 wpisano tytuł Miesiąc, pod nim w komórkach od E7 do E10 wpisano numery od 1 do 4. W komórce F6 wpisano tytuł Wygrane. W komórkach od F7 do F10 wpisano liczby. W komórce G6 wpisano tytuł Przegrane, pod nim w komórkach od G7 do G10 liczby, są to same 1. W komórce H6 wpisano tytuł Remisy, pod nim w komórkach od H8 do H10 wpisano liczby. W komórce H7 wpisano formułę =SUMA((MIESIĄC($A$2:$A$15)=$E7)*($B$2:$B$15=$C$15))

Kliknij, aby uruchomić podgląd

Ćwiczenie 2

Zmodyfikuj przygotowany arkusz i przedstaw uzyskane dane w formie wykresu liniowego.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

RVRNnAxoItnL0

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W komórce A1 wpisano tytuł data meczu. W komórce B1 wpisano tytuł zdobyte punkty. W komórce C1 wpisano tytuł stracone punkty. W kolumnie A, w komórkach od A2 do A15 wpisano daty meczów. W kolumnie B, w komórkach od B2 do B15 wpisano liczbę zdobytych punktów. W kolumnie C, w komórkach od C2 do C15 wpisano liczbę straconych punktów. W komórce F6 wpisano tytuł wygrane. W komórce G6 wpisnao tytuł przegrane. W komórce H6 wpisano tytuł remisy. W komórkach od E7 do E10 wpisano wartości od 1 do 4. W komórkach od F7 do H10 wpisano różne wartości od 0 do 4. Wyniki zaprezentowano w formie wykresu liniowego. Wykres przedstawia 4 linie, każda ma 4 punkty, które oznaczono wartościami liczbowymi którym odpowiadają.

Kliknij, aby uruchomić podgląd