Excel 2010 PL. Formuły...Spis treci Wstp 23 Co trzeba wiedzie 23 Co trzeba mie 23 Konwencje...

Idź do

• Spis treści• Przykładowy rozdział

• Katalog online

• Dodaj do koszyka

• Zamów cennik

• Zamów informacjeo nowościach

• Fragmenty książekonline

Helion SAul. Kościuszki 1c44-100 Gliwicetel. 32 230 98 63e-mail: [email protected]© Helion 1991–2010

Katalog książek

Twój koszyk

Cennik i informacje

Czytelnia

Kontakt

• Zamów drukowanykatalog

Excel 2010 PL.FormułyAutor: John Walkenbach

Tłumaczenie: Łukasz Piwko

ISBN: 978-83-246-2883-4

Tytuł oryginału: Excel 2010 Formulas

(Mr. Spreadsheet's Bookshelf)

Format: 172×245, stron: 880

Wykorzystaj wszystkie możliwości drzemiące w formułach!• Jak tworzyć formuły finansowe, tablicowe i tekstowe?

• Jak napisać własne funkcje arkusza w języku VBA?

• Jak za pomocą formuł tworzyć wykresy i tabele przestawne?

Jak potężnym i przydatnym narzędziem jest Excel, przekonują się nawet jego najzagorzalsi

przeciwnicy. Czy się ten program Microsoftu lubi, czy też nie, nie sposób kwestionować jego

możliwości, nieporównywalnych z innymi arkuszami kalkulacyjnymi. I choć dzięki temu Excel

w wielu zastosowaniach okazuje się niezastąpiony, wciąż znajdują się tacy, którzy boją się jego

obsługi i nie wychodzą poza kilka standardowych funkcji. W końcu nawet biegli użytkownicy

Excela bywają onieśmieleni jego imponującą funkcjonalnością. Jeśli zatem i Tobie wydawało się,

że doskonale znasz ten program, i nagle odkryłeś, jak wiele z jego potencjału wciąż jest poza

zasięgiem Twoich umiejętności, oto idealny podręcznik dla Ciebie! Książka napisana przez Johna

Walkenbacha, absolutnego guru Excela, dostarczy Ci wiedzy, która raz na zawsze odmieni Twoją

pracę w tym programie.

Najpierw przeczytasz, czym dokładnie jest formuła, jak ją stworzyć oraz edytować w Excelu 2010

i do czego można ją wykorzystywać. Dowiesz się wszystkiego na temat nazw, ich zakresów

i zarządzania nimi. W kolejnych częściach podręcznika zobaczysz, jak używać funkcji arkuszy

w formułach, przeczytasz o manipulowaniu tekstem, datach i godzinach oraz różnych technikach

liczenia. Odkryjesz, jak tworzyć bardzo przydatne formuły tekstowe, finansowe i tablicowe, oraz

przekonasz się, jak przydatne są one w pracy z wykresami i tabelami przestawnymi. Znajdziesz

tu również wiele interesujących, praktycznych formuł, których można używać w połączeniu

z funkcjami formatowania warunkowego i sprawdzania poprawności danych. A kiedy już

opanujesz te zagadnienia, dotrzesz do „megaformuł” i wykorzystywania języka VBA do tworzenia

własnych funkcji arkusza.

Dzięki tej książce:

• poznasz interfejs i nowe funkcje Excela 2010

• nauczysz się pracować z nazwami arkuszy, komórek i zakresów

• zaczniesz tworzyć przydatne formuły finansowe, tablicowe i tekstowe

• poznasz podstawowe i zaawansowane formuły liczące

• opanujesz formuły wyszukiwania oraz funkcje czasu i daty

• nauczysz się pracować z tabelami i bazami danych arkusza

• dowiesz się, czym są odwołania cykliczne i jak z nich korzystać

• poznasz techniki tworzenia wykresów i tabel przestawnych

• zaczniesz korzystać z formuł formatowania warunkowego

• opanujesz tworzenie i stosowanie „megaformuł”

• napiszesz własne, przydatne funkcje w języku VBA

• bez trudu usuniesz błędy w formułach

Chcesz, by Excel pracował za Ciebie? John Walkenbach pokaże Ci, jak to zrobić!

http://helion.pl

http://helion.pl/zamow_katalog.htm

http://helion.pl/katalog.htm

http://helion.pl/zakupy/add.cgi?id=ex21fo

http://helion.pl/emaile.cgi

http://helion.pl/cennik.htm

http://helion.pl/online.htm

mailto:[email protected]

http://helion.pl/ksiazki/ex21fo.htm

http://www.amazon.com/exec/obidos/ASIN/0470475366/helion-20

http://www.amazon.com/exec/obidos/ASIN/0470475366/helion-20

http://helion.pl/autorzy/waljoh.htm

Spis tre�ciWst�p 23Co trzeba wiedzie� 23Co trzeba mie� 23Konwencje typograficzne 24

Konwencje dotycz�ce klawiatury 24Konwencje myszy 25Co oznaczaj� ikony 26

Struktura ksi��ki 26Cz�� I: Informacje podstawowe 27Cz�� II: Stosowanie funkcji w formu�ach 27Cz�� III: Formu�y finansowe 27Cz�� IV: Formu�y tablicowe 27Cz�� V: Ró�ne techniki wykorzystania formu� 27Cz�� VI: Tworzenie niestandardowych funkcji arkusza 28Dodatki 28

Jak korzysta� z ksi��ki? 28Informacje o przyk�adach 29Informacje o dodatku Power Utility Pak 29Komentarze 29

Cz�� I Informacje podstawowe 31

Rozdzia� 1. Wst�p do Excela 33Historia Excela 34

Wszystko zacz��o si� od programu VisiCalc 34Nadej�cie Lotusa 34Do gry wchodzi Microsoft 35Wersje Excela 35

Koncepcja modelu obiektowego 39Zasada dzia�ania skoroszytów 40

Arkusze 41Arkusze wykresów 42Arkusze makr i arkusze dialogowe 42

Interfejs u�ytkownika programu Excel 43Nowy interfejs u�ytkownika 43Wst��ka 43Widok Backstage 46Menu podr�czne i minipasek narz�dzi 47Konfigurowanie interfejsu u�ytkownika 47Tagi inteligentne 48Okienko zada 49Przeci�gnij i upu�� 50Skróty klawiaturowe 50Dostosowywanie widoku na ekranie 51

8 Spis tre�ci

Wprowadzanie danych 51Zaznaczanie obiektów i komórek 53

Pomoc programu Excel 53Formatowanie komórek 54

Formatowanie numeryczne 54Formatowanie stylistyczne 55

Tabele 55Formu�y i funkcje arkuszy 56Obiekty na warstwie rysowania 56

Kszta�ty 57Ilustracje 57Obiekty obrazów po��czonych 57Formanty 58Wykresy 58Wykresy przebiegu w czasie 59

Dostosowywanie Excela 60Makra 60Dodatki 60

Funkcje internetowe 60Narz�dzia do analizy 61

Dost�p do baz danych 61Konspekty 62Zarz�dzanie scenariuszami 63Tabele przestawne 63Funkcje inspekcji 64Dodatek Solver 64

Opcje ochrony 64Ochrona formu� przed nadpisaniem 64Chronienie struktury skoroszytu 65Ochrona skoroszytu has�em 66

Rozdzia� 2. Podstawowe informacje na temat formu� 67Wprowadzanie i edycja formu� 68

Elementy formu�y 68Wstawianie formu�y 68Wklejanie nazw 70Spacje i z�amania wiersza 70Ograniczenia formu� 71Przyk�ady formu� 71Edytowanie formu� 72

Operatory u�ywane w formu�ach 73Operatory odniesienia 74Przyk�ady formu� z u�yciem operatorów 74Pierwszestwo operatorów 75Zagnie�d�anie nawiasów 77

Obliczanie warto�ci formu� 79Odniesienia do komórek i zakresów 80

Tworzenie odwo�a bezwzgl�dnych i mieszanych 80Tworzenie odwo�a do innych arkuszy lub skoroszytów 83

Robienie wiernej kopii formu�y 83Konwertowanie formu� na warto�ci 84Ukrywanie formu� 87

9Spis tre�ci

B��dy w formu�ach 88Co robi� z odwo�aniami cyklicznymi 89Szukanie wyniku 91

Przyk�ad szukania wyniku 91Szukanie wyniku — informacje dodatkowe 93

Rozdzia� 3. Praca z nazwami 95Co to jest nazwa 96Zakres nazw 97

Odwo�ania do nazw 97Odnoszenie si� do nazw z innego skoroszytu 98Konflikty nazw 98

Mened�er nazw 99Tworzenie nazw 100Edytowanie nazw 100Usuwanie nazw 101

Szybkie tworzenie nazw komórek i zakresów 101Okno dialogowe Nowa nazwa 101Tworzenie nazw przy u�yciu pola nazwy 103Automatyczne tworzenie nazw 104Nazywanie ca�ych wierszy i kolumn 106Nazwy tworzone przez Excela 106

Tworzenie nazw obejmuj�cych kilka arkuszy 107Praca z nazwami komórek i zakresów 109

Tworzenie listy nazw 109U�ywanie nazw w formu�ach 110U�ywanie operatora przeci�cia z nazwami 111U�ywanie operatora zakresu z nazwami 113Odwo�ywanie si� do pojedynczej komórki w zakresie nazwanym obejmuj�cym kilka arkuszy 113Wstawianie nazw do istniej�cych formu� 114Automatyczne wstawianie nazw podczas tworzenia formu�y 115Usuwanie nazw 115Nazwy z b��dami 115Przegl�danie nazw zakresów 116Stosowanie nazw w wykresach 117

Obs�uga nazw komórek i zakresów przez Excela 117Wstawianie wiersza lub kolumny 117Usuwanie wiersza lub kolumny 117Wycinanie i wklejanie 118

Potencjalne problemy z nazwami 118Problemy wyst�puj�ce podczas kopiowania arkuszy 118Problemy z nazwami przy usuwaniu arkuszy 120

Klucz do zrozumienia nazw 121Nazywanie warto�ci sta�ych 122Nazywanie sta�ych tekstowych 123U�ywanie funkcji arkusza w nazwanych formu�ach 124U�ywanie odwo�a do komórek i zakresów w formu�ach nazwanych 125U�ywanie formu� nazwanych zawieraj�cych odwo�ania wzgl�dne 126

Zaawansowane techniki u�ywania nazw 130U�ywanie funkcji ADR.PO�R z zakresem nazwanym 130U�ycie funkcji ADR.PO�R do tworzenia zakresu nazwanego o sta�ym adresie 131U�ywanie tablic w formu�ach nazwanych 131Tworzenie dynamicznych formu� nazwanych 133

10 Spis tre�ci

Cz�� II Stosowanie funkcji w formu�ach 135

Rozdzia� 4. Wprowadzenie do funkcji arkusza 137Co to jest funkcja 137

Upraszczanie formu� 138Wykonywanie oblicze niemo�liwych do wykonania w inny sposób 138Przyspieszanie zada edycyjnych 139Podejmowanie decyzji przez formu�� 139Wi�cej na temat funkcji 140

Typy argumentów funkcji 140Nazwy w roli argumentów 141Ca�e kolumny i wiersze w roli argumentów 142Warto�ci literalne w roli argumentów 142Wyra�enia w roli argumentów 143Funkcje w roli argumentów 143Tablice w roli argumentów 143

Sposoby wstawiania funkcji do formu� 144R�czne wpisywanie funkcji 144Biblioteka funkcji 145Okno dialogowe Wstawianie funkcji 146Dodatkowe wskazówki na temat wstawiania funkcji 148

Kategorie funkcji 150Funkcje finansowe 150Funkcje daty i godziny 150Funkcje matematyczne i trygonometryczne 151Funkcje statystyczne 151Funkcje wyszukiwania i odwo�a 151Funkcje baz danych 151Funkcje tekstowe 151Funkcje logiczne 152Funkcje informacyjne 152Funkcje zdefiniowane przez u�ytkownika 152Funkcje in�ynierskie 152Funkcje modu�owe 152Funkcje zgodno�ci 152Inne kategorie funkcji 153

Rozdzia� 5. Manipulowanie tekstem 155Kilka s�ów na temat tekstu 155

Ile znaków mo�e pomie�ci� jedna komórka 156Liczby jako tekst 156

Funkcje tekstowe 157Sprawdzanie, czy komórka zawiera tekst 158Praca z kodami znaków 158Sprawdzanie, czy dwa ci�gi s� identyczne 161��czenie dwóch lub wi�kszej liczby komórek 161Wy�wietlanie sformatowanych warto�ci jako tekst 162Wy�wietlanie warto�ci walutowych jako tekst 164Usuwanie niepotrzebnych spacji i niedrukowalnych znaków 164Liczenie znaków w ci�gu 164Powtarzanie znaku lub ci�gu 165Tworzenie histogramu tekstowego 165Dope�nianie liczby 166

11Spis tre�ci

Zmiana wielko�ci liter 168Wydobywanie znaków z ci�gu 169Podmienianie tekstu innym tekstem 169Znajdowanie i szukanie w ci�gu 170Znajdowanie i zamienianie ci�gów 171

Zaawansowane formu�y tekstowe 171Zliczanie okre�lonych znaków w komórce 172Zliczanie wyst�pie podci�gu w komórce 172Usuwanie znaków minusa z koca 172Sprawdzanie numeru litery kolumny po jej numerze 173Wydobywanie nazwy pliku ze �cie�ki 173Wydobywanie pierwszego wyrazu z ci�gu 173Wydobywanie ostatniego wyrazu z ci�gu 174Wydobywanie wszystkiego poza pierwszym wyrazem w ci�gu 174Wydobywanie pierwszych imion, drugich imion i nazwisk 175Usuwanie tytu�u sprzed imienia lub nazwiska 177Zliczanie s�ów w komórce 177

Rozdzia� 6. Funkcje daty i czasu 179Jak Excel obs�uguje daty i godziny 179

Liczby seryjne dat 180Wprowadzanie dat 181Liczby seryjne godzin i minut 183Wprowadzanie godzin 183Formatowanie dat i godzin 185Problemy z datami 186

Funkcje daty 188Wy�wietlanie aktualnej daty 188Wy�wietlanie dowolnej daty 190Generowanie serii dat 191Konwersja ci�gów tekstowych na daty 192Obliczanie liczby dni dziel�cych dwie daty 193Obliczanie liczby dni powszednich mi�dzy dwiema datami 193Obliczanie daty, bior�c pod uwag� tylko dni robocze 195Obliczanie liczby lat dziel�cych dwie daty 195Obliczanie wieku osób 197Okre�lanie dnia roku 197Okre�lanie dnia tygodnia 198Okre�lanie daty ostatniej niedzieli 198Okre�lanie daty pierwszego wyst�pienia dnia tygodnia po okre�lonej dacie 198Okre�lanie n-tego wyst�pienia dnia tygodnia w miesi�cu 199Zliczanie wyst�pie dnia tygodnia 199Obliczanie dat �wi�t 201Okre�lanie daty ostatniego dnia miesi�ca 203Sprawdzanie, czy dany rok jest przest�pny 204Sprawdzanie kwarta�u roku 204Konwersja roku na liczby rzymskie 204

Funkcje czasu 205Wy�wietlanie bie��cego czasu 205Wy�wietlanie dowolnego czasu 206Sumowanie czasów powy�ej 24 godzin 207Obliczanie ró�nicy mi�dzy dwiema warto�ciami czasu 209Konwersja z czasu wojskowego 211Konwersja godzin, minut i sekund w zapisie dziesi�tnym na warto�ci czasu 211

12 Spis tre�ci

Dodawanie godzin, minut i sekund do warto�ci czasu 212Konwersja pomi�dzy strefami czasowymi 213Zaokr�glanie warto�ci czasu 214Praca z warto�ciami nieb�d�cymi godzinami dnia 214

Rozdzia� 7. Techniki liczenia i sumowania 217Liczenie i sumowanie komórek 218Zliczanie i sumowanie rekordów w bazach danych i tabelach przestawnych 220Podstawowe formu�y licz�ce 221

Obliczanie sumy komórek 222Zliczanie pustych komórek 222Zliczanie niepustych komórek 223Zliczanie komórek z liczbami 223Zliczanie komórek niezawieraj�cych tekstu 223Zliczanie komórek tekstowych 224Zliczanie warto�ci logicznych 224Zliczanie warto�ci b��dów w zakresie 224

Zaawansowane formu�y licz�ce 225Liczenie komórek przy u�yciu funkcji LICZ.JE�ELI 225Zliczanie komórek spe�niaj�cych wiele kryteriów 225Zliczanie liczby wyst�pie najcz��ciej pojawiaj�cego si� wpisu 229Zliczanie wyst�pie okre�lonego tekstu 231Liczenie unikatowych warto�ci 233Tworzenie rozk�adu cz�sto�ci 234

Formu�y sumuj�ce 240Sumowanie wszystkich komórek w zakresie 240Obliczanie narastaj�cej sumy 242Sumowanie okre�lonej liczby najwi�kszych warto�ci 243

Sumowanie warunkowe z jednym kryterium 244Sumowanie tylko warto�ci ujemnych 245Sumowanie warto�ci w oparciu o inny zakres 245Sumowanie warto�ci w oparciu o porównanie tekstowe 246Sumowanie warto�ci w oparciu o porównanie daty 246

Sumowanie warunkowe przy zastosowaniu wielu kryteriów 247U�ycie kryteriów „i” 248U�ycie kryteriów „lub” 249U�ycie kryteriów „i” oraz „lub” 249

Rozdzia� 8. Funkcje wyszukiwania 251Co to jest formu�a wyszukiwania 251Funkcje zwi�zane z wyszukiwaniem 253Podstawowe formu�y wyszukiwania 253

Funkcja WYSZUKAJ.PIONOWO 254Funkcja WYSZUKAJ.POZIOMO 255Funkcja WYSZUKAJ 256��czne u�ycie funkcji PODAJ.POZYCJ i INDEKS 258

Wyspecjalizowane formu�y wyszukuj�ce 259Wyszukiwanie dok�adnej warto�ci 260Wyszukiwanie warto�ci w lew� stron� 262Wyszukiwanie z rozró�nianiem ma�ych i wielkich liter 263Wybieranie spo�ród wielu tabel 263Okre�lanie ocen na podstawie wyników testu 264Obliczanie �redniej ocen 265Wyszukiwanie w dwie strony 266

13Spis tre�ci

Wyszukiwanie dwukolumnowe 268Sprawdzanie adresu warto�ci w zakresie 269Wyszukiwanie warto�ci przy u�yciu najbli�szego dopasowania 270Wyszukiwanie warto�ci przy u�yciu interpolacji liniowej 271

Rozdzia� 9. Tabele i bazy danych arkusza 275Tabele i terminologia 276

Przyk�ad bazy danych arkusza 276Przyk�ad tabeli 277Zastosowania baz danych arkusza i tabel 278

Praca z tabelami 279Tworzenie tabeli 281Zmiana wygl�du tabeli 282Nawigacja i zaznaczanie w tabeli 283Dodawanie wierszy lub kolumn 283Usuwanie wierszy lub kolumn 284Przenoszenie tabeli 285Ustawianie opcji stylu tabeli 285Usuwanie powtarzaj�cych si� wierszy z tabeli 287Sortowanie i filtrowanie tabeli 288Praca z wierszem sumy 292Stosowanie formu� w tabelach 295Odwo�ywanie si� do danych w tabeli 297Konwersja tabeli na baz� danych arkusza 301

Filtrowanie zaawansowane 302Ustawianie zakresu kryteriów 303Stosowanie filtru zaawansowanego 304Usuwanie filtru zaawansowanego 305

Okre�lanie kryteriów filtru zaawansowanego 306Okre�lanie pojedynczego kryterium 306Okre�lanie wielu kryteriów 309Okre�lanie kryteriów utworzonych w wyniku u�ycia formu�y 312

Funkcje bazy danych 313Wstawianie sum cz��ciowych 315

Rozdzia� 10. Ró�ne obliczenia 319Konwersja jednostek 319Rozwi�zywanie trójk�tów prostok�tnych 321Obliczanie pola powierzchni, obwodu i obj�to�ci 324

Obliczanie pola powierzchni i obwodu kwadratu 324Obliczanie pola powierzchni i obwodu prostok�ta 324Obliczanie pola powierzchni i obwodu ko�a 325Obliczanie pola powierzchni trapezu 325Obliczanie pola powierzchni trójk�ta 325Obliczanie pola powierzchni i obj�to�ci kuli 325Obliczanie pola powierzchni i obj�to�ci sze�cianu 326Obliczanie pola powierzchni i obj�to�ci sto�ka 326Obliczanie obj�to�ci walca 326Obliczanie obj�to�ci ostros�upa 327

Rozwi�zywanie uk�adów równa 327Zaokr�glanie liczb 328

Podstawowe formu�y zaokr�glaj�ce 329Zaokr�glanie do najbli�szej wielokrotno�ci 330Zaokr�glanie warto�ci walutowych 330

14 Spis tre�ci

Praca z u�amkami dolarów 331Stosowanie funkcji ZAOKR.DO.CA�K i LICZBA.CA�K 332Zaokr�glanie do parzystej lub nieparzystej liczby ca�kowitej 333Zaokr�glanie do n cyfr znacz�cych 333

Cz�� III Formu�y finansowe 335

Rozdzia� 11. Formu�y kredytów i inwestycji 337Poj�cia finansowe 338

Warto�� pieni�dza w czasie 338Wp�ywy i p�atno�ci 338Dopasowywanie okresów czasu 339Wyznaczanie terminu pierwszej p�atno�ci 339

Podstawowe funkcje finansowe 340Obliczanie warto�ci bie��cej 340Obliczanie przysz�ej warto�ci 344Obliczanie p�atno�ci 347Obliczanie stóp 349Obliczanie liczby rat 351

Obliczanie sk�adników p�atno�ci 353Funkcje IPMT i PPMT 353Funkcje CUMIPMT i CUMPRINC 355

Konwersja stóp procentowych 356Metody przedstawiania stóp procentowych 356Formu�y konwersji 356

Ograniczenia funkcji finansowych 357Odroczony pocz�tek serii regularnych p�atno�ci 358Szacowanie serii zmiennych p�atno�ci 359

Obliczenia dotycz�ce obligacji 360Wyznaczanie ceny obligacji 360Obliczanie rentowno�ci 362

Rozdzia� 12. Formu�y dyskontowe i amortyzacji 363Funkcja NPV 364

Definicja funkcji NPV 364Przyk�ady u�ycia funkcji NPV 366Obliczanie kwot nagromadzonych za pomoc� funkcji NPV 372

Funkcja IRR — stosowanie 374Stopa zwrotu 375Geometryczne wskaniki przyrostu 376Sprawdzanie wyników 377

Kilka stóp funkcji IRR i MIRR 378Kilka wewn�trznych stóp zwrotu 378Rozdzielanie przep�ywów 380U�ycie sald zamiast przep�ywów 381

Nieregularne przep�ywy �rodków 382Warto�� bie��ca netto 382Wewn�trzna stopa zwrotu 383

Funkcja FVSCHEDULE 384Obliczanie zwrotu w skali roku 384

Obliczanie amortyzacji 385

15Spis tre�ci

Rozdzia� 13. Harmonogramy finansowe 389Tworzenie harmonogramów finansowych 389Tworzenie harmonogramów amortyzacji 390

Prosty harmonogram amortyzacji 390Dynamiczny harmonogram amortyzacji 393Tabele p�atno�ci i odsetek 397Obliczenia dotycz�ce karty kredytowej 399

Zestawianie opcji po�yczek w tabelach danych 400Tworzenie tabeli danych z jedn� zmienn� 401Tworzenie tabeli danych z dwiema zmiennymi 403

Sprawozdania finansowe 405Podstawowe sprawozdania finansowe 405Analiza wskaników 409

Tworzenie indeksów 412

Cz�� IV Formu�y tablicowe 415

Rozdzia� 14. Wprowadzenie do tablic 417Wprowadzenie do formu� tablicowych 417

Wielokomórkowa formu�a tablicowa 418Jednokomórkowa formu�a tablicowa 419Tworzenie sta�ej tablicowej 420Elementy sta�ej tablicowej 421

Wymiary tablicy — informacje 422Jednowymiarowe tablice poziome 422Jednowymiarowe tablice pionowe 422Tablice dwuwymiarowe 423

Nadawanie nazw sta�ym tablicowym 424Praca z formu�ami tablicowymi 425

Wprowadzanie formu�y tablicowej 426Zaznaczanie zakresu formu�y tablicowej 426Edycja formu�y tablicowej 426Powi�kszanie i zmniejszanie wielokomórkowych formu� tablicowych 428

Stosowanie wielokomórkowych formu� tablicowych 429Tworzenie tablicy z warto�ci w zakresie 429Tworzenie sta�ej tablicowej z warto�ci w zakresie 429Wykonywanie dzia�a na tablicach 430U�ywanie funkcji z tablicami 431Transponowanie tablicy 432Generowanie tablicy kolejnych liczb ca�kowitych 433

Jednokomórkowe formu�y tablicowe 434Liczenie znaków w zakresie 434Sumowanie trzech najmniejszych warto�ci w zakresie 435Zliczanie komórek tekstowych w zakresie 436Pozbywanie si� formu� po�rednich 438Zastosowanie tablicy zamiast adresu zakresu 440

Rozdzia� 15. Magia formu� tablicowych 441Stosowanie jednokomórkowych formu� tablicowych 441

Sumowanie zakresu zawieraj�cego b��dy 442Zliczanie b��dów warto�ci w zakresie komórek 443Sumowanie n najwi�kszych warto�ci w zakresie 444Obliczanie �redniej z pomini�ciem zer 444

16 Spis tre�ci

Sprawdzanie wyst�powania okre�lonej warto�ci w zakresie 446Zliczanie liczby ró�nic w dwóch zakresach 447Zwracanie lokalizacji maksymalnej warto�ci w zakresie 448Odszukiwanie wiersza n-tego wyst�pienia warto�ci w zakresie 448Zwracanie najd�u�szego tekstu w zakresie 449Sprawdzanie, czy zakres zawiera poprawne warto�ci 449Sumowanie cyfr liczby ca�kowitej 450Sumowanie warto�ci zaokr�glonych 451Sumowanie wszystkich n-tych warto�ci w zakresie 452Usuwanie nienumerycznych znaków z �acucha 453Odszukiwanie najbli�szej warto�ci w zakresie 454Zwracanie ostatniej warto�ci w kolumnie 455Zwracanie ostatniej warto�ci w wierszu 456Szeregowanie danych przy u�yciu formu�y tablicowej 456

Stosowanie wielokomórkowych formu� tablicowych 457Zwracanie wy��cznie dodatnich warto�ci w zakresie 458Zwracanie niepustych komórek z zakresu 459Odwracanie kolejno�ci komórek w zakresie 459Dynamiczne sortowanie warto�ci w zakresie 460Zwracanie listy unikalnych elementów zakresu 461Wy�wietlanie kalendarza w zakresie komórek 462

Cz�� V Ró�ne techniki wykorzystania formu� 465

Rozdzia� 16. Zamierzone odwo�ania cykliczne 467Czym s� odwo�ania cykliczne? 467

Korygowanie niezamierzonych odwo�a cyklicznych 468Istota po�rednich odwo�a cyklicznych 469

Zamierzone odwo�ania cykliczne 470W jaki sposób Excel okre�la ustawienia oblicze i iteracji 473Przyk�ady odwo�a cyklicznych 474

Generowanie losowych, unikalnych liczb ca�kowitych 474Rozwi�zywanie równa rekursywnych 475Rozwi�zywanie uk�adów równa przy u�yciu odwo�a cyklicznych 477Animowanie wykresów przy u�yciu iteracji 479

Potencjalne problemy z zamierzonymi odwo�aniami cyklicznymi 480Rozdzia� 17. Techniki tworzenia wykresów 481

Dzia�anie formu�y SERIE 482U�ywanie nazw w formule SERIE 484Oddzielanie serii danych na wykresie od zakresu danych 484

Tworzenie powi�za do komórek 487Tworzenie po��czenia z tytu�em wykresu 487Tworzenie powi�za z tytu�ami osi 488Tworzenie powi�za z etykietami danych 488Tworzenie powi�za tekstowych 488Dodawanie obrazu po��czonego do wykresu 489

Przyk�ady wykresów 489Wykres post�pów w d��eniu do celu 489Tworzenie wykresu w kszta�cie miernika 491Warunkowe wy�wietlanie kolorów na wykresie kolumnowym 492Tworzenie histogramu porównawczego 493Tworzenie wykresów Gantta 495

17Spis tre�ci

Tworzenie wykresu gie�dowego 497Kre�lenie co n-tego punktu danych 499Kre�lenie n ostatnich punktów danych 501Zaznaczanie serii danych za pomoc� okna kombi 502Tworzenie wykresów funkcji matematycznych 504Kre�lenie okr�gu 508Wykres w kszta�cie zegara 510Tworzenie wspania�ych wykresów 512

Tworzenie wykresów linii trendów 513Liniowe wykresy trendów 514Nieliniowe wykresy trendu 518

Rozdzia� 18. Tabele przestawne 523O tabelach przestawnych 523Przyk�ad tabeli przestawnej 524Dane odpowiednie dla tabeli przestawnej 526Tworzenie tabeli przestawnej 529

Wskazywanie danych 530Wyznaczanie lokalizacji tabeli przestawnej 530Definiowanie uk�adu tabeli przestawnej 531Formatowanie tabeli przestawnej 534Modyfikowanie tabeli przestawnej 535

Wi�cej przyk�adów tabel przestawnych 538Pytanie 1. 538Pytanie 2. 539Pytanie 3. 540Pytanie 4. 541Pytanie 5. 542Pytanie 6. 543Pytanie 7. 544

Grupowanie elementów tabeli przestawnej 544Przyk�ad grupowania r�cznego 545Przegl�danie zgrupowanych danych 546Przyk�ady grupowania automatycznego 547

Tworzenie rozk�adu liczebno�ci 551Tworzenie pól i elementów obliczeniowych 553

Tworzenie pola obliczeniowego 555Wstawianie elementu obliczeniowego 557

Filtrowanie tabel przestawnych przy u�yciu fragmentatorów 560Odwo�ania do komórek w tabeli przestawnej 562Jeszcze jeden przyk�ad tabeli przestawnej 564Tworzenie raportu tabeli przestawnej 567

Rozdzia� 19. Formatowanie warunkowe i sprawdzanie poprawno�ci danych 569Formatowanie warunkowe 569

Wybieranie formatowania warunkowego 571Formaty warunkowe wykorzystuj�ce grafik� 574Stosowanie formatów warunkowych 583Tworzenie regu� opartych na formu�ach 585

Sprawdzanie poprawno�ci danych 595Definiowanie kryteriów sprawdzania poprawno�ci danych 596Typy kryteriów sprawdzania poprawno�ci danych, jakich mo�esz u�y� 597Tworzenie list rozwijanych 599Stosowanie formu� w regu�ach sprawdzania poprawno�ci danych 600

18 Spis tre�ci

Rozdzia� 20. Tworzenie megaformu� 605Czym jest megaformu�a? 605Tworzenie megaformu�y — prosty przyk�ad 606Przyk�ady megaformu� 609

Usuwanie drugich imion przy u�yciu megaformu�y 609U�ycie megaformu�y zwracaj�cej pozycj� ostatniego znaku spacji w �acuchu 613Zastosowanie megaformu�y do sprawdzania poprawno�ci numerów kart kredytowych 617Generowanie nazwisk losowych 622

Zalety i wady megaformu� 623Rozdzia� 21. Narz�dzia i metody usuwania b��dów w formu�ach 625

Debugowanie formu�? 625Problemy z formu�ami i ich rozwi�zania 626

Niedopasowanie nawiasów 627Komórki wype�nione symbolami krzy�yka 629Puste komórki, które wcale nie s� puste 629Nadmiarowe znaki spacji 630Formu�y zwracaj�ce b��d 630Problemy z odwo�aniami wzgl�dnymi i bezwzgl�dnymi 635Problemy z pierwszestwem operatorów 635Formu�y nie s� obliczane 637Warto�ci rzeczywiste i wy�wietlane 637B��dy liczb zmiennoprzecinkowych 638B��dy nieistniej�cych ��czy 639B��dy warto�ci logicznych 640B��dy odwo�a cyklicznych 641

Narz�dzia inspekcyjne w Excelu 641Identyfikowanie komórek okre�lonego typu 641Przegl�danie formu� 642�ledzenie relacji pomi�dzy komórkami 644�ledzenie warto�ci b��dów 646Naprawianie b��dów odwo�a cyklicznych 646Funkcja sprawdzania b��dów w tle 647Korzystanie z funkcji Szacowanie formu�y 649

Cz�� VI Tworzenie niestandardowych funkcji arkusza 651

Rozdzia� 22. Wprowadzenie do VBA 653Kilka s�ów o VBA 653Wy�wietlanie karty Deweloper 654O bezpieczestwie makr 655Zapisywanie skoroszytów zawieraj�cych makra 656Wprowadzenie do Visual Basic Editor 657

Aktywowanie VB Editor 657Elementy VB Editor 658Korzystanie z okna projektu 659Korzystanie z okna kodu 662Wprowadzanie kodu VBA 664Zapisywanie projektu 667

19Spis tre�ci

Rozdzia� 23. Podstawy tworzenia funkcji niestandardowych 669Po co tworzy si� funkcje niestandardowe? 670Wprowadzaj�cy przyk�ad funkcji VBA 670O procedurach Function 673

Deklarowanie funkcji 673Wybór nazwy dla funkcji 674U�ywanie funkcji w formu�ach 674U�ycie argumentów w funkcjach 676

Korzystanie z okna dialogowego Wstawianie funkcji 676Dodawanie opisu funkcji 677Okre�lanie kategorii funkcji 678Dodawanie opisów argumentów 679

Testowanie i debugowanie funkcji 681U�ycie instrukcji VBA MsgBox 682U�ycie instrukcji Debug.Print w kodzie 684Wywo�ywanie funkcji z procedury Sub 685Ustawianie punktu kontrolnego w funkcji 688

Tworzenie dodatków 689Rozdzia� 24. Koncepcje programowania w VBA 691

Wprowadzaj�cy przyk�ad procedury Function 692Umieszczanie komentarzy wewn�trz kodu 694U�ycie zmiennych, typów danych i sta�ych 695

Definiowanie typów danych 695Deklarowanie zmiennych 697U�ycie sta�ych 698U�ycie �acuchów 700U�ycie dat 700

U�ycie wyra�e przypisania 701U�ycie tablic 702

Deklarowanie tablic 703Deklarowanie tablic wielowymiarowych 703

U�ycie wbudowanych funkcji VBA 704Sterowanie wykonaniem 705

Konstrukcja If-Then 706Konstrukcja Select Case 708Bloki zap�tlaj�ce 709Instrukcja On Error 713

U�ycie zakresów 715Konstrukcja For Each-Next 715Odwo�ania do zakresów 716Kilka u�ytecznych w�a�ciwo�ci zakresów 719S�owo kluczowe Set 723Funkcja Intersect 723Funkcja Union 724W�a�ciwo�� UsedRange 724

20 Spis tre�ci

Rozdzia� 25. Przyk�ady niestandardowych funkcji VBA 727Proste funkcje 728

Czy komórka zawiera formu��? 728Zwracanie formu�y zawartej w komórce 728Czy komórka jest ukryta? 729Zwracanie nazwy arkusza 730Odczytywanie nazwy skoroszytu 730Odczytywanie nazwy aplikacji 731Odczytywanie numeru wersji Excela 731Odczytywanie informacji o formatowaniu komórki 732

Sprawdzanie typu danych w komórce 734Funkcje wielofunkcyjne 735Generowanie liczb losowych 738

Generowanie niezmiennych liczb losowych 738Losowe zaznaczanie komórek 739

Obliczanie prowizji od sprzeda�y 741Funkcja dla prostej struktury prowizji 742Funkcja dla bardziej z�o�onej struktury prowizji 743

Funkcje do manipulowania tekstem 744Odwracanie �acucha 744Mieszanie tekstu 744Zwracanie akronimu 745Czy tekst jest zgodny z wzorcem? 746Czy komórka zawiera okre�lone s�owo? 747Czy komórka zawiera tekst? 748Wyodr�bnianie n-tego elementu �acucha 748S�owny zapis liczb 749

Funkcje zliczaj�ce 750Zliczanie komórek zgodnych z wzorcem 750Zliczanie arkuszy w skoroszycie 751Zliczanie wyrazów w zakresie 751Zliczanie kolorów 752

Funkcje operuj�ce na datach 752Obliczanie daty nast�pnego poniedzia�ku 753Obliczanie daty nast�pnego dnia tygodnia 753Który tydzie miesi�ca? 754Obs�uga dat sprzed 1900 roku 754

Zwracanie ostatniej, niepustej komórki w kolumnie lub wierszu 756Funkcja OSTATNIAWKOLUMNIE 756Funkcja OSTATNIAWWIERSZU 757

Funkcje wieloarkuszowe 757Zwracanie maksymalnej warto�ci z wielu arkuszy 758Funkcja SHEETOFFSET 759

Zaawansowane techniki tworzenia funkcji 760Zwracanie warto�ci b��du 760Zwracanie tablicy przez funkcj� 762Zwracanie tablicy niepowtarzalnych, losowych liczb ca�kowitych 763Zwracanie tablicy losowych liczb ca�kowitych z podanego zakresu 765Stosowanie argumentów opcjonalnych 767Pobieranie nieokre�lonej liczby argumentów 768

21Spis tre�ci

Dodatki

Dodatek A Wykaz funkcji Excela 775Dodatek B Niestandardowe formaty liczbowe 793

O formatowaniu liczb 793Automatyczne formatowanie liczb 794Formatowanie liczb przy u�yciu Wst��ki 795Formatowanie liczb przy u�yciu skrótów klawiaturowych 795Formatowanie liczb przy u�yciu okna dialogowego Formatowanie komórek 796

Tworzenie niestandardowego formatu liczbowego 797Elementy �acucha formatu liczbowego 799Kody niestandardowego formatu liczbowego 800

Przyk�ady niestandardowych formatów liczbowych 802Skalowanie warto�ci 803Ukrywanie zer 806Wy�wietlanie zer poprzedzaj�cych 807Wy�wietlanie u�amków 807Wy�wietlanie N/D zamiast tekstu 808Wy�wietlanie tekstu w cudzys�owach 808Powielanie wpisu w komórce 808Wy�wietlanie minusa po prawej stronie 809Warunkowe formatowanie liczb 809Wy�wietlanie warto�ci w kolorach 810Formatowanie dat i godzin 811Wy�wietlanie tekstu z liczbami 811Wy�wietlanie kresek zamiast zer 812U�ycie symboli specjalnych 812Ukrywanie poszczególnych typów informacji 813Wype�nianie komórek powtarzaj�cymi si� znakami 813Wy�wietlanie kropek wiod�cych 814

Dodatek C Dodatkowe zasoby Excela 815System pomocy Excela 815Wsparcie techniczne ze strony Microsoftu 816

Opcje wsparcia 816Pomoc techniczna firmy Microsoft 816Strona domowa programu Microsoft Excel 816Strona domowa pakietu Microsoft Office 816

Internetowe grupy dyskusyjne 817Dost�p do grup dyskusyjnych przy u�yciu czytnika 817Dost�p do grup dyskusyjnych przy u�yciu przegl�darki internetowej 818Przeszukiwanie grup dyskusyjnych 819

Witryny internetowe 820Strona Spreadsheet 820Daily Dose of Excel 820Strona Jona Peltiera 821Pearson Software Consulting 821Contextures 821Strony Davida McRitchiego 821Pointy Haired Dilbert 821Mr. Excel 821

22 Spis tre�ci

Dodatek D Przyk�ady do��czone do ksi��ki 823Rozdzia� 1. 824Rozdzia� 5. 824Rozdzia� 6. 825Rozdzia� 7. 825Rozdzia� 8. 825Rozdzia� 9. 826Rozdzia� 10. 826Rozdzia� 11. 826Rozdzia� 12. 827Rozdzia� 13. 827Rozdzia� 15. 827Rozdzia� 16. 828Rozdzia� 17. 828Rozdzia� 18. 829Rozdzia� 19. 830Rozdzia� 20. 830Rozdzia� 24. 831Rozdzia� 25. 831Dodatek A 832Dodatek B 832

Skorowidz 833

251

Rozdzia� 8

Funkcje wyszukiwaniaW 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 wyszukiwaniaFormu�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 dopieropotem sprawdzamy numer.

252 Cz�� II Stosowanie funkcji w formu�ach

UWAGAPod poj�ciem tabela rozumiem prostok�tny zakres danych. Nie musi to by�„prawdziwa” tabela, taka jak te, które tworzy si� za pomoc� opcjiWstawianie/Tabele/Tabela.

Rysunek 8.1 przedstawia prosty arkusz, na którym u�yto kilku funkcji wyszukiwania. Zawieraon 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

C2 =WYSZUKAJ.PIONOWO(B2; DanePracowników;2; FA�SZ)

D2 =WYSZUKAJ.PIONOWO(B2; DanePracowników;3; FA�SZ)

E2 =WYSZUKAJ.PIONOWO(B2; DanePracowników;4; FA�SZ)

F2 =WYSZUKAJ.PIONOWO(B2; DanePracowników;5; FA�SZ)

Rysunek 8.1. 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 czterechformu�. W wielu sytuacjach potrzebna jest tylko jedna informacja z tabeli. Wtedy nale�yu�y� tylko jednej formu�y.

253Rozdzia� 8. Funkcje wyszukiwania

Funkcje zwi�zanez wyszukiwaniemW Excelu jest dost�pnych kilka funkcji przydatnych podczas pisania formu� wyszukiwaniadanych 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�ciz ostatniej kolumny w zakresie.

WYSZUKAJ.PIONOWO Wyszukiwanie pionowe. Wyszukuje warto�ci w pierwszej kolumnietabeli i zwraca warto�� znajduj�c� si� w tym samym wierszuw okre�lonej kolumnie w tabeli.

WYSZUKAJ.POZIOMO Wyszukiwanie poziome. Szuka warto�ci w górnym wierszu tabelii zwraca warto�� znajduj�c� si� w tej samej kolumnie w okre�lonymwierszu w tabeli.

W przyk�adach prezentowanych w tym rozdziale u�ywane s� funkcje z tabeli 8.1.

Podstawoweformu�y wyszukiwaniaZa pomoc� podstawowych funkcji wyszukiwania mo�na przeszuka� kolumn� lub wierszw 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 adreswzgl�dny komórki zawieraj�cej poszukiwan� warto��.


NA FTPPrzyk�ady prezentowane w tym podrozdziale mo�na znale� w pliku podstawoweformu�y wyszukiwania.xlsx, który znajduje si� na serwerze FTP.

Funkcja WYSZUKAJ.PIONOWOFunkcja 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 kolumnietablicy.

� tablica — zakres zawieraj�cy tablic� do przeszukania.

� numer_kolumny — numer kolumny w tablicy, z której zwracana jest pasuj�cawarto��.

� kolumna — opcjonalny. Je�li ma warto�� PRAWDA lub nie jest podany, funkcja zwracadopasowanie 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 takiegonie znajdzie, zwróci b��d #N/D.

UWAGAJe�li argument kolumna ma warto�� PRAWDA lub zosta� pomini�ty, pierwsza kolumnaprzeszukiwanej 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� posortowanaw rosn�cym porz�dku. Je�li dok�adne dopasowanie nie zostanie znalezione,funkcja zwraca b��d #N/D.

WSKAZÓWKAJe�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 dowolnyjeden znak.


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 podatkudochodowego 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 argumentfunkcji 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 niezostanie znalezione dok�adne dopasowanie, funkcja wykorzystuje nast�pn� najwi�ksz� warto��mniejsz� od wyszukiwanej warto�ci. Innymi s�owy, funkcja u�ywa wiersza zawieraj�cegowarto�� 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.POZIOMOFunkcja WYSZUKAJ.POZIOMO dzia�a dok�adnie tak samo jak funkcja WYSZUKAJ.PIONOWO z tymwyj�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 wierszuw 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 wierszuprzeszukiwanej tabeli.


� tabela_tablica — zakres zawieraj�cy przeszukiwan� tabel�.

� numer_indeksu_wiersza — numer wiersza w tabeli, z którego ma by� zwróconapasuj�ca warto��.

� przeszukiwany_zakres — opcjonalny. Je�li ma warto�� PRAWDA lub zostaniepomini�ty, zwracane jest dopasowanie przybli�one (je�li dok�adne dopasowanienie zostanie znalezione, zwracana jest nast�pna najwi�ksza warto�� mniejsza odszukanej 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ÓWKAJe�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 WYSZUKAJSk�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 przeszukiwanymwektorze.

� przeszukiwany_wektor — sk�adaj�cy si� z jednej kolumny lub jednego wierszazakres zawieraj�cy warto�ci do wyszukania. Musz� one by� uporz�dkowanew kolejno�ci rosn�cej.

� wektor_wynikowy — sk�adaj�cy si� z jednej kolumny lub jednego wiersza zakreszawieraj�cy warto�ci, które maj� by� zwrócone. Musi mie� taki sam rozmiar jakprzeszukiwany wektor.


Funkcja WYSZUKAJ przeszukuje jednokolumnowy lub jednowierszowy zakres (przeszukiwany_wektor)w celu znalezienia warto�ci (szukana_warto��) i zwraca warto�� o takim samym po�o�eniuw drugim jednowierszowym lub jednokolumnowym zakresie (wektor_wynikowy).

OSTRZE�ENIEWarto�ci w przeszukiwanym wektorze musz� by� posortowane rosn�co.Je�li szukana warto�� jest mniejsza ni� najmniejsza warto�� w przeszukiwanymwektorze, funkcja WYSZUKAJ zwraca b��d #N/D.

UWAGAW pomocy jest te� mowa o tablicowej sk�adni funkcji WYSZUKAJ. Ta alternatywnask�adnia zosta�a dodana ze wzgl�du na zachowanie zgodno�ci z innymi arkuszamikalkulacyjnymi. Z regu�y zamiast sk�adni tablicowej mo�na u�ywa� funkcjiWYSZUKAJ.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 jestnast�puj�ca:

=WYSZUKAJ(B2;D2:D7;G4:G9)

Rysunek 8.4. U�ycie funkcji WYSZUKAJ do znalezienia stopy podatkowej

OSTRZE�ENIEJe�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 przeciwiestwie do funkcji WYSZUKAJ.PIONOWO) mo�ezwróci� warto�� znajduj�c� si� w innym wierszu ni� dopasowana warto��. Je�li argumentyprzeszukiwany_wektor i wektor_wynikowy nie nale�� do tej samej tabeli, funkcja WYSZUKAJmo�e by� bardzo pomocna. Je�li jednak nale�� do tej samej tabeli, lepiej jest u�ywa� funkcjiWYSZUKAJ.PIONOWO, cho�by dlatego, �e funkcja WYSZUKAJ nie dzia�a z nieposortowanymi danymi.


��czne u�ycie funkcjiPODAJ.POZYCJ i INDEKSFunkcje PODAJ.POZYCJ� i INDEKS s� cz�sto u�ywane w formu�ach wyszukuj�cych. FunkcjaPODAJ.POZYCJ� zwraca wzgl�dne po�o�enie w zakresie komórki, która pasuje do okre�lonejwarto�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 przeszukiwanejtablicy. 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ównywaniawarto�ci.

UWAGAJe�li argument typ_porównania ma warto�� 1, funkcja PODAJ.POZYCJ� znajdujenajwi�ksz� warto��, która jest mniejsza lub równa szukanej warto�ci (przeszukiwanatablica musi by� posortowana rosn�co). Warto�� 0 tego argumentu powodujeznalezienie pierwszej warto�ci, która jest identyczna z poszukiwan�. Je�li argumenttyp_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.

UWAGAJe�li tablica zawiera tylko jeden wiersz lub jedn� kolumn�, argument numer_wierszalub numer_kolumny jest opcjonalny.


Rysunek 8.5 przedstawia arkusz zawieraj�cy daty, nazwy dni tygodnia i kwoty w kolumnachD, 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 wierszaw zakresie F2:F21.

Wyspecjalizowaneformu�y wyszukuj�ceZa 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� innewyspecjalizowane i z�o�one wyszukiwania.


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 zwracawarto�� 0.

Aby odró�ni� zera od pustych komórek, konieczna jest modyfikacja formu�ypolegaj�ca na dodaniu funkcji JE�ELI, która sprawdzi, czy d�ugo�� zwróconejwarto�ci to 0. Je�li znaleziona warto�� jest pusta, d�ugo�� zwróconej warto�ciwynosi 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�ciwynosi 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 FTPPrzyk�ady prezentowane w tym podrozdziale mo�na znale� w plikuwyspecjalizowane formu�y wyszukiwania.xlsx, który jest dost�pny na serwerze FTP.

Wyszukiwanie dok�adnej warto�ciJak pokaza�em w poprzednich przyk�adach, funkcje WYSZUKAJ.PIONOWO i WYSZUKAJ.POZIOMOnie 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.


Aby wyszuka� tylko dok�adn� warto��, nale�y u�y� funkcji WYSZUKAJ.PIONOWO lubWYSZUKAJ.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��danedzia�anie, poniewa� zwracanie przybli�onej warto�ci dla numeru pracownika nie mia�obysensu. Zauwa� te�, �e numery pracowników w kolumnie C nie s� posortowane rosn�co. Jest tomo�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 numerupracownika na co� innego, mo�na u�y� funkcji JE�ELI.B� D, do znalezieniainformacji o b��dzie i zast�pienia jej w�asnym �a�cuchem znaków. Poni�szaformu�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))


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 byby�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). Nazwiskogracza, 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. Jednymz 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 jednymma�ym problemem: je�li wpisane zostanie nazwisko nieistniej�cego gracza (czyli komórkaSzukanaWarto� 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�adzia�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))


