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