Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00095 013590 12794459 na godz. na dobę w sumie
Excel. 101 formuł gotowych do użycia - ebook/pdf
Excel. 101 formuł gotowych do użycia - ebook/pdf
Autor: , Liczba stron: 200
Wydawca: Helion Język publikacji: polski
ISBN: 978-83-283-0632-5 Data wydania:
Lektor:
Kategoria: ebooki >> komputery i informatyka >> aplikacje biurowe >> vba - programowanie
Porównaj ceny (książka, ebook (-20%), audiobook).

Najlepsze formuły dla Excela!

Miliony użytkowników każdego dnia korzystają z najpopularniejszego arkusza kalkulacyjnego — Excela. To jedna z najlepszych aplikacji należących do pakietu Microsoft Office. Jej potencjał doceniają przede wszystkim menedżerowie, analitycy oraz studenci. Zastanawiasz się, jak w pełni wykorzystać możliwości Excela? Jeśli chcesz dowiedzieć się, jak pisać zaawansowane formuły, ta książka jest właśnie dla Ciebie!

Znajdziesz w niej ponad 100 gotowych zaawansowanych formuł. Nauczysz się także tworzyć je z użyciem popularnych funkcji matematycznych, tekstowych oraz wyszukujących. Ponadto poznasz najczęściej stosowane formuły biznesowe i finansowe, wykonasz analizę statystyczną oraz wykorzystasz formuły do formatowania warunkowego. Książka ta jest obowiązkową pozycją na półce każdego użytkownika Excela. Sięgnij po nią i przekonaj się sam!

Dzięki tej książce:

Wykorzystaj gotowe formuły w codziennej pracy z Excelem!

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

Darmowy fragment publikacji:

