Kümme parimat viisi andmete puhastamiseks

Valesti kirjutatud sõnad, allumatud lõputühikud, soovimatud eesliited, valed käändelõpud ja mitteprinditavad märgid loovad halva esmamulje. Ja see pole veel kõik, mis teie andmetega juhtuda võib. Käärime käised üles! On aeg teha Microsoft Exceli töölehtedel korralik suurpuhastus.

Alati pole võimalik kontrollida välisest andmeallikast (nt andmebaas, tekstifail või veebileht) imporditavate andmete vormingut ja tüüpi. Enne andmete analüüsima asumist tuleb need sageli esmalt puhastada. Õnneks on Excelis mitmeid funktsioone, mis aitavad andmed panna just soovitud vormingusse. Mõnikord on ülesanne lihtne ja on olemas spetsiaalne funktsioon, mis töö teie eest ära teeb. Näiteks saate kommentare ja kirjeldusi sisaldaval väljal olevad valesti kirjutatud sõnad parandada õigekirjakontrolli abil. Kui soovite eemaldada duplikaatread, saate seda kiiresti teha dialoogiboksis Duplikaatridade eemaldamine.

Mõnikord aga on vaja töödelda ühte või mitut veergu, kasutades imporditud väärtuste uuteks väärtusteks teisendamiseks valemeid. Kui soovite näiteks eemaldada lõputühikuid, saate valemi abil luua uue veeru andmete puhastamiseks, täita uue veeru, teisendada uue veeru valemid väärtusteks ning seejärel algse veeru eemaldada.

Andmete puhastamise põhitoimingud on järgmised.

  1. Importige andmed välisest andmeallikast.

  2. Looge algandmetest omaette töövihikusse varukoopia.

  3. Veenduge, et kõik andmed oleksid esitatud tabelina ehk ridades ja veergudes: igas veerus sarnased andmed, kõik veerud ja read nähtavad, vahemikus pole ühtegi tühja rida. Parima tulemuse saamiseks kasutage Exceli tabelit.

  4. Sooritage esmalt toimingud, mis ei eelda veergude töötlemist (nt õigekirjakontroll või dialoogiboksi Otsimine ja asendamine kasutamine).

  5. Järgmiseks sooritage toimingud, mis eeldavad veergude töötlemist. Veergude töötlemise üldised toimingud on järgmised.

    1. Lisage uus veerg (B) puhastamist vajava algveeru (A) kõrvale.

    2. Lisage valem, mis teisendab uue veeru (B) ülaosas olevad andmed.

    3. Sisestage valem uues veerus (B). Exceli tabelis luuakse automaatselt arvutuslik veerg, mis täidetakse väärtustega alla.

    4. Valige uus veerg (B), kopeerige see ja kleepige seejärel väärtustena uude veergu (B).

    5. Eemaldage algne veerg (A): uus veerg teisendatakse nüüd B-st A-ks.

Sama andmeallika regulaarseks puhastamiseks soovitame salvestada makro või kirjutada koodi, mis automatiseerib kogu protsessi. Samuti leidub muude osapoolte kirjutatud väliseid lisandmooduleid, mis on ära toodud jaotises Muud tootjad ning mida võite kasutada, kui teil pole protsessi iseseisvaks automatiseerimiseks aega või vahendeid.

Lisateave

Kirjeldus

Ülevaade andmete ühendamisest (importimisest)

Kirjeldab kõiki välisandmete Office Excelisse importimise viise.

Töölehe lahtrite automaatne täitmine andmetega

Kirjeldab käsu Täitmine kasutamist.

Exceli tabeli loomine või kustutamine

Exceli tabeli ridade ja veergude lisamine ja eemaldamine

Arvutuslike veergude kasutamine Exceli tabelis

Kirjeldab, kuidas luua Exceli tabelit ning lisada või kustutada veerge ja arvutuslikke veerge.

Makro loomine

Kirjeldab viise, kuidas automatiseerida korduvaid ülesandeid makro abil.

Lisaks valesti kirjutatud sõnade leidmisele saab õigekirjakontrolli kasutada ka selliste väärtuste leidmiseks, mida ei kasutata järjepidevalt (nt toodete või ettevõtete nimed). Selleks tuleb need väärtused lisada kohandatud sõnastikku.

Lisateave

Kirjeldus

Õigekirja ja grammatika kontrollimine

Kirjeldab, kuidas parandada töölehel valesti kirjutatud sõnu.

Kohandatud sõnastike abil õigekirjakontrollile sõnade lisamine

Selgitab, kuidas kasutada kohandatud sõnastikke.

Duplikaatread on andmete importimisel levinud probleem. Soovitame enne duplikaatväärtuste eemaldamist filtreerida kordumatud väärtused, et tagada soovitud tulemused.

Lisateave

Kirjeldus

Ainuväärtuste filtreerimine või duplikaatväärtuste eemaldamine

Kirjeldab kahte lähedalt seotud protseduuri: kuidas filtreerida kordumatuid ridu ja kuidas eemaldada duplikaatridu.

Kui teie andmetes leidub aegunud või mittevajalikke korduvaid eesstringe (nt kooloni ja tühikuga lõppev silt) või järelliiteid (nt sulgudes olev fraas stringi lõpus), saate need eemaldada. Selleks tuleb leida selle teksti esinemiskorrad ja tekst kustutada või asendada muu tekstiga.

Lisateave

Kirjeldus

Tähemärkide olemasolu kontrollimine lahtris (tõstutundetu)

Tähemärkide olemasolu kontrollimine lahtris (tõstutundlik)

Kirjeldab, kuidas kasutada käsku Otsi ja mitmesuguseid funktsioone teksti otsimiseks.

Märkide eemaldamine tekstist

Kirjeldab, kuidas kasutada käsku Asenda ja mitmesuguseid funktsioone teksti eemaldamiseks.

Teksti ja arvude otsimine ja asendamine töölehel

Otsing ja asendus

Kirjeldab, kuidas kasutada dialoogibokse Otsing ja Asendus.

FIND, FINDB

SEARCH, SEARCHB

REPLACE, REPLACEB

SUBSTITUTE

LEFT, LEFTB

RIGHT, RIGHTB

LEN, LENB

MID, MIDB

Neid funktsioone saate kasutada mitmesuguste stringitöötlustoimingute sooritamiseks (nt alamstringi otsimine ja asendamine stringiga, stringiosade ekstraktimine või stringi pikkuse määramine).

Imporditud tekst ei pruugi alati olla ühtne ja korralik, eriti suur- ja väiketähtede järjepideva kasutamise osas. Ühe või mitme täheregistrifunktsiooni abil saate teksti teisendada väiketäheliseks(nt meiliaadressid), suurtäheliseks (nt tootekoodid) või algsuurtähega tekstiks (nt nimed või ajakirjade pealkirjad).

Lisateave

Kirjeldus

Teksti täheregistri muutmine

Kirjeldab, kuidas kasutada kolme täheregistrifunktsiooni.

LOWER

Teisendab kõik tekstistringi suurtähed väiketähtedeks.

PROPER

Teisendab tekstistringi esimese tähe suurtäheks, samuti teksti kõik teised tähed, mis järgnevad suvalisele märgile, mis pole täht. Kõik teised tähed teisendab väiketähtedeks.

UPPER

Teisendab teksti suurtähtedeks.

Vahel sisaldavad tekstiväärtused manustatud algus-, lõpu- või topelttühiku märke (Unicode-märgistiku väärtused 32 ja 160) või printimatuid märke (Unicode-märgistiku väärtused 0–31, 127, 129, 141, 143, 144 ja 157). Need märgid võivad mõnikord põhjustada sortimisel, filtreerimisel või otsimisel ootamatuid tulemusi. Näiteks võivad kasutajad teha välises andmeallikas trükivigu, lisades kogemata ülearuseid tühikumärke. Samuti võivad välisallikatest imporditud andmed sisaldada teksti lisatud printimatuid märke. Kuna neid märke pole lihtne märgata, võivad ootamatud tulemused olla raskesti mõistetavad. Neid soovimatuid märke saate eemaldada funktsioonide TRIM, CLEAN ja SUBSTITUTE kombinatsioonide abil.

Lisateave

Kirjeldus

Kirjeldab, kuidas eemaldada Unicode-märgistikust kõik tühikud ja mitteprinditavad märgid.

CODE

Annab vastuseks tekstistringi esimese märgi arvkoodi.

CLEAN

Eemaldab tekstist esimesed 32 printimatut märki 7-bitises ASCII koodis (väärtused 0–31).

TRIM

Eemaldab tekstist 7-bitise ASCII tühikumärgi (väärtus 32).

SUBSTITUTE

Funktsiooni SUBSTITUTE saate kasutada suurema väärtusega Unicode’i märkide (väärtused 127, 129, 141, 143, 144, 157 ja 160) asendamiseks 7-bitise ASCII-märgistiku märkidega, mille jaoks on loodud funktsioonid TRIM ja CLEAN.

Arvudega seostub kaks peamist probleemi, mille korral tuleb andmed puhastada: arv imporditi tahtmatult tekstina või tuleb negatiivne märk muuta teie ettevõtte jaoks standardseks.

Lisateave

Kirjeldus

Tekstina talletatud arvude teisendamine arvudeks

Kirjeldab, kuidas teisendada arvud, mis on vormindatud ja lahtrites talletatud tekstina ning mis võivad põhjustada probleeme arvutustes või tekitada segadust sortimisjärjestuses, tagasi arvuvormingusse.

DOLLAR

Teisendab arvu tekstivormingusse ja rakendab valuutatähise.

TEXT

Teisendab väärtuse kindlas arvuvormingus tekstiks.

FIXED

Ümardab arvu määratud kümnendkohani, vormindab selle kümnendarvuna, kasutades punkti ja komasid ning tagastab tulemi tekstina.

VALUE

Teisendab arvu tähistava tekstistringi arvuks.

Kuna kuupäevavorminguid on palju ja need vormingud võib segi ajada nummerdatud osakoodide või muude kald- ja sidekriipse sisaldavate stringidega, tuleb kuupäevad ja kellaajad sageli teisendada ja uuesti vormindada.

Lisateave

Kirjeldus

Kuupäevasüsteemi, vormingu või kahekohaliste aastaarvude tõlgendamisviisi muutmine

Kirjeldab kuupäevasüsteemi toimimist Office Excelis.

Kellaaegade teisendamine

Kirjeldab, kuidas teisendada eri ajaühikuid.

Tekstina salvestatud kuupäevade teisendamine kuupäevadeks

Kirjeldab, kuidas teisendada kuupäevad, mis on vormindatud ja lahtrites talletatud tekstina ning mis võivad põhjustada probleeme arvutustes või tekitada segadust sortimisjärjestuses, kuupäevavormingusse.

DATE

Tagastab järjenumbri, mis tähistab kindlat kuupäeva. Kui enne funktsiooni sisestamist oli lahtri vorminguks Üldine, vormindatakse tulemus kuupäevana.

DATEVALUE

Teisendab tekstivormis esitatud kuupäeva järjenumbriks.

TIME

Tagastab aja asemel kümnendsüsteemis arvu. Kui lahtri vorming enne funktsiooni sisestamist oli Üldist, vormindatakse tulemus kuupäevaks.

TIMEVALUE

Tagastab kümnendsüsteemis arvu kellaaega tähistava tekstistringi asemel. Arvu väärtus jääb vahemikku 0 (null) kuni 0,99999999, mis tähistab kellaaegu alates 0:00:00 kuni 23:59:59.

Pärast välisest andmeallikast andmete importimist on levinud kahe või enama veeru ühendamine üheks veeruks või ühe veeru lahutamine kaheks või enamaks veeruks. Näiteks võite tükeldada veeru, mis sisaldab täisnime, eraldi ees- ja perekonnanime sisaldavaks veeruks. Samuti võite tükeldada veeru, mis sisaldab aadressivälja, omaette tänava, linna, piirkonna ja sihtnumbri veergudeks. Toimida võib ka vastupidi. Võite ühendada ees- ja perekonnanime veerud täisnimeveeruks või ühendada eraldi aadressiveerud ühte veergu. Levinumad väärtused, mida võib ühte veergu ühendada või mitmesse veergu tükeldada, on näiteks tootekoodid, failiteed ja IP-aadressid.

Lisateave

Kirjeldus

Ees- ja perekonnanimede kombineerimine

Teksti ja arvude kombineerimine

Teksti kombineerimine kuupäeva või kellaajaga

Kahe või enama veeru ühendamine funktsiooni abil

Kirjeldab tüüpilisi kahe või enama veeru väärtuste ühendamise näiteid.

Teksti tükeldamine eraldi veergudesse viisardi „Tekst veergudesse“ abil

Kirjeldab, kuidas kasutada viisardit veergude tükeldamises mitmesuguste üldiste eraldajate alusel.

Teksti tükeldamine eraldi veergudesse funktsioonide abil

Kirjeldab, kuidas kasutada funktsioone LEFT, MID, RIGHT, SEARCH ja LEN nimeveeru tükeldamisel kaheks või enamaks veeruks.

Lahtrite ühendamine ja tükeldamine

Kirjeldab, kuidas kasutada funktsiooni CONCATENATE, &-operaatorit (ampersandi) ja teksti veergudeks teisendamise viisardit.

Lahtrite ühendamine ja ühendatud lahtrite tükeldamine

Kirjeldab, kuidas kasutada käske Ühenda lahtrid, Ühenda horisontaalsuunas ning Ühenda ja tsentreeri.

CONCATENATE

Ühendab kaks või enam tekstistringi üheks tekstistringiks.

Enamik Office Exceli analüüsi- ja vormindamisfunktsioone eeldab, et andmed paiknevad ühes kahemõõtmelises lametabelis. Vahel on aga vaja muuta read veergudeks ja veerud omakorda ridadeks. Teinekord pole andmed tabelina struktureeritud ning tuleb leida viis teisendada tabelina mitte kuvatavad andmed tabelivormingusse.

Lisateave

Kirjeldus

TRANSPOSE

Tagastab lahtrite vertikaalse vahemiku horisontaalse vahemikuna või vastupidi.

Vahel kasutavad andmebaasi administraatorid kahe või enama tabeli liitmisel tekkivate vastendusvigade otsimiseks ja parandamiseks Office Excelit. See võib tähendada eri töölehtedel oleva kahe tabeli sobitamist (nt mõlema tabeli kõigi kirjete kuvamiseks või tabelite võrdlemiseks ja mittevastenduvate ridade leidmiseks).

Lisateave

Kirjeldus

Andmeloendist väärtuste otsimine

Kirjeldab kõige levinumaid viise andmete otsimiseks otsingufunktsioonide abil.

LOOKUP

Tagastab väärtuse kas üherealisest või üheveerulisest vahemikust või massiivist. Funktsioonil LOOKUP on kaks süntaksikuju: vektor- ja massiivkuju.

HLOOKUP

Otsib väärtust tabeli või väärtustemassiivi ülemisest reast ja seejärel tagastab väärtuse tabeli või massiivi sama veeru määratud reast.

VLOOKUP

Otsib väärtust massiivi vasakpoolseimast veerust ja tagastab väärtuse massiivi sama rea mõnest muust veerust.

INDEX

Tagastab tabelist või vahemikust väärtuse või viite väärtusele. Funktsioonil INDEX on kaks kuju: massiivkuju ja viitekuju.

MATCH

Tagastab määratud järjestuses oleva määratud väärtusega sobiva üksuse suhtelise asendi massiivis. Kasutage funktsiooni MATCH mõne LOOKUP-funktsiooni asemel, kui vajate üksuse enda asemel selle asendit vahemikus.

OFFSET

Tagastab viite vahemikule, mis on määratud arv ridu ja veerge kõrval antud lahtrist või lahtrite vahemikust. Tagastatav viide võib olla üks lahter või lahtrite vahemik. Võite määrata tagastatavate ridade ja tagastatavate veergude arvu.

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.

×