Pobieranie danych zewnętrznych za pomocą programu Microsoft Query

Za pomocą programu Microsoft Query można pobierać dane ze źródeł zewnętrznych. Używając programu Microsoft Query do pobierania danych z firmowych baz danych (baza danych: Kolekcja danych związanych z określonym tematem lub celem. W obrębie bazy danych informacje o każdej jednostce, na przykład informacje na temat pracownika lub zamówienia, są umieszczane w tabelach, rekordach i polach.) i plików, nie trzeba ponownie wpisywać danych, które mają być analizowane w programie Excel. Można również automatycznie odświeżać raporty i podsumowania programu Excel z oryginalnej źródłowej bazy danych za każdym razem, gdy baza danych jest aktualizowana o nowe informacje.

Co chcesz zrobić?


Dodatkowe informacje o programie Microsoft Query

Za pomocą programu Microsoft Query można się połączyć z zewnętrznymi źródłami danych, wybrać dane z tych źródeł zewnętrznych, zaimportować dane do arkusza i w razie potrzeby odświeżyć dane, aby były zsynchronizowane z danymi w źródłach zewnętrznych.

Typy baz danych, do których można uzyskać dostęp     Dane można pobierać z baz danych kilku typów, włącznie z bazami programów Microsoft Office Access, Microsoft SQL Server oraz Microsoft SQL Server OLAP Services. Można również pobierać dane ze skoroszytów programu Excel i z plików tekstowych.

Program Microsoft Office zawiera sterowniki, które umożliwiają pobieranie danych z następujących źródeł danych (źródło danych: Przechowywany zestaw informacji o źródle używanych do ustanawiania połączenia z bazą danych. Źródło danych może zawierać nazwę i lokalizację serwera bazy danych, nazwę sterownika bazy danych oraz dane wymagane przez bazę danych do logowania się użytkownika.):

Można także korzystać ze sterowników ODBC (sterownik Open Database Connectivity (ODBC): Plik programu używany do ustanawiania połączenia z określoną bazą danych. Każdy program bazy danych, taki jak Access lub dBASE, albo system zarządzania bazami danych, taki jak SQL Server, wymaga innego sterownika.) lub sterowników źródeł danych (sterownik źródła danych: Plik programu używany do ustanawiania połączenia z określoną bazą danych. Każdy program bazy danych lub system zarządzania bazami danych wymaga innego sterownika.) innych producentów, aby pobierać informacje z niewymienionych wyżej źródeł danych, włącznie z bazami danych OLAP innych typów. Aby uzyskać informacje o instalowaniu sterownika ODBC lub sterownika źródła danych, który nie został wymieniony wyżej, zajrzyj do dokumentacji bazy danych lub skontaktuj się z jej dostawcą.

Wybieranie danych z bazy danych     Dane można pobierać z bazy danych, tworząc kwerendę, która jest zapytaniem dotyczącym danych przechowywanych w zewnętrznej bazie danych. Na przykład, jeśli dane są przechowywane w bazie danych programu Access, można poznać wyniki sprzedaży określonego produktu według regionu. Można pobrać część danych, wybierając jedynie dane dotyczące produktu i regionu, które mają być analizowane.

Za pomocą programu Microsoft Query można wybrać kolumny danych i zaimportować do programu Excel tylko te dane.

Aktualizowanie arkusza w ramach jednej operacji     Umieściwszy dane zewnętrzne w skoroszycie programu Excel, można je odświeżać (odświeżanie: Aktualizowanie danych ze źródła zewnętrznego. Za każdym razem, gdy dane zostają odświeżone, użytkownik widzi najnowszą wersję informacji zawartych w bazie danych, w tym wszelkie wprowadzone zmiany danych.) po każdej zmianie bazy danych, aby aktualizować analizę — bez konieczności ponownego tworzenia raportów i wykresów podsumowań. Można na przykład utworzyć miesięczne podsumowanie sprzedaży i odświeżać je co miesiąc, gdy nadchodzą najnowsze dane o sprzedaży.

