Power Query 39 — Numer tygodnia w roku i miesiącu data początku i końca tygodnia

W dzisiejszym poście zajmiemy się wyznaczaniem numeru tygodnia w roku i miesiącu oraz początku i końca tygodnia. W poradach od 341 do 344 omawialiśmy wyznaczanie tych danych w Excelu za pomocą funkcji .

Excel — Numer tygodnia w roku — porada 341

Excel — Początek i koniec tygodnia na podstawie daty — porada 342

Excel — Data początku i końca tygodnia na podstawie numeru tygodnia w roku — porada 343

Excel — Numer tygodnia w miesiącu różne systemy — porada 344

Poznamy możliwości Power Query w tym zakresie. Temat ten omówimy na podstawie przykładowych danych przedstawionych na rys. nr 1.

rys. nr 1 — Przykładowe dane

Mamy tutaj przedstawione dane zawierające zbiór dat i informację jaki to jest dzień tygodnia. Aby zaczytać dane do Power Query wybieramy polecenie Z tabeli z karty Dane (rys. nr 2).

rys. nr 2 — Z tabeli

Otworzy nam się Edytor zapytań Power Query z wczytaną tabelą. Klikamy ikonkę kalendarza przy nazwie kolumny Data aby zmienić formatowanie. Z podręcznego menu wybieramy typ danych – Data (rys. nr 3). Wyświetli nam się komunikat o Zmianie typu danych, w którym za pomocą przycisku Zamień bieżącą zatwierdzamy zmianę.

rys. nr 3 — Zmiana typu danych

Otrzymamy dane przedstawione na rysunku nr 4.

rys. nr 4 — Dane w edytorze zapytań

Chcemy dodać nową kolumnę z danymi zawierającymi numer tygodnia więc korzystamy z poleceń z karty Dodaj kolumnę. Gdybyśmy natomiast chcieli przekształcić nasze dane musimy skorzystać z analogicznych poleceń na karcie Przekształć (rys. nr 5).

rys. nr 5 — Przekształcenie danych

Naszym zadaniem jest dodanie nowych kolumn i interesującymi nas danymi. Zaznaczamy kolumnę Data a następnie rozwijamy polecenie Data (punkt 2 na rys. nr 6)  z karty Dodaj kolumnę, następnie rozwijamy polecenie Tydzień (punkt 3) i wybieramy interesującą nas informację, czyli w naszym przykładzie Tydzień roku (punkt 4).

rys. nr 6 — Dodawanie nowej kolumny Tydzień roku

Otrzymamy dane z nową kolumną zawierającą numer tygodnia w roku odpowiadającego naszej dacie (rys. nr 7).

rys. nr 7 — Tydzień roku

Istotną informacją tutaj jest, że Power Query działa na systemie pierwszym funkcji NUM.TYGODNIA Excela, czyli data 1 stycznia każdego roku to pierwszy dzień pierwszego tygodnia nowego roku. W Power Query możemy jedynie skorygować od jakiego dnia rozpoczyna się nowy tydzień. W Polsce standardowo nowy tydzień zaczyna się w poniedziałek i takie właśnie jest domyślne działanie funkcji Date.WeekOfYear w Power Query. Możemy to zmienić, wystarczy skopiować nazwę funkcji a następnie nacisnąć przycisk fx obok paska formuły, aby dodać nowy krok i wkleić w pasek formuły zapis =Date.WeekOfYear. Kiedy zatwierdzimy taką formułę klawiszem Enter otrzymamy informacje na temat tej funkcji, między innymi jej parametrów oraz jakie zwraca wartości. Możemy sobie tutaj w polu firstDayOfWeek wybrać dzień jaki chcemy przyjąć za początek tygodnia (rys. nr 8).

rys. nr 8 — funkcja Date.WeekOfYear

Usuwamy ten krok z Zastosowanych kroków, ponieważ wprowadziliśmy go tylko w celu pokazania możliwości zmiany dnia początku tygodnia. Jeśli chcemy wprowadzić zmianę dotyczącą dnia początku tygodnia możemy wpisać w formułę dodatkowy parametr zawierający nazwę dnia, jaki chcemy przyjąć za początek tygodnia. Załóżmy, że chcemy aby tydzień rozpoczynał się we wtorek, więc w formułę wpiszemy Day.Tuesday (rys. nr 9). Formuła powinna wyglądać następująco:

=Table.AddColumn(#"Zmieniono typ", "Tydzień roku" each Date.WeekOfYear([Data], Day.Tuesday), Int64.Type)

rys. nr 9 — Formuła funkcji Date.WeekOfYear

Otrzymamy zmienione dane, w których każdy nowy tydzień będzie rozpoczynał się we wtorek (rys. nr 10).

rys. nr 10 — Zmienione dane

Zamiast Day.Tuesday możemy wpisać wartość 2 w formule. Zadziała ona prawidłowo. Zapis formuły powinien wtedy wyglądać następująco:

=Table.AddColumn(#"Zmieniono typ", "Tydzień roku" each Date.WeekOfYear([Data], 2), Int64.Type)

W Power Query dni są numerowane od niedzieli do soboty. Niedzieli odpowiada wartość 0, poniedziałek to wartość 1 itd., natomiast sobocie przypisana jest wartość 6.

Wszystkie kolejne funkcje, z których tutaj będziemy korzystać mają ten dodatkowy argument, w którym możemy przyjąć jaki dzień chcemy traktować jako początek tygodnia.

W kolejnym przykładzie wyznaczymy sobie numer tygodnia w miesiącu. Zaznaczamy kolumnę Data a następnie rozwijamy polecenie Data (punkt 2 na rys. nr 11)  z karty Dodaj kolumnę, następnie rozwijamy polecenie Tydzień (punkt 3) i wybieramy interesującą nas informację, czyli w naszym przykładzie Tydzień miesiąca (punkt 4).

rys. nr 11 — Tydzień miesiąca

Otrzymamy nową kolumnę z numerem tygodnia w miesiącu (rys. nr 12).

rys. nr 12 — Numer tygodnia w miesiącu

Dzień uznawany za początek tygodnia wstawił się domyślnie (poniedziałek). Jeśli chcemy zmienić dzień początku tygodnia musimy to zrobić analogicznie jak w poprzednim przykładzie, czyli dopisać ten argument w formułę funkcji na pasku formuły (rys. nr 13). Zakładamy, że chcemy aby tydzień rozpoczynał się w piątek, czyli musimy wpisać argument Day.Friday lub wartość 5. Zapis powinien wyglądać następująco:

=Table.AddColumn(#"Wstawiono Tydzień roku", "Tydzień miesiąca", each Date.WeekOfMonth([Data],5) Int64.Type)

rys. nr 13 — Funkcja Date.WeekOfMonth

Gdybyśmy nie mieli widocznego paska formuły, musimy go uruchomić w karcie Widok (rys. nr 14).

rys. nr 14 — Uruchomienie paska formuły

Otrzymamy dane przedstawiające numerację rozpoczynającą nowy tydzień w piątek (rys. nr 15).

rys. nr 15 — Dane przedstawiające numerację rozpoczynającą nowy tydzień w piątek

W kolejnym przykładzie dodamy kolumnę zawierającą Początek tygodnia. Zaznaczamy kolumnę Data a następnie rozwijamy polecenie Data (punkt 2 na rys. nr 16)  z karty Dodaj kolumnę, następnie rozwijamy polecenie Tydzień (punkt 3) i wybieramy interesującą nas informację, czyli w naszym przykładzie Początek tygodnia (punkt 4).

rys. nr 16 — Początek tygodnia

Dodamy również kolumnę zawierającą Koniec tygodnia w analogiczny sposób jak na rysunku nr 16. Otrzymamy wtedy dwie nowe kolumny przedstawione na rysunku nr 17.

rys. nr 17 — Dane z początkiem i końcem tygodnia

Zostało tu zastosowana numeracja domyślna, czyli tydzień rozpoczyna się w poniedziałek, a kończy w niedzielę.

Użyliśmy w tych przykładach dwóch funkcji Date.StartOfWeek oraz Date.EndOfWeek.

Istotną informacją dla działań w Power Query jest to, że data 1 stycznia zawsze rozpoczyna pierwszy tydzień nowego roku. Możemy jedynie zmieniać dni początku i końca tygodnia. Nie ma możliwości zrobić kontynuacji numeru tygodnia z poprzedniego roku w nowym roku, tak żeby tydzień zawsze miał 7 dni, jak to było możliwe w Excelu.

Tak przygotowane dane możemy załadować do Excela za pomocą polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 18).

rys. nr 18 — Zamknij i załaduj do

Otworzy nam się okno Ładowania do, gdzie ustawiamy sposób wyświetlania danych jako Tabela oraz wybieramy miejsce wstawienia danych – Istniejący arkusz i wskazujemy konkretną komórkę (rys. nr 19). Powyższe parametry zatwierdzamy przyciskiem Załaduj.

rys. nr 19 — Okno ładowania do

Otrzymamy dane wczytane do Excela przedstawione na rysunku nr 20.

rys. nr 20 — Dane zaczytane do Excela

Możemy sobie jeszcze odpowiednio sformatować dane korzystając ze skrótu klawiszowego Ctrl+1, aby przy tych datach wyświetlała się nazwa dnia tygodnia. Otworzy nam się okno formatowania komórek, gdzie wybieramy formatowanie Niestandardowe w karcie Liczby, następnie w polu Typ musimy dopisać zapis ddd (rys. nr 21).

rys. 21 — Formatowanie komórek

Otrzymamy sformatowane dane przedstawione na rysunku nr 22.

21 — Sformatowane dane

Przedsprzedaż Mistrz Excela II wydanie

Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą 😀)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.

Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".

Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach 😉 i z dużą ilością zdjęć.

Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.

Aktualnie w przedsprzedaży możesz mieć Mistrza Excela w obniżonej cenie:
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.

W ramach przedsprzedaży możesz mieć całą książkę za jedyne 30 zł (łącznie z darmową wysyłką listem ekonomicznym), a jeśli zależy Ci na dedykacji wystarczy, że dołożysz 10 zł.

Książka Mistrz Excela reklama

Power Query 38 — Tekst przed ogranicznikiem po ograniczniku i między ogranicznikami

W dzisiejszym poście zajmiemy się wyciąganiem tekstu po ograniczniku, między ogranicznikami bądź przed ogranicznikiem. W pytaniu od widzów nr 104 napisaliśmy formułę w Excelu, która wyciągała tekst znajdujący się pomiędzy przedostatnim a ostatnim ogranicznikiem np. oznaczony na rysunku nr 1 na czerwono.

rys. nr 1 — Tekst który będziemy wyciągać

Formuły te w Excelu były skomplikowane. W Power Query możemy zrobić to samo, ale dużo szybciej. Ponadto możemy wyciągnąć tekst znajdujący się za konkretnym ogranicznikiem, przed lub pomiędzy dwoma konkretnymi ogranicznikami. Zagadnienie to omówimy na podstawie przykładowych danych przedstawionych na rysunku nr 2.

rys. nr 2 — Przykładowe dane

W przykładowych danych mamy zestawione różne ścieżki do plików, z których będziemy chcieli wyciągnąć konkretną informację. Pierwszym krokiem jest zaczytanie danych do Power Query. Otrzymamy Dane w Edytorze zapytań przedstawione na rysunku nr 3.

rys. nr 3 — Edytor zapytań

Z zastosowanych kroków usuwamy krok o nazwie Zmieniono typ. Będziemy dodawać kolumny zawierające wyciągnięte informacje z tekstu z danych wejściowych. Rozwijamy polecenie wyodrębnij (punkt 2 na rys. nr 4) z karty Dodaj kolumnę, a następnie wybieramy polecenie Tekst przed ogranicznikiem (punkt 3 na rys. nr 4).

rys. nr 4 — Tekst przed ogranicznikiem

Otworzy nam się okno Tekstu przed ogranicznikiem, gdzie w polu Ogranicznik musimy wybrać lub wpisać ogranicznik oznaczający koniec zawartości, którą chcemy wyodrębnić. Wpisujemy / (backslash). Rozwijamy opcje zaawansowane, gdzie możemy wybrać dodatkowe parametry. Domyślnie Power Query czyta dane od lewej do prawej strony i znajdzie nam pierwsze wystąpienie ogranicznika. W opcjach zaawansowanych możemy wybrać ilość ograniczników, które chcemy pominąć. W polu liczba ograniczników do pominięcia wpisujemy wartość 1, czyli chcemy pominąć pierwsze wystąpienie ogranicznika (rys. nr 5). Tak ustawione parametry zatwierdzamy przyciskiem OK.

rys. nr 5 — Okno tekstu przed ogranicznikiem

Otrzymamy dane z nową kolumną o nazwie Tekst przed ogranicznikiem przedstawione na rysunku nr 6.

rys. nr 6 — dane z kolumną z tekstem przed ogranicznikiem

Ważne jest, że domyślnie rozpatrujemy dane od lewej strony, a w opcjach zaawansowanych możemy ten parametr zmienić. Opcja ta jest zapisana w pasku formuły naszej funkcji (Text.BeforeDelimiter), co zostało zaznaczone kolorem zielonym na rys. nr 7.

rys. nr 7 — Funkcja Text.BeforeDelimiter

Możemy edytować nasze zapytanie klikając na koło zębate obok nazwy kroku Wstawiony tekst przed ogranicznikiem (rys. nr 8).

rys. nr 8 — Koło zębate do edycji kroku

Ponownie otworzy nam się okno Tekstu przed ogranicznikiem gdzie możemy wybrać jedną z dwóch opcji rozpatrywania danych: Od początku danych wejściowych lub Od końca danych wejściowych (rys. nr 9).

rys. nr 9 — Parametry tekstu przed ogranicznikiem

Jeśli ustawimy parametr Od końca danych wyjściowych otrzymamy dane przedstawione na rysunku nr 10.

rys. nr 10 — Nowa kolumna z tekstem przed ogranicznikiem patrząc od końca

W tym przypadku Power Query będzie przeszukiwał dane od prawej strony. Ponieważ nie jest to domyślne działanie nasza formuła się rozbudowała (rys. nr 11).

rys. nr 11 — Rozbudowana formuła funkcji

W kolejnej nowej kolumnie chcemy otrzymać tekst za ogranicznikiem. W tym celu rozwijamy polecenie Wyodrębnij (punkt 2 na rys. nr 12) z karty Dodaj kolumnę, a następnie wybieramy polecenie Tekst za ogranicznikiem (punkt nr 3).

rys. nr 12 — Tekst za ogranicznikiem

Otworzy nam się okno Tekstu za ogranicznikiem, gdzie w polu Ogranicznik wpisujemy znak (/).Identycznie jak dla pierwszego przypadku, czyli Tekstu przed ogranicznikiem. Następnie w opcjach zawansowanych, wybieramy czy chcemy rozpatrywać dane od lewej do prawej strony czy od końca oraz wpisujemy liczbę ograniczników, które chcemy pominąć. Wpisujemy w naszym przykładzie, że chcemy pominąć 2 ograniczniki oraz rozpatrywanie danych domyślne, czyli od lewej do prawej. Ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 13).

rys. nr 13 — Okno tekstu za ogranicznikiem

Otrzymamy dane przedstawione na rysunku nr 14.

rys. nr 14 — Nowa kolumna z tekstem za ogranicznikiem

Ostatnie nasze przekształcenie danych będzie dotyczyć wyodrębnienia tekstu między konkretnymi ogranicznikami. W tym celu rozwijamy polecenie Wyodrębnij (punkt 2 na rys. nr 15) z karty Dodaj kolumnę, a następnie wybieramy polecenie Tekst między ogranicznikami (punkt 3).

rys. nr 15 — Tekst między ogranicznikami

Otworzy nam się okno Tekstu między ogranicznikami, gdzie wpisujemy ograniczniki w polach Ogranicznik początkowy i końcowy, czyli w naszym przykładzie w obu polach ten sam znak (/). W opcjach zaawansowanych możemy jak w innych omawianych dziś przypadkach ustawić czy chcemy przeszukiwać dane od początku do końca czy odwrotnie. W pytaniu od widzów było zadanie znalezienia tekstu między przedostatnim a ostatnim ogranicznikiem, więc ustawiamy rozpatrywanie danych od prawej do lewej strony (od końca). W polu Liczba ograniczników początkowych do pominięcia wpisujemy 1 ogranicznik, natomiast w polu Liczba ograniczników końcowych do pominięcia zostawiamy 0. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 16).

rys. nr 16 — Okno tekstu między ogranicznikami

Otrzymamy dane przedstawione na rysunku nr 17, czyli tekst między ostatnim a przedostatnim ogranicznikiem.

rys. nr 17 — Nowa kolumna z tekstem między ogranicznikami

Możemy zagłębić się bardziej w opcje zaawansowane, ale wtedy łatwiej się zgubić, ponieważ opcja Skanuj w poszukiwaniu ogranicznika końcowego daje nam dwie możliwości. Po znalezieniu pierwszego ogranicznika w tej opcji możemy wybrać czy chcemy przeszukiwać dane w lewą stronę od tego ogranicznika czy w prawo. Pokażemy te opcje na przykładzie zmiany drugiego ogranicznika na znak @ oraz przy pominięciu jednego znaku @. Ustawione parametry powinny wyglądać jak na rysunku nr 18.

rys. nr 18 — Zmiany parametrów tekstu między ogranicznikami

Otrzymamy wtedy dane przedstawione na rysunku nr 19. Możemy zauważyć, że w górnych wierszach nie ma znaków @ po znaku /, więc Power Query wyciągnie nam cały tekst do końca. W ostatnim wierszu otrzymamy fragment tekstu między ostatnim znakiem / a drugim znakiem @, zgodnie z ustawionymi parametrami.

rys. nr 19 — zmienione dane

Najlepiej przeczytać dokładnie wszystkie opcje i przetestować ich działanie.

Tak przygotowane dane możemy załadować do Excela za pomocą polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 20).

rys. nr 20 — Zamknij i załaduj do

Otrzymamy dane załadowane do Excela. Zadanie to jest dużo szybsze w Power Query, musimy tylko pamiętać o dokładnym czytaniu parametrów oraz o tym w którą stronę czytamy dane (od początku do końca czy odwrotnie).


Przedsprzedaż Mistrz Excela II wydanie

Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą 😀)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.

Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".

Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach 😉 i z dużą ilością zdjęć.

Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.

Aktualnie w przedsprzedaży możesz mieć Mistrza Excela w obniżonej cenie:
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.

W ramach przedsprzedaży możesz mieć całą książkę za jedyne 30 zł (łącznie z darmową wysyłką listem ekonomicznym), a jeśli zależy Ci na dedykacji wystarczy, że dołożysz 10 zł.

Książka Mistrz Excela reklama

Power Query 37 — Dynamiczna ilość kolumn przy podziale ogranicznikiem — Funkcje języka M

W dzisiejszym poście zajmiemy się kontynuacją poprzedniego, a dokładnie drugim sposobem rozwiązania zadania polegającego na dynamicznym określeniu ilości kolumn przy podziale ogranicznikiem. Rozwiązanie to jest wykorzystywane przez Billa Szysza, jest ono przeznaczone dla bardziej zaawansowanych użytkowników, ponieważ wymaga większej znajomości funkcji i kodu M w Power Query. Zadanie to wykonamy po podstawie przykładowych danych przedstawionych na rysunku nr 1.

