Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00271 004830 12594007 na godz. na dobę w sumie
Excel 2010 PL. Formuły - książka
Excel 2010 PL. Formuły - książka
Autor: Liczba stron: 881
Wydawca: Helion Język publikacji: polski
ISBN: 978-83-246-2883-4 Data wydania:
Lektor:
Kategoria: ebooki >> komputery i informatyka >> aplikacje biurowe >> excel
Porównaj ceny (książka, ebook, audiobook).

Wykorzystaj wszystkie możliwości drzemiące w formułach!

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. Nagle okaże się, jak wiele czasu możesz zaoszczędzić, opanowując tworzenie i praktyczne zastosowanie rozmaitych formuł!

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. Odtąd już żadne zadanie w Excelu nie będzie wydawało Ci się zbyt skomplikowane - z Johnem Walkenbachem odkryjesz wszystkie tajemnice tego programu!

Dzięki tej książce:

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

Znajdź podobne książki Ostatnio czytane w tej kategorii

Darmowy fragment publikacji:

Idź do • Spis treści • Przykładowy rozdział Katalog książek • Katalog online • Zamów drukowany katalog Twój koszyk • Dodaj do koszyka Cennik i informacje • Zamów informacje o nowościach • Zamów cennik Czytelnia • Fragmenty książek online Kontakt Helion SA ul. Kościuszki 1c 44-100 Gliwice tel. 32 230 98 63 e-mail: helion@helion.pl © Helion 1991–2010 Excel 2010 PL. Formuły Autor: John Walkenbach Tłumaczenie: Łukasz Piwko ISBN: 978-83-246-2883-4 Tytuł oryginału: Excel 2010 Formulas (Mr. Spreadsheet s Bookshelf) Format: 172×245, stron: 880 Wykorzystaj wszystkie możliwości drzemiące w formułach! • Jak tworzyć formuły finansowe, tablicowe i tekstowe? • Jak napisać własne funkcje arkusza w języku VBA? • Jak za pomocą formuł tworzyć wykresy i tabele przestawne? Jak potężnym i przydatnym narzędziem jest Excel, przekonują się nawet jego najzagorzalsi przeciwnicy. Czy się ten program Microsoftu lubi, czy też nie, nie sposób kwestionować jego możliwości, nieporównywalnych z innymi arkuszami kalkulacyjnymi. I choć dzięki temu Excel w wielu zastosowaniach okazuje się niezastąpiony, wciąż znajdują się tacy, którzy boją się jego obsługi i nie wychodzą poza kilka standardowych funkcji. W końcu nawet biegli użytkownicy Excela bywają onieśmieleni jego imponującą funkcjonalnością. Jeśli zatem i Tobie wydawało się, że doskonale znasz ten program, i nagle odkryłeś, jak wiele z jego potencjału wciąż jest poza zasięgiem Twoich umiejętności, oto idealny podręcznik dla Ciebie! Książka napisana przez Johna Walkenbacha, absolutnego guru Excela, dostarczy Ci wiedzy, która raz na zawsze odmieni Twoją pracę w tym programie. Najpierw przeczytasz, czym dokładnie jest formuła, jak ją stworzyć oraz edytować w Excelu 2010 i do czego można ją wykorzystywać. Dowiesz się wszystkiego na temat nazw, ich zakresów i zarządzania nimi. W kolejnych częściach podręcznika zobaczysz, jak używać funkcji arkuszy w formułach, przeczytasz o manipulowaniu tekstem, datach i godzinach oraz różnych technikach liczenia. Odkryjesz, jak tworzyć bardzo przydatne formuły tekstowe, finansowe i tablicowe, oraz przekonasz się, jak przydatne są one w pracy z wykresami i tabelami przestawnymi. Znajdziesz tu również wiele interesujących, praktycznych formuł, których można używać w połączeniu z funkcjami formatowania warunkowego i sprawdzania poprawności danych. A kiedy już opanujesz te zagadnienia, dotrzesz do „megaformuł” i wykorzystywania języka VBA do tworzenia własnych funkcji arkusza. Dzięki tej książce: • poznasz interfejs i nowe funkcje Excela 2010 • nauczysz się pracować z nazwami arkuszy, komórek i zakresów • zaczniesz tworzyć przydatne formuły finansowe, tablicowe i tekstowe • poznasz podstawowe i zaawansowane formuły liczące • opanujesz formuły wyszukiwania oraz funkcje czasu i daty • nauczysz się pracować z tabelami i bazami danych arkusza • dowiesz się, czym są odwołania cykliczne i jak z nich korzystać • poznasz techniki tworzenia wykresów i tabel przestawnych • zaczniesz korzystać z formuł formatowania warunkowego • opanujesz tworzenie i stosowanie „megaformuł” • napiszesz własne, przydatne funkcje w języku VBA • bez trudu usuniesz błędy w formułach Chcesz, by Excel pracował za Ciebie? John Walkenbach pokaże Ci, jak to zrobić! Spis treĂci Wst£p Co trzeba wiedzieó Co trzeba mieó Konwencje typograficzne Konwencje dotyczŸce klawiatury Konwencje myszy Co oznaczajŸ ikony Struktura ksiŸŗki Cz£ĺó I: Informacje podstawowe Cz£ĺó II: Stosowanie funkcji w formu§ach Cz£ĺó III: Formu§y finansowe Cz£ĺó IV: Formu§y tablicowe Cz£ĺó V: Róŗne techniki wykorzystania formu§ Cz£ĺó VI: Tworzenie niestandardowych funkcji arkusza Dodatki Jak korzystaó z ksiŸŗki? Informacje o przyk§adach Informacje o dodatku Power Utility Pak Komentarze CzÚĂÊ I Informacje podstawowe Rozdzia« 1. Wst¤p do Excela Historia Excela Wszystko zacz£§o si£ od programu VisiCalc Nadejĺcie Lotusa Do gry wchodzi Microsoft Wersje Excela Koncepcja modelu obiektowego Zasada dzia§ania skoroszytów Arkusze Arkusze wykresów Arkusze makr i arkusze dialogowe Interfejs uŗytkownika programu Excel Nowy interfejs uŗytkownika WstŸŗka Widok Backstage Menu podr£czne i minipasek narz£dzi Konfigurowanie interfejsu uŗytkownika Tagi inteligentne Okienko zadaħ PrzeciŸgnij i upuĺó Skróty klawiaturowe Dostosowywanie widoku na ekranie 23 23 23 24 24 25 26 26 27 27 27 27 27 28 28 28 29 29 29 31 33 34 34 34 35 35 39 40 41 42 42 43 43 43 46 47 47 48 49 50 50 51 8 Spis treĂci Wprowadzanie danych Zaznaczanie obiektów i komórek Pomoc programu Excel Formatowanie komórek Formatowanie numeryczne Formatowanie stylistyczne Tabele Formu§y i funkcje arkuszy Obiekty na warstwie rysowania Kszta§ty Ilustracje Obiekty obrazów po§Ÿczonych Formanty Wykresy Wykresy przebiegu w czasie Dostosowywanie Excela Makra Dodatki Funkcje internetowe Narz£dzia do analizy Dost£p do baz danych Konspekty ZarzŸdzanie scenariuszami Tabele przestawne Funkcje inspekcji Dodatek Solver Opcje ochrony Ochrona formu§ przed nadpisaniem Chronienie struktury skoroszytu Ochrona skoroszytu has§em Rozdzia« 2. Podstawowe informacje na temat formu« Wprowadzanie i edycja formu§ Elementy formu§y Wstawianie formu§y Wklejanie nazw Spacje i z§amania wiersza Ograniczenia formu§ Przyk§ady formu§ Edytowanie formu§ Operatory uŗywane w formu§ach Operatory odniesienia Przyk§ady formu§ z uŗyciem operatorów Pierwszeħstwo operatorów Zagnieŗdŗanie nawiasów Obliczanie wartoĺci formu§ Odniesienia do komórek i zakresów Tworzenie odwo§aħ bezwzgl£dnych i mieszanych Tworzenie odwo§aħ do innych arkuszy lub skoroszytów Robienie wiernej kopii formu§y Konwertowanie formu§ na wartoĺci Ukrywanie formu§ 51 53 53 54 54 55 55 56 56 57 57 57 58 58 59 60 60 60 60 61 61 62 63 63 64 64 64 64 65 66 67 68 68 68 70 70 71 71 72 73 74 74 75 77 79 80 80 83 83 84 87 Spis treĂci 9 B§£dy w formu§ach Co robió z odwo§aniami cyklicznymi Szukanie wyniku Przyk§ad szukania wyniku Szukanie wyniku — informacje dodatkowe Rozdzia« 3. Praca z nazwami Co to jest nazwa Zakres nazw Odwo§ania do nazw Odnoszenie si£ do nazw z innego skoroszytu Konflikty nazw Menedŗer nazw Tworzenie nazw Edytowanie nazw Usuwanie nazw Szybkie tworzenie nazw komórek i zakresów Okno dialogowe Nowa nazwa Tworzenie nazw przy uŗyciu pola nazwy Automatyczne tworzenie nazw Nazywanie ca§ych wierszy i kolumn Nazwy tworzone przez Excela Tworzenie nazw obejmujŸcych kilka arkuszy Praca z nazwami komórek i zakresów Tworzenie listy nazw Uŗywanie nazw w formu§ach Uŗywanie operatora przeci£cia z nazwami Uŗywanie operatora zakresu z nazwami Odwo§ywanie si£ do pojedynczej komórki w zakresie nazwanym obejmujŸcym kilka arkuszy Wstawianie nazw do istniejŸcych formu§ Automatyczne wstawianie nazw podczas tworzenia formu§y Usuwanie nazw Nazwy z b§£dami PrzeglŸdanie nazw zakresów Stosowanie nazw w wykresach Obs§uga nazw komórek i zakresów przez Excela Wstawianie wiersza lub kolumny Usuwanie wiersza lub kolumny Wycinanie i wklejanie Potencjalne problemy z nazwami Problemy wyst£pujŸce podczas kopiowania arkuszy Problemy z nazwami przy usuwaniu arkuszy Klucz do zrozumienia nazw Nazywanie wartoĺci sta§ych Nazywanie sta§ych tekstowych Uŗywanie funkcji arkusza w nazwanych formu§ach Uŗywanie odwo§aħ do komórek i zakresów w formu§ach nazwanych Uŗywanie formu§ nazwanych zawierajŸcych odwo§ania wzgl£dne Zaawansowane techniki uŗywania nazw Uŗywanie funkcji ADR.POĹR z zakresem nazwanym Uŗycie funkcji ADR.POĹR do tworzenia zakresu nazwanego o sta§ym adresie Uŗywanie tablic w formu§ach nazwanych Tworzenie dynamicznych formu§ nazwanych 88 89 91 91 93 95 96 97 97 98 98 99 100 100 101 101 101 103 104 106 106 107 109 109 110 111 113 113 114 115 115 115 116 117 117 117 117 118 118 118 120 121 122 123 124 125 126 130 130 131 131 133 10 Spis treĂci CzÚĂÊ II Stosowanie funkcji w formuïach 135 Rozdzia« 4. Wprowadzenie do funkcji arkusza Co to jest funkcja Upraszczanie formu§ Wykonywanie obliczeħ niemoŗliwych do wykonania w inny sposób Przyspieszanie zadaħ edycyjnych Podejmowanie decyzji przez formu§£ Wi£cej na temat funkcji Typy argumentów funkcji Nazwy w roli argumentów Ca§e kolumny i wiersze w roli argumentów Wartoĺci literalne w roli argumentów Wyraŗenia w roli argumentów Funkcje w roli argumentów Tablice w roli argumentów Sposoby wstawiania funkcji do formu§ R£czne wpisywanie funkcji Biblioteka funkcji Okno dialogowe Wstawianie funkcji Dodatkowe wskazówki na temat wstawiania funkcji Kategorie funkcji 137 137 138 138 139 139 140 140 141 142 142 143 143 143 144 144 145 146 148 150 150 150 151 151 151 151 151 152 152 152 152 152 152 153 155 155 156 156 157 158 158 161 161 162 164 164 164 165 165 166 Funkcje finansowe Funkcje daty i godziny Funkcje matematyczne i trygonometryczne Funkcje statystyczne Funkcje wyszukiwania i odwo§aħ Funkcje baz danych Funkcje tekstowe Funkcje logiczne Funkcje informacyjne Funkcje zdefiniowane przez uŗytkownika Funkcje inŗynierskie Funkcje modu§owe Funkcje zgodnoĺci Inne kategorie funkcji Rozdzia« 5. Manipulowanie tekstem Kilka s§ów na temat tekstu Ile znaków moŗe pomieĺció jedna komórka Liczby jako tekst Funkcje tekstowe Sprawdzanie, czy komórka zawiera tekst Praca z kodami znaków Sprawdzanie, czy dwa ciŸgi sŸ identyczne ¦Ÿczenie dwóch lub wi£kszej liczby komórek Wyĺwietlanie sformatowanych wartoĺci jako tekst Wyĺwietlanie wartoĺci walutowych jako tekst Usuwanie niepotrzebnych spacji i niedrukowalnych znaków Liczenie znaków w ciŸgu Powtarzanie znaku lub ciŸgu Tworzenie histogramu tekstowego Dope§nianie liczby Zmiana wielkoĺci liter Wydobywanie znaków z ciŸgu Podmienianie tekstu innym tekstem Znajdowanie i szukanie w ciŸgu Znajdowanie i zamienianie ciŸgów Zaawansowane formu§y tekstowe Zliczanie okreĺlonych znaków w komórce Zliczanie wystŸpieħ podciŸgu w komórce Usuwanie znaków minusa z koħca Sprawdzanie numeru litery kolumny po jej numerze Wydobywanie nazwy pliku ze ĺcieŗki Wydobywanie pierwszego wyrazu z ciŸgu Wydobywanie ostatniego wyrazu z ciŸgu Wydobywanie wszystkiego poza pierwszym wyrazem w ciŸgu Wydobywanie pierwszych imion, drugich imion i nazwisk Usuwanie tytu§u sprzed imienia lub nazwiska Zliczanie s§ów w komórce Rozdzia« 6. Funkcje daty i czasu Jak Excel obs§uguje daty i godziny Liczby seryjne dat Wprowadzanie dat Liczby seryjne godzin i minut Wprowadzanie godzin Formatowanie dat i godzin Problemy z datami Funkcje daty Wyĺwietlanie aktualnej daty Wyĺwietlanie dowolnej daty Generowanie serii dat Konwersja ciŸgów tekstowych na daty Obliczanie liczby dni dzielŸcych dwie daty Obliczanie liczby dni powszednich mi£dzy dwiema datami Obliczanie daty, biorŸc pod uwag£ tylko dni robocze Obliczanie liczby lat dzielŸcych dwie daty Obliczanie wieku osób Okreĺlanie dnia roku Okreĺlanie dnia tygodnia Okreĺlanie daty ostatniej niedzieli Okreĺlanie daty pierwszego wystŸpienia dnia tygodnia po okreĺlonej dacie Okreĺlanie n-tego wystŸpienia dnia tygodnia w miesiŸcu Zliczanie wystŸpieħ dnia tygodnia Obliczanie dat ĺwiŸt Okreĺlanie daty ostatniego dnia miesiŸca Sprawdzanie, czy dany rok jest przest£pny Sprawdzanie kwarta§u roku Konwersja roku na liczby rzymskie Wyĺwietlanie bieŗŸcego czasu Wyĺwietlanie dowolnego czasu Sumowanie czasów powyŗej 24 godzin Obliczanie róŗnicy mi£dzy dwiema wartoĺciami czasu Konwersja z czasu wojskowego Konwersja godzin, minut i sekund w zapisie dziesi£tnym na wartoĺci czasu Funkcje czasu Spis treĂci 11 168 169 169 170 171 171 172 172 172 173 173 173 174 174 175 177 177 179 179 180 181 183 183 185 186 188 188 190 191 192 193 193 195 195 197 197 198 198 198 199 199 201 203 204 204 204 205 205 206 207 209 211 211 12 Spis treĂci Dodawanie godzin, minut i sekund do wartoĺci czasu Konwersja pomi£dzy strefami czasowymi ZaokrŸglanie wartoĺci czasu Praca z wartoĺciami nieb£dŸcymi godzinami dnia Rozdzia« 7. Techniki liczenia i sumowania Liczenie i sumowanie komórek Zliczanie i sumowanie rekordów w bazach danych i tabelach przestawnych Podstawowe formu§y liczŸce Obliczanie sumy komórek Zliczanie pustych komórek Zliczanie niepustych komórek Zliczanie komórek z liczbami Zliczanie komórek niezawierajŸcych tekstu Zliczanie komórek tekstowych Zliczanie wartoĺci logicznych Zliczanie wartoĺci b§£dów w zakresie Zaawansowane formu§y liczŸce Liczenie komórek przy uŗyciu funkcji LICZ.JEŖELI Zliczanie komórek spe§niajŸcych wiele kryteriów Zliczanie liczby wystŸpieħ najcz£ĺciej pojawiajŸcego si£ wpisu Zliczanie wystŸpieħ okreĺlonego tekstu Liczenie unikatowych wartoĺci Tworzenie rozk§adu cz£stoĺci Formu§y sumujŸce Sumowanie wszystkich komórek w zakresie Obliczanie narastajŸcej sumy Sumowanie okreĺlonej liczby najwi£kszych wartoĺci Sumowanie warunkowe z jednym kryterium Sumowanie tylko wartoĺci ujemnych Sumowanie wartoĺci w oparciu o inny zakres Sumowanie wartoĺci w oparciu o porównanie tekstowe Sumowanie wartoĺci w oparciu o porównanie daty Sumowanie warunkowe przy zastosowaniu wielu kryteriów Uŗycie kryteriów „i” Uŗycie kryteriów „lub” Uŗycie kryteriów „i” oraz „lub” Rozdzia« 8. Funkcje wyszukiwania Co to jest formu§a wyszukiwania Funkcje zwiŸzane z wyszukiwaniem Podstawowe formu§y wyszukiwania Funkcja WYSZUKAJ.PIONOWO Funkcja WYSZUKAJ.POZIOMO Funkcja WYSZUKAJ ¦Ÿczne uŗycie funkcji PODAJ.POZYCJ¢ i INDEKS Wyspecjalizowane formu§y wyszukujŸce Wyszukiwanie dok§adnej wartoĺci Wyszukiwanie wartoĺci w lewŸ stron£ Wyszukiwanie z rozróŗnianiem ma§ych i wielkich liter Wybieranie spoĺród wielu tabel Okreĺlanie ocen na podstawie wyników testu Obliczanie ĺredniej ocen Wyszukiwanie w dwie strony 212 213 214 214 217 218 220 221 222 222 223 223 223 224 224 224 225 225 225 229 231 233 234 240 240 242 243 244 245 245 246 246 247 248 249 249 251 251 253 253 254 255 256 258 259 260 262 263 263 264 265 266 Wyszukiwanie dwukolumnowe Sprawdzanie adresu wartoĺci w zakresie Wyszukiwanie wartoĺci przy uŗyciu najbliŗszego dopasowania Wyszukiwanie wartoĺci przy uŗyciu interpolacji liniowej Rozdzia« 9. Tabele i bazy danych arkusza Tabele i terminologia Przyk§ad bazy danych arkusza Przyk§ad tabeli Zastosowania baz danych arkusza i tabel Praca z tabelami Tworzenie tabeli Zmiana wyglŸdu tabeli Nawigacja i zaznaczanie w tabeli Dodawanie wierszy lub kolumn Usuwanie wierszy lub kolumn Przenoszenie tabeli Ustawianie opcji stylu tabeli Usuwanie powtarzajŸcych si£ wierszy z tabeli Sortowanie i filtrowanie tabeli Praca z wierszem sumy Stosowanie formu§ w tabelach Odwo§ywanie si£ do danych w tabeli Konwersja tabeli na baz£ danych arkusza Filtrowanie zaawansowane Ustawianie zakresu kryteriów Stosowanie filtru zaawansowanego Usuwanie filtru zaawansowanego Okreĺlanie kryteriów filtru zaawansowanego Okreĺlanie pojedynczego kryterium Okreĺlanie wielu kryteriów Okreĺlanie kryteriów utworzonych w wyniku uŗycia formu§y Funkcje bazy danych Wstawianie sum cz£ĺciowych Rozdzia« 10. RóŚne obliczenia Konwersja jednostek RozwiŸzywanie trójkŸtów prostokŸtnych Obliczanie pola powierzchni, obwodu i obj£toĺci Obliczanie pola powierzchni i obwodu kwadratu Obliczanie pola powierzchni i obwodu prostokŸta Obliczanie pola powierzchni i obwodu ko§a Obliczanie pola powierzchni trapezu Obliczanie pola powierzchni trójkŸta Obliczanie pola powierzchni i obj£toĺci kuli Obliczanie pola powierzchni i obj£toĺci szeĺcianu Obliczanie pola powierzchni i obj£toĺci stoŗka Obliczanie obj£toĺci walca Obliczanie obj£toĺci ostros§upa RozwiŸzywanie uk§adów równaħ ZaokrŸglanie liczb Podstawowe formu§y zaokrŸglajŸce ZaokrŸglanie do najbliŗszej wielokrotnoĺci ZaokrŸglanie wartoĺci walutowych Spis treĂci 13 268 269 270 271 275 276 276 277 278 279 281 282 283 283 284 285 285 287 288 292 295 297 301 302 303 304 305 306 306 309 312 313 315 319 319 321 324 324 324 325 325 325 325 326 326 326 327 327 328 329 330 330 14 Spis treĂci Praca z u§amkami dolarów Stosowanie funkcji ZAOKR.DO.CA¦K i LICZBA.CA¦K ZaokrŸglanie do parzystej lub nieparzystej liczby ca§kowitej ZaokrŸglanie do n cyfr znaczŸcych CzÚĂÊ III Formuïy finansowe Rozdzia« 11. Formu«y kredytów i inwestycji Poj£cia finansowe Wartoĺó pieniŸdza w czasie Wp§ywy i p§atnoĺci Dopasowywanie okresów czasu Wyznaczanie terminu pierwszej p§atnoĺci Podstawowe funkcje finansowe Obliczanie wartoĺci bieŗŸcej Obliczanie przysz§ej wartoĺci Obliczanie p§atnoĺci Obliczanie stóp Obliczanie liczby rat Obliczanie sk§adników p§atnoĺci Funkcje IPMT i PPMT Funkcje CUMIPMT i CUMPRINC Konwersja stóp procentowych Metody przedstawiania stóp procentowych Formu§y konwersji Ograniczenia funkcji finansowych Odroczony poczŸtek serii regularnych p§atnoĺci Szacowanie serii zmiennych p§atnoĺci Obliczenia dotyczŸce obligacji Wyznaczanie ceny obligacji Obliczanie rentownoĺci Rozdzia« 12. Formu«y dyskontowe i amortyzacji Funkcja NPV Definicja funkcji NPV Przyk§ady uŗycia funkcji NPV Obliczanie kwot nagromadzonych za pomocŸ funkcji NPV Funkcja IRR — stosowanie Stopa zwrotu Geometryczne wskaŕniki przyrostu Sprawdzanie wyników Kilka stóp funkcji IRR i MIRR Kilka wewn£trznych stóp zwrotu Rozdzielanie przep§ywów Uŗycie sald zamiast przep§ywów Nieregularne przep§ywy ĺrodków Wartoĺó bieŗŸca netto Wewn£trzna stopa zwrotu Funkcja FVSCHEDULE Obliczanie zwrotu w skali roku Obliczanie amortyzacji 331 332 333 333 335 337 338 338 338 339 339 340 340 344 347 349 351 353 353 355 356 356 356 357 358 359 360 360 362 363 364 364 366 372 374 375 376 377 378 378 380 381 382 382 383 384 384 385 Rozdzia« 13. Harmonogramy finansowe Tworzenie harmonogramów finansowych Tworzenie harmonogramów amortyzacji Prosty harmonogram amortyzacji Dynamiczny harmonogram amortyzacji Tabele p§atnoĺci i odsetek Obliczenia dotyczŸce karty kredytowej Zestawianie opcji poŗyczek w tabelach danych Tworzenie tabeli danych z jednŸ zmiennŸ Tworzenie tabeli danych z dwiema zmiennymi Sprawozdania finansowe Podstawowe sprawozdania finansowe Analiza wskaŕników Tworzenie indeksów CzÚĂÊ IV Formuïy tablicowe Rozdzia« 14. Wprowadzenie do tablic Wprowadzenie do formu§ tablicowych Wielokomórkowa formu§a tablicowa Jednokomórkowa formu§a tablicowa Tworzenie sta§ej tablicowej Elementy sta§ej tablicowej Wymiary tablicy — informacje Jednowymiarowe tablice poziome Jednowymiarowe tablice pionowe Tablice dwuwymiarowe Nadawanie nazw sta§ym tablicowym Praca z formu§ami tablicowymi Wprowadzanie formu§y tablicowej Zaznaczanie zakresu formu§y tablicowej Edycja formu§y tablicowej Powi£kszanie i zmniejszanie wielokomórkowych formu§ tablicowych Stosowanie wielokomórkowych formu§ tablicowych Tworzenie tablicy z wartoĺci w zakresie Tworzenie sta§ej tablicowej z wartoĺci w zakresie Wykonywanie dzia§aħ na tablicach Uŗywanie funkcji z tablicami Transponowanie tablicy Generowanie tablicy kolejnych liczb ca§kowitych Jednokomórkowe formu§y tablicowe Liczenie znaków w zakresie Sumowanie trzech najmniejszych wartoĺci w zakresie Zliczanie komórek tekstowych w zakresie Pozbywanie si£ formu§ poĺrednich Zastosowanie tablicy zamiast adresu zakresu Rozdzia« 15. Magia formu« tablicowych Stosowanie jednokomórkowych formu§ tablicowych Sumowanie zakresu zawierajŸcego b§£dy Zliczanie b§£dów wartoĺci w zakresie komórek Sumowanie n najwi£kszych wartoĺci w zakresie Obliczanie ĺredniej z pomini£ciem zer Spis treĂci 15 389 389 390 390 393 397 399 400 401 403 405 405 409 412 415 417 417 418 419 420 421 422 422 422 423 424 425 426 426 426 428 429 429 429 430 431 432 433 434 434 435 436 438 440 441 441 442 443 444 444 16 Spis treĂci Sprawdzanie wyst£powania okreĺlonej wartoĺci w zakresie Zliczanie liczby róŗnic w dwóch zakresach Zwracanie lokalizacji maksymalnej wartoĺci w zakresie Odszukiwanie wiersza n-tego wystŸpienia wartoĺci w zakresie Zwracanie najd§uŗszego tekstu w zakresie Sprawdzanie, czy zakres zawiera poprawne wartoĺci Sumowanie cyfr liczby ca§kowitej Sumowanie wartoĺci zaokrŸglonych Sumowanie wszystkich n-tych wartoĺci w zakresie Usuwanie nienumerycznych znaków z §aħcucha Odszukiwanie najbliŗszej wartoĺci w zakresie Zwracanie ostatniej wartoĺci w kolumnie Zwracanie ostatniej wartoĺci w wierszu Szeregowanie danych przy uŗyciu formu§y tablicowej Stosowanie wielokomórkowych formu§ tablicowych Zwracanie wy§Ÿcznie dodatnich wartoĺci w zakresie Zwracanie niepustych komórek z zakresu Odwracanie kolejnoĺci komórek w zakresie Dynamiczne sortowanie wartoĺci w zakresie Zwracanie listy unikalnych elementów zakresu Wyĺwietlanie kalendarza w zakresie komórek CzÚĂÊ V Róĝne techniki wykorzystania formuï Rozdzia« 16. Zamierzone odwo«ania cykliczne Czym sŸ odwo§ania cykliczne? Korygowanie niezamierzonych odwo§aħ cyklicznych Istota poĺrednich odwo§aħ cyklicznych Zamierzone odwo§ania cykliczne W jaki sposób Excel okreĺla ustawienia obliczeħ i iteracji Przyk§ady odwo§aħ cyklicznych Generowanie losowych, unikalnych liczb ca§kowitych RozwiŸzywanie równaħ rekursywnych RozwiŸzywanie uk§adów równaħ przy uŗyciu odwo§aħ cyklicznych Animowanie wykresów przy uŗyciu iteracji Potencjalne problemy z zamierzonymi odwo§aniami cyklicznymi Rozdzia« 17. Techniki tworzenia wykresów Dzia§anie formu§y SERIE Uŗywanie nazw w formule SERIE Oddzielanie serii danych na wykresie od zakresu danych Tworzenie powiŸzaħ do komórek Tworzenie po§Ÿczenia z tytu§em wykresu Tworzenie powiŸzaħ z tytu§ami osi Tworzenie powiŸzaħ z etykietami danych Tworzenie powiŸzaħ tekstowych Dodawanie obrazu po§Ÿczonego do wykresu Przyk§ady wykresów Wykres post£pów w dŸŗeniu do celu Tworzenie wykresu w kszta§cie miernika Warunkowe wyĺwietlanie kolorów na wykresie kolumnowym Tworzenie histogramu porównawczego Tworzenie wykresów Gantta 446 447 448 448 449 449 450 451 452 453 454 455 456 456 457 458 459 459 460 461 462 465 467 467 468 469 470 473 474 474 475 477 479 480 481 482 484 484 487 487 488 488 488 489 489 489 491 492 493 495 Tworzenie wykresu gie§dowego Kreĺlenie co n-tego punktu danych Kreĺlenie n ostatnich punktów danych Zaznaczanie serii danych za pomocŸ okna kombi Tworzenie wykresów funkcji matematycznych Kreĺlenie okr£gu Wykres w kszta§cie zegara Tworzenie wspania§ych wykresów Tworzenie wykresów linii trendów Liniowe wykresy trendów Nieliniowe wykresy trendu Rozdzia« 18. Tabele przestawne O tabelach przestawnych Przyk§ad tabeli przestawnej Dane odpowiednie dla tabeli przestawnej Tworzenie tabeli przestawnej Wskazywanie danych Wyznaczanie lokalizacji tabeli przestawnej Definiowanie uk§adu tabeli przestawnej Formatowanie tabeli przestawnej Modyfikowanie tabeli przestawnej Wi£cej przyk§adów tabel przestawnych Filtrowanie tabel przestawnych przy uŗyciu fragmentatorów Odwo§ania do komórek w tabeli przestawnej Jeszcze jeden przyk§ad tabeli przestawnej Tworzenie raportu tabeli przestawnej Rozdzia« 19. Formatowanie warunkowe i sprawdzanie poprawnoĽci danych Formatowanie warunkowe Wybieranie formatowania warunkowego Formaty warunkowe wykorzystujŸce grafik£ Stosowanie formatów warunkowych Tworzenie regu§ opartych na formu§ach Sprawdzanie poprawnoĺci danych Definiowanie kryteriów sprawdzania poprawnoĺci danych Typy kryteriów sprawdzania poprawnoĺci danych, jakich moŗesz uŗyó Tworzenie list rozwijanych Stosowanie formu§ w regu§ach sprawdzania poprawnoĺci danych Pytanie 1. Pytanie 2. Pytanie 3. Pytanie 4. Pytanie 5. Pytanie 6. Pytanie 7. Grupowanie elementów tabeli przestawnej Przyk§ad grupowania r£cznego PrzeglŸdanie zgrupowanych danych Przyk§ady grupowania automatycznego Tworzenie rozk§adu liczebnoĺci Tworzenie pól i elementów obliczeniowych Tworzenie pola obliczeniowego Wstawianie elementu obliczeniowego Spis treĂci 17 497 499 501 502 504 508 510 512 513 514 518 523 523 524 526 529 530 530 531 534 535 538 538 539 540 541 542 543 544 544 545 546 547 551 553 555 557 560 562 564 567 569 569 571 574 583 585 595 596 597 599 600 18 Spis treĂci Rozdzia« 20. Tworzenie megaformu« Czym jest megaformu§a? Tworzenie megaformu§y — prosty przyk§ad Przyk§ady megaformu§ Usuwanie drugich imion przy uŗyciu megaformu§y Uŗycie megaformu§y zwracajŸcej pozycj£ ostatniego znaku spacji w §aħcuchu Zastosowanie megaformu§y do sprawdzania poprawnoĺci numerów kart kredytowych Generowanie nazwisk losowych Zalety i wady megaformu§ Rozdzia« 21. Narz¤dzia i metody usuwania b«¤dów w formu«ach Debugowanie formu§? Problemy z formu§ami i ich rozwiŸzania Niedopasowanie nawiasów Komórki wype§nione symbolami krzyŗyka Puste komórki, które wcale nie sŸ puste Nadmiarowe znaki spacji Formu§y zwracajŸce b§Ÿd Problemy z odwo§aniami wzgl£dnymi i bezwzgl£dnymi Problemy z pierwszeħstwem operatorów Formu§y nie sŸ obliczane Wartoĺci rzeczywiste i wyĺwietlane B§£dy liczb zmiennoprzecinkowych B§£dy nieistniejŸcych §Ÿczy B§£dy wartoĺci logicznych B§£dy odwo§aħ cyklicznych Narz£dzia inspekcyjne w Excelu Identyfikowanie komórek okreĺlonego typu PrzeglŸdanie formu§ Ĺledzenie relacji pomi£dzy komórkami Ĺledzenie wartoĺci b§£dów Naprawianie b§£dów odwo§aħ cyklicznych Funkcja sprawdzania b§£dów w tle Korzystanie z funkcji Szacowanie formu§y CzÚĂÊ VI Tworzenie niestandardowych funkcji arkusza Rozdzia« 22. Wprowadzenie do VBA Kilka s§ów o VBA Wyĺwietlanie karty Deweloper O bezpieczeħstwie makr Zapisywanie skoroszytów zawierajŸcych makra Wprowadzenie do Visual Basic Editor Aktywowanie VB Editor Elementy VB Editor Korzystanie z okna projektu Korzystanie z okna kodu Wprowadzanie kodu VBA Zapisywanie projektu 605 605 606 609 609 613 617 622 623 625 625 626 627 629 629 630 630 635 635 637 637 638 639 640 641 641 641 642 644 646 646 647 649 651 653 653 654 655 656 657 657 658 659 662 664 667 Rozdzia« 23. Podstawy tworzenia funkcji niestandardowych Po co tworzy si£ funkcje niestandardowe? WprowadzajŸcy przyk§ad funkcji VBA O procedurach Function Deklarowanie funkcji Wybór nazwy dla funkcji Uŗywanie funkcji w formu§ach Uŗycie argumentów w funkcjach Korzystanie z okna dialogowego Wstawianie funkcji Dodawanie opisu funkcji Okreĺlanie kategorii funkcji Dodawanie opisów argumentów Testowanie i debugowanie funkcji Uŗycie instrukcji VBA MsgBox Uŗycie instrukcji Debug.Print w kodzie Wywo§ywanie funkcji z procedury Sub Ustawianie punktu kontrolnego w funkcji Tworzenie dodatków Rozdzia« 24. Koncepcje programowania w VBA WprowadzajŸcy przyk§ad procedury Function Umieszczanie komentarzy wewnŸtrz kodu Uŗycie zmiennych, typów danych i sta§ych Definiowanie typów danych Deklarowanie zmiennych Uŗycie sta§ych Uŗycie §aħcuchów Uŗycie dat Uŗycie wyraŗeħ przypisania Uŗycie tablic Deklarowanie tablic Deklarowanie tablic wielowymiarowych Uŗycie wbudowanych funkcji VBA Sterowanie wykonaniem Konstrukcja If-Then Konstrukcja Select Case Bloki zap£tlajŸce Instrukcja On Error Uŗycie zakresów Konstrukcja For Each-Next Odwo§ania do zakresów Kilka uŗytecznych w§aĺciwoĺci zakresów S§owo kluczowe Set Funkcja Intersect Funkcja Union W§aĺciwoĺó UsedRange Spis treĂci 19 669 670 670 673 673 674 674 676 676 677 678 679 681 682 684 685 688 689 691 692 694 695 695 697 698 700 700 701 702 703 703 704 705 706 708 709 713 715 715 716 719 723 723 724 724 20 Spis treĂci Rozdzia« 25. Przyk«ady niestandardowych funkcji VBA Proste funkcje Czy komórka zawiera formu§£? Zwracanie formu§y zawartej w komórce Czy komórka jest ukryta? Zwracanie nazwy arkusza Odczytywanie nazwy skoroszytu Odczytywanie nazwy aplikacji Odczytywanie numeru wersji Excela Odczytywanie informacji o formatowaniu komórki Sprawdzanie typu danych w komórce Funkcje wielofunkcyjne Generowanie liczb losowych Generowanie niezmiennych liczb losowych Losowe zaznaczanie komórek Obliczanie prowizji od sprzedaŗy Funkcja dla prostej struktury prowizji Funkcja dla bardziej z§oŗonej struktury prowizji Funkcje do manipulowania tekstem Odwracanie §aħcucha Mieszanie tekstu Zwracanie akronimu Czy tekst jest zgodny z wzorcem? Czy komórka zawiera okreĺlone s§owo? Czy komórka zawiera tekst? Wyodr£bnianie n-tego elementu §aħcucha S§owny zapis liczb Funkcje zliczajŸce Zliczanie komórek zgodnych z wzorcem Zliczanie arkuszy w skoroszycie Zliczanie wyrazów w zakresie Zliczanie kolorów Funkcje operujŸce na datach Obliczanie daty nast£pnego poniedzia§ku Obliczanie daty nast£pnego dnia tygodnia Który tydzieħ miesiŸca? Obs§uga dat sprzed 1900 roku Zwracanie ostatniej, niepustej komórki w kolumnie lub wierszu Funkcja OSTATNIAWKOLUMNIE Funkcja OSTATNIAWWIERSZU Funkcje wieloarkuszowe Zwracanie maksymalnej wartoĺci z wielu arkuszy Funkcja SHEETOFFSET Zaawansowane techniki tworzenia funkcji Zwracanie wartoĺci b§£du Zwracanie tablicy przez funkcj£ Zwracanie tablicy niepowtarzalnych, losowych liczb ca§kowitych Zwracanie tablicy losowych liczb ca§kowitych z podanego zakresu Stosowanie argumentów opcjonalnych Pobieranie nieokreĺlonej liczby argumentów 727 728 728 728 729 730 730 731 731 732 734 735 738 738 739 741 742 743 744 744 744 745 746 747 748 748 749 750 750 751 751 752 752 753 753 754 754 756 756 757 757 758 759 760 760 762 763 765 767 768 Dodatki Spis treĂci 21 Dodatek A Wykaz funkcji Excela Dodatek B Niestandardowe formaty liczbowe O formatowaniu liczb Automatyczne formatowanie liczb Formatowanie liczb przy uŗyciu WstŸŗki Formatowanie liczb przy uŗyciu skrótów klawiaturowych Formatowanie liczb przy uŗyciu okna dialogowego Formatowanie komórek Tworzenie niestandardowego formatu liczbowego Elementy §aħcucha formatu liczbowego Kody niestandardowego formatu liczbowego Przyk§ady niestandardowych formatów liczbowych Skalowanie wartoĺci Ukrywanie zer Wyĺwietlanie zer poprzedzajŸcych Wyĺwietlanie u§amków Wyĺwietlanie N/D zamiast tekstu Wyĺwietlanie tekstu w cudzys§owach Powielanie wpisu w komórce Wyĺwietlanie minusa po prawej stronie Warunkowe formatowanie liczb Wyĺwietlanie wartoĺci w kolorach Formatowanie dat i godzin Wyĺwietlanie tekstu z liczbami Wyĺwietlanie kresek zamiast zer Uŗycie symboli specjalnych Ukrywanie poszczególnych typów informacji Wype§nianie komórek powtarzajŸcymi si£ znakami Wyĺwietlanie kropek wiodŸcych Dodatek C Dodatkowe zasoby Excela System pomocy Excela Wsparcie techniczne ze strony Microsoftu Opcje wsparcia Pomoc techniczna firmy Microsoft Strona domowa programu Microsoft Excel Strona domowa pakietu Microsoft Office Internetowe grupy dyskusyjne 775 793 793 794 795 795 796 797 799 800 802 803 806 807 807 808 808 808 809 809 810 811 811 812 812 813 813 814 815 815 816 816 816 816 816 817 817 818 819 820 820 820 821 821 821 821 821 821 Dost£p do grup dyskusyjnych przy uŗyciu czytnika Dost£p do grup dyskusyjnych przy uŗyciu przeglŸdarki internetowej Przeszukiwanie grup dyskusyjnych Witryny internetowe Strona Spreadsheet Daily Dose of Excel Strona Jona Peltiera Pearson Software Consulting Contextures Strony Davida McRitchiego Pointy Haired Dilbert Mr. Excel 22 Spis treĂci Dodatek D Przyk«ady do« czone do ksi Śki Rozdzia§ 1. Rozdzia§ 5. Rozdzia§ 6. Rozdzia§ 7. Rozdzia§ 8. Rozdzia§ 9. Rozdzia§ 10. Rozdzia§ 11. Rozdzia§ 12. Rozdzia§ 13. Rozdzia§ 15. Rozdzia§ 16. Rozdzia§ 17. Rozdzia§ 18. Rozdzia§ 19. Rozdzia§ 20. Rozdzia§ 24. Rozdzia§ 25. Dodatek A Dodatek B Skorowidz 823 824 824 825 825 825 826 826 826 827 827 827 828 828 829 830 830 831 831 832 832 833 Rozdziaï 8 Funkcje wyszukiwania W TYM ROZDZIALE: x Wprowadzenie do formu§ wyszukujŸcych wartoĺci w tabelach x PrzeglŸd funkcji arkusza uŗywanych do wyszukiwania x Podstawowe formu§y wyszukujŸce x 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 252 CzÚĂÊ II Stosowanie funkcji w formuïach 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 C2 D2 E2 F2 =WYSZUKAJ.PIONOWO(B2; DanePracowników;2; FA’SZ) =WYSZUKAJ.PIONOWO(B2; DanePracowników;3; FA’SZ) =WYSZUKAJ.PIONOWO(B2; DanePracowników;4; FA’SZ) =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 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 INDEKS PODAJ.POZYCJ} PRZESUNI}CIE WYBIERZ WYSZUKAJ Opis Zwraca wartoĂÊ (lub odwoïanie do wartoĂci) z tabeli lub zakresu. Zwraca wzglÚdne poïoĝenie elementu w zakresie, które pasuje do podanej wartoĂci. Zwraca odwoïanie do zakresu przesuniÚte o okreĂlonÈ liczbÚ wierszy i kolumn wzglÚdem jakiejĂ komórki lub jakiegoĂ zakresu komórek. Zwraca okreĂlonÈ wartoĂÊ z listy wartoĂci (do 29) podanych jako argumenty. 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ĺó. 254 CzÚĂÊ II Stosowanie funkcji w formuïach 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: x szukana_wartoĂÊ — wartoĺó, która ma byó wyszukana w pierwszej kolumnie tablicy. x tablica — zakres zawierajŸcy tablic£ do przeszukania. x numer_kolumny — numer kolumny w tablicy, z której zwracana jest pasujŸca wartoĺó. x 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: x szukana_wartoĂÊ — wartoĺó, która ma byó wyszukana w pierwszym wierszu przeszukiwanej tabeli. 256 CzÚĂÊ II Stosowanie funkcji w formuïach x tabela_tablica — zakres zawierajŸcy przeszukiwanŸ tabel£. x numer_indeksu_wiersza — numer wiersza w tabeli, z którego ma byó zwrócona pasujŸca wartoĺó. x 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: x szukana_wartoĂÊ — wartoĺó, która ma byó wyszukana w przeszukiwanym wektorze. x 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. x 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. 258 CzÚĂÊ II Stosowanie funkcji w formuïach ’È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: x 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 ?. x przeszukiwana_tablica — tablica, która ma byó przeszukana. x 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: x tablica — zakres; x numer_wiersza — numer wiersza w tablicy; x 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. 260 CzÚĂÊ II Stosowanie funkcji w formuïach 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¥m Aby zmieniÊ wyĂwietlanie bïÚdu #N/D w przypadku nieznalezienia numeru pracownika na coĂ innego, moĝna uĝyÊ funkcji JE¿ELI.B’kD, 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’kD(WYSZUKAJ.PIONOWO(B1;ListaPracowników;2;FA’SZ) ´ Nie znaleziono ) Funkcja JE¿ELI.B’kD 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)) 262 CzÚĂÊ II Stosowanie funkcji w formuïach 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. 264 CzÚĂÊ II Stosowanie funkcji w formuïach 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. 266 CzÚĂÊ II Stosowanie funkcji w formuïach 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 MiesiÈc Produkt Tabela ListaMiesiÚcy ListaProduktów Adres B1 B2 D1:H14 D1:D14 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)) 268 CzÚĂÊ II Stosowanie funkcji w formuïach 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 B1 B2 D2:D12 E2:E12 Kod Marka Model Marki 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. 270 CzÚĂÊ II Stosowanie funkcji w formuïach Wyszukiwanie wartoĂci przy uĝyciu najbliĝszego dopasowania Funkcje WYSZUKAJ.PIONOWO i WYSZUKAJ.POZIOMO sŸ przydatne w nast£pujŸcych sytuacjach: x Chcemy zidentyfikowaó wartoĺó dok§adnie pasujŸcŸ do docelowej wartoĺci. W takim przypadku ustawiamy czwarty argument funkcji na wartoĺó FA’SZ. x 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 272 CzÚĂÊ II Stosowanie funkcji w formuïach 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
Pobierz darmowy fragment (pdf)

Gdzie kupić całą publikację:

Excel 2010 PL. Formuły
Autor:

Opinie na temat publikacji:


Inne popularne pozycje z tej kategorii:


Czytaj również:


Prowadzisz stronę lub blog? Wstaw link do fragmentu tej książki i współpracuj z Cyfroteką: