Opracowanie: Tomasz Trawka
S p is tre ś ci
Krótki wstęp.................................................................................................................................2
Dlaczego arkusze są lepsze niż kalkulator..........................................................................................................2
Pakiet biurowy OpenOffice.org..........................................................................................................................2
Szybki wstęp do arkuszy kalkulacyjnych..................................................................................3
Wygląd okna arkusza kalkulacyjnego................................................................................................................3
Adresy komórek..................................................................................................................................................4
Zakresy komórek................................................................................................................................................4
Zawartość komórek............................................................................................................................................5
Wprowadzanie i edycja..................................................................................................................................5
Częste błędy...................................................................................................................................................5
Komunikaty i błędy dotyczące zawartości komórek.....................................................................................6
Operatory matematyczne....................................................................................................................................7
Kopiowanie wzorów...........................................................................................................................................7
Kopiowanie wzorów z niezablokowanymi adresami komórek.....................................................................7
Kopiowanie wzorów z zablokowanymi adresami komórek..........................................................................8
Przykład: w jakich przypadkach blokować adresy komórek.........................................................................9
Rozbijanie trudniejszych wzorów na części............................................................................10
Zasada postępowania........................................................................................................................................10
Przykład rozbijania wzoru...........................................................................................................................10
Funkcje logiczne w arkuszach kalkulacyjnych.......................................................................12
Funkcja JEŻELI................................................................................................................................................12
Składnia........................................................................................................................................................12
Przykładowe warunki funkcji JEŻELI.........................................................................................................12
Niektóre operatory stosowane w warunkach funkcji JEŻELI.....................................................................12
Przykłady użycia funkcji JEŻELI................................................................................................................13
Funkcja LUB (OR)...........................................................................................................................................13
Składnia........................................................................................................................................................13
Przykłady użycia funkcji LUB.....................................................................................................................14
Funkcja I (AND)...............................................................................................................................................14
Składnia........................................................................................................................................................14
Przykłady użycia funkcji I...........................................................................................................................15
Przykład: użycie funkcji logicznych w zadaniu...........................................................................................15
1 / 18
Krótki poradnik ułatwiający wejście w świat arkuszy kalkulacyjnych (wersja 1)
Krótki w stęp
Poradnik ten został napisany dla osób, które miały już kiedyś do czynienia z arkuszami
kalkulacyjnymi, ale z różnych przyczyn niewiele już z tego pamiętają. Nie ma więc
obowiązku czytania całego poniższego tekstu - wystarczy wybrać te fragmenty, które
się przydadzą.
Poradnik jest na etapie opracowywania, więc wszelkie uwagi dotyczące go (np. braku
jakichś opisów, za trudnych lub zbyt banalnych opisów, podanych lub brakujących
przykładów) proszę kierować do mnie na adres mailowy podany na zajęciach.
D lac z ego a r ku s z e są le p sz e n iż k alk u la to r
Większość ludzi na hasło arkusze kalkulacyjne mimowolnie wzdryga się i od razu
myśli to nie dla mnie . Faktycznie, jest mnóstwo ludzi, którym umiejętność korzystania z
arkuszy jest całkowicie zbędna i nigdy w życiu się nie przyda. Jednak spora grupa osób
dzięki umiejętności pracy w arkuszach kalkulacyjnych mogłaby znacząco ułatwić sobie
życie. Jak rozpoznać, do której grupy się należy?
Jeśli w pracy lub w domu często używasz kalkulatora do prowadzenia jakichś obliczeń,
jeśli jesteś uczniem/studentem lub jeśli prowadzisz jakieś zestawienia lub statystyki to
zdecydowanie jesteś w grupie, której praca z arkuszem kalkulacyjnym może przynieść
wiele pożytku.
P a kie t b iu row y Op e n Of fice .o rg
Wszystkie przykłady i obliczenia w tym poradniku będą przeprowadzane w arkuszu
kalkulacyjnym o nazwie Calc. Jest on częścią pakietu darmowego biurowego
OpenOffice.org, który w bardzo dużym stopniu jest zgodny z pakietem biurowym
Microsoft Office. O wyborze programu OpenOffice.org zadecydował przede wszystkim
właśnie fakt, że można go legalnie zainstalować bez żadnych opłat na dowolnym
komputerze.
Program można pobrać z wielu miejsc, choć najbezpieczniej sięgnąć do zródła, czyli
strony www producentów pakietu pod adresem http://pl.openoffice.org/. Pobierając
program z innych zródeł trzeba zwrócić uwagę na końcówkę nazwy, czyli .org , gdyż
zdarzają się inne odmiany tego pakietu biurowego starające się w różny sposób zarabiać
na korzystaniu z niego, na przykład przez wyświetlanie reklam na komputerze
użytkownika. Można je rozpoznać po innych końcówkach nazwy. Pakiet o nazwie
OpenOffice.org jest całkowicie darmowy.
2 / 18
Opracowanie: Tomasz Trawka
Szybki w stęp do arkuszy kalkulacyjnych
Wyg l ą d o k n a a rk u sz a k a lk u la c yj n e g o
Po włączeniu programu Calc z pakietu OpenOffice.org pojawi się nam okno o
wyglądzie zaprezentowanym poniżej.
Wygląd okna arkusza kalkulacyjnego Calc
Kolejne numery oznaczają:
1. pasek menu : zawiera wszystkie narzędzia arkusza pogrupowane tematycznie,
2. paski narzędziowe : zawierają przedstawione w formie ikon najczęściej używane
narzędzia arkusza, domyślnie włączone są dwa - pasek standardowy i pasek
formatowania. Paski narzędziowe można włączyć i wyłączyć w menu
Widok>Paski narzędzi,
3. pasek formuły : zawiera przyciski wstawiania sum i funkcji oraz wąski pasek, w
którym można przeglądać, wprowadzać i edytować zawartość poszczególnych
komórek,
4. pasek stanu : służy do pokazywania stanu programu, a po prawej zawiera
narzędzia do zmiany powiększenia obrazu,
5. zakładki arkuszy : obszar roboczy programu jest podzielony na części zwane
arkuszami, dzięki tym zakładkom można przechodzić między poszczególnymi
arkuszami,
6. oznaczenia kolumn i wierszy : wskazują adresy komórek oraz pozwalają na
zmianę ich rozmiaru,
7. obszar roboczy : główna część arkusza składająca się z poszczególnych
komórek, do których można wprowadzać różną zawartość - tekst, liczby, wzory
3 / 18
Krótki poradnik ułatwiający wejście w świat arkuszy kalkulacyjnych (wersja 1)
Ad re s y ko mó r ek
Każda komórka arkusza ma swój adres. Składa się on z litery (lub kilku liter)
oznaczających kolumnę oraz liczby określającej wiersz, na skrzyżowaniu których znajduje
się komórka.
Zasada wyznaczania adresu komórki
Rysunek powyżej przedstawia zasadę wyznaczania adresu komórek. Adres zaznaczonej
komórki lub zakresu można zawsze zobaczyć w polu zakresu (1). Oprócz kursora myszy,
który służy głównie do wskazywania komórek i narzędzi, w arkuszach jest także kursor
komórkowy (2). Jest to ramka wskazująca aktywną komórkę, do której będzie
wprowadzany tekst z klawiatury i do której będą się odnosiły działania użytkownika.
Kursor komórkowy można przesuwać przy użyciu myszki lub przycisków kursorów
klawiatury.
Z a k re s y k o mó re k
W niektórych funkcjach arkusza można wskazać do obliczeń więcej niż jedną komórkę.
Jest tak na przykład w funkcji suma, gdzie za jednym zamachem możemy dodać
zawartość wielu komórek na raz w jednej krótkiej funkcji. Aby tego dokonać należy
wskazać zakres komórek.
Zakres komórek zawsze ma kształt prostokąta. Można go wskazać myszką -
przytrzymując lewy klawisz i przeciągając kursorem po komórkach arkusza, lub z
klawiatury - przytrzymując klawisz SHIFT i naciskając przyciski kursorów na klawiaturze.
Zapis zakresu komórek w arkuszu wygląda następująco:
adres lewej górnej komórki zakresu : adres prawej dolnej komórki zakresu
Pomiędzy adresami komórek znajduje się symbol dwukropka łączący obie komórki
zakresu i oznaczający od-do . Zakres opisany jako A3:B5 oznacza wszystkie komórki
pomiędzy od A3 do B5, czyli A3, A4, A5, B3, B4, B5.
Na poniższym rysunku zaznaczony jest zakres B3:E9. Lewa górna komórka zakresu (1)
to B3, prawa dolna (2) to E9. W czasie wskazywania zakresu możemy zawsze zobaczyć
jego zapis w polu zakresu (3).
4 / 18
Opracowanie: Tomasz Trawka
Określanie zakresu komórek
Z aw arto ść ko mó r ek
W p r o w a d z a n i e i e d y c j a
Do pojedynczej komórki arkusza kalkulacyjnego można wprowadzić jeden z trzech
elementów:
" tekst : jako tekst jest traktowane wszystko, co zaczyna się od znaku nie będącego
cyfrą lub znakiem = . Teksty są automatycznie wyrównywane do lewej strony
komórki,
" liczby : wszystko co zaczyna się od cyfry i posiada dozwolone dla liczb znaki,
jest traktowane jako liczba. Liczby są automatycznie wyrównywane do prawej
strony komórki,
" wzory : każdy wzór musi zaczynać się od znaku = . Po nim następują liczby i
znaki działań matematycznych,
Aby wprowadzić do komórki jakąś zawartość wystarczy ją zaznaczyć i po prostu zacząć
pisać. Edycję zawartości komórki możemy wykonać po zaznaczeniu tej komórki kursorem
na kilka sposobów:
" albo klikamy myszą na zawartości paska formuły (wąski biały pasek u góry),
" albo klikamy dwukrotnie na komórce, którą chcemy zmienić,
" albo wcisnąć klawisz F2 powodujący przejście do edycji (jeśli w edytowanej
komórce znajdują się wzory, to na ekranie zostaną zaznaczone różnymi kolorami
komórki, których adresy znalazły się we wzorach),
C z ę s t e b ł ę d y
Wpisując lub kopiując z zewnątrz (np. z internetu) liczby do komórek, należy zwrócić
uwagę na znak rozdzielający część ułamkową od całkowitej. W Polsce stosowany jest
przecinek, a w krajach anglojęzycznych kropka. Przy domyślnych ustawieniach polskiej
wersji arkusza kalkulacyjnego Calc liczba wprowadzona jako 123.456 zostanie
potraktowana jako tekst, gdyż zawiera kropkę zamiast przecinka.
Wprowadzenie na początku wzoru przed znak = spacji powoduje, że wzór zostanie
5 / 18
Krótki poradnik ułatwiający wejście w świat arkuszy kalkulacyjnych (wersja 1)
potraktowany jako tekst, gdyż we wzorach pierwszym znakiem zawsze musi być
symbol = .
K o m u n i k a t y i b ł ę d y d o t y c z ą c e z a w a r t o ś c i k o m ó r e k
W trakcie pracy w arkuszu kalkulacyjnym program stara się nas informować o
ewentualnych problemach i błędach.
Sygnalizowanie błędów i problemów
Powyższy rysunek przedstawia najczęściej występujące komunikaty i oznaczenia
problemów.
W komórce B1 jest wprowadzony tekst, który wystaje poza tą komórkę. Ten sam tekst
wprowadzony do komórki B2 nie może zostać w całości pokazany, gdyż w komórce C2
znajduje się inny tekst, wobec czego nakładałyby się one na siebie. Symbolem
niedokończonego tekstu jest czerwona strzałka widoczna w komórce B2. Aby znikła
wystarczy po prostu zwiększyć szerokość kolumny B albo włączyć zawijanie tekstu w
komórce B2.
W komórce D3 znajdują się liczba 12345678. Jest ona tak szeroka, że nie mieści się w
wąskiej komórce. Arkusz sygnalizuje to pokazując znaki ###. Aby ponownie było widać
liczbę wystarczy powiększyć szerokość kolumny D lub zmniejszyć czcionkę w komórce
D3.
Liczba wprowadzona do komórki B5 była bardzo duża, wobec czego arkusz na jej
wyświetlenie potrzebowałby kolumny szerokiej na kilka centymetrów. W takich
przypadkach liczby są wyświetlane w formacie naukowym, gdzie E+36 oznacza 10
podniesione do potęgi 36, czyli 1036. Inne przykłady zapisu w formacie naukowym:
130 000 000 000 000 = 130 * 1012 = 130E+12
723 000 000 000 = 7,23 * 1011 = 7,23E+11
0,000 000 000 000 000 451 = 4,51 * 1016 = 4,51E-16
W komórce E6 błędnie wprowadzono adres komórki we wzorze =AAB12*4. Ponieważ
w arkuszu nie ma komórki o adresie AAB12, został wyświetlony komunikat o błędzie
nazwy. Zostaje on także wyświetlony, gdy wpiszemy niepoprawną nazwę funkcji lub
cokolwiek innego, czego nie może rozpoznać arkusz kalkulacyjny.
Często występujące i dobrze znane błędy, w typie dzielenia przez zero, mają swoje
własne opisujące je kody. W komórce B7 wprowadzono błędny wzór, skutkiem czego
6 / 18
Opracowanie: Tomasz Trawka
arkusz po rozpoznaniu rodzaju błędu wyświetlił w komórce komunikat o nim Błąd: 522
oraz umieścił w pasku stanu krótki opis charakteryzujący go (1).
Op e ra to r y ma te ma t yc zn e
We wzorach można wprowadzać następujące podstawowe działania matematyczne
bezpośrednio z klawiatury:
Działanie Operator Opis
dodawanie + =3+6
=A4+B17
odejmowanie - =7-34
=suma(A1:B15)-suma(C12:D15)
mnożenie * = 76*23
= A4*12
dzielenie / =45/5
=34/A6
potęgowanie ^ =2^3 to liczba 2 podniesiona do potęgi drugiej - 23
= B16^C13
pierwiastek ^ aby uzyskać z danej liczby pierwiastek stopnia x należy
podnieść ją do potęgi 1/x
=8^(1/3) to pierwiastek 3 stopnia z liczby 8
=1024^(1/10)
procent % do oznaczania wartości procentowych, zamienia liczbę
oznaczoną procentem (np. 13%=0,13)
=13%*546 czyli =0,13*546
=A12*13% czyli =A12*0,13
Ko p iow a n ie w zo rów
Swoją olbrzymią przydatność arkusze kalkulacyjne pokazują w przypadku, gdy mamy
te same obliczenia zastosować do dużej ilości danych. Wtedy dzięki kopiowaniu wzorów
można błyskawicznie dokonać dużej ilości koniecznych obliczeń. Aby jednak móc
korzystać z tej zalety arkuszy, należy zrozumieć co się dzieje z wzorem podczas
kopiowania.
Zasada kopiowania jest prosta: podczas kopiowania adresy komórek znajdujące się
we wzorze są przesuwane o tyle, o ile został przesunięty w czasie kopiowania cały
wzór.
Korzystając z kopiowania wzorów najlepiej zawsze sprawdzić którąś z nowo
powstałych komórek, aby sprawdzić, czy zawarty w niej wzór pobiera dane z właściwych
komórek. Najlepiej wykonać to przez wskazanie komórki z wzorem i wciśnięcie klawisza
F2 - powoduje on pokazanie różnymi kolorami komórek, z których korzysta wzór.
K o p i o w a n i e w z o r ó w z n i e z a b l o k o w a n y m i a d r e s a m i k o m ó r e k
Popatrzmy na przykład. W komórce B6 mam wzór =A4*5 i kopiuję go do komórki C8.
Zmiany we wzorze będą następujące:
7 / 18
Krótki poradnik ułatwiający wejście w świat arkuszy kalkulacyjnych (wersja 1)
" adres kolumny A z wzoru zmieni się na B, bo kopiując komórkę z B6 do C8 wzór
przesunął się o jedną kolumnę w prawo (z B do C),
" adres wiersza 4 z wzoru zmieni się na 6, bo kopiując komórkę z B6 do C8 wzór
przesunął się o dwa wiersze w dół (z 6 do 8),
Ostatecznie skopiowany do komórki C8 wzór będzie wyglądał następująco: =B6*5.
Zasadę kopiowania pokazuje poniższa ilustracja. Pokazano na niej, jak wzór z komórki C5
został skopiowany do kilku różnych komórek i jak wpłynęło to na adresy komórek
znajdujące się wewnątrz wzorów.
Zachowanie niezabezpieczonych adresów komórek podczas kopiowania wzorów
K o p i o w a n i e w z o r ó w z z a b l o k o w a n y m i a d r e s a m i k o m ó r e k
Czasami jednak zdarza się, że nie chcemy, aby podczas kopiowania wzoru zmieniały się
adresy komórek wewnątrz niego. W arkuszu istnieje możliwość zaznaczenia adresu
komórki wewnątrz wzoru w taki sposób, aby podczas kopiowania nie przesuwał się. Służy
do tego znak $. Umieszcza się go przed literą kolumny, jeśli nie chcę zmieniać kolumny
oraz przed numerem wiersza, jeśli nie chcę zmieniać wiersza. Przykłady:
" wzór =$A$4 podczas kopiowania zawsze będzie tak samo, bo i litera kolumny
(A) i numer wiersza (4) są zabezpieczone przed zmianami,
" wzór =$A4 podczas kopiowania będzie się zmieniał częściowo, bo tylko litera
kolumny (A) jest zabezpieczona, a numer wiersza (4) mam możliwość zmian,
" wzór =A$4 podczas kopiowania będzie się zmieniał częściowo, bo litera kolumny
(A) jest niezabezpieczona, a tylko numer wiersza (4) ma możliwość zmian,
Jak widać całkowite zabezpieczenie adresu komórki przed zmianami wynikającymi z
kopiowania wzoru uzyskuje się tylko wstawiając dwa znaki $: przed literę kolumny i
przed numer wiersza.
Poniższa ilustracja pokazuje, jak zachowują się podczas kopiowania wzorów
Zachowanie zabezpieczonych znakiem $ adresów komórek podczas kopiowania wzorów
8 / 18
Opracowanie: Tomasz Trawka
P r z y k ł a d : w j a k i c h p r z y p a d k a c h b l o k o w a ć a d r e s y k o m ó r e k
Poniższa ilustracja pokazuje, w jakich przypadkach należy stosować blokowanie
adresów komórek we wzorach. Pokazane są dwie metody rozwiązania zadania - dobra
oraz taka której najlepiej unikać.
Mamy tabelę z siedmioma cenami jakichś towarów. Potrzebujemy obliczyć cenę tych
towarów po naliczeniu marży, która podana jest w komórce B1. Wpierw tworzymy
właściwy wzór w zaznaczonej kolorem niebieskim komórce. Kwotę narzutu obliczamy
mnożąc cenę towaru i procent określający marżę, a następnie dodajemy do tego cenę
towaru, dzięki czemu uzyskujemy cenę towaru z doliczoną marżą. Następnie kopiujemy
wzór z niebieskiej komórki do pozostałych, oznaczonych kolorem żółtym, komórek.
Dwa rozwiązania - z użyciem blokowania adresów oraz bez niego
Rozwiązanie z lewej strony jest zdecydowanie lepsze od prawego. Wzory są powiązane
z komórką zawierającą marżę (B1), więc każda jej zmiana, na przykład na 15%,
automatycznie spowoduje przeliczenie wszystkich cen. Metoda ta wymaga jednak
zabezpieczenia jednego z adresów wewnątrz wzoru znakami $ ($B$1), aby nie zmieniał
się on podczas kopiowania.
Rozwiązanie po prawej stronie jest poprawne pod względem obliczeniowym, czyli da
poprawny wynik, i w dodatku prostsze, gdyż nie trzeba używać blokady adresu znakiem $.
Jednak w momencie zmiany marży czeka nas dodatkowa praca z poprawieniem
pierwszego wzoru i jego ponownym skopiowaniem na pozostałe komórki. Ponadto,
bardzo łatwo zapomnieć, że po zmianie marży trzeba wprowadzać jakiekolwiek zmiany
we wzorze, gdyż normalnie w arkuszu wzory są niewidoczne i zamiast nich w komórkach
widać tylko obliczone wartości.
9 / 18
Krótki poradnik ułatwiający wejście w świat arkuszy kalkulacyjnych (wersja 1)
Rozbijanie trudniejszych w zorów na części
Z a s ad a po stę p ow an ia
W trakcie obliczeń często można się spotkać z problemem wprowadzenia
skomplikowanych wzorów w rodzaju tego poniżej:
n
śą xn-xśrźą
z1=
"
xmin
i=1
Wzory tego typu nie da się obliczyć w jednej komórce za pomocą jednego wyrażenia.
Jednak można je łatwo obliczyć rozbijając na mniejsze części składowe. W przypadku
podanego wzoru musimy wpierw wyznaczyć średnią (x ) oraz najmniejszą (x ) wartość
śr min
zmiennej X, a następnie dla każdej wartości zmiennej X z zadanego zbioru danych
obliczyć wyrażenie (x -x )/x , gdzie x oznacza kolejne wartości zmiennej X.
n śr min n
P r z y k ł a d r o z b i j a n i a w z o r u
Załóżmy, że mamy do obliczenia zadanie podane poniżej. W komórce oznaczonej
kolorem zielonym powinna znalezć się wartość obliczona wg zadanego wzoru.
Ponieważ nie można wartości z1 obliczyć od razu, za pomocą jednej formuły, musimy
rozbić obliczenia na części. Wpierw obliczamy średnią i najmniejszą wartość zmiennej X.
Do komórki B8 wprowadzamy wzór =ŚREDNIA(B2:B7), a do komórki B9 wzór
=MIN(B2:B7).
Teraz musimy policzyć dla każdego x znajdującego się w zbiorze wartość ułamka,
n
czyli (x -x )/x . Do komórki C2 wpisujemy wzór =(B2-$B$8)/$B$9. Znaki dolara
n śr min
zabezpieczają adresy komórek przed zmianą podczas kopiowania wzoru. Ponieważ
obliczenia dla zmiennej x1 są takie same, jak dla pozostałych zmiennych ze zbioru, to
wzór z komórki C6 kopiujemy do komórek od C7 do C15. Można tego dokonać
ustawiając kursor komórkowy na komórce C2 i po złapaniu za mały kwadratowy uchwyt
w prawym dolnym rogu kursora komórkowego przeciągając w dół.
10 / 18
Opracowanie: Tomasz Trawka
Skoro mamy już policzone wartości ułamków dla wszystkich zmiennych X, to do
zakończenia obliczeń wystarczy już tylko zsumować liczby znajdujące się w kolumnie C.
Do komórki I7 wpisujemy wzór =suma(C2:C7).
Zadanie zostało obliczone.
11 / 18
Krótki poradnik ułatwiający wejście w świat arkuszy kalkulacyjnych (wersja 1)
Funkcje logiczne w arkuszach kalkulacyjnych
Fu n kc ja JEŻ E L I
Funkcja jeżeli pozwala na wybranie jednego z dwóch różnych działań na podstawie
zadanego warunku. Wykonanie funkcji zaczyna się od obliczenia parametru warunek -
sprawdzane jest, czy jego wynikiem jest wartość logiczna PRAWDA, czy wartość
logiczna FAASZ. Jeśli wynikiem jest PRAWDA, to w komórce zawierającej funkcję jeżeli
zostanie wykonane działanie z parametru drugiego - działanie dla prawdy. Natomiast jeśli
warunek zwróci FAASZ, to w komórce wykonuje się działanie z parametru trzeciego, czyli
działanie dla fałszu. Jako parametry działanie dla prawdy i działanie dla fałszu można
wpisać dowolne dające się obliczyć wyrażenie.
Jeżeli funkcja jeżeli powinna sprawdzić więcej warunków niż jeden, należy użyć
funkcji logicznych LUB oraz I.
S k ł a d n i a
= jeżeli ( warunek ; działanie dla prawdy ; działanie dla fałszu )
P r z y k ł a d o w e w a r u n k i f u n k c j i J E Ż E L I
Warunek Wynik Opis
5>6 FAASZ sprawdza czy liczba 5 jest większa od liczby 6
4-3<2 PRAWDA wpierw oblicza wyrażenie 4-3, a następnie sprawdza, czy
wynik odejmowania jest mniejszy od liczby 2
A4>7 zależny od pobiera wartość z komórki A4 i sprawdza, czy jest ona
wartości komórki większa od liczby 7
A4
A5<> zależny od sprawdza czy zawartość komórki jest różna od od
wartości komórki pustego łańcucha znaków (zawartego w dwóch
A5 cudzysłowach
A7=B3-B7 zależny od wpierw oblicza wartość wyrażenia po prawej, pobiera
wartości komórek wartości komórek z B3 i B7, a następnie sprawdza, czy
A7, B3, B7 liczba będąca wynikiem odejmowania jest równa
wartości komórki A7
N i e k t ó r e o p e r a t o r y s t o s o w a n e w w a r u n k a c h f u n k c j i J E Ż E L I
Operator Opis Przykłady
= RÓWNY: sprawdza czy lewa i prawa strona warunku są 3=5
równe C7= pies
<> RÓŻNY: sprawdza czy lewa i prawa strona warunku są
różne
> WIKSZY NIŻ: sprawdza czy lewa strona warunku jest
większa niż prawa strona warunku
>= WIKSZY LUB RÓWNY: sprawdza czy lewa strona
warunku jest większa lub równa prawej stronie warunku
12 / 18
Opracowanie: Tomasz Trawka
< MNIEJSZY NIŻ: sprawdza czy lewa strona warunku jest
mniejsza niż prawa strona warunku
<= MNIEJSZY LUB RÓWNY: sprawdza czy lewa strona
warunku jest mniejsza lub równa prawej stronie warunku
P r z y k ł a d y u ż y c i a f u n k c j i J E Ż E L I
= jeżeli ( A5>C7 ; A5 ; C7 ) : jeżeli wartość komórki A5 jest większa niż wartość
komórki C7, to do komórki zawierającej funkcję zostanie wstawiona zawartość komórki
A5. Jeśli jednak wartość z komórki A5 nie jest większa od wartości z komórki C7, to do
komórki zawierającej funkcję jeżeli zostanie wstawiona zawartość z komórki C7
= jeżeli ( A5<> ; A5 ; pusta ) : jeżeli komórka A5 nie jest pusta (czyli różni się od
pustego ciągu tekstowego), to do komórki zawierającej funkcję zostanie wstawiona
zawartość komórki A5. Jeśli jednak komórka A5 jest pusta, to do komórki zawierającej
funkcję jeżeli zostanie wstawiony napis pusta (napisy umieszczamy zawsze wewnątrz
cudzysłowów)
= jeżeli ( A5*2+C7 >= B6-A16*3 ; A5-3 ; ) : jeżeli lewa strona wyrażenia zadanego w
warunku jest większa lub równa stronie prawej, to do komórki zawierającej funkcję
zostanie wstawione wyrażenie A5-3. W przeciwnym wypadku, z powodu braku trzeciego
parametru, do komórki zawierającej funkcję jeżeli zostanie wstawiony napis FAASZ
Fu n kc ja LU B (OR )
Funkcja pozwala na łączenie kilku warunków logicznych w jeden wynik logiczny.
Ogólnie można powiedzieć, że wynikiem funkcji będzie FAASZ tylko wtedy, kiedy
wszystkie warunki będą fałszywe.
wynik wynik wynik funkcji
warunku 1 warunku 2 =LUB ( warunek1 ; warunek2 )
PRAWDA PRAWDA PRAWDA
PRAWDA FAASZ PRAWDA
FAASZ PRAWDA PRAWDA
FAASZ FAASZ FAASZ
S k ł a d n i a
= LUB ( warunek1 ; warunek2 ; ... ; warunek30 )
Funkcja LUB jest najczęściej używana w połączeniu z funkcją JEŻELI
= JEŻELI ( LUB ( warunek1 ; warunek2 ) ; działanie ; działanie )
Najpierw na podstawie warunku1 i warunku2 wyznacza się wynik funkcji LUB,
następnie wynik ten po wstawieniu do funkcji JEŻELI w miejsce parametru warunek
decyduje o tym, które działanie ma wykonać ta funkcja
13 / 18
Krótki poradnik ułatwiający wejście w świat arkuszy kalkulacyjnych (wersja 1)
P r z y k ł a d y u ż y c i a f u n k c j i L U B
= lub ( 5>6 ; 34/2=17 ) : funkcja zawiera dwa warunki. Pierwszy z nich (5>6) zwróci
wartość FAASZ, natomiast drugi (34/2=17) zwróci wartość PRAWDA. Po zastąpieniu
warunków wynikami otrzymamy lub(FAASZ;PRAWDA). Ponieważ jeden z warunków
jest prawdziwy, to wynikiem funkcji jest wartość PRAWDA
= lub ( Ala <> Ola ; 3>6 ; suma(3;6;9)<17-4) : funkcja zawiera trzy warunki.
Pierwszy z nich ( Ala <> Ola ) porównuje dwa teksty i ponieważ są różne, zwróci
wartość FAASZ. Drugi warunek (3>6) zwróci oczywiście wartość FAASZ. Trzeci warunek
wpierw obliczy wartość sumy i sprawdzi czy jest ona mniejsza od obliczonej liczby z
prawej strony warunku - wynikiem będzie FAASZ. Po zastąpieniu warunków wynikami
otrzymamy lub(FAASZ;FAASZ;FAASZ). Ponieważ żaden z warunków nie jest
prawdziwy, to wynikiem funkcji jest wartość FAASZ
= lub ( 3>=1 ; 3<=7 ) : funkcja zawiera dwa warunki. Pierwszy z nich (3>=1) zwróci
wartość PRAWDA, natomiast drugi (3<=7) również zwróci wartość PRAWDA. Po
zastąpieniu warunków wynikami otrzymamy lub(PRAWDA;PRAWDA). Ponieważ
przynajmniej jeden z warunków jest prawdziwy, to wynikiem funkcji jest wartość
PRAWDA
Fu n kc ja I (AN D )
Funkcja pozwala na łączenie kilku warunków logicznych w jeden wynik logiczny.
Ogólnie można powiedzieć, że jeżeli chociaż jeden warunek wstawiony do funkcji nie jest
PRAWD, to wynikiem funkcji będzie FAASZ.
We niektórych arkuszach obliczeniowych istnieje funkcja ORAZ działająca tak samo
jak funkcja I.
wynik wynik wynik funkcji
warunku 1 warunku 2 =I ( warunek1 ; warunek2 )
PRAWDA PRAWDA PRAWDA
PRAWDA FAASZ FAASZ
FAASZ PRAWDA FAASZ
FAASZ FAASZ FAASZ
S k ł a d n i a
=I ( warunek1 ; warunek2 ; ... ; warunek30 )
Funkcja I jest najczęściej używana w połączeniu z funkcją JEŻELI
= JEŻELI ( I ( warunek1 ; warunek2 ) ; działanie ; działanie )
Najpierw na podstawie warunku1 i warunku2 wyznacza się wynik funkcji I, a następnie
wynik ten po wstawieniu do funkcji JEŻELI w miejsce parametru warunek decyduje o
tym, które działanie ma wykonać ta funkcja
14 / 18
Opracowanie: Tomasz Trawka
P r z y k ł a d y u ż y c i a f u n k c j i I
= i ( 5>6 ; 34/2=17 ) : funkcja zawiera dwa warunki. Pierwszy z nich (5>6) zwróci
wartość FAASZ, natomiast drugi (34/2=17) zwróci wartość PRAWDA. Po zastąpieniu
warunków wynikami otrzymamy lub(FAASZ;PRAWDA). Ponieważ jeden z warunków
jest fałszywy, to wynikiem funkcji jest wartość FAASZ
= i ( Ala <> Ola ; 3>6 ; suma(3;6;9)<17-4) : funkcja zawiera trzy warunki.
Pierwszy z nich ( Ala <> Ola ) porównuje dwa teksty i ponieważ są różne, zwróci
wartość FAASZ. Drugi warunek (3>6) zwróci oczywiście wartość FAASZ. Trzeci warunek
wpierw obliczy wartość sumy i sprawdzi czy jest ona mniejsza od obliczonej liczby z
prawej strony warunku - wynikiem będzie FAASZ. Po zastąpieniu warunków wynikami
otrzymamy lub(FAASZ;FAASZ;FAASZ). Ponieważ w funkcji występują same warunki
fałszywe, to wynikiem funkcji jest wartość FAASZ
= i ( 3>=1 ; 3<=7 ) : funkcja zawiera dwa warunki. Pierwszy z nich (3>=1) zwróci
wartość PRAWDA, natomiast drugi (3<=7) również zwróci wartość PRAWDA. Po
zastąpieniu warunków wynikami otrzymamy lub(PRAWDA;PRAWDA). Ponieważ
wszystkie warunki są prawdziwe, to wynikiem funkcji jest wartość PRAWDA
P r z y k ł a d : u ż y c i e f u n k c j i l o g i c z n y c h w z a d a n i u
Poniżej przedstawiono zadanie, w którym użycie funkcji logicznych pomaga szybko i
całkowicie automatycznie wyznaczyć odpowiedzi na zadanie pytania. Osoby, które myślą,
że dane zadanie rozwiązałyby szybciej bez użycia arkusza niech wyobrażą sobie, że
zamiast danych 10 osób w zadaniu mamy dane 3000 osób.
Obliczanie średniego wzrostu według zadanych kryteriów, krok 1
Krok pierwszy to wpisanie poprawnego wzoru do komórki D3 (dla pierwszej osoby).
Mamy sprawdzić tylko jeden warunek - czy osoba jest starsza niż 50 lat. Jeśli jest starsza,
to chcemy jej wzrost podać do obliczeń średniej. Jeśli nie jest to wpisujemy tekst
informujący, że dana osoba nie pasuje do warunków zadania. Ostatecznie wzór ma
następujący wygląd: =jeżeli ( A3 > 50 ; B3 ; ten nie ).
Komórka A3 to wiek pierwszej osoby, podczas kopiowania wzoru w dół będzie się
zmieniała na komórkę z wiekiem osoby drugiej, trzeciej, itd. Komórka B3 to wzrost osoby
pierwszej, podczas kopiowania będzie się zmieniać podobnie jak komórka z wiekiem.
Napis ten nie będzie wstawiany do komórek, kiedy sprawdzana osoba nie będzie
pasowała do warunku starszy niż 50 lat. Sam napis nie jest konieczny, ale dzięki niemu
15 / 18
Krótki poradnik ułatwiający wejście w świat arkuszy kalkulacyjnych (wersja 1)
widać, do których komórek funkcja została skopiowana.
Po wpisaniu wzoru kopiujemy go na pozostałe osoby. Pozostało jedynie użyć funkcji do
szybkiego zliczenia średniej z podanych wzrostów. Do komórki D14 wprowadzamy
następujący wzór: =średnia(D3:D12) i zadanie pierwsze jest rozwiązane.
Obliczanie średniego wzrostu według zadanych kryteriów, krok 2
Kolejne zadanie wymaga zastosowania dwóch funkcji. Na pewno użyjemy funkcji
JEŻELI, aby sprawdzić, czy dane osoby pasują do warunków. Dodatkowo trzeba będzie
użyć jednej z funkcji łączącej kilka warunków logicznych w jedną całość (czyli funkcji
LUB albo funkcji I). W treści zadania drugiego (komórka E1) czytamy, że należy wybrać
wiek osób młodszych niż 45 lat lub o wzroście powyżej 163 cm. Użyty spójnik jasno
sugeruje, o którą funkcję logiczną chodzi. Poniższy rysunek pokazuje sposób rozwiązania
zadania drugiego.
Schemat tworzenia funkcji do zadania 2
Dalsze postępowanie jest analogiczne jak w zadaniu pierwszym. Funkcję jeżeli wpisaną
dla danych pierwszej osoby (komórka E3) kopiujemy do komórek E4:E12 dla pozostałych
osób. Po tej operacji w kolumnie E powinny się pokazać liczby określające wiek osób
pasujących do zadanych warunków. Aby wybrać z nich najstarszą, w komórce E14
wpisujemy funkcję MAKS z odpowiednim zakresem danych. Poniższa ilustracje pokazują
obie funkcje oraz wyniki ich działania.
16 / 18
Opracowanie: Tomasz Trawka
Szukanie najstarszej osoby według zadanych kryteriów, krok 1
Szukanie najstarszej osoby według zadanych kryteriów, krok 2
Zadanie trzecie można rozwiązać analogicznie jak zadanie drugie. Zmieni się jedynie
funkcja z LUB na I, ponieważ warunek wzrost pomiędzy 120 a 150 cm można zapisać
jako dwa warunki: wzrost większy niż 120 cm i wzrost niższy niż 150 cm. Czyli dla
pierwszej osoby zawartość komórki F3 będzie wyglądała następująco:
=jeżeli(i(B3>120;B3<150);A3; ten nie ).
Obliczanie sumy lat osób według zadanych kryteriów, krok 1
Aby policzyć sumę lat pasujących osób wystarczy już tylko użyć w komórce F14
17 / 18
Krótki poradnik ułatwiający wejście w świat arkuszy kalkulacyjnych (wersja 1)
funkcji suma z odpowiednim zakresem danych. Wynik zadania 3 pokazuje poniższa
ilustracja.
Obliczanie sumy lat osób według zadanych kryteriów, krok 2
18 / 18
Wyszukiwarka
Podobne podstrony:
Syllabus do Modułu AM4 Arkusze kalkulacyjne, poziom zaawansowany wersja 1 0 6E5 Arkusz kalkulacyjny w pracy wychowawcy i nauczyciela (kurs podstawowy) v1 0Syllabus do Modułu AM4 Arkusze kalkulacyjne, poziom zaawansowany wersja 2 0arkusz kalkulacyjny 4 wzorzec04 Arkusz kalkulacyjnyT03 Arkusz kalkulacyjny od podstaw cz 3 4 godzPomoc do programu programmerDo arkuszaarkusz kalkulacyjnyarkusz kalkulacyjny 5Budowa i zastosowanie arkusza kalkulacyjnego Microsoft Excel Sławomir Siudowskiarkusz kalkulacyjny 4arkusz kalkulacyjny minuty wzorzecwięcej podobnych podstron