rys. nr 1 — Przykładowe dane

Pierwszym krokiem jest zaczytanie danych wejściowych do Power Query. W tym celu klikamy polecenie Z tabeli/zakresu z karty Dane (rys. nr 2).

rys. 2 — Z tabeli/ zakresu

Otworzy nam się edytor zapytań Power Query z wczytaną tabelą z danymi przedstawioną na rysunku nr 3.

rys. 3 — Edytor zapytań

Z zastosowanych kroków usuwamy niepotrzebny krok Zmieniono typ. Do dalszych działań potrzebujemy tylko kolumny z produktami więc klikamy prawym przyciskiem myszy na nagłówek kolumny Produkt i z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 4).

rys. 4 — Usuń inne kolumny

Otrzymamy interesujące nas dane przedstawione na rysunku nr 5.

rys.5 — Dane po usunięciu innych kolumn

Do tego momentu kroki w działaniu były takie same jak w poprzednim poście https://exceliadam.pl/?p=9720 . Aby ułatwić zrozumienie tematu nie zaczniemy pisać od razu funkcji w pasku formuły tylko dodamy kolumnę niestandardową z karty Dodaj kolumnę (rys. nr 6). Dodamy tą kolumnę, żeby to w niej zacząć pisać funkcję.

rys. 6 — Kolumna niestandardowa

Otworzy nam się okno Kolumny niestandardowej, gdzie w polu Formuły kolumny niestandardowej wpiszemy funkcję. Zapis powinien wyglądać następująco:

=Text.Split([Produkt], ", ")

W funkcji Text.Split argumentem jest kolumna Produkt. Następnie wpisujemy ogranicznik, czyli w naszym przypadku — przecinek  spacja ", ". Po wpisaniu formuły zatwierdzamy naszą kolumnę niestandardową przyciskiem OK (rys. nr 7).

rys. 7 — Parametry kolumny niestandardowej

Otrzymamy dane przedstawione na rysunku nr 8.

rys. 8 — Kolumna niestandardowa w postaci list

Polecenie to dzieli nasze kolumny na Listy. Pod każdym słowem List jest ukryta lista produktów (przykład przedstawiony został na rysunku nr 9).

rys. 9 — Dane ukryte pod nazwą List

Kolejnym krokiem jest policzenie elementów każdej z tych list. Użyjemy do tego funkcji List.Count. klikamy na nazwę kroku Dodano kolumnę niestandardową, aby otworzyć znowu okno Kolumny niestandardowej i wprowadzić zmiany w formule tej kolumny (rys. nr 10). Zapis powinien wyglądać następująco:

=List.Count(Text.Split([Produkt], ", "))

rys. 10 — zmiany wprowadzone w oknie kolumny niestandardowej

Zatwierdzamy zmienioną formułę przyciskiem OK i otrzymujemy dane przedstawione na rysunku nr 11.

rys. 11 — zmieniona kolumna niestandardowa

Podsumowując najpierw podzieliliśmy dane na Poszczególne listy za pomocą funkcji Text.Split a następnie policzyliśmy ilość elementów w każdej liście za pomocą funkcji List.Count. W pasku formuły możemy wprowadzić zmiany, aby uzyskać interesujący nas wynik. Zamieniamy funkcję Table.AddColumn na List.Transform oraz dodajemy argument Produkt. Formuła powinna wyglądać jak na rysunku nr 12.

