Excel -formules vir algemene opruiming van data
Ek gebruik die publikasie al jare lank as 'n bron om nie net te beskryf hoe om dinge te doen nie, maar om ook vir myself 'n rekord te hou om later op te soek! Vandag het ons 'n kliënt gehad wat 'n kliëntedata-lêer aan ons gegee het wat 'n ramp was. Feitlik elke veld is verkeerd geformateer en; gevolglik kon ons nie die data invoer nie. Alhoewel daar 'n paar uitstekende byvoegings vir Excel is om die skoonmaak met Visual Basic uit te voer, gebruik ons Office for Mac wat nie makro's ondersteun nie. In plaas daarvan soek ons reguit formules om te help. Ek het gedink ek wil sommige hiervan deel, sodat ander dit kan gebruik.
Verwyder nie-numeriese karakters
Stelsels vereis dat telefoonnommers gereeld in 'n spesifieke 11-syferformule met die landkode en geen leestekens ingevoeg moet word nie. Mense voer hierdie data egter dikwels met streepies en tydperke in. Hier is 'n uitstekende formule vir verwyder alle nie-numeriese karakters in Excel. Die formule hersien die data in sel 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))Nou kan u die resulterende kolom kopieer en gebruik Wysig> Plak waardes om die data oor te skryf met die behoorlik geformateerde resultaat.
Evalueer verskeie velde met 'n OF
Ons maak dikwels onvolledige rekords skoon van 'n invoer. Gebruikers besef nie dat u nie altyd ingewikkelde hiërargiese formules hoef te skryf nie en dat u eerder 'n OF-stelling kan skryf. In hierdie onderstaande voorbeeld wil ek A2, B2, C2, D2 of E2 nagaan vir ontbrekende data. As daar geen data ontbreek nie, gaan ek 'n 0 teruggee, anders 'n 1. Dit sal my toelaat om die data te sorteer en die onvolledige rekords te verwyder.
=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)Knip en saambind velde
As u data 'n voor- en vannaam-velde het, maar u invoer 'n volledige naamveld het, kan u die velde netjies saamvat met behulp van die ingeboude Excel-funksie-aaneenskakeling, maar maak seker dat u TRIM gebruik om leë spasies voor of na die teks. Ons draai die hele veld met TRIM in die geval dat een van die velde nie data het nie:
=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))Kyk vir geldige e-posadres
'N Redelik eenvoudige formule wat beide die @ en. in 'n e-pos adres:
=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))Pak voor- en laaste name uit
Soms is die probleem die teenoorgestelde. U gegewens het 'n volledige naamveld, maar u moet die voor- en vanname ontleed. Hierdie formules soek die spasie tussen die voor- en vannaam en gryp teks waar nodig. Dit word ook hanteer as daar geen van is nie, of as daar 'n leë inskrywing in A2 is.
=IFERROR(IF(SEARCH(" ",A2,1),LEFT(A2, SEARCH(" ",A2,1)),A2),IF(LEN(A2)>0,A2,""))En die van:
=IFERROR(IF(SEARCH(" ",A2,1),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),A2),"")Beperk die aantal karakters en voeg by…
Wou u ooit u metabeskrywings opruim? As u die inhoud in Excel wil oplaai en dan die inhoud wil snoei vir gebruik in 'n Meta-beskrywingsveld (150 tot 160 karakters), kan u dit doen met behulp van hierdie formule vanaf My kol. Dit breek die beskrywing op 'n spasie skoon en voeg dan die ... by:
=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)Dit is natuurlik nie bedoel om omvattend te wees nie ... net 'n paar vinnige formules om u te help! Watter ander formules gebruik u? Voeg dit by die kommentaar en ek gee u krediet as ek hierdie artikel opdateer.
