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?” Może to 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.
Daty zawierające kropkę
Prawdopodobnie jednym z najczęstszych błędów popełnianych przez początkujących podczas wpisywania dat w Excelu jest używanie kropki jako separatora między dniem, miesiącem i rokiem.
Excel nie rozpozna tego jako wartości daty i zapisze go jako tekst. Możesz jednak rozwiązać ten problem, korzystając z 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 zostaną zamienione na ukośniki, a program Excel rozpozna 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ć funkcji SUBSTITUTE.
=VALUE(SUBSTITUTE(A2,".","/"))
Funkcja SUBSTITUTE jest funkcją tekstową, więc nie można jej samodzielnie przekonwertować na datę. Funkcja WARTOŚĆ skonwertuje wartość tekstową na wartość liczbową.
Wyniki muszą być sformatowane jako data.
Możesz to zrobić za pomocą listy „Format liczb” na karcie „Strona główna”.
Przykład separatora kropki jest typowy, ale możesz użyć tej samej techniki, aby zastąpić lub zamienić 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ż eliminuje 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 niewygodną strukturą daty, ponieważ jest przechowywana jako tekst.
Poniżej znajduje się lista dat w różnych strukturach, ale wszystkie są rozpoznawane jako daty. 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 wspomnę o dwóch funkcjach do obsługi tych scenariuszy: DATEVALUE i VALUE.
Funkcja DATEVALUE skonwertuje tekst na wartość daty, a funkcja VALUE 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, co będzie demonstracją drobnych różnic funkcji.
Poniższa formuła DATEVALUE 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 zwraca tylko wartość daty. Wynik nadal będzie musiał zostać sformatowany jako data.
Poniższa formuła wykorzystuje funkcję VALUE.
=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ąć).