صيغ تنظيف البيانات الشائعة في Excel

صيغ اكسل

لسنوات ، استخدمت المنشور كمورد ليس فقط لوصف كيفية القيام بالأشياء ، ولكن أيضًا للاحتفاظ بسجل لنفسي للبحث عنه لاحقًا! اليوم ، كان لدينا عميل قام بتسليمنا ملف بيانات عميل كان بمثابة كارثة. عمليا كل حقل كان منسقا بشكل خاطئ و ؛ نتيجة لذلك ، لم نتمكن من استيراد البيانات. على الرغم من وجود بعض الوظائف الإضافية الرائعة لبرنامج Excel لإجراء التنظيف باستخدام Visual Basic ، فإننا نقوم بتشغيل Office for 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))

الآن يمكنك نسخ العمود الناتج واستخدامه تحرير> لصق القيم للكتابة فوق البيانات بالنتيجة المنسقة بشكل صحيح.

تقييم الحقول المتعددة باستخدام OR

غالبًا ما نقوم بمسح السجلات غير المكتملة من الاستيراد. لا يدرك المستخدمون أنه ليس عليك دائمًا كتابة صيغ هرمية معقدة وأنه يمكنك كتابة عبارة OR بدلاً من ذلك. في هذا المثال أدناه ، أريد التحقق من 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 ثم قص المحتوى لاستخدامه في حقل 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 لتقليل الرسائل الضارة. تعرف كيف تتم معالجة بيانات تعليقك.