W dzisiejszym poście zajmiemy się wyszukiwaniem najdłuższego czasu operacji oraz opisu tej konkretnej operacji. Zakładamy, że mamy linię produkcyjną, na której są wykonywane pewne czynności. Naszym zadaniem jest znalezienie operacji, która zajmuje najwięcej czasu, a następnie uzyskanie opisu nazwy tej operacji. Dodatkowym warunkiem tego zadania jest opcja, że najdłuższy czas operacji może się powtórzyć – uzyskamy wtedy np. dwie operacje w jednym wierszu. Zadanie to wykonamy w Power Query na podstawie przykładowych danych przedstawionych na rysunku nr 1.

rys. nr 1 — Przykładowe dane

Pierwszym krokiem jest zaczytanie danych do Power Query, w tym celu wybieramy polecenie Z tabeli z karty Dane (rys. nr 2).

rys. nr 2 — Z tabeli

Otworzy nam się edytor zapytań z zaczytaną tabelą z danymi produkcyjnymi (rys. nr 3).

rys. nr 3 — Edytor zapytań

Zaznaczamy kolumnę Produkt ID a następnie wybieramy polecenie Grupowanie według z karty Narzędzia główne (rys. nr 4).

rys. nr 4 — Grupowanie według

Otworzy nam się okno Grupowania według, gdzie możemy ustalić parametry grupowania. Dane grupujemy według kolumny Produkt ID. W polu nazwa nowej kolumny wpisujemy nazwę Najdłuższy czas, a następnie w polu Operacja wybieramy Maksimum. W polu Kolumna wybieramy kolumnę Czas bo na tej kolumnie mają się opierać nasze wyniki. Tak przygotowane parametry grupowania zatwierdzamy przyciskiem OK (rys. nr 5).

rys. nr 5 — Parametry grupowania

Otrzymamy pogrupowane dane dla każdego ID produktu z maksymalnym czasem trwania operacji przedstawione na rysunku nr 6.

rys. nr 6 — Pogrupowane dane

Otrzymaliśmy kolumnę z numerem ID produktów i najdłuższy czas trwania operacji. Potrzebujemy teraz wyciągnąć informację z nazwą tych operacji. Z założenia opis ten pokazany jest w danych krok wcześniej w zastosowanych krokach. Możemy scalić zapytanie z tym samym zapytaniem lecz z innym krokiem. Rozwijamy polecenie Połącz (punkt 2 na rysunku nr 7) z karty Narzędzia główne, a następnie wybieramy polecenie Scal zapytania (punkt 3).

rys. nr 7 — Scal zapytania

Otworzy nam się okno Scalania danych. Dla tabeli tProdukcja (2) na górze okna zaznaczamy obie kolumny, czyli  Produkt ID oraz Najdłuższy czas. W drugim polu również wybieramy tabelę tProdukcja (2) (bieżący), a następnie w tej samej kolejności co powyżej zaznaczamy obie kolumny z danymi. W polu Rodzaj sprzężenia wybieramy rodzaj operacji, czyli Lewe zewnętrzne (wszystkie z pierwszej, pasujące z drugiej). Tak przygotowane parametry zatwierdzamy przyciskiem OK (rys. nr 8).

rys. nr 8 — Parametry scalania

Otrzymujemy dane przedstawione na rysunku nr 9, gdzie pod słowem Table ukryta jest tabela z danymi (wynik porównania dwóch tabel).

rys. nr 9 — Scalone dane

Tabelki te zawierają tylko jeden wiersz ponieważ grupowanie wykonaliśmy po ostatnim kroku zapytania, czyli Pogrupowano wiersze. Naszym zadaniem jest scalenie danych z kroków Pogrupowano wiersze i Zmieniono typ. Klikamy na nazwę kroku Zmieniono typ, następnie przechodzimy w tryb edycji tekstu za pomocą klawisza F2 i kopiujemy nazwę tego kroku za pomocą skrótu klawiszowego Ctrl+C.

Sprawdzamy czy mamy widoczny pasek formuły. Jeśli nie to możemy go włączyć na karcie Widok klikając checkbox przy poleceniu Pasek formuły.

Będziemy teraz wprowadzać zmiany zapisie naszej formuły scalania w pasku formuły. Pierwszy krok się zgadza, czyli Pogrupowano wiersze. Drugi krok jest taki sam, więc musimy zaznaczyć nazwę kroku (zaznaczone na niebiesko na rysunku nr 10), a następnie wkleić nazwę skopiowanego wcześniej zapytania (Zmieniono typ) za pomocą skrótu klawiszowego Ctrl+V.

rys. nr 10 — Zmiany w formule

