W dzisiejszym poście nauczymy się jak znaleźć cenę produktu dla konkretnej daty. Pomysł na ten odcinek zaczerpnąłem z Excel Magic Trick nr 1483, gdzie Mike Girvin rozwiązywał podobny problem w Excelu. W danych źródłowych mamy tabelkę  z cenami produktów oraz datami, kiedy te ceny ulegały zmianie (rys. nr 1).

rys. nr 1 — Przykładowe dane

Według danych bazowych produkt ABC dnia 2018-01-01 kosztował 10zł, następnie cena ta zmieniła się dnia 2018-03-01 na 12zł. Kolejna zmiana ceny miała miejsce 2018-07-15 kiedy spadła na 9zł. Naszym zadaniem jest znaleźć aktualną cenę dla konkretnej daty zamówienia (rys. nr 2). Mike Girvin pokazał różne metody rozwiązania tego problemu w Excelu.

rys. nr 2 — Dane z datą zamówienia

W tym poście pokażemy jak rozwiązać takie zadanie w Power Query za pomocą poleceń ze wstążki. W kolejnym poście (Power Query 34 https://exceliadam.pl/?p=9634 ) omówimy rozwiązanie bardziej eleganckie, o które zapytałem na forum Excela (ponieważ czułem że moje rozwiązanie jest zbyt skomplikowane). Rozwiązanie do kolejnego odcinka podpowiedział mi Bill Szysz – w jego pomyśle wystarczyły trzy kroki aby rozwiązać ten problem.

Przygotowałem odpowiednio tabelki, które wczytamy do Power Query. Pierwsza nazywa się tCeny, a druga tZamówienia. Nazwę tabel możemy sprawdzić w karcie Projektowanie, w grupie poleceń Właściwości (oznaczone zieloną strzałką na rysunku nr 3).

rys. nr 3 — Karta Projektowanie

Aby pobrać tabelę do Power Query zaznaczamy dowolną komórkę w tej tabeli a następnie wybieramy polecenie Z tabeli z karty Dane (rys. nr 4).

rys. nr 4 — Z tabeli

Otworzy nam się Edytor zapytań z wczytaną tabelą tCeny przedstawioną na rysunku nr 5.

rys. nr 5 — Edytor zapytań

Power Query błędnie zmienił typ danych w kolumnie Data zmiany ceny. Klikamy na ikonkę kalendarza przy nazwie tej kolumny i z podręcznego menu wybieramy polecenie Data (rys. nr 6).

rys. nr 6 — Format danych Data

Wyświetli nam się komunikat dotyczący zmiany typu kolumny, który musimy zatwierdzić przyciskiem Zamień bieżącą (rys. nr 7).

rys. nr 7 — Zamień bieżącą

W kolejnym kroku powinniśmy posortować nasze dane w kolumnie Produkt. W tym celu klikamy na ikonkę trójkąta obok nazwy kolumny i z podręcznego menu wybieramy polecenie Sortuj rosnąco (rys. nr 8).

rys. nr 8 — Sortuj rosnąco

Następnie analogicznie postępujemy dla drugiej kolumny (Data zmiany ceny), korzystając z polecenie Sortuj rosnąco. Ważnym jest, że w Power Query kolejność sortowania działa w odwrotnej kolejności niż w Excelu. Kolejność sortowania jest widoczna w tytułach kolumn (rys. nr 9). W naszym przykładzie dane zostaną najpierw posortowane po produkcie a później po dacie.

rys. nr 9 — Kolejność sortowania kolumn

Dla odmiany w Excelu przy takiej kolejności sortowania jak wyżej, ważniejsza było ostatnie sortowania, czyli najpierw dane zostały by posortowane po dacie a później po produkcie.

Otrzymamy dane przedstawione na rysunku nr 10.

rys. nr 10 — Posortowane dane

Drugą tabelę przygotowaliśmy wcześniej. Jej wczytywanie do Power Query odbywa się w sposób identyczny jak pierwszej. Mamy dwa zapytania tZamówienia i tCeny (rys. nr 11).

rys. nr 11 — Lista zapytań

W kolejnym kroku chcemy scalić tabelki z tych dwóch zapytań. Klikamy na zapytanie tZamówienia, potem rozwijamy polecenie Połącz (punkt nr 2 na rysunku nr 12) z karty Narzędzia główne i polecenie Scal zapytania (punkt nr 3), a następnie wybieramy polecenie Scal zapytania jako nowe (punkt nr 4).

rys. nr 12 — Scal zapytania jako nowe

Otworzy nam się okno Scalania, w którym wybieramy tabele i pasujące kolumny, po których chcemy utworzyć scaloną tabelę (punkt nr 1 i nr 2 na rysunku nr 13). Wybieramy również rodzaj sprzężenia (punkt nr 3) – Lewe zewnętrzne, czyli wszystkie z pierwszej tabeli i pasujące z drugiej tabeli). Wybrane parametry zatwierdzamy przyciskiem OK.

rys. nr 13 — Okno Scalania

W ustawieniach zapytania możemy zmienić nazwę scalonych danych na Scalone1 (rys. nr 14).

rys. nr 14 — Zmiana nazwy zapytania

Otrzymamy scalone dane przedstawione na rysunku nr 15.

rys. nr 15 — Scalone dane

W późniejszym etapie zrobimy kolejne scalanie – będzie to Scalone 2 więc możemy sobie od razu Zduplikować aktualne scalanie i zmienić mu nazwę na docelową. Klikamy prawym przyciskiem myszy na nazwę zapytania i z podręcznego menu wybieramy polecenie Duplikuj (rys. nr 16).

rys. nr 16 — Duplikuj zapytanie

W ustawieniach zapytania zmieniamy nazwę tego zapytania na Scalone2. W otrzymanych danych pod każdym skrótem Table kryje się tabelka z danymi(rys. nr 17).

rys. nr 17 — Table

W każdym wierszu z Table dla danego produktu, wyciągane są odpowiednie wiersze z tabeli tCena z tym samym produktem. Wartości te są skumulowane w małych tabelkach odpowiadających każdemu wierszowi.

Interesuje nas cena, która jest aktualna dla naszej daty zamówienia. Na przykład w pierwszym wierszu mamy 2018-02-26, data ta jest mniejsza od 2018-03-01, czyli obowiązuje dla tej konkretnej daty pierwsza cena (10 zł). Chcielibyśmy usunąć z tej tabelki pozostałe ceny (rys. nr 18).

rys. nr 18 — Właściwy wynik

Możemy to zrobić klikając ikonkę ze strzałkami w nazwie kolumny tCeny, a następnie rozwinąć naszą tabelkę. Potrzebujemy z tej tabelki Datę zmiany ceny i Ceny, nie potrzebujemy natomiast Produktu (rys. nr 19). Parametry rozwijania danych zatwierdzamy przyciskiem OK.

rys. nr 19 — filtrowanie danych

Otrzymamy dane przedstawione na rysunku nr 20.

rys. nr 20 — Przefiltrowane dane

Mamy liczbę porządkową zamówienia, do każdej daty zamówienia mamy przypisane daty ze zmianą ceny oraz z ceny. Cofniemy poprzedni krok za pomocą ikonki koła zębatego przy nazwie kroku, aby nie dodawał nam się prefiks – usuwamy zawartość pola Domyślny prefiks nazwy kolumny (rys. nr 21).

rys. nr 21 — Rozwijanie danych

Otrzymamy dane przedstawione na rysunku nr 22.

rys. nr 22 — rozwinięte dane

Interesuje nas maksymalna data poniżej daty zamówienia. Musimy dodać kolumnę warunkową. Wybieramy polecenie Kolumna warunkowa z karty Dodaj kolumnę (rys. nr 23).

rys. nr 23 — Kolumna warunkowa

Otworzy nam się okno Dodawania kolumny warunkowej, w którym określamy nazwę nowej kolumny (punkt nr 1 na rysunku nr 24 – nazwa Daty) i warunek jaki musi spełnić jej wynik. Nasz warunek będzie wyglądał następująco: Jeśli Data zmiany ceny (pole nr 2) jest przed lub równa (pole nr 3) wartości z kolumny Data zamówienia (punkt nr 4), to chcemy zwrócić wartość z kolumny Data zmiany ceny (punkt nr 5). W przeciwnym wypadku chcemy otrzymać wartość null (punkt nr 6). Wybraliśmy wartość null ponieważ później możemy łatwo odfiltrować dane po tej wartości. Powyższe parametry zatwierdzamy przyciskiem OK.

rys. nr 24 — Parametry kolumny warunkowej

Otrzymamy dane przedstawione na rysunku nr 25.

rys. nr 25 — Dane z kolumną warunkową

Możemy łatwo zauważyć dla pierwszego wiersza, że mamy już podany wynik. Prawidłowa cena to 10, ponieważ dla innych wartość mamy wartości null (rys. nr 26).

rys. nr 26 — właściwy wynik

Natomiast dla np. wiersza piątego mamy dwie daty, co może być mylące (rys. nr 27). Z tych danych interesuje nas cena, która jest przy starszej dacie.

rys. nr 27 dwa wyniki spełniające dany warunek

Przede wszystkim musimy odfiltrować nasze dane po wartościach null. Klikamy na ikonkę trójkącika przy nagłówku kolumny Daty i podręcznym menu odznaczamy checkbox przy wartości null (rys. nr 28). Nałożony filtr zatwierdzamy przyciskiem OK.

rys. nr 28 — Odfiltrowanie danych po wartości null

Otrzymamy dane przedstawione na rysunku nr 29.

rys. nr 29 — Aktualne dane

W następnej kolejności musimy zgrupować nasze dane. Wybieramy polecenie Grupowanie według z karty Narzędzia główne (rys. nr 30)

rys. nr 30 — Grupowanie według

