كيفية تكوين جداول Pivot Excel 2010

01 من 15

النتيجة النهائية

كانت هناك فجوة بين مايكروسوفت إكسل وأهم منصات ذكاء الأعمال (BI) لسنوات عديدة. جعلت التحسينات المحسّنة لـ Microsoft Excel 2010 Pivot Table مع بعض ميزات BI الأخرى منافسًا حقيقيًا لشركة BI. تم استخدام Excel تقليديًا للتحليل المستقل والأداة القياسية التي يصدرها الجميع لتقاريرهم النهائية. وقد احتفظ ذكاء الأعمال المهنية تقليديا لأمثال SAS و Business Objects و SAP.

Microsoft Excel 2010 (مع جدول Pivot 2010 Excel) مع SQL Server 2008 R2 و SharePoint 2010 ووظيفة "Microsoft PowerPivot" الإضافية في Microsoft Excel 2010 والتي نتج عنها حل ذكاء الأعمال وتقديم التقارير.

يغطي هذا البرنامج التعليمي سيناريو للأمام بشكل مستقيم مع PivotTable Excel 2010 متصل بقاعدة بيانات SQL Server 2008 R2 باستخدام استعلام SQL بسيط. أنا أستخدم أيضًا أدوات التصفية البصرية للمرشح المرئي الجديد في Excel 2010. سأغطي تقنيات BI الأكثر تعقيدًا باستخدام تعبيرات تحليل البيانات (DAX) في PowerPivot for Excel 2010 في المستقبل القريب. يمكن أن يوفر هذا الإصدار الأخير من Microsoft Excel 2010 قيمة حقيقية لمجتمع المستخدمين الخاص بك.

02 من 15

إدراج Pivot Table

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

• افتح مصنف Excel 2010 جديد أو موجود وانقر فوق الخلية حيث تريد أن يكون أعلى الزاوية اليسرى من Pivot Table.
• انقر فوق علامة التبويب إدراج وانقر فوق القائمة المنسدلة PivotTable في قسم "الجداول". اختر PivotTable. سيبدأ هذا في إنشاء نموذج الحوار PivotTable.

03 من 15

ربط Pivot Table بـ SQL Server (أو قاعدة بيانات أخرى)

يمكن لـ Excel 2010 استرداد البيانات من كافة موفري RDBMS الرئيسيين (نظام إدارة قواعد البيانات العلائقية) . يجب أن تكون برامج تشغيل SQL Server متوفرة للاتصال بشكل افتراضي. ولكن كافة برامج قاعدة البيانات الرئيسية تجعل برامج تشغيل ODBC (اتصال قاعدة البيانات المفتوح) تسمح لك بإجراء الاتصال. تحقق من موقعة على الإنترنت إذا كنت بحاجة إلى تنزيل برامج تشغيل ODBC.

في حالة هذا البرنامج التعليمي ، أقوم بالاتصال بـ SQL Server 2008 R2 (إصدار SQL Express المجاني).

• A - نموذج "إنشاء PivotTable" هو النموذج الأول في إنشاء الاتصال بـ SQL Server. حدد "استخدام مصدر بيانات خارجي" وانقر على زر اختيار اتصال. اترك موقع المكان الذي سيتم وضع Pivot Table فيه ما لم ترغب في إنشاء ورقة عمل جديدة ووضعها هناك.
• ب - يسرد نموذج الاتصالات الحالية أية اتصالات في المصنف الحالي وعلى الكمبيوتر والشبكة التي تتصل بها حاليًا. الاتصالات الموجودة هي بالفعل ملفات نصية تحتوي على معلومات الاتصال اللازمة للوصول إلى مصدر بيانات معين. في حالتنا ، سنقوم بإنشاء مصدر بيانات جديد. انقر فوق الزر Browse for More.
• ج - اضغط على زر المصدر الجديد سيطلق معالج اتصال البيانات.
• د - اختر Microsoft SQL Server وانقر فوق التالي.
• E - أدخل اسم الخادم وقم بتسجيل الدخول. اختر طريقة التوثيق المناسبة. إذا لم تكن متأكدًا من الطريقة المستخدمة ، فاتصل بمسؤول قاعدة البيانات.
  • استخدام مصادقة Windows: تستخدم هذه الطريقة تسجيل الدخول إلى الشبكة للوصول إلى قواعد بيانات SQL Server.
  • استخدم اسم المستخدم وكلمة المرور التاليين: يتم استخدام هذه الطريقة عند تكوين SQL Server مع المستخدمين المستقلين للوصول إلى قواعد البيانات.
