Microsoft Excel 2000

Korzystanie z funkcji logicznych

Uniwersalnym mechanizmem pozwalającym na podejmowanie

„decyzji” w arkuszu kalkulacyjnym jest funkcja JEŻELI. Szablon tej

funkcji ma postać:

JE ELI(warunek; wyra enie_gdy warunek_spełniony;

wyra enie_gdy_warunek_nie_spełniony).

Excel sprawdza prawdziwość warunku (pierwszego argumentu

funkcji) i alternatywnie wybiera jedno z pozostałych dwóch wyrażeń

jako wynik działania funkcji.. Siła tego mechanizmu polega na tym,

że każdy z trzech argumentów funkcji może być tekstem, liczbą lub

dowolnie skomplikowanym wyrażeniem, także funkcyjnym, co

pozwala wyrazić złożone sytuacje.

Przykład 1.

Sprzedawca otrzymuje prowizję w wysokości 3% wartości sprzedaży

począwszy od wartości 5000 zł lub 2% sprzedaży poniżej tej wartości:

Sprzeda

Prowizja

Warto

graniczna sprzeday

5

000,00 zł

1

000,00 zł

2

0,00 zł

Stawka poniej

2%

6

000,00 zł

1

80,00 zł

Stawka powyej

3%

3

800,00 zł

7

6,00 zł

7

200,00 zł

2

16,00 zł

Przykład 2.

-10 Liczba ujemna

15 Liczba dodatnia lub zero

6 Liczba dodatnia lub zero

-4 Liczba ujemna

2

!"

"

!"

"

#

#

#

0

0 Liczba dodatnia lub zero

$

'

()('*

+

'

,

)

$

'

+

'

%

&

-

&

%

.

/

%

&

-

.

1

- 133 -

Microsoft Excel 2000

Jeżeli chcemy potraktować zero oddzielnie, musimy skomplikować

nieco formułę. Dyskusja postępowania w złożonych przypadkach

przedstawiona jest w Zadaniu 11.

Zadanie 9.

Rozszerzyć tabelę z Zadania 6 wprowadzając dodatkową zniżkę dla

stałych klientów firmy wynoszącą 5% składki podstawowej. Pod

kolumną Do zapłaty umieścić dodatkowo sumę składek zapłaconych

przez stałych klientów.

Uwaga!

Jednym z możliwych rozwiązań jest następujące podejście:

1. Wstaw np. za liczbą zabezpieczeń dodatkową kolumnę „Stały

klient” i wpisz w niej dla niektórych klientów literę T (od

Tak)oznaczającą stałego klienta.

2. Wstaw kolumnę „Zniżka dla stałych klientów” zawierającą

funkcję JEŻELI. Pamiętaj, że w warunku logicznym musisz

wpisać T w cudzysłowie jako stałą znakową!

3. Skoryguj formuły wyliczające kwotę do zapłaty.

4. Do wyliczenia, jaka część kwoty do zapłaty pochodzi od stałych

klientów przydatna będzie funkcja SUMA.JEŻELI. Jej szablon

zapiszmy w uproszczeniu:

SUMA.JEŻELI

( gdzie_szukamy ; czego_szukamy ; co_sumujemy).

W naszym zadaniu można ją zastosować następująco:

SUMA.JEŻELI

( kolumna_Stały_klient ; "T" ; kolumna_Do_zapłaty).

- 134 -

Microsoft Excel 2000

Zadanie 10.

Dysponujemy 10. pomiarami pewnej wielkości. Należy wyliczyć, ile z

nich mieści się w zakresie +/- 10% w stosunku do średniej.

Procentowa wielkość odchylenia powinna być parametrem w zadaniu.

Zastosować nazwy dla tego parametru oraz dla średniej i nie

posługiwać się adresami komórek.

Rozwiązanie.

Zastosujemy dwa sposoby rozwiązania tego zadania:

1. Pomiary umie cimy w jednej kolumnie a funkcj wyboru „dobrych”

warto ci w kolumnie s siedniej. „Dobrym” warto ciom przypiszemy

warto 1 a pozostałym 0. Gdy zsumuje si kolumn „warto ciuj c ”

otrzymamy rozwi zanie.

2. Warto ciom dobrym przypiszemy tekst „DOBRY” a potem zliczymy te

„dobre” przy pomocy funkcji LICZ.JE ELI(...).

- 135 -

Microsoft Excel 2000

Uwaga!

1. Funkcja

MODUŁ.LICZBY

zwraca wartość bezwzględną

argumentu.

2. W polu nazwy została wpisana nazwa „odchylenie” dla komórki G2, podobnie wprowadzono nazwę „średnia” dla komórki G4.

3. Funkcję LICZ.JEŻELI znajdzie czytelnik w kategorii funkcji statystycznych.

Zadanie 11.

Wpisać odpowiednie wyrażenia logiczne do tablicy oceniającej wzrost

osób. Tablica ma dwie kolumny: WZROST (cm) oraz OCENA. W

pierwszej kolumnie wpisane zostaną przykładowe wartości wzrostu.

W kolumnie OCENA automatycznie powinien ukazać się tekst

NISKI, ŚREDNI lub WYSOKI.

Warunki:

• NISKI, gdy wzrost <=160cm

• ŚREDNI, gdy 160 cm< wzrost <=175

• WYSOKI, gdy wzrost > 175.

Uwaga!

1. Formuła musi być złożona, np. może mieć postać:

=JEŻELI(A1<=160;"NISKI";JEŻELI(A1>175;"WYSOKI";"ŚREDNI")). Założyliśmy tu, że aktualny wzrost osoby znajduje się w komórce A1.Wystąpiło tu zagłębienie funkcji JEŻELI.

2. Można ją skonstruować na wiele sposobów, np. gdyby zacząć od

wzrostu ŚREDNIEGO, mogłaby mieć postać:

=JEŻELI(ORAZ(A1>160;A1<=175);"ŚREDNI";JEŻELI(A1>175

;"WYSOKI";"NISKI")) , albo

- 136 -

Microsoft Excel 2000

=JEŻELI(LUB(A1<=160;A1>175);

JEŻELI(A1>175;"WYSOKI";"NISKI");”ŚREDNI”).

Mamy tutaj superpozycję JEŻELI(...) i ORAZ(...) albo JEŻELI(..) i LUB(...). Funkcja ORAZ() zwraca wartość PRAWDA lub FAŁSZ, w zależności od tego, czy koniunkcja

warunków będących argumentami funkcji jest prawdziwa,

natomiast wartość funkcji LUB(...) zależy od prawdziwości

alternatywy argumentów.

3. Do dyspozycji mamy funkcje logiczne ORAZ(...), LUB(...) i NIE(...) oraz stałe FAŁSZ i PRAWDA. ORAZ(...) i LUB(...) mogą akceptować wiele argumentów, a NIE(...) jest funkcją

jednoargumentową.

4. Przydatne jest uświadomienie sobie, że do takiego samego

rozwiązania zagadnienia prowadzi wiele dróg, gdyż przykładowo:

JEŻELI(NIE(A1<A2);wyr1;wyr2)

JEŻELI(A1<A2;wyr2;wyr1)

JEŻELI(A1>=A2;wyr1;wyr2)

dają ten sam wynik!

Często intuicyjnie wybieramy jakieś rozwiązanie, a dobrze było by

zastanowić się czy daje ono krótszy i bardziej oczywisty zapis w

stosunku do istniejących alternatywnych możliwości. Oto

formalnie równoważne konstrukcje:

JEŻELI(ORAZ(warunek1;warunek2);wylicz1;wylicz2)

można

wykonać bez ORAZ(...):

JEŻELI(warunek1;JEŻELI(warunek2;wylicz1;wylicz2);wylicz2).

JEŻELI(LUB(warunek1; warunek2);wylicz1;wylicz2) można

wykonać bez LUB(...):

JEŻELI(warunek1;wylicz1;JEŻELI(warunek2;wylicz1;wylicz2)).

JEŻELI(NIE(warunek);wylicz1;wylicz2) można wykonać bez

NIE(...):

JEŻELI(warunek;wylicz2;wylicz1).

- 137 -

Microsoft Excel 2000

Wielokrotnie zdarza się, że warunek składa się z koniunkcji wielu

warunków elementarnych. I wtedy aż się prosi by użyć jedną

wieloargumentową funkcję ORAZ i jedną funkcję JEŻELI, a nie skomplikowany ciąg zagłębionych funkcji JEŻELI!.

Niestety w praktyce czasem widać to drugie...

Zadanie 12.

Rozszerzyć tabelę z Zadania 5 o kolumnę opłaty za windę

wprowadzając następujące zasady jej obliczania:

• Opłata obowiązuje tylko lokatorów mieszkających powyżej 1-go

piętra

• Lokatorzy z pięter 2-5 płacą 3/4 stawki

• Począwszy od 6-go piętra obowiązuje cała stawka

Zadanie 13.

Na osi OX umieszczamy dwa odcinki opisane współrzędnymi

początków i końców: P1 i K1 oraz P2 i K2. Utworzyć wyrażenie,

które w pewnej komórce na podstawie tych danych wyda „werdykt” w

postaci jednego z trzech tekstów:

„ODCINKI SĄ ROZŁĄCZNE”,„ODCINKI ZACHODZĄ NA

SIEBIE”,

„JEDEN ODCINKEK ZAWARTY JEST W DRUGIM”

Uwaga!

1. Odcinek 1-szy (opisany przez P1 i K1) wcale nie musi być na

lewo od odcinka drugiego P2K2, ale zawsze powinno być Pi<Ki

(to założenie jest zgodne z intuicją i upraszcza problem)!

2. W zadaniach ze złożonymi wyrażeniami warunkowymi możemy

otrzymać mniej lub bardziej skomplikowane, choć równoważne

- 138 -

Microsoft Excel 2000

wyniki. Tu najlepiej rozpatrzyć wpierw rozłączność odcinków,

potem zawartość jednego w drugim a pozostałą sytuację (która jest

najbardziej złożona, gdyby ją analizować) zaliczyć do

zachodzących na siebie.

Zadanie 14.

Na podstawie następującej tabeli z danymi wykonać polecenia 1-3.

Baza danych o pracow nikach

Data

Sta

Płaca

Nagroda

Nazw isko

Imi

zatrudnienia

pracy

zasadnicza

nadzw yczajna

Chudy

Jan

01.03.86

960

Grab

Stanisław

01.02.97

1520

Izbicki

Tadeusz

01.02.89

980

Kasprzyk

Marta

01.07.67

840

Muszkat

Anna

01.08.82

1980

Rudnik

Bo ena

01.05.96

1400

Rybczyk

Marian

01.04.92

850

Rybczyk

Stefan

01.04.68

1500

Teodor

Marek

01.01.80

730

Zemborak

Barbara

01.06.76

2470

1. Obliczyć staż pracy pracowników (obcięty do pełnych lat) .

Wykorzystać funkcje: DZIŚ() dla określenia bieżącej daty oraz LICZBA.CAŁK (...). Przyjąć, że rok ma 365 dni.

2. Obliczyć wysokość nagrody nadzwyczajnej zgodnie z zasadami:

staż pracy

nagroda

<15

0

15-30

za każdy rok stażu pracy 10% płacy

30 i więcej

300% płacy zasadniczej

3. Dokonać sortowania danych w bazie według nazwiska i imienia.

- 139 -

Microsoft Excel 2000

Zadanie 15.

Dana jest tabelka zawierająca dane dotyczące zawartości magazynu:

Magazyn 11

Data

Toru ul. Jasna 12/3

Nazwa

j.m.

Ilo

Cena

Warto

VAT Stan min Zamawia

Ile

Towar 1

szt

8

12

0

10

Towar 2

kg

7

17

22

6

Towar 3

szt

12

9

7

12

Towar 4

szt

11

32

0

8

Towar 5

kg

