Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00151 004700 20272082 na godz. na dobę w sumie
wyszukaj dokładnie | pobierz wtyczkę
Excel. Wykresy, analiza danych, tabele przestawne. Niebieski podręcznik - ebook/pdf
Excel. Wykresy, analiza danych, tabele przestawne. Niebieski podręcznik - ebook/pdf
Autor: Liczba stron: 328
Wydawca: Helion Język publikacji: polski
ISBN: 978-83-283-0685-1 Data wydania:
Lektor:
Kategoria: ebooki >> komputery i informatyka >> aplikacje biurowe >> vba - programowanie
Porównaj ceny (książka, ebook (-50%), audiobook).
Czytaj online.
Komentuj

Zaawansowana wiedza w zasięgu ręki!

Excel to narzędzie należące do pakietu MS Office, charakteryzujące się nieskończoną cierpliwością w wykonywaniu najbardziej mozolnych i skomplikowanych obliczeń. Rozwijany od wielu lat, zdobył ogromną popularność i obecnie jest najbardziej znanym arkuszem kalkulacyjnym. Jego potencjał doceniają inżynierowie, księgowi, analitycy i studenci. Program znajduje zastosowanie wszędzie tam, gdzie są przechowywane i poddawane analizie ogromne zbiory danych. Świetnie sprawdza się też w roli kontrolera budżetu domowego. Chcesz poznać inne sposoby jego wykorzystania? Pomysłowość użytkowników nie zna granic!

Ta książka pozwoli Ci dogłębnie poznać najbardziej zaawansowane funkcje Excela. Tworzenie skomplikowanych wykresów i tabel przestawnych już nigdy nie będzie dla Ciebie problemem. Dowiesz się z niej, jak tworzyć formuły na potrzeby analizy danych oraz jak ominąć typowe trudności. Nauczysz się też analizować dane finansowe oraz statystyczne. Po zdobyciu tych umiejętności przejdziesz do zagadnień związanych z filtrowaniem danych oraz ich sortowaniem. Na sam koniec zapoznasz się z różnymi technikami analizy, poznasz narzędzie Solver oraz dodatek Analysis ToolPak. Zobaczysz także, jak zaimportować dane z innych źródeł lub skorzystać z kwerend bazodanowych. Książka ta jest doskonałym źródłem wiedzy dla wszystkich użytkowników Excela, którzy chcą wykorzystać w pełni jego możliwości.

W książce znajdziesz ponad 150 technik analizy danych w Excelu. Oto wybrane z tych metod:

Najlepsze techniki analizy danych podane na tacy!

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

Darmowy fragment publikacji:

