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

() translation by (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، وستجد أنها تحتوي على أرقام الطلبات، بالإضافة إلى حقول للشهر، المنتجات المطلوبة و ما إذا كان المنتج عضوياً أو لا.

اضغط على ورقة 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 إلى صيغ صديقة للويب وبالعكس.