Tworzenie formuł dodatku Power Query w programie Excel

Tylko za pomocą Edytora dodatku Power Query wszystkie formuły dodatku Power Query tworzysz w tym celu. Zobaczmy, jak działa program Power Query, patrząc w należy sobie pod kontrolę. Aby dowiedzieć się, jak aktualizować lub dodawać formuły, wystarczy obejrzeć działanie Edytora dodatku Power Query.  Za pomocą Edytora zaawansowanego możesz nawet samodzielnie samodzielnie roll formuły.           

Edytor dodatku Power Query udostępnia środowisko zapytania danych oraz możliwości kształtowania Excel, za pomocą których można ponownie kształtować dane z wielu źródeł danych. Aby wyświetlić okno Edytora dodatku Power Query,zaimportuj dane z zewnętrznych źródeł danych w arkuszu programu Excel, zaznacz komórkę w danych, a następnie wybierz pozycję Zapytanie > Edytuj. Poniżej przedstawiono podsumowanie głównych składników.

Części Edytora zapytań

  1. Wstążka Edytora dodatku Power Query, która umożliwia kształtowanie danych

  2. Okienko Zapytania, za pomocą których odszukasz źródła danych i tabele

  3. Menu kontekstowe, które są wygodnymi skrótami do poleceń na wstążce

  4. Podgląd danych, w którym są wyświetlane wyniki kroków zastosowanych do danych

  5. Okienko Ustawienia, które zawiera właściwości i poszczególne kroki zapytania.

W tle każdy krok w zapytaniu jest oparty na formule widocznej na pasku formuły.

Przykładowa formuła w Edytorze zapytań

Może się jednak okazać, że trzeba zmodyfikować lub utworzyć formułę. W formułach jest używany język formuł dodatku Power Query, za pomocą którego można tworzyć zarówno proste, jak i złożone wyrażenia. Aby uzyskać więcej informacji o składni, argumentach, spostrzeżeniach, funkcjach i przykładach, zobacz Język formuł M dodatku Power Query.

Używając jako przykładu listy drużyn piłkarskich, użyj dodatku Power Query, aby przechować nieprzetworzone dane znalezione w witrynie sieci Web i przekształcić je w dobrze sformatowaną tabelę. Zobacz, jak są tworzone kroki zapytania i odpowiadające im formuły dla każdego zadania w okienku Ustawienia zapytania w obszarze Zastosowane kroki i na pasku Formuła.

Twoja przeglądarka nie obsługuje wideo.

Procedura

  1. Aby zaimportować dane, wybierz pozycję Dane> z sieci Web,wprowadź "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" w polu Adres URL, a następnie wybierz przycisk OK.

  2. W oknie dialogowym Nawigator wybierz tabelę Results [Edit] (Wyniki [Edycja] po lewej stronie, a następnie wybierz pozycję Transform Data (Przekształć dane) u dołu. Zostanie wyświetlony edytor dodatku Power Query.

  3. Aby zmienić domyślną nazwę zapytania, w okienku Ustawienia zapytania w obszarze Właściwości usuń pozycję "Wyniki [Edycja]", a następnie wprowadź "EURO".

  4. Aby usunąć niechciane kolumny, zaznacz pierwszą, czwartą i piątą kolumnę, a następnie wybierz pozycję Narzędzia> Usuń kolumnę > Usuń inne kolumny.

  5. Aby usunąć niechciane wartości, wybierz pozycję Kolumna1,wybierz pozycję Narzędzia> Zamień wartości, wprowadź "szczegóły" w polu Wartości do znalezienia, a następnie wybierz przycisk OK.

  6. Aby usunąć wiersze z wyrazem "Rok", wybierz strzałkę filtru w kolumnie Kolumna1,wyczyść pole wyboru obok pozycji "Rok", a następnie wybierz przycisk OK.

  7. Aby zmienić nazwy nagłówków kolumn, kliknij dwukrotnie każdy z nich, a następnie zmień nazwę "Kolumna1" na "Rok", kolumnę "Kolumna4" na "Zwycięzca" i "Kolumna5" na "Wynik końcowy".

  8. Aby zapisać zapytanie, wybierz pozycję Narzędzia >Zamknij & Załaduj.

Wynik

Wyniki instruktażu — kilka pierwszych wierszy

W poniższej tabeli przedstawiono podsumowanie każdego zastosowanego etapu i odpowiadającej mu formuły.

Etap i zadanie kwerendy

Formuła

Źródło

Połączenie ze źródłem danych sieci Web

= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))

Nawigacja

Wybierz tabelę do połączenia

=Source{2}[Data]

Zmieniony typ

Zmienianie typów danych (które są automatycznie używane w dodatku Power Query)

= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})

Usunięto inne kolumny

Usuwanie innych kolumn w celu wyświetlenia tylko potrzebnych kolumn

= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})

Wartość zastąpiona

Zamienianie wartości w celu oczyszczenia wartości w zaznaczonej kolumnie

= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})

Filtrowane wiersze

Filtrowanie wartości w kolumnie

= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))

Kolumny o zmienionej nazwy

Zmieniono nagłówki kolumn tak, aby zawierały zrozumiały opis

= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Ważne    Podczas edytowania kroków Źródło, Nawigacjai Zmieniony typ należy zachować ostrożność, ponieważ są one tworzone przez dodatku Power Query w celu zdefiniowania i   skonfigurowania źródła danych.

Pokazywanie lub ukrywanie paska formuły

Domyślnie wyświetlany jest pasek formuły, ale jeśli jest niewidoczny, można go ponownie wyświetlać.

  • Wybierz pozycję Widok > układu > pasku formuły.

Edit a formula in the formula bar

  1. Aby otworzyć zapytanie, znajdź wcześniej załadowaną komórkę w Edytorze dodatku Power Query, zaznacz komórkę w danych, a następnie wybierz pozycję Zapytanie i> Edytuj. Aby uzyskać więcej informacji, zobacz Tworzenie, ładowanie i edytowanie zapytania w programie Excel.

  2. W okienku Ustawienia w obszarze Zastosowane krokiwybierz krok, który chcesz edytować.

  3. Na pasku formuły znajdź i zmień wartości parametrów, a następnie wybierz ikonę Wprowadź Ikona Enter z lewej strony paska formuły w dodatku Power Query lub naciśnij klawisz Enter. Na przykład zmień tę formułę tak, aby zawierała również kolumnę Kolumna2:

    Przed: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
    Po:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

  4. Wybierz ikonę Wprowadź Ikona Enter z lewej strony paska formuły w dodatku Power Query lub naciśnij klawisz Enter, aby wyświetlić nowe wyniki w podglądzie danych.

  5. Aby wyświetlić wynik w arkuszu Excel, wybierz pozycję Narzędzia> Zamknij & Załaduj.

Tworzenie formuły na pasku formuły

Aby uzyskać przykład prostej formuły, nadamy wartość tekstową z prawidłową literą przy użyciu funkcji Text.Proper.

  1. Aby otworzyć puste zapytanie, w programie Excel pozycję> Pobierz > z innych źródeł > puste zapytanie. Aby uzyskać więcej informacji, zobacz Tworzenie, ładowanie i edytowanie zapytania w programie Excel.

  2. Na pasku formuły wprowadź=Text.Proper("text value"), a następnie wybierz ikonę Wprowadź Ikona Enter z lewej strony paska formuły w dodatku Power Query lub naciśnij klawisz Enter.

    Wyniki są wyświetlane w podglądzie danych.

  3. Aby wyświetlić wynik w arkuszu Excel, wybierz pozycję Narzędzia> Zamknij & Załaduj.

Wynik:

Funkcja Text.Proper

 Podczas tworzenia formuły w dodatku Power Query jest sprawdzana poprawność składni formuły. Jednak wstawienie, zmiana kolejności lub usunięcie kroku pośredniego w zapytaniu może spowodować potencjalne zerwanie zapytania.  Zawsze sprawdzaj wyniki w podglądzie danych.

Ważne    Podczas edytowania kroków Źródło, Nawigacjai Zmieniony typ należy zachować ostrożność, ponieważ są one tworzone przez dodatku Power Query w celu zdefiniowania i   skonfigurowania źródła danych.

Edytowanie formuły przy użyciu okna dialogowego

Ta metoda umożliwia korzystanie z okien dialogowych, które różnią się w zależności od etapu. Nie musisz znać składni formuły.

  1. Aby otworzyć zapytanie, znajdź wcześniej załadowaną komórkę w Edytorze dodatku Power Query, zaznacz komórkę w danych, a następnie wybierz pozycję Zapytanie i> Edytuj. Aby uzyskać więcej informacji, zobacz Tworzenie, ładowanie i edytowanie zapytania w programie Excel.

  2. W okienku Ustawienia zapytania w obszarze Zastosowane kroki wybierz ikonę Edytuj Ustawienia Ikona Ustawienia dla kroku, który chcesz edytować, lub kliknij krok prawym przyciskiem myszy, a następnie wybierz pozycję Edytuj Ustawienia .

  3. W oknie dialogowym wpisz zmiany, a następnie wybierz przycisk OK.

Wstawianie kroku

Po zakończeniu kroku zapytania, który ponownie kształtuje dane, zostanie on dodany poniżej bieżącego kroku zapytania. ale wstawienie kroku zapytania w środku kroków może spowodować błąd w kolejnych krokach. Podczas próby wstawienia nowego kroku w dodatku Power Query jest wyświetlane ostrzeżenie Wstaw krok, a nowy krok powoduje zmianę pól, takich jak nazwy kolumn, które są używane w dowolnych krokach, które są następujące po wstawieniu kroku.

  1. W okienku Ustawienia w obszarze Zastosowane kroki wybierz krok,który ma być bezpośrednio poprzedzony nowym krokiem i odpowiadającą mu formułą.

  2. Wybierz ikonę Dodaj Ikona Funkcja krok po lewej stronie paska formuły. Możesz też kliknąć krok prawym przyciskiem myszy, a następnie wybrać polecenie Wstaw krok po.Zostanie utworzona nowa formuła w formacie :

    = <nameOfTheStepToReference>, na przykład =Production.WorkOrder.

  3. Wpisz nową formułę w formacie:

    =Class.Function(ReferenceStep[,otherparameters])

    Załóżmy na przykład, że masz tabelę z kolumną Płeć i chcesz dodać kolumnę z wartością "Ms". lub "Pan" w zależności od płci danej osoby. Formuła będzie miała

    formułę:=Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Przykład formuły

Zmiana kolejności kroku

  • W okienku Zapytania Ustawienia w obszarze Zastosowane krokikliknij prawym przyciskiem myszy krok, a następnie wybierz pozycję Przenieś w górę lub Przenieś w dół.

Usuń krok

  • Wybierz ikonę Usuń krok Usuń lub kliknij krok prawym przyciskiem myszy, a następnie wybierz pozycję Usuń lub Usuń do końca. Ikona usuwania Usuń krok jest również dostępna po lewej stronie paska formuły.

W tym przykładzie nadamy tekstom w kolumnie prawidłową literę, używając kombinacji formuł w Edytorze zaawansowanym. 

Na przykład masz tabelę z tabelą Excel o nazwie Zamówienia, zawierającą kolumnę ProductName, którą chcesz przekonwertować na tabelę z prawidłową literą. 

Przed:

Przed

Po:

Krok 4 — wynik

Podczas tworzenia zapytania zaawansowanego jest tworzyć serie kroków formuły zapytania na podstawie wyrażenia let.  Użyj wyrażenia let, aby przypisać nazwy i obliczyć wartości, do których następnie odwołuje się klauzula in definiująca klauzulę Step. W tym przykładzie zwracany jest taki sam wynik jak wynik w sekcji "Tworzenie formuły na pasku formuły".

let  
    Source = Text.Proper("hello world")
in  
    Source  

Zobaczysz, że każdy krok opiera się na poprzednim kroku, odwołując się do kroku według nazwy. Przypomnienie: w języku formuł dodatku Power Query jest wróżniana wielkość liter.

Etap 1. Otwieranie Edytora zaawansowanego

  1. W Excel wybierz pozycję Dane i> pobierz dane > inne źródła > puste zapytanie. Aby uzyskać więcej informacji, zobacz Tworzenie, ładowanie i edytowanie zapytania w programie Excel.

  2. W Edytorze dodatku Power Query wybierz pozycję Narzędzia > w Edytorze zaawansowanym, który zostanie otwarty z szablonem wyrażenia ze let.

Edytor zaawansowany (2)

Etap 2. Definiowanie źródła danych

  1. Utwórz wyrażenie ze Excel. Funkcja CurrentWorkbook w następujący sposób:

    let#x1

    in
        Source

    Krok 1 — edytor zaawansowany

  2. Aby załadować zapytanie do arkusza, wybierz pozycję Gotowe ,a następnie wybierz pozycję Narzędzia> Zamknij & Załaduj > Zamknij & Załaduj.

Wynik:

Krok 1 — wynik

Etap 3. Podniesienie pierwszego wiersza do nagłówków

  1. Aby otworzyć zapytanie, w arkuszu zaznacz komórkę w danych, a następnie wybierz pozycję Zapytanie w> Edytuj. Aby uzyskać więcej informacji, zobacz Tworzenie, ładowanie i edytowanie zapytania w programie Excel (Power Query).

  2. W Edytorze dodatku Power Query wybierz pozycję Narzędzia > Edytor zaawansowany, który zostanie otwarty z zestawieniem utworzonym w fazie 2:Definiowanie źródła danych.

  3. W wyrażeniu let dodaj funkcje #"First Row as Header" i Table.PromoteHeaders w następujący sposób:

    let
        
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   #"First Row as Header" = Table.PromoteHeaders(Source)#x3

        #"First Row as Header"

  4. Aby załadować zapytanie do arkusza, wybierz pozycję Gotowe ,a następnie wybierz pozycję Narzędzia> Zamknij & Załaduj > Zamknij & Załaduj.

Wynik:

Krok 3 — wynik

Etap 4. Zmienianie każdej wartości w kolumnie na wartość z odpowiednią literą

  1. Aby otworzyć zapytanie, w arkuszu zaznacz komórkę w danych, a następnie wybierz pozycję Zapytanie w> Edytuj. Aby uzyskać więcej informacji, zobacz Tworzenie, ładowanie i edytowanie zapytania w programie Excel.

  2. W Edytorze dodatku Power Query wybierz pozycję Narzędzia > Edytor zaawansowany, który zostanie otwarty wraz z instrukcje utworzone w fazie 3.Pod poziomem pierwszego wiersza wybierz pozycję Nagłówki .

  3. W wyrażeniu let przekonwertuj każdą wartość kolumny ProductName na odpowiednią wartość, używając funkcji Table.TransformColumns, odwołując się do poprzedniego kroku formuły zapytania "First Row as Header", dodając formułę #"Capitalized Each Word" do źródła danych, a następnie przypisując formułę #"Capitalized Each Word" do wyniku w in.

    let
        Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
        #"First Row as Header" = Table.PromoteHeaders(Source),
        #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
    in
        #"Capitalized Each Word"

  4. Aby załadować zapytanie do arkusza, wybierz pozycję Gotowe ,a następnie wybierz pozycję Narzędzia> Zamknij & Załaduj > Zamknij & Załaduj.

Wynik:

Krok 4 — wynik

Możesz sterować działaniem paska formuły w Edytorze dodatku Power Query dla wszystkich skoroszytów.

Wyświetlanie lub ukrywanie paska formuły

  1. Wybierz pozycję Opcje> i Ustawienia > Opcje zapytania.

  2. W lewym okienku w obszarze GLOBAL wybierzpozycję Edytor dodatku Power Query.

  3. W prawym okienku w obszarze Układzaznacz lub wyczyść pole wyboru Wyświetl pasek formuły.

Włączanie lub wyłączanie funkcji M IntelliSense

  1. Wybierz pozycję Opcje> i Ustawienia > Opcje zapytania.

  2. W lewym okienku w obszarze GLOBAL wybierzpozycję Edytor dodatku Power Query.

  3. W prawym okienku w obszarze Formułazaznacz lub wyczyść pole wyboru Włącz funkcję M IntelliSense na pasku formuły, w edytorze zaawansowanym i w oknie dialogowym kolumny niestandardowej.

Uwaga    Zmiana tego ustawienia zostanie w związku z tym w związku z następnym otwarciem okna Edytora dodatku Power Query.

Zobacz też

Dodatek Power Query dla programu Excel — pomoc

Tworzenie i wywoływanie funkcji niestandardowej

Używanie listy Zastosowane kroki (docs.com)

Używanie funkcji niestandardowych (docs.com)

Formuły M (docs.com) dodatku Power Query

Postępowania z błędami (docs.com)

Potrzebna dalsza pomoc?

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.

×