تنظيف البيانات في إكسل: خطوات مرتبة ومعادلات جاهزة للبيانات العربية
صدّرت قائمة عملاء متجرك من واتساب ومن نموذج الطلبات، وفتحتها في إكسل لتعرف عدد العملاء الحقيقي. النتيجة 1,200 اسم. لكن بعد قليل تلاحظ “محمد العتيبي” مكررًا ثلاث مرات. وتجد رقمًا يبدأ بـ 966 وآخر بـ 05، ومسافات زائدة في كل مكان. هل عندك فعلًا 1,200 عميل؟ غالبًا لا.
هنا تأتي أهمية تنظيف البيانات في إكسل قبل أي تحليل أو حملة تسويق. فالتقرير المبني على بيانات متسخة يعطيك أرقامًا مضللة، مهما كانت معادلاتك ذكية. في السطور التالية خطوات عملية، ومعادلات جاهزة للنسخ، ومشكلات خاصة بالبيانات العربية لا تجدها في الشروحات الأجنبية.
لماذا تهتم بتنظيف البيانات في إكسل؟
تخيل أنك سترسل عرضًا لعملائك. إذا كان العميل مكررًا ثلاث مرات، فسيصله العرض ثلاث مرات ويشعر بالإزعاج. وإذا كانت الأرقام بصيغ مختلفة، فستفشل أداة الإرسال في نصفها.
كذلك تتأثر قراراتك. على سبيل المثال، قد تظن أن مدينة ما هي الأقوى في المبيعات، بينما السبب أنها مكتوبة بطريقتين: “الرياض” و”الرياض ” بمسافة زائدة. لذلك يأتي التنظيف أولًا، ثم التحليل كما شرحنا في تحليل البيانات في إكسل للمبتدئين.
القاعدة الأولى: انسخ الملف قبل أن تلمسه
قبل أي خطوة، احفظ نسخة احتياطية من البيانات الأصلية في ملف منفصل. هذه نصيحة مايكروسوفت نفسها في دليلها لتنظيف البيانات. بعد ذلك حوّل النطاق إلى جدول، حتى تمتد المعادلات تلقائيًا مع كل صف جديد.
خطوات تنظيف البيانات في إكسل بالترتيب
اتبع هذا الترتيب، لأن كل خطوة تسهل التي بعدها:
- احذف المسافات الزائدة بالدالة TRIM.
- أزل الرموز الخفية بالدالة CLEAN، خصوصًا في البيانات المنسوخة من مواقع أو أنظمة أخرى.
- وحّد الكتابة: أسماء المدن، والحروف العربية المتشابهة، وحالة الأحرف الإنجليزية.
- حوّل الأرقام والتواريخ المخزنة كنص إلى أرقام وتواريخ حقيقية.
- افصل الأعمدة المدمجة، مثل الاسم الكامل أو العنوان، باستخدام “تحويل النص إلى أعمدة”.
- احذف التكرار في النهاية فقط، بعد أن أصبحت القيم موحدة.
لماذا التكرار في الأخير؟ لأن “محمد العتيبي” و”محمد العتيبي” بمسافتين يبدوان مختلفين لإكسل. وبالتالي لن يحذف أحدهما قبل التنظيف.
معادلات جاهزة لتنظيف البيانات في إكسل
انسخ هذه المعادلات وعدّل مرجع الخلية فقط:
| المشكلة | المعادلة | ماذا تفعل؟ |
|---|---|---|
| مسافات زائدة | =TRIM(A2) | تحذف المسافات في البداية والنهاية والمكررة في الوسط |
| مسافة خفية من مواقع الويب | =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) | تحول المسافة غير القابلة للكسر إلى مسافة عادية ثم تنظفها |
| رموز غير مطبوعة | =CLEAN(A2) | تزيل الرموز الخفية التي تفسد المطابقة |
| رقم مخزن كنص | =VALUE(A2) | تحول النص إلى رقم تستطيع جمعه |
| تاريخ مخزن كنص | =DATEVALUE(A2) | تحول النص إلى تاريخ حقيقي يقبل الترتيب والتصفية |
| أحرف إنجليزية غير موحدة | =PROPER(A2) | تجعل أول حرف من كل كلمة كبيرًا |
نصيحة: بعد أن تتأكد من النتيجة، انسخ العمود الجديد والصقه “قيمًا فقط” فوق العمود القديم. ثم احذف عمود المعادلات.
مشكلات خاصة بالبيانات العربية
هذا الجزء من تنظيف البيانات في إكسل يتجاهله أغلب الشرح الأجنبي، رغم أنه سبب نصف التكرار في ملفاتنا:
الهمزات والتاء المربوطة والألف المقصورة
قد يكتب عميل “أحمد” ويكتب آخر “احمد”. وقد تكتب “مكة” مرة و”مكه” مرة أخرى. لتوحيد الألف استخدم:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"أ","ا"),"إ","ا"),"آ","ا")
ثم استخدم الفكرة نفسها لتوحيد “ة” مع “ه” و”ى” مع “ي” في عمود مساعد للمطابقة فقط. أما الاسم الظاهر للعميل فاتركه كما كتبه.
أرقام الجوال بصيغ مختلفة
في ملفات المتاجر السعودية ستجد 0501234567 و966501234567 و+966501234567 للعميل نفسه. هذه المعادلة توحدها إلى صيغة تبدأ بـ 05:
=IF(LEFT(SUBSTITUTE(A2,"+",""),3)="966","0"&MID(SUBSTITUTE(A2,"+",""),4,9),A2)
مثال: الرقم +966501234567 يصبح 0501234567. لكن تأكد أولًا أن العمود منسق كنص، حتى لا يحذف إكسل الصفر في البداية.
الأرقام الهندية
إذا وصلتك أرقام مثل ١٢٣ بدل 123، فاستخدم البحث والاستبدال لكل رقم، أو اضبط مصدر التصدير على الأرقام العربية الغربية.
حذف التكرار عند تنظيف البيانات في إكسل
بعد التوحيد، حدد الجدول ثم اختر من تبويب البيانات “إزالة التكرارات”. بعد ذلك حدد العمود الذي يعرّف العميل، مثل رقم الجوال، وليس الاسم. فاسمان متطابقان قد يكونان لشخصين مختلفين فعلًا.
ومع ذلك، صفِّ القيم الفريدة أولًا وراجعها بعينك قبل الحذف النهائي. وإن كنت تعمل مع فريق على ملف مشترك، فقد تناسبك جداول بيانات جوجل بميزاتها المشابهة.
أدوات توفر وقتك في الملفات الكبيرة
- التعبئة السريعة: اكتب النتيجة المطلوبة لأول صفين، ثم اضغط Ctrl+E ليكمل إكسل النمط تلقائيًا. مفيدة لفصل الاسم الأول عن العائلة.
- Power Query: من تبويب البيانات، تسجل فيه خطوات التنظيف مرة واحدة، ثم تعيد تطبيقها على ملف الشهر القادم بضغطة تحديث.
- التنسيق الشرطي: لوّن القيم المكررة لتراها قبل أن تحذفها.
أخطاء شائعة في تنظيف البيانات في إكسل
- العمل على الملف الأصلي دون نسخة احتياطية.
- حذف التكرار قبل توحيد المسافات والحروف.
- الاعتماد على الاسم بدل معرف فريد مثل رقم الجوال أو رقم العميل.
- ترك المعادلات مرتبطة بأعمدة ستحذفها لاحقًا، فتظهر أخطاء المرجع.
- تنظيف البيانات مرة واحدة ونسيانها. الأفضل وضع قواعد إدخال تمنع الخطأ من البداية.
ولمعرفة أخطاء أخرى تفسد النتائج بعد التنظيف، راجع أخطاء تحليل البيانات.
منتجات قد تساعدك
- كتب تعلم إكسل من أمازون: مرجع مرتب تعود إليه بدل البحث المتكرر.
- لوحة مفاتيح لاسلكية عربي وإنجليزي من أمازون: الاختصارات تصبح أسرع مع لوحة مريحة وحروف واضحة.
- ماوس لاسلكي مريح من علي إكسبريس: خيار اقتصادي لمن يعمل ساعات طويلة على الجداول، والشحن أبطأ.
إفصاح: بعض الروابط في هذا المقال روابط تسويقية من أمازون وعلي إكسبريس. بصفتنا مشاركين في برنامج شركاء أمازون وبرامج تسويق أخرى، نحصل على عمولة من عمليات الشراء المؤهلة دون أي تكلفة إضافية عليك.
أسئلة شائعة عن تنظيف البيانات في إكسل
هل تحذف TRIM كل المسافات؟
تحذف المسافات العادية الزائدة فقط. أما المسافة الخفية القادمة من صفحات الويب فتحتاج SUBSTITUTE مع CHAR(160) أولًا.
كيف أعرف أن الرقم مخزن كنص؟
غالبًا يظهر في يسار الخلية بدل يمينها، وقد يظهر مثلث أخضر صغير في زاويتها. كذلك لا يدخل في نتيجة الجمع.
هل أستخدم Power Query أم المعادلات؟
للملف الصغير الذي تنظفه مرة واحدة تكفيك المعادلات. أما الملف الذي يتكرر كل أسبوع أو شهر فوفر وقتك بـ Power Query.
الخلاصة
تنظيف البيانات في إكسل خطوة لا تحب أن تفعلها، لكنها تحمي كل قرار يأتي بعدها. ابدأ بنسخة احتياطية، ثم المسافات والرموز، ثم التوحيد، واحذف التكرار في النهاية. وجرّب اليوم المعادلات الجاهزة على ملف عملائك، وستتفاجأ بعدد العملاء الحقيقي.
