البرنامج التعليمي: تحليل بيانات PivotTable باستخدام نموذج بيانات في Excel 2013

بأقل من ساعة، يمكنك إنشاء تقرير PivotTable في Excel يدمج بيانات من جداول متعددة. ينقلك الجزء الأول من هذا البرنامج التعليمي عبر عملية استيراد البيانات واستكشافها. وفي النصف الثاني، ستستخدم الوظيفة الإضافية Power Pivot لتحسين نموذج البيانات الذي يقف خلف التقرير، وستتعرّف على كيفية إضافة العمليات الحسابية والتسلسلات الهيكلية والتحسينات لتقارير Power View.

فلنبدأ باستيراد بعض البيانات.

  1. قم بتنزيل البيانات النموذجية (ContosoV2) لهذا البرنامج التعليمي. للحصول على مزيد من التفاصيل، راجع الحصول على بيانات نموذجية للبرامج التعليمية لـ DAX ونموذج البيانات. استخرج ملفات البيانات واحفظها في موقع يمكن الوصول إليه بسهولة، مثل التنزيلات أو المستندات.

  2. في Excel، افتح مصنفاً جديداً فارغاً.

  3. انقر فوق بيانات >‏ إحضار بيانات خارجية >‏ من Access.

  4. انتقل إلى المجلد الذي يحتوي على ملفات البيانات النموذجية، وحدد ContosoSales.

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

    مربع الحوار تحديد جدول

  6. في مربع الحوار "تحديد جدول"، حدد خانة الاختيار تمكين تحديد جداول متعددة.

  7. اختر كل الجداول، وانقر فوق موافق.

  8. في "استيراد البيانات"، انقر فوق تقرير PivotTable، وانقر فوق موافق.

    ملاحظات: 

    • ربما لم تدرك بعد أنك أنشأت نموذج بيانات جديداً. والنموذج عبارة عن طبق تكامل بيانات يتم إنشاؤها تلقائياً عندما تستورد عدة جداول أو تعمل معها في الوقت نفسه في تقرير PivotTable نفسه.

    • يكون التقرير شفافاً عادةً في Excel، ولكن يمكنك عرضه وتعديله مباشرةً باستخدام الوظيفة الإضافية Power Pivot . في Excel، يكون وجود نموذج البيانات واضحاً عندما ترى مجموعة جداول في قائمة حقول PivotTable. ثمة عدة طرق لإنشاء نموذج. للحصول على مزيد من التفاصيل، راجع إنشاء نموذج بيانات في Excel لمزيد من التفاصيل.

استكشاف البيانات باستخدام PivotTable

يمكنك استكشاف البيانات بسهولة عندما تسحب الحقول إلى نواحي القيم والأعمدة والصفوف على قائمة حقول PivotTable.

  1. في قائمة الحقول، قم بالتمرير للأسفل حتى تعثر على جدول FactSales.

  2. انقر فوق SalesAmount. بما أن هذه البيانات رقمية، يضع Excel تلقائياً SalesAmount في ناحية القيم.

  3. في DimDate، اسحب CalendarYear إلى الأعمدة.

  4. في DimProductSubcategory، اسحب ProductSubcategoryName إلى الصفوف.

  5. في DimProduct، اسحب BrandName إلى الصفوف، مع وضعه تحت الفئة الفرعية.

من المفترض أن يبدو جدول PivotTable مماثلاً للشاشة التالية.

جدول PivotTable يعرض بيانات نموذجية

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

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

إضافة المزيد من الجداول

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

إضافة فئات المنتجات

  1. في المصنف، افتح ورقة جديدة. ستستخدمها لتخزين بيانات إضافية.

  2. انقر فوق بيانات >‏ إحضار بيانات خارجية >‏ من Access.

  3. انتقل إلى المجلد الذي يحتوي على ملفات البيانات النموذجية، وحدد ProductCategories. انقر فوق فتح.

  4. في "استيراد البيانات"، حدد جدول، وانقر فوق موافق.

إضافة بيانات جغرافية

  1. أدرج ورقة أخرى.

  2. من ملفات البيانات النموذجية، افتح Geography.xlsx، وضع المؤشر في A1، ثم اضغط على Ctrl-Shift-End لتحديد كل البيانات.

  3. انسخ البيانات إلى الحافظة.

  4. الصق البيانات في الورقة الفارغة التي أضفتها.

  5. انقر فوق تنسيق كجدول، باختيار أي نمط. يتيح لك تنسيق البيانات كجدول تسمية الجدول، الأمر الذي سيكون مساعداً لك عندما تحدد العلاقات في وقت لاحق.

  6. في "تنسيق كجدول" تأكد من تحديد يحتوي الجدول على رؤوس. انقر فوق موافق.

  7. اكتب الاسم جغرافيا للجدول. في أدوات الجدول >‏ تصميم، اكتب الاسم جغرافيا في اسم الجدول.

  8. أغلق الملف Geography.xlsx لمسحه من مساحة العمل.

إضافة بيانات المخزن

  • كرر الخطوات السابقة للملف Stores.xlsx، مع لصق محتوياته في ورقة فارغة. اكتب الاسم متاجر للجدول.

من المفترض أن يكون لديك الآن أربع أوراق. تحتوي الورقة1 على جدول PivotTable، والورقة2 على ProductCategories، والورقة3 على Geography، والورقة4 على Stores. لأنك أخذت وقتاً كافياً لتسمية كل جدول، فستكون الخطوة التالية، إنشاء العلاقات، أكثر بساطة بكثير.

استخدام الحقول من جداول مستوردة حديثاً

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

  1. في أعلى حقول PivotTable، انقر فوق الكل لعرض قائمة كاملة بالجداول المتوفرة.

  2. قم بالتمرير لأسفل القائمة. هذا هو المكان الذي ستعثر فيه على الجداول الجديدة التي أضفتها للتوّ.

  3. وسّع المتاجر.

  4. اسحب StoreName إلى ناحية عوامل التصفية.

  5. لاحظ أن Excel يطالبك بإنشاء علاقة. يحدث هذا الإعلام لأنك استخدمت حقولاً من جدول غير مرتبط بالنموذج.

  6. انقر فوق إنشاء لفتح مربع الحوار "إنشاء علاقة".

  7. في الجدول، اختر FactSales. في البيانات النموذجية التي تستخدمها، يحتوي FactSales على معلومات مفصّلة حول المبيعات والتكاليف في شركة Contoso، بالإضافة إلى مفاتيح لجداول أخرى، بما فيها رموز المتجر الموجودة أيضاً في الملف Stores.xlsx الذي استوردته في الخطوة السابقة.

  8. في العمود (خارجي)، اختر StoreKey.

  9. في الجدول المرتبط، اختر متاجر.

  10. في العمود المرتبط (أساسي)، اختر StoreKey.

  11. انقر فوق موافق.

يعمل Excel في الخلفية على إنشاء نموذج بيانات يمكن استخدامه في المصنف بكامله في أي عدد من تقارير PivotTables أو PivotCharts أو Power View. تُعد علاقات الجدول التي تحدد مسارات التنقل والحساب المستخدمة في تقرير PivotTable أساسية بالنسبة إلى هذا النموذج. في الخطوة التالية، ستُنشئ العلاقات بطريقة يدوية لتوصيل البيانات التي استوردتها للتوّ.

إضافة علاقات

يمكنك إنشاء علاقات الجدول بطريقة منهجية لكل الجداول الجديدة التي تستوردها. إذا كنت تشارك المصنف مع زملائك، فإن وجود علاقات معرّفة مسبقاً سيكون أمراً مستحسناً بالنسبة لهم إذا لم يكونوا ملمين بالبيانات مثلك أنت.

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

لا يدعم المستعرض الفيديو. ثبّت Microsoft Silverlight أو Adobe Flash Player أو Internet Explorer 9.

الربط بين ProductSubcategory وProductCategory

  1. في Excel، انقر فوق فوق بيانات >‏ علاقات >‏ جديد.

  2. في الجدول، اختر DimProductSubcategory.

  3. في العمود (خارجي)، اختر ProductCategoryKey.

  4. في الجدول المرتبط، اختر Table_ProductCategory.accdb.

  5. في العمود المرتبط (أساسي)، اختر ProductCategoryKey.

  6. انقر فوق موافق.

  7. أغلق مربع الحوار إدارة العلاقات.

إضافة فئات إلى PivotTable

على الرغم من تحديث نموذج البيانات بحيث يتضمّن جداول وعلاقات إضافية، فإن جدول PivotTable لا يستخدمها بعد. في هذه المهمة، ستضيف ProductCategory إلى قائمة حقول PivotTable.

  1. في حقول PivotTable، انقر فوق الكل لإظهار الجداول الموجودة في نموذج البيانات.

  2. قم بالتمرير لأسفل القائمة.

  3. في ناحية الصفوف، قم بإزالة BrandName.

  4. وسّع Table_DimProductCategories.accdb.

  5. اسحب ProductCategoryName إلى ناحية الصفوف، مع وضعه أعلى ProductSubcategory.

  6. في حقول PivotTable، انقر فوق نشط للتأكد من أن الجداول التي استخدمتها للتوّ يتم استخدامها الآن بطريقة نشطة في جدول PivotTable.

نقطة تحقق: مراجعة الأمور التي تعلّمتها

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

على الرغم من أن جدول PivotTable يعمل بطريقة جيدة، إلا أنك لاحظت على الأرجح أن ثمة أمور عديدة يمكن تحسينها. يبدو أن قائمة حقول PivotTable تتضمّن جداول (DimEntity) وأعمدة (ETLLoadID) إضافية لا علاقة لها بشركة Contoso. كما أننا لم نقم بعد بتضمين بيانات الجغرافيا.

الخطوة التالية: عرض النموذج وتوسيعه بواسطة Power Pivot

في سلسلة المهام التالية، ستستخدم الوظيفة الإضافية Microsoft Office Power Pivot في Microsoft Excel 2013 في Excel 2013 لتوسيع النموذج. سيتبيّن لك أنك تنشئ العلاقات بسهولة أكثر باستخدام طريقة عرض الرسم التخطيطي التي توفرها الوظيفة الإضافية. كما ستستخدم الوظيفة الإضافية لإنشاء عمليات حسابية وتسلسلات هيكلية، وإخفاء عناصر من المفترض ألا تظهر في قائمة الحقول، وتحسين البيانات لمزيد من التقارير.

ملاحظة:  تتوفر الوظيفة الإضافية Power Pivot في Microsoft Excel 2013 في Office Professional Plus. راجع الوظيفة الإضافية Power Pivot في Microsoft Excel 2013 لمزيد من المعلومات.

أضف Power Pivot إلى شريط Excel بتمكين الوظيفة الإضافية Power Pivot.

  1. انتقل إلى ملف ‏> خيارات ‏> الوظائف الإضافية.

  2. في المربع إدارة، انقر فوق وظائف COM الإضافية، ثم فوق انتقال.

  1. حدد المربع Microsoft Office Power Pivot في Microsoft Excel 2013، ثم انقر فوق موافق.

تتوفر الآن علامة تبويب Power Pivot على الشريط.

إضافة علاقة باستخدام طريقة عرض الرسم التخطيطي في Power Pivot

  1. في Excel، انقر فوق ورقة3 لتحويلها إلى الورقة النشطة. تحتوي الورقة3 على جدول الجغرافيا الذي استوردته في وقت سابق.

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

  3. لاحظ أن نافذة Power Pivot تعرض كل الجداول الموجودة في النموذج، بما فيها جدول الجغرافيا. انقر عبر بعض الجداول. في الوظيفة الإضافية، يمكنك عرض كل البيانات التي يتضمّنها النموذج.

  4. في نافذة Power Pivot، في المقطع "عرض"، انقر فوق طريقة عرض الرسم التخطيطي.

  5. استخدم شريط التمرير لتغيير حجم الرسم التخطيطي بحيث يمكنك رؤية كل الكائنات الموجودة في الرسم التخطيطي. لاحظ وجود جدولين غير مرتبطين بباقي الرسم التخطيطي: DimEntity والجغرافيا.

  6. انقر بزر الماوس الأيمن فوق DimEntity، ثم انقر فوق حذف. هذا الجدول عبارة عن نوع بيانات اصطناعي من قاعدة البيانات الأصلية ولا حاجة له في النموذج.

  7. كبّر الجغرافيا لرؤية كل الحقول التابعة لها. يمكنك استخدام شريط التمرير لتكبير الرسم التخطيطي للجدول.

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

  9. انقر فوق بحث.

  10. في "بحث عن بيانات التعريف"، اكتب GeographyKey.

  11. انقر فوق بحث عن التالي عدة مرات. ستلاحظ ظهور GeographyKey في جدول الجغرافيا وجدول المتاجر.

  12. غيّر موضع جدول الجغرافيا بحيث يكون إلى جانب جدول المتاجر.

  13. اسحب العمود GeographyKey في جدول المتاجر إلى العمود GeographyKey في جدول الجغرافيا. ترسم الوظيفة الإضافية Power Pivot خطاً بين العمودين يشير إلى العلاقة.

لقد تعرّفت في هذه المهمة على أسلوب جديد لإضافة الجداول وإنشاء العلاقات. وحصلت الآن على نموذج متكامل بشكل تام، حيث كل الجداول مرتبطة ومتوفرة لجدول PivotTable في ورقة1.

تلميح:  في طريقة عرض الرسم التخطيطي، يتوسّع عدد كبير من الرسوم التخطيطية للجدول، لعرض أعمدة مثل ETLLoadID وLoadDate وUpdateDate. هذه الحقول المعينة عبارة عن نوع بيانات اصطناعي من مخزن بيانات Contoso الأصلي، وقد تمت إضافتها لدعم عمليات استخراج البيانات وتحميلها. إنك لا تحتاج إلى هذه الحقول في النموذج. للتخلّص منها، يمكنك تمييز الحقل والنقر فوقه بزر الماوس الأيمن، ثم النقر فوق حذف .

إنشاء عمود محسوب

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

  1. في نافذة Power Pivot، عد إلى طريقة عرض البيانات.

  2. غيّر اسم الجدول Table_ProductCategories accdb إلى اسم آخر مألوف. ستشير إلى هذا الجدول في الخطوات التالية، وسيؤدي اختيار اسم مختصر إلى تسهيل قراءة الحسابات. انقر بزر الماوس الأيمن فوق اسم الجدول، وانقر فوق إعادة تسمية، واكتب ProductCategories، ثم اضغط على Enter.

  3. حدد جدول FactSales.

  4. انقر فوق تصميم >‏ أعمدة >‏ إضافة.

  5. في شريط الصيغة أعلى الجدول، اكتب الصيغة التالية. تساعدك ميزة الإكمال التلقائي على كتابة أسماء الأعمدة والجداول المؤهلة بالكامل، وتسرد الدالات المتوفرة. يمكنك أيضاً النقر فوق العمود، فتضيف الوظيفة الإضافية Power Pivot اسم العمود إلى الصيغة.

    ‎= [SalesAmount] - [TotalCost] - [ReturnAmount]‎

  6. بعد أن تنتهي من إنشاء الصيغة، اضغط على Enter لقبول الصيغة.

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

  7. أعد تسمية العمود بالنقر بزر الماوس الأيمن فوق CalculatedColumn1 وتحديد إعادة تسمية العمود. اكتب الربح، ثم اضغط على Enter.

  8. الآن، حدد جدول DimProduct.

  9. انقر فوق تصميم >‏ أعمدة >‏ إضافة.

  10. في شريط الصيغة أعلى الجدول، اكتب الصيغة التالية.

    ‎=‎ RELATED(ProductCategories[ProductCategoryName])‎‎

    تقوم الدالة RELATED بإرجاع قيمة من جدول مرتبط. في هذه الحالة، يتضمّن الجدول ProductCategories أسماء فئات المنتجات، التي سيكون وجودها مفيداً في جدول DimProduct عندما تُنشئ تسلسلاً هيكلياً يتضمّن معلومات الفئات. لمزيد من المعلومات حول كيفية استخدام هذه الدالة، راجع الدالة RELATED (DAX)‎.

  11. بعد أن تنتهي من إنشاء الصيغة، اضغط على Enter لقبول الصيغة.

    تتم تعبئة القيم لكل الصفوف في العمود المحسوب. إذا قمت بالتمرير للأسفل عبر الجدول، سترى أن كل صف يحتوي الآن على اسم فئة المنتج.

  12. أعد تسمية العمود بالنقر بزر الماوس الأيمن فوق CalculatedColumn1 وتحديد إعادة تسمية العمود. اكتب ProductCategory، ثم اضغط على Enter.

  13. انقر فوق تصميم >‏ أعمدة >‏ إضافة.

  14. في شريط الصيغة أعلى الجدول، اكتب الصيغة التالية، ثم اضغط على Enter لقبول الصيغة.

    ‎= RELATED(DimProductSubcategory[ProductSubcategoryName])‎

  15. أعد تسمية العمود بالنقر بزر الماوس الأيمن فوق CalculatedColumn1 وتحديد إعادة تسمية العمود. اكتب ProductSubcategory، ثم اضغط على Enter.

إنشاء تسلسل هيكلي

تتضمّن معظم النماذج بيانات هيكلية بطبيعتها. تشتمل الأمثلة الشائعة على بيانات التقويم والبيانات الجغرافية وفئات المنتجات. ويُعد إنشاء التسلسلات الهيكلية مفيداً إذ يمكنك سحب عنصر (التسلسل الهيكلي) إلى تقرير بدلاً من تجميع الحقول نفسها وترتيبها مرات عديدة.

  1. في نافذة Power Pivot، انتقل إلى طريقة عرض الرسم التخطيطي. وسّع جدول DimDate بحيث تتمكّن من رؤية كل حقوله بسهولة أكبر.

  2. اضغط باستمرار على المفتاح Ctrl وانقر فوق الأعمدة CalendarYear وCalendarQuarter وCalendarMonth (ستحتاج إلى التمرير للأسفل في الجدول).

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

  4. اكتب التواريخ كاسم التسلسل الهيكلي الجديد.

  5. أضف العمود FullDateLabel إلى التسلسل الهيكلي. انقر بزر الماوس الأيمن فوق FullDateLabel وحدد إضافة إلى التسلسل الهيكلي. اختر التاريخ. يحتوي العمود FullDateLabel على تاريخ كامل، يتضمّن السنة والشهر واليوم. تأكد من ظهور FullDateLabel كعمود أخير في التسلسل الهيكلي. بات لديك الآن تسلسل هيكلي متعدد المستويات يتضمّن السنة وربع السنة والشهر وأيام تقويم فردية.

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

  7. اكتب فئات المنتجات كاسم التسلسل الهيكلي الجديد.

  8. لإنشاء عقد تسلسل هيكلي تابعة، اسحب ProductCategory وProductSubcategory إلى التسلسل الهيكلي.

  9. انقر بزر الماوس الأيمن فوق ProductName وحدد إضافة إلى التسلسل الهيكلي. اختر فئات المنتجات.

الآن وقد تعرّفت على بعض الطرق المختلفة لإنشاء تسلسل هيكلي، فلنستخدمها في جدول PivotTable.

  1. عد إلى Excel.

  2. في الورقة1(الورقة التي تحتوي على جدول PivotTable)، قم بإزالة الحقول في ناحية الصفوف.

  3. استبدلها بالتسلسل الهيكلي الجديد فئات المنتجات في DimProduct.

  4. بطريقة مماثلة، استبدل CalendarYear في ناحية الأعمدة بالتسلسل الهيكلي التواريخ في DimDate.

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

إخفاء الأعمدة

الآن وقد أنشأت تسلسلاً هيكلياً لفئات المنتجات ووضعته في DimProduct، لم تعد بحاجة إلى DimProductCategory أو DimProductSubcategory في قائمة حقول PivotTable. ستتعرّف في هذه المهمة على كيفية إخفاء جداول وأعمدة خارجية تحتل بعض المساحة في قائمة حقول PivotTable. ومع إخفاء الجداول والأعمدة، ستحسّن تجربة إعداد التقارير دون التأثير في النموذج الذي يوفر علاقات البيانات وحساباتها.

لا يدعم المستعرض الفيديو. ثبّت Microsoft Silverlight أو Adobe Flash Player أو Internet Explorer 9.

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

  1. في Power Pivot، تأكد من تحديد طريقة عرض البيانات.

  2. في علامات التبويب في الأسفل، انقر بزر الماوس الأيمن فوق DimProductSubcategory وحدد إخفاء من أدوات العميل.

  3. كرر الإجراء لـ ProductCategories.

  4. افتح DimProduct.

  5. انقر بزر الماوس الأيمن فوق الأعمدة التالية وانقر فوق إخفاء من أدوات العميل:

    • ProductKey

    • ProductLabel

    • ProductSubcategory

  6. حدد أعمدة متجاورة متعددة. ابدأ بالعمود ClassID وتابع إلى ProductSubcategory في النهاية. انقر بزر الماوس الأيمن فوق الأعمدة لإخفائها.

  7. كرر الإجراء لجداول أخرى، مع إزالة المعرّفات أو المفاتيح أو تفاصيل أخرى لن تستخدمها في هذا التقرير.

عد إلى Excel وانتقل إلى الورقة1 مع قائمة حقول PivotTable لمشاهدة الفرق. سترى أن عدد الجداول قد انخفض وأن الجدول DimProduct يتضمّن فقط العناصر التي ستستخدمها على الأرجح عند تحليل المبيعات.

إنشاء تقرير Power View

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

  1. في Excel، انقر فوق إدراج >‏ Power View.

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

  2. في حقول Power View، انقر فوق السهم إلى جانب جدول FactSales، وانقر فوق SalesAmount.

  3. وسّع جدول الجغرافيا، وانقر فوق RegionCountryName.

  4. انقر فوق خريطة في الشريط.

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

التحسين لتقارير Power View

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

كخطوة أولى، أضف عناوين URL إلى المصنف.

  1. في Excel، افتح ورقة جديدة وانسخ تلك القيم:

ManufacturerURL

ManufacturerID

http://www.contoso.com

Contoso, LTD

http://www.adventure-works.com

Adventure Works

http://www.fabrikam.com

Fabrikam, Inc.

  1. نسّق الخلايا كجدول، ثم قم بتسمية URL الجدول.

  2. أنشئ علاقة بين عنوان URL والجدول الذي يحتوي على أسماء الشركات المصنّعة، DimProduct‏:

    1. انقر فوق بيانات >‏ علاقات. يظهر مربع الحوار "إنشاء علاقة".

    2. انقر فوق جديد.

    3. في الجدول، حدد DimProduct.

    4. في العمود، حدد الشركة المصنّعة.

    5. في الجدول المرتبط، حدد URL.

    6. في العمود المرتبط (أساسي)، اختر ManufacturerID.

لمقارنة النتائج قبل وبعد، ابدأ تقريرPower View جديداً وأضف FactSales | SalesAmount، وdimProduct | والشركة المصنّعة وURL‏ | ManufacturerURL إلى تقرير. لاحظ أن عناوين URL تظهر كنص ثابت.

يحتاج عرض عنوان URL كارتباط تشعبي نشط إلى تصنيف. لتصنيف عمود، ستستخدم Power Pivot.

  1. في Power Pivot، افتح URL.

  2. حدد ManufacturerURL.

  3. انقر فوق خيارات متقدمة >‏ خصائص التقارير >‏ فئة البيانات: غير مصنفة.

  4. انقر فوق زر السهم للأسفل.

  5. حدد URL موقع ويب.

  6. في Excel، انقر فوق إدراج >‏ Power View.

  7. في حقول Power View، حدد FactSales | SalesAmount، و dimProduct | الشركة المصنّعة وURL‏ | ManufacturerURL. الآن، ستظهر عناوين URL كارتباطات تشعبية فعلية.

تتضمّن بعض تحسينات Power View الأخرى تعريف مجموعة حقول افتراضية لكل جدول وتعيين خصائص تحدد ما إذا كانت صفوف البيانات المتكررة مجمّعة أو مدرجة بشكل مستقل. لمزيد من المعلومات، راجع تكوين مجموعة الحقول الافتراضية لتقارير Power View وتكوين خصائص سلوك الجدول لتقارير Power View.

إنشاء حقول محسوبة

في المهمة الثانية، "استكشاف البيانات باستخدام PivotTable"، نقرت فوق الحقل SalesAmount في قائمة حقول PivotTable. لأن SalesAmount عبارة عن عمود رقمي، فقد تم وضعه تلقائياً في ناحية القيم في جدول PivotTable. بعد ذلك، أصبح مجموع SalesAmount جاهزاً لحساب قيم المبيعات لأي عوامل تصفية يجب تطبيقها. في هذه الحالة، لم يتم تطبيق أي عامل تصفية أولاً، بعد ذلك تم تطبيق CalendarYear وProductSubcategoryName وBrandName.

ما قمت به بالفعل هو إنشاء حقل محسوب ضمني، مما يسهّل تحليل قيم المبيعات من جدول FactSales مقابل حقول أخرى مثل فئة المنتج والمنطقة والتواريخ. يتم إنشاء الحقول المحسوبة الضمينة بواسطة Excel عندما تسحب حقلاً إلى ناحية القيم أو عندما تنقر فوق حقل رقمي، كما فعلت عندما نقرت فوق SalesAmount. تُعد الحقول المحسوبة الضمينة صيغاً تستخدم دالات تجميع قياسية مثل SUM وCOUNT وAVERAGE، يتم إنشاؤها تلقائياً نيابة عنك.

توجد أيضاً أنواع أخرى من الحقول المحسوبة. يمكنك إنشاء الحقول المحسوبة الواضحة في Power Pivot. بخلاف الحقل المحسوب الضمني، الذي يمكن استخدامه فقط في جدول PivotTable الذي تم إنشاؤه فيه، يمكن استخدام الحقول المحسوبة الواضحة في أي جدول PivotTable في المصنف، أو بواسطة أي تقرير يستخدم نموذج البيانات كمصدر بيانات. من خلال الحقول المحسوبة الواضحة التي يتم إنشاؤها في Power Pivot، يمكنك استخدام ميزة الجمع التلقائي لإنشاء الحقول المحسوبة تلقائياً باستخدام تجميعات قياسية، أو يمكنك إنشاء تجميعات خاصة بك باستخدام صيغة تم إنشاؤها باستخدام تعبيرات تحليل البيانات (DAX)‏.

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

يمكن إنشاء الحقول المحسوبة في Power Pivot بسهولة عند استخدام جمع تلقائي.

  1. في جدول FactSales، انقر فوق عمود الربح.

  2. انقر فوق حسابات >‏ جمع تلقائي. لاحظ أنه قد تم إنشاء حقل جديد محسوب مسمى مجموع الأرباح بشكل تلقائي في الخلية في منطقة الحساب مباشرة أسفل عمود الربح.

  3. في Excel، في الورقة1، في قائمة الحقول، في FactSales، انقر فوق مجموع الأرباح.

هذا كل شيء! هذا كل ما تتطلبه عملية إنشاء حقل محسوب باستخدام تجميع قياسي في Power Pivot. كما رأيت، لقد أنشأت الحقل المحسوب مجموع الأرباح في غضون دقائق قليلة، وأضفته إلى جدول PivotTable، مما سهّل تحليل الأرباح وفقاً لعوامل التصفية المطبّقة. في هذه الحالة، سترى مجموع الأرباح وقد تمت تصفيته حسب التسلسلات الهيكلية لفئة المنتج والتواريخ.

ولكن، ماذا لو احتجت إلى إجراء تحليل أكثر تفصيلاً، مثل عدد عمليات البيع لقناة أو منتج أو فئة معينة؟ لهذا الغرض، ستحتاج إلى إنشاء حقل محسوب آخر يقوم بتعداد عدد الصفوف، صف واحد لكل عملية بيع في جدول FactSales، وفقاً لعوامل التصفية المطبّقة.

  1. في جدول FactSales، انقر فوق عمود SalesKey.

  2. في الحسابات، انقر فوق السهم للأسفل على جمع تلقائي >‏ عدد.

  3. عاود تسمية الحقل المحسوب الجديد بالنقر بزر الماوس الأيمن فوق عدد SalesKey في ناحية الحسابات، ثم تحديد إعادة تسمية. اكتب عدد، ثم اضغط على Enter.

  4. في Excel، في Sheet1، في قائمة الحقول، في FactSales، انقر فوق عدد.

لاحظ إضافة عمود عدد جديد إلى PivotTable يعرض عدد عمليات البيع وفقاً لعوامل التصفية المطبّقة. تماماً كما في هو الحال مع الحقل المحسوب "مجموع الأرباح"، سترى "العدد" وقد تمت تصفيته حسب التسلسلات الهيكلية لفئة المنتج والتواريخ.

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

  1. في جدول FactSales، في ناحية الحساب، انقر فوق خلية فارغة. تلميح: تُعد الخلية العلوية اليمنى مكاناً مفيداً لبدء وضع الحقول المحسوبة فيه. فهذا المكان يسهّل العثور عليها. يمكنك تحريك أي حقل محسوب في ناحية الحساب.

  2. في شريط الصيغة، اكتب واستخدم IntelliSense لإنشاء الصيغة التالية: Percentage of All Products:=[Count]/CALCULATE([Count], ALL(DimProduct))

  3. اضغط على Enter لقبول الصيغة.

  4. في Excel، في الورقة1، في قائمة الحقول، في FactSales، انقر فوق النسبة المئوية لكل المنتجات.

  5. في جدول PivotTable، حدد أعمدة النسبة المئوية لكل المنتجات بطريقة متعددة.

  6. على علامة تبويب الصفحة الرئيسية، انقر فوق رقم >‏ نسبة مئوية. استخدم منزلتين عشريتين لتنسيق كل عمود جديد.

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

سيكون إنشاء الصيغ لكل من الأعمدة المحسوبة والحقول المحسوبة سهلاً بالنسبة لك إذا كنت ملماً بإنشاء صيغ Excel. سواء كنت ملماً بدالات Excel أم لا، ثمة مكان مفيد يمكنك فيه التعرّف على أساسيات صيغ DAX من خلال متابعة الدروس في بداية سريعة: تعرّف على أساسيات DAX في 30 دقيقة.

حفظ العمل

احفظ المصنف لكي تتمكّن من استخدامه مع برامج تعليمية أخرى أو عمليات استكشاف إضافية.

الخطوات التالية

على الرغم من إمكانية استيراد البيانات من Excel بسهولة، فإن استيرادها باستخدام الوظيفة الإضافية Power Pivot يكون في أغلب الأحيان أكثر سرعة وفعالية. ويمكنك تصفية البيانات التي تستوردها، باستثناء الأعمدة التي لن تحتاج إليها. كما يمكنك أن تختار إن كنت تريد استخدام منشئ الاستعلام أو أمر استعلام لاسترداد البيانات. كخطوة تالية، تعرّف على هذين الأسلوبين البديلين: إحضار بيانات من موجز بيانات في Power Pivot واستيراد بيانات من Analysis Services أو Power Pivot.

لقد تم تصميم تقارير Power View للعمل مع نماذج بيانات مماثلة لنموذج البيانات الذي أنشأته للتوّ. تابع القراءة لمعرفة المزيد حول مؤثرات عرض البيانات الغنية التي تحضرها Power View إلى Excel‏: بدء الوظيفة الإضافية PowerPivot في Excel 2013 وPower View: استكشاف البيانات وإظهار مؤثرات عرضها وتقديمها.

حاول تحسين نموذج البيانات لإنشاء تقارير Power View أفضل من خلال اتباع هذا البرنامج التعليمي: تحسين نموذج البيانات لتقارير Power View

توسيع المهارات
استكشاف التدريب
الحصول على الميزات الجديدة أولاً
الانضمام إلى Office Insider

هل كانت المعلومات مفيدة؟

نشكرك على ملاحظاتك!

شكراً لك على الملاحظات! يبدو أنه من المفيد إيصالك بأحد وكلاء دعم Office لدينا.

×