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.
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).
Otworzy nam się edytor zapytań Power Query z wczytaną tabelą z Excela przedstawiony na rys. nr 3.
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).
Otworzy nam się okno Zmiany typu kolumny, gdzie musimy zatwierdzić naszą decyzję za pomocą przycisku Zamień bieżącą (rys. nr 5).
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 Kolumna Indeksu (punkt 2 na rys. nr 6) z karty Dodaj kolumnę (punkt 1), a następnie wybieramy polecenie Od 0 (punkt 3).
Otrzymamy dane z nową kolumną (kolumna indeksu), która sprawi, że nasze dane zachowają swoją kolejność (rys. nr 7).
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).
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.
W następnym kroku musimy pogrupować dane po kategorii, czyli wybieramy polecenie Grupowanie według z karty Narzędzia główne (rys. nr 10).
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.
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.
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.
Otrzymamy dane z nową kolumną przedstawione na rys. nr 14.
Dzięki dodaniu tej kolumny w pasku formuły mamy gotowy kod na dodanie kolumny indeksu (rys. nr 15).
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).
Następnie wybieramy polecenie Kolumna niestandardowa z karty Dodaj kolumnę (rys. nr 17).
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).
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).
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).
Otrzymamy rozwinięte dane przedstawione na rys. nr 21.
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).
Otrzymamy dane posortowane według pierwotnej kolejności przedstawione na rys. nr 23.
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.
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).
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).
Otrzymamy dane wstawione do Excela przedstawione na rys. nr 27.
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).
Otrzymamy końcowe dane, odpowiednio sformatowane a przede wszystkim z numeracją poszczególnych elementów danych kategorii (rys. nr 29).
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY
Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY
Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY
Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY
VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY
Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY
