Microsoft Excel 2000
Formuły z wartościami wybieranymi z innych tabel
Tworząc formuły obliczeniowe często musimy znalez
ć
potrzebną wartość w innej tabeli, na przykład chcąc przeliczyć
wartość transakcji złotówkowej na dolary, musimy znalez
ć w tabeli
kursów kurs złotówki w stosunku do dolara w dniu transakcji.
Najczęściej stosowanym narzędziem automatyzującym tego typu
operacje jest funkcja WYSZUKAJ.PIONOWO.
Funkcję tę można użyć w dwóch podstawowych wariantach.
Wariant pierwszy pozwala znalez
ć dla zadanej wielkości jej
odpowiednik we wskazanej kolumnie innej tabeli. W wariancie
drugim funkcja określa przedział, do którego ta wielkość należy i
znajduje odpowiednik tej grupy w innej tabeli. Schemat jej użycia jest
następujący:
WYSZUKAJ.PIONOWO(co; 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 Warto Warto
transakcji 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
znalez
ć 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ę wartość równa szukanej lub od
niej mniejsza. Ten wariant zastosowania funkcji wymaga, by 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ępują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ż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!
1. Należy zastosować funkcję WYSZUKAJ.PIONOWO do
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ą
kolumną  ale to kłóci się z oczekiwanym automatyzmem).
2. Funkcje INDEKS i PODAJ.POZYCJ
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ń ograniczymy się do wersji polsko-angielskiej
i angielsko-polskiej . Czytelnik łatwo rozszerzy arkusz dla pozostałych
przypadków.
Pierwszym krokiem powinno być utworzenie tabeli zawierającej
znaczenia słów w trzech wymienionych językach (tutaj słownik jest
bardzo ubogi, mieści się w zakresie D2:F5).
Przy wyszukiwaniu słów zwykła funkcja WYSZUKAJ.PIONOWO
nie wystarcza, gdyż np. dla słownika angielsko-polskiego, czy polsko-
- 149 -
 Microsoft Excel 2000
niemieckiego wyszukiwanie nie ma miejsca w pierwszej kolumnie
zakresu.
Potrzebna jest funkcja uniwersalnego wyszukiwania - można ją
zrealizować w oparciu o złożenie funkcji PODAJ.POZYCJ
i
INDEKS:
INDEKS(tablica; PODAJ.POZYCJ
(wartość; kolumna
_przeszukiwana; 0); nr_kol_wyniku )
Funkcja PODAJ.POZYCJ
po wyszukaniu wartości we wskazanej
kolumnie zwraca numer wiersza w tabeli słownika.  0 jako ostatni
parametr funkcji pełni podobną rolę, jak w funkcji
WYSZUKAJ.PIONOWO. (Ostatnim parametrem może być jeszcze -
1 lub 1; wyjaśnienie ich znaczenia znajdzie czytelnik w Pomocy).
Funkcja INDEKS  zna teraz numer wiersza i kolumny w tablicy
słownika i wybiera właściwe słowo.
Zadanie 23.
Dysponujemy listą towarów złożoną m.in. z kolumn
NAZWA_TOWARU i CENA. Żadna z tych kolumn nie jest pierwsza
w tej tabeli i nie wolno ich przestawiać. W dwóch komórkach
powyżej listy umieścić nazwy towarów: w pierwszej najtańszego a w
drugiej najdroższego. Oczywiście możliwe są zmiany na liście, czyli
wybór musi być dynamiczny (automatyczny). Ceny na liście mają być
różne, dzięki czemu unikniemy np. dylematu, którą z kilku równych
cen minimalnych wybrać. Należy zastosować omówione uniwersalne
wyszukiwanie i funkcje MIN() i MAX().
- 150 -
 Microsoft Excel 2000
Zdanie 24.
Uzupełnić poniższą tabelę (zastosowane progi są fikcyjne, chodzi tu o
wielokrotne zastosowanie WYSZUKAJ.PIONOWO do progresywnej
metody naliczania podatku).
Dochód Wpłacona Podatek Do Do zwrotu
zaliczka zapłaty
15.000 0
12.000 3.000
8.000 1.000
30.000 7.000
50.000 6.000
Dochody są opodatkowane wg następujących reguł:
Od Do Podatek
0 zł 10000 zł 0 zł (mogłoby tak być!)
10000 zł 20000 zł 20% kwoty powyżej 10000 zł
20000 zł 40000 zł 2000 zł oraz 30% kwoty powyżej
20000 zł
40000 zł 8000 zł oraz 40% kwoty powyżej
40000 zł
Uwaga!
Specyficznym momentem zadania jest wyszukanie dolnego progu dla
danego dochodu. Polega to na wyszukiwaniu w pierwszej kolumnie i
wyborze też z pierwszej kolumny tabeli.
- 151 -
 Microsoft Excel 2000
