Excel 2010 PL.
Idz
do
Formuły
" Spis treści
Autor: John Walkenbach
TÅ‚umaczenie: A
ukasz Piwko
" Przykładowy rozdział
ISBN: 978-83-246-2883-4
Tytuł oryginału: Excel 2010 Formulas
Katalog książek
(Mr. Spreadsheet's Bookshelf)
Format: 172×245, stron: 880
" Katalog online
Wykorzystaj wszystkie możliwości drzemiące w formułach!
" Zamów drukowany
" Jak tworzyć formuły finansowe, tablicowe i tekstowe?
katalog
" Jak napisać własne funkcje arkusza w języku VBA?
" Jak za pomocą formuł tworzyć wykresy i tabele przestawne?
Twój koszyk
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
" Dodaj do koszyka
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
Cennik i informacje
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
" Zamów informacje
Walkenbacha, absolutnego guru Excela, dostarczy Ci wiedzy, która raz na zawsze odmieni Twoją
o nowościach
pracÄ™ w tym programie.
" Zamów cennik
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
Czytelnia
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
" Fragmenty książek
przekonasz siÄ™, jak przydatne sÄ… one w pracy z wykresami i tabelami przestawnymi. Znajdziesz
online
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
Kontakt
" 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
Helion SA
" opanujesz tworzenie i stosowanie  megaformuł
ul. Kościuszki 1c
44-100 Gliwice
" napiszesz własne, przydatne funkcje w języku VBA
tel. [phone]
" bez trudu usuniesz błędy w formułach
e-mail: [e-mail]
Chcesz, by Excel pracował za Ciebie? John Walkenbach pokaże Ci, jak to zrobić!
© Helion 1991 2010
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
Spis tre ci
8
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
Spis tre ci
9
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
Spis tre ci
10
Cz II Stosowanie funkcji w formu ach 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
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
Spis tre ci
11
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
Spis tre ci
12
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
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
Spis tre ci
13
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ó ne 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
Spis tre ci
14
Praca z u amkami dolarów 331
Stosowanie funkcji ZAOKR.DO.CA K i LICZBA.CA K 332
Zaokr glanie do parzystej lub nieparzystej liczby ca kowitej 333
Zaokr glanie do n cyfr znacz cych 333
Cz III Formu y 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 wska niki 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
Spis tre ci
15
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 wska ników 409
Tworzenie indeksów 412
Cz IV Formu y 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
Spis tre ci
16
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 formu 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
Spis tre ci
17
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 poprawno ci 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
Spis tre ci
18
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
Spis tre ci
19
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
Spis tre ci
20
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
Spis tre ci
21
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
Spis tre ci
22
Dodatek D Przyk ady do czone do ksi ki 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
Rozdzia 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 formu a 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 znale numer telefoniczny jakiej osoby, najpierw znajdujemy jej nazwisko, a dopiero
potem sprawdzamy numer.
251
Cz II Stosowanie funkcji w formu ach
252
UWAGA
Pod poj ciem tabela rozumiem prostok tny 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 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 Formu a
=WYSZUKAJ.PIONOWO(B2; DanePracowników;2; FA SZ)
C2
=WYSZUKAJ.PIONOWO(B2; DanePracowników;3; FA SZ)
D2
=WYSZUKAJ.PIONOWO(B2; DanePracowników;4; FA SZ)
E2
=WYSZUKAJ.PIONOWO(B2; DanePracowników;5; FA SZ)
F2
Rysunek 8.1. Formu y 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.
Rozdzia 8. Funkcje wyszukiwania
253
Funkcje zwi zane
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 U YWANE W FORMU ACH WYSZUKIWANIA
Funkcja Opis
INDEKS
Zwraca warto (lub odwo anie do warto ci) z tabeli lub zakresu.
PODAJ.POZYCJ
Zwraca wzgl dne po o enie elementu w zakresie, które pasuje
do podanej warto ci.
PRZESUNI CIE
Zwraca odwo anie do zakresu przesuni te o okre lon liczb wierszy
i kolumn wzgl dem jakiej komórki lub jakiego zakresu komórek.
WYBIERZ
Zwraca okre lon warto z listy warto ci (do 29) podanych jako argumenty.
WYSZUKAJ
Zwraca warto z jednowierszowego lub jednokolumnowego zakresu.
Podobnie dzia a 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 znajduj c si 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 znajduj c si 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
formu y wyszukiwania
Za pomoc podstawowych funkcji wyszukiwania mo na przeszuka kolumn lub wiersz
w celu znalezienia pewnej warto ci pozwalaj cej znale 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 .
Cz II Stosowanie funkcji w formu ach
254
NA FTP
Przyk ady prezentowane w tym podrozdziale mo na znale w pliku podstawowe
formu y 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. 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.
UWAGA
Je li argument kolumna ma warto PRAWDA lub zosta pomini ty, pierwsza kolumna
przeszukiwanej tabeli musi by posortowana w porz dku rosn cym. Je li warto
szukana jest mniejsza ni najmniejsza warto w pierwszej kolumnie tabeli tablicy,
funkcja WYSZUKAJ.PIONOWO zwróci b d #N/D. Je li argument kolumna ma warto
FA SZ, pierwsza kolumna przeszukiwanej tabeli nie musi by posortowana
w rosn cym porz dku. Je li dok adne dopasowanie nie zostanie znalezione,
funkcja zwraca b d #N/D.
WSKAZÓWKA
Je li argument szukana_warto jest typu tekstowego (a czwarty argument,
kolumna, ma warto FA SZ), mo na zastosowa symbole wieloznaczne * i ?.
Gwiazdka dopasowuje dowoln grup znaków, a znak zapytania dowolny
jeden znak.
Rozdzia 8. Funkcje wyszukiwania
255
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.
Cz II Stosowanie funkcji w formu ach
256
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.
WSKAZÓWKA
Je li argument szukana_warto jest tekstem, mo e zawiera symbole wieloznaczne
* i ?. Gwiazdka dopasowuje dowoln liczb 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. U ycie 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.
Rozdzia 8. Funkcje wyszukiwania
257
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).
OSTRZE ENIE
Warto ci w przeszukiwanym wektorze musz by posortowane rosn co.
Je li szukana warto jest mniejsza ni najmniejsza warto w przeszukiwanym
wektorze, funkcja WYSZUKAJ zwraca b d #N/D.
UWAGA
W pomocy jest te mowa o tablicowej sk adni funkcji WYSZUKAJ. Ta alternatywna
sk adnia zosta a dodana ze wzgl du na zachowanie zgodno ci z innymi arkuszami
kalkulacyjnymi. Z regu y zamiast sk adni tablicowej mo na u ywa 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. U ycie funkcji WYSZUKAJ do znalezienia stopy podatkowej
OSTRZE ENIE
Je li warto ci w pierwszej kolumnie nie s posortowane w rosn cej kolejno ci,
funkcja WYSZUKAJ mo e zwróci nieprawid ow warto .
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.
Cz II Stosowanie funkcji w formu ach
258
czne 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.
UWAGA
Je li argument typ_porównania ma warto 1, funkcja PODAJ.POZYCJ znajduje
najwi ksz warto , która jest mniejsza lub równa szukanej warto ci (przeszukiwana
tablica musi by posortowana rosn co). Warto 0 tego argumentu powoduje
znalezienie pierwszej warto ci, która jest identyczna z poszukiwan . Je li argument
typ_porównania ma warto -1, funkcja PODAJ.POZYCJ znajduje najmniejsz
warto wi ksz lub równ szukanej warto ci (przeszukiwana tablica musi by
posortowana malej co). Pomini cie 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.
UWAGA
Je li tablica zawiera tylko jeden wiersz lub jedn kolumn , argument numer_wiersza
lub numer_kolumny jest opcjonalny.
Rozdzia 8. Funkcje wyszukiwania
259
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 u yciu 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
formu y wyszukuj ce
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.
Cz II Stosowanie funkcji w formu ach
260
Kiedy pustka nie jest zerem
Funkcje Excela traktuj puste komórki w wynikowym zakresie jako zera.
Arkusz przedstawiony na poni szym rysunku zawiera dwukolumnow tabel
do przeszukiwania. Poni sza formu a wyszukuje nazw znajduj c 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 formu a zwraca
warto 0.
Aby odró ni zera od pustych komórek, konieczna jest modyfikacja formu y
polegaj ca na dodaniu funkcji JE ELI, która sprawdzi, czy d ugo zwróconej
warto ci to 0. Je li znaleziona warto jest pusta, d ugo zwróconej warto ci
wynosi zero. We wszystkich innych przypadkach jest ona ró na od zera.
Poni sza formu a wy wietla pusty ci g, je li d ugo znalezionej warto ci
wynosi zero, i rzeczywist warto , je li d ugo jest ró na od zera:
=JE ELI(D (WYSZUKAJ.PIONOWO(B1; D2:E8; 2))=0; ""; (WYSZUKAJ.PIONOWO(B1;
D2:E8; 2)))
NA FTP
Przyk ady prezentowane w tym podrozdziale mo na znale w pliku
wyspecjalizowane formu y wyszukiwania.xlsx, który jest dost pny na serwerze FTP.
Wyszukiwanie dok adnej 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.
Rozdzia 8. Funkcje wyszukiwania
261
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 dok adnego 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.
NOWO
Aby zmieni wy wietlanie b du #N/D w przypadku nieznalezienia numeru
pracownika na co innego, mo na u y funkcji JE ELI.B D, do znalezienia
informacji o b dzie i zast pienia jej w asnym a cuchem znaków. Poni sza
formu a 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 dzia a tylko w wersjach 2007 i 2010 Excela. Aby zachowa
zgodno z wcze niejszymi wersjami programu, nale y zastosowa poni sz formu :
=JE ELI(CZY.BRAK(WYSZUKAJ.PIONOWO(B1,ListaPracowników,2,
FA SZ)),"Nie znaleziono", WYSZUKAJ.PIONOWO
(B1,ListaPracowników,2, FA SZ))
Cz II Stosowanie funkcji w formu ach
262
Wyszukiwanie warto ci w lew 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 znale 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 SzukanaWarto .
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 mo e wyszuka warto ci znajduj cej si
w kolumnie B, opieraj c si 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 SzukanaWarto :
=WYSZUKAJ(SzukanaWarto ;Gracze; rednie)
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
SzukanaWarto 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( rednie;PODAJ.POZYCJ (SzukanaWarto ;Gracze;0))
Rozdzia 8. Funkcje wyszukiwania
263
Wyszukiwanie z rozró nianiem ma ych
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
bud et, wszystkie nast puj ce ci gi b d brane pod uwag : BUD ET, Bud et, BuD Et.
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 Warto ).
Rysunek 8.8. Wyszukiwanie z rozró nianiem ma ych i wielkich liter przy u yciu
formu y 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(Warto ;Zakres1);0))
Formu a ta szuka s owa PIES (pisanego wielkimi literami) i zwraca warto 300.
UWAGA
Pami taj o u yciu kombinacji klawiszy Ctrl+Shift+Enter przy wprowadzaniu
formu y 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.
Cz II Stosowanie funkcji w formu ach
264
Rysunek 8.9. Arkusz ten demonstruje u ycie 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 dego
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)
Rozdzia 8. Funkcje wyszukiwania
265
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.
Cz II Stosowanie funkcji w formu ach
266
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 u yciu kilku formu
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.
Rozdzia 8. Funkcje wyszukiwania
267
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
ListaMiesi cy D1:D14
ListaProduktów D1:H1
Poni sza formu a (znajduj ca si w komórce B4) zwraca pozycj miesi ca w zakresie Lista-
Miesi cy. 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))
Cz II Stosowanie funkcji w formu ach
268
WSKAZÓWKA
Innym sposobem na wykonanie wyszukiwania w dwie strony jest nazwanie ka dego
wiersza i ka dej kolumny tabeli. Mo na to szybko zrobi , zaznaczaj c ca tabel
i u ywaj c polecenia Formu y/Nazwy zdefiniowane/Utwórz z zaznaczenia. Po utworzeniu
tych nazw wyszukiwanie w dwie strony mo na wykonywa za pomoc prostych
formu , jak ta poni ej:
=Sworznie Lipiec
W formule tej zosta u yty operator przeci cia zakresów (spacja). Zwraca ona
wielko sprzeda y sworzni w lipcu. Aby odwo a si do komórek zawieraj cych
nazwy miesi cy i produktów, nale y napisa :
=ADR.PO R(Miesi c) ADR.PO R(Produkt)
Formu a ta konwertuje warto ci w komórkach Miesi c i Produkt na odwo ania
do zakresów oraz znajduje ich przeci cie. Szczegó owe informacje na temat
operatora przeci cia znajduj si 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
Rozdzia 8. Funkcje wyszukiwania
269
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. Formu a znajduj ca si w komórce B2 zwraca adres w obr bie zakresu
Dane dla warto ci znajduj cej si 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.
Cz II Stosowanie funkcji w formu ach
270
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 znale 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 najbli szej 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).
Rozdzia 8. Funkcje wyszukiwania
271
=ADR.PO R(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.PO R.
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
Cz II Stosowanie funkcji w formu ach
272
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 znale 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.
FORMU Y 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 formu y wyszukuj ce warto ci przy u yciu
interpolacji liniowej
Rozdzia 8. Funkcje wyszukiwania
273
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. FORMU Y DO WYSZUKIWANIA PRZY ZASTOSOWANIU
INTERPOLACJI LINIOWEJ
Komórka Formu a Opis
=WYSZUKAJ(B1;x;x)
B3 Wykonuje zwyk e wyszukiwanie w zakresie x
i zwraca znalezion warto .
=B1=B3
B4 Zwraca warto PRAWDA, je li znaleziona warto
jest równa szukanej warto ci.
=PODAJ.POZYCJ (B3;x;0)
B6 Zwraca numer wiersza w zakresie x, który zawiera
pasuj c warto .
=JE ELI(B4;B6;B6+1)
B7 Zwraca ten sam numer wiersza, co formu a
w komórce B6, je li zostanie znalezione dok adne
dopasowanie. W przeciwnym przypadku dodaje
1 do wyniku z komórki B6.
=INDEKS(x;B6)
B9 Zwraca warto z zakresu x odpowiadaj c
wierszowi o numerze z komórki B6.
=INDEKS(x;B7)
B10 Zwraca warto z zakresu x odpowiadaj c
wierszowi o numerze z komórki B7.
=WYSZUKAJ(B9;x;y)
B12 Zwraca warto z zakresu y, która odpowiada
warto ci z zakresu x znajduj cej si w komórce B9.
=WYSZUKAJ(B10;x;y)
B13 Zwraca warto z zakresu y, która odpowiada
warto ci z zakresu x znajduj cej si w komórce B10.
=JE ELI(B4;0;
B15 Oblicza wspó czynnik dopasowania na podstawie
(B1-B3)/(B10-B9))
ró nicy pomi dzy warto ciami z zakresu x.
=B12+((B13-B12)*B15)
B16 Wyznacza warto z zakresu y przy u yciu
wspó czynnika dopasowania z komórki B15.
CZNE U YCIE 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).
Cz II Stosowanie funkcji w formu ach
274
Rysunek 8.18. Arkusz ten zawiera formu wykorzystuj c funkcje WYSZUKAJ i REGLINW
Poni ej wida formu z komórki B2. Podejmuje ona decyzj za pomoc funkcji JE ELI. Je li
w zakresie x zostanie znaleziona dok adnie pasuj ca warto , formu a zwraca odpowiadaj c
jej warto z zakresu y (za pomoc funkcji WYSZUKAJ). Je li dok adnie pasuj ca warto nie
zostanie znaleziona, formu a oblicza najlepiej pasuj c warto z zakresu y przy u yciu funkcji
REGLINW (nie wykonuje interpolacji liniowej).
=JE ELI(B1=WYSZUKAJ(B1;x;x);WYSZUKAJ(INDEKS(x;PODAJ.POZYCJ (WYSZUKAJ(B1;x;x);
x;0));x;y);REGLINW(y;x;B1))