Excel F.Wyszukaj zad 18-29, Materiały uczelniane, UMK Toruń, Markeeting i Zarządzanie, Informatyka, ...
[ Pobierz całość w formacie PDF ]
Microsoft Excel 2000
Formuły z warto
ciami wybieranymi z innych tabel
Tworz
c
formuły
obliczeniowe
cz
sto
musimy
znale
potrzebn
warto
w innej tabeli, na przykład chc
c przeliczy
warto
w tabeli
kursów kurs złotówki w stosunku do dolara w dniu transakcji.
Najcz
transakcji złotówkowej na dolary, musimy znale
ciej stosowanym narz
dziem automatyzuj
cym tego typu
operacje jest funkcja
WYSZUKAJ.PIONOWO
.
Funkcj
w dwóch podstawowych wariantach.
Wariant pierwszy
pozwala znale
t
mo
na u
y
ci jej
odpowiednik we wskazanej kolumnie innej tabeli. W
wariancie
drugim
funkcja okre
dla zadanej wielko
la przedział, do którego ta wielko
nale
y i
znajduje odpowiednik tej grupy w innej tabeli. Schemat jej u
ycia jest
nast
cy:
WYSZUKAJ.PIONOWO(
co
;
puj
gdzie
;
nr_kolumny_z_wynikiem
;
sposób _szukania
)
U
ycie wariantu pierwszego pokazuje poni
szy przykład,
gdzie zało
ono,
e tabela kursów zawiera codzienne kursy dolara.
Data
transakcji
Warto
Warto
w zł
w USD
T a be la kursów
98-12-05
1205,28
348,65
Data
Kurs USD Kurs PLN
98-12-01
3,445
0,290276
98-12-02
3,448
0,290023
98-12-03
3,449
0,289939
98-12-04
3,458
0,289184
98-12-05
3,457
0,289268
98-12-06
3,492
0,286369
98-12-07
3,495
0,286123
98-12-08
3,489
0,286615
Pierwszym argumentem funkcji, czyli wielko
ci
, dla której chcemy
znale
odpowiednik w innej tabeli, jest data transakcji. Drugim
argumentem jest zakres komórek zawieraj
cych szukane dane, czyli
Tabela kursów. Funkcja
WYSZUKAJ.PIONOWO
zawsze szuka w
- 145 -
Microsoft Excel 2000
pierwszej kolumnie podanego zakresu.
Warto
potrzebna w
formule znajduje si
w trzeciej kolumnie tabeli, dlatego trzecim
argumentem funkcji jest 3. Ostatni argument okre
la wariant uzycia
funkcji. Gdy jest on 0, jak w powy
szym przykładzie, to stosujemy
wariant pierwszy
, czyli szukana jest warto
dokładnie równa
pierwszemu argumentowi. Je
eli nie zostanie znaleziona, wynikiem
funkcji jest bł
d..
Nast
pny przykład obrazuje
drugi wariant
u
ycia funkcji
WYSZUKAJ.PIONOWO.
Mamy
dane
na
temat
liczby
zatrudnionych w firmach i chcemy okre
li
ich wielko
na podstawie
liczby pracowników przyjmuj
c nast
puj
ce kryteria:
poni
ej 10 pracowników
mała
10-99
rednia
100 i wi
cej
du
a
Liczba
zatrudnionych Wielko
Firma1
19
rednia
0 mała
Firma2
7 mała
10
rednia
Firma3
99
rednia
100 du
a
Firma4
101 du
a
Firma5
100 du
a
Firma6
3 mała
Firma7
8 mała
Tutaj ostatni (czwarty) parametr jest pomini
ty (mo
na te
wpisa
PRAWDA) i wynikiem funkcji jest warto
z tego wiersza, w którym
w pierwszej kolumnie znajduje si
równa
szukanej
lub
od
niej
mniejsza.
Ten wariant zastosowania funkcji wymaga, by warto
warto
ci
w pierwszej kolumnie były uporz
dkowane
rosn
co.
- 146 -
Microsoft Excel 2000
Uwaga!
Funkcja
WYSZUKAJ.PIONOWO(...)
ma
swój
„wierszowy”
odpowiednik
w
WYSZUKAJ.POZIOMO(...)
,
o
identycznej
funkcjonalno
ci.
Zadanie 18.
Zastosuj funkcj
WYSZUKAJ.PIONOWO
dla danych z
Przykładu
1
w sytuacji, gdy w tabeli kursów znajduj
si
dane tylko dla dni, w
których kurs uległ zmianie.
Zadanie 19.
Uzupełni
tabel
utworzon
w
Zadaniu 6
(i rozszerzon
w
Zadaniu
9)
o
dodatkow
zni
k
lub
podwy
k
składki
podstawowej
uzale
nion
od bezszkodowej jazdy w poprzednich latach, przyjmuj
c
nast
ce zasady:
a) Gdy szkoda miała miejsce w ostatnim roku (0 lat bez szkód),
składka podstawowa jest podniesiona o 10%
b) Dla 1 roku bez szkód składka pozostaje bez zmian
c) Dla dwóch lat bez szkód zni
puj
ka wynosi 10% składki podstawowej
i dalej wzrasta o 10% rocznie, a
do 50% dla sze
ciu i wi
cej lat.
Kolejni ubezpieczeni w tabeli wykazali si
kolejno 0, 3, 2,1 ,7 ,6 ,0, 8
latami bezszkodowej jazdy.
Zadanie 20.
Arkusz ma realizowa
paragon sklepu spo
ywczego.
Paragon posiada kolumny:
NR_TOWARU ILO
NAZWA CENA WARTO
- 147 -
Microsoft Excel 2000
Przewidziano 20 pozycji na li
cie zakupów. Poni
ej powinna by
podana suma całkowita.
NR_TOWARU to kod wpisywany przez sprzedawc
(obecnie jest to
cz
sto kod kreskowy odczytywany czytnikiem z opakowania).
Sprzedawca wpisuje te
liczb
w pole ILO
w jednostkach miary
wła
ciwych dla danego asortymentu.
Pole NAZWA i CENA maj
by
wypełniane automatycznie na
podstawie bazy danych opisuj
cej towary. W najprostszej postaci
b
dzie to tabela o takich samych lub podobnych nazwach pól:
NR_TOWARU NAZWA CENA.
Uwaga!
Nale
y
zastosowa
funkcj
WYSZUKAJ.PIONOWO
do
1.
pobrania warto
ci z tabeli dla kolumn paragonu NAZWA i CENA.
Kolumn
indeksuj
c
baz
jest tu NR_TOWARU wg którego
wybieramy pozycj
z tablicy – funkcja
WYSZUKAJ.PIONOWO
wymaga, by była ona umieszczona jako pierwsza. Jest to wada,
gdy
chc
c na podstawie NAZWY uzyska
NUMER nie mo
emy
skorzysta
z
tej
wygodnej
funkcji
(chyba,
e
przedtem
przestawimy
kolumny
w tabeli
czyni
c NAZW
pierwsz
z oczekiwanym automatyzmem).
2. Funkcje
INDEKS
i
PODAJ.POZYCJ
kolumn
– ale to kłóci si
pozwalaj
na utworzenie
wyra
enia, które usuwa wspomnian
wad
– niestety kosztem
komplikacji wyra
enia (u
ywamy superpozycji dwóch funkcji),
patrz
Zadania 22 i 23.
Zadanie 21.
Na podstawie
redniej z ocen semestru student ma otrzyma
wpis do
indeksu. Zakładamy,
e w indeksie maj
by
tylko pełne oceny.
Nale
y wi
c okre
li
kryteria, czyli przedziały wg których b
dzie si
wybiera
stopie
ko
cowy.
Przyjmijmy,
e
- 148 -
Microsoft Excel 2000
gdy
rednia <2,5 to ocena w indeksie b
dzie 2
gdy
rednia < 3,6 to 3
gdy
rednia < 4,4 to 4
gdy
rednia >= 4,4 to 5
Utworzy
dziennik dla grupy studentów, który wykładowcy ułatwi
prac
i wygeneruje na podstawie stopni semestru ocen
ko
cow
dla
ka
dej osoby.
Zadanie 22.
Zbuduj arkusz pełni
cy rol
słownika polsko-niemiecko-angielskiego.
U
ytkownik b
dzie wpisywał słowo w jednym j
zyku i ma otrzyma
jego odpowiednik w drugim j
zyku.
Rozwi
zanie
W znajdowaniu znacze
do wersji polsko-angielskiej
i angielsko-polskiej
.
Czytelnik łatwo rozszerzy arkusz dla pozostałych
przypadków.
Pierwszym krokiem powinno by
ograniczymy si
utworzenie tabeli zawieraj
cej
znaczenia słów w trzech wymienionych j
zykach (tutaj słownik jest
bardzo ubogi, mie
w zakresie D2:F5).
Przy wyszukiwaniu słów zwykła funkcja
WYSZUKAJ.PIONOWO
nie wystarcza, gdy
ci si
np. dla słownika angielsko-polskiego, czy polsko-
- 149 -
[ Pobierz całość w formacie PDF ]