ARKUSZ KALKULACYJNY - pczerniak.zut.edu.pl · Arkusz kalkulacyjny to program komputerowy...
58
2013 WBiHZ ZUT w Szczecinie ARKUSZ KALKULACYJNY Skrypt do zajęć z przedmiotu Informatyka
Transcript of ARKUSZ KALKULACYJNY - pczerniak.zut.edu.pl · Arkusz kalkulacyjny to program komputerowy...
2013
WBiHZ ZUT w Szczecinie
ARKUSZ
KALKULACYJNY Skrypt do zajęć z przedmiotu Informatyka
Spis treści
1. Wprowadzenie ................................................................................................ 3
2. Czym jest OpenOffice Calc?........................................................................... 4
3. Tworzenie tabel ............................................................................................... 5
4. Nawigacja w arkuszu Calc .............................................................................. 6
5. Zaznaczanie, kopiowanie, wklejanie i usuwanie ............................................ 7
6. Szybkie wypełnianie i edycja komórek .......................................................... 9
7. Formatowanie zawartości - tekstu, liczb ....................................................... 14
8. Tworzymy wykresy ....................................................................................... 17
9. Tworzymy formuły ....................................................................................... 24
10. Funkcje arkusza Calc .................................................................................... 29
10.1. Funkcje matematyczne ............................................................................ 31
10.2. Funkcje logiczne ..................................................................................... 39
11. Sortowanie danych ........................................................................................ 42
12. Eksport danych .............................................................................................. 44
13. Zadania .......................................................................................................... 46
14. Funkcje baz danych ....................................................................................... 48
15. Źródła ............................................................................................................ 58
Strona 3 z 58
1. Wprowadzenie
Arkusz kalkulacyjny to program komputerowy przedstawiający dane, głównie liczbowe,
w postaci zestawu dużych tabel dwuwymiarowych, pozwalający na automatyczną obróbkę
tych danych oraz na prezentację ich w różny sposób. Popularny program stosowany
w księgowości oraz w biurze.
Najważniejszym narzędziem arkusza kalkulacyjnego są funkcje (matematyczne, statystyczne,
daty i czasu, finansowe, bazodanowe, logiczne), za pomocą których wprowadzone do arkusza
dane są automatycznie przetwarzane. Możliwe jest także tworzenie różnego rodzaju
symulacji.
Za pomocą arkusza kalkulacyjnego można także wizualizować dane, prezentując je w postaci
wykresów (kolumnowych, słupkowych, kołowych, liniowych, warstwowych itd.), które
pozwalają łatwiej zorientować się we wzajemnych zależnościach i tendencjach.
W zaawansowanych arkuszach kalkulacyjnych dostępne są również języki makropoleceń
i języki programowania, przetwarzanie danych.
Popularnym narzędziem są tabele przestawne pozwalające tworzyć rozmaite układy danych
w oparciu o tabelę podstawową i wyświetlać te same dane co w tabeli podstawowej, ale
odmiennie układając zakres i położenie wierszy i kolumn, co uwypukla zależności
niewidoczne w pierwotnym układzie.
W środowisku Windows najbardziej znanym arkuszem kalkulacyjnym jest Microsoft Excel
z pakietu Microsoft Office. Do najbardziej zaawansowanych produktów tej kategorii można
także zaliczyć arkusze w pakietach biurowych WordPerfect Office (Quattro Pro)
i OpenOffice.org (OpenOffice.org Calc).
Gnumeric - arkusz kalkulacyjny dla środowiska GNOME
KSpread - nieco mniej zaawansowany program dla KDE
pakiet biurowy
Strona 4 z 58
2. Czym jest OpenOffice Calc?
OpenOffice Calc pozwala na masowe, szybkie obliczenia, gdy chcemy stworzyć swego
rodzaju tabelkę, do której będziemy wstawiać dane, liczby i wszystko się samo podliczy.
Przykładowo kontrolujemy nasze roczne wydatki za prąd, gaz, wodę i chcemy wyliczyć ile
w sumie wydajemy na opłaty. Dzięki arkuszowi kalkulacyjnemu Calc możemy w szybki
sposób wyliczyć zużycie np. gazu a ponadto zrobić wykres uniezależniając go od zmiany cen
jednostki gazu w ciągu roku. Dane możemy drukować i analizować na różne sposoby.
Arkusz kalkulacyjny jest swego rodzaju bardzo rozbudowanym kalkulatorem i przydaje się
przede wszystkim gdy mamy:
bardzo dużo pozycji do przeliczenia
często wykonujemy obliczenia na tym samym wzorze, zmieniamy tylko ilości
np. jednostek prądu, jednak formuła do wyliczenia zostaje ta sama
gdy potrzebna jest wizualizacja danych - wykresy - sortowanie danych rosnącą lub
malejąco według jakiegokolwiek kryterium w tabeli
możemy nawet z OpenOffice Base i Calc zrobić program do drukowania faktur.
Strona 5 z 58
3. Tworzenie tabel
Kurs zaczniemy od stworzenia prostej tabelki informującej o ilości sprzedanego towaru
w tygodniu. Załóżmy, że prowadzimy sklep i wpisujemy w tabelkę ilości sprzedanych
towarów.
Tabelka będzie wyglądać w następujący sposób:
Jeśli wpiszemy tekst, który będzie za długi i nie zmieści się w komórce, zostanie on
przysłonięty. Aby uniknąć tego możemy zwiększyć szerokość komórek. Wystarczy umieścić
kursor myszki na przestrzeni pomiędzy A i B i gdy pojawi się kursor z dwoma strzałkami
przeciągnąć szerokość kolumny na taką aby nam odpowiadała.
W analogiczny sposób zmieniamy szerokości kolumn wszędzie tam gdzie jest to potrzebne.
Strona 6 z 58
4. Nawigacja w arkuszu Calc
Po skoroszycie poruszamy się za pomocą:
strzałki w lewo, w prawo, góra i dół. Przechodzimy wówczas o jedno pole
w odpowiednią stronę.
klawisz home - jego naciśnięcie powoduje, że kursor przechodzi na początek wiersza
klawisz end - przechodzimy na koniec wiersza
Page up - przechodzimy jeden ekran w górę
Page down - przechodzimy jeden ekran w dół
ctrl+home - przechodzimy na początek całego dokumentu
ctrl+end - przechodzimy na koniec całego dokumentu
Wiele arkuszy w jednym pliku
Jeśli zadanie, które chcemy rozwiązać wymaga kilku niezależnych tabelek, warto skorzystać
z kilku arkuszy. W jednym pliku możemy utworzyć ich wiele.
Przechodzenie między arkuszami jest proste wystarczy kliknąć na napisie Arkusz1,
Arkusz2, Arkusz3 itd. Po kliknięciu otrzymujemy nowy, czysty arkusz, w który możemy
wpisywać nowe dane.
Zmiana nazwy Arkusza
Oczywiście warto jest zmienić opis np. z Arkusz1 na Zestawienie finansowe 2008, aby
w przyszłości od razu wiedzieć co w danym arkuszu jest. By to uczynić wystarczy kliknąć
prawym klawiszem myszy na napisie Arkusz1 i wybrać "zmień nazwę". Teraz zostaje już
tylko wpisać jak ma się nazywać dany arkusz.
Gdy za mało arkuszy?
Jeśli potrzebujemy więcej arkuszy niż jest standardowo należy kliknąć na puste pole
za ostatnim arkuszem (w naszym przypadku za Arkusz3), lub wybrać z menu
Wstaw->Arkusz.
W otwartym okienku możemy zmienić nazwę i po zatwierdzeniu otrzymujemy nowy arkusz
na nasze potrzeby. Będąc w tabelce stworzonej w poprzednim linku, możemy przetestować
sposób nawigacji, jaki mamy dostępny w systemie Calc.
Strona 7 z 58
5. Zaznaczanie, kopiowanie, wklejanie i usuwanie
Zaznaczanie kolumn, lub wierszy
Gdy chcemy wykonać jakąś operację na arkuszu najczęściej musimy zaznaczyć dany obszar
na którym będziemy działać.
Sposoby zaznaczania:
Całe kolumny/wiersze możemy zaznaczyć poprzez kliknięcie na samej górze nad daną
kolumną/wierszem na literkę/cyfrę, która ją reprezentuje, czyli np. na literkę B.
Kolumny/wiersze możemy też zaznaczać klikając myszką na pierwszy element tej
kolumny/wiersza, następnie trzymając przyciśnięty lewy klawisz myszy możemy przesunąć
w dół kursorem myszy, co spowoduje zaznaczenie całego tekstu w dół
możemy też za pomocą klawiatury - strzałkami najechać np. na pierwszy element wiersza
i przytrzymać klawisz shift. Następnie możemy również strzałkami kierować kursor w dół
i zaznaczane będą kolejne komórki. Oczywiście możemy również użyć tu klawiszy
"page down" lub "page up" do zaznaczania całego ekranu w dół bądź w górę.
Strona 8 z 58
Wiersze zaznaczamy analogicznie do kolumn.
Zaznaczanie pojedynczych komórek
Pojedyncze komórki możemy zaznaczyć:
poprzez przytrzymanie klawisza shift i poruszanie się strzałkami po skoroszycie i już
mamy zaznaczone komórki w formie "prostokąta"
klikając na pierwszy element do zaznaczenia przytrzymać shift a następnie na ostatni
w danym wierszu lub kolumnie. Zostaną zaznaczone wszystkie elementy pomiędzy
pierwszym a ostatnim elementem.
możemy dodatkowo przytrzymać klawisz ctrl i zaznaczać przypadkowe komórki
arkusza - wówczas bez wpływu na inne zaznaczone elementy to, w co klikniemy
będzie zaznaczone, a klikając ponownie zostanie odznaczone.
Kopiowanie
Po zaznaczeniu obszaru możemy skopiować dany obszar do bufora a następnie wkleić
go gdzie tylko chcemy.
Aby skopiować obszar należy użyć skrótu ctrl+c, lub z menu Edycja->Kopiuj
Pamiętajmy jednak, że nie można skopiować obszarów złożonych z kilku zaznaczeń,
kopiowane będą jedynie te, które są zaznaczone jednym ciągiem.
Wklejanie
Następnie wchodzimy na początek obszaru gdzie ma być wklejona zawartość i dajemy
ctrl+v, lub Edycja->Wklej.
Cała zawartość zaznaczonego i skopiowanego fragmentu powinna znaleźć się w miejscu które
wyznaczyliśmy.
Strona 9 z 58
6. Szybkie wypełnianie i edycja komórek
Szybkie wypełnianie
Tabelkę możemy wypełnić ręcznie komórka po komórce, jednak wszędzie gdzie się da należy
korzystać z zasady upraszczania i automatyzowania wszystkiego co tylko się da. Zyskujemy
wówczas na czasie. Inwestujemy jednorazowo w naukę, która daje nam wielokrotnie
zysk czasowy później za każdym razem, gdy tylko wykonujemy tę czynność, którą udało się
nam zautomatyzować. Po to z resztą powstał komputer - żeby nam ułatwić życie, a nie
utrudnić!
1) Automatyczne wstawianie dni tygodnia
Zatem gdy wpisujemy dni tygodnia to możemy
napisać jednynie słowo poniedziałek,
następnie złapać za czarny kwadrat w prawym dolnym rogu komórki i przeciągnąć
w stronę dalszej części komórek. Zobaczymy wówczas, że w miarę jak przeciągamy
na kolejne komórki zaznaczenie pojawiają się kolejne dni tygodnia w okienku
podpowiedzi.
Po puszczeniu prawego klawisza myszy komórki zostaną wypełnione dniami
tygodnia.
W analogiczny sposób możemy skorzystać z dni miesiąca wpisując styczeń a następnie
przeciągając uzyskamy wszystkie miesiące.
2) Szybkie wpisywanie dat
Nie tylko miesiące i dni tygodnia, również data podlega takiej automatyzacji.
Przykładowo potrzebujemy szybko uzyskać wszystkie dni miesiąca - nie ma sprawy
wystarczy:
wpisać pierwszy dzień miesiąca
resztę uzyskamy przeciągając w dół z pomocą kwadratu w dolnym rogu komórki.
Nie musimy się martwić ile dni ma dany miesiąc - OpenOffice Calc sam o to zadba. Jeśli
przeciągniemy za dużo, to zostanie wstawiony 1 luty itd. Tak więc w szybki sposób możemy
wypełnić tak nawet cały rok, a nawet lata.
Strona 10 z 58
A co gdy potrzebujemy uzyskać np. daty 10 dnia każdego miesiąca w roku, którego mamy
zapłacić ZUS?
Po prostu:
wpisujemy daty dziesiątego stycznia
niżej dziesiątego lutego
zaznaczamy obie komórki i dopiero teraz przeciągamy za kwadrat w dolnym, prawym
rogu w dół.
Jaki będzie efekt? W kolumnie mamy wszystkie dni w roku, w których mija termin zapłaty
za ZUS.
3) Automatyczna numeracja typu 1, 2, 3, ... 30
W podobny sposób możemy skorzystać z automatu w celu ponumerowania - poindeksowania
kolejnych komórek tekstu.
Strona 11 z 58
Wpisujemy cyfrę 1 w pierwszą komórkę,
łapiemy za kwadrat w prawym dolnym rogu i przeciągamy na tyle komórek ile nam
trzeba.
Efekt będzie taki, że cała kolumna (lub wiersz) będzie wypełniona po kolei liczbami 1, 2, 3,
... itd. dokąd tylko przeciągniemy zaznaczenie
4) Automatyczna numeracja typu: co 10, itp.
W bardzo prosty sposób możemy mnożyć przykłady np. chcemy mieć liczby co 2,
lub co dziesięć. Co zrobić? Wystarczy:
w pierwszej komórce napisać liczbę 10,
w drugiej 20,
zaznaczyć obydwie i przeciągnąć kwadrat w prawym dolnym rogu w dół
Reasumując jeśli chcemy np. aby numeracja zmieniała się o 8, to wystarczy
wpisać 8
w następnej komórce 16
zaznaczyć obydwie i przeciągnąć w dół łapiąc za kwadrat w prawym dolnym rogu
5) Wypełnianie tą samą wartością całej kolumny
Aby wypełnić cały wiersz lub kolumnę jedną wartością należy:
Strona 12 z 58
wpisać liczbę, lub tekst
zaznaczyć go i klikając w znany już kwadrat przeciągnąć w dół - dokąd dociągniemy
zaznaczenie to po puszczeniu klawisza pojawi się tam nasza wartość.
6) Możemy również skorzystać z ciekawej kombinacji automatycznego numerowania liczb
występujących w tekście
Mianowicie jeśli potrzebujemy zrobić np. spis zeszytów, które są ponumerowane 1,2, 3 itd.
to możemy skorzystać z następującej opcji:
w pierwszą komórkę wpisujemy tekst "Zeszyt 1"
i będąc na tej komórce przeciągamy w dół zaznaczenie łapiąc za dolny kwadrat
Uzyskamy wówczas efekt numeracji z kopiowaniem. Słowo Zeszyt zostanie skopiowane
w dół bez zmian, natomiast cyfry/liczby będą się zmieniać.
Oczywiście wszystkie metody możemy "mieszać", czyli możemy liczyć zeszyty co 10 itd.
7) Automatyczne numerowanie z odstępem
Jeśli zechcemy numerować automatycznie z tym, że potrzebujemy zachować odstępy
np. 2 wierszy to należy:
wpisać np. 1 w pierwszą komórkę
zaznaczyć tą komórkę i dwie następne
Strona 13 z 58
mając zaznaczone te wszystkie komórki złapać za kwadrat w prawym dolnym rogu
i przeciągnąć go w dół
W ten sposób otrzymamy numerowanie, ale zrealizowane w odstępach. Efekt pokazany
na obrazku niżej:
Strona 14 z 58
7. Formatowanie zawartości - tekstu, liczb
Dodajemy krawędzie do tabelki
Tabelkę, którą utworzyliśmy możemy teraz wydrukować, no ale raczej nie będzie ona
wyglądała imponująco. Dlatego powinniśmy choćby dla przejrzystości dodać krawędzie
w tabelce i ją nieco sformatować.
Zaznaczamy zatem całą tabelkę - tam gdzie jest tekst, a następnie klikamy na ikonkę
"Krawędzie" z paska narzędzi. Tam w okienku, które się pojawi wybieramy opcję, która nam
odpowiada, powiedzmy dodajemy pełną kratkę.
Po tej operacji Otrzymamy tabelkę z krawędziami.
Zarządzamy kolorami tabelki
Teraz popracujemy nad kolorami naszej tabelki. Powiedzmy, że chcemy ją trochę
pokolorować. Niestety za bardzo nie poszalejemy, gdyż dostępnych mamy niewiele kolorów.
By to uczynić zaznaczamy pierwszy rząd, czyli nagłówek tabeli i wybieramy opcję ikonkę
pojemniczka z farbą i wybieramy odpowiedni kolor. Pamiętajmy, że jeżeli wybierzemy kolor
bardzo ciemny to musimy ustawić czcionkę na kolor biały lub jasny, by była widoczna.
Kolor czcionki wybieramy za pomocą drugiej ikonki z literką "A".
Analogicznie wybieramy kolejne wiersze i kolumny i ustawiamy kolor tła i czcionki jaki nam
odpowiada.
Strona 15 z 58
Formatowanie tekstu tabelki
Na koniec zostanie nam sformatowanie tekstu tabelki a mianowicie wielkości czcionki,
wyjustowania itd.
Jak zwykle musimy zaznaczyć to co chcemy zmienić, czyli zaznaczamy górny wiersz
i zwiększamy czcionkę na 12 i pogrubiamy. Na końcu poddajemy procesowi justowania -
wyrównania do środka
Natomiast komórki w których występują liczby zaznaczamy i dajemy wyrównanie do środka.
Oczywiście do zmiany czcionki, wielkości czcionki, pogrubiana (literka B) i wyśrodkowania
wykorzystujemy powyższe menu.
Strona 16 z 58
Efekt końcowy powinien wyglądać mniej więcej tak jak na rysunku poniżej.
Przykładowo sobotę chcemy wyróżnić jako dzień wolny od pracy i możemy ją zaznaczyć
na szaro. Możliwości jest wiele trzeba tylko dopasować je dla siebie.
Strona 17 z 58
8. Tworzymy wykresy
Nic tak nie odzwierciedla suchych danych jak dobry wykres. Zarówno Excel jak i arkusz Calc
znakomicie nadają się do tworzenia różnych wykresów. Zamiast ręcznie nanosić na kartce
kolejne punkty, możemy skorzystać z imponujących możliwości tych pakietów.
Bardzo często produkt ten jest wykorzystywany w elektronice, fizyce, matematyce,
finansach i wielu innych dziedzinach życia, które nie w sposób tutaj wymienić. Przykładowo
mamy tabelkę pomiarów z czujnika zmieniającego się ciśnienia w skali miesiąca. Te dane
aż się proszą o zobrazowanie ich na wykresie. Wystarczy tylko zaznaczyć komórki i wybrać
rodzaj wykresu i gotowe. Na wykresie jak na dłoni widzimy różne wahania ciśnienia w ciągu
miesiąca.
Inny przykład to wykres sprzedaży w danym dniu różnego rodzaju towaru. Jest to przykład
naszej tabelki, którą wpisaliśmy w poprzednich podstronach.
Tworzenie wykresu z tabelki
Zaznaczamy cały wykres i klikamy na ikonce:
Po naciśnięciu na powyższej ikonce, kursor myszy zmieni się nam na "celownik z miniaturką
wykresu". Wystarczy teraz kliknąć lewym klawiszem myszy tam gdzie ma się zaczynać
wykres i przeciągnąć go w dół i w prawo tworząc zaznaczenie a tym samym wyznaczając
wielkość tego wykresu. Innymi słowy wykres będzie tak duży jak zaznaczenie które teraz
zrobiliśmy.
Gdy puścimy klawisz myszy pojawi się nowe okienko - kreator wykresów. Dajemy dalej
i w następnym kroku wybieramy rodzaj wykresu jaki chcemy uzyskać. Może to być wykres
słupkowy, kołowy, powierzchniowy, punktowy, przestrzenny itd. Po prawej stronie okna
mamy do wyboru różne rodzaje wykresów, możemy paskiem zjechać w dół i wybrać wykresy
przestrzenne.
Strona 18 z 58
Zauważmy również bardzo ważną opcję:
Seria danych w: "wierszach" lub "kolumnach". Tą opcją możemy zamieniać miejscami
kolumny i wiersze na wykresie.
Wybierzmy wiersze i dajemy dalej.
W tym kroku wybieramy rodzaj prezentowania danych na danym wykresie, w naszym
przypadku słupkowym. Dajemy dalej.
Możemy zaznaczyć opcje "Pokaż elementy tekstu w podglądzie". Wówczas na miniaturce
wykresu będziemy mieli podgląd jak dokładnie wygląda nasz wykres.
Strona 19 z 58
W miejsce "Tytuł główny" możemy zmienić lub wyłączyć nazwę, która będzie wyświetlana
u góry wykresu, możemy również wyłączyć legendę, oraz włączyć opisy osi X i Y. Na koniec
dajemy opcję Utwórz. I już mamy gotowy wykres.
Powinien wyglądać mniej więcej tak jak obok na rysunku. Możemy oczywiście zmieniać jego
wielkość poprzez przytrzymania lewego klawisza myszy nad którymkolwiek kwadratem
znajdującym się na rogu, lub na krawędzi wykresu i przeciągnąć dowolnie myszą. Gdy
wypuścimy lewy klawisz myszy wykres dostosuje się do nowej wielkości. Możemy też
go dowolnie przesuwać po całym skoroszycie łapiąc go myszą i przesuwając.
Strona 20 z 58
Modyfikacja istniejących wykresów
Na tym etapie możemy również zmieniać praktycznie wszystkie opcje wykresu. Wystarczy
kliknąć dwukrotnie w niego a przejdzie w tryb edycji. Możemy kliknąć na legendzie, opisie
górnym i np. go usunąć klawiszem delete. Jeśli klikniemy dwukrotnie na elemencie
tekstowym "tekst główny" to zmieni się on do edycji i będziemy mogli wpisać nowy tekst.
Dwukrotne kliknięcie na legendzie spowoduje otwarcie nowego okienka, w którym będziemy
mogli wybrać wiele opcji, np. zmienić kolor tła, grubość i typ linii.
Zauważmy też, że po dwukrotnym kliknięciu na wykresie zmienia się nam górny pasek
narzędziowy i pojawiają się opcje dotyczące wykresów.
Kolejne ikony od lewej pozwalają włączyć/wyłączyć:
tytuł wykresu,
legendę
trzecia ikona tytuły osi X i Y
opisy osi
dwie kolejne włączają lub wyłączają siatkę osi.
Ikonka z wykresem kołowym pozwala zmienić typ wykresu, np. na trójwymiarowy.
Kolejna ikonka otwiera znane nam już okno kreatora wykresów.
Ważne są też kolejne dwie ikonki, które pozwalają na zamianę osi X z osią Y.
Zmiana kolorów słupków, tła, czcionki wykresu
Jeśli nie podobają się nam kolory np. poszczególnych słupków w wykresie, możemy bez
większych problemów zmienić je na inny kolor.
By to uzyskać klikamy na wybranym słupku dotąd aż pojawią się na nich kwadraty i otworzy
się okno, które tą zmianę nam umożliwi. Pamiętajmy jednak, by kwadraty były na wszystkich
słupkach, bo jeśli zaznaczony będzie tylko jeden słupek, to zmiany będą wykonane tylko na
nim. Teraz wybieramy odpowiedni dla nas kolor klikamy ok i wszystkie słupki zaznaczone
słupki zmienią swój kolor.
Strona 21 z 58
Ciekawe efekty można otrzymać stosując przezroczystość, lub gradienty, możemy zmienić
czcionkę jej kolor.
Strona 22 z 58
Podobne efekty można uzyskać dla tła wykresu. Wystarczy dwukrotnie kliknąć na pustym
obszarze koło legendy wykresu i pojawi się znajome już okno, w którym możemy wybrać
kolor, a następnie np. gradient.
Zmiana typu wykresu
Wykres możemy też przerobić na trójwymiarowy, lub inny. Aby dokonać takiej zmiany
wystarczy dwukrotnie kliknąć na wykresie i gdy górny pasek narzędzi zmieni się należy
wybrać ikonkę z wykresem kołowym.
Pojawi się wówczas okienko umożliwiające zmianę typu wykresu. Tu wybieramy u góry
opcję 3D i wybieramy słupkowy wykres trójwymiarowy. Na samym dole zmienią się nam
warianty, powiedzmy, że chcemy wybrać okrągłe słupki.
Strona 23 z 58
Efekt końcowy będzie wyglądał następująco.
Strona 24 z 58
9. Tworzymy formuły
Podstawowe operacje
Potęga arkusza kalkulacyjnego ukazuje się dopiero, gdy przeprowadzamy za jego pomocą
obliczenia, gdzie wiele liczb trzeba dodać pomnożyć, wykonać skomplikowane lub proste
operacje matematyczne, ale najlepiej masowe.
Jest to jeden z najważniejszych rozdziałów tego kursu.
Każdą formułę zaczynamy od znaku równa się. Operacje arytmetyczne możemy wykonywać
tylko na liczbach.
Generalnie przy tworzeniu formuł możemy używać standardowych operatorów:
+ - dodawanie
- - odejmowanie
* - mnożenie
/ - dzielenie
( ) - nawiasy - pamiętajmy, że D7+D12*10 to nie to samo co (D7+D12)*10
skorzystać funkcji sumy
skorzystać z ogromnej liczby funkcji wbudowanych w arkusz kalkulacyjny -
ikonka f(x)
Arkusz kalkulacyjny możemy wykorzystać jako kalkulator, tyle że na większą skalę i o wiele
większych możliwościach.
Dokonajmy zatem kilka prostych operacji, które pokażą na przykładach jak korzystać
z podstawowych operatorów.
Gdy chcemy wykonać jakiekolwiek działanie musimy na początku w komórce wpisać znak
"=". Formuły tworzymy nie na liczbach, ale korzystając z adresów, czyli np. B4+C4. Po
stworzeniu takiej formuły możemy zmieniać liczby zawarte w tych komórkach, czyli w B4
i w C4 a wynik zawsze będzie wykonany dla aktualnych liczb.
Strona 25 z 58
Sumujemy dwie komórki
Formułę wpisujemy w następujący sposób:
przechodzimy w naszym przypadku na komórkę D4
wstawiamy znak =
teraz kursor zaznaczający komórkę stanie się czerwony i jak przechodzimy w lewo
czy w prawo to za znakiem "=" pojawia się adres komórki na którą najedziemy.
W naszym przykładzie chcemy dostać adres B4, więc tam się udajemy i naciskamy
Enter
wpisujemy operator +
i znów za pomocą czerwonego kursora przechodzimy tym razem na C4 (sytuacja
przedstawiona na powyższym obrazku)
po naciśnięciu Enter otrzymamy wynik powinno wyjść 9.
Teraz spróbujmy zmieniać liczby w komórkach B4 lub C4. Wynik natychmiast
po zatwierdzeniu zmiany w którejkolwiek z komórek B4 lub C4 zostanie obliczony.
Zauważmy, że formuła działa niezależnie od danych, czyli raz tworzymy formułę
a procentuje ona cały czas. Może w tak banalnym przykładzie jest to mało istotne, ale
w przypadku skomplikowanych obliczeń mamy możliwość szybkiej analizy wyniku gdy
zmienimy liczby na wejściu.
Wyliczanie sum
Powiedzmy, że chcemy zsumować ilość sprzedanych towarów. Wpisujemy więc pod tabelką
napis suma, następnie w kolumnie pod poniedziałkiem sumujemy wynik z całego
poniedziałku. By zsumować należy kliknąć na ikonkę sumy obok znaku równości.
Po kliknięciu na ikonkę sumy pojawi się nam sytuacja ze zdjęcia obok. W polu, w którym
sumujemy pojawi się napis suma, natomiast nad tą komórką pojawi się niebieskie
zaznaczenie. Teraz wystarczy złapać za prawy dolny róg tego zaznaczenia i przeciągnąć go
w górę obejmując nim wszystkie komórki, które mają być sumowane.
Strona 26 z 58
Po naciśnięciu klawisza Enter otrzymamy wynik sumy w komórce. Teraz chcemy aby
wszędzie, pod każdym dniem tygodnia była suma ilości sprzedanych sztuk towarów.
Wystarczy tylko wejść na komórkę z formułą sumy złapać za kwadrat w prawym dolnym
rogu i przeciągnąć go w prawo aż do soboty. Teraz mamy skopiowaną sumę dla wszystkich
dni. Nie musimy dla każdego dnia robić sumy od nowa.
Gdy wejdziemy na np. sumę w środę - tak jak jest to pokazane obok zobaczymy w polu
formuł następującą formułę =SUMA(D4:D6), oznacza to, że sumowane są komórki
w kolumnie od D4 do D6, czyli system automatycznie domyślił się, że mają być sumowane
dane ze środy.
Teraz zrobimy sumę całkowitą poprzez zsumowanie wszystkich sum. Mianowicie Wpisujemy
tekst suma całkowita, natomiast pod poniedziałkiem klikamy znów na naszej ikonce sumy
i zaznaczamy obszar, który ma być zsumowany, czyli wszystkie sumy.
Strona 27 z 58
Szybkie oszacowanie sumy, bez formuły
Jeśli chcemy tylko szybko sprawdzić ile wynosi suma liczb w zaznaczonym obszarze
możemy skorzystać z pewnego triku.
Otóż zaznaczamy dany obszar z którego chcemy wyliczyć sumę i spoglądamy na prawo
dolnego paska informacyjnego. Jest tam napis Suma=55. Czyli zgadza się. Uprzednio
wyliczona suma całkowita wynosi 55 i suma zaznaczonego obszaru też wynosi tyle samo.
Strona 28 z 58
Kliknijmy teraz prawym klawiszem na napisie Suma=55. Teraz możemy wybrać sobie co
chcemy aby w dolnym pasku się pojawiało. Może to być np. średnia, lub minimalna liczba
zaznaczonego obszaru.
Strona 29 z 58
10. Funkcje arkusza Calc
Arkusz kalkulacyjny zarówno OpenOffice Calc jak i Microsoft Excel obfitują w wielkie
zbiory funkcji ułatwiające wykonanie nam zamierzonego zadania. Między innymi funkcje
podzielone są na funkcje:
Baz danych
Daty i godziny
Finansowe
Informacji
Logiczne
Matematyczne
do pracy na macierzach
statystyczne
do arkusza kalkulacyjnego
do obsługi tekstu
oraz dodatkowe, np. konwersja liczb na inne systemy liczbowe
1) Funkcje bazy danych
Funkcje daty i godziny to zbiór funkcji usprawniający nasze zarządzanie datami i godzinami,
na przykład mamy do dyspozycji funkcję, która liczy ilość dni między datami, wyznacza
liczbę lat między datami, sprawdza czy dany rok jest przestępny, wyznacza bieżącą godzinę,
liczbę tygodni w zadanym okresie i wiele innych przydatnych funkcji.
2) Funkcje finansowe
Funkcje te pozwalają na obliczenie na przykład procentu składanego, gdzie liczenie
"na kartce" jest bardzo uciążliwe, ułatwiają podejmowanie decyzji finansowych, gdyż liczą
na przykład zwrot z inwestycji za wiele lat z uwzględnieniem inflacji.
3) Funkcje informacji
Są to funkcje ułatwiające pracę nad arkuszem kalkulacyjnym, przekazując nam informację,
czy w komórce jest liczba, tekst, czy jest pusta itd.
4) Funkcje logiczne
Jest to zestaw logicznych funkcji.
5) Funkcje matematyczne
Pogrupowane są tutaj funkcje umożliwiające szybkie liczenie np. największego wspólnego
dzielnika, czy liczba jest parzysta, czy nie, zamienia stopnie na radiany, podaje resztę
Strona 30 z 58
z dzielenia, zaokrągla liczbę w dół, czy w górę, podaje sinus, cosinus, tg i ctg z podanej liczby
i wiele innych.
6) Macierze
Funkcje te pozwalają na podstawowe działania na macierzach, np. obliczenie macierzy
odwrotnej, mnoży macierze itp.
7) Funkcje statystyczne
Pozwala na obliczenia zadań statystycznych, mnóstwo funkcji dla tych, którzy potrzebują
i wykorzystują tę dziedzinę matematyki do swej pracy.
8) Pozostałe trzy to funkcje arkusza kalkulacyjnego - usprawniają obsługę zdarzeń,
do obsługi tekstu - np. zamiana z małych liter na duże itp. oraz funkcje dodatkowe,
np. konwersja liczb na inne systemy liczbowe.
Wszystkie funkcje są dość dobrze opisane w pomocy programu, oraz gdy wejdziemy
na konkretną funkcję, to pojawia się jej opis oraz sposób użycia.
Strona 31 z 58
10.1. Funkcje matematyczne
Powiedzmy mamy kilka zadań wymagających dużo liczenia polegających na jednym
rozwiązaniu, ale różnią się tylko danymi. Rozwiązujemy go na kartce - udało nam się
w końcu przejść do końca. Ok, teraz zabieramy się za drugi i trzecie itd. Ale to przecież tyle
liczenia a zadanie różni się tylko kilkoma danymi... Czy nie da się tego jakoś uprościć?
Oczywiście, można rozwiązać całość w arkuszu kalkulacyjnym a później tylko podstawiać
dowolne dane a reszta zadania się sama wykona.
Może powiecie, że rzadko się tak zdarza, może, ale w fizyce, czy w jakiejkolwiek nauce
badawczej często zachodzi taka potrzeba aby zasymulować co się stanie, gdy dane wejściowe
się zmienią. Wtedy możemy skorzystać z udogodnienia arkusza kalkulacyjnego, jakim jest
tworzenie formuł. Po co liczyć „na piechotę”, skoro można stworzyć formułę, a później
zmieniać tylko dane i cieszyć się natychmiastowym wynikiem.
1) Funkcja PIERWIASTEK – funkcja wbudowana w arkusz kalkulacyjny OpenOffice
Calc (kategoria matematyczne), zwraca dodatni pierwiastek kwadratowy z liczby
dodatniej (funkcja nie obsługuje liczb urojonych ani zespolonych).
Składnia funkcji
=PIERWIASTEK(liczba)
Przykład
=PIERWIASTEK(4)
zwraca 2
=PIERWIASTEK(-4)
zwraca błąd:502
czyli komunikat o błędnym parametrze
2) Funkcja POTĘGA – funkcja wbudowana w arkusz kalkulacyjny OpenOffice Calc
(kategoria matematyczne), zwraca potęgę danej liczby.
Składnia funkcji
=POTĘGA(podstawa;potęga)
W przypadku mniej zaawansowanych obliczeń prowadzonych w programie Calc, oczywiście
możliwy jest prostszy i bardziej naturalny zapis podstawa^potęga.
Przykład
25 podnieść do potęgi 3:
=POTĘGA(25;3)
zwraca 15625
=25^3
zwraca 15625
Strona 32 z 58
Twierdzenie pitagorasa
Obliczmy zatem ile długości musi mieć krawędź "c" w trójkącie, aby trójkąt był prostokątny,
czyli aby zawierał w sobie kąt prosty. Skorzystamy zatem z funkcji pitagorasa. Powiedzmy,
że bok a=3cm, bok b=4cm, zatem szukamy boku c.
c2=a
2+b
2
czyli c=pierwiastek z (a2+b
2)
Wpisujemy te wartości do arkusza Calc w postaci:
Następnie w komórce D4 chcemy by powstał wynik, czyli musimy wklepać tam wzór, który
obliczy długość boku c ze wzoru pitagorasa.
Wchodzimy na ikonkę f(x) i wybieramy kategorię "matematyczne" jak na rysunku powyżej
Strona 33 z 58
Następnie szukamy funkcji pierwiastek i klikamy dwukrotnie. Zauważmy, że na dole pojawi
się napis =PIERWIASTEK().
Teraz musimy zrobić sumę kwadratów boków a i b.
Zatem szukamy funkcji potęga i również klikamy dwukrotnie na niej. Teraz na dole pojawi
się napis =PIERWIASTEK(POTĘGA()). Musimy zatem ustalić z czego ma być potęga
i jakiego stopnia.
Klikamy więc na pole Podstawa i będąc tam kursorem, przechodzimy myszką na pole B4,
natomiast w polu potęga wpisujemy 2, bo przecież ma to być kwadrat z tej liczby.
Strona 34 z 58
Na dole w miejscu, gdzie jest formuła wpisujemy plus.
Następnie znów dajemy funkcję potęga i w miejsce podstawa dajemy C4, natomiast w polu
potęga wpisujemy 2. Po zatwierdzeniu dajemy ok i otrzymujemy wynik w komórce D4.
Na górze jest cała formuła, która oblicza nam nasze zadanie.
Strona 35 z 58
Teraz możemy podstawiać dowolne dane a formuła obliczy nam ile wyniesie krawędź c,
by trójkąt był prostokątny.
Niestety przy zadanych krawędziach a i b nie zawsze krawędź c wyjdzie liczbą całkowitą.
ZADANIE 1.
Napisz skróconą formę formuły wykorzystującej Twierdzenie Pitagorasa przy użyciu
operatorów matematycznych.
Domyślnie mamy zaokrąglanie do 2 miejsc po przecinku. Jeśli jest potrzebna nam większa
dokładność możemy skorzystać z ikonki zaznaczonej obok na rysunku, z czego jedna
zwiększa dokładność o jedno miejsce dziesiętne, druga zaś zmniejsza.
3) Funkcja LICZ.JEŻELI – funkcja wbudowana w arkusz kalkulacyjny OpenOffice Calc
(kategoria matematyczne), zwraca liczbę elementów, które w podanym zakresie
komórek spełniają określone kryterium.
Strona 36 z 58
Składnia funkcji
=LICZ.JEŻELI(zakres; kryterium)
zakres – zakres komórek w których zliczamy;
kryterium – kryteria przeszukiwania zakresu, ma postać liczby, wyrażenia lub adresu
komórki.
Kryteria funkcji:
X liczy komórki zawierające X
A1 liczy komórki mające tą samą wartość co A1
">=liczba" liczy komórki większe lub równe liczbie
"<=liczba" liczy komórki mniejsze lub równe liczbie
">liczba" liczy komórki większe od liczby
"<liczba" liczy komórki mniejsze od liczby
">="&A1 liczy komórki większe lub równe liczby z A1
"<="&A1 liczy komórki mniejsze lub równe liczby z A1
">"&A1 liczy komórki większe od liczby z A1
"<"&A1 liczy komórki mniejsze od liczby z A1
"d*" zaczynające się na literę d
4) Funkcja SUMA.JEŻELI (ang. SUMIF) – funkcja wbudowana w arkusz kalkulacyjny
OpenOffice Calc (kategoria matematyczne), zwraca wynik sumy wszystkich elementów
w podanym zakresie spełniających podane kryterium.
Składnia funkcji
=SUMA.JEŻELI(zakres; kryterium; zakres_sumy)
zakres – podaje zakres komórek objętych kryterium
kryterium – w której znajdują się kryteria wyszukiwania – lub samymi kryteriami
w postaci wartości
zakres_sumy – zakres sumowanych komórek – w przypadku pominięcia sumowane są
komórki z pola "zakres"
Kryteria funkcji – patrz wyżej ↑
5) Funkcja LICZ.PUSTE - funkcja wbudowana w arkusz kalkulacyjny OpenOffice Calc
(kategoria matematyczne), zwraca liczbę pustych komórek z podanego zakresu.
Składnia funkcji
=LICZ.PUSTE(zakres)
Strona 37 z 58
ZADANIE 2.
Zaprojektuj tabliczkę mnożenia. Wyśrodkuj zawartość kolumn. Zmień nazwę arkusza.
ZADANIE 3.
W sklepie obuwniczym w ramach promocji ustalono cenę 250 zł na buty męskie. buty różnią
się rozmiarem i kolorem. Wprowadź do arkusza poniższą tabelę i wykonaj stosowne
obliczenia.
Cena w zł: 250 Buty
Strona 38 z 58
ZADANIE 4.
Z arkusza do zadania 4 skopiuj dane do nowego pliku. Wyszczególnij nagłówki kolumn.
Wyśrodkuj zawartość komórek. Ustal jednakową szerokość kolumn zawierających parametry
mleka. Poniżej stwórz dodatkowe wiersze, w których wpiszesz formuły podające wartości
średnie, minimalne i maksymalne parametrów oraz medianę i modę
ZADANIE 5.
Z arkusza do zadania 5 skopiuj dane do nowego pliku. Wyszczególnij nagłówki kolumn.
Wyśrodkuj zawartość komórek. Dostosuj szerokość kolumn do ich zawartości. Uzupełnij
komórki i za pomocą określonych formuł wylicz sumy, średnie, MIN i MAX.
ZADANIE 6.
Z arkusza do zadania 6 skopiuj dane do nowego pliku. Wyszczególnij nagłówki kolumn.
Wyśrodkuj zawartość komórek. Dostosuj szerokość kolumn do ich zawartości. Uzupełnij
rubrykę dotyczącą kosztów zakupu paliwa i za pomocą określonych formuł wylicz sumy,
średnie, MIN i MAX.
Strona 39 z 58
10.2. Funkcje logiczne
1) Funkcja JEŻELI – funkcja wbudowana w arkusz kalkulacyjny OpenOffice Calc
(kategoria logiczne). Przedstawia jedną wartość, jeśli warunek zawarty w funkcji jest
spełniony (PRAWDA), lub wartość drugą, jeśli warunek nie jest spełniony (FAŁSZ).
Składnia funkcji
=JEŻELI(warunek;wartość_jeśli_PRAWDA;Wartość_jeśli_FAŁSZ)
Przykład 1
Jeżeli w komórce A1 jest jakikolwiek wpis, to w komórce, gdzie jest wpisana formuła
(np. B1), zostanie wpisany tekst "PEŁNA". Jeżeli ten warunek nie jest spełniony (A1
jest pusta), to zostanie wpisany tekst "PUSTA".
=JEŻELI(A1>0;"PEŁNA";"PUSTA")
Przykład 2
Funkcję JEŻELI można łączyć ze sobą, uzyskując możliwość spełnienia więcej niż
jednego warunku.
=JEŻELI(A1>0;"Większa niż zero";JEŻELI(A1<0;"Mniejsza od zera";Jeżeli(A1=0;"Równa
zero";"")))
Przykład 3
Jeśli zamierzasz użyć w jednym wierszu wiele razy osobnej funkcji JEŻELI, należy
po każdej wpisać znak &.
=JEŻELI((A2<=3,1)I(A3<150);"jeden";"niejeden")&JEŻELI((A2>3,1)I(A2<=3,4)I(A3<150)
;"dwa";"niedwa")
2) Funkcja LUB – funkcja wbudowana w arkusz kalkulacyjny OpenOffice Calc (kategoria
logiczne), zwraca wartość logiczną PRAWDĘ pod warunkiem, że co najmniej jeden
A B C D
1 -2
=JEŻELI(A1>0;"PEŁNA";"PUSTA") PUSTA
2 2
"=JEŻELI(A1>0;"Większa niż zero";JEŻELI(A1<0;"Mniejsza
od zera";JEŻELI(A1=0;"Równa zero";"")))
"Mniejsza od
zera"
3 3
=JEŻELI((A2<=3,1)I(A3<150);"jeden";"niejeden")&JEŻELI((A
2>3,1)I(A2<=3,4)I(A3<150);"dwa";"niedwa") jedenniedwa
Strona 40 z 58
argument to PRAWDA. Funkcja zwraca FAŁSZ tylko w momencie, gdy wszystkie
warunki mają wartość FAŁSZ. Funkcja obsługuje do 30 argumentów wartości
logicznych.
Składnia funkcji
=LUB(warunek; warunek2)
Przykład
=LUB(9<5;13<4;5>=4)
zwraca PRAWDA
=LUB(9<5;13<4;5=4)
zwraca FAŁSZ
=LUB(FAŁSZ;PRAWDA)
zwraca PRAWDA
3) Funkcja NIE – funkcja wbudowana w arkusz kalkulacyjny OpenOffice Calc (kategoria
logiczne), działa jak operator negacji, czyli odwraca wartość logiczną.
Składnia funkcji
=NIE(wartość)
Przykład
= NIE(PRAWDA)
zwraca FAŁSZ
= NIE(FAŁSZ)
zwraca PRAWDA
4) Funkcja FAŁSZ – funkcja wbudowana w arkusz kalkulacyjny OpenOffice Calc
(kategoria logiczne), zwraca wartość logiczną FAŁSZ. Nie jest konieczne podanie
argumentów funkcji.
Składnia funkcji
=FAŁSZ()
Strona 41 z 58
Przykład
= FAŁSZ()
zwraca FAŁSZ
= NIE(FAŁSZ())
zwraca PRAWDA
5) Funkcja PRAWDA – funkcja wbudowana w arkusz kalkulacyjny OpenOffice Calc
(kategoria logiczne), zwraca wartość logiczną PRAWDA. Nie jest konieczne podanie
argumentów funkcji.
Składnia funkcji
=PRAWDA()
Przykład
= PRAWDA()
zwraca PRAWDA
= NIE(PRAWDA())
zwraca FAŁSZ
6) Funkcja I – funkcja wbudowana w arkusz kalkulacyjny OpenOffice Calc (kategoria
logiczne), zwraca wartość logiczną FAŁSZ pod warunkiem, że co najmniej jeden
argument to FAŁSZ. Funkcja zwraca PRAWDĘ tylko w momencie, gdy wszystkie
warunki mają wartość PRAWDA – są spełnione. Funkcja obsługuje do 30 argumentów
wartości logicznych.
Składnia funkcji
=I(warunek; warunek2)
Przykład
=I(9<5; 13<4; 5>=4)
zwraca FAŁSZ
=I(9<5; 13<4; 5=4)
zwraca FAŁSZ
=I(9>5; 13>4; 5=5)
zwraca PRAWDA
= I(FAŁSZ;PRAWDA)
zwraca FAŁSZ
Strona 42 z 58
11. Sortowanie danych
Bardzo przydatną i ważną funkcją w każdym arkuszu kalkulacyjnym jest opcja sortowania.
Często potrzebujemy mieć dane posortowane według np. ceny, ilości sprzedanych sztuk,
alfabetycznie itd.
Aby prawidłowo korzystać z opcji sortowania pokażemy różne sposoby na przykładzie.
Skorzystamy z naszej już dobrze znanej tabelki i spróbujemy posortować według ilości sztuk
dla piątku.
Jeśli wejdziemy na piątek i zaznaczymy tą kolumnę (oczywiście bez napisu piątek) i damy
sortowanie za pomocą ikonki sortuj, zaznaczonej na czerwono na obrazku obok,
to posortowane zostaną dane tylko dla piątku, ale zaraz zaraz... przecież pomiesza to nam
całkowicie i sfałszuje tabelkę, bo liczby się przemieszczą, natomiast wpisy Książki, Zeszyty
i Akcesoria biurowe zostaną tam gdzie były. Zatem ten sposób nadaje się tylko do tabelek,
gdzie mamy jedną kolumnę i chcemy ją posortować.
W naszym przypadku skorzystamy z innej możliwości, która pozwoli nam na uniknięcia
fałszowania danych. Zaznaczamy zatem dane, które mają być posortowane (bez nazw tygodni
i sum) i dajemy Dane->Sortuj. Ponieważ chcemy sortować według piątku w okienku, które
się otworzy wybieramy kolumnę F i zaznaczamy rosnąco. Jeśli chcemy aby Calc w następnej
kolejności sortował według innej kolumny to możemy niżej wybrać takie opcje.
Strona 43 z 58
Zatwierdzamy Ok i zauważmy, że wszystko zostanie teraz posortowane prawidłowo według
dnia piątek z utrzymaniem danych tam gdzie powinny być. Miejscami zamienione zostały
Zeszyty i Akcesoria biurowe, ale wszystkie dane w wierszach powędrowały za nimi, nie
dokonując żadnych przemieszeń i o to nam chodziło.
Strona 44 z 58
12. Eksport danych
Bez większych problemów możemy nasze dane wyeksportować do Excela, lub też
do odpowiedniej bazy danych.
Jeśli na przykład potrzebujemy informacje zawarte w naszym skoroszycie przenieść
do naszego serwisu internetowego możemy skorzystać z formatu np. CVS.
Format ten jest zapisem, który umożliwia zapisanie całej zawartości arkusza w postaci pliku
tekstowego w specjalny sposób zorganizowanego. W wierszach takiego pliku będą się
znajdowały analogiczne wiersze arkusza, pola zaś są oddzielone znakiem np. średnikami,
wiersze zaś innym znakiem.
Dzięki takiem podziałowi baza danych może odczytać taką zawartość pliku.
Aby skorzystać z tej opcji dajemy zapisz jako, w miejscu zapisz jako typ: wybieramy Tekst
CSV(.csv) i dajemy zapisz. Jeśli pojawi się okienko informujące nas o usunięciu
formatowania to dajemy ok bo w pliku CSV i tak nie ma formatowania.
W kolejnym oknie wybieramy najpierw zestaw znaków, dla polskiej czcionki to będzie
Europa Środkowa (Windows-1250/ WinLatin2). Według potrzeby programu do którego
będziemy eksportować wybieramy separator pola i separator tekstu i dajemy OK.
Strona 45 z 58
Przykładowe podglądy takich plików przez notatnik będą wyglądały mniej więcej tak jak
obok. Pierwszy to z ustawieniami jak na okienku wyżej, drugi to gdy wybierzemy separator
pola jako tabulator.
Eksportować możemy również do formatu:
pdf, korzystając z funkcji plik- eksportuj jako PDF, lub klikając na ikonkę PDF
z paska narzędziowego.
Excel,
xml,
html,
formatu StarCalc.
dbf
Wszystko to jest dostępne przez zapisz jako i wybieramy typ pliku do jakiego chcemy
zapisać dane.
Strona 46 z 58
13. Zadania
Zadanie 7.
Za pomocą narzędzia Formatowanie warunkowe wyróżnij uczniów ze średnią ocen – powyżej
4,5 na niebiesko, poniżej 3,0 na czerwono.
Zadanie 8.
W nowym arkuszu napisz formuły, które pozwolą sprawdzić:
ile dni liczył sobie rok 1895
w jakim dniu tygodnia się urodziłaś/łeś
czy rok 2004 był przestępny
ile dni upłynęło od narodzin Jezusa do dnia dzisiejszego
ile dni minęło od Twoich narodzin.
Zadanie 9.
Otwórz plik „tabela zadanie 9.ods” i wykonaj następujące polecenia:
usuń 3 pierwsze wiersze,
zablokuj pierwszy wiersz tabeli,
dodaj nową kolumnę A i wprowadź w niej numerację wierszy,
uzupełnij dane dotyczące % zawartości składników mleka,
oblicz średnie wydajności laktacyjne (kg ml) dla każdej rasy krów,
wykonaj wykres dla w/w średnich,
korzystając z formatowania warunkowego podświetl na pomarańczowo osobniki,
których okres międzywycieleniowy był krótszy niż 365 dni,
Strona 47 z 58
korzystając z formatowania warunkowego podświetl na niebiesko osobniki, których
średnia wydajność w laktacji przekroczyła 8000 kg mleka,
wyróżnij na zielono osobniki o % zawartości tłuszczu i białka powyżej 4 i 3,7%,
korzystając z filtrowania wyświetl listę osobników po pierwszej laktacji.
Zadanie 10.
Otwórz plik „tabela zadanie 10.ods” i dla danych zawartych w tabeli stwórz wykres
liniowy obrazujący rozkład wydajności krów w kolejnych laktacjach. Laktację szczytową
(o najwyższej produkcji) wyróżnij poprzez przypisanie etykiety na wykresie.
Strona 48 z 58
14. Funkcje baz danych
Przykładowe dane nr 1:
W przykładach opisów funkcji użyto następujące dane:
Zakres A1:E10 zawiera listę dzieci zaproszonych na urodziny do Janka. W każdym wpisie
znajdują się następujące informacje: kolumna A zawiera imię, B - klasę, następnie wiek
w latach, odległość do szkoły w metrach i wagę w kilogramach.
A B C D E
1 Nazwa Klasa Wiek Odległość do szkoły Waga
2 Andrzej 3 9 150 40
3 Beata 4 10 1000 42
4 Karol 3 10 300 51
5 Daniel 5 11 1200 48
6 Ewa 2 8 650 33
7 Fpozycja 2 7 300 42
8 Gosia 1 7 200 36
9 Hubert 3 9 1200 44
10 Irenka 2 8 1000 42
11
12
13 Nazwa Klasa Wiek Odległość do szkoły Waga
14 >600
15
16 BD.ILE.REKORDÓW 5
Formuła w komórce B16 zawiera wpis =BD.ILE.REKORDÓW(A1:E10;0;A13:E14)
Strona 49 z 58
Argumenty funkcji baz danych:
Poniżej umieszczono definicje parametrów dla wszystkich funkcji bazy danych:
Baza danych jest zakresem komórek definiującym bazę danych.
Pole bazy danych określa kolumnę, na której funkcja działa po zastosowaniu kryteriów
wyszukiwania pierwszego parametru i zaznaczeniu wierszy danych. Nie ma to związku
z kryteriami wyszukiwania. Aby określić cały zakres danych, należy użyć liczby 0. Aby
odwołać się do kolumny poprzez nazwę jej nagłówka, należy umieścić tę nazwę
w cudzysłowie.
Kryteria wyszukiwania określają zakres komórek zawierający kryteria wyszukiwania.
W przypadku umieszczenia kilku kryteriów wyszukiwania w jednym wierszu są one
połączone operatorem logicznym I. W przypadku umieszczenia kryteriów wyszukiwania
w różnych wierszach są one połączone operatorem logicznym LUB. Puste komórki
w zakresie kryteriów wyszukiwania będą ignorowane.
1) Funkcja BD.ŚREDNIA zwraca średnią wartość wszystkich komórek (pola) we
wszystkich wierszach (rekordy bazy danych) odpowiadających określonym warunkom
wyszukiwania.
Wyszukiwanie umożliwia korzystanie z wyrażeń regularnych. Na przykład wprowadzenie
frazy "wszystko.*" pozwala odszukać pierwsze wystąpienie wyrazu "wszystko", po którym
następują dowolne znaki. Aby wyszukać tekst, który jest wyrażeniem regularnym, należy
poprzedzić każdy znak odwróconą kreską ukośną \.
Składnia
BD.ŚREDNIA(baza_danych; pole_bazy_danych; kryteria_wyszukiwania)
Aby odwołać się do kolumny poprzez nazwę jej nagłówka, należy umieścić tę nazwę
w cudzysłowie.
Przykład
Aby dowiedzieć się, jaka jest średnia waga wszystkich dzieci w tym samym wieku
w powyższym przykładzie, w komórce B16 należy wprowadzić następującą formułę:
=BD.ŚREDNIA(A1:E10;"Waga";A13:E14)
W wierszu 14 w kolumnie Wiek należy wprowadzić kolejno liczby 7, 8, 9 itd. Zostanie
wyświetlona średnia waga wszystkich dzieci w tym samym wieku.
Strona 50 z 58
2) Funkcja BD.MIN zwraca najmniejszą zawartość komórki (pola) w bazie danych
odpowiadającą określonym kryteriom wyszukiwania.
Składnia
=BD.MIN(baza_danych; pole_bazy_danych; kryteria_wyszukiwania)
Aby odwołać się do kolumny poprzez nazwę jej nagłówka, należy umieścić tę nazwę
w cudzysłowie.
Przykład
Aby dowiedzieć się, jaka jest najkrótsza droga do szkoły wśród dzieci w każdej klasie
w powyższym przykładzie, w komórce B16 należy wprowadzić następującą formułę:
=BD.MIN(A1:E10;”Odległość do szkoły”;A13:E14)
W wierszu 14 w kolumnie Klasa należy wprowadzić kolejno liczby 1, 2, 3, itd. Zostanie
wyświetlona najkrótsza odległość do szkoły dla każdej klasy.
3) Funkcja BD.MAKS zwraca największą zawartość komórki (pola) w bazie danych
(wszystkie rekordy) odpowiadającą określonym warunkom wyszukiwania.
Składnia
=BD.MAKS(baza_danych; pole_bazy_danych; kryteria_wyszukiwania)
Aby odwołać się do kolumny poprzez nazwę jej nagłówka, należy umieścić tę nazwę
w cudzysłowie.
Przykład
Aby dowiedzieć się, ile waży najcięższe dziecko w każdej klasie w powyższym przykładzie,
w komórce B16 należy wprowadzić następującą formułę:
=BD.MAKS(A1:E10;”Waga”;A13:E14)
W kolumnie Klasa należy wprowadzić kolejno liczby 1, 2, 3, itd. Po wprowadzeniu numeru
klasy zostanie wyświetlona waga najcięższego dziecka w tej klasie.
Strona 51 z 58
4) Funkcja BD.POLE zwraca zawartość komórki w bazie danych spełniającej określone
kryteria wyszukiwania. W przypadku błędu funkcja zwraca wartość #WARTOŚĆ!
w przypadku nie odnalezienia żadnego wiersza lub Błąd:502 w przypadku odnalezienia
więcej niż jednej komórki.
Składnia
=BD.POLE(baza_danych; pole_bazy_danych; kryteria_wyszukiwania)
Aby odwołać się do kolumny poprzez nazwę jej nagłówka, należy umieścić tę nazwę
w cudzysłowie.
Przykład
W powyższym przykładzie chcielibyśmy ustalić, w której klasie jest dziecko, którego imię
wprowadzono w komórce A14. Formuła jest wprowadzona w komórce B16 i różni się nieco
od poprzednich przykładów, ponieważ w parametrze pole_bazy_danych może zostać
wprowadzona tylko jedna kolumna (jedno pole bazy danych). Wprowadź następującą
formułę:
=BD.POLE(A1:E10;"Klasa";A13:E14)
W komórce A14 wprowadź imię Franek, co spowoduje wyświetlenie wyniku 2. Franek jest w
drugiej klasie. Jeśli zamiast słowa "Klasa" zostanie wpisane słowo "Wiek", wynikiem funkcji
będzie wiek Franka.
Można także wpisać w komórce C14 tylko wartość 11 i usunąć inne wpisy w tym wierszu.
Zmień formułę w komórce B16 w następujący sposób:
=BD.POLE(A1:E10;"Imię";A13:E14)
Zamiast wieku, zapytanie będzie dotyczyło imienia. Wynik zmieni się natychmiast: Jedynym
dzieckiem w wieku 11 lat jest Daniel.
5) Funkcja BD.ILE.REKORDÓW.A zwraca liczbę wierszy (rekordów) bazy danych
spełniających określone warunki wyszukiwania i zawiera wartości numeryczne lub
alfanumeryczne.
Składnia
=BD.ILE.REKORDÓW.A(baza_danych; pole_bazy_danych; kryteria_wyszukiwania)
Aby odwołać się do kolumny poprzez nazwę jej nagłówka, należy umieścić tę nazwę
w cudzysłowie.
Strona 52 z 58
Przykład
W powyższym przykładzie (należy przewinąć do góry) można wyszukać liczbę dzieci,
których imiona rozpoczynają się na literę E lub dalszą w alfabecie. Formułę w komórce B16
należy zmienić do postaci =BD.ILE.REKORDÓW.A(A1:E10;"Nazwa";A13:E14). Usuń
poprzednie kryteria wyszukiwania i w polu A14, w elemencie Nazwa, wpisz >=E. Wynik: 5.
Usunięcie wszystkich wartości liczbowych dla Grety (w wierszu 8) spowoduje zmianę
wyniku na 4. Wiersz 8 przestanie być uwzględniany w liczeniu, ponieważ nie zawiera
żadnych wartości liczbowych. Nazwa Greta jest tekstem, a nie liczbą. Uwaga: parametr
pole_bazy_danych musi wskazywać na kolumnę, która zawiera wartości.
6) Funkcja BD.SUMA zwraca sumę wartości wszystkich komórek w polu bazy danych
wszystkich wierszy (rekordy) odpowiadających określonym kryteriom wyszukiwania.
Składnia
=BD.SUMA(baza_danych; pole_bazy_danych; kryteria_wyszukiwania)
Aby odwołać się do kolumny poprzez nazwę jej nagłówka, należy umieścić tę nazwę
w cudzysłowie.
Przykład
Aby dowiedzieć się, ile wynosi sumaryczna odległość do szkoły wszystkich dzieci
na przyjęciu urodzinowym u Janka, które chodzą do drugiej klasy, w komórce B16 należy
wprowadzić następującą formułę:
=BD.SUMA (A1:E10;"Odległość do szkoły";A13:E14)
W wierszu 14 kolumny Klasa wprowadź wartość 2. Zostanie wyświetlona suma odległości
do szkoły wszystkich dzieci, które chodzą do drugiej klasy (1950).
7) Funkcja BD.ILE.REKORDÓW zwraca liczbę wierszy (rekordów) bazy danych
spełniających określone kryteria wyszukiwania i zawiera wartości numeryczne.
Składnia
=BD.ILE.REKORDÓW(baza_danych; pole_bazy_danych; kryteria_wyszukiwania)
Strona 53 z 58
Jako parametr pole_bazy_danych można podać komórkę określającą kolumnę lub liczbę
0 określającą całą bazę danych. Parametr nie może być pusty. Aby odwołać się do kolumny
poprzez nazwę jej nagłówka, należy umieścić tę nazwę w cudzysłowie.
Przykład
W powyższym przykładzie należy sprawdzić, ile dzieci ma do szkoły dalej niż 600 metrów.
Wynik ma być zapisany w komórce B16. Ustaw kursor w komórce B16. Wprowadź w niej
formułę =BD.ILE.REKORDÓW(A1:E10;0;A13:E14) Podczas wprowadzania zakresów
można skorzystać z pomocy Kreatora funkcji.
Baza_danych jest zakresem danych, obejmującym także nagłówki, uwzględnionym
w obliczeniach: w tym przypadku A1:E10. Pole_bazy_danych określa kolumnę kryteriów
wyszukiwania: w tym przypadku jest to cała baza danych. Kryteria_wyszukiwania stanowią
zakres, w którym można wprowadzić kryteria wyszukiwania: w tym przypadku A13:E14.
Aby dowiedzieć się, ile dzieci z drugiej klasy skończyło 7 lat, należy usunąć wpis >600
z komórki D14 i zamiast tego wprowadzić 2 w komórce B14 w kolumnie Klasa, natomiast
w komórce C14 po prawej stronie należy wprowadzić >7. Wynikiem formuły jest liczba 2.
Dwoje dzieci z drugiej klasy ukończyło 7 lat. Kryteria są połączone operatorem logicznym I,
ponieważ znajdują się w tym samym wierszu.
Strona 54 z 58
Przykładowe dane nr 2:
1. Funkcja BD.POLE – funkcja wbudowana w arkusz kalkulacyjny OpenOffice Calc
(kategoria bazy danych), zwraca zawartość komórki bazy danych spełniającej określone
kryterium wyszukiwania w oparciu o wyrażenia regularne, a w przypadku odnalezienia
więcej niż jednej komórki funkcja zwraca Błąd:502 (ilość trafnie odnalezionych komórek
możemy sprawdzić funkcją BD.ILE.REKORDÓW). Funkcja zwraca również treść
#WARTOŚĆ! w przypadku braku odnalezienia jakichkolwiek komórek spełniających dane
kryterium.
Składnia funkcji:
BD.POLE(baza; "kolumna"; kryterium)
baza – określenie obszaru bazy danych
kryterium – określenie kryterium przeszukiwania bazy danych
kolumna – określenie interesującej nas kolumny pól bazy danych
Baza danych powinna mieć określone nagłówki kolumn, do których odwoływać się można,
albo bezpośrednio podając adres komórki, albo podając w cudzysłowie tytuł nagłówka
kolumny.
Przykład
A B C D
1 Kod Artykuł Cena
2 123 Banan 2
3 124 Jabłko 1,2
4
5 Kod Artykuł Cena
6 124
7
Wyświetl: =BD.POLE()
Mając bazę cen i artykułów sklepowych, jak na tabeli z prawej (komórki A1:C3), mamy
konieczność na podstawie kodu artykułu wyświetlić jego cenę. Możemy więc skorzystać
z dodatkowej tabeli formularza kryteriów wyszukiwania – w polach A5:C6. Wpisując
w komórce A6 kod produktu, określimy kryterium wyszukiwania po kodzie. Komórka C7
będzie zawierała funkcję BD w użyciu umożliwiającym wyświetlenie ceny produktu.
Strona 55 z 58
Przykładowe zapytanie w polu C7:
= BD.POLE(A1:C3;"Cena";A5:C6)
zwraca 1,2
= BD.POLE(A1:C3;C1;A5:C6)
zwraca 1,2
= BD.POLE(A1:C3;"Artykuł";A5:C6)
zwraca Jabłko
2. Funkcja BD.ILE.REKORDÓW – funkcja wbudowana w arkusz kalkulacyjny
OpenOffice Calc (kategoria bazy danych), zwraca ilość rekordów bazy danych spełniającej
określone kryterium wyszukiwania w oparciu o wyrażenia regularne.
Składnia funkcji:
BD.ILE.REKORDÓW(baza; "kolumna"; kryterium)
baza – określenie obszaru bazy danych
kryterium – określenie kryterium przeszukiwania bazy danych
kolumna – określa kolumnę przeszukiwania w bazie danych, parametr 0 oznacza całą
bazę danych
Baza danych powinna mieć określone nagłówki kolumn, do których odwoływać się można,
albo bezpośrednio podając adres komórki, albo podając w cudzysłowie tytuł nagłówka
kolumny.
Przykład
A B C D
1 Kod Artykuł Cena
2 123 Banan 2
3 124 Jabłko 1,2
4
5 Kod Artykuł Cena
6 >1
7 Ile spełnia
warunek:
=BD.ILE.REKORDÓW()
Mając bazę cen i artykułów sklepowych, jak na tabeli z prawej (komórki A1:C3), mamy
konieczność określenia, ile artykułów w bazie danych spełnia kryterium kosztów. Możemy
Strona 56 z 58
więc skorzystać z dodatkowej tabeli formularza kryteriów wyszukiwania – w polach A5:C6.
Wpisując w komórce C6 kryterium ceny produktu, określając tym samym kryterium
wyszukiwania w bazie danych. Komórka C7 będzie zawierała funkcję BD w użyciu
umożliwiającym wyświetlenie ilości artykułów spełniających nasze kryteria.
Przykładowe zapytanie w polu C7:
= BD.ILE.REKORDÓW(A1:C3; 0;
A5:C6)
zwraca 2
czyli dwa przedmioty w całej bazie danych
kosztują więcej niż złotówkę
= BD.ILE.REKORDÓW(A1:C3;
"Cena"; A5:C6)
zwraca 2
czyli dwa przedmioty z kolumny "Cena",
posiadają cenę powyżej złotówki
= BD.ILE.REKORDÓW(A1:C3;
"Kod"; A5:C6)
zwraca 2
czyli
spośród wszystkich artykułów
posiadających kod, dwa są droższe niż
złotówka
Zadania
1. Stwórz bazę danych pracowników swojej firmy, w której zawarte będą następujące
informacje:
imię i nazwisko
datę urodzenia i wiek
datę zatrudnienia w firmie
wynagrodzenie za 12 miesięcy w minionym roku (styczeń – grudzień 2012)
informacja o aktualnym ubezpieczeniu NNW (tak/nie)
informacja o posiadaniu prawa jazdy kat. B (tak/nie)
informacja o posiadaniu aktualnych badań lekarskich
ilość dni przysługującego urlopu wypoczynkowego
ilość wykorzystanych dni urlopu
Informacje, które chcemy wyświetlić to:
średnie miesięczne wynagrodzenie pracownika
średni wiek wszystkich pracowników
pracownicy po 55 roku życia
ilość pracowników z wykorzystanym urlopem
pracownicy z niewykorzystanym urlopem
pracownicy posiadający prawo jazdy kat. B
Strona 57 z 58
2. Stwórz bazę danych zawierającą informacje na temat uczniów jednej klasy szkoły
podstawowej.
W tabeli zapisz informacje:
oceny końcowe z przedmiotów
średnia ze wszystkich ocen
oceny z zachowania
informację na temat promocji do następnej klasy (tak/nie)
Informacje, które chcemy wyświetlić:
średnia ocena dla klasy
liczba uczniów z zachowaniem nieodpowiednim i wzorowym
liczba uczniów promowanych i niepromowanych do następnej klasy
liczba uczniów ze średnią ocen powyżej 4,75
Strona 58 z 58
15. Źródła
1. OpenOffice.org
2. LibreOffice.org
3. WikiBooks.org
4. www.pcwiedza.pl
