فوائد Pivot by Data Model

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

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

ومع ذلك ، قبل النظر في هذه "الكعكات" عن قرب ، دعنا أولاً نفهم ما هو نموذج البيانات هذا في الواقع؟

ما هو نموذج البيانات

نموذج البيانات (يُشار إليها باختصار MD أو DM = نموذج البيانات) هي منطقة خاصة داخل ملف Excel حيث يمكنك تخزين البيانات المجدولة - واحد أو أكثر من الجداول المرتبطة ببعضها البعض ، إذا رغبت في ذلك. في الواقع ، هذه قاعدة بيانات صغيرة (مكعب OLAP) مضمنة داخل مصنف Excel. مقارنةً بالتخزين الكلاسيكي للبيانات في شكل جداول عادية (أو ذكية) على أوراق Excel نفسها ، فإن نموذج البيانات له العديد من المزايا المهمة:

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

تتم إدارة النموذج وحسابه بواسطة وظيفة إضافية خاصة مضمنة في Microsoft Excel - PowerPivotالتي كتبت عنها بالفعل. لتمكينه ، في علامة التبويب المطور انقر وظائف COM الإضافية (المطور - وظائف COM الإضافية) وحدد المربع المناسب:

إذا كانت علامات التبويب المطور (مطور)لا يمكنك رؤيته على الشريط ، يمكنك تشغيله من خلاله ملف - خيارات - إعداد الشريط (ملف - خيارات - تخصيص الشريط). إذا لم يكن لديك Power Pivot في النافذة الموضحة أعلاه في قائمة وظائف COM الإضافية ، فلن يتم تضمينه في إصدار Microsoft Office الخاص بك 🙁

في علامة التبويب Power Pivot التي تظهر ، سيكون هناك زر أخضر فاتح كبير الإدارة (يدير)، بالنقر فوق الذي سيفتح نافذة Power Pivot أعلى Excel ، حيث سنرى محتويات نموذج البيانات للكتاب الحالي:

ملاحظة مهمة على طول الطريق: يمكن لمصنف Excel أن يحتوي على نموذج بيانات واحد فقط.

تحميل الجداول في نموذج البيانات

لتحميل البيانات في النموذج ، نحول الجدول أولاً إلى اختصار لوحة مفاتيح ديناميكي "ذكي" CTRL+T ومنحه اسمًا مألوفًا في علامة التبويب منشئ (التصميم). هذا هو خطوة لازمة.

ثم يمكنك استخدام أي من الطرق الثلاث للاختيار من بينها:

• اضغط على زر أضف إلى النموذج (إضافة إلى نموذج البيانات) علامة التبويب PowerPivot علامة التبويب الصفحة الرئيسية (الرئيسية).
  
• اختيار الفرق إدراج - PivotTable (إدراج - جدول محوري) وقم بتشغيل مربع الاختيار أضف هذه البيانات إلى نموذج البيانات (أضف هذه البيانات إلى نموذج البيانات). في هذه الحالة ، وفقًا للبيانات التي تم تحميلها في النموذج ، يتم أيضًا إنشاء جدول محوري على الفور.
  
• في علامة التبويب خيارات متقدمة البيانات (تاريخ) انقر على الزر من الجدول / النطاق (من جدول / نطاق)لتحميل جدولنا في محرر Power Query. هذا المسار هو الأطول ، ولكن ، إذا رغبت في ذلك ، يمكنك هنا إجراء تنظيف إضافي للبيانات وتحريرها وجميع أنواع التحويلات ، حيث يكون Power Query قويًا جدًا.

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

نقوم ببناء ملخص لنموذج البيانات

لإنشاء نموذج بيانات ملخص ، يمكنك استخدام أي من الأساليب الثلاثة:

• اضغط الزر جدول التلخيص (جدول محوري) في نافذة Power Pivot.
• حدد الأوامر في Excel إدراج - PivotTable والتبديل إلى الوضع استخدم نموذج بيانات هذا الكتاب (إدراج - جدول محوري - استخدم نموذج بيانات هذا المصنف).
• اختيار الفرق إدراج - PivotTable (إدراج - جدول محوري) وقم بتشغيل مربع الاختيار أضف هذه البيانات إلى نموذج البيانات (أضف هذه البيانات إلى نموذج البيانات). سيتم تحميل الجدول "الذكي" الحالي في النموذج وسيتم إنشاء جدول ملخص للنموذج بأكمله.

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

الفائدة 1: العلاقات بين الجداول بدون استخدام الصيغ

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

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

بعد ذلك ، في ملخص نموذج البيانات ، يمكنك إلقاء في منطقة الملخص (الصفوف والأعمدة والفلاتر والقيم) أي حقول من أي جداول مرتبطة - سيتم ربط كل شيء وحسابه تلقائيًا:

فائدة 2: حساب القيم الفريدة

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

انقر بزر الماوس الأيمن فوق الحقل - الأمر خيارات حقل القيمة وعلى علامة التبويب عملية اختار عدد العناصر المختلفة (عدد مميز):

الفائدة 3: صيغ DAX المخصصة

في بعض الأحيان ، يتعين عليك إجراء عمليات حسابية إضافية متنوعة في الجداول المحورية. في الملخصات العادية ، يتم ذلك باستخدام الحقول والكائنات المحسوبة ، بينما يستخدم ملخص نموذج البيانات مقاييس بلغة DAX خاصة (DAX = تعبيرات تحليل البيانات).

لإنشاء مقياس ، حدد في علامة التبويب PowerPivot أمر المقاييس - إنشاء مقياس (تدابير - مقياس جديد) أو فقط انقر بزر الماوس الأيمن على الجدول في قائمة Pivot Fields وحدد أضف قياسًا (إضافة قياس) في قائمة السياق:

في النافذة التي تفتح ، اضبط:

• اسم الطاولةحيث سيتم تخزين التدبير الذي تم إنشاؤه.
• قياس الاسم - أي اسم تفهمه للحقل الجديد.
• الوصف - اختياري.
• المعادلة - أهم شيء ، لأننا هنا إما ندخل يدويًا أو نضغط على الزر fx وحدد دالة DAX من القائمة ، والتي يجب أن تحسب النتيجة عندما نطرح مقياسنا في منطقة القيم.
• في الجزء السفلي من النافذة ، يمكنك على الفور تعيين تنسيق الأرقام للمقياس في القائمة الفئة.
  

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

الفائدة 4: التدرجات الهرمية للحقول المخصصة

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

في نافذة Power Pivot ، بدّل إلى وضع المخطط باستخدام الزر عرض الرسم البياني علامة التبويب الصفحة الرئيسية (الصفحة الرئيسية - عرض الرسم التخطيطي)، حدد مع CTRL الحقول المطلوبة وانقر عليها بزر الماوس الأيمن. ستحتوي قائمة السياق على الأمر إنشاء تسلسل هرمي (إنشاء تسلسل هرمي):

يمكن إعادة تسمية التسلسل الهرمي الذي تم إنشاؤه وسحب الحقول المطلوبة إليه باستخدام الماوس ، بحيث يمكن إلقاؤها لاحقًا في حركة واحدة في الملخص:

الفائدة 5: الإستنسل المخصص

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

للقيام بذلك ، في علامة التبويب تحليل الجدول المحوري في القائمة المنسدلة الحقول والعناصر والمجموعات هناك أوامر مقابلة (تحليل - الحقول ، أناtems & Sets - إنشاء مجموعة بناءً على عناصر الصف / العمود):

في النافذة التي تفتح ، يمكنك بشكل انتقائي إزالة أو إضافة أو تغيير موضع أي عناصر وحفظ المجموعة الناتجة باسم جديد:

سيتم عرض جميع المجموعات التي تم إنشاؤها في لوحة حقول PivotTable في مجلد منفصل ، حيث يمكن سحبها بحرية إلى مناطق الصفوف والأعمدة في أي PivotTable جديد:

فائدة 6: إخفاء الجداول والأعمدة بشكل انتقائي

على الرغم من أن هذه ميزة صغيرة ولكنها ممتعة للغاية في بعض الحالات. بالنقر بزر الماوس الأيمن فوق اسم الحقل أو على علامة تبويب الجدول في نافذة Power Pivot ، يمكنك تحديد الأمر إخفاء من مجموعة أدوات العميل (إخفاء من أدوات العميل):

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

فائدة 7. حفر متقدم

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

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

بعد ذلك ، ستنتقل القيمة الحالية (Model = Explorer) إلى منطقة التصفية ، وسيتم إنشاء الملخص بواسطة المكاتب:

بالطبع ، يمكن تكرار مثل هذا الإجراء عدة مرات ، مع الخوض في بياناتك باستمرار في الاتجاه الذي تهتم به.

الفائدة 8: تحويل وظائف Pivot إلى Cube

إذا حددت أي خلية في الملخص الخاص بنموذج البيانات ثم حددت في علامة التبويب تحليل الجدول المحوري أمر أدوات OLAP - تحويل إلى صيغ (تحليل - أدوات OLAP - تحويل إلى صيغ)، ثم سيتم تحويل الملخص بالكامل تلقائيًا إلى الصيغ. الآن سيتم استرداد قيم الحقل في منطقة عمود الصف والنتائج في منطقة القيمة من نموذج البيانات باستخدام وظائف المكعب الخاصة: CUBEVALUE و CUBEMEMBER:

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

في الوقت نفسه ، يبقى الاتصال ببيانات المصدر ، بالطبع ، وسيتم تحديث هذه الصيغ في المستقبل عندما تتغير المصادر. الجمال!

• تحليل حقيقة الخطة في جدول محوري باستخدام Power Pivot و Power Query
• جدول محوري برأس متعدد الأسطر
• أنشئ قاعدة بيانات في Excel باستخدام Power Pivot