=List.Transform(#"Usunięto inne kolumny" [Produkt], each List.Count(Text.Split(_,", ")))

rys. 12 — Formuła funkcji List.Transform

Po zatwierdzeniu formuły otrzymamy dane przedstawione na rysunku nr 13.

rys. 13 — Wynik funkcji List.Transform

Otrzymaliśmy listę wszystkich elementów na konkretnych listach. Za pomocą funkcji List.Transform wyciągnęliśmy z danych tylko jedną kolumnę. Powinniśmy pamiętać, że nie potrzebujemy listy wszystkich elementów tylko maksymalną wartość. Wystarczy wstawić w formułę na pasku formuł funkcję List.Max. Formuła powinna wyglądać jak na rysunku nr 14.

=List.Max(List.Transform(#"Usunięto inne kolumny" [Produkt], each List.Count(Text.Split(_,", "))))

rys. 14 — Zapis funkcji List.Max

Kiedy zatwierdzimy formułę klawiszem Enter otrzymamy wynik przedstawiony na rysunku nr 15.

rys. 15 Wynik maksymalny

Kolejny krok pokrywa się z krokiem z wcześniejszego filmu, czyli Power Query odcinek 36.

Kopiujemy nazwę kroku Usunięto inne kolumny po zaznaczeniu go i wciśnięciu klawisza F2, za pomocą skrótu klawiszowego Ctrl+C. Klikamy przycisk fx przy pasku formuły i wklejamy skopiowaną nazwę kroku za pomocą skrótu klawiszowego Ctrl+V. Otrzymamy dane przedstawione na rysunku nr 16.

rys. 16 Dane z kroku Usunięto inne kolumny

Rozwijamy polecenie Podziel kolumny (punkt nr 2 na rysunku nr 17) z karty Narzędzia główne, a następnie wybieramy polecenie Według ogranicznika (punkt nr 3 na rysunku nr 17).

rys. 17 — Podziel kolumny według ogranicznika

Otworzy nam się okno Dzielenia kolumny według ogranicznika, gdzie w polu wybierz lub wprowadź ogranicznik wybieramy opcję Niestandardowe (punkt nr 1 na rysunku nr 18). Następnie w polu, które się pojawi wpisujemy nasz ogranicznik, czyli przecinek i spacja. Zaznaczamy Podziel przy każdym wystąpieniu ogranicznika (punkt nr 2), Podziel na Kolumny (punkt nr 3) i wpisujemy liczbę kolumn 2 (wpisujemy mniejszy parametr bo będziemy go zmieniać). Tak ustawione parametry dzielenia kolumny zatwierdzamy przyciskiem OK.

rys. 18 — parametry dzielenia kolumny

Otrzymamy dane przedstawione na rysunku nr 19. Usuwamy niepotrzebny krok o nazwie Zmieniono typ.

rys. 19 — Podzielone dane

W pasku formuły w tym zapytaniu zmieniamy nazwy poszczególnych kolumn (podświetlone na rysunku nr 20) na odwołanie do wcześniejszego kroku

rys.20 zmiana nazw kolumn na odwołanie do poprzedniego kroku w pasku formuły

Klikamy nazwę wcześniejszego kroku — Dodano kolumnę niestandardową, wciskamy klawisz F2, a następnie kopiujemy nazwę kroku za pomocą skrótu klawiszowego Ctrl+C. Następnie tworzymy odwołanie do tego kroku, czyli w formule w miejscu zaznaczonym na rysunku nr 20 wklejamy nazwę naszego kroku poprzedzoną znakiem # (hash) i w podwójnym cudzysłowie. Zapis powinien wyglądać jak na rysunku nr 21.

rys. 21 — Zapis formuły

Po zatwierdzeniu formuły otrzymamy dane przedstawione na rysunku nr 22.

rys. 22 — Dane uzyskane z działania funkcji Table.TransformColumn

Otrzymaliśmy jedenaście kolumn dynamicznych, czyli zmieniających się pod wpływem zmian w danych wejściowych.

Jeśli chcemy mieć mniej kroków w działaniach możemy usunąć krok Niestandardowe1, do którego się odwołaliśmy w formule. Wystarczy, że w pasku formuły z rysunku nr 21 zastąpimy nazwę kroku Niestandardowe1 na Usunięto inne kolumny, ponieważ oba kroki zawierają takie same dane. Zapis formuły po zastąpieniu nazw powinien wyglądać jak na rysunku nr 23.

rys. 23 zmiana nazw kroków w pasku formuły

Następnie możemy usunąć już zbędny krok Niestandardowe1. Klikamy na nazwę kroku i wciskamy przycisk Delete. Pojawi nam się komunikat Usuwanie kroku, gdzie potwierdzamy usunięcie go za pomocą przycisku Usuń (rys. nr 24).

rys. 24 — Usuwanie kroku

Tak przygotowane dane możemy załadować do Excela korzystając z polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 25).

rys. 25 — Zamknij i załaduj do

Otworzy nam się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako Tabela i wskazujemy miejsce ich wstawienia, czyli Istniejący arkusz oraz wskazujemy konkretną komórkę. Tak określone parametry zatwierdzamy przyciskiem OK (rys. nr 26)

rys. 26 — Okno importowania danych

Otrzymamy dane zaimportowane do Excela przedstawione na rysunku nr 27.

rys. 27 — Dane zaimportowane do Excela

Możemy w danych źródłowych dopisać dodatkowy wiersz z danymi, aby sprawdzić działanie dynamicznych kolumn. Po odświeżeniu danych w tabeli z Power Query otrzymamy aktualne dane. Tabelę odświeżamy klikając prawym przyciskiem myszy na jej obszar a następnie korzystając z polecenia Odśwież z podręcznego menu.


Przedsprzedaż Mistrz Excela II wydanie

Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą 😀)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.

Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".

Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach 😉 i z dużą ilością zdjęć.

Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.

Aktualnie w przedsprzedaży możesz mieć Mistrza Excela w obniżonej cenie:
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.

W ramach przedsprzedaży możesz mieć całą książkę za jedyne 30 zł (łącznie z darmową wysyłką listem ekonomicznym), a jeśli zależy Ci na dedykacji wystarczy, że dołożysz 10 zł.

Książka Mistrz Excela reklama

Power Query 36 — Dynamiczna ilość kolumn przy podziale ogranicznikiem — Interfejs użytkownika

W dzisiejszym poście nauczymy się jak w Power Query stworzyć dynamiczną ilość kolumn przy podziale kolumny według ogranicznika. Rozwiązanie tego problemu zostało przedstawione na rysunku nr 1.

rys. nr 1 — Rozwiązanie zadania

W Power Query często pojawia się problem, kiedy dzielimy dane na kolumny według ogranicznika, to w zależności ile tych produktów było rozdzielonych tym ogranicznikiem, Power Query mógł się nie odświeżyć poprawnie. Power Query zapisuje konkretną ilość kolumn powstałych w kroku Podziel kolumnę według ogranicznika i kiedy dopiszemy więcej danych, nie pojawią się nowe kolumny. Rozwiązanie, które przedstawimy wykorzystuje interfejs użytkownika. W pliku do pobrania możecie znaleźć trzy rozwiązania. Pierwsze, którego nie będę omawiał, bo przy większej ilości danych (powyżej 10) Power Query się gubił. Drugie rozwiązanie podpowiedziane przez Billa Szysza, z wykorzystaniem interfejsu użytkownika oraz trzecie dla bardziej zaawansowanych użytkowników, wykorzystujące język M.

Temat ten omówimy na podstawie przykładowych danych z rysunku nr 2.

rys. nr 2 — Przykładowe dane

Wybieramy polecenie Z tabeli/zakresu z karty Dane (rys. nr 3).

rys. nr 3 — Z tabeli

Otworzy nam się Edytor zapytań Power Query z wczytaną tabelą danych. Usuwamy krok Zmieniono typ z Zastosowanych kroków. Interesuje nas tylko kolumna Produkt, więc klikamy na jej nagłówek prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 4).

rys. nr 4 — Usuń inne kolumny

Otrzymamy dane przedstawione na rysunku nr 5.

rys. nr 5 — Dane po usunięciu innych kolumn

Do prawidłowego działania zapytania przy użyciu interfejsu musimy dodać Kolumnę indeksu z karty Dodaj kolumnę (rys. nr 6).

rys. nr 6 — kolumna indeksu

Bez znaczenia czy jest indeksowana od wartości 0 czy 1, ważne aby w każdym wierszu były inne liczby. Otrzymamy dane przedstawione na rysunku nr 7.

rys. nr 7 — Dane z kolumną indeksu

W kolejnym etapie dzielimy naszą kolumnę Produkt po ograniczniku. Rozwijamy polecenie Podziel kolumny (punkt nr 2 na rysunku nr 8) z karty Przekształć i z listy wybieramy polecenie Według ogranicznika (punkt nr 3 na rysunku nr 8).

rys. nr 8 — Podziel kolumny

Otworzy nam się okno Dzielenia kolumny według ogranicznika, gdzie musimy określić parametry podziału. Wybieramy rodzaj ogranicznika — przecinek, następnie, kiedy dzielimy dane, czyli w naszym przykładzie przy każdym wystąpieniu ogranicznika. Następnie w opcjach zaawansowanych zaznaczamy podział na wiersze. Podział na kolumny ma ustawianą ilość kolumn na 11 ponieważ tyle najwięcej kolumny powstanie dla naszych danych, więc gdy dodamy większą ilość produktów Power Query ich nie pokaże, bo zapisze sobie w pamięci podział na 11 kolumn. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 9).

rys. nr 9 — Dzielenie kolumny według ogranicznika

Otrzymamy dane przedstawione na rysunku nr 10.

rys. nr 10 — Podzielone dane

