Excel F.Logiczne zad 9-17, Materiały uczelniane, UMK Toruń, Markeeting i Zarządzanie, Informatyka, ...
[ Pobierz całość w formacie PDF ]
Microsoft Excel 2000
Korzystanie z funkcji logicznych
cym na podejmowanie
„decyzji” w arkuszu kalkulacyjnym jest funkcja JE
Uniwersalnym mechanizmem pozwalaj
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ł
1 000,00 zł
20,00 zł
Stawka poni
ej
2%
6 000,00 zł
180,00 zł
Stawka powy
ej
3%
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
!"
#
$
%&
’ ()(’*+
#
’ ,
-& %./
)
"!"
#
$
%&
’
-
0
.1
+’
"2
- 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
cej wzrost
osób. Tablica ma dwie kolumny: WZROST (cm) oraz OCENA. W
pierwszej kolumnie wpisane zostan
odpowiednie wyra
enia logiczne do tablicy oceniaj
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 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 -
[ Pobierz całość w formacie PDF ]