Jak program Microsoft Query korzysta ze źródeł danych     Po skonfigurowaniu źródła danych dla określonej bazy danych użytkownik może go używać, gdy zechce utworzyć kwerendę do wybierania i pobierania danych z bazy danych — bez potrzeby ponownego wpisywania informacji o połączeniu. Program Microsoft Query korzysta ze źródła danych, aby ustanowić połączenie z zewnętrzną bazą danych i pokazać, jakie dane są dostępne. Gdy zostanie utworzona kwerenda i dane zostaną zwrócone do programu Excel, program Microsoft Query dostarcza skoroszytowi programu Excel zarówno kwerendę, jak i informacje o źródle danych, dzięki czemu można ponownie łączyć się z bazą danych w celu odświeżenia danych.

Diagram ilustrujący sposób używania źródeł danych przez program Query

Importowanie danych za pomocą programu Microsoft Query     Aby zaimportować dane zewnętrzne do programu Excel za pomocą programu Microsoft Query, wykonaj proste czynności, które opisano szczegółowo w następnych sekcjach.

  1. Nawiązywanie połączenia ze źródłem danych.
  2. Definiowanie kwerendy za pomocą Kreatora kwerend.
  3. Praca z danymi w programie Excel.

Początek strony Początek strony

Nawiązywanie połączenia ze źródłem danych

Co to jest źródło danych?     Źródło danych jest przechowywanym zbiorem informacji, który umożliwia programom Excel i Microsoft Query połączenie z zewnętrzną bazą danych. Używając programu Microsoft Query do skonfigurowania źródła danych, nadaje się źródłu nazwę, a następnie podaje się nazwę i lokalizację bazy danych lub serwera, typ bazy danych oraz informacje o nazwie logowania i haśle. Informacje zawierają także nazwę sterownika ODBC lub sterownika źródła danych, który jest programem tworzącym połączenia z bazą danych określonego typu.

Aby skonfigurować źródło danych za pomocą programu Microsoft Query:

  1. Na karcie Dane w grupie Dane zewnętrzne kliknij opcję Z innych źródeł, a następnie kliknij opcję Z programu Microsoft Query.
  2. Wykonaj jedną z następujących czynności:
  3. Kliknij dwukrotnie <Nowe źródło danych>.

— lub —

Kliknij <Nowe źródło danych>, a następnie kliknij przycisk OK.

Zostanie wyświetlone okno dialogowe Utwórz nowe źródło danych.

  1. W kroku 1 wpisz nazwę w celu identyfikacji źródła danych.
  2. W kroku 2 kliknij sterownik typu bazy danych używanej jako źródło danych.

Jeśli zewnętrzna baza danych, do której trzeba uzyskać dostęp, nie jest obsługiwana przez sterowniki ODBC instalowane z programem Microsoft Query, należy uzyskać i zainstalować sterownik ODBC zgodny z pakietem Microsoft Office od innego producenta, na przykład od producenta bazy danych. Aby uzyskać instrukcje dotyczące instalacji, skontaktuj się z dostawcą bazy danych.

 Uwaga   Bazy danych OLAP nie wymagają sterowników ODBC. Podczas instalacji programu Microsoft Query są instalowane sterowniki dla baz danych utworzonych za pomocą Usług analiz programu Microsoft SQL Server. Aby ustanowić połączenie z innymi bazami danych OLAP, należy zainstalować sterownik źródła danych i oprogramowanie klienckie.

  1. Kliknij przycisk Połącz, a następnie podaj informacje potrzebne do połączenia ze źródłem danych. W przypadku baz danych, skoroszytów programu Excel i plików tekstowych wpisywane informacje zależą od typu wybranego źródła danych. Może się pojawić monit o wprowadzenie nazwy logowania, hasła, wersji używanej bazy danych, jej lokalizacji lub innych informacji specyficznych dla typu bazy danych.

