Kwerendy

Przykłady opisane w tej lekcji dostępne są w pliku: BazaDanych.mdb, tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji.

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

Przykład 1.

(Przykład należy wykonać w nowym pustym skoroszycie, przed jego wykonaniem należy zachować plik BazaDanych.mdb na swoim komputerze)

Kwerendy służą do łączenia arkusza Excela z bazą 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 np. filtrowanie, sortowanie, sumowanie, umożliwiają także łatwe odświeżenie danych lub zmianę kryteriów kwerendy w każdej chwili.

Aby wstawić kwerendę z menu Dane wybieramy ‘Importuj dane zewnętrzne’, po czym klikamy ‘Nowa kwerenda bazy danych...’.

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).

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ż Microsof 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’.

My jednak zaznaczamy drugą opcję (‘Wyświetlić dane lub edytować kwerendę w programie Microsoft Querry’ i klikamy ‘Zakończ’.

Zostanie uruchomiony program Microsoft Query, który możemy określić jako niepełną wersję Microsoft Access.

Osoby znające Access nie powinny mieć problemu w jego obsłudze, poniżej opisuje jego podstawowe 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 poniżej pokazaną ikonę.

Należy pamiętać, że kwerenda wybierze dane właściwie tylko jeśli wszystkie kolumny 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 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.

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 kwerendy, którą utworzyliśmy zapisanej w języku SQL, jeżeli znamy ten język możemy dokonywać zmian w poniższym zapisie.

Przycisk z symbolem sumy umożliwia sumowanie danych, jeśli nie potrzebujemy danych po miesiącach, a jedynie całkowitą sprzedaż, możemy usunąć kolumnę ‘miesiąc’ z kwerendy i po wybraniu kolumny sprzedaż kliknąć ikonę sumy.

Po pierwszym kliknięciu sumy uzyskamy sumę sprzedaży dla 3 produktów w naszej bazie.

Po kolejnym kliknięciu uzyskamy średnią.

Po trzecim kliknięciu zliczone zostaną miesiące w których produkt był sprzedawany.

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ę wg. 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, skrót klawiszowy Ctrl+Z, używany w Excelu, nie działa w tym programie, ‘Cofnij’ wybieramy zgodnie z poniższym rysunkiem.

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

Po zamknięciu Microsoft Query powrócimy do Excela i wyświetli 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 tych opcji uzyskamy po kliknięciu prawym klawiszem myszy w obszarze kwerendy.

Należy też 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ż dane’...

...z wprowadzonych zmian zostanie tylko formatowanie.

Szkolenie Excel 2003 Szkolenie Excel 2007