On monia tapoja muuntaa tekstiksi muotoiltuja päivämääriä todellisiksi päivämääriksi Excelissä, ja tämän opetusohjelman tarkoituksena on tutkia niitä kaikkia.
Kun tuot tietoja Exceliin, ne voivat olla monissa eri muodoissa, joita Excel ei tunnista. Joskus, kun päivämäärät tuodaan ulkoisesta lähteestä, ne tulevat muotoiltuina tekstinä.
On myös tärkeää tietää, että Excel käyttää päivämäärämuotoja järjestelmäsi alueasetusten perusteella. Joten kun tuot Exceliin eri maasta peräisin olevia tietoja, Excel ei välttämättä tunnista niitä ja tallenna niitä tekstimerkintöinä.
Jos sinulla on tämä ongelma, on monia tapoja, joilla voit muuntaa tekstin päivämääräksi Excelissä, ja käsittelemme ne kaikki tässä opetusohjelmassa.
Menetelmät tekstin muuntamiseksi päivämääräksi
Jos laskentataulukossasi on tekstimuotoiltuja päivämääriä todellisten päivämäärien sijaan, niitä ei voi käyttää laskelmissa. Joten sinun on muutettava ne takaisin todellisiksi päivämääriksi.
Jos päivämäärät on tasattu vasemmalle, se tarkoittaa, että ne on muotoiltu tekstiksi, koska tekstit tasataan oletuksena vasemmalle. Ja numerot ja päivämäärät on aina kohdistettu oikealle.
Voit muuntaa tekstin päivämääräksi Excelissä muutamalla eri tavalla. Ne ovat:
- Virheentarkistusvaihtoehto
- Excelin teksti sarakkeiksi -ominaisuus
- Etsi ja korvaa
- Liitä Erikoistyökalu
- Excel-kaava ja funktiot
Muunna teksti päivämääräksi käyttämällä virheentarkistusvaihtoehtoa
Excelissä on sisäänrakennettu virheentarkistusominaisuus, joka havaitsee joitain ilmeisiä virheitä tiedoissasi. Jos se löytää virheen, se näyttää pienen vihreän kolmion (virheilmaisimen) solun vasemmassa yläkulmassa, jossa virhe on. Jos valitset kyseisen solun, näkyviin tulee varoitusmerkki, jossa on keltainen huutomerkki. Kun siirrät osoittimen kyseisen merkin päälle, Excel ilmoittaa sinulle soluun mahdollisesti liittyvästä ongelmasta.
Kun esimerkiksi syötät vuoden kaksinumeroisessa muodossa päivämäärään, Excel olettaa päivämäärän tekstinä ja tallentaa sen tekstinä. Ja jos valitset kyseisen solun, näkyviin tulee huutomerkki ja varoitus: "Tämä solu sisältää päivämäärän merkkijonon, jossa on vain 2 numeroa vuodesta".
Jos soluissasi näkyy tämä virheilmaisin, napsauta huutomerkkiä, jolloin näyttöön tulee muutamia vaihtoehtoja, joilla voit muuntaa tekstiksi muotoillut päivämäärät todellisiksi päivämääriksi. Excel näyttää vaihtoehdot, joilla voit muuntaa sen muotoon 19XX tai 20XX (19XX vuodelle 1915, 20XX vuodelle 2015). Valitse sopiva vaihtoehto.
Tämän jälkeen teksti muunnetaan oikeaan päivämäärämuotoon.
Kuinka ottaa virheentarkistusvaihtoehto käyttöön Excelissä
Yleensä Virheentarkistus on oletusarvoisesti käytössä Excelissä. Jos virheentarkistustoiminto ei toimi sinulle, sinun on otettava Virheentarkistus käyttöön Excelissä.
Voit tehdä tämän napsauttamalla "Tiedosto"-välilehteä ja valitsemalla vasemmasta paneelista "Asetukset".
Napsauta Excelin asetukset -ikkunan vasemmassa paneelissa "Kaavat" ja ota oikeanpuoleisessa paneelissa käyttöön "Ota käyttöön taustavirheiden tarkistus" Virheentarkistus-osiossa. Ja rastita "Solut, jotka sisältävät vuodet ilmaistuna kahdella numerolla" Virheentarkistussäännöt-osiossa.
Muunna teksti päivämääräksi Excelin tekstistä sarakeominaisuuden avulla
Text to Column on loistava ominaisuus Excelissä, jonka avulla voit jakaa tiedot useisiin sarakkeisiin, ja se on myös tehokas työkalu tekstiarvojen muuntamiseen päivämääräarvoiksi. Tämä menetelmä tunnistaa useita erilaisia tietomuotoja ja muuntaa ne oikeaan päivämäärämuotoon.
Yksinkertaisten tekstijonojen muuntaminen päivämääriksi
Oletetaan, että päivämäärät on muotoiltu näin:
Voit käyttää ohjattua Text to Columns -toimintoa muotoillaksesi ne nopeasti takaisin päivämääriin.
Valitse ensin tekstimerkintöjen alue, jonka haluat muuntaa päivämääriksi. Siirry sitten nauhan "Data"-välilehteen ja napsauta "Teksti sarakkeisiin" -vaihtoehtoa Tietotyökalut-ryhmässä.
Teksti sarakkeiksi -velho tulee näkyviin. Valitse ohjatun Teksti sarakkeeksi -toiminnon vaiheessa 1 Alkuperäinen tietotyyppi -kohdassa Rajoitettu-vaihtoehto ja napsauta Seuraava.
Vaiheessa 2 poista valinta kaikista Erottimet-ruuduista ja napsauta Seuraava.
Ohjatun toiminnon viimeisessä vaiheessa valitse "Päivämäärä" kohdasta Saraketietomuoto ja valitse päivämäärämuoto "Päivämäärät"-kohdan vieressä olevasta avattavasta luettelosta ja napsauta "Valmis"-painiketta. Meidän tapauksessamme muunnamme tekstin päivämäärät, jotka esitetään muodossa "01 02 1995" (päivä kuukausi vuosi), joten valitsemme "Päivämäärä"-pudotusvalikosta "DMY".
Nyt Excel muuntaa tekstin päivämäärät todellisiksi päivämääriksi ja näyttää ne soluissa oikealle tasattuna.
merkintä: Ennen kuin aloitat tekstistä sarakkeeksi -ominaisuuden käyttämisen, varmista, että kaikki tekstimerkkijonosi ovat samassa muodossa, sillä muuten tekstejä ei muunneta. Jos esimerkiksi jotkin tekstin päivämäärät on muotoiltu muotoon kuukausi/päivä/vuosi (MDY), kun taas toiset ovat päivä/kuukausi/vuosi (DMY), ja kun valitset DMY vaiheessa 3, saat virheellisiä tuloksia. Kuten alla olevassa kuvassa näkyy.
Muunnetaan monimutkainen tekstimerkkijono päivämääräksi
Teksti sarakkeiksi -ominaisuus on kätevä, kun haluat muuntaa monimutkaiset tekstijonot päivämääriksi. Sen avulla voit käyttää erottimia sen tunnistamiseen, missä tietosi tulisi jakaa ja näyttää kahdessa tai useammassa sarakkeessa. Ja yhdistä päivämäärän jaetut osat kokonaiseksi päivämääräksi käyttämällä PÄIVÄYS-toimintoa.
Jos päivämäärät näkyvät esimerkiksi moniosaisissa tekstijonoissa, kuten näin:
Keskiviikkona 01.2.2020
1.2.2020, klo 16.10
Ohjatun Teksti sarakkeeseen -toiminnon avulla voit erottaa pilkulla rajatut päivä-, päivämäärä- ja aikatiedot ja näyttää ne useissa sarakkeissa.
Valitse ensin kaikki tekstijonot, jotka haluat muuntaa päivämääriksi. Napsauta "Teksti sarakkeisiin" -painiketta "Tiedot" -välilehdellä. Valitse ohjatun Teksti sarakkeeksi -toiminnon vaiheessa 1 Alkuperäinen tietotyyppi -kohdassa Rajoitettu-vaihtoehto ja napsauta Seuraava.
Valitse ohjatun toiminnon vaiheessa 2 merkkijonojen sisältämät erottimet ja napsauta Seuraava. Esimerkkitekstijonot pilkulla ja välilyönnillä erotettuina – "maanantai, 01. helmikuuta 2015, 13:00". Meidän tulisi valita "pilkku" ja "välilyönti" erottimiksi jakaaksesi tekstijonot useisiin sarakkeisiin.
Valitse viimeisessä vaiheessa "Yleinen" -muoto kaikille sarakkeille Tietojen esikatselu -osiossa. Määritä, mihin sarakkeet tulee lisätä Kohde-kenttään. Jos et, se korvaa alkuperäiset tiedot. Jos haluat jättää huomioimatta osan alkuperäisistä tiedoista, napsauta sitä Tietojen esikatselu -osiossa ja valitse Älä tuo saraketta (ohita) -vaihtoehto. Napsauta sitten "Valmis".
Nyt päivämäärien osat (viikonpäivät, kuukausi, vuosi, aika) on jaettu sarakkeisiin B, C, D, E, F ja G.
Yhdistä sitten päivämääräosat yhteen DATE-kaavan avulla saadaksesi koko päivämäärän.
Excelin DATE-funktion syntaksi:
=PÄIVÄYS(vuosi,kuukausi,päivä)Esimerkissämme kuukausi-, päivä- ja vuosiosat ovat sarakkeissa C, D ja E.
PÄIVÄYS-toiminto tunnistaa vain numerot, ei tekstiä. Koska kuukausiarvomme sarakkeessa C ovat kaikki tekstimerkkijonoja, meidän on muutettava ne numeroiksi. Tätä varten sinun on vaihdettava kuukauden nimi kuukauden numeroksi KUUKAUSI-toiminnolla.
Jos haluat muuntaa kuukauden nimen kuukauden numeroksi, käytä tätä KUUKAUSI-funktiota DATE-funktiossa:
=KUUKAUSI(1&C1)KUUKAUSI-funktio lisää 1:n soluun C2, joka sisältää kuukauden nimen, ja muuttaa kuukauden nimen vastaavaksi kuukauden numeroksi.
Tämä on PÄIVÄMÄÄRÄ-funktio, jota meidän on käytettävä yhdistämään päivämääräosia eri sarakkeista:
=PÄIVÄYS(E1,KUUKAUSI(1&C1),D1)
Käytä nyt kaavasolun alakulmassa olevaa täyttökahvaa ja käytä kaavaa sarakkeeseen.
Muunna teksti päivämääräksi Etsi ja korvaa -menetelmällä
Tämä menetelmä muuttaa tekstin muodon päivämääriksi erottimien avulla. Jos päivä, kuukausi ja vuosi on erotettu muulla erottimella kuin yhdysviivalla (-) tai kauttaviivalla (/), Excel ei tunnista niitä päivämääriksi ja tallentaa ne tekstinä.
Voit korjata tämän ongelman käyttämällä Etsi ja korvaa -ominaisuutta. Muuttamalla poikkeavat pisteen erottimet (.) vinoviivalla (/) tai viivalla (-), Excel muuttaa arvot automaattisesti päivämääriksi.
Valitse ensin kaikki tekstipäivämäärät, jotka haluat muuntaa päivämääriksi. Napsauta "Etusivu"-välilehdellä "Etsi ja valitse" -painiketta nauhan oikeassa reunassa ja valitse "Korvaa". Vaihtoehtoisesti paina Ctrl+H avataksesi Etsi ja korvaa -valintaikkunan.
Kirjoita Etsi ja korvaa -valintaikkunassa tekstisi sisältämä erotin (tässä tapauksessa piste (.) Etsi mitä -kenttään ja vinoviiva (/) tai viiva (-) Korvaa tällä -kenttään. . Napsauta "Korvaa kaikki" -painiketta korvataksesi erottimet ja napsauta "Sulje" sulkeaksesi ikkunan.
Nyt Excel tunnistaa, että tekstimerkkijonosi ovat nyt päivämääriä ja muotoilee ne automaattisesti päivämääriksi. Päivämääräsi kohdistetaan oikealle alla olevan kuvan mukaisesti.
Muunna teksti päivämääräksi Liitä erikoistyökalulla
Toinen nopea ja helppo tapa muuntaa tekstimerkkijonot päivämääriksi on lisätä 0 tekstimerkkijonoon käyttämällä erityistä liitätoimintoa. Nollan lisääminen arvoon muuntaa tekstin päivämäärän sarjanumeroksi, jonka voit muotoilla päivämääräksi.
Valitse ensin tyhjä solu ja kopioi se (valitse se ja paina Ctrl + C kopioida).
Valitse sitten solut, jotka sisältävät muunnettavat tekstipäivämäärät, napsauta hiiren kakkospainikkeella ja valitse Liitä määräten -vaihtoehto.
Valitse Liitä määräten -valintaikkunan Liitä-osiosta Kaikki, valitse Toiminto-osiosta Lisää ja napsauta OK.
Voit myös suorittaa muita aritmeettisia operaatioita vähennä/kerro/jaa kohdesolun arvo liitetyllä arvolla (kuten kerro solut 1:llä tai jaa 1:llä tai vähennä nolla).
Kun valitset kohdasta "Lisää", se lisää "nollan" kaikkiin valittuihin tekstipäivämääriin, koska "0":n lisääminen ei muuta arvoja, vaan saat sarjanumeron jokaiselle päivämäärälle. Nyt sinun tarvitsee vain muuttaa solujen muotoa.
Valitse sarjanumerot ja napsauta Koti-välilehdellä Numero-ryhmän avattavaa Numeromuoto-luetteloa. Valitse avattavasta valikosta vaihtoehto "Lyhyt päivämäärä".
Kuten näet nyt, numerot on muotoiltu päivämääriksi ja tasattu oikealle.
Muunna teksti päivämääräksi kaavojen avulla
Tekstin muuntamiseen päivämääräksi ensisijaisesti käytetään kahta funktiota: DATEVALUE ja VALUE.
Excelin DATEVALUE-funktion käyttö
Excelin PÄIVÄYSARVO-funktio on yksi helpoimmista tavoista muuntaa tekstinä esitetty päivämäärä päivämäärän sarjanumeroksi.
DATEVALUE-funktion syntaksi:
=PÄIVÄYSARVO(päivämäärän_teksti)Perustelu: päivämäärä_teksti määrittää tekstimerkkijonon, jonka haluat peittää tai viitata päivämäärätekstiä sisältävään soluun.
Kaava:
=PÄIVÄYSARVO(A1)Seuraava kuva havainnollistaa, kuinka PÄIVÄYSARVO-funktio käsittelee muutamia erilaisia päivämäärämuotoja, jotka tallennetaan tekstinä.
Sait päivämäärän sarjanumerot, nyt sinun on käytettävä päivämäärämuotoa näihin numeroihin. Voit tehdä tämän valitsemalla solut, joissa on sarjanumerot, siirtymällä sitten "Koti"-välilehteen ja valitsemalla "Lyhyt päivämäärä" avattavasta "Numeromuoto"-luettelosta.
Nyt sinulla on muotoillut päivämäärät sarakkeessa C.
Vaikka tekstipäivämäärässä (A8) ei olisi vuosiosaa, DATEVALUE käyttää kuluvaa vuotta tietokoneesi kellosta.
PÄIVÄYSARVO-funktio muuntaa vain päivämäärältä näyttävät tekstiarvot. Se ei voi muuntaa numeroa muistuttavaa tekstiä päivämääräksi, eikä se voi muuttaa numeroarvoa päivämääräksi, sillä tarvitset Excelin ARVO-funktion.
Excelin VALUE-funktion käyttäminen
Excelin ARVO-funktio pystyy muuttamaan minkä tahansa päivämäärää tai numeroa muistuttavan tekstijonon numeerisiksi arvoiksi, joten se on todella hyödyllinen muunneltaessa mitä tahansa lukua, ei vain päivämääriä.
ARVO-funktio:
=ARVO(teksti)teksti– tekstimerkkijono, jonka haluamme muuntaa, tai viittaus tekstijonon sisältävään soluun.
Esimerkkikaava tekstin päivämäärän muuntamiseksi:
=ARVO(A1)Alla oleva kaava VALUE voi muuttaa päivämäärältä näyttävät tekstijonot numeroiksi alla olevan kuvan mukaisesti.
ARVO-funktio ei kuitenkaan tue kaikentyyppisiä päivämääräarvoja. Jos esimerkiksi päivämäärät käyttävät desimaaleja (A11), se palauttaa #ARVO! virhe.
Kun sinulla on päivämääräsi sarjanumero, sinun on muotoiltava solu päivämäärän sarjanumerolla, jotta se näyttää päivämäärältä, kuten olemme tehneet PÄIVÄYSARVO-funktiolle. Voit tehdä tämän valitsemalla sarjanumerot ja valitsemalla Aloitus-välilehden avattavasta Numeromuoto-valikosta Päivämäärä.
Siinä kaikki, nämä ovat 5 eri tapaa muuntaa tekstiksi muotoiltuja päivämääriä päivämääriksi Excelissä.