طرق سهلة لتنظيف واستيراد البيانات في Excel باستخدام Power Query
تنظيف البيانات واستيرادها يمكن أن يكونان من أكثر المهام استهلاكًا للوقت عند العمل على جداول البيانات. باستخدام Power Query في Excel، يمكنك تبسيط هذه العمليات بسهولة وتحسين دقة البيانات وجاهزيتها للتحليل. في هذا المقال، سنأخذك عبر خطوات عملية لتنظيف بياناتك واستيرادها بكفاءة باستخدام Power Query، مما يوفر عليك الوقت ويزيد من إنتاجيتك.
في عام 2010، أضافت Microsoft مصطلحًا تقنيًا آخر – Power Query – إلى القائمة الطويلة من المصطلحات الخاصة بـ Excel، لكنه ليس معقدًا كما يبدو. في الواقع، بمجرد البدء، لن يكون الأمر صعبًا للغاية، بشرط أن يكون لديك على الأقل فهم مبتدئ لبرنامج Microsoft Excel.
ما هو Power Query؟
عند تقديمه لأول مرة إلى Excel، كان Power Query عبارة عن وظيفة إضافية، ولكن نظرًا لفائدته، فقد أصبح أداة أصلية منذ عام 2016.
باختصار، فإن Power Query له غرض أساسي واحد: توفير الوقت عند التعامل مع البيانات. وبشكل أكثر تحديدًا، يمكن استخدامه لتنظيف البيانات الموجودة بالفعل في Excel، واستيراد البيانات وتنظيمها من العديد من الأماكن المختلفة، أو دمج ملفات مختلفة قبل تحميل البيانات إلى موقع محدد من اختيارك. يمكنك أيضًا تحديث البيانات للتأكد من أن لديك أحدث إصدار.
يمكن العثور على أدوات Power Query في Excel في مجموعة الحصول على البيانات وتحويلها في علامة التبويب البيانات.
في هذه المقالة، سأوضح لك كيفية استخدام Power Query لتنظيف بياناتك وكيفية استيراد البيانات وتنظيمها من جدول بيانات آخر.
استخدام Power Query لتنظيف بيانات Excel
أستخدم Power Query دائمًا لتنظيف البيانات في جداول Excel الخاصة بي. لنفترض أنك قمت بنسخ قائمة بعناوين البريد الإلكتروني من حقل CC في بريد إلكتروني Outlook إلى الخلية A1، وتريد تحويل القائمة إلى جدول يحتوي على الأسماء الأولى (أو الألقاب) والألقاب وعناوين البريد الإلكتروني. على الرغم من أنه يمكنك استخدام وظائف Excel للقيام بذلك، إلا أنه أسهل كثيرًا (وليس منحنى تعليميًا شديد الانحدار!) في محرر Power Query في Excel.
ملاحظة
في هذه المرحلة، من المهم ملاحظة أن هذا مجرد مثال بسيط لكيفية استخدام Power Query لترتيب بياناتك. إذا لم تستخدم Power Query من قبل، فاتبع الخطوات التالية لمعرفة كيفية عمله في أبسط صوره، وبعد ذلك ستتمكن من تجربة أدوات Power Query الأخرى لترتيب بيانات Excel الأكثر تعقيدًا.
أولاً، افتح علامة التبويب “البيانات” على الشريط، وانقر فوق “من جدول/نطاق” في مجموعة البيانات.
بعد ذلك، في مربع الحوار “إنشاء جدول”، تأكد من تحديد الخلية أو الخلايا الصحيحة، وأخبر Excel ما إذا كانت بياناتك تحتوي على عناوين أم لا – في حالتي، لا تحتوي على عناوين، لذلك سأترك هذا المربع بدون تحديد – ثم انقر فوق “موافق“.
سيفتح Excel بعد ذلك محرر Power Query، وهنا يمكنك القيام بالعديد من الأشياء الغريبة والرائعة، بما في ذلك تنظيم بياناتك.
الخطوة الأولى هي تقسيم كل عنوان بريد إلكتروني إلى صف خاص به، لذا انقر بزر الماوس الأيمن فوق رأس العمود، وحرك الماوس فوق “تقسيم العمود”، وانقر فوق “حسب الفاصل”. يمكنك اختيار طرق أخرى لتقسيم بياناتك، مثل بعد عدد معين من الأحرف أو بين الأحرف الكبيرة. ومع ذلك، في حالتي، يتم فصل عناوين البريد الإلكتروني بفواصل منقوطة، لذا فإن خيار الفاصل يعمل.
في مربع الحوار “تقسيم العمود حسب الفاصل”، حدد “فاصلة منقوطة” في القائمة المنسدلة. ثم انقر فوق “خيارات متقدمة“، وحدد “الصفوف”، لأنك تريد تقسيم عناوين البريد الإلكتروني إلى صفوف فردية بدلاً من أعمدة فردية. الآن، انقر فوق “موافق“.
وقد نجح هذا الأمر بشكل جيد، حيث تم تقسيم عناوين البريد الإلكتروني إلى صفوف منفصلة، مع استخدام Power Query لفاصل الفاصلة المنقوطة لمعرفة متى انتهت إحدى رسائل البريد الإلكتروني ومتى بدأت التالية.
نصيحة
لاحظ أن لوحة إعدادات الاستعلام على الجانب الأيمن من الشاشة تسجل كل خطوة تتخذها. إذا أخطأت، فما عليك سوى حذف الخطوة ذات الصلة للعودة إلى حيث كنت من قبل!
بعد النقر المزدوج على رأس العمود وكتابة عنوان البريد الإلكتروني، تريد استخراج الاسم الأول (أو اللقب) والاسم الأخير في أعمدة منفصلة. هناك أكثر من طريقة للقيام بذلك، ولكن أفضل طريقة هي النقر بزر الماوس الأيمن فوق رأس العمود، وتحديد “إضافة عمود من الأمثلة”.
الآن، بما أن الاسم الأول في قائمتي هو Captain، فسوف أكتب هذا في العمود الذي ظهر على يمين الشاشة، ثم أضغط على Enter. وها هي ذي! يتعرف Power Query Editor على نمط ويقترح الأسماء المتبقية لاستكمال عمودي. بمجرد التأكد من أنك راضٍ عن الاقتراح، انقر فوق “موافق“.
بعد ذلك، اتبع نفس العملية للاسم الأخير، وأعد تسمية رؤوس الأعمدة في محرر Power Query.
يمكنك الآن إعادة ترتيب الأعمدة بالنقر عليها وسحبها. في حالتي، أريد أن يكون عمود عنوان البريد الإلكتروني على يمين الأسماء.
قبل تحميل هذا الجدول الجديد إلى جدول البيانات الخاص بك، قم بتسميته في جزء إعدادات الاستعلام.
الآن، انقر فوق القائمة المنسدلة “إغلاق وتحميل” في الشريط، ثم اختر “إغلاق وتحميل إلى”.
في حالتي، أريد تحميله كجدول في الخلية A4 في ورقة العمل الموجودة، لذا فهذه هي الخيارات التي سأختارها في مربع حوار استيراد البيانات قبل النقر فوق “موافق”.
نصيحة
لاختيار خلية حيث سيتم وضع الجدول، ضع المؤشر في مربع الحقل المسمى “3” في لقطة الشاشة أدناه، وحدد الخلية بالماوس
إذا كنت بحاجة إلى إجراء أي تغييرات على الجدول، فيمكنني النقر نقرًا مزدوجًا فوق الاستعلام في جزء الاستعلامات والاتصالات.
على نحو مماثل، إذا قمت بتعديل القائمة الأصلية التي قمت بنسخها من Outlook (مثل إضافة عنوان بريد إلكتروني آخر)، فيمكنني تحديث جدول Power Query الخاص بي عن طريق النقر بزر الماوس الأيمن في أي مكان داخل الجدول والنقر فوق “تحديث”.
استخدام Power Query لاستيراد البيانات وإعادة تنظيمها
يمكن أيضًا استخدام Power Query لاستيراد البيانات من مواقع مختلفة، مثل ملف PDF أو موقع ويب. في هذا المثال، سأوضح لك كيفية استيراد البيانات ومعالجتها من جدول بيانات آخر، وهو أمر مفيد بشكل خاص إذا كنت تريد استخراج مجموعة محددة للغاية من البيانات من مجموعة بيانات كبيرة، أو إذا كنت تريد إعادة تنظيم طريقة عرض بياناتك.
ملاحظة
كما هو الحال مع المثال أعلاه، سأعرض لك مثالاً بسيطًا للغاية لاستخدام Power Query لهذا الغرض، ويمكنك بعد ذلك الاستفادة من هذه المهارات في اللعب بالأداة.
أولاً، افتح علامة التبويب “البيانات”، وانقر فوق الحصول على البيانات > من ملف > من مصنف Excel.
بعد ذلك، استخدم نافذة استيراد البيانات لتحديد الملف الذي تريد استيراد البيانات منه، وانقر فوق “استيراد”. سيؤدي هذا إلى تشغيل نافذة المستكشف، التي تعرض معاينة للبيانات التي يمكنك استيرادها من جدول البيانات المرشح. في حالتي، سأنقر فوق “الورقة 1″، حيث توجد البيانات التي أريد استيرادها، ثم انقر فوق “تحويل البيانات”.
في محرر Power Query الذي يتم تحميله، يمكنني النقر فوق “حفظ وتحميل” مباشرةً في الزاوية العلوية اليسرى، ولكن هناك بعض التغييرات التي يجب إجراؤها قبل القيام بذلك.
أولاً، أحتاج إلى ترقية الصف الأول إلى رؤوس الأعمدة، بحيث يكون الصف الأول من جدول البيانات الخاص بي هو الصف الأول من البيانات. ومن السهل أن تجد زرًا يتيح لك القيام بذلك على الفور: “استخدام الصف الأول كرؤوس” في مجموعة التحويل في علامة التبويب الصفحة الرئيسية.
ثانيًا، أريد صفًا جديدًا من البيانات لكل متجر ولكل شهر، حتى أتمكن من تحليلها بشكل فردي. في الوقت الحالي، العمودان الأولان جيدان كما هما، لذا يمكنني تحديدهما بالنقر فوق رأس كل عمود مع الضغط على Ctrl. بعد ذلك، سأنقر بزر الماوس الأيمن فوق رأس أي عمود، ثم انقر فوق “إلغاء تثبيت الأعمدة الأخرى”.
الآن، أصبح لكل متجر صف خاص به لكل شهر.
أخيرًا، قبل تحميل هذا إلى جدول البيانات الخاص بي، أحتاج إلى ترتيب عناوين الأعمدة. إذا كنت تريد القيام بنفس الشيء، فانقر نقرًا مزدوجًا فوق أي من العناوين لإعادة تسميتها، وانقر فوق الرمز الموجود على يسار كل عنوان عمود لتحديد نوع البيانات. سيؤدي هذا إلى إجبار Power Query على تحديد أي مشكلات في البيانات، مثل عدم احتواء إحدى الخلايا في عمود الشهر على شهر.
الآن، بعد تسمية الجدول في جزء إعدادات الاستعلام على يمين نافذة Power Query، انقر فوق إغلاق وتحميل > إغلاق وتحميل إلى، وحدد الموقع الذي تريد ظهور الجدول فيه.
تذكر أن إحدى الفوائد الرئيسية لاستخدام Power Query بدلاً من مجرد نسخ البيانات ولصقها هي أنه إذا تغيرت بيانات المصدر، فيمكنك تحديث البيانات المعاد صياغتها في جدول البيانات الجديد. للتأكد من حصولك على أحدث إصدار من البيانات، ما عليك سوى النقر بزر الماوس الأيمن فوق الجدول الجديد، ثم النقر فوق “تحديث”.
يمكنك أيضًا استخدام Power Query لاستيراد الجداول من الويب. على سبيل المثال، قد ترغب في استيراد جدول دوري من المرجح أن يتغير كل عطلة نهاية أسبوع، لذا بدلاً من نسخ البيانات ولصقها يدويًا وتحول جدول الدوري بسرعة إلى جدول قديم، يمكن أن يساعدك استخدام Power Query في ضمان حصولك على أحدث إصدار من البيانات.
Power Query هو أداة قوية تجعل التعامل مع البيانات في Excel أكثر سهولة وفعالية. من خلال استخدام هذه الأداة، يمكنك تنظيف بياناتك بسرعة، التخلص من الأخطاء، واستيراد المعلومات اللازمة لتحليلها دون عناء. ابدأ الآن بتجربة الخطوات المذكورة لتبسيط عملك وتحقيق أقصى استفادة من وقتك.