Excel — Walidacja danych data dostawy w dniu pracującym — porada #375

W dzisiejszym poście będziemy kontynuować temat sprawdzania poprawności danych. Nauczymy się tak ustalać sprawdzanie poprawności (walidację) danych, aby było możliwe wpisanie daty, która przypada na dzień pracujący, pomija weekendy oraz święta. Zadanie to wykonamy na podstawie przykładowych danych z rysunku nr 1, gdzie dodatkowo zostały wypisane dni świąteczne wypadające w roku 2019.

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

W Excelu wystarczy nam do tego zadania funkcja DNI.ROBOCZE, która zwraca dni robocze pomiędzy dwiema datami. Pierwszym argumentem funkcji jest data_pocz, czyli data początkowa. Drugi argument funkcji to data_końc, czyli data końcowa. Trzecim argumentem (opcjonalnym) są święta, czyli dni świąteczne, które chcemy odliczyć z dni roboczych. Nasuwa się tutaj pytanie, co się stanie, kiedy w zapisie funkcji jako dzień początkowy i końcowy podamy tę samą datę. Zapis funkcji powinien wyglądać następująco:

=DNI.ROBOCZE(B3;B3)

W takiej sytuacji funkcja DNI.ROBOCZE zwróci nam wartość 1, kiedy ten dzień będzie dniem pracującym lub wartość 0, kiedy data będzie dniem wolnym od pracy (weekend lub święta) – rys. nr 2.

Rys. nr 2 –wartość 0 zwrócona przez funkcję DNI.ROBOCZE dla daty z weekendu
Rys. nr 2 –wartość 0 zwrócona przez funkcję DNI.ROBOCZE dla daty z weekendu

Taki zapis wystarczy nam do określenia warunku poprawności danych, ponieważ wartość 0 zostanie zinterpretowane przez Excela jako FAŁSZ, czyli Excel odrzuci nam taką datę. Natomiast po wpisaniu poprawnej daty otrzymamy wartość 1, odpowiadającą wartości logicznej PRAWDA. Do poprzedniego zapisu funkcji dołożymy sobie tylko opcjonalny trzeci argument funkcji, czyli święta określone w osobnej tabeli. Zakres ten blokujemy bezwzględnie za pomocą klawisza F4. Zapis funkcji powinien wyglądać następująco:

=DNI.ROBOCZE(B3;B3;$I$2:$I$14)

Rys. nr 3 – poprawny zapis funkcji DNI.ROBOCZE z uwzględnieniem świąt
Rys. nr 3 – poprawny zapis funkcji DNI.ROBOCZE z uwzględnieniem świąt

W trybie edycji komórki kopiujemy powyższą formułę za pomocą skrótu klawiszowego Ctrl+C, następnie użyjemy jej do sprawdzenia poprawności danych dla naszych przykładowych dat zakupów z kolumny Data.

Zaznaczamy komórki, na których chcemy użyć sprawdzania poprawności danych (B3:B6) i wybieramy ikonę polecenia Poprawność danych z karty Dane (rys. nr 4).

Rys. nr 4 – polecenie Poprawność danych
Rys. nr 4 – polecenie Poprawność danych

Otworzy nam się okno Sprawdzania poprawności danych, gdzie w karcie Ustawienia, w polu Dozwolone wybieramy typ Niestandardowe. Następnie w polu Formuła 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 – okno sprawdzania poprawności danych
Rys. nr 5 – okno sprawdzania poprawności danych

Po zatwierdzeniu sprawdzania poprawności danych możemy sprawdzić jego działanie, tzn. wpisujemy dowolną datę. Jeśli wpisana data będzie wypadała w weekend lub w dni świąteczne to pojawi się komunikat przedstawiony na rys. nr 6.

Rys. nr 6 – komunikat dla błędnej daty (daty dnia wolnego od pracy)
Rys. nr 6 – komunikat dla błędnej daty (daty dnia wolnego od pracy)

Możemy dodatkowo wykonać sprawdzanie poprawności danych dla dostaw do klienta tylko w dniach od poniedziałku do czwartku, czyli z wyłączeniem piątku oprócz weekendów i świąt. Aby widzieć na jaki dzień tygodnia wypada wpisana przez nas data, otwieramy okno Formatowania komórek za pomocą skrótu klawiszowego Ctrl+1, następnie przechodzimy do kategorii Niestandardowe i w polu Typ wpisujemy skrót ddd. Zapis powinien wyglądać następująco: rrrr-mm-dd, ddd. Ustawienie formatowania zatwierdzamy przyciskiem OK (rys., nr 7).

Rys. nr 7 – okno formatowania komórek
Rys. nr 7 – okno formatowania komórek

Otrzymamy datę z oznaczeniem dnia tygodnia przedstawioną na rys. nr 8.

Rys. nr 8 – data z oznaczeniem dnia tygodnia
Rys. nr 8 – data z oznaczeniem dnia tygodnia

Z tym zadaniem możemy poradzić sobie wykorzystując niejako funkcję rozwijającą możliwości standardowej funkcji DNI.ROBOCZE. Użyjemy tutaj funkcji DNI.ROBOCZE.NIESTAND, która zwraca dni robocze między dwoma datami z niestandardowymi parametrami dotyczącymi weekendów. Dwa pierwsze argumenty są takie same jak dla standardowej funkcji. Trzeci argument funkcji to weekend, czyli dni tygodnia jakie chcemy przyjąć za weekend. Mamy tutaj listę możliwości przedstawioną na rys. nr 9.

Rys. nr 9 – lista możliwości wyboru dni, które chcemy traktować jako dni wolne od pracy (weekend).
Rys. nr 9 – lista możliwości wyboru dni, które chcemy traktować jako dni wolne od pracy (weekend).

Trzeci argument możemy również ustalić jako ciąg cyfr 0 i 1, gdzie wartość 0 odpowiada dniom pracującym a wartość 1 to dni wolne od pracy. Zatem zapis formuły powinien wyglądać następująco:

=DNI.ROBOCZE.NIESTAND(F3;F3;"0000111")

Mamy określony trzeci argument funkcji, czyli dni jakie chcemy traktować jako weekend. Pozostaje nam określić dni świąteczne, czyli czwarty parametr funkcji – święta. Tak jak dla poprzedniego przykładu temu parametrowi odpowiada zakres z tabeli święta (I2:I14), zablokowany bezwzględnie za pomocą klawisza F4. Zapis całej funkcji powinien wyglądać następująco:

=DNI.ROBOCZE.NIESTAND(F3;F3;"0000111";$I$2:$I$14)

Wpisaną formułę kopiujemy w trybie edycji komórki za pomocą skrótu klawiszowego Ctrl+C. Zaznaczamy obszar na jaki chcemy nałożyć sprawdzanie poprawności danych, następnie wybieramy ikonę polecenia Poprawność danych z karty Dane (tak jak na rys. nr 4). Otworzy nam się okno Sprawdzania poprawności danych, gdzie karcie Ustawienia, w polu Dozwolone wybieramy typ Niestandardowe. Następnie w polu Formuła wklejamy wcześniej skopiowaną formułę za pomocą skrótu klawiszowego Ctrl+V. Tak ustawione parametry sprawdzania poprawności danych zatwierdzamy przyciskiem OK (rys. nr 10).

Rys. nr 10 – okno sprawdzania poprawności danych
Rys. nr 10 – okno sprawdzania poprawności danych

Dzięki sprawdzaniu poprawności danych Excel pozwoli nam wpisać datę, odpowiadającą tylko dniom od poniedziałku do czwartku. Jeśli spróbujemy wpisać datę z weekendu lub święto wyświetli nam się komunikat przedstawiony na rys. nr 11.

Rys. nr 11 – komunikat o błędnym wpisaniu daty
Rys. nr 11 – komunikat o błędnym wpisaniu daty

Podsumowując nasza formuła działa prawidłowo, mianowicie sprawdza czy nasza data jest dniem wolnym od pracy (założenie weekend i piątek)oraz czy nie wypada w święta.


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 — Walidacja danych — data do 30 dni w przyszłość — porada 374

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
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Ś
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
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
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
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
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
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.

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 — Indeks numerowanie wewnątrz grupy — porada #373

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
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
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
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
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.

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 #60 — Pobieranie danych z pliku pdf do Excela

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
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ę
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?
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
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
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
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
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
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)
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
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
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
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
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
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 danych
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ć
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
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
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
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
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
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
Rys. nr 22 – filtrowanie danych w tabeli

Otrzymamy przefiltrowane dane przedstawione na rys. nr 23.

Rys. nr 23 – przefiltrowane dane w tabeli
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)
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.

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 #59 — Lista wszystkich podfolderów we wskazanym katalogu

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 Pobierz dane (punkt 2 na rys. nr 1) z karty Dane (punkt 1), następnie rozwijamy polecenie Z pliku (punkt 3) i wybieramy polecenie Z folderu (punkt 4).

Rys. nr 1 – ścieżka dostępu do polecenie Z folderu
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
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
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
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
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 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
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
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)
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 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
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
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
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
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.

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