Tworzenie zapytania parametrycznego

Podczas wysyłania kwerend do danych w programie Excel warto użyć wartości wejściowej-parametru — w celu określenia czegoś dotyczącego kwerendy. W tym celu należy utworzyć zapytanie parametryczne. Sposób tworzenia zapytań parametrycznych oraz ich zachowania zależy od tego, czy korzystasz z programu Microsoft Query, czy z dodatku Power Query.

Porada: Parametry dodatku Power Query różnią się od parametrów używanych w kwerendach opartych na języku SQL. Ponadto można użyć kwerendy zamiast rzeczywistego parametru, jeśli tylko jest to konieczne do filtrowania danych. Przed utworzeniem parametrów w dodatku Power Query warto przeczytać sekcje przykładów dodatku Power Query.

Program Microsoft Query

Power Query

Jak parametry wpływają na kwerendy

Parametry są używane w klauzuli WHERE zapytania — zawsze działają jako filtr dla pobranych danych.

Parametrów można użyć w dowolnym kroku kwerendy. Oprócz działania jako filtr danych można użyć parametrów, aby określić takie elementy, jak ścieżka pliku lub nazwa serwera.

Opcje wprowadzania parametrów

Parametry mogą monitować użytkownika o wartość wejściową, gdy zapytanie jest uruchamiane lub odświeżane, użyj stałej jako wartości wejściowej lub użyj zawartości określonej komórki jako wartości wejściowej.

Parametry nie monitują o wprowadzenie danych. Zamiast tego możesz zmienić jego wartość, korzystając z edytora dodatku Power Query. Lub zamiast parametru dobrej wierze można użyć kwerendy odwołującej się do lokalizacji zewnętrznej z wartością, którą można łatwo edytować.

Zakres parametru

Parametr jest częścią kwerendy, którą modyfikuje i nie można go użyć ponownie w innych kwerendach.

Parametry są niezależne od kwerend po utworzeniu, możesz dodać parametr do zapytań w razie potrzeby.

  1. Kliknij pozycję dane > Pobierz & przekształcania danych > Pobierz dane > z innych źródeł > z programu Microsoft Query.

  2. Postępuj zgodnie z instrukcjami kreatora zapytań. Na ekranie Kreator kwerend — zakończenie wybierz pozycję Wyświetl dane lub Edytuj zapytanie w programie Microsoft Query , a następnie kliknij przycisk Zakończ. Zostanie otwarte okno Microsoft Query zawierające zapytanie.

  3. Kliknij pozycję wyświetl> SQL. W wyświetlonym oknie dialogowym SQL Znajdź klauzulę WHERE — linię zaczynającą się od wyrazu, na przykład na końcu kodu SQL. Jeśli nie ma klauzuli WHERE, Dodaj ją, wpisując miejsce w nowym wierszu na końcu kwerendy.

  4. Po określeniu wpisz nazwę pola, operator porównania (=, <, >, LIKE itd.) i jedno z następujących:

    • W polu rodzajowy monit o podanie parametru wpisz znak zapytania (?). W monicie wyświetlanym po uruchomieniu zapytania nie są wyświetlane pomocne frazy.

      Widok SQL programu MS Query z wyróżnioną klauzulą WHERE

    • W przypadku monitu o podanie parametru, który ułatwia użytkownikom wprowadzanie prawidłowych danych wejściowych, wpisz frazę ujętą w nawiasy kwadratowe. Fraza zostanie wyświetlona w monicie parametru po uruchomieniu zapytania.

      Widok SQL programu MS Query z wyróżnioną klauzulą WHERE

  5. Po zakończeniu dodawania warunków z parametrami do klauzuli WHERE kliknij przycisk OK , aby uruchomić zapytanie. Program Excel wyświetli monit o podanie wartości dla każdego parametru, a program Microsoft Query wyświetli wyniki.

  6. Gdy wszystko będzie gotowe do załadowania danych, Zamknij okno programu Microsoft Query, aby zwrócić wyniki do programu Excel. Zostanie otwarte okno dialogowe Importowanie danych.

    Okno dialogowe Importowanie danych w programie Excel

  7. Aby przejrzeć parametry, kliknij pozycję Właściwości. Następnie w oknie dialogowym właściwości połączenia na karcie Definicja kliknij przycisk Parametry.

    Okno dialogowe właściwości połączenia

  8. W oknie dialogowym Parametry są wyświetlane parametry używane w kwerendzie. Wybierz parametr w obszarze Nazwa parametru , aby przejrzeć lub zmienić sposób uzyskiwania wartości parametru. Możesz zmienić monit o podanie parametru, wprowadzić określoną wartość lub określić odwołanie do komórki.

    Okno dialogowe Parametry MS Query

  9. Kliknij przycisk OK , aby zapisać zmiany i zamknąć okno dialogowe parametry, a następnie w oknie dialogowym Importowanie danych kliknij przycisk OK , aby wyświetlić wyniki zapytania w programie Excel.

Teraz skoroszyt zawiera zapytanie parametryczne. Po każdym uruchomieniu kwerendy lub odświeżeniu połączenia danych program Excel sprawdzi parametr, aby ukończyć działanie klauzuli WHERE kwerendy. Jeśli parametr monituje o wartość, w programie Excel zostanie wyświetlone okno dialogowe Wprowadzanie wartości parametru w celu zebrania danych wejściowych — można wpisać wartość lub kliknąć komórkę zawierającą wartość. Możesz również określić, że zawsze należy używać podanej wartości lub odwołania, a jeśli jest używane odwołanie do komórki, możesz określić, że program Excel ma automatycznie odświeżać połączenie danych (tzn. Uruchom zapytanie ponownie), ilekroć zmieni się wartość określonej komórki.

Uwaga: W tym temacie założono, jak utworzyć połączenie z bazą danych programu Access przy użyciu dodatku Power Query. Aby uzyskać więcej informacji, zobacz nawiązywanie połączenia z bazą danych programu Access.

Parametrów można używać w większej liczbie scenariuszy dodatku Power Query, niż tylko filtrowanie danych — każdy krok dodatku Power Query może mieć parametry. Na przykład można użyć parametru w celu określenia części ciągu połączenia w kroku źródłowym, na przykład nazwy pliku.

Parametry dodatku Power Query mają nazwy. Aby użyć parametru, należy odwołać się do niego według nazwy w Formule dla kroku. Załóżmy na przykład, że chcesz przejrzeć dane dotyczące zachowywanych stron sieci Web, a chcesz filtrować dane według daty publikacji. Chociaż zawsze możesz korzystać z wbudowanych filtrów w podglądzie kwerend, korzystając z parametru umożliwiającego przeprowadzenie filtrowania, oszczędza czas i zapewnia większą elastyczność. Przyjrzyjmy się temu przykładowi.

W pustym skoroszycie tworzymy połączenie z bazą danych programu Access zawierającą rekordy sieci Web, które chcemy — w tym pola wskazujące, kiedy każda Strona została pierwotnie opublikowana. Załadowane do dodatku Power Query wygląda następująco:

Edytor dodatku Power Query przedstawiający załadowane dane

Ponieważ chcemy filtrować według daty, zmieniamy typ danych używanej kolumny, FirstPublishDate. Dane typu Data/godzina w źródle, ale nie mamy opieki, w jakiej chwili wystąpiła publikacja o porze dnia, a ich podanie może być tiresome — zmienimy go na typ danych Data.

Edytor dodatku Power Query wyświetlający wyniki

Następnie tworzymy parametr ograniczający wyniki w dniu, w którym strona była pierwotnie opublikowana. Kliknij pozycję Parametrygłówne>, > Zarządzaj parametrami , aby otworzyć okno dialogowe parametry.

Okno dialogowe Parametry dodatku Power Query

Kliknij pozycję Nowy, a w formularzu zostanie wyświetlony nowy parametr o nazwie parametr1 bez innych informacji.

Zmieniamy niektóre właściwości parametrów:

  • Zmienianie nazwy na FirstPubD

  • Zmień Opis na datę pierwszego opublikowania strony.

  • Zmień Typ na Date , tak aby parametr akceptował tylko wartości dat

  • Ustaw bieżącą wartość , aby parametr nie odfiltrować wszystkich wierszy, gdy nie wprowadzono danych wejściowych — używamy 1/1/2010.

