Start > Kursy on-line > Excel - kurs zaawansowany

Excel - kurs zaawansowany


Edycja zapytania w programie MS Query i parametryzacja

Załącznik z przykładami dla tego rozdziału znajduje się w tym miejscu.

Załącznik z przykładami dla tego rozdziału znajduje się w tym miejscu.

Najlepiej zapisać je na dysk komputera, a następnie wykorzystując je, śledzić zawartość tej strony. Najwygodniej jest pobrać przede wszystkim plik z bazą danych (ten drugi), a ćwiczenie przeprowadzać na pustym arkuszu Excela.

Obecnie będziemy edytować kwerendę, którą stworzyliśmy w poprzednim rozdziale. W tym celu użyjemy polecenia Odśwież z menu kontekstowym:




Przechodzimy wszystkie kroki kreatora, aż do ostatniego:



Tym razem wybieramy drugie polecenie, które umożliwi edycję kwerendy w programie MS Query. Po jego wybraniu, otworzy się program - zawierający aktualne dane:



W oknie programu MS Query mamy widoczne następujące elementy:
Menu programu - zlokalizowane na samej górze (cześć poleceń znajduje się też w formie przycisków poniżej menu). Udostępnia wszystkie polecenia, które są możliwe do zrealizowania przez tę aplikację.
Okno kwerendy - okno z konkretnymi wynikami bieżącej kwerendy oraz tabelami, na bazie których powstała kwerenda. W momencie kiedy wybierzemy w menu Widok polecenie Kryteria wówczas wyświetli się dodatkowa część w oknie kwerendy, która będzie prezentować założone filtry:




Dzięki czemu okno będzie wyglądać w taki sposób (dodatkowy element zaznaczony na czerwono):



Wykorzystanie przycisku SQL:



pozwoli natomiast na wyświetlenie i ewentualną modyfikację kwerendy przy użyciu tego języka:



My natomiast skupimy się w tym miejscu na dwóch podstawowych zagadnieniach: modyfikacja kwerendy oraz jej parametryzacja:

Modyfikacja: dodawanie/usuwanie pól

Do widocznej kwerendy możemy dodać kolejne pola. Jeśli klikniemy dwukrotnie w żądane pole na wyświetlanej liście (u nas będzie to pole Cena):



Spowodujemy dodanie tej informacji do kwerendy (jak też przedstawiono powyżej). W inny sposób natomiast usuwamy pola z kwerendy:



Zaznaczając określone pole (kliknięciem w jego nagłówek - jak powyżej), wystarczy nacisnąć klawisz Delete klawiatury, aby zniknęło ono z kwerendy.

W przypadku kiedy chcielibyśmy dodać inną tabelę, aby jej pola też były dostępne należy użyć polecenia Tabela a następnie Dodaj tabelę:




W polu dialogowym, które się wyświetli, będziemy mogli zaznaczyć wybraną tabelę i klikając przycisk Dodaj spowodujemy, że będzie ona dostępna dla wybierania z niej pól do kwerendy.

Parametry

Program MS Query pozwala nam na zakładanie parametrów dla tworzonych kwerend. Dzięki temu możemy filtrować dane już na etapie pobierania ich ze źródła.
Co ważne: program nie potrafi przekazać parametrów założonych na zapytanie stworzone w innym programie takim jak np. MS Access pomimo, że jako źródło danych może służyć także właśnie kwerenda tego programu. W takiej sytuacji należy utworzyć w zewnętrznym programie kwerendę bez parametrów, zdefiniować ją jako źródło danych programu MS Query, a następnie w nim dopiero założyć odpowiednie filtry - tu nazwane Kryteriami:




Wyświetli się okno, w którym dokonujemy następujących wyborów:
Obliczenia - używamy w przypadku, kiedy nasze warunki mają dotyczyć wartości (i ich np. sum).
Pole - oznacza pole, które będziemy ograniczać, na które będziemy zakładać filtr
Operator - dotyczy warunku dla podanego w poprzednim punkcie pola.
Wartość - jaki jest konkretny warunek brzegowy ograniczanego pola.

W naszym przypadku założymy warunek, który będzie mówił, że interesuje nas tylko sprzedaż z miesiąca 4 (kwietnia). Okno będzie więc wyglądać następująco:




Kliknięcie przycisku "Dodaj" - spowoduje pojawienie się tego warunku na ich liście poniżej:



Kolejne zmiany pól i warunków zakończone kliknięciem w przycisk "Dodaj" spowodują dodawanie się kolejnych kryteriów do kwerendy. Po zakończeniu tego procesu, możemy nacisnąć przycisk "Zakończ" zamykający okno kryteriów.
W tym momencie zwykłe zamknięcie programu MS Query (krzyżykiem w prawym górnym rogu) spowoduje, że w arkuszu Excel pojawią się dane przefiltrowane:




Wszystko to piękne, ale mało praktyczne zarazem. O ile wygodniej byłoby założyć parametr, który może być zmieniany dynamicznie tak, aby przy każdej zmianie nie trzeba było wchodzić do programu MS Query.
Właśnie teraz się zajmiemy przygotowaniem takiego parametru:

Wejdźmy ponownie do trybu edycji kwerendy w programie MS Query i w miejscu czwórki, która służyła za kryterium wpiszmy w kwadratowym nawiasie [Podaj miesiąc], a następnie kliknijmy klawisz Enter:




Pojawi się okno, wyświetlające przed chwilą wpisany tekst, który jest tak naprawdę komunikatem wołającym o podanie kryterium dynamicznego - to kwadratowe nawiasy sugerują programowi, że w tym miejscu kryterium ma być oparte o dynamiczny parametr:



Możemy w pole tego okna wpisać jakąkolwiek poprawną liczbę (symbolizującą miesiąc w roku), aby zmieniać dynamicznie wyniki. Po zamknięciu okna programu MS Query, kolejne odświeżanie danych będzie powodowało wyświetlenie się okna, które zapyta o wartość kryterium jaka ma być przyjęta:



Na koniec, krótko pokażemy jak jeszcze bardziej udoskonalić naszą parametryzację: zdefiniujemy parametr tak, że kwerenda będzie sama się odświeżała, po zmienieniu podanej wartości w komórce do której podłączymy parametr.
Aby to uczynić, musimy użyć menu kontekstowego kwerendy i wybrać polecenie Parametry.:




Wówczas na liście z lewej strony ujrzymy nasz parametr (gdyby było ich więcej, to wszystkie dotyczące tej kwerendy byłyby tu wyświetlone), który możemy wybrać i zaznaczyć dla niego dwie opcje: Pobierz wartość z następującej komórki, co zwiąże nasz parametr z podaną komórką arkusza oraz Odśwież automatycznie, gdy zmienia się wartość komórki - co spowoduje odświeżenie kwerendy z podanym w powyższej komórce parametrem (kryterium).

W naszym przypadku wpisanie jakiejkolwiek wartości w komórkę H1 będzie odświeżać kwerendę:




Przy czym jeśli użyjemy wartości nieprawidłowej (np. 13) kwerenda nie zwróci żadnych wyników: