Wiemy już, czego od danych oczekujemy, wiemy też, jak powinna wyglądać ich „wzorcowa” organizacja. Rodzi się pytanie, kto lub co może nam te dane zapewnić. W ofercie większości producentów systemów bazodanowych znajdują się moduły eksportu do Excela – to rozwiązanie połowiczne, bo dane przechodzą niestety nie tak, jak my je chcemy widzieć.

Rozwiązaniem może być zawarty w standardzie Excela program MS Query. MS Query to prosty, GRAFICZNY EDYTOR ZAPYTAŃ SQL.

 

Cóż to takiego? – Dowiemy się tego w praktyce – po prostu pobierając dane.

 

Krok 1. Uruchomienie

Z menu „dane” wybieramy opcję „Pobierz dane zewnętrzne” i „Utwórz nową kwerendę bazy danych”

 

Rys.1. Uruchomienie MS Query z menu Excela


Tu może nastąpić problem, polegający na wyświetleniu komunikatu o niezainstalowanym programie MS Query. Jeżeli wystąpi, trzeba go zainstalować, wykonując polecenia kreatora instalacji pakietu Office (sprowadza się to do włożenia odpowiedniej płytki do napędu CDROM)

 

Krok 2. Wybór źródła danych

Warunkiem dalszych kroków jest pojawienie się okna:

 

Rys.2. Okno „Wybierz źródło danych”

 

Aby zacząć posługiwać się programem, wybierzmy „Źródło danych”, odpowiedzialne za dostęp do baz typu DBF (najprostszy pierwszy krok), czyli „Pliki programu dbase” lub „dbase files” (w Excelu 97) WYŁĄCZAJĄC użycie kreatora kwerend (Kreator kwerend jest narzędziem nieprzydatnym i najbardziej awaryjnym z i tak bardzo awaryjnego MS Query).

 

Krok 3. Wybór tabel do kwerendy

W oknie wyboru „Dodaj tabelę” należy wybrać plik, z którego chcemy dane pobrać. W przypadku naszego kursu jest to plik „C:\GazetaPrawna\KursExcela\Dane\Handel\faktura.dbf”

Uwaga: Tabelę należy dodać tylko raz kliknięciem „dodaj”, które może nie zmienić nic na ekranie (Jeżeli okno „dodaj tabelę” przysłania obszar tabel programu – można je odsunąć. Gdy pomyłkowo – a jest to błąd standardowy – tabelę dodamy dwa razy, można ją w każdej chwili usunąć z kwerendy, zaznaczając myszką i naciskając [Delete]).

 

Rys.3. Okno „Dodaj tabelę”

Tabela dodana – musimy teraz określić, co chcemy z niej uzyskać. Dwukrotnie klikamy na „*” (pierwsza pozycja na liście pól w tabeli), co oznacza „wybierz wszystkie pola tabeli” do obszaru danych (poniżej). Na ekranie widzimy:

 

Rys.4. Widok kwerendy

 

Krok 3a. Podgląd stworzonego zapytania SQL (opcja)

Ciekawą opcją jest opcja podglądu stworzonego zapytania - zdania w języku SQL - służy do tego celu ikona  obok ikony zwrotu danych – po jej naciśnięciu otrzymamy pierwsze napisane przez nas zapytanie w języku SQL (Structured Query Language) – GRATULUJEMY!

 

Rys.5. Okno „SQL” – gotowy tekst zapytania w języku SQL

 

Powyższe, gotowe zapytanie możemy stąd wykopiować!

 

Krok 4. Zwrócenie danych do Excela

Wystarczy teraz kliknąć na ikonę „zwróć dane”  i po wskazaniu miejsca, gdzie dane należy umieścić, dane pojawiają się w Excelu, a ich obszar jest nazwany (zwykle „Kwerenda z ...). Kwerendę można powtórnie edytować, wybierając opcję edycji pod prawym przyciskiem myszy. Dane można odświeżać, klikając narzędzie  na pasku narzędzi lub wybierając odp. opcję albo w menu, albo pod prawym przyciskiem myszy.

 

Rys.6. Arkusz z danymi, zwróconymi przez MS Query

 

Na kursach polecamy w tym momencie kilkakrotne uruchomienie kwerendy, tak, aby te kroki zapamiętać. MS Query jest programem zawodnym i, chcąc nie chcąc, będziemy zmuszeni uruchamiać go wielokrotnie.

 

Tu wydaje się być uzasadniona pewna dygresja, która niewątpliwie ma wpływ na wykorzystanie MS Query. Nie polecamy tego programu jako profesjonalnego rozwiązania docelowego – „pewna” w tym programie jest jedynie jego zawodność. Gdy się zawiesi, nie warto dochodzić przyczyny – uruchamiamy go ponownie. Dlaczego więc się go uczymy? Bo jest to najlepszy „pierwszy krok” – najprostszy, graficzny i dość wszechstronny edytor SQL, a przy tym umożliwia wykopiowywanie, stworzonych zdań SQL do innych edytorów, np. do funkcji SQL.Request, o której w następnym odcinku. Nauczmy się więc i … zapomnijmy o MS Query!

 

Tak więc, osiągnęliśmy nasz cel – potrafimy pobrać dane do Excela.

Co jeszcze potrafi MS Query? Jak się okazuje, dość dużo:

  1. Pobierać dane z różnych formatów (tu: ze skoroszytu Excela i bazy MS Access)
    W kroku 2. : Pliki programu Excel - „C:\GazetaPrawna\KursExcela\Dane\Handel\HandelExcel.xls” – (tabela) faktura, dalej należy postępować analogicznie do procedury opisanej powyżej – lub
    W kroku 2. : Baza Danych MS Access - „C:\GazetaPrawna\KursExcela\Dane\Handel\HandelAccess.mdb” – i dalej podobnie.

 

Uwaga: W ten sposób pokazaliśmy, że wybór danych nie zależy od ich formatu, a wyłącznie od sposobu ich wewnętrznej organizacji w źródle danych. Czy dane są w formacie DBF, Excela, bazy Accessa, bazy Oracle, SQL Server, czy nawet zwykłego pliku tekstowego (!) – od dzisiaj nie ma to dla nas znaczenia!

 

  1. Dokonywać wyboru tylko interesujących nas danych z baz o znacznej ilości rekordów (np. tylko kont kosztowych z całej obrotówki) – tzw. kwerenda wybierająca
    Dodajemy kryteria wyboru do naszej kwerendy. Dochodzimy do Kroku 4. i dalej z menu Kryteria:Dodaj Kryteria
    (Zrzut ekranu przedstawia w oknie „Dodaj kryteria sposób definicji kryteriów wyboru, a pod nim, w oknie kwerendy, dane PO kliknięciu przycisku „Dodaj” czyli już po wyborze żądanych rekordów.)

 

Rys.7. Dodawanie kryteriów do kwerendy.

Dalej: Zamykamy okno „Dodaj kryteria” i przesyłamy dane do Excela

 

  1. Łączyć tabele, odtwarzając relację pomiędzy nimi.
    To prostsze niż myślisz! Dochodzimy do kroku 4., z menu Tabela:Dodaj tabelę, dodajemy kolejną tabelę do zapytania – tabelę odbiorca, zamykamy okno dodawania tabel, chwytamy myszą pole nazwa w tabeli faktura i „przeciągamy” na pole nazwa w tabeli odbiorca. Pojawia się łącznik między tabelami. Wystarczy jeszcze dwukrotnie kliknąć „*” w tabeli odbiorca, aby w naszym zapytaniu znalazły się wszystkie pola z obu tabel, połączonych relacją po polu nazwa.

 

Rys.8. Tworzenie relacji pomiędzy tabelami.

 

Tak stworzone dane nazywamy „szeroką tabelą”, czyli tabelą z danymi z dwóch lub więcej tabel, połączonych relacjami.

 

  1. Agregować informację, zanim zostanie ona przesłana do Excela.
    Ranking kontrahentów (nie faktur, bo to zwykłe sortowanie, ale posortowanie kontrahentów według sum ich faktur)? To również proste!
    Dochodzimy do kroku 4. i usuwamy z obszaru danych wszystkie pola, poza polami nazwa i wart_net (poprzez zaznaczenie myszą nagłówka pola i naciśnięcie klawisza [Delete]). Następnie dwukrotnie klikamy myszą nagłówek pola wart_net i, gdy pojawi się okno „Edytuj kolumnę”, wybieramy w polu Obliczenia pozycję „Suma” i naciskamy „OK”. Nagłówek kolumny zmienia się na „Suma z wart_net” – rysunek poniżej przedstawia sytuację już PO naciśnięciu „OK” w polu „Edytuj kolumnę”. Pozostaje nam jeszcze posortować dane malejąco  po polu „Suma z WART_NET” i ranking gotowy. Wysyłamy go do Excela.

 

