Unlimited WordPress themes, graphics, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Computer Skills
  2. Microsoft Excel
Computers

PivotTables متقدمة: دمج البيانات من أوراق متعددة

by
Difficulty:AdvancedLength:LongLanguages:

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

عندما ترغب بإنشاء PivotTable، ما الذي يجدر بك القيام به لو كانت بياناتك موجودةً في أوراق عملٍ مختلفة؟ إذا كنت تستعمل Excel 2013، فإن هنالك طريقة سهلة وبسيطة للقيام بذلك. يوجد طريقة تدعى نموذج البيانات (Data Model)، والتي تقوم باستعمال العلاقات بين البيانات بنفس الطريقة التي تقوم بها قاعدة البيانات بذلك.

في هذا الدرس، سأريك كل ما تحتاج إليه لإنشاء PivotTable في Excel 2013 عندما تكون البيانات موجودة في أوراق متعددة، باستعمال نموذج البيانات.

تسجيل للشاشة

إذا وددت متابعة الدرس معنا باستعمال ملف Excel الخاص بك، فإن بإمكانك القيام بذلك. أو إذا كنت تفضل، قم بتحميل ملف المضمن في هذا الدرس، والذي يحتوي على مصنف باسم Pivot Consolidate.xlsx.

الاطلاع على البيانات

يحتوي هذا المصنف على ثلاثة أوراق عمل: Customer Info (معلومات الزبون)، Order Info (معلومات الطلب)، وPayment Info (معلومات عملية الدفع).

اضغط على ورقة Customer Info، وستجد أنها تحتوي على أرقام الطلبات واسم وحالة كل الزبائن.

Customer Info sheet
ورقة معلومات الزبون

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

Order Info sheet
ورقة معلومات الطلب

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

Payment Info sheet
ورقة معلومات عملية الدفع

بربطنا لكل هذه الأوراق في داخل لوحة مهام PivotTable، فإننا سنتمكن من اختيار البيانات من كل ورقة من هذه الأوراق.  وبما أن أرقام الطلبات موجودة في الورقات الثلاثة، فإنها ستصبح بمثابة نقاطٍ للربط.  هذا ما يسمى مفتاح رئيسي (Primary key) في قاعدة البيانات. لاحظ أنه ليس من الضروري أن يكون لديك مفتاحٌ رئيسي دائماً، لكنه سيقلل من احتمال حدوث خطأ.

قم بإنشاء جداول ذات اسماء

قبل إنشاء PivotTable، دعنا ننشئ جدول لكلٍ من هذه الأوراق.

عد إلى Customer Table، واضغط على أي مكان في المنطقة المخصصة للبيانات.  اذهب إلى تبويبة إدراج (Insert) في الشريط العلوي، ومن ثم اضغط على أيقونة جدول (Table).

creating a table from existing data
تحويل البيانات الموجودة في ورقة العمل عن طريق إدراج ثم جدول

سيحدد مربع إنشاء جدول (Create Table) المنطقة الخاصة بالجدول بشكلٍ دقيق. مربع التحقق في الأسفل يجب أن يعرف أيضاً أن الصف الأول من الجدول خاص بالرؤوس (headers). (إذا لم يقم بذلك، فقم باختيار هذا الخيار بنفسك.)

Create Table dialog box
يجب أن يحزر مربع إنشاء جدول منطقة البيانات بالشكل الصحيح

اضغط على موافق، والآن سيكون لديك جدول مع تظليل مخطط وأزرار للفرز. يمكنك أن تنقر في داخله لكي تلغي تحديده، في حال كنت تريد أن تراه بشكلٍ أفضل (لكن لا تقم بالنقر خارج الجدول). سيظهر الشريط أيضاً تبويبة تصميم (Design) خاصة بالجدول. على يمين الشريط (يسار في النسخة الإنكليزية)، سيظهر مربع اسم الجدول (Table Name) اسماً مؤقتاً للجدول وهو الجدول 1 (Table 1). قم بحذفه، وسم الجدول Customer_Info (معلومات_الزبون) (واستعمل الشريط السفلي (underscore) بدلاً من المسافة (space)). بعد ذلك اضغط على زر إدخال (Enter).

applying a name to a table
تغيير اسم الجدول

قم بتكرار هذه العملية مع ورقتي Order Info وPayment Info. قم بتسمية هذه الجداول بـ Order_Info (معلومات الطلب) وPayment_Info (معلومات عملية الدفع)، على التوالي.

أصبحنا جاهزين الآن لإدراج PivotTable.

إدراج PivotTable

في ورقة Payment Info، تأكد من أن السهم موجود في مكانٍ ما ضمن الجدول.  عد إلى تبويبة إدراج (Insert) في الشريط العلوي، واضغط على أيقونة PivotTable (إنها الأيقونة الأولى).

inserting the PivotTable
بعد أن أصبح المؤشر داخل واحدٍ من الجداول، قم باختيار إدراج ثم PivotTable

مربع الحوار الذي سيظهر لك يجب ان يحدد بدقة الجدول وأن يختار خيار وضع الـ PivotTable في ورقة عملٍ جديدة.  في الأسفل، اضغط على مربع تحقق إضافة هذه البيانات إلى نموذج البيانات (Add this data to the Data Model).  ثم اضغط على موافق.

add data to the Data Model
إضافة البيانات إلى نموذج البيانات سيمكن الروابط من العمل

سيكون لديك الآن PivotTable في ورقة عمل جديدة، وستظهر لوحة على الجانب الأيسر من الشاشة، وستظهر في الشريط العلوي تبويبة تحليل (Analyze).

سيظهر في اللوحة اليسارية الجدول والحقول الموجودة فقط في الورقة الحالية، لذا اضغط على الكل (All) لرؤية كل الجداول التي قمت بإنشائها. قبل أن نتمكن من استعمالهم، علينا أن نربطهم ببعضهم، مما يعني إنشاء علاقات. اضغط على زر علاقات (Relationships) في الشريط العلوي.

إعداد العلاقات الخاصة بالجداول

النقر على هذا الزر سيظهر لك مربع إدارة العلاقات (Manage Relationships). اضغط على زر جديدة (New)، وهذا سيظهر لك مربع إنشاء علاقة (Create Relationship). سنقوم بإنشاء علاقتيني باستخدام حقل Order # للربط.

من القوائم المنسدلة، اختر Payment_Info كجدول، وبجانبه، اختر Order # من القائمة المنسدلة الخاصة بالعمود (Column).  في الصف الثاني، اختر Customer_Info من القائمة المنسدلة الخاصة بالجدول المرتبط (Related Table)، وبجانبه، اختر Order # من القائمة المنسدلة الخاصة بالعمود (Column).

creating a table relationship
هنالك ثلاثة جداول، لذا قم بإنشاء علاقتين

هذا يعني أن الجداول Payment_Info وCustomer_Info مرتبطان ببعضهما عند وجود أرقام طلبات متطابقة.

اضغط على موافق، وسنجد أن العلاقة معروضة في مربع إدارة العلاقات.

قم بتكرار هذه العملية لتصميم علاقة تربط بين Payment_Info وOrder_Info، باستعمال حقل Order #. يجب أن يبدو مربع إدارة العلاقات لديك بهذا الشكل:

list of all relationships
سيُظهِر مربع إدراة العلاقات العلاقات التي قمت بإنشائها

لاحظ أنه من غير الضروري إنشاء علاقة بين جداول Order_Info وCustomer_Info لأنها مرتبطان تلقائياً عن طريق جدول Payment_Info.

اضغط على إغلاق (close) في أسفل المربع. يمكننا الآن أخيراً أن نسحب الحقول إلى PivotTable. يمكننا أن ندرج أخيراً الحقول في الـ PivotTable.

إدراج حقول في الـ PivotTable

قم بسحب الحقول إلى نواحي الـ PivotTable كما يلي:  قم بسحب الحقول إلى نواحي الـ PivotTable كما يلي:

  • State وMonth إلى الصفوف
  • Product إلى الأعمدة
  • $ Sale إلى القيم (Values)
  • Status إلى عوامل التصفية (Filters)
final PivotTable
قم بسحب الحقول من كل واحدٍ من الجداول الثلاثة إلى الـ PivotTable

الآن يمكنك استعماله وتعديله مثل أي PivotTable آخر.

خاتمة

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

إذا كنت تبحث عن طرق جيدة لتقديم بياناتك، فإن متجر Envato يمتلك مجموعةً جيدة من قوالب PowerPoint وExcel، بالإضافة إلى تطبيقات ونصوص برمجية لتحويل بيانات Excel إلى صيغ صديقة للويب وبالعكس.

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.