9

24

7

10

Towar 6

szt

15

46

7

14

Towar 7

szt

18

31

22

23

1. Kolumnę „Wartość” wypełnić fomułami obliczającymi wartość towaru brutto, tzn. z uwzględnieniem podatku VAT. W kolumnie

„VAT” podane są procentowe stawki VAT dla poszczególnych

towarów.

2. Do kolumny „Zamawiać” wpisać formułę, która umieści tam

napis TAK jeśli trzeba dany towar zamówić (tzn. gdy Ilość < Stan

min).

3. Do kolumny „Ile” wpisać formuły obliczające ilość zamawianą, które umieszczą tam ilość zamawianego towaru zgodnie z regułą:

(Stan min - Ilość)*3+3, ale tylko wtedy, gdy towar trzeba

zamówić.

4. Podsumować kolumny „Wartość” oraz „Ile”.

5. W komórce obok napisu „Data” umieścić formułę obliczającą

bieżącą datę.

6. Pod kolumną „Zamawiać” zliczyć ilość pozycji zamawianych.

(przy pomocy funkcji LICZ.JEŻELI).

- 140 -

Microsoft Excel 2000

Zadanie 16.

Utworzyć listę płac analogiczną do zbudowanej w zadaniu 1, ale

rozszerzoną w taki sposób, by możliwe było używanie tabeli przez

różnych pracowników (nie znających Excel’a) z możliwością

wprowadzenia do tabeli do 10 pozycji (wierszy).

Rozwiązanie.

Uwaga!

1. W powyższej tabeli dla ilustracji pokazano dodatkowo błędne

rozwiązanie, czyli kolumnę G i odpowiednią średnią!

2. Należy uświadomić sobie, że podczas pracy z Excel’em

występować możemy w podwójnej roli:

-

bezpośredniego użytkownika,

-

twórcy (projektanta) narzędzia dla rutynowo mających

pracować użytkowników.

W pierwszym przypadku bez kłopotu modyfikujemy rozwiązanie

problemu wg potrzeb.W drugim przypadku trzeba pamiętać, że

pracownik używa naszą aplikację biernie – należy przewidzieć

jakim zmianom może ona podlegać. Omawiany problem właśnie

wymaga poprawnej pracy przy zmiennej liczbie wpisanych

pracowników, czyli pewnej automatyzacji zadania. Tabela

- 141 -

Microsoft Excel 2000

wstępnie przygotowana do zmiennego rozmiaru (ograniczonego

rzecz jasna od góry przez projektanta – tu do 10 pozycji) powinna

cechować się następującymi własnościami:

a) automatycznie generować liczbę porządkową tylko wtedy, gdy

pozycję wypełnia się danymi,

b) kolumny Premia i Wypłata powinny być wypełnione

formułami w całym założonym zakresie, aby wpisanie danych

w kolumnach B-E powodowało automatyczne wypełnienie

całej tabeli,

c) wiersze, w których jeszcze nie wpisano danych pracowników,

powinny być widoczne jako puste mimo, że zawierają formuły,

d) komórki zawierające formuły powinny być zabezpieczone

przed zmianami tak, aby użytkownik przypadkowo nie

zniszczył formuł.

3. Spełnienie wymagań a), b) i c) sprowadza się do stosowania

formuł warunkowych, czyli opartych o funkcje logiczne takie jak

JEŻELI(...),

ORAZ(...),

LUB(...)

i

informacyjne

jak

CZY.PUSTA(...), CZY.LICZBA(...) i innych. Ich zastosowanie

pokazane jest na przykładzie liczby porządkowej i Wypłaty w

kolumnie H. Wypłata w kolumnie G liczona po prostu jako suma

Płacy i Premii nie spełnia wymagania c). W ostatecznej postaci

arkusza powinna być usunięta. Podobna formuła, jak dla Wypłaty

powinna być wprowadzona dla Premii.

Wiersz 3 powinien być ukryty, gdyż pełni tylko rolę pomocniczą

przy wypełnianiu liczby porządkowej.

4. Wymaganie d) spełnimy:

a) odblokowując zakres komórek, do których będą wprowadzane

dane przez zaznaczenie zakresu B3:E12, dalej wybranie

Format | Komórki | Ochrona i wyłączenie opcji Zablokuj,

b) wybierając Narzędzia | Ochrona | Chroń arkusz.

W rezultacie wszystkie komórki arkusza będą zabezpieczone

przed zmianami, z wyjątkiem odblokowanego zakresu B3:E12.

- 142 -

Microsoft Excel 2000

5. Niektóre funkcje, jak ŚREDNIA(...), MAX(...) charakteryzują sie

tym, że jeżeli w podanym zakresie komórek oprócz liczb są teksty

albo puste komórki, to funkcja je ignoruje, tzn. nie uwzględnia w

obliczeniach. Jest to korzystne, bo dane te nie „psują” np. wartości

średniej czy minimalnej. Niestety w formułach Excel traktuje

komórki puste jakby zawierały liczbę 0. W efekcie pola Premia i

Wypłata (kolumna G) nieużywanych wierszy będą miały wartość

0 zamiast być puste. Będą więc brać udział w obliczaniu średniej,

minimum itd., fałszując wyniki! Formuły w kolumnie H nie dość,

że spełniają wymaganie c) z punktu 1., to jeszcze zapewniają

poprawne wyliczenie średniej! (porównaj wyniki w E13 i E14).

Zadanie 17.

Rozszerzyć tabelę z poprzedniego zadania, aby obsługiwała do 1000

pracowników. Powinna być też zastosowana kontrola poprawności

wprowadzanych liczb pod względem mieszczenia się w podanym

zakresie. Błędnie wprowadzona liczba powinna spowodować

wyświetlenie w komórce komunikatu, np.”NIEPOPRAWNA

PREMIA”, „NIEPOPRAWNA PENSJA” czy „POPRAW!”.

Rozwiązanie.

- 143 -

Microsoft Excel 2000

Uwaga!

1. Duży rozmiar tablicy sugeruje, że nie jest najlepszą metodą

ciągnięcie myszą dla powielania formuł w żądanym obszarze!

Np. 1000-krotne powielenie formuły w komórkach od C15 do

C1014 (czyli 15+999!) najlepiej zrealizować wg następującego

algorytmu:

a) Zaznaczyć obszar korzystając z Edycja | Przejdź do (lub klawisza F5) i wpisać w oknie dialogowym zakres C15:C1014

b) Wpisać potrzebną formułę

c) Powielić ją na cały wybrany obszar parą klawiszy Ctrl+Enter.

2. Aby uzyskać uniwersalność, nie należy ustalać w formule

weryfikującej zakres stałych liczbowych granic, np. 300 do 3000

zł, ale umieścić je jako zmienne w wyróżnionych (opisem)

komórkach, żeby użytkownik zaczynając pracę sam wybrał

odpowiednie dla swojej grupy granice. Należy pamiętać, że

komórki można nazwać i posługiwać się wybraną nazwą np.

MAXP zamiast adresem takim jak H10.

3. Wprowadzana liczba musi być testowana przez jakąś formułę „na

poprawność”. W tym celu trzeba stworzyć dodatkową kolumnę

(osobno dla premii, osobno dla płacy), która zawiera formułę

testującą – wyrażenie warunkowe sprawdzające, czy jest to

rzeczywiście liczba i czy wpada w żądany zakres. W efekcie

powinna być w tej kolumnie wygenerowana liczba lub tekst,

np."NIE”. Po takich zabiegach kolumna Premia i Wypłata

powinny zawierać także warunkowe wyrażenia bazujące na

wynikach

tej

nowej

kolumny,

wg

szablonu:

JEŻELI(CZY.LICZBA( w dodatkowej kolumnie); obliczenie premii;”NIEPOPRAWNA PREMIA”).

Kolumny dodatkowe muszą być ukryte, gdyż z punktu widzenia

użytkownika są to „elementy techniczne” wspomagające stronę

projektową.

- 144 -