Darmowy fragment publikacji:
Microsoft Office 2007 PL.
Rady i wskazówki.
Rozwi¹zania w biznesie
Autor: Paul McFedries
T³umaczenie: Tomasz Boszko
ISBN: 978-83-246-1377-9
Tytu³ orygina³u: Tricks of the
Microsoft Office 2007 Gurus
(Business Solutions)
Format: 170x230, stron: 520
Poznaj pe³niê mo¿liwoœci pakietu Office 2007
• Jak kontrolowaæ wygl¹d oraz treœæ dokumentów i arkuszy?
• W jaki sposób tworzyæ rozbudowane bazy danych?
• Jak zarz¹dzaæ terminami i kontaktami?
Pakiet biurowy Microsoft Office 2007 to u¿yteczne narzêdzie. Ogromna wiêkszoœæ jego
u¿ytkowników wykorzystuje jednak bardzo niewielk¹ czêœæ oferowanych mo¿liwoœci.
Szacuje siê, ¿e ponad 90 mo¿liwoœci Office’a pozostaje nieznane. Co jest tego
powodem? Owe ukryte mo¿liwoœci, pomimo swojej przydatnoœci, rzadko s¹ opisywane
podrêcznikach, a w dokumentacji programu pojawiaj¹ siê w formie czêsto trudnej
do zrozumienia. Prawie wszyscy koncentruj¹ siê na podstawowych zastosowaniach
Worda, Excela, PowerPointa i Accessa, zupe³nie pomijaj¹c te elementy, które mog¹
znacznie przyspieszyæ i usprawniæ pracê z pakietem Office.
Ksi¹¿ka „Microsoft Office 2007 PL. Rady i wskazówki. Rozwi¹zania w biznesie” jest
inna. Autor, bazuj¹c na ponad 20-letnim doœwiadczeniu w pracy z MS Office, zebra³
w niej setki wskazówek i praktycznych rozwi¹zañ pozwalaj¹cych na wyciœniêcie
maksimum mo¿liwoœci z Microsoft Office 2007 za pomoc¹ ma³o znanych lub dopiero
odkrytych ciekawych sztuczek i technik. Czytaj¹c tê ksi¹¿kê, poznasz niezwykle
wygodne sposoby formatowania tekstu i kontrolowania wygl¹du dokumentów,
usprawniania obliczeñ w Excelu, tworzenia wspania³ych prezentacji w PowerPoint
i zarz¹dzania danymi w Accessie, odkryjesz nowe mo¿liwoœci Outlooka i nauczysz siê
korzystaæ z makropoleceñ oraz jêzyka VBA.
• Optymalizacja pracy z tekstem
• Nieznane sposoby formatowania dokumentów
• Zaawansowanie formatowanie strony
• Efektywne funkcje i obliczenia
• Szybka analiza danych i wykresy
• Ciekawe animacje na slajdach w PowerPoint
• Niezwyk³e mo¿liwoœci poczty elektronicznej
• Sprawne zarz¹dzanie danymi
Usprawnij swoj¹ pracê – wyciœnij z MS Office 100 mo¿liwoœci
Spis treści
Wstęp ....................................................................................................... 15
Co znajduje się w książce? ........................................................................................................16
Wyjątkowe cechy tej książki .....................................................................................................16
I MICROSOFT WORD
1 Praca z tekstem ................................................................................... 21
Nagłówki tabeli w polu widzenia .............................................................................................22
Podsumowywanie kolumn w tabeli .........................................................................................23
Obliczanie podatku na fakturze ................................................................................................25
Podświetlanie słowa lub frazy ..................................................................................................27
Zastosowanie Autokorekty do wstawiania własnego podpisu .................................................28
Zastosowanie Autokorekty do wprowadzania powtarzalnych tekstów ...................................30
Zapisywanie powtarzalnego tekstu jako bloku konstrukcyjnego typu autotekst .....................31
Stworzenie obszaru „kliknij i wpisz” ........................................................................................33
Proszenie użytkownika o wpisanie tekstu ................................................................................35
Tworzenie dynamicznego wpisu Autokorekty .........................................................................36
Dodawanie zastępczego tekstu do dokumentu .......................................................................37
Zastosowanie funkcji RAND ..............................................................................................37
Zastosowanie komendy Powtórz ......................................................................................38
Wstawianie łącza do dokumentu .............................................................................................39
Tworzenie hiperłącza za pomocą Autokorekty .................................................................40
Tworzenie hiperłącza od zera ...........................................................................................41
Wklejanie hiperłącza w Wordzie .......................................................................................42
Wyświetlanie liczby wyrazów w zdaniu ...................................................................................42
Znajdowanie najdłuższego zdania w dokumencie ...................................................................44
Włączanie ukrytych symboli, kodów i tekstów ........................................................................46
Tworzenie ortograficznego słownika użytkownika ..................................................................47
Co dalej? ...................................................................................................................................49
Formatowanie ..................................................................................... 51
Szybka modyfikacja stylu Normalnego ....................................................................................52
Ustawianie galerii szybkich stylów, by odpowiadała charakterowi pracy ................................52
Zmiana formatowania planu dokumentu ................................................................................55
Zaznaczanie tekstu z podobnym formatowaniem ...................................................................56
Kopiowanie formatowania z jednej sekcji do innej ..................................................................57
2
4
Microsoft Office 2007 PL. Rady i wskazówki. Rozwiązania w biznesie
Porównywanie formatowania dwóch zaznaczonych fragmentów .......................................... 57
Precyzyjne usuwanie formatowania ........................................................................................ 59
Zastępowanie jednego stylu innym ......................................................................................... 60
Formatowanie za pomocą skrótów klawiaturowych ............................................................... 62
Dodawanie punktorów użytkownika ....................................................................................... 65
Tworzenie list numerowanych ................................................................................................. 67
Konwertowanie słów napisanych wielkimi literami ................................................................ 70
Zamiana cudzysłowów zwykłych na drukarskie ...................................................................... 71
Formatowanie pojedynczej kolumny jako tekstu odseparowanego tabulatorami .................. 73
Zapewnienie jednorodności formatowania ............................................................................. 74
Otaczanie obrazka tekstem ...................................................................................................... 76
Dodawanie inicjału .................................................................................................................. 77
Co dalej? .................................................................................................................................. 78
3 Praca z dokumentami ...........................................................................79
Odzyskiwanie większej części utraconych dokumentów dzięki krótszej przerwie
pomiędzy operacjami autoodzyskiwania .............................................................................. 79
Automatyczne częste zapisywanie plików ............................................................................... 80
Zamykanie dokumentu bez zapisywania ................................................................................. 82
Zamykanie wszystkich otwartych dokumentów ...................................................................... 84
Tworzenie kopii zapasowych podczas pracy ............................................................................ 85
Pokazywanie większej liczby elementów na liście niedawno używanych dokumentów ......... 87
Otwieranie ostatnio używanego dokumentu przy uruchamianiu aplikacji .............................. 87
Czyszczenie listy ostatnio używanych dokumentów ................................................................ 88
Tworzenie i otwieranie obszarów roboczych złożonych z dokumentów .................................. 89
Automatyczne pytanie o właściwości dokumentu ................................................................... 92
Tworzenie zaufanej lokalizacji dla dokumentów ..................................................................... 93
Podgląd całkowitego czasu edycji aktualizowany w czasie rzeczywistym ............................... 95
Obliczanie należności za przepracowany czas .......................................................................... 97
Zablokowanie formatowania dokumentu ............................................................................... 97
Śledzenie zmian ....................................................................................................................... 99
Edycja ustrukturalizowana .................................................................................................... 100
Badanie dokumentu pod kątem informacji osobistych ......................................................... 104
Przeglądanie dwóch dokumentów obok siebie ..................................................................... 105
Automatyczna aktualizacja wszystkich pól ............................................................................ 107
Co dalej? ................................................................................................................................ 108
Spis treści
5
4 Układ strony .......................................................................................109
Wyświetlanie tekstu w powiązanych polach tekstowych ......................................................109
Tworzenie odsyłaczy ..............................................................................................................111
Tworzenie indywidualnego znaku wodnego .........................................................................112
Wstawianie pergaminowego tła ............................................................................................114
Wyświetlanie dokumentu z przypadkową teksturą tła ..........................................................114
Wyświetlanie tekstu z numerami linii ....................................................................................116
Kontynuacja numerów w dwóch osobnych listach numerowanych ......................................118
Wykorzystanie różnych formatów numerowania stron w jednym dokumencie ....................119
Wstawianie unikalnego nagłówka i stopki na pierwszej stronie ............................................121
Zastosowanie różnych nagłówków dla stron parzystych i nieparzystych ...............................122
Dodawanie marginesu na oprawę .........................................................................................122
Tworzenie poziomów konspektu przez użytkownika .............................................................124
Co dalej? .................................................................................................................................124
5
II MICROSOFT EXCEL
Formuły i funkcje ................................................................................127
Komórki przyjmujące tylko określone wartości ......................................................................127
Wyszukiwanie wartości rabatu w tabeli .................................................................................129
Wyszukiwanie kodu klienta ...................................................................................................131
Generowanie kodów klienta ..................................................................................................132
Generowanie liczb losowych w celu przetestowania modelu arkusza ...................................135
Usuwanie zbędnych spacji z komórek ....................................................................................139
Usuwanie znaków niedrukowalnych z komórki .....................................................................140
Wydobywanie imion lub nazwisk z listy ................................................................................142
Arkusz wierzytelności .............................................................................................................145
Konwersja dat na format Excela .............................................................................................147
Obliczanie wartości premii .....................................................................................................148
Obliczanie liczby dni roboczych pomiędzy dwoma datami ....................................................148
Określanie kwartału obrachunkowego, do którego należy data ............................................150
Ustalanie dziesiętnej ceny produktu ......................................................................................152
Obliczanie rat kapitałowych i odsetek ....................................................................................152
Obliczanie maksymalnej wysokości pożyczki .........................................................................156
Obliczanie przyszłej wartości inwestycji .................................................................................157
Decyzja: kupić czy wziąć w leasing? .......................................................................................159
Co dalej? .................................................................................................................................161
6
Microsoft Office 2007 PL. Rady i wskazówki. Rozwiązania w biznesie
6 Arkusze i skoroszyty ............................................................................163
Formatowanie i wstawianie tekstu do wielu arkuszy ............................................................ 163
Dodawanie komentarzy do arkusza ....................................................................................... 165
Usuwanie wszystkich komentarzy ze skoroszytu ................................................................... 166
Śledzenie zmian w arkuszu .................................................................................................... 168
Udostępnianie skoroszytu innym użytkownikom .................................................................. 170
Zabezpieczenie arkusza przed zmianami dokonywanymi przez innych użytkowników ........ 173
Automatyczne sortowanie zakresu po wpisaniu danych ....................................................... 174
Zamienianie zakresów rzędów i kolumn ............................................................................... 176
Automatyczne otwieranie skoroszytu .................................................................................... 176
Tworzenie obszaru roboczego złożonego ze skoroszytów ..................................................... 177
Tworzenie skoroszytu z określoną liczbą arkuszy ................................................................... 178
Zaznaczanie komórki A1 we wszystkich skoroszytach ........................................................... 179
Zaznaczanie „komórki lokalnej” we wszystkich arkuszach .................................................... 180
Zaznaczanie nazwanego zakresu, który zawiera aktywną komórkę ...................................... 182
Zapisywanie wszystkich otwartych skoroszytów ................................................................... 183
Stosowanie formantów do wpisywania danych .................................................................... 185
Co dalej? ................................................................................................................................ 188
7 Analiza danych ...................................................................................189
Zastosowanie obrazu zakresu w celu obserwowania wartości komórki ................................ 189
Zastosowanie okna czujki do obserwowania wartości w komórce ........................................ 191
Obliczanie wielu rozwiązań dla formuły ................................................................................ 191
Podłączanie wielu wartości wejściowych do formuły ............................................................ 195
Określanie progu rentowności ............................................................................................... 198
Optymalizacja zysku i marży .................................................................................................. 202
Wyróżnianie komórek powyżej lub poniżej określonej wartości ........................................... 207
Wyróżnianie wartości poniżej mediany ................................................................................. 208
Analizowanie wartości w komórkach za pomocą pasków danych ......................................... 211
Zastosowanie ikon bazujących na percentylach .................................................................... 214
Co dalej? ................................................................................................................................ 219
8 Wykresy .............................................................................................221
Wykorzystanie tekstu z arkusza w wykresie .......................................................................... 222
Wykreślanie średniej na osi wartości ..................................................................................... 224
Wykorzystanie obrazka jako znacznika danych w wykresie słupkowym ............................... 227
Wykres zakresu dynamicznego .............................................................................................. 229
Spis treści
7
Automatyczne rozszerzanie wykresu w celu uwzględnienia nowych danych ........................232
Wyświetlanie drugiej pionowej osi ........................................................................................233
Wyróżnianie wycinka z wykresu kołowego ............................................................................234
Przedstawianie niewielkich wartości na słupku obok wykresu kołowego .............................235
Tworzenie przesuwającego się wykresu .................................................................................236
Wykreślanie linii trendu przedstawiającej najlepsze dopasowanie .......................................240
Wykreślanie wartości prognozowanych .................................................................................243
Co dalej? .................................................................................................................................244
III MICROSOFT POWERPOINT
9 Prezentacja i pokaz slajdów .................................................................247
Zamiana konspektu Worda na prezentację PowerPointa .......................................................248
Wstawianie tekstu użytkownika do stopki slajdu ..................................................................251
Tworzenie punktorów użytkownika .......................................................................................253
Zamiana czcionek w prezentacji .............................................................................................255
Dopasowanie koloru obrazka do prezentacji ..........................................................................256
Kopiowanie kształtów w równych odstępach ........................................................................257
Wykorzystanie prowadnic do precyzyjnego ustawiania obiektów .........................................258
Tworzenie własnego szablonu slajdu .....................................................................................259
Ukrywanie obiektu wzorca slajdów na wybranym slajdzie ....................................................263
Zastosowanie wielu wzorców slajdu ......................................................................................264
Tworzenie pustej prezentacji użytkownika ............................................................................265
Zarządzanie biblioteką slajdów ..............................................................................................267
Zamiana slajdu na obraz ........................................................................................................271
Tworzenie slajdu podsumowującego za pomocą makra ........................................................272
Wykorzystanie Worda do indywidualnego formatowania materiałów informacyjnych ........275
Co dalej? .................................................................................................................................276
10 Animacje ............................................................................................277
Ilustrowanie przejścia dźwiękiem ..........................................................................................278
Tworzenie animacji niestandardowych ..................................................................................280
Zastosowanie wyzwalaczy do wyświetlania i ukrywania obiektów .......................................283
Wyświetlanie punktorów kolejno ..........................................................................................285
Animowanie elementów wykresu .........................................................................................288
Animowanie wykresu organizacyjnego .................................................................................289
Odkrywanie jednej części obrazka po drugiej .........................................................................290
8
Microsoft Office 2007 PL. Rady i wskazówki. Rozwiązania w biznesie
Zastosowanie animacji do elementów tworzących clipart .................................................... 291
Odgrywanie wideo w celu przedstawienia slajdu .................................................................. 297
Podkreślanie aktywnego punktora ........................................................................................ 298
Dodawanie przesuwających się napisów końcowych ............................................................ 301
Co dalej? ................................................................................................................................ 302
11 Pokaz slajdów ....................................................................................303
Próbne pokazy slajdów .......................................................................................................... 303
Przypisywanie czasu do slajdu za pomocą makra .................................................................. 306
Obliczanie średniego czasu wyświetlania slajdu .................................................................... 308
Narracja w pokazie slajdów ................................................................................................... 309
Konfiguracja automatycznego pokazu slajdów ..................................................................... 310
Odgrywanie utworów z CD podczas pokazu slajdów ............................................................. 312
Tworzenie niestandardowego pokazu slajdów ...................................................................... 313
Prezentacja pokazu slajdów na dwóch monitorach ............................................................... 314
Kontrolowanie pokazu slajdów za pomocą klawiatury .......................................................... 316
Łączenie z ukrytym slajdem ................................................................................................... 318
Hiperłącze do ostatnio oglądanego slajdu ............................................................................. 320
Zabezpieczenie prezentacji poprzez jej dystrybucję w postaci pliku pokazu slajdów ............ 321
Publikowanie prezentacji slajdów w sieci .............................................................................. 322
Włączanie animacji w sieci .................................................................................................... 325
Kopiowanie prezentacji na CD ............................................................................................... 326
Co dalej? ................................................................................................................................ 328
IV MICROSOFT OUTLOOK
12 Poczta elektroniczna ..........................................................................331
Dostosowywanie pól wiadomości w skrzynce odbiorczej ...................................................... 332
Ustawienie poziomu zabezpieczeń przed niechcianą pocztą ................................................. 335
Blokowanie wiadomości od konkretnej osoby lub z określonej domeny ............................... 338
Blokowanie wiadomości zagranicznych lub napisanych w języku innym niż angielski ......... 339
Unikanie wirusów poprzez odczytywanie wiadomości zwykłym tekstem ............................. 340
Wysyłanie zablokowanego typu pliku jako załącznika .......................................................... 342
Ustawianie przypomnienia związanego z wysłaną wiadomością .......................................... 347
Wysyłanie e-maila w momencie uruchomienia przypomnienia ............................................ 348
Tworzenie indywidualnych kategorii kolorów ....................................................................... 350
Oznaczanie kolorem wiadomości od konkretnych nadawców ............................................... 352
Spis treści
9
Pobieranie poczty z jednego konta przez wiele komputerów ................................................353
Tworzenie skrótu do e-maila dla odbiorcy .............................................................................354
Wysyłanie odpowiedzi na inny adres .....................................................................................356
Korzystanie z innego portu poczty wychodzącej (SMTP) ........................................................357
Kontrola nad zapisywaniem wiadomości w folderze Wysłane elementy ...............................358
Zabezpieczenie folderu hasłem ..............................................................................................361
Zmniejszanie skrzynki odbiorczej do zadanego rozmiaru ......................................................364
Co dalej? .................................................................................................................................365
13 Kalendarz i kontakty ...........................................................................367
Otwarcie Outlooka z aktywnym folderem Kalendarz .............................................................368
Zmiana liczby dni, jakie wyświetla kalendarz ........................................................................369
Dostosowywanie skali czasu w widoku dnia ..........................................................................371
Dodawanie innej strefy czasowej do widoków dnia i tygodnia ..............................................372
Dostosowywanie świąt w kalendarzu ....................................................................................374
Drukowanie pustego kalendarza ............................................................................................376
Daty i czasy wyrażane w naturalnym języku ..........................................................................377
Dostosowywanie paska zadań do wykonania ........................................................................379
Automatyczne kodowanie terminów za pomocą koloru ........................................................381
Edycja wielu kontaktów jednocześnie ....................................................................................382
Telefonowanie do osób z listy kontaktów ..............................................................................383
Dodawanie zdjęcia do kontaktu .............................................................................................386
Wyświetlanie mapy dla adresu kontaktu ...............................................................................387
Drukowanie listy telefonów na podstawie listy kontaktów ...................................................388
Współdzielenie folderu z innymi użytkownikami ..................................................................389
Współdzielenie folderu przez e-mail ..............................................................................389
Co dalej ..................................................................................................................................391
V MICROSOFT ACCESS
14 Tabele i kwerendy ..............................................................................395
Zatwierdzanie wpisywanych danych .....................................................................................395
Zastosowanie maski wprowadzania w celu zapewnienia spójności wpisywanych danych ....399
Ustawianie wartości domyślnej dla pola ................................................................................403
Wartość wymagana dla pola ..................................................................................................404
Upewnianie się, że w polu nie wyświetlają się wartości zduplikowane .................................405
Wybór wartości pola z listy ....................................................................................................407
10
Microsoft Office 2007 PL. Rady i wskazówki. Rozwiązania w biznesie
Ustawianie kursora na końcu pola ......................................................................................... 409
Wstawianie hiperłączy do tabeli ............................................................................................ 410
Importowanie danych z foldera Outlooka .............................................................................. 412
Zbieranie danych Accessa poprzez e-mail .............................................................................. 415
Tworzenie wiadomości ADC ........................................................................................... 416
Praca z odpowiedziami ADC ........................................................................................... 417
Znajdowanie zduplikowanych rekordów za pomocą kwerendy ............................................ 418
Zaznaczanie górnych wartości za pomocą kwerendy ............................................................ 419
Dodawanie niestandardowej kolumny obliczeń do kwerendy .............................................. 420
Obliczanie wartości magazynowej ......................................................................................... 422
Obliczanie sumy zamówienia ................................................................................................ 424
Pytanie o kryteria kwerendy .................................................................................................. 427
Co dalej? ................................................................................................................................ 430
15 Formularze i raporty ...........................................................................431
Automatyczne otwieranie formularzy po załadowaniu bazy danych .................................... 431
Automatyczne przechodzenie do ostatniego rekordu podczas otwierania formularza ......... 433
Tworzenie formularza panelu przełączania ........................................................................... 434
Wykorzystanie przycisku przełącznika dla pola Tak/Nie ........................................................ 435
Zapobieganie zmianom w źródle danych formularza ............................................................ 437
Kolejność przechodzenia w polach formularza ...................................................................... 438
Tworzenie klawiszy skrótu dla formantów w formularzu ...................................................... 439
Formatowanie specjalnych wartości w formularzach ............................................................ 440
Tworzenie formantów formularza dokonujących obliczeń .................................................... 442
Tworzenie grup raportujących ............................................................................................... 444
Wstawianie numerów stron .................................................................................................. 448
Wstawianie aktualnej daty .................................................................................................... 449
Wstawianie podziału strony po sekcjach raportu .................................................................. 449
Zapobieganie występowaniu osamotnionych rekordów raportu .......................................... 450
Rozpoczynanie sekcji raportu na górze wiersza lub kolumny ................................................ 451
Tworzenie raportu z wieloma kolumnami ............................................................................. 452
Tworzenie etykiet adresowych .............................................................................................. 455
Dystrybucja migawki raportu ................................................................................................ 458
Co dalej? ................................................................................................................................ 460
Spis treści
11
VI DODATKI
A Praca z makrami VBA ..........................................................................463
Aktywacja zakładki Deweloper ..............................................................................................464
Uruchamianie makra VBA ......................................................................................................464
Wykorzystanie kodu przykładu .......................................................................................465
Wykorzystanie listy z nazwami makr ..............................................................................465
Przypisywanie skrótów klawiaturowych do makr Worda ...............................................466
Przypisywanie skrótów klawiaturowych do makr Excela ................................................467
Stworzenie przycisku makra na pasku szybkiego dostępu ..............................................468
Zastosowanie makra funkcjonalnego .............................................................................470
Nagrywanie makra VBA .........................................................................................................471
Nagrywanie makra w Wordzie ........................................................................................471
Nagrywanie makra w Excelu ...........................................................................................473
Praca z edytorem VBE ............................................................................................................475
Tworzenie modułu ..........................................................................................................475
Otwieranie modułu .........................................................................................................476
Uruchamianie makropolecenia z edytora VBE ................................................................477
B Skróty klawiaturowe w Office 2007 ......................................................479
Ogólne skróty klawiaturowe Office 2007 ...............................................................................480
Skróty klawiaturowe Worda 2007 ..........................................................................................481
Skróty klawiaturowe Excela 2007 ..........................................................................................487
Skróty klawiaturowe PowerPointa 2007 ................................................................................491
Skróty klawiaturowe Outlooka 2007 ......................................................................................494
Skróty klawiaturowe Accessa 2007 ........................................................................................498
SKOROWIDZ ....................................................................................501
Formuły i funkcje
Chociaż arkuszy Excela można użyć do zwykłego
przechowywania danych, to dużo częstsza jest sytu-
acja, kiedy to dane są wykorzystywane przez formuły
Excela, które po wykonaniu obliczeń zwracają wyniki
działań. Tworzenie formuł jest zresztą istotą Excela.
Za pomocą zaledwie kilku podstawowych działań
program może dokonać zadziwiających rzeczy. Jeśli
jednak chcesz w maksymalnym stopniu wykorzy-
stać możliwości Excela, musisz wyjść poza podstawy
i nauczyć się bardziej zaawansowanych technik, zwią-
zanych ze stosowaniem zarówno formuł, jak i funkcji
arkusza. Istnieje sporo sprytnych rozwiązań, które —
jak się niebawem przekonasz — nie są trudne do
opanowania. Mogą okazać się bardzo przydatnymi
narzędziami w pracy każdego użytkownika.
Komórki przyjmujące
tylko określone wartości
Błędy związane z wpisywaniem danych są zmorą każ-
dego użytkownika Excela, któremu zależy na dokład-
ności. Nawet jeśli formuły są perfekcyjnie napisane,
to jeśli dane zawierają błędy, obliczenia przeprowa-
dzane w arkuszu nie będą wiele warte. Programiści
mają powiedzonko opisujące to zjawisko, które po
angielsku brzmi GIGO (od Garbage in, garbage out,
co w wolnym tłumaczeniu oznacza, że jakość wyniku
jest funkcją jakości składników). Załóżmy, że stwo-
rzyłeś arkusz, który będzie wykorzystywany przez
innych użytkowników, albo że współdzielisz swoje
pliki z innymi. W jaki sposób możesz zredukować
(lub nawet wyeliminować) ryzyko wprowadzenia
złych danych i popsucia dobrze funkcjonującego
5
W T Y M R O Z D Z I A L E :
Komórki przyjmujące
tylko określone wartości ...........................127
Wyszukiwanie wartości rabatu w tabeli .....129
Wyszukiwanie kodu klienta ......................131
Generowanie kodów klienta .....................132
Generowanie liczb losowych
w celu przetestowania modelu arkusza .....135
Usuwanie zbędnych spacji z komórek ........139
Usuwanie znaków niedrukowalnych
z komórki ................................................140
Wydobywanie imion lub nazwisk z listy .....142
Arkusz wierzytelności ...............................145
Konwersja dat na format Excela ................147
Obliczanie wartości premii ........................148
Obliczanie liczby dni roboczych
pomiędzy dwoma datami .........................148
Określanie kwartału obrachunkowego,
do którego należy data .............................150
Ustalanie dziesiętnej ceny produktu ..........152
Obliczanie rat kapitałowych i odsetek ........152
Obliczanie maksymalnej
wysokości pożyczki ...................................156
Obliczanie przyszłej wartości inwestycji .....157
Decyzja: kupić czy wziąć w leasing? ............159
Co dalej? ..................................................161
128
Rozdział 5
Formuły i funkcje
modelu? Najlepszym sposobem będzie wykorzystanie funkcji sprawdzania poprawności.
Polega ona na stworzeniu jednej lub większej liczby reguł, które określają typ danych lub
zakres wartości, jakie użytkownik może wpisać do komórki. Jeśli użytkownik spróbuje wpi-
sać coś niedozwolonego, Excel wyświetli komunikat o błędzie i odrzuci wpis.
Zastosuj następującą procedurę, żeby stworzyć regułę sprawdzania poprawności, która
pozwala na wprowadzenie jedynie określonych wartości:
1. Zaznacz komórkę, dla której chcesz zastosować regułę sprawdzania poprawności.
2. Wybierz Dane/Poprawność danych. Excel wyświetli okno dialogowe Sprawdzanie
poprawności danych, które zostało pokazane na rysunku 5.1.
3. W zakładce Ustawienia otwórz listę Dozwolone, a następnie wybierz Lista.
4. W polu Źródło masz dwie możliwości:
•
Jeśli w arkuszu istnieje zestawienie dozwolonych wartości, określ albo ich zakres,
albo nazwę zakresu, w jakim zestawienie się zawiera (przed wpisaniem zakresu
lub nazwy zakresu wpisz znak równości).
• Wpisz dozwolone wartości bezpośrednio do pola Źródło i oddziel je przecinkiem,
jak pokazano to na rysunku 5.1.
Rysunek 5.1.
Okno dialogowe
Sprawdzanie poprawności
danych służy
do ustawiania reguł
sprawdzania, które będą
pozwalały na wpisanie
tylko określonych wartości
do komórki lub zakresu
komórek
5
5. Jeśli chcesz, aby użytkownik mógł za pomocą rozwijalnej listy wybierać z zakresu
dozwolonych wartości, pozostaw aktywne pole wyboru Rozwinięcia w komórce.
6. Żeby uniemożliwić pozostawienie pustych komórek, odznacz pole wyboru Ignoruj puste.
7. Wybierz zakładkę Komunikat wejściowy.
8. Jeśli chcesz, aby w momencie, gdy użytkownik zaznaczy komórkę z regułą lub zakres
z regułą, pojawiało się wyskakujące okienko, pozostaw zaznaczone pole wyboru Poka-
zuj komunikat wejściowy przy wyborze komórki. W polach Tytuł i Komunikat wejściowy
możesz wpisać wiadomość, która będzie się wtedy pojawiać. Możesz na przykład wyko-
rzystać ją do przekazania użytkownikowi komunikatu, że wartość komórki musi zostać
wybrana z rozwijalnej listy.
Wyszukiwanie wartości rabatu w tabeli
129
9. Kliknij OK, aby zastosować regułę poprawności danych.
Rysunek 5.2 pokazuje praktyczne zastosowanie reguły sprawdzania poprawności. Przy
komórce Forma wysyłki widnieje rozwijalna lista pokazująca dostępne opcje.
Rysunek 5.2.
Gdy ustawiasz regułę
sprawdzania poprawności,
Excel wyświetli listę
z możliwymi opcjami
UWAGA
Arkusz zawierający przykłady można znaleźć pod adresem ftp://ftp.helion.pl/przyklady/of27rw.zip.
5
(cid:206) Innym sposobem określenia dozwolonych wartości komórek jest kontrola okna dialogowego arkusza. Więcej na ten temat w rozdziale 6,
w podrozdziale „Stosowanie formantów do wpisywania danych”, s. 185.
Wyszukiwanie wartości rabatu w tabeli
Gdy jedna firma jest dostawcą produktu, który inna firma chce sprzedawać, to cena nabycia
tego produktu nie jest nigdy taka sama jak cena sprzedaży. Sektor detaliczny musi zarabiać
pieniądze. Rzecz jasna, najczęściej zyskiem jest różnica między ceną nabycia produktu a ceną
katalogową (lub bardzo zbliżoną). Innymi słowy, zysk to różnica między tym, co przedsię-
biorca zapłacił dostawcy, a tym, co uzyskał od klienta.
130
Rozdział 5
Formuły i funkcje
Cena, po jakiej firma kupuje wyroby od dostawcy, to zazwyczaj jakiś procent ceny detalicz-
nej, który nazywany jest rabatem. Na przykład większość wydawców sprzedaje książki do
księgarni po cenach uwzględniających rabat od cen katalogowych (sugerowanych cen sprze-
daży). Niemniej rabat ten nie jest zazwyczaj na stałe określoną wartością. Na przykład
zamówienie od 1 do 5 egzemplarzy może oznaczać rabat w wysokości 20 , a zamówienie od
6 do 25 egzemplarzy może skutkować rabatem w wysokości 40 .
Jeśli zamierzasz utworzyć arkusz dla tego typu transakcji (albo jako dostawca, albo jako
sprzedawca), to musisz sobie jakoś radzić z różnymi rabatami. W Excelu służy do tego funk-
cja wyszukiwania, która wykorzystuje daną wartość (np. liczbę kupionych książek) do
znalezienia odpowiedniego elementu w tabeli (takiej jak tabela ze zniżkami).
Najczęściej używana jest funkcja WYSZUKAJ.PIONOWO(), która w pierwszej kolumnie tabeli
poszukuje zadanej wartości (można też posłużyć się podobną funkcją WYSZUKAJ.POZIOMO,
która przeszukuje pierwszy rząd tabeli). Następnie funkcja przeszukuje kolejne kolumny,
zgodnie z dyspozycją użytkownika, i zwraca wartość, jaką w nich znalazła. Oto pełna skład-
nia funkcji WYSZUKAJ.PIONOWO():
WYSZUKAJ.PIONOWO(szukana_wartość;tablica;nr_kolumny[;kolumna])
szukana_wartość Jest to wartość, jaką chcesz znaleźć w pierwszej kolumnie tablicy.
5
Tablica
nr_kolumny
Kolumna
Możesz wpisać cyfrę, tekst lub odwołanie.
Jest to tablica, jaka zostanie przeszukana. Możesz użyć nazwy
zakresu lub odwołania do zakresu komórek.
Jeśli funkcja WYSZUKAJ.PIONOWO() znajduje szukaną wartość,
to nr_kolumny jest numerem kolumny zawierającej dane, które
mają zostać zwrócone (pierwszą kolumną, a więc kolumną wyszu-
kiwania będzie zatem 1, drugą kolumną będzie 2, itd.).
Wartość logiczna określająca, w jaki sposób Excel ma przeszu-
kiwać pierwszą kolumnę pod kątem szukanej_wartości. Jeśli
jest to:
PRAWDA, to WYSZUKAJ.PIONOWO() poszukuje dokładnego odpo-
wiednika dla szukana_wartość. Jeśli nie zostaje on odnaleziony,
to funkcja poszukuje największej wartości, która jest mniejsza od
szukana_wartość (jest to ustawienie domyślne).
FAŁSZ, to WYSZUKAJ.PIONOWO() poszukuje jedynie pierwszego
dokładnego odpowiednika dla szukana_wartość.
UWAGA
Jeśli parametr kolumna zostanie opuszczony albo ustawiony na PRAWDA, to wartości w pierwszej
kolumnie muszą być posortowane rosnąco.
Wyszukiwanie kodu klienta
131
Rysunek 5.3 pokazuje arkusz, który wykorzystuje funkcję WYSZUKAJ.PIONOWO() do okre-
ślenia, jaki rabat przysługuje klientowi przy zamówieniu określonej liczby egzemplarzy.
Rysunek 5.3.
Arkusz, który wykorzystuje
funkcję
WYSZUKAJ.PIONOWO()
do odnalezienia
przysługującego klientowi
rabatu w tabeli rabatowej
Na przykład komórka D4 wykorzystuje następującą formułę:
=WYSZUKAJ.PIONOWO(A4;$H$5:$I$11;2)
Argument kolumna został pominięty, co oznacza, że WYSZUKAJ.PIONOWO() poszukuje naj-
większej wartości, która jest równa lub mniejsza od wartości szukanej, czyli w tym przypadku
od wartości określonej w komórce A4. Komórka A4 zawiera ilość kupionych egzemplarzy
(20 sztuk), a zakres komórek $H$5:$I$11 jest tabelą rabatów. WYSZUKAJ.PIONOWO() prze-
szukuje pierwszą kolumnę w dół (H5:H11) pod kątem największej wartości, która jest
mniejsza lub równa 20. Pierwszą komórką spełniającą ten warunek jest H6 (ponieważ war-
tość w H7 równa 26 jest większa od 20). WYSZUKAJ.PIONOWO() przechodzi więc do dru-
giej kolumny tablicy (gdyż w nr_kolumny wpisaliśmy 2) i z komórki I6 pobiera wartość
(czyli 40 ).
5
Wyszukiwanie kodu klienta
Formuła opisana w poprzednim podrozdziale jest przykładem wyszukiwania w zakresie,
czyli sprawdzania, czy wartość wyszukiwana zawiera się w zakresie wartości, na przykład
od 1 do 5 lub od 6 do 25. Niemniej często spotykamy się z sytuacjami, gdy trzeba znaleźć
odpowiedniki określonych wartości, a nie zakresów. Na przykład może zaistnieć potrzeba
znalezienia numeru identyfikującego pracownika, kodu części lub numeru ISBN książki.
Są to przykłady wyszukiwań pojedynczych wartości, w których formuła musi zwrócić pre-
cyzyjną wartość.
132
Rozdział 5
Formuły i funkcje
Tablica z nazwami klientów lub ich numerami kont to dobry przykład tablicy wyszukiwa-
nia, która zawiera dyskretne wartości. W takim wypadku formułę WYSZUKAJ.PIONOWO()
(lub też, co mniej prawdopodobne, WYSZUKAJ.POZIOMO()) wykorzystać można do znale-
zienia dokładnej wartości. Przyjmijmy, że dla określonego nazwiska klienta ma zostać
znaleziony odpowiadający mu kod klienta. Rysunek 5.4 pokazuje prosty arkusz realizujący
to zadanie. W kolumnie E automatycznie wstawiany jest kod klienta wtedy, gdy użytkownik
wybierze nazwisko klienta z listy rozwijalnej w kolumnie D.
Rysunek 5.4.
Prosty arkusz do wpisywania
danych, w którym funkcja
WYSZUKAJ.PIONOWO()
została użyta do znalezienia
kodu klienta,
odpowiadającego
nazwie konta
5
Przykładowa funkcja, która obsługuje to zadanie, znajduje się w kolumnie E3:
=WYSZUKAJ.PIONOWO(D3;A3:B93;2;FAŁSZ)
Wartość z komórki D3 poszukiwana jest w liście nazwisk klientów w kolumnie A. Ponieważ
argument kolumna jest ustawiony na FAŁSZ, to WYSZUKAJ.PIONOWO() poszukuje dokładnego
odpowiednika. Jeśli go znajdzie, to zwraca odpowiadający mu kod klienta z kolumny B.
Generowanie kodów klienta
Generowanie kodów klienta to jedna z częściej stosowanych opcji. Jeśli masz ich dużą liczbę,
prawdopodobnie nie będziesz chciał tego robić ręcznie. Prostym sposobem utworzenia
kodu klienta jest po prostu wykorzystanie pierwszych n liter z imienia i nazwiska klienta
i zamiana ich na wielkie litery. Jeśli na przykład nazwisko klienta znajduje się w komórce A2,
następująca formuła wygeneruje kod klienta składający się z 5 znaków:
Generowanie kodów klienta
133
WSKAZÓWKA
Jeśli formułę znajdującą się w komórce E4 umieścisz we wszystkich komórkach kolumny E, w nowych
kolumnach pojawią się błędy #N/D. WYSZUKAJ.PIONOWO() generuje ten rodzaj błędu, gdy w
zadanym zakresie nie znajdzie dokładnego odpowiednika. Będzie tak się dziać dopóty, dopóki
odpowiadające komórki w kolumnie D pozostaną puste. Lepiej jednak, aby w takim wypadku Excel
wyświetlał łańcuch zerowy ( ). W tym celu wybierz funkcję JEŻELI.BŁĄD(), żeby przetestować,
czy poszukiwania się nie powiodą, co pokazano poniżej:
=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(D3;A3:B93;2;FALSE); )
Funkcja JEŻELI.BŁĄD() najpierw oblicza rezultat WYSZUKAJ.PIONOWO(). Jeśli nie otrzyma błędu,
wyświetli rezultat w sposób normalny; jeśli zaś otrzyma błąd, wyświetli łańcuch zerowy (ko-
mórka będzie pusta).
=LITERY.WIELKIE(LEWY(A2;5))
Funkcja LEWY() pobiera znaki na lewo od wartości w A2, a funkcja LITERY.WIELKIE()
zamienia je na wielkie litery.
Wszystko fajnie, co jednak stanie się w sytuacji, gdy posiadasz klientów, którzy mają takie
same 5 liter w nazwisku. Lub, co jeszcze bardziej skomplikowane, w pierwszych 5 znakach
występują spacje, kropki, myślniki lub zagraniczne litery? Innymi słowy, można powiedzieć,
że zależy nam na wygenerowaniu unikalnego kodu klienta, zawierającego jedynie małe i wiel-
kie litery od A do Z. W tym celu musimy skorzystać z VBA. Funkcja z listingu 5.1 generuje
właśnie taki kod klienta.
Listing 5.1. Funkcja VBA, która generuje ośmioliterowy kod klienta na bazie nazwiska klienta
5
Function GenerateAccountCode(strName As String) As String
Dim nPos As Integer Aktualna pozycja znaku w zmiennej strName
Dim strChar As String Aktualny znak w zmiennej strName
Dim strTemp As String Przejściowy kod klienta
Application.Volatile False Nie przeliczaj
Zainicjowanie zmiennych
nPos = 1
strTemp =
Przegląd znaków w nazwie klienta
Do While nPos = Len(strName)
Pobierz znak z pozycji określonej przez nPos
strChar = Mid(strName, nPos, 1)
Sprawdź wartość znaku
134
Rozdział 5
Formuły i funkcje
Select Case Asc(strChar)
Pomiędzy A i Z
Case 65 To 90
strTemp = strTemp strChar
Pomiędzy a i z
Case 97 to 122
strTemp = strTemp Ucase(strChar)
End Select
Jeśli długość tekstu w nazwie klienta wynosi
5 znaków, wyjdź z pętli
If Len(strTemp) = 5 Then Exit Do
nPos = nPos + 1
Loop
Sprawdzanie poprawności
If strTemp Then
Jeśli jak dotąd nazwa klienta jest prawidłowa, dodaj losową trzycyfrową liczbę
Randomize
GenerateAccount = strTemp Format(Rnd * 999, 000 )
Else
W przeciwnym wypadku wygeneruj błąd
GenerateAccountCode = Nie wygenerowano kodu klienta!
End If
End if
End function
5
Funkcja ta wykorzystuje nazwisko klienta jako argument w zmiennej strName. Główna pętla
Do sprawdza kolejno wszystkie znaki w zmiennej. Wewnątrz pętli każdy następny znak prze-
chowywany jest w zmiennej strChar. Struktura Select Case sprawdza wartość funkcji
Asc(strChar). Funkcja ta zwraca kod wybranego znaku, sprawdzając także, czy kod ten
znajduje się pomiędzy 65 a 90 (co oznacza, że znak jest literą z zakresu od A do Z), czy może
pomiędzy 97 a 122 (co oznacza, że znak jest literą z zakresu od a do z). Jeśli zachodzi powyższe,
to znak jest dołączany do zmiennej strTemp. Gdy strTemp osiągnie długość 5 znaków
(lub dotrzemy do końca nazwiska klienta), pętla kończy się. Wreszcie jeżeli strTemp nie
jest zerem, to funkcja wykorzystuje Randomize oraz Rnd do wygenerowania losowego trzy-
cyfrowego kodu, który jest dodawany do strTemp, a następnie zwracany jako rezultat funkcji.
Rysunek 5.5 pokazuje, jak funkcję tę wykorzystano w arkuszu.
Generowanie liczb losowych w celu przetestowania modelu arkusza
135
Rysunek 5.5.
Kody kont wygenerowane
przez funkcję
GenerateAccountCode
z listingu 5.1
WSKAZÓWKA
Po wygenerowaniu kodów dla klientów na pewno będziesz chciał, aby pozostały one na stałe. Niestety, za
każdym razem, gdy zastosujesz funkcję do nowej komórki, Excel wyliczy ją w stosunku do wszystkich ist-
niejących komórek! Powstaną nowe kody dla każdego kontrahenta. Nie o to przecież Ci chodziło. Rozwią-
zaniem jest dodanie następującej linii w górnej części kodu (jak pokazano na listingu 5.1):
Application.Volatile False
5
Jest to instrukcja dla VBA, że nie należy przeliczać funkcji, w związku z czym kody klientów nie
zmienia się.
Generowanie liczb losowych
w celu przetestowania modelu arkusza
Gdy budujesz arkusz modelowy, który analizuje dane albo przeprowadza symulację, być
może nie od razu będziesz mógł skorzystać z rzeczywistych danych. Nie jest to wielki pro-
blem, ponieważ do przetestowania modelu zawsze możesz posłużyć się jakimiś danymi
zastępczymi. Większość ludzi wpisuje przypadkowe liczby w miarę postępu prac, ale nie
zawsze jest to dobry pomysł. Jeśli model wymaga danych losowych, próba ręcznego ich
wprowadzania może prowadzić do wypaczenia rezultatów. Podobnie bywa, gdy model
wymaga danych pasujących do określonego rozkładu, na przykład rozkładu normalnego.
Nie da się ich po prostu wpisać.
136
Rozdział 5
Formuły i funkcje
Excel oferuje kilka narzędzi do automatycznego generowania liczb losowych. Najprostszym
z nich jest funkcja RAND(), która zwraca liczbę losową, większą lub równą 0 i mniejszą od 1.
Przydaje się ona do generowania losowych procentów lub losowych wartości czasu, gdyż obie
te kategorie wymagają wartości z zakresu od 0 do 1. Niemniej w większości rzeczywistych
przypadków wymagane są wartości z innych zakresów.
Na przykład przypadkowe wartości godzinowe wymagają liczb z zakresu od 0 a 23, a przy-
padkowe wartości dla groszy wymagają wartości z zakresu od 0 a 100. Jeśli musisz wygene-
rować liczby losowe większe lub równe 0 i mniejsze od n, wykorzystaj następujące polecenie:
INT(RAND()*n)
Funkcja INT() zaokrągla liczbę w dół do najbliższej liczby całkowitej (zakładam, że do swo-
jego arkusza potrzebujesz liczb całkowitych; jeśli tak nie jest, w powyższym poleceniu opuść
funkcję INT()). Na przykład poniższe polecenie generuje liczbę losową równą lub większą
od 0, a mniejszą od 24:
=INT(RAND()*24)
Oczywiście nie wszystkie zakresy liczb losowych rozpoczynają się od 0. Jeśli chcesz, aby
liczby losowe były większe lub równe liczbie m i mniejsze od liczby n, skorzystaj z polecenia:
INT(RAND()*(n-m))+m
Na przykład następująca formuła zwraca losowe wyniki większe lub równe od 40 i mniejsze
od 101:
=INT(RAND()*(101-40))+40)
Zamiast bawić się takimi formułami, Excel oferuje znacznie łatwiejszy sposób generowanie
liczb losowych z zadanego przedziału. Jest nim funkcja RANDBETWEEN(). Pozwala na ustale-
nie górnej i dolnej granicy przedziału, z którego wygenerowana zostanie liczba losowa.
Oto jej składnia:
RANDBETWEEN(dolna;górna)
dolna Najmniejsza możliwa liczba całkowita. Oznacza to, że Excel wygeneruje liczbę
losową, która jest większa lub równa wartości określonej w dolna.
górna Największa możliwa liczba całkowita. Oznacza to, że Excel wygeneruje liczbę
losową, która jest mniejsza lub równa wartości określonej w górna.
Następująca formuła zwraca losową liczbę całkowitą z przedziału od 0 do 59:
=RANDBETWEEN(0;59)
Jeśli musisz zapewnić liczbom losowym pewnego rodzaju rozkład, narzędzie Analysis ToolPak
(generator liczb pseudolosowych) wygeneruje statyczne liczby losowe w dowolnym zakre-
sie i w jednym z kilku różnych rozkładów, w zależności od potrzeb. Tabela 5.1 podsumowuje
siedem dostępnych typów rozkładu.
5
Generowanie liczb losowych w celu przetestowania modelu arkusza
137
Tabela 5.1. Dostępne rozkłady w narzędziu Generowanie liczb pseudolosowych
Rozkład
Jednostajny
Normalny
Bernoulliego
Dwumianowy
Poissona
Według wzorca
Dyskretny
Opis
Generuje z takim samym prawdopodobieństwem liczby z przedziału wartości,
jaki użytkownik zdefiniuje. Zastosowanie zakresu od 0 do 1 generuje taki sam
rozkład, jak funkcja RAND().
Generuje liczby rozkładu normalnego (krzywej dzwonowej) na podstawie
średniej i odchylenia standardowego, jakie użytkownik wprowadzi. Przydatny
do generowania próbek, na przykład wyników testu czy też wzrostu populacji.
Generuje przypadkową serię jedynek i zer, wykorzystując prawdopodobieństwo
sukcesu w pojedynczej próbie. Bardzo powszechnym przykładem rozkładu
Bernoulliego jest rzut monetą (w którym prawdopodobieństwo sukcesu wynosi
50 ). W tym wypadku, podobnie jak we wszystkich rozkładach Bernoulliego,
musisz przepisywać orłom i reszkom zera albo jedynki.
Generuje liczby losowe charakteryzujące się prawdopodobieństwem sukcesu
w serii prób. Na przykład mógłbyś wykorzystać ten typ rozkładu, aby modelować
liczbę odpowiedzi otrzymanych z kampanii wysyłkowej. Prawdopodobieństwem
sukcesu byłby średni (albo przewidywany) odsetek odpowiedzi, a liczbą prób
— liczba wysłanych przesyłek.
Generuje liczby losowe, wykorzystując prawdopodobieństwo przypisanej liczby
zdarzeń w danym czasie. Rozkład zależy od wartości lambda, która reprezentuje
średnią liczbę zdarzeń, o których wiadomo, że się wydarzą w danym okresie.
Generuje liczby losowe zgodnie ze wzorcem, który określony jest przez dolny
i górny próg, wartość krokową oraz częstotliwość powtórzeń zarówno dla każdej
liczby, jak i dla całej sekwencji.
Generuje liczby losowe z serii wartości i prawdopodobieństw dla tych wartości
(gdzie suma prawdopodobieństw równa się 1). Można wykorzystać ten rozkład
do symulacji rzutów kostką (gdzie wartości zawierają się w przedziale od 1 do 6,
a każda ma prawdopodobieństwo wystąpienia 1/6; patrz następny przykład).
5
OSTRZEŻENIE
RAND() oraz RANDBETWEEN()są funkcjami zmiennymi, co oznacza, że ich wartość zmienia się za
każdym razem, gdy otwierasz lub przeliczasz arkusz albo edytujesz dowolną jego komórkę. Żeby
wpisać statyczną liczbę losową do komórki, wpisz =RAND() lub =RANDBETWEEN(dolna;
(cid:180)górna), naciśnij F9, żeby wykonać funkcję i otrzymać liczbę losową, a następnie naciśnij Enter,
żeby umieścić ową liczbę w komórce jako wartość dosłowną.
Wykonaj następujące działania, aby skorzystać z narzędzia generującego liczby pseudolosowe.
138
Rozdział 5
Formuły i funkcje
UWAGA
Być może będziesz musiał zainstalować narzędzie Analysis ToolPak. Wybierz Przycisk pakietu Office/
Opcje programu Excel, żeby otworzyć okno dialogowe Opcje programu Excel, po czym kliknij Dodatki.
Z listy Zarządzaj: wybierz Dodatki programu Excel. Kliknij Przejdź..., żeby otworzyć okno dialogowe
Dodatki. Aktywuj pole wyboru Analysis ToolPak, po czym wybierz OK. Gdy Excel zapyta, czy chcesz
zainstalować dodatek, kliknij Tak.
UWAGA
Jeśli zamierzasz korzystać z rozkładu dyskretnego, koniecznie wpisz właściwe wartości i prawdopo-
dobieństwa, zanim uruchomisz generator liczb pseudolosowych.
1. Wybierz Dane/Analiza danych, aby wyświetlić okno dialogowe Analiza danych.
2. Z listy Narzędzia analizy: wybierz Generowanie liczb pseudolosowych, po czym klik-
nij OK. Pojawi się okno dialogowe Generowanie liczb pseudolosowych pokazane na
rysunku 5.6.
Rysunek 5.6.
Okno dialogowe
Generowanie liczb
pseudolosowych
umożliwia wybór opcji
dla liczb losowych
5
3. Jeśli chcesz wygenerować więcej niż jeden zestaw liczb losowych, wpisz ich liczbę
w polu Liczba zmiennych. Excel umieści każdy zestaw w osobnej kolumnie. Jeśli pozo-
stawisz to pole puste, Excel wypełni Zakres wyjściowy.
4. W polu tekstowym Liczba wartości wpisz, ile liczb losowych potrzebujesz. Excel wpi-
sze każdą liczbę w osobnym wierszu. Jeśli pozostawisz to okno puste, program wypełni
Zakres wyjściowy.
5. Z listy rozwijalnej Rozkład: wybierz rodzaj rozkładu.
6. W grupie Parametry wpisz parametry dla wybranego rozkładu. Opcje, jakie będą
widoczne, zależą od wybranego typu rozkładu.
Usuwanie zbędnych spacji z komórek
139
7. Parametr Rozrzut: to wartość, jakiej Excel używa do wygenerowania liczb losowych.
Jeśli pozostawisz to pole puste, to za każdym razem Excel wygeneruje nowy zestaw. Jeśli
wpiszesz wartość, która musi być liczbą całkowitą z zakresu od 1 do 32 767, możesz
wykorzystać ją później, aby jeszcze raz skorzystać z tego samego zestawu liczb.
8. Wykorzystaj grupę Opcje wyjścia, aby wybrać lokalizację dla danych wyjściowych.
9. Kliknij OK. Excel dokona kalkulacji liczb losowych i wyświetli je w arkuszu.
Usuwanie zbędnych spacji z komórek
Dane, jakie wykorzystujesz w Excelu, czasami pochodzą z zewnętrznych źródeł, takich jak
bazy danych komputerów centralnych, strony internetowe czy pliki tekstowe. Niestety często
zawierają one niepożądane znaki, z których najpowszechniejszymi są niepotrzebne spacje.
Na szczęście w Excelu istnieje funkcja USUŃ.ZBĘDNE.ODSTĘPY(), która usuwa niepotrzebne
spacje z komórek. Oto przykładowa składnia:
USUŃ.ZBĘDNE.ODSTĘPY(tekst)
tekst Łańcuch, z którego chcesz usunąć niepotrzebne spacje.
Funkcja USUŃ.ZBĘDNE.ODSTĘPY()usuwa z łańcucha tekstowego:
(cid:81) wszystkie spacje przed tekstem,
(cid:81) wszystkie spacje po tekście,
(cid:81) wszystkie co najmniej dwie następujące po sobie spacje wewnątrz tekstu, które każdo-
razowo zostają zamienione na spację pojedynczą.
Jeśli na przykład komórka A1 zawiera nazwę lub tekst z nadmiarem spacji, to następująca
formuła zwróci tekst z usuniętymi spacjami:
=USUŃ.ZBĘDNE.ODSTĘPY(A1)
Trochę inna sytuacja ma miejsce wtedy, gdy chcesz usunąć wszystkie spacje z komórki.
W niektórych kontekstach spacje nie są dopuszczalne, na przykład niektóre serwery w sieci
nie pozwalają na stosowanie spacji w nazwach plików wykorzystywanych na stronach inter-
netowych. W takim wypadku możesz wykorzystać funkcję PODSTAW():
PODSTAW(tekst;stary_tekst;nowy_tekst[;wystąpienie_liczba])
5
tekst
Tekst oryginalny, który zawiera ciąg znaków, jaki chcesz
zamienić.
Tekst, który chcesz zastąpić.
Tekst, którym chcesz zastąpić stary tekst.
stary_tekst
nowy_tekst
wystąpienie_liczba Liczba zastąpień, jakie mają zostać dokonane w tekście
(domyślnie: wszystkie wystąpienia).
140
Rozdział 5
Formuły i funkcje
Jeśli za pomocą funkcji PODSTAW()chcesz usunąć spacje z komórki, zastosuj spację jako
argument stary_tekst, a argument nowy_tekst pozostaw pusty. Oto przykład formuły,
która usuwa wszelkie spacje z tekstu w komórce A1:
=PODSTAW(A1; ; )
WSKAZÓWKA
Podobną technikę możesz zastosować do usunięcia dowolnego znaku z komórki. Oto uniwersalna
formuła:
=PODSTAW(tekst;znak; )
Należy tutaj zastąpić tekst oryginalnym łańcuchem, a w miejsce zmiennej znak wpisać znak,
jaki chcesz usunąć.
Gdy zastosujesz tę formułę do danych zaimportowanych ze strony
Pobierz darmowy fragment (pdf)