أفضل 3 صيغ Excel لتنظيف بيانات المبيعات باحتراف

Habib Benabdeslam
Habib Benabdeslam
قراءة لمدة 9 دقيقة
صيغ Excel لتنظيف بيانات المبيعات
أفضل 3 صيغ Excel لتنظيف بيانات المبيعات باحتراف
جدول المحتويات
  1. كيفية استخراج النطاقات من عناوين البريد الإلكتروني
    1. استخراج نطاق البريد الإلكتروني في إصدارات Excel القديمة
    2. راجع استثناءات البريد الإلكتروني قبل نسخ الصيغة
    3. تنظيف أرقام الهواتف وتنسيقها
    4. تنظيف أسماء الشركات
  2. FAQ

استخدم صيغ Excel لتنظيف بيانات المبيعات عندما تحتاج إلى استخراج نطاقات البريد الإلكتروني، وتوحيد نصوص أرقام الهواتف، ومراجعة أسماء الشركات قبل استيرادها إلى CRM. احتفظ بالأعمدة الأصلية، واكتب كل نتيجة في عمود جديد حتى تتمكن من فحص التغييرات.

سأبدأ ببعض الصفوف التي قد تسبب مشكلات: رقم هاتف يبدأ بصفر، وعنوان بريد إلكتروني يفتقد إلى الفاصل، واسم شركة يتضمن كلمة تشبه لاحقة قانونية. فقد تتسبب صيغة تعمل جيدًا في أول صف منظم في إفساد هذه الحالات عند نسخها إلى بقية الصفوف.

يتناول هذا الدليل ثلاث مهام:

📌 ملخص لمن لا يملك وقتًا

جهّز أعمدة المبيعات لاستيرادها إلى CRM بعد التحقق منها، باستخدام ثلاث عمليات مركزة في Excel. أبقِ القيم المصدر بجانب النتائج، ثم راجع الحالات الاستثنائية قبل استبدال أي شيء.

  • المدخلات: عناوين البريد الإلكتروني، ونصوص أرقام الهواتف، وأسماء الشركات.
  • المخرجات: نطاقات مستخرجة، ونصوص هواتف أزيلت منها علامات الترقيم المحددة، وأسماء شركات نُظفت وفق قاعدة صريحة للواحق.

كيفية استخراج النطاقات من عناوين البريد الإلكتروني

توجد في Excel طريقتان لاستخراج النطاقات من قائمة عناوين البريد الإلكتروني، وإليك شرحهما:

الطريقة الأولى: دالة TEXTAFTER، وهي متاحة في Microsoft 365 وExcel للويب وExcel 2024. راجع وثائق Microsoft الحالية للدالة لمعرفة مدى توفرها في إصدارك.

الطريقة الثانية: الجمع بين دوال RIGHT وLEN وFIND — وهي مناسبة لكل من لا يستطيع استخدام إصدار “TEXTAFTER”.

لاستخراج اسم النطاق من عنوان بريد إلكتروني، اتبع الخطوات التالية:

  1. أدخل الصيغة =TEXTAFTER في حقل الصيغة
  2. حدد الخلية التي تحتوي على أول عنوان بريد إلكتروني لديك (مثل B3)
  3. أضف @ بوصفها النص المطلوب البحث عنه

ستبدو صيغة Excel كما يلي:

=TEXTAFTER(B3,"@")
النطاق المستخرج من عنوان بريد إلكتروني
النطاق المستخرج من عنوان بريد إلكتروني

ولتطبيق ذلك على جميع عناوين البريد الإلكتروني، اسحب الزاوية السفلية اليمنى للخلية التي تحتوي على الصيغة الأصلية.

النطاقات المستخرجة من قائمة عناوين البريد الإلكتروني
النطاقات المستخرجة من قائمة عناوين البريد الإلكتروني

استخراج نطاق البريد الإلكتروني في إصدارات Excel القديمة

