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.


Przedsprzedaż Mistrz Excela II wydanie

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 przedsprzedaży możesz mieć Mistrza Excela w obniżonej cenie:
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 przedsprzedaży możesz mieć całą książkę za jedyne 30 zł (łącznie z darmową wysyłką listem ekonomicznym), a jeśli zależy Ci na dedykacji wystarczy, że dołożysz 10 zł.

Książka Mistrz Excela reklama