Należy używać silnych haseł, w których występują małe i wielkie litery, cyfry i symbole. W słabych hasłach nie występują połączenia tych elementów. Silne hasło: Y6dh!et5. Słabe hasło: Dom27. Hasła powinny mieć co najmniej 8 znaków. Lepsze są jednak hasła liczące co najmniej 14 znaków. Aby uzyskać więcej informacji, zobacz Ochrona danych osobowych silnym hasłem.

Zapamiętanie hasła jest bardzo ważne. Zapomnianego hasła firma Microsoft nie może odtworzyć. Zapisane hasła należy przechowywać w bezpiecznym miejscu, z dala od informacji chronionych przez te hasła.

  1. Po wprowadzeniu wymaganych informacji kliknij przycisk OK lub Zakończ, aby powrócić do okna dialogowego Utwórz nowe źródło danych.
  2. Jeśli baza danych zawiera tabele (tabela: Kolekcja danych dotyczących określonej kategorii, które są przechowywane w rekordach (wierszach) i polach (kolumnach).) i jedna z nich ma być automatycznie wyświetlana w Kreatorze kwerend, kliknij pole dla kroku czwartego, a następnie kliknij odpowiednią tabelę.
  3. Jeśli nie chcesz wpisywać nazwy logowania i hasła podczas korzystania ze źródła danych, zaznacz pole wyboru Zapisz mój numer ID i hasło w definicji źródła danych. Zapisane hasło nie jest szyfrowane. Jeśli to pole wyboru jest niedostępne, skontaktuj się z administratorem bazy danych w celu sprawdzenia, czy można udostępnić tę opcję.

Zabezpieczenia  Należy unikać zapisywania informacji logowania podczas łączenia się ze źródłami danych. Te informacje mogą być przechowywane jako zwykły tekst i złośliwy użytkownik będzie mógł uzyskać dostęp do informacji w celu złamania zabezpieczeń źródła danych.

Gdy ta procedura zostanie zakończona, nazwa źródła danych pojawi się w oknie dialogowym Wybierz źródło danych.

Początek strony Początek strony

Definiowanie kwerendy za pomocą Kreatora kwerend

W przypadku większości kwerend należy użyć Kreatora kwerend     Kreator kwerend ułatwia wybieranie oraz łączenie danych z różnych tabel i pól w bazie danych. Za pomocą Kreatora kwerend można wybrać tabele i pola, które mają zostać uwzględnione w kwerendzie. Sprzężenie wewnętrzne (operacja kwerendy określająca, że wiersze z dwóch tabel są połączone na podstawie identycznych wartości pól) jest tworzone automatycznie, gdy kreator rozpoznaje pole klucza podstawowego w jednej tabeli i pole o tej samej nazwie w drugiej tabeli.

Kreatora można także używać do sortowania zestawu wyników i prostego filtrowania. W końcowym kroku kreatora można wybrać przekazanie danych do programu Excel lub dalsze uściślenie kwerendy w programie Microsoft Query. Utworzoną kwerendę można uruchomić w programie Excel lub w programie Microsoft Query.

Aby uruchomić Kreatora kwerend, wykonaj następujące kroki.

  1. Na karcie Dane w grupie Dane zewnętrzne kliknij opcję Z innych źródeł, a następnie kliknij opcję Z programu Microsoft Query.
  2. W oknie dialogowym Wybierz źródło danych upewnij się, że jest zaznaczone pole wyboru Używaj Kreatora kwerend do tworzenia/edycji kwerend.
  3. Kliknij dwukrotnie źródło danych, którego chcesz użyć.

— lub —

Kliknij źródło danych, którego chcesz użyć, a następnie kliknij przycisk OK.