سأوضح الآن كيفية استخراج اسم النطاق من عناوين البريد الإلكتروني باستخدام إصدار قديم من Excel لا يدعم دالة “TEXTAFTER”.

الصيغة التي سنستخدمها هي =RIGHT(B3,LEN(B3)-FIND("@",B3))

لاستخدام هذه الصيغة، الصقها في حقل الصيغة وطبّقها على الخلية التي تحتوي على أول عنوان بريد إلكتروني لديك.

أول نطاق مستخرج باستخدام الصيغة =RIGHT(B3,LEN(B3)-FIND("@",B3))
أول نطاق مستخرج باستخدام الصيغة =RIGHT(B3,LEN(B3)-FIND("@",B3))

إليك طريقة عمل الصيغة: تستخدم دالة RIGHT لجلب الأحرف من نهاية عنوان البريد الإلكتروني، بينما تعمل FIND وLEN معًا لتحديد عدد الأحرف المطلوب استخراجه بدقة.

لنستخدم john.doe123@abc.com مثالًا — يتكون هذا العنوان من 19 حرفًا إجمالًا:

تعطينا LEN(B3) عددًا قدره 19 حرفًا.

تبحث FIND عن الرمز @ في john.doe123@abc.com وتخبرنا بأنه الحرف رقم 12:

تعطينا FIND("@",B3) الرقم 12.

نطرح 12 من 19، ثم تنتقل النتيجة 7 مباشرة إلى دالة RIGHT. وهكذا نطلب من Excel جلب آخر 7 أحرف، لنحصل في النهاية على abc.com.

لتطبيق هذه الصيغة على جميع الخلايا، اسحب الزاوية السفلية اليمنى للخلية التي تحتوي على الصيغة الأصلية إلى أسفل.

تطبيق الصيغة =RIGHT(B3,LEN(B3)-FIND("@",B3)) لاستخراج جميع أسماء النطاقات من قائمة البريد الإلكتروني.
تطبيق الصيغة =RIGHT(B3,LEN(B3)-FIND("@",B3)) لاستخراج جميع أسماء النطاقات من قائمة البريد الإلكتروني.

إذا كنت تفضل العمل باستخدام CSV، فيمكنك استخراج النطاقات في Datablist ومراجعة المخرجات بجانب العمود المصدر.

راجع استثناءات البريد الإلكتروني قبل نسخ الصيغة

تعرض TEXTAFTER(B3,"@") خطأً عند غياب الفاصل. ومن أمثلة صيغ المراجعة: =IFERROR(LOWER(TRIM(TEXTAFTER(B3,"@"))),"Review email"). تستخرج هذه الصيغة النص فحسب؛ ولا تتحقق من وجود العنوان أو إمكانية تسليم الرسائل إليه. ارفض الصفوف التي تحتوي على عدة عناوين أو عدة رموز @ بدلًا من اعتبار النص المستخرج بعد الرمز نطاق شركة موثوقًا.

على سبيل المثال، يعطينا alex@example.com النطاق example.com، بينما يحتاج alex.example.com إلى المراجعة. احتفظ بعنوان البريد الإلكتروني كاملًا بجانب النطاق كي لا يُدمج شخصان يعملان في الشركة نفسها عن طريق الخطأ باعتبارهما جهة اتصال واحدة.

تنظيف أرقام الهواتف وتنسيقها

سنقوم الآن بتنظيف أرقام الهواتف وتنسيقها في خطوتين.

تتمثل الخطوة الأولى في تنظيف أرقام الهواتف باستخدام الصيغة التالية:

=TEXTJOIN("",TRUE,TEXTSPLIT(B3,{"(",")","-"," ","."},,TRUE))

لا تزيل هذه الصيغة سوى علامات الترقيم المذكورة، وتعيد النتيجة في صورة نص. لا تضف +0 ولا تحوّل النتيجة إلى رقم بأي طريقة أخرى: فهذا يحذف الأصفار في البداية وعلامة + الدولية. استورد عمود الهاتف الأصلي كنص أيضًا؛ إذ لا تستطيع الصيغة استعادة الأصفار التي حذفها Excel بالفعل. تشرح Microsoft هذا السلوك في دليل الأصفار البادئة.

