Анализът на бизнес данни често изисква работа със стойности на дати в Excel, за да се отговори на въпроси като „колко пари направихме днес“ или „как се сравнява това със същия ден миналата седмица?“ И това може да бъде трудно, когато Excel не разпознава стойностите като дати.
За съжаление, това не е необичайно, особено когато множество потребители въвеждат тази информация, копират и поставят от други системи и импортират от бази данни.
В тази статия ще опишем четири различни сценария и решенията за преобразуване на текста в стойности за дата.
Съдържание
Дати, които съдържат точка/точка
Вероятно една от най-честите грешки, които начинаещите правят, когато въвеждат дати в Excel, е да го правят със знака точка за разделяне на деня, месеца и годината.
Excel няма да разпознае това като стойност за дата и ще продължи и ще го съхрани като текст. Можете обаче да разрешите този проблем с инструмента за намиране и замяна. Чрез замяна на точките с наклонени черти (/), Excel автоматично ще идентифицира стойностите като дати.
Изберете колоните, върху които искате да извършите намирането и замяната.
Щракнете върху Начало > Намери и изберете > Замени—или натиснете Ctrl+H.
В прозореца Find and Replace въведете точка (.) в полето „Find what“ и наклонена черта (/) в полето „Replace with“. След това щракнете върху „Замяна на всички“.
Всички точки се преобразуват в наклонени черти и Excel разпознава новия формат като дата.
Ако данните в електронната ви таблица се променят редовно и искате автоматизирано решение за този сценарий, можете да използвате функцията ЗАМЕНЯВА.
=VALUE(SUBSTITUTE(A2,".","https://www.wdzwdz.com/"))
Функцията SUBSTITUTE е текстова функция, така че не може да я преобразува в дата самостоятелно. Функцията VALUE ще преобразува текстовата стойност в числова стойност.
Резултатите са показани по-долу. Стойността трябва да бъде форматирана като дата.
Можете да направите това с помощта на списъка „Формат на номера“ в раздела „Начало“.
Примерът тук за разделител на точка е типичен. Но можете да използвате същата техника, за да замените или замените всеки знак за разделител.
Преобразуване на формата yyyymmdd
Ако получавате дати във формата, показан по-долу, това ще изисква различен подход.
Този формат е доста стандартен в технологиите, тъй като премахва всяка неяснота относно това как различните държави съхраняват стойностите си за дати. Въпреки това Excel първоначално няма да го разбере.
За бързо ръчно решение можете да използвате Текст към колони.
Изберете диапазона от стойности, които трябва да конвертирате, и след това щракнете върху Данни > Текст в колони.
Появява се съветникът за текст в колони. Щракнете върху „Напред“ в първите две стъпки, така че да сте на стъпка три, както е показано на изображението по-долу. Изберете Дата и след това изберете формата на датата, който се използва в клетките от списъка. В този пример имаме работа с формат YMD.
Ако искате формулно решение, тогава можете да използвате функцията Date, за да конструирате датата.
Това ще се използва заедно с текстовите функции Ляво, Средно и Вдясно за извличане на трите части на дата (ден, месец, година) от съдържанието на клетката.
Формулата по-долу показва тази формула, използвайки нашите примерни данни.
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
Използвайки някоя от тези техники, можете да преобразувате произволна осемцифрена числова стойност. Например, може да получите датата във формат ddmmyyyy или mmddyyyy.
Функции DATEVALUE и VALUE
Понякога проблемът не е причинен от знак за разделител, а има неудобна структура на датата, просто защото се съхранява като текст.
По-долу е даден списък с дати в различни структури, но всички те са разпознаваеми за нас като дата. За съжаление, те са били съхранени като текст и се нуждаят от конвертиране.
За тези сценарии е лесно да се преобразува с помощта на различни техники.
За тази статия исках да спомена две функции за справяне с тези сценарии. Те са DATEVALUE и VALUE.
Функцията DATEVALUE ще преобразува текста в стойност на дата (вероятно е видяла, че идва), докато функцията VALUE ще преобразува текста в обща числова стойност. Разликите между тях са минимални.
На изображението по-горе една от стойностите съдържа и информация за времето. И това ще бъде демонстрация на малките разлики между функциите.
Формулата DATEVALUE по-долу ще преобразува всеки от тях в стойност на дата.
=DATEVALUE(A2)
Забележете как времето беше премахнато от резултата в ред 4. Тази формула връща стриктно само стойността на датата. Резултатът все още трябва да бъде форматиран като дата.
Следната формула използва функцията VALUE.
=VALUE(A2)
Тази формула ще даде същите резултати с изключение на ред 4, където стойността на времето също се поддържа.
След това резултатите могат да бъдат форматирани като дата и час или като дата, за да се скрие стойността на времето (но не и да се премахне).