Idź do
• Spis treści
• Przykładowy rozdział
• Katalog online
• Dodaj do koszyka
• Zamów cennik
• Zamów informacje
o nowościach
• Fragmenty książek
online
Helion SA
ul. Kościuszki 1c
44-100 Gliwice
tel. 32 230 98 63
e-mail: helion@helion.pl
© Helion 1991–2010
Katalog książek
Twój koszyk
Cennik i informacje
Czytelnia
Kontakt
• Zamów drukowany
katalog
Excel 2010 PL.
Formuły
Autor:
John Walkenbach
Tłumaczenie: Łukasz Piwko
ISBN: 978-83-246-2883-4
Tytuł oryginału:
Excel 2010 Formulas
(Mr. Spreadsheet's Bookshelf)
Format: 172×245, stron: 880
Wykorzystaj wszystkie możliwości drzemiące w formułach!
• Jak tworzyć formuły finansowe, tablicowe i tekstowe?
• Jak napisać własne funkcje arkusza w języku VBA?
• Jak za pomocą formuł tworzyć wykresy i tabele przestawne?
Jak potężnym i przydatnym narzędziem jest Excel, przekonują się nawet jego najzagorzalsi
przeciwnicy. Czy się ten program Microsoftu lubi, czy też nie, nie sposób kwestionować jego
możliwości, nieporównywalnych z innymi arkuszami kalkulacyjnymi. I choć dzięki temu Excel
w wielu zastosowaniach okazuje się niezastąpiony, wciąż znajdują się tacy, którzy boją się jego
obsługi i nie wychodzą poza kilka standardowych funkcji. W końcu nawet biegli użytkownicy
Excela bywają onieśmieleni jego imponującą funkcjonalnością. Jeśli zatem i Tobie wydawało się,
że doskonale znasz ten program, i nagle odkryłeś, jak wiele z jego potencjału wciąż jest poza
zasięgiem Twoich umiejętności, oto idealny podręcznik dla Ciebie! Książka napisana przez Johna
Walkenbacha, absolutnego guru Excela, dostarczy Ci wiedzy, która raz na zawsze odmieni Twoją
pracę w tym programie.
Najpierw przeczytasz, czym dokładnie jest formuła, jak ją stworzyć oraz edytować w Excelu 2010
i do czego można ją wykorzystywać. Dowiesz się wszystkiego na temat nazw, ich zakresów
i zarządzania nimi. W kolejnych częściach podręcznika zobaczysz, jak używać funkcji arkuszy
w formułach, przeczytasz o manipulowaniu tekstem, datach i godzinach oraz różnych technikach
liczenia. Odkryjesz, jak tworzyć bardzo przydatne formuły tekstowe, finansowe i tablicowe, oraz
przekonasz się, jak przydatne są one w pracy z wykresami i tabelami przestawnymi. Znajdziesz
tu również wiele interesujących, praktycznych formuł, których można używać w połączeniu
z funkcjami formatowania warunkowego i sprawdzania poprawności danych. A kiedy już
opanujesz te zagadnienia, dotrzesz do „megaformuł” i wykorzystywania języka VBA do tworzenia
własnych funkcji arkusza.
Dzięki tej książce:
• poznasz interfejs i nowe funkcje Excela 2010
• nauczysz się pracować z nazwami arkuszy, komórek i zakresów
• zaczniesz tworzyć przydatne formuły finansowe, tablicowe i tekstowe
• poznasz podstawowe i zaawansowane formuły liczące
• opanujesz formuły wyszukiwania oraz funkcje czasu i daty
• nauczysz się pracować z tabelami i bazami danych arkusza
• dowiesz się, czym są odwołania cykliczne i jak z nich korzystać
• poznasz techniki tworzenia wykresów i tabel przestawnych
• zaczniesz korzystać z formuł formatowania warunkowego
• opanujesz tworzenie i stosowanie „megaformuł”
• napiszesz własne, przydatne funkcje w języku VBA
• bez trudu usuniesz błędy w formułach
Chcesz, by Excel pracował za Ciebie? John Walkenbach pokaże Ci, jak to zrobić!
Spis tre&ci
Wst p
23
Co trzeba wiedzie#
23
Co trzeba mie#
23
Konwencje typograficzne
24
Konwencje dotycz$ce klawiatury
24
Konwencje myszy
25
Co oznaczaj$ ikony
26
Struktura ksi$&ki
26
Cz '# I: Informacje podstawowe
27
Cz '# II: Stosowanie funkcji w formu(ach
27
Cz '# III: Formu(y finansowe
27
Cz '# IV: Formu(y tablicowe
27
Cz '# V: Ró&ne techniki wykorzystania formu(
27
Cz '# VI: Tworzenie niestandardowych funkcji arkusza
28
Dodatki
28
Jak korzysta# z ksi$&ki?
28
Informacje o przyk(adach
29
Informacje o dodatku Power Utility Pak
29
Komentarze
29
Cz)&* I Informacje podstawowe
31
Rozdzia% 1. Wst+p do Excela
33
Historia Excela
34
Wszystko zacz (o si od programu VisiCalc
34
Nadej'cie Lotusa
34
Do gry wchodzi Microsoft
35
Wersje Excela
35
Koncepcja modelu obiektowego
39
Zasada dzia(ania skoroszytów
40
Arkusze
41
Arkusze wykresów
42
Arkusze makr i arkusze dialogowe
42
Interfejs u&ytkownika programu Excel
43
Nowy interfejs u&ytkownika
43
Wst$&ka
43
Widok Backstage
46
Menu podr czne i minipasek narz dzi
47
Konfigurowanie interfejsu u&ytkownika
47
Tagi inteligentne
48
Okienko zada+
49
Przeci$gnij i upu'#
50
Skróty klawiaturowe
50
Dostosowywanie widoku na ekranie
51
8
Spis tre'ci
Wprowadzanie danych
51
Zaznaczanie obiektów i komórek
53
Pomoc programu Excel
53
Formatowanie komórek
54
Formatowanie numeryczne
54
Formatowanie stylistyczne
55
Tabele
55
Formu(y i funkcje arkuszy
56
Obiekty na warstwie rysowania
56
Kszta(ty
57
Ilustracje
57
Obiekty obrazów po($czonych
57
Formanty
58
Wykresy
58
Wykresy przebiegu w czasie
59
Dostosowywanie Excela
60
Makra
60
Dodatki
60
Funkcje internetowe
60
Narz dzia do analizy
61
Dost p do baz danych
61
Konspekty
62
Zarz$dzanie scenariuszami
63
Tabele przestawne
63
Funkcje inspekcji
64
Dodatek Solver
64
Opcje ochrony
64
Ochrona formu( przed nadpisaniem
64
Chronienie struktury skoroszytu
65
Ochrona skoroszytu has(em
66
Rozdzia% 2. Podstawowe informacje na temat formu%
67
Wprowadzanie i edycja formu(
68
Elementy formu(y
68
Wstawianie formu(y
68
Wklejanie nazw
70
Spacje i z(amania wiersza
70
Ograniczenia formu(
71
Przyk(ady formu(
71
Edytowanie formu(
72
Operatory u&ywane w formu(ach
73
Operatory odniesienia
74
Przyk(ady formu( z u&yciem operatorów
74
Pierwsze+stwo operatorów
75
Zagnie&d&anie nawiasów
77
Obliczanie warto'ci formu(
79
Odniesienia do komórek i zakresów
80
Tworzenie odwo(a+ bezwzgl dnych i mieszanych
80
Tworzenie odwo(a+ do innych arkuszy lub skoroszytów
83
Robienie wiernej kopii formu(y
83
Konwertowanie formu( na warto'ci
84
Ukrywanie formu(
87
9
Spis tre'ci
B( dy w formu(ach
88
Co robi# z odwo(aniami cyklicznymi
89
Szukanie wyniku
91
Przyk(ad szukania wyniku
91
Szukanie wyniku — informacje dodatkowe
93
Rozdzia% 3. Praca z nazwami
95
Co to jest nazwa
96
Zakres nazw
97
Odwo(ania do nazw
97
Odnoszenie si do nazw z innego skoroszytu
98
Konflikty nazw
98
Mened&er nazw
99
Tworzenie nazw
100
Edytowanie nazw
100
Usuwanie nazw
101
Szybkie tworzenie nazw komórek i zakresów
101
Okno dialogowe Nowa nazwa
101
Tworzenie nazw przy u&yciu pola nazwy
103
Automatyczne tworzenie nazw
104
Nazywanie ca(ych wierszy i kolumn
106
Nazwy tworzone przez Excela
106
Tworzenie nazw obejmuj$cych kilka arkuszy
107
Praca z nazwami komórek i zakresów
109
Tworzenie listy nazw
109
U&ywanie nazw w formu(ach
110
U&ywanie operatora przeci cia z nazwami
111
U&ywanie operatora zakresu z nazwami
113
Odwo(ywanie si do pojedynczej komórki w zakresie nazwanym obejmuj$cym kilka arkuszy
113
Wstawianie nazw do istniej$cych formu(
114
Automatyczne wstawianie nazw podczas tworzenia formu(y
115
Usuwanie nazw
115
Nazwy z b( dami
115
Przegl$danie nazw zakresów
116
Stosowanie nazw w wykresach
117
Obs(uga nazw komórek i zakresów przez Excela
117
Wstawianie wiersza lub kolumny
117
Usuwanie wiersza lub kolumny
117
Wycinanie i wklejanie
118
Potencjalne problemy z nazwami
118
Problemy wyst puj$ce podczas kopiowania arkuszy
118
Problemy z nazwami przy usuwaniu arkuszy
120
Klucz do zrozumienia nazw
121
Nazywanie warto'ci sta(ych
122
Nazywanie sta(ych tekstowych
123
U&ywanie funkcji arkusza w nazwanych formu(ach
124
U&ywanie odwo(a+ do komórek i zakresów w formu(ach nazwanych
125
U&ywanie formu( nazwanych zawieraj$cych odwo(ania wzgl dne
126
Zaawansowane techniki u&ywania nazw
130
U&ywanie funkcji ADR.PO-R z zakresem nazwanym
130
U&ycie funkcji ADR.PO-R do tworzenia zakresu nazwanego o sta(ym adresie
131
U&ywanie tablic w formu(ach nazwanych
131
Tworzenie dynamicznych formu( nazwanych
133
10
Spis tre'ci
Cz)&* II Stosowanie funkcji w formu5ach
135
Rozdzia% 4. Wprowadzenie do funkcji arkusza
137
Co to jest funkcja
137
Upraszczanie formu(
138
Wykonywanie oblicze+ niemo&liwych do wykonania w inny sposób
138
Przyspieszanie zada+ edycyjnych
139
Podejmowanie decyzji przez formu(
139
Wi cej na temat funkcji
140
Typy argumentów funkcji
140
Nazwy w roli argumentów
141
Ca(e kolumny i wiersze w roli argumentów
142
Warto'ci literalne w roli argumentów
142
Wyra&enia w roli argumentów
143
Funkcje w roli argumentów
143
Tablice w roli argumentów
143
Sposoby wstawiania funkcji do formu(
144
R czne wpisywanie funkcji
144
Biblioteka funkcji
145
Okno dialogowe Wstawianie funkcji
146
Dodatkowe wskazówki na temat wstawiania funkcji
148
Kategorie funkcji
150
Funkcje finansowe
150
Funkcje daty i godziny
150
Funkcje matematyczne i trygonometryczne
151
Funkcje statystyczne
151
Funkcje wyszukiwania i odwo(a+
151
Funkcje baz danych
151
Funkcje tekstowe
151
Funkcje logiczne
152
Funkcje informacyjne
152
Funkcje zdefiniowane przez u&ytkownika
152
Funkcje in&ynierskie
152
Funkcje modu(owe
152
Funkcje zgodno'ci
152
Inne kategorie funkcji
153
Rozdzia% 5. Manipulowanie tekstem
155
Kilka s(ów na temat tekstu
155
Ile znaków mo&e pomie'ci# jedna komórka
156
Liczby jako tekst
156
Funkcje tekstowe
157
Sprawdzanie, czy komórka zawiera tekst
158
Praca z kodami znaków
158
Sprawdzanie, czy dwa ci$gi s$ identyczne
161
5$czenie dwóch lub wi kszej liczby komórek
161
Wy'wietlanie sformatowanych warto'ci jako tekst
162
Wy'wietlanie warto'ci walutowych jako tekst
164
Usuwanie niepotrzebnych spacji i niedrukowalnych znaków
164
Liczenie znaków w ci$gu
164
Powtarzanie znaku lub ci$gu
165
Tworzenie histogramu tekstowego
165
Dope(nianie liczby
166
11
Spis tre'ci
Zmiana wielko'ci liter
168
Wydobywanie znaków z ci$gu
169
Podmienianie tekstu innym tekstem
169
Znajdowanie i szukanie w ci$gu
170
Znajdowanie i zamienianie ci$gów
171
Zaawansowane formu(y tekstowe
171
Zliczanie okre'lonych znaków w komórce
172
Zliczanie wyst$pie+ podci$gu w komórce
172
Usuwanie znaków minusa z ko+ca
172
Sprawdzanie numeru litery kolumny po jej numerze
173
Wydobywanie nazwy pliku ze 'cie&ki
173
Wydobywanie pierwszego wyrazu z ci$gu
173
Wydobywanie ostatniego wyrazu z ci$gu
174
Wydobywanie wszystkiego poza pierwszym wyrazem w ci$gu
174
Wydobywanie pierwszych imion, drugich imion i nazwisk
175
Usuwanie tytu(u sprzed imienia lub nazwiska
177
Zliczanie s(ów w komórce
177
Rozdzia% 6. Funkcje daty i czasu
179
Jak Excel obs(uguje daty i godziny
179
Liczby seryjne dat
180
Wprowadzanie dat
181
Liczby seryjne godzin i minut
183
Wprowadzanie godzin
183
Formatowanie dat i godzin
185
Problemy z datami
186
Funkcje daty
188
Wy'wietlanie aktualnej daty
188
Wy'wietlanie dowolnej daty
190
Generowanie serii dat
191
Konwersja ci$gów tekstowych na daty
192
Obliczanie liczby dni dziel$cych dwie daty
193
Obliczanie liczby dni powszednich mi dzy dwiema datami
193
Obliczanie daty, bior$c pod uwag tylko dni robocze
195
Obliczanie liczby lat dziel$cych dwie daty
195
Obliczanie wieku osób
197
Okre'lanie dnia roku
197
Okre'lanie dnia tygodnia
198
Okre'lanie daty ostatniej niedzieli
198
Okre'lanie daty pierwszego wyst$pienia dnia tygodnia po okre'lonej dacie
198
Okre'lanie n-tego wyst$pienia dnia tygodnia w miesi$cu
199
Zliczanie wyst$pie+ dnia tygodnia
199
Obliczanie dat 'wi$t
201
Okre'lanie daty ostatniego dnia miesi$ca
203
Sprawdzanie, czy dany rok jest przest pny
204
Sprawdzanie kwarta(u roku
204
Konwersja roku na liczby rzymskie
204
Funkcje czasu
205
Wy'wietlanie bie&$cego czasu
205
Wy'wietlanie dowolnego czasu
206
Sumowanie czasów powy&ej 24 godzin
207
Obliczanie ró&nicy mi dzy dwiema warto'ciami czasu
209
Konwersja z czasu wojskowego
211
Konwersja godzin, minut i sekund w zapisie dziesi tnym na warto'ci czasu
211
12
Spis tre'ci
Dodawanie godzin, minut i sekund do warto'ci czasu
212
Konwersja pomi dzy strefami czasowymi
213
Zaokr$glanie warto'ci czasu
214
Praca z warto'ciami nieb d$cymi godzinami dnia
214
Rozdzia% 7. Techniki liczenia i sumowania
217
Liczenie i sumowanie komórek
218
Zliczanie i sumowanie rekordów w bazach danych i tabelach przestawnych
220
Podstawowe formu(y licz$ce
221
Obliczanie sumy komórek
222
Zliczanie pustych komórek
222
Zliczanie niepustych komórek
223
Zliczanie komórek z liczbami
223
Zliczanie komórek niezawieraj$cych tekstu
223
Zliczanie komórek tekstowych
224
Zliczanie warto'ci logicznych
224
Zliczanie warto'ci b( dów w zakresie
224
Zaawansowane formu(y licz$ce
225
Liczenie komórek przy u&yciu funkcji LICZ.JE<ELI
225
Zliczanie komórek spe(niaj$cych wiele kryteriów
225
Zliczanie liczby wyst$pie+ najcz 'ciej pojawiaj$cego si wpisu
229
Zliczanie wyst$pie+ okre'lonego tekstu
231
Liczenie unikatowych warto'ci
233
Tworzenie rozk(adu cz sto'ci
234
Formu(y sumuj$ce
240
Sumowanie wszystkich komórek w zakresie
240
Obliczanie narastaj$cej sumy
242
Sumowanie okre'lonej liczby najwi kszych warto'ci
243
Sumowanie warunkowe z jednym kryterium
244
Sumowanie tylko warto'ci ujemnych
245
Sumowanie warto'ci w oparciu o inny zakres
245
Sumowanie warto'ci w oparciu o porównanie tekstowe
246
Sumowanie warto'ci w oparciu o porównanie daty
246
Sumowanie warunkowe przy zastosowaniu wielu kryteriów
247
U&ycie kryteriów „i”
248
U&ycie kryteriów „lub”
249
U&ycie kryteriów „i” oraz „lub”
249
Rozdzia% 8. Funkcje wyszukiwania
251
Co to jest formu(a wyszukiwania
251
Funkcje zwi$zane z wyszukiwaniem
253
Podstawowe formu(y wyszukiwania
253
Funkcja WYSZUKAJ.PIONOWO
254
Funkcja WYSZUKAJ.POZIOMO
255
Funkcja WYSZUKAJ
256
5$czne u&ycie funkcji PODAJ.POZYCJ@ i INDEKS
258
Wyspecjalizowane formu(y wyszukuj$ce
259
Wyszukiwanie dok(adnej warto'ci
260
Wyszukiwanie warto'ci w lew$ stron
262
Wyszukiwanie z rozró&nianiem ma(ych i wielkich liter
263
Wybieranie spo'ród wielu tabel
263
Okre'lanie ocen na podstawie wyników testu
264
Obliczanie 'redniej ocen
265
Wyszukiwanie w dwie strony
266
13
Spis tre'ci
Wyszukiwanie dwukolumnowe
268
Sprawdzanie adresu warto'ci w zakresie
269
Wyszukiwanie warto'ci przy u&yciu najbli&szego dopasowania
270
Wyszukiwanie warto'ci przy u&yciu interpolacji liniowej
271
Rozdzia% 9. Tabele i bazy danych arkusza
275
Tabele i terminologia
276
Przyk(ad bazy danych arkusza
276
Przyk(ad tabeli
277
Zastosowania baz danych arkusza i tabel
278
Praca z tabelami
279
Tworzenie tabeli
281
Zmiana wygl$du tabeli
282
Nawigacja i zaznaczanie w tabeli
283
Dodawanie wierszy lub kolumn
283
Usuwanie wierszy lub kolumn
284
Przenoszenie tabeli
285
Ustawianie opcji stylu tabeli
285
Usuwanie powtarzaj$cych si wierszy z tabeli
287
Sortowanie i filtrowanie tabeli
288
Praca z wierszem sumy
292
Stosowanie formu( w tabelach
295
Odwo(ywanie si do danych w tabeli
297
Konwersja tabeli na baz danych arkusza
301
Filtrowanie zaawansowane
302
Ustawianie zakresu kryteriów
303
Stosowanie filtru zaawansowanego
304
Usuwanie filtru zaawansowanego
305
Okre'lanie kryteriów filtru zaawansowanego
306
Okre'lanie pojedynczego kryterium
306
Okre'lanie wielu kryteriów
309
Okre'lanie kryteriów utworzonych w wyniku u&ycia formu(y
312
Funkcje bazy danych
313
Wstawianie sum cz 'ciowych
315
Rozdzia% 10. RóDne obliczenia
319
Konwersja jednostek
319
Rozwi$zywanie trójk$tów prostok$tnych
321
Obliczanie pola powierzchni, obwodu i obj to'ci
324
Obliczanie pola powierzchni i obwodu kwadratu
324
Obliczanie pola powierzchni i obwodu prostok$ta
324
Obliczanie pola powierzchni i obwodu ko(a
325
Obliczanie pola powierzchni trapezu
325
Obliczanie pola powierzchni trójk$ta
325
Obliczanie pola powierzchni i obj to'ci kuli
325
Obliczanie pola powierzchni i obj to'ci sze'cianu
326
Obliczanie pola powierzchni i obj to'ci sto&ka
326
Obliczanie obj to'ci walca
326
Obliczanie obj to'ci ostros(upa
327
Rozwi$zywanie uk(adów równa+
327
Zaokr$glanie liczb
328
Podstawowe formu(y zaokr$glaj$ce
329
Zaokr$glanie do najbli&szej wielokrotno'ci
330
Zaokr$glanie warto'ci walutowych
330
14
Spis tre'ci
Praca z u(amkami dolarów
331
Stosowanie funkcji ZAOKR.DO.CA5K i LICZBA.CA5K
332
Zaokr$glanie do parzystej lub nieparzystej liczby ca(kowitej
333
Zaokr$glanie do
n cyfr znacz$cych
333
Cz)&* III Formu5y finansowe
335
Rozdzia% 11. Formu%y kredytów i inwestycji
337
Poj cia finansowe
338
Warto'# pieni$dza w czasie
338
Wp(ywy i p(atno'ci
338
Dopasowywanie okresów czasu
339
Wyznaczanie terminu pierwszej p(atno'ci
339
Podstawowe funkcje finansowe
340
Obliczanie warto'ci bie&$cej
340
Obliczanie przysz(ej warto'ci
344
Obliczanie p(atno'ci
347
Obliczanie stóp
349
Obliczanie liczby rat
351
Obliczanie sk(adników p(atno'ci
353
Funkcje IPMT i PPMT
353
Funkcje CUMIPMT i CUMPRINC
355
Konwersja stóp procentowych
356
Metody przedstawiania stóp procentowych
356
Formu(y konwersji
356
Ograniczenia funkcji finansowych
357
Odroczony pocz$tek serii regularnych p(atno'ci
358
Szacowanie serii zmiennych p(atno'ci
359
Obliczenia dotycz$ce obligacji
360
Wyznaczanie ceny obligacji
360
Obliczanie rentowno'ci
362
Rozdzia% 12. Formu%y dyskontowe i amortyzacji
363
Funkcja NPV
364
Definicja funkcji NPV
364
Przyk(ady u&ycia funkcji NPV
366
Obliczanie kwot nagromadzonych za pomoc$ funkcji NPV
372
Funkcja IRR — stosowanie
374
Stopa zwrotu
375
Geometryczne wskaQniki przyrostu
376
Sprawdzanie wyników
377
Kilka stóp funkcji IRR i MIRR
378
Kilka wewn trznych stóp zwrotu
378
Rozdzielanie przep(ywów
380
U&ycie sald zamiast przep(ywów
381
Nieregularne przep(ywy 'rodków
382
Warto'# bie&$ca netto
382
Wewn trzna stopa zwrotu
383
Funkcja FVSCHEDULE
384
Obliczanie zwrotu w skali roku
384
Obliczanie amortyzacji
385
15
Spis tre'ci
Rozdzia% 13. Harmonogramy finansowe
389
Tworzenie harmonogramów finansowych
389
Tworzenie harmonogramów amortyzacji
390
Prosty harmonogram amortyzacji
390
Dynamiczny harmonogram amortyzacji
393
Tabele p(atno'ci i odsetek
397
Obliczenia dotycz$ce karty kredytowej
399
Zestawianie opcji po&yczek w tabelach danych
400
Tworzenie tabeli danych z jedn$ zmienn$
401
Tworzenie tabeli danych z dwiema zmiennymi
403
Sprawozdania finansowe
405
Podstawowe sprawozdania finansowe
405
Analiza wskaQników
409
Tworzenie indeksów
412
Cz)&* IV Formu5y tablicowe
415
Rozdzia% 14. Wprowadzenie do tablic
417
Wprowadzenie do formu( tablicowych
417
Wielokomórkowa formu(a tablicowa
418
Jednokomórkowa formu(a tablicowa
419
Tworzenie sta(ej tablicowej
420
Elementy sta(ej tablicowej
421
Wymiary tablicy — informacje
422
Jednowymiarowe tablice poziome
422
Jednowymiarowe tablice pionowe
422
Tablice dwuwymiarowe
423
Nadawanie nazw sta(ym tablicowym
424
Praca z formu(ami tablicowymi
425
Wprowadzanie formu(y tablicowej
426
Zaznaczanie zakresu formu(y tablicowej
426
Edycja formu(y tablicowej
426
Powi kszanie i zmniejszanie wielokomórkowych formu( tablicowych
428
Stosowanie wielokomórkowych formu( tablicowych
429
Tworzenie tablicy z warto'ci w zakresie
429
Tworzenie sta(ej tablicowej z warto'ci w zakresie
429
Wykonywanie dzia(a+ na tablicach
430
U&ywanie funkcji z tablicami
431
Transponowanie tablicy
432
Generowanie tablicy kolejnych liczb ca(kowitych
433
Jednokomórkowe formu(y tablicowe
434
Liczenie znaków w zakresie
434
Sumowanie trzech najmniejszych warto'ci w zakresie
435
Zliczanie komórek tekstowych w zakresie
436
Pozbywanie si formu( po'rednich
438
Zastosowanie tablicy zamiast adresu zakresu
440
Rozdzia% 15. Magia formu% tablicowych
441
Stosowanie jednokomórkowych formu( tablicowych
441
Sumowanie zakresu zawieraj$cego b( dy
442
Zliczanie b( dów warto'ci w zakresie komórek
443
Sumowanie
n najwi kszych warto'ci w zakresie
444
Obliczanie 'redniej z pomini ciem zer
444
16
Spis tre'ci
Sprawdzanie wyst powania okre'lonej warto'ci w zakresie
446
Zliczanie liczby ró&nic w dwóch zakresach
447
Zwracanie lokalizacji maksymalnej warto'ci w zakresie
448
Odszukiwanie wiersza
n-tego wyst$pienia warto'ci w zakresie
448
Zwracanie najd(u&szego tekstu w zakresie
449
Sprawdzanie, czy zakres zawiera poprawne warto'ci
449
Sumowanie cyfr liczby ca(kowitej
450
Sumowanie warto'ci zaokr$glonych
451
Sumowanie wszystkich
n-tych warto'ci w zakresie
452
Usuwanie nienumerycznych znaków z (a+cucha
453
Odszukiwanie najbli&szej warto'ci w zakresie
454
Zwracanie ostatniej warto'ci w kolumnie
455
Zwracanie ostatniej warto'ci w wierszu
456
Szeregowanie danych przy u&yciu formu(y tablicowej
456
Stosowanie wielokomórkowych formu( tablicowych
457
Zwracanie wy($cznie dodatnich warto'ci w zakresie
458
Zwracanie niepustych komórek z zakresu
459
Odwracanie kolejno'ci komórek w zakresie
459
Dynamiczne sortowanie warto'ci w zakresie
460
Zwracanie listy unikalnych elementów zakresu
461
Wy'wietlanie kalendarza w zakresie komórek
462
Cz)&* V Ró@ne techniki wykorzystania formu5
465
Rozdzia% 16. Zamierzone odwo%ania cykliczne
467
Czym s$ odwo(ania cykliczne?
467
Korygowanie niezamierzonych odwo(a+ cyklicznych
468
Istota po'rednich odwo(a+ cyklicznych
469
Zamierzone odwo(ania cykliczne
470
W jaki sposób Excel okre'la ustawienia oblicze+ i iteracji
473
Przyk(ady odwo(a+ cyklicznych
474
Generowanie losowych, unikalnych liczb ca(kowitych
474
Rozwi$zywanie równa+ rekursywnych
475
Rozwi$zywanie uk(adów równa+ przy u&yciu odwo(a+ cyklicznych
477
Animowanie wykresów przy u&yciu iteracji
479
Potencjalne problemy z zamierzonymi odwo(aniami cyklicznymi
480
Rozdzia% 17. Techniki tworzenia wykresów
481
Dzia(anie formu(y SERIE
482
U&ywanie nazw w formule SERIE
484
Oddzielanie serii danych na wykresie od zakresu danych
484
Tworzenie powi$za+ do komórek
487
Tworzenie po($czenia z tytu(em wykresu
487
Tworzenie powi$za+ z tytu(ami osi
488
Tworzenie powi$za+ z etykietami danych
488
Tworzenie powi$za+ tekstowych
488
Dodawanie obrazu po($czonego do wykresu
489
Przyk(ady wykresów
489
Wykres post pów w d$&eniu do celu
489
Tworzenie wykresu w kszta(cie miernika
491
Warunkowe wy'wietlanie kolorów na wykresie kolumnowym
492
Tworzenie histogramu porównawczego
493
Tworzenie wykresów Gantta
495
17
Spis tre'ci
Tworzenie wykresu gie(dowego
497
Kre'lenie co
n-tego punktu danych
499
Kre'lenie
n ostatnich punktów danych
501
Zaznaczanie serii danych za pomoc$ okna kombi
502
Tworzenie wykresów funkcji matematycznych
504
Kre'lenie okr gu
508
Wykres w kszta(cie zegara
510
Tworzenie wspania(ych wykresów
512
Tworzenie wykresów linii trendów
513
Liniowe wykresy trendów
514
Nieliniowe wykresy trendu
518
Rozdzia% 18. Tabele przestawne
523
O tabelach przestawnych
523
Przyk(ad tabeli przestawnej
524
Dane odpowiednie dla tabeli przestawnej
526
Tworzenie tabeli przestawnej
529
Wskazywanie danych
530
Wyznaczanie lokalizacji tabeli przestawnej
530
Definiowanie uk(adu tabeli przestawnej
531
Formatowanie tabeli przestawnej
534
Modyfikowanie tabeli przestawnej
535
Wi cej przyk(adów tabel przestawnych
538
Pytanie 1.
538
Pytanie 2.
539
Pytanie 3.
540
Pytanie 4.
541
Pytanie 5.
542
Pytanie 6.
543
Pytanie 7.
544
Grupowanie elementów tabeli przestawnej
544
Przyk(ad grupowania r cznego
545
Przegl$danie zgrupowanych danych
546
Przyk(ady grupowania automatycznego
547
Tworzenie rozk(adu liczebno'ci
551
Tworzenie pól i elementów obliczeniowych
553
Tworzenie pola obliczeniowego
555
Wstawianie elementu obliczeniowego
557
Filtrowanie tabel przestawnych przy u&yciu fragmentatorów
560
Odwo(ania do komórek w tabeli przestawnej
562
Jeszcze jeden przyk(ad tabeli przestawnej
564
Tworzenie raportu tabeli przestawnej
567
Rozdzia% 19. Formatowanie warunkowe i sprawdzanie poprawnoHci danych
569
Formatowanie warunkowe
569
Wybieranie formatowania warunkowego
571
Formaty warunkowe wykorzystuj$ce grafik
574
Stosowanie formatów warunkowych
583
Tworzenie regu( opartych na formu(ach
585
Sprawdzanie poprawno'ci danych
595
Definiowanie kryteriów sprawdzania poprawno'ci danych
596
Typy kryteriów sprawdzania poprawno'ci danych, jakich mo&esz u&y#
597
Tworzenie list rozwijanych
599
Stosowanie formu( w regu(ach sprawdzania poprawno'ci danych
600
18
Spis tre'ci
Rozdzia% 20. Tworzenie megaformu%
605
Czym jest megaformu(a?
605
Tworzenie megaformu(y — prosty przyk(ad
606
Przyk(ady megaformu(
609
Usuwanie drugich imion przy u&yciu megaformu(y
609
U&ycie megaformu(y zwracaj$cej pozycj ostatniego znaku spacji w (a+cuchu
613
Zastosowanie megaformu(y do sprawdzania poprawno'ci numerów kart kredytowych
617
Generowanie nazwisk losowych
622
Zalety i wady megaformu(
623
Rozdzia% 21. Narz+dzia i metody usuwania b%+dów w formu%ach
625
Debugowanie formu(?
625
Problemy z formu(ami i ich rozwi$zania
626
Niedopasowanie nawiasów
627
Komórki wype(nione symbolami krzy&yka
629
Puste komórki, które wcale nie s$ puste
629
Nadmiarowe znaki spacji
630
Formu(y zwracaj$ce b($d
630
Problemy z odwo(aniami wzgl dnymi i bezwzgl dnymi
635
Problemy z pierwsze+stwem operatorów
635
Formu(y nie s$ obliczane
637
Warto'ci rzeczywiste i wy'wietlane
637
B( dy liczb zmiennoprzecinkowych
638
B( dy nieistniej$cych ($czy
639
B( dy warto'ci logicznych
640
B( dy odwo(a+ cyklicznych
641
Narz dzia inspekcyjne w Excelu
641
Identyfikowanie komórek okre'lonego typu
641
Przegl$danie formu(
642
-ledzenie relacji pomi dzy komórkami
644
-ledzenie warto'ci b( dów
646
Naprawianie b( dów odwo(a+ cyklicznych
646
Funkcja sprawdzania b( dów w tle
647
Korzystanie z funkcji Szacowanie formu(y
649
Cz)&* VI Tworzenie niestandardowych funkcji arkusza
651
Rozdzia% 22. Wprowadzenie do VBA
653
Kilka s(ów o VBA
653
Wy'wietlanie karty Deweloper
654
O bezpiecze+stwie makr
655
Zapisywanie skoroszytów zawieraj$cych makra
656
Wprowadzenie do Visual Basic Editor
657
Aktywowanie VB Editor
657
Elementy VB Editor
658
Korzystanie z okna projektu
659
Korzystanie z okna kodu
662
Wprowadzanie kodu VBA
664
Zapisywanie projektu
667
19
Spis tre'ci
Rozdzia% 23. Podstawy tworzenia funkcji niestandardowych
669
Po co tworzy si funkcje niestandardowe?
670
Wprowadzaj$cy przyk(ad funkcji VBA
670
O procedurach Function
673
Deklarowanie funkcji
673
Wybór nazwy dla funkcji
674
U&ywanie funkcji w formu(ach
674
U&ycie argumentów w funkcjach
676
Korzystanie z okna dialogowego Wstawianie funkcji
676
Dodawanie opisu funkcji
677
Okre'lanie kategorii funkcji
678
Dodawanie opisów argumentów
679
Testowanie i debugowanie funkcji
681
U&ycie instrukcji VBA MsgBox
682
U&ycie instrukcji Debug.Print w kodzie
684
Wywo(ywanie funkcji z procedury Sub
685
Ustawianie punktu kontrolnego w funkcji
688
Tworzenie dodatków
689
Rozdzia% 24. Koncepcje programowania w VBA
691
Wprowadzaj$cy przyk(ad procedury Function
692
Umieszczanie komentarzy wewn$trz kodu
694
U&ycie zmiennych, typów danych i sta(ych
695
Definiowanie typów danych
695
Deklarowanie zmiennych
697
U&ycie sta(ych
698
U&ycie (a+cuchów
700
U&ycie dat
700
U&ycie wyra&e+ przypisania
701
U&ycie tablic
702
Deklarowanie tablic
703
Deklarowanie tablic wielowymiarowych
703
U&ycie wbudowanych funkcji VBA
704
Sterowanie wykonaniem
705
Konstrukcja If-Then
706
Konstrukcja Select Case
708
Bloki zap tlaj$ce
709
Instrukcja On Error
713
U&ycie zakresów
715
Konstrukcja For Each-Next
715
Odwo(ania do zakresów
716
Kilka u&ytecznych w(a'ciwo'ci zakresów
719
S(owo kluczowe Set
723
Funkcja Intersect
723
Funkcja Union
724
W(a'ciwo'# UsedRange
724
20
Spis tre'ci
Rozdzia% 25. Przyk%ady niestandardowych funkcji VBA
727
Proste funkcje
728
Czy komórka zawiera formu( ?
728
Zwracanie formu(y zawartej w komórce
728
Czy komórka jest ukryta?
729
Zwracanie nazwy arkusza
730
Odczytywanie nazwy skoroszytu
730
Odczytywanie nazwy aplikacji
731
Odczytywanie numeru wersji Excela
731
Odczytywanie informacji o formatowaniu komórki
732
Sprawdzanie typu danych w komórce
734
Funkcje wielofunkcyjne
735
Generowanie liczb losowych
738
Generowanie niezmiennych liczb losowych
738
Losowe zaznaczanie komórek
739
Obliczanie prowizji od sprzeda&y
741
Funkcja dla prostej struktury prowizji
742
Funkcja dla bardziej z(o&onej struktury prowizji
743
Funkcje do manipulowania tekstem
744
Odwracanie (a+cucha
744
Mieszanie tekstu
744
Zwracanie akronimu
745
Czy tekst jest zgodny z wzorcem?
746
Czy komórka zawiera okre'lone s(owo?
747
Czy komórka zawiera tekst?
748
Wyodr bnianie
n-tego elementu (a+cucha
748
S(owny zapis liczb
749
Funkcje zliczaj$ce
750
Zliczanie komórek zgodnych z wzorcem
750
Zliczanie arkuszy w skoroszycie
751
Zliczanie wyrazów w zakresie
751
Zliczanie kolorów
752
Funkcje operuj$ce na datach
752
Obliczanie daty nast pnego poniedzia(ku
753
Obliczanie daty nast pnego dnia tygodnia
753
Który tydzie+ miesi$ca?
754
Obs(uga dat sprzed 1900 roku
754
Zwracanie ostatniej, niepustej komórki w kolumnie lub wierszu
756
Funkcja OSTATNIAWKOLUMNIE
756
Funkcja OSTATNIAWWIERSZU
757
Funkcje wieloarkuszowe
757
Zwracanie maksymalnej warto'ci z wielu arkuszy
758
Funkcja SHEETOFFSET
759
Zaawansowane techniki tworzenia funkcji
760
Zwracanie warto'ci b( du
760
Zwracanie tablicy przez funkcj
762
Zwracanie tablicy niepowtarzalnych, losowych liczb ca(kowitych
763
Zwracanie tablicy losowych liczb ca(kowitych z podanego zakresu
765
Stosowanie argumentów opcjonalnych
767
Pobieranie nieokre'lonej liczby argumentów
768
21
Spis tre'ci
Dodatki
Dodatek A Wykaz funkcji Excela
775
Dodatek B Niestandardowe formaty liczbowe
793
O formatowaniu liczb
793
Automatyczne formatowanie liczb
794
Formatowanie liczb przy u&yciu Wst$&ki
795
Formatowanie liczb przy u&yciu skrótów klawiaturowych
795
Formatowanie liczb przy u&yciu okna dialogowego Formatowanie komórek
796
Tworzenie niestandardowego formatu liczbowego
797
Elementy (a+cucha formatu liczbowego
799
Kody niestandardowego formatu liczbowego
800
Przyk(ady niestandardowych formatów liczbowych
802
Skalowanie warto'ci
803
Ukrywanie zer
806
Wy'wietlanie zer poprzedzaj$cych
807
Wy'wietlanie u(amków
807
Wy'wietlanie N/D zamiast tekstu
808
Wy'wietlanie tekstu w cudzys(owach
808
Powielanie wpisu w komórce
808
Wy'wietlanie minusa po prawej stronie
809
Warunkowe formatowanie liczb
809
Wy'wietlanie warto'ci w kolorach
810
Formatowanie dat i godzin
811
Wy'wietlanie tekstu z liczbami
811
Wy'wietlanie kresek zamiast zer
812
U&ycie symboli specjalnych
812
Ukrywanie poszczególnych typów informacji
813
Wype(nianie komórek powtarzaj$cymi si znakami
813
Wy'wietlanie kropek wiod$cych
814
Dodatek C Dodatkowe zasoby Excela
815
System pomocy Excela
815
Wsparcie techniczne ze strony Microsoftu
816
Opcje wsparcia
816
Pomoc techniczna firmy Microsoft
816
Strona domowa programu Microsoft Excel
816
Strona domowa pakietu Microsoft Office
816
Internetowe grupy dyskusyjne
817
Dost p do grup dyskusyjnych przy u&yciu czytnika
817
Dost p do grup dyskusyjnych przy u&yciu przegl$darki internetowej
818
Przeszukiwanie grup dyskusyjnych
819
Witryny internetowe
820
Strona Spreadsheet
820
Daily Dose of Excel
820
Strona Jona Peltiera
821
Pearson Software Consulting
821
Contextures
821
Strony Davida McRitchiego
821
Pointy Haired Dilbert
821
Mr. Excel
821
22
Spis tre'ci
Dodatek D Przyk%ady do%Pczone do ksiPDki
823
Rozdzia( 1.
824
Rozdzia( 5.
824
Rozdzia( 6.
825
Rozdzia( 7.
825
Rozdzia( 8.
825
Rozdzia( 9.
826
Rozdzia( 10.
826
Rozdzia( 11.
826
Rozdzia( 12.
827
Rozdzia( 13.
827
Rozdzia( 15.
827
Rozdzia( 16.
828
Rozdzia( 17.
828
Rozdzia( 18.
829
Rozdzia( 19.
830
Rozdzia( 20.
830
Rozdzia( 24.
831
Rozdzia( 25.
831
Dodatek A
832
Dodatek B
832
Skorowidz
833
251
Rozdzia5
8
Funkcje wyszukiwania
W TYM ROZDZIALE:
Wprowadzenie do formu( wyszukuj$cych warto'ci w tabelach
Przegl$d funkcji arkusza u&ywanych do wyszukiwania
Podstawowe formu(y wyszukuj$ce
Wyspecjalizowane formu(y wyszukuj$ce
W rozdziale tym opisuj ró&ne techniki wyszukiwania warto'ci w tabeli. Do tego celu w progra-
mie Excel zaprojektowano trzy funkcje (
WYSZUKAJ
,
WYSZUKAJ.PIONOWO
i
WYSZUKAJ.POZIOMO
),
ale mo&e si okaza#, &e to za ma(o. Zawar(em tu wiele przyk(adów formu( wyszukuj$cych,
($cznie z alternatywnymi technikami znacznie wykraczaj$cymi poza zwyk(e funkcje wyszu-
kiwania Excela.
Co to jest formu5a wyszukiwania
Formu%a wyszukiwania
zwraca warto'# z tabeli (w zakresie), szukaj$c innej warto'ci.
Z analogiczn$ sytuacj$ mamy do czynienia, gdy szukamy numeru w ksi$&ce telefonicznej.
Aby znaleQ# numer telefoniczny jakiej' osoby, najpierw znajdujemy jej nazwisko, a dopiero
potem sprawdzamy numer.
252
Cz2'3 II Stosowanie funkcji w formu0ach
U
WAGA
Pod poj2ciem tabela rozumiem prostok:tny zakres danych. Nie musi to by3
„prawdziwa” tabela, taka jak te, które tworzy si2 za pomoc: opcji
Wstawianie/Tabele/Tabela
.
Rysunek 8.1 przedstawia prosty arkusz, na którym u&yto kilku funkcji wyszukiwania. Zawiera
on tabel informacji o pracownikach (o nazwie
DanePracowników) zaczynaj$c$ si w wierszu 7.
Je'li wpiszemy nazwisko w komórce
B2, formu(y wyszukiwania w komórkach C2:F2 znajd$
pasuj$ce informacje w tabeli. W poni&szych formu(ach u&yto funkcji
WYSZUKAJ.PIONOWO
.
Komórka
Formu5a
C2
=WYSZUKAJ.PIONOWO(B2; DanePracowników;2; FA)SZ)
D2
=WYSZUKAJ.PIONOWO(B2; DanePracowników;3; FA)SZ)
E2
=WYSZUKAJ.PIONOWO(B2; DanePracowników;4; FA)SZ)
F2
=WYSZUKAJ.PIONOWO(B2; DanePracowników;5; FA)SZ)
Rysunek 8.1. FormuGy wyszukiwania w wierszu 2. szukaj: informacji o pracowniku,
którego nazwisko wpisano w komórce B2
Ten konkretny przyk(ad zwraca informacje z zakresu
DanePracowników przy u&yciu czterech
formu(. W wielu sytuacjach potrzebna jest tylko jedna informacja z tabeli. Wtedy nale&y
u&y# tylko jednej formu(y.
253
RozdziaG 8. Funkcje wyszukiwania
Funkcje zwiLzane
z wyszukiwaniem
W Excelu jest dost pnych kilka funkcji przydatnych podczas pisania formu( wyszukiwania
danych w tabeli. Tabela 8.1 zawiera ich zestawienie i krótkie opisy.
TABELA 8.1. FUNKCJE URYWANE W FORMUVACH WYSZUKIWANIA
Funkcja
Opis
INDEKS
Zwraca warto'3 (lub odwoGanie do warto'ci) z tabeli lub zakresu.
PODAJ.POZYCJ-
Zwraca wzgl2dne poGoNenie elementu w zakresie, które pasuje
do podanej warto'ci.
PRZESUNI-CIE
Zwraca odwoGanie do zakresu przesuni2te o okre'lon: liczb2 wierszy
i kolumn wzgl2dem jakiej' komórki lub jakiego' zakresu komórek.
WYBIERZ
Zwraca okre'lon: warto'3 z listy warto'ci (do 29) podanych jako argumenty.
WYSZUKAJ
Zwraca warto'3 z jednowierszowego lub jednokolumnowego zakresu.
Podobnie dziaGa funkcja
WYSZUKAJ.PIONOWO
, ale zwraca tylko warto'ci
z ostatniej kolumny w zakresie.
WYSZUKAJ.PIONOWO
Wyszukiwanie pionowe. Wyszukuje warto'ci w pierwszej kolumnie
tabeli i zwraca warto'3 znajduj:c: si2 w tym samym wierszu
w okre'lonej kolumnie w tabeli.
WYSZUKAJ.POZIOMO
Wyszukiwanie poziome. Szuka warto'ci w górnym wierszu tabeli
i zwraca warto'3 znajduj:c: si2 w tej samej kolumnie w okre'lonym
wierszu w tabeli.
W przyk(adach prezentowanych w tym rozdziale u&ywane s$ funkcje z tabeli 8.1.
Podstawowe
formu5y wyszukiwania
Za pomoc$ podstawowych funkcji wyszukiwania mo&na przeszuka# kolumn lub wiersz
w celu znalezienia pewnej warto'ci pozwalaj$cej znaleQ# inn$ warto'#. W Excelu dost pne s$
trzy podstawowe funkcje wyszukiwania:
WYSZUKAJ.POZIOMO
,
WYSZUKAJ.PIONOWO
i
WYSZUKAJ
.
Funkcji
PODAJ.POZYCJ-
i
INDEKS
zawsze u&ywa si razem. Zwracaj$ one adres lub adres
wzgl dny komórki zawieraj$cej poszukiwan$ warto'#.
254
Cz2'3 II Stosowanie funkcji w formu0ach
N
A
FTP
PrzykGady prezentowane w tym podrozdziale moNna znaleP3 w pliku podstawowe
formu,y wyszukiwania.xlsx
, który znajduje si2 na serwerze FTP.
Funkcja WYSZUKAJ.PIONOWO
Funkcja
WYSZUKAJ.PIONOWO
wyszukuje warto'# w pierwszej kolumnie tablicy i zwraca war-
to'# z tego samego wiersza w innej kolumnie tablicy. Przeszukiwana tablica jest zorganizo-
wana pionowo. Sk(adnia tej funkcji jest nast puj$ca:
SZUKAJ.PIONOWO(szukana_warto(); tablica; numer_kolumny; kolumna)
Poni&ej znajduje si opis argumentów funkcji
WYSZUKAJ.PIONOWO
:
szukana_warto&'
— warto'#, która ma by# wyszukana w pierwszej kolumnie
tablicy.
tablica
— zakres zawieraj$cy tablic do przeszukania.
numer_kolumny
— numer kolumny w tablicy, z której zwracana jest pasuj$ca
warto'#.
kolumna
— opcjonalny. Je'li ma warto'#
PRAWDA
lub nie jest podany, funkcja zwraca
dopasowanie przybli&one (je'li nie zostanie znalezione dok(adne dopasowanie,
funkcja zwraca nast pn$ najwi ksz$ warto'# mniejsz$ od szukanej warto'ci).
Je'li ma warto'#
FA)SZ
, funkcja poszukuje dok(adnego dopasowania. Je'li takiego
nie znajdzie, zwróci b($d
#N/D
.
U
WAGA
Je'li argument
kolumna
ma warto'3
PRAWDA
lub zostaG pomini2ty, pierwsza kolumna
przeszukiwanej tabeli musi by3 posortowana w porz:dku rosn:cym. Je'li warto'3
szukana jest mniejsza niN najmniejsza warto'3 w pierwszej kolumnie tabeli tablicy,
funkcja
WYSZUKAJ.PIONOWO
zwróci bG:d
#N/D
. Je'li argument
kolumna
ma warto'3
FA)SZ
, pierwsza kolumna przeszukiwanej tabeli nie musi by3 posortowana
w rosn:cym porz:dku. Je'li dokGadne dopasowanie nie zostanie znalezione,
funkcja zwraca bG:d
#N/D
.
W
SKAZÓWKA
Je'li argument
szukana_warto()
jest typu tekstowego (a czwarty argument,
kolumna
, ma warto'3
FA)SZ
), moNna zastosowa3 symbole wieloznaczne
*
i
?
.
Gwiazdka dopasowuje dowoln: grup2 znaków, a znak zapytania dowolny
jeden znak.
255
RozdziaG 8. Funkcje wyszukiwania
Klasyczny przyk(ad formu(y wyszukuj$cej ma zwi$zek z rozk(adem wysoko'ci stopy opro-
centowania podatku dochodowego (rysunek 8.2). Pokazuje on stopy procentowe podatku
dochodowego dla ró&nych wysoko'ci zarobków. Poni&sza formu(a (komórka
B3) zwraca stop
oprocentowania dla dochodów w komórce
B2:
=WYSZUKAJ.PIONOWO(B2; D2:F7; 3)
Rysunek 8.2. Wyszukiwanie stopy oprocentowania za pomoc: funkcji WYSZUKAJ.PIONOWO
Przeszukiwana tabela zajmuje komórki w trzech kolumnach (
D2:F7). Jako &e trzeci argument
funkcji
WYSZUKAJ.PIONOWO
to
3
, formu(a zwraca odpowiedni$ warto'# z trzeciej kolumny tabeli.
Zauwa&, &e nie jest wymagane dok(adne dopasowanie. Je'li w pierwszej kolumnie tabeli nie
zostanie znalezione dok(adne dopasowanie, funkcja wykorzystuje nast pn$ najwi ksz$ warto'#
mniejsz$ od wyszukiwanej warto'ci. Innymi s(owy, funkcja u&ywa wiersza zawieraj$cego
warto'# wi ksz$ od wyszukiwanej warto'ci lub jej równ$, ale mniejsz$ od warto'ci w nast p-
nym wierszu. W przypadku tabeli stóp oprocentowania jest to dok(adnie takie dzia(anie,
jakiego chcemy.
Funkcja WYSZUKAJ.POZIOMO
Funkcja
WYSZUKAJ.POZIOMO
dzia(a dok(adnie tak samo jak funkcja
WYSZUKAJ.PIONOWO
z tym
wyj$tkiem, &e tabela jest uporz$dkowana poziomo, a nie pionowo. Szuka warto'ci w pierw-
szym wierszu tabeli i zwraca odpowiadaj$c$ jej warto'# znajduj$c$ si w okre'lonym wierszu
w tabeli.
Sk(adnia tej funkcji jest nast puj$ca:
WYSZUKAJ.POZIOMO(szukana_warto(); tabela_tablica; numer_indeksu_wiersza;
przeszukiwany_zakres)
Oto opis argumentów funkcji
WYSZUKAJ.POZIOMO
:
szukana_warto&'
— warto'#, która ma by# wyszukana w pierwszym wierszu
przeszukiwanej tabeli.
256
Cz2'3 II Stosowanie funkcji w formu0ach
tabela_tablica
— zakres zawieraj$cy przeszukiwan$ tabel .
numer_indeksu_wiersza
— numer wiersza w tabeli, z którego ma by# zwrócona
pasuj$ca warto'#.
przeszukiwany_zakres
— opcjonalny. Je'li ma warto'#
PRAWDA
lub zostanie
pomini ty, zwracane jest dopasowanie przybli&one (je'li dok(adne dopasowanie
nie zostanie znalezione, zwracana jest nast pna najwi ksza warto'# mniejsza od
szukanej warto'ci). Je'li ma warto'#
FA)SZ
, funkcja szuka dok(adnego dopasowania.
Je'li go nie znajdzie, zwracany jest b($d
#N/D
.
W
SKAZÓWKA
Je'li argument
szukana_warto()
jest tekstem, moNe zawiera3 symbole wieloznaczne
*
i
?
. Gwiazdka dopasowuje dowoln: liczb2 znaków, a znak zapytania jeden znak.
Rysunek 8.3 przedstawia przyk(ad ze stopami oprocentowania z poziom$ tabel$ do przeszu-
kiwania (w zakresie
E1:J3). Formu(a w komórce B3 to:
WYSZUKAJ.POZIOMO(B2; E1:J3; 3)
Rysunek 8.3. UNycie funkcji WYSZUKAJ.POZIOMO do znalezienia stopy procentowej
Funkcja WYSZUKAJ
Sk(adnia funkcji
WYSZUKAJ
jest nast puj$ca:
WYSZUKAJ(szukana_warto(); przeszukiwany_wektor; wektor_wynikowy)
Opis argumentów funkcji
WYSZUKAJ
:
szukana_warto&'
— warto'#, która ma by# wyszukana w przeszukiwanym
wektorze.
przeszukiwany_wektor
— sk(adaj$cy si z jednej kolumny lub jednego wiersza
zakres zawieraj$cy warto'ci do wyszukania. Musz$ one by# uporz$dkowane
w kolejno'ci rosn$cej.
wektor_wynikowy
— sk(adaj$cy si z jednej kolumny lub jednego wiersza zakres
zawieraj$cy warto'ci, które maj$ by# zwrócone. Musi mie# taki sam rozmiar jak
przeszukiwany wektor.
257
RozdziaG 8. Funkcje wyszukiwania
Funkcja
WYSZUKAJ
przeszukuje jednokolumnowy lub jednowierszowy zakres (
przeszukiwany_wektor
)
w celu znalezienia warto'ci (
szukana_warto()
) i zwraca warto'# o takim samym po(o&eniu
w drugim jednowierszowym lub jednokolumnowym zakresie (
wektor_wynikowy
).
O
STRZERENIE
Warto'ci w przeszukiwanym wektorze musz: by3 posortowane rosn:co.
Je'li szukana warto'3 jest mniejsza niN najmniejsza warto'3 w przeszukiwanym
wektorze, funkcja
WYSZUKAJ
zwraca bG:d
#N/D
.
U
WAGA
W pomocy jest teN mowa o tablicowej skGadni funkcji
WYSZUKAJ
. Ta alternatywna
skGadnia zostaGa dodana ze wzgl2du na zachowanie zgodno'ci z innymi arkuszami
kalkulacyjnymi. Z reguGy zamiast skGadni tablicowej moNna uNywa3 funkcji
WYSZUKAJ.POZIOMO
i
WYSZUKAJ.PIONOWO
.
Na rysunku 8.4 ponownie widzimy tabel stóp podatkowych. Tym razem formu(a w komór-
ce
B3 u&ywa funkcji
WYSZUKAJ
do zwrócenia odpowiedniej stopy podatkowej. Formu(a ta jest
nast puj$ca:
=WYSZUKAJ(B2;D2:D7;G4:G9)
Rysunek 8.4. UNycie funkcji WYSZUKAJ do znalezienia stopy podatkowej
O
STRZERENIE
Je'li warto'ci w pierwszej kolumnie nie s: posortowane w rosn:cej kolejno'ci,
funkcja
WYSZUKAJ
moNe zwróci3 nieprawidGow: warto'3.
Zauwa&, &e funkcja
WYSZUKAJ
(w przeciwie+stwie do funkcji
WYSZUKAJ.PIONOWO
) mo&e
zwróci# warto'# znajduj$c$ si w innym wierszu ni& dopasowana warto'#. Je'li argumenty
przeszukiwany_wektor
i
wektor_wynikowy
nie nale&$ do tej samej tabeli, funkcja
WYSZUKAJ
mo&e by# bardzo pomocna. Je'li jednak nale&$ do tej samej tabeli, lepiej jest u&ywa# funkcji
WYSZUKAJ.PIONOWO
, cho#by dlatego, &e funkcja
WYSZUKAJ
nie dzia(a z nieposortowanymi danymi.
258
Cz2'3 II Stosowanie funkcji w formu0ach
VLczne u@ycie funkcji
PODAJ.POZYCJ[ i INDEKS
Funkcje
PODAJ.POZYCJ-
i
INDEKS
s$ cz sto u&ywane w formu(ach wyszukuj$cych. Funkcja
PODAJ.POZYCJ-
zwraca wzgl dne po(o&enie w zakresie komórki, która pasuje do okre'lonej
warto'ci. Jej sk(adnia jest nast puj$ca:
PODAJ.POZYCJ-(szukana_warto(); przeszukiwana_tablica; typ_porównania)
Oto opis argumentów tej funkcji:
szukana_warto&'
— warto'#, która ma by# dopasowana do warto'ci w przeszukiwanej
tablicy. Je'li argument
typ_porównania
ma warto'#
0
, a szukana warto'# to tekst,
argument ten mo&e zawiera# symbole wieloznaczne
*
i
?
.
przeszukiwana_tablica
— tablica, która ma by# przeszukana.
typ_porównania
— liczba ca(kowita (
-1
,
0
lub
1
) okre'laj$ca sposób porównywania
warto'ci.
U
WAGA
Je'li argument
typ_porównania
ma warto'3
1
, funkcja
PODAJ.POZYCJ-
znajduje
najwi2ksz: warto'3, która jest mniejsza lub równa szukanej warto'ci (przeszukiwana
tablica musi by3 posortowana rosn:co). Warto'3
0
tego argumentu powoduje
znalezienie pierwszej warto'ci, która jest identyczna z poszukiwan:. Je'li argument
typ_porównania
ma warto'3
-1
, funkcja
PODAJ.POZYCJ-
znajduje najmniejsz:
warto'3 wi2ksz: lub równ: szukanej warto'ci (przeszukiwana tablica musi by3
posortowana malej:co). Pomini2cie tego argumentu oznacza nadanie mu warto'ci
1
.
Funkcja
INDEKS
zwraca komórk z zakresu. Jej sk(adnia jest nast puj$ca:
INDEKS(tablica; numer_wiersza; numer_kolumny)
Oto opis argumentów tej funkcji:
tablica
— zakres;
numer_wiersza
— numer wiersza w tablicy;
numer_kolumny
— numer kolumny w tablicy.
U
WAGA
Je'li tablica zawiera tylko jeden wiersz lub jedn: kolumn2, argument
numer_wiersza
lub
numer_kolumny
jest opcjonalny.
259
RozdziaG 8. Funkcje wyszukiwania
Rysunek 8.5 przedstawia arkusz zawieraj$cy daty, nazwy dni tygodnia i kwoty w kolumnach
D, E i F. Po wpisaniu daty do komórki B1 widoczna poni&ej formu(a (znajduj$ca si
w komórce
B2) przeszukuje daty w kolumnie D i zwraca odpowiedni$ kwot z kolumny F.
Formu(a w komórce
B2 jest nast puj$ca:
INDEKS(F2:F21; PODAJ.POZYCJ-(B1; D2:D21; 0))
Rysunek 8.5. Wyszukiwanie przy uNyciu funkcji INDEKS i PODAJ.POZYCJ]
Aby zapozna# si ze sposobem dzia(ania tej formu(y, zacznijmy od funkcji
PODAJ.POZYCJ-
.
Przeszukuje ona zakres komórek
D2:D21 w celu znalezienia daty znajduj$cej si w komórce B1.
Zwraca wzgl dny numer wiersza, w którym znalaz(a t dat . Warto'# ta jest nast pnie wyko-
rzystywana jako drugi argument funkcji
INDEKS
. Wynikiem jest warto'# z tego samego wiersza
w zakresie
F2:F21.
Wyspecjalizowane
formu5y wyszukujLce
Za pomoc$ kilku dodatkowych formu( wyszukuj$cych mo&na wykonywa# bardziej wyspe-
cjalizowane wyszukiwania. Mo&na na przyk(ad wyszuka# dok(adn$ warto'#, przeszuka# ko-
lumny poza pierwsz$ kolumn$ w tabeli, przeprowadzi# wyszukiwanie z rozró&nianiem ma-
(ych i wielkich liter, zwróci# warto'# spomi dzy kilku tabel, a tak&e wykonywa# inne
wyspecjalizowane i z(o&one wyszukiwania.
260
Cz2'3 II Stosowanie funkcji w formu0ach
Kiedy pustka nie jest zerem
Funkcje Excela traktuj: puste komórki w wynikowym zakresie jako zera.
Arkusz przedstawiony na poniNszym rysunku zawiera dwukolumnow: tabel2
do przeszukiwania. PoniNsza formuGa wyszukuje nazw2 znajduj:c: si2
w komórce B1 i zwraca odpowiedni: ilo'3:
WYSZUKAJ.PIONOWO(B1; D2:E8; 2)
Zwró3 uwag2, Ne komórka Ilo45 jest pusta dla imienia Karol, ale formuGa zwraca
warto'3
0
.
Aby odróNni3 zera od pustych komórek, konieczna jest modyfikacja formuGy
polegaj:ca na dodaniu funkcji
JE>ELI
, która sprawdzi, czy dGugo'3 zwróconej
warto'ci to
0
. Je'li znaleziona warto'3 jest pusta, dGugo'3 zwróconej warto'ci
wynosi zero. We wszystkich innych przypadkach jest ona róNna od zera.
PoniNsza formuGa wy'wietla pusty ci:g, je'li dGugo'3 znalezionej warto'ci
wynosi zero, i rzeczywist: warto'3, je'li dGugo'3 jest róNna od zera:
=JE>ELI(D)(WYSZUKAJ.PIONOWO(B1; D2:E8; 2))=0; ""; (WYSZUKAJ.PIONOWO(B1;
D2:E8; 2)))
N
A
FTP
PrzykGady prezentowane w tym podrozdziale moNna znaleP3 w pliku
wyspecjalizowane formu,y wyszukiwania.xlsx
, który jest dost2pny na serwerze FTP.
Wyszukiwanie dok5adnej warto&ci
Jak pokaza(em w poprzednich przyk(adach, funkcje
WYSZUKAJ.PIONOWO
i
WYSZUKAJ.POZIOMO
nie wymagaj$ dok(adnego dopasowania szukanej warto'ci do warto'ci znajduj$cych si
w przeszukiwanej tabeli. Przyk(adem takiego przybli&onego dopasowywania jest wyszukiwa-
nie stopy procentowej podatku w tabeli podatków. Czasami mo&e jednak by# potrzebne do-
k(adne dopasowanie, na przyk(ad przy wyszukiwaniu numeru pracownika.
261
RozdziaG 8. Funkcje wyszukiwania
Aby wyszuka# tylko dok(adn$ warto'#, nale&y u&y# funkcji
WYSZUKAJ.PIONOWO
lub
WYSZUKAJ.POZIOMO
z opcjonalnym czwartym argumentem ustawionym na warto'#
FA)SZ
.
Rysunek 8.6 przedstawia arkusz zawieraj$cy tabel z numerami pracowników (kolumna
C)
oraz ich imionami i nazwiskami (kolumna
D). Nazwa tej tabeli to ListaPracowników. Przed-
stawiona poni&ej formu(a znajduj$ca si w komórce
B2 wyszukuje numer pracownika wpro-
wadzony do komórki
B1 i zwraca odpowiadaj$ce mu imi i nazwisko pracownika:
=WYSZUKAJ.PIONOWO(B1; ListaPracowników; 2; FA)SZ)
Rysunek 8.6. Ta tabela wymaga dokGadnego dopasowania
Jako &e ostatni argument funkcji
WYSZUKAJ.PIONOWO
ma warto'#
FA)SZ
, funkcja zwraca imi
i nazwisko pracownika, tylko je'li znajdzie dok(adnie pasuj$c$ warto'#. Je'li numer pracow-
nika nie zostanie znaleziony, funkcja ta zwraca b($d
#N/D
. Jest to jak najbardziej po&$dane
dzia(anie, poniewa& zwracanie przybli&onej warto'ci dla numeru pracownika nie mia(oby
sensu. Zauwa& te&, &e numery pracowników w kolumnie
C nie s$ posortowane rosn$co. Jest to
mo&liwe w przypadku, gdy ostatni argument funkcji
WYSZUKAJ.PIONOWO
ma warto'#
FA)SZ
.
N
OWO\]
Aby zmieni3 wy'wietlanie bG2du
#N/D
w przypadku nieznalezienia numeru
pracownika na co' innego, moNna uNy3 funkcji
JE>ELI.B)@D
, do znalezienia
informacji o bG2dzie i zast:pienia jej wGasnym Ga`cuchem znaków. PoniNsza
formuGa wy'wietla tekst Nie znaleziono, zamiast
#N/D
:
=JE>ELI.B)@D(WYSZUKAJ.PIONOWO(B1;ListaPracowników;2;FA)SZ)
"Nie znaleziono")
Funkcja
JE>ELI.B)@D
dziaGa tylko w wersjach 2007 i 2010 Excela. Aby zachowa3
zgodno'3 z wcze'niejszymi wersjami programu, naleNy zastosowa3 poniNsz: formuG2:
=JE>ELI(CZY.BRAK(WYSZUKAJ.PIONOWO(B1,ListaPracowników,2,
FA)SZ)),"Nie znaleziono", WYSZUKAJ.PIONOWO
(B1,ListaPracowników,2, FA)SZ))
262
Cz2'3 II Stosowanie funkcji w formu0ach
Wyszukiwanie warto&ci w lewL stron)
Funkcja
WYSZUKAJ.PIONOWO
zawsze wyszukuje warto'ci w pierwszej kolumnie przeszukiwa-
nego zakresu. Co zrobi#, aby wyszuka# warto'# w innej ni& pierwsza kolumnie? Dobrze by
by(o, gdyby'my mogli jako trzeci argument tej funkcji poda# ujemn$ warto'# — ale nie mo&emy.
Rysunek 8.7 ilustruje ten problem. Za(ó&my, &e chcemy znaleQ# 'redni$ pa(kowania (kolum-
na
B, w zakresie o nazwie /rednie) gracza z kolumny C (w zakresie o nazwie Gracze). Nazwisko
gracza, którego dane chcemy zobaczy#, znajduje si w komórce o nazwie
SzukanaWarto56.
Funkcja
WYSZUKAJ.PIONOWO
nie zadzia(a, poniewa& dane nie s$ odpowiednio u(o&one. Jednym
z wyj'# jest poprzestawianie danych, ale to nie zawsze jest mo&liwe.
Rysunek 8.7. Funkcja WYSZUKAJ.PIONOWO nie moNe wyszuka3 warto'ci znajduj:cej si2
w kolumnie B, opieraj:c si2 na warto'ci w kolumnie C
Innym rozwi$zaniem jest u&ycie funkcji
WYSZUKAJ
, która wymaga dwóch argumentów w po-
staci zakresów. Poni&sza formu(a zwraca 'redni$ pa(kowania z kolumny
B dla gracza z ko-
mórki o nazwie
SzukanaWarto56:
=WYSZUKAJ(SzukanaWartoFG;Gracze;Hrednie)
Funkcja
WYSZUKAJ
wymaga, aby przeszukiwany zakres (w tym przypadku o nazwie
Gracze)
by( posortowany w porz$dku rosn$cym. Poza tym formu(a ta jest obarczona jeszcze jednym
ma(ym problemem: je'li wpisane zostanie nazwisko nieistniej$cego gracza (czyli komórka
SzukanaWarto56 b dzie zawiera(a warto'#, której nie ma w zakresie Gracze), zwróci b( dny wynik.
Lepszym rozwi$zaniem jest u&ycie funkcji
INDEKS i PODAJ.POZYCJ@. Poni&sza formu(a
dzia(a jak poprzednia, ale zwraca b($d
#N/D
, je'li gracz nie zostanie znaleziony. Drug$ zalet$
tej formu(y jest to, &e nazwiska graczy nie musz$ by# posortowane.
=INDEKS(Hrednie;PODAJ.POZYCJ-(SzukanaWartoFG;Gracze;0))
263
RozdziaG 8. Funkcje wyszukiwania
Wyszukiwanie z rozró@nianiem ma5ych
i wielkich liter
Funkcje wyszukiwania w Excelu (
WYSZUKAJ
,
WYSZUKAJ.POZIOMO
i
WYSZUKAJ.PIONOWO
) nie
rozró&niaj$ ma(ych i wielkich liter. Je'li na przyk(ad napiszemy formu( wyszukuj$c$ ci$g
budQet
, wszystkie nast puj$ce ci$gi b d$ brane pod uwag :
BUD>ET
,
BudQet
,
BuDQEt
.
Rysunek 8.8 przedstawia prosty przyk(ad. Zakres komórek
D2:D7 ma nazw Zakres1, a zakres
E2:E7 nazywa si Zakres2. S(owo do znalezienia znajduje si w komórce B1 (o nazwie Warto56).
Rysunek 8.8. Wyszukiwanie z rozróNnianiem maGych i wielkich liter przy uNyciu
formuGy tablicowej
Widoczna poni&ej formu(a znajduje si w komórce
B2. Wykonuje ona wyszukiwanie z roz-
ró&nianiem ma(ych i wielkich liter w zakresie
Zakres1 i zwraca odpowiedni$ warto'# z zakre-
su
Zakres2.
=INDEKS(Zakres2;PODAJ.POZYCJ-(PRAWDA;PORÓWNAJ(WartoFG;Zakres1);0))
Formu(a ta szuka s(owa
PIES
(pisanego wielkimi literami) i zwraca warto'#
300
.
U
WAGA
Pami2taj o uNyciu kombinacji klawiszy Ctrl+Shift+Enter przy wprowadzaniu
formuGy tablicowej.
Wybieranie spo&ród wielu tabel
W arkuszu mo&na oczywi'cie mie# dowoln$ liczb tabel do przeszukiwania. Mo&e si zda-
rzy#, &e konieczne b dzie wybranie przez formu( jednej z nich. Rysunek 8.9 przedstawia
przyk(adowy arkusz.
264
Cz2'3 II Stosowanie funkcji w formu0ach
Rysunek 8.9. Arkusz ten demonstruje uNycie wielu tabel do przeszukiwania
Arkusz ten oblicza prowizje od sprzeda&y i zawiera dwie tabele do przeszukiwania:
G3:H9
(o nazwie
Tabela1) i J3:K8 (o nazwie Tabela2). Wysoko'# stopy procentowej prowizji ka°o
przedstawiciela handlowego zale&y od dwóch czynników: liczby lat pracy (kolumna
B) i ilo'ci
sprzedanego towaru (kolumna
C). Kolumna D zawiera formu(y, które wyszukuj$ stop procen-
tow$ prowizji w odpowiedniej tabeli. Na przyk(ad formu(a w komórce
D2 jest nast puj$ca:
=WYSZUKAJ.PIONOWO(C2;JE>ELI(B2<3;Tabela1;Tabela2);2)
Drugi argument funkcji
WYSZUKAJ.PIONOWO
zawiera funkcj
JE>ELI
, która na podstawie war-
to'ci znajduj$cej si w kolumnie
B okre'la, któr$ tabel przeszuka#.
Formu(y w kolumnie
E mno&$ tylko ilo'# sprzedanego towaru z kolumny C przez stop pro-
centow$ prowizji z kolumny
D. Na przyk(ad formu(a w komórce E2 jest nast puj$ca:
=C2*D2
Okre&lanie ocen
na podstawie wyników testu
Cz sto spotykanym sposobem u&ycia tabeli do przeszukiwania jest przypisanie ocen wyni-
kom testów. Rysunek 8.10 przedstawia arkusz zawieraj$cy wyniki testu uczniów. Zakres
E2:F6 (o nazwie ListaOcen) stanowi tabel do przeszukiwania w celu dopasowania oceny do
wyniku testu.
Kolumna
C zawiera formu(y u&ywaj$ce funkcji
WYSZUKAJ.PIONOWO
i przypisuj$ce uczniom
oceny na podstawie wyników testu znajduj$cych si w kolumnie
B. Na przyk(ad formu(a
w komórce
C2 jest nast puj$ca:
=WYSZUKAJ.PIONOWO(B2;ListaOcen;2)
265
RozdziaG 8. Funkcje wyszukiwania
Rysunek 8.10. Wyszukiwanie ocen dla wyników testu
Kiedy tabela do przeszukiwania jest niewielka (jak w przyk(adowym arkuszu widocznym na
rysunku 8.10), mo&na zamiast niej u&y# tablicy. Na przyk(ad widoczna poni&ej formu(a
zwraca ocen bez u&ycia tabeli do przeszukiwania. Informacje z tabeli zosta(y zakodowane
w sta(ej tablicowej. Wi cej informacji na temat sta(ych tablicowych znajduje si w rozdziale 14.
=WYSZUKAJ.PIONOWO(B2;{0;"1"\40;"2"\70;"3"\80;"4"\90;"5"};2)
Inne podej'cie z u&yciem bardziej czytelnej formu(y polega na u&yciu funkcji
WYSZUKAJ
z dwoma argumentami tablicowymi:
=WYSZUKAJ(B2;{0;40;70;80;90};{"1";"2";"3";"4";"5"})
Ostatecznie za ka&dym razem, gdy jest mo&liwo'# przekonwertowania danych wej'ciowych,
w tym przypadku liczby punktów, na liczby ca(kowite ze zbioru 1 – 254, mo&na u&y# funkcji
WYBIERZ
. Liczba punktów jest dzielona przez 10, odrzucana jest cz '# dziesi tna i dodawana
jest liczba 1 w celu utworzenia liczb od 1 do 11. Pozosta(e argumenty definiuj$ warto'ci
zwrotne dla tych jedenastu opcji.
=WYBIERZ(LICZBA.CA)K(B2/10)+1;"1";"1";"1";"1";"2";"2";"2";"3";"4";"5";"5")
Obliczanie &redniej ocen
Na wy&szych uczelniach w Ameryce miernikiem jako'ci pracy studenta jest jego 'rednia ocen
(ang.
Grade Point Average — GPA) z zaj #, na które ucz szcza(. W tym przyk(adzie system
ocen sk(ada si z warto'ci od 0 do 4 przypisanych odpowiednio literom (A = 4, B = 3, C = 2,
D = 1, F = 0). GPA oblicza si poprzez wyci$gni cie 'redniej wa&onej z ocen pomno&onych
przez liczb kredytowanych godzin kursu. Na przyk(ad ocena z kursu trwaj$cego jedn$ go-
dzin ma mniejsz$ wag ni& ocena z kursu trwaj$cego trzy godziny. Warto'ci 'redniej zawie-
raj$ si w zbiorze od 0 do 4.
266
Cz2'3 II Stosowanie funkcji w formu0ach
Rysunek 8.11 przedstawia arkusz z informacjami dotycz$cymi jednego studenta. Uczestniczy(
on w pi ciu kursach w sumie trwaj$cych 13 godzin. Zakres
B2:B6 ma nazw KredytowaneGo-
dziny. Oceny za ka&dy kurs znajduj$ si w kolumnie C (zakres C2:C6 ma nazw OcenyLitery).
Formu(y w kolumnie
D obliczaj$ za pomoc$ formu(y wyszukiwania ocen za ka&dy kurs.
Na przyk(ad poni&ej wida# formu( z komórki
D2. Przeszukuje ona tabel w zakresie G2:H6
(o nazwie
TabelaOcen).
=WYSZUKAJ.PIONOWO(C2;TabelaOcen;2;FA)SZ)
Rysunek 8.11. Obliczanie 'redniej przy uNyciu kilku formuG
Formu(y w kolumnie
E obliczaj$ warto'ci wywa&one. Formu(a w komórce E2 jest nast puj$ca:
=D2*B2
Formu(a w komórce
B8 obliczaj$ca GPA jest nast puj$ca:
=SUMA(E2:E6)/SUMA(B2:B6)
Powy&sze formu(y dzia(aj$ prawid(owo, ale mo&na obliczenia te nieco upro'ci#. Da si nawet
unikn$# konieczno'ci u&ycia tabeli do przeszukiwania i formu( w kolumnach
D i E na rzecz
jednej formu(y tablicowej. Poni&sza formu(a wykonuje niezb dne obliczenia:
{=SUMA((PODAJ.POZYCJ-(OcenyLitery;{"F";"D";"C";"B";"A"};0)-1)*KredytowaneGodziny)
/SUMA(KredytowaneGodziny)}
Wyszukiwanie w dwie strony
Rysunek 8.12 przedstawia arkusz zawieraj$cy tabel z miesi cznymi danymi sprzeda&y pro-
duktów. Aby sprawdzi# sprzeda& okre'lonego produktu w okre'lonym miesi$cu, u&ytkownik
wpisuje w komórce
B1 nazw miesi$ca i w komórce B2 nazw produktu.
267
RozdziaG 8. Funkcje wyszukiwania
Rysunek 8.12. Wyszukiwanie w dwie strony
Aby by(o pro'ciej, zastosowa(em w tym arkuszu nast puj$ce nazwy zakresów:
Nazwa
Adres
Miesi=c
B1
Produkt
B2
Tabela
D1:H14
ListaMiesiCcy
D1:D14
ListaProduktów
D1:H1
Poni&sza formu(a (znajduj$ca si w komórce
B4) zwraca pozycj miesi$ca w zakresie Lista-
MiesiLcy. Dla stycznia na przyk(ad zwraca cyfr
2
, poniewa& jest to drugi element tego zakresu
(pierwszy to pusta komórka
D1).
=PODAJ.POZYCJ-(Miesi^c;ListaMiesi_cy;0)
Formu(a znajduj$ca si w komórce
B5 dzia(a w podobny sposób w zakresie ListaProduktów:
=PODAJ.POZYCJ-(Produkt;ListaProduktów;0)
Formu(a w komórce
B6 natomiast zwraca liczb sprzedanych sztuk danego towaru. Robi to
za pomoc$ funkcji
INDEKS
z argumentami w postaci wyników z komórek
B4 i B5.
=INDEKS(Tabela;B4;B5)
Formu(y te mo&na oczywi'cie po($czy# w jedn$ formu( , jak ta poni&ej:
=INDEKS(Tabela; PODAJ.POZYCJ-(Miesi^c;ListaMiesi_cy;0);
PODAJ.POZYCJ-(Produkt;ListaProduktów;0))
268
Cz2'3 II Stosowanie funkcji w formu0ach
W
SKAZÓWKA
Innym sposobem na wykonanie wyszukiwania w dwie strony jest nazwanie kaNdego
wiersza i kaNdej kolumny tabeli. MoNna to szybko zrobi3, zaznaczaj:c caG: tabel2
i uNywaj:c polecenia Formu,y/Nazwy zdefiniowane/Utwórz z zaznaczenia. Po utworzeniu
tych nazw wyszukiwanie w dwie strony moNna wykonywa3 za pomoc: prostych
formuG, jak ta poniNej:
=Sworznie Lipiec
W formule tej zostaG uNyty operator przeci2cia zakresów (spacja). Zwraca ona
wielko'3 sprzedaNy sworzni w lipcu. Aby odwoGa3 si2 do komórek zawieraj:cych
nazwy miesi2cy i produktów, naleNy napisa3:
=ADR.POHR(Miesi^c) ADR.POHR(Produkt)
FormuGa ta konwertuje warto'ci w komórkach Miesi=c i Produkt na odwoGania
do zakresów oraz znajduje ich przeci2cie. SzczegóGowe informacje na temat
operatora przeci2cia znajduj: si2 w rozdziale 3.
Wyszukiwanie dwukolumnowe
W pewnych sytuacjach konieczne jest wyszukanie danych na podstawie warto'ci z dwóch
kolumn. Na rysunku 8.13 wida# przyk(ad takiej sytuacji:
Rysunek 8.13. Arkusz ten wykonuje wyszukiwanie, wykorzystuj:c informacje z dwóch
kolumn (D i E)
Tabela do przeszukiwania zawiera marki i modele samochodów oraz odpowiadaj$ce im kody.
W arkuszu u&ywane s$ nast puj$ce nazwane zakresy:
F2:F12
Kod
B1
Marka
B2
Model
D2:D12
Marki
E2:E12
Modele
269
RozdziaG 8. Funkcje wyszukiwania
Poni&sza formu(a tablicowa wy'wietla kod odpowiadaj$cy wybranemu modelowi marki samochodu:
{=INDEKS(Kod; PODAJ.POZYCJ-(Marka&Model;Marki&Modele;0))}
Formu(a ta ($czy zawarto'# komórek
Marka i Model, a nast pnie wyszukuje ten tekst w tablicy
zawieraj$cej ten sam tekst odpowiednio w zakresach
Marki i Modele.
Sprawdzanie adresu warto&ci w zakresie
W wi kszo'ci przypadków zadaniem formu(y wyszukuj$cej jest zwrócenie warto'ci. Mo&e si
jednak zdarzy#, &e zechcemy sprawdzi# adres komórki w zakresie zawieraj$cej okre'lon$ warto'#.
Na przyk(ad na rysunku 8.14 widoczny jest arkusz zawieraj$cy zbiór liczb zajmuj$cy jedn$ kolumn
(o nazwie
Dane). Komórka B1, w której znajduje si warto'# do wyszukania, ma nazw Cel.
Rysunek 8.14. FormuGa znajduj:ca si2 w komórce B2 zwraca adres w obr2bie zakresu
Dane dla warto'ci znajduj:cej si2 w komórce B1
Widoczna poni&ej formu(a znajduj$ca si w komórce
B2 zwraca adres komórki z zakresu
Dane zawieraj$cej warto'# z komórki Cel:
=ADRES(WIERSZ(Dane)+PODAJ.POZYCJ-(Cel;Dane;0)-1;NR.KOLUMNY(Dane))
Je'li zakres
Dane zajmuje jeden wiersz, do wyszukania adresu docelowej warto'ci u&yj nast -
puj$cej formu(y:
=ADRES(WIERSZ(Dane); NR.KOLUMNY(Dane)+PODAJ.POZYCJ-(Cel;Dane;0)-1)
Je'li warto'# docelowa wyst puje w zakresie
Dane wi cej ni& jeden raz, zwracany jest adres
pierwszej z nich. Je'li nie zostanie ona znaleziona w ogóle, formu(a zwraca b($d
#N/D
.
270
Cz2'3 II Stosowanie funkcji w formu0ach
Wyszukiwanie warto&ci
przy u@yciu najbli@szego dopasowania
Funkcje
WYSZUKAJ.PIONOWO
i
WYSZUKAJ.POZIOMO
s$ przydatne w nast puj$cych sytuacjach:
Chcemy zidentyfikowa# warto'# dok(adnie pasuj$c$ do docelowej warto'ci.
W takim przypadku ustawiamy czwarty argument funkcji na warto'#
FA)SZ
.
Chcemy zlokalizowa# przybli&on$ warto'#. Je'li czwarty argument funkcji ma
warto'#
PRAWDA
lub zostanie pomini ty i zostanie znaleziona warto'# pasuj$ca
dok(adnie, zostaje zwrócona nast pna najwi ksza warto'#, która jest mniejsza
od szukanej warto'ci.
Ale co zrobi#, je'li potrzebujemy znaleQ# najbli&sz$ pasuj$c$ warto'#? Nie zrobimy tego ani
za pomoc$ funkcji
WYSZUKAJ.PIONOWO
, ani
WYSZUKAJ.POZIOMO
.
Na rysunku 8.15 widoczny jest arkusz zawieraj$cy imiona studentów w kolumnie
A i warto'ci
w kolumnie
B. Zakres komórek B2:B20 ma nazw Dane. Komórka E2 (o nazwie Cel) zawiera
warto'#, która ma by# wyszukana w zakresie
Dane. Komórka E3 (o nazwie PrzesKol) zawiera
warto'# reprezentuj$c$ przesuni cie kolumny wzgl dem zakresu
Dane.
Rysunek 8.15. Arkusz ten demonstruje sposób wyszukiwania najbliNszej pasuj:cej warto'ci
Poni&sza formu(a znajduje w zakresie
Dane warto'# najbli&sz$ warto'ci docelowej i zwraca imi
odpowiadaj$cego jej studenta z kolumny
A (czyli kolumny o przesuni ciu –1). Formu(a ta zwraca
imi
Lech
(z którym skojarzona jest warto'# 8000 — najbli&sza szukanej warto'ci 8025).
271
RozdziaG 8. Funkcje wyszukiwania
=ADR.POHR(ADRES(WIERSZ(Dane)+PODAJ.POZYCJ-(MIN(MODU).LICZBY(Cel-Dane));
MODU).LICZBY(Cel-Dane);0)-1;NR.KOLUMNY(Dane)+PrzesKol))
Je'li w zakresie
Dane znajduj$ si dwie warto'ci tak samo bliskie warto'ci docelowej, formu(a
zwraca pierwsz$ na li'cie.
Warto'# w komórce o nazwie
PrzesKol mo&e by# ujemna (oznaczaj$c kolumn po lewej stro-
nie zakresu
Dane), dodatnia (oznaczaj$c kolumn po prawej stronie zakresu Dane) lub wyno-
si#
0
(oznaczaj$c rzeczywist$ najbli&sz$ warto'# w zakresie
Dane).
Aby zrozumie# zasad dzia(ania tej formu(y, nale&y zrozumie#, jak dzia(a funkcja
ADR.POHR
.
Pierwszym argumentem tej funkcji jest ci$g tekstowy w postaci adresu komórki (lub adresu
komórki zawieraj$cej ci$g tekstowy). W tym przypadku ci$g ten jest tworzony przez funkcj
ADRES
, która przyjmuje adres wiersza i kolumny i zwraca adres komórki.
Wyszukiwanie warto&ci
przy u@yciu interpolacji liniowej
Interpolacja to metoda wyznaczania brakuj$cej warto'ci przy u&yciu dost pnych warto'ci.
Ilustruje to rysunek 8.16. Kolumna
D zawiera list warto'ci (nazywa si x), a kolumna E od-
powiadaj$ce im warto'ci (nazywa si
y).
Rysunek 8.16. Arkusz ten demonstruje wyszukiwanie w tabeli za pomoc:
interpolacji liniowej
272
Cz2'3 II Stosowanie funkcji w formu0ach
Na arkuszu znajduje si te& wykres graficznie obrazuj$cy powi$zania pomi dzy zakresami
x i y.
Jak wida#, pomi dzy odpowiadaj$cymi sobie warto'ciami w tych zakresach wyst puj$ przy-
bli&one powi$zania liniowe — kiedy zwi ksza si warto'#
x, zwi ksza si te& warto'# y.
Zauwa&, &e warto'ci w zakresie
x nie s$ pe(nym zbiorem kolejnych liczb ca(kowitych.
Na przyk(ad brakuje warto'ci 3, 6, 7, 14, 17, 18 i 19.
Mo&emy utworzy# formu( wyszukuj$c$ warto'# w zakresie
x i zwracaj$c$ odpowiadaj$c$ jej
warto'# z zakresu
y. Co jednak zrobi#, je'li zechcemy znaleQ# warto'# z zakresu y dla braku-
j$cej warto'ci z zakresu
x? Zwyk(a formu(a wyszukuj$ca nie jest dobrym rozwi$zaniem,
poniewa& zwróci istniej$c$ warto'# z zakresu
y (a nie wyznaczon$ warto'# z tego zakresu).
Na przyk(ad poni&sza formu(a wyszukuje warto'#
3
i zwraca warto'#
18.00
(odpowiadaj$c$
warto'ci
2
w zakresie
x):
=WYSZUKAJ(3; x; y)
W takim przypadku najlepiej jest zastosowa# interpolacj . Innymi s(owy, bior$c pod uwag ,
&e warto'#
3
znajduje si w po(owie drogi pomi dzy warto'ciami
2
i
4
, chcemy, aby nasza
formu(a zwróci(a warto'#
21.00
— znajduj$c$ si w po(owie drogi pomi dzy odpowiadaj$-
cymi dwójce i czwórce warto'ciami
18.00
i
24.00
.
F
ORMUVY DO WYKONYWANIA INTERPOLACJI LINIOWEJ
Rysunek 8.17 przedstawia arkusz zawieraj$cy w kolumnie
B formu(y. Warto'# do wyszuka-
nia znajduje si w komórce
B1. Ostatnia formu(a, znajduj$ca si w komórce B16, zwraca
wynik. Je'li warto'# znajduj$ca si w komórce
B3 zostanie znaleziona w zakresie x, formu(a
zwróci odpowiadaj$c$ jej warto'# z zakresu
y. W przeciwnym przypadku formu(a znajduj$ca si
w komórce
B16 zwróci wyznaczon$ warto'# z zakresu y, uzyskan$ za pomoc$ interpolacji liniowej.
Rysunek 8.17. Kolumna B zawiera formuGy wyszukuj:ce warto'ci przy uNyciu
interpolacji liniowej
273
RozdziaG 8. Funkcje wyszukiwania
Bardzo wa&ne jest, aby warto'ci w zakresie
x by(y ustawione w rosn$cej kolejno'ci. Je'li
komórka
B1 zawiera warto'# mniejsz$ od najmniejszej warto'ci w zakresie x lub wi ksz$
od najwi kszej warto'ci w tym zakresie, formu(a zwraca b($d. Tabela 8.2 zawiera zestawienie
i opisy tych formu(.
TABELA 8.2. FORMUVY DO WYSZUKIWANIA PRZY ZASTOSOWANIU
INTERPOLACJI LINIOWEJ
Komórka Formu5a
Opis
B3
=WYSZUKAJ(B1;x;x)
Wykonuje zwykGe wyszukiwanie w zakresie x
i zwraca znalezion: warto'3.
B4
=B1=B3
Zwraca warto'3
PRAWDA
, je'li znaleziona warto'3
jest równa szukanej warto'ci.
B6
=PODAJ.POZYCJ-(B3;x;0)
Zwraca numer wiersza w zakresie x, który zawiera
pasuj:c: warto'3.
B7
=JE>ELI(B4;B6;B6+1)
Zwraca ten sam numer wiersza, co formuGa
w komórce B6, je'li zostanie znalezione dokGadne
dopasowanie. W przeciwnym przypadku dodaje
1 do wyniku z komórki B6.
B9
=INDEKS(x;B6)
Zwraca warto'3 z zakresu x odpowiadaj:c:
wierszowi o numerze z komórki B6.
B10
=INDEKS(x;B7)
Zwraca warto'3 z zakresu x odpowiadaj:c:
wierszowi o numerze z komórki B7.
B12
=WYSZUKAJ(B9;x;y)
Zwraca warto'3 z zakresu y, która odpowiada
warto'ci z zakresu x znajduj:cej si2 w komórce B9.
B13
=WYSZUKAJ(B10;x;y)
Zwraca warto'3 z zakresu y, która odpowiada
warto'ci z zakresu x znajduj:cej si2 w komórce B10.
B15
=JE>ELI(B4;0;
(B1-B3)/(B10-B9))
Oblicza wspóGczynnik dopasowania na podstawie
róNnicy pomi2dzy warto'ciami z zakresu x.
B16
=B12+((B13-B12)*B15)
Wyznacza warto'3 z zakresu y przy uNyciu
wspóGczynnika dopasowania z komórki B15.
V
cCZNE URYCIE FUNKCJI
WYSZUKAJ
I
REGLINW
Troch inna metoda, któr$ mo&na wybra# zamiast interpolacji liniowej do wyszukiwania,
polega na u&yciu funkcji
WYSZUKAJ
i
REGLINW
. Jedn$ z jej zalet jest to, &e wymaga u&ycia tylko
jednej formu(y (rysunek 8.18).
Wyszukiwarka
Podobne podstrony:
informatyka excel 2010 pl najlepsze sztuczki i chwyty vademecum walkenbacha john walkenbach ebook
informatyka excel 2010 pl rozwiazywanie problemow dla kazdego witold wrotek ebook
informatyka excel 2010 pl pierwsza pomoc bartosz gajda ebook
informatyka excel 2010 pl cwiczenia praktyczne dla bystrzakow colin banfield ebook
Excel 2010 PL Formuly ex21fo
Microsoft Excel 2010 PL Formuly i funkcje Akademia Excela e21fae
Microsoft Excel 2010 PL Formuly i funkcje Akademia Excela e21fae
Microsoft Excel 2010 PL Formuly i funkcje Akademia Excela e21fae
Microsoft Excel 2010 PL Formuly i funkcje Akademia Excela
Excel 2010 PL Formuly ex21fo
Excel 2010 PL Formuly ex21fo
Excel 2010 PL Formuly ex21fo
[PL] Excel Excel 2010 PL Formuly
Microsoft Excel 2010 PL Formuly i funkcje Akademia Excela e21fae 2
Microsoft Excel 2010 PL Formuly i funkcje Akademia Excela e21fae
Microsoft Excel 2010 PL Formuly i funkcje Akademia Excela 2
Excel 2010 PL Formuly 2
Excel 2010 PL Formuly ex21fo
więcej podobnych podstron