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.
Pierwszym krokiem jest zaczytanie danych do Power Query, w tym celu wybieramy polecenie Z tabeli z karty Dane (rys. nr 2).
Otworzy nam się edytor zapytań z zaczytaną tabelą z danymi produkcyjnymi (rys. nr 3).
Zaznaczamy kolumnę Produkt ID a następnie wybieramy polecenie Grupowanie według z karty Narzędzia główne (rys. nr 4).
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).
Otrzymamy pogrupowane dane dla każdego ID produktu z maksymalnym czasem trwania operacji przedstawione na rysunku nr 6.
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).
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).
Otrzymujemy dane przedstawione na rysunku nr 9, gdzie pod słowem Table ukryta jest tabela z danymi (wynik porównania dwóch tabel).
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.
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).
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.
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).
Otrzymalibyśmy wtedy dane przedstawione na rysunku nr 14.
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).
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.
Otrzymamy dane przedstawione na rysunku nr 17. Dla pierwszego wiersza pod List ukryte są dwie wartości – dwie nazwy operacji.
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).
Następnie klikamy ikonkę strzałek przy nazwie kolumny Opis operacji i wybieramy polecenie Wyodrębnij wartości (rys. nr 19).
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).
Otrzymamy wyodrębnione dane przedstawione na rysunku nr 21.
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).
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).
Otrzymamy dane zaczytane do Excela przedstawione na rysunku nr 24.
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
