W Excelu daty są przechowywane jako wartości liczbowe. Jest to dobre rozwiązanie, umożliwiające chociażby łatwe wykonywanie działań matematycznych na datach. Jednak system ten nie jest idealny i wynika z tego pewien drażniący problem. Programiści Excela rozpoczęli numerowanie dat od 1. stycznia 1900 roku przypisując tej dacie wartość 1. Każdy kolejny dzień to wielokrotność tej wartości. W momencie tworzenia tego wpisu numer aktualnego dnia to 43 512. W przypadku operowania datami po 1900-01-01 nie jesteśmy w żaden sposób ograniczeni. Problem pojawia się gdy chcemy działać na datach wcześniejszych. Nie mogą one bowiem przyjmować wartości ujemnych. Ustawiając format daty na ujemnych wartościach Excel wyświetla „######”, mimo że w komórce de facto mamy liczbę. Dodatkowo na takich wartościach nie możemy prowadzić działań matematycznych. Możemy przechowywać je jedynie jako tekst.
Lista dat przed 1900-01-01 w Power Query
Chcąc w Excelu szybko utworzyć listę pierwszych dni miesiąca, przykładowo dla lat 1790 do 1850, zetkniemy się ze wspomnianym problemem dla ujemnych wartości. Opiszę jak łatwo rozwiązać ten kłopot za pomocą mojego ulubionego narzędzia – Power Query 🙂
Dla jasności – moja metoda pozwoli na szybkie utworzenie listy dat sprzed 1900 roku przechowywanych jako tekst. Nie rozwiąże ona problemu dotyczącego wykonywania działań na tych datach. Tutaj mogę polecić rozwiązanie stworzone przez Johan Walkenbacha.
Wracając do Power Query to rozpoczynamy od utworzenia pustego zapytania. Wybieramy kolejno z karty Dane > Pobierz dane > Z innych źródeł > Puste zapytanie.
Lata
W pierwszym kroku skorzystamy z funkcji List.Repeat, która stworzy listę lat bez powtórzeń dla zakresu od 1790 do 1850 roku. W pasku formuły wpisujemy:
= List.Repeat({1790..1850},1)
Następnie pobieramy dane z listy do tabeli klikając pierwszy przycisk po lewej stronie wstążki Power Query. W oknie, które się pojawi, nie zmieniamy domyślnych opcji i zatwierdzamy przyciskiem OK. Dla lepszej orientacji nazwę kolumny zmieniamy na „Rok”.
Miesiące
W kolejnym kroku dodamy dane dotyczące miesięcy. W tym celu tworzymy kolumnę niestandardową. Jej nazwę zmieniamy na „Miesiąc” i w miejsce formuły wstawiamy funkcję przypisującą każdemu wierszowi obiekt listy z wartościami od 1 do 12:
= Table.AddColumn(#"Zmieniono nazwy kolumn", "Miesiąc", each Table.FromList(List.Repeat({1..12}, 1), Splitter.SplitByNothing(), null, null, ExtraValues.Error))
Każdej komórce zawierającej rok został przypisany obiekt typu tabela przechowujący wartości od 1 – 12. W celu wyodrębnienia tych danych wciskamy małą ikonkę z dwoma strzałkami w nagłówku kolumny. Odznaczamy opcję „Użyj oryginalnej nazwy kolumny jako prefiksu” i wciskamy „OK”. Widzimy teraz, że każdy rok został powielony 12 razy, o wartości oznaczające kolejne miesiące.
Jednak to jeszcze nie wszystko co musimy zrobić z numerami miesięcy. Wiemy, że w domyślnym formacie dat, czyli rrrr-mm-dd, miesiące od stycznia do września powinny posiadać w numeracji tzw. zera wiodące. W celu ich dodania kolejny raz wstawiamy kolumną niestandardową. Tym razem korzystamy z funkcji
=Text.PadStart(Text.From([Column1]),2,"0")
Doda ona jedno 0 przed każdą jednocyfrową liczbą. Następnie usuwamy niepotrzebną już środkową kolumnę.
Dni
W przedostatnim krokiem naszego zapytania dodajemy kolumnę „Dzień”. Robimy to również z wykorzystaniem kolumny niestandardowej. Zależy nam tylko na pierwszych dniach miesiąca więc jako formułę pisujemy po prostu:
=”01”
Finalizacja
Ostatni krok to połączenie 3 kolumn w jedną. Jako ogranicznik stosowany w datach przyjmujemy znak pauzy „-”. Najpierw zaznaczamy z wciśniętym klawiszem Ctrl wszystkie 3 kolumny. Następnie po kliknięciu prawym przyciskiem myszy wybieramy opcję „Scal kolumny”. Wybieramy niestandardowy separator „-” oraz zmieniamy nazwę nowej kolumny.
Po wykonaniu ostatniej czynności ładujemy dane z naszego zapytania do arkusza Excel. W efekcie otrzymujemy 732 wiersze co jest odpowiednikiem 61 lat po 12 miesięcy.
W przypadku gdy chcemy mieć listę lat i miesięcy bez dat wystarczy pominąć przedostatni krok z dodawaniem kolumny z pierwszym dniem miesiąca. Dodatkowo parametry zapytania mogą być przez nas dowolnie zmieniane i stosowane nie tylko dla dat przed 1900.
Poniżej wstawiam pełne treści zapytań w języku M. Wystarczy je wkleić do edytora zaawansowanego i lista dat przed 1900-01-01 w Power Query zostanie utworzona.
Przeczytaj również jak utworzyć listę liczb za pomocą funkcji użytkownika napisanej w VBA.
Pełny kod zapytania w języku M dla opcji Rok-Miesiąc-Dzień
let
Źródło = List.Repeat({1790..1850},1),
#"Przekonwertowane na tabelę" = Table.FromList(Źródło, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Zmieniono nazwy kolumn" = Table.RenameColumns(#"Przekonwertowane na tabelę",{{"Column1", "Rok"}}),
#"Dodano kolumnę niestandardową" = Table.AddColumn(#"Zmieniono nazwy kolumn", "Miesiąc", each Table.FromList(List.Repeat({1..12}, 1), Splitter.SplitByNothing(), null, null, ExtraValues.Error)),
#"Rozwinięty element Miesiąc" = Table.ExpandTableColumn(#"Dodano kolumnę niestandardową", "Miesiąc", {"Column1"}, {"Column1"}),
#"Dodano kolumnę niestandardową1" = Table.AddColumn(#"Rozwinięty element Miesiąc", "Miesiąc", each Text.PadStart(Text.From([Column1]),2,"0")),
#"Usunięto kolumny" = Table.RemoveColumns(#"Dodano kolumnę niestandardową1",{"Column1"}),
#"Dodano kolumnę niestandardową2" = Table.AddColumn(#"Usunięto kolumny", "Dzień", each "01"),
#"Scalono kolumny" = Table.CombineColumns(Table.TransformColumnTypes(#"Dodano kolumnę niestandardową2", {{"Rok", type text}}, "pl-PL"),{"Rok", "Miesiąc", "Dzień"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Rok-Miesiąc-Dzień")
in
#"Scalono kolumny"
Pełny kod zapytania w języku M dla opcji Rok-Miesiąc
let
Źródło = List.Repeat({1790..1850},1),
#"Przekonwertowane na tabelę" = Table.FromList(Źródło, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Zmieniono nazwy kolumn" = Table.RenameColumns(#"Przekonwertowane na tabelę",{{"Column1", "Rok"}}),
#"Dodano kolumnę niestandardową" = Table.AddColumn(#"Zmieniono nazwy kolumn", "Miesiąc", each Table.FromList(List.Repeat({1..12}, 1), Splitter.SplitByNothing(), null, null, ExtraValues.Error)),
#"Rozwinięty element Miesiąc" = Table.ExpandTableColumn(#"Dodano kolumnę niestandardową", "Miesiąc", {"Column1"}, {"Column1"}),
#"Dodano kolumnę niestandardową1" = Table.AddColumn(#"Rozwinięty element Miesiąc", "Miesiąc", each Text.PadStart(Text.From([Column1]),2,"0")),
#"Usunięto kolumny" = Table.RemoveColumns(#"Dodano kolumnę niestandardową1",{"Column1"}),
#"Scalono kolumny" = Table.CombineColumns(Table.TransformColumnTypes(#"Usunięto kolumny", {{"Rok", type text}}, "pl-PL"),{"Rok", "Miesiąc"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Rok-Miesiąc")
in
#"Scalono kolumny"
Loading...
Nie, żebym się czepiał (bo zapytanie robi co trzeba) ale….. masz tam trochę nadmiarowego kodu w Twoim zapytaniu (np zamiast List.Repeat({1790..1850},1) wystarczy {1790..1850} ale również w innych miejscach).
Ze swej strony proponowałbym prawdziwe daty w PQ czyli:
let
Source = Table.FromRecords(List.Generate( ()=> [Daty = #date(1790,1,1)],
each [Daty] <= #date(1850,12,1),
each [Daty = Date.AddMonths([Daty], 1)]
))
in
Source
a jeśli mają być teksty to dodamy krok zmieniający typ czyli:
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Daty", type text}})
Pozdrawiam ;-))
Fajnie, że czuwasz 🙂 Wiele nauki jeszcze przede mną.
Co do zapytania zaproponowanego przez Ciebie, czyli w całości:
letSource = Table.FromRecords(List.Generate( ()=> [Daty = #date(1790,1,1)],
each [Daty] <= #date(1850,12,1), each [Daty = Date.AddMonths([Daty], 1)] )), #"Changed Type" = Table.TransformColumnTypes(Source, {{"Daty", type text}}) in #"Changed Type"
Jako wynik zapytanie zwraca daty widoczne jako dd.mm.rrrr, które po załadowaniu do arkusza nie są możliwe do formatowania jako rrrr-mm-dd
Pozdrawiam
To, że widzisz dd.mm.rrrr zamiast rrrr-mm-dd wynika z ustawień regionalnych systemu operacyjnego. Podejrzewam, że korzystasz z Windows 10 gdzie domyślnym formatem daty jest własnie dd.mm.rrrr.
U mnie na lapku (win 8.1) wyrzucone zostają do arkusza daty jako tekst w formacie rrrr-mm-dd.
Zeby uniezależnić sie od wersji systemu operacyjnego proponuję zastąpić drugi krok tym poniżej
#”Changed Type” = Table.TransformColumns(Source, {{„Daty”,each Date.ToText(_, „yyyy-MM-dd”), type text}})
Pozdrawiam 🙂
Właśnie ustawienia regionalne mam ustawione na rrrr-mm-dd stąd moje zdziwienie.
Niezależnie od tego zmiana kodu w drugim kroku, na podany przez Ciebie, rozwiązuje problem.
Wrzucam pełną treść zapytania, być może kiedyś komuś pomoże 🙂
letSource = Table.FromRecords(List.Generate( ()=> [Daty = #date(1790,1,1)],
each [Daty] <= #date(1850,12,1), each [Daty = Date.AddMonths([Daty], 1)] )), #"Changed Type" = Table.TransformColumns(Source, {{"Daty",each Date.ToText(_, "yyyy-MM-dd"), type text}}) in #"Changed Type"
Swoją drogą to świetna jest ta opcja tworzenia list w Power Query. Nie ma konieczności tworzenia ich w Excelu i późniejszego zaczytywania do zapytań, wystarczy bezpośrednio wygenerować je z poziomu PQ 🙂
Dzięki za komentarze i również pozdrawiam.
Witam, osobiście całkowicie zgadzam się z powyższym !
Dzięki za komentarz 🙂