Idź do
• Spis treści
• Przykładowy rozdział
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
Excel 2010 PL.
Formuły
Autor:
Tłumaczenie: Łukasz Piwko
ISBN: 978-83-246-2883-4
Tytuł oryginału:
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 treci
Wstp
23
Co trzeba wiedzie
23
Co trzeba mie
23
Konwencje typograficzne
24
Konwencje dotyczce klawiatury
24
Konwencje myszy
25
Co oznaczaj ikony
26
Struktura ksiki
26
Cz I: Informacje podstawowe
27
Cz II: Stosowanie funkcji w formuach
27
Cz III: Formuy finansowe
27
Cz IV: Formuy tablicowe
27
Cz V: Róne techniki wykorzystania formu
27
Cz VI: Tworzenie niestandardowych funkcji arkusza
28
Dodatki
28
Jak korzysta z ksiki?
28
Informacje o przykadach
29
Informacje o dodatku Power Utility Pak
29
Komentarze
29
Cz I Informacje podstawowe
31
Rozdzia 1. Wstp do Excela
33
Historia Excela
34
Wszystko zaczo si od programu VisiCalc
34
Nadejcie Lotusa
34
Do gry wchodzi Microsoft
35
Wersje Excela
35
Koncepcja modelu obiektowego
39
Zasada dziaania skoroszytów
40
Arkusze
41
Arkusze wykresów
42
Arkusze makr i arkusze dialogowe
42
Interfejs uytkownika programu Excel
43
Nowy interfejs uytkownika
43
Wstka
43
Widok Backstage
46
Menu podrczne i minipasek narzdzi
47
Konfigurowanie interfejsu uytkownika
47
Tagi inteligentne
48
Okienko zada
49
Przecignij i upu
50
Skróty klawiaturowe
50
Dostosowywanie widoku na ekranie
51
8
Spis treci
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
Formuy i funkcje arkuszy
56
Obiekty na warstwie rysowania
56
Ksztaty
57
Ilustracje
57
Obiekty obrazów poczonych
57
Formanty
58
Wykresy
58
Wykresy przebiegu w czasie
59
Dostosowywanie Excela
60
Makra
60
Dodatki
60
Funkcje internetowe
60
Narzdzia do analizy
61
Dostp do baz danych
61
Konspekty
62
Zarzdzanie 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 hasem
66
Rozdzia 2. Podstawowe informacje na temat formu
67
Wprowadzanie i edycja formu
68
Elementy formuy
68
Wstawianie formuy
68
Wklejanie nazw
70
Spacje i zamania wiersza
70
Ograniczenia formu
71
Przykady formu
71
Edytowanie formu
72
Operatory uywane w formuach
73
Operatory odniesienia
74
Przykady formu z uyciem operatorów
74
Pierwszestwo operatorów
75
Zagniedanie nawiasów
77
Obliczanie wartoci formu
79
Odniesienia do komórek i zakresów
80
Tworzenie odwoa bezwzgldnych i mieszanych
80
Tworzenie odwoa do innych arkuszy lub skoroszytów
83
Robienie wiernej kopii formuy
83
Konwertowanie formu na wartoci
84
Ukrywanie formu
87
9
Spis treci
Bdy w formuach
88
Co robi z odwoaniami cyklicznymi
89
Szukanie wyniku
91
Przykad szukania wyniku
91
Szukanie wyniku — informacje dodatkowe
93
Rozdzia 3. Praca z nazwami
95
Co to jest nazwa
96
Zakres nazw
97
Odwoania do nazw
97
Odnoszenie si do nazw z innego skoroszytu
98
Konflikty nazw
98
Meneder 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 uyciu pola nazwy
103
Automatyczne tworzenie nazw
104
Nazywanie caych wierszy i kolumn
106
Nazwy tworzone przez Excela
106
Tworzenie nazw obejmujcych kilka arkuszy
107
Praca z nazwami komórek i zakresów
109
Tworzenie listy nazw
109
Uywanie nazw w formuach
110
Uywanie operatora przecicia z nazwami
111
Uywanie operatora zakresu z nazwami
113
Odwoywanie si do pojedynczej komórki w zakresie nazwanym obejmujcym kilka arkuszy
113
Wstawianie nazw do istniejcych formu
114
Automatyczne wstawianie nazw podczas tworzenia formuy
115
Usuwanie nazw
115
Nazwy z bdami
115
Przegldanie nazw zakresów
116
Stosowanie nazw w wykresach
117
Obsuga 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 wystpujce podczas kopiowania arkuszy
118
Problemy z nazwami przy usuwaniu arkuszy
120
Klucz do zrozumienia nazw
121
Nazywanie wartoci staych
122
Nazywanie staych tekstowych
123
Uywanie funkcji arkusza w nazwanych formuach
124
Uywanie odwoa do komórek i zakresów w formuach nazwanych
125
Uywanie formu nazwanych zawierajcych odwoania wzgldne
126
Zaawansowane techniki uywania nazw
130
Uywanie funkcji ADR.POR z zakresem nazwanym
130
Uycie funkcji ADR.POR do tworzenia zakresu nazwanego o staym adresie
131
Uywanie tablic w formuach nazwanych
131
Tworzenie dynamicznych formu nazwanych
133
10
Spis treci
Cz II Stosowanie funkcji w formuach
135
Rozdzia 4. Wprowadzenie do funkcji arkusza
137
Co to jest funkcja
137
Upraszczanie formu
138
Wykonywanie oblicze niemoliwych do wykonania w inny sposób
138
Przyspieszanie zada edycyjnych
139
Podejmowanie decyzji przez formu
139
Wicej na temat funkcji
140
Typy argumentów funkcji
140
Nazwy w roli argumentów
141
Cae kolumny i wiersze w roli argumentów
142
Wartoci literalne w roli argumentów
142
Wyraenia w roli argumentów
143
Funkcje w roli argumentów
143
Tablice w roli argumentów
143
Sposoby wstawiania funkcji do formu
144
Rczne 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 odwoa
151
Funkcje baz danych
151
Funkcje tekstowe
151
Funkcje logiczne
152
Funkcje informacyjne
152
Funkcje zdefiniowane przez uytkownika
152
Funkcje inynierskie
152
Funkcje moduowe
152
Funkcje zgodnoci
152
Inne kategorie funkcji
153
Rozdzia 5. Manipulowanie tekstem
155
Kilka sów na temat tekstu
155
Ile znaków moe pomieci 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 cigi s identyczne
161
czenie dwóch lub wikszej liczby komórek
161
Wywietlanie sformatowanych wartoci jako tekst
162
Wywietlanie wartoci walutowych jako tekst
164
Usuwanie niepotrzebnych spacji i niedrukowalnych znaków
164
Liczenie znaków w cigu
164
Powtarzanie znaku lub cigu
165
Tworzenie histogramu tekstowego
165
Dopenianie liczby
166
11
Spis treci
Zmiana wielkoci liter
168
Wydobywanie znaków z cigu
169
Podmienianie tekstu innym tekstem
169
Znajdowanie i szukanie w cigu
170
Znajdowanie i zamienianie cigów
171
Zaawansowane formuy tekstowe
171
Zliczanie okrelonych znaków w komórce
172
Zliczanie wystpie podcigu w komórce
172
Usuwanie znaków minusa z koca
172
Sprawdzanie numeru litery kolumny po jej numerze
173
Wydobywanie nazwy pliku ze cieki
173
Wydobywanie pierwszego wyrazu z cigu
173
Wydobywanie ostatniego wyrazu z cigu
174
Wydobywanie wszystkiego poza pierwszym wyrazem w cigu
174
Wydobywanie pierwszych imion, drugich imion i nazwisk
175
Usuwanie tytuu sprzed imienia lub nazwiska
177
Zliczanie sów w komórce
177
Rozdzia 6. Funkcje daty i czasu
179
Jak Excel obsuguje 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
Wywietlanie aktualnej daty
188
Wywietlanie dowolnej daty
190
Generowanie serii dat
191
Konwersja cigów tekstowych na daty
192
Obliczanie liczby dni dzielcych dwie daty
193
Obliczanie liczby dni powszednich midzy dwiema datami
193
Obliczanie daty, biorc pod uwag tylko dni robocze
195
Obliczanie liczby lat dzielcych dwie daty
195
Obliczanie wieku osób
197
Okrelanie dnia roku
197
Okrelanie dnia tygodnia
198
Okrelanie daty ostatniej niedzieli
198
Okrelanie daty pierwszego wystpienia dnia tygodnia po okrelonej dacie
198
Okrelanie n-tego wystpienia dnia tygodnia w miesicu
199
Zliczanie wystpie dnia tygodnia
199
Obliczanie dat wit
201
Okrelanie daty ostatniego dnia miesica
203
Sprawdzanie, czy dany rok jest przestpny
204
Sprawdzanie kwartau roku
204
Konwersja roku na liczby rzymskie
204
Funkcje czasu
205
Wywietlanie biecego czasu
205
Wywietlanie dowolnego czasu
206
Sumowanie czasów powyej 24 godzin
207
Obliczanie rónicy midzy dwiema wartociami czasu
209
Konwersja z czasu wojskowego
211
Konwersja godzin, minut i sekund w zapisie dziesitnym na wartoci czasu
211
12
Spis treci
Dodawanie godzin, minut i sekund do wartoci czasu
212
Konwersja pomidzy strefami czasowymi
213
Zaokrglanie wartoci czasu
214
Praca z wartociami niebdcymi 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 formuy liczce
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 niezawierajcych tekstu
223
Zliczanie komórek tekstowych
224
Zliczanie wartoci logicznych
224
Zliczanie wartoci bdów w zakresie
224
Zaawansowane formuy liczce
225
Liczenie komórek przy uyciu funkcji LICZ.JEELI
225
Zliczanie komórek speniajcych wiele kryteriów
225
Zliczanie liczby wystpie najczciej pojawiajcego si wpisu
229
Zliczanie wystpie okrelonego tekstu
231
Liczenie unikatowych wartoci
233
Tworzenie rozkadu czstoci
234
Formuy sumujce
240
Sumowanie wszystkich komórek w zakresie
240
Obliczanie narastajcej sumy
242
Sumowanie okrelonej liczby najwikszych wartoci
243
Sumowanie warunkowe z jednym kryterium
244
Sumowanie tylko wartoci ujemnych
245
Sumowanie wartoci w oparciu o inny zakres
245
Sumowanie wartoci w oparciu o porównanie tekstowe
246
Sumowanie wartoci w oparciu o porównanie daty
246
Sumowanie warunkowe przy zastosowaniu wielu kryteriów
247
Uycie kryteriów „i”
248
Uycie kryteriów „lub”
249
Uycie kryteriów „i” oraz „lub”
249
Rozdzia 8. Funkcje wyszukiwania
251
Co to jest formua wyszukiwania
251
Funkcje zwizane z wyszukiwaniem
253
Podstawowe formuy wyszukiwania
253
Funkcja WYSZUKAJ.PIONOWO
254
Funkcja WYSZUKAJ.POZIOMO
255
Funkcja WYSZUKAJ
256
czne uycie funkcji PODAJ.POZYCJ i INDEKS
258
Wyspecjalizowane formuy wyszukujce
259
Wyszukiwanie dokadnej wartoci
260
Wyszukiwanie wartoci w lew stron
262
Wyszukiwanie z rozrónianiem maych i wielkich liter
263
Wybieranie sporód wielu tabel
263
Okrelanie ocen na podstawie wyników testu
264
Obliczanie redniej ocen
265
Wyszukiwanie w dwie strony
266
13
Spis treci
Wyszukiwanie dwukolumnowe
268
Sprawdzanie adresu wartoci w zakresie
269
Wyszukiwanie wartoci przy uyciu najbliszego dopasowania
270
Wyszukiwanie wartoci przy uyciu interpolacji liniowej
271
Rozdzia 9. Tabele i bazy danych arkusza
275
Tabele i terminologia
276
Przykad bazy danych arkusza
276
Przykad tabeli
277
Zastosowania baz danych arkusza i tabel
278
Praca z tabelami
279
Tworzenie tabeli
281
Zmiana wygldu 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 powtarzajcych si wierszy z tabeli
287
Sortowanie i filtrowanie tabeli
288
Praca z wierszem sumy
292
Stosowanie formu w tabelach
295
Odwoywanie 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
Okrelanie kryteriów filtru zaawansowanego
306
Okrelanie pojedynczego kryterium
306
Okrelanie wielu kryteriów
309
Okrelanie kryteriów utworzonych w wyniku uycia formuy
312
Funkcje bazy danych
313
Wstawianie sum czciowych
315
Rozdzia 10. Róne obliczenia
319
Konwersja jednostek
319
Rozwizywanie trójktów prostoktnych
321
Obliczanie pola powierzchni, obwodu i objtoci
324
Obliczanie pola powierzchni i obwodu kwadratu
324
Obliczanie pola powierzchni i obwodu prostokta
324
Obliczanie pola powierzchni i obwodu koa
325
Obliczanie pola powierzchni trapezu
325
Obliczanie pola powierzchni trójkta
325
Obliczanie pola powierzchni i objtoci kuli
325
Obliczanie pola powierzchni i objtoci szecianu
326
Obliczanie pola powierzchni i objtoci stoka
326
Obliczanie objtoci walca
326
Obliczanie objtoci ostrosupa
327
Rozwizywanie ukadów równa
327
Zaokrglanie liczb
328
Podstawowe formuy zaokrglajce
329
Zaokrglanie do najbliszej wielokrotnoci
330
Zaokrglanie wartoci walutowych
330
14
Spis treci
Praca z uamkami dolarów
331
Stosowanie funkcji ZAOKR.DO.CAK i LICZBA.CAK
332
Zaokrglanie do parzystej lub nieparzystej liczby cakowitej
333
Zaokrglanie do n cyfr znaczcych
333
Cz III Formuy finansowe
335
Rozdzia 11. Formuy kredytów i inwestycji
337
Pojcia finansowe
338
Warto pienidza w czasie
338
Wpywy i patnoci
338
Dopasowywanie okresów czasu
339
Wyznaczanie terminu pierwszej patnoci
339
Podstawowe funkcje finansowe
340
Obliczanie wartoci biecej
340
Obliczanie przyszej wartoci
344
Obliczanie patnoci
347
Obliczanie stóp
349
Obliczanie liczby rat
351
Obliczanie skadników patnoci
353
Funkcje IPMT i PPMT
353
Funkcje CUMIPMT i CUMPRINC
355
Konwersja stóp procentowych
356
Metody przedstawiania stóp procentowych
356
Formuy konwersji
356
Ograniczenia funkcji finansowych
357
Odroczony pocztek serii regularnych patnoci
358
Szacowanie serii zmiennych patnoci
359
Obliczenia dotyczce obligacji
360
Wyznaczanie ceny obligacji
360
Obliczanie rentownoci
362
Rozdzia 12. Formuy dyskontowe i amortyzacji
363
Funkcja NPV
364
Definicja funkcji NPV
364
Przykady uycia funkcji NPV
366
Obliczanie kwot nagromadzonych za pomoc funkcji NPV
372
Funkcja IRR — stosowanie
374
Stopa zwrotu
375
Geometryczne wskaniki przyrostu
376
Sprawdzanie wyników
377
Kilka stóp funkcji IRR i MIRR
378
Kilka wewntrznych stóp zwrotu
378
Rozdzielanie przepywów
380
Uycie sald zamiast przepywów
381
Nieregularne przepywy rodków
382
Warto bieca netto
382
Wewntrzna stopa zwrotu
383
Funkcja FVSCHEDULE
384
Obliczanie zwrotu w skali roku
384
Obliczanie amortyzacji
385
15
Spis treci
Rozdzia 13. Harmonogramy finansowe
389
Tworzenie harmonogramów finansowych
389
Tworzenie harmonogramów amortyzacji
390
Prosty harmonogram amortyzacji
390
Dynamiczny harmonogram amortyzacji
393
Tabele patnoci i odsetek
397
Obliczenia dotyczce karty kredytowej
399
Zestawianie opcji poyczek 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 wskaników
409
Tworzenie indeksów
412
Cz IV Formuy tablicowe
415
Rozdzia 14. Wprowadzenie do tablic
417
Wprowadzenie do formu tablicowych
417
Wielokomórkowa formua tablicowa
418
Jednokomórkowa formua tablicowa
419
Tworzenie staej tablicowej
420
Elementy staej tablicowej
421
Wymiary tablicy — informacje
422
Jednowymiarowe tablice poziome
422
Jednowymiarowe tablice pionowe
422
Tablice dwuwymiarowe
423
Nadawanie nazw staym tablicowym
424
Praca z formuami tablicowymi
425
Wprowadzanie formuy tablicowej
426
Zaznaczanie zakresu formuy tablicowej
426
Edycja formuy tablicowej
426
Powikszanie i zmniejszanie wielokomórkowych formu tablicowych
428
Stosowanie wielokomórkowych formu tablicowych
429
Tworzenie tablicy z wartoci w zakresie
429
Tworzenie staej tablicowej z wartoci w zakresie
429
Wykonywanie dziaa na tablicach
430
Uywanie funkcji z tablicami
431
Transponowanie tablicy
432
Generowanie tablicy kolejnych liczb cakowitych
433
Jednokomórkowe formuy tablicowe
434
Liczenie znaków w zakresie
434
Sumowanie trzech najmniejszych wartoci w zakresie
435
Zliczanie komórek tekstowych w zakresie
436
Pozbywanie si formu porednich
438
Zastosowanie tablicy zamiast adresu zakresu
440
Rozdzia 15. Magia formu tablicowych
441
Stosowanie jednokomórkowych formu tablicowych
441
Sumowanie zakresu zawierajcego bdy
442
Zliczanie bdów wartoci w zakresie komórek
443
Sumowanie n najwikszych wartoci w zakresie
444
Obliczanie redniej z pominiciem zer
444
16
Spis treci
Sprawdzanie wystpowania okrelonej wartoci w zakresie
446
Zliczanie liczby rónic w dwóch zakresach
447
Zwracanie lokalizacji maksymalnej wartoci w zakresie
448
Odszukiwanie wiersza n-tego wystpienia wartoci w zakresie
448
Zwracanie najduszego tekstu w zakresie
449
Sprawdzanie, czy zakres zawiera poprawne wartoci
449
Sumowanie cyfr liczby cakowitej
450
Sumowanie wartoci zaokrglonych
451
Sumowanie wszystkich n-tych wartoci w zakresie
452
Usuwanie nienumerycznych znaków z acucha
453
Odszukiwanie najbliszej wartoci w zakresie
454
Zwracanie ostatniej wartoci w kolumnie
455
Zwracanie ostatniej wartoci w wierszu
456
Szeregowanie danych przy uyciu formuy tablicowej
456
Stosowanie wielokomórkowych formu tablicowych
457
Zwracanie wycznie dodatnich wartoci w zakresie
458
Zwracanie niepustych komórek z zakresu
459
Odwracanie kolejnoci komórek w zakresie
459
Dynamiczne sortowanie wartoci w zakresie
460
Zwracanie listy unikalnych elementów zakresu
461
Wywietlanie kalendarza w zakresie komórek
462
Cz V Róne techniki wykorzystania formu
465
Rozdzia 16. Zamierzone odwoania cykliczne
467
Czym s odwoania cykliczne?
467
Korygowanie niezamierzonych odwoa cyklicznych
468
Istota porednich odwoa cyklicznych
469
Zamierzone odwoania cykliczne
470
W jaki sposób Excel okrela ustawienia oblicze i iteracji
473
Przykady odwoa cyklicznych
474
Generowanie losowych, unikalnych liczb cakowitych
474
Rozwizywanie równa rekursywnych
475
Rozwizywanie ukadów równa przy uyciu odwoa cyklicznych
477
Animowanie wykresów przy uyciu iteracji
479
Potencjalne problemy z zamierzonymi odwoaniami cyklicznymi
480
Rozdzia 17. Techniki tworzenia wykresów
481
Dziaanie formuy SERIE
482
Uywanie nazw w formule SERIE
484
Oddzielanie serii danych na wykresie od zakresu danych
484
Tworzenie powiza do komórek
487
Tworzenie poczenia z tytuem wykresu
487
Tworzenie powiza z tytuami osi
488
Tworzenie powiza z etykietami danych
488
Tworzenie powiza tekstowych
488
Dodawanie obrazu poczonego do wykresu
489
Przykady wykresów
489
Wykres postpów w deniu do celu
489
Tworzenie wykresu w ksztacie miernika
491
Warunkowe wywietlanie kolorów na wykresie kolumnowym
492
Tworzenie histogramu porównawczego
493
Tworzenie wykresów Gantta
495
17
Spis treci
Tworzenie wykresu giedowego
497
Krelenie co n-tego punktu danych
499
Krelenie n ostatnich punktów danych
501
Zaznaczanie serii danych za pomoc okna kombi
502
Tworzenie wykresów funkcji matematycznych
504
Krelenie okrgu
508
Wykres w ksztacie zegara
510
Tworzenie wspaniaych 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
Przykad tabeli przestawnej
524
Dane odpowiednie dla tabeli przestawnej
526
Tworzenie tabeli przestawnej
529
Wskazywanie danych
530
Wyznaczanie lokalizacji tabeli przestawnej
530
Definiowanie ukadu tabeli przestawnej
531
Formatowanie tabeli przestawnej
534
Modyfikowanie tabeli przestawnej
535
Wicej przykadó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
Przykad grupowania rcznego
545
Przegldanie zgrupowanych danych
546
Przykady grupowania automatycznego
547
Tworzenie rozkadu liczebnoci
551
Tworzenie pól i elementów obliczeniowych
553
Tworzenie pola obliczeniowego
555
Wstawianie elementu obliczeniowego
557
Filtrowanie tabel przestawnych przy uyciu fragmentatorów
560
Odwoania do komórek w tabeli przestawnej
562
Jeszcze jeden przykad tabeli przestawnej
564
Tworzenie raportu tabeli przestawnej
567
Rozdzia 19. Formatowanie warunkowe i sprawdzanie poprawnoci danych
569
Formatowanie warunkowe
569
Wybieranie formatowania warunkowego
571
Formaty warunkowe wykorzystujce grafik
574
Stosowanie formatów warunkowych
583
Tworzenie regu opartych na formuach
585
Sprawdzanie poprawnoci danych
595
Definiowanie kryteriów sprawdzania poprawnoci danych
596
Typy kryteriów sprawdzania poprawnoci danych, jakich moesz uy
597
Tworzenie list rozwijanych
599
Stosowanie formu w reguach sprawdzania poprawnoci danych
600
18
Spis treci
Rozdzia 20. Tworzenie megaformu
605
Czym jest megaformua?
605
Tworzenie megaformuy — prosty przykad
606
Przykady megaformu
609
Usuwanie drugich imion przy uyciu megaformuy
609
Uycie megaformuy zwracajcej pozycj ostatniego znaku spacji w acuchu
613
Zastosowanie megaformuy do sprawdzania poprawnoci numerów kart kredytowych
617
Generowanie nazwisk losowych
622
Zalety i wady megaformu
623
Rozdzia 21. Narzdzia i metody usuwania bdów w formuach
625
Debugowanie formu?
625
Problemy z formuami i ich rozwizania
626
Niedopasowanie nawiasów
627
Komórki wypenione symbolami krzyyka
629
Puste komórki, które wcale nie s puste
629
Nadmiarowe znaki spacji
630
Formuy zwracajce bd
630
Problemy z odwoaniami wzgldnymi i bezwzgldnymi
635
Problemy z pierwszestwem operatorów
635
Formuy nie s obliczane
637
Wartoci rzeczywiste i wywietlane
637
Bdy liczb zmiennoprzecinkowych
638
Bdy nieistniejcych czy
639
Bdy wartoci logicznych
640
Bdy odwoa cyklicznych
641
Narzdzia inspekcyjne w Excelu
641
Identyfikowanie komórek okrelonego typu
641
Przegldanie formu
642
ledzenie relacji pomidzy komórkami
644
ledzenie wartoci bdów
646
Naprawianie bdów odwoa cyklicznych
646
Funkcja sprawdzania bdów w tle
647
Korzystanie z funkcji Szacowanie formuy
649
Cz VI Tworzenie niestandardowych funkcji arkusza
651
Rozdzia 22. Wprowadzenie do VBA
653
Kilka sów o VBA
653
Wywietlanie karty Deweloper
654
O bezpieczestwie makr
655
Zapisywanie skoroszytów zawierajcych 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 treci
Rozdzia 23. Podstawy tworzenia funkcji niestandardowych
669
Po co tworzy si funkcje niestandardowe?
670
Wprowadzajcy przykad funkcji VBA
670
O procedurach Function
673
Deklarowanie funkcji
673
Wybór nazwy dla funkcji
674
Uywanie funkcji w formuach
674
Uycie argumentów w funkcjach
676
Korzystanie z okna dialogowego Wstawianie funkcji
676
Dodawanie opisu funkcji
677
Okrelanie kategorii funkcji
678
Dodawanie opisów argumentów
679
Testowanie i debugowanie funkcji
681
Uycie instrukcji VBA MsgBox
682
Uycie instrukcji Debug.Print w kodzie
684
Wywoywanie funkcji z procedury Sub
685
Ustawianie punktu kontrolnego w funkcji
688
Tworzenie dodatków
689
Rozdzia 24. Koncepcje programowania w VBA
691
Wprowadzajcy przykad procedury Function
692
Umieszczanie komentarzy wewntrz kodu
694
Uycie zmiennych, typów danych i staych
695
Definiowanie typów danych
695
Deklarowanie zmiennych
697
Uycie staych
698
Uycie acuchów
700
Uycie dat
700
Uycie wyrae przypisania
701
Uycie tablic
702
Deklarowanie tablic
703
Deklarowanie tablic wielowymiarowych
703
Uycie wbudowanych funkcji VBA
704
Sterowanie wykonaniem
705
Konstrukcja If-Then
706
Konstrukcja Select Case
708
Bloki zaptlajce
709
Instrukcja On Error
713
Uycie zakresów
715
Konstrukcja For Each-Next
715
Odwoania do zakresów
716
Kilka uytecznych waciwoci zakresów
719
Sowo kluczowe Set
723
Funkcja Intersect
723
Funkcja Union
724
Waciwo UsedRange
724
20
Spis treci
Rozdzia 25. Przykady niestandardowych funkcji VBA
727
Proste funkcje
728
Czy komórka zawiera formu?
728
Zwracanie formuy 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 sprzeday
741
Funkcja dla prostej struktury prowizji
742
Funkcja dla bardziej zoonej struktury prowizji
743
Funkcje do manipulowania tekstem
744
Odwracanie acucha
744
Mieszanie tekstu
744
Zwracanie akronimu
745
Czy tekst jest zgodny z wzorcem?
746
Czy komórka zawiera okrelone sowo?
747
Czy komórka zawiera tekst?
748
Wyodrbnianie n-tego elementu acucha
748
Sowny zapis liczb
749
Funkcje zliczajce
750
Zliczanie komórek zgodnych z wzorcem
750
Zliczanie arkuszy w skoroszycie
751
Zliczanie wyrazów w zakresie
751
Zliczanie kolorów
752
Funkcje operujce na datach
752
Obliczanie daty nastpnego poniedziaku
753
Obliczanie daty nastpnego dnia tygodnia
753
Który tydzie miesica?
754
Obsuga 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 wartoci z wielu arkuszy
758
Funkcja SHEETOFFSET
759
Zaawansowane techniki tworzenia funkcji
760
Zwracanie wartoci bdu
760
Zwracanie tablicy przez funkcj
762
Zwracanie tablicy niepowtarzalnych, losowych liczb cakowitych
763
Zwracanie tablicy losowych liczb cakowitych z podanego zakresu
765
Stosowanie argumentów opcjonalnych
767
Pobieranie nieokrelonej liczby argumentów
768
21
Spis treci
Dodatki
Dodatek A Wykaz funkcji Excela
775
Dodatek B Niestandardowe formaty liczbowe
793
O formatowaniu liczb
793
Automatyczne formatowanie liczb
794
Formatowanie liczb przy uyciu Wstki
795
Formatowanie liczb przy uyciu skrótów klawiaturowych
795
Formatowanie liczb przy uyciu okna dialogowego Formatowanie komórek
796
Tworzenie niestandardowego formatu liczbowego
797
Elementy acucha formatu liczbowego
799
Kody niestandardowego formatu liczbowego
800
Przykady niestandardowych formatów liczbowych
802
Skalowanie wartoci
803
Ukrywanie zer
806
Wywietlanie zer poprzedzajcych
807
Wywietlanie uamków
807
Wywietlanie N/D zamiast tekstu
808
Wywietlanie tekstu w cudzysowach
808
Powielanie wpisu w komórce
808
Wywietlanie minusa po prawej stronie
809
Warunkowe formatowanie liczb
809
Wywietlanie wartoci w kolorach
810
Formatowanie dat i godzin
811
Wywietlanie tekstu z liczbami
811
Wywietlanie kresek zamiast zer
812
Uycie symboli specjalnych
812
Ukrywanie poszczególnych typów informacji
813
Wypenianie komórek powtarzajcymi si znakami
813
Wywietlanie kropek wiodcych
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
Dostp do grup dyskusyjnych przy uyciu czytnika
817
Dostp do grup dyskusyjnych przy uyciu przegldarki 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 treci
Dodatek D Przykady doczone do ksiki
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
Rozdzia
8
Funkcje wyszukiwania
W TYM ROZDZIALE:
x Wprowadzenie do formu wyszukujcych wartoci w tabelach
x Przegld funkcji arkusza uywanych do wyszukiwania
x Podstawowe formuy wyszukujce
x Wyspecjalizowane formuy wyszukujce
W rozdziale tym opisuj róne techniki wyszukiwania wartoci w tabeli. Do tego celu w progra-
mie Excel zaprojektowano trzy funkcje (
WYSZUKAJ
,
WYSZUKAJ.PIONOWO
i
WYSZUKAJ.POZIOMO
),
ale moe si okaza, e to za mao. Zawarem tu wiele przykadów formu wyszukujcych,
cznie z alternatywnymi technikami znacznie wykraczajcymi poza zwyke funkcje wyszu-
kiwania Excela.
Co to jest formua wyszukiwania
Formua wyszukiwania zwraca warto z tabeli (w zakresie), szukajc innej wartoci.
Z analogiczn sytuacj mamy do czynienia, gdy szukamy numeru w ksice telefonicznej.
Aby znale numer telefoniczny jakiej osoby, najpierw znajdujemy jej nazwisko, a dopiero
potem sprawdzamy numer.
252
Cz II Stosowanie funkcji w formuach
U
WAGA
Pod pojciem tabela rozumiem prostoktny zakres danych. Nie musi to by
„prawdziwa” tabela, taka jak te, które tworzy si za pomoc opcji
Wstawianie/Tabele/Tabela.
Rysunek 8.1 przedstawia prosty arkusz, na którym uyto kilku funkcji wyszukiwania. Zawiera
on tabel informacji o pracownikach (o nazwie DanePracowników) zaczynajc si w wierszu 7.
Jeli wpiszemy nazwisko w komórce B2, formuy wyszukiwania w komórkach C2:F2 znajd
pasujce informacje w tabeli. W poniszych formuach uyto funkcji
WYSZUKAJ.PIONOWO
.
Komórka
Formua
C2
=WYSZUKAJ.PIONOWO(B2; DanePracowników;2; FASZ)
D2
=WYSZUKAJ.PIONOWO(B2; DanePracowników;3; FASZ)
E2
=WYSZUKAJ.PIONOWO(B2; DanePracowników;4; FASZ)
F2
=WYSZUKAJ.PIONOWO(B2; DanePracowników;5; FASZ)
Rysunek 8.1. Formuy wyszukiwania w wierszu 2. szukaj informacji o pracowniku,
którego nazwisko wpisano w komórce B2
Ten konkretny przykad zwraca informacje z zakresu DanePracowników przy uyciu czterech
formu. W wielu sytuacjach potrzebna jest tylko jedna informacja z tabeli. Wtedy naley
uy tylko jednej formuy.
253
Rozdzia 8. Funkcje wyszukiwania
Funkcje zwizane
z wyszukiwaniem
W Excelu jest dostpnych kilka funkcji przydatnych podczas pisania formu wyszukiwania
danych w tabeli. Tabela 8.1 zawiera ich zestawienie i krótkie opisy.
TABELA 8.1. FUNKCJE UYWANE W FORMUACH WYSZUKIWANIA
Funkcja
Opis
INDEKS
Zwraca warto (lub odwoanie do wartoci) z tabeli lub zakresu.
PODAJ.POZYCJ
Zwraca wzgldne pooenie elementu w zakresie, które pasuje
do podanej wartoci.
PRZESUNICIE
Zwraca odwoanie do zakresu przesunite o okrelon liczb wierszy
i kolumn wzgldem jakiej komórki lub jakiego zakresu komórek.
WYBIERZ
Zwraca okrelon warto z listy wartoci (do 29) podanych jako argumenty.
WYSZUKAJ
Zwraca warto z jednowierszowego lub jednokolumnowego zakresu.
Podobnie dziaa funkcja
WYSZUKAJ.PIONOWO
, ale zwraca tylko wartoci
z ostatniej kolumny w zakresie.
WYSZUKAJ.PIONOWO
Wyszukiwanie pionowe. Wyszukuje wartoci w pierwszej kolumnie
tabeli i zwraca warto znajdujc si w tym samym wierszu
w okrelonej kolumnie w tabeli.
WYSZUKAJ.POZIOMO
Wyszukiwanie poziome. Szuka wartoci w górnym wierszu tabeli
i zwraca warto znajdujc si w tej samej kolumnie w okrelonym
wierszu w tabeli.
W przykadach prezentowanych w tym rozdziale uywane s funkcje z tabeli 8.1.
Podstawowe
formuy wyszukiwania
Za pomoc podstawowych funkcji wyszukiwania mona przeszuka kolumn lub wiersz
w celu znalezienia pewnej wartoci pozwalajcej znale inn warto. W Excelu dostpne s
trzy podstawowe funkcje wyszukiwania:
WYSZUKAJ.POZIOMO
,
WYSZUKAJ.PIONOWO
i
WYSZUKAJ
.
Funkcji
PODAJ.POZYCJ
i
INDEKS
zawsze uywa si razem. Zwracaj one adres lub adres
wzgldny komórki zawierajcej poszukiwan warto.
254
Cz II Stosowanie funkcji w formuach
N
A
FTP
Przykady prezentowane w tym podrozdziale mona znale w pliku podstawowe
formuy wyszukiwania.xlsx, który znajduje si 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. Skadnia tej funkcji jest nastpujca:
SZUKAJ.PIONOWO(szukana_warto; tablica; numer_kolumny; kolumna)
Poniej znajduje si opis argumentów funkcji
WYSZUKAJ.PIONOWO
:
x
szukana_warto
— warto, która ma by wyszukana w pierwszej kolumnie
tablicy.
x
tablica
— zakres zawierajcy tablic do przeszukania.
x
numer_kolumny
— numer kolumny w tablicy, z której zwracana jest pasujca
warto.
x
kolumna
— opcjonalny. Jeli ma warto
PRAWDA
lub nie jest podany, funkcja zwraca
dopasowanie przyblione (jeli nie zostanie znalezione dokadne dopasowanie,
funkcja zwraca nastpn najwiksz warto mniejsz od szukanej wartoci).
Jeli ma warto
FASZ
, funkcja poszukuje dokadnego dopasowania. Jeli takiego
nie znajdzie, zwróci bd
#N/D
.
U
WAGA
Jeli argument
kolumna
ma warto
PRAWDA
lub zosta pominity, pierwsza kolumna
przeszukiwanej tabeli musi by posortowana w porzdku rosncym. Jeli warto
szukana jest mniejsza ni najmniejsza warto w pierwszej kolumnie tabeli tablicy,
funkcja
WYSZUKAJ.PIONOWO
zwróci bd
#N/D
. Jeli argument
kolumna
ma warto
FASZ
, pierwsza kolumna przeszukiwanej tabeli nie musi by posortowana
w rosncym porzdku. Jeli dokadne dopasowanie nie zostanie znalezione,
funkcja zwraca bd
#N/D
.
W
SKAZÓWKA
Jeli argument
szukana_warto
jest typu tekstowego (a czwarty argument,
kolumna
, ma warto
FASZ
), mona zastosowa symbole wieloznaczne
*
i
?
.
Gwiazdka dopasowuje dowoln grup znaków, a znak zapytania dowolny
jeden znak.
255
Rozdzia 8. Funkcje wyszukiwania
Klasyczny przykad formuy wyszukujcej ma zwizek z rozkadem wysokoci stopy opro-
centowania podatku dochodowego (rysunek 8.2). Pokazuje on stopy procentowe podatku
dochodowego dla rónych wysokoci zarobków. Ponisza formua (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
, formua zwraca odpowiedni warto z trzeciej kolumny tabeli.
Zauwa, e nie jest wymagane dokadne dopasowanie. Jeli w pierwszej kolumnie tabeli nie
zostanie znalezione dokadne dopasowanie, funkcja wykorzystuje nastpn najwiksz warto
mniejsz od wyszukiwanej wartoci. Innymi sowy, funkcja uywa wiersza zawierajcego
warto wiksz od wyszukiwanej wartoci lub jej równ, ale mniejsz od wartoci w nastp-
nym wierszu. W przypadku tabeli stóp oprocentowania jest to dokadnie takie dziaanie,
jakiego chcemy.
Funkcja WYSZUKAJ.POZIOMO
Funkcja
WYSZUKAJ.POZIOMO
dziaa dokadnie tak samo jak funkcja
WYSZUKAJ.PIONOWO
z tym
wyjtkiem, e tabela jest uporzdkowana poziomo, a nie pionowo. Szuka wartoci w pierw-
szym wierszu tabeli i zwraca odpowiadajc jej warto znajdujc si w okrelonym wierszu
w tabeli.
Skadnia tej funkcji jest nastpujca:
WYSZUKAJ.POZIOMO(szukana_warto; tabela_tablica; numer_indeksu_wiersza;
przeszukiwany_zakres)
Oto opis argumentów funkcji
WYSZUKAJ.POZIOMO
:
x
szukana_warto
— warto, która ma by wyszukana w pierwszym wierszu
przeszukiwanej tabeli.
256
Cz II Stosowanie funkcji w formuach
x
tabela_tablica
— zakres zawierajcy przeszukiwan tabel.
x
numer_indeksu_wiersza
— numer wiersza w tabeli, z którego ma by zwrócona
pasujca warto.
x
przeszukiwany_zakres
— opcjonalny. Jeli ma warto
PRAWDA
lub zostanie
pominity, zwracane jest dopasowanie przyblione (jeli dokadne dopasowanie
nie zostanie znalezione, zwracana jest nastpna najwiksza warto mniejsza od
szukanej wartoci). Jeli ma warto
FASZ
, funkcja szuka dokadnego dopasowania.
Jeli go nie znajdzie, zwracany jest bd
#N/D
.
W
SKAZÓWKA
Jeli argument
szukana_warto
jest tekstem, moe zawiera symbole wieloznaczne
*
i
?
. Gwiazdka dopasowuje dowoln liczb znaków, a znak zapytania jeden znak.
Rysunek 8.3 przedstawia przykad ze stopami oprocentowania z poziom tabel do przeszu-
kiwania (w zakresie E1:J3). Formua w komórce B3 to:
WYSZUKAJ.POZIOMO(B2; E1:J3; 3)
Rysunek 8.3. Uycie funkcji WYSZUKAJ.POZIOMO do znalezienia stopy procentowej
Funkcja WYSZUKAJ
Skadnia funkcji
WYSZUKAJ
jest nastpujca:
WYSZUKAJ(szukana_warto; przeszukiwany_wektor; wektor_wynikowy)
Opis argumentów funkcji
WYSZUKAJ
:
x
szukana_warto
— warto, która ma by wyszukana w przeszukiwanym
wektorze.
x
przeszukiwany_wektor
— skadajcy si z jednej kolumny lub jednego wiersza
zakres zawierajcy wartoci do wyszukania. Musz one by uporzdkowane
w kolejnoci rosncej.
x
wektor_wynikowy
— skadajcy si z jednej kolumny lub jednego wiersza zakres
zawierajcy wartoci, które maj by zwrócone. Musi mie taki sam rozmiar jak
przeszukiwany wektor.
257
Rozdzia 8. Funkcje wyszukiwania
Funkcja
WYSZUKAJ
przeszukuje jednokolumnowy lub jednowierszowy zakres (
przeszukiwany_wektor
)
w celu znalezienia wartoci (
szukana_warto
) i zwraca warto o takim samym pooeniu
w drugim jednowierszowym lub jednokolumnowym zakresie (
wektor_wynikowy
).
O
STRZEENIE
Wartoci w przeszukiwanym wektorze musz by posortowane rosnco.
Jeli szukana warto jest mniejsza ni najmniejsza warto w przeszukiwanym
wektorze, funkcja
WYSZUKAJ
zwraca bd
#N/D
.
U
WAGA
W pomocy jest te mowa o tablicowej skadni funkcji
WYSZUKAJ
. Ta alternatywna
skadnia zostaa dodana ze wzgldu na zachowanie zgodnoci z innymi arkuszami
kalkulacyjnymi. Z reguy zamiast skadni tablicowej mona uywa funkcji
WYSZUKAJ.POZIOMO
i
WYSZUKAJ.PIONOWO
.
Na rysunku 8.4 ponownie widzimy tabel stóp podatkowych. Tym razem formua w komór-
ce B3 uywa funkcji
WYSZUKAJ
do zwrócenia odpowiedniej stopy podatkowej. Formua ta jest
nastpujca:
=WYSZUKAJ(B2;D2:D7;G4:G9)
Rysunek 8.4. Uycie funkcji WYSZUKAJ do znalezienia stopy podatkowej
O
STRZEENIE
Jeli wartoci w pierwszej kolumnie nie s posortowane w rosncej kolejnoci,
funkcja
WYSZUKAJ
moe zwróci nieprawidow warto.
Zauwa, e funkcja
WYSZUKAJ
(w przeciwiestwie do funkcji
WYSZUKAJ.PIONOWO
) moe
zwróci warto znajdujc si w innym wierszu ni dopasowana warto. Jeli argumenty
przeszukiwany_wektor
i
wektor_wynikowy
nie nale do tej samej tabeli, funkcja
WYSZUKAJ
moe by bardzo pomocna. Jeli jednak nale do tej samej tabeli, lepiej jest uywa funkcji
WYSZUKAJ.PIONOWO
, choby dlatego, e funkcja
WYSZUKAJ
nie dziaa z nieposortowanymi danymi.
258
Cz II Stosowanie funkcji w formuach
czne uycie funkcji
PODAJ.POZYCJ i INDEKS
Funkcje
PODAJ.POZYCJ
i
INDEKS
s czsto uywane w formuach wyszukujcych. Funkcja
PODAJ.POZYCJ
zwraca wzgldne pooenie w zakresie komórki, która pasuje do okrelonej
wartoci. Jej skadnia jest nastpujca:
PODAJ.POZYCJ(szukana_warto; przeszukiwana_tablica; typ_porównania)
Oto opis argumentów tej funkcji:
x
szukana_warto
— warto, która ma by dopasowana do wartoci w przeszukiwanej
tablicy. Jeli argument
typ_porównania
ma warto
0
, a szukana warto to tekst,
argument ten moe zawiera symbole wieloznaczne
*
i
?
.
x
przeszukiwana_tablica
— tablica, która ma by przeszukana.
x
typ_porównania
— liczba cakowita (
-1
,
0
lub
1
) okrelajca sposób porównywania
wartoci.
U
WAGA
Jeli argument
typ_porównania
ma warto
1
, funkcja
PODAJ.POZYCJ
znajduje
najwiksz warto, która jest mniejsza lub równa szukanej wartoci (przeszukiwana
tablica musi by posortowana rosnco). Warto
0
tego argumentu powoduje
znalezienie pierwszej wartoci, która jest identyczna z poszukiwan. Jeli argument
typ_porównania
ma warto
-1
, funkcja
PODAJ.POZYCJ
znajduje najmniejsz
warto wiksz lub równ szukanej wartoci (przeszukiwana tablica musi by
posortowana malejco). Pominicie tego argumentu oznacza nadanie mu wartoci
1
.
Funkcja
INDEKS
zwraca komórk z zakresu. Jej skadnia jest nastpujca:
INDEKS(tablica; numer_wiersza; numer_kolumny)
Oto opis argumentów tej funkcji:
x
tablica
— zakres;
x
numer_wiersza
— numer wiersza w tablicy;
x
numer_kolumny
— numer kolumny w tablicy.
U
WAGA
Jeli tablica zawiera tylko jeden wiersz lub jedn kolumn, argument
numer_wiersza
lub
numer_kolumny
jest opcjonalny.
259
Rozdzia 8. Funkcje wyszukiwania
Rysunek 8.5 przedstawia arkusz zawierajcy daty, nazwy dni tygodnia i kwoty w kolumnach
D
, E i F. Po wpisaniu daty do komórki B1 widoczna poniej formua (znajdujca si
w komórce B2) przeszukuje daty w kolumnie D i zwraca odpowiedni kwot z kolumny F.
Formua w komórce B2 jest nastpujca:
INDEKS(F2:F21; PODAJ.POZYCJ(B1; D2:D21; 0))
Rysunek 8.5. Wyszukiwanie przy uyciu funkcji INDEKS i PODAJ.POZYCJ
Aby zapozna si ze sposobem dziaania tej formuy, zacznijmy od funkcji
PODAJ.POZYCJ
.
Przeszukuje ona zakres komórek D2:D21 w celu znalezienia daty znajdujcej si w komórce B1.
Zwraca wzgldny numer wiersza, w którym znalaza t dat. Warto ta jest nastpnie wyko-
rzystywana jako drugi argument funkcji
INDEKS
. Wynikiem jest warto z tego samego wiersza
w zakresie F2:F21.
Wyspecjalizowane
formuy wyszukujce
Za pomoc kilku dodatkowych formu wyszukujcych mona wykonywa bardziej wyspe-
cjalizowane wyszukiwania. Mona na przykad wyszuka dokadn warto, przeszuka ko-
lumny poza pierwsz kolumn w tabeli, przeprowadzi wyszukiwanie z rozrónianiem ma-
ych i wielkich liter, zwróci warto spomidzy kilku tabel, a take wykonywa inne
wyspecjalizowane i zoone wyszukiwania.
260
Cz II Stosowanie funkcji w formuach
Kiedy pustka nie jest zerem
Funkcje Excela traktuj puste komórki w wynikowym zakresie jako zera.
Arkusz przedstawiony na poniszym rysunku zawiera dwukolumnow tabel
do przeszukiwania. Ponisza formua wyszukuje nazw znajdujc si
w komórce B1 i zwraca odpowiedni ilo:
WYSZUKAJ.PIONOWO(B1; D2:E8; 2)
Zwró uwag, e komórka Ilo jest pusta dla imienia Karol, ale formua zwraca
warto
0
.
Aby odróni zera od pustych komórek, konieczna jest modyfikacja formuy
polegajca na dodaniu funkcji
JEELI
, która sprawdzi, czy dugo zwróconej
wartoci to
0
. Jeli znaleziona warto jest pusta, dugo zwróconej wartoci
wynosi zero. We wszystkich innych przypadkach jest ona róna od zera.
Ponisza formua wywietla pusty cig, jeli dugo znalezionej wartoci
wynosi zero, i rzeczywist warto, jeli dugo jest róna od zera:
=JEELI(D(WYSZUKAJ.PIONOWO(B1; D2:E8; 2))=0; ""; (WYSZUKAJ.PIONOWO(B1;
´D2:E8; 2)))
N
A
FTP
Przykady prezentowane w tym podrozdziale mona znale w pliku
wyspecjalizowane formuy wyszukiwania.xlsx, który jest dostpny na serwerze FTP.
Wyszukiwanie dokadnej wartoci
Jak pokazaem w poprzednich przykadach, funkcje
WYSZUKAJ.PIONOWO
i
WYSZUKAJ.POZIOMO
nie wymagaj dokadnego dopasowania szukanej wartoci do wartoci znajdujcych si
w przeszukiwanej tabeli. Przykadem takiego przyblionego dopasowywania jest wyszukiwa-
nie stopy procentowej podatku w tabeli podatków. Czasami moe jednak by potrzebne do-
kadne dopasowanie, na przykad przy wyszukiwaniu numeru pracownika.
261
Rozdzia 8. Funkcje wyszukiwania
Aby wyszuka tylko dokadn warto, naley uy funkcji
WYSZUKAJ.PIONOWO
lub
WYSZUKAJ.POZIOMO
z opcjonalnym czwartym argumentem ustawionym na warto
FASZ
.
Rysunek 8.6 przedstawia arkusz zawierajcy tabel z numerami pracowników (kolumna C)
oraz ich imionami i nazwiskami (kolumna D). Nazwa tej tabeli to ListaPracowników. Przed-
stawiona poniej formua znajdujca si w komórce B2 wyszukuje numer pracownika wpro-
wadzony do komórki B1 i zwraca odpowiadajce mu imi i nazwisko pracownika:
=WYSZUKAJ.PIONOWO(B1; ListaPracowników; 2; FASZ)
Rysunek 8.6. Ta tabela wymaga dokadnego dopasowania
Jako e ostatni argument funkcji
WYSZUKAJ.PIONOWO
ma warto
FASZ
, funkcja zwraca imi
i nazwisko pracownika, tylko jeli znajdzie dokadnie pasujc warto. Jeli numer pracow-
nika nie zostanie znaleziony, funkcja ta zwraca bd
#N/D
. Jest to jak najbardziej podane
dziaanie, poniewa zwracanie przyblionej wartoci dla numeru pracownika nie miaoby
sensu. Zauwa te, e numery pracowników w kolumnie C nie s posortowane rosnco. Jest to
moliwe w przypadku, gdy ostatni argument funkcji
WYSZUKAJ.PIONOWO
ma warto
FASZ
.
N
OWO
Aby zmieni wywietlanie bdu
#N/D
w przypadku nieznalezienia numeru
pracownika na co innego, mona uy funkcji
JEELI.BD
, do znalezienia
informacji o bdzie i zastpienia jej wasnym acuchem znaków. Ponisza
formua wywietla tekst Nie znaleziono, zamiast
#N/D
:
=JEELI.BD(WYSZUKAJ.PIONOWO(B1;ListaPracowników;2;FASZ)
´"Nie znaleziono")
Funkcja
JEELI.BD
dziaa tylko w wersjach 2007 i 2010 Excela. Aby zachowa
zgodno z wczeniejszymi wersjami programu, naley zastosowa ponisz formu:
=JEELI(CZY.BRAK(WYSZUKAJ.PIONOWO(B1,ListaPracowników,2,
´FASZ)),"Nie znaleziono", WYSZUKAJ.PIONOWO
´(B1,ListaPracowników,2, FASZ))
262
Cz II Stosowanie funkcji w formuach
Wyszukiwanie wartoci w lew stron
Funkcja
WYSZUKAJ.PIONOWO
zawsze wyszukuje wartoci w pierwszej kolumnie przeszukiwa-
nego zakresu. Co zrobi, aby wyszuka warto w innej ni pierwsza kolumnie? Dobrze by
byo, gdybymy mogli jako trzeci argument tej funkcji poda ujemn warto — ale nie moemy.
Rysunek 8.7 ilustruje ten problem. Zaómy, e chcemy znale redni pakowania (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 SzukanaWarto.
Funkcja
WYSZUKAJ.PIONOWO
nie zadziaa, poniewa dane nie s odpowiednio uoone. Jednym
z wyj jest poprzestawianie danych, ale to nie zawsze jest moliwe.
Rysunek 8.7. Funkcja WYSZUKAJ.PIONOWO nie moe wyszuka wartoci znajdujcej si
w kolumnie B, opierajc si na wartoci w kolumnie C
Innym rozwizaniem jest uycie funkcji
WYSZUKAJ
, która wymaga dwóch argumentów w po-
staci zakresów. Ponisza formua zwraca redni pakowania z kolumny B dla gracza z ko-
mórki o nazwie SzukanaWarto:
=WYSZUKAJ(SzukanaWarto;Gracze;rednie)
Funkcja
WYSZUKAJ
wymaga, aby przeszukiwany zakres (w tym przypadku o nazwie Gracze)
by posortowany w porzdku rosncym. Poza tym formua ta jest obarczona jeszcze jednym
maym problemem: jeli wpisane zostanie nazwisko nieistniejcego gracza (czyli komórka
SzukanaWarto
bdzie zawieraa warto, której nie ma w zakresie Gracze), zwróci bdny wynik.
Lepszym rozwizaniem jest uycie funkcji INDEKS i PODAJ.POZYCJ. Ponisza formua
dziaa jak poprzednia, ale zwraca bd
#N/D
, jeli gracz nie zostanie znaleziony. Drug zalet
tej formuy jest to, e nazwiska graczy nie musz by posortowane.
=INDEKS(rednie;PODAJ.POZYCJ(SzukanaWarto;Gracze;0))
263
Rozdzia 8. Funkcje wyszukiwania
Wyszukiwanie z rozrónianiem maych
i wielkich liter
Funkcje wyszukiwania w Excelu (
WYSZUKAJ
,
WYSZUKAJ.POZIOMO
i
WYSZUKAJ.PIONOWO
) nie
rozróniaj maych i wielkich liter. Jeli na przykad napiszemy formu wyszukujc cig
budet
, wszystkie nastpujce cigi bd brane pod uwag:
BUDET
,
Budet
,
BuDEt
.
Rysunek 8.8 przedstawia prosty przykad. Zakres komórek D2:D7 ma nazw Zakres1, a zakres
E2:E7
nazywa si Zakres2. Sowo do znalezienia znajduje si w komórce B1 (o nazwie Warto).
Rysunek 8.8. Wyszukiwanie z rozrónianiem maych i wielkich liter przy uyciu
formuy tablicowej
Widoczna poniej formua znajduje si w komórce B2. Wykonuje ona wyszukiwanie z roz-
rónianiem maych i wielkich liter w zakresie Zakres1 i zwraca odpowiedni warto z zakre-
su Zakres2.
=INDEKS(Zakres2;PODAJ.POZYCJ(PRAWDA;PORÓWNAJ(Warto;Zakres1);0))
Formua ta szuka sowa
PIES
(pisanego wielkimi literami) i zwraca warto
300
.
U
WAGA
Pamitaj o uyciu kombinacji klawiszy Ctrl+Shift+Enter przy wprowadzaniu
formuy tablicowej.
Wybieranie sporód wielu tabel
W arkuszu mona oczywicie mie dowoln liczb tabel do przeszukiwania. Moe si zda-
rzy, e konieczne bdzie wybranie przez formu jednej z nich. Rysunek 8.9 przedstawia
przykadowy arkusz.
264
Cz II Stosowanie funkcji w formuach
Rysunek 8.9. Arkusz ten demonstruje uycie wielu tabel do przeszukiwania
Arkusz ten oblicza prowizje od sprzeday i zawiera dwie tabele do przeszukiwania: G3:H9
(o nazwie Tabela1) i J3:K8 (o nazwie Tabela2). Wysoko stopy procentowej prowizji kadego
przedstawiciela handlowego zaley od dwóch czynników: liczby lat pracy (kolumna B) i iloci
sprzedanego towaru (kolumna C). Kolumna D zawiera formuy, które wyszukuj stop procen-
tow prowizji w odpowiedniej tabeli. Na przykad formua w komórce D2 jest nastpujca:
=WYSZUKAJ.PIONOWO(C2;JEELI(B2<3;Tabela1;Tabela2);2)
Drugi argument funkcji
WYSZUKAJ.PIONOWO
zawiera funkcj
JEELI
, która na podstawie war-
toci znajdujcej si w kolumnie B okrela, któr tabel przeszuka.
Formuy w kolumnie E mno tylko ilo sprzedanego towaru z kolumny C przez stop pro-
centow prowizji z kolumny D. Na przykad formua w komórce E2 jest nastpujca:
=C2*D2
Okrelanie ocen
na podstawie wyników testu
Czsto spotykanym sposobem uycia tabeli do przeszukiwania jest przypisanie ocen wyni-
kom testów. Rysunek 8.10 przedstawia arkusz zawierajcy wyniki testu uczniów. Zakres
E2:F6
(o nazwie ListaOcen) stanowi tabel do przeszukiwania w celu dopasowania oceny do
wyniku testu.
Kolumna C zawiera formuy uywajce funkcji
WYSZUKAJ.PIONOWO
i przypisujce uczniom
oceny na podstawie wyników testu znajdujcych si w kolumnie B. Na przykad formua
w komórce C2 jest nastpujca:
=WYSZUKAJ.PIONOWO(B2;ListaOcen;2)
265
Rozdzia 8. Funkcje wyszukiwania
Rysunek 8.10. Wyszukiwanie ocen dla wyników testu
Kiedy tabela do przeszukiwania jest niewielka (jak w przykadowym arkuszu widocznym na
rysunku 8.10), mona zamiast niej uy tablicy. Na przykad widoczna poniej formua
zwraca ocen bez uycia tabeli do przeszukiwania. Informacje z tabeli zostay zakodowane
w staej tablicowej. Wicej informacji na temat staych tablicowych znajduje si w rozdziale 14.
=WYSZUKAJ.PIONOWO(B2;{0;"1"\40;"2"\70;"3"\80;"4"\90;"5"};2)
Inne podejcie z uyciem bardziej czytelnej formuy polega na uyciu funkcji
WYSZUKAJ
z dwoma argumentami tablicowymi:
=WYSZUKAJ(B2;{0;40;70;80;90};{"1";"2";"3";"4";"5"})
Ostatecznie za kadym razem, gdy jest moliwo przekonwertowania danych wejciowych,
w tym przypadku liczby punktów, na liczby cakowite ze zbioru 1 – 254, mona uy funkcji
WYBIERZ
. Liczba punktów jest dzielona przez 10, odrzucana jest cz dziesitna i dodawana
jest liczba 1 w celu utworzenia liczb od 1 do 11. Pozostae argumenty definiuj wartoci
zwrotne dla tych jedenastu opcji.
=WYBIERZ(LICZBA.CAK(B2/10)+1;"1";"1";"1";"1";"2";"2";"2";"3";"4";"5";"5")
Obliczanie redniej ocen
Na wyszych uczelniach w Ameryce miernikiem jakoci pracy studenta jest jego rednia ocen
(ang. Grade Point Average — GPA) z zaj, na które uczszcza. W tym przykadzie system
ocen skada si z wartoci od 0 do 4 przypisanych odpowiednio literom (A = 4, B = 3, C = 2,
D = 1, F = 0). GPA oblicza si poprzez wycignicie redniej waonej z ocen pomnoonych
przez liczb kredytowanych godzin kursu. Na przykad ocena z kursu trwajcego jedn go-
dzin ma mniejsz wag ni ocena z kursu trwajcego trzy godziny. Wartoci redniej zawie-
raj si w zbiorze od 0 do 4.
266
Cz II Stosowanie funkcji w formuach
Rysunek 8.11 przedstawia arkusz z informacjami dotyczcymi jednego studenta. Uczestniczy
on w piciu kursach w sumie trwajcych 13 godzin. Zakres B2:B6 ma nazw KredytowaneGo-
dziny
. Oceny za kady kurs znajduj si w kolumnie C (zakres C2:C6 ma nazw OcenyLitery).
Formuy w kolumnie D obliczaj za pomoc formuy wyszukiwania ocen za kady kurs.
Na przykad poniej wida formu z komórki D2. Przeszukuje ona tabel w zakresie G2:H6
(o nazwie TabelaOcen).
=WYSZUKAJ.PIONOWO(C2;TabelaOcen;2;FASZ)
Rysunek 8.11. Obliczanie redniej przy uyciu kilku formu
Formuy w kolumnie E obliczaj wartoci wywaone. Formua w komórce E2 jest nastpujca:
=D2*B2
Formua w komórce B8 obliczajca GPA jest nastpujca:
=SUMA(E2:E6)/SUMA(B2:B6)
Powysze formuy dziaaj prawidowo, ale mona obliczenia te nieco uproci. Da si nawet
unikn koniecznoci uycia tabeli do przeszukiwania i formu w kolumnach D i E na rzecz
jednej formuy tablicowej. Ponisza formua wykonuje niezbdne obliczenia:
{=SUMA((PODAJ.POZYCJ(OcenyLitery;{"F";"D";"C";"B";"A"};0)-1)*KredytowaneGodziny)
´/SUMA(KredytowaneGodziny)}
Wyszukiwanie w dwie strony
Rysunek 8.12 przedstawia arkusz zawierajcy tabel z miesicznymi danymi sprzeday pro-
duktów. Aby sprawdzi sprzeda okrelonego produktu w okrelonym miesicu, uytkownik
wpisuje w komórce B1 nazw miesica i w komórce B2 nazw produktu.
267
Rozdzia 8. Funkcje wyszukiwania
Rysunek 8.12. Wyszukiwanie w dwie strony
Aby byo prociej, zastosowaem w tym arkuszu nastpujce nazwy zakresów:
Nazwa
Adres
Miesic
B1
Produkt
B2
Tabela
D1:H14
ListaMiesicy
D1:D14
ListaProduktów
D1:H1
Ponisza formua (znajdujca si w komórce B4) zwraca pozycj miesica w zakresie Lista-
Miesicy
. Dla stycznia na przykad zwraca cyfr
2
, poniewa jest to drugi element tego zakresu
(pierwszy to pusta komórka D1).
=PODAJ.POZYCJ(Miesic;ListaMiesicy;0)
Formua znajdujca si w komórce B5 dziaa w podobny sposób w zakresie ListaProduktów:
=PODAJ.POZYCJ(Produkt;ListaProduktów;0)
Formua 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)
Formuy te mona oczywicie poczy w jedn formu, jak ta poniej:
=INDEKS(Tabela; PODAJ.POZYCJ(Miesic;ListaMiesicy;0);
´PODAJ.POZYCJ(Produkt;ListaProduktów;0))
268
Cz II Stosowanie funkcji w formuach
W
SKAZÓWKA
Innym sposobem na wykonanie wyszukiwania w dwie strony jest nazwanie kadego
wiersza i kadej kolumny tabeli. Mona to szybko zrobi, zaznaczajc ca tabel
i uywajc polecenia Formuy/Nazwy zdefiniowane/Utwórz z zaznaczenia. Po utworzeniu
tych nazw wyszukiwanie w dwie strony mona wykonywa za pomoc prostych
formu, jak ta poniej:
=Sworznie Lipiec
W formule tej zosta uyty operator przecicia zakresów (spacja). Zwraca ona
wielko sprzeday sworzni w lipcu. Aby odwoa si do komórek zawierajcych
nazwy miesicy i produktów, naley napisa:
=ADR.POR(Miesic) ADR.POR(Produkt)
Formua ta konwertuje wartoci w komórkach Miesic i Produkt na odwoania
do zakresów oraz znajduje ich przecicie. Szczegóowe informacje na temat
operatora przecicia znajduj si w rozdziale 3.
Wyszukiwanie dwukolumnowe
W pewnych sytuacjach konieczne jest wyszukanie danych na podstawie wartoci z dwóch
kolumn. Na rysunku 8.13 wida przykad takiej sytuacji:
Rysunek 8.13. Arkusz ten wykonuje wyszukiwanie, wykorzystujc informacje z dwóch
kolumn (D i E)
Tabela do przeszukiwania zawiera marki i modele samochodów oraz odpowiadajce im kody.
W arkuszu uywane s nastpujce nazwane zakresy:
F2:F12
Kod
B1
Marka
B2
Model
D2:D12
Marki
E2:E12
Modele
269
Rozdzia 8. Funkcje wyszukiwania
Ponisza formua tablicowa wywietla kod odpowiadajcy wybranemu modelowi marki samochodu:
{=INDEKS(Kod; PODAJ.POZYCJ(Marka&Model;Marki&Modele;0))}
Formua ta czy zawarto komórek Marka i Model, a nastpnie wyszukuje ten tekst w tablicy
zawierajcej ten sam tekst odpowiednio w zakresach Marki i Modele.
Sprawdzanie adresu wartoci w zakresie
W wikszoci przypadków zadaniem formuy wyszukujcej jest zwrócenie wartoci. Moe si
jednak zdarzy, e zechcemy sprawdzi adres komórki w zakresie zawierajcej okrelon warto.
Na przykad na rysunku 8.14 widoczny jest arkusz zawierajcy zbiór liczb zajmujcy jedn kolumn
(o nazwie Dane). Komórka B1, w której znajduje si warto do wyszukania, ma nazw Cel.
Rysunek 8.14. Formua znajdujca si w komórce B2 zwraca adres w obrbie zakresu
Dane dla wartoci znajdujcej si w komórce B1
Widoczna poniej formua znajdujca si w komórce B2 zwraca adres komórki z zakresu
Dane
zawierajcej warto z komórki Cel:
=ADRES(WIERSZ(Dane)+PODAJ.POZYCJ(Cel;Dane;0)-1;NR.KOLUMNY(Dane))
Jeli zakres Dane zajmuje jeden wiersz, do wyszukania adresu docelowej wartoci uyj nast-
pujcej formuy:
=ADRES(WIERSZ(Dane); NR.KOLUMNY(Dane)+PODAJ.POZYCJ(Cel;Dane;0)-1)
Jeli warto docelowa wystpuje w zakresie Dane wicej ni jeden raz, zwracany jest adres
pierwszej z nich. Jeli nie zostanie ona znaleziona w ogóle, formua zwraca bd
#N/D
.
270
Cz II Stosowanie funkcji w formuach
Wyszukiwanie wartoci
przy uyciu najbliszego dopasowania
Funkcje
WYSZUKAJ.PIONOWO
i
WYSZUKAJ.POZIOMO
s przydatne w nastpujcych sytuacjach:
x Chcemy zidentyfikowa warto dokadnie pasujc do docelowej wartoci.
W takim przypadku ustawiamy czwarty argument funkcji na warto
FASZ
.
x Chcemy zlokalizowa przyblion warto. Jeli czwarty argument funkcji ma
warto
PRAWDA
lub zostanie pominity i zostanie znaleziona warto pasujca
dokadnie, zostaje zwrócona nastpna najwiksza warto, która jest mniejsza
od szukanej wartoci.
Ale co zrobi, jeli potrzebujemy znale najblisz pasujc warto? Nie zrobimy tego ani
za pomoc funkcji
WYSZUKAJ.PIONOWO
, ani
WYSZUKAJ.POZIOMO
.
Na rysunku 8.15 widoczny jest arkusz zawierajcy imiona studentów w kolumnie A i wartoci
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 reprezentujc przesunicie kolumny wzgldem zakresu Dane.
Rysunek 8.15. Arkusz ten demonstruje sposób wyszukiwania najbliszej pasujcej wartoci
Ponisza formua znajduje w zakresie Dane warto najblisz wartoci docelowej i zwraca imi
odpowiadajcego jej studenta z kolumny A (czyli kolumny o przesuniciu –1). Formua ta zwraca
imi
Lech
(z którym skojarzona jest warto 8000 — najblisza szukanej wartoci 8025).
271
Rozdzia 8. Funkcje wyszukiwania
=ADR.POR(ADRES(WIERSZ(Dane)+PODAJ.POZYCJ(MIN(MODU.LICZBY(Cel-Dane));
´MODU.LICZBY(Cel-Dane);0)-1;NR.KOLUMNY(Dane)+PrzesKol))
Jeli w zakresie Dane znajduj si dwie wartoci tak samo bliskie wartoci docelowej, formua
zwraca pierwsz na licie.
Warto w komórce o nazwie PrzesKol moe by ujemna (oznaczajc kolumn po lewej stro-
nie zakresu Dane), dodatnia (oznaczajc kolumn po prawej stronie zakresu Dane) lub wyno-
si
0
(oznaczajc rzeczywist najblisz warto w zakresie Dane).
Aby zrozumie zasad dziaania tej formuy, naley zrozumie, jak dziaa funkcja
ADR.POR
.
Pierwszym argumentem tej funkcji jest cig tekstowy w postaci adresu komórki (lub adresu
komórki zawierajcej cig tekstowy). W tym przypadku cig ten jest tworzony przez funkcj
ADRES
, która przyjmuje adres wiersza i kolumny i zwraca adres komórki.
Wyszukiwanie wartoci
przy uyciu interpolacji liniowej
Interpolacja to metoda wyznaczania brakujcej wartoci przy uyciu dostpnych wartoci.
Ilustruje to rysunek 8.16. Kolumna D zawiera list wartoci (nazywa si x), a kolumna E od-
powiadajce im wartoci (nazywa si y).
Rysunek 8.16. Arkusz ten demonstruje wyszukiwanie w tabeli za pomoc
interpolacji liniowej
272
Cz II Stosowanie funkcji w formuach
Na arkuszu znajduje si te wykres graficznie obrazujcy powizania pomidzy zakresami x i y.
Jak wida, pomidzy odpowiadajcymi sobie wartociami w tych zakresach wystpuj przy-
blione powizania liniowe — kiedy zwiksza si warto x, zwiksza si te warto y.
Zauwa, e wartoci w zakresie x nie s penym zbiorem kolejnych liczb cakowitych.
Na przykad brakuje wartoci 3, 6, 7, 14, 17, 18 i 19.
Moemy utworzy formu wyszukujc warto w zakresie x i zwracajc odpowiadajc jej
warto z zakresu y. Co jednak zrobi, jeli zechcemy znale warto z zakresu y dla braku-
jcej wartoci z zakresu x? Zwyka formua wyszukujca nie jest dobrym rozwizaniem,
poniewa zwróci istniejc warto z zakresu y (a nie wyznaczon warto z tego zakresu).
Na przykad ponisza formua wyszukuje warto
3
i zwraca warto
18.00
(odpowiadajc
wartoci
2
w zakresie x):
=WYSZUKAJ(3; x; y)
W takim przypadku najlepiej jest zastosowa interpolacj. Innymi sowy, biorc pod uwag,
e warto
3
znajduje si w poowie drogi pomidzy wartociami
2
i
4
, chcemy, aby nasza
formua zwrócia warto
21.00
— znajdujc si w poowie drogi pomidzy odpowiadaj-
cymi dwójce i czwórce wartociami
18.00
i
24.00
.
F
ORMUY DO WYKONYWANIA INTERPOLACJI LINIOWEJ
Rysunek 8.17 przedstawia arkusz zawierajcy w kolumnie B formuy. Warto do wyszuka-
nia znajduje si w komórce B1. Ostatnia formua, znajdujca si w komórce B16, zwraca
wynik. Jeli warto znajdujca si w komórce B3 zostanie znaleziona w zakresie x, formua
zwróci odpowiadajc jej warto z zakresu y. W przeciwnym przypadku formua znajdujca si
w komórce B16 zwróci wyznaczon warto z zakresu y, uzyskan za pomoc interpolacji liniowej.
Rysunek 8.17. Kolumna B zawiera formuy wyszukujce wartoci przy uyciu
interpolacji liniowej
273
Rozdzia 8. Funkcje wyszukiwania
Bardzo wane jest, aby wartoci w zakresie x byy ustawione w rosncej kolejnoci. Jeli
komórka B1 zawiera warto mniejsz od najmniejszej wartoci w zakresie x lub wiksz
od najwikszej wartoci w tym zakresie, formua zwraca bd. Tabela 8.2 zawiera zestawienie
i opisy tych formu.
TABELA 8.2. FORMUY DO WYSZUKIWANIA PRZY ZASTOSOWANIU
INTERPOLACJI LINIOWEJ
Komórka Formua
Opis
B3
=WYSZUKAJ(B1;x;x)
Wykonuje zwyke wyszukiwanie w zakresie x
i zwraca znalezion warto.
B4
=B1=B3
Zwraca warto
PRAWDA
, jeli znaleziona warto
jest równa szukanej wartoci.
B6
=PODAJ.POZYCJ(B3;x;0)
Zwraca numer wiersza w zakresie x, który zawiera
pasujc warto.
B7
=JEELI(B4;B6;B6+1)
Zwraca ten sam numer wiersza, co formua
w komórce B6, jeli zostanie znalezione dokadne
dopasowanie. W przeciwnym przypadku dodaje
1 do wyniku z komórki B6.
B9
=INDEKS(x;B6)
Zwraca warto z zakresu x odpowiadajc
wierszowi o numerze z komórki B6.
B10
=INDEKS(x;B7)
Zwraca warto z zakresu x odpowiadajc
wierszowi o numerze z komórki B7.
B12
=WYSZUKAJ(B9;x;y)
Zwraca warto z zakresu y, która odpowiada
wartoci z zakresu x znajdujcej si w komórce B9.
B13
=WYSZUKAJ(B10;x;y)
Zwraca warto z zakresu y, która odpowiada
wartoci z zakresu x znajdujcej si w komórce B10.
B15
=JEELI(B4;0;
´(B1-B3)/(B10-B9))
Oblicza wspóczynnik dopasowania na podstawie
rónicy pomidzy wartociami z zakresu x.
B16
=B12+((B13-B12)*B15)
Wyznacza warto z zakresu y przy uyciu
wspóczynnika dopasowania z komórki B15.
CZNE UYCIE FUNKCJI
WYSZUKAJ
I
REGLINW
Troch inna metoda, któr mona wybra zamiast interpolacji liniowej do wyszukiwania,
polega na uyciu funkcji
WYSZUKAJ
i
REGLINW
. Jedn z jej zalet jest to, e wymaga uycia tylko
jednej formuy (rysunek 8.18).
274
Cz II Stosowanie funkcji w formuach
Rysunek 8.18. Arkusz ten zawiera formu wykorzystujc funkcje WYSZUKAJ i REGLINW
Poniej wida formu z komórki B2. Podejmuje ona decyzj za pomoc funkcji
JEELI
. Jeli
w zakresie x zostanie znaleziona dokadnie pasujca warto, formua zwraca odpowiadajc
jej warto z zakresu y (za pomoc funkcji
WYSZUKAJ
). Jeli dokadnie pasujca warto nie
zostanie znaleziona, formua oblicza najlepiej pasujc warto z zakresu y przy uyciu funkcji
REGLINW
(nie wykonuje interpolacji liniowej).
=JEELI(B1=WYSZUKAJ(B1;x;x);WYSZUKAJ(INDEKS(x;PODAJ.POZYCJ(WYSZUKAJ(B1;x;x);
´x;0));x;y);REGLINW(y;x;B1))
Wyszukiwarka
Podobne podstrony:
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
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
Microsoft Excel 2010 PL Formuly i funkcje Akademia Excela e21fae
Excel 2010 PL Formuly
informatyka excel 2010 pl formuly john walkenbach ebook
Microsoft Excel 2010 PL Formuly i funkcje Akademia Excela e21fae
ABC Excel 2010 PL abce21
więcej podobnych podstron