Excel Power Query 58 — Indeks numerowanie wewnątrz grup

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
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
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ą
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ę
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
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 Kolumna Indeksu (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)
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
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
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
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
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
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
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)
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
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
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
Rys. nr 16 – usuwanie kroku

Następnie wybieramy polecenie Kolumna niestandardowa z karty Dodaj kolumnę (rys. nr 17).

Rys. nr 17 – polecenie Kolumna niestandardowa
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
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ą
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
Rys. nr 20 – Rozwijanie kolumny niestandardowej

Otrzymamy rozwinięte dane przedstawione na rys. nr 21.

Rys. nr 21 – rozwinięte dane z kolumny niestandardowej
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
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
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
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
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
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
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ą
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
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.

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

Excel Power Query 57 — Czy element jest na liście

W dzisiejszym poście omówimy funkcjonalność polegającą na sprawdzaniu czy dany element jest na liście. W przykładowych danych przedstawionych na rys. nr 1 mamy tabelę ze sprzedawcami, gdzie w zależności na której liście znajduje się dany sprzedawca, to z tej listy chcemy pobierać dane.

Rys. nr 1 – przykładowe dane
Rys. nr 1 – przykładowe dane

Zakładamy, że główna lista zawiera województwo, w którym działa dany sprzedawca i właśnie stamtąd chcemy pobierać dane. W pierwszym kroku musimy pobrać nasze dane z tabel pobrać do Power Query. Możemy to zrobić wybierając polecenie Z tabeli/zakresu z karty Dane (rys. nr 2).

Rys. nr 2 – polecenie Z tabeli/ zakresu
Rys. nr 2 – polecenie Z tabeli/ zakresu

Otworzy nam się edytor zapytań Power Query z wczytaną tabelą pobraną z Excela przedstawioną na rys. nr 3.

Rys. nr 3 – edytor zapytań Power Query z wczytaną tabelą z danymi
Rys. nr 3 – edytor zapytań Power Query z wczytaną tabelą z danymi

W analogiczny sposób pobieramy dane z drugiej tabeli. Ustawiamy aktywną komórkę w obszarze tabeli z regionami i wybieramy polecenie Z tabeli/ zakresu  z karty Dane (jak na rys. nr 2). Otrzymamy dwa odrębne zapytania w Edytorze zapytań Power Query (rys. nr 4).

Rys. nr 4 – dwa zapytania w Power Query (dwie tabele)
Rys. nr 4 – dwa zapytania w Power Query (dwie tabele)

Możemy przybliżyć dane (powiększyć) za pomocą skrótu klawiszowego Ctrl+Shift+=. Przechodzimy na zapytanie tSprzedaż_k, gdzie naszym zadaniem będzie napisanie kolumny warunkowej, która na podstawie sprzedawcy będzie wybierała wartość z kolumny Świętokrzyskie albo Mazowieckie. W tym celu wybieramy polecenie  Kolumna warunkowa  z karty Dodaj kolumnę (rys. nr 5).

Rys. nr 5 – polecenie Kolumna warunkowa
Rys. nr 5 – polecenie Kolumna warunkowa

Otworzy nam się okno Dodawania kolumny warunkowej. Ta funkcjonalność Power Query posłuży nam do zbudowania struktury funkcji IF (funkcji JEŻELI). Kolumnę tą łatwo będzie stworzyć, ale później dokonamy pewnych modyfikacji, żeby sprawdzić czy dany element jest na liście. W polu Nazwa nowej kolumny wpisujemy Główny region (punkt 1 na rys. nr 6). W polu Nazwa kolumny wybieramy z listy rozwijanej kolumnę Sprzedawca (punkt 2), w polu Operator zostawiamy domyślny, czyli równa się (punkt 3). W polu Wartość wpisujemy nazwę dowolnego sprzedawcy z regionu Świętokrzyskie np. Aleksandra (punkt 4), natomiast w polu Wartość wyjściowa najpierw rozwijamy ikonkę obok pola i wybieramy z listy rodzaj danych – Wybierz kolumnę (oznaczone strzałką) i w polu wybieramy wartość z kolumny Świętokrzyskie (punkt 5). Następnie klikamy przycisk Dodaj regułę, aby stworzyć kolejną strukturę funkcji JEŻELI.

Rys. nr 6 – okno dodawania kolumny warunkowej (pierwsza funkcja JEŻELI)
Rys. nr 6 – okno dodawania kolumny warunkowej (pierwsza funkcja JEŻELI)

