فرمول های Excel برای پاکسازی مشترک داده ها

فرمول های پاکسازی داده های اکسل

سالهاست که من از انتشارات به عنوان منبعی استفاده می کردم که نه تنها توصیف چگونگی انجام کارها ، بلکه همچنین ثبت رکورد برای خودم است که بعداً به جستجوی آنها بپردازم! امروز ، ما یک مشتری داشتیم که یک پرونده داده مشتری به ما تحویل داد که یک فاجعه بود. در واقع هر زمینه ای نادرست شکل گرفته و در نتیجه ، ما قادر به وارد کردن داده ها نبودیم. در حالی که چند افزونه عالی برای اکسل وجود دارد که می تواند با استفاده از Visual Basic پاکسازی کند ، ما Office for Mac را اجرا می کنیم که از ماکرو پشتیبانی نمی کند. در عوض ، ما به دنبال فرمول های مستقیم برای کمک هستیم. فکر کردم بعضی از آنها را در اینجا به اشتراک می گذارم تا دیگران بتوانند از آنها استفاده کنند.

نویسه های غیر عددی را حذف کنید

سیستم ها معمولاً شماره تلفن ها را باید در فرمول 11 رقمی مشخص با کد کشور و بدون علائم نگارشی درج کنند. با این حال ، افراد اغلب این داده ها را با خط تیره و دوره به جای آن وارد می کنند. در اینجا یک فرمول عالی برای حذف تمام نویسه های غیر عددی در اکسل فرمول داده های موجود در سلول 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))

اکنون می توانید ستون حاصل را کپی کرده و استفاده کنید ویرایش> Paste Values برای نوشتن روی داده ها با نتیجه صحیح قالب بندی شده.

چندین فیلد را با یک OR ارزیابی کنید

ما اغلب سوابق ناقص را از واردات پاک می کنیم. کاربران متوجه نمی شوند که شما همیشه مجبور نیستید فرمول های سلسله مراتبی پیچیده ای بنویسید و می توانید به جای آن دستور OR بنویسید. در این مثال زیر ، من می خواهم A2 ، B2 ، C2 ، D2 یا E2 را برای اطلاعات از دست رفته بررسی کنم. اگر داده ای وجود نداشته باشد ، من می خواهم 0 و در غیر این صورت 1 را برمی گردانم. این به من امکان می دهد داده ها را مرتب کرده و سوابق ناقص را حذف کنم.

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

زمینه های اصلاح و اتصال

اگر داده های شما دارای فیلدهای نام و نام خانوادگی است ، اما واردات شما دارای یک نام کامل است ، می توانید با استفاده از ساخته شده در Excel Function Concatenate ، زمینه ها را با هم وصل کنید ، اما مطمئن باشید که برای حذف هرگونه فضای خالی قبل یا بعد از متن در صورتی که یکی از فیلدها داده ای نداشته باشد ، کل قسمت را با TRIM می پیچیم:

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

آدرس ایمیل معتبر را بررسی کنید

یک فرمول کاملاً ساده که به دنبال @ و. در یک آدرس ایمیل:

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

نام و نام خانوادگی را استخراج کنید

گاهی اوقات ، مسئله برعکس است. داده های شما دارای یک قسمت نام کامل است اما شما باید نام و نام خانوادگی را تجزیه کنید. این فرمول ها به دنبال فضای بین نام و نام خانوادگی هستند و در صورت لزوم متن را می گیرند. اگر نام خانوادگی وجود نداشته باشد یا ورودی خالی در A2 وجود داشته باشد ، IT نیز رسیدگی می کند.

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

تعداد نویسه ها را محدود کنید و Add اضافه کنید

آیا هرگز می خواستید توضیحات متای خود را پاک کنید؟ اگر می خواهید محتوا را به اکسل بکشید و سپس محتوا را برای استفاده در قسمت Meta Description اصلاح کنید (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 برای کاهش هرزنامه استفاده می کند. بدانید که چگونه نظر شما پردازش می شود.