0
0 Produkty w koszyku

No products in the cart.

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

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.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
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 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.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama

Power Query 34 — Cena produktu na podstawie daty zamówienia — funkcja Table.SelectRows

W dzisiejszym poście omówimy sobie wyznaczenie ceny produktu na podstawie daty zamówienia. Zadanie to w Power Query rozwiązaliśmy już w poprzednim poście nr 33 https://exceliadam.pl/?p=9575 , za pomocą poleceń ze wstążki. W tym poście rozwiążemy ten problem przy użyciu funkcji Table.SelectRows. Temat ten omówimy na podstawie przykładowych danych przedstawionych na rysunku nr 1.

rys. nr 1 — Przykładowe dane

Zadanie polega na znalezieniu ceny danego produktu na podstawie daty zamówienia. Mamy podane również daty, kiedy cena ta ulegała zmianie.

Zakładamy, że dwie tabelki z rysunku nr 1 mamy zaczytane do Power Query z rozwiązania przedstawionego w poprzednim poście. Aby mieć podgląd zapytań wybieramy polecenie Pokaż zapytania z karty Dane (rys. nr 2).

rys. nr 2 — pokaż zapytania

Następnie klikamy dwa razy myszką na nazwę naszego zapytania w oknie Zapytań dotyczących skoroszytu, aby przejść do Power Query (rys. nr 3).

rys. nr 3 — Lista zapytań dotyczących skoroszytu

Otworzy nam się edytor zapytań z wczytaną tabelą tCeny oraz tZamówienia (oznaczone zielonym prostokątem na rysunku nr 4). Naszym zadaniem jest scalenie tych zapytań. Rozwijamy polecenie Połącz (punkt nr 2 na rysunku nr 4) z karty Narzędzia główne, następnie rozwijamy polecenie Scal zapytania (punkt nr 3) i wybieramy polecenie Scal zapytania jako nowe (punkt nr 4).

rys. nr 4 — Scal zapytania jako nowe

Otworzy nam się okno Scalanie, w którym musimy określić jego parametry. W obu zapytaniach zaznaczamy kolumnę Produkt, ponieważ po tym elemencie chcemy scalić zapytania. Wybieramy rodzaj sprzężenia jako Lewe zewnętrzne, czyli wszystkie elementy z pierwszego zapytania i pasujące z drugiego. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 5).

rys. nr 5 — Parametry scalania

Otrzymamy scalone dane przedstawione na rysunku nr 6.

rys. nr 6 — Scalone dane

W kolejnym etapie zmieniamy nazwę kroku na Scalone2 we właściwościach w Ustawieniach zapytania. Pierwszy krok w Power Query jest identyczny z rozwiązaniem przedstawionym w poprzednim poście. W danych tych mamy już ukryte interesujące nas informacje, czyli pod słowem Table kryje się tabela z datami zmiany ceny produktu (rys. nr7).

rys. nr 7 — Dane ukryte pod Table

Dane w tabelce poniżej musimy odfiltrować, czyli wyrzucić z niej te daty które nie pasują do daty zamówienia. Dla przypomnienia – nasze dane posortowaliśmy wcześniej po produkcie i dacie zmiany ceny, dzięki temu możemy teraz relatywnie prosto znaleźć rozwiązanie. Z karty Dodaj kolumnę wybieramy polecenie Kolumna niestandardowa (rys. nr 8).

rys. nr 8 — Kolumna niestandardowa

Otworzy nam się okno kolumny niestandardowej, gdzie zmieniamy nazwę nowej kolumny na REC (jak Bill Szysz w swoim rozwiązaniu). Następnie w polu Formuła kolumny niestandardowej wpisujemy formułę funkcji Table.SelectRows. Zapis jej powinien wyglądać następująco:

=Table.SelectRows([tCeny] each [Data zmiany ceny] <= #date (2018,2,1))

Funkcja ta pozwoli nam wyciągnąć konkretne wiersze z kolumny tCeny pod nałożonym warunkiem, czyli dla każdego wiersza gdzie Data zmiany ceny jest mniejsza bądź równa dacie zamówienia. Wpiszemy w formule zamiast Daty zamówienia znak # ponieważ na razie nie możemy sięgnąć po datę z tej kolumny. Tak ustawione parametry zatwierdzamy klikając przycisk OK (rys. nr 9).

rys. nr 9 — Parametry kolumny niestandardowej

Otrzymamy dane przedstawione na rysunku nr 10.

rys. nr 10 — Dane

Formuła ta ogranicza nam wyniki lecz nie we właściwy sposób. My chcemy odwoływać się nie do konkretnej podanej daty a do innej daty dla każdego wiersza, czyli do daty zamówienia każdego produktu. Nie możemy ręcznie wpisać w formule odwołania do kolumny Data zamówienia ponieważ funkcja zwróci nam błąd (nie może znaleźć pola). Rys. nr 11.

rys. nr 11 — Error

Kiedy wpiszemy nazwę funkcji w pasku formuły Power Query wyświetli nam informacje o niej. Możemy wpisywać w niej albo wartości stałe albo wartości z wierszy z tej tabeli. Nie możemy użyć kolumny Data zamówienia ponieważ nie zawiera się w tabelach ukrytych pod Table w kolumnie tCena (rys. nr 12).

rys. nr 12 

Bill Szysz podpowiedział mi rozwiązanie tego problemu. Możemy sobie wkleić funkcję (x) w funkcję Table.SelectRows. Funkcja ta to tak naprawdę cały wiersz naszych danych. Zapis powinien wyglądać następująco:

=Table.AddColumn (Źródło, "REC", (x) => Table.SelectRows (x[tCeny], each [Data zmiany ceny] <= x[Data zamówienia]))

Otrzymamy dane przedstawione na rysunku nr 13, gdzie nadal czasem w wynikach mamy dwa wiersze.

rys.nr 13 — dane

Aby uzyskać pojedyncze rozwiązanie musimy wstawić kolejną funkcję. Pamiętamy, że nasze dane posortowaliśmy według Produktu i Daty zmiany ceny więc wynikiem będzie ostatni wiersz.

rys. nr 14 — Wynik prawidłowy

Zapis funkcji będzie wyglądał następująco:

= Table.AddColumn (Źródło, "REC", (x) => Table.Last( Table.SelectRows (x[tCeny], each [Data zmiany ceny] <= x[Data zamówienia])))

Otrzymamy tym razem pojedyncze wyniki dla każdego wiersza przedstawione na rysunku nr 15.

rys. nr 15 pojedyncze wyniki dla każdego wiersza

Aby wyciągnąć dane, które nas interesują, czyli cenę, klikamy na ikonkę ze strzałkami w nazwie kolumny REC i zaznaczamy checkbox przy wartości Cena i odznaczamy checkbox przy poleceniu Użyj oryginalnej nazwy kolumny jako prefiksu. Tak ustawione parametry zatwierdzamy klikając przycisk OK (rys. nr 16).

rys. nr 16 — Odfiltrowanie danych

Otrzymamy dane przedstawione na rysunku nr 17. 

rys. nr 17 — Odfiltrowane dane

Na tym etapie możemy już usunąć kolumnę tCeny.

Aby uczyć się formuł w Power Query możemy usunąć krok Rozwinięty element REC z Zastosowanych kroków i wprowadzić kolejny element do formuły naszej funkcji. Poprzednia formuła zwróciła nam Rekord, czyli jeden cały wiersz. Kiedy dopiszemy w formule [Cena], otrzymamy już interesujący nas wynik (rys. nr 18). Zapis formuły powinien wyglądać następująco: 

= Table.AddColumn (Źródło, "REC", (x) => Table.Last( Table.SelectRows (x[tCeny], each [Data zmiany ceny] <= x[Data zamówienia])) [Cena])

rys. nr 17 — Aktualne dane

Klikamy prawym przyciskiem myszy na nazwę kolumny tCeny i za pomocą polecenia Usuń z podręcznego menu usuwamy niepotrzebną kolumnę (rys. nr 19).

rys. nr 19 — Usuń kolumnę

Możemy jeszcze w zapisie formuły zmienić nazwę kolumny REC na Cena. Otrzymamy dane przedstawione na rysunku nr 20.

rys. nr 20 — Dane

Podsumowując dziś omówiony sposób może wydawać się trudniejszy, ale gdy się nauczymy funkcji i formuł Power Query rozwiązywanie tego typu problemów zajmie nam chwilę. Skomplikowane w tym rozwiązaniu jest to, że łączymy pracę na tabelach wewnętrznych (ukrytych pod wartościami Table) oraz na tabelach danych z zapytań (rys. nr 21).

rys. nr 21 — Dane ukryte pod Table

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

rys. nr 22 — Zamknij i załaduj do

Otworzy nam się okno Ładowania do, gdzie ustawiamy sposób wyświetlania danych jako Tabela oraz miejsce wstawienia danych – istniejący arkusz i wskazujemy konkretną komórkę (rys. nr 23).

rys. nr 23 — Okno ładowanie do

Otrzymamy dane wczytane do Excela przedstawione na rysunku nr 24.

rys. nr 24 — Dane wczytane do Excela

Pierwsze rozwiązanie przedstawione w poprzednim poście jest czasochłonne i zawiera wiele kroków, ale wszystkie kroki możemy wykonać z interfejsu użytkownika. Drugie rozwiązanie jest szybsze ale wymaga dobrej znajomości języka M i funkcji Power Query.


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.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
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 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.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama

Power Query 33 — Cena produktu na podstawie daty zamówienia — funkcjonalności ze wstążki

W dzisiejszym poście nauczymy się jak znaleźć cenę produktu dla konkretnej daty. Pomysł na ten odcinek zaczerpnąłem z Excel Magic Trick nr 1483, gdzie Mike Girvin rozwiązywał podobny problem w Excelu. W danych źródłowych mamy tabelkę  z cenami produktów oraz datami, kiedy te ceny ulegały zmianie (rys. nr 1).

rys. nr 1 — Przykładowe dane

Według danych bazowych produkt ABC dnia 2018-01-01 kosztował 10zł, następnie cena ta zmieniła się dnia 2018-03-01 na 12zł. Kolejna zmiana ceny miała miejsce 2018-07-15 kiedy spadła na 9zł. Naszym zadaniem jest znaleźć aktualną cenę dla konkretnej daty zamówienia (rys. nr 2). Mike Girvin pokazał różne metody rozwiązania tego problemu w Excelu.

rys. nr 2 — Dane z datą zamówienia

W tym poście pokażemy jak rozwiązać takie zadanie w Power Query za pomocą poleceń ze wstążki. W kolejnym poście (Power Query 34 https://exceliadam.pl/?p=9634 ) omówimy rozwiązanie bardziej eleganckie, o które zapytałem na forum Excela (ponieważ czułem że moje rozwiązanie jest zbyt skomplikowane). Rozwiązanie do kolejnego odcinka podpowiedział mi Bill Szysz – w jego pomyśle wystarczyły trzy kroki aby rozwiązać ten problem.

Przygotowałem odpowiednio tabelki, które wczytamy do Power Query. Pierwsza nazywa się tCeny, a druga tZamówienia. Nazwę tabel możemy sprawdzić w karcie Projektowanie, w grupie poleceń Właściwości (oznaczone zieloną strzałką na rysunku nr 3).

rys. nr 3 — Karta Projektowanie

Aby pobrać tabelę do Power Query zaznaczamy dowolną komórkę w tej tabeli a następnie wybieramy polecenie Z tabeli z karty Dane (rys. nr 4).

rys. nr 4 — Z tabeli

Otworzy nam się Edytor zapytań z wczytaną tabelą tCeny przedstawioną na rysunku nr 5.

rys. nr 5 — Edytor zapytań

Power Query błędnie zmienił typ danych w kolumnie Data zmiany ceny. Klikamy na ikonkę kalendarza przy nazwie tej kolumny i z podręcznego menu wybieramy polecenie Data (rys. nr 6).

rys. nr 6 — Format danych Data

Wyświetli nam się komunikat dotyczący zmiany typu kolumny, który musimy zatwierdzić przyciskiem Zamień bieżącą (rys. nr 7).

rys. nr 7 — Zamień bieżącą

W kolejnym kroku powinniśmy posortować nasze dane w kolumnie Produkt. W tym celu klikamy na ikonkę trójkąta obok nazwy kolumny i z podręcznego menu wybieramy polecenie Sortuj rosnąco (rys. nr 8).

rys. nr 8 — Sortuj rosnąco

Następnie analogicznie postępujemy dla drugiej kolumny (Data zmiany ceny), korzystając z polecenie Sortuj rosnąco. Ważnym jest, że w Power Query kolejność sortowania działa w odwrotnej kolejności niż w Excelu. Kolejność sortowania jest widoczna w tytułach kolumn (rys. nr 9). W naszym przykładzie dane zostaną najpierw posortowane po produkcie a później po dacie.

rys. nr 9 — Kolejność sortowania kolumn

Dla odmiany w Excelu przy takiej kolejności sortowania jak wyżej, ważniejsza było ostatnie sortowania, czyli najpierw dane zostały by posortowane po dacie a później po produkcie.

Otrzymamy dane przedstawione na rysunku nr 10.

rys. nr 10 — Posortowane dane

Drugą tabelę przygotowaliśmy wcześniej. Jej wczytywanie do Power Query odbywa się w sposób identyczny jak pierwszej. Mamy dwa zapytania tZamówienia i tCeny (rys. nr 11).

rys. nr 11 — Lista zapytań

W kolejnym kroku chcemy scalić tabelki z tych dwóch zapytań. Klikamy na zapytanie tZamówienia, potem rozwijamy polecenie Połącz (punkt nr 2 na rysunku nr 12) z karty Narzędzia główne i polecenie Scal zapytania (punkt nr 3), a następnie wybieramy polecenie Scal zapytania jako nowe (punkt nr 4).

rys. nr 12 — Scal zapytania jako nowe

Otworzy nam się okno Scalania, w którym wybieramy tabele i pasujące kolumny, po których chcemy utworzyć scaloną tabelę (punkt nr 1 i nr 2 na rysunku nr 13). Wybieramy również rodzaj sprzężenia (punkt nr 3) – Lewe zewnętrzne, czyli wszystkie z pierwszej tabeli i pasujące z drugiej tabeli). Wybrane parametry zatwierdzamy przyciskiem OK.

rys. nr 13 — Okno Scalania

W ustawieniach zapytania możemy zmienić nazwę scalonych danych na Scalone1 (rys. nr 14).

rys. nr 14 — Zmiana nazwy zapytania

Otrzymamy scalone dane przedstawione na rysunku nr 15.

rys. nr 15 — Scalone dane

W późniejszym etapie zrobimy kolejne scalanie – będzie to Scalone 2 więc możemy sobie od razu Zduplikować aktualne scalanie i zmienić mu nazwę na docelową. Klikamy prawym przyciskiem myszy na nazwę zapytania i z podręcznego menu wybieramy polecenie Duplikuj (rys. nr 16).

rys. nr 16 — Duplikuj zapytanie

W ustawieniach zapytania zmieniamy nazwę tego zapytania na Scalone2. W otrzymanych danych pod każdym skrótem Table kryje się tabelka z danymi(rys. nr 17).

rys. nr 17 — Table

W każdym wierszu z Table dla danego produktu, wyciągane są odpowiednie wiersze z tabeli tCena z tym samym produktem. Wartości te są skumulowane w małych tabelkach odpowiadających każdemu wierszowi.

Interesuje nas cena, która jest aktualna dla naszej daty zamówienia. Na przykład w pierwszym wierszu mamy 2018-02-26, data ta jest mniejsza od 2018-03-01, czyli obowiązuje dla tej konkretnej daty pierwsza cena (10 zł). Chcielibyśmy usunąć z tej tabelki pozostałe ceny (rys. nr 18).

rys. nr 18 — Właściwy wynik

Możemy to zrobić klikając ikonkę ze strzałkami w nazwie kolumny tCeny, a następnie rozwinąć naszą tabelkę. Potrzebujemy z tej tabelki Datę zmiany ceny i Ceny, nie potrzebujemy natomiast Produktu (rys. nr 19). Parametry rozwijania danych zatwierdzamy przyciskiem OK.

rys. nr 19 — filtrowanie danych

Otrzymamy dane przedstawione na rysunku nr 20.

rys. nr 20 — Przefiltrowane dane

Mamy liczbę porządkową zamówienia, do każdej daty zamówienia mamy przypisane daty ze zmianą ceny oraz z ceny. Cofniemy poprzedni krok za pomocą ikonki koła zębatego przy nazwie kroku, aby nie dodawał nam się prefiks – usuwamy zawartość pola Domyślny prefiks nazwy kolumny (rys. nr 21).

rys. nr 21 — Rozwijanie danych

Otrzymamy dane przedstawione na rysunku nr 22.

rys. nr 22 — rozwinięte dane

Interesuje nas maksymalna data poniżej daty zamówienia. Musimy dodać kolumnę warunkową. Wybieramy polecenie Kolumna warunkowa z karty Dodaj kolumnę (rys. nr 23).

rys. nr 23 — Kolumna warunkowa

Otworzy nam się okno Dodawania kolumny warunkowej, w którym określamy nazwę nowej kolumny (punkt nr 1 na rysunku nr 24 – nazwa Daty) i warunek jaki musi spełnić jej wynik. Nasz warunek będzie wyglądał następująco: Jeśli Data zmiany ceny (pole nr 2) jest przed lub równa (pole nr 3) wartości z kolumny Data zamówienia (punkt nr 4), to chcemy zwrócić wartość z kolumny Data zmiany ceny (punkt nr 5). W przeciwnym wypadku chcemy otrzymać wartość null (punkt nr 6). Wybraliśmy wartość null ponieważ później możemy łatwo odfiltrować dane po tej wartości. Powyższe parametry zatwierdzamy przyciskiem OK.

rys. nr 24 — Parametry kolumny warunkowej

Otrzymamy dane przedstawione na rysunku nr 25.

rys. nr 25 — Dane z kolumną warunkową

Możemy łatwo zauważyć dla pierwszego wiersza, że mamy już podany wynik. Prawidłowa cena to 10, ponieważ dla innych wartość mamy wartości null (rys. nr 26).

rys. nr 26 — właściwy wynik

Natomiast dla np. wiersza piątego mamy dwie daty, co może być mylące (rys. nr 27). Z tych danych interesuje nas cena, która jest przy starszej dacie.

rys. nr 27 dwa wyniki spełniające dany warunek

Przede wszystkim musimy odfiltrować nasze dane po wartościach null. Klikamy na ikonkę trójkącika przy nagłówku kolumny Daty i podręcznym menu odznaczamy checkbox przy wartości null (rys. nr 28). Nałożony filtr zatwierdzamy przyciskiem OK

rys. nr 28 — Odfiltrowanie danych po wartości null

Otrzymamy dane przedstawione na rysunku nr 29.

rys. nr 29 — Aktualne dane

W następnej kolejności musimy zgrupować nasze dane. Wybieramy polecenie Grupowanie według z karty Narzędzia główne (rys. nr 30)

rys. nr 30 — Grupowanie według

Otworzy nam się okno Grupowania według, gdzie ustawiamy grupowanie zaawansowane (punkt nr 1 na rysunku nr 31) i trzy grupy po których chcemy pogrupować dane (L.p., Data zamówienia i Produkt – punkt nr 2 na rysunku nr 31)). W punkcie oznaczonym numerem 3 na rysunku nr 31 wybieramy rodzaj operacji – Maksimum z kolumny Daty. Zmieniamy nazwę nowej kolumny na Najnowsza cena. Ustawione parametry zatwierdzamy przyciskiem OK.

rys. nr 31 — Parametry grupowania

Otrzymamy dane przedstawione na rysunku nr 32, czyli najnowsze daty z tabeli tCena.

rys. nr 32 — Pogrupowane dane

Kiedy mamy już wyznaczone te daty, brakuje nam wartości ceny dla tych dat. Rozwijamy polecenie Połącz (punkt nr 2 na rysunku nr 33) z karty Narzędzia główne, a następnie wybieramy polecenie Scal zapytania (punkt nr 3).

rys. nr 33 — Scal zapytania

Otworzy nam się okno Scalania, gdzie wybieramy tabele i pasujące w nich kolumny (zaznaczamy dwie kolumny przytrzymując klawisz Ctrl). Interesują nas kolumny Produkt i Data. Wybieramy rodzaj sprzężenia Lewe zewnętrzne (wszystkie z pierwszej i pasujące z drugiej). Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 34).

rys. nr 34 — Parametry scalania

Otrzymamy dane z kolumną tCeny, w której dane są ukryte pod postacią tablic. Klikamy ikonkę ze strzałkami przy nazwie kolumny tCeny a następnie wybieramy z podręcznego menu polecenie Rozwiń i zaznaczamy checkbox przy wartości Cena. Odznaczamy opcję Użyj oryginalnej nazwy kolumny jako prefiksu i zatwierdzamy przyciskiem OK. (rys. nr 35). 

