العمل مع الجداول الكبيرة في Excel قد يكون مرهقًا إذا لم تكن تستخدم الأدوات الصحيحة. Power Query يُوفّر طريقة فعالة لتنظيف البيانات وتحويلها بسرعة ودقة، دون الحاجة إلى تكرار الخطوات يدويًا. سواء كنت تريد حذف الأعمدة الفارغة، دمج الجداول، تحويل الصيغ، أو تصفية القيم المكررة، هناك أوامر محددة تُنجز المهمة بسهولة. في هذه المقالة، نستعرض مجموعة أوامر أساسية في Power Query تُساعدك على تنظيم بياناتك بطريقة احترافية، وتوفّر عليك الكثير من الوقت في عمليات المعالجة اليدوية.
هل سمعتَ عن Power Query في Microsoft Excel، لكنك ظننتَ دائمًا أنه مخصصٌ فقط لخبراء Excel؟ دعني أتوقف عند هذا الحد، ففي الواقع، صُمِّم Power Query ليكون سهل الاستخدام، والأهم من ذلك، أنه يُجنِّبك الكثير من العناء عند تنظيم بياناتك.
ملاحظة
هذا الدليل مُخصَّصٌ للقراء الجدد على Microsoft Excel أو Power Query. لا تتطلب هذه النصائح معرفةً أو استخدام أيٍّ من لغات صيغ Power Query M، وهي تُوفِّر نقطة انطلاقٍ جيدةٍ للتعرف على بعض الأوامر التي يمكنك استخدامها في هذه الأداة الفعّالة لمعالجة البيانات.
1. تقسيم الخلايا باستخدام المُحدِّد لعزل القيم
هناك العديد من الطرق في Excel لتقسيم البيانات إلى أكثر من عمود واحد، بما في ذلك أداة “تحويل النص إلى أعمدة”، وميزة “التعبئة السريعة”، والوظائف المُضمَّنة. ومع ذلك، فإن الطريقة الأكثر سهولةً هي تشغيل مُحرِّر Power Query، وإجراء العملية هناك.
لنفترض أنك وجدتَ جدولًا على ويكيبيديا تُريد تحويله باستخدام Power Query. لاستيراد الجدول، أولاً، انقر فوق “الحصول على البيانات” في علامة التبويب الصفحة الرئيسية على الشريط، ثم حدد من مصادر أخرى > من الويب.
بعد ذلك، بعد لصق عنوان URL في حقل النص في مربع الحوار “من الويب” والنقر فوق “موافق”، قمت بتحديد الجدول في الجزء الأيسر من المستعرض، ثم قمت بالنقر فوق “تحويل البيانات”.
عند تحميل الجدول في محرر Power Query، لاحظت أن عمود Career Span يحتوي على قطعتين من البيانات – السنة التي بدأ فيها كل لاعب مسيرته المهنية، والسنة الأخيرة التي لعب فيها – وتريد أن تكون هذه القطعتين في أعمدة منفصلة.
للقيام بذلك، انقر بزر الماوس الأيمن فوق رأس العمود، ثم مرر الماوس فوق “تقسيم العمود”، وحدد “حسب الفاصل”.
ملاحظة
الفاصل هو حرف أو رمز أو مسافة تُستخدم لفصل العناصر في تسلسل.
في هذه المرحلة، يراجع محرر Power Query البيانات في العمود للتحقق من إمكانية اكتشاف فاصل محتمل. في هذه الحالة، يتم تحديد أن كل خلية تحتوي على شرطة، ويفترض بشكل صحيح أن هذه هي النقطة التي يجب تقسيم الخلية عندها. مع ذلك، يمكنك اختيار فاصل مختلف من القائمة المنسدلة الأولى إذا لزم الأمر.
نظرًا لوجود فاصل واحد فقط في كل خلية من العمود المحدد في هذا المثال، فلا داعي لإجراء أي تغييرات أخرى على الخيارات في مربع الحوار، لذا انقر فوق “موافق“، وشاهد البيانات المحولة حديثًا في محرر Power Query.
لإعادة تسمية الأعمدة الجديدة، انقر نقرًا مزدوجًا فوق رؤوس الأعمدة، واكتب تسميات البيانات الجديدة.
الآن، نجحت في تقسيم البيانات باستخدام محرر Power Query، ولكن هل لاحظتَ اختلافًا في محاذاة الخلايا في العمودين الجديدين؟ يعود ذلك إلى أن القيم في عمود “أول تشغيل” جميعها أرقام (كما هو موضح بالرمز 123 بجوار رأس العمود)، ولكن بما أن القيم في عمود “آخر تشغيل” تتضمن نصًا وأرقامًا، فإن Excel يعامل هذا العمود كعمود نصي (ومن هنا جاء الرمز ABC في رأس العمود).
لإصلاح هذه المشكلة، حدد رمز “ABC” في عمود “آخر تشغيل”، ثم انقر فوق “عدد صحيح”.
الآن، تم تنسيق كلا العمودين كمجموعات بيانات رقمية.
مع ذلك، نظرًا لاحتواء بعض الخلايا في عمود “آخر تشغيل” على قيم نصية، فإنها تظهر الآن كأخطاء. لكن لا تقلق، تابع القراءة لمعرفة كيفية إصلاح هذه المشكلة!
2. استبدال الأخطاء لاستخدامها في العمليات الحسابية
يُعد Power Query في Excel أداة فعّالة للتعامل مع أي أخطاء تظهر في بياناتك.
بالنظر إلى الاستعلام في المثال السابق، تحتوي بعض الخلايا في عمود “آخر تشغيل” على أخطاء، لأنها كانت تحتوي سابقًا على القيمة النصية “Present” في عمود بتنسيق أعداد صحيحة.
حاليًا، إذا نقرت على “إغلاق وتحميل” في الزاوية العلوية اليسرى من محرر Power Query، فستكون الخلايا التي تحتوي على قيم أخطاء فارغة في الجدول الناتج. قد يبدو هذا مثاليًا، إلا أن الخلايا الفارغة في عمود Excel قد تُسبب مشاكل عند فرز البيانات وتصفيتها أو عند الإشارة إلى عناوين الأعمدة في الصيغ، لذا يُفضل ملؤها بقيم مفيدة.
للقيام بذلك، انقر بزر الماوس الأيمن على عنوان العمود، ثم حدد “استبدال الأخطاء”.
ملاحظة
قد ترغب باختيار “إزالة الأخطاء” من قائمة النقر بزر الماوس الأيمن. مع ذلك، سيؤدي هذا إلى حذف الصف بأكمله، وليس الخطأ فقط، لذا انقر على هذا الخيار فقط إذا كنت ترغب في ذلك.
الآن، في مربع حوار “استبدال الأخطاء”، اكتب القيمة التي تريد ظهورها بدلاً من الأخطاء. في هذه الحالة، بما أن الأخطاء ظهرت في الخلية التي كُتب فيها “الحاضر”، يمكنك كتابة السنة الحالية.
عند النقر فوق “موافق“، يتم استبدال كافة قيم الخطأ بهذه القيمة الجديدة.
لأن جميع الخلايا في العمودين الأيمن تحتوي على نفس أنواع البيانات دون أي أخطاء، يمكنك إنشاء عمود جديد لحساب إجمالي عدد سنوات اللعب لكل لاعب.
هنا، نقرتُ على “عمود مخصص” في علامة تبويب “إضافة عمود”، وغيّرت اسم العمود إلى “سنوات اللعب”، واستخدمتُ قائمة الأعمدة في قائمة “الأعمدة المتاحة” لإجراء عملية طرح بسيطة.
وبعد ذلك، عندما قمت بالنقر فوق “موافق”، تمت إضافة هذا العمود الجديد – الخالي من أي أخطاء – إلى الجانب الأيمن من الاستعلام.
عند الانتهاء، انقر فوق أيقونة “إغلاق وتحميل” في علامة التبويب الصفحة الرئيسية في محرر Power Query لإرسال الجدول إلى جدول بيانات جديد في مصنف Excel الخاص بك.
3. إلغاء محورية البيانات للمساعدة في التحليل
عند إنشاء مجموعة بيانات جديدة في Microsoft Excel، أتبع، قدر الإمكان، مبدأ حقل السجل، حيث:
- يحتوي كل صف على مجموعة من أنواع البيانات ذات الصلة ولكن المختلفة (تُعرف أيضًا باسم السجل)، و
- يحتوي كل عمود على نوع بيانات فريد واحد (يُعرف أيضًا باسم الحقل) يتعلق بكل سجل.
في هذا المثال البسيط، كل دولة هي سجل، وقاراتها، وعدد سكانها، وعملاتها هي حقول.
يُهيئ استخدام هذا التنسيق البيانات لمزيد من التحليل، حيث يمكنك بسهولة تصفية البيانات وفرزها، والإشارة إلى عناوين الأعمدة في الصيغ، وتصور الإحصاءات.
قارن الجدول في المثال أعلاه بلقطة الشاشة التالية، حيث يحتوي كل عمود (حقل) على أنواع بيانات متطابقة.
نتيجةً لذلك، يستحيل تصفية البيانات حسب السنة، ولا يمكنك بسهولة تحديد السنة الأكثر ربحية لكل شركة. لإجراء هذه التحليلات، ستحتاج إلى إلغاء محور (أو تسطيح) البيانات، والذي يتضمن تحويل البيانات من جدول عريض إلى جدول طويل عن طريق تحويل كل عمود سنة إلى صف. بمعنى آخر، يجب تخزين جميع القيم المالية في حقل واحد.
أولًا، حدد أي خلية في البيانات، وفي علامة التبويب “البيانات” على الشريط، انقر على “من جدول/نطاق”.
ملاحظة
في هذه المرحلة، إذا لم تكن بياناتك مُنسّقة كجدول Excel، فسيظهر لك مربع حوار يُطالبك بإصلاح هذه المشكلة. هذا ليتمكن مُحرر Power Query من قراءة بياناتك بسهولة أكبر.
بعد ذلك، في مُحرر Power Query، حدد عناوين جميع الأعمدة التي تُريد إلغاء محورها. في هذا المثال، ستحتاج إلى النقر على عنوان “2020”، ثم الضغط على مفتاح Shift، ثم النقر على عنوان “2024”.
الآن، في علامة التبويب “تحويل” على الشريط، قم بتوسيع القائمة المنسدلة “إلغاء محور الأعمدة”، وانقر فوق “إلغاء محور الأعمدة المحددة فقط”.
الآن، كل عمود هو حقل منفصل (الشركة والسنة والربح)، وكل صف هو سجل مفصل للشركة.
أخيرًا، انقر نقرًا مزدوجًا فوق رؤوس الأعمدة لإعادة تسميتها بحيث تتطابق مع الحقل الذي تمثله، وانقر فوق أيقونات تنسيق الأرقام للتأكد من أن كل عمود يحتوي على نوع البيانات الصحيح.
الآن، عندما تنقر فوق “إغلاق وتحميل” في علامة التبويب الصفحة الرئيسية على الشريط، يتم إرسال الجدول غير المحوري إلى جدول بيانات جديد في مصنف Excel الخاص بك.
ونتيجة لذلك، يمكنك إجراء تحليلات للبيانات كانت مستحيلة قبل تسوية الجدول، مثل تصفية البيانات حسب السنة والشركة أو فرز أرباح الشركة بترتيب تنازلي.
ملاحظة
بعد إضافة المزيد من البيانات إلى الجدول الأصلي، مثل جدول آخر من الأرقام السنوية، انتقل إلى الجدول الذي أنشأته Power Query سابقًا، وفي علامة تبويب “الاستعلام”، انقر على “تحديث”. سيضيف Excel البيانات الجديدة إلى الجدول الناتج بتنسيقها غير المحوري، مما يوفر عليك الحاجة إلى العودة إلى محرر Power Query في كل مرة.
4. املأ الخلايا الفارغة بناءً على الخلية أعلاه (أو أدناه):
كما ذكرتُ سابقًا، قد تؤدي الخلايا الفارغة في مجموعة البيانات إلى مشاكل عند فرز البيانات وتصفيتها أو استخدام صيغ تشير إلى عناوين الأعمدة، لذا يُنصح بملئها.
في هذا المثال، يُخصص رقم فريق للاعب الأول فقط في كل فريق في العمود “أ”، لذا إذا أعدت ترتيب البيانات، فلن تتمكن من رؤية أي لاعب ينتمي إلى أي فريق. كما أنه لا يمكنك حاليًا تصفية البيانات حسب رقم الفريق.
يُفضّل أن تحتوي الخلايا من A3 إلى A5 على الرقم 1، والخلايا من A7 إلى A9 على الرقم 2، وهكذا. كتابة هذه الأرقام يدويًا تستغرق وقتًا طويلاً، خاصةً إذا كانت مجموعة البيانات، كما في هذا المثال، تحتوي على صفوف كثيرة. استخدم Power Query لتعبئة الخلايا الفارغة في ثوانٍ.
أولًا، بعد تحديد أي خلية في البيانات، انقر على “من جدول/نطاق” في علامة التبويب “البيانات” على الشريط.
عند فتح محرر Power Query، لاحظ أن الخلايا الفارغة في العمود المعني تحتوي على كلمة “null”. لإصلاح ذلك، حدد العمود بالنقر على عنوانه، ثم في علامة التبويب “تحويل”، انقر على “تعبئة”.
بعد ذلك، اختر ما إذا كنت تريد ملء البيانات تنازليًا أم تصاعديًا. يؤدي النقر على “أسفل” إلى العثور على خلية في النطاق المحدد تحتوي على قيمة، ويملأ أي خلايا فارغة أسفلها بالقيمة نفسها. أما النقر على “أعلى” فيؤدي إلى ملء الخلايا الفارغة فوق الخلية التي تحتوي على قيمة. في هذه الحالة، بما أن الصف الأول فقط من كل مجموعة يحتوي على رقم، فيجب ملؤه تنازليًا.
الآن، تم تعيين رقم فريق لكل لاعب بشكل صحيح، لذا يمكنك النقر فوق “إغلاق وتحميل” في علامة التبويب الصفحة الرئيسية لإرسال الاستعلام إلى ورقة عمل جديدة.
بفضل هذا التغيير السريع ولكن المهم، يمكنك فرز وتصفية مجموعة البيانات، مع العلم أنك لن تفقد المسار الخاص باللاعب الموجود في كل فريق.
إذا كان لديك عدة جداول في أوراق عمل Excel منفصلة، بشرط أن يكون لها نفس رؤوس الأعمدة، يمكنك استخدام Power Query لتكديس البيانات في جدول واحد. يتضمن ذلك خطوة إضافية (ولكنها بسيطة) لإنشاء روابط بيانات بين كل جدول، قبل دمج الاستعلامات في محرر Power Query.
استخدام Power Query بشكل فعّال لا يتطلب معرفة عميقة بالبرمجة، بل يكفي إتقان بعض الأوامر الأساسية التي تُحدث فرقًا كبيرًا في طريقة التعامل مع البيانات. الأوامر التي عرضناها تساعدك على التخلص من البيانات الفوضوية، تحويل الجداول المعقدة إلى شكل منظم، وتحقيق نتائج دقيقة بجهد أقل. بمجرد تجربتها، ستدرك كيف تُحوّل Power Query تجربة العمل في Excel من مجرّد جداول إلى بيئة مرنة وذكية لمعالجة البيانات.
