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 sprzeda y 5 000,00 zł

Stawka poni ej 2%
1 000,00 zł 20,00 zł

Stawka powy ej 3%
6 000,00 zł 180,00 zł
3 800,00 zł 76,00 zł



7 200,00 zł 216,00 zł
Przykład 2.
-10 Liczba ujemna
15 Liczba dodatnia lub zero
6 Liczba dodatnia lub zero
-4 Liczba ujemna

0 Liczba dodatnia lub zero
- 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 MODUA
.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";"ŚRE-
DNI")). 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 FAA
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 FAA
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(A1JEŻELI(A1JEŻ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
ROZA

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(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.CAA
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 | Przejdz
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 -