تعد عمليات التجميع طريقة لطي البيانات أو تلخيصها أو تجميعها. عند البدء بالبيانات الأولية من الجداول أو مصادر البيانات الأخرى، غالبا ما تكون البيانات مسطحة، مما يعني أن هناك الكثير من التفاصيل، ولكن لم يتم تنظيمها أو تجميعها بأي شكل من الأشكال. قد يجعل هذا النقص في الملخصات أو البنية من الصعب اكتشاف الأنماط في البيانات. جزء مهم من نمذجة البيانات هو تعريف التجميعات التي تبسط الأنماط أو تجردها أو تلخصها في إجابة على سؤال عمل معين.
يمكن إنشاء التجميعات الأكثر شيوعا، مثل تلك التي تستخدم AVERAGE أو COUNT أو DISTINCTCOUNT أو MAX أو MIN أو SUM ، في مقياس تلقائيا باستخدام الجمع التلقائي. ترجع أنواع أخرى من التجميعات، مثل AVERAGEX أو COUNTX أو COUNTROWS أو SUMX، جدولا وتتطلب صيغة منشأة باستخدام تعبيرات تحليل البيانات (DAX).
التعرف على التجميعات في Power Pivot
اختيار المجموعات للتجميع
عند تجميع البيانات، يمكنك تجميع البيانات حسب السمات مثل المنتج أو السعر أو المنطقة أو التاريخ ثم تحديد صيغة تعمل على كل البيانات في المجموعة. على سبيل المثال، عند إنشاء إجمالي لمدة عام، فأنت تقوم بإنشاء تجميع. إذا قمت بعد ذلك بإنشاء نسبة لهذا العام على السنة السابقة وتقديمتها كنسب مئوية ، فهذا نوع مختلف من التجميع.
يعتمد قرار كيفية تجميع البيانات على سؤال العمل. على سبيل المثال، بإمكان التجميعات الإجابة على الأسئلة التالية:
الأعداد كم عدد العمليات التي تمت في الشهر؟
المتوسطات ماذا كان متوسط المبيعات في هذا الشهر من قبل مندوب المبيعات؟
الحد الأدنى والأقصى للقيم ما هي مناطق المبيعات التي كانت الخمسة الأولى من حيث الوحدات المباعة؟
لإنشاء عملية حسابية تجيب على هذه الأسئلة، يجب أن تتوفر لديك بيانات مفصلة تحتوي على الأرقام المراد حسابها أو جمعها، ويجب أن تكون تلك البيانات الرقمية مرتبطة بطريقة ما بالمجموعات التي ستستخدمها لتنظيم النتائج.
إذا كانت البيانات لا تحتوي بالفعل على قيم يمكنك استخدامها للتجميع، كفئة منتج أو اسم المنطقة الجغرافية التي يقع فيها المتجر، فقد ترغب في تعريف مجموعات ببياناتك عن طريق إضافة الفئات. عند إنشاء مجموعات في Excel، يجب كتابة المجموعات التي تريد استخدامها أو تحديدها يدويا من بين الأعمدة في ورقة العمل. ومع ذلك، في النظام العلائقي، يتم غالبا تخزين التسلسلات الهيكلية مثل فئات المنتجات في جدول مختلف عن جدول الحقائق أو القيم. عادة ما يكون جدول الفئات مرتبطا ببيانات الحقائق بواسطة نوع المفتاح. على سبيل المثال، افترض أنك وجدت أن بياناتك تحتوي على معرفات المنتجات وليس أسماء المنتجات أو فئاتها. لإضافة الفئة إلى ورقة عمل Excel مسطحة، يجب النسخ في العمود الذي يحتوي على أسماء الفئات. باستخدام Power Pivot، يمكنك استيراد جدول فئة المنتج إلى نموذج البيانات وإنشاء علاقة بين الجدول وبيانات الأرقام وقائمة فئات المنتج، ثم استخدام الفئات لتجميع البيانات. لمزيد من المعلومات، راجع إنشاء علاقة بين الجداول.
اختيار دالة للتجميع
بعد تحديد المجموعات المراد استخدامها وإضافتها، يجب تحديد الدالات الرياضية التي تريد استخدامها للتجميع. غالبا ما تستخدم كلمة تجميع كمرادف للعمليات الرياضية أو الإحصائية المستخدمة في التجميعات، مثل المجاميع أو المتوسطات أو الحد الأدنى أو التعدادات. ومع ذلك، يمكنك Power Pivot من إنشاء صيغ مخصصة للتجميع، بالإضافة إلى التجميعات القياسية الموجودة في كل من Power Pivot وExcel.
على سبيل المثال، بالاستناد إلى مجموعة القيم والتجميعات نفسها التي تم استخدامها في الأمثلة السابقة، يمكنك إنشاء تجميعات مخصصة تجيب على الأسئلة التالية:
الأعداد التي تمت تصفيتها ما عدد العمليات التي تم إجراؤها في الشهر، باستثناء نافذة الصيانة في نهاية الشهر؟
النسب باستخدام المتوسطات مع مرور الوقت ما هي النسبة المئوية للنمو أو الانخفاض في المبيعات مقارنة بالفترة نفسها من العام الماضي؟
الحد الأدنى والأقصى القيم المجمعة ما هي مناطق المبيعات التي تم تصنيفها في المرتبة الأولى لكل فئة منتج أو لكل عرض ترويجي للمبيعات؟
إضافة التجميعات إلى الصيغ وجداول PivotTable
عندما تكون لديك فكرة عامة عن كيفية تجميع البيانات لتكون ذات معنى، والقيم التي تريد العمل عليها، يمكنك تحديد ما إذا كنت تريد إنشاء PivotTable أو إنشاء عمليات حسابية داخل جدول. يعمل Power Pivot على توسيع إمكانية Excel الأصلية لإنشاء تجميعات مثل المجاميع أو التعدادات أو المتوسطات وتحسينها. يمكنك إنشاء تجميعات مخصصة في Power Pivot إما ضمن نافذة Power Pivot أو ضمن منطقة Excel PivotTable.
- في عمود محسوب، يمكنك إنشاء تجميعات تأخذ في الاعتبار سياق الصف الحالي لاسترداد الصفوف المرتبطة من جدول آخر، ثم جمع هذه القيم أو عدها أو حساب متوسطها في الصفوف المرتبطة.
- في مقياس، يمكنك إنشاء تجميعات ديناميكية تستخدم كلا من عوامل التصفية المعرفة في الصيغة، وعوامل التصفية التي يفرضها تصميم PivotTable وتحديد مقسمات طرق العرض وعناوين الأعمدة وعناوين الصفوف. يمكن إنشاء المقاييس باستخدام التجميعات القياسية في Power Pivot باستخدام الجمع التلقائي أو إنشاء صيغة. يمكنك أيضا إنشاء مقاييس ضمنية باستخدام التجميعات القياسية في PivotTable في Excel.
إضافة تجميعات إلى PivotTable
عندما تقوم بتصميم PivotTable، اسحب الحقول التي تمثل تجميعات أو فئات أو تسلسلات هيكلية إلى مقطع الأعمدة والصفوف في PivotTable لتجميع البيانات. ثم اسحب الحقول التي تحتوي على قيم رقمية إلى منطقة القيم بحيث يمكن حسابها أو حساب متوسطها أو جمعها.
إذا أضفت فئات إلى PivotTable ولكن بيانات الفئة غير مرتبطة ببيانات الحقائق، فقد تحصل على خطأ أو نتائج غريبة. سيحاول Power Pivot عادة تصحيح المشكلة من خلال الكشف التلقائي عن العلاقات واقتراحها. لمزيد من المعلومات، راجع استخدام العلاقات في PivotTables.
يمكنك أيضا سحب الحقول إلى مقسمات طرق العرض لتحديد مجموعات معينة من البيانات لعرضها. تتيح لك مقسمات طرق العرض تجميع النتائج وفرزها وتصفيتها بشكل تفاعلي في PivotTable.
استخدام المجموعات في صيغة
يمكنك أيضا استخدام المجموعات والفئات لتجميع البيانات المخزنة في الجداول عن طريق إنشاء علاقات بين الجداول، ثم إنشاء الصيغ التي ترفع من تلك العلاقات للبحث عن القيم ذات الصلة.
بمعنى آخر، إذا أردت إنشاء صيغة تجمع القيم حسب فئة، فاستخدم أولا علاقة لتوصيل الجدول الذي يحتوي على بيانات التفاصيل والجداول التي تحتوي على الفئات، ثم أنشئ الصيغة.
لمزيد من المعلومات حول كيفية إنشاء الصيغ التي تستخدم عمليات البحث، راجع عمليات البحث في صيغ Power Pivot.
استخدام عوامل التصفية في التجميعات
ثمة ميزة جديدة في Power Pivot وهي القدرة على تطبيق عوامل التصفية على الأعمدة وجداول البيانات، ليس فقط في واجهة المستخدم وضمن PivotTable أو مخطط، ولكن أيضا في الصيغ ذاتها التي تستخدمها لحساب التجميعات. يمكن استخدام عوامل التصفية في الصيغ في كل من الأعمدة المحسوبة وفي s.
على سبيل المثال، في دالات تجميع DAX الجديدة، بدلا من تحديد القيم التي يتم جمعها أو عدها، يمكنك تحديد جدول بأكمله كوسيطة. إذا لم تقم بتطبيق أي عوامل تصفية على ذلك الجدول، فستعمل وظيفة التجميع مقابل كل القيم الموجودة في العمود المحدد في الجدول. ومع ذلك، في DAX، يمكنك إنشاء إما عامل تصفية ديناميكي أو ثابت في الجدول، بحيث يعمل التجميع مقابل مجموعة فرعية مختلفة من البيانات وفقا لحالة التصفية والسياق الحالي.
من خلال جمع الشروط وعوامل التصفية في الصيغ، يمكنك إنشاء تجميعات تتغير استنادا إلى القيم المتوفرة في الصيغ، أو تتغير استنادا إلى تحديد الصفوف والعناوين وعناوين الأعمدة في PivotTable.
لمزيد من المعلومات، انظر تصفية البيانات في الصيغ.
مقارنة بين دالات تجميع Excel ودالات تجميع DAX
يسرد الجدول التالي بعض وظائف التجميع القياسية التي يوفرها Excel، ويوفر ارتباطات لتنفيذ هذه الدالات في PowerPivot. يعمل إصدار DAX لهذه الدالات إلى حد كبير مثل إصدار Excel، مع بعض الاختلافات البسيطة في بناء الجملة ومعالجة أنواع بيانات معينة.
وظائف التجميع Standard
| الدالة | استخدم |
|---|---|
| AVERAGE | ترجع هذه الدالة المتوسط (الوسط الحسابي) لكل الأرقام الموجودة في عمود. |
| AVERAGEA | ترجع هذه الدالة المتوسط (الوسط الحسابي) لكل القيم في عمود. يعالج القيم النصية وغير الرقمية. |
| COUNT | حساب عدد القيم الرقمية في عمود. |
| COUNTA | حساب عدد القيم غير الفارغة في عمود. |
| MAX | ترجع هذه الدالة أكبر قيمة رقمية في عمود. |
| MAXX | ترجع هذه الدالة القيمة الكبرى من مجموعة من التعبيرات التي تم تقييمها في جدول. |
| MIN | ترجع أصغر قيمة رقمية في عمود. |
| مينكس | ترجع هذه الدالة أصغر قيمة من مجموعة من التعبيرات التي تم تقييمها في جدول. |
| SUM | تجمع كافة الأرقام الموجودة في عمود. |
دالات تجميع DAX
يتضمن DAX دالات تجميع تسمح لك بتحديد جدول يتم تنفيذ التجميع عليه. وبالتالي، بدلا من مجرد إضافة القيم أو حساب متوسطها في عمود، تتيح لك هذه الدالات إنشاء تعبير يعرف البيانات المراد تجميعها بشكل ديناميكي.
يسرد الجدول التالي دالات التجميع المتوفرة في DAX.
| الدالة | استخدم |
|---|---|
| AVERAGEX | تحسب هذه الدالة مجموعة من التعبيرات التي تم تقييمها في جدول. |
| كونتاكس | حساب مجموعة من التعبيرات التي تم تقييمها في جدول. |
| COUNTBLANK | حساب عدد القيم الفارغة في عمود. |
| COUNTX | حساب العدد الإجمالي للصفوف في جدول. |
| عدد الصفوف | تحسب عدد الصفوف التي تم إرجاعها من دالة جدول متداخلة، مثل دالة التصفية. |
| SUMX | ترجع هذه الدالة مجموع مجموعة من التعبيرات التي تم تقييمها في جدول. |
الاختلافات بين دالات التجميع في DAX وExcel
على الرغم من أن هذه الدالات لها نفس أسماء نظيراتها في Excel، إلا أنها تستخدم محرك تحليل الذاكرة في Power Pivot وقد تمت إعادة كتابتها للعمل مع الجداول والأعمدة. لا يمكنك استخدام صيغة DAX في مصنف Excel، والعكس صحيح. لا يمكن استخدامها إلا في نافذة Power Pivot وفي PivotTables المستندة إلى بيانات Power Pivot. علاوة على ذلك، على الرغم من أن الدالات لها أسماء متطابقة، إلا أن السلوك قد يختلف بعض الشيء. لمزيد من المعلومات، راجع موضوعات مراجع الدالات الفردية.
تختلف الطريقة التي يتم بها تقييم الأعمدة في التجميع أيضا عن الطريقة التي يعالج بها Excel عمليات التجميع. مثال قد يساعد في توضيح ذلك.
لنفترض أنك تريد الحصول على مجموع القيم في عمود "القيم" في جدول "المبيعات"، فستنشئ الصيغة التالية:
=SUM('Sales'[Amount])
في أبسط أحوال، تحصل الدالة على القيم من عمود واحد لم تتم تصفيته، وتكون النتيجة هي نفسها كما في Excel، الذي يضيف دائما القيم الموجودة في العمود "Amount". ومع ذلك، في Power Pivot، يتم تفسير الصيغة على أنها "احصل على القيمة بالمبلغ لكل صف من صفوف جدول المبيعات ثم أضف تلك القيم الفردية. يقيم Power Pivot كل صف يتم فيه إجراء التجميع ويحسب قيمة عددية واحدة لكل صف، ثم يجري تجمعا على تلك القيم. وبالتالي، يمكن أن تختلف نتيجة الصيغة إذا تم تطبيق عوامل التصفية على جدول، أو إذا تم حساب القيم استنادا إلى تجميعات أخرى قد تمت تصفيتها. لمزيد من المعلومات، راجع السياق في صيغ DAX.
دالات تحليل معلومات الوقت في DAX
بالإضافة إلى وظائف تجميع الجداول الموضحة في القسم السابق، يتضمن DAX وظائف تجميع تعمل مع التواريخ والأوقات التي تحددها، لتوفير معلومات الوقت المضمنة. تستخدم هذه الدالات نطاقات من التواريخ للحصول على القيم ذات الصلة وتجميع القيم. يمكنك أيضا مقارنة القيم عبر نطاقات التواريخ.
يسرد الجدول التالي دالات معلومات الوقت التي يمكن استخدامها للتجميع.
| الدالة | استخدم |
|---|---|
|
CLOSINGBALANCEMONTH CLOSINGBALANCEQUARTER CLOSINGBALANCEYEAR |
تحسب هذه الدالة قيمة في نهاية التقويم من الفترة المحددة. |
|
OPENINGBALANCEMONTH OPENINGBALANCEQUARTER OPENINGBALANCEYEAR |
تحسب هذه الدالة قيمة عند نهاية التقويم من الفترة التي تسبق الفترة المحددة. |
|
TOTALMTD TOTALYTD TOTALQTD |
تحسب هذه الدالة قيمة عبر الفاصل الزمني الذي يبدأ في اليوم الأول من الفترة الزمنية وينتهي عند التاريخ الأبعد في عمود التاريخ المحدد. |
الدالات الأخرى في قسم الدالة "معلومات الوقت" (دالات تحليل معلومات الوقت) هي الدالات التي يمكن استخدامها لاسترداد التواريخ أو النطاقات المخصصة للتواريخ لاستخدامها في التجميع. على سبيل المثال، يمكنك استخدام الدالة DATESINPERIOD لإرجاع نطاق من التواريخ واستخدام مجموعة التواريخ هذه كوسيطة لدالة أخرى لحساب تجميع مخصص لتلك التواريخ فقط.