Przeczytaj
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ł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:
Zaznaczyć komórkę
C3
(w komórceC2
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łę:
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.Skopiować ją w dół, aż do komórki
C13
.
Zauważ, że na skutek kopiowania, dzięki zastosowaniu adresowania bezwzględnegoadresowania 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
:
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:
Zaznacz komórkę
D2
i wprowadź następującą formułę:
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 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
.
Pobierz przykładowe dane:
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ć.
Rozbuduj swój plan treningowy o kolejną kolumnę, w której sprawdzisz, ile razy w kolejnych dniach trenowana była ta sama dyscyplina.
Słownik
adresowanie nakazujące bezwzględne odwoływanie się do adresowanej komórki; wykorzystuje znak dolara, np. $B$3, $H$7
wydajne formuły pozwalające na wykonywanie złożonych obliczeń, których często nie da się wykonać za pomocą standardowych funkcji arkusza; są one nazywane również formułami „Ctrl Shift Enter” lub „CSE”, ponieważ do ich wpisania konieczne jest naciśnięcie klawiszy Ctrl+Shift+Enter
element języka programowania, pozwalający na wykonanie różnych instrukcji – w zależności od tego czy zdefiniowane przez programistę wyrażenie logiczne jest prawdziwe czy fałszywe; możliwość warunkowego decydowania o tym, jaki krok zostanie wykonany w dalszej kolejności