Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00066 005736 11250931 na godz. na dobę w sumie
Excel zaawansowany - pobieranie i analiza zewnętrznych danych - ebook/pdf
Excel zaawansowany - pobieranie i analiza zewnętrznych danych - ebook/pdf
Autor: Liczba stron:
Wydawca: Wiedza i Praktyka Język publikacji: polski
ISBN: 978-83-269-3241-0 Data wydania:
Lektor:
Kategoria: ebooki >> poradniki
Porównaj ceny (książka, ebook, audiobook).

Publikacja jest skierowana do osób sporządzających analizy i raporty w Excelu na importowanych z zewnętrznych źródeł danych, takich jak strony www, bazy danych, hurtownie danych, systemy księgowe, pliki tekstowe i inne.  Niezależnie od źródła danych, z reguły wymagają one żmudnej pracy nad ich uporządkowaniem zanim będą gotowe do dalszej obróbki w Excelu. Publikacja 'Excel zaawansowany  - pobieranie i analiza zewnętrznych danych' ma na celu pomóc w zaimportowaniu danych do Excela i sprawnym przygotowaniu ich do dalszej analizy. Książka powstała na podstawie wersji 2013 arkusza Excel, jednak wszystkie opisane w niej techniki można też zastosować do wersji 2010.
W szczególności książka omawia takie tematy jak: metody importowania danych do Excela (plików ODC, danych z baz SQL Server, połączenia z bazą relacyjną, hurtownią danych, pobieranie ze stron www, importowanie z bazy Acces i inne), porządkowanie zaimportowanych danych w Excelu, Power Query, czyli samoobsługowe Business Intelingence.

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

Darmowy fragment publikacji:

