Kas tuleb ette, et impordite või sisestate Excelisse andmeid, mis sisaldavad algusnulle (nt 00123), või suuri arve (nt 1 234 567 890 876 543)? See hõlmab näiteks isikukoode, telefoninumbreid, krediitkaardinumbreid, tootekoode, kontonumbreid ja sihtnumbreid. Excel eemaldab valemite ja matemaatiliste tehete võimaldamiseks algusnullid automaatselt ja teisendab suured arvud teaduskujule (nt 1.23E+15). Selles artiklis selgitatakse, kuidas säilitada andmeid algses vormingus, kui Excel peab seda tekstiks.
Arvu teisendamine tekstiks tekstandmete importimisel
Exceli võimaluse Toomine ja teisendus (Power Query) abil saate vormindada andmete importimisel üksikuid veerge tekstina. Sel juhul impordime tekstifaili, kuid andmete teisendamise etapid on teistest allikatest (nt XML, Web, JSON jne) imporditud andmete puhul samad.
-
Klõpsake menüüd Andmed ja seejärel nupu Too andmed kõrval olevat nuppu Teksti-/CSV-failist. Kui nuppu Too andmed ei kuvata, valige Uus päring > Failist > Tekstist, otsige sirvides tekstifail üles ning klõpsake seejärel käsku Impordi.
-
Excel laadib andmed eelvaatepaanile. Valige eelvaatepaanil akna Päringuredaktor avamiseks käsk Redigeeri.
-
Kui veerge on vaja tekstiks teisendada, klõpsake teisendatava veeru valimiseks veerupäist ja valige seejärel Avaleht > Transformatsioon > Andmetüüp > Tekst.
Näpunäide.: Kombinatsiooni Ctrl + paremklõps abil saate valida mitu veergu.
-
Järgmiseks klõpsake dialoogiboksis Veerutüübi muutmine käsku Asenda praegune ja Excel teisendab valitud veerud tekstiks.
-
Kui see on valmis, klõpsake käsku Sule ja laadi ning Excel tagastab päringuandmed töölehele.
Kui andmed hiljem muutuvad, valige Andmed > Värskenda ja Excel värskendab andmed automaatselt ning rakendab transformatsiooni ise.
Rakendustes Excel 2010 ja 2013 on kaks võimalust tekstifailide importimiseks ja arvude tekstiks teisendamiseks. Soovitatav viis on kasutada lisandmoodulit Power Query, mis on saadaval, kui laadite alla Power Query lisandmooduli. Kui te ei saa Power Query lisandmoodulit alla laadida, saate kasutada Tekstiimpordiviisardit. Sel juhul impordime tekstifaili, kuid andmete teisendamise etapid on teistest allikatest (nt XML, Web, JSON jne) imporditud andmete puhul samad.
-
Klõpsake lindimenüüd Power Query ja valige seejärel Välisandmete toomine > Tekstist.
-
Excel laadib andmed eelvaatepaanile. Valige eelvaatepaanil akna Päringuredaktor avamiseks käsk Redigeeri.
-
Kui veerge on vaja tekstiks teisendada, klõpsake teisendatava veeru valimiseks veerupäist ja valige seejärel Avaleht > Transformatsioon > Andmetüüp > Tekst.
Näpunäide.: Kombinatsiooni Ctrl + paremklõps abil saate valida mitu veergu.
-
Järgmiseks klõpsake dialoogiboksis Veerutüübi muutmine käsku Asenda praegune ja Excel teisendab valitud veerud tekstiks.
-
Kui see on valmis, klõpsake käsku Sule ja laadi ning Excel tagastab päringuandmed töölehele.
Kui andmed hiljem muutuvad, valige Andmed > Värskenda ja Excel värskendab andmed automaatselt ning rakendab transformatsiooni ise.
Algusnullide säilitamine kohandatud arvuvormingu abil
Kui soovite probleemi lahendada ainult töövihikus, kuna teised programmid ei kasuta seda andmeallikana, saate algusnullide säilitamiseks kasutada kohandatud või erivormingut. See sobib numberkoodide korral, mis koosnevad vähem kui 16 numbrimärgist. Lisaks saate vormindada enda numberkoode kriipsjoonte vms kirjavahemärkidega. Näiteks telefoninumbri loetavamaks muutmiseks saate lisada kriipsud suunakoodi, kohaliku kaugekõnekoodi, piirkonnakoodi, eesliite ja ülejäänud numbrimärkide vahele.
|
Numberkood |
Näide |
Kohandatud arvuvorming |
|---|---|---|
|
Sotsiaalse |
012345678 |
000-00-0000 |
|
Telefon |
0012345556789 |
00-0-000-000-0000 |
|
Posti |
00123 |
00000 |
Juhised
-
Valige lahter või lahtrivahemik, mida soovite vormindada.
-
Vajutage dialoogiboksi Lahtrite vormindamine laadimiseks klahvikombinatsiooni Ctrl + 1.
-
Valige vahekaart Arv ja klõpsake seejärel loendis Kategooria väärtust Kohandatud ja tippige siis väljale Tüüp soovitud arvuvorming (nt 0-000000-0000 isikukoodi jaoks või 00000 viiekohalise sihtnumbri jaoks.
Näpunäide.: Samuti saate klõpsata väärtust Muud ja valida siis näiteks väärtuse Sihtnumber, Sihtnumber + 4, Telefoninumber või Isikukood.
Kohandatud koodide kohta leiate lisateavet teemast Kohandatud arvuvormingu loomine või kustutamine.
Märkus.: Sellega ei taastata algusnulle, mis eemaldati enne vormindamist. See mõjutab ainult pärast vormingu rakendamist sisestatud numbreid.
Funktsiooni TEXT kasutamine vormingu rakendamiseks
Saate kasutada andmete kõrval olevat tühja veergu ja seejärel teisendada selle funktsiooni TEXT abil soovitud vormingusse.
|
Numberkood |
Näide (lahtris A1) |
Funktsioon TEXT ja uus vorming |
|---|---|---|
|
Sotsiaalse |
012345678 |
=TEXT(A1;"000-00-0000") |
|
Telefon |
0012345556789 |
=TEXT(A1;"00-0-000-000-0000") |
|
Posti |
00123 |
=TEXT(A1;"00000") |
Krediitkaardinumbrid ümardatakse allapoole
Exceli arvutustäpsus piirdub 15 tüvenumbriga, mis tähendab, et 16-kohaliste või pikemate numbrite korral (nt krediitkaardinumbrid) ümardatakse 15.-st väiksemad numbrikohad nulliks. 16-kohaliste või pikemate numberkoodide korral tuleb kasutada tekstivormingut. Selleks on kaks võimalust.
-
Veeru vormindamine tekstina
Valige andmevahemik ja vajutage dialoogiboksi Vorming > Lahtrid käivitamiseks klahvikombinatsiooni Ctrl + 1. Klõpsake vahekaardil Arv väärtust Tekst.
Märkus.: Varem sisestatud numbreid see ei mõjuta. See mõjutab ainult pärast vormingu rakendamist sisestatud numbreid.
-
Ülakoma kasutamine
Kui kasutate ülakoma (') arvu ees, loeb Excel seda tekstiks.
Kas vajate rohkem abi?
Kui teil on küsimusi, saate need esitada Exceli tehnikakogukonnafoorumis, kus teile vastavad asjatundjad, või teistele kasutajatele kogukonnafoorumis.
