Ir daudz veidu, kā pārvērst datumus, kas formatēti kā teksts, par faktiskajiem datumiem programmā Excel, un šīs apmācības mērķis ir tos visus izpētīt.
Importējot datus programmā Excel, tiem var būt dažādas formas, kuras programma Excel neatpazīst. Dažreiz, kad datumi tiek importēti no ārēja avota, tie tiek formatēti kā teksts.
Ir arī svarīgi zināt, ka programmā Excel tiek lietoti datuma formāti, pamatojoties uz jūsu sistēmas reģiona iestatījumiem. Tātad, importējot programmā Excel datus, kas iegūti no citas valsts, programma Excel tos var neatpazīt un saglabāt tos kā teksta ierakstus.
Ja jums ir šī problēma, ir daudz veidu, kā pārvērst tekstu par datumu programmā Excel, un mēs tos visus apskatīsim šajā apmācībā.
Metodes teksta konvertēšanai uz datumu
Ja darblapā ir datumi, kas formatēti kā teksts, nevis faktiskie datumi, tos nav iespējams izmantot aprēķinos. Tātad, jums tie ir jāpārvērš atpakaļ uz faktiskajiem datumiem.
Ja redzat, ka jūsu datumi ir līdzināti pa kreisi, tas nozīmē, ka tie ir formatēti kā teksts, jo teksti pēc noklusējuma ir līdzināti pa kreisi. Un cipars un datumi vienmēr ir līdzināti pa labi.
Ir daži dažādi veidi, kā programmā Excel tekstu var pārvērst datumā. Tie ir:
- Kļūdu pārbaudes opcija
- Excel funkcija Teksts uz kolonnām
- Atrast un aizstāt
- Ielīmēt Īpašs rīks
- Excel formula un funkcijas
Konvertējiet tekstu uz datumu, izmantojot kļūdu pārbaudes opciju
Programmā Excel ir iebūvēta kļūdu pārbaudes funkcija, kas pamana dažas acīmredzamas kļūdas jūsu datos. Ja tiek atrasta kļūda, šūnas augšējā kreisajā stūrī tiks parādīts neliels zaļš trīsstūris (kļūdas indikators). Ja atlasīsit šo šūnu, tiks parādīta brīdinājuma zīme ar dzeltenu izsaukuma zīmi. Pārvietojot kursoru virs šīs zīmes, programma Excel jūs informēs par iespējamo problēmu ar šo šūnu.
Piemēram, kad datumā ievadāt gadu divu ciparu formātā, programma Excel pieņem šo datumu kā tekstu un saglabā to kā tekstu. Un, ja atlasīsit šo šūnu, tiks parādīta izsaukuma zīme ar brīdinājumu: “Šajā šūnā ir datuma virkne, kas attēlota tikai ar 2 gada cipariem”.
Ja jūsu šūnās ir redzams šis kļūdas indikators, noklikšķiniet uz izsaukuma zīmes, un tiks parādītas dažas opcijas teksta formāta datumu konvertēšanai faktiskos datumos. Programmā Excel tiks parādītas iespējas to konvertēt uz 19XX vai 20XX (19XX 1915. gadam, 20XX 2015. gadam). Izvēlieties atbilstošo opciju.
Pēc tam teksts tiks pārveidots pareizā datuma formātā.
Kā programmā Excel iespējot kļūdu pārbaudes opciju
Parasti kļūdu pārbaudes opcija programmā Excel ir ieslēgta pēc noklusējuma. Ja kļūdu pārbaudes līdzeklis jums nedarbojas, programmā Excel ir jāiespējo kļūdu pārbaude.
Lai to izdarītu, noklikšķiniet uz cilnes "Fails" un kreisajā panelī atlasiet "Opcijas".
Excel opciju logā noklikšķiniet uz "Formulas" kreisajā panelī un labās puses panelī iespējojiet "Iespējot fona kļūdu pārbaudi" sadaļā Kļūdu pārbaude. Sadaļā Kļūdu pārbaudes noteikumi atzīmējiet “Šūnas, kurās ir gadi, kas attēloti kā 2 cipari”.
Konvertējiet tekstu uz datumu, izmantojot Excel teksta par kolonnu līdzekli
Teksts uz kolonnu ir lieliska programma programmā Excel, kas ļauj sadalīt datus vairākās kolonnās, kā arī tas ir spēcīgs rīks teksta vērtību konvertēšanai datuma vērtībās. Šī metode atpazīst vairākus dažādus datu formātus un pārvērš tos pareizā datuma formātā.
Vienkāršu teksta virkņu pārvēršana datumos
Pieņemsim, ka jūsu datumi ir formatēti šādās teksta virknēs:
Varat izmantot vedni Teksts uz kolonnām, lai ātri pārformatētu tos uz datumiem.
Vispirms atlasiet teksta ierakstu diapazonu, ko vēlaties konvertēt uz datumiem. Pēc tam lentē atveriet cilni “Dati” un grupā Datu rīki noklikšķiniet uz opcijas “Teksts uz kolonnām”.
Tiks parādīts vednis Teksts uz kolonnām. Vedņa Teksts uz kolonnu 1. darbībā sadaļā Sākotnējais datu veids izvēlieties opciju “Atdalīts” un noklikšķiniet uz “Tālāk”.
2. darbībā noņemiet atzīmi no visām izvēles rūtiņām “Atdalītāji” un noklikšķiniet uz Tālāk.
Vedņa pēdējā darbībā sadaļā Kolonnas datu formāts atlasiet “Datums” un blakus “Datumi” nolaižamajā sarakstā izvēlieties datuma formātu un noklikšķiniet uz pogas “Pabeigt”. Mūsu gadījumā mēs pārveidojam teksta datumus, kas attēloti kā “01 02 1995” (diena, mēnesis gads), tāpēc nolaižamajā sarakstā “Datums:” izvēlamies “DMY”.
Tagad programma Excel pārvērš jūsu teksta datumus faktiskajos datumos un parāda tos šūnās līdzinātus pa labi.
Piezīme: Pirms sākat lietot funkciju Teksts uz kolonnu, pārliecinieties, vai visas teksta virknes ir identiskā formātā, pretējā gadījumā teksti netiks konvertēti. Piemēram, ja daži teksta datumi ir formatēti kā mēnesis/diena/gads (MDY), bet citi — diena/mēnesis/gads (DMY), un, 3. darbībā izvēloties “DMY”, tiks iegūti nepareizi rezultāti. Kā tas ir parādīts zemāk esošajā attēlā.
Sarežģīta teksta virkne tiek pārveidota par datumu
Funkcija Teksts uz kolonnām ir noderīga, ja vēlaties pārveidot sarežģītas teksta virknes datumos. Tas ļauj izmantot atdalītājus, lai noteiktu, kur jūsu dati ir jāsadala un jāparāda 2 vai vairāk kolonnās. Un apvienojiet sadalītās datuma daļas veselā datumā, izmantojot funkciju DATE.
Piemēram, ja jūsu datumi tiek parādīti vairāku daļu teksta virknēs, piemēram:
Trešdien, 01.02.2020
2020. gada 1. februāris, plkst. 16.10
Varat izmantot vedni Teksts uz kolonnu, lai atdalītu dienas, datuma un laika informāciju, kas ir atdalīta ar komatu, un parādītu tos vairākās kolonnās.
Vispirms atlasiet visas teksta virknes, kuras vēlaties konvertēt uz datumiem. Cilnē Dati noklikšķiniet uz pogas Teksts uz kolonnām. Vedņa Teksts uz kolonnu 1. darbībā sadaļā Sākotnējais datu veids izvēlieties opciju “Atdalīts” un noklikšķiniet uz “Tālāk”.
Vedņa 2. darbībā atlasiet norobežotājus, kas ietverti jūsu teksta virknēs, un noklikšķiniet uz “Tālāk”. Mūsu teksta virkņu piemēri, kas atdalīti ar komatu un atstarpi – “pirmdiena, 2015. gada 1. februāris, 13:00”. Lai teksta virknes sadalītu vairākās kolonnās, kā atdalītājus vajadzētu izvēlēties “Komats” un “atstarpes”.
Pēdējā darbībā atlasiet formātu “Vispārīgi” visām slejām sadaļā Datu priekšskatījums. Norādiet, kur slejas jāievieto laukā "Galamērķis", ja to nedarīsit, tiks pārrakstīti sākotnējie dati. Ja vēlaties ignorēt kādu sākotnējo datu daļu, noklikšķiniet uz tās sadaļā Datu priekšskatījums un atlasiet opciju Neimportēt kolonnu (izlaist). Pēc tam noklikšķiniet uz "Pabeigt".
Tagad datu daļas (nedēļas dienas, mēnesis, gads, laiks) ir sadalītas kolonnās B, C, D, E, F un G.
Pēc tam apvienojiet datuma daļas, izmantojot DATE formulu, lai iegūtu visu datumu.
Excel funkcijas DATE sintakse:
=DATE(gads, mēnesis, diena)Mūsu piemērā mēneša, dienas un gada daļas ir attiecīgi C, D un E kolonnās.
Funkcija DATE atpazīst tikai ciparus, nevis tekstu. Tā kā visas mūsu mēneša vērtības kolonnā C ir teksta virknes, mums tās ir jāpārvērš skaitļos. Lai to izdarītu, izmantojiet funkciju MONTH, lai mainītu mēneša nosaukumu uz mēneša numuru.
Lai mēneša nosaukumu pārvērstu par mēneša skaitli, izmantojiet šo funkciju MONTH funkcijā DATE:
=MĒNESIS(1&C1)Funkcija MONTH pievieno 1 šūnai C2, kurā ir mēneša nosaukums, lai mēneša nosaukumu pārveidotu par atbilstošo mēneša numuru.
Šī ir funkcija DATE, kas mums jāizmanto, lai apvienotu datuma daļas no dažādām kolonnām:
=DATUMS(E1,MĒNESIS(1&C1),D1)
Tagad izmantojiet aizpildīšanas turi formulas šūnas apakšējā stūrī un lietojiet formulu kolonnai.
Konvertējiet tekstu uz datumu, izmantojot atrašanas un aizstāšanas metodi
Šī metode izmanto atdalītājus, lai mainītu teksta formātu uz datumiem. Ja diena, mēnesis un gads jūsu datumos ir atdalīti ar kādu norobežotāju, kas nav domuzīme (-) vai slīpsvītra (/), programma Excel tos neatpazīs kā datumus un saglabās tos kā tekstu.
Lai novērstu šo problēmu, izmantojiet funkciju Atrast un aizstāt. Mainot nestandarta periodu atdalītājus (.) ar slīpsvītām (/) vai domuzīmi (-), programma Excel automātiski mainīs vērtības uz datumiem.
Vispirms atlasiet visus teksta datumus, kurus vēlaties pārvērst datumos. Cilnē “Sākums” noklikšķiniet uz pogas “Atrast un atlasīt” lentes pašā labajā stūrī un atlasiet “Aizstāt”. Alternatīvi nospiediet Ctrl+H lai atvērtu dialoglodziņu Atrast un aizstāt.
Dialoglodziņā Atrast un aizstāt ierakstiet tekstā ietverto norobežotāju (mūsu gadījumā — punktu (.) laukā “Atrast ko” un uz priekšu slīpsvītru (/) vai domuzīmi (-) laukā “Aizstāt ar”. Noklikšķiniet uz pogas "Aizstāt visu", lai aizstātu atdalītājus, un noklikšķiniet uz "Aizvērt", lai aizvērtu logu.
Tagad Excel atpazīst, ka jūsu teksta virknes tagad ir datumi, un automātiski formatē tos kā datumus. Jūsu datumi tiks saskaņoti, kā parādīts tālāk.
Konvertējiet tekstu uz datumu, izmantojot īpašo ielīmēšanas rīku
Vēl viens ātrs un vienkāršs veids, kā pārvērst teksta virknes datumos, ir pievienot 0 teksta virknei, izmantojot īpašo ielīmēšanas opciju. Pievienojot vērtībai nulli, teksts tiek pārveidots par datuma sērijas numuru, ko varat formatēt kā datumu.
Vispirms atlasiet tukšu šūnu un kopējiet to (atlasiet to un nospiediet Ctrl+C kopēt).
Pēc tam atlasiet šūnas, kurās ir teksta datumi, kurus vēlaties konvertēt, ar peles labo pogu noklikšķiniet un atlasiet opciju Īpašā ielīmēšana.
Dialoglodziņā Īpašā ielīmēšana sadaļā Ielīmēt atlasiet "Visi", sadaļā Darbība izvēlieties "Pievienot" un noklikšķiniet uz "OK".
Varat arī veikt citas aritmētiskās darbības, lai atņemtu/reizinātu/dalītu vērtību mērķa šūnā ar ielīmēto vērtību (piemēram, reizināt šūnas ar 1 vai dalīt ar 1 vai atņemt nulli).
Atlasot opciju “Pievienot”, visiem atlasītajiem teksta datumiem tiek pievienota “nulle”, jo, pievienojot “0”, vērtības nemainās, jūs saņemsiet sērijas numuru katram datumam. Tagad viss, kas jums jādara, ir mainīt šūnu formātu.
Atlasiet sērijas numurus un cilnē "Sākums" noklikšķiniet uz nolaižamā saraksta "Numura formāts" numuru grupā. Nolaižamajā izvēlnē atlasiet opciju “Īss datums”.
Kā redzat, tagad skaitļi ir formatēti kā datumi un līdzināti pa labi.
Konvertējiet tekstu uz datumu, izmantojot formulas
Teksta konvertēšanai uz datumu galvenokārt tiek izmantotas divas funkcijas: DATEVALUE un VALUE.
Izmantojot Excel DATEVALUE funkciju
Funkcija Excel DATEVALUE ir viens no vienkāršākajiem veidiem, kā tekstu, kas attēlots kā teksts, pārvērst datuma sērijas numurā.
Funkcijas DATEVALUE sintakse:
=DATEVALUE(datuma_teksts)Arguments: datuma_teksts norāda teksta virkni, kuru vēlaties slēpt, vai atsauci uz šūnu, kurā ir teksta datumi.
Formula:
=DATEVALUE(A1)Nākamajā attēlā ir parādīts, kā funkcija DATEVALUE apstrādā dažus dažādus datuma formātus, kas tiek saglabāti kā teksts.
Jūs saņēmāt datuma sērijas numurus, tagad šiem numuriem ir jāpiemēro datuma formāts. Lai to izdarītu, atlasiet šūnas ar sērijas numuriem, pēc tam dodieties uz cilni "Sākums" un nolaižamajā sarakstā "Numura formāts" izvēlieties "Īss datums".
Tagad C slejā ir formatēti datumi.
Pat ja jūsu teksta datumā (A8) nav gada daļas, DATEVALUE izmantos pašreizējo gadu no jūsu datora pulksteņa.
Funkcija DATEVALUE konvertēs tikai tās teksta vērtības, kas izskatās pēc datuma. Tā nevar pārveidot tekstu, kas līdzinās skaitlim, par datumu, kā arī nevar mainīt skaitļa vērtību uz datumu, jo jums būs nepieciešama Excel funkcija VALUE.
Izmantojot Excel VALUE funkciju
Funkcija Excel VALUE var pārvērst ciparu vērtībā jebkuru teksta virkni, kas atgādina datumu vai skaitli, tāpēc tā ir ļoti noderīga, pārvēršot jebkuru skaitli, ne tikai datumus.
Funkcija VALUE:
=VĒRTĪBA(teksts)tekstu– teksta virkne, kuru vēlamies konvertēt, vai atsauce uz šūnu, kurā ir teksta virkne.
Piemēra formula teksta datuma konvertēšanai:
=VĒRTĪBA(A1)Tālāk norādītā formula VALUE var mainīt teksta virknes, kas izskatās kā datums, par skaitli, kā parādīts tālāk.
Tomēr funkcija VALUE neatbalsta visu veidu datuma vērtības. Piemēram, ja datumos tiek izmantotas decimāldaļas (A11), tiks atgriezta vērtība #VALUE! kļūda.
Kad esat ieguvis datuma sērijas numuru, šūna būs jāformatē ar datuma sērijas numuru, lai tā izskatītos pēc datuma, kā mēs to darījām funkcijai DATEVALUE. Lai to izdarītu, atlasiet sērijas numurus un cilnes Sākums nolaižamajā izvēlnē "Numura formāts" atlasiet opciju "Datums".
Tas arī viss, šie ir 5 dažādi veidi, kā programmā Excel var pārvērst datumus, kas formatēti kā teksts, par datumiem.