Nie znasz dobrze obsługi Excela? A może nie znasz jej wcale? Doskonale wiem, że w dzisiejszych czasach i realiach, znajomość tego programu jest nie tylko niezbędna, ale przede wszystkim korzystna dla Ciebie samego. Moje szkolenie z Excela online pokaże Ci, że arkusz kalkulacyjny nie jest wcale Twoim wrogiem, a sprzymierzeńcem w porządkowaniu i analizie danych - nie tylko liczbowych. Stworzyłem kurs Excel online na każdym poziomie, zarówno dla osób początkujących, średniozaawansowanych, jak i zaawansowanych. Znajdziesz u mnie także szkolenie z Excela online dedykowane specjalnie przedsiębiorcom. Każdy kurs zawiera starannie wyselekcjonowany zakres wiedzy i zagadnień, które odpowiadają na ściśle sprecyzowane potrzeby jego użytkowników.
Excel - kurs online. Dlaczego warto?
Zastanawiasz się, czy Excel i kurs online to dobre połączenie? Czy taka forma nauki pracy z arkuszem kalkulacyjnym może być efektywna? Pozwól, że rozwieję Twoje wątpliwości.
Od lat z powodzeniem zajmuję się dzieleniem swoją wiedzą zdalnie. Ta forma nauki, szczególnie w ostatnim czasie, mocno zyskała na popularności, a jej techniki znacząco się rozwinęły. Abyś mógł biegle posługiwać się programem Excel, każdy swój kurs zaprojektowałem z ogromną starannością i w oparciu o zasady metodyki nauczania na odległość.
Jednak szkolenie z Excela online, to przede wszystkim ogromna wygoda dla Ciebie, ponieważ nie musisz wychodzić z domu, możesz uczyć się wraz ze mną z dowolnego miejsca i w wybranym przez siebie czasie. Dodatkowo każdy mój kurs Excela online zawiera materiały dydaktyczne dostępne dla Ciebie już po zakończeniu szkolenia. Krótkie nagrania filmowe, prezentujące różnego rodzaju działania w arkuszu kalkulacyjnym, które w dowolnej chwili możesz zatrzymać, odtworzyć ponownie, by jeszcze raz przeanalizować i utrwalić wiedzę, to doskonały sposób, by opanować Excel. Kurs online to nie tylko wygoda, to także oszczędność Twojego i mojego czasu - zamiast dojeżdżać w umówione miejsce, możemy poświęcić więcej chwil na ćwiczenia i praktykę.
W
dzisiejszym poście nauczymy się jak policzyć wszystkie komórki zawierające
konkretny tekst. Naszym zadaniem będzie policzenie ile ciasteczek było w
paczkach, oraz wskazanie wszystkich możliwych par.
W Power
Query odcinku 43 obliczaliśmy ile produktów w paczkach sprzedał konkretny
sprzedawca. Pojawiał się tam taki problem, że mieliśmy np. czterech sprzedawców
a tylko trzech z nich sprzedało produkt ciasteczka. W wyniku naszych działań
pojawiali się tylko sprzedawcy, którzy dany produkt sprzedali (rys. nr 1).
rys. nr 1 — Dane ze sprzedawcami i produktami
Naszym
zadaniem będzie otrzymanie wyniku, w którym Power Query będzie pokazywał każdą
możliwą parę sprzedawca – produkt, również „puste” pary jak na rys. nr 2.
rys. nr 2 — zestawienie danych ze sprzedawcami, którzy nie sprzedali danego produktu
W odcinku
Power Query nr 44 omawialiśmy iloczyn kartezjański, który moglibyśmy wykonać
ręcznie, chociaż jest to bardzo czaso- i pracochłonne przy większej ilości
danych. Zależy nam, aby Power Query sam wyznaczył pary sprzedawca- produkt oraz
dodatkowo podliczył ile poszczególnych produktów sprzedał każdy sprzedawca.
Zadanie to wykonamy na podstawie przykładowych danych z rys. nr 3.
rys. nr 3 — Przykładowe dane
W pierwszym
kroku musimy zaczytać nasze przykładowe dane do Power Query. W tym celu
wybieramy polecenie Z tabeli/zakresu z karty Dane (rys. nr 4).
rys. nr 4 — polecenie zaczytania danych z tabeli/zakresu
Otworzy nam
się Edytor zapytań z wczytaną tabelą tPaczki. Do dalszej pracy musimy
zduplikować nasze zapytanie. Na tych zapytaniach wykonamy różne operacje,
których wyniki odpowiednio ze sobą połączymy. Będą nam potrzebne takie dane jak
unikatowa lista Produktów, Sprzedawców oraz podsumowane wartości sprzedaży.
Musimy wyznaczyć ilość konkretnych produktów sprzedanych przez każdego
sprzedawcę. Będziemy potrzebować 3 zapytań z naszymi danymi bazowymi. W panelu
bocznym z listą zapytań klikamy na nazwę zapytania tPaczki prawym przyciskiem
myszy i z podręcznego menu wybieramy polecenie Duplikuj (zaznaczone zieloną
strzałką na rys. nr 5). Czynność tę powtarzamy, aby otrzymać trzy jednakowe
zapytania.
rys. nr 5 — polecenie Duplikuj zapytanie
Zaczniemy od
grupowania. W tym celu klikamy na zapytanie tPaczki(3) i w panelu bocznym
Ustawień zapytania po prawej stronie ekranu zmieniamy jego nazwę na tGrupowanie
(rys. nr 6).
rys. nr 6 — Zmiana nazwy zapytania w Ustawieniach zapytania
Nasze dane
pogrupujemy po sprzedawcach i produktach, w tym celu musimy najpierw rozdzielić
paczki na poszczególne produkty. Zaznaczamy kolumnę Paczki i rozwijamy
polecenie Podziel kolumny (punkt 2 na rys. nr 7) z karty Narzędzia główne
(punkt 1), a następnie wybieramy polecenie Według ogranicznika (punkt 3).
Należy pamiętać, że nasze dane chcemy rozdzielić na wiersze.
rys. nr 7 — polecenie Podziel kolumny Według ogranicznika
Otworzy nam
się okno Dzielenia kolumny według ogranicznika, gdzie w polu Wybierz lub
wprowadź ogranicznik wybieramy typ –Niestandardowe–, a następnie w polu
poniżej wpisujemy nasz ogranicznik, czyli przecinek i spacja. Rozwijamy opcje
zaawansowane, w których w polu Podziel na, zaznaczamy checkbox Wiersze. Tak
ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 8).
rys. nr 8 — Okno Dzielenia kolumny według ogranicznika
Otrzymamy
podzielone dane przedstawione na rys. nr 9.
rys. nr 9 — Dane podzielone według ogranicznika
Podzielone
dane chcemy pogrupować według sprzedawcy, aby mieć jasne wyniki ile dany
sprzedawca sprzedał produktów. Zaznaczamy kolumny Sprzedawca oraz Paczka,
następnie wybieramy polecenie Grupowanie według z karty Narzędzia główne (rys.
nr 10).
rys. nr 10 — Polecenie Grupowanie według
Otworzy nam
się okno Grupowania według, gdzie możemy pozostać przy domyślnych ustawieniach,
ponieważ naszym celem jest zliczenie produktów. Domyślne parametry zatwierdzamy
przyciskiem OK (rys. nr 11).
rys. nr 11 — Okno Grupowania według
Otrzymamy
pogrupowane dane przedstawione na rys. nr 12.
rys. nr 12 — Pogrupowane dane
Teraz
przechodzimy do zapytania tPaczki (2), któremu zmieniamy nazwę na tProdukty w
Ustawieniach zapytania. Naszym zadaniem w tym zapytaniu jest podzielenie paczek
na poszczególne produkty. Inne kolumny są zbędne. Zaznaczamy kolumnę Paczki, a
następnie klikamy prawym przyciskiem myszy na jej obszarze i z podręcznego menu
wybieramy polecenie Usuń inne kolumny (rys. nr 13).
rys. nr 13 — polecenie Usuń inne kolumny
Otrzymamy
kolumnę Paczki przedstawioną na rys. nr 14.
rys. nr 14 — dane po usunięciu niepotrzebnych kolumn
Następnie
powyższą kolumnę musimy podzielić według ogranicznika. Rozwijamy polecenie
Podziel kolumny z karty Narzędzia główne, a następnie wybieramy polecenie
Według ogranicznika (tak jak na rys. nr 7).
Otworzy nam
się okno Dzielenia kolumny według ogranicznika, w którym ustawiamy takie same
parametry jak dla zapytania tGrupowanie (jak na rys. nr 8). Parametry te
zatwierdzamy przyciskiem OK.
Otrzymamy
listę wszystkich produktów z poszczególnych paczek przedstawioną na rys. nr 15.
rys. nr 15 — lista produktów z poszczególnych paczek
Naszym
zadaniem jest stworzenie unikatowej listy produktów, więc musimy usunąć
powtarzające się dane. Zaznaczamy kolumnę, rozwijamy polecenie Usuń wiersze
(punkt 2 na rys. nr 16) z karty Narzędzia główne (punkt 1), a następnie
wybieramy polecenie Usuń duplikaty (punkt 3).
rys. nr 16 — polecenie Usuń duplikaty
Ponadto
posortujemy sobie naszą listę alfabetycznie od A do Z za pomocą polecenia na
karcie Narzędzia główne (zaznaczone strzałką na rys. nr 17).
rys. nr 17 — Sortowanie danych od A do Z
Otrzymamy posortowaną
unikatową listę produktów przedstawioną na rys. nr 18.
rys. nr 18 — posortowana unikatowa lista produktów
Ponadto
zmienimy nazwę naszej kolumny na Produkty (rys. nr 19).
rys. nr 19 — Zmiana nazwy kolumny
Teraz
przechodzimy do naszego zapytania głównego tPaczki. Zmieniamy nazwę zapytania w
Ustawieniach zapytania na tPary (rys. nr 20).
rys. nr 20 — Zmiana nazwy zapytania w Ustawieniach zapytania
W pierwszym
etapie chcemy uzyskać unikatową listę sprzedawców. Zaznaczamy kolumnę
Sprzedawca, klikamy prawym przyciskiem myszy na obszarze kolumny i z
podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 21).
rys. nr 21 — polecenie Usuń inne kolumny
Otrzymamy
kolumnę Sprzedawca, z której chcemy usunąć powtarzających się sprzedawców.
Rozwijamy polecenie Usuń wiersze z karty Narzędzia główne, a następnie
wybieramy polecenie Usuń duplikaty (tak jak na rys. nr 16).
Uzyskaną
listę sortujemy od A do Z za pomocą polecenia z karty Narzędzia główne (jak na
rys. nr 17).
Otrzymamy
posortowaną unikatową listę sprzedawców przedstawioną na rys nr 22.
rys. nr 22 — posortowana unikatowa lista sprzedawców
Na tym
etapie do listy sprzedawców z zapytania tPary chcemy dołożyć listę produktór z
zapytania tProdukty. Kopiujemy nazwę zapytania tProdukty za pomocą skrótu
klawiszowego Ctrl+C. Przechodzimy do zapytania tPary (ważne które zapytanie
jest aktywne) i wybieramy polecenie Kolumna niestandardowa z karty Dodaj
kolumnę (rys. nr 23).
rys. nr 23 — polecenie Kolumna niestandardowa z karty Dodaj kolumnę
Otworzy nam
się okno Kolumny niestandardowej, gdzie w polu Nazwa nowej kolumny wpisujemy
Produkty oraz w polu Formuła kolumny niestandardowej wklejamy skopiowaną nazwę
drugiego zapytania za pomocą skrótu klawiszowego Ctrl+V. Zapis powinien
wyglądać następująco: =tProdukty (w polu formuły robimy odwołanie do innego zapytania
– zaznaczone strzałką na rys. nr 24). Tak ustawione parametry zatwierdzamy
przyciskiem OK.
rys. nr 24 — parametry kolumny niestandardowej
Otrzymamy
pary sprzedawca – produkty. W kolumnie Produkty są tabele z listą unikatowych
produktów (rys. nr 25).
rys. nr 25 — Pary sprzedawca — produkty
Aby rozwinąć
te listy produktów musimy kliknąć na ikonkę ze strzałkami przy nazwie kolumny
Produkty. Otworzy nam się okienko, gdzie zaznaczamy checkbox przy poleceniu
Rozwiń, a następnie odznaczamy checkbox przy poleceniu Użyj oryginalnej nazwy
kolumny jako prefiksu. Tak ustawione parametry zatwierdzamy przyciskiem OK
(rys. nr 26).
rys. nr 26 — polecenie Rozwiń
Otrzymaliśmy
listę wszystkich możliwych par sprzedawca – produkt przedstawioną na rys. nr 27.
rys. nr 27 — lista wszystkich możliwych par sprzedawca — produkt
Zmieniła się
nazwa kolumny z produktami, aby ją zmienić rozwijamy pasek formuły i tam
wprowadzamy zmianę (zaznaczone strzałką na rys. nr 28).
rys. nr 28 — zmiana nazwy kolumny w pasku formuły
W zapytaniu
tPary otrzymaliśmy wszystkie możliwe kombinacje par między sprzedawcą a
produktem, natomiast w zapytaniu tGrupowanie mamy listę tych par, które
faktycznie istnieją. Z zapytania tGrupowanie będziemy chcieli skorzystać z
kolumny Liczność. W tym celu scalimy te zapytania.
Główne
zapytanie (tPary) musi być aktywne. Rozwijamy polecenie Scal zapytania (punkt 2
na rys. nr 29) z karty Narzędzia główne (punkt 1), a następnie wybieramy
polecenie Scal zapytania (punkt 3).
rys. nr 29 — droga dostępu do polecenia Scal zapytania
Otworzy nam
się okno Scalania, gdzie w drugim polu wybieramy zapytanie tGrupowanie.
Zaznaczamy kolumny Sprzedawca i Produkty z pierwszego zapytania przytrzymując
klawisz Ctrl a następnie te same kolumny z drugiego zapytania. Wybieramy rodzaj
sprzężenia jako Lewe zewnętrzne. Należy zwrócić uwagę, aby zaznaczanie kolumn
odbyło się w tej samej kolejności, czyli jeśli w pierwszym zapytaniu najpierw
zaznaczymy kolumnę Sprzedawca to w drugim musimy zrobić tak samo. Nazwy kolumn
nie muszą się pokrywać. Tak ustawione parametry zatwierdzamy przyciskiem OK
(rys. nr 30).
rys. nr 30 — Okno Scalania z ustawionymi parametrami
Otrzymaliśmy
listę wszystkich możliwych par sprzedawca – produkt oraz ilość tych produktów z
zapytania tGrupowanie (rys. nr 31).
rys. nr 31 — lista wszystkich możliwych par sprzedawca – produkt oraz ilość tych produktów z zapytania tGrupowanie
Sprawdzimy
teraz co się dzieje w sytuacji sprzedawcy, który nie sprzedał żadnego produktu
z listy. Sprzedawca Robert nie sprzedał żadnych ciasteczek, więc jego tabela
jest pusta (rys. nr 32).
rys. nr 32 — pusta tabela dla sprzedawcy, który nie sprzedał danego produktu
Na tym
etapie musimy rozwinąć kolumnę tGrupowanie, czyli klikamy na ikonkę ze
strzałkami przy nazwie kolumny i zaznaczamy polecenie Rozwiń oraz kolumnę,
którą chcemy rozwinąć. Tak ustawione parametry zatwierdzamy przyciskiem OK
(rys. nr 33).
rys. nr 33 — Polecenie Rozwiń
Otrzymamy
listę sprzedawców, produktów oraz ilość sprzedanych produktów (rys. nr 34).
rys. nr 34 — rozwinięte dane
Chcemy
zmienić nazwę kolumny otrzymanej po rozwinięciu na Ilość, w tym celu zmieniamy
nazwę w pasku formuły. Pierwsze wystąpienie nazwy Liczność jest nazwą kolumny,
którą rozwijaliśmy, natomiast drugie wystąpienie – nazwą kolumny docelowej
(rys. nr 35).
rys. nr 35 — Zmiana nazwy kolumny w pasku formuły
Po zmianie
nazwy kolumny otrzymamy dane przedstawione na rys. nr 36.
rys. nr 36 — Dane po zmianie nazwy kolumny
Wartości
null w danych po załadowaniu danych do Excela przekształcą się w puste komórki.
Jeśli chcemy aby przekształciły się w wartość zero, to musimy przekształcić
naszą kolumnę. W tym celu rozwijamy ikonę polecenia Zamieniania (punkt 2 na
rys. nr 37) z karty Przekształć (punkt 1), a następnie wybieramy polecenie Zamienianie
wartości (punkt 3).
rys. nr 37 — polecenie Zmienianie wartości
Otworzy nam
się okno Zamieniania wartości, gdzie w polu wartość do znalezienia wpisujemy
null, natomiast w polu Zamień na wpisujemy zero (0). Tak ustawione parametry
zatwierdzamy przyciskiem OK (rys. nr 38).
rys. nr 38 — okno Zamieniania wartości
Otrzymamy
zmienione dane przedstawione na rys. nr 39.
rys. nr 39 — dane ze zmienionymi wartościami null
Aby dane
były bardziej czytelne musimy je posortować za pomocą polecenia na karcie
Narzędzia główne. Najpierw sortujemy kolumnę Produkt a następnie kolumnę
Sprzedawca. Otrzymamy dane przedstawione na rys. nr 40.
rys. nr 40 — posortowane dane
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 41).
rys. nr 41 — polecenie Zamknij i załaduj do
Otworzy nam
się okno Importowania danych. Tutaj pojawi się pewien problem, mianowicie stworzyliśmy
trzy zapytania, więc nie możemy ich wstawić w konkretną komórkę. Dlatego
wybieramy sposób wyświetlania danych w skoroszycie jako Utwórz tylko połączenie
(rys. nr 42).
rys. nr 42 — Okno Importowania danych
Wstawiliśmy
dane jako połączenie ponieważ interesuje nas tylko jedno ze stworzonych
zapytań. Klikamy prawym przyciskiem myszy na zapytanie tPary i z podręcznego
menu wybieramy polecenie Załaduj do (rys. nr 43).
rys. nr 43 — polecenie Załaduj do (ponieważ wstawiliśmy dane jako połączenie)
Otworzy nam
się okno Importowania danych, gdzie wybieramy rodzaj wyświetlania danych w
skoroszycie jako Tabela, a następnie wskazujemy konkretną komórkę w istniejącym
arkuszu, gdzie ma zostać wstawiona. Tak ustawione parametry zatwierdzamy
przyciskiem OK (rys. nr 44).
rys. nr 44 — ustawienia sposobu wstawienia danych z zapytania
Otrzymamy
wstawione dane do Excela przedstawione na rys. nr 45. Otrzymaliśmy listę par
sprzedawca – produkt, zawierającą każde możliwe połączenie.
rys. nr 45 — lista par sprzedawca – produkt, zawierającą każde możliwe połączenie
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 zajmiemy się wyszukiwaniem najdłuższego
czasu operacji oraz opisu tej konkretnej operacji. Zakładamy, że mamy linię
produkcyjną, na której są wykonywane pewne czynności. Naszym zadaniem jest
znalezienie operacji, która zajmuje najwięcej czasu, a następnie uzyskanie
opisu nazwy tej operacji. Dodatkowym warunkiem tego zadania jest opcja, że
najdłuższy czas operacji może się powtórzyć – uzyskamy wtedy np. dwie operacje
w jednym wierszu. Zadanie to wykonamy w Power Query na podstawie przykładowych
danych przedstawionych na rysunku nr 1.
rys. nr 1 — Przykładowe dane
Pierwszym krokiem jest zaczytanie danych do Power Query, w
tym celu wybieramy polecenie Z tabeli z karty Dane (rys. nr 2).
rys. nr 2 — Z tabeli
Otworzy nam się edytor zapytań z zaczytaną tabelą z danymi
produkcyjnymi (rys. nr 3).
rys. nr 3 — Edytor zapytań
Zaznaczamy kolumnę Produkt ID a następnie wybieramy polecenie
Grupowanie według z karty Narzędzia główne (rys. nr 4).
rys. nr 4 — Grupowanie według
Otworzy
nam się okno Grupowania według, gdzie możemy ustalić parametry grupowania. Dane
grupujemy według kolumny Produkt ID. W polu nazwa nowej kolumny wpisujemy nazwę
Najdłuższy czas, a następnie w polu Operacja wybieramy Maksimum. W polu Kolumna
wybieramy kolumnę Czas bo na tej kolumnie mają się opierać nasze wyniki. Tak
przygotowane parametry grupowania zatwierdzamy przyciskiem OK (rys. nr 5).
rys. nr 5 — Parametry grupowania
Otrzymamy pogrupowane dane dla każdego ID produktu z
maksymalnym czasem trwania operacji przedstawione na rysunku nr 6.
rys. nr 6 — Pogrupowane dane
Otrzymaliśmy kolumnę z numerem ID produktów i najdłuższy czas
trwania operacji. Potrzebujemy teraz wyciągnąć informację z nazwą tych
operacji. Z założenia opis ten pokazany jest w danych krok wcześniej w
zastosowanych krokach. Możemy scalić zapytanie z tym samym zapytaniem lecz z
innym krokiem. Rozwijamy polecenie Połącz (punkt 2 na rysunku nr 7) z karty Narzędzia
główne, a następnie wybieramy polecenie Scal zapytania (punkt 3).
rys. nr 7 — Scal zapytania
Otworzy nam się okno Scalania danych. Dla tabeli tProdukcja
(2) na górze okna zaznaczamy obie kolumny, czyli Produkt ID oraz Najdłuższy czas. W drugim
polu również wybieramy tabelę tProdukcja (2) (bieżący), a następnie w tej samej
kolejności co powyżej zaznaczamy obie kolumny z danymi. W polu Rodzaj
sprzężenia wybieramy rodzaj operacji, czyli Lewe zewnętrzne (wszystkie z
pierwszej, pasujące z drugiej). Tak przygotowane parametry zatwierdzamy
przyciskiem OK (rys. nr 8).
rys. nr 8 — Parametry scalania
Otrzymujemy dane przedstawione na rysunku nr 9, gdzie pod
słowem Table ukryta jest tabela z danymi (wynik porównania dwóch tabel).
rys. nr 9 — Scalone dane
Tabelki te zawierają tylko jeden wiersz ponieważ grupowanie wykonaliśmy
po ostatnim kroku zapytania, czyli Pogrupowano wiersze. Naszym zadaniem jest
scalenie danych z kroków Pogrupowano wiersze i Zmieniono typ. Klikamy na nazwę
kroku Zmieniono typ, następnie przechodzimy w tryb edycji tekstu za pomocą
klawisza F2 i kopiujemy nazwę tego kroku za pomocą skrótu klawiszowego Ctrl+C.
Sprawdzamy czy mamy widoczny pasek formuły. Jeśli nie to
możemy go włączyć na karcie Widok klikając checkbox przy poleceniu Pasek
formuły.
Będziemy teraz wprowadzać zmiany zapisie naszej formuły
scalania w pasku formuły. Pierwszy krok się zgadza, czyli Pogrupowano wiersze.
Drugi krok jest taki sam, więc musimy zaznaczyć nazwę kroku (zaznaczone na
niebiesko na rysunku nr 10), a następnie wkleić nazwę skopiowanego wcześniej
zapytania (Zmieniono typ) za pomocą skrótu klawiszowego Ctrl+V.
rys. nr 10 — Zmiany w formule
Jeśli teraz zatwierdzimy naszą zmianę klawiszem Enter Power
Query zwróci nam błąd ponieważ w kroku Zmieniono typ nie było kolumny
Najdłuższy czas (rys. nr 11).
rys. nr 11 — Error
W kroku zmieniono typ mamy natomiast kolumnę Czas [s], której
możemy użyć zamiennie. Wystarczy zmienić w zapisie formuły dla tego kroku nazwę
Najdłuższy czas na Czas [s].
Otrzymamy dane przedstawione na rysunku nr 12. Mamy tutaj sytuację, że dwie operacje mają taki sam czas.
rys. nr 12 — dwie operacje z takim samym czasem
Gdybyśmy nie mieli duplikatów w danych, wystarczyło by teraz rozwinąć
kolumnę Pogrupowano wiersze po kolumnie Opis operacji (jak pokazano na rys. nr 13).
rys. nr 13 — Rozwiń
Otrzymalibyśmy wtedy dane przedstawione na rysunku nr 14.
rys. nr 14 — Dane po rozwinięciu
Mamy tutaj dwa wiersze z tym samym czasem, a nam zależy aby
były one w jednym wierszu. Usuwamy ostatni krok jaki wykonaliśmy, czyli wracamy
do danych z rysunku nr 12.
W kolumnie Pogrupowano dane mamy dane w formie tabeli. Musimy
dodać kolumnę niestandardową z karty Dodaj kolumnę (rys. nr 15).
rys. nr 15 — Kolumna niestandardowa
Otworzy nam się okno Kolumny niestandardowej. Zmieniamy nazwę
nowej kolumny na Opis operacji, a następnie wpisujemy formułę tej kolumny.
Będzie się ona opierać na funkcji Table.Column. Pierwszy argument funkcji to
miejsce gdzie jest nasza tabela, czyli kolumna Pogrupowano wiersze. Drugi
argument to nazwa kolumny, którą chcemy
wyciągnąć, czyli kolumna Opis operacji (rys. nr 16). Zapis będzie wyglądał
następująco:
Powyższe parametry kolumny niestandardowej zatwierdzamy
przyciskiem OK.
rys. nr 15 — Parametry kolumny niestandardowej
Otrzymamy dane przedstawione na rysunku nr 17. Dla pierwszego
wiersza pod List ukryte są dwie wartości – dwie nazwy operacji.
rys. nr 17 — Dane z nową kolumną
W kolejnym kroku usuwamy kolumnę Pogrupowano wiersze. Klikamy
na jej nagłówek prawym przyciskiem myszy, a następnie z podręcznego menu
wybieramy polecenie Usuń (rys. nr 18).
rys. nr 18 — polecenie Usuń
Następnie klikamy ikonkę strzałek przy nazwie kolumny Opis
operacji i wybieramy polecenie Wyodrębnij wartości (rys. nr 19).
rys. nr 19 — Wyodrębnij wartości
Otworzy nam się okno wyodrębniania wartości z listy, gdzie w
polu Wybierz ogranicznik, który zostanie użyty do połączenia wartości listy
wybieramy Niestandardowe, a następnie w polu poniżej wpisujemy nasz
ogranicznik, czyli przecinek i spacja. Powyższe parametry zatwierdzamy
przyciskiem OK (rys. nr 20).
rys. nr 20 — Parametry wyodrębniania wartości
Otrzymamy wyodrębnione dane przedstawione na rysunku nr 21.
rys. nr 21 — Wyodrębnione dane
Pozostaje nam wczytać tak przygotowane dane do Excela. W tym
celu korzystamy z 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 wybieramy sposób
wyświetlania danych jako Tabela, a następnie miejsce ich wstawienia –
Istniejący arkusz oraz wskazujemy konkretną komórkę. Tak ustawione parametry
zatwierdzamy przyciskiem Załaduj (rys. nr 23).
rys. nr 23 — Parametry ładowania danych
Otrzymamy dane zaczytane do Excela przedstawione na rysunku
nr 24.
rys. nr 24 — Dane w Excelu
Możemy wprowadzać dowolne zmiany w tabeli z danymi
wejściowymi, a następnie wystarczy kliknąć prawym przyciskiem myszy na tabelę z
naszego zapytania i z podręcznego menu wybrać polecenie Odśwież, aby dane
zostały zaktualizowane.
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 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.
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 nauczymy się jak filtrować dane po
liście wartości. Zagadnienie to omówimy no podstawie przykładowych danych z
rysunku nr 1.
rys. nr 1 — Przykładowe dane
W danych mamy podaną tabelę z listą sprzedawców i wartości
sprzedaży im przypisaną. W naszym zadaniu chcemy wyciągnąć z tej tabeli listę
tylko tych sprzedawców, którzy należą do grupy A (druga tabelka). Możemy to
zadanie zrobić w Power Query. Przede wszystkim musimy te tabelki załadować do
Power Query. Wybieramy polecenie Z tabeli (punkt nr 2 na rysunku nr 2) z karty Dane.
rys. nr 2 — Z tabeli
Wczytujemy sobie najpierw mniejsza tabelkę, czyli ze
sprzedawcami z grupy A. Otworzy nam się okno Edytor zapytań z wczytaną tabelą
tGrupa. Chcemy ją mieć tylko w pamięci, więc od razu, za pomocą polecenia
Zamknij i załaduj do (punkt nr 2 na rysunku nr 3) z karty Narzędzia główne,
przenosimy do Excela.
rys. nr 3 — Zamknij i załaduj do
Otworzy nam się w Excelu okno Ładowanie do, gdzie ustawiamy
parametry naszej nowej tabeli – ustawiamy opcję Utwórz tylko połączenie i
zatwierdzamy przyciskiem Załaduj (rys. nr 4).
rys. nr 4 — Okno Ładowanie do
Teraz ustawiamy aktywną komórkę w obszarze pierwszej tabelki
ze sprzedawcami i jak wyżej korzystając z polecenie Z tabeli z karty Dane,
wczytujemy tabelę do Power Query. Otworzy nam się Edytor zapytań z wczytaną
tabelą tSprzedaż. Aby móc wyciągnąć tylko tych sprzedawców którzy znajdują się
w grupie sprzedawców A musimy scalić nasze dane. Wybieramy polecenie Połącz
(punkt nr 2 na rysunku nr 5) z karty Narzędzia główne, a następnie Scal
zapytania (punkt nr 3 na rysunku nr 5).
rys. nr 5 — Scal zapytania
Otworzy nam się okno Scalanie. W punkcie nr 1 oznaczonym na
rysunku nr 7 wybieramy kolumnę, po której będziemy scalać dane – Sprzedawca. W
punkcie oznaczonym numerem 2 na rysunku nr 7 wybieramy z listy rozwijanej
zapytanie tGrupa. Następnie w punkcie oznaczonym numerem 3 na rysunku nr 7 wybieramy
rodzaj sprzężenia. Musimy pamiętać, że w scalaniu tabelka u góry jest
traktowana jako lewa (oznaczona na rysunku nr 6 strzałką czerwoną, a tabelka na
dole jako prawa (strzałka niebieska).
rys. nr 6 — Okno scalanie
Wybieramy sprzężenie Prawe zewnętrzne, czyli wszystkie z drugiej
tabeli i pasujące z pierwszej. Nasze ustawione parametry zatwierdzamy
przyciskiem OK (rys. nr 7).
rys. nr 7
Otrzymujemy dane przedstawione na rysunku nr 8.
rys. nr 8 — Scalone dane
Kolumnę tGrupa możemy sobie rozwinąć klikając na strzałki
oznaczone na rysunku nr 9 zieloną strzałką. Zatwierdzamy Przyciskiem OK.
rys. nr 9 — Rozwiń
W wyniku rozwinięcia kolumny tGrupa otrzymamy dane
przedstawione na rysunku nr 10.
rys. nr 10 — rozwinięte dane
Są to ci sami sprzedawcy co w pierwszej kolumnie, więc możemy
sobie usunąć ten krok z bocznego panelu Zastosowane kroki i usunąć całkowicie
tą kolumnę za pomocą polecenia Usuń z podręcznego menu (rys. nr 11).
rys. nr 11 — Usuń kolumnę
Otrzymaliśmy wynik jaki nas interesuje, więc musimy go
załadować do Excela. Z karty Narzędzia główne wybieramy polecenie Zamknij i
załaduj do (punkt nr 2 na rysunku nr 12).
rys. nr 12 — Zamknij i załaduj do
Otworzy się w Excelu okno Ładowanie do, gdzie ustawiamy
parametry ładowanych danych i zatwierdzamy przyciskiem Załaduj. Dane wstawione
jako tabela w istniejącym arkuszu, w konkretnej komórce (rys. nr 13).
rys. nr 13 — Okno Ładowanie do
Nasze dane w Excelu pokazane zostały na rysunku nr 14.
rys. nr 14 — Dane zaczytane do Excela
Co jest ciekawego w naszych działaniach w scalaniu – jeśli
wyznaczyliśmy wszystkich sprzedawców, którzy są na liście grupy A, to możemy w
bardzo łatwy sposób wyznaczyć tych, których na tej liście nie ma. Wystarczy
wrócić do Power Query i zmienić typ scalania. Aby nie stracić aktualnych
wyników, skopiujemy je za pomocą polecenia Duplikuj z podręcznego menu (rys. nr 15).
rys. nr 15 — Duplikuj
W ustawieniach zapytania w Zastosowanych krokach musimy
wrócić do kroku Scalanie zapytania i kliknąć w koło zębate przy nazwie tego
zapytania. Otworzy nam się okno Scalanie, gdzie możemy zmienić (w miejscu
oznaczonym zieloną strzałką na rysunku nr 16) rodzaj sprzężenia. Chcemy
wyrzucić te elementy które są w prawej kolumnie (tGrupa), a zachować tylko te, których tam nie ma, więc
musimy wybrać Lewe anty (wiersze tylko w pierwszej).
rys. nr 16 — Lewe anty
Po usunięciu dodatkowej kolumny (tak jak w poprzednim
scalaniu) otrzymujemy dane przedstawione na rysunku nr 17.
rys. nr 17
Następnie wybieramy polecenie Zamknij i załaduj z karty
Narzędzia główne (jak na rysunku nr 12). Otworzy nam się okno Ładowanie do,
gdzie ustawiamy wstawiane dane jako tabela oraz miejsce wstawienia danych –
konkretna komórka w istniejącym arkuszu (analogicznie jak na rysunku nr 13).
Zatwierdzamy te parametry przyciskiem Załaduj i otrzymujemy dane przedstawione
na rysunku nr 18.
rys. nr 18
Innym sposobem rozwiązania tego zagadnienia jest użycie
filtrów. Po raz kolejny wczytujemy dane z tabeli do Power Query. Otworzy nam
się Edytor zapytań z wczytaną tabelą tSprzedaż(3). Rozwijamy podręczne menu za
pomocą ikonki z boku nazwy kolumny Sprzedawca i wybieramy polecenie Filtry
tekstu, a następnie Zawiera (rys. nr 19).
rys. nr 19 — Filtry tekstu
Otworzy nam się okno Filtrowanie wierszy, w którym wpisujemy
jakiś ciąg znaków (bez znaczenia bo później go zmodyfikujemy) i zatwierdzamy OK
(rys. nr 20).
rys. nr 20 — Filtrowanie wierszy
Otrzymamy pustą tabele przedstawioną na rysunku nr 21. Jest
pusta ponieważ żadne ze sprzedawców nie zawiera ciągu liter asd, jakie
wpisaliśmy.
rys. nr 21 — pusta tabela
Została tu użyta funkcja Text.Contains, która ma zwrócić każdy
wiersz, który zawiera ciąg znaków. My chcemy aby funkcja zwróciła nam tabelę
więc użyjemy następującej formuły:
List.ContainsAny(tGrupa[Grupa A],[{Sprzedawca}])
Możemy tę funkcje przetłumaczyć na przypadek, jeśli lista
zawiera którąkolwiek z wartości. Tą formułę musimy wkleić w pasek formuły
zamiast funkcji Text.Contains. Zapis formuły oraz jej wyniki zostały
przedstawione na rysunku nr 22.
rys. nr 22
Otrzymany wynik jest identyczny z tym otrzymanym przez
scalanie. Moim zdaniem dużo bardziej skomplikowany i przeznaczony dla osób
bardziej zaawansowanych w Power Query. Ważną informacją do zapamiętania jest
to, że Power Query nie obsługuje symboli wieloznacznych, takich jak np. * czy
?. Formuły wtedy nie zadziałają. Najlepiej korzystać z tego rozwiązania, które
rozumiemy i potrafimy go używać, ponieważ jest większe prawdopodobieństwo, że
nie popełnimy błędu. Jeśli użyjemy rozwiązania, które sprawia nam trudności i
jest nie do końca zrozumiałe, może się zdarzyć że otrzymamy błędne 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.
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 zajmiemy się tematem scalania tego
samego zapytania w Power Query. Zagadnienie to omówimy na podstawie danych z
poprzedniego postu przedstawionych na rysunku nr 1.
rys. nr 1 — Przykładowe dane
Poprzedni post dotyczył wyciągania pierwszych i ostatnich
wierszy po unikatowych wartościach z kolumn Data i Firma. Stworzyliśmy dwa
zapytania i scaliliśmy je ze sobą. Tym razem chcemy mieć jedno zapytanie.
Zagadnienie to jest wstępem do języka zapytań M, czyli kulis zapytań Power
Query. Pozwoli nam to w miarę naszego rozwoju nie korzystać tylko z gotowych
rozwiązań tego dodatku do Excela.
Pierwszym krokiem jest wczytanie naszych danych z tabeli do
Power Query. Musimy w tym celu użyć polecenia Z tabeli z karty Dane (rys. nr 2).
rys. nr 2 — Pobieranie danych zewnętrznych
Tak jak w poprzednim poście dane zostały wczytane w innym
formacie więc musimy dla kolumny Data i Czas zmienić typ kolumny na bieżącą –
klikamy prawym przyciskiem myszy na tytuł kolumn Data i analogicznie Czas,
następnie wybieramy polecenie Data lub dla drugiej kolumny Godzina i na
komunikacie, który się wyświetli klikamy przycisk Zamień bieżącą (zaznaczono
zieloną strzałką na rysunku nr 3). Celem tych działań jest to, żeby Power Query
odpowiednio interpretował te dane.
rys. nr 3 — Zmiana typu kolumny
Na karcie Widok musimy mieć włączony Pasek formuły (rys. nr
4). Będziemy robić na nim drobne zmiany w kodzie.
rys. nr 4 — Pasek formuły
Wczytane dane przedstawione zostały na rysunku nr 5.
rys. nr 5 — Wczytane dane
Kolejnym krokiem jest posortowanie danych. Klikamy prawym
przyciskiem myszki na trójkącik przy tytule kolumny Data i wybieramy polecenie
Sortuj rosnąco (rys. nr 6).
rys. nr 6 — Sortuj rosnąco
Te same działania musimy powtórzyć dla kolumny Czas. Klikamy
prawym przyciskiem myszki na trójkącik przy tytule kolumny Czas i wybieramy
polecenie Sortuj rosnąco. Z boku ekranu w Ustawieniach zapytania mamy wpisane
kroki, które wykonaliśmy na naszych danych (rys. nr 7).
rys. nr 7 — Ustawienia zapytania
Możemy zmienić nazwę tego kroku, aby w każdym momencie
działań widzieć co konkretnie zrobiliśmy. Naciskamy klawisz F2 i zmieniamy
nazwę na Posortowano Asc (nie napisałem rosnąco, bo ze względu na język
programowania staram się nie używać polskich znaków) – rys. nr 8.
rys. nr 8 — Zmiana nazwy kroku
Teraz tak jak w poprzednim poście musimy usunąć duplikaty po
kolumnach Data i Firma. Zaznaczamy kolumny Data i Firma, a następnie wybieramy
z karty Narzędzia główne polecenie Usuń wiersze (punkt nr 2 na rysunku nr 9), a
potem Usuń duplikaty (punkt nr 3 na rysunku nr 9).
rys. nr 8 — Usuń duplikaty
Otrzymamy wyciągnięte pierwsze wiersze po unikatowych
wartościach. Zostały one przedstawione na rysunku nr 10.
rys. nr 10 — Pierwsze wiersze po unikatowych wartościach
Analogicznie jak dla poprzedniego etapu zmieniamy nazwę
zastosowanego kroku w Ustawieniach zapytania za pomocą klawisza F2 z Usunięto
duplikaty na Pierwsze.
Chcemy aby teraz kolejny krok jaki wykonamy nie był zależny
od kroku Pierwsze tylko od wcześniejszego czyli Posortowano Asc. Klikamy na
krok Posortowano Asc i kopiujemy formułę z paska formuły za pomocą skrótu
klawiszowego Ctrl+C. Następnie przechodzimy do ostatniego naszego kroku, czyli
Pierwsze i na pasku formuły kliknąć przycisk funkcyjny fx – dodać krok
niestandardowy i wkleić (Ctrl+V lub kliknąć prawym przyciskiem myszy i z
podręcznego menu wybrać polecenie Wklej) w pasek formuły skopiowaną wcześniej
formułę z kroku Posortowano Asc.
Musimy zmienić część wklejonej formuły, bo chcemy aby kolumna
Czas była posortowana malejąco. Najprościej wystarczyło by zmienić rodzaj
sortowania klikając na znaczek sortowania przy nazwie kolumny Czas (rys. nr 12).
rys. nr 12 — Sortuj malejąco po kolumnie Czas
Dla bardziej zaawansowanych użytkowników jest inny sposób.
Wystarczy zmienić w formule dla kolumny czas sortowanie z Ascending na Descending,
co zostało pokazane na rysunku nr 13.
rys. nr 13 — Zmiana na pasku formuły
Oba sposoby zadziałają w ten sam sposób. Zmieniamy nazwę
kroku Niestandardowe 1 za pomocą klawisza F2 na Posortowano Desc. Musimy
pamiętać, że Power Query ma problem z tym, że dane nie zostały wczytane od
nowa, pamięta wcześniejszy bufor więc jak w poprzednim poście musimy dodać
kolumnę indeksu. Z karty Dodaj kolumnę wybieramy polecenie Kolumna indeksu
(punkt nr 2 na rysunku nr 14) a następnie Od 0 (punkt nr 3 na rysunku nr 14).
Nie ma znaczenia jaki typ kolumny indeksu wybierzemy bo jest to kolumna
pomocnicza i w późniejszym etapie ją usuniemy.
rys. nr 14 — Kolumna indeksu
Power Query dzięki dodaniu dodatkowej kolumny od nowa zaczyta
dane i zapamięta kolejność z odwrotnym sortowaniem. Zaznaczamy kolumny Data i
Firma i z karty Narzędzia główne wybieramy polecenie Usuń wiersze a następnie
Usuń duplikaty (analogicznie jak na rysunku nr 9 dla pierwszych wierszy).
Otrzymamy ostatnie wiersze dla unikatowych wartości przedstawione na rysunku nr 15.
rys. nr 15 — Ostatnie wiersze dla unikatowych wartości
Zaznaczamy kolumnę Czas i Indeks i usuwamy za pomocą polecenia Usuń kolumny z podręcznego menu (rys. nr 16).
rys. nr 16 — Usuń kolumny
W
poprzednim poście te same wyniki otrzymaliśmy w dwóch osobnych zapytaniach. W
tym poście otrzymaliśmy takie same wyniki w jednym zapytaniu tylko w innych
krokach. Pierwsze wiersze są w kroku Pierwsze, a ostatnie wiersze są w kroku
Usunięto kolumny. Zmienimy jeszcze nazwę ostatniego kroku za pomocą klawisza F2
na Ostatnie (rys. nr 17). Teraz wiemy, które kroki chcemy połączyć.
rys. nr 17 — Kroki które trzeba połączyć
Z karty Narzędzia główne wybieramy polecenie Połącz (punkt nr
2 na rysunku nr 18), a następnie Scal zapytania (punkt nr 3 na rysunku nr 18).
rys. nr 18 — Scal zapytania
Otworzy nam się okno Scalanie. Scalać będziemy to samo
zapytanie, czyli wybierzemy tNotowania i w drugim okienku tNotowania (bieżący).
Zaznaczamy w obu zapytaniach kolumny Data i Firma. One są na razie identyczne.
W kolejnym kroku zrobimy pewną zmianę. Zatwierdzamy nasz wybór przyciskiem OK
(rys. nr 19).
rys. nr 19 — Okno scalanie
W tym kroku bardzo ważne jest, że widzimy pasek formuły, bo
mamy tam podane, na którym kroku wykonujemy dane zadanie (rys. nr 20).
rys. nr 20 — Pasek formuły
W naszej formule powinniśmy zmienić pierwsze wystąpienie
słowa Ostanie na słowo Pierwsze (nazwy kroków zapytania). Aby otrzymać
prawidłowe dane formuła na pasku formuły powinna wyglądać jak na rysunku nr 21.
rys. nr 21 — Zmiana nazwy kroku w formule
W formule powyżej mamy informację, że chcemy połączyć dane z
dwóch kroków w tym samym zapytaniu – krok Pierwsze i krok Ostatnie.
Z naszej tabeli musimy się pozbyć kolumny Czas za pomocą
polecenie Usuń kolumnę z podręcznego menu. Otrzymamy dane przedstawione na
rysunku nr 22.
rys. nr 22 — Usuń kolumnę
Następnie rozwijamy kolumnę Ostatnie (strzałki w prawym roku
nazwy kolumny), odznaczamy Datę i Firmę, a zostawiamy tylko Cenę. Nie chcemy
używać oryginalnej nazwy kolumny jako prefiksu i na koniec zatwierdzamy nasze
ustawienia przyciskiem OK (rys. nr 23).
rys. nr 23
W otrzymanych danych zmienimy nazwę dwóch ostatnich kolumn z
Cenami na Pierwsze i Ostatnie. Otrzymamy końcowe dane dla pierwszych i
ostatnich wierszy unikatowych wartości przedstawione na rysunku nr 24.
rys. nr 24 — Pierwsze i ostatnie wiersze dla unikatowych wartości
Są to dane uzyskane w jednym zapytaniu, włącznie ze
scalaniem. Był to wstęp do zagłębiania się w język M, aby poznać lepiej formuły
i możliwości jakie nam dają. Ostatnim krokiem jest załadowanie danych do Excela
za pomocą polecenia Zamknij i załaduj z karty Narzędzia główne (rys. nr 25).
rys. nr 25 — Zamknij i załaduj
W Excelu otworzy nam się okno Ładowanie do, gdzie wybieramy
sposób wyświetlania danych jako Tabela i miejsce załadowania danych jako
Istniejący arkusz (konkretną komórkę). Nasz wybór zatwierdzamy przyciskiem
Załaduj (rys. nr 26).
rys. nr 26 — Okno Ładowanie do
Dane wklejone do Excela prezentują się jak na rysunku nr 27.
Dane wklejone do Excela
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.
