Vispārīgas datu tīrīšanas formulas programmā Excel

Excel formulas

Gadiem ilgi esmu izmantojis publikāciju kā resursu, lai ne tikai aprakstītu, kā darīt lietas, bet arī lai saglabātu uzskaiti, lai vēlāk varētu meklēt! Šodien mums bija klients, kurš mums nodeva klienta datu failu, kas bija katastrofa. Praktiski katrs lauks bija nepareizi formatēts un; kā rezultātā mēs nevarējām importēt datus. Lai gan Excel ir daži lieliski papildinājumi tīrīšanas veikšanai, izmantojot Visual Basic, mēs darbinām Office for Mac, kas neatbalstīs makro. Tā vietā mēs meklējam taisnas formulas, lai palīdzētu. Es domāju, ka padalīšos ar dažiem šeit esošajiem, lai citi tos varētu izmantot.

Noņemt bez ciparu rakstzīmes

Sistēmas bieži pieprasa tālruņu numurus ievietot noteiktā vienpadsmitciparu formulā ar valsts kodu un bez pieturzīmēm. Tomēr ļaudis bieži ievada šos datus ar domuzīmēm un punktiem. Šeit ir lieliska formula noņemot visas bez cipariem rakstzīmes programmā Excel. Formula pārskata datus šūnā A2:

=IF(A2="","",SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

Tagad jūs varat kopēt iegūto kolonnu un izmantot Rediģēt> Ielīmēt vērtības pārrakstīt datus ar pareizi noformētu rezultātu.

Novērtējiet vairākus laukus ar OR

Nepilnīgus ierakstus mēs bieži iztīra no importa. Lietotāji neapzinās, ka jums ne vienmēr ir jāraksta sarežģītas hierarhiskas formulas un ka tā vietā varat uzrakstīt priekšrakstu VAI. Šajā zemāk redzamajā piemērā es vēlos pārbaudīt, vai A2, B2, C2, D2 vai E2 trūkst datu. Ja trūkst datu, es atgriezīšu 0, pretējā gadījumā 1. Tas ļaus man kārtot datus un izdzēst nepilnīgos ierakstus.

=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)

Apgrieziet un savienojiet laukus

Ja jūsu datiem ir lauki Vārds un Uzvārds, bet importēšanai ir pilns vārda lauks, jūs varat kārtīgi apvienot laukus, izmantojot iebūvēto Excel funkciju Saķēdēt, taču noteikti izmantojiet TRIM, lai noņemtu tukšās vietas pirms vai pēc tekstu. Mēs aptinam visu lauku ar TRIM, ja vienā no laukiem nav datu:

=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))

Pārbaudiet, vai ir derīga e-pasta adrese

Diezgan vienkārša formula, kas meklē gan @, gan. e-pasta adresē:

=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))

Izraksts vārdi un uzvārdi

Dažreiz problēma ir pretēja. Jūsu datiem ir pilns vārda lauks, taču jums ir jāizpēta vārdi un uzvārdi. Šīs formulas meklē atstarpi starp vārdu un uzvārdu un vajadzības gadījumā satver tekstu. IT rīkojas arī tad, ja uzvārda nav vai A2 ir tukšs ieraksts.

=IFERROR(IF(SEARCH(" ",A2,1),LEFT(A2, SEARCH(" ",A2,1)),A2),IF(LEN(A2)>0,A2,""))

Un uzvārds:

=IFERROR(IF(SEARCH(" ",A2,1),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),A2),"")

Ierobežojiet rakstzīmju skaitu un pievienojiet…

Vai jūs kādreiz gribējāt iztīrīt savus meta aprakstus? Ja vēlaties ievietot saturu programmā Excel un pēc tam sagriezt saturu lietošanai laukā Meta Description (150 līdz 160 rakstzīmes), to varat izdarīt, izmantojot šo formulu no Mana vieta. Tas atstaro vietu aprakstā un pēc tam pievieno…:

=IF(LEN(A1)>155,LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ",""))))) & IF(LEN(A1)>FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ","")))),"…",""),A1)

Protams, tie nav domāti kā visaptveroši ... tikai dažas ātras formulas, kas palīdzēs jums sākt darbu! Kādas citas formulas jūs izmantojat? Pievienojiet tos komentāros, un es atjaunināšu šo rakstu.

Ko jūs domājat?

Šī vietne izmanto Akismet, lai samazinātu surogātpastu. Uzziniet, kā tiek apstrādāts jūsu komentārs.