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).
Książka Mistrz Excela + promo na 35 urodziny
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.
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 promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W
dzisiejszym poście omówimy temat rozrastającej się listy rozwijanej z
unikatowymi wartościami w
Power Query. Rozwiązanie tego zadania w Excelu przedstawione zostało w poradzie
nr 322 https://exceliadam.pl/?s=porada+322
. W danych źródłowych mamy już
listę rozwijaną a naszym zadaniem jest dodanie do niej kolejnych elementów,
chcemy dopisać do listy nowe osoby. Dane, na których omówimy to zagadnienie
zostały przedstawione na rysunku nr 1.
rys. nr 1 — Przykładowe dane
Rozwiążemy takie zadanie nie korzystając z formuł, ale przy użyciu Power Query – dodatku do Excela. Pierwszym krokiem jest zaczytanie danych do Power Query. Wybieramy polecenie Z tabeli z karty Dane (rys. nr 2).
rys. nr 2 — Z tabeli
Otworzy
nam się edytor zapytań z wczytaną tabelą tSprzedawcy.
rys. nr 3 — Edytor zapytań
Usuwamy krok Zmieniono typ z zastosowanych kroków, bo jest on zbędny.
Następnie odfiltrowujemy kolumnę Sprzedawca po wartościach null, czyli klikamy
na ikonkę trójkąta w nazwie kolumny Sprzedawca i w podręcznym menu ozdnaczamy
checkbox przy wartości null (rys. nr 4). Nasz filtr zatwierdzamy przyciskiem
OK.
rys. nr 4 — Odfiltruj dane po wartości null
W kolejnym kroku usuwamy inne kolumny, czyli klikamy prawym przyciskiem
myszy na tytuł kolumny Sprzedawca i z podręcznego menu wybieramy polecenie Usuń
inne kolumny (rys. nr 5).
rys. nr 5 — Usuń inne kolumny
Otrzymamy dane przedstawione na rysunku nr 6.
rys. nr 6
Interesuje nas tylko kolumna Sprzedawca. Chcemy mieć unikatową listę
sprzedawców, więc rozwijamy polecenie Usuń wiersze (punkt nr 2 na rysunku nr 7)
z karty Narzędzia główne, a następnie wybieramy polecenie Usuń duplikaty (punkt
nr 3 na rysunku nr 7).
rys. nr 7 — Usuń duplikaty
Tak przygotowaną listę danych możemy załadować do Excela. W tym celu
rozwijamy polecenie Zamknij i załaduj (punkt nr 2 na rysunku nr 8) z karty
Narzędzia główne, a następnie wybieramy polecenie Zamknij i załaduj do (punkt
nr 3 na rysunku nr 8).
rys. nr 8 — Zamknij i załaduj do
Otworzy nam się okno Ładowania do, gdzie wybieramy sposób wyświetlania
danych jako Tabela, a nastepnie określamy lokalizaję wstawienia danych –
Istaniejący arkusz i wskazujemy konkretną komórkę. Tak ustawione parametry
zatwierdzamy przyciskiem Załaduj (rys. nr 9).
rys. nr 9 — Okno Ładowania do
Otrzymamy dane wczytane do Excela przedstawione na rysunku nr 10.
rys. nr 10 — Dane wczytane do Excela
Zaznaczamy zakres danych w tabeli z zapytania z Power Query a następnie w
polu obok paska formuły zmieniamy nazwę tego zakresu na Sprzedawcy (pole
oznaczone zieloną strzałką na rysunku nr 11).
rys. nr 11 — Zmiana nazwy zakresu
W kolejnym etapie zaznaczamy zakres w tabeli z danymi źródłowymi i
wybieramy polecenie Poprawność danych (punkr nr 2 na rysunku nr 12) z karty Dane.
rys. nr 12 — Poprawność danych
Otworzy nam się okno Sprawdzania poprawności danych, gdzie w karcie
Ustawienia (rys. nr 13) ustalamy Kryteria poprawności danych i podajemy źródło
danych (klawisz F3) – wcześniej nazwany zakres Sprzedawcy z tabeli zaczytanej z
Power Query. W karcie Komunikat wejściowy odznaczamy checkbox przy opcji
Pokazuj komunikat wejściowy przy wyborze komórki. W karcie Alert o błędzie
odznaczamy checkbox przy opcji Pokazuj alerty po wprowadzeniu nieprawidłowych
danych. Nie chcemy informacji o błędnie wpisanych danych ponieważ chcemy
dopisywać nowe osoby do listy sprzedawców. Tak ustawione parametry zatwierdzamy
przyciskiem OK.
rys. nr 13 — Użycie klawisza F3
Teraz możemy sobie dopisać sprzedawcę w
tabeli z danymi źródłowymi, ale nie ma jej na liście rozwijanej w tej tabeli co
przedstawia rysunek nr 14.
rys. nr 14 — Lista rozwijana
Wynika to z podstawowej wady Power Query –
nie odświeża się automatycznie. Musimy kliknąc prawym przyciskiem myszy na
dowolną komórkę w zakresie Sprzedawcy i z podręcznego menu wybrać polecenie
Odśwież (rys. nr 15).
rys. nr 15 — Odśwież
Po odświeżeniu danych z Power Query dodany sprzedawca będzie widoczny na liście
rozwijanej (rys. nr 16).
rys. nr 16
Istnieje możliwość ustawienia automatycznego odświeżania
danych za pomocą kodu VBA. Korzystając ze skrótu klawiszowego Alt+F11 możemy
przejść do okna Edytora VBA. Jest tam wcześniej przygotowany kod (rys. nr 17).
rys. nr 17 — Alt+F4 przejście do VBA
W arkuszu (Arkusz4 (PQ31)- punkt nr 1 na rysunku nr 18), w
którym mamy te listy , musimy dopisać kod VBA. Kod ten będzie działał tylko w
momencie, kiedy w naszym arkuszu (Worksheet – punkt nr 2 na rysunku nr 18))
dokona się zmiana (Change – Punkt nr 3 na rysunku nr 18). W sytuacji zmiany w
kolumnie A, chcemy aby odpalił się kod VBA i sprawdził czy zmieniane komórki
miały część wspólną z kolumną A. Konkretnie sprawdzamy czy zakres który był
zmieniany ma część wspólną z kolumną która nas interesuje. Jeśli zmiana
nastąpiła w kolumnie A, to nastąpi automatyczne odświeżenie danych w tabeli z
Power Query.
rys. nr 18 — Edytor VBA
Zapisujemy nasz kod za pomocą skrótu klawiszowego Ctrl+S.
Przechodzimy do Excela i możemy sprawdzić działanie kodu VBA. Dopisujemy
kolejnego sprzedawcę (Agnieszka) do danych źródłowych i dane automatycznie się
odświeżą i nasz nowy sprzedawca zostanie dodany do listy rozwijanej, co widać
na rysunku nr 19.
rys. nr 19 — Dane z kodem z VBA
Podsumowując rozrastającą się listę rozwijaną z unikatowymi
wartościami robi się prościej za pomocą Power Query, ale niestety nie jest
automatyczna i musimy pamiętać o odświeżaniu danych. Jedynym sposobem na
zautomatyzowanie jest dodanie kodu VBA, ale to już temat dla bardziej
zaawansowanych użytkowników Excela.
Możemy również zarejestrować makro odświeżania danych w
karcie Deweloper, wybierając polecenie Rejestruj makro (punkt nr 2 na rysunku
nr 20).
rys. nr 20 — Zarejestruj makro
Otworzy się okno Rejestrowania makra, gdzie wpisujemy nazwę
makra i zatwierdzamy przyciskiem OK (rys. nr 21).
rys. nr 21 — Okno rejestrowania makra
Następnie klikamy prawym przyciskiem myszy na dowolną komórkę
z zakresu zapytania z Power Query i z podręcznego menu wybieramy polecenie
Odśwież (rys. nr 22).
rys. nr 22 — Odśwież
Następnie klikamy polecenie Zatrzymaj rejestrowanie (punkt nr
2 na rysunku nr 23) z karty Deweloper.
rys. nr 23 — zatrzymaj rejestrowanie makra
Teraz w VBA mamy dostępny nowy Moduł, odpowiadający
odświeżeniu danych (zaznaczony zieloną strzałką na rysunku nr 24).
rys. nr 24 — Nowy moduł
Dzięki stworzeniu takiego makra mamy automatycznie
rozrastającą się listę rozwijaną.
Książka Mistrz Excela + promo na 35 urodziny
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.
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 promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
