Za pomocą programu Microsoft Query można pobierać dane ze źródeł zewnętrznych. Dzięki pobieraniu danych z firmowych baz danych i plików za pomocą programu Microsoft Query nie trzeba ponownie wpisywać danych, które mają być analizowane w programie Excel. Można także automatycznie odświeżać raporty i podsumowania programu Excel z oryginalnej źródłowej bazy danych po każdej aktualizacji bazy danych przez wprowadzenie nowych informacji.
Więcej informacji o programie Microsoft Query
Za pomocą programu Microsoft Query można łączyć się z zewnętrznymi źródłami danych, wybierać dane z tych źródeł, importować je do arkusza i odświeżać stosownie do potrzeb, aby dane arkusza były zsynchronizowane z danymi w źródłach zewnętrznych.
Typy baz danych, do których można uzyskać dostęp Można pobierać dane z kilku typów baz danych, takich jak bazy danych programu Microsoft Office Access, Microsoft SQL Server i usług Microsoft SQL Server OLAP. Można również pobierać dane ze skoroszytów programu Excel i plików tekstowych.
Pakiet Microsoft Office udostępnia sterowniki, za pomocą których można pobierać dane z następujących źródeł danych:
- Microsoft SQL Server Analysis Services (dostawca OLAP)
- Microsoft Office Access
- dBASE
- Microsoft FoxPro
- Microsoft Office Excel
- Oracle
- Paradoks
- Bazy danych plików tekstowych
Informacje ze źródeł danych, których tu nie wymieniono, w tym także ze sterowników ODBC lub sterowników źródeł danych innych producentów, można także używać sterowników ODBC lub sterowników źródeł danych innych producentów. Aby uzyskać informacje na temat instalowania sterownika ODBC lub sterownika źródła danych, którego tu nie wymieniono, należy zapoznać się z dokumentacją bazy danych lub skontaktować się z dostawcą tej bazy.
Wybieranie danych z bazy danych Aby pobrać dane z bazy danych, należy utworzyć zapytanie, czyli pytanie dotyczące danych przechowywanych w zewnętrznej bazie danych. Jeśli na przykład dane są przechowywane w bazie danych programu Access, może być przydatna znajomość danych sprzedaży określonego produktu w regionie. Część danych można odzyskać, wybierając tylko dane dla produktu i regionu, które mają zostać przeanalizowane.
Za pomocą programu Microsoft Query można zaznaczyć kolumny danych i zaimportować tylko te dane do programu Excel.
Aktualizowanie arkusza za pomocą jednej operacji Gdy masz już dane zewnętrzne w skoroszycie programu Excel, po każdej zmianie w bazie danych możesz je odświeżać w celu zaktualizowania analizy — bez konieczności ponownego tworzenia raportów podsumowujących i wykresów. Można na przykład utworzyć miesięczne podsumowanie sprzedaży i odświeżać je co miesiąc, gdy pojawią się nowe wyniki sprzedaży.
Jak program Microsoft Query używa źródeł danych Po skonfigurowaniu źródła danych dla określonej bazy danych można z niego korzystać w celu utworzenia kwerendy wybierającej i pobierającej dane z tej bazy danych — bez konieczności ponownego wpisywania wszystkich informacji o połączeniu. Program Microsoft Query używa tego źródła danych do łączenia się z zewnętrzną bazą danych i wyświetlania dostępnych danych. Po utworzeniu zapytania i zwróceniu danych do programu Excel program Microsoft Query udostępnia skoroszyt programu Excel zarówno informacje o zapytaniu, jak i o źródle danych, dzięki czemu w razie potrzeby można ponownie połączyć się z bazą danych.
Importowanie danych za pomocą programu Microsoft Query Aby zaimportować dane zewnętrzne do programu Excel za pomocą programu Microsoft Query, wykonaj poniższe podstawowe czynności, z których każda została opisana bardziej szczegółowo w kolejnych sekcjach.
Nawiązywanie połączenia ze źródłem danych
Co to jest źródło danych? Źródło danych to przechowywany zestaw informacji, który umożliwia programom Excel i Microsoft Query łączenie się z zewnętrzną bazą danych. Konfigurując źródło danych za pomocą programu Microsoft Query, podajesz nazwę źródła danych, a następnie podajesz nazwę i lokalizację bazy danych lub serwera, typ bazy danych, a także informacje dotyczące logowania i hasła. Informacje zawierają również nazwę sterownika OBDC lub sterownika źródła danych, czyli programu nawiązującego połączenia z określonym typem bazy danych.
Aby skonfigurować źródło danych przy użyciu programu Microsoft Query:
Na karcie Dane w grupie Pobieranie danych zewnętrznych kliknij pozycję Z innych źródeł, a następnie kliknij pozycję Z programu Microsoft Query.
Uwaga
Program Excel 365 przeniósł aplikację Microsoft Query do grupy menu Starsi kreatorzy . Domyślnie to menu nie jest wyświetlane. Aby włączyć tę funkcję, przejdź do pozycji Plik, Opcje, Dane i włącz w sekcji Pokaż starszych kreatorów importu danych .
Wykonaj jedną z następujących czynności:
- Aby określić źródło danych dla bazy danych, pliku tekstowego lub skoroszytu programu Excel, kliknij kartę Bazy danych .
- Aby określić źródło danych modułu OLAP, kliknij kartę Moduły OLAP . Ta karta jest dostępna tylko w przypadku uruchomienia programu Microsoft Query z poziomu programu Excel.
Kliknij <dwukrotnie pozycję Nowe źródło> danych.
— lub —
Kliknij <pozycję Nowe źródło> danych, a następnie kliknij przycisk OK.
Zostanie wyświetlone okno dialogowe Tworzenie nowego źródła danych .W kroku 1 wpisz nazwę identyfikującą źródło danych.
W kroku 2 kliknij sterownik dla typu bazy danych, której używasz jako źródła danych.
Uwaga
- Jeśli zewnętrzna baza danych, do której użytkownik chce uzyskać dostęp, nie jest obsługiwana przez sterowniki ODBC zainstalowane z programem Microsoft Query, należy uzyskać i zainstalować sterownik ODBC zgodny z pakietem Microsoft Office od innego dostawcy, na przykład producenta bazy danych. Aby uzyskać instrukcje instalacji, skontaktuj się z dostawcą bazy danych.
- Bazy danych OLAP nie wymagają sterowników ODBC. Podczas instalacji programu Microsoft Query są instalowane sterowniki dla baz danych utworzonych przy użyciu programu Microsoft SQL Server Analysis Services. Aby nawiązać połączenie z innymi bazami danych OLAP, należy zainstalować sterownik źródła danych i oprogramowanie klienckie.
Kliknij przycisk Połącz, a następnie podaj informacje niezbędne do połączenia się ze źródłem danych. W przypadku baz danych, skoroszytów programu Excel i plików tekstowych podawane informacje zależą od wybranego typu źródła danych. Może pojawić się monit o podanie nazwy logowania, hasła, wersji używanej bazy danych, lokalizacji bazy danych lub innych informacji właściwych dla typu bazy danych.
Ważne
- 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.
- 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.
Po wprowadzeniu wymaganych informacji kliknij przycisk OK lub przycisk Zakończ , aby powrócić do okna dialogowego Tworzenie nowego źródła danych .
Jeśli baza danych zawiera tabele i chcesz, aby konkretna tabela była automatycznie wyświetlana w Kreatorze kwerend, kliknij pole kroku 4, a następnie kliknij odpowiednią tabelę.
Jeśli nie chcesz wpisywać nazwy użytkownika i hasła podczas korzystania ze źródła danych, zaznacz pole wyboru Zapisz mój identyfikator użytkownika 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, aby dowiedzieć się, czy można udostępnić tę opcję.
Uwaga
Unikaj zapisywania informacji logowania podczas łączenia się ze źródłami danych. Informacje te mogą być przechowywane w postaci zwykłego tekstu, a dostęp do nich może uzyskać złośliwy użytkownik, aby zagrozić bezpieczeństwu źródła danych.
Po wykonaniu tych czynności nazwa źródła danych zostanie wyświetlona w oknie dialogowym Wybieranie źródła danych .
Definiowanie zapytania za pomocą Kreatora zapytań
W przypadku większości zapytań używaj Kreatora zapytań Kreator kwerend ułatwia wybieranie i łączenie danych z różnych tabel i pól w bazie danych. Za pomocą Kreatora zapytań możesz wybrać tabele i pola, które chcesz uwzględnić. Sprzężenie wewnętrzne (operacja zapytania określająca, że wiersze z dwóch tabel są łączone na podstawie identycznych wartości pól) jest tworzone automatycznie, gdy kreator rozpozna pole klucza podstawowego w jednej tabeli i pole o tej samej nazwie w drugiej tabeli.
Za pomocą kreatora możesz też posortować zestaw wyników i wykonać proste filtrowanie. W ostatnim kroku kreatora można wybrać zwrócenie danych do programu Excel lub dalsze uściślenie zapytania w programie Microsoft Query. Utworzone zapytanie można uruchomić w programie Excel lub Microsoft Query.
Aby uruchomić Kreatora zapytań, wykonaj następujące czynności.
- Na karcie Dane w grupie Pobieranie danych zewnętrznych kliknij pozycję Z innych źródeł, a następnie kliknij pozycję Z programu Microsoft Query.
- Upewnij się, że w oknie dialogowym Wybieranie źródła danych jest zaznaczone pole wyboru Użyj Kreatora kwerend do tworzenia/edytowania zapytań .
- 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 innych typów zapytań Jeśli chcesz utworzyć bardziej złożone zapytanie, niż jest to dozwolone przez Kreatora zapytań, możesz pracować bezpośrednio w programie Microsoft Query. Za pomocą programu Microsoft Query można wyświetlać i zmieniać zapytania rozpoczęte w Kreatorze zapytań lub tworzyć nowe zapytania bez korzystania z kreatora. Pracując bezpośrednio w programie Microsoft Query, jeśli chcesz tworzyć zapytania, które wykonują następujące zadania:
- Wybieranie określonych danych z pola W przypadku dużej bazy danych może być konieczne wybranie części danych w polu, a pominięcie danych, które nie są potrzebne. Jeśli na przykład potrzebujesz danych dla dwóch produktów w polu zawierającym informacje o wielu produktach, możesz użyć kryteriów, aby wybrać dane tylko dla dwóch produktów, które chcesz uzyskać.
- Pobieranie danych na podstawie innych kryteriów przy każdym uruchomieniu zapytania Jeśli trzeba utworzyć ten sam raport programu Excel lub podsumowanie dla kilku obszarów w tych samych danych zewnętrznych — na przykład oddzielny raport sprzedaży dla każdego regionu — możesz utworzyć zapytanie parametryczne. Po uruchomieniu zapytania parametrycznego podczas wybierania rekordów przez zapytanie jest wyświetlany monit o podanie wartości, która ma służyć jako kryterium. Na przykład zapytanie parametryczne może wyświetlić monit o wprowadzenie określonego regionu i może być ponownie użyte to zapytanie do utworzenia każdego regionalnego raportu sprzedaży.
- Łączenie danych na różne sposoby Sprzężenia wewnętrzne tworzone przez Kreatora zapytań to najczęściej używany typ sprzężenia podczas tworzenia zapytań. Czasem jednak może być konieczne użycie innego typu sprzężenia. Jeśli na przykład jest dostępna tabela zawierająca dane sprzedaży produktów oraz tabelę informacji o klientach, sprzężenie wewnętrzne (typ utworzony przez Kreatora zapytań) uniemożliwi pobieranie rekordów klientów, którzy nie dokonali zakupu. Za pomocą programu Microsoft Query można połączyć te tabele w celu pobrania wszystkich rekordów klientów, łącznie z danymi sprzedaży klientów, którzy dokonali zakupów.
Aby uruchomić program Microsoft Query, wykonaj następujące czynności.
- Na karcie Dane w grupie Pobieranie danych zewnętrznych kliknij pozycję Z innych źródeł, a następnie kliknij pozycję Z programu Microsoft Query.
- Upewnij się, że w oknie dialogowym Wybieranie źródła danych nie jest zaznaczone pole wyboru Użyj Kreatora kwerend do tworzenia/edytowania zapytań .
- 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 zapytań Zarówno w Kreatorze kwerend, jak i w programie Microsoft Query można zapisywać zapytania w postaci pliku dqy, który można modyfikować, ponownie używać i udostępniać. W programie Excel można otwierać pliki dqy bezpośrednio, dzięki czemu Ty i inni użytkownicy możecie tworzyć dodatkowe zakresy danych zewnętrznych przy użyciu tego samego zapytania.
Aby otworzyć zapisane zapytanie z programu Excel:
- Na karcie Dane w grupie Pobieranie danych zewnętrznych kliknij pozycję Z innych źródeł, a następnie kliknij pozycję Z programu Microsoft Query. Zostanie wyświetlone okno dialogowe Wybieranie źródła danych .
- W oknie dialogowym Wybieranie źródła danych kliknij kartę Zapytania .
- Kliknij dwukrotnie zapisane zapytanie, które chcesz otworzyć. Zapytanie zostanie wyświetlone w programie Microsoft Query.
Jeśli chcesz otworzyć zapisane zapytanie, a program Microsoft Query jest już otwarty, kliknij menu Plik programu Microsoft Query, a następnie kliknij polecenie Otwórz.
Dwukrotne kliknięcie pliku dqy powoduje otwarcie i uruchomienie zapytania w programie Excel, a następnie wstawienie wyników do nowego arkusza.
Aby udostępnić podsumowanie programu Excel lub raport oparty na danych zewnętrznych, możesz przekazać innym użytkownikom skoroszyt zawierający zakres danych zewnętrznych lub utworzyć szablon. Szablon umożliwia zapisanie podsumowania lub raportu bez zapisywania danych zewnętrznych, przez co plik jest mniejszy. Dane zewnętrzne są pobierane, gdy użytkownik otwiera szablon raportu.
Praca z danymi w programie Excel
Po utworzeniu zapytania w Kreatorze zapytań lub programie Microsoft Query można zwrócić dane do arkusza programu Excel. Dane staną się wówczas zewnętrznym zakresem danych lub raportem w formie tabeli przestawnej, który można formatować i odświeżać.
Formatowanie pobranych danych W programie Excel można używać narzędzi, takich jak wykresy lub automatyczne sumy częściowe, aby przedstawiać i podsumowywać dane pobrane przez program Microsoft Query. Dane można formatować, ale formatowanie jest zachowywane po odświeżeniu danych zewnętrznych. Możesz użyć własnych etykiet kolumn zamiast nazw pól i automatycznie dodać numery wierszy.
Program Excel może automatycznie formatować nowe dane wpisywane na końcu zakresu tak, aby pasowały do poprzednich wierszy. Program Excel może także automatycznie kopiować formuły powtarzane w poprzednich wierszach i rozszerzać je na dodatkowe wiersze.
Uwaga
Aby formaty i formuły mogły obejmować nowe wiersze w zakresie, muszą występować w co najmniej trzech z pięciu poprzedzających wierszy.
Tę opcję można włączyć (lub wyłączyć ponownie) w dowolnym momencie:
- Kliknijpozycję Zaawansowane opcje>pliku>.
- W sekcji Opcje edycji zaznacz pole wyboru Formaty i formuły rozszerz zakres danych . Aby ponownie wyłączyć automatyczne formatowanie zakresu danych, wyczyść to pole wyboru.
Odświeżanie danych zewnętrznych Podczas odświeżania danych zewnętrznych możesz uruchomić zapytanie w celu pobrania wszelkich nowych lub zmienionych danych zgodnych ze specyfikacją. Zapytanie można odświeżyć zarówno w programie Microsoft Query, jak i w programie Excel. W programie Excel jest dostępnych kilka opcji odświeżania zapytań, w tym odświeżanie danych przy każdym otwarciu skoroszytu i automatyczne odświeżanie ich w określonych odstępach czasu. Możesz kontynuować pracę w programie Excel podczas odświeżania danych, a także sprawdzać stan podczas odświeżania danych. Aby uzyskać więcej informacji, zobacz Odświeżanie połączenia danych zewnętrznych w programie Excel.