Zadanie 25.
Dana jest następująca tabela z danymi:
Baza danych o zawodnikach
Data
Nazwisko Imi W iek Płe Kategoria
urodzenia
Izbicki Tadeusz 01.02.89 m
Rudnik Bo ena 01.05.86 k
Rybczyk Marian 01.04.82 m
Kasprzyk Marek 01.01.80 m
Zemborak Barbara 01.06.78 k
Rybczyk Stefan 01.04.72 m
Grab Bo ena 01.02.70 k
Kasprzyk Anatol 01.07.62 m
Chudy Jan 01.03.58 m
Muszkat Anna 01.08.52 k
1. Obliczyć wiek zawodników (zaokrąglony do pełnych lat)
2. Określić Kategorię do której należy zawodnik zgodnie z zasadami:
wiek kategoria mężczyzn
<10 młodzik
10-19 junior
20 i > senior
wiek kategoria kobiet
<12 młodziczka
12-17 juniorka
18 i > seniorka
3. Dokonać sortowania danych w bazie według nazwiska i imienia.
Uwaga!
1. Skonstruować osobne tabelki dla kobiet i mężczyzn zawierające w
pierwszej kolumnie dolne granice przedziałów wiekowych., a w
drugiej nazwy kategorii.
- 152 -
 Microsoft Excel 2000
2. Zastosować funkcję JEŻELI dla zdecydowania, z której tabelki
mają być pobierane dane
Zadanie 26.
Wpisz do arkusza imiona siedmiu dziewcząt i siedmiu chłopców i
ponumeruj je.
Zabaw się w losowe łączenie ich w pary wykorzystując funkcje: LOS,
WYSZUKAJ.PIONOWO oraz operator łączenia łańcuchów
znakowych  & .
Rozwiązanie może wyglądać np. tak:
CHA
OPIEC DZIEWCZYNA PARA
6 2 Aleksandra Walerian
2 1 Alicja Marian
5 6 Joanna Andrzej
2 4 Jolanta Marian
7 4 Jolanta Zbigniew
4 1 Alicja Józef
3 2 Aleksandra Tadeusz
W kolumnach  Chłopiec i  Dziewczyna znajdują się ich
wylosowane numery, natomiast kolumna  Para zawiera formuły
wyszukiwania ich imion w tabeli i łączenia w jeden łańcuch wg
schematu:
WYSZUKAJ.PIONOWO(dziewczyna) & " " &
WYSZUKAJ.PIONOWO(chłopiec)
Uwaga!
Wciśnięcie klawisza F9 spowoduje ponowne przeliczenie arkusza i
utworzenie nowych par.
- 153 -
 Microsoft Excel 2000
Zadanie 27.
Poniższa tabela zawiera dane na temat rozmów telefonicznych kilku
abonentów z Torunia (Czas w kolumnie D jest wyliczony za pomocą
odpowiedniej formuły).
Twoim zadaniem jest wyliczenie należności za każdą rozmowę.
Opłata ma być wyliczana w oparciu o następujące stawki za minutę
rozmowy:
W okresach czasowych
Odległość 8:00-18:00 18:00-22:00 22:00-8:00
1. Miejscowe 30gr
2. I strefa 50gr 40gr 30gr
3. II strefa 80gr 60gr 40gr
- 154 -
 Microsoft Excel 2000
Rozmowę zalicza się do okresu czasowego na podstawie godziny jej
rozpoczęcia. Do wyliczonej opłaty należy dodać 22% VAT.
Miejscowości występujące w tabeli należą do następujących stref
(przepisz poniższą tabelkę bezpośrednio do arkusza):
Uwaga!
1. Czas rozmowy wylicz na podstawie różnicy komórek
zawierających jej koniec i początek Czas jest pamiętany jako
część ułamkowa wspólnej liczby zawierającej datę i czas i oznacza
ułamek doby. W związku z tym, aby otrzymać liczbę minut, trzeba
pomnożyć wspomnianą wyżej różnicę przez 1440.
2. Tabela opłat powinna być przetworzona do postaci możliwej do
przeszukiwania przez odpowiednie funkcje programu Excel. Jedna
z możliwych postaci jest następująca:
Dla znalezienia opłaty za minutę rozmowy w dwuwymiarowej
tablicy H26:J28 przydatna będzie funkcja INDEKS. Dla
ustalenia numeru wiersza (strefa) i kolumny (okres czasowy)
tabeli opłat należy zastosować funkcję WYSZUKAJ.
PIONOWO odpowiednio dla zakresów A25:B30 i D25:E28.
- 155 -
 Microsoft Excel 2000
Aby ustalić godzinę rozpoczęcia rozmowy należy użyć funkcji
GODZINA. Dla zwiększenia czytelności formuł można te
pomocnicze wartości umieścić w dodatkowych kolumnach (tu: K
i L).
Zadanie 29.
Zmodyfikuj arkusz z Zadania 28 tak, by uwzględnić obniżkę taryfy
w weekendy i święta. Obowiązujące opłaty podane są w tabeli:
W okresach czasowych
Odległość 8:00-18:00 18:00-22:00 22:00-8:00
1.Miejscowe 30gr
2. I strefa 40gr 30gr
3. II strefa 60gr 40gr
Uwaga!
Można wstawić obok daty rozpoczęcia rozmowy kolumnę
zawierającą literę "R" dla oznaczenia dni roboczych i "W" dla
oznaczenia dni wolnych.
- 156 -