Wśród wielu funkcji, jakie oferuje arkusz kalkulacyjny, znajdują się także kategorie specjalistyczne, np. finansowe, inżynierskie, matematyczne oraz te, które zaprezentujemy w lekcji: statystyczne.
Microsoft Excel
Powyższe kategorie można odnaleźć, wybierając Formuły ze wstążki, a następnie w obszarze Biblioteka funkcji, wybierając opcję Więcej funkcji.
RcYEXJVVBkH141
Ilustracja przedstawia program Microsoft Excel. Ze wstążki wybrano zakładkę Formuły, a w niej: Więcej funkcji. Rozwinęła się lista, z której wybrano: Statystyczny. Po prawej stronie rozwinęła się kolejna lista, a na niej między innymi opcje: CHI.TEST, CZĘSTOŚĆ, F.TEST, FORECAST.ETS, GAMMA, GAUSS, KURTOZA, LICZ.JEŻELI, LICZ.PUSTE.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Funkcje specjalistyczne przeznaczone są głównie dla użytkowników, którzy znają dany obszar wiedzy i jej zastosowań (np. dla statystyki funkcjefunkcje statystycznestatystyki funkcje te znajdują się w kategorii Statystyczny), jednak wśród tych funkcji możemy znaleźć także takie, które pomogą rozwiązać wiele częstych problemów u mniej zaawansowanych użytkowników.
LibreOffice Calc
Powyższe kategorie można odnaleźć, klikając ikonkę fx przy pasku formuły.
RfdecIJGY3FXE
Ilustracja przedstawia fragment paska formuły. Na początku jest fx. Obok jest symbol sumy.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Opcja ta znajduje się również w menu głównym po wybraniu Wstaw.
R1aO2hWlucgZN
Ilustracja przedstawia okno programu LibreOffice Calc. Z menu wybrano opcję: Wstaw. Po rozwinięciu listy wybrano: Funkcja, Ctrl+F2.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
W wyświetlonym oknie możemy łatwo wyszukać lub wybrać odpowiadającą nam funkcję, wpisać ją i od razu podejrzeć wynik.
R1BRVe4Mo8R2v
Ilustracja przedstawia okno dialogowe dotyczące Kreatora funkcji. Wybrano zakładkę: Funkcje. Na liście zaznaczono opcję Wszystko. Zastosowano przycisk OK.
Funkcje specjalistyczne przeznaczone są głównie dla użytkowników, którzy znają dany obszar wiedzy i jej zastosowań (np. dla statystyki funkcje te znajdują się w kategorii Statystyka), jednak wśród tych funkcji możemy znaleźć także takie, które pomogą rozwiązać wiele częstych problemów u mniej zaawansowanych użytkowników.
Przykład
W jednej ze szkół zorganizowano zawody o charakterze edukacyjnym (uświadomienie młodzieży szkolnej zasad segregacji śmieci). Zawody miały być przeprowadzone w kwartałach leśnych, które wyznaczono dla każdej klasy. Uczniowie mieli zbierać śmieci w tej kategorii, na którą natknęli się w lesie jako pierwszą. W workach miały być posegregowane szkło, metal, plastyk oraz pozostałe rodzaje. W celu rejestracji wyników działań podjętych przez uczniów przygotowano arkusz, do którego wpisano liczbę worków w poszczególnych kategoriach śmieci (pod warunkiem, że worki te były całkowicie wypełnione).
Dane obrazujące rodzaj śmieci i ich ilość zostały przedstawione w dwóch odrębnych kolumnach (np. kolumny B i C dla edycji ). Ponieważ w trakcie zawodów okazało się, że trzeba przeprowadzić następne edycje rywalizacji (tak dużo zostało śmieci w lesie), organizator zdecydował się dołożyć następne pary kolumn za kolumną C. W kolumnie ostatniej (H) wprowadzono formułę sumującą wartości z trzech kolumn: C, E, G – co pokazuje ilościowy wynik osiągnięty przez daną klasę.
1
Microsoft Excel
Microsoft Excel
Z tak zorganizowanego pliku danych można wydobyć wiele informacji. Na przykład: jeśli chcemy się dowiedzieć, ile klas znalazło śmieci z plastiku w pierwszej edycji, zastosujemy formułę LICZ.JEŻELI która zwraca liczbę komórek spełniających określone kryterium. Składnia tej formuły jest następująca:
LICZ.JEŻELI(zakres;kryteria)
Przy czym zakres to adres pojedynczej komórki, różnych komórek lub obszaru komórek, a kryteria to warunek, jaki musi spełnić zawartość komórki z zakresu, aby została ona zliczona.
Chcemy sprawdzić ile klas w pierwszej edycji zawodów zbierało plastyk. Formuła wprowadzona w zamieszczonym przykładzie do komórki A19 wyglądałaby następująco:
R1MTv520gxzPV
Ilustracja przedstawia fragment arkusza. Ma dziewiętnaście wierszy i dwie widoczne kolumny A i B. Kolumna A to Klasa, B to Edycja 1. W wierszach kolumny A podano kolejne klasy szkoły podstawowej, od pierwszej a do piątej d. W Edycji 1 w wierszach wpisano plastyk, szkło albo inne. Niektóre wiersze są puste. W komórce A19 wpisano formułę: =LICZ.JEŻELI(B2:B16;"plastyk"). Na niebiesko zaznaczono dane kolumny B.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Osiągnięty wynik możemy zauważyć na poniższej ilustracji.
RemegrelgPacY
Ilustracja przedstawia fragment arkusza. Ma dziewiętnaście wierszy i dwie widoczne kolumny A i B. Kolumna A to Klasa, B to Edycja 1. W wierszach kolumny A podano kolejne klasy szkoły podstawowej, od pierwszej a do piątej d. W Edycji 1 w wierszach wpisano plastyk, szkło albo inne. Niektóre wiersze są puste. W komórce A19 wpisano liczbę 3. Wiersz zaznaczono kolorem czerwonym.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Słowo „plastyk” pojawiło się w kolumnie B trzy razy – komórki B2, B9 oraz B13. Stworzona przez nas formuła zwróciła wartość 3, a więc poprawnie obliczyła, ile klas w pierwszej edycji zbierało worki z plastykiem.
Gdybyśmy natomiast chcieli się dowiedzieć, ile klas nie znalazło w pierwszej edycji żadnych śmieci, zastosowalibyśmy formułę LICZ.PUSTE, która zwraca liczbę komórek pustych w danym zakresie.
LICZ.PUSTE(zakres)
Przy czym zakres to adres pojedynczej komórki, różnych komórek lub obszaru komórek.
W przypadku problemu, który chcemy rozwiązać, formuła wprowadzona w zamieszczonym przykładzie do komórki A20 wyglądałaby następująco:
R4JYjtjP2M4SX
Ilustracja przedstawia fragment arkusza. Ma dwadzieścia wierszy i dwie kolumny A i B. Kolumna A to Klasa, B to Edycja 1. W wierszach kolumny A podano kolejne klasy szkoły podstawowej, od pierwszej a do piątej d. W Edycji 1 w wierszach wpisano plastyk, szkło albo inne. Niektóre wiersze są puste. W komórce A19 wpisano formułę: =LICZ.JEŻELI(B2:B16;"plastyk"). Komórkę zaznaczono na czerwono. W komórce A20 wpisano formułę: =LICZ.JEŻELI(B2:B16). Komórka jest zielona.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Osiągnięty wynik:
R7bNoYZa7wnal
Ilustracja przedstawia fragment arkusza. Ma dwadzieścia wierszy i dwie kolumny A i B. Kolumna A to Klasa, B to Edycja 1. W wierszach kolumny A podano kolejne klasy szkoły podstawowej, od pierwszej a do piątej d. W Edycji 1 w wierszach wpisano plastyk, szkło albo inne. Niektóre wiersze są puste. W komórce A19 wpisano liczbę 3. Komórkę zaznaczono na czerwono. W komórce A20 wpisano liczbę 3. Komórka jest zielona.
Źródło: Englishsquare.pl sp. z o.o., licencja: CC BY-SA 3.0.
W pierwszej edycji – kolumna B – komórek bez żadnych wartości jest trzy – komórki B3, B8 oraz B12. Funkcja wpisana do komórki A20 zwróciła wartość 3, a więc poprawnie obliczyła ile klas nie zebrało żadnych śmieci w pierwszej edycji zawodów.
Wyobraźmy sobie ponadto, że klasa a, która zebrała 14 worków w trzech edycjach chce znać odpowiedź na pytanie, które miejsce w zawodach zajmie. W takiej sytuacji zastosujemy formułę, POZYCJA.NAJW, która pokazuje pozycję względem innych liczb na liście (jeśli więcej niż jedna liczba ma taką samą pozycję, wówczas zwracana jest pozycja najwyższa). Składnia tej formuły jest następująca:
POZYCJA.NAJW(liczba;lista;[lp])
Przy czym:
liczba, to liczba wpisana do komórki, której pozycji wśród innych liczb poszukujemy;
lista, to zakres komórek, w którym dokonujemy poszukiwania;
lp (parametr opcjonalny), to kierunek sortowania.
Jeśli chcemy sortować pozycje malejąco, wówczas lp=0, w przeciwnym wypadku lp=1. W przypadku problemu, który chcemy rozwiązać, formuła wprowadzona w zamieszczonym przykładzie do komórki A21 wyglądałaby następująco:
R1dRqy5jT0np2
Ilustracja przedstawia fragment arkusza. Ma dwadzieścia jeden wierszy i dwie kolumny A i B. Kolumna A to Klasa, B to Edycja 1. W wierszach kolumny A podano kolejne klasy szkoły podstawowej, od pierwszej a do piątej d. W Edycji 1 w wierszach wpisano plastyk, szkło albo inne. Niektóre wiersze są puste. W komórce A19 wpisano formułę: =LICZ.JEŻELI(B2:B16;"plastyk"). Komórkę zaznaczono na czerwono. W komórce A20 wpisano formułę: =LICZ.JEŻELI(B2:B16). Komórka jest zielona. W komórce A21 zaznaczonej na żółto jest formula: =POZYCJA.NAJW(14;H2:H16;0).
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Ważne!
Funkcja POZYCJA.NAJW nie występuje w wersji programu Excel 2007 oraz jego wcześniejszych wersjach. W tych wersjach można ją zastąpić funkcją POZYCJA, a argumenty tej funkcji są dokładnie takie same jak dla POZYCJA.NAJW.
Co daje następujący wynik:
RyK9CMe3Gx6Qa1
Ilustracja przedstawia fragment arkusza. Ma dwadzieścia jeden wierszy i dwie kolumny od A do H. Kolumna A to Klasa, B to Edycja 1, C to Ilość, D - Edycja 2, E - Ilość, F - Edycja 3, G Ilość, H Suma. W wierszach kolumny A podano kolejne klasy szkoły podstawowej, od pierwszej a do piątej d. W Edycji 1 w wierszach wpisano plastyk, szkło albo inne. Niektóre wiersze są puste. W kolumnie C wpisano cyfry od jeden do ośmiu w zależności od klasy. Niektóre wiersze są puste. W kolumnie D wpisano metal, szkło, plastyk albo inne. Niektóre wiersze są puste. W kolumnie E są liczby od jeden do dwudziestu w zależności od klasy. W kolumnie F wpisano metal, inne, plastyk. Większość wierszy jest pusta. W kolumnie G są liczby do jeden do czterech. Większość wierszy jest pusta. W kolumnie H są liczby od zera do dwudziestu ośmiu w zależności od klasy. Liczba jest w każdym wierszu. W komórce A19 jest liczba 3. Komórkę zaznaczono na czerwono. W komórce A20 jest liczba 3. Komórkę zaznaczono na zielono. W komórce A21 jest liczba 3. Komórkę zaznaczono na żółto.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Sprawdźmy, czy zwrócona została poprawna wartość. Klasa a zebrała 14 worków, więcej zebrała tylko klasa c – 17 worków oraz klasa d – 28 worków. Jak widzimy Klasa a zajęła miejsce. Formuła zwróciła wartość 3, a więc poprawnie obliczyła, na którym miejscu znajduje się klasa a.
LibreOffice Calc
LibreOffice Calc
Z tak zorganizowanego pliku danych można wydobyć wiele informacji. Na przykład: jeśli chcemy się dowiedzieć, ile klas znalazło śmieci z plastiku w pierwszej edycji, zastosujemy formułę LICZ.JEŻELI, która zwraca liczbę komórek spełniających określone kryterium. Składnia tej formuły jest następująca:
LICZ.JEŻELI(zakres;kryteria)
Przy czym zakres to adres pojedynczej komórki, różnych komórek lub obszaru komórek, a kryteria to warunek, jaki musi spełnić zawartość komórki z zakresu, aby została ona zliczona.
W przypadku problemu, który chcemy rozwiązać, formuła wprowadzona w zamieszczonym przykładzie do komórki A19 wyglądałaby następująco:
R1XN7WUHISbjc
Ilustracja przedstawia fragment arkusza. Ma dziewiętnaście wierszy i dwie kolumny A i B. Kolumna A to Klasa, B to Edycja 1. W wierszach kolumny A podano kolejne klasy szkoły podstawowej, od pierwszej a do piątej d. W Edycji 1 w wierszach wpisano plastyk, szkło albo inne. Niektóre wiersze są puste. W komórce A19, zaznaczonej na czerwono, wpisano formułę: =LICZ.JEŻELI(B2:B16;"plastyk").
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Osiągnięty wynik możemy zauważyć na poniższej ilustracji.
R19oEhkEDzfnl
Ilustracja przedstawia fragment arkusza. Ma dziewiętnaście wierszy i dwie kolumny A i B. Kolumna A to Klasa, B to Edycja 1. W wierszach kolumny A podano kolejne klasy szkoły podstawowej, od pierwszej a do piątej d. W Edycji 1 w wierszach wpisano plastyk, szkło albo inne. Niektóre wiersze są puste. W komórce A19 wpisano liczbę 3. Wiersz zaznaczono kolorem czerwonym.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Słowo „plastyk” pojawiło się w kolumnie B trzy razy – komórki B2, B9 oraz B13. Stworzona przez nas formuła zwróciła wartość 3, a więc poprawnie obliczyła ile klas w pierwszej edycji zbierało worki z plastykiem.
Gdybyśmy natomiast chcieli się dowiedzieć, ile klas nie znalazło w pierwszej edycji żadnych śmieci, zastosowalibyśmy formułę LICZ.PUSTE, która zwraca liczbę komórek pustych w danym zakresie.
LICZ.PUSTE(zakres)
Przy czym zakres, to adres pojedynczej komórki, różnych komórek lub obszaru komórek.
W przypadku problemu, który chcemy rozwiązać, formuła wprowadzona w zamieszczonym przykładzie do komórki A20 wyglądałaby następująco:
RmwMMGOWWoUiQ
Ilustracja przedstawia fragment arkusza. Ma dwadzieścia wierszy i dwie kolumny A i B. Kolumna A to Klasa, B to Edycja 1. W wierszach kolumny A podano kolejne klasy szkoły podstawowej, od pierwszej a do piątej d. W Edycji 1 w wierszach wpisano plastyk, szkło albo inne. Niektóre wiersze są puste. W komórce A19 wpisano formułę: =LICZ.JEŻELI(B2:B16;"plastyk"). Komórkę zaznaczono na czerwono. W komórce A20 wpisano formułę: =LICZ.PUSTE(B2:B16). Komórka jest zielona.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Osiągnięty wynik:
Rz1MtjzZfAZGr
Ilustracja przedstawia fragment arkusza. Ma dwadzieścia wierszy i dwie kolumny A i B. Kolumna A to Klasa, B to Edycja 1. W wierszach kolumny A podano kolejne klasy szkoły podstawowej, od pierwszej a do piątej d. W Edycji 1 w wierszach wpisano plastyk, szkło albo inne. Niektóre wiersze są puste. W komórce A19 wpisano liczbę 3. Komórkę zaznaczono na czerwono. W komórce A20 wpisano liczbę 3. Komórka jest zielona.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
W pierwszej edycji – kolumna B – komórek bez żadnych wartości jest trzy – komórki B3, B8 oraz B12. Funkcja wpisana do komórki A20 zwróciła wartość 3, a więc poprawnie obliczyła ile klas nie zebrało żadnych śmieci w pierwszej edycji zawodów.
Wyobraźmy sobie ponadto, że klasa a, która zebrała 14 worków w trzech edycjach chce znać odpowiedź na pytanie, które miejsce w zawodach zajmie (zawody wygrywa klasa, która uzbierała najwięcej worków we wszystkich trzech edycjach). W takiej sytuacji zastosujemy formułę POZYCJA.NAJW, która pokazuje pozycję względem innych liczb na liście (jeśli więcej niż jedna liczba ma taką samą pozycję, wówczas zwracana jest pozycja najwyższa). Składnia tej formuły jest następująca:
POZYCJA.NAJW(wartość;dane;typ)
Przy czym:
wartość to liczba wpisana do komórki, której pozycji wśród innych liczb poszukujemy;
dane to zakres komórek, w którym dokonujemy poszukiwania;
typ (parametr opcjonalny) to kierunek sortowania.
Jeśli chcemy sortować pozycje w porządku malejącym, wówczas typ=0 lub zostawiamy parametr bez uzupełnienia, jeśli jednak chcemy sortować pozycje w porządku rosnącym, wpiszemy dowolną wartość inną niż zero. W przypadku problemu, który chcemy rozwiązać, formuła wprowadzona w zamieszczonym przykładzie do komórki A21 wyglądałaby następująco:
Ri6LUta9MJXdF
Ilustracja przedstawia fragment arkusza. Ma dwadzieścia jeden wierszy i dwie kolumny A i B. Kolumna A to Klasa, B to Edycja 1. W wierszach kolumny A podano kolejne klasy szkoły podstawowej, od pierwszej a do piątej d. W Edycji 1 w wierszach wpisano plastyk, szkło albo inne. Niektóre wiersze są puste. W komórce A19 wpisano formułę: =LICZ.JEŻELI(B2:B16;"plastyk"). Komórkę zaznaczono na czerwono. W komórce A20 wpisano formułę: =LICZ.PUSTE(B2:B16). Komórka jest zielona. W komórce A21 wpisano: =POZYCJA.NAJW(14;H2:H16;0). Komórka jest żółta.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Co daje następujący wynik:
R1ALdfwTvaSWT1
Ilustracja przedstawia fragment arkusza. Ma dwadzieścia jeden wierszy i dwie kolumny od A do H. Kolumna A to Klasa, B to Edycja 1, C to Ilość, D - Edycja 2, E - Ilość, F - Edycja 3, G Ilość, H Suma. W wierszach kolumny A podano kolejne klasy szkoły podstawowej, od pierwszej a do piątej d. W Edycji 1 w wierszach wpisano plastyk, szkło albo inne. Niektóre wiersze są puste. W kolumnie C wpisano cyfry od jeden do ośmiu w zależności od klasy. Niektóre wiersze są puste. W kolumnie D wpisano metal, szkło, plastyk albo inne. Niektóre wiersze są puste. W kolumnie E są liczby od jeden do dwudziestu w zależności od klasy. W kolumnie F wpisano metal, inne, plastyk. Większość wierszy jest pusta. W kolumnie G są liczby do jeden do czterech. Większość wierszy jest pusta. W kolumnie H są liczby od zera do dwudziestu ośmiu w zależności od klasy. Liczba jest w każdym wierszu. W komórce A19 jest liczba 3. Komórkę zaznaczono na czerwono. W komórce A20 jest liczba 3. Komórkę zaznaczono na zielono. W komórce A21 jest liczba 3. Komórkę zaznaczono na żółto.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Sprawdźmy, czy zwrócona została poprawna wartość. Klasa Va zebrała 14 worków, więcej zebrała tylko klasa c – 17 worków oraz klasa Vd – 28 worków. Jak widzimy Klasa a zajęła miejsce. Formuła zwróciła wartość 3, a więc poprawnie obliczyła, na którym miejscu znajduje się klasa a.
Słownik
funkcje statystyczne
funkcje statystyczne
formuły w arkuszu kalkulacyjnym, które umożliwiają wyliczenie większości znanych statystyk charakteryzujących rozkład, takich jak statystyki z grup: struktura rozkładu i jego kształt, miary tendencji centralne i rozproszenia, miary współwystępowania i inne; formuły wyliczające częstości dają możliwość wyszukiwania w zbiorze określonych informacji zapisanych w poszczególnych komórkach