rys. nr 35 — Rozwiń dane

Otrzymamy dane, z których usuwamy kolumnę najnowsza data ponieważ jest niepotrzebna (rys. nr 36).

rys. nr 36 — Rozwinięte dane

Tak przygotowane dane możemy wczytać do Excela. Rozwijamy polecenie Zamknij i załaduj z karty Narzędzia główne, a następnie wybieramy polecenie Zamknij i załaduj do (rys. nr 37).

rys. nr 37 — Zamknij i załaduj do

Otworzy nam się w Excelu okno Ładowania do, gdzie ustawiamy sposób wyświetlania danych jako Utwórz tylko połączenie i zatwierdzamy przyciskiem Załaduj (rys. nr 38).

rys. nr 38 — Parametry ładowania danych

W oknie Zapytania dotyczące skoroszytu wybieramy zapytanie, które chcemy wstawić do arkusza (Scalone1), a następnie wybieramy polecenie Pokaż zapytania z karty Dane (rys. nr 39).

rys. nr 39 — Załadowane dane do Excela

Klikamy prawym przyciskiem myszy na nazwę zapytania Scalone1 i z podręcznego menu wybieramy polecenie Załaduj do (rys. nr 40).

rys. nr 40 — Załaduj do

Otworzy nam się okno Ładowania do, gdzie wybieramy wstawienie danych jako tabela w istniejącym arkuszu, a następnie wskazujemy konkretną komórkę (rys. nr 41). Tak ustawione parametry zatwierdzamy przyciskiem Załaduj.

rys. nr 41 — Parametry ładowania danych

Otrzymamy dane przedstawione na rysunku nr 42

rys. nr 42 — Dane w Excelu

Zapomnieliśmy posortować dane po liczbie porządkowej, więc klikamy dwukrotnie na nazwę naszego zapytania Scalone1 i przechodzimy do Edytora zapytań Power Query. Klikamy na ikonkę trójkącika przy nazwie kolumny L.p. i w menu wybieramy polecenie Sortuj rosnąco (rys. nr 43).

rys. nr 43 — Sortuj rosnąco

Klikamy polecenie Zamknij załaduj z karty Narzędzia główne. Po powrocie do Excela otrzymujemy prawidłowe, posortowane dane przedstawione na rysunku nr 44.

rys. nr 44 — Prawidłowe dane w Excelu

Podsumowując przedstawione rozwiązanie było długie i nazbyt skomplikowane, ale najważniejsze, że działa prawidłowo. Co ważne w tym zadaniu korzystaliśmy tylko z poleceń, które są na wstążce. W kolejnym odcinku przedstawimy szybsze rozwiązanie, ale będziemy musieli w nim skorzystać z funkcji języka M.


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.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
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 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.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama

Power Query 32 — Powtarzanie kodów kreskowych w kolejnych wierszach

W dzisiejszym poście zajmiemy się tematem powtarzania kodów kreskowych bądź innych wartości w kolejnych wierszach. Omówimy rozwiązanie tego zagadnienia w Power Query na podstawie przykładowych danych źródłowych z rysunku nr 1.

rys. nr 1 — Przykładowe dane

Podczas jednej z moich konsultacji online pojawił się problem – potrzeba, aby powtarzać kodu kreskowe kilka razy pod rząd w kolejnych wierszach. Można to zrobić za pomocą VBA, ale tutaj omówimy rozwiązanie w Power Query. W naszych danych źródłowych kody kreskowe zapisane są w postaci ciągów cyfr, w drugiej kolumnie mamy podaną ilość powtórzeń danego kodu.

Aby wczytać dane 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ą tKody (rys. nr 3). W Ustawieniach zapytania usuwamy krok Zmieniono typ z Zastosowanych kroków, ponieważ jest zbędny. Musimy zmienić typ danych ręcznie. 

rys. nr 3 — Edytor zapytań

Nasz kod kreskowy jest zmieniony na liczby, a my chcemy by był traktowany jako tekst, ponieważ funkcja z jakiej będziemy korzystać w Power Query radzi sobie z tekstem, natomiast z liczbami nie (trzeba by dokonywać konwersji). Klikamy na ikonkę ABC123 z boku nazwy tytułu kolumny Kod kreskowy i z podręcznego menu wybieramy polecenie Tekst (rys. nr 4).

rys. nr 4 — Polecenie Tekst

W kolejnym kroku musimy dodać kolumnę za pomocą polecenie Kolumna niestandardowa (punkt nr 2 na rysunku nr 5) z karty Dodaj kolumnę.

rys. nr 5 — Kolumna niestandardowa

Otworzy nam się okno Kolumny niestandardowej, gdzie będziemy korzystać z funkcji. Zmieniamy nazwę kolumny na Powt, a następnie w polu Formuła kolumny niestandardowej wpisujemy formułę funkcji dla tej kolumny. Do zapisania formuły użyjemy kolumn z pola Dostępne kolumny (oznaczone strzałkami na rysunku nr 6). Zapis formuły będzie wyglądał następująco:

=Text.Repeat([#"Kod#(1f)kreskowy"],#"Powtórz [x] razy"]) 

Tak wpisane parametry kolumny niestandardowej zatwierdzamy przyciskiem OK.

rys. nr 6 — Tworzenie kolumny niestandardowej

Otrzymamy dane przedstawione na rysunku nr 7.

rys. nr 7 — dane z nową kolumną

Dane te mają widoczne od razu niedociągnięcie, mianowicie poszczególne kody nie są od siebie niczym oddzielone. W naszym przykładzie kody kreskowe mają po 5 znaków, ale nie każde kody są jednakowe. Musimy wprowadzić modyfikacje w naszą kolumnę niestandardową. Klikamy na ikonkę koła zębatego obok nazwy kroku Dodano kolumnę niestandardową w Zastosowanych krokach. Otworzy nam się po raz kolejny okno Kolumny niestandardowej (rys. nr 8), gdzie musimy zmienić naszą formułę funkcji i wstawić rozdzielenie kodów za pomocą jakiegoś symbolu (w naszym przykładzie @, bo lubię małpki ?). Zapis formuły będzie wyglądał następująco:

=Text.Repeat([#"Kod#(1f)kreskowy"] & "@",#"Powtórz [x] razy"])

Wprowadzoną zmianę zatwierdzamy przyciskiem OK.

rys. nr 8 Kolumna niestandardowa

Otrzymamy dane przedstawione na rysunku nr 9, gdzie kody zostały rozdzielone symbolem @.

rys. nr 9 — Dane z kodami rozdzielonymi znakiem @

Symbole te posłużą nam do rozdzielenia kolumn. Rozwijamy polecenia Podziel kolumny (punkt nr 2 na rysunku nr 10) z karty Narzędzia główne i wybieramy polecenie Według ogranicznika (punkt nr 3 na rysunku nr 10).

rys. nr 10 — Podziel kolumny

Otworzy nam się okno Dzielenia kolumny według ogranicznika (rys. nr 11), gdzie Power Query rozpozna nasz ogranicznik. Musimy rozwinąć sobie Opcje zaawansowane, ponieważ chcemy zrobić podział na Wiersze (zaznaczone zielonym prostokątem na rysunku nr 11) , a nie kolumny, czyli podział ma być w dół danych (zaznaczone zieloną strzałką na rysunku nr 11) a nie w bok.

rys. nr 11 _ dzielenie kolumny według ogranicznika

Wprowadzone parametry podziały kolumny zatwierdzamy przyciskiem OK. i otrzymamy dane przedstawione na rysunku nr 12.

rys. nr 12 — Wydzielone kolumny

Usuwamy krok Zmieniono typ1 z Zastosowanych kroków i otrzymamy dane przedstawione na rysunku nr 13.

rys. nr 13 — Dane

W danych tych odfiltrujemy puste komórki, czyli klikamy ikonkę trójkąta przy nazwie kolumny Powt i w podręcznym menu odznaczamy checkbox przy nazwie Puste (rys. nr 14).

rys. nr 14 — Odfiltrowanie pustych

Nasz filtr zatwierdzamy przyciskiem OK i otrzymamy dane przedstawione na rysunku nr 15, które możemy wczytać do Excela.

rys. nr 15 — Odfiltrowane dane

Tak naprawdę nie potrzebujemy aż trzech kolumn, ponieważ mamy te same dane w kolumnie pierwszej i trzeciej (Kod kreskowy i Powt). Wystarczy nam jedna kolumna z powtórzonymi kodami. Zaznaczamy kolumnę Powt i klikamy na jej tytuł prawym przyciskiem myszy, z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 16).

rys. nr 16 — Usuń inne kolumny

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

rys. nr 17 — zamknij i załaduj do

Otworzy nam się okno Ładowania do, gdzie wybieramy sposób wyświetlania danych jako Tabela, a następnie określamy miejsce wstawienia danych jako Istniejący arkusz i wskazujemy konkretną komórkę w tym arkuszu (rys. nr 18). Tak ustawione parametry wstawienia danych zatwierdzamy przyciskiem Załaduj.

rys. nr 18 — Okno ładowania do

Otrzymamy dane załadowane do Excela przedstawione na rysunku nr 19.

rys. nr 19 — Dane w Excelu

Mamy obliczoną dodatkowo ilość wszystkich powtórzeń – 88 sztuk. Taką samą ilość pokazuje nam zapytanie tKody w Zapytaniach dotyczących arkusza (oznaczone zielonymi strzałkami na rysunku nr 20).

rys. 20 — Ilość powtórzeń

Możemy sprawdzić działanie zapytania zmieniając dane w tabeli z danymi źródłowymi. Wystarczy odświeżyć dane z zapytania aby otrzymać aktualne wyniki. 


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.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
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 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.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama

Power Query 31 — Rozrastająca się lista rozwijana

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.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
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 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.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama