Algusnullide ja suurte arvude säilitamine

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.

  1. 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.

  2. Excel laadib andmed eelvaatepaanile. Valige eelvaatepaanil akna Päringuredaktor avamiseks käsk Redigeeri.

  3. Kui veerge on vaja tekstiks teisendada, klõpsake teisendatava veeru valimiseks veerupäist ja valige seejärel Avaleht > Transformatsioon > Andmetüüp > Tekst.

    Power Query: andmed tekstiks teisendamise järel

    Näpunäide. : Kombinatsiooni Ctrl + paremklõps abil saate valida mitu veergu.

  4. Järgmiseks klõpsake dialoogiboksis Veerutüübi muutmine käsku Asenda praegune ja Excel teisendab valitud veerud tekstiks.

    Toomine ja teisendus > Teisenda andmed tekstiks
  5. 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.

  1. Klõpsake lindimenüüd Power Query ja valige seejärel Välisandmete toomine > Tekstist.

  2. Excel laadib andmed eelvaatepaanile. Valige eelvaatepaanil akna Päringuredaktor avamiseks käsk Redigeeri.

  3. Kui veerge on vaja tekstiks teisendada, klõpsake teisendatava veeru valimiseks veerupäist ja valige seejärel Avaleht > Transformatsioon > Andmetüüp > Tekst.

    Power Query: andmed tekstiks teisendamise järel

    Näpunäide. : Kombinatsiooni Ctrl + paremklõps abil saate valida mitu veergu.

  4. Järgmiseks klõpsake dialoogiboksis Veerutüübi muutmine käsku Asenda praegune ja Excel teisendab valitud veerud tekstiks.

    Toomine ja teisendus > Teisenda andmed tekstiks
  5. 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

Isikukood

012345678

000-00-0000 
012-34-5678 

Telefon

0012345556789

00-0-000-000-0000 
00-1-234-555-6789 

Sihtnumber

00123

00000 
00123 

Juhised   

  1. Valige lahter või lahtrivahemik, mida soovite vormindada.

  2. Vajutage dialoogiboksi Lahtrite vormindamine laadimiseks klahvikombinatsiooni Ctrl + 1.

  3. 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

Isikukood

012345678

=TEXT(A1;"000-00-0000")
012-34-5678

Telefon

0012345556789

=TEXT(A1;"00-0-000-000-0000")
00-1-234-555-6789

Sihtnumber

00123

=TEXT(A1;"00000")
00123

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.

Lehe algusse

Kas vajate rohkem abi?

Võite oma küsimuse alati esitada mõnele Exceli tehnikakogukonna eksperdile, otsida abi vastustefoorumist või soovitada mõnd uut funktsiooni või täiustust Exceli User Voice’i lehel.

Täiendage oma oskusi
Tutvuge koolitusmaterjalidega
Kasutage uusi funktsioone enne teisi
Liituge Office Insideri programmiga

Kas sellest teabest oli abi?

Täname tagasiside eest!

Täname tagasiside eest! Tundub, et võiksime teid kokku viia ühega meie Office'i tugiagentidest, kes aitab teil probleemi lahendada.

×