Darmowy fragment publikacji:
Praktyczny Excel
Przenoszenie
danych z i do Excela
Excel jest niezastąpionym narzędziem do analizy danych – prostym
w użyciu, szybkim i oferującym ogromne możliwości. Nie tylko oferu-
je wiele wbudowanych funkcji, ale również umożliwia skomplikowane
analizy statystyczne. Do przeprowadzenia analiz potrzebne są dane,
te jednak często znajdują się w zewnętrznych źródłach: plikach teksto-
wych, bazach danych czy stronach WWW. W tej książce przeczytasz, jak
je pozyskać, aby móc pracować z nimi w Excelu.
6
i
P
r
z
e
n
o
s
z
e
n
e
d
a
n
y
c
h
z
i
d
o
E
x
c
e
a
l
Praktyczne poradniki – skuteczne analizy w Excelu
e-book
Książka dostępna jako e-book na:
ISBN 978-83-269-6512-8
0
5
R
O
U
9 788326 965128
Cena brutto: 49 zł
6
1 NUMER PRAWNICZY
RAZ NA KWARTAŁ,
przygotowany przez
Kancelarię Prawną
Traple, Konarski,
Podrecki i Wspólnicy
OCHRONA
DANYCH OSOBOWYCH
profesjonalnie i kompleksowo
W Excelu brakuje poleceń, które ułatwiłyby pracę, np. zaznaczyły puste
wiersze czy kopiowały jednocześnie dany arkusz określoną liczbę razy. Ta-
kie operacje trzeba wykonać krok po kroku. Nie dość, że jest czasochłonne,
to jeszcze łatwo popełnić błąd. Nie musisz już jednak tracić niepotrzebnie
czasu i nerwów na takie działania. W tej książce pokazujemy, jak napisać
makra, które bezbłędnie wykonają takie żmudne operacje. Zawiera ona
zbiór kodów, które nie mają swoich odpowiedników w poleceniach Excela i
na pewno przydadzą się w codziennej pracy i porządkowaniu danych.
Polecamy nasze pozostałe publikacje:
Praktyczny Excel
Praktyczny Excel
Błyskawiczne
porządkowanie danych
Odnalezienie komórki zawierającej określoną wartość w zestawieniu
obejmującym kilkanaście tysięcy wierszy może być bardzo czasochłonne.
W książce omawiamy kilkadziesiąt praktycznych formuł wyszukujących,
wraz z przykładami ich zastosowania. Przedstawiamy też inne mechani-
zmy ułatwiające dotarcie do poszukiwanych informacji, np. wyróżniające
interesujące wartości. Omawiamy możliwości autofi ltra, za pomocą któ-
rego można szybko pozostawić na ekranie widoczne tylko te dane, które
w danej chwili potrzebne.
z użyciem makr
B
ł
y
s
k
a
w
i
c
z
n
e
p
o
r
z
ą
d
k
o
w
a
n
e
d
a
n
y
c
h
z
u
ż
y
c
i
e
m
m
a
k
r
i
i
i
i
S
z
y
b
k
e
w
y
s
z
u
k
w
a
n
e
d
a
n
y
c
h
w
E
x
c
e
u
l
Szybkie wyszukiwanie
danych w Excelu
e-book
Książka dostępna jako e-book na:
ISBN 978-83-269-5270-8
5
3
R
O
U
Cena: 59 zł brutto
e-book
Książka dostępna jako e-book na:
ISBN 978-83-269-5269-2
4
3
R
O
U
Cena: 59 zł brutto
UOR34-Okladka.indd 1
19.08.2016 11:34:40
18.08.2016 15:57:03
Dostępne pod adresem: fabrykawiedzy.com
W MIESIĘCZNIKU ZNAJDZIESZ:
gotowe, w pełni edytowalne wzory dokumentówzwiązanych
zprzetwarzaniemdanychosobowychwrazzinstrukcjamiwypełnienia
krokpokroku,
przykładowe zapisy umowne,
wyjaśnienie zawiłych kwestii prawnych,wszczególnościnastyku
ochronydanychosobowychinowychtechnologii,
porady, jak zachować się podczas kontroli GIODO,
zmiany w prawie i ich konsekwencjedlapracyABI,ADOiASI,
szczegółowe porady na temat danych osobowychdlafirmprywatnych
UOR35-Okladka.indd 1
orazadministracjipublicznej,
gotowe materiały do przeprowadzania szkoleńzzakresu
danychosobowych.
Zamów prenumeratę!
1
2
Półroczną z 20 zniżką
+
myszka gratis
Roczną z 30 zniżką
+
tablet gratis
GRATIS
myszka
rabat
20
GRATIS
tablet
rabat
30
Zamów prenumeratę już dziś na FabrykaWiedzy.com,
lub przez Centrum Obsługi Klienta:
tel. 22 518 29 29, email: cok@wip.pl
DN 210x297 05.2016 prenumerata v3.indd 1
19.05.2016 10:28:25
Przenoszenie danych
z i do Excela
Krzysztof Chojnacki, Piotr Dynia
Polecamy nasze pozostałe publikacje:
Dostępne pod adresem: fabrykawiedzy.com
Spis treści
Wstęp ������������������������������������������������������������������������������������������������������������������������������������������� 5
1. Przegląd metod importowania danych do Excela ������������������������������������������������������ 7
1�1� Pliki typu ODC ������������������������������������������������������������������������������������������������������ 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ę ODBC �������������������������������������������������� 12
1�6� Pobieranie danych z baz analitycznych ������������������������������������������������������������ 13
1�7� Pobieranie danych z plików tekstowych ����������������������������������������������������������� 14
1�8� Pobieranie danych ze stron WWW ������������������������������������������������������������������ 16
1�9� Praca z plikami typu ODC ��������������������������������������������������������������������������������� 17
1�10� Odświeżanie zapisanych w arkuszu danych ����������������������������������������������������� 17
1�11� Importowanie danych z bazy Accessa do Excela �������������������������������������������� 17
1�12� Usuwanie połączeń danych �������������������������������������������������������������������������������� 18
1�13� Konfiguracja połączeń danych �������������������������������������������������������������������������� 19
1�14� Eksport pliku połączenia ������������������������������������������������������������������������������������ 20
2. Szybkie importowanie danych z bazy MS Access ����������������������������������������������������� 22
2�1� Importowanie wszystkich rekordów ����������������������������������������������������������������� 22
2�2� Pobieranie wyselekcjonowanych rekordów bazy danych ������������������������������ 24
3. Importowanie danych z plików tekstowych za pomocą kreatora ������������������������� 30
3�1� Importowanie danych o tej samej szerokości pól ������������������������������������������� 32
3�2� Przydatne informacje dotyczące importu �������������������������������������������������������� 33
4. Importowanie danych do tabeli przestawnej ������������������������������������������������������������ 35
4�1� Microsoft Query – pobieranie danych zewnętrznych ������������������������������������ 37
4�2� Niestandardowe pozycje obliczeniowe tabeli przestawnej
– element obliczeniowy �������������������������������������������������������������������������������������� 43
4�3� Modyfikacja elementów obliczeniowych oraz wyświetlanie
listy stworzonych elementów ����������������������������������������������������������������������������� 45
4�4� Odświeżanie danych w tabeli oraz modyfikacja kwerendy
w Microsoft Query ���������������������������������������������������������������������������������������������� 46
5. Power Query – zaawansowane możliwości importu danych ��������������������������������� 47
5�1� Język „M” �������������������������������������������������������������������������������������������������������������� 48
3
5�2� Instalacja dodatku ����������������������������������������������������������������������������������������������� 49
5�3� Wyszukiwanie danych ���������������������������������������������������������������������������������������� 51
5�4� Przygotowanie danych ���������������������������������������������������������������������������������������� 53
5�5� Wizualizacja pobranych danych za pomocą dodatku Power View �������������� 56
5�6� Pobieranie danych z baz SQL Server ���������������������������������������������������������������� 57
5�7� Połączenie z bazą relacyjną (pobieranie danych z pojedynczej tabeli
lub widoku) ���������������������������������������������������������������������������������������������������������� 57
5�8� Połączenie z hurtownią danych (pobieranie danych
z połączonych ze sobą tabel lub widoków) ������������������������������������������������������ 60
5�9� Pobieranie danych ze stron WWW ������������������������������������������������������������������ 61
5�10� Pobieranie danych tabelarycznych �������������������������������������������������������������������� 62
5�11� Pobieranie danych z jednego źródła ����������������������������������������������������������������� 63
5�12� Wizualizacja pobranych danych za pomocą dodatku Power Map ��������������� 63
5�13� Pobieranie danych w formacie ODATA ����������������������������������������������������������� 65
5�14� Pobieranie danych z kilku źródeł ���������������������������������������������������������������������� 68
6. Pobieranie danych ze stron WWW ����������������������������������������������������������������������������� 72
7. Konsolidowanie danych z wielu arkuszy �������������������������������������������������������������������� 75
8. Szybkie modyfikowanie zaimportowanych danych ������������������������������������������������� 79
8�1� Usuwanie niedrukowanych znaków ����������������������������������������������������������������� 79
8�2� Usuwanie lub zamiana przecinków na kropki w liczbach ����������������������������� 80
8�3� Wyszukiwanie i usuwanie symboli wieloznacznych ��������������������������������������� 82
9. Formatowanie zaimportowanych danych ������������������������������������������������������������������ 84
9�1� Formatowanie danych liczbowych za pomocą skrótów ��������������������������������� 84
9�2� Wartości czasu wyświetlane jako ułamki ��������������������������������������������������������� 85
9�3� Znikające zero na początku wartości liczbowych ������������������������������������������� 86
10. Porządkowanie danych o niestandardowym układzie �������������������������������������������� 89
10�1� Zestawienie składające się z wielowierszowych bloków ��������������������������������� 89
10�2� Wprowadzenie kryterium sortowania �������������������������������������������������������������� 90
10�3� Wprowadzenie numeracji porządkowej ����������������������������������������������������������� 91
10�4� Budowa posortowanej wersji zestawienia ������������������������������������������������������� 93
11. Wymiana danych między Excelem i Wordem ����������������������������������������������������������� 96
11�1� Dopasowanie raportu sprzedaży do układu strony ���������������������������������������� 96
11�2� Wstawione zestawienie sprzedaży połączone z plikiem źródłowym ����������� 99
11�3� Przenoszenie treści raportu z Worda do Excela �������������������������������������������� 101
11�4� Odwołanie do treści dokumentu Worda �������������������������������������������������������� 102
4
Przenoszenie danych z i do ExcelaWstęp
Excel jest niezastąpionym narzędziem do analizy danych – prostym w uży-
ciu, szybkim i oferującym ogromne możliwości� Nie tylko oferuje wiele
wbudowanych funkcji, ale również umożliwia skomplikowane analizy sta-
tystyczne� Do przeprowadzenia analiz potrzebne są dane, te jednak często
znajdują się w zewnętrznych źródłach: plikach tekstowych, bazach danych
czy stronach WWW� W tej książce przeczytasz, jak je pozyskać, aby móc
pracować z nimi w Excelu�
Zaawansowanych użytkowników z pewnością zainteresuje rozdział na temat
dodatku Power Query� Jest to bezpłatne rozszerzenie Excela, które pozwala
użytkownikom wyszukiwać, dostosowywać do własnych potrzeb i pobierać
dane z różnych źródeł zewnętrznych, w tym z Internetu�
Zaimportowanie danych to jednak nie koniec pracy związanej z przygoto-
waniem danych do analizy� Typowym problemem w tej sytuacji jest „za-
śmiecenie” danych zbędnymi ciągami znaków czy też występowanie kropek
zamiast przecinków w liczbach� Excel oferuje kilka przydatnych funkcji i na-
rzędzi, dzięki którym błyskawicznie pozbędziesz się ich z arkusza� Ważnym
etapem porządkowania zestawienia jest również formatowanie� W książce
pokazujemy, jak można tę operację szybciej, a także w jaki sposób radzić
sobie z formatowaniem komórek zawierających informacje o czasie i dacie�
Dane przechowywane w programie Access można przetwarzać w skoro-
szycie programu Excel, co umożliwia korzystanie z funkcji analizy danych
i tworzenia wykresów, elastyczne rozmieszczanie i tworzenie układów da-
nych oraz stosowanie wielu innych funkcji, które są niedostępne w progra-
mie Access� W tym rozdziale przeczytasz, w jaki sposób zaimportować całą
bazę danych oraz wybrane rekordy�
Innym wyzwaniem jest przenoszenie danych z Excela do innych aplikacji�
Zestawienia wklejane do dokumentów Worda zwykle mają niepoprawny
układ, co zmusza do ich ponownego formatowania� Okazuje się, że wcale nie
musisz tracić na to czasu� Pełne możliwości Excela i Worda wykorzystasz,
jeśli nauczysz się wymieniać dane między skoroszytami i dokumentami�
5
Wszystkie pliki Excela z przykładami
omawianymi w książce można pobrać
ze strony:
http://online.wip.pl/download/excel_import.zip
1. Przegląd metod importowania danych do Excela
Excel jest niezastąpionym narzędziem do analizy danych – prostym w użyciu,
szybkim i oferującym ogromne możliwości� Nie tylko możemy korzystać z wielu
wbudowanych funkcji, ale również przeprowadzać za jego pomocą skompliko-
wane analizy statystyczne� Nie powinien być jednak używany w roli źródła da-
nych, 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ępnienia 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 sko-
roszyty zawierają kopie danych z różnych dni, w konsekwencji ta sama
analiza przeprowadzona przez poszczególnych użytkowników daje od-
mienne wyniki�
Rozwiązaniem obu tych problemów (gwarantującym bezpieczeństwo danych
i spójność wyników ich analiz) jest pobieranie danych do analizy z zewnętrz-
nych źródeł� Od wersji 2007 można to zrobić za pośrednictwem połączeń
danych pakietu Office�
1.1. Pliki typu ODC
Połączenia danych to pliki w formacie XML, w których zapisane są informa-
cje potrzebne do połączenia się z zewnętrznym źródłem danych i pobrania
z niego wybranych danych� Bez obaw, żeby pobrać dane, nie będziemy jed-
nak 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
Rysunek 1.1. Fragment pliku typu ODC definiującego połączenie
z widokiem 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 in-
teresują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 bazodanowego�
8
Przenoszenie danych z i do Excela
Pobierz darmowy fragment (pdf)