Excel Forum - Porady, Pomoc,  Excel Help, Excel FAQ Strona Główna
 FAQ  RegulaminRegulamin  Szukaj   Użytkownicy   Grupy   Rejestracja   Profil   Twoje wiadomości   Zaloguj 


Poprzedni temat «» Następny temat
ID tematu: 70518 Skopiuj do schowka Uporządkowanie wieloletniej serii dat z danymi
Autor Wiadomość
naat3
Forumowicz


Posty: 17
Wysłany: 26-03-2021, 16:31   Uporządkowanie wieloletniej serii dat z danymi

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

ID posta: 402780 Skopiuj do schowka
 
 
caelian
ExcelGaduła 500+


Wersja: Win Office 365
Pomógł: 61 razy
Posty: 563
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

ID posta: 402782 Skopiuj do schowka
 
 
Marecki 
Excel Expert



Wersja: Win Office 2019
Pomógł: 2530 razy
Posty: 8431
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
_________________
Hardware - ta część komputera, którą można kopnąć kiedy software przestanie funkcjonować.

Szkolenia z Excela , FB
Office 2019 Professional Plus , Windows 10 x64
Pozdrawiam, były mkkk23 teraz Marecki.
ID posta: 402792 Skopiuj do schowka
 
 
naat3
Forumowicz


Posty: 17
Wysłany: 27-03-2021, 15:27   

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
ID posta: 402833 Skopiuj do schowka
 
 
ąćęłńóś
Excel Expert


Pomógł: 327 razy
Posty: 1615
Wysłany: 27-03-2021, 16:30   

Czym się objawiło/objawia to "niezadziałanie" makra Mareckiego ?
ID posta: 402836 Skopiuj do schowka
 
 
Marecki 
Excel Expert



Wersja: Win Office 2019
Pomógł: 2530 razy
Posty: 8431
Wysłany: 27-03-2021, 17:25   

naat3 napisał/a:
czy można stworzyć makro, które porządkuje wieloletnią serię danych gdzie daty są rzeczywistymi prawidłowymi datami excela ?
Można.
Na przyszłość pamiętaj aby wstawiony załącznik odzwierciedlał prawdziwy typ danych.

Makro działa na arkuszu aktywnym, i wkleja dane w miejsce starych, więc zrób sobie kopię pliku i na nim testuj.
Kod:
Sub Test()
Dim vArr_tmp() As Variant
Dim vArr_out() As Variant
Dim vArr() As Variant
Dim Tmp As Variant
Dim Poz As Variant
Dim Data_od As Long
Dim Data_do As Long
Dim lRow As Long
Dim i As Long
Dim j As Long
Dim k As Long

    With ActiveSheet
        .Range("A3").Copy
        lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Columns("A:A").NumberFormat = "0"
        vArr = .Range("A3:Y" & lRow).Value

        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Range("A3:A" & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With .Sort
            .SetRange Range("A2:Y" & lRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

        Data_od = .Range("A3").Value
        Data_do = .Range("A" & lRow).Value
        vArr_tmp = .Range("A3:A" & lRow).Value

        .Range("A3:Y33").ClearContents
        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

        .Range("A3").Resize(k, 25) = vArr_out
        .Columns(1).NumberFormat = "dd.mm.yyyy"
        .Range("A3").Copy
        lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range("A3:A" & lRow).PasteSpecial Paste:=xlPasteFormats

        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("A3:A" & lRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With .Sort
            .SetRange Range("A2:Y" & lRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

    End With

End Sub
_________________
Hardware - ta część komputera, którą można kopnąć kiedy software przestanie funkcjonować.

Szkolenia z Excela , FB
Office 2019 Professional Plus , Windows 10 x64
Pozdrawiam, były mkkk23 teraz Marecki.
ID posta: 402840 Skopiuj do schowka
 
 
naat3
Forumowicz


Posty: 17
Wysłany: 27-03-2021, 19:56   

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
ID posta: 402849 Skopiuj do schowka
 
 
Wyświetl posty z ostatnich:   
Odpowiedz do tematu
Nie możesz pisać nowych tematów
Nie możesz odpowiadać w tematach
Nie możesz zmieniać swoich postów
Nie możesz usuwać swoich postów
Nie możesz głosować w ankietach
Nie możesz załączać plików na tym forum
Możesz ściągać załączniki na tym forum
Dodaj temat do Ulubionych
Wersja do druku

Skocz do:  

Powered by phpBB modified by Przemo © 2003 phpBB Group
Theme xandgreen created by spleen& Programosy modified v0.3 by warna
Opieka techniczna www.marketingNET.pl

Archiwum

Strona używa plików cookies.

Kliknij tutaj, żeby dowiedzieć się jaki jest cel używania cookies oraz jak zmienić ustawienia cookie w przeglądarce.
Korzystając ze strony użytkownik wyraża zgodę na używanie plików cookies, zgodnie z bieżącymi ustawieniami przeglądarki.
Sprawdź, w jaki sposób przetwarzamy dane osobowe