Yra daug būdų, kaip konvertuoti datas, suformatuotas kaip tekstas, į tikrąsias datas programoje „Excel“, ir šioje mokymo programoje siekiama juos visus ištirti.
Kai importuojate duomenis į „Excel“, jie gali būti įvairių formų, kurių „Excel“ neatpažįsta. Kartais, kai datos importuojamos iš išorinio šaltinio, jos bus suformatuotos kaip tekstas.
Taip pat svarbu žinoti, kad „Excel“ taiko datos formatus pagal jūsų sistemos regiono nustatymus. Taigi, kai į „Excel“ importuojate duomenis, gautus iš kitos šalies, „Excel“ gali jų neatpažinti ir saugoti kaip teksto įrašus.
Jei turite šią problemą, yra daug būdų, kuriais galite konvertuoti tekstą į datą programoje „Excel“, ir mes juos visus aptarsime šioje mokymo programoje.
Teksto konvertavimo į datą metodai
Jei darbalapyje yra datos, suformatuotos kaip tekstas, o ne tikrosios datos, jų negalima naudoti jokiuose skaičiavimuose. Taigi, jūs turite konvertuoti juos atgal į tikrąsias datas.
Jei matote, kad jūsų datos sulygiuotos į kairę, tai reiškia, kad jos suformatuotos kaip tekstas, nes tekstai pagal numatytuosius nustatymus yra sulygiuoti kairėje. Skaičius ir datos visada lygiuojami į dešinę.
Yra keli skirtingi būdai, kaip galite konvertuoti tekstą į datą programoje „Excel“. Jie yra šie:
- Klaidų tikrinimo parinktis
- „Excel“ teksto į stulpelius funkcija
- Rasti ir pakeisti
- Įklijuoti Specialus įrankis
- Excel formulė ir funkcijos
Konvertuokite tekstą į datą naudodami klaidų tikrinimo parinktį
„Excel“ turi integruotą klaidų tikrinimo funkciją, kuri aptinka keletą akivaizdžių jūsų duomenų klaidų. Jei randama klaida, viršutiniame kairiajame langelio kampe bus rodomas mažas žalias trikampis (klaidos indikatorius). Jei pasirinksite tą langelį, pasirodys įspėjimo ženklas su geltonu šauktuku. Kai perkeliate žymeklį ant šio ženklo, „Excel“ informuos jus apie galimą to langelio problemą.
Pavyzdžiui, kai į datą įvedate metus dviejų skaitmenų formatu, programa „Excel“ laiko tą datą kaip tekstą ir išsaugo ją kaip tekstą. Ir jei pasirinksite tą langelį, pasirodys šauktukas su įspėjimu: „Šiame langelyje yra datos eilutė, pavaizduota tik 2 metų skaitmenimis“.
Jei langeliuose rodomas klaidos indikatorius, spustelėkite šauktuką ir bus parodytos kelios parinktys, kaip konvertuoti datas, suformatuotas kaip tekstas, į tikrąsias datas. „Excel“ parodys parinktis konvertuoti jį į 19XX arba 20XX (19XX 1915 m., 20XX 2015 m.). Pasirinkite tinkamą parinktį.
Tada tekstas bus konvertuojamas į tinkamą datos formatą.
Kaip „Excel“ įjungti klaidų tikrinimo parinktį
Paprastai „Excel“ pagal numatytuosius nustatymus yra įjungta parinktis „Klaidų tikrinimas“. Jei klaidų tikrinimo funkcija neveikia, turite įjungti klaidų tikrinimą programoje „Excel“.
Norėdami tai padaryti, spustelėkite skirtuką „Failas“ ir kairiajame skydelyje pasirinkite „Parinktys“.
„Excel“ parinkčių lange kairiajame skydelyje spustelėkite „Formulės“, o dešiniajame skydelyje „Klaidų tikrinimo“ skiltyje įgalinkite „Įgalinti fono klaidų tikrinimą“. Skiltyje „Klaidų tikrinimo taisyklės“ pažymėkite langelį „Ląstelės, kuriose yra metai, pavaizduoti kaip 2 skaitmenys“.
Konvertuokite tekstą į datą naudodami „Excel“ teksto į stulpelio funkciją
Tekstas į stulpelį yra puiki „Excel“ funkcija, leidžianti padalyti duomenis į kelis stulpelius, taip pat tai yra galingas įrankis konvertuoti teksto reikšmes į datos reikšmes. Šis metodas atpažįsta kelis skirtingus duomenų formatus ir konvertuoja juos į tinkamą datos formatą.
Paprastų teksto eilučių konvertavimas į datas
Tarkime, kad jūsų datos suformatuotos tokiomis teksto eilutėmis:
Galite naudoti vedlį Tekstas į stulpelius, kad greitai suformatuotų juos į datas.
Pirmiausia pasirinkite teksto įrašų diapazoną, kurį norite konvertuoti į datas. Tada eikite į juostelės skirtuką „Duomenys“ ir duomenų įrankių grupėje spustelėkite parinktį „Tekstas į stulpelius“.
Atsiras vedlys Teksto į stulpelius. Vykdydami teksto į stulpelį vedlio 1 veiksmą, skiltyje Pradinis duomenų tipas pasirinkite parinktį „Atskirta“ ir spustelėkite „Kitas“.
2 veiksme panaikinkite visų laukelių „Skyrikliai“ žymėjimą ir spustelėkite Pirmyn.
Paskutiniame vedlio veiksme stulpelio duomenų formatas pasirinkite „Data“ ir išskleidžiamajame sąraše šalia „Datos“ pasirinkite datos formatą ir spustelėkite mygtuką „Baigti“. Mūsų atveju mes konvertuojame teksto datas, pavaizduotas kaip "01 02 1995" (diena, mėnuo, metai), todėl išskleidžiamajame sąraše "Data:" pasirenkame "DMY".
Dabar „Excel“ konvertuoja teksto datas į tikrąsias datas ir rodo jas langeliuose sulygiuotas dešinėje pusėje.
Pastaba: Prieš pradėdami naudoti funkciją Tekstas į stulpelį, įsitikinkite, kad visos teksto eilutės yra identiško formato, kitaip tekstai nebus konvertuojami. Pavyzdžiui, jei kai kurios teksto datos suformatuotos kaip mėnesio / dienos / metų (MDY), o kitos - dienos / mėnesio / metų (DMY), ir pasirinkę „DMY“ 3 veiksme, gautumėte neteisingus rezultatus. Kaip parodyta žemiau esančiame paveikslėlyje.
Sudėtingo teksto eilutės konvertavimas į datą
Teksto į stulpelius funkcija praverčia, kai sudėtingas teksto eilutes norite konvertuoti į datas. Tai leidžia naudoti skyriklius, kad nustatytų, kur jūsų duomenys turėtų būti padalyti ir pateikti 2 ar daugiau stulpelių. Ir sujunkite padalintas datų dalis į visą datą naudodami DATE funkciją.
Pavyzdžiui, jei jūsų datos rodomos kelių dalių teksto eilutėse, pavyzdžiui:
2020 m. vasario 01 d., trečiadienis
2020 m. vasario 01 d., 16.10 val
Galite naudoti vedlį Tekstas į stulpelį, kad atskirtumėte dienos, datos ir laiko informaciją, kuri yra atskirta kableliu, ir parodytumėte juos keliuose stulpeliuose.
Pirmiausia pasirinkite visas teksto eilutes, kurias norite konvertuoti į datas. Skirtuke „Duomenys“ spustelėkite mygtuką „Tekstas į stulpelius“. Vykdydami teksto į stulpelį vedlio 1 veiksmą, skiltyje Pradinis duomenų tipas pasirinkite parinktį „Atskirta“ ir spustelėkite „Kitas“.
2 vedlio veiksme pasirinkite skirtukus, esančius jūsų teksto eilutėse, ir spustelėkite „Kitas“. Mūsų pavyzdinės teksto eilutės, atskirtos kableliu ir tarpu – „pirmadienis, 2015 m. vasario 1 d., 13:00“. Norėdami padalinti teksto eilutes į kelis stulpelius, kaip skyriklius turėtume pasirinkti „Kableliai“ ir „tarpai“.
Paskutiniame etape pasirinkite visų duomenų peržiūros stulpelių formatą „Bendra“. Nurodykite, kur stulpeliai turi būti įterpti lauke „Paskirtis“, jei to nepadarysite, pradiniai duomenys bus perrašyti. Jei norite nepaisyti tam tikros pradinių duomenų dalies, spustelėkite ją duomenų peržiūros skiltyje ir pasirinkite parinktį „Neimportuoti stulpelio (praleisti)“. Tada spustelėkite „Baigti“.
Dabar datų dalys (savaitės dienos, mėnuo, metai, laikas) suskirstytos į B, C, D, E, F ir G stulpelius.
Tada sujunkite datos dalis naudodami DATE formulę, kad gautumėte visą datą.
„Excel“ funkcijos DATE sintaksė:
=DATE(metai, mėnuo, diena)Mūsų pavyzdyje mėnesio, dienos ir metų dalys yra atitinkamai C, D ir E stulpeliuose.
Funkcija DATE atpažįsta tik skaičius, o ne tekstą. Kadangi visos mūsų mėnesio reikšmės C stulpelyje yra teksto eilutės, turime jas konvertuoti į skaičius. Norėdami tai padaryti, naudokite funkciją MONTH, kad mėnesio pavadinimą pakeistumėte į mėnesio numerį.
Norėdami konvertuoti mėnesio pavadinimą į mėnesio skaičių, naudokite šią funkciją MONTH funkcijoje DATE:
=MĖNESIS(1&C1)Funkcija MONTH prideda 1 į langelį C2, kuriame yra mėnesio pavadinimas, kad mėnesio pavadinimą konvertuotų į atitinkamą mėnesio numerį.
Tai funkcija DATE, kurią turime naudoti norėdami sujungti datos dalis iš skirtingų stulpelių:
=DATE(E1,MONTH(1&C1),D1)
Dabar naudokite užpildymo rankenėlę, esančią apatiniame formulės langelio kampe, ir pritaikykite formulę stulpeliui.
Konvertuokite tekstą į datą naudodami paieškos ir keitimo metodą
Šis metodas naudoja skyriklius, kad pakeistų teksto formatą į datas. Jei diena, mėnuo ir metai jūsų datose yra atskirti kokiu nors skyrikliu, o ne brūkšneliu (-) arba pasviruoju brūkšniu (/), „Excel“ neatpažins jų kaip datų ir išsaugos juos kaip tekstą.
Norėdami išspręsti šią problemą, naudokite funkciją Rasti ir pakeisti. Pakeitus nestandartinius laikotarpio skyriklius (.) pasviraisiais brūkšniais (/) arba brūkšneliu (-), „Excel“ automatiškai pakeis reikšmes į datas.
Pirmiausia pasirinkite visas teksto datas, kurias norite konvertuoti į datas. Skirtuke „Pagrindinis“ spustelėkite mygtuką „Rasti ir pasirinkti“ pačiame dešiniajame juostelės kampe ir pasirinkite „Pakeisti“. Arba paspauskite Ctrl + H Norėdami atidaryti dialogo langą Rasti ir pakeisti.
Dialogo lange „Rasti ir pakeisti“ įveskite tekste esantį skyriklį (mūsų atveju tašką (.) lauke „Rasti ką“ ir pasvirąjį brūkšnį (/) arba brūkšnį (-) lauke „Pakeisti į“ . Spustelėkite mygtuką „Pakeisti viską“, kad pakeistumėte skyriklius, ir spustelėkite „Uždaryti“, kad uždarytumėte langą.
Dabar „Excel“ atpažįsta, kad jūsų teksto eilutės dabar yra datos, ir automatiškai suformatuoja jas kaip datas. Jūsų datos bus sulygiuotos taip, kaip parodyta toliau.
Konvertuokite tekstą į datą naudodami specialų įklijavimo įrankį
Kitas greitas ir paprastas būdas konvertuoti teksto eilutes į datas yra pridėti 0 prie teksto eilutės naudojant specialią įklijavimo parinktį. Pridėjus nulį prie vertės, tekstas konvertuojamas į datos serijos numerį, kurį galite formatuoti kaip datą.
Pirmiausia pasirinkite tuščią langelį ir nukopijuokite jį (pasirinkite ir paspauskite Ctrl + C Kopijuoti).
Tada pasirinkite langelius, kuriuose yra teksto datos, kurias norite konvertuoti, dešiniuoju pelės mygtuku spustelėkite ir pasirinkite parinktį „Įklijuoti specialų“.
Dialogo lange Specialusis įklijavimas skiltyje Įklijuoti pasirinkite „Visi“, skiltyje „Operacija“ pasirinkite „Pridėti“ ir spustelėkite „Gerai“.
Taip pat galite atlikti kitas aritmetines operacijas atimti/dauginti/padalyti reikšmę paskirties langelyje iš įklijuotos reikšmės (pvz., padauginti langelius iš 1 arba padalyti iš 1 arba atimti nulį).
Kai pasirenkate „Pridėti“, prie visų pasirinktų teksto datų pridedamas „nulis“, nes pridėjus „0“ reikšmės nekeičiamos, gausite kiekvienos datos serijos numerį. Dabar viskas, ką jums reikia padaryti, tai pakeisti langelių formatą.
Pasirinkite serijos numerius ir skirtuke „Pagrindinis“ spustelėkite išskleidžiamąjį sąrašą „Skaičių formatas“, esantį numerių grupėje. Išskleidžiamajame meniu pasirinkite parinktį „Trumpa data“.
Kaip matote, dabar skaičiai suformatuoti kaip datos ir sulygiuoti į dešinę.
Konvertuokite tekstą į datą naudodami formules
Tekstui konvertuoti į datą pirmiausia naudojamos dvi funkcijos: DATEVALUE ir VALUE.
Naudojant „Excel“ DATEVALUE funkciją
„Excel“ funkcija DATEVALUE yra vienas iš paprasčiausių būdų konvertuoti datą, pateiktą kaip tekstas, į datos serijos numerį.
Funkcijos DATEVALUE sintaksė:
=DATEVALUE(datos_tekstas)Argumentas: data_text nurodo teksto eilutę, kurią norite paslėpti, arba nuorodą į langelį, kuriame yra teksto datos.
Formulė:
=DATAVALUE(A1)Toliau pateiktame paveikslėlyje parodyta, kaip funkcija DATEVALUE apdoroja kelis skirtingus datos formatus, kurie saugomi kaip tekstas.
Gavote datos serijos numerius, dabar šiems numeriams turite taikyti datos formatą. Norėdami tai padaryti, pasirinkite langelius su serijos numeriais, tada eikite į skirtuką „Pagrindinis“ ir išskleidžiamajame sąraše „Skaičių formatas“ pasirinkite „Trumpa data“.
Dabar C stulpelyje turite suformatuotas datas.
Net jei teksto datoje (A8) nėra metų dalies, DATEVALUE naudos einamuosius metus pagal jūsų kompiuterio laikrodį.
Funkcija DATEVALUE konvertuos tik tas teksto reikšmes, kurios atrodo kaip data. Jis negali konvertuoti teksto, panašaus į skaičių, į datą, taip pat negali pakeisti skaičiaus reikšmės į datą, todėl jums reikės „Excel“ funkcijos VALUE.
Naudojant „Excel VALUE“ funkciją
Funkcija Excel VALUE gali konvertuoti bet kokią teksto eilutę, panašią į datą ar skaičių, į skaitinę reikšmę, todėl ji tikrai naudinga konvertuojant bet kokį skaičių, ne tik datas.
Funkcija VALUE:
=VERTĖ(tekstas)tekstą– teksto eilutę, kurią norime konvertuoti, arba nuorodą į langelį, kuriame yra teksto eilutė.
Teksto datos konvertavimo formulės pavyzdys:
=VERTĖ(A1)Toliau pateikta formulė VALUE gali pakeisti bet kokias teksto eilutes, kurios atrodo kaip data, į skaičių, kaip parodyta toliau.
Tačiau funkcija VALUE nepalaiko visų tipų datos reikšmių. Pavyzdžiui, jei datos naudoja dešimtainius skaitmenis (A11), bus pateikta #VALUE! klaida.
Kai turėsite savo datos serijos numerį, turėsite suformatuoti langelį su datos serijos numeriu, kad jis atrodytų kaip data, kaip tai padarėme funkcijai DATEVALUE. Norėdami tai padaryti, pasirinkite serijos numerius ir skirtuke „Pagrindinis“ esančiame išskleidžiamajame meniu „Numerio formatas“ pasirinkite parinktį „Data“.
Štai viskas, tai yra 5 skirtingi būdai, kaip galite konvertuoti datas, suformatuotas kaip tekstas, į datas programoje „Excel“.