Kolumna indeks odpowiednio się zduplikowała. Mamy w niej informacje, z którego wiersza pochodzą poszczególne dane. W kolejnym etapie możemy zrobić grupowanie po indeksie, żeby policzyć, ile było elementów w poszczególnych pozycjach w danych wejściowych. Wybieramy polecenie Grupowanie według z karty Przekształć (rys. nr 11).

rys. 11 — Grupowanie według

Otworzy nam się okno Grupowania według, w którym musimy określić jego parametry. W polu Grupowania według wybieramy kolumnę Indeks, Wpisujemy nazwę nowej kolumny (Liczność) i wybieramy operację jaka ma zostać wykonana, czyli w naszym przykładzie Zlicz wiersze. Zatwierdzamy przyjęte założenia przyciskiem OK (rys. nr 12).

rys. 12 — Parametry grupowania danych

Otrzymamy dane przedstawione na rysunku nr 13.

rys. 13 — pogrupowane dane

W powyższych wynikach interesuje nas maksymalna wartość z kolumny Liczność. Zaznaczamy kolumnę Liczność i rozwijamy polecenie Statystyka (punkt nr 2 na rysunku nr 14) z karty Przekształć, a następnie wybieramy polecenie Maksimum (punkt nr 3 na rysunku nr 14).

rys. nr 14 — Maksimum

Power Query wyciągnie nam, jako wynik, maksymalną wartość i otrzymamy daną przedstawioną na rysunku nr 15.

rys. 15 — Maksymalny wynik

Przechodzimy do kroku Usunięto inne kolumny w Zastosowanych krokach, czyli do danych przedstawionych na rysunku nr 5 wyżej, następnie kopiujemy po wciśnięciu klawisza F2 nazwę tego kroku za pomocą skrótu klawiszowego Ctrl+C. Dołożymy teraz nowy krok w działaniu – zmienimy formułę w pasku formuły, a dokładniej zastąpimy nazwę kroku Obliczona wartość maksymalna na tą, którą skopiowaliśmy wcześniej, czyli Usunięto inne kolumny (rys. nr 16).

rys. 16 — Pasek formuły

Zmianę tą zatwierdzamy przyciskiem Enter i otrzymamy dane przedstawione na rysunku nr 17.

rys. 17

W kroku Niestandardowe1 mamy kopię kroku Usunięto inne kolumny. Na tym etapie rozwijamy polecenie Podziel kolumny z karty Narzędzia główne, a następnie wybieramy polecenie Według ogranicznika (rys. nr 18).

rys. 18 — Podziel kolumny

Otworzy nam się okno Dzielenia kolumny według ogranicznika. W polu wybierz lub wprowadź ogranicznik wybieramy opcję Niestandardowe, a następnie wpisujemy ogranicznik przecinek i spacja (, ). Na liście Podziel przy wybieramy opcję Każde wystąpienie ogranicznika. Następnie w opcjach zaawansowanych wybieramy podział na kolumny i wpisujemy ilość kolumn, na która zostanie podzielona kolumna jako wartość 2. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 19).

rys.19 — Parametry dzielenia według ogranicznika

Otrzymamy dane przedstawione na rysunku nr 20. Usuwamy krok Zmieniono typ z zastosowanych kroków.

rys. 20 — Podzielone dane

Gdybyśmy mieli wpisaną wartość domyślną 11, jako liczbę kolumn, na która podzielić kolumny na rysunku nr 19, to Power Query w pasku formuły wypisałby 11 nazw (rys. nr 21).

rys.21 Ilość nazw kolumn wyznaczona na pasku formuły

Użyliśmy tutaj funkcji Table.SplitColumn, więc kiedy podejrzymy sobie jej działanie tej funkcji otrzymamy między innymi informacje przedstawione na rysunku nr 22.

rys. 22 — Table.SplitColumn

Interesuje nas opcja ColumnNamesOrNumbers, czyli liczba kolumn bądź ich nazwy. W pasku formuły zamiast nazw kolumn (podświetlone na rysunku nr 23) wpisujemy wartość 11.

rys. nr 23 — Pasek formuły

Zatwierdzamy zmiany w formule przyciskiem Enter i otrzymujemy dane przedstawione na rysunku nr 24.

rys. 24 — Dane

Nie chcemy, aby wartość 11 była wpisana na stałe. Chcemy aby Power Query pobierał nam tą wartość z poprzedniego kroku, czyli z kroku Obliczona wartość maksymalna. W tym celu kopiujemy (po wciśnięciu klawisza F2) nazwę tego kroku za pomocą skrótu klawiszowego Ctrl+C. Teraz zamiast wartości 11 w pasku formuły wklejamy nazwę skopiowanego kroku poprzedzoną znakiem # (hash) i w cudzysłowie (rys. nr 25).

rys. 25 — Zmiany w pasku formuły

Po zatwierdzeniu formuły klawiszem Enter, otrzymujemy dane przedstawione na rysunku nr 26.

rys. 26 — Końcowe dane

Tak przygotowane dane możemy zaimportować do Excela. W tym celu wybieramy polecenie Zamknij i załaduj do z karty Narzędzia główne (rys. nr 27).

rys. 27 — Zamknij i załaduj do

Otworzy nam się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako Tabela i wskazujemy miejsce ich wstawienia, czyli Istniejący arkusz oraz wskazujemy konkretną komórkę. Tak określone parametry zatwierdzamy przyciskiem OK (rys. nr 28)

rys. 28 — Okno importowania danych

Otrzymamy dane przedstawione na rysunku nr 29.

rys. 29 Dane zaimportowane do Excela

Jeśli w danych wejściowych w wierszu nr 5, który rozpatrujemy, dopiszemy sobie dodatkowy produkt, a następnie odświeżymy nasze dane z Power Query, otrzymamy powiększoną tabelę o ten dopisany produkt. Tabela wczytana z Power Query jest tabelą dynamiczną, która reaguje po odświeżeniu na wprowadzane zmiany w danych bazowych. To rozwiązanie wykonaliśmy za pomocą interfejsu, natomiast w kolejnym filmie skorzystamy z kodu M (rozwiązanie podpowiedziane przez Billa Szysza).


Przedsprzedaż Mistrz Excela II wydanie

Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą 😀)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.

Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".

Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach 😉 i z dużą ilością zdjęć.

Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.

Aktualnie w przedsprzedaży możesz mieć Mistrza Excela w obniżonej cenie:
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.

W ramach przedsprzedaży możesz mieć całą książkę za jedyne 30 zł (łącznie z darmową wysyłką listem ekonomicznym), a jeśli zależy Ci na dedykacji wystarczy, że dołożysz 10 zł.

