Napačno črkovane besede, neprilagodljivi končni presledki, neželene predpone, neprimerni časi in znaki, ki jih ni mogoče natisniti, naredijo slab prvi vtis. To pa niti ni celoten seznam načinov, kako lahko pokvarite svoje podatke. Zavihajte rokave. Čas je, da se lotite velikega spomladanskega čiščenja svojih delovnih listov z Microsoft Excelom.
Osnove čiščenje podatkov
Nimate vedno nadzora nad obliko zapisa in vrsto podatkov, ki jih uvozite iz zunanjega vira podatkov, kot je zbirka podatkov, besedilna datoteka ali spletna stran. Preden lahko analizirate podatke, jih morate pogosto očistiti. Na srečo je v Excelu na voljo veliko funkcij, s katerimi lahko pretvorite podatke v točno želeno obliko. Včasih je naloga enostavna in obstaja določena funkcija, ki vse opravi namesto vas. Tako lahko na primer s pregledovalnikom črkovanja očistite napačno črkovane besede v stolpcih, ki vsebujejo opombe ali opise. Ali pa, če želite odstraniti podvojene vrstice, lahko to hitro naredite v pogovornem oknu Odstranjevanje dvojnikov.
Kdaj drugič boste morda želeli spremeniti enega ali več stolpcev s formulo, da boste lahko pretvorili uvožene vrednosti v nove vrednosti. Če želite na primer odstraniti končne presledke, lahko ustvarite nov stolpec za čiščenje podatkov, tako da uporabite formulo, zapolnite nov stolpec, pretvorite formule novega stolpca v vrednosti in nato odstranite prvotni stolpec.
Osnovni koraki za čiščenje podatkov so:
Uvozite podatke iz zunanjega vira podatkov.
Ustvarite varnostno kopijo izvirnih podatkov v ločenem delovnem zvezku.
Prepričajte se, da so podatki v obliki tabele v vrsticah in stolpcih urejeni tako: podobni podatki v posameznih stolpcih, vsi stolpci in vrstice so vidni in v obsegu ni praznih vrstic. Za najboljše rezultate uporabite Excelovo tabelo.
Najprej izvedite opravila, pri katerih ni treba spreminjati stolpcev, na primer preverjanje črkovanja ali uporaba pogovornega okna Najdi in zamenjaj.
Nato izvedite opravila, pri katerih morate spremeniti stolpec. Splošna navodila za spreminjanje stolpca so:
- Vstavite nov stolpec (B) zraven prvotnega stolpec (A), ki ga morate očistiti.
- Dodajte formulo, ki bo preoblikovala podatke, na vrh novega stolpca (B).
- Izpolnite nov stolpec (B) s formulo. V Excelovi tabeli se samodejno ustvari izračunani stolpec z izpolnjenimi vrednostmi.
- Izberite nov stolpec (B), kopirajte ga in nato prilepite vrednosti v nov stolpec (B).
- Odstranite prvotni stolpec (A), ki pretvori novi stolpec iz B v A.
Če želite redno čisti isti vir podatkov, priporočamo, da posnamete makro ali zapišete kodo za avtomatizacijo celotnega postopka. Obstajajo tudi številni zunanji dodatki neodvisnih ponudnikov, navedeni v razdelku Neodvisni ponudniki, ki jih lahko uporabite, če nimate časa ali virov za avtomatizacijo postopka.
| Več informacij | Opis |
|---|---|
| Samodejno zapolnjevanje podatkov v celicah delovnega lista | Prikazana je uporaba ukaza Zapolni. |
|
Ustvarjanje in oblikovanje tabel Spreminjanje velikosti tabele z dodajanjem vrstic in stolpcev Uporaba izračunanih stolpcev v Excelovi tabeli |
Prikazan je način ustvarjanja Excelove tabele in dodajanje ali brisanje stolpcev ali izračunanih stolpcev. |
| Ustvarjanje makra | Predstavljenih je več načinov za avtomatizacijo ponavljajočih se opravil z makrom. |
Preverjanje črkovanja
Pregledovalnik črkovanja lahko poleg iskanja napačno črkovanih besed uporabite tudi za iskanje vrednosti, ki niso uporabljene dosledno, kot so imena izdelkov ali podjetij, tako da dodate te vrednosti v slovar po meri.
| Več informacij | Opis |
|---|---|
| Preverjanje črkovanja in slovnice | Prikazan je postopek popravljanja napačno črkovanih besed v delovnem listu. |
| Uporaba slovarjev po meri za dodajanje besed v pregledovalnik črkovanja | Razloženo je, kako se uporabljajo slovarji po meri. |
Odstranjevanje podvojenih vrstic
Podvojene vrstice predstavljajo pogosto težavo pri uvažanju podatkov. Preden odstranite podvojene vrednosti, priporočamo, da najprej filtrirate enolične vrednosti in tako preverite, ali imate želene rezultate.
| Več informacij | Opis |
|---|---|
| Filter za iskanje enoličnih vrednosti ali odstranjevanje podvojenih | Prikaže dva tesno povezana postopka: kako filtrirate enolične vrstice in odstranite podvojene. |
Iskanje in zamenjava besedila
Morda želite odstraniti skupen vodilni niz, na primer oznako, ki ji sledita dvopičje in presledek, ali pripono, na primer stavek v oklepaju na koncu niza, ki je zastarel ali nepotreben. To lahko storite tako, da poiščete primerke tega besedila in ga nato zamenjate brez besedila ali z drugim besedilom.
| Več informacij | Opis |
|---|---|
|
Preverjanje, ali celica vsebuje besedilo (ne razlikuje med velikimi in malimi črkami) Preverjanje, ali celica vsebuje besedilo (razlikovanje med velikimi in malimi črkami) |
Prikazana je uporaba ukaza Najdi in več funkcij za iskanje besedila. |
| Odstranjevanje znakov iz besedila | Prikazana je uporaba ukaza Zamenjaj in več funkcij za odstranjevanje besedila. |
| Iskanje ali zamenjava besedila in številk na delovnem listu | Prikazana je uporaba pogovornih oken Najdi in Zamenjaj. |
|
FIND, FINDB SEARCH, SEARCHB REPLACE, REPLACEB SUBSTITUTE LEFT, LEFTB RIGHT, RIGHTB LEN, LENB MID, MIDB |
To so funkcije, ki jih lahko uporabite za izvajanje različnih opravil spreminjanja nizov, kot so iskanje in zamenjava podniza v nizu, izvlečenje delov niza ali določanje dolžine niza. |
Spreminjanje velikih in malih črk v besedilu
Včasih so besedila zelo neurejena, še posebej, kar se tiče velikih in malih črk. Z eno ali več od treh funkcij za razlikovanj med malimi in velikimi črkami lahko pretvorite besedilo v male črke, na primer e-poštne naslove, velike črke, na primer kode izdelkov, ali velike in male črke, na primer imena ali naslove knjig.
| Več informacij | Opis |
|---|---|
| Spreminjanje velikih in malih črk | Prikazana je uporaba treh funkcij za razlikovanje med malimi in velikimi črkami. |
| LOWER | Pretvori vse velike črke v besedilnem nizu v male črke. |
| PROPER | Prvo črko vsake besede v besednem nizu spremeni v veliko začetnico (vse črke v besedilu, pred katerimi stoji drug znak kot črka). Vse druge črke pa spremeni v male črke. |
| UPPER | Pretvori besedilo v same velike črke. |
Odstranjevanje presledkov in nenatisljivih znakov iz besedila
Včasih besedilne vrednosti vsebujejo začetne, končne ali več vdelanih znakov presledka (vrednosti nabora znakov Unicode 32 in 160) ali znake, ki se ne natisnejo (vrednosti nabora znakov Unicode od 0 do 31, 127, 129, 141, 143, 144 in 157). Ti znaki lahko včasih povzročijo nepričakovane rezultate pri razvrščanju, filtriranju ali iskanju. V zunanjih virih podatkov lahko na primer uporabniki naredijo tipkarske napake, tako da nehote dodajo dodatne presledke, ali pa uvoženi besedilni podatki iz zunanjih virov vsebujejo nenatisljive znake, ki so vgrajeni v besedilu. Ker teh znakov ni mogoče preprosto opaziti, je nepričakovane rezultate morda težko razumeti. Če želite odstraniti te neželene znake, lahko uporabite kombinacijo funkcij TRIM, CLEAN in SUBSTITUTE.
| Več informacij | Opis |
|---|---|
| CODE | Vrne številsko kodo za prvi znak v besedilnem nizu. |
| CLEAN | Odstrani prvih 32 nenatisljivih znakov v 7-bitni kodi ASCII (vrednosti od 0 do 31) iz besedila. |
| TRIM | Odstrani 7-bitni znak za presledek ASCII (vrednost 32) iz besedila. |
| SUBSTITUTE | S funkcijo SUBSTITUTE lahko zamenjati znake Unicode z višjimi vrednostmi (vrednosti 127, 129, 141, 143, 144, 157 in 160) s 7-bitnimi znaki ASCII, za katere sta bili zasnovani funkciji TRIM in CLEAN. |
Določanje številk in znakov za števila
Obstajata dve glavni težavi s številkami, zaradi katerih boste morda morali počistiti podatke: število je bilo nehote uvoženo kot besedilo in znak za negativno vrednost je treba spremeniti skladno s standardom za vašo organizacijo.
| Več informacij | Opis |
|---|---|
| Pretvarjanje števil v obliki besedila v število | Prikazuje, kako pretvorite števila, ki so oblikovana in shranjena v celicah kot besedilo, kar lahko povzroča težave pri izračunavanju ali vrne nejasna zaporedja, v številsko obliko. |
| DOLLAR | Pretvori število v besedilno oblik in uporabi simbol valute. |
| TEXT | Pretvori vrednost v besedilo v določeni obliki zapisa številke. |
| FIXED | Zaokroži število na navedeno število decimalk, oblikuje število v decimalni obliki zapisa z uporabo vejice in pik ter vrne rezultat kot besedilo. |
| VALUE | Pretvori besedni niz, ki predstavlja število, v število. |
Določanje datumov in časov
Ker obstaja toliko različnih oblik zapisa datuma in ker je te oblike zapisa mogoče zamenjati z oštevilčenimi kodami delov ali drugimi nizi, ki vsebujejo poševnice ali pomišljaje, je treba datume in čase pogosto pretvoriti in preoblikovati.
| Več informacij | Opis |
|---|---|
| Spreminjanje prikaza sistema datumov, oblike ali dvomestne letnice | Opisuje, kako deluje datumski sistem v Office Excelu. |
| Pretvorba ure | Prikazuje, kako lahko pretvorite med različnimi časovnimi enotami. |
| Pretvarjanje datumov, ki so shranjeni kot besedilo, v datume | Prikazuje, kako pretvorite datume, ki so oblikovani in shranjeni v celicah kot besedilo, kar lahko povzroči težave pri izračunavanju ali vrne nejasna zaporedja, v številsko obliko. |
| DATE | Vrne zaporedno številko, ki predstavlja določen datum. Če je imela celica pred vnosom funkcije »splošno« obliko, se rezultat oblikuje kot datum. |
| DATEVALUE | Pretvori datum, ki je predstavljen v obliki besedila, v zaporedno število. |
| TIME | Vrne decimalno število določene ure. Če je imela celica pred vnosom funkcije splošno obliko, se rezultat oblikuje kot datum. |
| TIMEVALUE | Vrne decimalno število časa, ki ga predstavlja besedilni niz. Desetiško število, ki ga vrne funkcija TIME, je vrednost v obsegu od 0 (nič) do 0,99999999, pri tem pa predstavlja čase od 0:00:00 (12:00:00 AM) do 23:59:59 (11:59:59 PM). |
Spajanje in razdelitev stolpcev
Pogosto opravilo po uvozu podatkov iz zunanjega vira podatkov vključuje združevanje dveh ali več stolpcev v enega ali pa deljenje enega stolpca na dva ali več stolpcev. Morda želite na primer razdeliti stolpec, ki vsebuje polno ime v ime in priimek. Lahko pa razdelite stolpec, ki vsebuje polje z naslovom, na ločene stolpce z ulico, mestom, regijo in poštno številko. Lahko naredite tudi obratno. Morda boste želeli spojiti stolpec z imenom in priimkom v stolpec s polnim imenom in priimkom ali pa povezati ločene stolpce z naslovi v en stolpec. Dodatne skupne vrednosti, ki jih boste morda morali združiti v en stolpec ali razdeliti v več stolpec, zajemajo kode izdelkov, poti datotek in naslove internetnega protokola (IP).
| Več informacij | Opis |
|---|---|
|
Kombiniranje imen in priimkov Oblikovanje besedila in števil Kombinacija besedila z datumom ali uro Združevanje dveh ali več stolpcev s funkcijo |
Prikazani so tipični primeri združevanja vrednosti iz dveh ali več stolpcev. |
| Razdeljevanje besedila v različne stolpce s čarovnikom za pretvorbo besedila v stolpce | Prikazuje, kako lahko s čarovnikom razdelite stolpce glede na različna običajna ločila. |
| Razdeljevanje besedila v različne stolpce s funkcijami | Prikazuje, kako lahko s funkcijami LEFT, MID, RIGHT, SEARCH in LEN razdelite stolpec z imenom v dva ali več stolpcev. |
| Združevanje ali razdeljevanje vsebine celic | Prikazuje, kako lahko uporabite funkcijo CONCATENATE, & (znak za »in«) in čarovnika za pretvarjanje besedila v stolpce. |
| Spajanje celic ali razdruževanje spojenih celic | Prikazuje, kako lahko uporabite ukaze Spoji celice, Spoji prek in Spoji in usredini. |
| CONCATENATE | Združi dva ali več besedilnih nizov v en besedilni niz. |
Preoblikovanje in prerazporejanje stolpcev in vrstic
Večina analiz in funkcij oblikovanja v Office Excelu predpostavlja, da podatki obstaja v eni dvodimenzionalni tabeli. Včasih boste morda želeli, da vrstice postanejo stolpci in da se stolpci spremenijo v vrstice. Kdaj drugič podatki celo niso strukturirani v obliki tabele in potrebujete način za pretvorbo podatkov iz oblike zapisa, ki ni v tabeli, v obliko tabele.
| Več informacij | Opis |
|---|---|
| TRANSPOSE | Vrne navpični obseg celic kot vodoravni obseg ali obratno. |
Usklajevanje podatkov v tabeli z združevanjem ali ujemanjem
Občasno skrbniki zbirke podatkov z Office Excelom iščejo in popravljajo ujemajoče se napake, kadar sta združeni dve tabeli ali več. To lahko vključuje usklajevanju dveh tabel iz različnih delovnih listov, če si želite na primer ogledati vse zapise v obeh tabelah ali primerjati tabele in poiskati vrstice, ki se ne ujemajo.
| Več informacij | Opis |
|---|---|
| Iskanje vrednosti na seznamu podatkov | Prikaže običajne načine za iskanje podatkov z uporabo funkcije za iskanje. |
| LOOKUP | Vrne vrednost iz obsega ene vrstice ali enega stolpca ali iz polja. Funkcija »LOOKUP« ima dve obliki sintakse: vektorsko in matrično. |
| HLOOKUP | Išče vrednost v zgornji vrstici tabele ali polja vrednosti, in nato vrne vrednost v istem stolpcu iz vrstice, ki jo določite v tabeli ali polju. |
| VLOOKUP | Išče vrednost v prvem stolpcu matrike tabele in vrne vrednost v isti vrstici iz drugega stolpca v matriki tabele. |
| INDEX | Vrne vrednost ali sklic na vrednost iz tabele ali obsega. Obstajata dve obliki funkcije INDEX: oblika polja ali sklica. |
| MATCH | Vrne relativen položaj elementa v polju, ki se ujema z določeno vrednostjo v navedenem vrstnem redu. Ko vas zanima položaj elementa v obsegu in ne samo en element, uporabite funkcijo MATCH namesto ene od funkcij LOOKUP. |
| OFFSET | Vrne sklic na obseg, ki je določeno število vrstic in stolpcev, iz celice ali obsega celic. Vrnjeni sklic je lahko ena sama celica ali obseg celic. Določite lahko število vrstic in stolpcev, ki jih funkcija vrne. |
Neodvisni ponudniki
To je delni seznam neodvisnih ponudnikov, ki imajo izdelke, ki se uporabljajo za čiščenje podatkov na različne načine.
Opomba
Microsoft ne nudi podpore za izdelke neodvisnih proizvajalcev.