Unlimited Powerpoint templates, graphics, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Business
  2. Microsoft Excel
Business

كيفية تحويل الأعمدة إلى صفوف في برنامج الإكسل من خلال أداة البور كويري(Power Query)

by
Difficulty:AdvancedLength:MediumLanguages:

Arabic (العربية/عربي) translation by Baraeh Al-Aydi (you can also view the original English article)

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

هنا يأتي دور أداة الـ Power Query، حيث تسمى أيضاَ بـ"جلب البيانات وتحويلها"، توجد داخل الاكسل وتقوم بأتمتة عملية تنظيف البيانات.

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

لماذا يتم تحويل الأعمدة إلى صفوف؟

قبل أن نبدأ، قد تتساءل لماذا من المفيد جداً أن تأخذ البيانات التي تم تنظيمها في العديد من الأعمدة وتحويلها إلى صفوف.

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

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

Converting columns to Rows in Microsoft Excel
تحويل الأعمدة إلى صفوف في ميكروسوفت إكسل.

أيضا، إذا كنت من مستخدمي جدول الـ Pivot، من المهم جدا أن تكون البيانات الخاصة بك في صفوف بدلا من الأعمدة عند استخدامك لإنشاء جدول الـ Pivot. البيانات في الأعمدة ببساطة لا تغذي جداول الـ Pivot بشكل صحيح.

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

كيفية تحويل الأعمدة بسرعة إلى صفوف في إكسل (شاهد وتعلم)

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

دعونا أيضا نقرأ التعليمات لمعرفة المزيد من البيانات عن Power Query و "unpivoting" في إكسل.

تعرف على Power Query في إكسل

Power Query، الذي يطلق عليه رسميا "الحصول على البيانات وتحويلها" في إكسل 2016، هو أداة ذات وظيفتين رئيسيتين:

  1. يمكنك استخدامه للحصول على البيانات، مثل الإشارة إلى ملفات إكسل أخرى، وقواعد البيانات، وحتى صفحات الويب لسحب البيانات إلى أسفل وإلى جدول بيانات.
  2. يمكن أن تساعدك على تحويل البيانات، مما يعني إعادة ترتيبه بسرعة، وإضافة المزيد من الأعمدة، أو دمج مجموعات أخرى من البيانات معها.
Power Query Window in Microsoft Excel
عند إرسال البيانات إلى Power Query للعمل، فإنه يفتح في نافذة جديدة أعلى إكسل حيث يمكنك تطبيق تحويلات البيانات.

Power Query أداة عميقة وقوية تعيش داخل إكسل. بدلا من إعادة ترتيب البيانات يدويا وتكرار العملية، يتيح لك Power Query إنشاء مجموعة من الخطوات التي يمكنك تطبيقها على بياناتك بشكل متكرر.

تثبيت Power Query

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

لتثبيت Power Query لـ إكسل على الويندوز (مطلوب فقط لبرنامج إكسل 2013 وحتى إكسل 2010، حيث أن هذه الميزة مضمنة في إكسل 2016) انتقل إلى موقع ميكروسوفت على الويب لتنزيل وتثبيت الإضافة Power Query.

إرسال البيانات إلى Power Query

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

بالنسبة إلى البيانات غير المؤهلة(unpivot) في إكسل، سيلزمك أولا تحويل بيانات إكسل إلى جدول إذا لم يكن في هذا التنسيق بالفعل. يمكنك تسليم جدول البيانات إلى Power Query  للعمل مع بياناتك.

إذا كنت ترغب في المتابعة معي في هذا الدرس التعليمي، فقد جعلت عينة البيانات متاحة في ملف الإكسل هذا مجانا.

التحويل إلى جدول

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

Convert Data to Table format in Excel
لتحويل البيانات المسطحة إلى جدول، اختر الزر تنسيق كجدول على شريط إكسل.

التمرير إلى Power Query

بعد أن تصبح بياناتك بتنسيق جدول، انتقل إلى علامة التبويب بيانات على شريط إكسل، وانقر على الزر من الجدول لإرسال بيانات الجدول إلى Power Query لتحويل بياناتك.

