W dzisiejszym poście nauczymy się jak wyciągnąć rok, miesiąc i dzień z daty. Nauczymy się tworzyć prostą tabelę kalendarza w Power Query. W tym przykładzie interesujące jest to, że nie mamy żadnych danych bazowych, zaczynamy od pustego arkusza w Excelu. Wszystko stworzymy w Power Query. Aby przejść do Edytora zapytań w Power Query rozwijamy polecenie Nowe zapytanie (punkt nr 2 na rysunku nr 1) z karty Dane, a następnie wybieramy kolejno polecenia Z innych źródeł (punkt nr 3) i Puste zapytanie (punkt nr 4).
Otworzy nam się pusty Edytor zapytań. W pasku formuły
wpiszemy funkcję List.Dates. Musimy pamiętać o tym, że Power Query jest case
sensitive, czyli zwraca uwagę na wielkość liter. Po wpisaniu tej funkcji w
pasku formuły wciskamy przycisk Enter. Power Query rozpozna funkcję List.Dates
i pokaże jej parametry, które musimy określić (rys. nr 2).
Power
Query podpowiada nam co to jest za funkcja i podaje przykład jej zastosowania.
W miejscu wprowadź parametry w polu Start podajemy datę początkową, u nas
2017-01-01. W polu Count wpisujemy 365, co oznacza, że chcemy mieć rozpisany
cały rok. Natomiast w polu Step wpisujemy wartość 1, czyli chcemy mieć przeskok
o jeden dzień. Wpisane parametry zatwierdzamy przyciskiem Wywołaj (rys. nr 3).
Otrzymamy listę dni w roku 2017, począwszy od daty 2017-01-01
z krokiem co jeden dzień. Wywołanie funkcji spowoduje, że pojawi się nam ona na
liście zapytań, jako nowe zapytanie (oznaczone zieloną strzałką na rysunku nr 4).
Jeśli nie chcemy aby pokazywało się nam nowe zapytanie, to
możemy wyciąć (Ctrl+X) parametry z paska formuły dla zapytania Wywołano funkcję
(część zaznaczona na rysunku nr 5), a następnie wkleić je (Ctrl+V) w pasku
formuły do zapytania fx Zapytanie2.
Lista z datami wyświetli nam się teraz w zapytaniu Zapytanie
2, a w zapytaniu Wywołano funkcję wyświetli się błąd. W kolejnym kroku musimy
usunąć to zapytanie — klikamy prawym przyciskiem myszy na jego nazwę i z
podręcznego menu wybieramy polecenie Usuń (rys. nr 6).
Pojawi nam się komunikat Usuwanie zapytania, w którym musimy
potwierdzić naszą decyzję klikając przycisk Usuń (rys. nr 7).
Nasze zapytanie cały czas jest listą, a my potrzebujemy
tabeli, bo chcemy z tej listy wyciągnąć rok, miesiąc i dzień. Klikamy prawym
przyciskiem myszy na nagłówek Lista i z podręcznego menu wybieramy polecenie Do
tabeli.
Pojawi się nam okno Do tabeli, w którym możemy wybrać
ogranicznik i sposób obsługi dodatkowych danych. Na tym etapie nic nie
zmieniamy, zostawiamy domyślne parametry, a następnie zatwierdzamy przyciskiem
OK. (rys. nr 9).
Otrzymamy tabelę danych z jedną kolumną Column1, którą możemy
dowolnie przekształcać (rys. nr 10).
Na stronie Microsoftu możemy znaleźć informację, że jeśli
chcemy mieć nazwy kolumn to musimy zmienić w pasku formuły parametr
"null". Nazwę wpisujemy w nawiasach klamrowych {"Data"} co
pokazaliśmy na rysunku nr 12.
Jeśli nie znamy na tyle Power Query wystarczy dwa razy
kliknąć na nazwę kolumny i ją zmienić.
Jeśli chcemy wyciągnąć z naszych dat rok, miesiąc i dzień to
najlepiej dodać kolumny. Możemy również użyć poleceń z karty Przekształć, ale
wtedy przekształcimy istniejącą kolumnę a nam chodzi o dodanie nowych kolumn z
interesującymi nas danymi. Z karty Dodaj kolumnę wybieramy kolejno polecenia
Data (punkt nr 2 na rysunku nr 13), następnie Rok (punkt nr 3) i znowu Rok
(punkt nr 4).
Otrzymamy tabelę z dodatkową kolumną Rok. Analogicznie
korzystając z polecenia Data z karty Dodaj kolumnę, a następnie Miesiąc i
Miesiąc. Następnie powtarzamy ścieżkę z tą różnicą że na ostatnim etapie
wybieramy polecenie Nazwa miesiąca (punkt nr 4 na rysunku nr 14).
Wybierając na końcu polecenie Dzień otrzymamy kolumnę z
numerem dnia w miesiącu. Możemy też wybrać w czwartym kroku polecenie Nazwa
dnia, wtedy otrzymamy nazwy dni tygodnia przypisane do konkretnych dat (rys. nr 15).
Otrzymamy dane przedstawione na rysunku nr 16.
W panelu bocznym Ustawienia zapytania możemy zmienić nazwę zapytania na Kalendarz (rys. nr 17).
Nasz kalendarz jest już gotowy i możemy go sobie wczytać do
Excela. W tym celu używamy polecenia Zamknij i załaduj do (punkt nr 3 na
rysunku nr 18) z karty Narzędzia główne.
Otworzy nam się okno Ładowanie do, gdzie możemy ustawić
parametry naszego kalendarza. Mianowicie wybrać sposób wyświetlania danych jako
tabela i miejsce wstawienia danych – Istniejący arkusz i konkretna komórka.
Ustawione parametry zatwierdzamy przyciskiem Załaduj (rys. nr 19).
Otrzymamy dane przedstawione na rysunku nr 20.
Z mojego niedopatrzenia wynika sposób wyświetlania danych w kolumnie Data, nie zmieniliśmy sposobu wyświetlania danych. Wybieramy polecenie Edytuj (punkt nr 2 na rysunku nr 21) z karty Zapytanie.
Wracamy do Edytora zapytań z naszym kalendarzem. Klikamy na
tytuł kolumny Data i z podręcznego menu wybieramy polecenie Data, aby zmienić
formatowanie domyślne (rys. nr 22).
Power Query zmieni nam typ wyświetlania danych. Ponownie używając polecenia Zamknij i załaduj do z karty Narzędzia główne wczytujemy dane do Excela. Otrzymamy kalendarz przedstawiony na rysunku nr 23.
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 zajmiemy się tworzeniem tabeli
pomocniczej przefiltrowanej z tabeli głównej. Taki sam temat został omówiony w
pytaniu od widzów nr 25, gdzie wyciągaliśmy interesujące nas informacje z
tabeli głównej na podstawie jakiegoś kryterium i z tych informacji tworzyliśmy
tabelę pomocniczą. Zagadnienie to omówimy na podstawie przykładowych danych z
rysunku nr 1.
W pytaniu od widzów nr 25 do rozwiązania problemu
wykorzystywaliśmy skomplikowaną formułę tablicową. W tym poście omówimy
rozwiązanie w dodatku do Excela — Power Query, w którym to zadanie jest bardzo
szybkie i proste. Warunkiem szybkiego rozwiązania tego zadania jest to, że
kryterium według którego będziemy wyciągać dane, nie może się często zmieniać i
nie powinno być skomplikowane. W naszym przykładzie wyciągniemy z tabeli
głównej dane według kryterium nazwy fabryki – Żuczek. Załóżmy że mamy tabelę
główną z danymi sprzedażowymi (rys. nr 2).
Pierwszym krokiem jest zaczytanie danych do Power Query. W
tym celu korzystamy z polecenia Z tabeli (punkt nr 2 na rysunku nr 3) z karty Dane.
Otworzy nam się Edytor zapytań z wczytaną tabelą tMain
przedstawioną na rysunku nr 4.
Aby uzyskać tabelę zawierającą tylko dane z fabryki Żuczek,
możemy sobie nałożyć na nasza tabelę główną filtr. Klikamy na ikonkę trójkącika
(Zaznaczony strzałką na rysunku nr 5) w rogu tytułu kolumny Fabryka i w
podręcznym menu zaznaczamy filtr Żuczek (zaznaczony zielonym kwadratem na
rysunku nr 5). Nasz wybór zatwierdzamy klikając przycisk OK.
Otrzymamy przefiltrowane dane, które możemy wczytać do Excela
za pomocą polecenia Zamknij i załaduj do (punkt nr 2 na rysunku nr 6) z karty
Narzędzia główne.
Otworzy nam się w Excelu okno Ładowanie do, gdzie możemy
ustawić parametry wczytywanych danych. Wybieramy sposób wyświetlania danych
jako Tabela (punkt nr 1 na rysunku nr 7), następnie lokalizację wstawienia
danych – Istniejący arkusz i wskazujemy konkretną komórkę (punkt nr 2 na
rysunku nr 7). Nasze ustawienia zatwierdzamy klikając przycisk Załaduj.
Otrzymujemy wczytane dane spełniające kryterium jakie
ustawiliśmy, czyli fabrykę Żuczek przedstawione na rysunku nr 8.
Jeśli dołożymy sobie dane do naszej tabeli głównej, wystarczy
że klikniemy na dowolną komórkę w obszarze tabeli pomocniczej zaczytanej z
Power Query prawym przyciskiem myszy i z podręcznego menu wybierzemy polecenie
Odśwież (rys. nr 9), a otrzymamy aktualne dane.
Możemy
również użyć poleceń Odśwież lub Odśwież wszystko z karty Dane (rys. nr 10).
Kiedy mamy stałe warunki naszych filtrów zadanie to jest
bardzo proste, natomiast jeśli potrzebujemy bardziej skomplikowanych kryteriów
polecam odcinek Power Query #10 https://exceliadam.pl/?s=power+query+%2310
.
Dodatkową zaletą użycia Power Query jest to, że dane mogą być zapisane w
różnych miejscach (strony internetowe, baza danych lub nawet połączonych ze
sobą wiele plików), natomiast przy formułach tablicowych musimy mieć wszystkie
dane w jednym miejscu.
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 filtrować dane po
liście wartości. Zagadnienie to omówimy no podstawie przykładowych danych z
rysunku nr 1.
W danych mamy podaną tabelę z listą sprzedawców i wartości
sprzedaży im przypisaną. W naszym zadaniu chcemy wyciągnąć z tej tabeli listę
tylko tych sprzedawców, którzy należą do grupy A (druga tabelka). Możemy to
zadanie zrobić w Power Query. Przede wszystkim musimy te tabelki załadować do
Power Query. Wybieramy polecenie Z tabeli (punkt nr 2 na rysunku nr 2) z karty Dane.
Wczytujemy sobie najpierw mniejsza tabelkę, czyli ze
sprzedawcami z grupy A. Otworzy nam się okno Edytor zapytań z wczytaną tabelą
tGrupa. Chcemy ją mieć tylko w pamięci, więc od razu, za pomocą polecenia
Zamknij i załaduj do (punkt nr 2 na rysunku nr 3) z karty Narzędzia główne,
przenosimy do Excela.
Otworzy nam się w Excelu okno Ładowanie do, gdzie ustawiamy
parametry naszej nowej tabeli – ustawiamy opcję Utwórz tylko połączenie i
zatwierdzamy przyciskiem Załaduj (rys. nr 4).
Teraz ustawiamy aktywną komórkę w obszarze pierwszej tabelki
ze sprzedawcami i jak wyżej korzystając z polecenie Z tabeli z karty Dane,
wczytujemy tabelę do Power Query. Otworzy nam się Edytor zapytań z wczytaną
tabelą tSprzedaż. Aby móc wyciągnąć tylko tych sprzedawców którzy znajdują się
w grupie sprzedawców A musimy scalić nasze dane. Wybieramy polecenie Połącz
(punkt nr 2 na rysunku nr 5) z karty Narzędzia główne, a następnie Scal
zapytania (punkt nr 3 na rysunku nr 5).
Otworzy nam się okno Scalanie. W punkcie nr 1 oznaczonym na
rysunku nr 7 wybieramy kolumnę, po której będziemy scalać dane – Sprzedawca. W
punkcie oznaczonym numerem 2 na rysunku nr 7 wybieramy z listy rozwijanej
zapytanie tGrupa. Następnie w punkcie oznaczonym numerem 3 na rysunku nr 7 wybieramy
rodzaj sprzężenia. Musimy pamiętać, że w scalaniu tabelka u góry jest
traktowana jako lewa (oznaczona na rysunku nr 6 strzałką czerwoną, a tabelka na
dole jako prawa (strzałka niebieska).
Wybieramy sprzężenie Prawe zewnętrzne, czyli wszystkie z drugiej
tabeli i pasujące z pierwszej. Nasze ustawione parametry zatwierdzamy
przyciskiem OK (rys. nr 7).
Otrzymujemy dane przedstawione na rysunku nr 8.
Kolumnę tGrupa możemy sobie rozwinąć klikając na strzałki
oznaczone na rysunku nr 9 zieloną strzałką. Zatwierdzamy Przyciskiem OK.
W wyniku rozwinięcia kolumny tGrupa otrzymamy dane
przedstawione na rysunku nr 10.
Są to ci sami sprzedawcy co w pierwszej kolumnie, więc możemy
sobie usunąć ten krok z bocznego panelu Zastosowane kroki i usunąć całkowicie
tą kolumnę za pomocą polecenia Usuń z podręcznego menu (rys. nr 11).
Otrzymaliśmy wynik jaki nas interesuje, więc musimy go
załadować do Excela. Z karty Narzędzia główne wybieramy polecenie Zamknij i
załaduj do (punkt nr 2 na rysunku nr 12).
Otworzy się w Excelu okno Ładowanie do, gdzie ustawiamy
parametry ładowanych danych i zatwierdzamy przyciskiem Załaduj. Dane wstawione
jako tabela w istniejącym arkuszu, w konkretnej komórce (rys. nr 13).
Nasze dane w Excelu pokazane zostały na rysunku nr 14.
Co jest ciekawego w naszych działaniach w scalaniu – jeśli
wyznaczyliśmy wszystkich sprzedawców, którzy są na liście grupy A, to możemy w
bardzo łatwy sposób wyznaczyć tych, których na tej liście nie ma. Wystarczy
wrócić do Power Query i zmienić typ scalania. Aby nie stracić aktualnych
wyników, skopiujemy je za pomocą polecenia Duplikuj z podręcznego menu (rys. nr 15).
W ustawieniach zapytania w Zastosowanych krokach musimy
wrócić do kroku Scalanie zapytania i kliknąć w koło zębate przy nazwie tego
zapytania. Otworzy nam się okno Scalanie, gdzie możemy zmienić (w miejscu
oznaczonym zieloną strzałką na rysunku nr 16) rodzaj sprzężenia. Chcemy
wyrzucić te elementy które są w prawej kolumnie (tGrupa), a zachować tylko te, których tam nie ma, więc
musimy wybrać Lewe anty (wiersze tylko w pierwszej).
Po usunięciu dodatkowej kolumny (tak jak w poprzednim
scalaniu) otrzymujemy dane przedstawione na rysunku nr 17.
Następnie wybieramy polecenie Zamknij i załaduj z karty
Narzędzia główne (jak na rysunku nr 12). Otworzy nam się okno Ładowanie do,
gdzie ustawiamy wstawiane dane jako tabela oraz miejsce wstawienia danych –
konkretna komórka w istniejącym arkuszu (analogicznie jak na rysunku nr 13).
Zatwierdzamy te parametry przyciskiem Załaduj i otrzymujemy dane przedstawione
na rysunku nr 18.
Innym sposobem rozwiązania tego zagadnienia jest użycie
filtrów. Po raz kolejny wczytujemy dane z tabeli do Power Query. Otworzy nam
się Edytor zapytań z wczytaną tabelą tSprzedaż(3). Rozwijamy podręczne menu za
pomocą ikonki z boku nazwy kolumny Sprzedawca i wybieramy polecenie Filtry
tekstu, a następnie Zawiera (rys. nr 19).
Otworzy nam się okno Filtrowanie wierszy, w którym wpisujemy
jakiś ciąg znaków (bez znaczenia bo później go zmodyfikujemy) i zatwierdzamy OK
(rys. nr 20).
Otrzymamy pustą tabele przedstawioną na rysunku nr 21. Jest
pusta ponieważ żadne ze sprzedawców nie zawiera ciągu liter asd, jakie
wpisaliśmy.
Została tu użyta funkcja Text.Contains, która ma zwrócić każdy
wiersz, który zawiera ciąg znaków. My chcemy aby funkcja zwróciła nam tabelę
więc użyjemy następującej formuły:
List.ContainsAny(tGrupa[Grupa A],[{Sprzedawca}])
Możemy tę funkcje przetłumaczyć na przypadek, jeśli lista
zawiera którąkolwiek z wartości. Tą formułę musimy wkleić w pasek formuły
zamiast funkcji Text.Contains. Zapis formuły oraz jej wyniki zostały
przedstawione na rysunku nr 22.
Otrzymany wynik jest identyczny z tym otrzymanym przez
scalanie. Moim zdaniem dużo bardziej skomplikowany i przeznaczony dla osób
bardziej zaawansowanych w Power Query. Ważną informacją do zapamiętania jest
to, że Power Query nie obsługuje symboli wieloznacznych, takich jak np. * czy
?. Formuły wtedy nie zadziałają. Najlepiej korzystać z tego rozwiązania, które
rozumiemy i potrafimy go używać, ponieważ jest większe prawdopodobieństwo, że
nie popełnimy błędu. Jeśli użyjemy rozwiązania, które sprawia nam trudności i
jest nie do końca zrozumiałe, może się zdarzyć że otrzymamy błędne wyniki.
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ę wyznaczać ilość dni pomiędzy datami w danym
okresie. Jest to temat kontynuujący zagadnienie omówione w poprzednim poście,
czyli ilość dni między datami w konkretnym miesiącu. Przetestowałam trzy
rozwiązania, jakie udało mi się wymyślić. W tym poście przedstawię to, które
uważam za najbardziej trafne. Rozwiązanie to opiera się o ciągi liczbowe. Temat
ten omówimy na przykładowych danych z rysunku nr 1
W przykładowych danych mamy daty od – do, między którymi
chcemy znaleźć różnicę. Musimy się zastanowić, czy w różnicy dni chcemy
uwzględniać (jak w poprzednim poście) pierwszy dzień. Objaśnijmy to na
przykładzie dat 2018-01-02 i 2018-01-03. Różnica między datami wynosi 1, ale
jeśli chcemy uwzględnić pierwszy dzień to wtedy wyjdzie nam 2 dni. Jeśli chcemy
uwzględnić pierwszy dzień, od którego zaczynamy liczyć do różnicy w datach
musimy dodać wartość 1. Pierwszym krokiem jest zaczytanie tabelki z
przedziałami do Power Query. W tym celu zaznaczamy dowolną komórkę na obszarze
tej tabelki i wybieramy polecenie Z tabeli (punkt nr 2 na rysunku nr 2) z karty Dane.
Otworzy nam się Edytor zapytań – z wczytaną tabelą tPrzedziały.
Aby dane lepiej się prezentowały musimy zmienić formatowanie – chcemy aby
wyświetlała się tylko data, bez godziny, więc wybieramy polecenie Data z karty
Narzędzia główne (rys. nr 3).
Wyświetli się komunikat o zmianie typu kolumny. Zatwierdzamy
naszą zmianę przyciskiem Zamień bieżącą (rys. nr 4).
Analogicznie postępujemy dla drugiej kolumny, wybieramy
polecenie Data z karty Narzędzia główne i zatwierdzamy w oknie Zmień typ
kolumny nasz wybór. Otrzymamy dane przedstawione na rysunku nr 5.
W Power Query znalazłem funkcję, która wyciąga z danych
tabele i wybrane kolumny. Zapis funkcji wygląda następująco:
Table.SelectColumns(tOdDo{"od","do"})
Z karty Dodaj kolumnę wybieramy polecenie Kolumna
niestandardowa (punkt nr 2 na rysunku nr 6).
Otworzy nam się okno Kolumna niestandardowa, gdzie w okienku
Formuła kolumny niestandardowej wklejamy za pomocą skrótu klawiszowego Ctrl+V
wcześniej przygotowaną funkcję (zaznaczone strzałką na rysunku nr 7). Musimy
pamiętać, że Power Query jest Case sensitive, czyli musimy zwracać uwagę na
wielkość liter przy wpisywaniu nazw funkcji i nazw kolumn. Nasze parametry kolumny
niestandardowej zatwierdzamy przyciskiem OK.
Na skutek naszych działań do każdego wiersza dodała się
tabela – w dole ekranu (rys. nr 8).
Dzięki temu dla każdego wiersza z kolumn Start i Koniec mamy
cała tabelę z datami w kolumnie Niestandardowej. Możemy sobie rozwinąć te
tabele ukryte w dole ekranu, w tym celu naciskamy strzałki w tytule kolumny
Niestandardowe (zaznaczone zieloną strzałką na rysunku nr 9). Pojawi się okno,
gdzie Wybieramy polecenie Rozwiń (punkt 1 na rysunku nr 9), odznaczamy
polecenie Użyj oryginalnej nazwy kolumny jako prefiksu (punkt 2) i nasze
parametry zatwierdzamy klikając przycisk OK.
Power Query zduplikował wiersze z kolumn Start i Koniec, a
następnie do każdego podstawił odpowiednie zakresu Od – Do (rys. nr 10).
Korzystając z rozwiązania wymyślonego przez Billa
Szyszkowskiego, musimy zaznaczyć cała tabelę i zamienić typ danych na liczby
całkowite. Możemy to zrobić jak wyżej w karcie Narzędzia główne, w grupie
poleceń Przekształć (rys. nr 11).
Otrzymamy dane przedstawione na rysunku nr 12.
Pod tymi liczbami całkowitymi kryją się daty. Naszym celem
jest sprawdzenie czy konkretne daty należą do naszych przedziałów. W kolejnym
etapie musimy dodać Kolumnę niestandardową z karty Dodaj kolumnę (identycznie
jak na rysunku nr 6 powyżej). Pojawi nam się okno Kolumna niestandardowa, gdzie
zmieniamy nazwę nowej kolumny na Dni (punkt 1 na rysunku nr 13) i wpisujemy
formułę kolumny niestandardowej (punkt 2 na rysunku nr 13). Formuła powinna
wyglądać następująco: {[od]..[do]}. Zatwierdzamy ustawione parametry kolumny
niestandardowej klikając przycisk OK.
Nowa kolumna będzie wyglądać jak na rysunku nr 14.
Pod każdym wierszem oznaczonym List w kolumnie Dni kryje się lista
dni, co zostało zaprezentowane na rysunku nr 15.
Analogicznie jak w poprzednim przypadku rozwijamy dane, czyli
klikamy strzałki przy nazwie kolumny Dni i wybieramy polecenie Rozwiń do nowych
wierszy (rys. nr 16).
Otrzymamy kolejne powtórzenie wartości – przedziałów. Dane
będą wyglądały jak na rysunku nr 17.
Będziemy teraz sprawdzać czy dzień z kolumny Dni, jest
większy od dnia z kolumny Start i mniejszy bądź równy dacie w kolumnie Koniec.
Najprostszym sposobem na to jest wstawienie kolumny warunkowej. Wybieramy
polecenie Kolumna warunkowa (punkt nr 2 na rysunku nr 18) z karty Dodaj
kolumnę.
Otworzy nam się okno Dodawanie kolumny warunkowej, w której w
miejscu oznaczonym na rysunku nr 19 zieloną strzałką ustawiamy warunki, jakie musi
spełnić konkretna data. W polu Nazwa Kolumny wybieramy kolumnę Dni, następnie w
polu Operator wybieramy, że wartość z kolumny Dni ma być Większa niż lub równa
wartości z kolumny Start (pole Wartość). Jeśli to jest prawda, to w polu
Wartość wyjściowa chcemy otrzymać wartość 1 (czyli chcemy liczyć ten dzień). W
polu W przeciwnym razie chcemy otrzymać 0 (czyli jeśli data nie spełnia warunku
nie chcemy jej liczyć). Tak ustawiony warunek zatwierdzamy klikając przycisk
OK.
Otrzymamy dane przedstawione na rysunku nr 20. Zapomnieliśmy
o zmianie nazwy kolumny, ale dzięki temu że mamy włączony pasek formuły, możemy
zmienić tą nazwę ręcznie na Ile dni (zaznaczone zieloną strzałką na rysunku nr 20).
W kolejnym kroku musimy zmodyfikować funkcję IF ponieważ mamy
tylko jeden warunek (nie mamy narzuconej górnej granicy). Dopisujemy ręcznie w
pasku formuły drugi warunek, czyli że wartość z kolumny Dni ma być mniejsza
bądź równa wartości z kolumny Koniec. Zapis powinien wyglądać następująco:
If [Dni] >= [Start] and [Dni] <= [Koniec] then 1 else 0
W formule tej najważniejsze są znaki równości, które
informują o tym, czy chcemy brać pod uwagę w liczeniu pierwszy dzień (od kiedy
zaczynamy liczyć) i dzień ostatni (rys. nr 21).
Zatwierdzamy zmiany przyciskiem Enter. Otrzymamy dane
przedstawione na rysunku nr 22. Jeśli dany dzień spełnia warunek otrzymujemy
wartość 1, jeśli nie mieści się w przedziale dostajemy wartość 0.
W kolejnym etapie zamieniamy wartości z kolumn Start i Koniec na daty w karcie Narzędzia główne, co zostało pokazane na rysunku nr 23.
Po zmianie typu danych chcemy pogrupować te dane po kolumnach
Start i Koniec. Z karty Narzędzia główne wybieramy polecenie Grupuj według
(punkt nr 2 na rysunku nr 24).
Otworzy nam się okno Grupowanie według. Power Query
automatycznie sam wykrył, że chcemy grupować po kolumnach Start i Koniec
(ponieważ były zaznaczone po wcześniejszej operacji). W miejscu oznaczonym
strzałką na rysunku nr 25 wybieramy typ Operacji, jaki chcemy wykonać, czyli
Suma oraz kolumnę po jakiej ma zostać wykonana ta operacja – Ile dni. Zmieniamy
nazwę nowej kolumny na Ile dni. Nasze ustawienia zatwierdzamy przyciskiem OK.
Otrzymamy dane przedstawione na rysunku nr 26.
Następnie z karty Narzędzia główne wybieramy polecenie
Zamknij i załaduj do, aby wczytać dane do Excela (rys. nr 27).
Otworzy nam się w Excelu okno Ładowanie do. Zaznaczamy, że
chcemy umieścić dane w Tabeli (punkt nr 1 na rysunku nr 28), w Istniejącym
arkuszu (punkt nr 2) i wybieramy konkretną komórkę (punkt nr 3). Nasze
parametry zatwierdzamy przyciskiem Załaduj.
Dane w Excelu zostały zaprezentowane na rysunku nr 29.
Z rozwiązań, które przetestowałem, to z użyciem funkcji If
jest najkrótsze, ma najmniejsza ilość kroków w tworzeniu zapytania, ale
jednocześnie najbardziej skomplikowane (rys 30).
Jest wiele warunków, dodatkowo musimy dodawać wartość 1 jeśli
chcemy uwzględniać pierwszy dzień. Wartość 1 możemy dodać do liczby a nie do
daty, więc musimy skorzystać dodatkowo z funkcji Number.From. Może ten sposób
jest szybszy od tego, który przedstawiłem, ale jest bardziej skomplikowany i
przeznaczony dla osób bardziej zaawansowanych w 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.
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ę wyznaczać ilość dni w
miesiącu pomiędzy datami w Power Query. Odcinek ten powstał jako odpowiedź na
komentarz Billa Szyszkowskiego do postu z pytaniami od widzów nr 121,
poruszającego ten sam problem. Wyznaczymy ilość dni pomiędzy datami w danym
miesiącu ale tym razem za pomocą Power Query. Musimy pamiętać, że kiedy w
Excelu mamy daty to tak naprawdę mamy do czynienia z liczbami, bo daty w Excelu
to liczby. Bill Szysz zwrócił uwagę, że czasami nie uwzględniamy tego pierwszego
dnia, np. między datami 2018-01-02 a 2018-01-04 są 2 dni różnicy, ale jeśli
policzymy to dokładnie to mamy 2018-01-02, 2018-01-03 i 2018-01-04, a więc 3
dni. W zależności czy liczymy ten pierwszy dzień czy nie to otrzymujemy różne
wyniki. Bill Szysz zaproponował rozwiązanie tego problemu w Power Query.
Zagadnienie to omówimy na przykładowych danych z rysunku nr 1.
Standardowo mamy dane umieszczone w tabeli i w Excelu od 2016
wybieramy polecenie Z tabeli (punkt nr 2 na rysunku nr 2) z karty Dane, aby zaczytać
dane do Power Query.
Otworzy nam się okno Edytora zapytań dla danych zaczytanych z
tabeli tDaty. Musimy pamiętać, że Power Query chce zapisać dane jako daty, a my
chcemy skorzystać z liczb. Klikamy ikonkę (oznaczoną zieloną strzałką na
rysunku nr 3) przy nazwie kolumny Od i z podręcznego menu wybieramy polecenie
Liczba całkowita.
Pojawi się nam komunikat o zmianie typu kolumny, gdzie musimy
kliknąć przycisk Zamień bieżącą (rys. nr 4).
Tę samą czynność powtarzamy dla kolumny obok nazwanej Do. Klikamy
ikonkę przy jej nazwie i z podręcznego menu wybieramy polecenie Liczba
całkowita. Pojawi nam się komunikat o zmianie typu kolumny, gdzie klikamy
przycisk Zamień bieżącą. Otrzymamy dane przedstawione na rysunku nr 5.
W kolejnym kroku wybieramy polecenie Kolumna niestandardowa
(punkt nr 2 na rysunku nr 6) z karty Dodaj kolumnę.
Pojawi nam się okno Kolumna niestandardowa, w której zmienimy
nazwę kolumny na Miesiąc, a następnie w okienku formuły kolumny niestandardowej
wpiszemy ={[od]..[do]}. Następnie nasze ustawienia zatwierdzamy klikając
przycisk OK (rys. nr 7).
Musimy tutaj pamiętać , że pod wartościami [od] i [do] kryją
się liczby. Otrzymamy dane przedstawione na rysunku nr 8, gdzie dla każdego
wiersza w kolumnie Miesiąc mamy listę liczb między tymi datami – zapisanymi też
jako liczby.
Dla pierwszego wiersza lista ta została przedstawiona na
rysunku nr 9.
W kolejnym kroku zaznaczamy kolumnę Miesiąc, klikamy prawym
przyciskiem myszy na nazwę tej kolumny i
z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 11).
Zostanie nam jedna kolumna. Następnie klikamy ikonkę
oznaczoną na rysunku nr 12 zieloną strzałką i z podręcznego menu wybieramy
polecenie Rozwiń do nowych wierszy.
Otrzymamy listę wszystkich dni pomiędzy datami (rys. nr 13).
Nas interesuje konkretnie ilość dni w danym miesiącu, więc w
pierwszym kroku musimy znowu zmienić typ danych na daty. Rozwijamy ikonkę przy
nazwie kolumny i z podręcznego menu wybieramy polecenie Data (rys. nr 14).
Otrzymamy listę dni w postaci dat, przedstawioną na rysunku
nr 15 (jej fragment).
Przy tak przygotowanych danych wybieramy polecenie Data
(punkt nr 2 na rysunku nr 16) z karty Dodaj kolumnę.
Za pomocą tego polecenia możemy z daty wyciągać różne
parametry, takie jak rok, kwartał, miesiąc itd. My wybieramy Rok (rys. nr 17),
następnie analogicznie wybieramy Miesiąc.
Otrzymamy dane przedstawione na rysunku nr 18.
W kolejnym kroku z karty Przekształć wybieramy polecenie
Data, a następnie Miesiąc i Nazwa miesiąca (po kolei kroki oznaczone na rysunku
nr 19).
Kiedy korzystamy z poleceń z karty Dodaj kolumnę to powstaje
nowa kolumna, jeśli zaś korzystamy z polecenia z karty Przekształć, to działamy
na istniejącej kolumnie i w niej wprowadzamy zmiany. Otrzymamy dane
przedstawione na rysunku nr 20.
Zaznaczamy wszystkie trzy kolumny a następnie wybieramy
polecenie Grupowanie według (punkt nr 2 na rysunku nr 21) z karty Narzędzia
główne.
Otworzy
nam się okno Grupowanie według, gdzie wybieramy grupowanie Zawansowane (punkt
nr 1 na rysunku nr 22). Automatycznie pojawią nam się trzy poziomy grupowania
(punkt nr 2). Następnie nazywamy nową kolumnę Ile dni (punkt nr 3) i wybieramy
operację Zlicz wiersze (punkt nr 4), która ma zliczyć dni z danego miesiąca.
Nie interesuje nas jakie to były dni tylko ich ilość. Nasze parametry
zatwierdzamy przyciskiem OK.
Otrzymamy pogrupowane dane według miesięcy przedstawione na
rysunku nr 23.
Tak przygotowane dane chcemy załadować do Excela, więc
korzystamy z polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 24).
Otworzy nam się okno Ładowanie do, gdzie ustawiamy parametry wstawianych danych, sposób wyświetlania danych jako Tabela i miejsce wstawienia danych jako istniejący arkusz i wybieramy konkretną komórkę, gdzie chcemy umieścić nasze dane. Parametry zatwierdzamy przyciskiem Załaduj (rys. nr 25).
Otrzymamy dane przedstawione na rysunku nr 26.
W naszych wczytanych dołożyliśmy Rok tylko dla sytuacji kiedy
mamy w danych przeskok lat. Przygotowałem sobie dodatkowy wiersz, który kiedy
dodam do tabeli z pierwotnymi danymi, a następnie naszą tabele z Power Query
odświeżę – klikamy prawym przyciskiem myszy na dane (dowolną komórkę w tabeli
zwróconej przez zapytanie Power Query), a następnie z podręcznego menu wybieramy
polecenie Odśwież (rys. nr 27).
Otrzymamy przeliczone dane z uwzględnionymi miesiącami w
kolejnym roku (który dołożyliśmy do tabeli i danymi bazowymi), przedstawione na
rysunku nr 28.
Podsumowując
Power Query świetnie sprawdza się w wyznaczaniu ilości dni między datami w
danym miesiącu. Rozwiązanie jest dużo szybsze i prostsze niż w Excelu
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.