Wyszukiwanie z rozró�nianiem ma�ychi wielkich literFunkcje wyszukiwania w Excelu (WYSZUKAJ, WYSZUKAJ.POZIOMO i WYSZUKAJ.PIONOWO) nierozró�niaj� ma�ych i wielkich liter. Je�li na przyk�ad napiszemy formu�� wyszukuj�c� ci�gbudet, wszystkie nast�puj�ce ci�gi b�d� brane pod uwag�: BUD�ET, Budet, BuDEt.

Rysunek 8.8 przedstawia prosty przyk�ad. Zakres komórek D2:D7 ma nazw� Zakres1, a zakresE2: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�yciuformu�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.

UWAGAPami�taj o u�yciu kombinacji klawiszy Ctrl+Shift+Enter przy wprowadzaniuformu�y tablicowej.

Wybieranie spo�ród wielu tabelW 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 przedstawiaprzyk�adowy arkusz.


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�degoprzedstawiciela handlowego zale�y od dwóch czynników: liczby lat pracy (kolumna B) i ilo�cisprzedanego 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 ocenna podstawie wyników testuCz�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. ZakresE2:F6 (o nazwie ListaOcen) stanowi tabel� do przeszukiwania w celu dopasowania oceny dowyniku testu.

Kolumna C zawiera formu�y u�ywaj�ce funkcji WYSZUKAJ.PIONOWO i przypisuj�ce uczniomoceny na podstawie wyników testu znajduj�cych si� w kolumnie B. Na przyk�ad formu�aw komórce C2 jest nast�puj�ca:

=WYSZUKAJ.PIONOWO(B2;ListaOcen;2)


Rysunek 8.10. Wyszukiwanie ocen dla wyników testu

Kiedy tabela do przeszukiwania jest niewielka (jak w przyk�adowym arkuszu widocznym narysunku 8.10), mo�na zamiast niej u�y� tablicy. Na przyk�ad widoczna poni�ej formu�azwraca ocen� bez u�ycia tabeli do przeszukiwania. Informacje z tabeli zosta�y zakodowanew 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 WYSZUKAJz 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� funkcjiWYBIERZ. Liczba punktów jest dzielona przez 10, odrzucana jest cz�� dziesi�tna i dodawanajest liczba 1 w celu utworzenia liczb od 1 do 11. Pozosta�e argumenty definiuj� warto�cizwrotne dla tych jedenastu opcji.

=WYBIERZ(LICZBA.CA�K(B2/10)+1;"1";"1";"1";"1";"2";"2";"2";"3";"4";"5";"5")

Obliczanie �redniej ocenNa 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 systemocen 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�onychprzez 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.


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� nawetunikn�� konieczno�ci u�ycia tabeli do przeszukiwania i formu� w kolumnach D i E na rzeczjednej 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 stronyRysunek 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�ytkownikwpisuje w komórce B1 nazw� miesi�ca i w komórce B2 nazw� produktu.


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 toza 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))


