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.
Andmete puhastamise põhitõed
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.
Importige andmed välisest andmeallikast.
Looge algandmetest omaette töövihikusse varukoopia.
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.
Sooritage esmalt toimingud, mis ei eelda veergude töötlemist (nt õigekirjakontroll või dialoogiboksi Otsimine ja asendamine kasutamine).
Järgmiseks sooritage toimingud, mis eeldavad veergude töötlemist. Veergude töötlemise üldised toimingud on järgmised.
- Lisage uus veerg (B) puhastamist vajava algveeru (A) kõrvale.
- Lisage valem, mis teisendab uue veeru (B) ülaosas olevad andmed.
- Sisestage valem uues veerus (B). Exceli tabelis luuakse automaatselt arvutuslik veerg, mis täidetakse väärtustega alla.
- Valige uus veerg (B), kopeerige see ja kleepige seejärel väärtustena uude veergu (B).
- 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 |
|---|---|
| Töölehe lahtrite automaatne täitmine andmetega | Kirjeldab käsu Täitmine kasutamist. |
|
Tabelite koostamine ja vormindamine Tabeli suuruse muutmine ridade ja veergude lisamise või eemaldamisega 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. |
Õigekirjakontroll
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. |
Duplikaatridade eemaldamine
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. |
Teksti otsimine ja asendamine
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 |
|---|---|
|
Kontrollige, kas lahter sisaldab teksti (tõstutundetu). Kontrollige, kas lahter sisaldab teksti (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 | 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). |
Teksti täheregistri muutmine
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. |
Tühikute ja mitteprinditavate märkide eemaldamine tekstist
Mõnikord sisaldavad tekstiväärtused algus-, lõpu- või mitut manustatud tühikumärki (Unicode'i märgistiku väärtused 32 ja 160) või printimatuid märke (Unicode'i märgistiku väärtused 0–31, 127, 129, 141, 143, 144 ja 157). Need märgid võivad sortimisel, filtreerimisel või otsimisel mõnikord ootamatuid tulemeid põhjustada. Näiteks välises andmeallikas võivad kasutajad teha tüpograafilisi tõrkeid, lisades tahtmatult tühikuid või välistest allikatest imporditud tekstiandmed võivad sisaldada mitteprinditavaid märke, mis on teksti manustatud. Kuna neid märke pole kerge märgata, võib ootamatuid tulemeid olla raske mõista. Nende soovimatute märkide eemaldamiseks saate kasutada funktsioonide TRIM, CLEAN ja SUBSTITUTE kombinatsiooni.
| Lisateave | Kirjeldus |
|---|---|
| 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. |
Arvude ja numbrimärkide parandamine
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. |
Kuupäevade ja kellaaegade parandamine
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. |
Veergude ühendamine ja tükeldamine
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 ühendamine Teksti ja arvude ühendamine Teksti ühendamine kuupäeva- või kellaajaga Mitme 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. |
Veergude ja ridade teisendamine ja ümberkorraldamine
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. |
Tabeliandmete sobitamine ühendamise ja vastendamise teel
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. |
Muud teenusepakkujad
Järgnev on osaline loend muudest teenusepakkujatest, kes pakuvad tooteid andmete puhastamiseks.
Märkus.
Microsoft ei paku tuge muude ettevõtete toodete jaoks.