Otworzy nam się okno Grupowania według, gdzie ustawiamy grupowanie zaawansowane (punkt nr 1 na rysunku nr 31) i trzy grupy po których chcemy pogrupować dane (L.p., Data zamówienia i Produkt – punkt nr 2 na rysunku nr 31)). W punkcie oznaczonym numerem 3 na rysunku nr 31 wybieramy rodzaj operacji – Maksimum z kolumny Daty. Zmieniamy nazwę nowej kolumny na Najnowsza cena. Ustawione parametry zatwierdzamy przyciskiem OK.

rys. nr 31 — Parametry grupowania

Otrzymamy dane przedstawione na rysunku nr 32, czyli najnowsze daty z tabeli tCena.

rys. nr 32 — Pogrupowane dane

Kiedy mamy już wyznaczone te daty, brakuje nam wartości ceny dla tych dat. Rozwijamy polecenie Połącz (punkt nr 2 na rysunku nr 33) z karty Narzędzia główne, a następnie wybieramy polecenie Scal zapytania (punkt nr 3).

rys. nr 33 — Scal zapytania

Otworzy nam się okno Scalania, gdzie wybieramy tabele i pasujące w nich kolumny (zaznaczamy dwie kolumny przytrzymując klawisz Ctrl). Interesują nas kolumny Produkt i Data. Wybieramy rodzaj sprzężenia Lewe zewnętrzne (wszystkie z pierwszej i pasujące z drugiej). Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 34).

rys. nr 34 — Parametry scalania

Otrzymamy dane z kolumną tCeny, w której dane są ukryte pod postacią tablic. Klikamy ikonkę ze strzałkami przy nazwie kolumny tCeny a następnie wybieramy z podręcznego menu polecenie Rozwiń i zaznaczamy checkbox przy wartości Cena. Odznaczamy opcję Użyj oryginalnej nazwy kolumny jako prefiksu i zatwierdzamy przyciskiem OK. (rys. nr 35).

rys. nr 35 — Rozwiń dane

Otrzymamy dane, z których usuwamy kolumnę najnowsza data ponieważ jest niepotrzebna (rys. nr 36).

rys. nr 36 — Rozwinięte dane

Tak przygotowane dane możemy wczytać do Excela. Rozwijamy polecenie Zamknij i załaduj z karty Narzędzia główne, a następnie wybieramy polecenie Zamknij i załaduj do (rys. nr 37).

rys. nr 37 — Zamknij i załaduj do

Otworzy nam się w Excelu okno Ładowania do, gdzie ustawiamy sposób wyświetlania danych jako Utwórz tylko połączenie i zatwierdzamy przyciskiem Załaduj (rys. nr 38).

rys. nr 38 — Parametry ładowania danych

W oknie Zapytania dotyczące skoroszytu wybieramy zapytanie, które chcemy wstawić do arkusza (Scalone1), a następnie wybieramy polecenie Pokaż zapytania z karty Dane (rys. nr 39).

rys. nr 39 — Załadowane dane do Excela

Klikamy prawym przyciskiem myszy na nazwę zapytania Scalone1 i z podręcznego menu wybieramy polecenie Załaduj do (rys. nr 40).

rys. nr 40 — Załaduj do

Otworzy nam się okno Ładowania do, gdzie wybieramy wstawienie danych jako tabela w istniejącym arkuszu, a następnie wskazujemy konkretną komórkę (rys. nr 41). Tak ustawione parametry zatwierdzamy przyciskiem Załaduj.

rys. nr 41 — Parametry ładowania danych

Otrzymamy dane przedstawione na rysunku nr 42

rys. nr 42 — Dane w Excelu

Zapomnieliśmy posortować dane po liczbie porządkowej, więc klikamy dwukrotnie na nazwę naszego zapytania Scalone1 i przechodzimy do Edytora zapytań Power Query. Klikamy na ikonkę trójkącika przy nazwie kolumny L.p. i w menu wybieramy polecenie Sortuj rosnąco (rys. nr 43).

rys. nr 43 — Sortuj rosnąco

Klikamy polecenie Zamknij załaduj z karty Narzędzia główne. Po powrocie do Excela otrzymujemy prawidłowe, posortowane dane przedstawione na rysunku nr 44.

rys. nr 44 — Prawidłowe dane w Excelu

Podsumowując przedstawione rozwiązanie było długie i nazbyt skomplikowane, ale najważniejsze, że działa prawidłowo. Co ważne w tym zadaniu korzystaliśmy tylko z poleceń, które są na wstążce. W kolejnym odcinku przedstawimy szybsze rozwiązanie, ale będziemy musieli w nim skorzystać z funkcji języka M.


Przedsprzedaż Mistrz Excela II wydanie

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 przedsprzedaży możesz mieć Mistrza Excela w obniżonej cenie:
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 przedsprzedaży możesz mieć całą książkę za jedyne 30 zł (łącznie z darmową wysyłką listem ekonomicznym), a jeśli zależy Ci na dedykacji wystarczy, że dołożysz 10 zł.

Książka Mistrz Excela reklama