• F - في هذه الخطوة ، سنختار جدولًا كعنصر نائب. سنقوم باستبدال الجدول ب SQL المخصص الذي سيوفر بالضبط البيانات التي نريدها في مصنف Excel الخاص بنا.
  • حدد قاعدة البيانات التي سيتم الاتصال بها. في هذا المثال ، نقوم بالاتصال بقاعدة بيانات نموذج AdventureWorks التي توفرها Microsoft. تحقق من الاتصال بجدول محدد واختر الجدول الأول. تذكر ، لن نقوم باسترجاع البيانات من هذا الجدول.
  • انقر فوق "إنهاء" التي سيتم إغلاق المعالج وإرجاعك إلى المصنف. سنقوم بمبادلة جدول العناصر النائبة لاستعلام SQL المخصص الخاص بنا.

سيتم إرجاعك إلى نموذج إنشاء PivotTable (A). انقر فوق موافق.

04 من 15

Pivot Table تم الاتصال مؤقتًا بجدول SQL

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

05 من 15

افتح خصائص الاتصال

قبل البدء في اختيار البيانات لـ PivotTable ، نحتاج إلى تغيير الاتصال إلى استعلام SQL. تأكد من أنك في علامة التبويب "خيارات" وانقر فوق "تغيير مصدر البيانات" المنسدلة من قسم "البيانات". اختر خصائص الاتصال.

هذا بإظهار نموذج "خصائص الاتصال". انقر فوق علامة التبويب التعريف. يعرض لك هذا معلومات الاتصال للاتصال الحالي بـ SQL Server. بينما تشير إلى ملف اتصال ، فإن البيانات مدمجة فعليًا في جدول البيانات.

06 من 15

تحديث خصائص الاتصال مع الاستعلام

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

SELECT Sales.SalesOrderHeader.SalesOrderID،
Sales.SalesOrderHeader.OrderDate،
Sales.SalesOrderHeader.ShipDate،
Sales.SalesOrderHeader.Status،
Sales.SalesOrderHeader.SubTotal،
Sales.SalesOrderHeader.TaxAmt،
Sales.SalesOrderHeader.Freight،
Sales.SalesOrderHeader.TotalDue،
Sales.SalesOrderDetail.SalesOrderDetailID،
Sales.SalesOrderDetail.OrderQty،
Sales.SalesOrderDetail.UnitPrice،
Sales.SalesOrderDetail.LineTotal،
Production.Product.Name ،
Sales.vIndividualCustomer.StateProvinceName، Sales.vIndividualCustomer.CountryRegionName،
Sales.Customer.CustomerType ،
Production.Product.ListPrice ،
الإنتاج. المنتج.
Production.ProductSubcategory.Name AS ProductCategory
من Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ON
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN الإنتاج. المنتج على Sales.SalesOrderDetail.ProductID =
الإنتاج.المنتج.المنتجأونيد الإنضمام المبيعات.العميل على
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID AND
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOIN
Sales.vIndividualCustomer ON Sales.Customer.CustomerID =
Sales.vIndividualCustomer.CustomerID INNER JOIN
الإنتاج. المنتجالمنتج على الإنتاج
Production.ProductSubcategory.ProductSubcategoryID

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

07 من 15

تلقي اتصال تحذير

ستتلقى مربع حوار تحذير Microsoft Excel. هذا لأننا غيرنا معلومات الاتصال. عندما أنشأنا في الأصل الاتصال ، قام بحفظ المعلومات في ملف .ODC خارجي (اتصال بيانات ODBC). كانت البيانات الموجودة في المصنف هي نفس ملف .ODC حتى تم تغييرها من نوع أمر جدول إلى نوع أمر SQL في الخطوة رقم 6. يخبرك التحذير أن البيانات لم تعد متزامنة وسيتم حذف المرجع إلى الملف الخارجي في المصنف. لابأس. انقر فوق نعم.

08 من 15

Pivot Table متصل بـ SQL Server مع الاستعلام

هذا يعود إلى مصنف Excel 2010 مع PivotTable فارغة. يمكنك أن ترى أن الحقول المتاحة مختلفة الآن وتتوافق مع الحقول في استعلام SQL. يمكننا الآن إضافة حقول إلى PivotTable.

09 من 15

أضف الحقول إلى Pivot Table

في قائمة حقول PivotTable ، اسحب ProductCategory إلى منطقة تسميات الصفوف ، OrderDate إلى منطقة تسميات الأعمدة و TotalDue إلى مساحة القيم. تظهر الصورة النتائج. كما ترى ، يحتوي حقل التاريخ على تواريخ فردية ، لذلك قام PivotTable بإنشاء عمود لكل تاريخ فريد. لحسن الحظ ، يحتوي Excel 2010 على بعض الوظائف المضمنة لمساعدتنا في تنظيم حقول التاريخ.

10 من 15

إضافة التجميع لحقول التاريخ

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

11 من 15

اختر التجميع حسب القيم

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

انقر على الأشهر والسنوات وانقر فوق موافق.

12 من 15

Pivot Table مجمعة حسب السنوات والأشهر

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

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

13 من 15

Insert Slicer (الجديد في Excel 2010)

تكون "Slicers" جديدة في Excel 2010. وهي تقارن بشكل أساسي عوامل تصفية الإعداد المرئي للحقول الموجودة وإنشاء عوامل تصفية التقرير في حالة عدم وجود العنصر الذي تريد تصفيته في طريقة العرض PivotTable الحالية. هذا الشيء الجميل حول Slicers يصبح من السهل جدًا على المستخدم تغيير طريقة عرض البيانات في PivotTable بالإضافة إلى توفير مؤشرات مرئية للحالة الحالية للفلاتر.

لإدراج Slicers ، انقر فوق علامة التبويب Options وانقر فوق Insert Slicer من المقطع Sort & Filter. اختر Insert Slicer الذي يفتح نموذج Insert Slicers. تحقق من أكبر عدد من الحقول التي تريد توفرها. في المثال الخاص بنا ، أضفت سنوات و CountryRegionName و ProductCategory. قد تضطر إلى وضع الشرائح حيث تريدها. بشكل افتراضي ، يتم تحديد جميع القيم مما يعني أنه لم يتم تطبيق أي فلاتر.

14 من 15

الجدول المحوري مع تقطيع اللحم سهل الاستعمال

كما ترى ، تعرض الشرائح جميع البيانات كما تم تحديدها. من الواضح جداً للمستخدم ما هي البيانات الموجودة في طريقة العرض الحالية لـ PivotTable.

15 من 15

اختر القيم من Slicers التي تحديث Pivot Table

انقر على مجموعات مختلفة من القيم وشاهد كيف يتغير عرض PivotTable. يمكنك استخدام نقر Microsoft نموذجي في Slicers بمعنى أنه إذا كان بإمكانك استخدام Control + انقر لتحديد قيم متعددة أو Shift + Click لتحديد نطاق من القيم. يعرض كل Slicer القيم المحددة التي تجعل من الواضح حقًا حالة PivotTable من حيث عوامل التصفية. يمكنك تغيير أنماط "القطوعات" إذا كنت تريد بالنقر فوق "أنماط سريعة" المنسدلة في المقطع Slicer لعلامة التبويب خيارات.

لقد أدى إدخال شرائح Slicers إلى تحسين إمكانية استخدام PivotTables حقًا وقد نقل Excel 2010 إلى حد كبير لكونه أداة استخبارات أعمال مهنية. لقد تحسنت PivotTables إلى حد كبير في Excel 2010 ، وعندما يتم دمجها مع PowerPivot الجديد ، فإنها تخلق بيئة تحليلية عالية الأداء.