WSKAZÓWKAInnym sposobem na wykonanie wyszukiwania w dwie strony jest nazwanie ka�degowiersza 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 utworzeniutych nazw wyszukiwanie w dwie strony mo�na wykonywa� za pomoc� prostychformu�, jak ta poni�ej:

=Sworznie Lipiec

W formule tej zosta� u�yty operator przeci�cia zakresów (spacja). Zwraca onawielko�� sprzeda�y sworzni w lipcu. Aby odwo�a� si� do komórek zawieraj�cychnazwy 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�aniado zakresów oraz znajduje ich przeci�cie. Szczegó�owe informacje na tematoperatora przeci�cia znajduj� si� w rozdziale 3.

Wyszukiwanie dwukolumnoweW pewnych sytuacjach konieczne jest wyszukanie danych na podstawie warto�ci z dwóchkolumn. Na rysunku 8.13 wida� przyk�ad takiej sytuacji:

Rysunek 8.13. Arkusz ten wykonuje wyszukiwanie, wykorzystuj�c informacje z dwóchkolumn (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


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 tablicyzawieraj�cej ten sam tekst odpowiednio w zakresach Marki i Modele.

Sprawdzanie adresu warto�ci w zakresieW 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 zakresuDane 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 zakresuDane 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 adrespierwszej z nich. Je�li nie zostanie ona znaleziona w ogóle, formu�a zwraca b��d #N/D.


Wyszukiwanie warto�ciprzy u�yciu najbli�szego dopasowaniaFunkcje 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 mawarto�� PRAWDA lub zostanie pomini�ty i zostanie znaleziona warto�� pasuj�cadok�adnie, zostaje zwrócona nast�pna najwi�ksza warto��, która jest mniejszaod szukanej warto�ci.

Ale co zrobi�, je�li potrzebujemy znale� najbli�sz� pasuj�c� warto��? Nie zrobimy tego aniza pomoc� funkcji WYSZUKAJ.PIONOWO, ani WYSZUKAJ.POZIOMO.

Na rysunku 8.15 widoczny jest arkusz zawieraj�cy imiona studentów w kolumnie A i warto�ciw kolumnie B. Zakres komórek B2:B20 ma nazw� Dane. Komórka E2 (o nazwie Cel) zawierawarto��, która ma by� wyszukana w zakresie Dane. Komórka E3 (o nazwie PrzesKol) zawierawarto�� 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 zwracaimi� Lech (z którym skojarzona jest warto�� 8000 — najbli�sza szukanej warto�ci 8025).


=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�azwraca 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 adresukomó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�ciprzy u�yciu interpolacji liniowejInterpolacja 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


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� jejwarto�� 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 naszaformu�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, zwracawynik. Je�li warto�� znajduj�ca si� w komórce B3 zostanie znaleziona w zakresie x, formu�azwró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�yciuinterpolacji liniowej


Bardzo wa�ne jest, aby warto�ci w zakresie x by�y ustawione w rosn�cej kolejno�ci. Je�likomó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 zestawieniei opisy tych formu�.

TABELA 8.2. FORMU�Y DO WYSZUKIWANIA PRZY ZASTOSOWANIUINTERPOLACJI LINIOWEJ

Komórka Formu�a Opis

B3 =WYSZUKAJ(B1;x;x) Wykonuje zwyk�e wyszukiwanie w zakresie xi zwraca znalezion� warto��.

B4 =B1=B3 Zwraca warto�� PRAWDA, je�li znaleziona warto��jest równa szukanej warto�ci.

B6 =PODAJ.POZYCJ�(B3;x;0) Zwraca numer wiersza w zakresie x, który zawierapasuj�c� warto��.

B7 =JE�ELI(B4;B6;B6+1) Zwraca ten sam numer wiersza, co formu�aw komórce B6, je�li zostanie znalezione dok�adnedopasowanie. W przeciwnym przypadku dodaje1 do wyniku z komórki B6.

B9 =INDEKS(x;B6) Zwraca warto�� z zakresu x odpowiadaj�c�wierszowi o numerze z komórki B6.

B10 =INDEKS(x;B7) Zwraca warto�� z zakresu x odpowiadaj�c�wierszowi o numerze z komórki B7.

B12 =WYSZUKAJ(B9;x;y) Zwraca warto�� z zakresu y, która odpowiadawarto�ci z zakresu x znajduj�cej si� w komórce B9.

B13 =WYSZUKAJ(B10;x;y) Zwraca warto�� z zakresu y, która odpowiadawarto�ci z zakresu x znajduj�cej si� w komórce B10.

B15 =JE�ELI(B4;0;�(B1-B3)/(B10-B9))

Oblicza wspó�czynnik dopasowania na podstawieró�nicy pomi�dzy warto�ciami z zakresu x.

B16 =B12+((B13-B12)*B15) Wyznacza warto�� z zakresu y przy u�yciuwspó�czynnika dopasowania z komórki B15.

��CZNE U�YCIE FUNKCJI WYSZUKAJ I REGLINWTroch� 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 tylkojednej formu�y (rysunek 8.18).


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�liw 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�� niezostanie znaleziona, formu�a oblicza najlepiej pasuj�c� warto�� z zakresu y przy u�yciu funkcjiREGLINW (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))

Excel 2010 PL. Formuły...Spis treci Wstp 23 Co trzeba wiedzie 23 Co trzeba mie 23 Konwencje...

Documents

Transcript of Excel 2010 PL. Formuły...Spis treci Wstp 23 Co trzeba wiedzie 23 Co trzeba mie 23 Konwencje...