W tym poście nauczymy się jak stworzyć ranking sprzedawców. W przykładowych danych na rys. nr 1 mamy zestawienie sprzedawców i ich sprzedaż. Z tych danych musimy zrobić podsumowanie sprzedaży poszczególnych sprzedawców, a następnie zrobić ranking, czyli wyciągnąć sprzedawców z największą sprzedażą.
Rys. nr 1 – przykładowe dane
Najpierw
zadanie to wykonamy za pomocą formuł i funkcjonalności Excela, a następnie to
samo zadanie wykonamy z wykorzystaniem tabeli przestawnej.
Pierwszym
krokiem będzie wyciągnięcie unikatowych wartości z kolumny Sprzedawca. Możemy
ręcznie skopiować wszystkich sprzedawców z kolumny B za pomocą skrótu
klawiszowego Ctrl+C, a następnie wkleić do kolumny G za pomocą skrótu Ctrl+V. Zaznaczamy
kolumnę G (Sprzedawca), a następnie wybieramy ikonkę polecenia Usuń
duplikaty z karty Dane (rys. nr 2).
Rys. nr 2 – polecenie Usuń duplikaty
Excel wyświetli nam komunikat Ostrzeżenie o usuwaniu duplikatów, w którym zaznaczamy checkbox przy opcji Pozostawić obecnie zaznaczony obszar, następnie zatwierdzamy komunikat przyciskiem Usuń duplikaty (rys. nr 3).
Rys. nr 3 – Ostrzeżenie o usuwaniu duplikatów
Wyświetli się okno Usuwania duplikatów, które zatwierdzamy przyciskiem OK (rys. nr 4).
Rys. nr 4 – okno Usuwania duplikatów
Wyświetli się kolejny komunikat, tym razem z informacją o liczbie usuniętych duplikatów i unikatowych wartości przedstawiony na rys. nr 5, który zatwierdzamy przyciskiem OK.
Rys. nr 5 – komunikat Excela
Otrzymamy kolumnę z sześcioma unikatowymi wartościami przedstawioną na rys. nr 6.
Rys. nr 6 – kolumna z unikatowymi wartościami (sprzedawcami)
Powyższe dane możemy posortować od A do Z za pomocą ikonki polecenia na karcie Dane (rys. nr 7).
Rys. nr 7 – polecenie sortowania danych od A do Z
Teraz musimy
podsumować sprzedaż poszczególnych sprzedawców. Użyjemy do tego funkcji SUMA.JEŻELI.
Pierwszym argumentem funkcji jest zakres, czyli zakres po którym
będziemy sprawdzać nasze kryterium (aby zaznaczyć całą kolumnę możemy użyć skrótu
klawiszowego Ctrl+Shift+Strzałka w dół. Zaznaczony zakres (B2:B325)
blokujemy bezwzględnie za pomocą klawisza F4. Drugi argument funkcji to kryteria,
czyli nazwa aktualnego sprzedawcy, którego nie blokujemy, ponieważ ma się
przesuwać w dół razem z kopiowanie formuły na wiersze poniżej. Trzeci argument
funkcji to suma_zakres, czyli kolumna, po której chcemy podsumować nasze
dane (E2:E325), zablokowana bezwzględnie. Zapis całej formuły powinien wyglądać
następująco:
=SUMA.JEŻELI($B$2:$B$325;G2;$E$2:$E$325)
Powyższą formułę
zatwierdzamy i kopiujemy w dół. Otrzymamy podsumowanie sprzedaży przedstawione
na rys. nr 8.
Rys. nr 8 – podsumowanie sprzedaży unikatowych sprzedawców
Otrzymaliśmy
sprzedaż sumaryczną wszystkich sprzedawców. Teraz możemy wykorzystać funkcję POZYCJA.NAJW
(pozycja największa), żeby określić pozycję danego wyniku w rankingu. Pierwszym
argumentem funkcji jest liczba, czyli konkretna wartość sprzedaży (H2).
Drugi argument funkcji to odwołanie, czyli zakres, z którego chcemy
zrobić ranking (H2:H7), zablokowany bezwzględnie za pomocą klawisza F4. Zapis
całej formuły powinien wyglądać następująco:
=POZYCJA.NAJW(H2;$H$2:$H$7)
Powyższą
formułę zatwierdzamy i przeciągamy na wiersze poniżej. Otrzymamy ranking pozycję
poszczególnych sprzedawców w rankingu sprzedaży przedstawiony na rys. nr 9.
Rys. nr 9 – ranking sprzedawców według wysokości sprzedaży
Jak widać na
rysunku powyżej mamy przyznane miejsca w rankingu, Cezary jest na pierwszym
miejscu a Paulina na ostatnim – szóstym. Aby ranking dobrze się prezentował to
musimy mieć posortowanych sprzedawców według miejsc, które kolejno zajmują.
Wykorzystamy do tego funkcje INDEKS, która będzie "patrzyła"
na kolumnę G (Sprzedawca), czyli w pierwszym argumencie podajemy zakres
zablokowany bezwzględnie — $G$2:$G$7. Drugi argument to nr_wiersza, czyli
pozycja poszczególnych sprzedawców. Aby uzyskać te pozysje musimy użyć funkcji PODAJ.POZYCJĘ.
Pierwszym argumentem funkcji jest szukana_wartość, czyli numer pozycji,
której szukamy – pozycja 1 (komórka K2). Drugi argument to przeszukiwana_tab,
czyli kolumna z rankingiem zablokowana bezwzględnie ($I$2:$I$7). Trzeci
argument to typ_porównania, wpisujemy wartość 0 – dopasowanie dokładne.
Zapis całej formuły powinien wyglądać następująco:
=INDEKS($G$2:$G$7;PODAJ.POZYCJĘ(K2;$I$2:$I$7;0))
Funkcja PODAJ.POZYCJĘ
zwraca nam numer wiersza z danym numerem w indeksie, a funkcja INDEKS zwraca
nam wartość z tego wiersza. Po zatwierdzeniu powyższej formuły i skopiowaniu
jej w dół, otrzymamy ranking przedstawiony na rys. nr 10.
Rys. nr 10 – ranking sprzedawców
Teraz pokażemy rozwiązanie wykorzystujące tabelę przestawną. Zaznaczamy pojedynczą komórkę w tablicy danych, na podstawie których chcemy wykonać tabelę przestawną. Wybieramy polecenie Tabela przestawna z karty Wstawianie (rys. nr 11).
Rys. nr 11 – polecenie Tabela przestawna
Otworzy nam się okno Tworzenia tabeli przestawnej, w którym wybieramy, gdzie chcemy umieścić raport w postaci tabeli przestawnej (Istniejący arkusz), wskazujemy konkretną komórkę (np. G10). Tak ustawione parametry zatwierdzamy przyciskiem OK.
Rys. nr 12 – okno Tworzenia tabeli przestawnej
Przeciągamy Sprzedawcę do obszaru Etykiet wierszy (punkt 1 na rys. nr 13), następnie przeciągamy Sprzedaż do obszaru Podsumowań wartości (punkt 2).
Rys. nr 13 – tworzenie tabeli przestawnej
Otrzymamy tabelę przedstawioną na rys. nr 14.
Rys. nr 14 – tabela przestawna
Mamy podsumowanie sprzedaży. W kolumnie Suma z Sprzedaż powinniśmy zmienić formatowanie, w tym celu klikamy prawym przyciskiem myszy na dowolną komórkę z tej kolumny, następnie z podręcznego menu wybieramy polecenie Format liczby (rys. nr 15).
Rys. nr 15 – polecenie Format liczby
Otworzy nam się okno Formatowania komórek, gdzie w polu Kategoria wybieramy Walutowe, następnie zatwierdzamy zmianę przyciskiem OK (rys. nr 16).
Rys. nr 16 – zmiana formatowania na walutowe
Otrzymamy sformatowane dane przedstawione na rys. nr 17.
Rys. nr 17 – sformatowane dane
W tabeli przestawnej możemy sobie posortować dane od największej do najmniejszej za pomocą ikony sortowania od Z do A na karcie Dane. Posortowane dane zostały przedstawione na rys. nr 18.
Rys. nr 18 – posortowane dane
Otrzymaliśmy niejako ranking, ale nas mniej interesuje wartość sprzedaży, a bardziej numer pozycji danego sprzedawcy. Pole Sprzedaż jeszcze raz przeciągniemy do obszaru podsumowań wartości jak na rys. nr 13 (punkt 2). W nowej kolumnie klikamy prawym przyciskiem myszy i z podręcznego menu rozwijamy polecenie Pokaż wartości jako, a następnie wybieramy polecenie Porządkuj wartości od największych do najmniejszych (rys. nr 19).
Rys. nr 19 – polecenie Porządkuj wartości od największych do najmniejszych
Otworzy nam się okno Pokazywania wartości jako, gdzie na liście rozwijanej w polu podstawowym wybieramy Sprzedawca. Tak ustawiony parametr zatwierdzamy przyciskiem OK (rys. nr 20).
Rys. nr 20 – okno Pokazywania wartości jako
Otrzymamy ranking sprzedawców w odpowiedniej kolejności, ponieważ wcześniej posortowaliśmy dane. Ponadto możemy zmienić nazwę kolumny Suma z Sprzedaż2 na Ranking (rys. nr 21).
Rys. nr 21 – ranking sprzedawców
Możemy pozbyć się wartości Suma końcowa, ponieważ jest nam zbędna. W tym celu rozwijamy polecenie Sumy końcowe (punkt 2 na rys. nr 22) z karty Projektowanie (punkt 1), a następnie wybieramy polecenie Wyłącz dla wierszy i kolumn (punkt 2).
Rys. nr 22 – ścieżka dostępu do polecenia Wyłącz dla wierszy i kolumn
Otrzymamy tabelę przestawną z rankingiem sprzedawców bez sumy końcowej przedstawioną na rys. nr 23.
Rys. nr 23 – ranking sprzedawców bez sumy końcowej
Podsumowując stworzenie rankingu możemy wykonać na dwa sposoby, za pomocą funkcjonalności klasycznego Excela i tabeli przestawnej. Jak widać powyżej szybszym sposobem jest wykorzystanie tabeli przestawnej.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W
dzisiejszym poście omówimy jak za pomocą Power Query możemy wyciągnąć listę
wszystkich folderów, podfolderów wskazanego folderu. Rozwiązanie to jest
proste, ale ma jedną wadę, o której pomówimy na końcu. W celu wykonania zadania
rozwijamy polecenie Pobierzdane (punkt 2 na rys. nr 1) z karty Dane
(punkt 1), następnie rozwijamy polecenie Zpliku (punkt 3) i
wybieramy polecenie Zfolderu (punkt 4).
Rys. nr 1 – ścieżka dostępu do polecenie Z folderu
Na potrzeby
naszego zadania przygotowałem katalog z większą ilością podfolderów o nazwie
WebinarPQ. Po użyciu polecenia z rys. nr 1 otworzy nam się okno Folder, gdzie musimy
wkleić ścieżkę dostępu do tego folderu lub przy użyciu przycisku Przeglądaj,
wyszukać folder ręcznie. Wklejoną ścieżkę do wybranego folderu zatwierdzamy
przyciskiem OK (rys. nr 2).
Rys. nr 2 – okno Folder
Wtedy Power
Query pobierze informacje o wszystkich plikach znajdujących się w folderze
głównym i jego pod folderach. Otworzy się okno z wybranej ścieżki dostępu,
gdzie wystarczy kliknąć przycisk Przekształć dane, aby przejść do ich
edycji (rys. nr 3). Dzięki drobnym przekształceniom danych otrzymamy listę
wszystkich elementów wybranego folderu.
Rys. nr 3 – okno z elementami wybranego folderu
Otworzy nam
się Edytor zapytań Power Query z wczytanymi danymi przedstawiony na rys. nr 4.
Rys. nr 4 – fragment danych z edytora zapytań Power Query
Dla nas
istotna jest tylko ostatnia kolumna o nazwie Folder Path, inne są zbędne
ponieważ zawierają ind=formacje na temat poszczególnych plików. Klikamy na
nazwę kolumny Folder Path prawym przyciskiem myszy i z podręcznego menu
wybieramy polecenie Usuń inne kolumny (rys. nr 5).
Rys. nr 5 – polecenie Usuń inne kolumny
Otrzymamy
listę wszystkich folderów i pod folderów zawartych w katalogu WebinarPQ
przedstawioną na rys. nr 6.
Rys. nr 6 – lista wszystkich folderów i pod folderów katalogu WebinarPQ
Rys. nr 7 – polecenie Usuń duplikaty Jak łatwo zauważyć poszczególne ścieżki się powtarzają, więc musimy je usunąć. Zaznaczamy kolumnę i rozwijamy polecenie Usuń wiersze a następnie wybieramy polecenie Usuń duplikaty (rys. nr 7).
Rys. nr 7 – polecenie Usuń duplikaty
Otrzymamy
listę ścieżek dostępu do wszystkich pod folderów wybranego katalogu
przedstawioną na rys. nr 8.
Rys. nr 8 – Lista ścieżek dostępu do wszystkich pod folderów wybranego katalogu
Jeśli
potrzebujemy jakieś dodatkowe dane to możemy dalej przekształcać te dane.
Naszym zadaniem było przygotowanie listy wszystkich pod folderów. Nasza lista
zawiera również główny folder, czyli WebinarPQ, możemy go usunąć rozwijając
podręcznego menu (przycisk z trójkącikiem przy nazwie kolumny), a następnie
odznaczając checkbox przy ścieżce dostępu do głównego katalogu. Zmiany w
filtrowaniu zatwierdzamy przyciskiem OK (rys. nr 9).
Rys. nr 9 – odfiltrowanie danych (usunięcie ścieżki dostępu do głównego katalogu)
Otrzymamy
dane z listą ścieżek dostępu do pod folderów, natomiast bez ścieżki dostępu do
głównego katalogu (rys. nr 10).
Rys. nr 10 – lista ścieżek dostępu do pod folderów katalogu WebinarPQ
Rys. nr 11 – polecenie Zamknij i załaduj doTak przygotowane dane możemy załadować do Excela za pomocą polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 11).
Rys. nr 11 – polecenie Zamknij i załaduj do
Otworzy nam
się okno Importowania danych, gdzie ustawiamy sposób wyświetlania danych jako
tabela i wskazujemy miejsce ich wstawienia, czyli istniejący arkusz i konkretna
komórka. Tak ustawione parametry importowania danych zatwierdzamy przyciskiem
OK (rys. nr 12).
Rys. nr 12 – okno Importowania danych
Otrzymamy
dane zaimportowane do Excela i przedstawione na rys. nr 13.
Rys. nr 13 – Dane zaimportowane z Power Query
Jak wspominaliśmy
na początku, rozwiązanie to jest proste, lecz ma jedną wadę. Mianowicie jeśli
podejrzymy pliki w folderze WebinarPQ, widzimy że mamy dodatkowy folder o
nazwie Nowy folder, który nie pojawił się na naszej liście (rys. nr 14). Dzieje
się tak dlatego, że folder ten jest pusty.
Rys. nr 14 – pod foldery katalogu WebinarPQ
Pusty folder
nie pojawił się na liście otrzymanej przy użyciu funkcji, która wyciąga
informacje o wszystkich plikach znajdujących się we wskazanym folderze,
ponieważ nie zawierał żadnych plików. Podsumowując przedstawiony sposób działa
poprawnie w 90%, ale musimy pamiętać o pustych folderach.
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 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 omówimy temat rozrastającej się listy rozwijanej z
unikatowymi wartościami w
Power Query. Rozwiązanie tego zadania w Excelu przedstawione zostało w poradzie
nr 322 https://exceliadam.pl/?s=porada+322
. W danych źródłowych mamy już
listę rozwijaną a naszym zadaniem jest dodanie do niej kolejnych elementów,
chcemy dopisać do listy nowe osoby. Dane, na których omówimy to zagadnienie
zostały przedstawione na rysunku nr 1.
rys. nr 1 — Przykładowe dane
Rozwiążemy takie zadanie nie korzystając z formuł, ale przy użyciu Power Query – dodatku do Excela. Pierwszym krokiem jest zaczytanie danych do Power Query. Wybieramy polecenie Z tabeli z karty Dane (rys. nr 2).
rys. nr 2 — Z tabeli
Otworzy
nam się edytor zapytań z wczytaną tabelą tSprzedawcy.
rys. nr 3 — Edytor zapytań
Usuwamy krok Zmieniono typ z zastosowanych kroków, bo jest on zbędny.
Następnie odfiltrowujemy kolumnę Sprzedawca po wartościach null, czyli klikamy
na ikonkę trójkąta w nazwie kolumny Sprzedawca i w podręcznym menu ozdnaczamy
checkbox przy wartości null (rys. nr 4). Nasz filtr zatwierdzamy przyciskiem
OK.
rys. nr 4 — Odfiltruj dane po wartości null
W kolejnym kroku usuwamy inne kolumny, czyli klikamy prawym przyciskiem
myszy na tytuł kolumny Sprzedawca i z podręcznego menu wybieramy polecenie Usuń
inne kolumny (rys. nr 5).
rys. nr 5 — Usuń inne kolumny
Otrzymamy dane przedstawione na rysunku nr 6.
rys. nr 6
Interesuje nas tylko kolumna Sprzedawca. Chcemy mieć unikatową listę
sprzedawców, więc rozwijamy polecenie Usuń wiersze (punkt nr 2 na rysunku nr 7)
z karty Narzędzia główne, a następnie wybieramy polecenie Usuń duplikaty (punkt
nr 3 na rysunku nr 7).
rys. nr 7 — Usuń duplikaty
Tak przygotowaną listę danych możemy załadować do Excela. W tym celu
rozwijamy polecenie Zamknij i załaduj (punkt nr 2 na rysunku nr 8) z karty
Narzędzia główne, a następnie wybieramy polecenie Zamknij i załaduj do (punkt
nr 3 na rysunku nr 8).
rys. nr 8 — Zamknij i załaduj do
Otworzy nam się okno Ładowania do, gdzie wybieramy sposób wyświetlania
danych jako Tabela, a nastepnie określamy lokalizaję wstawienia danych –
Istaniejący arkusz i wskazujemy konkretną komórkę. Tak ustawione parametry
zatwierdzamy przyciskiem Załaduj (rys. nr 9).
rys. nr 9 — Okno Ładowania do
Otrzymamy dane wczytane do Excela przedstawione na rysunku nr 10.
rys. nr 10 — Dane wczytane do Excela
Zaznaczamy zakres danych w tabeli z zapytania z Power Query a następnie w
polu obok paska formuły zmieniamy nazwę tego zakresu na Sprzedawcy (pole
oznaczone zieloną strzałką na rysunku nr 11).
rys. nr 11 — Zmiana nazwy zakresu
W kolejnym etapie zaznaczamy zakres w tabeli z danymi źródłowymi i
wybieramy polecenie Poprawność danych (punkr nr 2 na rysunku nr 12) z karty Dane.
rys. nr 12 — Poprawność danych
Otworzy nam się okno Sprawdzania poprawności danych, gdzie w karcie
Ustawienia (rys. nr 13) ustalamy Kryteria poprawności danych i podajemy źródło
danych (klawisz F3) – wcześniej nazwany zakres Sprzedawcy z tabeli zaczytanej z
Power Query. W karcie Komunikat wejściowy odznaczamy checkbox przy opcji
Pokazuj komunikat wejściowy przy wyborze komórki. W karcie Alert o błędzie
odznaczamy checkbox przy opcji Pokazuj alerty po wprowadzeniu nieprawidłowych
danych. Nie chcemy informacji o błędnie wpisanych danych ponieważ chcemy
dopisywać nowe osoby do listy sprzedawców. Tak ustawione parametry zatwierdzamy
przyciskiem OK.
rys. nr 13 — Użycie klawisza F3
Teraz możemy sobie dopisać sprzedawcę w
tabeli z danymi źródłowymi, ale nie ma jej na liście rozwijanej w tej tabeli co
przedstawia rysunek nr 14.
rys. nr 14 — Lista rozwijana
Wynika to z podstawowej wady Power Query –
nie odświeża się automatycznie. Musimy kliknąc prawym przyciskiem myszy na
dowolną komórkę w zakresie Sprzedawcy i z podręcznego menu wybrać polecenie
Odśwież (rys. nr 15).
rys. nr 15 — Odśwież
Po odświeżeniu danych z Power Query dodany sprzedawca będzie widoczny na liście
rozwijanej (rys. nr 16).
rys. nr 16
Istnieje możliwość ustawienia automatycznego odświeżania
danych za pomocą kodu VBA. Korzystając ze skrótu klawiszowego Alt+F11 możemy
przejść do okna Edytora VBA. Jest tam wcześniej przygotowany kod (rys. nr 17).
rys. nr 17 — Alt+F4 przejście do VBA
W arkuszu (Arkusz4 (PQ31)- punkt nr 1 na rysunku nr 18), w
którym mamy te listy , musimy dopisać kod VBA. Kod ten będzie działał tylko w
momencie, kiedy w naszym arkuszu (Worksheet – punkt nr 2 na rysunku nr 18))
dokona się zmiana (Change – Punkt nr 3 na rysunku nr 18). W sytuacji zmiany w
kolumnie A, chcemy aby odpalił się kod VBA i sprawdził czy zmieniane komórki
miały część wspólną z kolumną A. Konkretnie sprawdzamy czy zakres który był
zmieniany ma część wspólną z kolumną która nas interesuje. Jeśli zmiana
nastąpiła w kolumnie A, to nastąpi automatyczne odświeżenie danych w tabeli z
Power Query.
rys. nr 18 — Edytor VBA
Zapisujemy nasz kod za pomocą skrótu klawiszowego Ctrl+S.
Przechodzimy do Excela i możemy sprawdzić działanie kodu VBA. Dopisujemy
kolejnego sprzedawcę (Agnieszka) do danych źródłowych i dane automatycznie się
odświeżą i nasz nowy sprzedawca zostanie dodany do listy rozwijanej, co widać
na rysunku nr 19.
rys. nr 19 — Dane z kodem z VBA
Podsumowując rozrastającą się listę rozwijaną z unikatowymi
wartościami robi się prościej za pomocą Power Query, ale niestety nie jest
automatyczna i musimy pamiętać o odświeżaniu danych. Jedynym sposobem na
zautomatyzowanie jest dodanie kodu VBA, ale to już temat dla bardziej
zaawansowanych użytkowników Excela.
Możemy również zarejestrować makro odświeżania danych w
karcie Deweloper, wybierając polecenie Rejestruj makro (punkt nr 2 na rysunku
nr 20).
rys. nr 20 — Zarejestruj makro
Otworzy się okno Rejestrowania makra, gdzie wpisujemy nazwę
makra i zatwierdzamy przyciskiem OK (rys. nr 21).
rys. nr 21 — Okno rejestrowania makra
Następnie klikamy prawym przyciskiem myszy na dowolną komórkę
z zakresu zapytania z Power Query i z podręcznego menu wybieramy polecenie
Odśwież (rys. nr 22).
rys. nr 22 — Odśwież
Następnie klikamy polecenie Zatrzymaj rejestrowanie (punkt nr
2 na rysunku nr 23) z karty Deweloper.
rys. nr 23 — zatrzymaj rejestrowanie makra
Teraz w VBA mamy dostępny nowy Moduł, odpowiadający
odświeżeniu danych (zaznaczony zieloną strzałką na rysunku nr 24).
rys. nr 24 — Nowy moduł
Dzięki stworzeniu takiego makra mamy automatycznie
rozrastającą się listę rozwijaną.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
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.