z a a w a n s o w a n y z a a w a n s o w a n y Pobieranie i analiza zewnętrznych danych Coraz częściej używane do analiz dane pochodzą z zewnętrznych źródeł, np. Internetu, baz danych, plików tekstowych, opracowanych przez różne osoby raportów. Niezależnie od źródła danych, do ich analizy z reguły używamy arkusza Excel. Książka ta ma za zadanie pomóc w zaimportowaniu danych do Excela i przygotowaniu ich do dalszej analizy. Książka powstała na podstawie wersji 2013 arkusza Excel. Jednak wszystkie opisane w niej techniki można też zastosować w poprzedniej wersji tego programu. NAJBLIŻSZE TOMY • Kontroling finansowy w Excelu Czytelnik dowie się, jak wykorzystać tabele przestawne do analizy kosztów według układu MPK. Autor opisuje również narzędzia do analizy różnych wariantów budżetu, a także pokazuje, jak znaleźć optymalny wariant redukcji kosztów w fi rmie. • Zaawansowane narzędzia graficznej prezentacji wyników w Excelu Podręcznik opisuje dostępne w Excelu narzędzia wizualizacji, m.in. Smart Art, formatowanie warunkowe (paski danych, skale kolorów i zestawy ikon), zaawansowane wykresy, przebiegi w czasie, pokrętła i suwaki tworzone z wykorzystaniem ActiveX czy sposoby prezentacji danych na dwóch monitorach. • Zastosowanie tabel przestawnych w kontrolingu Tabele przestawne zna większość użytkowników Excela, ale niewielu wie, jak duże możliwości oferuje ta funkcjonalność. Autor drobiazgowo przedstawia różnorodne zastosowania tabel przestawnych do analizy danych, zdradzając wiele ciekawych trików. ACX03 ISBN 978-83-269-3241-0 Cena: 79 zł 3 POBIERANIE I ANALIZA ZEWNĘTRZNYCH DANYCH Tom III NPV COS WSP.KORELACJIJEŻELI ROZKŁ.EXP KOMÓRKA VBA LOGSUMA DNI.ROBOCZE ILOCZYN CZY.LICZBA Pobieranie i analiza zewnętrznych danych Marcin Szeliga Autor: Marcin Szeliga Kierownik grupy wydawniczej: Ewa Ziętek-Maciejczyk Wydawca: Monika Kijok Redaktor prowadzący: Rafał Janus Korekta: Zespół Skład i łamanie: Triograf, Dariusz Kołacz Projekt okładki: Piotr Fedorczyk Druk: Miller ISBN: 978-83-269-3241-0 Copyright by Wydawnictwo Wiedza i Praktyka sp. z o.o. Warszawa 2014 Wydawnictwo Wiedza i Praktyka sp. z o.o. 03-918 Warszawa, ul. Łotewska 9a tel. 22 518 29 29, faks 22 617 60 10 NIP: 526-19-92-256 Numer KRS: 0000098264 – Sąd Rejonowy dla m.st. Warszawy, Sąd Gospodarczy XIII Wydział Gospodarczy Rejestrowy. Wysokość kapitału zakładowego: 200.000 zł „Pobieranie i analiza zewnętrznych danych” wraz z przysługującym Czytelnikom innymi elemen- tami dostępnymi w subskrypcji (e-letter, strona WWW i inne) chronione są prawem autorskim. Przedruk materiałów opublikowanych w „Pobieranie i analiza zewnętrznych danych” oraz w innych dostępnych elementach subskrypcji – bez zgody wydawcy – jest zabroniony. Zakaz nie dotyczy cytowania publikacji z powołaniem się na źródło. Publikacja „„Pobieranie i analiza zewnętrznych danych” została przygotowana z zachowaniem najwyższej staranności i wykorzystaniem wysokich kwalifi kacji, wiedzy i doświadczenia autorów oraz konsultantów. Zaproponowane w publikacji „Pobieranie i analiza zewnętrznych danych” oraz w innych dostępnych elementach subskrypcji wskazówki, porady i interpretacje nie mają charak- teru porady prawnej. Ich zastosowanie w konkretnym przypadku może wymagać dodatkowych, pogłębionych konsultacji. Publikowane rozwiązania nie mogą być traktowane jako ofi cjalne stanowisko organów i urzędów państwowych. W związku z powyższym redakcja nie może ponosić odpowiedzialności prawnej za zastosowanie zawartych w publikacji „Pobieranie i analiza ze- wnętrznych danych” lub w innych dostępnych elementach subskrypcji wskazówek, przykładów, informacji itp. do konkretnych przykładów. Spis treści 1.4. 1. Metody importowania danych do Excela ...................................................................... 7 1.1. Pliki typu ODC – definiowanie połączeń ze źródłami danych ....................... 7 1.2. Pobieranie danych z baz SQL Server ................................................................... 8 1.3. Połączenie z bazą relacyjną (pobieranie danych z pojedynczej tabeli lub widoku) ............................................................................................................. 9 Połączenie z hurtownią danych (pobieranie danych z połączonych ze sobą tabel lub widoków) .................................................................................... 11 1.5. Pobieranie danych poprzez dostawcę ODBC.... .................................................. 12 1.6. Pobieranie danych z baz analitycznych i tworzenie wykresu przestawnego ... 13 1.7. Pobieranie danych o kursach walut z plików tekstowych .................................. 14 1.8. Pobieranie danych ze stron WWW ....................................................................... 16 1.9. Praca z plikami typu ODC – konfigurowanie połączeń z zewnętrznym źródłem danych ........................................................................................................ 17 1.10. Odświeżanie zapisanych w arkuszu danych ........................................................ 17 1.11. Importowanie danych z bazy Accessa do Excela ................................................ 18 1.12. Usuwanie połączeń danych ................................................................................... 19 1.13. Konfiguracja połączeń danych .............................................................................. 20 1.14. Eksport pliku połączenia ...................................................................................... 21 1.15. Podsumowanie ........................................................................................................ 22 2. Porządkowanie w Excelu zaimportowanych danych .................................................... 23 2.1. Zmiana typów kolumn .......................................................................................... 23 2.2. Separatory liczb: tysięcy i dziesiętnych ............................................................... 23 2.3. Zmiana globalnych ustawień Excela .................................................................... 24 2.4. Zmiana separatorów w zaimportowanych danych ............................................. 25 2.5. Porządkowanie formatu daty ............................................................................... 26 2.6. Zmiana ustawień regionalnych systemu Windows ............................................ 26 2.7. Usunięcie zbędnych informacji o czasie ............................................................. 27 2.8. Zmiana formatu daty .............................................................................................. 28 2.9. Usuwanie niechcianych znaków .......................................................................... 30 2.10. Problem z wartością NULL .................................................................................. 30 2.11. Podział tekstu na kolumny ................................................................................... 31 2.12. Eliminowanie powtarzających się rekordów ...................................................... 32 2.13. Oznaczanie powtarzających się wartości ............................................................ 33 2.14. Kolumny wyliczeniowe .......................................................................................... 34 2.15. Oznaczanie powtarzających się rekordów ........................................................... 34 2.16. Wykrywanie wartości spoza prawidłowego zakresu .......................................... 35 2.17. Sprawdzanie, czy wartości liczbowe mieszczą się w ustalonym przedziale ......................................................................................... 35 2.18. Sprawdzanie długości ciągów znaków ................................................................. 36 2.19. Zmiana formatu danych tekstowych .................................................................... 37 2.20. Porządkowanie zaimportowanych danych tekstowych ..................................... 37 2.21. Porządkowanie arkusza z wykorzystaniem funkcji tekstowych ....................... 38 2.22. Zestawienie dotyczące zatrudnienia ....................................................................... 9 2.23. Podsumowanie ........................................................................................................ 43 3. Power Query, czyli samoobsługowe Business Intelligence ............................................44 3.1. Pobieranie danych z użyciem języka „M” ..............................................................45 3.2. Instalacja dodatku Power Query w Excelu ............................................................46 3 Pobieranie i analiza zewnętrznych danych 3.3. Wyszukiwanie danych ...............................................................................................48 3.4. Uporządkowanie danych przed zaimportowaniem ........................................... 50 3.5. Wizualizacja pobranych danych za pomocą dodatku Power View .................. 53 3.6. Pobieranie danych z baz SQL Server .................................................................... 54 3.7. Połączenie z bazą relacyjną (pobieranie danych z pojedynczej tabeli lub widoku) ................................................................................................... 55 Połączenie z hurtownią danych (pobieranie danych z połączonych ze sobą tabel lub widoków) .................................................................................... 57 3.9. Pobieranie danych ze stron WWW ..................................................................... 59 3.10. Pobieranie danych tabelarycznych ze stron WWW z użyciem 3.8. dodatku Power Query ............................................................................................. 59 3.11. Pobieranie danych z jednego źródła .................................................................... 60 3.12. Wizualizacja pobranych danych za pomocą dodatku Power Map ................... 61 3.13. Pobieranie danych w formacie ODATA ............................................................... 63 3.14. Scalanie danych pobranych z wielu źródeł ............................................................69 3.15. Wizualizacja pobranych danych za pomocą dodatku Power View .................. 71 3.16. Edycja wyrażeń języka „M” ................................................................................... 73 3.17. Uzupełnianie danych o informacje słownikowe ................................................ 76 3.18. Podział tekstu na kolumny .................................................................................... 76 3.19. Edycja zapytań ......................................................................................................... 78 3.20. Łączenie danych ze słownikami ........................................................................... 80 3.21. Pobieranie i scalenia danych z wielu plików znajdujących się w tym samym folderze ........................................................................................... 82 3.22. Odczytanie danych z przykładowego pliku ........................................................ 83 3.23. Modyfikacja zapytania ........................................................................................... 84 3.24. Scalenie danych odczytanych ze zdefiniowanego za pomocą funkcji źródła ...................................................................................... 86 3.25. Korzystanie z utworzonej funkcji ......................................................................... 89 3.26. Wyświetlanie pomocy na temat funkcji języka „M” ......................................... 90 3.27. Wywoływanie funkcji we wcześniej utworzonym zapytaniu ............................ 92 3.28. Generowanie listy opisów wbudowanych funkcji języka „M” ......................... 93 3.29. Pobieranie danych z serwisu Facebook ............................................................... 97 3.30. Wizualizacja pobranych danych za pomocą wykresu przestawnego ............... 99 3.31. Podsumowanie ....................................................................................................... 99 4 Wstęp Dziś prawie każdy z nas zajmuje się zarówno prywatnie, jak i zawodowo analizą danych. Na przykład chcąc jak najkorzystniej kupić jakiś towar, po- równujemy jego parametry i ceny. Planując wyjazd, szukamy odpowied- nich połączeń i hoteli. Prowadząc projekt, sprawdzamy, czy poszczególne jego etapy przebiegają zgodnie z przyjętym planem. Przygotowując oferty sprzedaży, opracowujemy profile klientów. Lista zadań, które wymagają od nas pracy z danymi, jest bardzo długa. Coraz częściej używane do tych analiz dane (w książce będziemy je nazy- wać danymi źródłowymi) pochodzą z zewnętrznych źródeł, np. Interne- tu (w tym serwisów społecznościowych), baz danych, plików tekstowych, opracowanych przez różne osoby raportów. Niezależnie od źródła danych, do ich analizy z reguły używamy arkusza Excel – bez wątpienia najpopular- niejszego narzędzia analitycznego. Swoją popularność arkusz Excel zawdzięcza między innymi temu, że mogą go używać osoby o podstawowej wiedzy komputerowej, zaawansowani użytkownicy, jak też zawodowi analitycy. Intuicyjność tego narzędzia oraz bogactwo dokumentacji (w tym dostępnych w Internecie przykładów) sprawia, że każda z nich znajdzie odpowiadający jej sposób rozwiązania swoich problemów. Książka ta ma za zadanie pomóc Czytelnikom w zaimportowaniu do arku- sza Excel i przygotowaniu do dalszej analizy interesujących ich danych. Pu- blikacja powstała na podstawie wersji 2013 arkusza Excel. Jednak wszystkie opisane w niej techniki można też zastosować w poprzedniej wersji (2010) tego programu. 5 Pobieranie i analiza zewnętrznych danych Wszystkie pliki Excela z przykładami o mawianymi w książce można pobrać ze strony: http://online.wip.pl/download/exceltom3.zip 6 Rozdział 1 – Metody importowania danych do Excela 1. Metody importowania danych do Excela Excel jest niezastąpionym narzędziem do analizy danych – prostym w uży- ciu, szybkim i  oferującym ogromne możliwości. Nie tylko możemy ko- rzystać z wielu wbudowanych funkcji, ale również przeprowadzać za jego pomocą skomplikowane analizy statystyczne. Nie powinien być jednak używany w roli źródła danych, szczególnie jeżeli z tymi samymi danymi pracuje więcej niż jedna osoba. Przechowywanie danych źródłowych bezpośrednio w skoroszytach Excela ma dwie poważne wady: 1. Arkusze te z reguły są zapisane w komputerach użytkowników, a więc poza bezpośrednią kontrolą administratorów. W rezultacie są narażo- ne na utratę, np. w wyniku uszkodzenia komputera czy przypadkowego skasowania pliku i udostępnienie niepowołanym osobom (stacje robo- cze z reguły są gorzej zabezpieczone niż serwery, często też fizyczny do- stęp do nich mają różne osoby). 2. Skoroszyty mogą zawierać rozbieżne kopie tych samych danych. Ponie- waż najczęściej sami użytkownicy są odpowiedzialni za synchronizację (aktualizację) zapisanych w  nich danych, zdarza się, że poszczególne skoroszyty zawierają kopie danych z  różnych dni, w  konsekwencji ta sama analiza przeprowadzona przez poszczególnych użytkowników daje odmienne wyniki. Rozwiązaniem obu tych problemów (gwarantującym bezpieczeństwo da- nych i  spójność wyników ich analiz) jest pobieranie danych do analizy z zewnętrznych źródeł. Od wersji 2007 można to zrobić za pośrednictwem połączeń danych pakietu Office. 1.1. Pliki typu ODC – definiowanie połączeń ze źródłami danych Połączenia danych to pliki w formacie XML, w których zapisane są infor- macje potrzebne do połączenia się z zewnętrznym źródłem danych i po- brania z  niego wybranych danych. Bez obaw, żeby pobrać dane nie bę- dziemy jednak musieli samodzielnie tworzyć i edytować pokazanego na rysunku 1.1. dokumentu XML – zrobi to za nas arkusz Excel. Tworzenie połączeń przećwiczymy na przykładzie różnych baz danych, za- czynając od serwera SQL Server firmy Microsoft. 7 Pobieranie i analiza zewnętrznych danych Rysunek 1.1. Fragment pliku typu ODC definiującego połączenie z wi- dokiem bazy danych UWAGA Bazy danych zawierają tabele i widoki. Z naszej perspektywy różnice między tymi obiektami są nieistotne – każdy z nich służy do odczytania interesujących nas danych. 1.2. Pobieranie danych z baz SQL Server SQL Server jest jednym z popularniejszych serwerów bazodanowych – to ten serwer (w darmowej edycji Express) jest wykorzystywany m.in. w pro- gramach Płatnik, Asseco WAPRO czy Comarch Optima. Żeby pobrać dane z tego typu bazy, potrzebne są: 1. Adres lub nazwa serwera SQL Server. 2. Nazwa bazy danych. 3. Nazwy tabel lub widoków, z których odczytamy dane. 4. Konto użytkownika posiadającego co najmniej uprawnienia do odczy- tywania wybranych tabel lub widoków. Wszystkie te informacje dostaniemy od administratora serwera bazoda- nowego. 8 Rozdział 1 – Metody importowania danych do Excela 1.3. Połączenie z bazą relacyjną (pobieranie danych z pojedyn- czej tabeli lub widoku) Baza relacyjna (baza OLTP) jest najczęściej używanym typem baz danych. Bazy tego typu przechowują i zarządzają danymi operacyjnymi, np. dany- mi dotyczącymi bieżących operacji sprzedaży. Aby pobrać dane z bazy relacyjnej SQL Server: 1. Uruchom arkusz Excel. 2. Utwórz nowy skoroszyt. 3. Przejdź do zakładki Dane. 4. Z lewej strony wstążki wyświetlona zostanie sekcja Pobieranie danych zewnętrznych. Znajdujące się na niej przyciski pozwalają utworzyć nowe lub wybrać istniejące połączenie danych pakietu Office. 5. Kliknij przycisk Z innych źródeł, a następnie z listy dostępnych typów źródeł danych wybierz Z programu SQL Server (rysunek 1.2). Rysunek 1.2. Zakładka Dane pozwala m.in. tworzyć i pracować z połą- czeniami danych pakietu Office UWAGA Sekcja Pobieranie danych zewnętrznych będzie nieaktywna, jeżeli w da- nym momencie zaznaczona komórka znajduje się w  obszarze tabeli Excela. Aby je uaktywnić, należy kliknąć dowolną, nienależącą do tabe- li, komórkę arkusza. 9
Pobierz darmowy fragment (pdf)

Gdzie kupić całą publikację:

Excel zaawansowany - pobieranie i analiza zewnętrznych danych
Autor:

Opinie na temat publikacji:


Inne popularne pozycje z tej kategorii:


Czytaj również:


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