فرمول های معمول پاک سازی داده ها در 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)

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

اگر داده های شما دارای فیلدهای نام و نام خانوادگی است ، اما واردات شما دارای یک نام کامل است ، می توانید با استفاده از ساخته شده در عملکرد عملکرد اکسل ساخته شده ، زمینه ها را با هم وصل کنید ، اما مطمئن باشید که برای حذف هرگونه فضای خالی قبل یا بعد از TRIM ، از TRIM استفاده کنید متن در صورتی که یکی از فیلدها داده ای نداشته باشد ، کل قسمت را با 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 برای کاهش هرزنامه استفاده می کند. بدانید که چگونه نظر شما پردازش می شود.