W drugiej regule w polu Nazwa kolumny  wybieramy kolumnę Sprzedawca, pole Operator zostaje bez zmian, w polu Wartość  wpisujemy Dominik, następnie zmieniamy typ Wartości wyjściowej na Wybierz kolumnę (jak dla pierwszej reguły) i wybieramy kolumnę Mazowieckie. Powinniśmy stworzyć takie reguły dla każdej pojedynczej osoby. Było by to bardzo czasochłonne. My zbudowaliśmy tu tylko strukturę a później przez odpowiednie przekształcenie, sprawimy, żeby to działało dla dowolnego sprzedawcy. W polu W przeciwnym razie wpisujemy wartość null (oznaczone strzałką na rys. nr 7). Tak ustawione parametry kolumny warunkowej zatwierdzamy przyciskiem OK.

Rys. nr 7 – okno dodawania kolumny warunkowej (druga funkcja JEŻELI)
Rys. nr 7 – okno dodawania kolumny warunkowej (druga funkcja JEŻELI)

Otrzymamy dane z nową kolumną przedstawione na rys. nr 8. W kolumnie Główny region stworzyliśmy strukturę, zbudowaną za podstawie prostych obliczeń funkcji JEŻELI.

Rys. nr 8 – Dane z nową kolumną warunkową
Rys. nr 8 – Dane z nową kolumną warunkową

W zapisie formuły kolumny warunkowej w pasku formuły sprzedawca został porównany do konkretnego sprzedawcy, a naszym celem będzie sprawdzenie czy dany sprzedawca jest na liście. Podsumowując zastąpimy to porównanie funkcją List.Contains (rys. nr 9).

Rys. nr 9 – zapis formuły kolumny warunkowej
Rys. nr 9 – zapis formuły kolumny warunkowej

Funkcja List.Contains potrzebuje jako pierwszego argumentu listy wszystkich sprzedawców, listę w Power Query zapisujemy w nawiasach klamrowych. Następnie sprawdzamy czy w danej liście znajduje się ten konkretny sprzedawca z danego wiersza, czyli tutaj możemy użyć struktury zbudowanej przez kolumnę warunkową. Zapis formuły powinien wyglądać następująco:

