Analiza wystąpień określonych wartości

Przyjmijmy, że w arkuszu gromadzisz dane miesięczne dotyczące np. liczby przejechanych na rowerze tras lub jakiekolwiek inne wartości liczbowe. Potrzebujesz dowiedzieć się, od ilu miesięcy nie wystąpiła dana liczba i od ilu kolejnych miesięcy dana liczba się powtarza. Pokażemy, jak w prosty sposób wykonać taką analizę za pomocą formuły tablicowejformuła tablicowaformuły tablicowej w pierwszym przypadku i funkcji JEŻELI() w drugim.

W celu wykonania analizy, od ilu miesięcy nie wystąpiła dana liczba, należy:

1. Zaznaczyć komórkę C3 (w komórce C2 nie możesz obliczyć, od ilu miesięcy nie wystąpił dany wynik, ponieważ jest to pierwszy miesiąc i nie ma dla niego wartości do porównania) i wstawić następującą formułę:

=WIERSZ(B3)-MAX(JEŻELI($B$2:B2=B3;WIERSZ($B$2:B2));2)

2. Zatwierdzić ją kombinacją klawiszy [Ctrl]+[Shift]+[Enter]. Formuła zostanie ujęta w nawiasy klamrowe, co jest charakterystyczne dla formuły tablicowej. Nie próbuj dopisywać ręcznie nawiasów klamrowych. Jeżeli to zrobisz, Excel potraktuje całość jako tekst.

3. Skopiować ją w dół, aż do komórki C13.

Zauważ, że na skutek kopiowania, dzięki zastosowaniu adresowania bezwzględnegoadresowanie bezwzględneadresowania bezwzględnego pierwszej komórki, początkowa komórka obszaru działania formuły pozostaje bez zmian, natomiast ostatnia zmienia się zgodnie z pozycją formuły w arkuszu. Wynik powinien być taki jak widać na filmie.

Wyjaśnienie działania formuły:

Teraz przeanalizujmy działanie formuły na przykładzie komórki C9:

{=WIERSZ(B9)-MAX(JEŻELI($B$2:B8=B9;WIERSZ($B$2:B8));2)}

Funkcja WIERSZ() zwraca najpierw numer wiersza, w tym przypadku jest to liczba 9. Następnie formuła za pomocą funkcji JEŻELI() sprawdza, czy wartość z komórki B9, czyli liczba 4, występowała w zakresie B2:B8. Ze względu na to, że mamy do czynienia z formułą tablicową, kolejne wyniki sprawdzenia dla poszczególnych komórek są umieszczane w tablicy wyników. W przypadku gdy badana komórka zawiera liczbę 4, to w tablicy wyników jest umieszczany numer wiersza tej komórki. Jeżeli nie – wstawiana jest tam wartość logiczna FAŁSZ. Na koniec za pomocą funkcji MAX() z tablicy wyników jest wybierana największa wartość oznaczająca numer wiersza, w którym po raz ostatni wystąpiła poszukiwana wartość. Jeżeli tablica zawiera tylko wartości logiczne FAŁSZ, ignorowane przez funkcję MAX(), to zwrócona zostanie liczba 2, czyli numer początkowego wiersza zawierającego dane. Jeżeli otrzymaną wartość odejmiemy od numeru wiersza, w którym znajduje się formuła, uzyskamy liczbę miesięcy, od kiedy nie pojawiała się poszukiwana liczba. W naszym przypadku wartość 4 jeszcze nie występowała, więc zwracana jest liczba 2, zatem po odjęciu jej od liczby 9 otrzymujemy w wyniku 7, co oznacza, że liczba 4 nie pojawiła się od siedmiu miesięcy.

Aby sprawdzić, od ilu kolejnych miesięcy dana liczba się powtarza, wykonaj następujące czynności:

1. Zaznacz komórkę D2 i wprowadź następującą formułę:

=JEŻELI(C2=1;D1;0)+1

2. Skopiuj ją aż do komórki D13.

Wynik powinien być taki jak widać na filmie.

Jak widać, dla lipca wartość 2 wystąpiła np. miesiąc temu i powtarza się już czwarty raz. W sierpniu wartość 4 nie pojawiła się w zestawieniu od siedmiu miesięcy i powtórzyła się tylko raz.

Wyjaśnienie działania formuły:

Zasada działania jest bardzo prosta. Funkcja warunkowafunkcja warunkowaFunkcja warunkowa JEŻELI() sprawdza, czy w komórce kolumny C znajduje się liczba 1, co oznacza, że dana wartość pojawiła się w poprzednim miesiącu. Jeżeli tak, to zwraca wartość z komórki w kolumnie D umieszczonej powyżej. Jeśli nie, to zwraca wartość 0. Następnie do wyniku dodawana jest liczba 1. W naszym przypadku w komórce C2 znajduje się wartość równa 0, więc w komórce D2 w wyniku otrzymujemy 0 + 1, czyli 1.

RndcyNZCN1BGv

Nagranie filmowe dotyczące analizy wystąpień określonych wartości.

Nagranie filmowe dotyczące analizy wystąpień określonych wartości.

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

Nagranie filmowe dotyczące analizy wystąpień określonych wartości.

Pobierz przykładowe dane:

Polecenie 1

Załóżmy, że twoim celem jest przygotowanie się podczas wakacji do twojego pierwszego triathlonu. Treningi zostały podzielone w ten sposób, że jednego dnia biegasz, drugiego pływasz, a jeszcze innego jeździsz na rowerze. Rodzaje swoich treningów każdego dnia zapisujesz w Excelu. Twoim zadaniem jest, korzystając z poznanych funkcji, sprawdzić jakie były przerwy między jednym rodzajem treningu w danym okresie czasu. Dzięki takiemu zestawieniu dowiesz się, jaką dyscyplinę zdarza ci się najbardziej zaniedbywać.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

RmwBMJLrXa6eH

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A i B wprowadzono dane dotyczące rodzajów treningu w konkretnych dniach. W arkuszu kolejno dodano opisy: w komórce A1 Data, w komórce B1 Rodzaj treningu. W kolumnie A, w komórkach od A2 do A16 wpisano kolejne daty. W kolumnie B, w komórkach od B2 do B16 wpisano rodzaje treningów, na przykład pływanie, rower. Dodatkowo zaprezentowano sposób obliczania braku wystąpień. Formułę obliczania należy wpisać w odpowiedniej komórce. W tym przypadku w komórce C3. Brzmi ona następująco: =WIERSZ(B3)-MAX(JEŻELI($B$2:B2=B3;WIERSZ($B$2:B2));2).

Polecenie 2

Rozbuduj swój plan treningowy o kolejną kolumnę, w której sprawdzisz, ile razy w kolejnych dniach trenowana była ta sama dyscyplina.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

RfwASZKvyjwDG

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A i B wprowadzono dane dotyczące rodzajów treningu w konkretnych dniach. W arkuszu kolejno dodano opisy: w komórce A1 Data, w komórce B1 Rodzaj treningu. W kolumnie A, w komórkach od A2 do A16 wpisano kolejne daty treningów. W kolumnie B, w komórkach od B2 do B16 wpisano rodzaje treningów, na przykład pływanie, rower. Komórkę C2 zatytułowano Brak wystąpień. Dodatkowo zaprezentowano sposób obliczania, ile razy w kolejnych dniach trenowana była ta sama dyscyplina. Formułę obliczania należy wpisać w odpowiedniej komórce. W tym przypadku w komórce D2. Brzmi ona następująco: =JEŻELI(C2=1;D1;0)+1.

Słownik