Konwersja formatu daty RRRRMMDD w programie Excel dla danych JSON

Obsługa dat JSON w Excelu

Praca ze zbiorami danych JSON często wiąże się z operowaniem datami w różnych formatach. Powszechnym formatem jest RRRRMMDD, gdzie daty są wyświetlane jako liczby, np. 20190611 oznacza 11 czerwca 2019 r.

W tym artykule sprawdzimy, dlaczego normalne formatowanie dat w programie Excel może nie działać w przypadku tych dat i omówimy rozwiązania umożliwiające przekonwertowanie ich na czytelny format. W razie potrzeby podamy również wskazówki dotyczące prawidłowego wstawiania łączników.

Konwersja dat JSON na czytelny format w programie Excel

Skrypt VBA przedstawiony w poprzednich przykładach ma na celu przeformatowanie dat zapisanych jako liczby w formacie RRRRMMDD na bardziej czytelny format RRRR-MM-DD w programie Excel. Osiąga się to poprzez iterację po określonym zakresie komórek, sprawdzenie, czy każda komórka zawiera wartość liczbową o długości ośmiu znaków, a następnie zmianę układu i wstawienie łączników w odpowiednich pozycjach. Komenda Set ws = ThisWorkbook.Sheets("Sheet1") ustawia arkusz, w którym znajdują się dane, oraz Set rng = ws.Range("A1:A100") określa zakres komórek do przetworzenia. The IsNumeric(cell.Value) polecenie służy do sprawdzenia, czy wartość komórki jest numeryczna, zapewniając przetworzenie tylko odpowiednich komórek. Dzięki zastosowaniu tych poleceń skrypt efektywnie formatuje daty zgodnie z wymaganiami.

Skrypt Pythona wykorzystuje bibliotekę pandas do obsługi konwersji daty. Komenda import pandas as pd importuje bibliotekę pandas, która jest kluczowa przy manipulacji danymi. Funkcja df['Date'].apply(convert_date) stosuje zwyczaj convert_date do każdego elementu w kolumnie „Data”, zmieniając format daty. Wreszcie, df.to_excel('formatted_data.xlsx', index=False) zapisuje nowo sformatowaną ramkę DataFrame z powrotem do pliku Excel bez uwzględniania indeksu. Ten skrypt stanowi potężną alternatywę dla VBA dla użytkowników zaznajomionych z Pythonem. Dodatkowo formuła Excel TEXT(LEFT(A1, 4) & "-" & MID(A1, 5, 2) & "-" & RIGHT(A1, 2), "yyyy-mm-dd") zapewnia szybkie, oparte na formułach rozwiązanie do konwertowania poszczególnych dat bezpośrednio w komórkach programu Excel. Każda z tych metod rozwiązuje problem konwersji dat ze zbiorów danych JSON na przyjazny dla użytkownika format w programie Excel, zapewniając wszechstronne rozwiązania dla różnych preferencji użytkownika.

Sub ConvertDates()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Adjust sheet name if necessary
    Set rng = ws.Range("A1:A100") ' Adjust range if necessary
    For Each cell In rng
        If IsNumeric(cell.Value) And Len(cell.Value) = 8 Then
            cell.Value = Left(cell.Value, 4) & "-" & Mid(cell.Value, 5, 2) & "-" & Right(cell.Value, 2)
        End If
    Next cell
End Sub

import pandas as pd
df = pd.read_excel('data.xlsx') # Replace with your file name
def convert_date(date_str):
    return f"{date_str[:4]}-{date_str[4:6]}-{date_str[6:]}"
df['Date'] = df['Date'].apply(convert_date)
df.to_excel('formatted_data.xlsx', index=False)

=TEXT(LEFT(A1, 4) & "-" & MID(A1, 5, 2) & "-" & RIGHT(A1, 2), "yyyy-mm-dd")

Skuteczne metody konwersji dat JSON w Excelu

Innym podejściem do konwertowania dat JSON w programie Excel jest użycie Power Query — technologii łączenia danych, która umożliwia użytkownikom odkrywanie, łączenie, łączenie i udoskonalanie danych z wielu różnych źródeł. Dodatek Power Query może być szczególnie przydatny w przypadku dużych zestawów danych lub gdy konwersja daty musi być częścią większego procesu transformacji danych. Aby użyć dodatku Power Query do konwersji daty, możesz zaimportować zestaw danych do programu Excel, a następnie użyć dodatku Power Query do przekształcenia kolumny daty. Zacznij od wybrania danych i wybrania opcji „Z tabeli/zakresu” w edytorze Power Query. Użyj funkcji „Dodaj kolumnę”, aby utworzyć kolumnę niestandardową i zastosuj funkcję, aby poprawnie sformatować daty. Ta metoda jest wydajna i bezproblemowo integruje się z innymi etapami przetwarzania danych w dodatku Power Query.

Oprócz dodatku Power Query inną skuteczną metodą jest użycie funkcji zamiany tekstu na kolumny programu Excel. To wbudowane narzędzie umożliwia użytkownikom dzielenie pojedynczej kolumny tekstu na wiele kolumn w oparciu o ograniczniki. W przypadku dat w formacie RRRRMMDD możesz użyć funkcji zamiany tekstu na kolumny, aby podzielić tekst na osobne kolumny roku, miesiąca i dnia, a następnie ponownie połączyć te kolumny, umieszczając łączniki w odpowiednich miejscach. Metoda ta jest prosta i nie wymaga wiedzy programistycznej. Zarówno dodatek Power Query, jak i funkcja zamiany tekstu na kolumny zapewniają dodatkową elastyczność i mogą stanowić wartościową alternatywę dla używania skryptów VBA lub Python, w zależności od znajomości użytkownika i konkretnych potrzeb.

Podsumowanie przewodnika po konwersji daty JSON

Ponowne formatowanie dat w programie Excel z formatu RRRRMMDD, zwłaszcza ze zbiorów danych JSON, wymaga specjalnych technik wykraczających poza zwykłe opcje formatowania. Korzystanie z takich metod, jak skrypty VBA i Python, wraz z wbudowanymi narzędziami programu Excel, takimi jak zamiana tekstu na kolumny i Power Query, gwarantuje dokładne i wydajne konwertowanie dat. Rozwiązania te zapewniają wszechstronność, dostosowując się do potrzeb użytkowników o różnym poziomie wiedzy programistycznej i różnych potrzebach w zakresie przetwarzania danych.