تبسيط تنظيف بيانات Excel: من النفور إلى الشغف بتحليل البيانات القيمة

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

Power Query: تبسيط عملية تنظيف البيانات بفعالية

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

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

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

كيفية إعداد Power Query في Excel

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

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

ولكن من السهل إصلاح ذلك باستخدام Power Query. إليك كيفية إعداد أول عملية تحويل للبيانات باستخدام Power Query:

1. حدد نطاق البيانات الخاص بك أو انقر في أي مكان داخل مجموعة البيانات الخاصة بك.
2. انتقل إلى علامة التبويب Data، ثم انقر فوق From Table/Range.
3. سيكتشف Excel حدود البيانات الخاصة بك وينشئ جدولاً.
4. انقر فوق OK لفتح محرر Power Query.
5. قم بإجراء التحويلات باستخدام أدوات الشريط. يمكنك تنظيف البيانات، وتوحيد التنسيقات، وإزالة الصفوف المكررة، وإضافة أعمدة محسوبة، وغير ذلك الكثير.
6. انقر فوق Close & Load لتطبيق التغييرات مرة أخرى على Excel.

يفتح محرر Power Query في نافذة منفصلة. تتيح لك هذه البيئة المعزولة التجربة دون التأثير على بياناتك الأصلية – فكر في الأمر على أنه صندوق رمل لتنظيف البيانات. هذه الميزة مهمة بشكل خاص عند التعامل مع مصادر بيانات متعددة أو معقدة.

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

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

خطوات تنظيف البيانات هذه تعالج معظم المشاكل

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

الخطوة 1: إزالة الرؤوس المدمجة

تتسبب الرؤوس المدمجة في حدوث مشكلات كبيرة. لا يستطيع Power Query اكتشاف أنواع الأعمدة بشكل صحيح عندما يكون لديك عنوان مثل “معلومات المبيعات” يمتد عبر عدة أعمدة. لإصلاح ذلك، اتبع الخطوات التالية:

1. في محرر Power Query، حدد الصف الذي يحتوي على الرأس المدمج.
2. انقر بزر الماوس الأيمن واختر إزالة الصفوف > إزالة الصفوف العلوية.
3. أدخل الرقم 1 لحذف صف الرأس المدمج.
4. انقر فوق موافق. الآن، ستكون رؤوس الأعمدة الحقيقية في موضعها الصحيح، مما يتيح لـ Power Query تحليل البيانات بشكل فعال.

الخطوة 2: توحيد تنسيقات التاريخ في Power Query

عند استيراد البيانات إلى Power Query، قد تواجه مشكلة اختلاف تنسيقات التاريخ، وهو أمر شائع جدًا عند التعامل مع مصادر بيانات متنوعة. على سبيل المثال، قد يحتوي جدول البيانات الخاص بك على ثمانية تنسيقات مختلفة للتاريخ، بما في ذلك إدخالات مثل “16/01/2024”. بشكل افتراضي، يعتمد Power Query على تنسيق التاريخ الأمريكي (MM/DD/YYYY)، لذلك فإن الإدخال “16/01/2024” سيؤدي إلى ظهور خطأ لأن الرقم 16 لا يمكن أن يكون شهرًا صالحًا. لسوء الحظ، ستحتاج إلى استبدال هذه القيم يدويًا أو استخدام دوال التاريخ في Excel.

لتوحيد تنسيقات التاريخ في Power Query، اتبع الخطوات التالية:

1. حدد عمود التاريخ.
2. انتقل إلى تحويل (Transform) > نوع البيانات (Data Type) > تاريخ (Date).
3. سيكتشف Power Query معظم التنسيقات تلقائيًا، بما في ذلك “January 15, 2024” و “15-Jan-24”.
4. تحقق من المعاينة للتأكد من تحويل جميع التواريخ بشكل صحيح.

عند التعامل مع تنسيقات التاريخ الدولية المختلطة، من الضروري دائمًا معاينة التحويلات التي تجريها. يعمل الاكتشاف التلقائي في Power Query بشكل جيد للتنسيقات الواضحة، ولكنه يفشل في تنسيق DD/MM/YYYY عندما تتجاوز قيم اليوم 12.

للتعامل بشكل صحيح مع تنسيقات التاريخ الدولية في Power Query، يجب عليك تحديد نوع البيانات بشكل صريح مع اللغة المناسبة (على سبيل المثال، الإنجليزية (المملكة المتحدة) لتنسيق DD/MM/YYYY) عند تغيير نوع العمود. هذه الخطوة ضرورية لضمان تفسير Power Query للبيانات بشكل صحيح وتجنب الأخطاء.

يمكنك القيام بذلك عن طريق النقر بزر الماوس الأيمن فوق العمود، وتحديد تغيير النوع (Change Type) > باستخدام الإعدادات المحلية (Using Locale)، ثم اختيار تاريخ (Date) والإعدادات المحلية المطلوبة. هذه الطريقة أكثر دقة من مجرد الاعتماد على الاكتشاف التلقائي أثناء “استيراد البيانات الأولي”. استخدام الإعدادات المحلية يضمن أن Power Query يفسر ترتيب اليوم والشهر والسنة بشكل صحيح بناءً على الاصطلاحات الخاصة بالمنطقة المحددة.

الخطوة الثالثة: تنظيف تناسق النصوص

تتطلب أسماء المنتجات مثل “surface laptop” كتابة الحرف الأول كبيراً، بينما تحتاج الاختلافات في كتابة “iPhone 15” مثل “iphone15” و “iPhone-15” إلى توحيد. هنا يظهر تفوق Power Query الحقيقي في معالجة البيانات وتنقيتها:

1. حدد عمود “اسم المنتج”.
2. انتقل إلى Transform > Format واختر الحالة المناسبة (Proper Case) لتصحيح الأخطاء الإملائية في الأحرف الكبيرة والصغيرة. هذه الخطوة مهمة لضمان تنسيق موحد لأسماء المنتجات.
3. بعد ذلك، انقر مرة أخرى على Transform > Replace Values لتوحيد الاختلافات في الكتابة. على سبيل المثال، يمكنك استبدال جميع الحالات المختلفة لـ “iPhone 15” بالصيغة الموحدة “iPhone 15”. هذه العملية تضمن تجانس البيانات وتسهل تحليلها.

الخطوة 4: إزالة المسافات الزائدة

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

1. حدد عمود “مندوب المبيعات”.
2. انتقل إلى Transform > Format > Trim.

ستختفي المسافات الزائدة من الإدخالات مثل ” sarah johnson ” و ” Emma Taylor”. هذه النقرة الواحدة تعالج مشكلات المسافات التي قد تتسبب في تعطيل الجداول المحورية وصيغ VLOOKUP. إن إزالة هذه المسافات الزائدة تضمن دقة البيانات وتسهل عمليات التحليل المتقدمة، مما يوفر الوقت والجهد ويقلل من احتمالية الأخطاء في التقارير.

الخطوة 5: التعامل الذكي مع القيم المفقودة

تتطلب الخلايا الفارغة في عمودي “الفئة” و “الوحدات المباعة” اهتمامًا خاصًا. يوفر Power Query عدة طرق للتعامل مع هذه المشكلة بفعالية.

بالنسبة لعمود “الفئات”:

1. حدد عمود “الفئة”.
2. انتقل إلى تحويل (Transform) > استبدال القيم (Replace Values).
3. استبدل القيم الفارغة (null) بـ “غير مصنف” (Uncategorized) أو اتركها فارغة للمراجعة اليدوية لاحقًا. هذه الطريقة تضمن عدم وجود فراغات في بياناتك وتسمح لك بتصنيف المنتجات لاحقًا.

بالنسبة للأعمدة الرقمية مثل “الوحدات المباعة”، قد يؤدي استبدال القيم الفارغة بالصفر إلى تحريف المتوسطات الحسابية بشكل كبير، مما يؤثر على دقة تحليل البيانات. بدلًا من ذلك، فكر في وضع علامة عليها كـ “بيانات مفقودة” (Data Missing) أو استخدام قيمة أخرى محايدة لا تؤثر على الحسابات. يمكنك أيضًا استخدام Power Query لإنشاء عمود إضافي يشير إلى ما إذا كانت البيانات مفقودة أم لا، مما يسمح لك بتصفية هذه القيم أو استبعادها من التحليلات إذا لزم الأمر. هذه الممارسة تضمن الحصول على نتائج تحليلية أكثر دقة وموثوقية عند التعامل مع القيم المفقودة.

الخطوة 6: توحيد تنسيقات العملة

تظهر قيم الإيرادات بتنسيقات مختلفة مثل “$1,200” و “1200” و “$1.2K”، ويتطلب كل منها معالجة مختلفة لضمان دقة التحليل المالي. لتوحيد هذه التنسيقات في Power Query، اتبع الخطوات التالية:

1. حدد عمود “الإيرادات” الذي يحتوي على القيم المراد توحيدها.
2. استخدم وظيفة استبدال القيم لتحويل التنسيق “$1.2K” إلى “1200”. هذه الخطوة ضرورية لأن Power Query لا يتعرف تلقائيًا على اختصار “K” (للألف).
3. بعد ذلك، انتقل إلى تحويل > نوع البيانات > عملة. هذه الخطوة ستقوم تلقائيًا بمعالجة علامات الدولار والفواصل، وتحويل القيم إلى تنسيق عملة موحد.
4. قم بمعاينة النتائج للتأكد من أن عملية التحويل قد تمت بشكل صحيح وأن جميع القيم تظهر الآن بتنسيق العملة المطلوب.

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

الخطوة 7: إنشاء فئات متسقة

إنّ توحيد الفئات أمر بالغ الأهمية لتحليل بيانات دقيق. يجب اعتبار المصطلحات المتشابهة مثل “Electronics” و “electronics” و “ELECTRONICS” متطابقة. لحسن الحظ، توفر وظائف النصوص في Power Query حلولًا سريعة وفعالة لهذه المشكلة.

1. حدد عمود الفئة (Category).
2. انقر فوق Transform > Format، ثم حدد الحالة المناسبة (Proper Case) لتصحيح الأحرف الكبيرة والصغيرة. هذه الخطوة تضمن توحيد تنسيق الفئات.

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

تظهر كل عملية تحويل في لوحة “الخطوات المطبقة” (Applied Steps). يمكنك تعديل الخطوات أو حذفها أو إعادة ترتيبها دون الحاجة إلى البدء من جديد. تمنح هذه المرونة Power Query ميزة كبيرة، خاصة عندما تحتاج إلى تعديل عملية التنظيف الخاصة بك.

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