تتطلب TEXTSPLIT إصدارًا مدعومًا من Excel. يستخدم هذا المثال أسماء الدوال بالإنجليزية والفواصل للفصل بين الوسيطات؛ وقد تستخدم النسخة المترجمة أسماء أو فواصل مختلفة. كما أن الصيغة لا تتحقق من رموز الدول أو الأرقام الداخلية أو إمكانية الاتصال بالأرقام.

إليك شكل النتيجة بعد تطبيق هذه الصيغة.

رقم هاتف بعد التنظيف
رقم هاتف بعد التنظيف

طبّق الصيغة على جميع أرقام الهواتف بسحب الزاوية السفلية اليمنى للخلية الأصلية إلى أسفل، كما هو موضح أدناه.

تطبيق صيغة التنظيف على جميع أرقام الهواتف
تطبيق صيغة التنظيف على جميع أرقام الهواتف

على سبيل المثال، يتحول النص 020 7946 0958 إلى 02079460958، ويتحول +44 (20) 7946-0958 إلى +442079460958. احتفظ بالأرقام الداخلية بصورة منفصلة، وعلّم الصفوف التي تحتوي على حروف أو رموز غير متوقعة لمراجعتها. فرقم الهاتف معرّف وليس كمية تُستخدم في العمليات الحسابية.

توضح لقطات الشاشة التالية تنسيق عرض أمريكي اختياريًا. لا تستخدمه إلا مع أرقام أمريكية مكوّنة من عشرة أرقام بعد مراجعتها، وفي عمود عرض منفصل. احتفظ بالنص المنظف لعمليات التصدير الدولية والاستيراد إلى CRM؛ فخطوة العرض هذه ليست توحيدًا دوليًا للأرقام.

أنشئ عمودًا منفصلًا باسم أرقام الهواتف المنسقة.

العمود الجديد لإدراج أرقام الهواتف المنسقة
العمود الجديد لإدراج أرقام الهواتف المنسقة

حدد الخلية الموجودة أسفل العنوان، ثم استخدم اختصار Excel‏ "Command" + "1" لفتح جميع خيارات التنسيق.

حدد "Special" بوصفه خيار التنسيق.

خيارات التنسيق في Excel
خيارات التنسيق في Excel

حدد "Phone Numbers" ثم اختر دولة أرقام الهواتف.

اختيار تنسيق الأرقام كأرقام هاتف أمريكية
اختيار تنسيق الأرقام كأرقام هاتف أمريكية

💡 ملاحظة حول تنسيق الأرقام

تغير تنسيقات الأرقام في Excel طريقة ظهور الخلايا الرقمية، لكنها لا تضيف رمز دولة مفقودًا ولا تتحقق من صحة رقم الهاتف. بالنسبة إلى القوائم الدولية، احتفظ بالقيم النصية وراجع معلومات الدولة قبل اختيار آلية مخصصة لتنظيف الهواتف.

يمكنك الآن إغلاق اللوحة، ثم التحقق من تطبيق Excel للتنسيق.

ولفعل ذلك، تحقق ببساطة من ظهور "Special" في حقل التنسيق الخاص بالعمود. وقد أبرزت ذلك في الصورة أدناه.

عمود Excel منسق لعرض أرقام الهواتف الأمريكية
عمود Excel منسق لعرض أرقام الهواتف الأمريكية

الخطوة الأخيرة هي إدخال صيغة في هذا العمود تنقل أرقام الهواتف التي نظفناها للتو إلى العمود الجديد المُعدّ لعرض الأرقام الأمريكية.