Jeśli teraz zatwierdzimy naszą zmianę klawiszem Enter Power Query zwróci nam błąd ponieważ w kroku Zmieniono typ nie było kolumny Najdłuższy czas (rys. nr 11).

rys. nr 11 — Error

W kroku zmieniono typ mamy natomiast kolumnę Czas [s], której możemy użyć zamiennie. Wystarczy zmienić w zapisie formuły dla tego kroku nazwę Najdłuższy czas na Czas [s].

Zapis formuły powinien wyglądać następująco:

=Table.NestedJoin(#"Pogrupowano wiersze", {"ProduktID", "Najdłuższy czas"}, #"Zmieniono typ", {"ProduktID", "Czas [s]"}, "Pogrupowano wiersze", JoinKind.LeftOuter)

Otrzymamy dane przedstawione na rysunku nr 12. Mamy tutaj sytuację, że dwie operacje mają taki sam czas.

rys. nr 12 — dwie operacje z takim samym czasem

Gdybyśmy nie mieli duplikatów w danych, wystarczyło by teraz rozwinąć kolumnę Pogrupowano wiersze po kolumnie Opis operacji (jak pokazano na rys. nr 13).

rys. nr 13 — Rozwiń

Otrzymalibyśmy wtedy dane przedstawione na rysunku nr 14.

rys. nr 14 — Dane po rozwinięciu

Mamy tutaj dwa wiersze z tym samym czasem, a nam zależy aby były one w jednym wierszu. Usuwamy ostatni krok jaki wykonaliśmy, czyli wracamy do danych z rysunku nr 12.

W kolumnie Pogrupowano dane mamy dane w formie tabeli. Musimy dodać kolumnę niestandardową z karty Dodaj kolumnę (rys. nr 15).

rys. nr 15 — Kolumna niestandardowa

Otworzy nam się okno Kolumny niestandardowej. Zmieniamy nazwę nowej kolumny na Opis operacji, a następnie wpisujemy formułę tej kolumny. Będzie się ona opierać na funkcji Table.Column. Pierwszy argument funkcji to miejsce gdzie jest nasza tabela, czyli kolumna Pogrupowano wiersze. Drugi argument  to nazwa kolumny, którą chcemy wyciągnąć, czyli kolumna Opis operacji (rys. nr 16). Zapis będzie wyglądał następująco:

=Table.Column([Pogrupowano wiersze], "Opis operacji")

Powyższe parametry kolumny niestandardowej zatwierdzamy przyciskiem OK.

rys. nr 15 — Parametry kolumny niestandardowej

Otrzymamy dane przedstawione na rysunku nr 17. Dla pierwszego wiersza pod List ukryte są dwie wartości – dwie nazwy operacji.

rys. nr 17 — Dane z nową kolumną

W kolejnym kroku usuwamy kolumnę Pogrupowano wiersze. Klikamy na jej nagłówek prawym przyciskiem myszy, a następnie z podręcznego menu wybieramy polecenie Usuń (rys. nr 18).

rys. nr 18 — Usuń kolumnę

Następnie klikamy ikonkę strzałek przy nazwie kolumny Opis operacji i wybieramy polecenie Wyodrębnij wartości (rys. nr 19).

rys. nr 19 — Wyodrębnij wartości

Otworzy nam się okno wyodrębniania wartości z listy, gdzie w polu Wybierz ogranicznik, który zostanie użyty do połączenia wartości listy wybieramy Niestandardowe, a następnie w polu poniżej wpisujemy nasz ogranicznik, czyli przecinek i spacja. Powyższe parametry zatwierdzamy przyciskiem OK (rys. nr 20).

rys. nr 20 — Parametry wyodrębniania wartości

Otrzymamy wyodrębnione dane przedstawione na rysunku nr 21.

rys. nr 21 — Wyodrębnione dane

Pozostaje nam wczytać tak przygotowane dane do Excela. W tym celu korzystamy z polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 22).

rys. nr 22 — Zamknij i załaduj do

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

rys. nr 23 — Parametry ładowania danych

Otrzymamy dane zaczytane do Excela przedstawione na rysunku nr 24.

rys. nr 24 — Dane w Excelu

Możemy wprowadzać dowolne zmiany w tabeli z danymi wejściowymi, a następnie wystarczy kliknąć prawym przyciskiem myszy na tabelę z naszego zapytania i z podręcznego menu wybrać polecenie Odśwież, aby dane zostały zaktualizowane.


Książka Mistrz Excela + promo na 35 urodziny

Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.

Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".

Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.

Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.

W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.

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

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

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

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

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

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

Książka Mistrz Excela reklama