يتضمن PivotTable العديد من التخطيطات التي توفر بنية معرفة مسبقا للتقرير، ولكن لا يمكنك تخصيص هذه التخطيطات. إذا كنت بحاجة إلى مزيد من المرونة في تصميم تخطيط تقرير PivotTable، فبإمكانك تحويل الخلايا إلى صيغ ورقة عمل، ثم تغيير تخطيط هذه الخلايا عن طريق الاستفادة الكاملة من كافة الميزات المتوفرة في ورقة العمل. يمكنك إما تحويل الخلايا إلى صيغ تستخدم دالات Cube أو تستخدم الدالة GETPIVOTDATA. ويبسط تحويل الخلايا إلى صيغ إلى حد بعيد عملية إنشاء جداول PivotTable المخصصة هذه وتحديثها وصيانتها.
عند تحويل الخلايا إلى صيغ، تصل هذه الصيغ إلى نفس بيانات PivotTable ويمكن تحديثها لرؤية نتائج محدثة. ومع ذلك، باستثناء عوامل تصفية التقارير المحتملة، لم يعد لديك حق الوصول إلى الميزات التفاعلية ل PivotTable، مثل التصفية أو الفرز أو توسيع المستويات وطويها.
ملاحظة
عندما تقوم بتحويل PivotTable للمعالجة التحليلية عبر الإنترنت (OLAP)، يمكنك الاستمرار في تحديث البيانات للحصول على قيم قياس محدثة، ولكن لا يمكنك تحديث الأعضاء الفعليين الذين يتم عرضهم في التقرير.
تعرف على السيناريوهات الشائعة لتحويل جداول PivotTable إلى صيغ ورقة عمل
فيما يلي أمثلة نموذجية لما يمكنك القيام به بعد تحويل خلايا PivotTable إلى صيغ ورقة عمل لتخصيص تخطيط الخلايا المحولة.
إعادة ترتيب الخلايا وحذفها
لنفترض أن لديك تقريرا دوريا تحتاج إلى إعداده كل شهر لموظفيك. إنك تحتاج فقط إلى مجموعة فرعية من معلومات التقرير وتفضل تخطيط البيانات بطريقة مخصصة. ما عليك سوى نقل الخلايا وترتيبها في تخطيط التصميم الذي تريده، وحذف الخلايا غير الضرورية للتقرير الشهري لفريق العمل، ثم تنسيق الخلايا وورقة العمل بما تتناسب مع تفضيلاتك.
إدراج الصفوف والأعمدة
لنفترض أنك تريد عرض معلومات المبيعات للعامين الماضيين مقسمة حسب المنطقة ومجموعة المنتجات، وأنك تريد إدراج تعليقات موسعة في صفوف إضافية. ما عليك سوى إدراج صف وإدخال النص. بالإضافة إلى ذلك، تحتاج إلى إضافة عمود يعرض المبيعات حسب المنطقة ومجموعة المنتجات غير الموجودة في PivotTable الأصلي. ما عليك سوى إدراج عمود، وإضافة صيغة للحصول على النتائج التي تريدها، ثم تعبئة العمود بالأسفل للحصول على النتائج لكل صف.
استخدام مصادر بيانات متعددة
لنفترض أنك تريد مقارنة النتائج بين قاعدة بيانات الإنتاج وقاعدة بيانات الاختبار للتأكد من أن قاعدة بيانات الاختبار تقدم النتائج المتوقعة. يمكنك بسهولة نسخ صيغ الخلايا ثم تغيير وسيطة الاتصال للإشارة إلى قاعدة بيانات الاختبار لمقارنة هاتين النتيجتين.
استخدام مراجع الخلايا لتغيير إدخال المستخدم
لنفترض أنك تريد أن يتغير التقرير بأكمله استنادا إلى إدخال المستخدم. يمكنك تغيير الوسيطات الخاصة بصيغ Cube إلى مراجع خلايا في ورقة العمل، ثم إدخال قيم مختلفة في تلك الخلايا للحصول على نتائج مختلفة.
إنشاء تخطيط صف أو عمود غير منتظم (يسمى أيضا التقارير غير المتماثلة)
لنفترض أنك تحتاج إلى إنشاء تقرير يحتوي على عمود في عام 2008 يسمى "المبيعات الفعلية"، وعمود عام 2009 يسمى "المبيعات المتوقعة"، ولكنك لا تريد أي أعمدة أخرى. يمكنك إنشاء تقرير يحتوي على هذه الأعمدة فقط، بخلاف PivotTable الذي يتطلب تقارير متماثلة.
إنشاء صيغ Cube وتعبيرات MDX خاصة بك
تخيل أنك تريد إنشاء تقرير يعرض مبيعات منتج معين بثلاثة مندوبي مبيعات محددين لشهر يوليو. إذا كنت على دراية بتعبيرات MDX واستعلامات OLAP، يمكنك إدخال صيغ Cube بنفسك. على الرغم من أن هذه الصيغ قد تصبح معقدة إلى حد ما، إلا أنه يمكنك تبسيط عملية إنشاء هذه الصيغ وتحسين دقتها باستخدام الإكمال التلقائي للصيغ. لمزيد من المعلومات، راجع استخدام الإكمال التلقائي للصيغ.
تحويل خلايا إلى صيغ تستخدم دالات Cube
ملاحظة
يمكنك تحويل PivotTable "معالجة تحليلية عبر الإنترنت" (OLAP) فقط باستخدام هذا الإجراء.
لحفظ PivotTable لاستخدامه في المستقبل، نوصي بإنشاء نسخة من المصنف قبل تحويل PivotTable عن طريق النقر فوق "حفظ باسم".> لمزيد من المعلومات، راجع حفظ ملف.
قم بإعداد PivotTable لكي تتمكن من تقليل إعادة ترتيب الخلايا بعد التحويل عن طريق القيام بما يلي:
- غير إلى تخطيط يشبه إلى حد كبير التخطيط الذي تريده.
- تفاعل مع التقرير، مثل تصفية التقرير وفرزه وإعادة تصميمه، للحصول على النتائج المرجوة.
انقر فوق PivotTable.
ضمن علامة التبويب "خيارات "، في المجموعة " أدوات "، انقر فوق أدوات OLAP، ثم انقر فوق "تحويل إلى صيغ".
في حال عدم وجود عوامل تصفية التقرير، تكتمل عملية التحويل. إذا كان هناك عامل تصفية تقرير واحد أو أكثر، يتم عرض مربع الحوار "تحويل إلى صيغ".حدد الطريقة التي تريد تحويل PivotTable بها:
تحويل PivotTable بالكاملحدد خانة الاختيار "تحويل عوامل تصفية التقرير ".
يحول هذا الخيار كافة الخلايا إلى صيغ ورقة عمل ويحذف PivotTable بأكمله.
قم بتحويل تسميات الصفوف وتسميات الأعمدة وناحية القيم في PivotTable فقط، مع الاحتفاظ بعوامل تصفية التقريرتأكد من إلغاء تحديد خانة الاختيار "تحويل عوامل تصفية التقرير ". (هذا هو الإعداد الافتراضي.)
يؤدي هذا إلى تحويل كافة تسميات الصفوف وتسميات الأعمدة وخلايا ناحية القيم إلى صيغ ورقة عمل والاحتفاظ بجدول PivotTable الأصلي، ولكن مع عوامل تصفية التقرير فقط حتى تتمكن من متابعة التصفية باستخدام عوامل تصفية التقرير.ملاحظة
إذا كان تنسيق PivotTable هو الإصدار 2000-2003 أو إصدار سابق، فيمكنك تحويل PivotTable بأكمله فقط.
انقر فوق تحويل.
تقوم عملية التحويل أولا بتحديث PivotTable لضمان استخدام البيانات المحدثة.
يتم عرض رسالة في شريط المعلومات أثناء حدوث عملية التحويل. إذا استغرقت العملية وقتا طويلا وكنت تفضل التحويل في وقت آخر، فاضغط ESC لإلغاء العملية.ملاحظة
- لا يمكنك تحويل الخلايا باستخدام عوامل التصفية المطبقة إلى مستويات مخفية.
- لا يمكنك تحويل الخلايا التي تحتوي الحقول بها على عملية حسابية مخصصة تم إنشاؤها من خلال علامة التبويب "إظهار القيم ك " في مربع الحوار "إعدادات حقول القيم ". (من علامة التبويب "خيارات "، في المجموعة " حقل نشط" ، انقر فوق "حقل نشط"، ثم انقر فوق "إعدادات حقل القيم").
- بالنسبة إلى الخلايا التي يتم تحويلها، يتم الاحتفاظ بتنسيق الخلية، ولكن تتم إزالة أنماط PivotTable لأنه يمكن تطبيق هذه الأنماط على PivotTables فقط.
تحويل خلايا باستخدام الدالة GETPIVOTDATA
يمكنك استخدام الدالة GETPIVOTDATA في صيغة لتحويل خلايا PivotTable إلى صيغ ورقة عمل عندما تريد العمل باستخدام مصادر بيانات غير OLAP، أو عندما تفضل عدم الترقية إلى تنسيق الإصدار 2007 الجديد من PivotTable مباشرة، أو عندما تريد تجنب التعقيدات المتعلقة باستخدام دالات Cube.
تأكد من تشغيل الأمر "إنشاء GETPIVOTDATA " في المجموعة PivotTable على علامة التبويب "خيارات ".
ملاحظة
يقوم الأمر "إنشاء GETPIVOTDATA" بتعيين الخيار "استخدام دالات GETPIVOTTABLE لمراجع PivotTable " أو إلغاء تحديده في الفئة "الصيغ" من المقطع "العمل باستخدام الصيغ" في مربع الحوار "خيارات Excel ".
في PivotTable، تأكد من أن الخلية التي تريد استخدامها في كل صيغة مرئية.
في خلية ورقة عمل خارج PivotTable، اكتب الصيغة التي تريدها حتى النقطة التي تريد تضمين البيانات من التقرير فيها.
انقر فوق الخلية الموجودة في PivotTable والتي تريد استخدامها في الصيغة في PivotTable. تتم إضافة دالة ورقة عمل GETPIVOTDATA إلى الصيغة التي تقوم باسترداد البيانات من PivotTable. تستمر هذه الدالة في استرداد البيانات الصحيحة إذا تم تغيير تخطيط التقرير أو إذا قمت بتحديث البيانات.
انته من كتابة الصيغة واضغط على ENTER.
ملاحظة
إذا قمت بإزالة أي من الخلايا المشار إليها في صيغة GETPIVOTDATA من التقرير، سترجع الصيغة #REF!.