Tworzenie formuł dodatku Power Query w programie Excel

Uwaga:  Dodatek Power Query w programie Excel 2016 ma nazwę Pobieranie i przekształcanie. Podane tutaj informacje dotyczą obu tych funkcji. Aby dowiedzieć się więcej, zobacz Pobieranie i przekształcanie w programie Excel 2016.

Aby tworzyć formuły dodatku Power Query w programie Excel, możesz korzystać z paska formuły edytora zapytań albo z Edytora zaawansowanego. Edytor zapytań to narzędzie dostępne w dodatku Power Query, które pozwala na tworzenie w tym dodatku zapytań i formuł dotyczących danych. Język używany do tworzenia tych formuł to język formuł dodatku Power Query. Istnieje wiele formuł dodatku Power Query, których można używać w celu odnajdowania, łączenia i uściślania danych. Aby dowiedzieć się więcej na temat pełnego zakresu formuł dodatku Power Query, zobacz Kategorie formuł w dodatku Power Query.

Utworzymy prostą formułę, a następnie utworzymy formułę zaawansowaną.

Tworzenie prostej formuły

Aby przedstawić przykład prostej formuły, zapiszemy wartości tekstowe z wielkiej litery, korzystając z formuły Text.Proper() .

  1. Na karcie wstążki POWER QUERY wybierz pozycję Z innych źródeł > Puste zapytanie.

    Power Query Ribbon
  2. Na pasku formuły w Edytorze zapytań wpisz = Text.Proper("wartość tekstowa") i naciśnij klawisz Enter lub wybierz ikonę Enter. Edytor formuł

  3. Wyniki w dodatku Power Query zostaną wyświetlone w okienku wyników formuły.

  4. Aby wyświetlić wynik w arkuszu programu Excel, wybierz pozycję Zamknij i załaduj.

Wynik w arkuszu będzie wyglądać następująco:

Funkcja Text.Proper

W Edytorze zapytań możesz także tworzyć formuły zapytań zaawansowanych.

Tworzenie formuły zaawansowanej

Aby przedstawić przykład formuły zaawansowanej, nadamy tekstom w kolumnie prawidłową wielkość liter, korzystając z kombinacji formuł. W celu połączenia wielu formuł w kroki zapytania, które zwrócą wynik w postaci zbioru danych, można użyć języka formuł dodatku Power Query. Wynik może również zostać zaimportowany do arkusza programu Excel.

Uwaga: Niniejszy temat stanowi wprowadzenie do zaawansowanych formuł dodatku Power Query. Aby dowiedzieć się więcej o formułach dodatku Power Query, zobacz Więcej informacji o formułach programu Power Query.

Załóżmy na przykład, że masz tabelę programu Excel zawierającą nazwy produktów, które chcesz zapisać z wielkiej litery.

Pierwotna tabela wygląda następująco:

Przed

Chcesz, aby tabela wynikowa wyglądała następująco:

Po

Przyjrzyjmy się krokom formuły zapytania, aby w pierwotnej tabeli wartości w kolumnie ProductName zapisać z wielkiej litery.

Przykład tworzenia zapytania zaawansowanego przy użyciu Edytora zaawansowanego

Aby wyczyścić pierwotną tabelę, użyj Edytora zaawansowanego w celu utworzenia kroków formuły zapytania. Skonstruujemy poszczególne kroki formuły zapytania, aby zaprezentować sposób tworzenia zapytania zaawansowanego. Poniżej przedstawiono wszystkie kroki formuły zapytania. Podczas tworzenia zapytania zaawansowanego należy postępować zgodnie z tym procesem:

  • Utwórz serię kroków formuły zapytania, które zaczynają się od instrukcji let. Pamiętaj, że w języku formuł dodatku Power Query jest uwzględniana wielkość liter.

  • Każdy krok formuły zapytania opiera się na poprzednim kroku, odwołując się do kroku według nazwy.

  • W celu uzyskania danych wyjściowych z kroku formuły zapytania użyj instrukcji in. W ogólnym ujęciu ostatni krok zapytania jest stosowany jako wynik ostatniego zbioru danych z instrukcji in.

Krok 1. Otwieranie Edytora zaawansowanego

  1. Na karcie wstążki POWER QUERY wybierz pozycję Z innych źródeł > Puste zapytanie.

  2. W Edytorze zapytań wybierz pozycję Edytor zaawansowany.

    Edytor zaawansowany

  3. Zostanie wyświetlony Edytor zaawansowany.

    Edytor zaawansowany (2)

Krok 2. Definiowanie oryginalnego źródła

W Edytorze zaawansowanym:

  1. Użyj instrukcji let, która spowoduje przypisanie formuły Source = Excel.CurrentWorkbook(). Dzięki temu tabela programu Excel będzie używana jako źródło danych. Aby uzyskać więcej informacji na temat formuły Excel.CurrentWorkbook(), zobacz Excel.CurrentWorkbook.

  2. Przypisz Source (Źródło) do wyniku in.

    let Source =
    Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
    in Source
  3. Zaawansowane zapytanie będzie wyglądało następująco w Edytorze zaawansowanym.

    Edytor zaawansowany (3)
  4. Aby wyświetlić wyniki w arkuszu:

    1. Kliknij pozycję Gotowe.

    2. Na wstążce Edytora zapytań kliknij pozycję Zamknij i załaduj.

Krok 1 — edytor zaawansowany

Wynik w arkuszu wygląda następująco:

Krok 1 — wynik

Krok 3. Przekształcanie pierwszego wiersza w nagłówki

Aby przekonwertować wartości w kolumnie ProductName na wartości zapisane z wielkiej litery, należy najpierw przekształcić pierwszy wiersz w nagłówki kolumn. Zrób to w Edytorze zaawansowanym:

  1. Dodaj formułę #"First Row as Header" = Table.PromoteHeaders() do kroków formuły zapytania i utwórz odniesienie do Source jako do źródła danych. Aby uzyskać więcej informacji na temat formuły Table.PromoteHeaders(), zobacz Table.PromoteHeaders.

  2. Przypisz formułę #"First Row as Header" do wyniku w instrukcji in.

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

Wynik w arkuszu wygląda następująco:

Krok 3 — wynik

Krok 4. Zmienianie każdej wartości w kolumnie na wartość zapisaną z wielkiej litery

Aby przekonwertować każdą wartość w kolumnie ProductName na zapisaną z wielkiej litery, użyj formuły Table.TransformColumns() i utwórz odniesienie do kroku formuły zapytania „First Row as Header” (Pierwszy wiersz jako nagłówek). Zrób to w Edytorze zaawansowanym:

  1. Dodaj formułę zamiany pierwszej litery każdego wyrazu na wielką #"Capitalized Each Word" = Table.TransformColumns() do kroków formuły zapytania, a następnie utwórz odniesienie do formuły #"First Row as Header" jako do źródła danych. Aby uzyskać więcej informacji na temat formuły Table.TransformColumns(), zobacz Table.TransformColumns.

  2. Przypisz formułę #"Capitalized Each Word" do wyniku instrukcji 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"

Ostateczny wynik zmieni każdą wartość w kolumnie ProductName na zapisaną z wielkiej litery, a wynik będzie wyglądał następująco w arkuszu:

Krok 4 — wynik

Za pomocą języka formuł dodatku Power Query można tworzyć zapytania proste oraz zaawansowane przeznaczone do odnajdowania, łączenia i uściślania danych. Aby uzyskać więcej informacji na temat dodatku Power Query, zobacz Microsoft Power Query dla programu Excel — 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.

×