=Table.AddColumn(#"Zmieniono typ", "Główny region", each if List.Contains ({"Aleksandra", "Robert"}, [Sprzedawca]) then [Świętokrzyskie] else if [Sprzedawca]="Dominik" then [Mazowieckie] else null))

Zmieniliśmy tylko część formuły – dla pierwszych 2 sprzedawców i po zatwierdzaniu formuły otrzymamy zmienione dane przedstawione na rys. nr 10.

Rys. nr 10 – zapis funkcji List.Contains dla pierwszej reguły kolumny warunkowej
Rys. nr 10 – zapis funkcji List.Contains dla pierwszej reguły kolumny warunkowej

Ten sposób jest łatwiejszy, ponieważ bezpośrednio w formule możemy wypisać wszystkich sprzedawców z danego województwa. Musimy pamiętać, że pobraliśmy drugą tabelę z danymi (zapytanie tRegiony_k), gdzie mamy listy sprzedawców z podziałem na województwa. W zapisie formuły możemy się odwołać bezpośrednio do danych z tego zapytania (do poszczególnych kolumn), zamiast wpisywać ręcznie nazwy poszczególnych sprzedawców (rys. nr 11). Zapis formuły będzie wtedy wyglądał następująco:

=Table.AddColumn(#"Zmieniono typ", "Główny region", each if List.Contains (tRegiony_k [Świętokrzyskie], [Sprzedawca]) then [Świętokrzyskie] else if [Sprzedawca]="Dominik" then [Mazowieckie] else null))

Rys nr 11 – odwołanie do kolumny Świętkorzyskie z zapytania tRegiony_k
Rys nr 11 – odwołanie do kolumny Świętokrzyskie z zapytania tRegiony_k

Po zatwierdzeniu formuły otrzymamy dane, gdzie każde wystąpienie sprzedawcy z regionu świętokrzyskiego zostanie rozpoznane. To odwołanie działa dla pierwszej reguły, którą stworzyliśmy dla kolumny warunkowej. Analogicznie możemy zmienić drugą regułę – dla regionu Mazowieckiego. Zapis formuły będzie wyglądał następująco:

=Table.AddColumn(#"Zmieniono typ", "Główny region", each if List.Contains (tRegiony_k [Świętokrzyskie], [Sprzedawca]) then [Świętokrzyskie] else if List.Contains (tRegiony_k [Mazowieckie] then [Mazowieckie] else null))

Power Query przeliczy dane po zatwierdzeniu kolumny i otrzymamy dane przedstawione na rys. nr 12.

Rys. nr 12 – dane po użyciu w zapisie formuły funkcji List.Contains
Rys. nr 12 – dane po użyciu w zapisie formuły funkcji List.Contains

W danych mamy jednego sprzedawcę (Beata), który nie znajduje się na żadnej z list. Formuła zadziała tak, że otrzymamy dla tego sprzedawcy wartość null. Dzieje się tak dlatego, że w kolumnie warunkowej wpisaliśmy, że jeśli warunki nie zostaną spełnione kolumna warunkowa ma nam zwrócić wartość null ( parametr W przeciwnym razie na rys. nr 7).

Tak przekształcone dane możemy załadować do Excela za pomocą polecenia Zamknij i załaduj do  z karty Narzędzia główne (rys. nr 13).

Rys. nr 13 – polecenie Zamknij i załaduj do
Rys. nr 13 – polecenie 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 – konkretny arkusz oraz wskazujemy konkretną komórkę. Tak ustawione parametry importowania danych zatwierdzamy przyciskiem OK (rys. nr 14).

Rys. nr 14 – okno Importowania danych
Rys. nr 14 – okno Importowania danych

Otrzymamy dane zaimportowane do Excela i przedstawione na rys. nr 15.

Rys. nr 15 – Dane zaimportowane do Excela
Rys. nr 15 – Dane zaimportowane do Excela

Jak widać na rysunku powyżej dane z odpowiednich kolumn są pobierane do kolumny warunkowej Główny region.


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

Power Query 34 — Cena produktu na podstawie daty zamówienia — funkcja Table.SelectRows

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.

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

Power Query 33 — Cena produktu na podstawie daty zamówienia — funkcjonalności ze wstążki

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.


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

Licznik faktur przy ostatnim wystąpieniu kontrahenta — widzowie #58

Jak ustawić wynik przy ostatnim wystąpieniu kontrahenta?

Licznik faktur przy ostatnim wystąpieniu kontrahenta — widzowie #58 Licznik faktur przy ostatnim wystąpieniu kontrahenta -  widzowie #58

Pytanie widza polegało na tym, że pewne obliczenia (liczbę faktur dla danego kontrahenta) pokazać tylko przy ostatnim wystąpieniu tego kontrahenta.

Samo obliczenie liczby faktur załatwia się przeważnie korzystając z zakresów dynamicznych, które mają jeden koniec zablokowany, a drugi ruchomy.

=LICZ.WARUNKI($D$2:D2;D2;$C$2:C2;"faktura")

Trzeba było jeszcze wykorzystać funkcję JEŻELI, żeby sprawdzać warunek czy to ostatnie wystąpienie kontrahenta. Tu też można wykorzystać dynamiczne tylko zmniejszające się, czyli od pierwszego wiersza do wiersza bardzo daleko np: D2:$D$1000 w sumie daje to taką formułę:

=JEŻELI(LICZ.JEŻELI(D2:$D$1000;D2)>1;"";LICZ.WARUNKI($D$2:D2;D2;$C$2:C2;"faktura"))

P.S.

Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić to napisz do mnie. Ja w miarę możliwości odpowiem na Twoje pytanie.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube — Licznik faktur przy ostatnim wystąpieniu kontrahenta — widzowie #58

Formula by wyciągnąć posortowaną listy unikalnych numerów, CZĘSTOŚĆ i AGREGUJ — sztuczki #64

Jak wyciągnąć z kolumny posortowaną listę unikalnych numerów?


Formula by wyciągnąć posortowaną listy unikalnych numerów, CZĘSTOŚĆ i AGREGUJ — sztuczki #64

Zobacz jak wykorzystać komórkę pomocniczą i formułę tablicową by wyciągnąć posortowaną listy unikalnych numerów z kolumny wypełnionej powtarzającymi się numerami Zobacz dwie metody:

      1. Metoda dla programu Excel 2010 lub nowszego funkcje: CZĘSTOŚĆ, AGREGUJ, ILE.WIERSZY, JEŻELI, SUMA, MIN.K lub MAX.K
      2. Metoda dla programu Excel 2007 lub wcześniej: LICZ.JEŻELI, SUMA.ILOCZYNÓW, MAX.K, JEŻELI, ILE.WIERSZY

P.S.

Wpis na podstawie Excel Magic Trick 1040

Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube — Formula by wyciągnąć posortowaną listy unikalnych numerów, CZĘSTOŚĆ i AGREGUJ — sztuczki #64