تنظيف بيانات Excel المعقدة بسهولة باستخدام Power Query لتعزيز دقة الجداول

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

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

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

تخيل هذا: تقرير Excel آخر يصل إلى بريدي الوارد، ومرة ​​أخرى، إنه كابوسٌ من المسافات البادئة، والتهجئة غير المتناسقة، والصفوف غير المفيدة. في السابق، كنت أقضي ساعاتٍ في إصلاحه يدويًا. أما الآن، فأستخدم Power Query لتحليل الفوضى، وتنقية النص، وترتيب هيكله، والتخلص من كل الفوضى غير الضرورية.

يتوفر محرر Power Query في Excel كأداةٍ مدمجة في Excel لـ Microsoft 365 والإصدارات المستقلة من تطبيق سطح المكتب الصادرة في عام 2016 وما بعده. كما يتوفر Power Query أيضًا في Excel للويب، ولكن مع بعض القيود مقارنةً بإصدار سطح المكتب.

البحث عن محرر Power Query وفتحه

لتنظيف البيانات غير المرتبة، يجب تحميلها إلى محرر Power Query.

بعد التأكد من احتواء بياناتك على صف رأسي وتنسيقها كجدول Excel، انقر على أي خلية في الجدول، ثم في علامة تبويب “البيانات“، انقر على “من جدول/نطاق”.

في هذه المرحلة، يفتح محرر Power Query، ويعرض بياناتك.

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

تشخيص الأخطاء باستخدام تحليل البيانات

عند فتح مجموعة بيانات Excel جديدة لأول مرة، أول ما أفعله دائمًا هو تحديد مكامن الأخطاء والتناقضات بدقة. تُتيح لي أدوات تحليل البيانات في Power Query تشخيصًا بصريًا فوريًا لسلامة البيانات.

ملاحظة
افتراضيًا، يُجري Power Query تحليلًا للصفوف الألف الأولى فقط، لذا إذا كانت بياناتك تحتوي على صفوف أكثر من ذلك، فانقر على “تحليل الأعمدة بناءً على الصفوف الألف الأولى” في الشريط الأخضر أسفل النافذة، ثم حدد “تحليل الأعمدة بناءً على مجموعة البيانات بأكملها”. تأكد أيضًا من تعيين تنسيق الأرقام الصحيح لكل عمود بالنقر على الرمز الموجود في عنوانه.

في محرر Power Query، افتح علامة التبويب “عرض“. هناك، حدد “جودة الأعمدة”، و“توزيع الأعمدة”، و”تحليل الأعمدة”.

جودة الأعمدة: مقاييس صحة البيانات

تمنحك إحصائيات الأعمدة والنسب المئوية، الموجودة مباشرةً أسفل عناوين الأعمدة، نظرة عامة على جودة بيانات كل عمود.

إليك ما تعنيه الألوان:

• أخضر: جميع قيم البيانات صحيحة.
• رمادي داكن أو أسود: يحتوي العمود على قيم فارغة أو خالية.
• أحمر: يحتوي العمود على أخطاء، مثل عدم تطابق أنواع البيانات.
• مخطط أحمر وأبيض: أخطاء محددة، غالبًا ما ترتبط بانقطاع اتصال البيانات أو خطوة مُطبقة، تُسبب مشاكل في معالجة البيانات باستخدام Power Query.
• مخطط أخضر وأبيض: هناك أخطاء، وجودة البيانات المتبقية غير معروفة حاليًا.

توزيع الأعمدة: مخططات تكرار القيم

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

ملف تعريف العمود: الإحصائيات ومعلومات التوزيع

عند النقر على رأس عمود، تعرض لوحة “ملف التعريف” معلومات مفصلة حول محتويات العمود.

انقر بزر الماوس الأيمن على شريط توزيع أفقي لفتح خيارات التصفية. على سبيل المثال، يمكنك تصفية البيانات لعرض قيم الخطأ فقط.

تنظيف وتنسيق قيم النصوص

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

تنظيف وتنسيق البيانات

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

لتنظيف الأحرف غير القابلة للطباعة، حدد رأس عمود نص، وفي علامة التبويب “تحويل“، انقر على “تنسيق” > “تنظيف”.

تتخلص هذه الأداة فورًا من الأشياء غير المرئية التي تُعيق معالجة البيانات، مثل علامات الترقيم وعلامات الترقيم.

بعد ذلك، في نفس القائمة، انقر على “قص” لإزالة جميع المسافات البادئة واللاحقة غير الضرورية، مع ترك جميع المسافات المفردة بين الكلمات كما هي.

الحصول على اتساق الأحرف الكبيرة

في هذا المثال، يحتوي عمود “القسم” على “Mexico” و”mexico” و”MEXICO”. بما أنك ترغب في احتساب هذه البيانات كدولة واحدة لاحقًا عند التصفية والتجميع والدمج، عليك التأكد من اتساق الأحرف الكبيرة.

انقر بزر الماوس الأيمن فوق رأس العمود، ثم مرر الماوس فوق “تحويل”، ثم انقر فوق “كتابة كل كلمة بأحرف كبيرة”.

ومع ذلك، يؤدي هذا أيضًا إلى تحويل الولايات المتحدة إلى الولايات المتحدة والمملكة المتحدة إلى المملكة المتحدة، لذا يتعين عليك إصلاح هذه المشكلة بالنقر فوق “استبدال القيم” في علامة التبويب “تحويل”.

إنشاء هيكل باستخدام أعمدة شرطية ومخصصة

أحيانًا، تحتاج البيانات الخام إلى أكثر من مجرد تنظيف، بل تحتاج إلى هيكل جديد بناءً على القيم الموجودة، وهنا تبرز أهمية ميزة “إضافة عمود” في Power Query.

إنشاء سيناريو شرطي (IF-THEN) باستخدام أعمدة شرطية

أستخدم الأعمدة الشرطية لتصنيف البيانات الرقمية بناءً على حد معين، مما يُسهّل قراءة بياناتي وتلخيصها.

لنفترض أنك تريد إنشاء عمود جديد باسم “تصنيف المبيعات” بناءً على عمود المبيعات الحالي، حيث تُصنّف المبيعات التي تساوي أو تزيد عن 10,000 دولار أمريكي على أنها “مرتفعة” وجميع القيم الأخرى على أنها “منخفضة”. في محرر Power Query، ضمن علامة التبويب “إضافة عمود”، انقر على “عمود شرطي”.

الآن، في مربع الحوار “العمود الشرطي”، قم بما يلي:

عند النقر فوق “موافق“، يقوم Power Query على الفور بإنشاء العمود المصنف الجديد.

إنشاء أعمدة مخصصة باستخدام لغة M

لتصنيف البيانات بناءً على النص، أنشئ عمودًا مخصصًا جديدًا باستخدام صيغة M بسيطة. يتيح لي هذا توحيد الفئات حتى مع اختلاف قيم النص قليلاً.

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

في محرر Power Query، انتقل إلى علامة التبويب “إضافة عمود” وانقر على “عمود مخصص”.

في مربع الحوار، سمِّ العمود الجديد “المنطقة”. ثم، بالنسبة لصيغة العمود المخصص، أستخدم مزيجًا من عمليات التحقق البسيطة وعبارات if-then-else في عمود “البلد” لتجميعها في مناطق، مع التأكد من تجميع المتغيرات مثل الولايات المتحدة الأمريكية والولايات المتحدة الأمريكية معًا.

if [Country] = "Mexico" or [Country] = "US" or [Country] = "United States" or [Country] = "Canada" then "The Americas"
else if [Country] = "UK" or [Country] = "England" then "Europe"
else "Other"

انقر فوق “موافق” لرؤية العمود الجديد الذي يصنف بنجاح بيانات النص الفوضوي إلى مجموعات موحدة للتصفية والتجميع في المستقبل.

نصيحة
تُضاف الأعمدة الجديدة دائمًا إلى الحافة اليمنى لمجموعة البيانات. ما عليك سوى النقر على العمود وسحبه من رأسه لنقله. تذكر أيضًا التأكد من تطبيق التنسيق الصحيح عليه بالنقر على الرمز الموجود على يسار النص في رأس العمود.

إزالة الفوضى بإزالة الصفوف

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

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

إزالة العناصر غير المرغوب فيها من الأعلى والأسفل

أولاً، في علامة التبويب “الصفحة الرئيسية”، وسّع القائمة المنسدلة “إزالة الصفوف” ضمن مجموعة “تقليل الصفوف”.

ثم انقر على الخيارات التالية:

• إزالة الصفوف العليا: يتيح لك هذا الخيار إزالة عدد محدد من الصفوف من أعلى مجموعة البيانات. على سبيل المثال، إذا كان هناك نص في الصفين 1 و2 أعلى عناوين الأعمدة المقصودة، فاكتب 2 لإزالة هذين الصفين.
• إزالة الصفوف السفلى: استخدم هذه الأداة لإزالة عدد محدد من الصفوف التي تحتوي على معلومات التذييل، مثل تواريخ التقارير أو الملخصات.

بعد القيام بذلك، سيعرض لك محرر Power Query مجموعة بيانات مرتبة خالية من أي فوضى غير مرغوب فيها.

ترقية العنوان

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

إزالة الصفوف الفارغة

وأخيرًا، احذف أي صفوف فارغة تمامًا، ولكنها لا تزال تشغل مساحة، وقد تُعيق عمليات التحليل لاحقًا. في علامة التبويب “الصفحة الرئيسية”، انقر على “إزالة الصفوف” > “إزالة الصفوف الفارغة”.

بالإضافة إلى تحليل الأخطاء، وتنظيف النصوص، وإضافة الأعمدة، وإزالة الصفوف غير المرغوب فيها، يُمكن لـ Power Query من Excel مساعدتك في العديد من مهام تنظيم البيانات الأخرى، مثل تقسيم الخلايا، واستبدال الأخطاء، وإلغاء تمحور البيانات، وملء الفراغات بناءً على الخلايا أعلاه. السر يكمن في البدء مباشرةً – فبعد نصف ساعة من تجربة الأدوات المختلفة، ستُدرك مدى فائدة هذه الميزة المُدمجة.

تنظيف البيانات داخل Excel لم يعد مهمة معقدة عند استخدام Power Query، فهو يقدّم حلولًا عملية وسريعة للتعامل مع الجداول الفوضوية. اعتماد هذه الأداة يضمن بيانات أكثر دقة ويقلل من الوقت المهدور في المعالجة اليدوية.

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