Rys.9. Pole wyboru „Edytuj kolumnę”



  1. Automatyzować import danych dla narzędzi analitycznych, np. autofiltru lub tabel przestawnych.
    Tworzymy kwerendę (najlepiej z relacjami, ale raczej bez wyborów pól i kryteriów), wysyłamy ją do Excela – tak jak w krokach 1-4 na wstępie. Następnie zaznaczamy CAŁY obszar zwróconej kwerendy i budujemy na tym obszarze tabelę przestawną – patrz: poprzedni odcinek naszego kursu. Gdy tabela jest gotowa, możemy w każdej chwili dane odświeżać – najpierw kwerendę, wybierając opcję Odśwież dane pod prawym przyciskiem myszy (ustawionej na obszarze kwerendy), a następnie samą tabelę przestawną, również wybierając opcję Odśwież dane pod prawym przyciskiem myszy (ustawionej na obszarze tabeli przestawnej).

 

  1. Grupować lub atrybutować dane przed ich przesłaniem do Excela.
    To zadanie trudne, już z pogranicza informatyki, ale... warto! Np. w celu automatycznego stworzenia pola „kombi”, o którym mowa w poprzednim odcinku kursu.
    Dochodzimy do kroku 4., i, już w samej kwerendzie, klikamy na pierwszym wolnym pustym polu, na prawo od pola VAT. Wpisujemy tam ręcznie tekst: data&nazwa i naciskamy [Enter]

    Rys.10. Tworzenie pola „kombi” w kwerendzie.


Musimy jeszcze tylko zmienić nazwę pola (nagłówek kolumny) na np. „kombi” (patrz: rys. Okno „Edytuj kolumnę”) i wysyłamy dane do Excela.
W dowolnym miejscu arkusza możemy teraz wpisać funkcję =Suma.Jeżeli(...), aby wyszukiwać i sumować dane, wyszukane po dwóch (lub więcej!) polach. Na rysunku przedstawiono sumę faktur z 2004-01-03 dla klientów o nazwach, zaczynających się na literę „B”.

 

Rys.11. Funkcja, sumująca dane na polu „kombi”, stworzonym kwerendą MS Query.

 

  1. Pobierać dane z Internetu (!), wybierając w kroku 1. „Utwórz kwerendę sieci Web” – opcja rzadko używana, ale warto spróbować, np. na „www.gielda.onet.pl”

Podsumowanie:

W każdym Excelu, w jego standardzie, od wersji 97 w górę, znajduje się narzędzie, umożliwiające pobór danych z zewnętrznych źródeł danych – MS Query. Jest to narzędzie dość uniwersalne i wręcz idealne do pierwszych kroków. Jego zawodność stanowi pewną wadę, ale gdy umiemy się jej wystrzegać, tworząc tylko proste kwerendy, uzyskaliśmy poważne narzędzie, eliminujące standardowe wpisywanie danych do Excela.

 

Więcej o MS Query:

  1. Pomoc MS Query
  2. W.Gardziński „MS Query”, Controlling i Rachunkowość Zarządcza, nr 6/2001

 

Test sprawdzający:

  1. Przenieść do arkusza Excela (zaimportować dane) tabelę towar, najpierw z pliku towar.dbf, potem z plików HandelExcel.xls i HandelAccess.mdb.
  2. Połączyć relacją tabele faktura i odbiorca oraz (już w Excelu), wybrać autofiltrem dane faktur, dotyczących klientów z Wrocławia.
  3. Sporządzić kwerendę z tabel faktura i pozycja i zbudować na niej tabelę przestawną, prezentującą dane w układzie klient(nazwa) X towar (indeks towaru).