Nepareizi uzrakstīti vārdi, spītīgas beigu atstarpes, nevēlami priedēkļi, nepareizi raksti un nedrukājamas rakstzīmes rada sliktu pirmo iespaidu. Un tas pat nav pilnīgs saraksts ar veidiem, kā jūsu dati var tikt netīri. Atlociet piedurknes. Pienācis laiks veikt darblapu pavasara tīrīšanu, izmantojot programmu Microsoft Excel.
Datu tīrīšanas pamati
Ne vienmēr varat kontrolēt datu formātu un tipu, kurus importējat no ārēja datu avota, piemēram, datu bāzes, teksta faila vai tīmekļa lapas. Pirms datu analīzes bieži ir nepieciešams tos iztīrīt. Par laimi, programmā Excel ir daudz līdzekļu, kas palīdz iegūt datus tieši vajadzīgajā formātā. Dažreiz uzdevums ir vienkāršs, un ir īpaša funkcija, kas veic darbu jūsu vietā. Piemēram, varat viegli izmantot pareizrakstības pārbaudītāju, lai iztīrītu nepareizi uzrakstītus vārdus kolonnās, kurās ir komentāri vai apraksti. Vai, ja vēlaties noņemt dublētās rindas, to var ātri izdarīt, izmantojot dialoglodziņu Dublikātu noņemšana .
Citos gadījumos var būt nepieciešams manipulēt ar vienu vai vairākām kolonnām, izmantojot formulu, lai importētās vērtības pārvērstu jaunās vērtībās. Piemēram, ja vēlaties noņemt beigu atstarpes, varat izveidot jaunu kolonnu, lai notīrītu datus, izmantojot formulu, aizpildot jauno kolonnu, pārvēršot šīs jaunās kolonnas formulas par vērtībām un pēc tam noņemot sākotnējo kolonnu.
Datu tīrīšanas pamatdarbības ir šādas:
Importējiet datus no ārēja datu avota.
Izveidojiet sākotnējo datu dublējumkopiju atsevišķā darbgrāmatā.
Pārliecinieties, vai dati ir tabulas formāta, kurā ir rindas un kolonnas ar līdzīgiem datiem katrā kolonnā, visām kolonnām un rindām redzamām un diapazonā nav tukšu rindu. Lai iegūtu vislabākos rezultātus, izmantojiet Excel tabulu.
Vispirms veiciet uzdevumus, kuriem nav nepieciešamas manipulācijas ar kolonnām, piemēram, pareizrakstības pārbaudi vai dialoglodziņa Atrašana un aizstāšana izmantošanu.
Pēc tam veiciet uzdevumus, kuru izpildei nepieciešama manipulācija ar kolonnām. Vispārīgās darbības manipulēšanai ar kolonnu ir šādas:
- Ievietojiet jaunu kolonnu (B) blakus sākotnējai kolonnai (A), kas ir jānotīra.
- Pievienojiet formulu, kas pārveidos datus jaunās kolonnas (B) augšdaļā.
- Aizpildiet formulu jaunajā kolonnā (B). Excel tabulā automātiski tiek izveidota aprēķinātā kolonna ar vērtībām, kuras vērtības ir aizpildītas uz leju.
- Atlasiet jauno kolonnu (B), kopējiet to un pēc tam ielīmējiet kā vērtības jaunajā kolonnā (B).
- Noņemiet sākotnējo kolonnu (A), kas jauno kolonnu pārvērš par B par A.
Lai periodiski notīrītu vienu datu avotu, apsveriet iespēju ierakstīt makro vai rakstīt kodu, lai automatizētu visu procesu. Pastāv arī vairākas ārējās pievienojumprogrammas, ko izstrādājuši trešo pušu piegādātāji, kas norādītas sadaļā Trešo pušu nodrošinātāji un kuras varat apsvērt izmantošanu, ja jums nav laika vai resursu, lai patstāvīgi automatizētu procesu.
| Papildinformācija | Apraksts |
|---|---|
| Darblapas šūnu automātiska aizpildīšana ar datiem | Parāda, kā izmantot aizpildījuma komandu. |
|
Tabulu izveide un formatēšana Tabulas izmēru maiņa, pievienojot vai noņemot rindas un kolonnas Aprēķinātu kolonnu izmantošana Excel tabulā |
Uzziniet, kā izveidot Excel tabulu un pievienot vai dzēst kolonnas vai aprēķinātās kolonnas. |
| Makro izveidošana | Parādīti vairāki veidi, kā automatizēt atkārtotus uzdevumus, izmantojot makro. |
Pareizrakstības pārbaude
Pareizrakstības pārbaudītāju var izmantot ne tikai, lai atrastu kļūdaini uzrakstītus vārdus, bet arī atrastu vērtības, kas netiek izmantotas konsekventi, piemēram, produktu vai uzņēmumu nosaukumus, pievienojot šīs vērtības pielāgotai vārdnīcai.
| Papildinformācija | Apraksts |
|---|---|
| Pareizrakstības un gramatikas pārbaudīšana | Parāda, kā labot kļūdaini uzrakstītus vārdus darblapā. |
| Īpašu vārdnīcu izmantošana vārdu pievienošanai pareizrakstības pārbaudītājam | Paskaidrots, kā izmantot pielāgotas vārdnīcas. |
Rindu dublikātu noņemšana
Rindu dublikāti ir bieži sastopama problēma, importējot datus. Pirms vērtību dublikātu noņemšanas ieteicams vispirms filtrēt unikālās vērtības, lai pārliecinātos, vai rezultāti atbilst vēlamajiem.
| Papildinformācija | Apraksts |
|---|---|
| Unikālo vērtību filtrēšana un vērtību dublikātu noņemšana | Parādītas divas cieši saistītas procedūras: kā filtrēt unikālās rindas un kā noņemt rindu dublikātus. |
Teksta atrašana un aizstāšana
Iespējams, vēlēsities noņemt bieži sastopamu sākuma virkni, piemēram, etiķeti, kam seko kols un atstarpe, vai sufiksu, piemēram, iekavu frāzi virknes beigās, kas ir novecojusi vai nevajadzīga. To var izdarīt, atrodot šāda teksta gadījumus un aizstājot to ar tekstu vai citu tekstu.
| Papildinformācija | Apraksts |
|---|---|
|
Pārbaude, vai šūnā ir teksts (nav reģistrjutīga) Pārbaude, vai šūnā ir teksts (reģistrjutīga) |
Uzziniet, kā lietot komandu Atrast un vairākas funkcijas, lai atrastu tekstu. |
| Rakstzīmju noņemšana no teksta | Parāda, kā izmantot komandu Aizstāt un vairākas funkcijas, lai noņemtu tekstu. |
| Teksta un skaitļu atrašana vai aizstāšana darblapā | Uzziniet, kā izmantot dialoglodziņus Atrašana un aizstāšana . |
|
FIND, FINDB SEARCH, SEARCHB REPLACE, REPLACEB SUBSTITUTE LEFT, LEFTB RIGHT, RIGHTB LEN, LENB MID, MIDB |
Šīs funkcijas var izmantot, lai veiktu dažādus virkņu manipulācijas uzdevumus, piemēram, atrastu un aizstātu apakšvirkni virknē, izvilktu virknes daļas vai noteiktu virknes garumu. |
Teksta burtu reģistra maiņa
Dažreiz teksts ir jaukts, it īpaši, ja runa ir par tekstu. Izmantojot vienu vai vairākas no trim funkcijām Case, tekstu var pārvērst par mazajiem burtiem, piemēram, e-pasta adresēm, lielajiem burtiem, piemēram, produktu kodiem, vai īpašburtiem, piemēram, nosaukumiem vai grāmatu nosaukumiem.
| Papildinformācija | Apraksts |
|---|---|
| Teksta burtu reģistra maiņa | Parāda, kā izmantot trīs Case funkcijas. |
| LOWER | Visus burtus teksta virknē konvertē par mazajiem burtiem. |
| PROPER | Lieto lielo burtu pirmajam burtam teksta virknē un visiem citiem tekstā esošiem burtiem, kuri seko kādai rakstzīmei, kas nav burts. Pārvērš visus pārējos burtus par mazajiem burtiem. |
| UPPER | Pārvērš tekstu par lielajiem burtiem. |
Atstarpju un nedrukājamu rakstzīmju izņemšana no teksta
Dažreiz teksta vērtībās ir sākuma, beigu vai vairākas iegultas atstarpes rakstzīmes (unikoda rakstzīmju kopas vērtības 32 un 160) vai nedrukājamas rakstzīmes (unikoda rakstzīmju kopas vērtības no 0 līdz 31, 127, 129, 141, 143, 144 un 157). Šīs rakstzīmes dažkārt var radīt neparedzētus rezultātus, kad kārtojat, filtrējat vai meklējat. Piemēram, ārējā datu avotā lietotāji var pieļaut drukas kļūdas, nejauši pievienojot papildu atstarpes rakstzīmes, vai importētajos teksta datos no ārējiem avotiem var būt nedrukājamas rakstzīmes, kas ir iegultas tekstā. Tā kā šīs rakstzīmes nav viegli pamanāmas, var būt grūti saprast neparedzētus rezultātus. Lai noņemtu šīs nevajadzīgās rakstzīmes, varat izmantot funkciju TRIM, CLEAN un SUBSTITUTE kombināciju.
| Papildinformācija | Apraksts |
|---|---|
| CODE | Atgriež teksta virknes pirmās rakstzīmes skaitlisko kodu. |
| CLEAN | No teksta noņem pirmās 32 nedrukājamās rakstzīmes 7 bitu ASCII kodā (vērtības no 0 līdz 31). |
| TRIM | Noņem 7 bitu ASCII atstarpes rakstzīmi (vērtība 32) no teksta. |
| SUBSTITUTE | Varat izmantot funkciju SUBSTITUTE, lai aizstātu lielākas vērtības unikoda rakstzīmes (vērtības 127, 129, 141, 143, 144, 157 un 160) ar 7 bitu ASCII rakstzīmēm, kurām paredzētas funkcijas TRIM un CLEAN. |
Skaitļu un numura zīmju labošana
Ir divas galvenās problēmas ar skaitļiem, kuru dēļ var būt nepieciešams notīrīt datus: numurs tika nejauši importēts kā teksts, un mīnuszīme ir jāmaina uz jūsu organizācijas standartu.
| Papildinformācija | Apraksts |
|---|---|
| Skaitļu, kas saglabāti kā teksts, pārvēršana par skaitļiem | Parāda, kā skaitļu formātā pārvērst skaitļus, kas ir formatēti un saglabāti šūnās kā teksts, kas var radīt aprēķinu problēmas vai radīt nepareizu kārtošanas secību. |
| DOLLAR | Konvertē skaitli teksta formātā un izmanto valūtas simbolu. |
| TEKSTS | Vērtību konvertē par tekstu noteiktā skaitļu formātā. |
| FIKSĒTS | Noapaļo skaitli līdz noteiktam ciparu skaitam aiz komata, formatē skaitli decimālformātā, izmantojot punktu un komatus, un atgriež rezultātu kā tekstu. |
| VĒRTĪBA | Pārvērš skaitli saturošu teksta virkni skaitlī. |
Datumu un laiku labošana
Pastāv ļoti daudz atšķirīgu datumu formātu un šos formātus var sajaukt ar numurēto daļu kodiem vai citām virknēm, kas satur slīpsvītras vai defises, tāpēc datumi un laiki bieži ir jākonvertē un jāpārformatē.
| Papildinformācija | Apraksts |
|---|---|
| Datumu sistēmas, formāta vai divciparu gada interpretācijas maiņa | Aprakstīts, kā datumu sistēma darbojas programmā Office Excel. |
| Laiku konvertēšana | Parādīts, kā konvertēt dažādas laika vienības. |
| Datumu, kas saglabāti kā teksts, konvertēšana par datumiem | Parāda, kā datumu formātā konvertēt datumus, kas ir formatēti un saglabāti šūnās kā teksts, kas var radīt aprēķinu problēmas vai radīt mulsinošas kārtošanas secības. |
| DATUMS | Atgriež secīgu sērijas numuru, kas apzīmē noteiktu datumu. Ja šūnas formāts pirms funkcijas ievadīšanas ir Vispārīgs, rezultāts tiek formatēts kā datums. |
| DATEVALUE | Pārvērš datumu, kas attēlots ar tekstu, par sērijas numuru. |
| TIME | Atgriež decimāldaļskaitli uz noteiktu laiku. Ja šūnas formāts pirms funkcijas ievadīšanas ir Vispārīgs, rezultāts tiek formatēts kā datums. |
| TIMEVALUE | Atgriež teksta rindā izteiktā laika decimāldaļskaitli. Atgrieztais decimāldaļskaitlis ir vērtība diapazonā no 0 (nulles) līdz 0,99999999, kas atbilst laikam no 0:00:00 (12:00:00 AM) līdz 23:59:59 (11:59:59 PM). |
Kolonnu sapludināšana un sadalīšana
Bieži sastopams uzdevums pēc datu importēšanas no ārēja datu avota ir vai nu sapludināt divas vai vairākas kolonnas vienā kolonnā, vai arī vienas kolonnas sadalīšana divās vai vairāk kolonnās. Piemēram, kolonnu, kurā ir vārds un uzvārds, varat sadalīt vienā vārdā un uzvārdā. Vai kolonnu, kurā ir adreses lauks, varat sadalīt atsevišķās ielās, pilsētās, reģionos un pasta indeksos. Var būt arī otrādi. Iespējams, vēlēsities sapludināt vārda un uzvārda kolonnu kolonnā Pilns vārds vai apvienot atsevišķas adrešu kolonnas vienā kolonnā. Papildu bieži sastopamās vērtības, kuras var būt nepieciešams sapludināt vienā kolonnā vai sadalīt vairākās kolonnās, ir produktu kodi, failu ceļi un interneta protokola (IP) adreses.
| Papildinformācija | Apraksts |
|---|---|
|
Vārda un uzvārda apvienošana Teksta un skaitļu apvienošana Teksta apvienošana ar datumu vai laiku Divu vai vairāku kolonnu apvienošana, izmantojot funkciju |
Parādiet tipiskus piemērus vērtību apvienošanai no divām vai vairākām kolonnām. |
| Teksta sadalīšana vairākās kolonnās, izmantojot vedni teksta konvertēšanai pa kolonnām | Parāda, kā izmantot šo vedni, lai sadalītu kolonnas, pamatojoties uz dažādiem bieži lietotiem norobežotājiem. |
| Teksta sadalīšanas vairākās kolonnās, izmantojot funkcijas | Parāda, kā izmantot funkcijas LEFT, MID, RIGHT, SEARCH un LEN, lai nosaukuma kolonnu sadalītu divās vai vairāk kolonnās. |
| Šūnu satura apvienošana vai sadalīšana | Parāda, kā izmantot funkciju CONCATENATE, & (zīmi un) operatoru un vedni teksta konvertēšanai par kolonnām. |
| Sapludināt šūnas vai sadalīt sapludinātās šūnas | Parāda, kā izmantot komandas Sapludināt šūnas, Sapludināt pār un Sapludināt un centrēt . |
| CONCATENATE | Divas vai vairāk teksta virknes tiek savienotas vienā teksta virknē. |
Kolonnu un rindu transformēšana un pārkārtošana
Lielākā daļa programmas Office Excel analīzes un formatēšanas līdzekļu pieņem, ka dati atrodas vienā plakanā divdimensiju tabulā. Dažreiz rindas var pārvērst par kolonnām un kolonnas par rindām. Citreiz dati pat nav strukturēti tabulas formātā, un ir nepieciešams veids, kā datus pārveidot no netabulas formāta uz tabulas formātu.
| Papildinformācija | Apraksts |
|---|---|
| TRANSPOSE | Atgriež vertikālu šūnu diapazonu kā horizontālu diapazonu, kā arī pretēji. |
Tabulas datu saskaņošana, savienojot vai saskaņojot tabulas datus
Dažkārt datu bāzu administratori izmanto programmu Office Excel, lai atrastu un labotu atbilstības kļūdas, kad divas vai vairāk tabulas ir savienotas. Tas var ietvert divu tabulu saskaņošanu no dažādām darblapām, piemēram, lai skatītu visus ierakstus abās tabulās vai salīdzinātu tabulas un atrastu rindas, kas neatbilst.
| Papildinformācija | Apraksts |
|---|---|
| Vērtību uzmeklēšana datu sarakstā | Parādīti izplatītākie veidi, kā uzmeklēt datus, izmantojot uzmeklēšanas funkcijas. |
| LOOKUP | Atgriež vērtību no vienas rindas vai vienas kolonnas diapazona vai no masīva. Funkcijai LOOKUP ir divas sintakses formas: vektora forma un masīva forma. |
| HLOOKUP | Meklē vērtību tabulas augšējā rindā vai vērtību masīvā un pēc tam no tabulā vai masīvā norādītās rindas atgriež vērtību tajā pašā kolonnā. |
| VLOOKUP | Meklē vērtību tabulas masīva pirmajā kolonnā un atgriež vērtību tajā pašā rindā no citas tabulas masīva kolonnas. |
| INDEKSS | No tabulas vai diapazona atgriež vērtību vai atsauci uz vērtību. Ir divas funkcijas INDEX() formas: masīva forma un atsauces forma. |
| MATCH | Atgriež vienuma relatīvo atrašanās vietu masīvā, kas atbilst norādītajai vērtībai noteiktā secībā. Ja ir jāatrod nevis vienums, bet vienuma atrašanās vieta diapazonā, izmantojiet funkciju MATCH kādas LOOKUP funkcijas vietā. |
| OFFSET | Atgriež atsauci uz diapazonu, kas ir noteikts šūnas vai šūnu diapazona rindu un kolonnu skaits. Atgrieztā atsauce var būt viena šūna vai šūnu diapazons. Var norādīt atgriežamo rindu un kolonnu skaitu. |
Trešo pušu pakalpojumu sniedzēji
Tālāk ir sniegts daļējs saraksts ar trešo pušu pakalpojumu sniedzējiem, kuru produktiem ir produkti, kas tiek izmantoti datu tīrīšanai dažādos veidos.
Piezīme
Microsoft nenodrošina atbalstu trešo pušu produktiem.