Kwerendy

Przykład opisany w tej lekcji należy wykonać w nowym pustym skoroszycie Excela, przed przystąpieniem do jego wykonywania należy zapisać na swoim komputerze plik BazaDanych.mdb, Tylko samodzielne wykonanie przykładu daje gwarancję zapamiętania tej lekcji.

Przykłady dla wszystkich lekcji szkolenia Excel 2013: ExcelSzkolenie.pl Cwiczenia Excel 2013.zip

Kwerendy służą do pobierania danych do arkusza Excela z bazy danych.

Nie należy o nich myśleć jako o sposobie kopiowania danych z bazy do Excela, ponieważ oferują znacznie więcej możliwości: filtrowanie, sortowanie, sumowanie i inne operacje na danych, umożliwiają także łatwe odświeżenie danych lub zmianę kryteriów kwerendy w każdej chwili.

Przykład 1.

Excel oferuje kilka możliwości pracy z danymi zewnętrznymi. Najprostszy z nich to import całej tabeli danych, dokładnie w takiej formie w jakiej jest w bazie danych.

Na karcie ‘DANE’ klikamy ikonę ‘Z programu Access’.

W kolejnym oknie wskazujemy miejsce zachowania pliku BazaDanych.mdb.

Po czym wybieramy jedną z tabel bazy danych i klikamy OK.

Import danych zostanie zakończony po wskazaniu miejsca w arkuszu w którym mają być wstawione dane zewnętrzne.

Tabela zostanie wstawiona do arkusza. Jeżeli aktywna jest komórka wewnątrz tabeli wyświetlana jest karta ‘Projektowanie’.

Najczęściej używane polecenia tej karty to:

- ‘Odśwież’, dzięki któremu możemy uaktualnić dane gdy wiemy, że w bazie danych nastąpiły zmiany.

- ‘Rozłącz’ który przerywa połączenie pomiędzy danymi i ich źródłem (ikona:).

Przykład 2.

Znacznie więcej możliwości oferują kwerendy.

Aby wstawić kwerendę z karty ‘Dane’ wybieramy ‘D zewnętrzne’, po czym klikamy ‘Z innych źródeł’ i wybieramy ‘Z programu Microsoft Query’.

W oknie ‘Wybierz źródło danych’ dwukrotnie klikamy ‘MS Access DataBase’.

W kolejnym oknie wskazujemy, gdzie zapisaliśmy plik BazaDanych.mdb

W oknie kreatora kwerend wybieramy kolumny, które chcielibyśmy pokazać w kwerendzie.

Klikając symbol + pokazujemy nazwy kolumn znajdujące się w wybranej tabeli (w poniższym przykładzie ‘Produkty’ i ‘Sprzedaż’ to nazwy tabel, pozostałe nazwy to kolumny występujące w tych tabelach).

Możemy też wybrać tylko jedną kolumnę i przejść dalej, a ostatecznego wyboru dokonać w Microsoft Query.

Kreator kwerend umożliwia podstawowe opcje filtrowania, w tym przykładzie przejdziemy dalej ponieważ MS Query, do którego przejdziemy, daje więcej możliwości.

W kolejnym oknie mamy możliwość sortowania danych, nie będziemy z niej korzystać, klikamy ‘Dalej>’.

Jeżeli kwerenda przez nas przygotowywana jest prosta, możliwości Kreatora kwerend mogą okazać się wystarczające i w tym momencie możemy zakończyć jej przygotowywanie, wybierając w oknie ‘Kreator kwerend – koniec’ pierwszą opcję (‘Zwróć dane do programu Microsoft Office Excel’) i klikając ‘Zakończ’.

Dane są zrzucane do Excela i przygotowywanie kwerendy jest zakończone.

Gdy wybierzemy drugą opcję (‘Wyświetlić dane lub edytować kwerendę w programie Microsoft Querry’) i klikniemy ‘Zakończ’, zostanie uruchomiony program Microsoft Query, który możemy określić jako zubożoną wersję Microsoft Access.

Osoby znające Access nie powinny mieć problemu w jego obsłudze, poniżej opisuje jego funkcje dla pozostałych osób.

W górnej części okna ‘Kwerenda z MS Access Database’ pokazane są 2 wybrane tabele: ‘Produkty’ oraz ‘Sprzedaż’ oraz nazwy kolumn jakie się w nich znajdują (‘data_wprowadzenia’, nazwa_produktu’, ‘Opis’ w tabeli ‘Produkty i ‘miesiąc’, ‘nazwa_produktu’, ‘Numer’, ‘sprzedaż’ w tabeli ‘Sprzedaż’).

Możemy dodać dodatkowe tabele klikając na ikonę zaznaczoną na poniższym rysunku.

Należy pamiętać, że kwerenda wybierze dane właściwie, tylko jeśli wszystkie tabele, z których korzystamy są powiązane, na poniższym rysunku powiązanie takie widać jako linię łączącą obie tabele.

Jeżeli chcemy pokazać dane z tabel nie połączonych należy znaleźć tabele pośrednie, które będą stanowić połączenie pomiędzy wybranymi.

Baza danych w tym przykładzie jest wyjątkowo prosta, zawiera tylko 2 tabele, nie będziemy więc korzystać z tego polecenia.

Aby dodać kolejną kolumnę do naszej tabeli dwukrotnie klikamy jej nazwę w górnej części okna.

Dodajmy kolumnę ‘data_wprowadzenia’ z tabeli ‘Produkty’.