Tytuł oryginału: 101 Ready-to-Use Excel® Formulas Tłumaczenie: Andrzej Watrak ISBN: 978-83-283-0629-5 Copyright © 2014 by John Wiley Sons, Inc., Hoboken, New Jersey. Translation copyright © 2015 by Helion S.A. All Rights Reserved. This translation published under license with the original publisher John Wiley Sons, Inc. No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise without the prior written permission of the Publisher. Wiley and the Wiley logo are trademarks or registered trademarks of John Wiley Sons, Inc. and/or its affiliates in the United States and other countries and may not be used without written permission. Microsoft and Excel are registered trademarks of the Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley Sons, Inc. is not associated with any product or vendor mentioned in this book. Wszelkie prawa zastrzeżone. Nieautoryzowane rozpowszechnianie całości lub fragmentu niniejszej publikacji w jakiejkolwiek postaci jest zabronione. Wykonywanie kopii metodą kserograficzną, fotograficzną, a także kopiowanie książki na nośniku filmowym, magnetycznym lub innym powoduje naruszenie praw autorskich niniejszej publikacji. Wszystkie znaki występujące w tekście są zastrzeżonymi znakami firmowymi bądź towarowymi ich właścicieli. Autor oraz Wydawnictwo HELION dołożyli wszelkich starań, by zawarte w tej książce informacje były kompletne i rzetelne. Nie biorą jednak żadnej odpowiedzialności ani za ich wykorzystanie, ani za związane z tym ewentualne naruszenie praw patentowych lub autorskich. Autor oraz Wydawnictwo HELION nie ponoszą również żadnej odpowiedzialności za ewentualne szkody wynikłe z wykorzystania informacji zawartych w książce. Wydawnictwo HELION ul. Kościuszki 1c, 44-100 GLIWICE tel. 32 231 22 19, 32 230 98 63 e-mail: helion@helion.pl WWW: http://helion.pl (księgarnia internetowa, katalog książek) Drogi Czytelniku! Jeżeli chcesz ocenić tę książkę, zajrzyj pod adres http://helion.pl/user/opinie/exc101 Możesz tam wpisać swoje uwagi, spostrzeżenia, recenzję. Printed in Poland. • Kup książkę • Poleć książkę • Oceń książkę • Księgarnia internetowa • Lubię to! » Nasza społeczność Spis tre(cid:308)ci O autorach ............................................................................................ 11 Wprowadzenie ...................................................................................... 13 Rozdzia(cid:281) 1. Wprowadzenie do formu(cid:281) Excela ........................................................ 17 Tworzenie i edycja formu(cid:225) ...................................................................................................17 Sposoby wprowadzania formu(cid:225) .....................................................................................18 Edycja formu(cid:225)y ..............................................................................................................19 Operatory u(cid:298)ywane w formu(cid:225)ach .........................................................................................19 Hierarchia operatorów ...................................................................................................19 Zagnie(cid:298)d(cid:298)one nawiasy ..................................................................................................21 Wzgl(cid:266)dne i bezwzgl(cid:266)dne odwo(cid:225)ania do komórek ................................................................21 Zewn(cid:266)trzne odwo(cid:225)ania do komórek .....................................................................................23 Tryby przeliczania formu(cid:225) ...................................................................................................24 Funkcje Excela ....................................................................................................................24 Dlaczego nale(cid:298)y u(cid:298)ywa(cid:252) funkcji Excela? ......................................................................24 Argumenty funkcji ........................................................................................................25 Uzyskiwanie pomocy kreatora Wstawianie funkcji ......................................................27 B(cid:225)(cid:266)dy zwracane przez formu(cid:225)y ............................................................................................28 Nazwane zakresy komórek w formu(cid:225)ach .............................................................................29 Definiowanie nazwanych zakresów ..............................................................................30 Pole nazwy ....................................................................................................................31 Rozdzia(cid:281) 2. Popularne operacje matematyczne .................................................. 33 Formu(cid:225)a 1.: obliczanie procentowej realizacji celu .............................................................33 Jak to dzia(cid:225)a ...................................................................................................................34 Alternatywa: wspólny cel ..............................................................................................34 Formu(cid:225)a 2.: obliczanie odchylenia procentowego ...............................................................34 Jak to dzia(cid:225)a ...................................................................................................................35 Alternatywa: uproszczone obliczanie odchylenia procentowego ..................................35 Formu(cid:225)a 3.: obliczanie odchylenia procentowego dla liczb ujemnych ................................36 Jak to dzia(cid:225)a ...................................................................................................................36 Formu(cid:225)a 4.: obliczanie rozk(cid:225)adu procentowego ...................................................................37 Jak to dzia(cid:225)a ...................................................................................................................37 Alternatywa: rozk(cid:225)ad procentowy bez osobnej komórki z warto(cid:286)ci(cid:261) ca(cid:225)kowit(cid:261) ............37 Formu(cid:225)a 5.: obliczanie bie(cid:298)(cid:261)cej sumy ..................................................................................38 Jak to dzia(cid:225)a ...................................................................................................................38 Formu(cid:225)a 6.: procentowe zwi(cid:266)kszanie i zmniejszanie warto(cid:286)ci ............................................39 Jak to dzia(cid:225)a ...................................................................................................................39 Kup książkęPoleć książkę 6 Excel. 101 formu(cid:273) gotowych do u(cid:318)ycia Formu(cid:225)a 7.: obs(cid:225)uga b(cid:225)(cid:266)dów dzielenia przez zero ................................................................40 Jak to dzia(cid:225)a ...................................................................................................................41 Formu(cid:225)a 8.: proste zaokr(cid:261)glanie liczb ..................................................................................41 Jak to dzia(cid:225)a ...................................................................................................................41 Formu(cid:225)a 9.: zaokr(cid:261)glanie do groszy .....................................................................................42 Jak to dzia(cid:225)a ...................................................................................................................42 Formu(cid:225)a 10.: zaokr(cid:261)glanie do cyfr znacz(cid:261)cych ....................................................................43 Jak to dzia(cid:225)a ...................................................................................................................43 Formu(cid:225)a 11.: zliczanie warto(cid:286)ci w zakresie komórek ..........................................................44 Jak to dzia(cid:225)a ...................................................................................................................45 Formu(cid:225)a 12.: tworzenie tabeli zamiany jednostek ...............................................................45 Jak to dzia(cid:225)a ...................................................................................................................46 Rozdzia(cid:281) 3. Przetwarzanie tekstu za pomoc(cid:225) formu(cid:281) ............................................. 47 Formu(cid:225)a 13.: (cid:225)(cid:261)czenie ci(cid:261)gów znaków .................................................................................47 Jak to dzia(cid:225)a ...................................................................................................................48 Formu(cid:225)a 14.: wielka litera na pocz(cid:261)tku zdania ....................................................................48 Jak to dzia(cid:225)a ...................................................................................................................48 Formu(cid:225)a 15.: usuwanie odst(cid:266)pów z ci(cid:261)gu znaków ...............................................................49 Jak to dzia(cid:225)a ...................................................................................................................50 Formu(cid:225)a 16.: wyodr(cid:266)bnianie fragmentów ci(cid:261)gów znaków ..................................................50 Jak to dzia(cid:225)a ...................................................................................................................50 Formu(cid:225)a 17.: wyszukiwanie okre(cid:286)lonego znaku w tek(cid:286)cie ..................................................51 Jak to dzia(cid:225)a ...................................................................................................................52 Alternatywa: wyszukiwanie drugiego wyst(cid:261)pienia znaku .............................................53 Formu(cid:225)a 18.: zamiana ci(cid:261)gów znaków ................................................................................53 Jak to dzia(cid:225)a ...................................................................................................................54 Formu(cid:225)a 19.: zliczanie okre(cid:286)lonych znaków w komórce .....................................................55 Jak to dzia(cid:225)a ...................................................................................................................55 Formu(cid:225)a 20.: umieszczanie podzia(cid:225)u wiersza za pomoc(cid:261) formu(cid:225)y .......................................56 Jak to dzia(cid:225)a ...................................................................................................................56 Formu(cid:225)a 21.: usuwanie dziwnych znaków z tekstu ..............................................................57 Jak to dzia(cid:225)a ...................................................................................................................57 Formu(cid:225)a 22.: wype(cid:225)nianie liczb zerami ................................................................................57 Jak to dzia(cid:225)a ...................................................................................................................58 Formu(cid:225)a 23.: formatowanie liczb w tekstach .......................................................................58 Jak to dzia(cid:225)a ...................................................................................................................59 Alternatywa: funkcja KWOTA .....................................................................................60 Rozdzia(cid:281) 4. Data i czas ............................................................................................. 61 Formu(cid:225)a 24.: odczyt bie(cid:298)(cid:261)cej daty i czasu ...........................................................................61 Jak to dzia(cid:225)a ...................................................................................................................61 Formu(cid:225)a 25.: obliczanie wieku ............................................................................................62 Jak to dzia(cid:225)a ...................................................................................................................62 Formu(cid:225)a 26.: obliczanie liczby dni pomi(cid:266)dzy dwiema datami .............................................63 Jak to dzia(cid:225)a ...................................................................................................................63 Formu(cid:225)a 27.: obliczanie liczby dni roboczych pomi(cid:266)dzy dwiema datami ...........................64 Jak to dzia(cid:225)a ...................................................................................................................64 Alternatywa: funkcja DNI.ROBOCZE.NIESTAND .....................................................64 Formu(cid:225)a 28.: tworzenie listy dni z pomini(cid:266)ciem dni wolnych od pracy ..............................65 Jak to dzia(cid:225)a ...................................................................................................................66 Formu(cid:225)a 29.: wyodr(cid:266)bnianie cz(cid:266)(cid:286)ci daty .............................................................................66 Jak to dzia(cid:225)a ...................................................................................................................67 Kup książkęPoleć książkę Spis tre(cid:295)ci 7 Formu(cid:225)a 30.: obliczanie liczby lat i miesi(cid:266)cy pomi(cid:266)dzy dwiema datami .............................68 Jak to dzia(cid:225)a ...................................................................................................................68 Formu(cid:225)a 31.: zmiana formatu dat na format julia(cid:276)ski ..........................................................69 Jak to dzia(cid:225)a ...................................................................................................................69 Formu(cid:225)a 32.: obliczanie minionej i pozosta(cid:225)ej procentowej cz(cid:266)(cid:286)ci roku ..............................70 Jak to dzia(cid:225)a ...................................................................................................................71 Formu(cid:225)a 33.: okre(cid:286)lanie daty ostatniego dnia miesi(cid:261)ca .......................................................71 Jak to dzia(cid:225)a ...................................................................................................................71 Alternatywa: funkcja NR.SER.OST.DN.MIES .............................................................72 Formu(cid:225)a 34.: obliczanie kwarta(cid:225)u roku kalendarzowego na podstawie daty ........................72 Jak to dzia(cid:225)a ...................................................................................................................73 Formu(cid:225)a 35.: obliczanie kwarta(cid:225)u roku obrotowego na podstawie daty ...............................73 Jak to dzia(cid:225)a ...................................................................................................................74 Formu(cid:225)a 36.: okre(cid:286)lanie miesi(cid:261)ca obrotowego na podstawie daty .......................................74 Jak to dzia(cid:225)a ...................................................................................................................75 Formu(cid:225)a 37.: obliczanie daty n-tego dnia tygodnia w miesi(cid:261)cu ...........................................75 Jak to dzia(cid:225)a ...................................................................................................................76 Formu(cid:225)a 38.: obliczanie daty ostatniego dnia tygodnia w miesi(cid:261)cu .....................................76 Jak to dzia(cid:225)a ...................................................................................................................77 Formu(cid:225)a 39.: wyodr(cid:266)bnianie cz(cid:266)(cid:286)ci czasu ...........................................................................77 Jak to dzia(cid:225)a ...................................................................................................................77 Formu(cid:225)a 40.: obliczanie odst(cid:266)pu czasu ................................................................................78 Jak to dzia(cid:225)a ...................................................................................................................78 Formu(cid:225)a 41.: zaokr(cid:261)glanie oznaczenia czasu .......................................................................79 Jak to dzia(cid:225)a ...................................................................................................................79 Formu(cid:225)a 42.: zamiana liczby godzin, minut i sekund na oznaczenie czasu .........................80 Jak to dzia(cid:225)a ...................................................................................................................80 Formu(cid:225)a 43.: dodawanie godzin, minut i sekund do oznaczenia czasu ................................80 Jak to dzia(cid:225)a ...................................................................................................................81 Rozdzia(cid:281) 5. Analiza warunkowa .............................................................................. 83 Formu(cid:225)a 44.: sprawdzanie, czy spe(cid:225)niony jest prosty warunek ............................................83 Jak to dzia(cid:225)a ...................................................................................................................84 Formu(cid:225)a 45.: sprawdzanie wielu warunków ........................................................................85 Jak to dzia(cid:225)a ...................................................................................................................85 Alternatywa: wyszukiwanie warto(cid:286)ci ............................................................................87 Formu(cid:225)a 46.: sprawdzanie, czy spe(cid:225)niony jest warunek1 ORAZ warunek2 ........................87 Jak to dzia(cid:225)a ...................................................................................................................88 Alternatywa: odwo(cid:225)ania do komórek z warto(cid:286)ciami logicznymi ...................................89 Formu(cid:225)a 47.: sprawdzanie, czy spe(cid:225)niony jest warunek1 LUB warunek2 ...........................90 Jak to dzia(cid:225)a ...................................................................................................................90 Formu(cid:225)a 48.: suma wszystkich warto(cid:286)ci spe(cid:225)niaj(cid:261)cych okre(cid:286)lony warunek ........................91 Jak to dzia(cid:225)a ...................................................................................................................92 Alternatywa: sumowanie warto(cid:286)ci wi(cid:266)kszych od zera ..................................................92 Formu(cid:225)a 49.: sumowanie wszystkich warto(cid:286)ci spe(cid:225)niaj(cid:261)cych dwa lub wi(cid:266)cej warunków ...94 Jak to dzia(cid:225)a ...................................................................................................................95 Alternatywa: funkcja SUMA.ILOCZYNÓW ................................................................95 Formu(cid:225)a 50.: sumowanie warto(cid:286)ci z okre(cid:286)lonego przedzia(cid:225)u dat .........................................95 Jak to dzia(cid:225)a ...................................................................................................................96 Alternatywa 1.: funkcja SUMA.WARUNKÓW ...........................................................97 Alternatywa 2.: funkcja SUMA.ILOCZYNÓW ............................................................97 Formu(cid:225)a 51.: zliczanie warto(cid:286)ci spe(cid:225)niaj(cid:261)cych okre(cid:286)lony warunek .....................................98 Jak to dzia(cid:225)a ...................................................................................................................98 Alternatywa: funkcja SUMA.ILOCZYNÓW ................................................................99 Kup książkęPoleć książkę 8 Excel. 101 formu(cid:273) gotowych do u(cid:318)ycia Formu(cid:225)a 52.: zliczanie warto(cid:286)ci spe(cid:225)niaj(cid:261)cych dwa lub wi(cid:266)cej warunków ..........................99 Alternatywa: SUMA.ILOCZYNÓW ...........................................................................101 Formu(cid:225)a 53.: obliczanie (cid:286)redniej wszystkich warto(cid:286)ci spe(cid:225)niaj(cid:261)cych okre(cid:286)lony warunek .....102 Jak to dzia(cid:225)a .................................................................................................................102 Alternatywa: funkcja LICZ.JE(cid:297)ELI ............................................................................103 Formu(cid:225)a 54.: obliczanie (cid:286)redniej wszystkich warto(cid:286)ci spe(cid:225)niaj(cid:261)cych dwa Rozdzia(cid:281) 6. lub wi(cid:266)cej warunków .....................................................................................................103 Jak to dzia(cid:225)a .................................................................................................................104 Alternatywa: funkcje SUMA.WARUNKÓW i LICZ.WARUNKI .............................104 Formu(cid:281)y do wyszukiwania danych .................................................... 105 Formu(cid:225)a 55.: wyszukiwanie dok(cid:225)adnej warto(cid:286)ci w oparciu o lew(cid:261) skrajn(cid:261) kolumn(cid:266) danych ....105 Jak to dzia(cid:225)a .................................................................................................................106 Formu(cid:225)a 56.: wyszukiwanie dok(cid:225)adnej warto(cid:286)ci w oparciu o dowoln(cid:261) kolumn(cid:266) danych .....107 Jak to dzia(cid:225)a .................................................................................................................108 Alternatywa: funkcja WYSZUKAJ .............................................................................108 Formu(cid:225)a 57.: wyszukiwanie warto(cid:286)ci w poziomie .............................................................109 Jak to dzia(cid:225)a .................................................................................................................109 Alternatywa: funkcje INDEKS i PODAJ.POZYCJ(cid:265) ..................................................110 Formu(cid:225)a 58.: ukrywanie b(cid:225)(cid:266)dów zwracanych przez funkcje wyszukuj(cid:261)ce ........................110 Jak to dzia(cid:225)a .................................................................................................................110 Alternatywa: funkcja CZY.BRAK ..............................................................................112 Formu(cid:225)a 59.: wyszukiwanie najlepszego dopasowania w li(cid:286)cie zakresów warto(cid:286)ci ..........112 Jak to dzia(cid:225)a .................................................................................................................113 Alternatywa: funkcje INDEKS i PODAJ.POZYCJ(cid:265) ..................................................114 Formu(cid:225)a 60.: wyszukiwanie warto(cid:286)ci w ró(cid:298)nych tabelach .................................................115 Jak to dzia(cid:225)a .................................................................................................................117 Formu(cid:225)a 61.: wyszukiwanie warto(cid:286)ci w dwuwymiarowej tabeli .......................................117 Jak to dzia(cid:225)a .................................................................................................................117 Alternatywa: funkcja PODAJ.POZYCJ(cid:265) i warto(cid:286)ci domy(cid:286)lne ...................................118 Formu(cid:225)a 62.: wyszukiwanie warto(cid:286)ci na podstawie wielu kryteriów ................................119 Jak to dzia(cid:225)a .................................................................................................................120 Alternatywa: funkcja SUMA.ILOCZYNÓW zwracaj(cid:261)ca tekst ...................................120 Formu(cid:225)a 63.: wyszukiwanie ostatniej warto(cid:286)ci w kolumnie danych ..................................121 Jak to dzia(cid:225)a .................................................................................................................121 Alternatywa: wyszukiwanie ostatniej warto(cid:286)ci za pomoc(cid:261) funkcji WYSZUKAJ .......121 Formu(cid:225)a 64.: wyszukiwanie n-tego wyst(cid:261)pienia warto(cid:286)ci .................................................122 Jak to dzia(cid:225)a .................................................................................................................123 Formu(cid:225)a 65.: wyszukiwanie z uwzgl(cid:266)dnieniem wielko(cid:286)ci znaków ...................................124 Jak to dzia(cid:225)a .................................................................................................................124 Formu(cid:225)a 66.: wybór metody agregacji danych przez u(cid:298)ytkownika ....................................125 Jak to dzia(cid:225)a .................................................................................................................125 Alternatywa: funkcje WYBIERZ i PODAJ.POZYCJ(cid:265) ...............................................126 Rozdzia(cid:281) 7. Najcz(cid:245)(cid:308)ciej stosowane formu(cid:281)y biznesowe i finansowe ................. 127 Formu(cid:225)a 67.: obliczanie zysku brutto i procentowego zysku brutto ..................................127 Jak to dzia(cid:225)a .................................................................................................................127 Alternatywa: obliczanie narzutu ..................................................................................128 Formu(cid:225)a 68.: obliczanie wska(cid:296)ników EBIT i EBITDA .....................................................129 Jak to dzia(cid:225)a .................................................................................................................129 Formu(cid:225)a 69.: obliczanie kosztu w(cid:225)asnego sprzeda(cid:298)y .........................................................130 Jak to dzia(cid:225)a .................................................................................................................130 Kup książkęPoleć książkę Spis tre(cid:295)ci 9 Formu(cid:225)a 70.: obliczanie stopy zwrotu z aktywów .............................................................131 Jak to dzia(cid:225)a .................................................................................................................131 Alternatywa: obliczanie zwrotu na kapitale w(cid:225)asnym .................................................132 Formu(cid:225)a 71.: obliczanie progu rentowno(cid:286)ci .......................................................................132 Jak to dzia(cid:225)a .................................................................................................................133 Formu(cid:225)a 72.: obliczanie wska(cid:296)nika odp(cid:225)ywu klientów ......................................................134 Jak to dzia(cid:225)a .................................................................................................................134 Alternatywa: roczny wska(cid:296)nik odp(cid:225)ywu klientów .......................................................135 Formu(cid:225)a 73.: obliczanie (cid:286)redniej okresowej warto(cid:286)ci klienta ............................................135 Jak to dzia(cid:225)a .................................................................................................................136 Formu(cid:225)a 74.: obliczanie wska(cid:296)nika rotacji pracowników ..................................................136 Jak to dzia(cid:225)a .................................................................................................................137 Formu(cid:225)a 75.: przeliczanie oprocentowania ........................................................................137 Jak to dzia(cid:225)a .................................................................................................................138 Alternatywa: obliczanie oprocentowania efektywnego za pomoc(cid:261) funkcji FV ...........139 Formu(cid:225)a 76.: kalkulator sp(cid:225)aty kredytu ..............................................................................139 Jak to dzia(cid:225)a .................................................................................................................139 Alternatywa: utworzenie harmonogramu sp(cid:225)at kredytu ...............................................140 Formu(cid:225)a 77.: utworzenie harmonogramu sp(cid:225)aty kredytu ze zmiennym oprocentowaniem ....142 Jak to dzia(cid:225)a .................................................................................................................143 Alternatywa: daty zamiast numerów rat ......................................................................143 Formu(cid:225)a 78.: obliczanie amortyzacji .................................................................................144 Jak to dzia(cid:225)a .................................................................................................................145 Alternatywa: amortyzacja przyspieszona ....................................................................146 Formu(cid:225)a 79.: obliczanie warto(cid:286)ci bie(cid:298)(cid:261)cej inwestycji ........................................................148 Jak to dzia(cid:225)a .................................................................................................................148 Alternatywa: obliczanie warto(cid:286)ci bie(cid:298)(cid:261)cej przysz(cid:225)ych p(cid:225)atno(cid:286)ci .................................149 Formu(cid:225)a 80.: obliczenia warto(cid:286)ci bie(cid:298)(cid:261)cej netto ................................................................150 Jak to dzia(cid:225)a .................................................................................................................151 Alternatywa: dodatnie i ujemne przep(cid:225)ywy pieni(cid:266)(cid:298)ne .................................................151 Formu(cid:225)a 81.: obliczanie wewn(cid:266)trznej stopy zwrotu ..........................................................152 Jak to dzia(cid:225)a .................................................................................................................153 Alternatywa: nieregularne przysz(cid:225)e przep(cid:225)ywy pieni(cid:266)(cid:298)ne ...........................................153 Rozdzia(cid:281) 8. Najcz(cid:245)(cid:308)ciej wykonywane analizy statystyczne ............................... 155 Formu(cid:225)a 82.: obliczanie (cid:286)redniej wa(cid:298)onej .........................................................................155 Jak to dzia(cid:225)a .................................................................................................................156 Alternatywa: funkcja SUMA .......................................................................................156 Formu(cid:225)a 83.: wyg(cid:225)adzanie danych i (cid:286)rednia ruchoma ........................................................157 Jak to dzia(cid:225)a .................................................................................................................158 Formu(cid:225)a 84.: wyg(cid:225)adzanie wyk(cid:225)adnicze silnie zmiennych danych ....................................160 Jak to dzia(cid:225)a .................................................................................................................160 Formu(cid:225)a 85.: wyszukiwanie najwi(cid:266)kszej lub najmniejszej warto(cid:286)ci ..................................161 Jak to dzia(cid:225)a .................................................................................................................162 Formu(cid:225)a 86.: znajdowanie k-tej najwi(cid:266)kszej lub najmniejszej warto(cid:286)ci ............................162 Jak to dzia(cid:225)a .................................................................................................................163 Alternatywa: funkcja POZYCJA .................................................................................164 Formu(cid:225)a 87.: obliczanie (cid:286)redniej, mediany i najcz(cid:266)(cid:286)ciej wyst(cid:266)puj(cid:261)cej warto(cid:286)ci ................165 Jak to dzia(cid:225)a .................................................................................................................166 Alternatywa: funkcja WYST.NAJCZ(cid:265)(cid:285)CIEJ.TABL ..................................................167 Formu(cid:225)a 88.: dzielenie danych na przedzia(cid:225)y ....................................................................167 Jak to dzia(cid:225)a .................................................................................................................168 Alternatywa: funkcja KWARTYL.PRZEDZ.OTW ....................................................170 Kup książkęPoleć książkę 10 Excel. 101 formu(cid:273) gotowych do u(cid:318)ycia Formu(cid:225)a 89.: wyszukiwanie warto(cid:286)ci odstaj(cid:261)cych metod(cid:261) rozst(cid:266)pu (cid:252)wiartkowego ................171 Jak to dzia(cid:225)a .................................................................................................................172 Formu(cid:225)a 90.: tworzenie rozk(cid:225)adu cz(cid:266)sto(cid:286)ci ........................................................................173 Jak to dzia(cid:225)a .................................................................................................................174 Alternatywa: funkcja LICZ.WARUNKI .....................................................................174 Formu(cid:225)a 91.: kompensacja sezonowo(cid:286)ci danych przed ich prognozowaniem ...................175 Jak to dzia(cid:225)a .................................................................................................................176 Formu(cid:225)a 92.: prognozowanie trendu ..................................................................................177 Jak to dzia(cid:225)a .................................................................................................................177 Zastosowanie formu(cid:281) w formatowaniu warunkowym ...................... 179 Formu(cid:225)a 93.: wyró(cid:298)nianie komórek spe(cid:225)niaj(cid:261)cych okre(cid:286)lone kryteria ...............................179 Jak to dzia(cid:225)a .................................................................................................................179 Formu(cid:225)a 94.: wyró(cid:298)nianie komórek w zale(cid:298)no(cid:286)ci od warto(cid:286)ci innych komórek ...............181 Jak to dzia(cid:225)a .................................................................................................................181 Formu(cid:225)a 95.: wyró(cid:298)nianie komórek, których warto(cid:286)ci znajduj(cid:261) si(cid:266) na li(cid:286)cie nr 1, ale nie ma ich na li(cid:286)cie nr 2 ............................................................................................182 Jak to dzia(cid:225)a .................................................................................................................182 Formu(cid:225)a 96.: wyró(cid:298)nianie komórek, których warto(cid:286)ci znajduj(cid:261) si(cid:266) na li(cid:286)cie nr 1 i na li(cid:286)cie nr 2 .................................................................................................................184 Jak to dzia(cid:225)a .................................................................................................................185 Formu(cid:225)a 97.: wyró(cid:298)nianie dat weekendów ........................................................................186 Jak to dzia(cid:225)a .................................................................................................................186 Formu(cid:225)a 98.: wyró(cid:298)nianie dni pomi(cid:266)dzy dwiema datami ..................................................188 Jak to dzia(cid:225)a .................................................................................................................188 Formu(cid:225)a 99.: formatowanie dat na podstawie okre(cid:286)lonego terminu ..................................189 Jak to dzia(cid:225)a .................................................................................................................190 Formu(cid:225)a 100.: wyró(cid:298)nianie dat na podstawie klasyfikacji percentylowej .........................191 Jak to dzia(cid:225)a .................................................................................................................192 Formu(cid:225)a 101.: wyró(cid:298)nianie warto(cid:286)ci statystycznie odstaj(cid:261)cych ........................................194 Jak to dzia(cid:225)a .................................................................................................................195 Skorowidz ............................................................................................. 197 Rozdzia(cid:281) 9. Kup książkęPoleć książkę Rozdzia(cid:273) 3. Przetwarzanie tekstu za pomoc(cid:225) formu(cid:281) Praca w Excelu cz(cid:266)sto polega nie tylko na wykonywaniu oblicze(cid:276), ale równie(cid:298) na prze- kszta(cid:225)caniu danych tak, aby otrzyma(cid:252) (cid:298)(cid:261)dany rezultat. Wiele z tych czynno(cid:286)ci dotyczy przetwarzania tekstu. W tym rozdziale opisane s(cid:261) operacje przekszta(cid:225)cania tekstu, które u(cid:298)ytkownik Excela musi cz(cid:266)sto wykonywa(cid:252). Dzi(cid:266)ki nim poznasz kilka funkcji teksto- wych oferowanych przez Excela. Przyk(cid:228)adowe pliki ze wszystkimi formu(cid:228)ami mo(cid:276)esz pobra(cid:232) pod adresem http://www.helion.pl/ksiazki/exc101.htm. Formu(cid:281)a 13.: (cid:281)(cid:225)czenie ci(cid:225)gów znaków Jedn(cid:261) z najbardziej podstawowych operacji przetwarzania tekstu jest (cid:225)(cid:261)czenie ci(cid:261)gów znaków. W przyk(cid:225)adzie pokazanym na rysunku 3.1 utworzona jest kolumna zawieraj(cid:261)ca pe(cid:225)ne imiona i nazwiska powsta(cid:225)e w wyniku z(cid:225)(cid:261)czenia imion z nazwiskami. Rysunek 3.1. (cid:227)(cid:241)czenie imienia i nazwiska Kup książkęPoleć książkę 48 Excel. 101 formu(cid:273) gotowych do u(cid:318)ycia Jak to dzia(cid:281)a Ten przyk(cid:225)ad ilustruje u(cid:298)ycie operatora (cid:225)(cid:261)czenia ( ) dwóch warto(cid:286)ci. Jak pokazuje rysu- nek 3.1, za jego pomoc(cid:261) mo(cid:298)na (cid:225)(cid:261)czy(cid:252) dowolne warto(cid:286)ci tekstowe. W tym przyk(cid:225)adzie (cid:225)(cid:261)czona jest zawarto(cid:286)(cid:252) komórki B3 i C3 z odst(cid:266)pem pomi(cid:266)dzy nimi (utworzonym po- przez zamkni(cid:266)cie spacji w cudzys(cid:225)owach). Excel oferuje równie(cid:276) funkcj(cid:246) Z(cid:146)(cid:107)CZ.TEKSTY, która (cid:228)(cid:241)czy ci(cid:241)gi znaków bez konieczno(cid:264)ci stosowania operatora . W tym przyk(cid:228)adzie mo(cid:276)esz u(cid:276)y(cid:232) formu(cid:228)y =Z(cid:146)(cid:107)CZ.TEKSTY(B3; ;C3). Lepiej jest jednak nie korzysta(cid:232) z tej funkcji i po prostu stosowa(cid:232) operator. Funkcja bardziej obci(cid:241)(cid:276)a system i wymaga wpisania wi(cid:246)kszej liczby znaków. Formu(cid:281)a 14.: wielka litera na pocz(cid:225)tku zdania Excel oferuje trzy przydatne funkcje do zamiany wszystkich znaków w tek(cid:286)cie na wielkie lub ma(cid:225)e litery oraz do zamiany tylko pierwszych znaków w s(cid:225)owach na wielkie litery. Jak pokazuje rysunek 3.2, funkcje u(cid:298)yte w wierszach 6., 7. i 8. nie wymagaj(cid:261) niczego wi(cid:266)cej poza wskazaniem tekstu, który ma by(cid:252) przekszta(cid:225)cony. Jak si(cid:266) zapewne domy(cid:286)lasz, funkcja LITERY.WIELKIE zamienia wszystkie znaki na wielkie litery, a funkcja Z.WIELKIEJ.LITERY zamienia na wielk(cid:261) liter(cid:266) tylko pierwszy znak ka(cid:298)dego s(cid:225)owa. Rysunek 3.2. Zamiana wszystkich znaków na wielkie lub ma(cid:228)e litery oraz zamiana pierwszych znaków w s(cid:228)owach i zdaniach na wielkie litery W Excelu brakuje jednak funkcji, która zamienia na wielk(cid:261) liter(cid:266) tylko pierwszy znak pierwszego s(cid:225)owa w zdaniu. Jednak jak pokazuje rysunek 3.2, aby zamieni(cid:252) na wielk(cid:261) pierwsz(cid:261) liter(cid:266) zdania, mo(cid:298)esz u(cid:298)y(cid:252) poni(cid:298)szej formu(cid:225)y: =LITERY.WIELKIE(LEWY(C4;1)) LITERY.MA(cid:272)E(PRAWY(C4;D(cid:272)(C4)-1)) Jak to dzia(cid:281)a Je(cid:298)eli dok(cid:225)adniej przyjrzysz si(cid:266) powy(cid:298)szej formule, zauwa(cid:298)ysz, (cid:298)e sk(cid:225)ada si(cid:266) ona z dwóch cz(cid:266)(cid:286)ci po(cid:225)(cid:261)czonych operatorem . Pierwsza cz(cid:266)(cid:286)(cid:252) formu(cid:225)y wykorzystuje funkcj(cid:266) LEWY: LITERY.WIELKIE(LEWY(C4;1)) Kup książkęPoleć książkę Rozdzia(cid:273) 3. (cid:139) Przetwarzanie tekstu za pomoc(cid:230) formu(cid:273) 49 Funkcja LEWY umo(cid:298)liwia wyodr(cid:266)bnienie okre(cid:286)lonej liczby pierwszych znaków zadanego tekstu. Funkcja wymaga podania dwóch argumentów: przetwarzanego tekstu oraz liczby znaków, które maj(cid:261) by(cid:252) wyodr(cid:266)bnione z jego pocz(cid:261)tku. W tym przyk(cid:225)adzie wyodr(cid:266)b- niany jest jeden znak z tekstu z komórki C4. Znak ten nast(cid:266)pnie jest przetwarzany przez funkcj(cid:266) LITERY.WIELKIE. Druga cz(cid:266)(cid:286)(cid:252) formu(cid:225)y jest nieco bardziej z(cid:225)o(cid:298)ona. Zastosowana jest w niej funkcja PRAWY: LITERY.MA(cid:272)E(PRAWY(C4;D(cid:272)(C4)-1)) Funkcja PRAWY, podobnie jak funkcja LEWY, wymaga podania dwóch argumentów: przetwa- rzanego tekstu oraz liczby znaków, które maj(cid:261) by(cid:252) wyodr(cid:266)bnione z jego ko(cid:276)ca. W tym przypadku jednak nie mo(cid:298)na po prostu wpisa(cid:252) na sta(cid:225)e warto(cid:286)ci drugiego argumentu. Trzeba j(cid:261) obliczy(cid:252) poprzez odj(cid:266)cie jedno(cid:286)ci od d(cid:225)ugo(cid:286)ci ca(cid:225)ego tekstu. Odj(cid:266)cie jedno(cid:286)ci jest konieczne z tego powodu, (cid:298)e pierwszy znak jest ju(cid:298) zamieniony na wielk(cid:261) liter(cid:266) w pierwszej cz(cid:266)(cid:286)ci formu(cid:225)y. Do okre(cid:286)lenia d(cid:225)ugo(cid:286)ci tekstu jest zastosowana funkcja D(cid:146). Od d(cid:225)ugo(cid:286)ci jest odejmowana liczba 1, dzi(cid:266)ki czemu otrzymuje si(cid:266) liczb(cid:266) znaków potrzebn(cid:261) w funkcji PRAWY. Na koniec tak utworzona formu(cid:225)a jest umieszczona w funkcji LITERY.MA(cid:146)E, dzi(cid:266)ki czemu wszystkie znaki, oprócz pierwszego, s(cid:261) zamieniane na ma(cid:225)e litery. W wyniku po(cid:225)(cid:261)czenia obu cz(cid:266)(cid:286)ci formu(cid:225)y zdanie jest rozpoczynane wielk(cid:261) liter(cid:261): =LITERY.WIELKIE(LEWY(C4;1)) LITERY.MA(cid:272)E(PRAWY(C4;D(cid:272)(C4)-1)) Formu(cid:281)a 15.: usuwanie odst(cid:245)pów z ci(cid:225)gu znaków Gdy b(cid:266)dziesz pobiera(cid:225) dane z zewn(cid:266)trznej bazy danych lub ze starszych systemów, z ca(cid:225)(cid:261) pewno(cid:286)ci(cid:261) otrzymasz teksty zawieraj(cid:261)ce niepotrzebne odst(cid:266)py. Niektóre z nich b(cid:266)d(cid:261) znajdowa(cid:225)y si(cid:266) na pocz(cid:261)tku tekstu, inne na jego ko(cid:276)cu. Dodatkowe odst(cid:266)py s(cid:261) generalnie niepo(cid:298)(cid:261)dane, poniewa(cid:298) s(cid:261) przyczyn(cid:261) problemów podczas stosowania funkcji wyszukuj(cid:261)cych, tworzenia wykresów, zmiany szeroko(cid:286)ci kolumn i drukowania arkuszy. Rysunek 3.3 pokazuje, jak mo(cid:298)na usun(cid:261)(cid:252) niepotrzebne odst(cid:266)py za pomoc(cid:261) funkcji USU(cid:148).ZB(cid:125)DNE.ODST(cid:125)PY. Rysunek 3.3. Usuwanie z tekstu zb(cid:246)dnych odst(cid:246)pów Kup książkęPoleć książkę 50 Excel. 101 formu(cid:273) gotowych do u(cid:318)ycia Jak to dzia(cid:281)a Funkcja USU(cid:148).ZB(cid:125)DNE.ODST(cid:125)PY jest ca(cid:225)kiem prosta. Wystarczy poda(cid:252) jej tekst, a usunie z niego wszystkie odst(cid:266)py z wyj(cid:261)tkiem pojedynczych spacji mi(cid:266)dzy s(cid:225)owami. Podobnie jak w przypadku innych funkcji, mo(cid:298)esz zagnie(cid:296)dzi(cid:252) funkcj(cid:266) USU(cid:148).ZB(cid:125)DNE.ODST(cid:125)PY w innych funkcjach w celu oczyszczenia tekstu i wykonania innych operacji. Na przyk(cid:225)ad poni(cid:298)sza formu(cid:225)a za jednym razem usuwa odst(cid:266)py z tekstu w komórce A1 i zamienia znaki na wielkie litery: =LITERY.WIELKIE(USU(cid:274).ZB(cid:249)DNE.ODST(cid:249)PY(A1)) Funkcja USU(cid:148).ZB(cid:125)DNE.ODST(cid:125)PY s(cid:228)u(cid:276)y do usuwania z tekstu tylko spacji ze standardu ASCII, tj. znaku o kodzie 32. Jednak w standardzie Unicode znajduje si(cid:246) inny znak, tzw. twarda spacja. Jest ona cz(cid:246)sto wykorzystywana na stronach WWW, a jej kod Unicode jest równy 160. Funkcja USU(cid:148).ZB(cid:125)DNE.ODST(cid:125)PY umo(cid:276)liwia usuwanie tylko spacji o kodzie 32. Nie usuwa spacji o kodzie 160. Aby usun(cid:241)(cid:232) takie spacje, musisz u(cid:276)y(cid:232) funkcji PODSTAW, by wyszuka(cid:232) spacje o kodzie 160 i zast(cid:241)pi(cid:232) je spacjami o kodzie 32, dzi(cid:246)ki czemu b(cid:246)d(cid:241) mog(cid:228)y by(cid:232) usuni(cid:246)te za pomoc(cid:241) funkcji USU(cid:148).ZB(cid:125)DNE.ODST(cid:125)PY. Wszystkie operacje mo(cid:276)esz wykona(cid:232) za jednym razem przy u(cid:276)yciu nast(cid:246)puj(cid:241)cej formu(cid:228)y: =USU(cid:274).ZB(cid:249)DNE.ODST(cid:249)PY(PODSTAW(A4;ZNAK(160);ZNAK(32))) Szczegó(cid:228)owy opis funkcji PODSTAW znajduje si(cid:246) w podrozdziale „Formu(cid:228)a 18.: zamiana ci(cid:241)gów znaków”. Formu(cid:281)a 16.: wyodr(cid:245)bnianie fragmentów ci(cid:225)gów znaków Jedn(cid:261) z najwa(cid:298)niejszych technik przetwarzania ci(cid:261)gów znaków w Excelu jest wyodr(cid:266)b- nianie okre(cid:286)lonych fragmentów tekstu. Za pomoc(cid:261) funkcji LEWY, PRAWY i FRAGMENT.TEKSTU mo(cid:298)esz wykona(cid:252) takie operacje jak: (cid:132) Zamiana dziewi(cid:266)ciocyfrowego kodu terytorialnego na kod pocztowy. (cid:132) Oddzielenie numeru telefonu od numeru kierunkowego. (cid:132) Wyodr(cid:266)bnienie fragmentu kodu pracownika lub stanowiska na potrzeby innych zastosowa(cid:276). Rysunek 3.4 pokazuje, jak (cid:225)atwo za pomoc(cid:261) funkcji LEWY, PRAWY i FRAGMENT.TEKSTU wyko- nuje si(cid:266) powy(cid:298)sze czynno(cid:286)ci. Jak to dzia(cid:281)a Funkcja LEWY umo(cid:298)liwia wyodr(cid:266)bnienie okre(cid:286)lonej liczby znaków z pocz(cid:261)tku zadanego tekstu. Funkcja ta wymaga podania dwóch argumentów: przetwarzanego tekstu i liczby znaków, które maj(cid:261) by(cid:252) wyodr(cid:266)bnione. W poni(cid:298)szym przyk(cid:225)adzie wyodr(cid:266)bnianych jest pi(cid:266)(cid:252) pierwszych znaków z tekstu w komórce A4: =LEWY(A4;5) Kup książkęPoleć książkę Rozdzia(cid:273) 3. (cid:139) Przetwarzanie tekstu za pomoc(cid:230) formu(cid:273) 51 Rysunek 3.4. Zastosowanie funkcji LEWY, PRAWY i FRAGMENT.TEKSTU Funkcja PRAWY umo(cid:298)liwia wyodr(cid:266)bnienie okre(cid:286)lonej liczby znaków z ko(cid:276)ca zadanego tekstu. Funkcja ta wymaga podania dwóch argumentów: przetwarzanego tekstu i liczby znaków, które maj(cid:261) by(cid:252) wyodr(cid:266)bnione. W poni(cid:298)szym przyk(cid:225)adzie wyodr(cid:266)bnianych jest osiem ostatnich znaków z tekstu w komórce A9: =PRAWY(A9;8) Funkcja FRAGMENT.TEKSTU umo(cid:298)liwia wyodr(cid:266)bnienie okre(cid:286)lonej liczby znaków ze (cid:286)rodka zadanego tekstu. Funkcja ta wymaga podania trzech argumentów: przetwarzanego tekstu, pozycji znaku, od którego ma si(cid:266) rozpocz(cid:261)(cid:252) wyodr(cid:266)bnianie, i liczby znaków do wyod- r(cid:266)bnienia. W poni(cid:298)szym przyk(cid:225)adzie wyodr(cid:266)bniany jest jeden znak, pocz(cid:261)wszy od czwartej pozycji: =FRAGMENT.TEKSTU(A14;4;1) Formu(cid:281)a 17.: wyszukiwanie okre(cid:308)lonego znaku w tek(cid:308)cie Funkcje LEWY, PRAWY i FRAGMENT.TEKSTU doskonale nadaj(cid:261) si(cid:266) do wyodr(cid:266)bniania znaków, ale pod warunkiem, (cid:298)e dok(cid:225)adnie znasz ich po(cid:225)o(cid:298)enie w tek(cid:286)cie. Co jednak zrobisz, gdy nie b(cid:266)dziesz wiedzia(cid:225) dok(cid:225)adnie, od którego miejsca maj(cid:261) by(cid:252) wyodr(cid:266)bniane znaki? Je(cid:298)eli na przyk(cid:225)ad b(cid:266)dziesz mia(cid:225) poni(cid:298)sz(cid:261) list(cid:266) kodów produktów, jak b(cid:266)dziesz móg(cid:225) wyodr(cid:266)bni(cid:252) wszystkie znaki po my(cid:286)lniku? PRT-432 COPR-6758 SVCCALL-58574 Kup książkęPoleć książkę 52 Excel. 101 formu(cid:273) gotowych do u(cid:318)ycia Funkcji LEWY nie mo(cid:298)na u(cid:298)y(cid:252), poniewa(cid:298) musisz wyodr(cid:266)bni(cid:252) kilka ostatnich znaków. Sama funkcja PRAWY równie(cid:298) nie sprawdzi si(cid:266), poniewa(cid:298) musisz w niej dok(cid:225)adnie poda(cid:252), ile znaków ma by(cid:252) wyodr(cid:266)bnionych z ko(cid:276)ca tekstu. Podanie jakiejkolwiek liczby mo(cid:298)e spowodowa(cid:252), (cid:298)e wyodr(cid:266)bnionych zostanie za ma(cid:225)o lub za du(cid:298)o znaków. W praktyce cz(cid:266)sto b(cid:266)dziesz musia(cid:225) wyszukiwa(cid:252) okre(cid:286)lony znak, aby znale(cid:296)(cid:252) w(cid:225)a(cid:286)ciw(cid:261) pocz(cid:261)tkow(cid:261) pozycj(cid:266) wyodr(cid:266)bnianego ci(cid:261)gu. W tym miejscu pojawia si(cid:266) funkcja ZNAJD(cid:189). Przy jej u(cid:298)yciu mo(cid:298)esz okre(cid:286)li(cid:252) pozycj(cid:266) za- danego znaku w tek(cid:286)cie i wykorzysta(cid:252) j(cid:261) do innych operacji. W przyk(cid:225)adzie pokazanym na rysunku 3.5 funkcja ZNAJD(cid:189) jest stosowana w po(cid:225)(cid:261)czeniu z funkcj(cid:261) FRAGMENT.TEKSTU w celu wyodr(cid:266)bnienia (cid:286)rodkowych cyfr z kodu produktu. Jak wida(cid:252), w formule tej jest wyszukiwana pozycja my(cid:286)lnika, któr(cid:261) nast(cid:266)pnie wykorzystuje funkcja FRAGMENT.TEKSTU. =FRAGMENT.TEKSTU(B3;ZNAJD(cid:315)( - ;B3)+1;2) Rysunek 3.5. Zastosowanie funkcji ZNAJD(cid:189) do wyodr(cid:246)bnienia znaków na podstawie po(cid:228)o(cid:276)enia my(cid:264)lnika Jak to dzia(cid:281)a Funkcja ZNAJD(cid:189) wymaga podania dwóch argumentów. Pierwszym z nich jest tekst, któ- ry ma by(cid:252) wyszukany. Drugim argumentem jest tekst przeszukiwany. Domy(cid:286)lnie funk- cja ta zwraca numer pozycji wyszukiwanego ci(cid:261)gu. Je(cid:298)eli przeszukiwany tekst zawiera wi(cid:266)cej ni(cid:298) jeden poszukiwany znak, funkcja zwraca pozycj(cid:266) pierwszego wyst(cid:261)pienia poszukiwanego znaku. Na przyk(cid:225)ad poni(cid:298)sza formu(cid:225)a wyszukuje my(cid:286)lnik w ci(cid:261)gu PWR-16-Small . Wynikiem jest liczba 4, poniewa(cid:298) pierwszy wyszukany my(cid:286)lnik jest czwartym znakiem ci(cid:261)gu. =ZNAJD(cid:315)( - ; PWR-16-Small ) Funkcji ZNAJD(cid:189) mo(cid:298)esz u(cid:298)y(cid:252) jako argumentu funkcji FRAGMENT.TEKSTU i wyodr(cid:266)bni(cid:252) ci(cid:261)g znaków, pocz(cid:261)wszy od numeru pozycji okre(cid:286)lonej za pomoc(cid:261) funkcji ZNAJD(cid:189). Wpisanie w komórce poni(cid:298)szej formu(cid:225)y spowoduje wyodr(cid:266)bnienie dwóch znaków znaj- duj(cid:261)cych si(cid:266) po my(cid:286)lniku znalezionym w ci(cid:261)gu znaków. Zwró(cid:252) uwag(cid:266) na dodawanie +1 w formule, dzi(cid:266)ki któremu pomijany jest jeden znak, aby okre(cid:286)li(cid:252) pozycj(cid:266) znaków po my(cid:286)lniku. =FRAGMENT.TEKSTU( PWR-16-Small ;ZNAJD(cid:315)( - ; PWR-16-Small )+1;2) Kup książkęPoleć książkę Rozdzia(cid:273) 3. (cid:139) Przetwarzanie tekstu za pomoc(cid:230) formu(cid:273) 53 Alternatywa: wyszukiwanie drugiego wyst(cid:225)pienia znaku Domy(cid:286)lnie funkcja ZNAJD(cid:189) zwraca pozycj(cid:266) pierwszego wyst(cid:261)pienia wyszukiwanego znaku. Je(cid:298)eli potrzebujesz znale(cid:296)(cid:252) pozycj(cid:266) drugiego wyst(cid:261)pienia, mo(cid:298)esz u(cid:298)y(cid:252) opcjo- nalnego argumentu liczba_pocz(cid:200)tkowa. Ten argument pozwala okre(cid:286)li(cid:252) pozycj(cid:266) znaku, od którego ma si(cid:266) rozpocz(cid:261)(cid:252) wyszukiwanie. Na przyk(cid:225)ad poni(cid:298)sza formu(cid:225)a zwraca pozycj(cid:266) drugiego my(cid:286)lnika, poniewa(cid:298) zawiera wskazanie, (cid:298)e wyszukiwanie ma rozpocz(cid:261)(cid:252) si(cid:266) od znaku na pi(cid:261)tej pozycji (po pierwszym my(cid:286)lniku). =ZNAJD(cid:315)( - ; PWR-16-Small ;5) Aby korzysta(cid:252) z tej formu(cid:225)y w sposób dynamiczny (tj. nie wiedz(cid:261)c, od której pozycji ma rozpocz(cid:261)(cid:252) si(cid:266) wyszukiwanie), mo(cid:298)esz zagnie(cid:296)dzi(cid:252) funkcj(cid:266) ZNAJD(cid:189) jako argument liczba_pocz(cid:200)tkowa w drugiej funkcji ZNAJD(cid:189). By znale(cid:296)(cid:252) pozycj(cid:266) drugiego my(cid:286)lnika, wpisz poni(cid:298)sz(cid:261) formu(cid:225)(cid:266): =ZNAJD(cid:315)( - ; PWR-16-Small ;ZNAJD(cid:315)( - ; PWR-16-Small )+1) Rysunek 3.6 pokazuje wzi(cid:266)ty z (cid:298)ycia przyk(cid:225)ad zastosowania tego sposobu. Z kodu pro- duktu wyodr(cid:266)bniane jest oznaczenie rozmiaru poprzez wyszukanie drugiego wyst(cid:261)pienia my(cid:286)lnika i u(cid:298)ycie jego pozycji jako pozycji pocz(cid:261)tkowej w funkcji FRAGMENT.TEKSTU. Formu(cid:225)a u(cid:298)yta w komórce C3 wygl(cid:261)da nast(cid:266)puj(cid:261)co: =FRAGMENT.TEKSTU(B3;ZNAJD(cid:315)( - ;B3;ZNAJD(cid:315)( - ;B3)+1)+1;10000) Rysunek 3.6. Zagnie(cid:276)d(cid:276)enie funkcji ZNAJD(cid:273) w celu wyodr(cid:246)bnienia wszystkich znaków po drugim my(cid:264)lniku Powy(cid:298)sza formu(cid:225)a wyszukuje pozycj(cid:266) drugiego my(cid:286)lnika, zwi(cid:266)ksza j(cid:261) o jeden znak i wy- odr(cid:266)bnia nast(cid:266)pnych 10 000 znaków. Oczywi(cid:286)cie tekst nie zawiera a(cid:298) tylu znaków, ale u(cid:298)ycie tak du(cid:298)ej liczby jak ta zapewnia wyodr(cid:266)bnienie wszystkich znaków znajduj(cid:261)cych si(cid:266) po drugim my(cid:286)lniku. Formu(cid:281)a 18.: zamiana ci(cid:225)gów znaków W niektórych sytuacjach przydaje si(cid:266) mo(cid:298)liwo(cid:286)(cid:252) zast(cid:261)pienia jednego tekstu innym. Takim przypadkiem jest irytuj(cid:261)cy sufiks ’S otrzymywany w wyniku u(cid:298)ycia funkcji Z.WIELKIEJ.LITERY. Aby zrozumie(cid:252), co mam na my(cid:286)li, wpisz poni(cid:298)sz(cid:261) formu(cid:225)(cid:266): =Z.WIELKIEJ.LITERY( STAR S COFFEE ) Kup książkęPoleć książkę 54 Excel. 101 formu(cid:273) gotowych do u(cid:318)ycia Formu(cid:225)a ta ma za zadanie zamienia(cid:252) pierwsze znaki s(cid:225)ów na wielkie litery. Zwracany przez ni(cid:261) wynik jest nast(cid:266)puj(cid:261)cy: Star S Coffee Zwró(cid:252) uwag(cid:266), (cid:298)e funkcja zamieni(cid:225)a na wielk(cid:261) liter(cid:266) S po apostrofie. Jest to co najmniej irytuj(cid:261)ce. Jednak z niewielk(cid:261) pomoc(cid:261) funkcji PODSTAW mo(cid:298)esz usun(cid:261)(cid:252) t(cid:266) niedogodno(cid:286)(cid:252). Rysunek 3.7 pokazuje rozwi(cid:261)zanie wykorzystuj(cid:261)ce nast(cid:266)puj(cid:261)c(cid:261) formu(cid:225)(cid:266): =PODSTAW(Z.WIELKIEJ.LITERY(PODSTAW(B4; ; qzx )); qzx ; ) Rysunek 3.7. Rozwi(cid:241)zanie problemu z wielk(cid:241) liter(cid:241) S po apostrofie za pomoc(cid:241) funkcji PODSTAW Jak to dzia(cid:281)a Powy(cid:298)sza formu(cid:225)a wykorzystuje funkcj(cid:266) PODSTAW, która wymaga podania trzech ar- gumentów: przetwarzanego tekstu, nast(cid:266)pnie tekstu, który ma by(cid:252) zast(cid:261)piony, oraz nowego tekstu. Je(cid:298)eli przyjrzysz si(cid:266) ca(cid:225)ej formule, zauwa(cid:298)ysz, (cid:298)e u(cid:298)yte s(cid:261) w niej dwie funkcje PODSTAW. S(cid:261) to w rzeczywisto(cid:286)ci dwie formu(cid:225)y (jedna zagnie(cid:298)d(cid:298)ona w drugiej). Pierwsza z nich wygl(cid:261)da nast(cid:266)puj(cid:261)co: Z.WIELKIEJ.LITERY(PODSTAW(B4; ; qzx )) W tej cz(cid:266)(cid:286)ci funkcja PODSTAW jest wykorzystywana do zast(cid:261)pienia apostrofu (’) ci(cid:261)giem qzx. Wygl(cid:261)da to do(cid:286)(cid:252) dziwnie, ale jest w tym metoda. Funkcja Z.WIELKIEJ.LITERY z de- finicji zamienia na wielkie litery wszystkie znaki znajduj(cid:261)ce si(cid:266) bezpo(cid:286)rednio po okre- (cid:286)lonych symbolach. Funkcja jest „oszukiwana” poprzez zamian(cid:266) apostrofu na okre(cid:286)lony ci(cid:261)g znaków, który z niewielkim prawdopodobie(cid:276)stwem mo(cid:298)e ju(cid:298) si(cid:266) znajdowa(cid:252) w orygi- nalnym tek(cid:286)cie. Druga formu(cid:225)a obejmuje pierwsz(cid:261). Zmienia ona ci(cid:261)g qzx z powrotem na apostrof. =PODSTAW(Z.WIELKIEJ.LITERY(PODSTAW(B4; ; qzx )); qzx ; ) Zatem ca(cid:225)a formu(cid:225)a zamienia apostrof na ci(cid:261)g qzx, nast(cid:266)pnie wywo(cid:225)uje funkcj(cid:266) Z.WIELKIEJ.LITERY i na koniec zamienia ci(cid:261)g qzx z powrotem na apostrof. Kup książkęPoleć książkę Rozdzia(cid:273) 3. (cid:139) Przetwarzanie tekstu za pomoc(cid:230) formu(cid:273) 55 Formu(cid:281)a 19.: zliczanie okre(cid:308)lonych znaków w komórce Jedna z przydatnych sztuczek polega na zliczaniu liczby wyst(cid:261)pie(cid:276) okre(cid:286)lonego znaku w tek(cid:286)cie. Sposób, w jaki mo(cid:298)na to osi(cid:261)gn(cid:261)(cid:252) w Excelu, jest do(cid:286)(cid:252) sprytny. Aby na przy- k(cid:225)ad sprawdzi(cid:252), ile liter s znajduje si(cid:266) w wyrazie Missisipi, mo(cid:298)esz oczywi(cid:286)cie policzy(cid:252) je r(cid:266)cznie, ale (cid:298)eby to zrobi(cid:252) racjonalnie, powiniene(cid:286) wykona(cid:252) nast(cid:266)puj(cid:261)ce kroki: (cid:132) Okre(cid:286)li(cid:252) d(cid:225)ugo(cid:286)(cid:252) wyrazu Missisipi (9 znaków). (cid:132) Okre(cid:286)li(cid:252) d(cid:225)ugo(cid:286)(cid:252) wyrazu po usuni(cid:266)ciu wszystkich liter s (6 znaków). (cid:132) Odj(cid:261)(cid:252) d(cid:225)ugo(cid:286)(cid:252) nowego tekstu od pocz(cid:261)tkowej d(cid:225)ugo(cid:286)ci. W ten sposób mo(cid:298)esz dok(cid:225)adnie okre(cid:286)li(cid:252), (cid:298)e liczba liter s w wyrazie Missisipi jest równa 3. Praktycznym zastosowaniem tej metody jest zliczanie okre(cid:286)lonych znaków w celu poli- czenia s(cid:225)ów w komórce Excela. Rysunek 3.8 pokazuje formu(cid:225)(cid:266) u(cid:298)yt(cid:261) do policzenia s(cid:225)ów w komórce B4: =D(cid:272)(B4)-D(cid:272)(PODSTAW(B4; ; ))+1 Rysunek 3.8. Zliczanie s(cid:228)ów w komórce Jak to dzia(cid:281)a Powy(cid:298)sza formu(cid:225)a dok(cid:225)adnie realizuje czynno(cid:286)ci opisane wcze(cid:286)niej w tym podrozdziale. Najpierw funkcja D(cid:146) sprawdza d(cid:225)ugo(cid:286)(cid:252) tekstu w komórce B4: D(cid:272)(B4) Nast(cid:266)pnie funkcja PODSTAW usuwa z tekstu odst(cid:266)py: PODSTAW(B4; ; ) Umieszczenie funkcji PODSTAW w funkcji D(cid:146) pozwala sprawdzi(cid:252) d(cid:225)ugo(cid:286)(cid:252) tekstu bez spacji. Zwró(cid:252) uwag(cid:266), (cid:298)e do uzyskanego wyniku musisz doda(cid:252) jeden, poniewa(cid:298) ostatnie s(cid:225)owo w tek(cid:286)cie nie ma odpowiadaj(cid:261)cej mu spacji. D(cid:272)(PODSTAW(B4; ; ))+1 Po odj(cid:266)ciu skorygowanej d(cid:225)ugo(cid:286)ci od d(cid:225)ugo(cid:286)ci oryginalnego tekstu wyliczana jest liczba s(cid:225)ów: =D(cid:272)(B4)-D(cid:272)(PODSTAW(B4; ; ))+1 Kup książkęPoleć książkę 56 Excel. 101 formu(cid:273) gotowych do u(cid:318)ycia Formu(cid:281)a 20.: umieszczanie podzia(cid:281)u wiersza za pomoc(cid:225) formu(cid:281)y Niekiedy podczas tworzenia wykresów w Excelu przydatne jest wymuszenie podzia(cid:225)ów wierszy w tek(cid:286)cie, aby uzyska(cid:252) lepszy efekt wizualny. Popatrzmy na przyk(cid:225)ad na wy- kres na rysunku 3.9. Etykiety osi X zawieraj(cid:261) imiona handlowców i dane o sprzeda(cid:298)y. Ten sposób opisywania wykresu sprawdza si(cid:266) w przypadku, gdy nie chcesz go za- ciemnia(cid:252), umieszczaj(cid:261)c na nim etykiety danych. Rysunek 3.9. Etykiety osi X na tym wykresie zawieraj(cid:241) podzia(cid:228)y wierszy i warto(cid:264)ci danych Sekret tej sztuczki polega na u(cid:298)yciu funkcji ZNAK w formule tworz(cid:261)cej etykiety danych (patrz rysunek 3.10). Rysunek 3.10. U(cid:276)ycie funkcji ZNAK w celu wymuszenia podzia(cid:228)u wiersza pomi(cid:246)dzy imieniem handlowca a warto(cid:264)ci(cid:241) sprzeda(cid:276)y Jak to dzia(cid:281)a Ze wszystkimi znakami w Excelu s(cid:261) skojarzone kody ASCII. S(cid:261) to kody znaków wy- (cid:286)wietlanych na ekranie w systemie Windows. Tabela kodów sk(cid:225)ada si(cid:266) z 255 znaków ponumerowanych od 1 do 255. Wielka litera A ma kod 97. Cyfra 9 ma kod 57. Swoje kody maj(cid:261) nawet znaki, które nie s(cid:261) drukowane. Kod spacji to 32. Znak podzia(cid:225)u wiersza ma kod 10. Kup książkęPoleć książkę Rozdzia(cid:273) 3. (cid:139) Przetwarzanie tekstu za pomoc(cid:230) formu(cid:273) 57 Za pomoc(cid:261) funkcji ZNAK mo(cid:298)esz umie(cid:286)ci(cid:252) w formule dowolny znak. W przyk(cid:225)adzie po- kazanym na rysunku 3.10 pobierany jest znak podzia(cid:225)u wiersza i z(cid:225)(cid:261)czany z warto(cid:286)ciami w komórkach A3 i C3: =A3 ZNAK(10) C3 Je(cid:298)eli w komórce nie zostanie zastosowane zawijanie tekstu, znaki podzia(cid:225)u wiersza nie b(cid:266)d(cid:261) widoczne. Ale nawet je(cid:298)eli tego nie zrobisz, podzia(cid:225)y wierszy umieszczone w da- nych zwracanych przez t(cid:266) formu(cid:225)(cid:266) zostan(cid:261) uwzgl(cid:266)dnione na wykresie. Formu(cid:281)a 21.: usuwanie dziwnych znaków z tekstu W danych importowanych z zewn(cid:266)trznych (cid:296)róde(cid:225), na przyk(cid:225)ad z plików tekstowych lub kana(cid:225)ów internetowych, mog(cid:261) pojawi(cid:252) si(cid:266) dziwne znaki. Zamiast usuwa(cid:252) je r(cid:266)cznie, mo(cid:298)esz skorzysta(cid:252) z funkcji OCZY(cid:165)(cid:109) (patrz rysunek 3.11). Rysunek 3.11. Oczyszczanie danych za pomoc(cid:241) funkcji OCZY(cid:263)(cid:231) Jak to dzia(cid:281)a Funkcja OCZY(cid:165)(cid:109) usuwa z zadanego tekstu znaki, których nie mo(cid:298)na wydrukowa(cid:252). Funk- cj(cid:266) t(cid:266) mo(cid:298)esz umie(cid:286)ci(cid:252) wewn(cid:261)trz funkcji USU(cid:148).ZB(cid:125)DNE.ODST(cid:125)PY, dzi(cid:266)ki czemu za jednym razem usuniesz niedrukowalne znaki i zb(cid:266)dne odst(cid:266)py. =USU(cid:274).ZB(cid:249)DNE.ODST(cid:249)PY(OCZY(cid:294)(cid:231)(B3)) Formu(cid:281)a 22.: wype(cid:281)nianie liczb zerami Cz(cid:266)sto zdarza si(cid:266), (cid:298)e dane, które przygotowujesz w Excelu, umieszczane s(cid:261) w bazach danych w Twojej firmie. Tego typu bazy danych cz(cid:266)sto wymagaj(cid:261), aby pola danych mia(cid:225)y okre(cid:286)lon(cid:261) d(cid:225)ugo(cid:286)(cid:252) wyra(cid:298)on(cid:261) za pomoc(cid:261) liczby znaków. Cz(cid:266)sto stosowan(cid:261) techni- k(cid:261) do tworzenia pól danych sk(cid:225)adaj(cid:261)cych si(cid:266) z zadanej liczby znaków jest wype(cid:225)nianie liczb zerami. Wype(cid:225)nianie danych zerami jest metod(cid:261) do(cid:286)(cid:252) prost(cid:261) w zastosowaniu. Je(cid:298)eli na przyk(cid:225)ad pole ID klienta musi mie(cid:252) d(cid:225)ugo(cid:286)(cid:252) 10 znaków, to aby spe(cid:225)ni(cid:252) ten wymóg, musisz dopi- sa(cid:252) do liczby odpowiedni(cid:261) liczb(cid:266) zer. Zatem do identyfikatora 5381656 musisz dopisa(cid:252) trzy zera i w efekcie otrzymasz identyfikator 5381656000. Kup książkęPoleć książkę 58 Excel. 101 formu(cid:273) gotowych do u(cid:318)ycia Na rysunku 3.12 w komórce C4 zastosowana jest formu(cid:225)a wype(cid:225)niaj(cid:261)ca zerami identyfi- katory z kolumny ID klienta: =LEWY(B4 0000000000 ;10) Rysunek 3.12. Wype(cid:228)nianie zerami pola ID klienta Jak to dzia(cid:281)a Formu(cid:225)
Pobierz darmowy fragment (pdf)

Gdzie kupić całą publikację:

Excel. 101 formuł gotowych do użycia
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ą: