W
dzisiejszym poście omówimy numerowanie poszczególnych elementów danej kategorii
sprzedaży. Rozwiązanie takiego problemu w Excelu omówiliśmy w poradzie 373.
Dziś zajmiemy się rozwiązaniem tego problemu w Power Query. Przykładowe dane do
zadania zostały przedstawione na rys. nr 1.
Rys. nr 1 – przykładowe dane do zadania
Rozwiązanie,
które omówimy opiera się na pomyśle Gena Pulsa (www.excelguru.ce). Zacznijmy od pobrania naszych
danych do zadania. Wybieramy polecenie Z tabeli/ zakresu z karty Dane
(rys. nr 2).
Rys. nr 2 – polecenie Z tabeli/ zakresu
Otworzy nam
się edytor zapytań Power Query z wczytaną tabelą z Excela przedstawiony na rys.
nr 3.
Rys. nr 3 – edytor zapytań z wczytaną tabelą
We wczytanych
danych musimy zrobić drobną korektę. Mianowicie zmienić typ danych w kolumnie
Data, czyli klikamy ikonę kalendarza w tytule kolumny i wybieramy z podręcznego
menu polecenie Data (rys. nr 4).
Rys. nr 4 – zmiana typu danych na Datę
Otworzy nam
się okno Zmiany typu kolumny, gdzie musimy zatwierdzić naszą decyzję za pomocą
przycisku Zamień bieżącą (rys. nr 5).
Rys. nr 5 – okno Zmiany typu kolumny
Naszym celem
jest ponumerowanie elementów poszczególnych kategorii, ale pod warunkiem
zachowania aktualnej kolejności elementów. Z tego powodu w pierwszej kolejności
musimy dodać kolumnę indeksu. W tym celu rozwijamy polecenie KolumnaIndeksu
(punkt 2 na rys. nr 6) z karty Dodaj kolumnę (punkt 1), a następnie
wybieramy polecenie Od 0 (punkt 3).
Rys. nr 6 – ścieżka dostępu do polecenia Od 0 (kolumna indeksu)
Otrzymamy
dane z nową kolumną (kolumna indeksu), która sprawi, że nasze dane zachowają
swoją kolejność (rys. nr 7).
Rys. nr 7 – Dane z dodaną kolumną indeksu
Będziemy
potrzebować paska formuły w dalszych działaniach. Jeśli nie macie go
włączonego, można go uruchomić za pomocą polecenia Pasek formuły na
karcie Widok (oznaczone strzałką na rys. nr 8).
Rys. nr 8 – uruchomienie paska formuły
Dodatkowo
możemy zmienić nazwę nowej kolumny w pasku formuły, żeby nie dodawać kolejnego
kroku do Zastosowanych kroków. Zmieniamy nazwę nowej kolumny na IndeksTmp (rys.
nr 9), żebyśmy wiedzieli że jest on tymczasowy i służy tylko do zachowania
oryginalnej kolejności elementów w tabeli.
Rys. nr 9 – zmiana nazwy nowej kolumny w pasku formuły
W następnym
kroku musimy pogrupować dane po kategorii, czyli 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 w polu Grupuj według wybieramy grupowanie
według Kategorii (punkt 1 na rys. nr 11), następnie ustawiamy rodzaj Operacji
jako Wszystkie wiersze (punkt 2). Ponadto możemy zmienić nazwę kolumny na TMP
(punkt 3), ponieważ znowu jest to kolumna tymczasowa i w następnych etapach
będziemy ją modyfikować. Tak ustawione parametry grupowania zatwierdzamy
przyciskiem OK.
Rys. nr 11 – parametry grupowania według
Otrzymamy
pogrupowane dane przedstawione na rys. nr 12. Istotne jest, że mamy podział na
4 główne grupy produktów i w kolumnie TMP mamy tabele z poszczególnymi
elementami danej grupy. Warto zaznaczyć, że są to tabele ze wszystkimi
kolumnami z naszej oryginalnej tabeli z zachowaniem kolejności z kolumny
IndeksTmp.
Rys. nr 12 – Pogrupowane dane
Naszym
zadaniem teraz jest dołożenie kolejnej kolumny indeks w tych wewnętrznych
tabelach z kolumny TMP. Dodajemy nową kolumnę indeks, czyli rozwijamy polecenie
Kolumna indeksu z karty Dodaj kolumnę, a następnie wybieramy
polecenie Od 1 (tym razem numerowanie zaczynamy od 1) jak na rys. nr 13.
Rys. nr 13 – polecenie Od 1 (kolumna indeks)
Otrzymamy
dane z nową kolumną przedstawione na rys. nr 14.
Rys. nr 14 – Dane z nową kolumną Indeks
Dzięki
dodaniu tej kolumny w pasku formuły mamy gotowy kod na dodanie kolumny indeksu
(rys. nr 15).
Rys. nr 15 – kod na dodanie kolumny indeks numerowanej od 1
Kopiujemy
ten kod za pomocą skrótu klawiszowego Ctrl+C i usuwamy dodatkowy krok (Dodano
indeks 1), ponieważ był nam potrzebny tylko do otrzymania kodu. Krok usuwamy za
pomocą oznaczenia x przed nazwą danego kroku (rys. nr 16).
Rys. nr 16 – usuwanie kroku
Następnie
wybieramy polecenie Kolumna niestandardowa z karty Dodaj kolumnę
(rys. nr 17).
Rys. nr 17 – polecenie Kolumna niestandardowa
Otworzy nam
się okno Kolumny niestandardowej, gdzie w polu Formuła kolumny niestandardowej
wklejamy nasz skopiowany wcześniej kod (z paska formuły) za pomocą skrótu
klawiszowego Ctrl+V. W formule tej musimy zmienić jedną rzecz ponieważ ta
formuła opierała się na wcześniejszym kroku, czyli na pogrupowaniu wierszy).
Naszym celem jest dodanie kolumny indeksu do tabeli, która znajduje się w
kolumnie TMP. Czyli kasujemy z zapisu formuły nazwę kroku a wstawiamy nazwę
kolumny. Zapis formuły powinien wyglądać następująco:
=Table.AddIndexColumn([TMP],
"Indeks", 1, 1)
Tak
ustawione parametry kolumny niestandardowej zatwierdzamy przyciskiem OK (rys.
nr 18).
Rys. nr 18 – parametry kolumny niestandardowej
Otrzymamy
dane z nową kolumną niestandardową przedstawione na rys. nr 19, gdzie w
kolumnie są ukryte tabele z poprzedniej kolumny ale z dodatkową kolumną indeksu
(ponumerowane elementy w każdej kategorii).
Rys. nr 19 – dane z nową kolumną niestandardową
Możemy teraz
usunąć dwie pierwsze kolumny, ponieważ one nic nam nie wnoszą do wyników,
jakich potrzebujemy. Zaznaczamy dwie kolumny Kategoria i TMP przytrzymując
klawisz Ctrl, następnie usuwamy je za pomocą klawisza Delete. Otrzymamy kolumnę
Niestandardowe, którą będziemy musieli rozwinąć. W tym celu klikamy ikonkę ze
strzałkami obok tytułu kolumny i odznaczamy checkbox przy opcji Użyj
oryginalnej nazwy kolumny jako indeksu. Parametry rozwinięcia zatwierdzamy
przyciskiem OK (rys. nr 20).
Rys. nr 20 – Rozwijanie kolumny niestandardowej
Otrzymamy
rozwinięte dane przedstawione na rys. nr 21.
Rys. nr 21 – rozwinięte dane z kolumny niestandardowej
Przez to że
pogrupowaliśmy dane po kategoriach mamy najpierw wszystkie myszki, potem
klawiatury itd. Pamiętajmy, że dodaliśmy kolumnę IndeksTmp po to, aby móc
wrócić do oryginalnej kolejności elementów. Wystarczy teraz zanzcayć kolumnę
IndeksTmp i wybrać polecenie sortuj od A do Z z karty Narzędzia
główne (rys. nr 22).
Rys. nr 22 – polecenie Sortuj od A do Z
Otrzymamy
dane posortowane według pierwotnej kolejności przedstawione na rys. nr 23.
Rys. nr 23 – posortowane dane
Kolumna
IndeksTmp jest nam już niepotrzebna, więc zaznaczamy ją i usuwamy za pomocą
klawisza Delete. Otrzymamy dane z numerowaniem (indeksem) elementów odpowiednio
w kategoriach przedstawione na rys. nr 24.
Rys. nr 24 – Dane z numerowaniem elementów poszczególnych kategorii
Tak
przygotowane dane możemy zaczytać do Excela. W tym celu wybieramy polecenie Zamknij
i załaduj do z karty Narzędzia główne (rys. nr 25).
Rys. nr 25 – polecenie Zamknij i załaduj do
W Excelu
otworzy nam się okno Importowania danych, gdzie ustawiamy sposób wyświetlania
danych jako Tabela, a następnie wskazujemy miejsce wstawienia danych, czyli
istniejący arkusz i wskazujemy konkretną
komórkę. Tak ustawione parametry importowania danych zatwierdzamy przyciskiem
OK (rys. nr 26).
Rys. nr 26 – parametry importowania danych do Excela
Otrzymamy
dane wstawione do Excela przedstawione na rys. nr 27.
Rys. nr 27 – dane zaimportowane z Power Query
Musimy
pamiętać o odpowiednim formatowaniu danych, ponieważ w kolumnie Data
otrzymaliśmy liczby zamiast dat. Zaznaczamy kolumnę Data i na karcie Narzędzia
główne możemy zmienić formatowanie w kategorii Liczba na Data krótka
(rys. nr 28).
Rys. nr 28 – zmiana formatowania na Datę krótką
Otrzymamy
końcowe dane, odpowiednio sformatowane a przede wszystkim z numeracją
poszczególnych elementów danych kategorii (rys. nr 29).
Rys. nr 29 – Dane z numeracją elementów poszczególnych kategorii
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ę wyznaczaniem numeru tygodnia w roku i miesiącu oraz początku i końca tygodnia. W poradach od 341 do 344 omawialiśmy wyznaczanie tych danych w Excelu za pomocą funkcji .
Poznamy możliwości Power Query w tym zakresie. Temat ten
omówimy na podstawie przykładowych danych przedstawionych na rys. nr 1.
rys. nr 1 — Przykładowe dane
Mamy tutaj przedstawione dane zawierające zbiór dat i
informację jaki to jest dzień tygodnia. Aby zaczytać dane do Power Query
wybieramy polecenie Z tabeli z karty Dane (rys. nr 2).
rys. nr 2 — Z tabeli
Otworzy nam się Edytor zapytań Power Query z wczytaną tabelą.
Klikamy ikonkę kalendarza przy nazwie kolumny Data aby zmienić formatowanie. Z
podręcznego menu wybieramy typ danych – Data (rys. nr 3). Wyświetli nam się
komunikat o Zmianie typu danych, w którym za pomocą przycisku Zamień bieżącą
zatwierdzamy zmianę.
rys. nr 3 — Zmiana typu danych
Otrzymamy dane przedstawione na rysunku nr 4.
rys. nr 4 — Dane w edytorze zapytań
Chcemy dodać nową kolumnę z danymi zawierającymi numer
tygodnia więc korzystamy z poleceń z karty Dodaj kolumnę. Gdybyśmy natomiast
chcieli przekształcić nasze dane musimy skorzystać z analogicznych poleceń na
karcie Przekształć (rys. nr 5).
rys. nr 5 — Przekształcenie danych
Naszym zadaniem jest dodanie nowych kolumn i interesującymi
nas danymi. Zaznaczamy kolumnę Data a następnie rozwijamy polecenie Data (punkt
2 na rys. nr 6) z karty Dodaj kolumnę,
następnie rozwijamy polecenie Tydzień (punkt 3) i wybieramy interesującą nas
informację, czyli w naszym przykładzie Tydzień roku (punkt 4).
rys. nr 6 — Dodawanie nowej kolumny Tydzień roku
Otrzymamy dane z nową kolumną zawierającą numer tygodnia w
roku odpowiadającego naszej dacie (rys. nr 7).
rys. nr 7 — Tydzień roku
Istotną informacją tutaj jest, że Power Query działa na
systemie pierwszym funkcji NUM.TYGODNIA Excela, czyli data 1 stycznia każdego
roku to pierwszy dzień pierwszego tygodnia nowego roku. W Power Query możemy
jedynie skorygować od jakiego dnia rozpoczyna się nowy tydzień. W Polsce
standardowo nowy tydzień zaczyna się w poniedziałek i takie właśnie jest domyślne
działanie funkcji Date.WeekOfYear w Power Query. Możemy to zmienić, wystarczy
skopiować nazwę funkcji a następnie nacisnąć przycisk fx obok paska formuły,
aby dodać nowy krok i wkleić w pasek formuły zapis =Date.WeekOfYear. Kiedy
zatwierdzimy taką formułę klawiszem Enter otrzymamy informacje na temat tej
funkcji, między innymi jej parametrów oraz jakie zwraca wartości. Możemy sobie
tutaj w polu firstDayOfWeek wybrać dzień jaki chcemy przyjąć za początek
tygodnia (rys. nr 8).
rys. nr 8 — funkcja Date.WeekOfYear
Usuwamy ten krok z Zastosowanych kroków, ponieważ
wprowadziliśmy go tylko w celu pokazania możliwości zmiany dnia początku
tygodnia. Jeśli chcemy wprowadzić zmianę dotyczącą dnia początku tygodnia
możemy wpisać w formułę dodatkowy parametr zawierający nazwę dnia, jaki chcemy
przyjąć za początek tygodnia. Załóżmy, że chcemy aby tydzień rozpoczynał się we
wtorek, więc w formułę wpiszemy Day.Tuesday (rys. nr 9). Formuła powinna
wyglądać następująco:
=Table.AddColumn(#"Zmieniono typ", "Tydzień
roku" each Date.WeekOfYear([Data], Day.Tuesday), Int64.Type)
rys. nr 9 — Formuła funkcji Date.WeekOfYear
Otrzymamy zmienione dane, w których każdy nowy tydzień będzie
rozpoczynał się we wtorek (rys. nr 10).
rys. nr 10 — Zmienione dane
Zamiast Day.Tuesday możemy wpisać wartość 2 w formule.
Zadziała ona prawidłowo. Zapis formuły powinien wtedy wyglądać następująco:
=Table.AddColumn(#"Zmieniono typ", "Tydzień
roku" each Date.WeekOfYear([Data], 2), Int64.Type)
W Power Query dni są numerowane od niedzieli do soboty.
Niedzieli odpowiada wartość 0, poniedziałek to wartość 1 itd., natomiast
sobocie przypisana jest wartość 6.
Wszystkie kolejne funkcje, z których tutaj będziemy korzystać
mają ten dodatkowy argument, w którym możemy przyjąć jaki dzień chcemy
traktować jako początek tygodnia.
W kolejnym przykładzie wyznaczymy sobie numer tygodnia w
miesiącu. Zaznaczamy kolumnę Data a następnie rozwijamy polecenie Data (punkt 2
na rys. nr 11) z karty Dodaj kolumnę,
następnie rozwijamy polecenie Tydzień (punkt 3) i wybieramy interesującą nas
informację, czyli w naszym przykładzie Tydzień miesiąca (punkt 4).
rys. nr 11 — Tydzień miesiąca
Otrzymamy nową kolumnę z numerem tygodnia w miesiącu (rys. nr 12).
rys. nr 12 — Numer tygodnia w miesiącu
Dzień uznawany za początek tygodnia wstawił się domyślnie
(poniedziałek). Jeśli chcemy zmienić dzień początku tygodnia musimy to zrobić
analogicznie jak w poprzednim przykładzie, czyli dopisać ten argument w formułę
funkcji na pasku formuły (rys. nr 13). Zakładamy, że chcemy aby tydzień
rozpoczynał się w piątek, czyli musimy wpisać argument Day.Friday lub wartość
5. Zapis powinien wyglądać następująco:
=Table.AddColumn(#"Wstawiono Tydzień roku",
"Tydzień miesiąca", each Date.WeekOfMonth([Data],5) Int64.Type)
rys. nr 13 — Funkcja Date.WeekOfMonth
Gdybyśmy nie mieli widocznego paska formuły, musimy go
uruchomić w karcie Widok (rys. nr 14).
rys. nr 14 — Uruchomienie paska formuły
Otrzymamy dane przedstawiające numerację rozpoczynającą nowy
tydzień w piątek (rys. nr 15).
rys. nr 15 — Dane przedstawiające numerację rozpoczynającą nowy tydzień w piątek
W kolejnym przykładzie dodamy kolumnę zawierającą Początek
tygodnia. Zaznaczamy kolumnę Data a następnie rozwijamy polecenie Data (punkt 2
na rys. nr 16) z karty Dodaj kolumnę,
następnie rozwijamy polecenie Tydzień (punkt 3) i wybieramy interesującą nas
informację, czyli w naszym przykładzie Początek tygodnia (punkt 4).
rys. nr 16 — Początek tygodnia
Dodamy również kolumnę zawierającą Koniec tygodnia w
analogiczny sposób jak na rysunku nr 16. Otrzymamy wtedy dwie nowe kolumny
przedstawione na rysunku nr 17.
rys. nr 17 — Dane z początkiem i końcem tygodnia
Zostało tu zastosowana numeracja domyślna, czyli tydzień
rozpoczyna się w poniedziałek, a kończy w niedzielę.
Użyliśmy w tych przykładach dwóch funkcji Date.StartOfWeek
oraz Date.EndOfWeek.
Istotną informacją dla działań w Power Query jest to, że data
1 stycznia zawsze rozpoczyna pierwszy tydzień nowego roku. Możemy jedynie
zmieniać dni początku i końca tygodnia. Nie ma możliwości zrobić kontynuacji
numeru tygodnia z poprzedniego roku w nowym roku, tak żeby tydzień zawsze miał
7 dni, jak to było możliwe w Excelu.
Tak przygotowane dane możemy załadować do Excela za pomocą
polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 18).
rys. nr 18 — Zamknij i załaduj do
Otworzy nam się okno Ładowania do, gdzie ustawiamy sposób
wyświetlania danych jako Tabela oraz wybieramy miejsce wstawienia danych –
Istniejący arkusz i wskazujemy konkretną komórkę (rys. nr 19). Powyższe
parametry zatwierdzamy przyciskiem Załaduj.
rys. nr 19 — Okno ładowania do
Otrzymamy dane wczytane do Excela przedstawione na rysunku nr 20.
rys. nr 20 — Dane zaczytane do Excela
Możemy sobie jeszcze odpowiednio sformatować dane korzystając
ze skrótu klawiszowego Ctrl+1, aby przy tych datach wyświetlała się nazwa dnia
tygodnia. Otworzy nam się okno formatowania komórek, gdzie wybieramy
formatowanie Niestandardowe w karcie Liczby, następnie w polu Typ musimy
dopisać zapis ddd (rys. nr 21).
rys. 21 — Formatowanie komórek
Otrzymamy sformatowane dane przedstawione na rysunku nr 22.
rys. nr 21 — Sformatowane dane
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
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ę kontynuacją poprzedniego, a
dokładnie drugim sposobem rozwiązania zadania polegającego na dynamicznym
określeniu ilości kolumn przy podziale ogranicznikiem. Rozwiązanie to jest
wykorzystywane przez Billa Szysza, jest ono przeznaczone dla bardziej
zaawansowanych użytkowników, ponieważ wymaga większej znajomości funkcji i kodu
M w Power Query. Zadanie to wykonamy po podstawie przykładowych danych
przedstawionych na rysunku nr 1.
rys. nr 1 — Przykładowe dane
Pierwszym krokiem jest zaczytanie danych wejściowych do Power
Query. W tym celu klikamy polecenie Z tabeli/zakresu z karty Dane (rys. nr 2).
rys. 2 — Z tabeli/ zakresu
Otworzy nam się edytor zapytań Power Query z wczytaną tabelą
z danymi przedstawioną na rysunku nr 3.
rys. 3 — Edytor zapytań
Z zastosowanych kroków usuwamy niepotrzebny krok Zmieniono
typ. Do dalszych działań potrzebujemy tylko kolumny z produktami więc klikamy
prawym przyciskiem myszy na nagłówek kolumny Produkt i z podręcznego menu
wybieramy polecenie Usuń inne kolumny (rys. nr 4).
rys. 4 — Usuń inne kolumny
Otrzymamy interesujące nas dane przedstawione na rysunku nr 5.
rys.5 — Dane po usunięciu innych kolumn
Do tego momentu kroki w działaniu były takie same jak w
poprzednim poście https://exceliadam.pl/?p=9720
. Aby ułatwić zrozumienie tematu nie zaczniemy pisać od razu funkcji w pasku
formuły tylko dodamy kolumnę niestandardową z karty Dodaj kolumnę (rys. nr 6).
Dodamy tą kolumnę, żeby to w niej zacząć pisać funkcję.
rys. 6 — Kolumna niestandardowa
Otworzy nam się okno Kolumny niestandardowej, gdzie w polu
Formuły kolumny niestandardowej wpiszemy funkcję. Zapis powinien wyglądać
następująco:
=Text.Split([Produkt], ", ")
W funkcji Text.Split argumentem jest kolumna Produkt. Następnie wpisujemy ogranicznik, czyli w naszym przypadku — przecinek spacja ", ". Po wpisaniu formuły zatwierdzamy naszą kolumnę niestandardową przyciskiem OK (rys. nr 7).
rys. 7 — Parametry kolumny niestandardowej
Otrzymamy dane przedstawione na rysunku nr 8.
rys. 8 — Kolumna niestandardowa w postaci list
Polecenie to dzieli nasze kolumny na Listy. Pod każdym słowem
List jest ukryta lista produktów (przykład przedstawiony został na rysunku nr 9).
rys. 9 — Dane ukryte pod nazwą List
Kolejnym krokiem jest policzenie elementów każdej z tych
list. Użyjemy do tego funkcji List.Count. klikamy na nazwę kroku Dodano kolumnę
niestandardową, aby otworzyć znowu okno Kolumny niestandardowej i wprowadzić
zmiany w formule tej kolumny (rys. nr 10). Zapis powinien wyglądać następująco:
=List.Count(Text.Split([Produkt],
", "))
rys. 10 — zmiany wprowadzone w oknie kolumny niestandardowej
Zatwierdzamy zmienioną formułę przyciskiem OK i otrzymujemy
dane przedstawione na rysunku nr 11.
rys. 11 — zmieniona kolumna niestandardowa
Podsumowując najpierw podzieliliśmy dane na Poszczególne
listy za pomocą funkcji Text.Split a następnie policzyliśmy ilość elementów w
każdej liście za pomocą funkcji List.Count. W pasku formuły możemy wprowadzić
zmiany, aby uzyskać interesujący nas wynik. Zamieniamy funkcję Table.AddColumn
na List.Transform oraz dodajemy argument Produkt. Formuła powinna wyglądać jak
na rysunku nr 12.
=List.Transform(#"Usunięto inne kolumny" [Produkt],
each List.Count(Text.Split(_,", ")))
rys. 12 — Formuła funkcji List.Transform
Po zatwierdzeniu formuły otrzymamy dane przedstawione na
rysunku nr 13.
rys. 13 — Wynik funkcji List.Transform
Otrzymaliśmy listę wszystkich elementów na konkretnych
listach. Za pomocą funkcji List.Transform wyciągnęliśmy z danych tylko jedną
kolumnę. Powinniśmy pamiętać, że nie potrzebujemy listy wszystkich elementów
tylko maksymalną wartość. Wystarczy wstawić w formułę na pasku formuł funkcję
List.Max. Formuła powinna wyglądać jak na rysunku nr 14.
=List.Max(List.Transform(#"Usunięto inne kolumny"
[Produkt], each List.Count(Text.Split(_,", "))))
rys. 14 — Zapis funkcji List.Max
Kiedy zatwierdzimy formułę klawiszem Enter otrzymamy wynik
przedstawiony na rysunku nr 15.
rys. 15 Wynik maksymalny
Kolejny krok pokrywa się z krokiem z wcześniejszego filmu,
czyli Power Query odcinek 36.
Kopiujemy nazwę kroku Usunięto inne kolumny po zaznaczeniu go
i wciśnięciu klawisza F2, za pomocą skrótu klawiszowego Ctrl+C. Klikamy
przycisk fx przy pasku formuły i wklejamy skopiowaną nazwę kroku za pomocą
skrótu klawiszowego Ctrl+V. Otrzymamy dane przedstawione na rysunku nr 16.
rys. 16 Dane z kroku Usunięto inne kolumny
Rozwijamy polecenie Podziel kolumny (punkt nr 2 na rysunku nr
17) z karty Narzędzia główne, a następnie wybieramy polecenie Według
ogranicznika (punkt nr 3 na rysunku nr 17).
rys. 17 — Podziel kolumny według ogranicznika
Otworzy nam się okno Dzielenia kolumny według ogranicznika,
gdzie w polu wybierz lub wprowadź ogranicznik wybieramy opcję Niestandardowe
(punkt nr 1 na rysunku nr 18). Następnie w polu, które się pojawi wpisujemy
nasz ogranicznik, czyli przecinek i spacja. Zaznaczamy Podziel przy każdym
wystąpieniu ogranicznika (punkt nr 2), Podziel na Kolumny (punkt nr 3) i
wpisujemy liczbę kolumn 2 (wpisujemy mniejszy parametr bo będziemy go
zmieniać). Tak ustawione parametry dzielenia kolumny zatwierdzamy przyciskiem
OK.
rys. 18 — parametry dzielenia kolumny
Otrzymamy dane przedstawione na rysunku nr 19. Usuwamy
niepotrzebny krok o nazwie Zmieniono typ.
rys. 19 — Podzielone dane
W
pasku formuły w tym zapytaniu zmieniamy nazwy poszczególnych kolumn
(podświetlone na rysunku nr 20) na odwołanie do wcześniejszego kroku
rys.20 zmiana nazw kolumn na odwołanie do poprzedniego kroku w pasku formuły
Klikamy nazwę wcześniejszego kroku — Dodano kolumnę
niestandardową, wciskamy klawisz F2, a następnie kopiujemy nazwę kroku za
pomocą skrótu klawiszowego Ctrl+C. Następnie tworzymy odwołanie do tego kroku,
czyli w formule w miejscu zaznaczonym na rysunku nr 20 wklejamy nazwę naszego
kroku poprzedzoną znakiem # (hash) i w podwójnym cudzysłowie. Zapis powinien
wyglądać jak na rysunku nr 21.
rys. 21 — Zapis formuły
Po zatwierdzeniu formuły otrzymamy dane przedstawione na
rysunku nr 22.
rys. 22 — Dane uzyskane z działania funkcji Table.TransformColumn
Otrzymaliśmy jedenaście kolumn dynamicznych, czyli
zmieniających się pod wpływem zmian w danych wejściowych.
Jeśli chcemy mieć mniej kroków w działaniach możemy usunąć
krok Niestandardowe1, do którego się odwołaliśmy w formule. Wystarczy, że w
pasku formuły z rysunku nr 21 zastąpimy nazwę kroku Niestandardowe1 na Usunięto
inne kolumny, ponieważ oba kroki zawierają takie same dane. Zapis formuły po
zastąpieniu nazw powinien wyglądać jak na rysunku nr 23.
rys. 23 zmiana nazw kroków w pasku formuły
Następnie możemy usunąć już zbędny krok Niestandardowe1.
Klikamy na nazwę kroku i wciskamy przycisk Delete. Pojawi nam się komunikat
Usuwanie kroku, gdzie potwierdzamy usunięcie go za pomocą przycisku Usuń (rys.
nr 24).
rys. 24 — Usuwanie kroku
Tak przygotowane dane możemy załadować do Excela korzystając
z polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 25).
rys. 25 — Zamknij i załaduj do
Otworzy nam się okno Importowania danych, gdzie wybieramy
sposób wyświetlania danych jako Tabela i wskazujemy miejsce ich wstawienia,
czyli Istniejący arkusz oraz wskazujemy konkretną komórkę. Tak określone
parametry zatwierdzamy przyciskiem OK (rys. nr 26)
rys. 26 — Okno importowania danych
Otrzymamy dane zaimportowane do Excela przedstawione na
rysunku nr 27.
rys. 27 — Dane zaimportowane do Excela
Możemy w danych źródłowych dopisać dodatkowy wiersz z danymi,
aby sprawdzić działanie dynamicznych kolumn. Po odświeżeniu danych w tabeli z
Power Query otrzymamy aktualne dane. Tabelę odświeżamy klikając prawym
przyciskiem myszy na jej obszar a następnie korzystając z polecenia Odśwież z
podręcznego menu.
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 sobie wyznaczenie ceny produktu na podstawie daty zamówienia. Zadanie to w Power Query rozwiązaliśmy już w poprzednim poście nr 33 https://exceliadam.pl/?p=9575 , za pomocą poleceń ze wstążki. W tym poście rozwiążemy ten problem przy użyciu funkcji Table.SelectRows. Temat ten omówimy na podstawie przykładowych danych przedstawionych na rysunku nr 1.
rys. nr 1 — Przykładowe dane
Zadanie polega na znalezieniu ceny danego produktu na
podstawie daty zamówienia. Mamy podane również daty, kiedy cena ta ulegała
zmianie.
Zakładamy, że dwie tabelki z rysunku nr 1 mamy zaczytane do
Power Query z rozwiązania przedstawionego w poprzednim poście. Aby mieć podgląd
zapytań wybieramy polecenie Pokaż zapytania z karty Dane (rys. nr 2).
rys. nr 2 — pokaż zapytania
Następnie klikamy dwa razy myszką na nazwę naszego zapytania
w oknie Zapytań dotyczących skoroszytu, aby przejść do Power Query (rys. nr 3).
rys. nr 3 — Lista zapytań dotyczących skoroszytu
Otworzy nam się edytor zapytań z wczytaną tabelą tCeny oraz
tZamówienia (oznaczone zielonym prostokątem na rysunku nr 4). Naszym zadaniem
jest scalenie tych zapytań. Rozwijamy polecenie Połącz (punkt nr 2 na rysunku
nr 4) z karty Narzędzia główne, następnie rozwijamy polecenie Scal zapytania
(punkt nr 3) i wybieramy polecenie Scal zapytania jako nowe (punkt nr 4).
rys. nr 4 — Scal zapytania jako nowe
Otworzy nam się okno Scalanie, w którym musimy określić jego
parametry. W obu zapytaniach zaznaczamy kolumnę Produkt, ponieważ po tym
elemencie chcemy scalić zapytania. Wybieramy rodzaj sprzężenia jako Lewe
zewnętrzne, czyli wszystkie elementy z pierwszego zapytania i pasujące z
drugiego. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 5).
rys. nr 5 — Parametry scalania
Otrzymamy scalone dane przedstawione na rysunku nr 6.
rys. nr 6 — Scalone dane
W kolejnym etapie zmieniamy nazwę kroku na Scalone2 we
właściwościach w Ustawieniach zapytania. Pierwszy krok w Power Query jest
identyczny z rozwiązaniem przedstawionym w poprzednim poście. W danych tych
mamy już ukryte interesujące nas informacje, czyli pod słowem Table kryje się
tabela z datami zmiany ceny produktu (rys. nr7).
rys. nr 7 — Dane ukryte pod Table
Dane w tabelce poniżej musimy odfiltrować, czyli wyrzucić z
niej te daty które nie pasują do daty zamówienia. Dla przypomnienia – nasze
dane posortowaliśmy wcześniej po produkcie i dacie zmiany ceny, dzięki temu
możemy teraz relatywnie prosto znaleźć rozwiązanie. Z karty Dodaj kolumnę
wybieramy polecenie Kolumna niestandardowa (rys. nr 8).
rys. nr 8 — Kolumna niestandardowa
Otworzy nam się okno kolumny niestandardowej, gdzie zmieniamy
nazwę nowej kolumny na REC (jak Bill Szysz w swoim rozwiązaniu). Następnie w
polu Formuła kolumny niestandardowej wpisujemy formułę funkcji
Table.SelectRows. Zapis jej powinien wyglądać następująco:
=Table.SelectRows([tCeny] each
[Data zmiany ceny] <= #date (2018,2,1))
Funkcja ta pozwoli nam wyciągnąć konkretne wiersze z kolumny
tCeny pod nałożonym warunkiem, czyli dla każdego wiersza gdzie Data zmiany ceny
jest mniejsza bądź równa dacie zamówienia. Wpiszemy w formule zamiast Daty
zamówienia znak # ponieważ na razie nie możemy sięgnąć po datę z tej kolumny.
Tak ustawione parametry zatwierdzamy klikając przycisk OK (rys. nr 9).
rys. nr 9 — Parametry kolumny niestandardowej
Otrzymamy dane przedstawione na rysunku nr 10.
rys. nr 10 — Dane
Formuła ta ogranicza nam wyniki lecz nie we właściwy sposób.
My chcemy odwoływać się nie do konkretnej podanej daty a do innej daty dla
każdego wiersza, czyli do daty zamówienia każdego produktu. Nie możemy ręcznie
wpisać w formule odwołania do kolumny Data zamówienia ponieważ funkcja zwróci
nam błąd (nie może znaleźć pola). Rys. nr 11.
rys. nr 11 — Error
Kiedy wpiszemy nazwę funkcji w pasku formuły Power Query
wyświetli nam informacje o niej. Możemy wpisywać w niej albo wartości stałe
albo wartości z wierszy z tej tabeli. Nie możemy użyć kolumny Data zamówienia
ponieważ nie zawiera się w tabelach ukrytych pod Table w kolumnie tCena (rys.
nr 12).
rys. nr 12
Bill Szysz podpowiedział mi rozwiązanie tego problemu. Możemy
sobie wkleić funkcję (x) w funkcję Table.SelectRows. Funkcja ta to tak naprawdę
cały wiersz naszych danych. Zapis powinien wyglądać następująco:
=Table.AddColumn (Źródło, "REC", (x) =>
Table.SelectRows (x[tCeny], each [Data zmiany ceny] <= x[Data zamówienia]))
Otrzymamy dane przedstawione na rysunku nr 13, gdzie nadal
czasem w wynikach mamy dwa wiersze.
rys.nr 13 — dane
Aby uzyskać pojedyncze rozwiązanie musimy wstawić kolejną
funkcję. Pamiętamy, że nasze dane posortowaliśmy według Produktu i Daty zmiany
ceny więc wynikiem będzie ostatni wiersz.
rys. nr 14 — Wynik prawidłowy
Zapis funkcji będzie wyglądał następująco:
= Table.AddColumn (Źródło, "REC", (x) =>
Table.Last( Table.SelectRows (x[tCeny], each [Data zmiany ceny] <= x[Data
zamówienia])))
Otrzymamy tym razem pojedyncze wyniki dla każdego wiersza
przedstawione na rysunku nr 15.
rys. nr 15 pojedyncze wyniki dla każdego wiersza
Aby wyciągnąć dane, które nas interesują, czyli cenę, klikamy
na ikonkę ze strzałkami w nazwie kolumny REC i zaznaczamy checkbox przy
wartości Cena i odznaczamy checkbox przy poleceniu Użyj oryginalnej nazwy
kolumny jako prefiksu. Tak ustawione parametry zatwierdzamy klikając przycisk
OK (rys. nr 16).
rys. nr 16 — Odfiltrowanie danych
Otrzymamy
dane przedstawione na rysunku nr 17.
rys. nr 17 — Odfiltrowane dane
Na tym etapie możemy już usunąć kolumnę tCeny.
Aby uczyć się formuł w Power Query możemy usunąć krok Rozwinięty element REC z Zastosowanych kroków i wprowadzić kolejny element do formuły naszej funkcji. Poprzednia formuła zwróciła nam Rekord, czyli jeden cały wiersz. Kiedy dopiszemy w formule [Cena], otrzymamy już interesujący nas wynik (rys. nr 18). Zapis formuły powinien wyglądać następująco:
= Table.AddColumn (Źródło, "REC", (x) => Table.Last( Table.SelectRows (x[tCeny], each [Data zmiany ceny] <= x[Data zamówienia])) [Cena])
rys. nr 17 — Aktualne dane
Klikamy prawym przyciskiem myszy na nazwę kolumny tCeny i za
pomocą polecenia Usuń z podręcznego menu usuwamy niepotrzebną kolumnę (rys. nr 19).
rys. nr 19 — Usuń kolumnę
Możemy jeszcze w zapisie formuły zmienić nazwę kolumny REC na
Cena. Otrzymamy dane przedstawione na rysunku nr 20.
rys. nr 20 — Dane
Podsumowując dziś omówiony sposób może wydawać się
trudniejszy, ale gdy się nauczymy funkcji i formuł Power Query rozwiązywanie
tego typu problemów zajmie nam chwilę. Skomplikowane w tym rozwiązaniu jest to,
że łączymy pracę na tabelach wewnętrznych (ukrytych pod wartościami Table) oraz
na tabelach danych z zapytań (rys. nr 21).
rys. nr 21 — Dane ukryte pod Table
Tak przygotowane dane możemy zaczytać do Excela za pomocą polecenia Zamknij i załaduj do z karty
Narzędzia główne (rys. nr 22).
rys. nr 22 — Zamknij i załaduj do
Otworzy nam się okno Ładowania do, gdzie ustawiamy sposób
wyświetlania danych jako Tabela oraz miejsce wstawienia danych – istniejący
arkusz i wskazujemy konkretną komórkę (rys. nr 23).
rys. nr 23 — Okno ładowanie do
Otrzymamy dane wczytane do Excela przedstawione na rysunku nr 24.
rys. nr 24 — Dane wczytane do Excela
Pierwsze rozwiązanie przedstawione w poprzednim poście jest czasochłonne
i zawiera wiele kroków, ale wszystkie kroki możemy wykonać z interfejsu
użytkownika. Drugie rozwiązanie jest szybsze ale wymaga dobrej znajomości
języka M i funkcji Power Query.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
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.
