Уобичајене формуле за чишћење података у програму Екцел

екцел формуле

Годинама сам користио ову публикацију као ресурс да не само опишем како се то ради, већ и да сачувам евиденцију да бих је касније потражио! Данас смо имали клијента који нам је предао датотеку података о купцима која је била катастрофа. Практично свако поље је било погрешно форматирано и; као резултат, нисмо успели да увозимо податке. Иако постоје неки сјајни додаци за Екцел за чишћење помоћу Висуал Басиц-а, ми покрећемо Оффице за Мац који неће подржавати макронаредбе. Уместо тога, тражимо равне формуле за помоћ. Мислио сам да неке од њих поделим овде само да би их други могли користити.

Уклоните не-нумеричке знакове

Системи често захтевају да се бројеви телефона убаце у одређену, 11-цифрену формулу са позивним бројем државе и без интерпункције. Међутим, људи често уносе ове податке цртицама и тачкама. Ево одличне формуле за уклањање свих ненумеричких знакова у програму Екцел. Формула даје преглед података у ћелији А2:

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

Сада можете копирати резултујућу колону и користити Уреди> Налепи вредности да препише податке са правилно форматираним резултатом.

Процените више поља са ИЛИ

Често уклањамо непотпуне записе из увоза. Корисници не схватају да не морате увек да пишете сложене хијерархијске формуле и да уместо тога можете да напишете израз ОР. У овом доњем примеру желим да проверим А2, Б2, Ц2, Д2 или Е2 да ли недостају подаци. Ако недостају неки подаци, вратићу 0, у супротном 1. То ће ми омогућити сортирање података и брисање непотпуних записа.

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

Трим и Цонцатенате Фиелдс

Ако ваши подаци садрже поља Име и Презиме, али ваш увоз има поље пуног имена, поља можете уредно повезати помоћу уграђене функције Екцел Цонцатенате, али обавезно користите ТРИМ за уклањање празних простора пре или после текст. Обмотавамо цело поље ТРИМ-ом у случају да једно од поља нема податке:

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

Проверите да ли је важећа адреса е-поште

Прилично једноставна формула која тражи и @ и. на имејл адресу:

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

Издвој име и презиме

Проблем је понекад супротан. Ваши подаци имају поље с пуним именом, али морате рашчланити имена и презимена. Ове формуле траже размак између имена и презимена и увлаче текст тамо где је то потребно. ИТ такође поступа ако нема презимена или ако је у А2 празан унос.

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

Ограничите број знакова и додајте…

Да ли сте икада желели да очистите своје мета описе? Ако сте желели да повучете садржај у Екцел, а затим исечете садржај за употребу у пољу Мета опис (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)

Наравно, нису замишљени као свеобухватни ... само неке брзе формуле које ће вам помоћи да започнете! Које друге формуле користите? Додајте их у коментаре и одаћу вам признање док ажурирам овај чланак.

Шта ви мислите?

Ова страница користи Акисмет како би смањила нежељену пошту. Сазнајте како се ваш коментар обрађује.