Książka Mistrz Excela reklama

Power Query 35 — Liczba arkuszy w pliku, ukryte i widoczne arkusze

W dzisiejszym poście policzymy liczbę arkuszy w pliku z podziałem na arkusze ukryte,  widoczne i wszystkie. W odcinku VBA #24 (
https://exceliadam.pl/wp-admin/post.php?post=9634&action=edit ) wykonaliśmy to zadanie w VBA. W tej poradzie policzymy arkusze za pomocą dodatku do Excela – Power Query. Przykładowe wyniki naszej operacji możemy zobaczyć na rysunku nr 1.

rys. nr 1

Rozwijamy polecenie Pobierz dane (punkt nr 2 na rysunku nr 2) z karty Dane (grupa poleceń Pobieranie i przekształcanie danych), następnie rozwijamy polecenie Z pliku (punkt nr 3) i wybieramy polecenie Ze skoroszytu (punkt nr 4).

rys. nr 2 — Ze skoroszytu

Otworzy nam się okno Importowania danych, gdzie musimy odszukać nasz plik (odpowiedni dla tego postu). Wybieramy plik i zatwierdzamy nasz wybór przyciskiem Importuj (rys. nr 3).

rys. nr 3 — Importowanie danych

Otworzy nam się okno Nawigatora, gdzie wybieramy dowolny arkusz z pliku, a następnie zatwierdzamy wybór przyciskiem Edytuj (rys. nr 4). Ten krok nie do końca jest właściwy, ale ze względu na to iż korzystamy z interfejsu, ułatwia nam pracę.

rys. nr 4 — Nawigator

Otworzy nam się okno Edytora Power Query z pustym arkuszem, który wybraliśmy. Nie potrzebujemy tutaj żadnych kroków, więc w panelu bocznym Ustawień zapytania usuwamy z Zastosowanych kroków kroki: Zmieniono typ oraz Nawigacja (oznaczone strzałką na rysunku nr 5).

rys. nr 5 — edytor zapytań

My potrzebujemy zachować tylko jeden krok, mianowicie Źródło. Otrzymamy wtedy dane przedstawione na rysunku nr 6.

rys. nr 6 — Dane w edytorze zapytań

Power Query sam napisał ten krok, nie musiałem tego robić sam, ponieważ jest on skomplikowany. Widać na pasku formuły cała ścieżkę dostępu do tego pliku (rys. nr 7) oraz funkcję jaka jest potrzebna do wczytania pliku Excela. Power Query wyciągnął również elementy jakie zawiera dany skoroszyt.

rys. nr 7 — Pasek formuły

Nasz plik Power Query jest plikiem "prostym", ponieważ w kolumnie Kind ma elementy tylko jednego rodzaju – Sheet czyli arkusz. Gdyby nasz plik zawierał tabele lub oznaczone zakresy to one również pojawiły by się w tej tabeli.

Gdybyśmy mieli więcej rodzajów elementów w kolumnie Kind to moglibyśmy przefiltrować dane, żeby otrzymać tylko te które nas interesują. Rozwijamy ikonę trójkącika przy nazwie kolumny Kind i z podręcznego menu rozwijamy polecenie Filtry tekstu a następnie wybieramy polecenie Równa się (rys. nr 8).

rys. nr 8 — Filtry tekstu

Otworzy nam się okno Filtrowania wierszy, gdzie wpisujemy, że chcemy zachować wiersze, w których Kind równa się Sheet. Tak wpisany parametr zatwierdzamy przyciskiem OK (rys. nr 9).

rys. nr 9 — Filtrowanie wierszy

Nałożyliśmy dodatkowy filtr na wypadek gdyby w naszych danych pojawiły się nowe elementy takie jak np. tabela. Na rysunku nr 6 w kolumnie Hidden mamy zawartą informację, czy dany arkusz jest widoczny czy ukryty. False dla widocznego arkusza, natomiast True dla arkusza ukrytego.

Aby na podstawie takiej tabelki zliczyć ilość wszystkich arkuszy wybieramy polecenie Zlicz wiersze na karcie Przekształć (rys. nr 10).

rys. nr 10 — Zlicz wiersze

Otrzymamy wynik przedstawiony na rysunku nr 11. Ta postać wyniku nie jest zbyt użyteczna do dalszych działań.

rys. nr 11

Chcemy później wykonać proste operacje więc wystarczy te dane przekształcić do postaci tabeli. W tym celu wybieramy polecenie Do tabeli z karty Przekształć (rys. nr 12).

rys. nr 12 — Do tabeli

Otrzymamy poprzedni wynik w formie tabelarycznej, co ułatwi nam kolejne działania na tym wyniku (rys. nr 13).

rys. nr 13 — Dane

W kolejnym etapie chcemy policzyć arkusze ukryte i widoczne. W tym celu musimy zduplikować nasze zapytanie. Klikamy prawym przyciskiem myszy na nazwę zapytania PQ35 i z podręcznego menu wybieramy polecenie Duplikuj (rys. nr 14).

rys. nr 14 — Duplikuj

W zduplikowanym zapytaniu pousuwać Zastosowane kroki do momentu Przefiltrowano wiersze, ponieważ właśnie w tym kroku mamy informacje o ukrytych i widocznych arkuszach. Otrzymamy dane przedstawione na rysunku nr 15.

rys. nr 15 — Dane

Najłatwiej policzyć ilość poszczególnych arkuszy grupując dane. W tym celu wybieramy polecenie Grupowanie według z karty Narzędzia główne (rys. nr 16).

rys. nr 16 — Grupowanie według

Otworzy nam się okno Grupowania według, gdzie możemy ustawić parametry grupowania danych. Zmieniamy nazwę nowej kolumny na Liczba arkuszy, wybieramy rodzaj operacji jako Zlicz wiersze i tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 17).

rys. nr 17 — Parametry grupowania

Otrzymamy zliczone arkusze ukryte i widoczne przedstawione na rysunku nr 18.

rys. nr 18 — Pogrupowane dane

W kolejnym etapie chcemy zmienić nazwy elementów z Hidden, FALSE i TRUE na widoczne i ukryte. Najprostszym sposobem jest dodanie nowej kolumny. Zaznaczamy kolumnę Hidden i wybieramy polecenie Kolumna warunkowa z karty Dodaj kolumnę (rys. nr 19).

rys. nr 19 — kolumna warunkowa

