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 -