محور الطاقة. استخدام PowerPivot في Microsoft Excel. إنشاء عمود محسوب

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

ما هو محور الطاقة

Excel Power Pivot هي أداة تتيح للمستخدمين إنشاء أنظمة ذكاء الأعمال الخاصة بهم باستخدام Excel - استنادًا إلى قواعد البيانات الجدولية.

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

تعمل الوظيفة الإضافية Excel Power Pivot فقط في Office 2010 والإصدارات الأحدث وتحتوي على عدد من الميزات:

• ينفذ جميع العمليات الحسابية مباشرة في ذاكرة الوصول العشوائي للكمبيوتر، مما يسمح بأداء عالٍ للنظام، فضلاً عن القدرة على معالجة كميات كبيرة من البيانات (يمكن أن يصل حجم الجداول المصدر إلى ملايين الصفوف)؛
• يسمح لك بإنشاء جداول محورية على قاعدتك. تكمن القيمة التطبيقية لـ Excel PowerPivot للممولين والمحللين في حقيقة أنه بمساعدة الوظيفة الإضافية يمكنك الآن بشكل مستقل معالجة مثل هذه الكميات من المعلومات التي كانت تتطلب في السابق استخدام البرامج المتخصصة (DBMS) وإشراك متخصصي تكنولوجيا المعلومات المؤهلين ;
• يحتوي على لغة تعبير تحليل البيانات (Data Analysis eXpression، والمختصرة بـ DAX)، المصممة لتطوير قواعد منطق الأعمال الجديدة. ببساطة، يمكن اعتباره إصدارًا متقدمًا من صيغ الصفيف. تم تصميم صيغ لغة DAX خصيصًا لتكون أقرب ما يمكن إلى بناء جملة Excel. على الرغم من تشابهها مع دالات ورقة عمل Excel القياسية، إلا أن صيغ DAX تتم كتابتها فقط داخل Power Pivot Shell. تتمتع لغة DAX بنطاق واسع من الإمكانات، وتتوفر معلومات حول الطرق المختلفة لاستخدامها على الإنترنت.

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

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

1. خلال فترة تقويمية واحدة، يمكن للعميل استخدام العديد من الخدمات في وقت واحد (شراء العديد من السلع) - لذلك، لحساب المستوى الإجمالي للربحية، من الضروري تلخيص الدخل المستلم من جميع الخدمات المقدمة للعميل في الفترة التي تم تحليلها؛
2. اعتمادًا على مقدار الدخل المحسوب، يجب تعيين العميل إلى إحدى المجموعات المحددة مسبقًا.

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

مرجع

استخدام صيغ المصفوفة

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

دعونا نلقي نظرة على التعبير (انظر الشكل 1). معلمات الإدخال الخاصة به عبارة عن نطاقين من الخلايا: "الصف"، المميز باللون الأصفر، و"العمود" ذو التعبئة الزرقاء. في المنطقة المحدودة بهذه النطاقات، يتم تقديم صيغة صفيف بالشكل التالي: (=عمود الصف).

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

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

العمل مع صيغ Excel القياسية

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

في الشكل 2، يتم عرض البيانات المصدر في أعمدة "الشركة"، "الخدمة"، "الفترة"، "الإيرادات". نريد إنشاء عمودين محسوبين جديدين "Revenue_Amount"، حيث يتم حساب إجمالي الدخل للعميل في فترة معينة، وكذلك "Revenue_Level"، والذي يحدد، اعتمادًا على مستوى "Revenue_Amount"، مجموعة الدخل (الدخل تتم الإشارة إلى المستويات على الجانب الأيمن من الشكل 2).

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

هدفنا. لكل صف من جدول الحقائق، احسب إجمالي الدخل الذي يتوافق مع العميل والشهر. على سبيل المثال، تم تزويد "العميل أ" في فبراير 2013 بثلاث خدمات بمبلغ 15 و15 و25 وحدة. لذلك، في كل سطر يتعلق بفترة معينة، يجب إدخال نفس القيمة - 55 وحدة (15 + 15 + 25). يتم تنفيذ هذه العملية على مرحلتين.

المرحلة 1.من بين جميع صفوف جدول الحقائق، تتم تصفية تلك الصفوف التي تتطابق قيم سمات "الشركة" و"الفترة" الخاصة بها مع الصف الحالي.

المرحلة 2.يتم تلخيص قيم سمة "الدخل" في المجموعة المصفاة الناتجة.

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

دعنا ندخل التعبير 1 في أي من صفوف عمود "Income_Amount"، فقط اضغط على Enter، وبعد ذلك سيتم احتواؤه في جميع صفوف الجدول.

التعبير 1:
SUMIFS([الإيرادات]; [الشركة]; Fact_Table[[#هذا السطر];[الشركة]]; [الفترة]; Fact_Table[[#هذا السطر]; [الفترة]])

دعونا ننظر في كيفية عمل الصيغة. في كل مرة تقوم فيها باستدعاء الدالة SUMIFS (في كل صف)، يتم استخدام جدول الحقائق بأكمله (عمود "الإيرادات" الخاص به) مرة واحدة. ولكن في كل تكرار، يتم جمع الصفوف التي تتوافق مع قيم السمات للصف الحالي فقط. للقيام بذلك، تمت إضافة وسيطة خاصة إلى الصيغة - [# هذا السطر]، والتي تعمل بمثابة رابط للسطر الحالي. على وجه الخصوص، بالنسبة للصفوف المتعلقة بـ "العميل، أ" وفبراير 2013، ستكون قيمة المعلمة [[#هذا الصف]؛[الشركة]] مساوية لـ "العميل، أ"، و[[#هذا الصف]؛ [الفترة]] - "2013/02/01".

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

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

التعبير 2:
=SUMIFS(Regular_Range!$D$2:$D$37; Regular_Range!$A$2:$A$37; Regular_Range!$A5; Regular_Range!$C$2:$C$37; Regular_Range!$C5)

بالنسبة للسطر 6، سيبدو التعبير متطابقًا، مع الاختلاف الوحيد أنه بدلاً من $A5 سيكون هناك $A6 (وبدلاً من $C5 - $C6). في هذه الحالة، تتطابق نتيجة حساب التعبير 2 مع قيمة التعبير 1 في السطر المقابل، مما يسمح لنا باعتبارهما متطابقين.

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

على وجه الخصوص، تتيح لنا الدالة INDEX الحصول بسهولة على قيمة من مصفوفة ثنائية الأبعاد في أي خلية مصنف؛ ونحتاج فقط إلى معرفة معرف الكائن المقابل. لنكتب التعبير 3 في عمود "مستوى_الدخل":

التعبير 3:
=INDEX(المستويات[المستوى]; MATCH(Fact_Table[[#هذا الصف]; [Revenue_Amount]]; المستويات[البدء]))

يتم تنفيذ الصيغة المقدمة في التعبير 3، مثل التعبير 1، في السياق الحالي لجدول الحقائق - فهي تستخدم مرجعًا إلى الصف النشط. بالإضافة إلى ذلك، باستخدام وظيفة INDEX، يمكننا الوصول إلى كائن خارجي - جدول "المستويات". في ذلك، باستخدام وظيفة البحث، يتم البحث عن سلسلة مناسبة.

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

العمل مع برنامج Excel Power Pivot

دعونا نلقي نظرة على كيفية تنفيذ Power Pivot للعمليات الموضحة في الجزء الأول من المقالة.

تثبيت برنامج Excel Power Pivot.أولاً، يجب تثبيت الوظيفة الإضافية (يمكنك تنزيلها مجانًا من موقع Microsoft الرسمي). يجب أن تظهر علامة التبويب "Power Pivot" الجديدة على شريط Excel. إذا لم يتم تمكين الوظيفة الإضافية على الفور، فأنت بحاجة إلى تمكينها يدويًا: "خيارات Excel" - "الوظائف الإضافية"، في قائمة "الإدارة"، حدد "وظائف COM الإضافية" - "انتقال". كل ما تبقى هو تحديد المربع بجوار "Power Pivot for Excel". النافذة نشطة فقط في المستندات بتنسيق xlsx.

إعداد البيانات الأولية. تدعم قاعدة بيانات PowerPivot مجموعة واسعة من المصادر. على وجه الخصوص، يمكنك تحميل البيانات فيه من MS Access أو SQL Server DBMS. في هذه الحالة، يمكن أن يصل حجم المعلومات التي تمت معالجتها إلى ملايين الصفوف. ولكن بالنسبة للمستخدمين العاديين، فإن الأمر الأكثر قيمة هو القدرة على تخزين معلومات المصدر على أوراق في مصنف Excel عادي.

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

1. ضع الجداول في صفحات منفصلة من الكتاب.لاحظ أن هذا المتطلب يختلف عن وضع التشغيل القياسي لبرنامج Excel، والذي يسمح بوضع عدد غير محدود من الجداول التي تصف كيانات مختلفة في ورقة واحدة.

2. قم بتخزين الجداول بشكل منفصل عن ملف التقرير التلخيصي.إذا لزم الأمر، يمكن أيضًا وضع الجداول التي تحتوي على بيانات في مصنفات Excel مختلفة - يمكن أن يدعم Power Pivot اتصالات مفتوحة متوازية متعددة بمصادر بيانات من نوع معين.

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

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

لنقم الآن بإنشاء مصنف Excel آخر، واستدعاء نافذة PowerPivot فيه واستيراد البيانات من ملف "البيانات الأصلية" إليه. لاستيراد البيانات، تحتاج إلى استدعاء الأمر "الحصول على بيانات خارجية من مصادر أخرى" في القائمة "ملف"، ثم النوع "ملف Excel"، وتشغيل معالج استيراد البيانات.

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

في المرحلة الثانية، نقوم باختيار الجداول التي سيتم استخدامها في النموذج.وفي حالتنا، ينبغي لنا أن نختار كليهما.

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

في كل جدول، يمكنك إنشاء نوعين من الكائنات: الأعمدة المحسوبة والحقول المحسوبة.

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

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

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

كما هو الحال مع الجداول العادية، يتم تنفيذ صيغ DAX في سياق الصف. ولكن على عكس الجداول، ليست هناك حاجة لتحديد المعلمة [# هذا السطر] بشكل صريح. ببساطة، حتى بدون كتابة المعلمة [# هذا السطر] في صيغة DAX، يمكنك افتراض وجودها فيها واستخدامها في العمليات الحسابية. على الرغم من أن هذه الميزة تجعل صيغ DAX أقل وضوحًا، إلا أنها تسمح بكتابة التعبيرات بشكل أكثر إحكاما.

مع الأخذ في الاعتبار الميزات المدرجة للعمل مع صيغ DAX، سنقوم بإنشاء عمود جديد "العائد" بالصيغة التالية (التعبير 4):

التعبير 4:
=CALCULATE(sum([Sum]); ALLEXCEPT("Fact_Table"; "Fact_Table"[الشركة]; "Fact_Table"[الفترة]))

دعونا نلقي نظرة على مبدأ تشغيل التعبير 4.

أولاً، بالنسبة لكل صف من جدول الحقائق، يتلقى هذا العامل كمدخل جدولًا مؤقتًا يتزامن مع جدول الحقائق نفسه. يتم بعد ذلك تطبيق عوامل التصفية المحددة بواسطة السياق الحالي على هذا الجدول المرحلي. يحتوي على أسطر تتطابق سماتها مع قيم السطر الحالي: [[#This line]; [الشركة]]، [[#هذا الخط]؛ [الخدمة]]، [[#هذا السطر]؛ [الفترة]]، [[#هذا السطر]؛ [مجموع]].

ومع ذلك، فإن تعبير ALLEXCEPT ("Fact_Table"; "Fact_Table" [الشركة]; "Fact_Table" [الفترة]) يتطلب مسح عوامل تصفية السياق الحالية لجميع الأعمدة باستثناء [الشركة] و[الفترة]. اتضح أننا في الجدول الوسيط نترك فقط تلك الصفوف التي تتطابق قيم سمات [الشركة] و [الفترة] مع الصف الحالي. في الجدول الذي تمت تصفيته بهذه الطريقة، يتم تحديد السمة [Sum]، ثم يتم جمع قيمها.

للوهلة الأولى، قد تبدو الصيغة مربكة للغاية، ولكنها في الواقع تستنسخ فقط حسابات التعبير 1، التي ذكرناها في بداية المقال. ستكون القيم الموجودة في عمود "العائد" بمثابة تأكيد واضح، لأنها تتزامن مع عمود "Revenue_Amount" في الشكل. 2.

الآن دعونا نضيف عمودًا محسوبًا آخر إلى الجدول - "المستوى_التقني". وفيه، مقابل كل مبلغ محسوب في المرحلة الأولى، سنشير إلى القيمة الرقمية للمقياس من جدول “المستويات”. في الواقع، نحن بحاجة للحصول على القيم من جدول آخر في قاعدة “Fact_Table”. كما هو الحال مع الحسابات العادية في Excel، للقيام بذلك، ما عليك سوى تحديد الوظيفة المناسبة وتمرير معرف الكائن المطلوب كوسيطة.

دعنا ندخل الصيغة التالية في عمود "المستوى_التقني" (التعبير 5):

التعبير 5:
=CALCULATE(max("المستويات"[ابدأ]);filter(all("المستويات"[ابدأ]);"المستويات"[ابدأ]

في التعبير المقدم، يتم تطبيق وظيفة التجميع MAX() على الجدول الخارجي "المستويات". أولاً، تتم تصفيته وفقًا لقيود السياق الحالي - يتم ترك فقط تلك الصفوف التي يقل عددها عن الرقم الموجود في عمود العائد للصف النشط في الجدول.

أخيرًا، دعونا نضيف آخر عمود محسوب إلى جدولنا - "Dokh_Level". سنعرض فيه سطرًا باسم المستوى من جدول "المستويات". يتم تنفيذ مثل هذه العمليات باستخدام وظائف VLOOKUP في Excel وGLOOKUP (VLOOKUP وHLOOKUP). تحتوي لغة DAX على وظيفة تسمى LOOKUPVALUE، والتي تشبه عامل التشغيل VLOOKUP.

لذلك يكفي في عمود "مستوى_الدخل" أن تكتب تعبيرًا بسيطًا بالصيغة التالية (التعبير 6):

التعبير 6:
=LOOKUPVALUE("المستويات"[المستوى]; "المستويات"[البدء]; [المستوى_التقني])

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

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

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

يمكن العثور على تعليمات حول استخدام الوظيفة الإضافية PowerPivot ولغة DAX، بما في ذلك البرنامج التعليمي "تعلم أساسيات DAX في 30 دقيقة" على موقع ويب MS Office الرسمي، عبر الإنترنت.

office.microsoft

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

لنبدأ باستيراد البيانات.

1. قم بتنزيل البيانات النموذجية (ContosoV2) لهذا البرنامج التعليمي. قم باستخراج ملفات البيانات وحفظها في مكان مناسب، مثل مجلد التنزيلات أو مجلد المستندات.
2. افتح مصنفًا فارغًا في Excel.
3. يختار بيانات > تلقي البيانات الخارجية > من الوصول.
4. استعرض للوصول إلى المجلد الذي يحتوي على ملفات البيانات النموذجية وحدد ContosoSales.
5. انقر يفتح. بما أنك تتصل بملف قاعدة بيانات يحتوي على جداول متعددة، سيظهر مربع حوار اختيار الجدول، حيث يمكنك تحديد الجداول التي تريد استيرادها.

1. في مربع الحوار تحديد جدول، حدد خانة الاختيار السماح باختيار جدول متعدد.
2. حدد كافة الجداول وانقر فوق الزر نعم.
3. في علامة التبويب استيراد البيانات، حدد تقرير الجدول المحوريواضغط على الزر نعم.

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

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

عرض البيانات في PivotTable

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

1. قم بالتمرير خلال قائمة الحقول وابحث عن جدول FactSales.
2. حدد العمود SalesAmount. نظرًا لاحتوائه على بيانات رقمية، سيقوم Excel تلقائيًا بوضع SalesAmount في منطقة القيم.
3. من الجدول DimDate، اسحب العمود CalendarYear إلى منطقة الأعمدة.
4. من الجدول DimProductSubcategory، اسحب العمود ProductSubcategoryName إلى منطقة الصفوف.
5. من جدول DimProduct، اسحب عمود BrandName إلى منطقة الصفوف، وضعه ضمن الفئة الفرعية.

يجب أن يبدو PivotTable كما هو موضح أدناه.

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

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

إضافة جداول إضافية

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

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

1. افتح ورقة جديدة في الكتاب. سيتم تخزين بيانات إضافية.
2. يختار بيانات > تلقي البيانات الخارجية > من الوصول.
3. استعرض للوصول إلى المجلد الذي يحتوي على ملفات البيانات النموذجية وحدد ProductCategories. انقر فوق الزر يفتح.
4. في علامة التبويب استيراد البيانات، حدد طاولةواضغط على الزر نعم.

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

1. أضف ورقة أخرى.
2. من ملفات البيانات النموذجية، افتح Geography.xlsx، ثم ضع المؤشر في الحقل A1، ثم اضغط على CTRL+SHIFT+END لتحديد كافة البيانات.
3. انسخ البيانات إلى الحافظة.
4. الصق البيانات في ورقة العمل الفارغة التي أضفتها للتو.
5. يختار تنسيق كجدولمع أي نمط. يتيح لك تنسيق بياناتك كجدول منحها اسمًا، وهو أمر مفيد جدًا عند تحديد العلاقات في الخطوة التالية.
6. تأكد من تحديد مربع تنسيق الجدول الجدول مع الرؤوس. انقر فوق الزر نعم.
7. اسم الجدول الجغرافيا. حدد علامة تبويب العمل مع الجداول > البناءوأدخل الاسم الجغرافيا في حقل اسم الجدول.
8. أغلق الملف Geography.xlsx لإزالته من منطقة العمل.

إضافة بيانات المتجر

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

الآن هناك أربع أوراق. تحتوي الورقة 1 على جدول محوري، بينما تحتوي الورقة 2 على جدول محوري فئات المنتجات، تحتوي الورقة 3 على الجغرافيا، وتحتوي الورقة 4 على المتاجر. نظرًا لأنك قمت بالفعل بتسمية جميع الجداول، فإن الخطوة التالية - إنشاء العلاقات - ستكون أسهل بكثير.

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

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

1. حدد عنصر الجميعفي أعلى قائمة حقول PivotTable للاطلاع على القائمة الكاملة للجداول المتوفرة.
2. قم بالتمرير لأسفل القائمة. هناك جداول جديدة قمت بإضافتها للتو.
3. قم بتوسيع المتاجر.
4. اسحب عمود StoreName إلى منطقة عوامل التصفية.
5. لاحظ أن Excel سيطالبك بإنشاء العلاقة. يظهر هذا الإشعار لأنك استخدمت حقولاً في جدول غير مرتبط بنموذج.
6. انقر يخلقلفتح مربع الحوار إنشاء ارتباط.
7. في منطقة الجدول، حدد FactSales. يحتوي جدول FactSales من البيانات النموذجية على معلومات مفصلة عن المبيعات والتكلفة لشركة Contoso، بالإضافة إلى مفاتيح من جداول أخرى، بما في ذلك رموز المتجر الموجودة في ملف Stores.xlsx الذي تم استيراده في الخطوة السابقة.
8. في منطقة العمود (الخارجية)، حدد StoreKey.
9. في منطقة الجدول المرتبط، حدد المتاجر.
10. في منطقة العمود المرتبط (الأساسي)، حدد StoreKey.
11. انقر فوق الزر نعم.

ربط فئة فرعية من المنتج بفئة المنتج

1. في إكسيل، حدد بيانات > علاقة > يخلق.
2. في منطقة الجدول، حدد DimProductSubcategory.
3. في منطقة العمود (الخارجية)، حدد ProductCategoryKey.
4. في منطقة الجدول المرتبط، حدد Table_ProductCategory.accdb.
5. في منطقة العمود المرتبط (الأساسي)، حدد ProductCategoryKey.
6. انقر فوق الزر نعم.
7. أغلق مربع الحوار إدارة العلاقات.

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

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

1. الجميعلعرض الجداول الموجودة في نموذج البيانات.
2. قم بالتمرير لأسفل القائمة.
3. في منطقة الصفوف، احذف BrandName.
4. قم بتوسيع Table_DimProductCategories.accdb.
5. اسحب ProductCategoryName إلى منطقة الصفوف، وضعه فوق ProductSubcategory.
6. في منطقة حقول PivotTable، حدد نشيطبحيث سيتم استخدام الجداول التي استخدمتها للتو في PivotTable.

نقطة التفتيش: راجع ما تعلمته

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

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

التالي: عرض النموذج الخاص بك وتوسيعه باستخدام Power Pivot

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

ملحوظة.تتوفر الوظيفة الإضافية Power Pivot في Microsoft Excel 2013 في Office ProPlus.

يضيف محور القوةأن يلصق اكسلعن طريق تمكين الوظيفة الإضافية Power Pivot.

1. انتقل إلى علامة التبويب ملف > خيارات > الإضافات.
2. في الميدان يتحكميختار الوظائف الإضافية لـ COM> يذهب.
3. تفقد الصندوق مايكروسوفت أوفيس باور بيفوت في مايكروسوفت إكسل 2013، ثم انقر فوق الزر نعم.

تظهر علامة التبويب Power Pivot على الشريط.

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

1. في Excel، حدد Sheet3 لتنشيطه. تحتوي الورقة3 على جدول الجغرافيا الذي تم استيراده مسبقًا.
2. على الشريط، حدد محور القوة > إضافة إلى نموذج البيانات. عند هذه النقطة، سيتم إضافة جدول الجغرافيا إلى النموذج. سيتم أيضًا فتح الوظيفة الإضافية Power Pivot، والتي يمكنك استخدامها لإكمال الخطوات المتبقية من المهمة.
3. لاحظ أن نافذة Power Pivot تعرض كافة الجداول الموجودة في النموذج، بما في ذلك جدول الجغرافيا. انظر من خلال بعض الجداول. في هذه الوظيفة الإضافية، يمكنك عرض كافة البيانات الموجودة في النموذج.
4. في نافذة Power Pivot، ضمن عرض، حدد عرض الرسم البياني.
5. استخدم شريط التمرير لتغيير حجم المخطط حتى تتمكن من رؤية كافة الكائنات الموجودة في المخطط. لاحظ أن الجدولين غير متصلين ببقية الرسم التخطيطي: DimEntity وGeography.
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 بإضافة اسمه إلى الصيغة.

= - -

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

1. أعد تسمية العمود بالنقر بزر الماوس الأيمن فوق CalculatedColumn1 وتحديده إعادة تسمية العمود. اكتب الربح واضغط على ENTER.
2. الآن حدد جدول DimProduct.
3. يختار البناء > أعمدة > يضيف.
4. في شريط الصيغة أعلى الجدول، أدخل الصيغة التالية.

ذات صلة(فئات المنتجات)

ترجع الدالة RELATED قيمة من جدول مرتبط. في حالتنا، يحتوي جدول ProductCategories على أسماء فئات المنتجات التي ستحتاج إلى إضافتها إلى جدول DimProduct عند إنشاء التسلسل الهرمي الذي يتضمن معلومات الفئة.

1. عند الانتهاء من إدخال الصيغة، اضغط على ENTER لتأكيدها.

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

1. إعادة تسمية العمود. اكتب فئة المنتج واضغط على Enter.
2. يختار البناء > أعمدة > يضيف.
3. في شريط الصيغة أعلى الجدول، أدخل الصيغة التالية، ثم اضغط على Enter لتأكيدها.

ذات صلة(DimProductSubcategory)

1. أعد تسمية العمود بالنقر بزر الماوس الأيمن فوق CalculatedColumn1 وتحديده إعادة تسمية العمود. اكتب ProductSubcategory واضغط على ENTER.

إنشاء التسلسل الهرمي

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

1. في Power Pivot، انتقل إلى طريقة عرض المخطط. قم بتوسيع جدول DimDate لتسهيل التعامل مع حقوله.
2. اضغط مع الاستمرار على المفتاح CTRL وانقر فوق أعمدة CalendarYear وCalendarQuarter وCalenderMonth (ستحتاج إلى التمرير لأسفل في الجدول).
3. مع تحديد ثلاثة أعمدة، انقر بزر الماوس الأيمن فوق أحدها ثم انقر إنشاء التسلسل الهرمي. سيتم إنشاء عقدة التسلسل الهرمي الأصل، التسلسل الهرمي 1، في أسفل الجدول، وسيتم نسخ الأعمدة المحددة إلى التسلسل الهرمي كعقد فرعية.
4. أدخل التواريخ كاسم للتسلسل الهرمي الجديد.
5. أضف عمود FullDateLabel إلى التسلسل الهرمي. انقر بزر الماوس الأيمن فوق FullDateLabel وحدد أضف إلى التسلسل الهرمي. حدد نوع التاريخ. يحتوي عمود FullDateLabel على التاريخ بالتنسيق الكامل، بما في ذلك السنة والشهر واليوم. تأكد من ظهور عمود FullDateLabel في أسفل التسلسل الهرمي. لديك الآن تسلسل هرمي متعدد المستويات يتضمن السنة وربع السنة والشهر وأيام التقويم الفردية.
6. بينما لا تزال في طريقة عرض المخطط، حدد جدول DimProduct وانقر فوقه إنشاء التسلسل الهرميفي رأس الجدول. تظهر عقدة التسلسل الهرمي الأصل الفارغة في أسفل الجدول.
7. أدخل فئات المنتجات كاسم للتسلسل الهرمي الجديد.
8. لإنشاء عقد فرعية لتسلسل هرمي، اسحب عمودي ProductCategory وProductSubcategory إلى التسلسل الهرمي.
9. انقر بزر الماوس الأيمن فوق اسم المنتج وحدد أضف إلى التسلسل الهرمي. حدد فئات المنتجات.

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

1. ارجع إلى اكسل.
2. في الورقة1 (الورقة التي تحتوي على الجدول المحوري)، احذف الحقول الموجودة في منطقة الصفوف.
3. استبدلها بالتسلسل الهرمي الجديد لفئات المنتجات في DimProduct.
4. وبالمثل، استبدل CalendarYear في منطقة Columns بالتسلسل الهرمي للتواريخ من DimDate.

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

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

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

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

1. تأكد من تحديد طريقة عرض البيانات في Power Pivot.
2. من علامات التبويب الموجودة أسفل الشاشة، انقر بزر الماوس الأيمن فوق DimProductSubcategory وحدد.
3. كرر لفئات المنتجات.
4. افتح بيئة DimProduct.
5. انقر بزر الماوس الأيمن فوق الأعمدة التالية وحدد إخفاء في أدوات العميل.

• مفتاح المنتج
• ملصق المنتج
• الفئة الفرعية للمنتج

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

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

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

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

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

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

1. في جدول FactSales، حدد عمودًا مفتاح المبيعات.
2. في المنطقة الحساباتانقر فوق السهم لأسفل الموجود أسفل الزر جمع تلقائي > يفحص.
3. أعد تسمية الحقل المحسوب الجديد بالنقر بزر الماوس الأيمن فوق العمود عدد مفاتيح المبيعاتفي منطقة الحساب وتحديد الأمر إعادة تسمية. يدخل عددواضغط على ENTER.
4. في Excel، في الورقة1، في قائمة الحقول، حدد مبيعات الحقائقو اضغط عدد.

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

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

1. في جدول FactSales، في منطقة الحساب، حدد خلية فارغة. تلميح: من الأفضل وضع الحقول المحسوبة بدءًا من الخلية في الزاوية اليسرى العليا. هذا سيجعل العثور عليهم أسهل. يمكنك الانتقال إلى أي حقل محسوب في منطقة الحساب.
2. في شريط الصيغة، أدخل الصيغة التالية باستخدام التحسس الذكي: النسبة المئوية لجميع المنتجات:=/CALCULATE(), ALL(DimProduct))
3. اضغط على ENTER لتأكيد الصيغة.
4. في Excel على الورقة1 في قائمة الحقول في الجدول مبيعات الحقائقيختار النسبة المئوية لجميع المنتجات.
5. في جدول محوري، حدد أعمدة متعددة النسبة المئوية لجميع المنتجات.
6. على علامة التبويب بيتيختار رقم > تنسيق النسبة المئوية. لتنسيق أعمدة جديدة، استخدم منزلتين عشريتين بعد العلامة العشرية.

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

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

إنقاذ الوظيفة

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

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

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

تم تصميم تقارير Power View للعمل مع نماذج البيانات المشابهة للنموذج الذي أنشأته للتو. اقرأ المزيد حول العرض الغني الذي يوفره Power View في Excel: تمكين Power View في Excel 2013 وPower View: استكشاف بياناتك وتصورها وتقديمها.

حاول تحسين نموذج البيانات الخاص بك لإنشاء تقارير Power View أكثر فعالية في المستقبل عن طريق مراجعة البرنامج التعليمي التالي. تحسين نموذج البيانات لتقارير Power View.

مايكروسوفت اكسل 2019/2016. المستوى 6. ذكاء الأعمال باستخدام PowerPivot وPowerView وPowerMap

يتم التدريب على أحدث إصدار من برنامج Excel 2019!

هل سئمت من تحليل البيانات ويبدو الأمر صعبا بالنسبة لك؟ ستكون قادرًا على تحليل البيانات من مصادر خارجية مثل قواعد بيانات Access وخادم SQL ومكعب OLAP وملفات Excel وما إلى ذلك، وتحميلها في النموذج باور بيفوتمباشرة أو تمت معالجتها مسبقًا في PowerQuery. إذا لزم الأمر، قم بإنشاء اتصالات بين كل هذه المصادر، وقم بإجراء حسابات مختلفة باستخدام الحسابات البسيطة واستخدام الوظائف. لن تعتمد بعد الآن على المطورين، لأن... يمكنك إنشاء كافة التقارير بنفسك.

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

تعد PowerPivot وPowerQuery وPowerView وPowerMap من أدوات تحليل البيانات الحديثة القوية في Microsoft Excel!

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

يتم تدريس هذه الدورة فقط من قبل مدربين معتمدين من Microsoft!

يرجى ملاحظة البرنامج المطلوب لكل إصدار من إصدارات Excel!

ل 2010 :

1. مايكروسوفت باور بيفوت لبرنامج Excel

ل 2013 :

1. مايكروسوفت Power Query لبرنامج Excel
2. معاينة خريطة الطاقة لبرنامج Excel 2013
3. مايكروسوفت سيلفرلايت

ل 2016/2019

1. مايكروسوفت سيلفرلايت

مهم! هذه الوظائف الإضافية غير مدعومة في Excel لنظام التشغيل Mac.

تم تقديم الوظيفة الإضافية PowerPivot المجانية في Excel 2010. وهي تشتمل على ست أدوات مفيدة جدًا وتم إنشاؤها بواسطة مطورين مستقلين. في Excel 2013، تم دمج وظيفة PowerPivot، جنبًا إلى جنب مع نموذج البيانات، مباشرةً في جوهر البرنامج.

يوفر نموذج البيانات الذي قمنا بمراجعته إمكانية الوصول إلى بعض ميزات PowerPivot فقط، وللوصول إلى كافة الأدوات، ستحتاج إلى تثبيت الوظيفة الإضافية PowerPivot نفسها، المضمنة في Office 2013 Professional Plus. يمكنك أيضًا الحصول على الوظيفة الإضافية عن طريق الاشتراك في Office 365 Business. إذا كنت تستخدم Office 2013 Home and Student أو Standard، فسيظل PowerPivot متاحًا، ولكن نافذة PowerPivot مقفلة ويتم تعطيل بعض الميزات.

قم بتنزيل المذكرة بالصيغة أو التنسيق، والأمثلة بتنسيق (تحتوي على عدة ملفات Excel وقاعدة بيانات بتنسيق txt)

مزايا وعيوب PowerPivot ونموذج البيانات

لنبدأ بالنظر إلى الميزات الثلاثة الرئيسية لبرنامج PowerPivot وننظر إلى كيفية تنفيذها في كل إصدار من إصدارات Excel.

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

استيراد 100 مليون صف إلى مصنف.يمكن أن تحتوي جداول PowerPivot على عدد غير محدود من الصفوف. لقد رأيت جداول مماثلة تتكون من 100 مليون صف. القيد الوحيد هو حجم ملف الكتاب، وهو 2 جيجابايت، ومقدار ذاكرة الوصول العشوائي. باستخدام خوارزمية ضغط VertiPaq، يتم ضغط ملف نصي بحجم 50 ميجابايت في ملف جدول PowerPivot بحجم 4 ميجابايت. إذا كان عدد الأعمدة في هذا الجدول هو 10، فسيكون عدد الصفوف 950 مليونًا. تتوفر هذه الميزة في كافة إصدارات Excel 2013. في Office Standard، يمكنك استيراد السجلات من جدول PowerPivot وإنشاء جداول محورية، ولكن لا يمكنك عرض السجلات. لإجراء المعاينة، يجب عليك تثبيت الوظيفة الإضافية PowerPivot. إن عدم القدرة على عرض سجلات PivotTable يمثل إزعاجًا خطيرًا لأنه قبل إنشاء أي تقرير، سيكون من الجيد مراجعة المعلومات المضمنة في هذا التقرير.

قم بإنشاء صيغ أفضل باستخدام لغة صيغة DAX.غير متوفر في الإصدارات القياسية من Excel 2013. للوصول إلى كافة الأدوات الموجودة في Excel 2013 والتي تتيح لك إنشاء صيغ جديدة في جداول PowerPivot وإضافة أعمدة محسوبة جديدة إلى الجداول المحورية، قم بتثبيت الوظيفة الإضافية PowerPivot. توفر لغة صيغة DAX للمستخدم مجموعة متنوعة من الإمكانات، على الرغم من أنه ليس من السهل تعلمها.

استفد من نموذج بيانات PowerPivot، المتوفر في جميع إصدارات Excel.فيما يلي عدد من المزايا الإضافية التي يوفرها نموذج البيانات:

• تمت إضافة وظيفة لحساب الإجماليات عدد العناصر المميزة (انظر مثلا). قبل أن تصبح هذه الميزة متاحة، كان عليك تحديد عدد مرات ظهور العناصر المختلفة يدويًا.
• يمكن أن تتضمن منطقة ملخص الجدول المحوري البيانات التي تمت تصفيتها فقط أو جميع البيانات. للبدء، عند إنشاء جدول محوري، قم بتضمين البيانات الموجودة في نموذج البيانات (الشكل 1). في PivotTable، حدد عامل تصفية أول 10...في مجال المجاميع، يتم تلخيص المؤشرات المقابلة للعشرة الأوائل (الشكل 2 أ). يمكنك الآن إنشاء إجمالي يتضمن مقاييس للعملاء ذوي مبالغ الطلبات الصغيرة التي تمت تصفيتها عند إنشاء التقرير. انتقل إلى علامة التبويب السياق البناءوفي القائمة المنسدلة المجاميع الفرعيةحدد الخيار قم بتضمين العناصر التي تمت تصفيتها في الإجماليات(الشكل 2 ب). يرجى ملاحظة أنه إذا لم تقم بإنشاء نموذج بيانات، فلن يكون خيار الإجمالي الفرعي هذا متاحًا للتحديد. لاحظ ذلك أيضًا في العنوان المجموع الإجماليتظهر علامة النجمة (*).
• تحتوي الجداول المحورية في Excel 2010 على أداة جديدة رائعة - المجموعات التي تسمح لك بإنشاء تقارير غير متماثلة. القيد الوحيد للمجموعات هو القدرة على استخدامها حصريًا عند العمل مع جداول OLAP المحورية (لمزيد من التفاصيل، راجع). ومع ذلك، إذا قمت بفتح جدول عادي باستخدام الوظيفة الإضافية PowerPivot، فسيتم تحويله تلقائيًا إلى جدول محوري OLAP.
• يمكن لأولئك الذين اعتادوا على استخدام الدالة لاستخراج البيانات من PivotTables اتخاذ خطوة واحدة وتحويل PivotTable إلى صيغ مكعبة. يمكن قص هذه الصيغ ولصقها بأي تنسيق تختاره.

أرز. 2. إدراج القيم التي تمت تصفيتها مسبقًا في النتائج: (أ) تتضمن النتائج دخل أفضل 10 عملاء؛ (ب) تتضمن النتائج الدخل لجميع العملاء، على الرغم من ظهور العشرة الأوائل فقط في الجدول الملخص

فوائد الوظيفة الإضافية PowerPivot في Excel Pro Plus.إذا كنت تستخدم إصدارًا من Excel Pro Plus يدعم وظيفة PowerPivot الإضافية، فستحصل على المزايا التالية:

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

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

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

الانضمام إلى جداول متعددة باستخدام نموذج بيانات في Excel 2013

لقد جلبت Microsoft أفضل ما في PowerPivot إلى Excel 2013. ويتوفر Office Pro Plus أيضًا مع وحدات PowerPivot وPower View وInquire كاملة الميزات. في الإصدارات القياسية من Excel 2013، يتم تنفيذ الإمكانات الأساسية لـ PowerPivot باستخدام نموذج بيانات. في التين. 1 يظهر مربع الحوار إنشاء جدول محوري. وجود مربع اختيار في هذه النافذة يعني أنك تتعامل مع PowerPivot. لمزيد من المعلومات حول دمج جداول متعددة في جدول محوري واحد باستخدام نموذج بيانات، راجع. تعمل هذه الميزة عند تثبيت PowerPivot وعند استخدام إمكانيات Excel المضمنة.

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

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

أرز. 4. لا يعد PivotTable الناتج مفيدًا جدًا للمديرين، ولكن في كثير من الحالات، سيساعد العرض السريع في إنشاء PivotTable أكثر إفادة

قم بإنشاء جدول محوري جديد بناءً على نموذج بيانات موجود.هذه العملية أكثر تعقيدًا قليلاً من العمل مع الجداول المحورية العادية:

1. اختر فريقا إدراج — جدول محوري.
2. في مربع الحوار إنشاء جدول محورياضبط المفتاح استخدام مصدر بيانات خارجي(الشكل 5 أ). حتى إذا تم تخزين البيانات في جدول PowerPivot الموجود في مصنف، تذكر أن PowerPivot كان في الأصل وظيفة إضافية خارجية.
3. انقر فوق الزر حدد الاتصال الاتصالات الموجودة.
4. حدد علامة تبويب الجداول.
5. حدد عنصر الجداول في نموذج بيانات المصنفوانقر يفتح(الشكل 5 ب).

حساب عدد العناصر المختلفة .إحدى ميزات الجداول المحورية هي حساب عدد السجلات الفريدة. يمكن لجداول Excel PivotTables حساب القيم النصية. يظهر مثال نموذجي لمثل هذا الجدول المحوري في الشكل. 6. في هذا الجدول المجال قطاعموجود في منطقة ROW، والحقول عميلو دخل- في منطقة VALUE. إجمالي عدد العملاء المعروضين بواسطة هذا الجدول المحوري هو 563، وهذا غير صحيح. في الواقع، نحن نتحدث في هذه الحالة عن 563 سجلًا غير فارغ يتوافق مع العملاء ويمكن تكراره. ويرجع هذا الحمل إلى القيود المفروضة على الجداول المحورية التقليدية.

إذا كان الجدول المحوري يستند إلى نموذج بيانات، فاتبع الخطوات التالية:

1. انقر بزر الماوس الأيمن على أي من الخلايا في المنطقة عدد العناصر في عمود العميل(النطاق B1:B9). حدد عنصر خيارات حقل القيمة.
2. فى الشباك خيارات حقل القيمةعلى علامة التبويب عمليةيختار عدد العناصر المميزة(لاحظ أنه بالمقارنة مع الجداول المحورية العادية لا توجد ميزات عملو فِهرِس). انقر فوق موافق.
3. يعرض الجدول المحوري قائمة تضم 27 عميلًا فريدًا، 11 منهم في هذا القطاع معدات(الشكل 7).

استخدام الوظيفة الإضافية PowerPivot في Excel 2013 Pro Plus

إذا كان إصدار Excel 2013 لديك يدعم وظيفة PowerPivot الإضافية، فيمكنك:

• يمكنك تحميل البيانات إلى PowerPivot بعدة طرق. لديك مصادر بيانات متعددة، وجداول مرتبطة، والقدرة على نسخ البيانات ولصقها، والقدرة على إنشاء الاشتراكات.
• يمكنك عرض البيانات وفرزها وتصفيتها في جداول PowerPivot.
• يمكنك استيراد ملايين الصفوف إلى ورقة عمل واحدة في جدول PowerPivot.
• يمكنك إنشاء صيغ DAX إما في جدول أو كحقل محسوب جديد يسمى يقيس. يشير الاختصار DAX إلى تعبيرات تحليل البيانات(تعبيرات تحليل البيانات). يتضمن DAX 117 دالة تسمح لك بإجراء نوعين من العمليات الحسابية. يمكنك استخدام 81 وظيفة Excel قياسية لإضافة أعمدة محسوبة إلى جدول في نافذة PowerPoint. ومع 54 وظيفة، يمكنك إنشاء مقاييس جديدة في الجداول المحورية. بفضل هذه الميزات، تكتسب جداول PivotTables إمكانات لم يسبق لها مثيل من قبل.
• لديك طرق إضافية لإنشاء العلاقات، بما في ذلك طريقة عرض الرسم التخطيطي التي يمكنك استخدامها لعرض العلاقات.
• يمكنك إخفاء أو إعادة تسمية الأعمدة.
• قبل إنشاء PivotTable، يمكنك تعيين تنسيق الأرقام للعمود.
• أصبح من الممكن الآن تعيين فئات للحقول، على سبيل المثال، جغرافية, عنوان URLوصلةعلىصورةأو رابط موقع.
• يمكنك تحديد مؤشرات الأداء الرئيسية أو التسلسلات الهرمية.
• إذا كانت شركتك قد قامت بتثبيت PowerPivot Server، فستتمكن من نشر تقارير PowerPivot التفاعلية على موقع SharePoint.

إذا كنت تخطط لمعالجة ملايين السجلات، فقم بتثبيت إصدارات 64 بت من Office وPowerPivot. في هذه الحالة، ستظل القيود المرتبطة بنقص ذاكرة الوصول العشوائي سارية، ولكن يتم تعويضها جزئيًا بحقيقة أن PowerPivot يمكنه ضغط البيانات المخزنة في ملف PowerPivot بما يقرب من 10 مرات. يسمح الإصدار 64 بت من Office 2013 بالوصول إلى الذاكرة التي تتجاوز الحد الأقصى البالغ 4 جيجابايت المحدد للإصدار 32 بت من Windows.

تفعيل الوظيفة الإضافية PowerPivot.إذا كنت تستخدم Office 365، أو Office 2013 Pro Plus، أو Office 2013 Enterprise، أو الإصدار المعبأ من Excel 2013، فستتمكن من الوصول إلى الوظيفة الإضافية PowerPivot. لتمكين هذه الوظيفة الإضافية، اتبع الخطوات التالية:

1. افتح Excel 2013. هل تريد رؤية علامة تبويب شريط PowerPivot (الشكل 8)؟ إذا كانت الإجابة بنعم، ليس عليك القيام بما يلي.
2. قم بتشغيل الأمر ملف — خيارات، حدد الوظائف الإضافية. في القائمة المنسدلة يتحكمالموجود في أسفل النافذة، حدد الوظائف الإضافية لـ COMوانقر على الزر يذهب(الشكل 9 أ).
3. في قائمة وظائف COM الإضافية المتوفرة، ابحث عن الوظيفة الإضافية Microsoft Office PowerPivot for Excel 2013 حدد المربع المناسب وانقر فوق OK (الشكل 9 ب). (يُرجى ملاحظة أنك تحتاج إلى الوظيفة الإضافية PowerPivot for Excel 2013. الوظيفة الإضافية PowerPivot for Excel القديمة المستخدمة في Excel 2010 غير مدعومة في Excel 2013.)
4. إذا لم تتمكن من رؤية علامة التبويب PowerPivot على الشريط، فقم بإنهاء Excel 2013 وتشغيله مرة أخرى.

بعد تثبيت الوظيفة الإضافية، ستظهر علامة التبويب PowerPivot على شريط Excel 2013 (الشكل 8).

استيراد ملف نصي.يتضمن الجدول المصدر 1.8 مليون سجل موجود في ملف BigDatal.txt (يظهر جزء من الملف في نافذة برنامج Notepad في الشكل 10). عناوين الأعمدة موجودة في السطر الأول من الملف. قد ترغب في إزالة الأسطر غير القياسية الموجودة في الجزء العلوي من الملف لتجنب حدوث مشكلات عند قيام PowerPivot بمعالجتها. لسوء الحظ، التواريخ الموجودة في الملف بالتنسيق الأمريكي، لذلك لا تتم معالجتها بشكل صحيح في المستقبل.

لاستيراد ملف يحتوي على 1.8 مليون صف إلى PowerPivot، اتبع الخطوات التالية:

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

أرز. 11. انقر فوق أيقونة إدارة في علامة التبويب PowerPivot في شريط Excel لفتح نافذة PowerPivot Add-in، التي تعرض الشريط الخاص بها

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

3. في النافذة معالج استيراد الجدولحدد العنصر الأدنى ملف نصي. انقر فوق الزر إضافي.

4. أدخل في هذا المجال اسم مألوف للاتصالاسم اتصالك.

5. انقر فوق الزر مراجعةوابحث عن الملف النصي. إذا كانت البيانات تتضمن رؤوسًا، فسيقوم PowerPivot باكتشافها.

6. تأكد من تحديد الفاصلة كمحدد. في القائمة المنسدلة فاصل العموديتم عرض عدد من المحددات القياسية، مثل الفاصلة، والفاصلة المنقوطة، والشريط العمودي، وما إلى ذلك.

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

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

9. بعد النقر على الزر مستعديبدأ PowerPivot بتحميل الملف إلى الذاكرة. يتم عرض عدد الصفوف المحملة حاليًا (الشكل 13).

10. بمجرد اكتمال استيراد الملف، يتم عرض عدد الصفوف التي تم تحميلها. انقر فوق الزر يغلقللعودة إلى نافذة PowerPivot.

11. تعرض نافذة PowerPivot 1.8 مليون سجل. يمكنك استخدام شريط التمرير العمودي لعرضها. يمكنك أيضًا فرز تنسيق الأرقام أو تغييره أو تطبيق مرشح (الشكل 14).

تعيين تنسيق أرقام للأعمدة. تم تصميم حقل التاريخ لتخزين التاريخ والوقت. إذا كانت البيانات المصدر لا تتضمن مكونًا زمنيًا، فحدد رأس التاريخ واستخدم القائمة المنسدلة شكلفي المجموعة التنسيقنوافذ التبويب إلى البدايةأشرطة PowerPivot. حدد التنسيق 14/03/2001. إلى العمود دخلتطبيق تنسيق العملة. إذا كنت لا تريد ظهور المنازل العشرية في PivotTable، فقم بتقليل عدد المنازل العشرية إلى الصفر.

يؤدي النقر بزر الماوس الأيمن فوق أحد الأعمدة إلى عرض قائمة السياق التي تسمح لك بإعادة تسمية الأعمدة وتجميدها ونسخها. استخدم هذه القائمة لإخفاء عمود من قائمة حقول PivotTable في Excel.

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

لتغيير عرض الأعمدة، اسحب الحد بين عناوين الأعمدة (كما في Excel).

إذن، لديك 1.8 مليون سجل يمكنك فرزها وتصفيتها وضمها إلى جداول محورية. لاحظ أنه يتم تخزين 1.8 مليون صف مستورد من الملف النصي في مصنف Excel. يمكنك نسخ ملف .xlsx إلى كمبيوتر جديد، وبعد ذلك ستنتهي جميع الأسطر على الكمبيوتر الجديد. يبلغ حجم الملف النصي الأصلي 58 ميجابايت، لكن حجم ملف Excel المضغوط هو 4 ميجابايت فقط.

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

لاستخدام عمليات النسخ واللصق، اتبع الخطوات التالية:

1. افتح المصنف الذي يحتوي على النطاق الذي يربط معرفات المتجر بالعناوين (الشكل 15).
2. حدد البيانات باستخدام مجموعة المفاتيح Ctrl+Shift*.
3. انسخ البيانات بالضغط على مجموعة المفاتيح Ctrl+C.
4. انتقل إلى علامة التبويب PowerPivot في Excel.
5. انقر فوق الزر يتحكملفتح نافذة PowerPivot. ستتمكن الآن من رؤية مجموعة البيانات المستوردة مسبقًا والتي تحتوي على 1.8 مليون صف.
6. على الجانب الأيسر من علامة التبويب إلى البدايةنافذة PowerPivot انقر على الأيقونة إدراج. سيظهر مربع حوار على الشاشة عرض التضمين.
7. قم بتسمية الجدول الجديد باسم أكثر وضوحًا من اسم الجدول الافتراضي، على سبيل المثال: المحلات(الشكل 16). انقر فوق موافق.

أرز. 16. نافذة عرض التضمين

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

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

1. للبدء، قم بتحويل البيانات (كما في الشكل 15) إلى جدول من خلال النقر على أي خلية في النطاق والضغط على Ctrl+T.
2. حدد علامة تبويب سياقية البناء. يعرض الجانب الأيسر من الشريط اسم الجدول الذي تم إنشاؤه حديثًا - الجدول 1. في هذا الحقل، أدخل اسمًا جديدًا، على سبيل المثال، Store_Code.
3. انتقل إلى علامة التبويب باور بيفوتوفي المجموعة الجداولانقر إضافة إلى نموذج البيانات. سيتم إنشاء نسخة من الجدول وعرضها في النافذة باور بيفوت(الشكل 18).

أرز. 18. جدول جديد في باور بيفوت Store_code

تعريف الاتصالات.عادةً ما يستخدم Excel الدالة VLOOKUP لربط جدولين. في PowerPivot، يتم حل هذه المهمة بشكل أسهل بكثير:

1. في نافذة PowerPivot، انتقل إلى إلى البدايةوانقر على الزر أداء الرسوم البيانية. سيتم عرض جدولين بجانب بعضهما البعض (الشكل 19).

2. انقر في هذا المجال شفرةفي الجدول الرئيسي (BigData)، اسحبه وحرره أثناء وجودك فوق الحقل رمز المتجرمنطقة المحلات. سوف تظهر الأسهم المقابلة للاتصال القائم.

3. للعودة إلى جدول PowerPivot، انقر فوق عرض بياناتتقع على علامة التبويب إلى البدايةنافذة او شباك باور بيفوت.

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

1. انتقل إلى الإشارة المرجعية باور بيفوت. انقر فوق الزر يتحكموفي النافذة التي تفتح، انقر فوق علامة تبويب ورقة العمل الأولى - مبيعاتالموجود في الجزء السفلي من النافذة باور بيفوت.
2. يسمى العمود الموجود في أقصى اليمين إضافة عمود. انقر فوق الخلية الأولى من هذا العمود الفارغ.
3. انقر على الأيقونة fxالموجود على الجانب الأيسر من شريط الصيغة. سيظهر مربع حوار على الشاشة إدراج وظيفة، والتي تشمل عددا من الفئات. اختر تصنيف التاريخ و الوقت. يرجى ملاحظة أن الميزات المعروضة في هذه القائمة تختلف عن الميزات الموجودة في الفئة التاريخ و الوقتفي اكسل.
4. قم بتمرير القائمة وحدد وظيفة سنةو اضغط نعم(الشكل 20). انقر على رأس العمود تاريخ الطلب. البنية الفوقية باور بيفوتيقترح الصيغة =YEAR([تاريخ الطلب). أكمل الصيغة عن طريق إدخال قوس الإغلاق والضغط يدخل. يقوم Excel بملء العمود بقيم السنة المرتبطة بالتاريخ.
5. انقر بزر الماوس الأيمن على العمود وحدد الخيار من قائمة السياق إعادة تسمية العمود. أدخل اسم عمود جديد، على سبيل المثال، سنة(الشكل 21).

إنشاء جدول محوري:

1. انتقل إلى علامة التبويب إلى البدايةشرائط باور بيفوت.قم بتوسيع القائمة الموجودة أسفل الزر جدول محوريوحدد جدول محوري(الشكل 22).
2. في النافذة التي تفتح، حدد زر الاختيار الذي يحدد كيفية إدراج الجدول المحوري في ورقة جديدة. سيتم إعادتك إلى نافذة Excel مرة أخرى. ستعرض قائمة حقول PivotTable جميع الجداول الموجودة فيها باور بيفوت.لعرض الحقول المضمنة في كل جدول، قم بتوسيع القائمة المقابلة للجدول (بالنقر على "المثلث").
3. في قائمة الحقول باور بيفوتافتح الطاولة البيانات الكبيرةوحدد الحقل دخل. قم بتوسيع الجدول المحلات o وحدد الحقل الموجود فيه منطقة. سيقوم برنامج Excel بإنشاء جدول محوري يعرض المبيعات حسب المنطقة (الشكل 23). لذلك، لديك جدول محوري تحت تصرفك، والذي تم إنشاؤه على أساس 1.8 مليون صف من البيانات ويحتوي على رابط افتراضي للجدول ذي الصلة.

أرز. 22. إنشاء جدول محوري في النافذة باور بيفوت

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

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

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

نوعان من حسابات DAX

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

استخدام دالات DAX على الأعمدة المحسوبة.تشبه هذه الوظائف إلى حد كبير وظائف Excel العادية، لذا فإن معظمها لا يحتاج إلى مزيد من الشرح. لكن بعض وظائف DAX تختلف عن وظائف Excel:

• تمت إعادة تسمية وظيفة Excel RAZNDAT التي نادرًا ما يتم ذكرها إلى YEARFRAC وتمت إعادة كتابة الكود الخاص بها. في الواقع، وظيفة RAZNDAT نفسها ليست موجودة في تعليمات Excel. علاوة على ذلك، فهو ليس موجودًا في معالج الوظائف أيضًا. وعند كتابة الأحرف الأولى من اسم الوظيفة يدويًا، لن يعرض Excel أيضًا تلميحًا في القائمة المنسدلة. وأوضح هذا بكل بساطة. هذه الوظيفة ليست في الأصل وظيفة Excel. إنه مدعوم ببساطة بواسطة Excel للتوافق مع أنظمة جداول البيانات الأخرى. جاءت هذه الوظيفة إلى Excel من جداول بيانات Lotus 1-2-3. انظر المزيد من التفاصيل.
• تمت إعادة تسمية الدالة Excel TEXT إلى FORMAT.
• تم استبدال الدالة SUMIFS بالدالة CALCULATE المحسنة.
• بدلاً من الدالة VLOOKUP، يتم استخدام الدالة RELATED الأبسط.
• قدمت DAX الدالة BLANK(). نظرًا لأن بعض وظائف التجميع قد تعتمد العمليات الحسابية على معلمات ALLN0NBLANKR0W أو FIRSTNONBLANK، يمكن استخدام الدالة BLANK() كوسيطة للدالة IF() لاستبعاد صفوف معينة من حسابات القياس.
• تمت إعادة تسمية الدالة SELECT إلى SWITCH. بينما تأخذ الدالة SELECT القيم الرقمية من 1 إلى 255 كوسيطات، يمكن برمجة الدالة SWITCH للعمل مع القيم الأخرى.

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

يستخدم هذا المثال جدول BigData الذي يتضمن حقلاً شفرة[متجر] و دخل(كمية المبيعات يوميا). غالبًا ما تستخدم تقارير المبيعات مؤشرًا مثل المبيعات لكل متر مربع من المساحة. في جدول مرتبط المحلاتهناك حقول رمز المتجرو منطقة تجارة. تحتوي مجموعة هذه الحقول على كافة البيانات المطلوبة لإجراء العمليات الحسابية.

لبدء إنشاء صيغة جديدة، انتقل إلى الجدول باور بيفوتوانقر في خلية فارغة من العمود إضافة عمود. اكتب علامة يساوي وانقر في خلية في العمود دخل. ابدأ بإدخال الصيغة باور بيفوت: = [الإيرادات]. أدخل حرف الشرطة المائلة لتمثيل القسمة. الآن أنت بحاجة للوصول إلى الميدان منطقة تجارة، الموجود في الجدول المحلات. ابدأ بإدخال دالة متعلق ب(. أدخل الأحرف القليلة الأولى من اسم الجدول - أماه. سيتم عرض قائمة الحقول في الجدول المحلات(الشكل 24). انقر مرتين في هذا المجال منطقة تجارة. أكمل الصيغة عن طريق كتابة قوس إغلاق والضغط على Enter. انقر بزر الماوس الأيمن على العمود وحدد الخيار من قائمة السياق إعادة تسمية العمود. أعط اسمًا جديدًا، على سبيل المثال. SalesNaKvM.

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

قم بإنشاء حقل محسوب في جدول محوري باستخدام صيغ DAX.تتمتع الحقول المحسوبة في DAX بالعديد من المزايا مقارنة بالحقول المحسوبة العادية. وتتمثل إحدى الميزات في أنه عند استخدام حقل مثل هذا، يتم تنفيذ العمليات الحسابية مرة واحدة في كل خلية في PivotTable الناتج. في التين. يوضح الشكل 25 جدولاً محوريًا تحتوي فيه الخلايا B5:C12 على قيم رقمية. سيتم حساب حقل DAX المحسوب الذي تقوم بإنشائه فقط للخلايا الرقمية البالغ عددها 16 خلية في PivotTable. وهذا أسرع بكثير من حساب القيم في 1.8 مليون خلية ثم إضافتها. نظرًا لتنسيق التاريخ الأمريكي في الملف المصدر BigData.txt، هناك عدد من التواريخ في النموذج باور بيفوتلا يتم عرضه بشكل صحيح، مما يؤدي إلى ظهور حقل (فارغ)من حيث التواريخ. قبل البدء في إنشاء الحقل المحسوب الأول، يجب أن تفهم عوامل التصفية.

أرز. 25. الجدول المحوري القائم على النموذج باور بيفوت

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

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

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

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

بمجرد إنشائه، يمكن استخدام الحقل المحسوب لإجراء العمليات الحسابية في المستقبل. الحقل المحسوب مبيعات المتجر(الشكل 28) يستخدم الصيغة التالية:

SUMX(BigData;BigData[الإيرادات])/[عدد المتاجر]

أرز. 28. الميدان مبيعات المتجريتم حسابه بناءً على حقل تم إنشاؤه مسبقًا عدد من المتاجر

لتبسيط بنية PivotTable، يمكنك إخفاء الحقول المبلغ حسب عمود الدخلو عدد من المتاجر، مغادرة الميدان مبيعات المتجر.

كيفية إلغاء التصفية القياسية.الآن دعونا نلقي نظرة على المشكلة التالية. في التين. 29، يتم تمييز الخلية C5. يؤدي تطبيق عوامل التصفية على هذه الخلية إلى عرض السجلات التي تطابق اسم التاجر آمبر وتاريخ البيع 01/06/2014. يستخدم هذا والأقسام التالية (حتى ورشة العمل) ملف Excel مثال التصفية.xlsx

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

لاحظ أنه تتم تصفية العمليات الحسابية التي يتم إجراؤها في الخلية C5 ضمنيًا لإظهار مبيعات Amber فقط. نحن بحاجة إلى إنشاء صيغة DAX التي ستقوم بإلغاء تصفية البيانات. ستتجاوز هذه الصيغة عامل التصفية الذي يعرض المنشورات التي تطابق Amber وتعرض المنشورات غير المرتبطة بـ Amber. يمكن لوظيفة DAX CALCULATE إزالة عوامل التصفية المثبتة وتطبيق عوامل تصفية أخرى. تشبه هذه الدالة الدالة SUMIFS، ولكنها توفر للمستخدمين المزيد من الخيارات.

وظيفة الحسابيستخدم لحساب عمود إجمالي مع مراعاة مرشح واحد أو أكثر. إذا للمجال بائعتعيين عامل تصفية، فإنه سيحل محل عامل التصفية المحدد مسبقًا لهذا الحقل. إذا قمت بإنشاء مرشح بائع=هيوز، سيتجاهل DAX عامل التصفية الضمني تلقائيًا بائع=العنبر، والذي سيعرض كافة الإدخالات المطابقة لـ Hughes. إذا كنت تواجه مشكلة في تعلم كيفية إنشاء صيغ DAX على الفور، فجرّب الأسلوب خطوة بخطوة. بدلاً من الصيغ متعددة الطوابق، قم بتكوين مجموعة تتكون من صيغ صغيرة. الصيغة التي تحسب حجم مبيعات هيوز (الشكل 30):

احسب([المبلغ حسب مبيعات العمود];Sls[بائع]= "هيوز")

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

قم بتعيين الحقول المحسوبة التالية:

حان الوقت الآن لتطبيق عامل التصفية على العمود بائعلإخفاء حصة هيوز من الجدول المحوري. ونتيجة لذلك، حصلنا على مبلغ مبيعات قدره 911 دولارًا أمريكيًا ليوم 2 يونيو (القيمة في الخلية H6، الشكل 31)، على الرغم من حقيقة أن حصة هيوز موزعة على البائعين الآخرين. قارن القيم لكل بائع على حدة والإجماليات حسب اليوم في الجداول في الشكل. 30 و31. لا يمكن تحقيق هذه النتيجة باستخدام الحقول المحسوبة التي تم إنشاؤها في PivotTable عادي.

دراسة الحالة: حساب النطاق السعري للسلع

يتم دائمًا حساب الحقول المحسوبة المحددة في PivotTable العادي عبر صفوف مجموعة البيانات الأصلية. حاول أحد المشتركين في موقع MrExcel حساب النطاق السعري للسلع باستخدام الصيغة الحد الأقصى (السعر) - الحد الأدنى (السعر)المحددة في جدول محوري عادي. عندما تقوم بإنشاء حقل محسوب في جدول محوري، ينظر إكسيل إلى كل صف من البيانات المصدر ويحسب القيمة القصوى في هذا الصف (الشكل 32). وبما أن كل صف يعرض قيمة سعر واحدة فقط، فإن الحد الأقصى للقيمة في كل صف من بيانات المصدر سوف يتوافق مع قيمة سعر واحدة. ويمكن قول الشيء نفسه عن الحد الأدنى للقيمة. بعبارة أخرى، الحد الأقصى – الحد الأدنى = السعر – السعرأي 0. ونتيجة لذلك، لكل منتج معروض في الجدول المحوري، سيكون الفرق بين أعلى وأدنى الأسعار صفرًا. تم الحصول على هذه النتيجة غير الصحيحة تمامًا بسبب القواعد المتأصلة في الحقول المحسوبة في الجداول المحورية العادية (الشكل 33).

استفد من قوة DAX لحل هذه المشكلة.

1. حدد مجموعة البيانات الأصلية. اضغط على Ctrl+T لتحويل مجموعة البيانات المحددة إلى جدول.
2. باستخدام مجموعة من علامات التبويب السياقية العمل مع الجداول (مصمم)قم بتسمية الجدول الجديد مبيعات.
3. قم بتشغيل الأمر إدراج — جدول محوريوحدد المربع أضف هذه البيانات إلى نموذج البيانات.
4. أضف حقلاً منتجإلى منطقة ROW.
5. أضف الحقل مرتين سعرإلى منطقة القيم.
6. في أسفل قائمة حقول الجدول المحوري، قم بتوسيع القائمة للحقل الأول السعر ثم حدد خيارات حقل القيمة. قم بتغيير الدالة الناتجة إلى الحد الأدنى.
7. وكذلك الأمر بالنسبة للمجال الثاني سعرحدد الوظيفة النهائية أقصى.
8. عند أداء الفقرات. 6 و7 ينشئان بالفعل حقلاً محسوبًا ضمنيًا. وتتم مناقشة هذه الحقول بالتفصيل في القسم التالي.
9. قم بتشغيل الأمر باور بيفوت — الحقول المحسوبة — إنشاء حقل محسوبلإنشاء حقل محسوب. إعطاء هذا الحقل اسما يتراوح. في نافذة إدخال الصيغة، أدخل اسم الجدول مبيعات. في قائمة الحقول، ابحث عن حقل المبيعات [الحد الأقصى في عمود السعر] واضغط على المفتاح<Таb>. أدخل علامة الطرح. أدخل المبيعات مرة أخرى. ابحث عن حقل المبيعات [الحد الأدنى في عمود السعر] واضغط على المفتاح<Таb>لإدراجه في الصيغة. الآن تصبح صيغة النطاق =[الحد الأقصى في عمود السعر]-[الحد الأدنى في عمود السعر].
10. لإضافة الصيغة إلى نموذج البيانات، انقر فوق موافق. بعد العودة إلى نافذة Excel، ابحث عن الحقل الجديد يتراوحفي الطاولة مبيعات. انقر على هذا الحقل. الآن يتم حساب النطاق السعري بشكل صحيح (الشكل 34).

قم بإنشاء حقول DAX المحسوبة عن طريق إضافة حقول إلى منطقة VALUES.بعد إضافة حقل سعرإلى منطقة VALUES واختيار الوظيفة الحد الأدنىيقوم PowerPivot تلقائيًا بإنشاء حقل DAX محسوب يعرض النتيجة. بهذه الطريقة لن تضطر إلى استخدام الصيغة =MINX('Sales','Sales'[Price]). يمكنك عرض الحقول المحسوبة الضمنية في نافذة PowerPivot باور بيفوت — يتحكموعلى علامة التبويب بالإضافة إلى ذلكانقر على الزر إظهار الحقول المحسوبة الضمنية. ستظهر حقول جديدة في السطور المعروضة أسفل النافذة (الشكل 35).

إذا قمت بتمرير مؤشر الماوس فوق الجزء السفلي من النافذة، فسترى تلميح أداة يقول "قام PowerPivot تلقائيًا بإنشاء هذا الحقل المحسوب عن طريق إضافة حقل إلى منطقة القيم في قائمة حقول Excel. الحقل للقراءة فقط وسيتم حذفه تلقائيًا عند حذف العمود."

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

لحساب النطاق السعري، يمكنك إنشاء صيغة DAX التالية:

ماكس ("المبيعات"؛ "المبيعات" [السعر]) -Minx ("المبيعات"؛ "المبيعات" [السعر])

استخدام وظائف الوقت

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

1. انتقل إلى مجموعة البيانات الأصلية. حدد العمود الذي يتضمن حقل التاريخ وانسخه.
2. انتقل إلى ورقة فارغة وأدخل التواريخ في العمود A.
3. مع تحديد العمود A، قم بتشغيل الأمر بيانات — إزالة التكراراتوانقر على الزر نعم.
4. إذا لزم الأمر، أضف أعمدة إضافية، مثل السنة ويوم الأسبوع ويوم الأسبوع والشهر والشهر (الشكل 36). توضح الصيغ الموضحة في أعلى الشكل كيفية حساب كل عمود.
5. تحويل نطاق من البيانات إلى جدول بالضغط على Ctrl+T.
6. باستخدام علامة التبويب السياقية البناءالمتعلقة بمجموعة من علامات التبويب السياقية العمل مع الجداول، حدد اسم التقويم للجدول الذي سيتم إنشاؤه.

ومن هذا القسم وحتى نهاية الملاحظة، يتم استخدام البيانات من ملف Excel مثال على Calendar.xlsx.

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

1. حدد خلية في جدول مبيعات.
2. على علامة التبويب باور بيفوتفي برنامج Excel، انقر على الأيقونة إضافة إلى نموذج البيانات.
3. حدد خلية في الجدول تقويم.
4. إضافة إلى نموذج البيانات.
5. ضمن علامة التبويب PowerPivot في Excel، انقر فوق يتحكم; اذهب الى النافذة باور بيفوت.

الآن دعونا نقوم بتنسيق هذين الجدولين في النافذة باور بيفوت:

1. في الطاولة مبيعاتحدد العمود تاريخ.
2. فى الشباك باور بيفوتحدد علامة التبويب إلى البداية. انتبه إلى المجموعة التنسيق. بشكل افتراضي، يتم تحديد التنسيق الصحيح بالفعل تاريخ، ولكن تنسيق التاريخ نفسه غير صحيح. قم بتوسيع القائمة شكلوحدد القيمة *14.03.2001. (هذا ليس خطأ؛ هذا هو الشكل الذي يبدو عليه عنصر القائمة المنسدلة.)
3. حدد العمود مبيعاتفي الطاولة مبيعات.
4. على علامة التبويب إلى البدايةحدد التنسيق عملة. لإزالة المنازل العشرية، انقر نقرًا مزدوجًا تقليل عدد المنازل العشرية.
5. حدد العمود مرة أخرى تاريخفي الطاولة مبيعات.
6. على علامة التبويب البناءانقر على الأيقونة إنشاء اتصال.
7. في مربع الحوار إنشاء اتصاليتم ملء الحقلين الأولين بالقيم مبيعاتو تاريخ. قم بتوسيع القائمة جدول البحث المرتبطوحدد تقويم. في القائمة المنسدلة عمود البحث ذو الصلةسوف تظهر القيمة تلقائيا تاريخ(الشكل 37). انقر نعملإنشاء اتصال.
8. حدد ورقة العمل تقويمفى الشباك باور بيفوت.
9. حدد عمودًا تاريخ.
10. انتقل إلى علامة التبويب إلى البدايةوقم بتغيير تنسيق التاريخ إلى *03/14/2001.
11. اختيار عمود تاريخ، انتقل إلى علامة التبويب البناء. قم بتوسيع القائمة وضع علامة كجدول التاريخوحدد وضع علامة كجدول التاريخ. سيظهر مربع حوار على الشاشة وضع علامة كجدول بيانات. تم تحديد الحقل الصحيح بالفعل في هذه النافذة تاريخ. انقر فوق الزر نعم. يعد هذا الإجراء ضروريًا لإنشاء عوامل التصفية حسب التاريخ في قائمة حقول الجدول المحوري. وتظهر النتيجة في الشكل. 39.

أرز. 37. إنشاء علاقة بين الجداول مبيعاتو تقويم

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

تقوم الجداول المحورية العادية التي تستخدم ذاكرة التخزين المؤقت القياسية بفرز جميع الحقول تلقائيًا بناءً على قوائم مخصصة. ولكن، للأسف، هذا الفرز غير متوفر لجداول PowerPivot المحورية. للتغلب على هذا:

1. فى الشباك باور بيفوتحدد الجدول تقويم.
2. حدد إحدى الخلايا في العمود يوم من أيام الأسبوع.
3. على علامة التبويب إلى البدايةفى الشباك باور بيفوتفي مجموعة الفرز والتصفيةانقر على الأيقونة الفرز حسب الأعمدة.
4. في مربع الحوار الترتيب حسب العمودحدد فرز يوم الأسبوع حسب رقم يوم الأسبوع (الشكل 38). انقر نعم.
5. حدد خلية في عمود شهر. كرر ص. 3 و 4، ولكن هذه المرة حدد فرز الأعمدة رقم شهر بعد شهر.

إنشاء جدول محوري بإمكانيات متقدمة.فى الشباك باور بيفوتحدد الجدول مبيعات. على علامة التبويب إلى البدايةقم بتوسيع القائمة جدول محوريوحدد جدول محوري. سيتم إعادتك إلى Excel وستظهر حقول الجدول في قائمة حقول PivotTable التقويم والجداول مبيعات. ضع مؤشر الماوس فوق الحقل تاريخوتوسيع القائمة. حدد عنصر مرشحات حسب التاريخ، والذي يسمح لك بالوصول إلى جميع مرشحات التاريخ المستخدمة في الجداول المحورية العادية (الشكل 39).

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

اتبع هذه الخطوات لإضافة حقول إلى PivotTable:

1. اسحب الحقل يوم من أيام الأسبوعمن الطاولة تقويمإلى منطقة العمود.
2. اسحب الحقل مبيعاتمن الطاولة مبيعاتإلى منطقة القيم.
3. حدد علامة تبويب سياقية تحليلوانقر على الأيقونة أدخل شريحة.
4. أضف شريحة ميدانية يوم من أيام الأسبوع.
5. في علامة التبويب السياق خياراتمجموعة من علامات التبويب السياقية أدوات القطعحدد للخيار أعمدةالقيمة 7.
6. بالنسبة للشريحة، حدد أيام الأسبوع فقط.

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

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

وظائف الوقت "الذكية".بعد الانتهاء من العمل التمهيدي، حان الوقت للتعامل بجدية مع وظائف الوقت الذكية. ابدأ بإعادة تنظيم الجدول المحوري الموضح في الشكل. 40:

1. في علامة التبويب السياق تحليلقم بتشغيل الأمر واضح — امسح الكل.
2. افتح الطاولة تقويمواسحب الحقل تاريخإلى منطقة ROW.
3. افتح الطاولة مبيعاتواسحب الحقل مبيعاتإلى منطقة القيم.
4. أضف شريحة ميدانية سنة.
5. في نافذة الشريحة سنةحدد 2015.

ستكون النتيجة جدول ملخص كما هو موضح في الشكل. 41. بلغت المبيعات في 2 يونيو 2015 937 دولارًا.

سنقوم الآن بإنشاء حقول DAX المحسوبة التي ستقارن حجم المبيعات في اليوم الحالي مع حجم المبيعات في نفس اليوم من العام السابق. لقد ذكرنا سابقًا أن هذه المهمة ليست بهذه البساطة، نظرًا لأن الخلية C9 تحتوي على مرشح مطبق عليها، مما يعرض السجلات بتاريخ 2015/06/2. لذلك، تحتاج إلى التراجع عن عامل التصفية في حقل التاريخ، ثم تطبيق عامل تصفية جديد يسمح لك بعرض السجلات من العام السابق.

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

لتحديد نفس اليوم قبل عام واحد بالضبط، استخدم الصيغة DATEADD('Calendar'[Date];–1;year). يمكن أن تكون الوسيطة الثالثة يومًا أو شهرًا أو سنة. عند البرمجة في Excel، يتم تعريفها على أنها متغيرات عامة يتم تحويلها إلى رمز رقمي عند تشغيل البرنامج)، بحيث لا يتم تضمينها بين علامتي اقتباس مثل الوسائط النصية.

يتم استخدام الدالة DATEADD لإنشاء العديد من الصيغ التي تحسب الفترات الزمنية. على سبيل المثال، لعرض حجم المبيعات اليومية التي حدثت قبل ثلاثة أشهر، استخدم الصيغة =DATEADD('Calendar"[Date];-3;month). ولا تنس أن الدالة DATEADD هي مجرد واحدة من 34 دالة ذكية » الوظائف المصممة للتعامل مع الوقت، على سبيل المثال، يتم استخدام دالة أخرى من هذه الفئة في صيغة DATESMTD ("التقويم" [التاريخ])، والتي تعرض جميع التواريخ حتى اليوم الحالي من الشهر.

لنفترض أن عمود التاريخ موجود في جدول في نموذج البيانات الخاص بك مبيعاتوفي الجدول تقويم. ستعمل وظائف الوقت "الذكية" دائمًا بشكل صحيح إذا استخدمت مرجعًا إلى حقل "التقويم" [التاريخ] وستعمل فقط في 10% من الحالات إذا قمت بالرجوع إلى حقل "المبيعات" [التاريخ]. إذا كنت تريد أن تشغل عقلك بمشكلة معقدة، فحاول إنشاء صيغة DAX مع الإشارة إلى حقل "المبيعات" [التاريخ] بعد التحقق من صحة بناء الجملة، تعرض الشاشة رسالة مبهجة تفيد بعدم وجود أخطاء، و ومع ذلك، يعرض الجدول المحوري نتائج غير صحيحة ولا يمكنني اكتشاف سبب هذا السلوك الغريب للصيغة، ولكن إذا تم تطبيق الصيغة على حقل "التقويم" [التاريخ]، فسيتم الحصول على النتائج الصحيحة.

لذلك، لديك وظيفة DATEADD تحت تصرفك، والتي تسمح لك بتحديد تاريخ بعد سنة واحدة بالضبط من التاريخ المحدد في صف الجدول المحوري. لعرض المبيعات لهذا التاريخ، استخدم الدالة CALCULATE، التي تتجاوز عوامل التصفية الضمنية الموجودة. لأن الصف 9 من PivotTable يستخدم عامل تصفية ضمني لعرض السجلات التي تطابق تاريخ 2 يونيو 2015، استخدم الصيغة التالية التي تتضمن الدالة CALCULATE:

احسب([المبلغ حسب مبيعات العمود];DATEADD('التقويم"[التاريخ];-1;سنة))

اتبع الخطوات التالية:

1. باور بيفوتوتشغيل الأمر الحقول المحسوبة — إنشاء حقل محسوب. سيتم فتح نافذة الحقل المحسوب(الشكل 42).
2. قم بتسمية الحقل المبيعاتالعام الماضي.
3. أدخل الصيغة
   =CALCULATE([المبلغ حسب مبيعات العمود];DATEADD('التقويم"[التاريخ];-1;سنة))
4. في مجموعة فئةاختر العملة. ثَبَّتَ منازل عشرية 0. اختر رمز -$ الإنجليزية (الولايات المتحدة الأمريكية).
5. انقر فوق الزر التحقق من الصيغةللتأكد من صحة الصيغة. إذا منعك تلميح الأداة الذي يظهر على الشاشة من رؤية نتائج التحقق من الصيغة، فانقر فوق المربع وصف. انقر نعملإنهاء إنشاء الحقل المحسوب.
6. سيتم إعادتك إلى نافذة Excel. انقر في قائمة حقول الجدول المحوريفي الطاولة تقويمفي الميدان المبيعاتالعام الماضي. تتم إضافة الحقل إلى منطقة القيم.

لإكمال التحليل، قم بإضافة حقل محسوب ثانٍ سيتم استخدامه لحساب اتجاه التغيير بالنسبة المئوية. لكي تفعل هذا، اتبع هذه الخطوات:

1. في نافذة Excel، حدد علامة التبويب باور بيفوتوتشغيل الأمر قابلة للحساب مجالات — إنشاء حقل محسوب.
2. قم بتسمية الحقل النسبة المئوية للتغير.
3. أدخل الصيغة =[المجموع حسب مبيعات العمود]/[SalesLastYear]–1.
4. في مجموعة فئةحدد الرقم. ثَبَّتَ منازل عشرية 1. اختر التنسيق - النسبة المئوية.
5. كرر ص. 5 و 6 من التعليمات السابقة.

يظهر الجدول الملخص الناتج في الشكل. 43.

يمكنك الآن إزالة الحقول من PivotTable مبيعاتو المبيعاتالعام الماضي، مغادرة الميدان النسبة المئوية للتغيروأضف اسم البائعين (الحقل تاجر) إلى منطقة العمود (الشكل 44). تُظهر كل خلية في الجدول المحوري ديناميكيات المبيعات مقارنة بالعام الماضي (غريب، لكنني لم أر أنه تم تحليل الديناميكيات اليومية).

أرز. 44. جدول محوري مع حقل محسوب النسبة المئوية للتغير

للحصول على الجدول الموضح في الشكل 44 اتبع الخطوات التالية:

1. تعرض الخلية G9 (وبعض الخلايا الأخرى) رسالة خطأ تفيد بعدم وجود مبيعات في العام السابق (وبالتالي القسمة على 0). انقر بزر الماوس الأيمن على أي خلية في الجدول المحوري وحدد من قائمة السياق خيارات الجدول المحوري(الشكل 45). انتقل إلى علامة التبويب التخطيط والشكلوحدد المربع لعرض الأخطاءوأدخل الرموز - - في الحقل المناسب.
2. قم بتمييز القيم الرقمية في الجدول المحوري (النطاق C9:H38). انتقل إلى علامة التبويب بيت، وقم بتشغيل الأمر تنسيق مشروط — مجموعات الأيقوناتوحدد مجموعة من مثلثين ومستطيل واحد. إذا أردت، قم بتحرير قواعد التنسيق الافتراضية في Excel.

لمزيد من المعلومات حول DAX، تفضل بزيارة مدونة Rob Colley http://www.powerpivotpro.com/ (لقد اشتريت كتاب Colley، لذا أتوقع قراءة رائعة).

استخدام مؤشرات الأداء الرئيسية

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

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

1. على علامة التبويب باور بيفوتاختر فريق مؤشرات الأداء الرئيسية - إنشاء مؤشر الأداء الرئيسي. سيظهر مربع حوار على الشاشة مؤشر الأداء الرئيسي.
2. قم بتوسيع القائمة الحقل الأساسي لمؤشرات الأداء الرئيسية (القيمة)وحدد أحد مقاييس DAX المحسوبة.
3. لتعيين القيمة المستهدفة، حدد المفتاح قيمه مطلقهوأدخل قيمة.
4. حدد أحد أنماط عتبة الحالة الأربعة (المنطقة 1 في الشكل 46).
5. حدد نمط الأيقونة (المنطقة 2 في الشكل 46).
6. يرجى ملاحظة أنه لا يمكنك سحب الحقول النصية، على الرغم من أن هذا قد يبدو ممكنًا للوهلة الأولى. أدخل قيمًا جديدة في الحقول النصية أعلى العتبات. بعد إدخال قيمة جديدة والضغط على مفتاح Tab، سينتقل حقل النص إلى الموقع المطلوب.
7. انقر على هذا البند الأوصافالموجود أسفل مربع الحوار لتغيير أسماء الحقول التي ستظهر في الخطوة 10.
8. انقر نعم.
9. افتح جدول التقويم في قائمة حقول PivotTable. بالقرب من الميدان النسبة المئوية للتغيرسيتم عرض أيقونة إشارة المرور.
10. انقر فوق علامة الزائد لعرض ثلاثة حقول: معنى, هدفو ولاية. اسحب الحقل ولايةإلى منطقة القيم. سيظهر رمز في PivotTable. الآن، بغض النظر عن الرمز المحدد في مربع الحوار، يعرض الجدول المحوري ثلاثة أيقونات مستديرة: الأحمر والأصفر والأخضر (الشكل 47).

تمت كتابة المذكرة بناءً على كتاب جيلين ألكسندر. . الفصل 10.

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

ولذلك نقدم لكم إضافة ل مايكروسوفت اكسل، والتي سوف تسمح لك بإنشاء جداول فائقة - محور القوة .

إذن ما هو PowerPivot؟

محور القوة- هذه إضافة إضافية لـ اكسل، مما يوسع الوظيفة الجداول المحورية (الجداول المحورية). يستخدم فقط لـ MS Office 2010. في الإصدار الجديد من Office 2013 باور بيفوتهو بالفعل عنصر لا يتجزأ اكسل.

كيفية تثبيت باور بيفوت؟

قبل التثبيت باور بيفوت، تحتاج (ل ويندوز إكس بي):

الغاء التثبيت مايكروسوفت أوفيس 2010

ثَبَّتَ SP3ل شبابيك XP

قم بتثبيت .NET Framework 4.0 وأدوات Visual Studio 2010 لـ Office Runtime

تثبيت تماما مايكروسوفت أوفيس 2010(مع كافة البرامج والأدوات المشتركة)

قم بتثبيت باور بيفوت

بعد تثبيت PowerPivot في Excel، تظهر علامة تبويب إضافية:

النقر نافذة باور بيفوتسيتم فتح نافذة منفصلة يمكنك من خلالها تحميل البيانات من مصادر مختلفة.

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

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

وأيضا، كما نرى، في جدول محوريتظهر حقول إضافية جديدة تسمى المرشحات المرئية (قطاعات العرض).

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

نقترح أيضًا مشاهدة مقطع فيديو حول أدوات التحليل الذاتي للبيانات باور بيفوت.