W dzisiejszym poście omówimy różnice pomiędzy poleceniami
Usuń inne kolumny a Usuń wybrane kolumny w Power Query. Tak naprawdę będą to
różnice pomiędzy funkcjami, które kryją się pod tymi poleceniami, czyli
odpowiednio Table.RemoveColumns oraz Table.SelectColumns. Zagadnienie to
omówimy na podstawie przykładowych danych z rysunku nr 1.
rys. nr 1 — Przykładowe dane
Aby obrazowo wyjaśnić temat – chodzi o to, że mamy za dużo kolumn z danymi i po prostu część z nich chcemy usunąć lub chcemy usunąć wszystkie oprócz zaznaczonych. Mamy tabelę danych w Excelu, zaznaczamy w jej obszarze dowolną komórkę, a następnie wybieramy polecenie Z tabeli (punkt nr 2 na rysunku nr 2) z karty Dane.
rys. nr 2 — Z tabeli
Otworzy nam się edytor zapytań w Power Query z wczytaną
tabelą tSprzedaż (rys. nr 3).
rys. nr 3 — Edytor zapytań
Przede wszystkim, aby dane dobrze się prezentowały musimy
zmienić format danych z kolumny Data. Klikamy na ikonkę po lewej stronie nazwy
kolumny Data a następnie z podręcznego menu wybieramy polecenie Data (rys. nr 4).
rys. nr 4 — Zmiana typu wyświetlania danych
Wyświetli się nam komunikat dotyczący zmiany typu kolumny, w
którym musimy zatwierdzić zmianę klikając przycisk Zamień bieżącą (rys. nr 5).
rys. nr 5 — Komunikat o zmianie typu kolumny
Zakładamy, że w naszym przypadku interesują nas trzy kolumny:
Data, Sprzedawca i Zysk. Zaznaczamy pozostałe kolumny (od kolumny Region do
kolumny KWS) i klikamy prawym przyciskiem myszy na nagłówek dowolnej kolumny z
tych zaznaczonych i z podręcznego menu wybieramy polecenie Usuń kolumny (rys.
nr 6)
rys. nr 6 — Usuń kolumny
Otrzymamy interesujące nas kolumny (rys. nr 7). W pasku
formuły pokaże się nam funkcja Table.RemoveColumns odpowiadająca poleceniu Usuń
kolumny.
rys. nr 7 funkcja Table.RemoveColumns
Jeśli nie mamy widocznego paska formuły możemy to łatwo
zmienić, klikając checkbox przy poleceniu Pasek formuły na karcie Widok
(zaznaczone na rysunku nr 8).
rys. nr 8 — Pasek formuły
Na pasku formuły dla funkcji Table.RemoveColumns w formule
mamy wypisane nazwy kolumn, które usunęliśmy (rys. nr 9).
rys. nr 9 — Argumenty funkcji Table.RemoveColumns
W Power Query w formułach funkcji są podawane nazwy kolumn. Wystarczy, że nazwa kolumny ulegnie zmianie i pojawiają się błędy.
Możemy usunąć ostatni krok z Zastosowanych kroków, aby wrócić do danych wejściowych. Na danych wejściowych pokażemy bowiem drugie polecenie, które moim zdaniem lepiej się sprawdza, czyli polecenie Usuń inne kolumny. Zaznaczamy kolumny, które nas interesują (które chcemy zostawić). Zaznaczamy w pierwszej kolejności kolumnę Sprzedawca, następnie przytrzymując klawisz Ctrl Kolumnę Data i Zysk. Klikamy na tytuł którejś z tych zaznaczonych kolumn i z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 10).
rys. nr 10 — Usuń inne kolumny
Po użyciu tego polecenia zostały usunięte kolumny, które nie
były zaznaczone (rys. nr 11).
rys. nr 11 — Dane po usunięciu kolumn
W pasku formuły pojawi się zapis funkcji Table.SelectColumns, w której mamy wypisane nazwy kolumn, które nas interesują (rys. nr 12). Przy tej funkcji nie interesuje nas, że ktoś dołoży kolejną kolumnę do danych wejściowych. Nowa kolumna nie zostanie tutaj uwzględniona i nasze końcowe dane się nie zmienią.
rys. nr 12 — Zapis funkcji Table.SelectColumns
Bez względu na zmianę w danych wejściowych ilość
wyświetlanych kolumn się nie zmieni. Kolejnym plusem jest to, że kolumny są wyświetlane
w takiej kolejności w jakiej je zaznaczyliśmy przed użyciem polecenia Usuń inne
kolumny. Możemy również ręcznie zmienić kolejność wyświetlania kolumn w kodzie
w pasku formuły.
W Power Query, jeśli znamy nazwę interesującej nas funkcji i
wpiszemy ją w pasku formuły a następnie zatwierdzimy przyciskiem Enter, to
wyświetlą nam się informacje o tej funkcji z oficjalnej strony Microsoftu (rys.
nr 13).
Możemy się tutaj dowiedzieć, że funkcja ta posiada dodatkowy
parametr missingField, który może przyjąć jedną z trzech wartości. Będzie się
wyświetlał błąd jeśli podaliśmy złą nazwę kolumny lub zignoruje ten brak albo
wypełni je wartościami null. Poniżej mamy także podane przykłady działania tej
funkcji.
Zrobimy przykład, w którym dopiszemy do formuły
MissingField.UseNull i zmienimy nazwy kolumn Data i Zysk na Data2 i Zysk2,
czyli na takie które nie występują w danych. Power Query wypełni je wartościami
null (rys. nr 14).
rys. nr 14 — Dane wypełnione wartościami null
Jeśli do zapisu formuły użyjemy MissingField.Ignore to
zostanie nam tylko kolumna ze Sprzedawcami. Jeśli nie podamy tego parametru, to
Power Query domyślnie wybierze wyświetlenie błędu (rys. nr 15).
rys. nr 15 — Domyślny komunikat jeśli nie podamy parametru MissingField
Ja wole funkcje Table.SelectColumns czyli usunięcie innych
kolumn a pozostawienie tych, które są mi potrzebne. Przede wszystkim dlatego,
że dodawanie innych kolumn nie wpłynie na wynik tej funkcji. Łatwiej jest
korzystać z tych domyślnych opcji na pasku narzędzi niż z kodu w pasku formuły,
ponieważ przy słabej znajomości języka M w Power Query możemy nie wiedzieć jak
wpisać poszczególne parametry danej funkcji.
Wynik tej funkcji z rysunku nr 11 możemy sobie załadować do
Excela za pomocą polecenia Zamknij i załaduj z karty Narzędzia główne (rys. nr 16).
rys. nr 16 — Zamknij i załaduj
Otrzymamy dane wczytane do Excela przedstawione na rysunku nr 17.
rys. nr 17 — Dane wczytane do Excela
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
rys. nr 1 — Przykładowe dane
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.
rys. nr 2 — Wczytanie danych do Power Query
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).
rys. nr 3 — zmiana typu danych
Wyświetli się komunikat o zmianie typu kolumny. Zatwierdzamy
naszą zmianę przyciskiem Zamień bieżącą (rys. nr 4).
rys. nr 4 — Zmień typ kolumny
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.
rys. nr 5 — Dane po zmianie typu danych
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).
rys. nr 6 — Kolumna niestandardowa
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.
rys. nr 7 — Parametry kolumny niestandardowej
Na skutek naszych działań do każdego wiersza dodała się
tabela – w dole ekranu (rys. nr 8).
rys. nr 8 — Nowa kolumna
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.
rys. nr 9 — Rozwijanie kolumny
Power Query zduplikował wiersze z kolumn Start i Koniec, a
następnie do każdego podstawił odpowiednie zakresu Od – Do (rys. nr 10).
rys. nr 10 — Rozwinięte dane
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).
rys. nr 11 — Zmiana typu danych na Liczby całkowite
Otrzymamy dane przedstawione na rysunku nr 12.
rys. nr 12 — Daty w postaci liczb
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.
rys. nr 13 — Kolumna niestandardowa
Nowa kolumna będzie wyglądać jak na rysunku nr 14.
rys. nr 14 — Dane z nową kolumną
Pod każdym wierszem oznaczonym List w kolumnie Dni kryje się lista
dni, co zostało zaprezentowane na rysunku nr 15.
rys. nr 15 — Dane z wiersza List
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).
rys. nr 16 Rozwiń do nowych wierszy
Otrzymamy kolejne powtórzenie wartości – przedziałów. Dane
będą wyglądały jak na rysunku nr 17.
rys. nr 17 — Dane po rozwinięciu kolumny Dni
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ę.
rys. nr 18 — Dodawanie kolumny warunkowej
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.
rys. nr 19 — Parametry kolumny warunkowej
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).
rys. nr 20 — Dane z kolumną warunkową
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).
rys. nr 21 — Znaki większy lub równy i mniejszy lub równy
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.
rys. nr 22
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.
rys. nr 23 — Zmiana typu danych
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).
rys. nr 24 — Grupowanie według
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.
rys. nr 25 — Parametry grupowania
Otrzymamy dane przedstawione na rysunku nr 26.
rys. nr 26 — Pogrupowane dane
Następnie z karty Narzędzia główne wybieramy polecenie
Zamknij i załaduj do, aby wczytać dane do Excela (rys. nr 27).
rys. nr 27 — Zamknij i załaduj do
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.
rys. nr 28 — Okno ładowanie do
Dane w Excelu zostały zaprezentowane na rysunku nr 29.
Dane z Power Query wczytane do Excela
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).
rys. nr 30 — Skomplikowana formuła
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.
rys. nr 1 — Przykładowe dane
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.
rys. nr 2 — Polecenie Z tabeli
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.
rys. nr 3 — Zmiana formatu danych
Pojawi się nam komunikat o zmianie typu kolumny, gdzie musimy
kliknąć przycisk Zamień bieżącą (rys. nr 4).
rys. nr 4 — Komunikat o zmianie typu kolumny
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.
rys. nr 5 — Dane po zmianie z daty na format liczbowy
W kolejnym kroku wybieramy polecenie Kolumna niestandardowa
(punkt nr 2 na rysunku nr 6) z karty Dodaj kolumnę.
rys. nr 6 — Kolumna niestandardowa
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).
rys. nr 7 — Parametry kolumny niestandardowej
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.
rys. nr 8 — Kolumna niestandardowa Miesiąc
Dla pierwszego wiersza lista ta została przedstawiona na
rysunku nr 9.
rys. nr 9 — Dni kryjące się w tabeli pod nazwą List
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).
rys. nr 11 — Usuń inne kolumny
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.
rys. nr 12 — Rozwiń do nowych wierszy
Otrzymamy listę wszystkich dni pomiędzy datami (rys. nr 13).
rys. nr 13 — Lista dni między datami
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).
rys. nr 14 — Zmiana typy wyświetlania danych
Otrzymamy listę dni w postaci dat, przedstawioną na rysunku
nr 15 (jej fragment).
rys. nr 15 — Lista dni między datami w postaci dat
Przy tak przygotowanych danych wybieramy polecenie Data
(punkt nr 2 na rysunku nr 16) z karty Dodaj kolumnę.
rys. nr 16 — Polecenie Data
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.
rys. nr 17 — Dodanie kolumny Rok
Otrzymamy dane przedstawione na rysunku nr 18.
rys. nr 18 — Dane z dodaną kolumną Rok
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).
rys. nr 19 — Dodawanie kolumny Miesiąc
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.
rys. nr 20 — Dane z dodaną kolumną Miesiąc
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.
rys. nr 21 — Grupowanie według
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.
rys. nr 22 — Parametry w oknie Grupowanie według
Otrzymamy pogrupowane dane według miesięcy przedstawione na
rysunku nr 23.
rys. nr 23 — Pogrupowane dane
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).
rys. nr 24 — Zamknij i załaduj
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).
rys. nr 25 — parametry okna Ładowanie do
Otrzymamy dane przedstawione na rysunku nr 26.
rys. nr 26 — Dane załadowane do Excela
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).
rys. nr 27 — Odśwież zaczytane dane
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.
rys. nr 27 — Odświeżone dane
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.
