Analiza danych biznesowych często wymaga pracy z wartościami dat w programie Excel, aby odpowiedzieć na pytania, takie jak „ile pieniędzy dzisiaj zarobiliśmy” lub „jak wypada to w porównaniu z tym samym dniem w zeszłym tygodniu?” A to może być trudne, gdy Excel nie rozpoznaje wartości jako dat.
Niestety nie jest to niczym niezwykłym, zwłaszcza gdy wielu użytkowników wpisuje te informacje, kopiuje i wkleja z innych systemów oraz importuje z baz danych.
W tym artykule opiszemy cztery różne scenariusze i rozwiązania umożliwiające konwersję tekstu na wartości daty.
Spis treści:
Daty zawierające kropkę / kropkę
Prawdopodobnie jednym z najczęstszych błędów popełnianych przez początkujących podczas wpisywania dat w Excelu jest robienie tego z kropką, aby oddzielić dzień, miesiąc i rok.
Excel nie rozpozna tego jako wartości daty i zapisze go jako tekst. Możesz jednak rozwiązać ten problem za pomocą narzędzia Znajdź i zamień. Zastępując kropki ukośnikami (/), program Excel automatycznie identyfikuje wartości jako daty.
Wybierz kolumny, na których chcesz przeprowadzić wyszukiwanie i zamianę.
Kliknij Start> Znajdź i wybierz> Zastąp – lub naciśnij Ctrl + H.
W oknie Znajdź i zamień wpisz kropkę (.) W polu „Znajdź” i ukośnik (/) w polu „Zamień na”. Następnie kliknij „Zamień wszystko”.
Wszystkie kropki są konwertowane na ukośniki, a program Excel rozpoznaje nowy format jako datę.
Jeśli dane w arkuszu kalkulacyjnym regularnie się zmieniają i potrzebujesz zautomatyzowanego rozwiązania dla tego scenariusza, możesz użyć funkcja SUBSTITUTE.
=VALUE(SUBSTITUTE(A2,".","https://www.newsblog.pl.com/"))
Funkcja SUBSTITUTE jest funkcją tekstową, więc nie można jej samodzielnie przekonwertować na datę. Funkcja WARTOŚĆ skonwertuje wartość tekstową na wartość liczbową.
Wyniki przedstawiono poniżej. Wartość musi być sformatowana jako data.
Możesz to zrobić za pomocą listy „Format liczb” na karcie „Strona główna”.
Przykład separatora kropki jest tutaj typowy. Ale możesz użyć tej samej techniki, aby zastąpić lub zastąpić dowolny znak separatora.
Konwersja formatu rrrrmmdd
Jeśli otrzymasz daty w formacie pokazanym poniżej, będzie to wymagało innego podejścia.
Ten format jest dość standardowy w technologii, ponieważ usuwa wszelkie niejasności dotyczące sposobu przechowywania wartości dat w różnych krajach. Jednak program Excel początkowo tego nie zrozumie.
Aby szybko rozwiązać problem, możesz użyć opcji Tekst na kolumny.
Wybierz zakres wartości, które chcesz przekonwertować, a następnie kliknij Dane> Tekst na kolumny.
Zostanie wyświetlony kreator tekstu na kolumny. Kliknij „Dalej” w pierwszych dwóch krokach, aby przejść do kroku trzeciego, jak pokazano na poniższym obrazku. Wybierz opcję Data, a następnie wybierz z listy format daty używany w komórkach. W tym przykładzie mamy do czynienia z formatem YMD.
Jeśli potrzebujesz rozwiązania opartego na formule, możesz użyć funkcji Date do skonstruowania daty.
Byłoby to używane wraz z funkcjami tekstowymi Lewo, Środek i Prawo, aby wyodrębnić trzy części daty (dzień, miesiąc, rok) z zawartości komórki.
Poniższy wzór przedstawia tę formułę przy użyciu naszych przykładowych danych.
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
Korzystając z jednej z tych technik, możesz przekonwertować dowolną ośmiocyfrową wartość liczbową. Na przykład możesz otrzymać datę w formacie ddmmrrrr lub mmddyrrr.
Funkcje DATEVALUE i VALUE
Czasami problem nie jest spowodowany separatorem, ale ma niewygodną strukturę daty po prostu dlatego, że jest przechowywany jako tekst.
Poniżej znajduje się lista dat w różnych strukturach, ale wszystkie są dla nas rozpoznawalne jako data. Niestety zostały zapisane jako tekst i wymagają konwersji.
W tych scenariuszach konwersję można łatwo przeprowadzić przy użyciu różnych technik.
W tym artykule chciałem wspomnieć o dwóch funkcjach do obsługi tych scenariuszy. Mają wartości DATEVALUE i VALUE.
Funkcja DATA.WARTOŚĆ skonwertuje tekst na wartość daty (prawdopodobnie zauważyła, że nadejdzie), a funkcja WARTOŚĆ przekształci tekst w ogólną wartość liczbową. Różnice między nimi są minimalne.
Na powyższym obrazku jedna z wartości zawiera również informacje o czasie. A to będzie demonstracja drobnych różnic funkcji.
Poniższa formuła DATA.WARTOŚĆ przekształci każdy z nich w wartość daty.
=DATEVALUE(A2)
Zwróć uwagę, jak czas został usunięty z wyniku w wierszu 4. Ta formuła ściśle zwraca tylko wartość daty. Wynik nadal będzie musiał zostać sformatowany jako data.
Poniższa formuła wykorzystuje funkcję WARTOŚĆ.
=VALUE(A2)
Ta formuła da te same wyniki, z wyjątkiem wiersza 4, w którym zachowywana jest również wartość czasu.
Wyniki można następnie sformatować jako datę i godzinę lub jako datę, aby ukryć wartość czasu (ale nie można jej usunąć).