Porada: Nazwa i opis powinny zapewniać wystarczający kontekst, aby ułatwić użytkownikom zrozumienie, jak i dlaczego należy korzystać z tego parametru. Nawet jeśli jesteś jedyną osobą, która będzie korzystać z tego parametru, może być konieczne przypomnienie od czasu do czasu.

Kliknij przycisk OK , aby utworzyć parametr i wyświetlić go w edytorze dodatku Power Query.

Edytor dodatku Power Query z wyświetlonym parametrem

Teraz nasz parametr jest wyświetlany w panelu zapytania — można go wybrać w celu wyświetlenia go w panelu głównym lub można go kliknąć prawym przyciskiem myszy, aby uzyskać więcej opcji. Gdy jest wybrany parametr, możemy edytować bieżącą wartość w panelu głównym lub kliknąć przycisk Zarządzaj parametrami , aby zmienić inne ustawienia.

Teraz możemy użyć tego parametru w oryginalnej kwerendzie. Klikamy oryginalne zapytanie w panelu zapytania, aby je wyświetlić. Chcemy użyć naszego parametru, aby przefiltrować wyniki na podstawie daty pierwszej publikacji, więc wybierzemy kolumnę FirstPublishDate , kliknij strzałkę filtr/sortowanie na prawej krawędzi nagłówka kolumny, wskaż polecenie filtry dat, a następnie kliknij polecenie po....

Edytor dodatku Power Query z wyświetlonym menu filtru daty

W oknie dialogowym Filtrowanie wierszy wybierz parametr z listy opcji dostępnych w filtrze.

Okno dialogowe Filtrowanie wierszy

Wprowadź lub wybierz wartość zastąpioną listą dostępnych parametrów. Istnieje tylko jedna utworzona, FirstPubD.

Okno dialogowe Filtrowanie wierszy z wyświetlonym wybranym parametrem

Wybierz go i kliknij przycisk OK. Edytor dodatku Power Query ładuje zapytanie, używając nowego parametru jako filtru.

Edytor dodatku Power Query wyświetlający przefiltrowane wyniki

Aby przetestować parametr, zmieniamy jego wartość na 1/1/2018.

Edytor dodatku Power Query z wyświetlonym parametrem

Odświeżenie zapytania, które teraz zawiera tylko wiersze o FirstPublishDate po 1/1/2018.

Edytor dodatku Power Query wyświetlający przefiltrowane wyniki

Teraz mamy zapytanie, które filtruje według daty przy użyciu parametru. Aby filtrować wyniki według FirstPublishDate nie trzeba już szukać tego pola, kliknij strzałkę filtr/sortowanie, a następnie wybierz przycisk po... Typ filtru i wprowadź wartość daty — można zmienić wartość FirstPubD i odświeżyć zapytanie. Ponadto możemy ponownie użyć nowego parametru, na przykład w przypadku podjęcia decyzji o pojęciu różnych zestawów pól z oryginalnego źródła danych w nowym arkuszu, a mimo to chcesz uwzględnić FirstPubDate i użyć go do filtrowania wyników.

Parametry są wyraźnie bardzo przydatne, ale muszę użyć edytora dodatku Power Query, aby zmienić wartość parametru. Chcemy mieć możliwość zmiany wartości filtru bez otwierania Edytora dodatku Power Query. W tym celu utworzymy tabelę w arkuszu, w którym jest ładowana kwerenda, oraz nową połączeniem dodatku Power Query z tabelą, a następnie użyj nowej kwerendy, aby przefiltrować nasze zapytanie główne.

W arkuszu, w którym są ładowane nasze kwerendy, wstawiamy wiersze powyżej zaimportowanych danych. Następnie tworzymy tabelę programu Excel z jednym wierszem, w którym ma się odbywać wartość parametru.

Skoroszyt programu Excel zawierający tabelę parametrów i dane załadowane z dodatku Power Query

Aby użyć nowej tabeli w celu filtrowania zapytań, musimy się z nią połączyć w dodatku Power Query. Tworzymy połączenie z tabelą, zaznaczając je, a następnie klikając pozycję z tabeli/zakresu na karcie dane . Zostanie otwarte nowe połączenie i zostanie wyświetlona nowa tabela w edytorze dodatku Power Query.

Dane tabeli programu Excel załadowane w edytorze dodatku Power Query

Ponieważ dane są ładowane jako typ danych Data/godzina, musimy zmienić go na typ danych Data, aby dopasować go do naszego parametru, więc klikam pozycję domowa > przekształcanie > Typ danych > Data.

Wskaźnik myszy nad poleceniem typ danych w grupie Przekształć na karcie Narzędzia główne na Wstążce Edytor dodatku Power Query.

Ponadto zmienimy nazwę naszej kwerendy na bardziej opisową niż tabela2. Aby wyczyścić, do czego służy, imię i nazwisko FirstPubDate.

Edytor dodatku Power Query z wyróżnionym polem nazwa

Ponieważ chcemy przekazać wartość, a nie samą tabelę, należy przejść do szczegółów wartości daty. W tym celu kliknij prawym przyciskiem myszy wartość w przeglądanych danych, a następnie kliknij polecenie Drąż.

Menu kontekstowe edytora dodatku Power Query dla wartości pola

Podgląd wyświetla teraz wartość zamiast tabeli.

Edytor dodatku Power Query wyświetlający pojedynczą wartość daty

Nie potrzebuję danych nowej kwerendy do załadowania w dowolnym miejscu — dane są już w arkuszu, w którym chcesz. Wystarczy połączenie, więc dodatek Power Query może uzyskać wartość parametru. Więc klikam pozycję plik> Zamknij & Wczytaj do... , aby otworzyć okno dialogowe Importowanie danych, a następnie wybierz pozycję Utwórz tylko połączenie.

Okno dialogowe Importowanie danych z wybraną opcją Utwórz połączenie tylko

Teraz mamy zapytanie o nazwie "FirstPubDate", które pobiera pojedynczą wartość daty z tabeli znajdującej się w arkuszu tuż nad tym, gdzie są ładowane nasze zapytania główne. Teraz wystarczy użyć tej kwerendy jako parametru do filtrowania naszej kwerendy głównej. Otwieramy więc zapytanie główne i edytujemy krok, który filtruje wiersze, przy użyciu kolumny FirstPublishDate. Rozwijamy pasek formuły i wybieram uprzednio utworzony parametr (FirstPubD). Następnie należy wpisać "a" po FirstPubD , ponieważ nazwa nowej kwerendy rozpoczyna się od tych samych liter, co parametr, dodatek Power Query wyświetla go jako opcję pobrania.

Rozwinięty pasek formuły edytora dodatku Power Query

Wybierz go, a następnie kliknij poza paskiem formuły, aby zastosować krok.

Edytor dodatku Power Query z załadowanymi danymi

Wszystko wygląda poprawnie, więc nie możemy zamknąć edytora dodatku Power Query i zapisać zmiany. W celu przetestowania parametru w arkuszu raportu zmienimy wartość komórki w tabeli u góry na 5/4/2019, a następnie odświeżymy połączenie, aby zobaczyć filtrowane dane.

Filtrowane dane w programie Excel

Nasz nowy filtr działa! Skoroszyt zostanie zapisany i zamknięty. Teraz każda osoba korzystająca ze skoroszytu może określić datę pierwszej publikacji, która ma zostać użyta jako filtr kwerendy — w tym samym arkuszu, w którym znajduje się kwerenda.

  1. Kliknij pozycję dane > Pobierz & przekształcania danych > Pobierz dane , > Uruchom Edytor dodatku Power Query.

  2. W edytorze dodatku Power Query kliknij pozycję Parametrygłówne >, > Zarządzaj parametrami.

  3. W oknie dialogowym Parametry kliknij pozycję Nowy.

  4. W razie potrzeby ustaw następujące elementy:

    • Nazwa — powinna odpowiadać funkcji parametru, ale powinna być możliwie najkrótsza.

    • Opis — może zawierać jakieś dane, które będą pomocne w poprawnym użyciu parametru.

    • Wymagane — wybierz, aby ten parametr wymagał wartości.

    • Typ -określa typ danych wymagany przez parametr.

    • Sugerowane wartości — w razie potrzeby Dodaj listę wartości lub określ zapytanie, aby uzyskać sugestie dotyczące wprowadzania danych.

    • Wartość domyślna — ten element jest wyświetlany tylko wtedy, gdy sugerowane wartości są ustawione na listę wartości i określa, która pozycja listy jest domyślna.

    • Bieżąca wartość — w zależności od użytego parametru, jeśli to pole jest puste, zapytanie może nie zwracać żadnych wyników. Jeśli jest zaznaczona pozycja wymagane , Bieżąca wartość nie może być pusta.

  5. Kliknij przycisk OK , aby utworzyć parametr.

  1. Otwórz zapytanie w edytorze dodatku Power Query.

  2. Kliknij strzałkę na prawej krawędzi nagłówka kolumny, której chcesz użyć do filtrowania danych, a następnie wybierz filtr z wyświetlonego menu.

  3. W oknie dialogowym Filtrowanie wierszy kliknij przycisk po prawej stronie warunku filtru, a następnie wykonaj dowolną z następujących czynności:

    • Aby użyć istniejącego parametru, kliknij pozycję parametr, a następnie wybierz odpowiedni parametr z listy wyświetlanej po prawej stronie.

    • Aby użyć nowego parametru, kliknij pozycję Nowy parametr..., a następnie Utwórz parametr.

  1. W arkuszu, w którym jest ładowany zapytanie, które chcesz filtrować, Utwórz tabelę z dwiema komórkami: nagłówek i wartość.

  2. Kliknij wartość, a następnie kliknij pozycję dane , > uzyskać & przekształcanie > danychz tabeli/zakresu.

  3. W edytorze dodatku Power Query Wprowadź wszelkie zmiany w połączeniu z tabelą (na przykład zmień typ danych lub nazwę), a następnie kliknij pozycję Narzędzia główne > zamknij > zamknij & obciążenie > Zamknij & Załaduj do...

  4. W oknie dialogowym Importowanie danych kliknij pozycję Utwórz tylko połączenie, opcjonalnie wybierz pozycję Dodaj do modelu danych, a następnie kliknij przycisk OK.

  5. Otwórz kwerendę, którą chcesz filtrować, w edytorze dodatku Power Query.

  6. Kliknij strzałkę na prawej krawędzi nagłówka kolumny, której chcesz użyć do filtrowania danych, a następnie wybierz filtr z wyświetlonego menu.

  7. Wykonaj jedną z następujących czynności:

    • Wybierz wartość z listy rozwijanej wartości (te dane pochodzą z danych zapytania).

    • Wybierz wartość przy użyciu przycisku na prawej krawędzi warunku filtru.

  8. Kliknij strzałkę na prawej krawędzi paska formuły, aby wyświetlić całą kwerendę.

  9. Warunek filtru jest następujący:

    • Nazwa filtrowanej kolumny jest wyświetlana w nawiasach kwadratowych.

    • Operator porównania występuje bezpośrednio po nazwie kolumny.

    • Wartość filtru znajduje się bezpośrednio po operatorze porównania i kończy się w nawiasie zamykającym. Zaznacz tę całą wartość.

  10. Zacznij wpisywać nazwę utworzonego właśnie połączenia tabeli, a następnie wybierz ją z wyświetlonej listy.

  11. Kliknij pozycję Narzędzia główne > zamknij > Zamknij & obciążenie.

    Kwerenda używa teraz wartości w tabeli utworzonej w celu filtrowania wyników kwerendy. Aby użyć nowej wartości, Edytuj zawartość komórki, a następnie Odśwież zapytanie.

Zobacz też

Tworzenie listy rozwijanej

Uwaga:  Ta strona została przetłumaczona automatycznie i może zawierać błędy gramatyczne lub nieścisłości. Chcemy, aby ta zawartość była dla Ciebie przydatna. Czy możesz dać nam znać, czy te informacje były pomocne? Oto angielskojęzyczny artykuł do wglądu.

Rozwijaj umiejętności związane z pakietem Office
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

Dziękujemy za opinię!

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×