Data from Excel Table
انقر على بيانات > من الجدول لإرسال البيانات من طريقة عرض إكسل القياسية، إلى Power Query.

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

بيانات Unpivot

عند هذه النقطة، يجب أن تشاهد شيئا مماثلا للقطات الشاشة أدناه. هذه هي النافذة التي تحمل الأدوات التي تحتاجها إلى unpivot.

لإلغاء تنشيط البيانات في إكسل، حدد كل الأعمدة التي تريد إلغاء تنشيطها. في الأساس، باور كيري ستحول كل هذه الأعمدة إلى صفوف تلقائياً. For the sample data here, I've highlighted each of the columns for the project types:

Unpivot Columns in Microsoft Excel
حدد جميع الأعمدة التي تريد إلغاء تنشيطها في الصفوف، ثم انقر على Unpivot Columns مباشرة أعلى بياناتك.

بعد النقر على Unpivot Columns، سيقوم إكسل بتحويل البيانات العمودية إلى صفوف. كل صف هو نفسه السجل، بكل سهولة يمكنك الدخول على جدول Pivot أو العمل مع في ورقة البيانات الخاصة بك لكل سجل.

Microsoft Excel spreadsheet unpivoted data
تظهر البيانات الملغي تنشيطها في صفوف بدلاً من الأعمدة.

من خلال هذه الميزة تشعر وكأنه سحر أو شيء خارق. والآن، دعونا نرسل البيانات أكثر من مرة أخرى إلى Microsoft Excel للعمل عليها.

أغلق وحمّل

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

Unpivoted data with close and load
بعد إغلاق وتحميل طلب البحث، سيقوم إكسل بوضع البيانات unpivoted في ورقة جديدة وعرض الاستعلام الذي أنشأناه على الجانب الأيمن فقط.

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

قم بتحديث الاستعلام

إذا، ماذا لو تغيرت بياناتك الأصلية؟ إذا كان لديك المزيد من الصفوف المضافة إلى البيانات الأصلية، فلست مضطرا إلى تكرار هذه العملية بالكامل من أجل إلغاء تنشيط هذه الصفوف.

بدلا من إعادة إنشاء الاستعلام من الصفر، تحتاج إلى استخدام الخيار تحديث البيانات. في لقطة الشاشة أدناه، سأضيف صفا جديدا تماما لعميل جديد تماما إلى مصدر بياناتي الأصلية:

Added new client data into Excel spreadsheet
على سبيل المثال، لقد أضفتُ عميلا جديدا على الصف المميز إلى مجموعة البيانات الأصلية.

الآن، لنعود إلى علامة التبويب حيث يتم تحميل بياناتنا unpivoted. عندما نضغط داخل الجدول، سترى قائمة طلبات الإستعلام المصنف مفتوحة بجوار بياناتك. اضغط على الزر تحديث (الجانب الأيمن من القائمة) لتحديث الاستعلام.

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

Updated data in Excel spreadsheet
انقر على تحديث بجوار اسم الاستعلام في الشريط الجانبي لإضافة الصفوف الجديدة إلى مجموعة البيانات unpivoted .

هذا واحد من الميزات المفضلة لدي بخصوص باور كيري. أضفنا البيانات إلى المصدر الأصلي، ولكن بنقرة واحدة قد تم تحديث وإجراء unpivoted للبيانات الجديدة أيضاً.

حافظ على تعلم المزيد حول استخدام ميكروسوفت اكسل

تعلمتَ في هذا الدرس التعليمي، كيف أنه من السهل تحويل الأعمدة إلى صفوف في ميكروسوفت إكسل. Power Query أداة مرنة وقوية لإمساك وإعادة ترتيب البيانات الخاصة بك بسرعة مذهلة. تابع تعلم المزيد من تقنيات ميكروسوفت إكسل في هذه الدروس التعليمية لإنفاتو توتس +:

كيف تستخدم باور كيري أو أدوات أخرى لإجراء unpivot لبياناتك؟ إذا كان لديك نصيحة مفضلة للمشاركة، فتأكد من تسجيل الدخول بتعليق أدناه.

Advertisement
Advertisement
Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.