Otworzy nam się okno Dodawania kolumny warunkowej. W punkcie oznaczonym na rysunku nr 20 numerem 1 zmieniamy nazwę nowej kolumny na Typ arkuszy, następnie w polu Nazwa kolumny wybieramy Hidden (punkt nr 2). Operator ustawiamy jako Równa się (punkt nr 3). W polu Wartość wpisujemy FALSE (punkt nr 4). Jeśli warunek się zgadza (ma wartość FALSE) to chcemy otrzymać Widoczny (wartość wyjściowa – punkt nr 5). W przeciwnym wypadku chcemy otrzymać wartość Ukryty (punkt nr 6). Tak ustawione parametry zatwierdzamy przyciskiem OK.

rys. nr 20 — Dodawanie kolumny warunkowej

Otrzymamy dane, w których usuwamy niepotrzebną kolumnę Hidden, klikając prawym przyciskiem myszy na tytuł kolumny i wybierając z podręcznego menu polecenie Usuń. Ponadto przenosimy kolumnę Typ arkusza przed kolumnę Liczba arkuszy. Uzyskamy postać danych przedstawioną na rysunku nr 21.

rys. nr 21

W zapytaniu PQ35 mamy podaną liczbę wszystkich arkuszy, natomiast w zapytaniu PQ35 (2) mamy liczbę arkuszy widocznych i ukrytych. W Power Query nie mamy standardowego pojęcia Sumy jak w Excelu, więc musimy połączyć oba zapytania. W tym celu rozwijamy polecenie Połącz (punkt nr 2 na rysunku nr 22) z karty Narzędzia główne i wybieramy polecenie Dołącz zapytania (punkt nr 3).

rys. nr 22 — Dołącz zapytania

Otworzy nam się okno Dołączania, gdzie wybieramy ilość tabel, jakie chcemy połączyć. A następnie wybieramy tabelę do dołączenia, czyli PQ35 (rys. nr 23). Tak ustawione parametry zatwierdzamy przyciskiem OK.

rys. nr 23 — Dołączanie

Otrzymamy dane przedstawione na rysunku nr 24.

rys. nr 24

Przy dołączaniu pojawia się problem, polegający na tym, że zapytanie PQ35 zawierało tylko jedną kolumnę, brakuje kolumny Typ arkusza. Pierwszym sposobem rozwiązania tego problemu jest zmiana wartości null na Wszystkie, w tym celu wybieramy ikonkę oznaczoną strzałką na rysunku nr 25 w karcie Przekształć.

rys. nr 25 ikonka na karcie Przekształć

Otworzy nam się okno Zamieniania wartości, gdzie możemy zmienić wartość null na dowolną jaką wpiszemy, w naszym przykładzie Wszystkie (rys. nr 26). Zmianę tą zatwierdzamy przyciskiem OK.

rys. nr 26 — Zamienianie wartości

Otrzymamy dane przedstawione na rysunku nr 27.

rys. nr 27

Drugim sposobem rozwiązania tego problemu jest dodanie nowej kolumny w zapytaniu PQ35. W tym celu wybieramy polecenie Kolumna niestandardowa z karty Dodaj kolumnę (rys. nr 28).

rys. nr 28 — Kolumna niestandardowa

Otworzy nam się okno Kolumny niestandardowej, gdzie zmieniamy jej nazwę na Typ arkusza, a następnie w polu Formuła kolumny niestandardowej wpisujemy stałą wartość "Wszystkie". Tak ustawione parametry kolumny zatwierdzamy przyciskiem OK (rys. nr 29).

rys. nr 29 — dodawanie kolumny niestandardowej

Otrzymamy dane przedstawione na rysunku nr 30.

rys. nr 30

Po przejściu na zapytanie PQ 35 (2) otrzymamy zaktualizowane dane przedstawione na rysunku nr 31.

rys. nr 31

Nie ma znaczenia kolejność kolumn w zapytaniu które dołączamy, ponieważ o tej kolejności decyduje nasze główne zapytanie. Tak przygotowane dane możemy załadować do Excela. W tym celu wybieramy polecenie Zamknij i załaduj do z karty Narzędzia główne (rys. nr 32).

rys. nr 32 — Zamknij i załaduj do

Otworzy nam się okno Importowanie danych. Okienko to może wyglądać różnie w zależności jaka aktualizację Power Query posiadamy. Ustawiamy sposób wyświetlania danych jako Tabela. Mamy dwa zapytania dlatego nie możemy podać konkretnej lokalizacji. Wybieramy Nowy arkusz i zatwierdzamy przyciskiem OK. (rys. nr 33).

rys. nr 33 — Importowanie danych

Otrzymamy dane wczytane do Excela przedstawione na rysunku nr 34 .

rys. nr 34 — Dane w Excelu

Co jest ważne stworzyliśmy właśnie nowe arkusze, więc kiedy odświeżymy nasze dane, otrzymamy zaktualizowane wyniki z dodatkowymi arkuszami przedstawione na rysunku nr 35.

rys. nr 35

Należy bezwzględnie pamiętać o zapisaniu zmian w pliku przed odświeżeniem zapytania. Nie chcemy mieć poszczególnych wyników z zapytań. Interesuje nas tylko wynik sumaryczny z zapytania PQ35 (2). Wystarczy, że klikniemy prawym przyciskiem myszy na arkusz z zapytaniem PQ35 i z podręcznego menu wybierzemy polecenie Usuń (rys. nr 36).

rys. nr 36 — Usuń

Zapytanie to zostaje tylko w pamięci, a nie zajmuje osobnego arkusza. Zapytanie PQ35 (2) możemy zaznaczyć i wyciąć jak zwykłą tabelkę. Po czym wkleić do arkusza, w którym chcemy mieć wynik. Pusty arkusz z którego wycięliśmy zapytanie możemy również usunąć jak wyżej na rysunku nr 36. Na tym etapie po wprowadzeniu zmian musimy zapisać plik za pomocą skrótu klawiszowego Ctrl + S. Następnie klikamy prawym przyciskiem myszy na zapytanie i z podręcznego menu wybieramy polecenie Odśwież. Otrzymamy odświeżone dane bez arkuszy które usunęliśmy (rys. nr 37).

rys. nr 37 Ostateczne dane

Przedsprzedaż Mistrz Excela II wydanie

Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą 😀)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.

Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".

Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach 😉 i z dużą ilością zdjęć.

Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.

Aktualnie w przedsprzedaży możesz mieć Mistrza Excela w obniżonej cenie:
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.

W ramach przedsprzedaży możesz mieć całą książkę za jedyne 30 zł (łącznie z darmową wysyłką listem ekonomicznym), a jeśli zależy Ci na dedykacji wystarczy, że dołożysz 10 zł.

Książka Mistrz Excela reklama