Praca bezpośrednio w programie Microsoft Query w przypadku kwerend innych typów     Aby utworzyć bardziej zaawansowaną kwerendę, niż pozwala na to Kreator kwerend, można pracować bezpośrednio w programie Microsoft Query. Za pomocą programu Microsoft Query można wyświetlać i zmieniać kwerendy, których tworzenie rozpoczęto w Kreatorze kwerend, lub można tworzyć nowe kwerendy bez użycia kreatora. Bezpośrednia praca w programie Microsoft Query jest wymagana, gdy należy utworzyć kwerendy wykonujące następujące zadania:

  • Wybieranie określonych danych z pola     W dużej bazie danych można wybrać część danych z pola i pominąć niepotrzebne dane. Jeśli na przykład potrzebne są dane o dwóch produktach z pola, w którym są informacje dotyczące wielu produktów, można użyć kryteriów (kryteria: Warunki określane w celu ograniczenia liczby rekordów uwzględnianych w zestawie wyników kwerendy lub filtru.) w celu wybrania danych tylko o odpowiednich dwóch produktach.
  • Pobieranie danych na podstawie różnych kryteriów przy każdym uruchomieniu kwerendy     Aby utworzyć takie samo podsumowanie lub taki sam raport programu Excel dla kilku obszarów tych samych danych zewnętrznych — na przykład oddzielny raport ze sprzedaży dla każdego regionu — można utworzyć kwerendę parametryczną (kwerenda parametryczna: Typ kwerendy, która po uruchomieniu monituje o podanie wartości (kryteriów), na podstawie których ma wybrać rekordy do zestawu wyników, dzięki czemu ta sama kwerenda może być używana do pobierania różnych zestawów wyników.). Gdy zostanie uruchomiona kwerenda parametryczna, pojawi się monit o podanie wartości, która zostanie użyta jako kryterium przy wybieraniu rekordów przez kwerendę. Na przykład kwerenda parametryczna może wyświetlić monit o podanie określonego regionu i można jej użyć ponownie do utworzenia raportów ze sprzedaży w poszczególnych regionach.
  • Sprzęganie danych na różne sposoby     Sprzężenia wewnętrzne tworzone przez Kreatora kwerend są najczęściej używanym typem sprzężenia stosowanym przy tworzeniu kwerend. Czasami jednak chce się użyć sprzężenia innego typu. Jeśli na przykład istnieje tabela informacji o sprzedaży produktów i tabela informacji o klientach, to sprzężenie wewnętrzne (typ sprzężenia tworzony przez Kreatora kwerend) uniemożliwi pobranie rekordów klientów dla tych klientów, którzy nie dokonali zakupów. Za pomocą programu Microsoft Query można sprzęgnąć te tabele tak, by były pobierane wszystkie rekordy klientów oraz dane dotyczące sprzedaży dla tych klientów, którzy dokonali zakupów.

Aby uruchomić program Microsoft Query, wykonaj następujące kroki.

  1. Na karcie Dane w grupie Dane zewnętrzne kliknij opcję Z innych źródeł, a następnie kliknij opcję Z programu Microsoft Query.
  2. W oknie dialogowym Wybierz źródło danych upewnij się, że pole wyboru Używaj Kreatora kwerend do tworzenia/edycji kwerend jest wyczyszczone.
  3. Kliknij dwukrotnie źródło danych, którego chcesz użyć.

— lub —

Kliknij źródło danych, którego chcesz użyć, a następnie kliknij przycisk OK.

Ponowne używanie i udostępnianie kwerend     Zarówno w Kreatorze kwerend, jak i w programie Microsoft Query można kwerendy zapisywać jako plik dqy, który można modyfikować i udostępniać oraz którego można używać ponownie. Pliki dqy można otwierać bezpośrednio w programie Excel, co umożliwia tworzenie dodatkowych zakresów danych zewnętrznych na podstawie tej samej kwerendy.

Aby otworzyć zapisaną kwerendę z poziomu programu Excel:

  1. Na karcie Dane w grupie Dane zewnętrzne kliknij opcję Z innych źródeł, a następnie kliknij opcję Z programu Microsoft Query. Zostanie wyświetlone okno dialogowe Wybierz źródło danych.
  2. W oknie dialogowym Wybierz źródło danych kliknij kartę Kwerendy.
  3. Kliknij dwukrotnie zapisaną kwerendę, którą chcesz otworzyć. Kwerenda ta zostanie wyświetlona w programie Microsoft Query.

Aby otworzyć zapisaną kwerendę, gdy program Microsoft Query jest już otwarty, kliknij menu Plik w programie Microsoft Query, a następnie kliknij polecenie Otwórz.

Dwukrotne kliknięcie pliku dqy spowoduje otwarcie programu Excel, który uruchomi kwerendę i wstawi jej wyniki do nowego arkusza.

Aby udostępnić podsumowanie lub raport programu Excel oparty na danych zewnętrznych, można dać innym użytkownikom skoroszyt zawierający zakres danych zewnętrznych albo utworzyć szablon (szablon: Skoroszyt, który jest tworzony i używany jako podstawa dla innych podobnych skoroszytów. Szablony można tworzyć dla skoroszytów i arkuszy. Szablonem domyślnym dla skoroszytów jest szablon Skoroszyt.xlt, a dla arkuszy szablon Arkusz.xlt.). Szablon pozwala zapisać podsumowanie lub raport bez zapisywania danych zewnętrznych, dzięki czemu plik jest mniejszy. Dane zewnętrzne są pobierane, gdy użytkownik otwiera szablon raportu.

Początek strony Początek strony

Praca z danymi w programie Excel

Po utworzeniu kwerendy za pomocą Kreatora kwerend lub programu Microsoft Query można zwrócić dane do arkusza programu Excel. Dane stają się wówczas zewnętrznym zakresem danych (zakres danych zewnętrznych: Zakres danych przeniesiony do arkusza, ale pochodzący spoza programu Excel, na przykład z bazy danych lub pliku tekstowego. W programie Excel dane takie można formatować i wykorzystywać w obliczeniach w taki sam sposób, jak inne dane.) lub raportem tabeli przestawnej (raport w formie tabeli przestawnej: Interakcyjny, krzyżowy raport programu Excel, który podsumowuje i analizuje dane, takie jak rekordy bazy danych, z różnych źródeł, w tym również źródeł zewnętrznych dla programu Excel.), który można formatować i odświeżać.

Formatowanie pobranych danych     W programie Excel można używać narzędzi, takich jak wykresy czy automatyczne sumy pośrednie, aby przedstawiać i podsumowywać dane pobrane przez program Microsoft Query. Można formatować dane, przy czym formatowanie to zostanie zachowane po odświeżeniu danych zewnętrznych. Można też stosować własne etykiety kolumn zamiast nazw pól oraz automatycznie dodawać numery wierszy.

Program Excel może automatycznie formatować nowe dane wpisywane na końcu zakresu, tak aby były zgodne z poprzednimi wierszami. Program Excel może też automatycznie kopiować formuły powtarzające się w poprzednich wierszach i stosować je do dodatkowych wierszy.

 Uwaga   Aby formaty i formuły można było zastosować do nowych wierszy w zakresie, te formaty i formuły muszą występować w co najmniej trzech z pięciu poprzednich wierszy.

Tę opcję można włączyć (lub ponownie wyłączyć) w dowolnym momencie:

  1. Kliknij przycisk Microsoft Office Obraz przycisku, kliknij przycisk Opcje programu Excel, a następnie kliknij kategorię Zaawansowane.
  1. W sekcji Edycja zaznacz pole wyboru Rozszerz formaty i formuły zakresu danych, aby włączyć tę opcję. Aby ponownie wyłączyć automatyczne formatowanie zakresu danych, wyczyść to pole wyboru.

Odświeżanie danych zewnętrznych     Odświeżając dane zewnętrzne, uruchamia się kwerendę w celu pobrania wszelkich nowych lub zmienionych danych, które odpowiadają określonej charakterystyce. Można odświeżyć kwerendę zarówno w programie Microsoft Query, jak i w programie Excel. Program Excel zapewnia kilka opcji odświeżania kwerend, w tym odświeżanie danych przy każdym otwarciu skoroszytu i automatyczne odświeżanie danych w określonych odstępach. Podczas odświeżania danych można kontynuować pracę w programie Excel, a także sprawdzać stan. Aby uzyskać więcej informacji, zobacz Odświeżanie połączonych (zaimportowanych) danych.

Początek strony Początek strony

 
 
Dotyczy:
Excel 2007