في مثال العرض المخصص للأرقام الأمريكية التي تمت مراجعتها، توفر =VALUE(D3) قيمة رقمية لعمود العرض. أبقِ D3 دون تغيير بوصفها نصًا. أما المرجع مثل =D3 وحده فيظل نصًا، ولن يكتسب تنسيق عرض أرقام الهواتف الرقمية.

صيغة بسيطة لإدراج أرقام الهواتف في عمود جديد
صيغة بسيطة لإدراج أرقام الهواتف في عمود جديد

بعد كتابة الصيغة، اضغط على "ENTER" لتطبيقها.

إذا نفذت كل شيء بطريقة صحيحة، فستبدو النتيجة كما يلي:

رقم هاتف أمريكي منسق في Excel
رقم هاتف أمريكي منسق في Excel

اسحب الآن الزاوية السفلية اليمنى إلى أسفل لتطبيق التنسيق على جميع أرقام الهواتف في Excel، كما هو موضح أدناه:

جميع أرقام الهواتف بالتنسيق الأمريكي
جميع أرقام الهواتف بالتنسيق الأمريكي

تنظيف أسماء الشركات

قد يكون تنظيف أسماء الشركات من أهم الخطوات التي ينبغي تنفيذها قبل بدء Outbound للعملاء المحتملين، ولهذا حرصت على تضمينه في هذا الدليل.

استخدم قاعدة للواحق يمكنك تفسيرها بوضوح. تستبدل SUBSTITUTE النص المطابق أينما ظهر. فعلى سبيل المثال، تعرض =SUBSTITUTE("Acme Consulting"," Co","") النتيجة Acmensulting، ما يعني حذف جزء من الاسم الحقيقي. كما أن المطابقة حساسة لحالة الأحرف: إذا استخدمت " co" بدلًا من ذلك، فسيبقى Acme Consulting دون تغيير ما لم تحوّل النص المصدر أولًا إلى أحرف صغيرة. وتعتمد سلسلة الاستبدالات أيضًا على ترتيبها. لذلك لا أنصح بتطبيق سلسلة الاستبدال الواسعة هذه على أحد أعمدة CRM.

بالنسبة إلى قائمة تعتمد القاعدة المعتمدة إزالة LLC عندما تسبقها مسافة وترد في نهاية الاسم، استخدم:

=IF(RIGHT(TRIM(B4),4)=" LLC",TRIM(LEFT(TRIM(B4),LEN(TRIM(B4))-4)),TRIM(B4))

هذه القاعدة ضيقة عن قصد. يتحول Acme LLC إلى Acme، بينما يظل كل من Acme LLC Services وAcme Consulting دون تغيير. ولا تغطي القاعدة L.L.C. أو جميع الأشكال القانونية أو كل البلدان. أضف قواعد منفصلة تمت مراجعتها عندما تتطلب بياناتك ذلك.

تعرض لقطات الشاشة الحالية أدناه المثال القديم للاستبدال الواسع. استخدم القاعدة الصريحة أعلاه لإنتاج النتيجة النهائية، وقارنها بالاسم الأصلي قبل قبول التغيير.

صيغة لتحويل أسماء الشركات إلى أحرف صغيرة وحذف اللواحق
صيغة لتحويل أسماء الشركات إلى أحرف صغيرة وحذف اللواحق

طريقة عمل الصيغة الأضيق: تزيل TRIM المسافات العادية المحيطة بالنص، وتفحص RIGHT الأحرف الأربعة الأخيرة، ثم تحذف LEFT هذه النهاية التي تم التحقق منها فقط. لا تراعي المقارنة حالة الأحرف في صيغ Excel العادية. ولا تفترض أن TRIM تزيل جميع محارف المسافات في Unicode.

إليك شكل النتيجة عند تطبيق صيغة تنظيف اسم الشركة:

تطبيق صيغة تنظيف اسم الشركة
تطبيق صيغة تنظيف اسم الشركة