Tytuł oryginału: Excel® Data Analysis: Your visual blueprint™ for analyzing data, charts, and PivotTables, 4th Edition Tłumaczenie: Tomasz Walczak z wykorzystaniem fragmentów książki „Microsoft Excel 2010 PL. Formuły i funkcje. Akademia Excela” w tłumaczeniu Ireneusza Jakóbika ISBN: 978-83-283-0682-0 Copyright © 2013 by John Wiley Sons, Inc., Indianapolis, Indiana All Rights Reserved. This translation published under license with the original publisher John Wiley Sons, Inc. Translation copyright © 2015 by Helion S.A. 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 either the prior written permission of the Publisher. The Teach Yourself Visually Brand trade dress is a trademark of John Wiley Sons, Inc. in the United Stated and/or other countries. Used by permission. Wiley, the Wiley logo, Visual, the Visual logo, Visual Blueprint, Read Less - Learn More and related trade dress are trademarks or registered trademarks of John Wiley Sons, Inc. and/or its affiliates. Excel is a registered trademark of Microsoft Corporation in the United States and/ or other countries. 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 irzetelne. 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) Pliki z przykładami omawianymi w książce można znaleźć pod adresem: ftp://ftp.helion.pl/przyklady/excnie.zip Drogi Czytelniku! Jeżeli chcesz ocenić tę książkę, zajrzyj pod adres http://helion.pl/user/opinie/excnie 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ść 1. Tworzenie formuł na potrzeby analizy danych ................... 2 Wprowadzenie do analizy danych ....................................................................................................... 2 Wprowadzenie do formuł ....................................................................................................................... 4 Rodzaje formuł .......................................................................................................................................... 6 Tworzenie formuł...................................................................................................................................... 8 Dodawanie nazw zakresów do formuł .............................................................................................10 Wskazywanie w formułach zakresów z innych arkuszy .............................................................12 Przenoszenie lub kopiowanie formuł ................................................................................................14 Stosowanie bezwzględnych odwołań do komórek ........................................................................ 16 Tworzenie formuł tablicowych ...........................................................................................................18 Uruchamianie obliczeń iteracyjnych .................................................................................................20 2. Rozwiązywanie problemów z formułami ..........................22 Wartości błędów w Excelu ...................................................................................................................22 Wyświetlanie formuł zamiast wyników ..........................................................................................24 Używanie okna czujki do śledzenia wartości komórki ................................................................26 Przechodzenie przez formułę krok po kroku ................................................................................28 Wyświetlanie tekstu zamiast wartości błędów ..............................................................................30 Sprawdzanie błędów w formułach w arkuszu ...............................................................................32 Dokonywanie inspekcji formuły w celu zlokalizowania błędów ..............................................34 3. Wzbogacanie formuł za pomocą funkcji ............................36 Funkcje Excela .........................................................................................................................................36 Typy funkcji ..............................................................................................................................................38 Dodawanie funkcji do formuły ...........................................................................................................40 Dodawanie liczb z wiersza lub kolumny ........................................................................................42 Tworzenie formuły z autosumowaniem ...........................................................................................44 Zaokrąglanie wartości ............................................................................................................................46 Tworzenie formuł warunkowych ........................................................................................................48 Warunkowe obliczanie sumy ................................................................................................................50 Warunkowe zliczanie elementów ........................................................................................................51 Obliczanie pierwiastka kwadratowego .............................................................................................52 Pobieranie numeru kolumny lub wiersza ........................................................................................53 vi EXCNIE_ksiazka.indb 6 2015-05-14 11:25:24 Spis treściPoleć książkęKup książkę Wyszukiwanie wartości ........................................................................................................................54 Określanie lokalizacji wartości ...........................................................................................................56 Zwracanie wartości komórki za pomocą funkcji INDEKS .......................................................57 Wykonywanie obliczeń na datach i czasie .......................................................................................58 4. Analizowanie danych finansowych .................................. 60 Obliczanie przyszłej wartości .............................................................................................................60 Obliczanie wartości bieżącej ...............................................................................................................62 Określanie wysokości rat pożyczki ....................................................................................................64 Obliczanie części kapitałowej i odsetkowej ....................................................................................66 Obliczanie dopuszczalnego oprocentowania ...................................................................................68 Określanie wewnętrznej stopy zwrotu .............................................................................................70 Obliczanie amortyzacji metodą liniową ...........................................................................................72 Obliczanie amortyzacji metodą równomiernie malejącego salda ............................................74 Obliczanie amortyzacji metodą DDB ..............................................................................................76 Obliczanie amortyzacji metodą sumy cyfr rocznych ...................................................................78 5. Analizowanie danych statystycznych .............................. 80 Obliczanie średniej .................................................................................................................................80 Warunkowe obliczanie średniej ...........................................................................................................81 Wyznaczanie mediany lub wartości modalnej ...............................................................................82 Wyznaczanie pozycji .............................................................................................................................84 Określanie n-tej największej lub najmniejszej wartości .............................................................86 Tworzenie rozkładów częstości dla przedziałów ..........................................................................88 Obliczanie wariancji i odchylenia standardowego ........................................................................90 Obliczanie korelacji ................................................................................................................................92 6. Tworzenie tabel na potrzeby analizy danych .....................94 Tabele ..........................................................................................................................................................94 Wprowadzenie do funkcjonalności tabel .........................................................................................95 Przekształcanie zakresu w tabelę .......................................................................................................96 Zaznaczanie danych w tabeli ...............................................................................................................98 Wstawianie wiersza do tabeli ........................................................................................................... 100 Wstawianie kolumny do tabeli ......................................................................................................... 101 Usuwanie wiersza tabeli .................................................................................................................... 102 EXCNIE_ksiazka.indb 7 vii 2015-05-14 11:25:24 Poleć książkęKup książkę Usuwanie kolumny tabeli .................................................................................................................. 103 Wyznaczanie sum częściowych dla kolumn ................................................................................ 104 7. Sortowanie i filtrowanie danych ................................... 106 Proste sortowanie lub filtrowanie .................................................................................................... 106 Sortowanie wielopoziomowe ............................................................................................................ 108 Sortowanie niestandardowe ...............................................................................................................110 Sortowanie według koloru komórki, koloru czcionki lub ikony komórki .............................111 Stosowanie filtrów szybkich przy złożonym sortowaniu .........................................................112 Wprowadzanie kryteriów wyszukiwania rekordów ...................................................................114 Tworzenie filtrów zaawansowanych ................................................................................................116 Wyświetlanie unikatowych rekordów w wynikach filtrowania ..............................................118 Zliczanie przefiltrowanych rekordów ............................................................................................ 120 8. Techniki analizowania danych .......................................122 Wyróżnianie komórek spełniających określone kryteria ......................................................... 122 Wyróżnianie największej lub najmniejszej wartości z przedziału ....................................... 124 Wyświetlanie powtarzających się wartości .................................................................................. 126 Wyświetlanie komórek o wartościach powyżej lub poniżej średniej ................................... 127 Analizowanie wartości komórek za pomocą pasków danych ................................................. 128 Analizowanie wartości komórek za pomocą skali kolorów.......................................................130 Analizowanie wartości komórek za pomocą zestawów ikon ....................................................132 Tworzenie niestandardowych reguł formatowania warunkowego .........................................134 Wyróżnianie komórek na podstawie formuły ...............................................................................136 Modyfikowanie reguły formatowania warunkowego ..................................................................138 Usuwanie formatowania warunkowego z zakresu ...................................................................... 140 Usuwanie formatowania warunkowego z arkusza ...................................................................... 141 Tworzenie reguł sprawdzania poprawności danych ................................................................. 142 Podsumowywanie danych za pomocą sum częściowych ......................................................... 144 Grupowanie powiązanych danych .................................................................................................. 146 Usuwanie powtarzających się wartości z zakresu lub tabeli................................................... 148 Konsolidacja danych z różnych arkuszy ....................................................................................... 150 viii EXCNIE_ksiazka.indb 8 2015-05-14 11:25:25 Spis treściPoleć książkęKup książkę 9. Narzędzia do analizowania danych ................................154 Tworzenie tabeli danych .................................................................................................................... 154 Konfigurowanie tabeli danych z dwoma wartościami wejściowymi .................................... 156 Pomijanie tabel danych przy obliczeniach w skoroszycie ....................................................... 158 Analizowanie danych z wykorzystaniem funkcji szukania wyniku ......................................160 Analizowanie danych za pomocą scenariuszy ..............................................................................162 Dodatek Solver .......................................................................................................................................166 Wczytywanie dodatku Solver ............................................................................................................168 Optymalizowanie wyniku za pomocą Solvera............................................................................. 170 Dodawanie warunków ograniczających dla Solvera .................................................................. 172 Zapisywanie rozwiązania jako scenariusza .................................................................................. 174 10. Śledzenie trendów i dokonywanie prognoz ......................176 Kreślenie linii trendu .......................................................................................................................... 176 Obliczanie wartości najlepszego dopasowania ............................................................................ 178 Nanoszenie prognozowanych wartości na wykres ......................................................................180 Przedłużanie trendu liniowego..........................................................................................................182 Obliczanie prognozowanych wartości trendu liniowego ...........................................................184 Kreślenie linii trendu wykładniczego .............................................................................................186 Obliczanie wartości w trendzie wykładniczym ...........................................................................188 Kreślenie linii trendu logarytmicznego ..........................................................................................190 Kreślenie linii trendu potęgowego ...................................................................................................192 Kreślenie linii trendu wielomianowego ..........................................................................................194 11. Korzystanie z dodatku Analysis ToolPak ........................196 Wczytywanie dodatku Analysis ToolPak .......................................................................................196 Obliczanie średniej ruchomej ............................................................................................................198 Porównywanie wariancji .................................................................................................................... 200 Obliczanie korelacji ............................................................................................................................. 202 Przeprowadzanie analiz regresji ...................................................................................................... 204 Określanie rangi i percentyla ............................................................................................................ 206 Obliczanie statystyk opisowych ...................................................................................................... 208 Generowanie liczb losowych ............................................................................................................ 210 Tworzenie rozkładu częstości .......................................................................................................... 212 EXCNIE_ksiazka.indb 9 ix 2015-05-14 11:25:25 Poleć książkęKup książkę 12. Analizowanie danych za pomocą tabel przestawnych ........214 Wprowadzenie do tabel przestawnych .......................................................................................... 214 Przegląd możliwości tabel przestawnych ..................................................................................... 215 Tworzenie tabeli przestawnej na podstawie tabeli lub zakresu ..............................................216 Tworzenie tabeli przestawnej na podstawie danych zewnętrznych ......................................218 Odświeżanie danych w tabeli przestawnej .................................................................................. 222 Dodawanie wielu pól do obszaru wierszy lub kolumn ............................................................. 224 Dodawanie wielu pól do obszaru danych ..................................................................................... 226 Przenoszenie pola do innego obszaru ............................................................................................ 228 Grupowanie wartości w tabelach przestawnych ......................................................................... 230 Modyfikowanie podsumowań w tabeli przestawnej .................................................................. 232 Obliczenia niestandardowe ............................................................................................................... 234 Dodawanie niestandardowego pola obliczeniowego .................................................................. 236 Dodawanie niestandardowego elementu obliczeniowego ........................................................ 238 13. Wizualizowanie danych za pomocą wykresów ................ 240 Elementy wykresów ............................................................................................................................ 240 Typy wykresów ..................................................................................................................................... 241 Tworzenie wykresu ............................................................................................................................. 242 Wyświetlanie tabeli danych .............................................................................................................. 244 Zmienianie układu i stylu wykresu ................................................................................................ 245 Wybieranie wykresu innego typu ................................................................................................... 246 Dodawanie wykresu przebiegu w czasie do komórki ............................................................... 248 14. Importowanie danych do Excela ................................... 250 Dane zewnętrzne .................................................................................................................................. 250 Importowanie informacji ze źródła danych ................................................................................. 252 Importowanie danych z tabeli Accessa .......................................................................................... 254 Importowanie danych z tabeli edytora Word ............................................................................... 256 Importowanie danych z pliku tekstowego ..................................................................................... 258 Importowanie danych ze stron WWW .......................................................................................... 262 Importowanie danych z pliku XML ............................................................................................... 264 Odświeżanie zaimportowanych danych ........................................................................................ 266 Rozdzielanie tekstu z komórek na kolumny ................................................................................ 268 x EXCNIE_ksiazka.indb 10 2015-05-14 11:25:25 Spis treściPoleć książkęKup książkę 15. Kwerendy źródeł danych ............................................ 270 Wprowadzenie do programu Microsoft Query ........................................................................... 270 Definiowanie źródła danych ............................................................................................................. 272 Uruchamianie programu Microsoft Query .................................................................................. 276 Przegląd okna Microsoft Query ...................................................................................................... 277 Dodawanie tabel do kwerend ........................................................................................................... 278 Dodawanie pól do kwerend ............................................................................................................... 280 Filtrowanie rekordów za pomocą kryteriów kwerendy............................................................. 282 Sortowanie rekordów kwerendy ...................................................................................................... 284 Zwracanie wyników kwerend .......................................................................................................... 286 16. Używanie języka VBA do analizowania danych ................288 Rejestrowanie makr ............................................................................................................................. 288 Otwieranie edytora kodu VBA ........................................................................................................ 290 Model obiektowy Excela .................................................................................................................... 292 Dodawanie makra do modułu .......................................................................................................... 294 Uruchamianie makra .......................................................................................................................... 296 Przypisywanie klawisza skrótu do makr....................................................................................... 298 Przypisywanie makra do paska szybkiego dostępu ................................................................... 300 Przypisywanie makr do wstążki ...................................................................................................... 302 Ustawianie poziomu bezpieczeństwa makr ................................................................................. 304 Cyfrowe podpisywanie makr Excela .............................................................................................. 306 Dodatek A. Używanie skrótów klawiaturowych Excela .......... 308 Skorowidz ......................................................................314 EXCNIE_ksiazka.indb 1 1 2015-05-14 11:25:25 Poleć książkęKup książkę Wprowadzenie   do tabel przestawnych T abele i zewnętrzne bazy danych mogą zawierać ty- siące rekordów. Analizowanie takich ilości danych bez odpowiednich narzędzi może być prawdziwym koszmarem. Właśnie w takich sytuacjach będzie pomoc- ny Excel ze swoim potężnym narzędziem do analizy danych o nazwie tabela przestawna. Umożliwia ona pod- sumowanie setek rekordów w zwięzłym, tabelarycznym formacie, a następnie manipulowanie układem tabeli Grupowanie (przestawianie jej) w celu uzyskania spojrzeń na dane z in- nej perspektywy. Tabele przestawne pomagają analizować duże ilości da- nych za pomocą trzech operacji: grupowania danych we- dług kategorii, podsumowywania danych z wykorzysta- niem obliczeń i filtrowania danych w celu wyświetlenia tylko potrzebnych rekordów. Tabele przestawne są przydatnym narzędziem do analizowania danych po części dlatego, że automatycznie grupują duże zbiory danych w mniejsze, łatwiejsze w użyciu kategorie. Załóżmy, że masz źródło danych z polem Region, w którym każda komórka zawiera jedną z czterech wartości: Wschód, Zachód, Północ i Południe. Pierwotne dane mogą obejmować tysiące rekordów, jeśli jednak tworzysz tabelę przestawną z polem Region, w wynikowej tabeli mogą wystę- pować tylko cztery wiersze — po jednym dla każdej z czterech unikatowych wartości tego pola. Ponadto można tworzyć własne sposoby grupowania po utworzeniu tabeli przestawnej. Jeśli dane obejmują pole Kraj, możesz w tabeli przestawnej pogrupować wszystkie rekordy o tej samej wartości tego pola. Potem można dodatkowo po- grupować elementy z poszczególnych krajów według kontynentów: Ameryka Północna, Ameryka Południowa, Europa itd. Podsumowywanie Oprócz grupowania danych na podstawie unikatowych wartości jednego lub kilku pól Excel wyświetla ponadto podsumo- wania dla poszczególnych grup. Domyślnie obliczana jest suma (Excel dodaje do siebie wszystkie wartości z danego pola). Na przykład jeśli w danych występują pola Region i Sprzedaż, w tabeli przestawnej można pogrupować wartości we- dług regionów i wyświetlić sumaryczny poziom sprzedaży dla każdego regionu. Excel udostępnia też inne podsumowania: liczbę elementów, średnią, maksimum, minimum i odchylenie standardowe. Jeszcze większe możliwości dają podsumowania dla grup z podziałem na podgrupy. Załóżmy, że w danych o sprzedaży znaj- duje się też pole Produkt. Można wtedy wyświetlić w tabeli przestawnej łączny poziom sprzedaży dla każdego produktu z podziałem na regiony. Filtrowanie Tabele przestawne umożliwiają też wyświetlanie podzbiorów danych. Na przykład przy domyślnym pogrupowaniu tabela przestawna pokazuje wszystkie unikatowe wartości z grupującego pola. Możesz tak jednak zmodyfikować tabelę, aby ukryć niepotrzebne wartości. Każda tabela przestawna udostępnia też filtr raportów, który można zastosować do całej tabeli. Załóżmy, że w danych na temat sprzedaży znajduje się pole Klient. Jeśli wykorzystasz je w filtrze raportu, możesz prze- filtrować raport i wyświetlić wyniki dla tylko jednego klienta. 214 EXCNIE_ksiazka.indb 214 2015-05-14 11:26:32 Poleć książkęKup książkę Przegląd możliwości   tabel przestawnych W arto poznać tabele przestawne, ponieważ mają one wiele zalet. Na przykład tworzenie ich i za- rządzanie nimi jest proste, a ponadto niezwykle szybko przeprowadzają rozbudowane i złożone oblicze- nia. Możesz też szybko zaktualizować tabelę przestawną i uwzględnić nowe dane. Ponieważ takie tabele są dyna- miczne, można łatwo przenosić, filtrować i dodawać kom- ponenty. Tabele przestawne są w pełni konfigurowalne, Wprowadzenie do elementów tabel przestawnych co pozwala opracować każdy raport w pożądany sposób. Dostępna jest też większość opcji formatowania używa- nych dla zwykłych zakresów Excela. Pracę z tabelami przestawnymi można zacząć bardzo szybko po zapoznaniu się z kilkoma podstawowymi za- gadnieniami. Musisz zrozumieć elementy typowej tabe- li przestawnej (zwłaszcza cztery z nich: wiersze, kolum- ny, dane i filtry), do których dodawane są pola danych. A Filtr Wyświetla listę rozwijaną z unikatowymi wartościami pola. Gdy wybierzesz wartość z tej listy, Excel przefiltruje wyniki w tabeli przestawnej i wyświetli tylko te rekordy, które pasują do zaznaczonej wartości. B Obszar kolumn Wyświetla w poziomie unikatowe wartości z wybranego pola z danych. E Etykiety wierszy Identyfikują pola z obszaru wierszy. Etykiety wierszy moż- na też zastosować do filtrowania wartości pól wyświetla- nych w obszarze wierszy. F Etykiety kolumn Identyfikują pola z obszaru kolumn. Etykiety kolumny można też zastosować do filtrowania wartości pól wy- świetlanych w obszarze kolumn. C Obszar wierszy Wyświetla w pionie unikatowe wartości z wybranego pola z danych. G Nagłówek pola danych Określa obliczenia (na przykład „suma”) i pole (na przy- kład „wartość faktury”) używane w obszarze danych. D Obszar danych Wyświetla wyniki obliczeń, które Excel przeprowadził na polu liczbowym z danych. H Elementy pól Obejmują unikatowe wartości pól dodane do danego obszaru. E H G C B A F D EXCNIE_ksiazka.indb 215 215 2015-05-14 11:26:32 Rozdział 12. Analizowanie danych za pomocą tabel przestawnychPoleć książkęKup książkę Tworzenie tabeli przestawnej   na podstawie tabeli lub zakresu J eśli analizowane dane znajdują się w zakresie lub tabeli Excela, można zastosować polecenie Tabela przestawna. Trzeba tylko wskazać lokalizację danych źródłowych, a następnie określić, gdzie ma znaleźć się wygenerowana tabela przestawna. Excel tworzy pustą tabelę przestawną w nowym arkuszu lub we wskazanym miejscu. Ponadto Excel wyświetla panel Pola tabeli przestawnej obejmujący cztery obszary: FILTRY, KOLUMNY, WIERSZE i WARTOŚCI. Aby za- kończyć tworzenie tabeli przestawnej, zapełnij niektóre lub wszystkie z tych obszarów polami danych. Tworzenie tabeli przestawnej na podstawie tabeli lub zakresu 1 Kliknij komórkę w źródle danych (może to być zakres lub tabela). 2 Otwórz zakładkę WSTAWIANIE. 3 Wybierz opcję Tabela przestawna. 2 3 1 4 A 5 Pojawi się okno dialogowe Tworzenie tabeli przestawnej. 4 Zaznacz opcję Nowy arkusz (pole zmieni się w A Jeśli chcesz umieścić tabelę ). przestawną w istniejącym arkuszu, kliknij opcję Istniejący arkusz (pole zmieni się w ), a następnie w polu Lokalizacja wybierz arkusz i komórkę, gdzie ma znaleźć się tabela przestawna. 5 Kliknij przycisk OK. 216 EXCNIE_ksiazka.indb 216 2015-05-14 11:26:32 Poleć książkęKup książkę B Excel utworzy pustą tabelę przestawną. C Excel wyświetli panel Pola tabeli przestawnej. 6 Kliknij i przeciągnij pole, a następnie upuść je w obszarze WIERSZE. B D Excel doda unikatowe wartości pola do obszaru wierszy tabeli przestawnej. C 6 7 Kliknij i przeciągnij pole liczbowe, a następnie upuść je w obszarze WARTOŚCI. E Excel zsumuje wartości liczbowe na podstawie wartości wierszy. 8 W razie potrzeby kliknij i przeciągnij pola, a następnie upuść je w obszarach KOLUMNY i FILTRY. Za każdym razem, gdy upuścisz pole w jednym z obszarów, Excel zaktua- lizuje tabelę przestawną i uwzględni w niej nowe dane. D E 8 7 Dodatkowe informacje Excel udostępnia kilka szybkich technik pomocnych przy tworzeniu tabel przestawnych. Gdy w panelu Pola tabeli przestawnej zaznaczysz pole tekstowe lub pole z datami (pole ), zostanie ono dodane do obszaru WIERSZE. Jeśli zaznaczysz pole liczbowe (pole ), Excel doda je do obszaru WARTOŚCI. Możesz też kliknąć pole prawym przyciskiem myszy, a następnie kliknąć obszar, który chcesz wybrać. Obszar FILTRY pozwala dodać do tabeli przestawnej pole filtra, które umożliwia wyświetlenie podzbioru danych na pod- stawie wartości z tego pola. W tym celu kliknij skierowaną w dół strzałkę w polu z filtrem, wybierz z listy rozwijanej element, który chcesz zastosować jako filtr, a następnie kliknij przycisk OK. zmieni się w zmieni się w EXCNIE_ksiazka.indb 217 217 2015-05-14 11:26:32 Rozdział 12. Analizowanie danych za pomocą tabel przestawnychPoleć książkęKup książkę Tworzenie tabeli przestawnej   na podstawie danych zewnętrznych Z amiast tworzyć tabelę przestawną na podstawie da- nych z arkusza Excela, można zbudować ją z wy- korzystaniem zewnętrznego źródła danych. Dzięki temu można budować raporty dla bardzo dużych zbiorów danych i relacyjnych systemów bazodanowych. Analizowane dane mogą być zapisane nie w zakresie albo tabeli Excela, ale w systemie RDBMS, takim jak Mic- rosoft Access lub SQL Server. Gdy używasz takich pro- gramów, możesz skonfigurować tabelę, zapytanie lub inny obiekt z danymi, z którymi zamierzasz pracować. Następ- nie możesz utworzyć tabelę przestawną na podstawie ta- kiego zewnętrznego źródła danych. Tworzenie tabeli przestawnej na podstawie danych zewnętrznych 1 Wciśnij kombinację Alt+D, a następnie wciśnij P. Pojawi się okno dialogowe Kreator tabel i wykresów przestawnych — krok 1 z 3. danych (pole 2 Zaznacz opcję Zewnętrzne źródło ). 3 Zaznacz opcję Tabela przestawna zmieni się w (pole zmieni się w 4 Kliknij przycisk Dalej. ). 2 3 4 Pojawi się okno dialogowe Kreator tabel i wykresów przestawnych — krok 2 z 3. 5 Kliknij przycisk Pobierz dane. 5 218 EXCNIE_ksiazka.indb 218 2015-05-14 11:26:32 Poleć książkęKup książkę Pojawi się okno dialogowe Wybierz źródło danych. 6 Kliknij wybrany typ źródła danych. 7 Kliknij przycisk OK. Pojawi się okno dialogowe Wybieranie bazy danych. 8 Kliknij katalog zawierający potrzebną bazę danych. 9 Kliknij wybraną bazę danych. 10 Kliknij przycisk OK. 9 Pojawi się okno dialogowe Kreator kwerend — wybierz kolumny. 11 Kliknij tabelę lub kolumnę, którą chcesz wykorzystać jako źródło danych dla tabeli przestawnej. 11 12 Kliknij przycisk Dodaj. A Pola wybranej tabeli pojawią się na liście. 13 Kliknij przycisk Dalej. 7 10 6 8 12 A 13 Dodatkowe informacje Aby utworzyć źródło danych, otwórz zakładkę DANE, wybierz opcję Pobieranie danych zewnętrznych, kliknij Z innych źródeł, a następnie wybierz Z programu Microsoft Query. W oknie dialogowym Wybierz źródło danych kliknij Nowe źródło danych. Usuń zaznaczenie przy polu Używaj Kreatora kwerend do tworzenia/edycji kwerend (pole ), a następnie kliknij przycisk OK. W oknie dialogowym Utwórz nowe źródło danych wprowadź nazwę źródła danych, wybierz sterownik bazy danych potrzebny dla danego źródła, a następnie kliknij przycisk OK. zmieni się w EXCNIE_ksiazka.indb 219 cdn. 219 2015-05-14 11:26:32 Rozdział 12. Analizowanie danych za pomocą tabel przestawnychPoleć książkęKup książkę Tworzenie tabeli przestawnej   na podstawie danych zewnętrznych (ciąg dalszy) G dy tworzysz tabelę przestawną na podstawie da- nych zewnętrznych, musisz wcześniej zdefiniować odpowiednie źródło danych. Nie musisz bezpo- średnio korzystać z programu Microsoft Query. Ponadto gdy budujesz tabelę przestawną opartą na danych ze- wnętrznych, możesz pominąć okna dialogowe Kreatora kwerend związane z filtrowaniem i sortowaniem danych, ponieważ w kontekście tworzenia tabeli przestawnej ope- racje te zwykle nie są istotne. Przy tworzeniu tabeli przestawnej na podstawie danych zewnętrznych nie musisz najpierw importować tych da- nych do Excela. Dane są wtedy dostępne tylko w nowej tabeli przestawnej, a nie w skoroszycie. Tworzenie tabeli przestawnej na podstawie danych zewnętrznych (ciąg dalszy) Pojawi się okno dialogowe Kreator kwerend — filtr danych. 14 Kliknij przycisk Dalej. Pojawi się okno dialogowe Kreator kwerend — kolejność sortowania. 15 Kliknij przycisk Dalej. 14 15 220 EXCNIE_ksiazka.indb 220 2015-05-14 11:26:32 Poleć książkęKup książkę Pojawi się okno dialogowe Kreator kwerend — koniec. 16 Zaznacz pole Zwróć dane do progra- zmieni się mu Microsoft Excel (pole w ). 17 Kliknij przycisk Zakończ. Excel wróci do okna dialogowego Kreator tabel i wykresów przestaw- nych — krok 2 z 3. 18 Kliknij przycisk Zakończ. Excel utworzy pustą tabelę przestawną. A Pola dostępne w tabeli lub kwerendzie wybranej w kroku 11. pojawią się w panelu Pola tabeli przestawnej. 19 Kliknij pola z panelu Pola tabeli przestawnej i przeciągnij je do odpowiednich obszarów. B Excel wyświetli w tabeli przestawnej podsumowanie zewnętrznych danych. 16 B 18 17 A 19 Dodatkowe informacje Najczęstszym problemem przy korzystaniu z zewnętrznych źródeł danych jest to, że użytkownik często nie ma kontroli nad zewnętrznym plikiem. Gdy spróbujesz na przykład odświeżyć tabelę przestawną z zewnętrznymi danymi, Excel może wyświetlić komunikat o błędzie. Jeśli podejrzewasz, że źródłem problemu jest zmiana danych używanych do logowania się do bazy, kliknij przycisk OK, aby wyświetlić okno dialogowe Logowanie, i poproś administratora bazy o nową nazwę użytkownika i nowe hasło. Źródłem problemu może być też przeniesienie lub zmiana nazwy pliku bazy danych. W takiej sytuacji kliknij przy- cisk OK w oknie z komunikatem błędu, a następnie wybierz opcję Baza danych w oknie dialogowym Logowanie. Potem za pomocą okna dialogowego Wybierz bazę danych znajdź i wskaż plik z bazą. EXCNIE_ksiazka.indb 221 221 2015-05-14 11:26:33 Rozdział 12. Analizowanie danych za pomocą tabel przestawnychPoleć książkęKup książkę Excel udostępnia dwie techniki odświeżania tabel prze- stawnych: ręczną i automatyczną. Ręczne odświeżanie zwykle przeprowadza się wtedy, gdy wiadomo, że źród- łowe dane się zmieniły, lub w celu upewnienia się, że naj- nowsze dane są uwzględnione w raporcie. Automatyczne odświeżanie Excel przeprowadza za użytkownika. 3 A 2 2 Odświeżanie danych   w tabeli przestawnej N iezależnie od tego, czy w tabeli przestawnej znajdu- ją się wyniki finansowe, odpowiedzi do ankiety lub informacje o zbiorach książek lub płyt DVD, uży- wane dane prawdopodobnie nie są statyczne. Oznacza to, że zmieniają się po pojawieniu się nowych wyników, prze- prowadzeniu kolejnych ankiet lub dodaniu nowych pozycji do zbiorów. Aby zapewnić aktualność analiz reprezento- wanych w tabeli przestawnej, należy ją odświeżyć. Odświeżanie danych w tabeli przestawnej 1 3 4 1 Ręczne odświeżanie danych 1 Kliknij dowolną komórkę w tabeli przestawnej. 2 Otwórz zakładkę ANALIZA. 3 Wybierz opcję Odśwież. Możesz też wcisnąć kombinację Alt+F5. A Aby zaktualizować wszystkie tabele przestawne w skoroszycie, kliknij skierowaną w dół strzałkę pod przyciskiem Odśwież, a następnie wybierz opcję Odśwież wszystko. W celu zaktualizowania wszystkich ta- bel przestawnych możesz też wcisnąć kombinację Ctrl+Alt+F5. Excel zaktualizuje dane w tabeli przestawnej. Automatyczne odświeżanie danych 1 Kliknij dowolną komórkę w tabeli przestawnej. 2 Otwórz zakładkę ANALIZA. 3 Kliknij przycisk Tabela przestawna. 4 Wybierz opcję Opcje. Uwaga: możesz też kliknąć dowolną komórkę tabeli przestawnej prawym przyciskiem myszy, a następnie wybrać opcję Opcje tabeli przestawnej. 222 EXCNIE_ksiazka.indb 222 2015-05-14 11:26:33 Poleć książkęKup książkę Pojawi się okno dialogowe Opcje tabeli przestawnej. 5 Otwórz zakładkę Dane. 6 Wybierz opcję Odśwież dane podczas zmieni się otwierania pliku (pole w ). 7 Kliknij przycisk OK. Excel zastosuje wybrane opcje odświeżania. 6 5 7 Zastosuj to Jeśli tabela przestawna jest oparta na danych zewnętrznych, możesz przygotować harmonogram pozwalający automatycznie odświeżać tabelę w określonych odstępach czasu. Kliknij dowolną komórkę tabeli przestawnej, otwórz zakładkę ANALIZA, kliknij skierowaną w dół strzałkę pod przyciskiem Odśwież, a następnie wybierz opcję Właściwości połączenia z listy rozwijanej. Zaznacz pole wyboru Odśwież co ( ), a następnie użyj kontrolek do określenia w minutach odstępu między operacjami odświeżania. Zauważ, że przy automatycznym odświeżaniu warto unikać zbyt częstego aktualizowania danych. W zależności od lokalizacji danych i ich ilości proces odświeżania może zajmować dużo czasu i spowalniać wykonywanie innych zadań. zmieni się w EXCNIE_ksiazka.indb 223 223 2015-05-14 11:26:33 Rozdział 12. Analizowanie danych za pomocą tabel przestawnychPoleć książkęKup książkę Dodawanie wielu pól   do obszaru wierszy lub kolumn D o każdego obszaru tabeli przestawnej możesz do- dać wiele pól. Ten przydatny mechanizm pozwala przeprowadzać dodatkowe analizy dzięki innemu spojrzeniu na dane. Załóżmy, że analizujesz efekty kampa- nii sprzedażowej, w ramach której przeprowadzono kilka akcji promocyjnych z wykorzystaniem kilku technik re- klamowych. Podstawowa tabela przestawna może pokazy- wać poziom sprzedaży każdego produktu (pole wiersza) dla poszczególnych technik reklamowych (pole kolumny). Można też sprawdzić poziom sprzedaży każdego produk- tu dla poszczególnych akcji promocyjnych. W tym celu należy dodać pole promocji do obszaru wierszy. Dodawanie wielu pól do obszaru wierszy lub kolumn Dodawanie pola do obszaru WIERSZE 1 Kliknij komórkę w tabeli przestawnej. 1 2 Kliknij pole wyboru przy polu tekstowym lub polu z datą, które chcesz dodać (pole zmieni się w A Excel doda pole do obszaru ). WIERSZE. B Excel doda unikatowe wartości wybranego pola do obszaru wierszy w tabeli przestawnej. B 2 A 224 EXCNIE_ksiazka.indb 224 2015-05-14 11:26:33 Poleć książkęKup książkę Dodawanie pola do obszaru WIERSZE lub KOLUMNY 1 Kliknij komórkę w tabeli przestawnej. 2 Na liście w panelu Pola tabeli przestawnej kliknij pole, które chcesz dodać, a następnie przeciągnij je do obszaru WIERSZE lub KOLUMNY. 1 C Excel doda pole do obszaru WIERSZE lub KOLUMNY. D Excel doda unikatowe wartości pola do obszaru wierszy lub kolumn tabeli przestawnej. D D 2 C Zastosuj to Po dodaniu drugiego pola do obszaru wierszy lub kolumn można zmienić pozycje pól, aby zmodyfikować wygląd tabeli przestawnej. Na liście Pola tabeli przestawnej w obszarze WIERSZE lub KOLUMNY kliknij wybrany przycisk z polem i przenieś go nad lub pod inny przycisk. W Excelu można umieścić w obszarze wierszy lub kolumn więcej niż dwa pola. W zależności od wymagań zwią- zanych z przeprowadzaną analizą można wstawić w tych obszarach trzy, cztery pola, a nawet większą ich liczbę. EXCNIE_ksiazka.indb 225 225 2015-05-14 11:26:33 Rozdział 12. Analizowanie danych za pomocą tabel przestawnychPoleć książkęKup książkę Dodawanie wielu pól   do obszaru danych E xcel umożliwia dodanie wielu pól do obszaru da- nych tabeli przestawnej. Pozwala to wyświetlić jed- nocześnie wiele podsumowań, co wzbogaca analizy. Załóżmy, że analizujesz efekty kampanii sprzedażowej. Prosta tabela przestawna może pokazywać sumę sprze- danych sztuk. Ponadto może Cię interesować wartość Dodawanie wielu pól do obszaru danych Dodawanie pól do obszaru danych za pomocą pola wyboru 1 Kliknij komórkę w tabeli przestawnej. 1 sprzedanych towarów. Aby ją uzyskać, można dodać do obszaru danych pole z wartością sprzedaży. Aby umieś- cić w obszarze danych wiele pól, należy zastosować panel Pola tabeli przestawnej lub przeciągnąć pola do odpowied- niego obszaru. 2 Zaznacz pole wyboru przy polu, które chcesz dodać do obszaru danych. A Excel doda reprezentujący pole przycisk do obszaru WARTOŚCI. B Excel doda pole do obszaru danych tabeli przestawnej. 2 B B A 226 EXCNIE_ksiazka.indb 226 2015-05-14 11:26:33 Poleć książkęKup książkę Dodawanie pól do obszaru danych za pomocą przeciągania 1 Kliknij komórkę w tabeli przestawnej. 2 W panelu Pola tabeli przestawnej kliknij pole, które chcesz dodać, i przeciągnij je do obszaru WARTOŚCI. 1 C Excel doda pole do obszaru danych tabeli przestawnej. 2 C C Dodatkowe informacje Gdy dodajesz drugie pole do obszaru danych, Excel przenosi etykiety (na przykład Suma z Sztuk lub Suma z Cena) do obszaru kolumn, co zwiększa czytelność tabeli. Dodatkowo w obszarze KOLUMNY w panelu Pola tabeli przestawnej pojawia się pole Wartości. Dzięki niemu można przestawiać wartości w raporcie. W Excelu w obszarze danych można umieścić więcej niż dwa pola. Możesz swobodnie dodać trzecie, czwarte i kolejne pola, aby wzbogacić analizy. EXCNIE_ksiazka.indb 227 227 2015-05-14 11:26:33 Rozdział 12. Analizowanie danych za pomocą tabel przestawnychPoleć książkęKup książkę Przenoszenie pola   do innego obszaru T abela przestawna to cenne narzędzie do analizy da- nych, ponieważ potrafi podsumować setki, a nawet tysiące rekordów i przedstawić je w formie zwię- złego, zrozumiałego raportu. Jednak tabele przestawne (w odróżnieniu od większości innych mechanizmów analizowania danych w Excelu) nie są statycznym zbio- rem komórek arkusza. Pola tabeli przestawnej można przenosić między obszarami. To pozwala spojrzeć na Przenoszenie pól do różnych obszarów Przenoszenie pól między obszarami wierszy i kolumn 1 Kliknij komórkę w tabeli przestawnej. 2 Kliknij i przeciągnij przycisk pola z obszaru KOLUMNY do obszaru WIERSZE. A Excel wyświetli wartości z pola w obszarze wierszy. Możesz też przeciągnąć przycisk pola z obszaru WIERSZE do obszaru KOLUMNY. 1 A dane z różnych perspektyw, co znacznie wzbogaca ana- lizy. Przenoszenie pól w tabelach przestawnych to proces przestawiania danych. Najczęściej stosowany sposób przestawiania danych to przenoszenie pól między obszarami wierszy i kolumn. Możesz też jednak przenieść pole z obszaru wiersza lub kolumny do obszaru filtrów. 2 228 EXCNIE_ksiazka.indb 228 2015-05-14 11:26:34 Poleć książkęKup książkę Przenoszenie pól z obszaru wierszy do obszaru filtrów 1 Kliknij komórkę w tabeli przestawnej. 2 Kliknij i przeciągnij przycisk pola z obszaru WIERSZE do obszaru FILTRY. 1 A Excel przeniesie przycisk pola do obszaru filtrów raportu. B Możesz też przeciągnąć przycisk pola z obszaru KOLUMNY do obszaru FILTRY. 2 Zastosuj to Możesz przenieść dowolne pole z obszaru wierszy, kolumn i filtrów do obszaru danych tabeli przestawnej. Może wydawać się to dziwne, ponieważ pola w obszarach wierszy, kolumn i filtrów prawie zawsze zawierają tekst, a domyślne obliczenia dla obszaru danych to sumowanie. Jak można zsumować wartości tekstowe? Jest to oczy- wiście niemożliwe. Zamiast tego w Excelu domyślnym podsumowaniem dla wartości tekstowych w tabelach przestawnych jest zliczanie. Dlatego jeśli na przykład przeciągniesz pole Promocja do obszaru wartości, Excel utworzy drugie pole danych o nazwie Liczba z Promocja. EXCNIE_ksiazka.indb 229 229 2015-05-14 11:26:34 Rozdział 12. Analizowanie danych za pomocą tabel przestawnychPoleć książkęKup książkę Grupowanie wartości   w tabelach przestawnych A by ułatwić sobie pracę z tabelami przestawnymi o dużej liczbie wierszy lub kolumn, warto pogru- pować te elementy. Można na przykład pogrupo- wać miesiące w kwartały, co zmniejszy liczbę elementów z 12 do 4. Podobnie w raporcie z dziesiątkami państw można pogrupować je według kontynentów i zmniej- Grupowanie wartości w tabelach przestawnych 1 Kliknij dowolny element pola liczbowego, którego wartości chcesz pogrupować. 1 szyć w ten sposób liczbę pozycji do czterech lub pięciu (w zależności od analizowanych krajów). Jeśli w obszarze wierszy lub kolumn występuje pole liczbowe, pojawiają się czasem setki elementów — po jednym dla każdej war- tości. Można wtedy ulepszyć raport dzięki utworzeniu kilku przedziałów liczbowych. 2 Otwórz zakładkę ANALIZA. 3 Kliknij przycisk Grupowanie. 4 Wybierz opcję Grupuj pole. 2 3 4 230 EXCNIE_ksiazka.indb 230 2015-05-14 11:26:34 Poleć książkęKup książkę Pojawi się okno dialogowe Grupowanie. 5 Wprowadź początkową wartość liczbową. A Zaznacz te pola wyboru (pole ), aby Excel pobrał minimalną i maksymalną wartość pola i umieścił je w polach tekstowych. zmieni się w A 8 5 6 7 6 Wprowadź końcową wartość liczbową. 7 Podaj wielkość każdej grupy. 8 Kliknij przycisk OK. B Excel pogrupuje wartości liczbowe. B Zastosuj to Aby pogrupować daty i czas, najpierw kliknij dowolną komórkę pola. Następnie otwórz zakładkę ANALIZA, klik- nij przycisk Grupowanie i wybierz opcję Grupuj pole. W oknie dialogowym Grupowanie wprowadź początkowe i końcowe daty lub czas. Na liście Według kliknij wybrany sposób grupowania (na przykład Miesiące lub Kwarta- ły). Przy grupowaniu wartości tekstowych trzeba utworzyć niestandardowe grupy. Na przykład aby pogrupować państwa, należy dodać niestandardowe grupy Ameryka Północna, Europa itd. Trzeba zaznaczyć elementy, które mają znaleźć się w jednej z grup, otworzyć zakładkę ANALIZA, kliknąć przycisk Grupowanie i wybrać opcję Gru- puj zaznaczenie. Następnie wystarczy kliknąć etykietę grupy, wprowadzić nazwę, wcisnąć Enter i powtórzyć ten proces dla następnej niestandardowej grupy. EXCNIE_ksiazka.indb 231 231 2015-05-14 11:26:34 Rozdział 12. Analizowanie danych za pomocą tabel przestawnychPoleć książkęKup książkę Modyfikowanie podsumowań   w tabeli przestawnej J eśli analiza danych wymaga obliczeń innych niż sumowanie (dla danych liczbowych) lub zliczanie (dla danych tekstowych), można dla pola danych zastosować dowolne z dziewięciu innych podsumowań dostępnych w Excelu. Na przykład Średnia oblicza śred- nią z wartości z pola liczbowego. Maksimum i Minimum wyświetlają największą i najmniejszą wartość takiego pola. Iloczyn mnoży wartości z pola liczbowego, a Licznik wyświetla łączną liczbę wartości w źródłowym polu. OdchStd i OdchStdc obliczają odchylenie standardowe dla próbki i całej populacji. Wariancja i Wariancja populacji zwracają wariancję z próbki i z całej populacji. Modyfikowanie podsumowań w tabeli przestawnej 1 Kliknij dowolną komórkę wybranego pola danych. 1 2 Otwórz zakładkę ANALIZA. 3 Kliknij przycisk Aktywne pole. 4 Wybierz opcję Ustawienia pola. 3 4 2 232 EXCNIE_ksiazka.indb 232 2015-05-14 11:26:36 Poleć książkęKup książkę Pojawi się okno dialogowe Ustawienia pola wartości z otwartą zakładką Podsumowanie wartości według. 5 Wybierz podsumowanie, które chcesz zastosować. 6 Kliknij przycisk OK. A Excel ponownie przeliczy wyniki w tabeli przestawnej. B Excel zmieni etykietę w polu danych, aby odzwierciedlić zastosowanie nowego podsumowania. 5 6 B A Dodatkowe informacje Jeśli wyniki w tabeli przestawnej wyglądają na nieprawidłowe, sprawdź, czy Excel nie zastosował podsumowania Licznik zamiast Suma. Jeśli pole danych zawiera przynajmniej jedną komórkę tekstową lub pustą, Excel domyślnie zliczy elementy, zamiast obliczyć ich sumę. Gdy dodasz drugie pole do obszaru wierszy lub kolumn, Excel wyświetli sumę częściową dla każdego elementu z zewnętrznego pola. Aby zmienić obliczenia sum częściowych, kliknij dowolną komórkę zewnętrznego pola, otwórz zakładkę ANALIZA, kliknij przycisk Aktywne pole, a następnie wy- bierz opcję Ustawienia pola. Zaznacz pole opcji Niestandardowe (pole ), a następnie wybierz rodzaj obliczeń dla sum częściowych i kliknij przycisk OK. zmieni się w EXCNIE_ksiazka.indb 233 233 2015-05-14 11:26:36 Rozdział 12. Analizowanie danych za pomocą tabel przestawnychPoleć książkęKup książkę Obliczenia   niestandardowe O bliczenia niestandardowe generuje formuła, którą na- leży zdefiniować, aby uzyskać w tabeli przestaw- nej wartości niemożliwe do otrzymania za pomocą źródłowych pól danych i wbudowanych podsumowań Excela. Obliczenia niestandardowe umożliwiają rozbu- dowanie analiz danych o wyniki dostosowane do potrzeb użytkownika. Załóżmy, że tabela przestawna wyświetla kwartalne wy- niki sprzedaży uzyskane przez pracowników i chcesz przyznać 10-procentową premię każdej osobie, która w dowolnym kwartale sprzedała produkty o wartości ponad 25 000 złotych. Możesz przygotować obliczenia niestandardowe, aby sprawdzały, czy poziom sprzedaży jest wyższy niż 25 000 złotych, i jeśli ten warunek jest spełniony, mnożyły wynagrodzenie przez 0,1 w celu otrzymania wysokości premii. Typy obliczeń niestandardowych Gdy tworzysz obliczenia niestandardowe w tabeli prze- stawnej, Excel udostępnia dwie metody: pola obliczeniowe i elementy obliczeniowe. Pole obliczeniowe Jeśli analizy danych wymagają umieszczenia w tabeli przestawnej pola, które jest niedostępne w źródle danych i niemożliwe do otrzymania za pomocą wbudowanych podsumowań z Excela, możesz wstawić niestandardową formułę, aby uzyskać potrzebne pole. Pole obliczeniowe to nowe pole danych o wartości wyliczanej przez niestan- dardową formułę. Pole obliczeniowe można wyświetlać razem z innymi polami danych lub niezależnie od nich. Pole obliczeniowe jest tak naprawdę niestandardowym podsu- mowaniem, dlatego prawie zawsze jest oparte na polach z danych źródłowych. Więcej informacji na ten temat znaj- dziesz w podrozdziale „Dodawanie niestandardowego pola obliczeniowego” w dalszej części rozdziału. Excel przeprowadza obliczenia niestandardowe na da- nych źródłowych, aby wygenerować podsumowanie. W większości sytuacji obliczenia niestandardowe przy- pominają wbudowane podsumowania tabel przestawnych Excela, przy czym użytkownik może precyzyjnie zdefi- niować szczegóły formuły. Ponieważ tworzony jest wzór, można wykorzystać większość związanych z formułami możliwości, jakie daje Excel. Zapewnia to bardzo dużą swobodę przy opracowywaniu obliczeń niestandardo- wych dostosowanych do potrzeb z zakresu analizy da- nych. Dzięki umieszczeniu obliczeń w tabeli przestawnej, a nie w danych źródłowych można w razie potrzeby łatwo zaktualizować obliczenia i odświeżyć wyniki w raporcie. Element obliczeniowy Jeśli przeprowadzane analizy danych wymagają umieszcze- nia w tabeli przestawnej elementu, który jest niedostępny w źródle danych i niemożliwy do otrzymania za pomocą wbudowanych podsumowań z Excela, możesz wstawić niestandardową formułę, aby uzyskać potrzebny element. Element obliczeniowy to nowa pozycja w wierszu albo ko- lumnie, której wartość jest obliczana za pomocą formuły niestandardowej. W formule elementu obliczeniowego używane są elementy z danego pola. 234 EXCNIE_ksiazka.indb 234 2015-05-14 11:26:36 Poleć książkęKup książkę Ograniczenia obliczeń niestandardowych Niezależnie od tego, czy używasz pól, czy elementów ob- liczeniowych, obliczenia niestandardowe są przydatnym dodatkiem do zestawu narzędzi analitycznych w tabe- lach przestawnych. Jednak choć formuły w obliczeniach niestandardowych wyglądają jak w zwykłych arkuszach, nie dają wszystkich możliwości dostępnych w standardo- wych formułach. Istnieje szereg ograniczeń, które Excel nakłada na formuły niestandardowe. Nie można na przy- kład stosować odwołań do wartości spoza danych tabeli przestawnej ani elementów obliczeniowych w pogrupo- wanej tabeli. Ogólne ograniczenia Głównym ograniczeniem związanym z obliczeniami nie- standardowymi jest to, że nie można stosować odwołań do wartości spoza danych źródłowych tabeli przestawnej (wyjątkiem są tu stałe, na przykład liczby): • W niestandardowych formułach nie można używać jako operandów odwołań do komórek, adresów za- kresów i nazw zakresów. • Nie można stosować funkcji, która wymaga odwo- łań do komórek, zakresów lub zdefiniowanych nazw. Można jednak korzystać z wielu funkcji Excela, jeśli zamiast odwołania do komórki lub nazwy zakresu podane zostanie pole lub element. Na przykład jeżeli chcesz utworzyć element obliczeniowy zwracający średnią dla elementów o nazwach Sty, Lut i Mar, mo- żesz zastosować następującą formułę: =ŚREDNIA(Sty, Lut, Mar) • W niestandardowych formułach nie można używać jako operandów sum częściowych, sum końcowych wierszy, sum końcowych kolumn ani sumy końcowej tabeli przestawnej. Ograniczenia dotyczące pól obliczeniowych Jeśli chcesz używać pól obliczeniowych, musisz zrozu- mieć, jak wskazywać inne pola tabeli przestawnej w obli- czeniach i jakie ograniczenia obowiązują przy korzystaniu z odwołań do pól. odwołania do pól Excel traktuje odwołania do pól w formułach jako sumy wartości z tych pól. Na przykład formuła =Sprzedaż + 1 nie powoduje dodania 1 do każdej wartości z pola Sprzedaż i zwrócenia sumy wszystkich uzyskanych w ten sposób liczb. Excel nie interpretuje tej formuły jako =Suma z  (Sprzedaż + 1). Zamiast tego formuła dodaje 1 do sumy wartości z pola Sprzedaż. Excel traktuje więc pokazaną formułę jako =(Suma z Sprzedaż) + 1. pRoblemy z odwołaniami do pól Gdy w obliczeniach niestandardowych używane jest od- wołanie do innego pola, Excel domyślnie oblicza sumę, co może prowadzić do problemów. Jest tak, ponieważ sumo- wanie niektórych rodzajów danych nie ma sensu. Załóżmy na przykład, że źródło danych ze spisem towarów zawiera pola Liczba sztuk i Cena jednostkowa. Użyt- kownik chce obliczyć łączną wartość towarów, dlatego tworzy pole obliczeniowe z następującą formułą: =Liczba sztuk * Cena jednostkowa Niestety ta formuła nie zadziała, ponieważ Excel traktuje operand Cena jednostkowa jako sumę takich cen. Oczywiście dodawanie cen do siebie nie ma sensu, formu- ła zwróci więc nieprawidłowy wynik. Ograniczenia dotyczące elementów obliczeniowych Excel nakłada następujące ograniczenia związane z ele- mentami obliczeniowymi: • W przypadku elementu obliczeniowego formuła nie- standardowa nie może odwoływać się do elementów z innych pól z wyjątkiem pola, w którym sam element obliczeniowy się znajduje. • Nie można tworzyć elementów obliczeniowych w ta- beli przestawnej, w której przynajmniej jedno pole jest pogrupowane. Przed utworzeniem elementu obliczeniowego należy rozgrupować wszystkie pola tabeli przestawnej. • Nie można pogrupować pola w tabeli przestawnej zawierającej choć jeden element obliczeniowy. • Elementu obliczeniowego nie można używać jako fil- tra raportu. Ponadto pola wiersza lub kolumny, które zawiera obiekt obliczeniowy, nie można przenieść do obszaru filtrów. • Nie można wstawić elementu obliczeniowego do tabeli przestawnej, w której określone pole zostało użyte więcej niż jeden raz. • Nie można wstawić elementu obliczeniowego do ta- beli przestawnej, w której użyto obliczeń podsumo- wujących typu Średnia, OdchStd, OdchStdc, Wariancja albo Wariancja populacji. EXCNIE_ksiazka.indb 235 235 2015-05-14 11:26:36 Rozdział 12. Analizowanie danych za pomocą tabel przestawnychPoleć książkęKup książkę Dodawanie niestandardowego   pola obliczeniowego J eśli w analizach danych potrzebne jest pole tabeli przestawnej, które nie występuje w polach danych źródłowych i jest niemożliwe do uzyskania za po- mocą wbudowanych podsumowań Excela, można wsta- wić pole obliczeniowe z niestandardową formułą i w ten sposób otrzymać pożądane wyniki. Niestandardowe pole obliczeniowe jest oparte na formule bardzo podobnej do zwykłych formuł Excela. Jednak formuły pola obliczenio- wego nie należy wstawiać do komórki arkusza. Zamiast tego Excel udostępnia okno dialogowe, w którym można wprowadzić nazwę pola obliczeniowego i utworzyć for- mułę. Następnie Excel zapisuje formułę razem z resztą danych tabeli przestawnej w jej pamięci podręcznej. Dodawanie niestandardowego pola obliczeniowego 1 Kliknij dowolną komórkę w obszarze danych tabeli przestawnej. 2 Otwórz zakładkę ANALIZA. 3 Kliknij przycisk Obliczenia. 4 Wybierz opcję Pola, elementy i zestawy. 5 Wybierz opcję Pole obliczeniowe. Pojawi się okno dialogowe Wstawianie pola obliczeniowego. 6 Wpisz nazwę pola obliczeniowego. 7 Rozpocznij wpisywanie formuły dla pola obliczeniowego. 2 3 4 5 1 6 7 236 EXCNIE_ksiazka.indb 236 2015-05-14 11:26:36 Poleć książkęKup książkę 8 Aby wstawić do formuły pole w miejscu, w którym znajduje się kursor, kliknij nazwę tego pola. 9 Kliknij Wstaw pole. 10 Gdy formuła będzie gotowa, kliknij przycisk Dodaj. 11 Kliknij przycisk OK. A Excel doda pole obliczeniowe do obszaru danych tabeli przestawnej. B Excel doda pole obliczeniowe do panelu Pola tabeli przestawnej. 10 8 9 11 A B Dodatkowe informacje Gdy do tabeli przestawnej dodajesz pole obliczeniowe, Excel uwzględnia niestandardowe obliczenia w wierszu z sumą końcową. Niestety często są one błędne, dlatego nie należy zakładać, że są prawidłowe. Źródłem prob- lemu jest to, że wynik nie jest sumą wartości z pola obliczeniowego. Zamiast tego Excel stosuje formułę z pola obliczeniowego do sumy pól użytych w formule. Gdy dodasz opisane w przykładzie pole obliczeniowe, Excel zastosuje formułę do wartości Suma końcowa pola Suma z Cena w sumie. Nie jest to prawidłowy sposób oblicza- nia sumy prowizji. Aby rozwiązać problem, powinieneś utworzyć formułę sumującą kwoty prowizji poza tabelą przestawną. EXCNIE_ksiazka.indb 237 237 2015-05-14 11:26:36 Rozdział 12. Anali
Pobierz darmowy fragment (pdf)

Gdzie kupić całą publikację:

Excel. Wykresy, analiza danych, tabele przestawne. Niebieski podręcznik
Autor:
Sklep
Format
Cena
Helion.pl Helion.pl
ebook/pdf
29,50 zł
Idź do sklepu »

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ą: