Агульныя формулы ачысткі дадзеных у Excel

Excel формулы

На працягу многіх гадоў я выкарыстоўваю публікацыю як рэсурс, каб не проста апісаць, як гэта рабіць, але і весці ўлік для сябе, каб потым шукаць! Сёння ў нас быў кліент, які перадаў нам файл дадзеных кліента, які быў катастрофай. Практычна кожнае поле было няправільна адфарматавана і; у выніку мы не змаглі імпартаваць дадзеныя. Хоць для ачысткі з дапамогай Visual Basic ёсць некалькі выдатных дапаўненняў для Excel, мы запускаем Office для Mac, які не падтрымлівае макрасы. Замест гэтага мы шукаем прамыя формулы для дапамогі. Я думаў, што падзялюся некаторымі з іх тут, каб іншыя маглі імі карыстацца.

Выдаліць нелічэбныя сімвалы

Сістэмы часта патрабуюць увядзення нумароў тэлефонаў у пэўную 11-значную формулу з кодам краіны без знакаў прыпынку. Аднак людзі часта ўводзяць гэтыя дадзеныя з працяжнікамі і кропкамі. Вось выдатная формула для выдаленне ўсіх не лічбавых знакаў у Excel. Формула разглядае дадзеныя ў ячэйцы 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))

Цяпер вы можаце скапіяваць атрыманы слупок і выкарыстоўваць Рэдагаваць> Уставіць значэнні для запісу дадзеных з правільна адфарматаваным вынікам.

Ацэніце некалькі палёў АБО

Мы часта выдаляем няпоўныя запісы з імпарту. Карыстальнікі не разумеюць, што вам не заўсёды трэба пісаць складаныя іерархічныя формулы і замест гэтага вы можаце напісаць заяву АБО. У гэтым прыкладзе ніжэй я хачу праверыць A2, B2, C2, D2 або E2 на наяўнасць адсутных дадзеных. Калі якія-небудзь дадзеныя адсутнічаюць, я збіраюся вярнуць 0, у адваротным выпадку 1. Гэта дазволіць мне сартаваць дадзеныя і выдаляць няпоўныя запісы.

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

Абрэзаць і аб'яднаць палі

Калі вашы дадзеныя маюць імя Імя і Прозвішча, але імпарт мае поле поўнага імя, вы можаце акуратна аб'яднаць палі разам, выкарыстоўваючы ўбудаваную функцыю "Злучыць функцыі Excel", але абавязкова выкарыстоўвайце TRIM для выдалення пустых прабелаў да або пасля тэкст. Мы ахінаем усё поле TRIM, калі ў адным з палёў няма дадзеных:

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

Праверце сапраўдны адрас электроннай пошты

Даволі простая формула, якая шукае як @, так і. на адрас электроннай пошты:

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

Вытрымка імёнаў і прозвішчаў

Часам праблема ў адваротным. Вашы дадзеныя маюць поўнае імя, але вам трэба разабраць імя і прозвішча. Гэтыя формулы шукаюць прастору паміж імем і прозвішчам і захопліваюць тэкст, дзе гэта неабходна. ІТ таксама апрацоўвае, калі прозвішча адсутнічае альбо ў A2 ёсць пусты запіс.

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

І прозвішча:

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

Абмяжуйце колькасць сімвалаў і дадайце ...

Вы калі-небудзь хацелі ачысціць свае метаапісанні? Калі вы хочаце выцягнуць змесціва ў Excel, а потым абрэзаць змест для выкарыстання ў полі Мета-апісанне (ад 150 да 160 сімвалаў), вы можаце зрабіць гэта, выкарыстоўваючы гэтую формулу з Мая пляма. Ён чыста разбівае апісанне на прастору, а потым дадае…:

=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)

Вядома, яны не прызначаны для таго, каб быць усёабдымнымі ... проста некалькі хуткіх формул, якія дапамогуць вам пачаць рывок! Якімі яшчэ формуламі вы карыстаецеся? Дадайце іх у каментарыі, і я дам вам заслугу пры абнаўленні гэтага артыкула.

Што вы думаеце?

Гэты сайт выкарыстоўвае Akismet для барацьбы са спамам. Даведайцеся, як дадзеныя апрацоўваюцца каментар.