Pobieranie i analiza zewnętrznych danych
Excel zaawansowany t.3
Dane, których używamy do analiz w Excelu coraz częściej pochodzą z różnorakich
źródeł, takich jak Internet, bazy danych, pliki tekstowe czy też opracowane przez inne
osoby raporty.
Publikacja ta ma za zadanie pomóc w zaimportowaniu danych do Excela i przygotowaniu
ich do dalszej analizy.
Powstała na podstawie wersji 2013 arkusza Excel. Jednak wszystkie opisane w niej
techniki można też zastosować w poprzedniej wersji tego programu.
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
1.4. Połączenie z hurtownią danych (pobieranie danych z połączonych ze sobą tabel
lub widoków) 11
1.5. Pobieranie danych poprzez dostawcę ODBC12
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.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
3.8. 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 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
100 stron, Format: 16.0x23.0cm, oprawa miękka
