Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - Uporządkowanie wieloletniej serii dat z danymi

naat3 · Forumowicz Posty: 17

Witam.
Mam problem z uporządkowaniem wieloletniej serii dat (z okresu 1961-2021).
Do każdej daty (pojedynczy dzień) przyporządkowane są godzinne dane.
Niestety w serii dat zdarzają się brakujące dni (załącznik, arkusz przykład, np: - 5 i 6.01.1961 lub 13.01.1961 lub 24-26.01.1961 )
Te braki są w różnych miejscach, a rekordów do sprawdzenia w Excelu jest ponad 20 000.
Chodzi o to żeby stworzyć takie makro, które samo porządkowałoby serię danych, wstawiając chronologicznie brakującą datę, oraz pusty wiersz (załącznik, arkusz wynik).
Czy jest to możliwe?
Czy ktoś mógłby mi pomóc?
Ręcznie to sprawdzając to można się załamać, a mam do przeanalizowania jeszcze kilkanaście takich wieloletnich serii danych.

przykład i wynik.xlsx

Pobierz Plik ściągnięto 19 raz(y) 16.9 KB

caelian · Wysłany: 26-03-2021, 17:10

Cześć
a takie rozwiązanie?

tylko tak:
1) Pamietaj że data a tekst wyglądający jak data to 2 różne rzeczy.
Excel, może ale nie musi rozpoznać formatu jako excelowa datę.
W takim wypadku skopiuj dowolną pustą komórkę
zaznacz daty
wklej specjalnie i wybierz dodaj , odejmij (bez różnicy)
wizualnie nic się nie powinno stać a excel skonwertuje tekst na właściwą datę.

2) w twoim pliku zamieniłem formatowanie daty na ogólne. to możesz łatwo zmienić.
3) zaleta tego rozwiązania jest to że tabelę źródłową możesz podmienić w miejscu docelowym (nie usuwając arkusza z danymi źródłowymi).
4) dałem ci też w rozwiązaniu kilka opcji.

przykład i wynik.xlsx

Pobierz Plik ściągnięto 11 raz(y) 40.89 KB

Marecki · Wysłany: 26-03-2021, 21:12

Zobacz taki kod:

Kod:

Sub Test()
Dim vArr() As Variant
Dim vArr_tmp() As Variant
Dim vArr_out() As Variant
Dim lRow As Long
Dim i As Long
Dim j As Long
Dim Tmp As Variant
Dim Data_od As Long
Dim Data_do As Long
Dim Poz As Variant
Dim k As Long

With Sheets("przykład")
lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
vArr = .Range("A3:Y" & lRow).Value

For i = 1 To UBound(vArr)
Tmp = Split(vArr(i, 1), ".")
vArr(i, 1) = CLng(DateSerial(Tmp(2), Tmp(1), Tmp(0)))
Next i
.Range("A3").Resize(lRow - 2).Value = vArr

Data_od = .Range("A3").Value
Data_do = .Range("A" & lRow).Value
vArr_tmp = Application.Index(vArr, 0, 1)

ReDim vArr_out(1 To Data_do - Data_od + 1, 1 To 25)

For i = Data_od To Data_do
k = k + 1
Poz = Application.Match(i, vArr_tmp, 0)
If IsError(Poz) Then
vArr_out(k, 1) = i
Else
For j = 1 To 25
vArr_out(k, j) = vArr(Poz, j)
Next j
End If
Next i
End With

Sheets("wynik").Range("A3").Resize(k, 25) = vArr_out
Sheets("wynik").Columns(1).NumberFormat = "dd.mm.yyyy"

End Sub

naat3 · Forumowicz Posty: 17

Jestem wdzięczny za zainteresowanie moim problemem. Jeżeli chodzi o Caeliana to dziękuję za informacje odnośnie konwersji komórek z nieprawidłową datą tekstową na właściwą to przetestowałem jego wskazówki. Faktycznie wychodzi bez problemu ale na excelu 2013 lub wyższym. Na excelu 2007 tego się nie dało zrobić. Cealian - dziękuję za zaproponowane rozwiązania aż w 3 wariantach przy pomocy formuł dynamicznych (nie miałem o nich wcześniej pojęcia). Na razie po wstępnym przetestowaniu tych formuł nowe dane wchodzą ale nie do końca.Muszę się jeszcze temu przyjrzeć i bardzo dziękuję za pomoc.

Bardzo dziękuję Mareckiemu. Wypróbowałem Twoje makro i zadziałało!. O to mi chodziło. Zaoszczędzi mi to masę czasu. Trzeba było tylko mieć 2 arkusze o nazwie "przykład" i "wynik" ( i w tym pierwszym arkuszu wklejać dane źródłowe). Makro Twoje funkcjonuję wykorzystując i konwertując nieprawidłowe daty tekstowe. Jak próbowałem uruchomić Twoje makro z serią dat sformatowanych jako prawidłowa data w excelu to makro już nie zadziałało. Stąd moje pytanie - prośba do Mareckiego - czy można stworzyć makro, które porządkuje wieloletnią serię danych gdzie daty są rzeczywistymi prawidłowymi datami excela ? I czy może takie makro funkcjonować w obrębie jednego arkusza - czyli danych źródłowych. Z góry dziękuję za pomoc.

Serdecznie Pozdrawiam i dziękuję obu Panom
naat3

ąćęłńóś · Excel Expert **Pomógł:** 327 razy Posty: 1615

Czym się objawiło/objawia to "niezadziałanie" makra Mareckiego ?

Marecki · Wysłany: 27-03-2021, 17:25

naat3 · Forumowicz Posty: 17

Marecki - jesteś niesamowity, bardzo dziękuję. Wypróbowałem te drugie makro na ponad 20 000 rekordów serii danych i makro zadziałało bezbłędnie. Mój pierwszy post oczywiście przedstawiał prawdziwy typ danych - czyli serię nieprawidłowych dat tekstowych z danymi. Natomiast moja druga prośba dotyczyła drugiego typu danych z którymi też mam do czynienia i muszę je porządkować - czyli serie dat sformatowanych prawidłowo jako data w excelu.

Także zarówno Twoje pierwsze makro jak i te drugie jest świetne i bardzo mi pomoże i ułatwi porządkowanie i analizy danych.

Serdecznie dziękuję za pomoc.
Pozdrawiam
naat3 :-D