W
dzisiejszym poście nauczymy się pisać taką walidację danych (sprawdzanie
poprawności danych), żeby ograniczyć możliwość wpisywania daty do maksymalnie
30 dni w przyszłość. Zadanie to wykonamy na podstawie przykładowych danych z
rysunku nr 1.
Rys. nr 1 – przykładowe dane
W naszym
przykładzie wartością graniczną będzie 30 dni, ale zasada jest taka sama dla
dowolnej liczby wpisanej w konkretną komórkę. Zaczniemy od wpisania formuły dla
funkcji DZIŚ, która zwraca aktualną datę. Otrzymamy komórkę z dzisiejszą
datą przedstawioną w komórce C3 na rys. nr 2.
Rys. nr 2 – wartość zwrócona przez funkcję DZIŚ
Kolejnym
etapem jest dodanie do funkcji DZIŚ wartości 30 dni lub najlepiej odwołania do
komórki, w której mamy podaną ilość dni, u nas jest to komórka E2. Musimy
pamiętać o zablokowaniu tej komórki bezwzględnie za pomocą klawisza F4. Zapis
formuły powinien wyglądać następująco:
=DZIEŃ()+$E$2
Po
zatwierdzeniu formuły otrzymamy datę po upływie 30 dni, czy maksymalną datę
jaką użytkownik może wpisać w dane w kolumnie Data. Ponadto formułę tą
kopiujemy na wiersze poniżej dla wszystkich danych (rys. nr 3).
Rys. nr 3 – Data po upływie 30 dni od dzisiejszej daty
Nasze
zadanie polega na sprawdzeniu czy wpisana data dostawy nie przekracza progu 30
dni od dnia dzisiejszego. Ustaliliśmy datę graniczną, czyli datę jaka będzie po
upływie 30 dni. Teraz w trybie edycji komórki kopiujemy cała formułę za pomocą
skrótu klawiszowego Ctrl+C, następnie zaznaczamy wszystkie komórki, które mają
mieć sprawdzanie poprawności danych i wybieramy polecenie Sprawdzanie
poprawności danych z karty Dane (rys. nr 4).
Rys. nr 4 – polecenie Sprawdzanie poprawności danych
Otworzy nam
się okno Sprawdzania poprawności danych, gdzie w Kryteriach poprawności,
w polu Dozwolone wybieramy z listy rozwijanej typ Data. W polu Wartości
danych wybieramy z listy rozwijanej Mniejsza niż lub równa, natomiast w
polu Data końcowa wklejamy wcześniej skopiowaną formułę za pomocą skrótu
klawiszowego Ctrl+V. Tak ustawione parametry sprawdzania poprawności danych
zatwierdzamy przyciskiem OK (rys. nr 5).
Rys. nr 5 – parametry sprawdzania poprawności danych
Teraz Excel
w zaznaczonych komórkach pozwoli wpisać nam datę dzisiejszą, datę wcześniejszą
i każdą datę poniżej 30 dni w przód. Excel dzięki sprawdzaniu poprawności
danych nie pozwoli nam natomiast wpisać w komórki innych danych, np. tekstu,
czy innych dowolnych znaków, jak również daty przekraczającej datę graniczną,
czyli data dzisiejsza plus 30 dni. Po wpisaniu błędnej daty wyświetli nam się
komunikat o błędnym wpisaniu danych przedstawiony na rys. nr 6.
Rys. nr 6 – komunikat o wpisaniu danych, nie spełniających kryteriów poprawności danych
Nie możemy
wstawić daty przekraczającej 30 dni od daty dzisiejszej, ale możemy zmienić
kryterium ustawione w komórce E2. Wtedy automatycznie zmieni nam się kryterium
poprawności danych (rys. nr 7).
Rys. nr 7 – zmiana kryterium poprawności danych
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.
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.
W
dzisiejszym poście nauczymy się jak numerować przedmioty wewnątrz grupy
produktów, jak wstawić odpowiedni indeks. Zadanie to omówimy na podstawie
przykładowych danych z rysunku nr 1.
Rys. nr 1 – przykładowe dane
Przykładowo
mamy różne kategorie produktów takie jak myszki, klawiatury, monitory, naszym
celem jest ponumerowanie przedmiotów w danej kategorii, czyli nadanie numeru
porządkowego. Na przykład Mysz Mini to będzie L.p. 1, Klawiatura Dark to L.p.
1, a mysz bezprzewodowa L.p. 2 itd. Ręcznie wpisaną numerację pokazano na rys.
nr 2.
Rys. nr 2 – ręczne numerowanie produktów w danej kategorii
W Excelu
rozwiązanie takiego zadania jest bardzo proste. Wystarczy skorzystać tutaj z
funkcji LICZ.JEŻELI. Najważniejsze dla tej funkcji jest odpowiednie
zbudowanie Zakresu, czyli pierwszego argumentu funkcji. Zakres ten musi
się dynamicznie rozrastać jak będziemy naszą formułę przeciągać w dół – na
wiersze poniżej. Zakres dla pierwszego wiersza powinien wyglądać następująco
C2:C2. Drugi argument funkcji to Kryteria, czyli będziemy tutaj
sprawdzać czy w zakresie określonym w pierwszym argumencie znajduje się wartość
ze sprawdzanej w tym momencie komórki, czyli C2. Zanim zatwierdzimy formułę
musimy dobrze określić argument zakres, czyli zablokować pierwszą komórkę
(pierwszą część zakresu), aby uzyskać zakres rozrastający się. Pierwszą komórkę
C2 blokujemy bezwzględnie za pomocą klawisza F4. Druga część zakresu powinna
pozostać bez zmian, dzięki temu będzie się przesuwać w dół. Zapis funkcji
powinien wyglądać następująco:
=LICZ.JEŻELI
($C$2:C2;C2)
Powyższą
funkcje zatwierdzamy przyciskiem Enter i kopiujemy na wiersze poniżej.
Otrzymamy dane przedstawione na rys. nr 3.
Rys. nr 3 – ponumerowane produkty z poszczególnych kategorii
Za pomocą
klawisza F2 możemy podejrzeć jak wygląda formuła dla kolejnego wiersza. Zapis
funkcji wygląda następująco:
=LICZ.JEŻELI
($C$2:C3;C3)
Podsumowując
z każdym kolejnym wierszem zakres się powiększa o kolejną komórkę (rys. nr 4).
Rys. nr 4 – formuła funkcji LICZ.JEŻELI dla komórki C3
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.
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.
W
dzisiejszym poście nauczymy się jak pobrać dane z pliku PDF do Power Query.
Omówimy tutaj dwie możliwości. W Excelu, Power Query nie ma możliwości pobrania
danych bezpośrednio z pliku PDF, natomiast taka możliwość pojawia się w Power
BI Desktop (drugie rozwiązanie). Pierwsze rozwiązanie będzie wykonane na
podstawie filmu znalezionego w sieci, gdzie najpierw przekształcimy dane z
pliku PDF do takiego formatu, który Power Query będzie w stanie pobrać.
Załóżmy, że mamy przykładowy plik PDF jak na rys. nr 1.
Rys. nr 1 – przykładowy plik pdf do pobrania
Musimy ten
plik otworzyć za pomocą WORDA, czyli klikamy na niego prawym przyciskiem myszy
i z podręcznego menu rozwijamy polecenie Otwórz za pomocą, a następnie
wybieramy polecenie Wybierz inną aplikację (nie mamy na domyślnej liście
programu WORD) – rys. nr 2.
Rys. nr 2 – polecenie Wybierz inną aplikację
Otworzy się
okno o nazwie Jak chcesz otworzyć ten plik, gdzie musimy znaleźć na liście
program WORD, zaznaczyć go i nasz wybór zatwierdzić przyciskiem OK (rys. nr 3).
Rys. nr 3 – okno o nazwie Jak chcesz otworzyć ten plik?
Po
zatwierdzeniu otwarcia pliku PDF w programie Word, plik PDF zostaje
przekonwertowany na edytowalny dokument tekstowy. Funkcjonalność ta działa od
WORDA 2016, we wcześniejszej wersji nie działało. Pojawi nam się okno , gdzie
musimy zatwierdzić decyzję, że chcemy przekształcić plik PDF za pomocą
przycisku OK. Otworzy nam się WORD z plikiem przedstawionym na rys. nr 4.
Rys. nr 4 – plik pdf otwarty w programie WORD
Mamy dane w
wordzie, razem z tabelkami, jakie nas interesują. Nie potrzebujemy danych z
Worda tylko danych zapisanych jako html, czyli stronę web. Przechodzimy do menu
Plik, następnie wybieramy polecenie Zapisz jako (rys. nr 5).
Rys. nr 5 – polecenie Zapisz jako
Otworzy nam
się panel Zapisywania jako, gdzie możemy wybrać lokalizację w jakiej chcemy
zapisać plik, zmienić tytuł (nazwę) pliku a przede wszystkim wybrać typ pliku.
Rozwijamy listę z rodzajami plików i wybieramy Strona sieci Web (rys. nr
6).
Rys. nr 6 – typ pliku Strona sieci Web
Po wybraniu
odpowiedniego typu pliku, a ze względu na to że żyjemy w Polsce, musimy
rozwinąć polecenie Więcej opcji (zaznaczone strzałką na rys. nr 7).
Rys. nr 7 – polecenie Więcej opcji
Otworzy nam
się okno Zapisz jako, gdzie w menu Narzędzia wybieramy polecenie Opcje
sieci Web (rys. nr 8).
Rys. nr 8 – Opcje sieci Web
Otworzy nam
się okno Opcji sieci Web, gdzie przechodzimy na zakładkę Kodowanie, a
następnie upewniamy się że wybrane jest kodowanie Unicode (UTF‑8), żeby
Power Query mógł to prawidłowo odczytać. Tak ustawione kodowanie zatwierdzamy
przyciskiem OK (rys. nr 9).
Rys. nr 9 – kodowanie Unicode (UTF‑8)
Automatycznie
nastąpi powrót do okna Zapisz jako, gdzie ustawione parametry zatwierdzamy
przyciskiem Zapisz. Word stworzył nam plik html i dopiero ten plik będziemy
mogli zaczytać do Power Query.
Przy
pobieraniu danych Power Query może mieć problem z polskimi znaczkami, dlatego
tak ważne było ustawienie kodu UTF‑8. Rozwijamy polecenie Z pliku (punkt
2 na rys. nr 10) z karty Dane (punkt 1), a następnie wybieramy polecenie
Ze skoroszytu (punkt 3).
Rys. nr 10 – ścieżka dostępu do polecenia Ze skoroszytu
Otworzy nam
się okno Importowania danych, gdzie na dole obok wybranej Nazwy pliku musimy
wybrać opcję Wszystkie pliki a nie tylko pliki Excel. Dzięki tej opcji
wyświetlą nam się wszystkie pliki w tym katalogu. Wybieramy plik html i
zatwierdzamy nasz wybór przyciskiem Importuj (rys. nr 11).
Rys. nr 11 – okno Importowania danych
Power Query
domyśli się że nie importujemy pliku Excela i otworzy nam okno Nawigatora,
gdzie możemy wybrać poszczególne elementy, które chcemy zaczytać do Power
Query. Wybieramy tabelę nr 2, następnie przechodzimy do Power Query za pomocą
przycisku Przekształć dane. Naszym zadaniem było tylko zaczytanie danych a nie
ich przekształcanie więc możemy dane na tym etapie od razu załadować do Excela
za pomocą przycisku Załaduj (rys. nr 12).
Rys. nr 12 – okno Nawigator, gdzie wybieramy dane, które chcemy zaczytać do Power Query
Otrzymamy
wybrane dane załadowane do Excela przedstawione na rys. nr 13, dane w dokładnie
takiej postaci, w jakiej były w pliku PDF.
Rys. nr 13 – wybrane dane zaczytane do Excela
Istotne
jest, że dzięki ustawieniu odpowiedniego kodowania widać polskie znaki w
danych. W razie potrzeby dane te możemy edytować za pomocą polecenia Edytuj
z karty Zapytanie (rys. nr 14).
Rys. nr 14 – polecenie Edytuj
Pierwsze
rozwiązanie jest dość długie, ale nie wymaga instalacji dodatkowego
oprogramowania, wszystkie działania wykonujemy w pakiecie Microsoft Office.
Drugie
rozwiązanie wykorzystuje Power BI Desktop, gdzie rozwijamy polecenie Pobierz dane z karty Strona główna, następnie
wybieramy polecenie Więcej (rys. nr 15). Power Bi ma możliwość
pobierania danych bezpośrednio z pliku pdf.
Rys. nr 15 – Więcej opcji pobierania
Otworzy nam
się okno Pobierania danych, gdzie w zakładce Wszystkie wyszukujemy plik typu
PDF. Wybór rodzaju pliku do pobrania zatwierdzamy przyciskiem Połącz (rys. nr
16).
Rys. nr 16 – okno Pobierania danych, gdzie wybieramy typ pliku jaki chcemy pobrać
Otworzy się
okno Otwieranie, gdzie musimy wskazać plik pdf, z którego chcemy pobrać dane.
Power Bi pamięta ścieżkę dostępu do pliku, na którym wykonujemy poszczególne
czynności w tym odcinku. Zaznaczamy plik, który chcemy pobrać i klikamy
dwukrotnie lub zatwierdzamy przyciskiem Otwórz (rys. nr 17).
Rys. nr 17 – okno Otwieranie, gdzie wybieramy plik do pobrania
Trochę
dłużej trwa wczytywanie (łączenie) niż dla standardowego pliku czy to z Excela,
czy np. html, ale Power Bi radzi sobie z wyciąganiem danych z pliku pdf.
Otworzy nam się okno Nawigator z wyświetlonymi elementami pliku pdf. Nas
interesuje ta tabelka, która wybieraliśmy w pierwszym przykładzie, czyli tutaj
Table004 (page 3) i dodatkowo inna tabelka, aby pokazać działanie pobierania
danych. Nasze wybrane tabelki zatwierdzamy przyciskiem Załaduj (rys. nr 18).
Rys. nr 18 – okno Nawigatora, gdzie wybieramy interesujące nas elementy pliku pdf
Podsumowując
pobieramy dwie tabelki z pliku pdf. Ważne jest, że plik pdf nie może być
obrazkowy, tzn. nie mogą to być rysunki (zdjęcia) tabel tylko fizycznie
utworzone tabelki np. w pliku Word lub Excel. Jeśli mamy do czynienia z plikiem
pdf, gdzie znajdują się pliki jpg, czyli zdjęcia jakichś elementów to nic nie
jest w stanie odczytać takich danych, przynajmniej ja nie znam takiego
programu. Otrzymamy zaczytane do Power Bi dwie tabelki (rys. nr 19).
Rys. nr 19 – tabelki zaczytane do Power Bi
Jeśli chcemy
je skopiować do Excela, to wystarczy kliknąć w obszar danej tabelki prawym
przyciskiem myszy i z podręcznego menu wybrać polecenie Kopiuj tabelę
(rys. nr 20).
Rys. nr 20 – polecenie Kopiuj tabelę z podręcznego menu
Po
skopiowaniu tabeli wystarczy przejść do okna Excela i ją wkleić za pomocą
skrótu klawiszowego Ctrl+V. Dane zostały wklejone do Excela identycznie jak
wyglądały w programie Power Bi (rys. nr 21).
Rys. nr 21 – Tabelka wklejona do Excela
Możemy
wrócić do Power BI i zrobić szybkie filtrowanie danych, mianowicie kliknąć w
ikonkę trójkąta obok nazwy województwa i odznaczyć checkboxy przy nazwach
województw, które nas w tym momencie nie interesują. Parametry filtru
zatwierdzamy przyciskiem OK (rys. nr 22).
Rys. nr 22 – filtrowanie danych w tabeli
Otrzymamy
przefiltrowane dane przedstawione na rys. nr 23.
Rys. nr 23 – przefiltrowane dane w tabeli
Przygotowaliśmy
sobie przefiltrowaną tabelkę, na którą klikamy prawym przyciskiem myszy i z
podręcznego menu wybieramy polecenie Kopiuj tabelę jak na rys. nr 20.
Następnie przechodzimy do Excela, ustawiamy aktywną komórkę obok poprzednio
wklejonej tabelki i za pomocą skrótu klawiszowego Ctrl+V, wklejamy
przefiltrowaną tabelkę (rys. nr 24).
Rys. nr 24 – Druga tabelka wklejona do Excela (z uwzględnionymi filtrami)
Podsumowując
Power Bi tak samo jak Power Query przy kopiowaniu danych uwzględnia nałożone
wcześniej filtry. Jest to istotne, szczególnie przy pobieraniu danych z pliku
pdf.
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.
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.
W
dzisiejszym poście omówimy jak za pomocą Power Query możemy wyciągnąć listę
wszystkich folderów, podfolderów wskazanego folderu. Rozwiązanie to jest
proste, ale ma jedną wadę, o której pomówimy na końcu. W celu wykonania zadania
rozwijamy polecenie Pobierzdane (punkt 2 na rys. nr 1) z karty Dane
(punkt 1), następnie rozwijamy polecenie Zpliku (punkt 3) i
wybieramy polecenie Zfolderu (punkt 4).
Rys. nr 1 – ścieżka dostępu do polecenie Z folderu
Na potrzeby
naszego zadania przygotowałem katalog z większą ilością podfolderów o nazwie
WebinarPQ. Po użyciu polecenia z rys. nr 1 otworzy nam się okno Folder, gdzie musimy
wkleić ścieżkę dostępu do tego folderu lub przy użyciu przycisku Przeglądaj,
wyszukać folder ręcznie. Wklejoną ścieżkę do wybranego folderu zatwierdzamy
przyciskiem OK (rys. nr 2).
Rys. nr 2 – okno Folder
Wtedy Power
Query pobierze informacje o wszystkich plikach znajdujących się w folderze
głównym i jego pod folderach. Otworzy się okno z wybranej ścieżki dostępu,
gdzie wystarczy kliknąć przycisk Przekształć dane, aby przejść do ich
edycji (rys. nr 3). Dzięki drobnym przekształceniom danych otrzymamy listę
wszystkich elementów wybranego folderu.
Rys. nr 3 – okno z elementami wybranego folderu
Otworzy nam
się Edytor zapytań Power Query z wczytanymi danymi przedstawiony na rys. nr 4.
Rys. nr 4 – fragment danych z edytora zapytań Power Query
Dla nas
istotna jest tylko ostatnia kolumna o nazwie Folder Path, inne są zbędne
ponieważ zawierają ind=formacje na temat poszczególnych plików. Klikamy na
nazwę kolumny Folder Path prawym przyciskiem myszy i z podręcznego menu
wybieramy polecenie Usuń inne kolumny (rys. nr 5).
Rys. nr 5 – polecenie Usuń inne kolumny
Otrzymamy
listę wszystkich folderów i pod folderów zawartych w katalogu WebinarPQ
przedstawioną na rys. nr 6.
Rys. nr 6 – lista wszystkich folderów i pod folderów katalogu WebinarPQ
Rys. nr 7 – polecenie Usuń duplikaty Jak łatwo zauważyć poszczególne ścieżki się powtarzają, więc musimy je usunąć. Zaznaczamy kolumnę i rozwijamy polecenie Usuń wiersze a następnie wybieramy polecenie Usuń duplikaty (rys. nr 7).
Rys. nr 7 – polecenie Usuń duplikaty
Otrzymamy
listę ścieżek dostępu do wszystkich pod folderów wybranego katalogu
przedstawioną na rys. nr 8.
Rys. nr 8 – Lista ścieżek dostępu do wszystkich pod folderów wybranego katalogu
Jeśli
potrzebujemy jakieś dodatkowe dane to możemy dalej przekształcać te dane.
Naszym zadaniem było przygotowanie listy wszystkich pod folderów. Nasza lista
zawiera również główny folder, czyli WebinarPQ, możemy go usunąć rozwijając
podręcznego menu (przycisk z trójkącikiem przy nazwie kolumny), a następnie
odznaczając checkbox przy ścieżce dostępu do głównego katalogu. Zmiany w
filtrowaniu zatwierdzamy przyciskiem OK (rys. nr 9).
Rys. nr 9 – odfiltrowanie danych (usunięcie ścieżki dostępu do głównego katalogu)
Otrzymamy
dane z listą ścieżek dostępu do pod folderów, natomiast bez ścieżki dostępu do
głównego katalogu (rys. nr 10).
Rys. nr 10 – lista ścieżek dostępu do pod folderów katalogu WebinarPQ
Rys. nr 11 – polecenie Zamknij i załaduj doTak przygotowane dane możemy załadować do Excela za pomocą polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 11).
Rys. nr 11 – polecenie Zamknij i załaduj do
Otworzy nam
się okno Importowania danych, gdzie ustawiamy sposób wyświetlania danych jako
tabela i wskazujemy miejsce ich wstawienia, czyli istniejący arkusz i konkretna
komórka. Tak ustawione parametry importowania danych zatwierdzamy przyciskiem
OK (rys. nr 12).
Rys. nr 12 – okno Importowania danych
Otrzymamy
dane zaimportowane do Excela i przedstawione na rys. nr 13.
Rys. nr 13 – Dane zaimportowane z Power Query
Jak wspominaliśmy
na początku, rozwiązanie to jest proste, lecz ma jedną wadę. Mianowicie jeśli
podejrzymy pliki w folderze WebinarPQ, widzimy że mamy dodatkowy folder o
nazwie Nowy folder, który nie pojawił się na naszej liście (rys. nr 14). Dzieje
się tak dlatego, że folder ten jest pusty.
Rys. nr 14 – pod foldery katalogu WebinarPQ
Pusty folder
nie pojawił się na liście otrzymanej przy użyciu funkcji, która wyciąga
informacje o wszystkich plikach znajdujących się we wskazanym folderze,
ponieważ nie zawierał żadnych plików. Podsumowując przedstawiony sposób działa
poprawnie w 90%, ale musimy pamiętać o pustych folderach.
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.
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.
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
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
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ą
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ę
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
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 KolumnaIndeksu
(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)
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
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
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
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
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
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
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)
Otrzymamy
dane z nową kolumną przedstawione na rys. nr 14.
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
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
Następnie
wybieramy polecenie Kolumna niestandardowa z karty Dodaj kolumnę
(rys. nr 17).
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
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ą
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
Otrzymamy
rozwinięte dane przedstawione na rys. nr 21.
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
Otrzymamy
dane posortowane według pierwotnej kolejności przedstawione na rys. nr 23.
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
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
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
Otrzymamy
dane wstawione do Excela przedstawione na rys. nr 27.
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ą
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
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.
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.