Po tym jak ‘data_wprowadzenia’ pojawi się jako ostatnia kolumna kwerendy (w dolnej części ekranu), zaznaczamy tą kolumnę klikając na jej nagłówek i wciskamy przycisk ‘Delete’ (na klawiaturze), w celu usunięcia tej kolumny z kwerendy.

Przygotowywanie kwerend w ‘Microsoft Query’ nie ingeruje w bazę danych, możemy być spokojni kolumna data_wprowadzenia, jak i jej zawartość nie zostaną skasowane z bazy danych.

Zaznaczone poniżej polecenie ‘Pokaż/Ukryj kryteria’ dodaje kolejny element do okna ‘Kwerenda z MS Access Database’.

W wierszu ‘Pole kryterium’ wybieramy której kolumny ma dotyczyć kryterium filtrowania, wybierzmy kolumnę ‘sprzedaż’ z tabeli ‘sprzedaż’.

Poniżej w wierszu ‘Wartość’ wpisujemy warunek filtrowania np. >120

Po zatwierdzeniu wpisanego kryterium wciśnięciem klawisza Enter, lista w dolnej części okna zostaje przefiltrowana.

Kryteria możemy usunąć zaznaczając je i wciskając klawisz ‘Delete’ lub korzystając z polecenia ‘Usuń wszystkie kryteria’.

Przycisk pozwala nam zobaczenie kwerendę, którą utworzyliśmy zapisaną w języku SQL, jeżeli znamy ten język możemy dokonywać zmian bezpośrednio w poniższym zapisie.

Bardziej zaawansowanych kwerend nie da się utworzyć w trybie graficznym więc dla osób chcących na poważnie zajmować się bazami danych znajomość SQL jest nieodzowna.

Usuńmy teraz kolumnę z miesiącem. Klikając na jej nagłówek i wciskając przycisk ‘Delete’ z klawiatury.

Przycisk z symbolem sumy umożliwia sumowanie danych, wybieramy kolumnę sprzedaż i klikamy ikonę sumy.

Po pierwszym kliknięciu sumy uzyskamy sumę sprzedaży.

Po kolejnym kliknięciu uzyskamy średnią.

Po trzecim kliknięciu zliczone zostaną miesiące w których produkt był sprzedawany czyli ile razy pozycja spełniająca te kryteria występuje w bazie danych.

Dzięki kolejnym kliknięciom uzyskamy minimum i maksimum, po czym kolumna wraca do sprzedaży.

Powróćmy teraz do poprzedniej wersji kwerendy ponownie dodając miesiąc.

Sortowanie umożliwiają ikony , przed ich kliknięciem należy zaznaczyć kolumnę według której dane mają być sortowane.

Aby zmienić układ kolumn wystarczy zaznaczyć kolumnę do przeniesienia i przeciągnąć ją w wybrane miejsce, podczas przeciągania pokazywana jest gruba linia, dzięki której wiemy, gdzie zostanie przeniesiona kolumna, którą przeciągamy.

UWAGA:

W programie Microsoft Query mamy możliwość cofnięcia tylko jednej ostatnie operacji ‘Cofnij’ wybieramy zgodnie z poniższym rysunkiem, skrót klawiszowy Ctrl+Z, używany w Excelu działa także w tym programie.

Pracę w Microsoft Query kończymy zamykając okno programu poprzez kliknięcie ikony ‘Zamknij’ w prawym górnym rogu okna programu (czerwony X), lub klikając ikonę .

Po zamknięciu Microsoft Query powrócimy do Excela.

Wyświetlone zostanie okno ‘Importowanie danych’, w którym wybieramy już tylko gdzie mają się znaleźć importowane dane.

Komórki kwerendy na pierwszy rzut oka niczym nie różnią się od wartości wprowadzonych z klawiatury do komórek Excela.

W rzeczywistości oferują znacznie więcej możliwości.

Kwerenda nie jest „jednorazowego użytku” w dowolnym momencie możemy ją edytować lub odświeżyć dane.

Dostęp do edycji kwerendy jest dobrze ukryty, aby edytować kwerendę należy kliknąć napis ‘Odśwież wszystko’ na karcie ‘DANE’ i po rozwinięciu się menu wybrać polecenie ‘Właściwości połączenia…’.

W oknie Właściwości połączenia’ przechodzimy na kartę ‘Definicja’ i klikamy przycisk ‘Edytuj kwerendę…’.

Uruchomiony zostaje ‘Kreator kwerend’ w którym możemy dokonać zmian w kwerendzie lub klikając kilkakrotnie ‘Dalej >’ przejść do MS Query, aby w tym programie wykonać zmiany.

Należy pamiętać, że lepiej nie wprowadzać zmian w obszarze kwerendy, jeżeli wprowadzimy zmiany (np. dodamy wiersz a w nim podsumowanie) po wybraniu polecenia ‘Odśwież wszystko’...

...z wprowadzonych zmian pozostanie tylko formatowanie.

A to właśnie możliwość łatwego odświeżania danych jest główną zaletą kwerend w Excelu przemawiającą za ich używaniem.

Moje osobiste preferencje są takie, że wolę kwerendy przygotowywać w programie Access, a dopiero ich ostateczne wyniki eksportować do Excela.

Access posiada znacznie większe możliwości w tym zakresie i jest to program do obsługi baz danych, gdy Excel jest arkuszem kalkulacyjnym z ograniczonymi możliwościami pracy na bazach danych.