ولا تنسَ سحب الزاوية السفلية اليمنى للخلية التي تحتوي على الصيغة الأصلية إلى أسفل لتطبيقها على جميع أسماء الشركات، كما هو موضح هنا:

تطبيق صيغة تنظيف أسماء الشركات على القائمة كاملة
تطبيق صيغة تنظيف أسماء الشركات على القائمة كاملة

لكن ما زال هناك أمر واحد: حالة الأحرف في أسماء الشركات غير منسقة.

استخدم =PROPER(D4) فقط عندما تكون حالة أحرف العناوين هي النتيجة المطلوبة. فهي تحول IBM إلى Ibm، لذا حافظ على حالة الأحرف الرسمية للعلامة التجارية أو أضف استثناءات صريحة عند الحاجة. توضح لقطات الشاشة عملية التحويل، ولا تضمن الحصول على الاسم التجاري الصحيح.

صيغة تنسق حالة الأحرف في أسماء الشركات كأسماء علم
صيغة تنسق حالة الأحرف في أسماء الشركات كأسماء علم

إليك شكل النتيجة عند تنفيذ الخطوة بطريقة صحيحة:

تطبيق صيغة تنسيق حالة الأحرف في أسماء الشركات
تطبيق صيغة تنسيق حالة الأحرف في أسماء الشركات

أخيرًا، اسحب الزاوية السفلية اليمنى إلى أسفل لتطبيق الصيغة على قائمة أسماء الشركات كاملة.

تطبيق الصيغة على جميع أسماء الشركات
تطبيق الصيغة على جميع أسماء الشركات

إذا كانت لديك بالفعل قائمة معتمدة بالأسماء القديمة والجديدة، فاتبع آلية استبدال قيم متعددة لتطبيق عمليات الربط هذه.

بالنسبة إلى الأسماء الأكثر تنوعًا، يقدم دليل تنظيف أسماء الشركات باستخدام AI خيارًا بديلًا. احتفظ بالقيم الأصلية، وراجع اللواحق القانونية والاختصارات والحالات الملتبسة أيًا كانت الطريقة التي تختارها.

الأسئلة الشائعة

كيف أستخرج النطاقات من عناوين البريد في Excel؟

يمكنك استخدام دالة TEXTAFTER في إصدارات Excel الحديثة، أو الجمع بين RIGHT وLEN وFIND في الإصدارات القديمة.

ما أفضل طريقة لتنظيف أرقام الهواتف في Excel؟

احتفظ بمدخلات الهاتف كنص، ولا تزل سوى علامات الترقيم التي تقصد حذفها، ثم افحص الأصفار البادئة ورموز الدول والأرقام الداخلية. تنسيق العرض الرقمي مثال أمريكي اختياري ومنفصل، وليس وسيلة للتحقق من الأرقام أو توحيدها دوليًا.

هل يمكن تنسيق أرقام دول أخرى في Excel؟

لا تطبق مثال العرض الأمريكي على قائمة تضم عدة دول. احتفظ بالنص الأصلي ومعلومات الدولة، ثم اختر قواعد صريحة للتوحيد الدولي أو أداة مناسبة لمعالجة أرقام الهواتف. فالتنسيق وحده لا يثبت صحة الرقم.

كيف أزيل لواحق LLC وInc وLtd من أسماء الشركات؟

ابدأ بقاعدة تمت مراجعتها لإزالة اللاحقة النهائية، مثل حذف LLC عندما ترد في نهاية الاسم. تجنب استبدال أجزاء النص بلا قيود داخل الأسماء. وتعامل مع حالة الأحرف كقرار منفصل لأن PROPER تغير الاختصارات وطريقة كتابة العلامات التجارية.

ماذا أفعل إذا لم تتوفر دالة TEXTAFTER؟

يمكنك استخدام صيغ بديلة لإصدارات Excel القديمة، مثل الجمع بين دوال RIGHT وLEN وFIND، أو التحقق مما إذا كان إصدار Excel الحالي لديك يدعم TEXTAFTER.