جدول محوري VBA | خطوات إنشاء جدول محوري في VBA
Excel VBA Pivot Table
تعد الجداول المحورية جوهر تلخيص تقرير كمية كبيرة من البيانات. يمكننا أيضًا أتمتة عملية إنشاء جدول محوري من خلال تشفير VBA. إنها جزء مهم من أي تقرير أو لوحة معلومات ، في التفوق ، من السهل إنشاء جداول بزر ولكن في VBA علينا كتابة بعض الرموز لأتمتة جدولنا المحوري ، قبل Excel 2007 وإصداراته الأقدم في VBA لم نكن بحاجة إلى ذلك إنشاء ذاكرة تخزين مؤقت للجداول المحورية ولكن في Excel 2010 والإصدارات الأحدث ، يلزم وجود ذاكرة تخزين مؤقت.
يمكن لـ VBA توفير الكثير من الوقت لنا في مكان العمل لدينا ، على الرغم من أن إتقانه ليس بهذه السهولة ولكنه يستحق قضاء الوقت لتعلم ذلك. لقد استغرقت 6 أشهر لفهم عملية إنشاء الجداول المحورية من خلال VBA. أنت تعرف ما فعلته تلك الأشهر الستة من المعجزات بالنسبة لي لأنني ارتكبت العديد من الأخطاء أثناء محاولتي إنشاء الجدول المحوري.
لكن الشيء الحقيقي هو أنني تعلمت من أخطائي وأنا الآن أكتب هذه المقالة لأوضح لك كيفية إنشاء جداول محورية باستخدام التعليمات البرمجية.
بنقرة زر واحدة ، يمكننا إنشاء تقارير.
خطوات إنشاء جدول محوري في VBA
يمكنك تنزيل VBA Pivot Table Template هنا - VBA Pivot Table Templateلإنشاء جدول محوري ، من المهم أن يكون لديك بيانات. لهذا قمت بإنشاء بعض البيانات الوهمية ، يمكنك تنزيل المصنف لمتابعة معي بنفس البيانات.
الخطوة 1: Pivot Table هو كائن للإشارة إلى الجدول المحوري يعلن عن المتغير كـ PivotTables.
رمز:
PivotTable الفرعي () خافت PTable كـ PivotTable End Sub
الخطوة 2: قبل إنشاء جدول محوري ، نحتاج أولاً إلى إنشاء ذاكرة تخزين مؤقت محورية لتحديد مصدر البيانات.
في الجدول المحوري لورقة العمل العادية دون أن يزعجنا ، سينشئ ذاكرة تخزين مؤقت محورية في الخلفية. ولكن في VBA علينا أن نخلق.
لهذا حدد المتغير PivotCache.
رمز:
خافت PCache باسم PivotCache
الخطوة 3: لتحديد نطاق البيانات المحورية ، حدد المتغير كنطاق.
رمز:
Dim PRange كمجموعة
الخطوة 4: لإدراج جدول محوري ، نحتاج إلى ورقة منفصلة لإضافة ورقة عمل للجدول المحوري للإعلان عن المتغير كورقة عمل.
رمز:
ورقة PS خافتة كورقة عمل
الخطوة 5: وبالمثل للإشارة إلى البيانات التي تحتوي على ورقة العمل ، قم بتعريف متغير آخر على أنه ورقة عمل.
رمز:
خافت DSheet كورقة عمل
الخطوة 6: أخيرًا للعثور على آخر صف وعمود مستخدمين ، حدد متغيرين آخرين على أنه طويل.
رمز:
خافت LR وطول خافت LC وطول
الخطوة 7: الآن نحتاج إلى إدراج ورقة جديدة لإنشاء جدول محوري. قبل ذلك ، إذا كانت هناك أي ورقة محورية ، فنحن بحاجة إلى حذفها.
الخطوة 8: الآن قم بتعيين متغير الكائن PSheet و DSheet على Pivot Sheet و Data Sheet على التوالي.
الخطوة 9: ابحث عن آخر صف مستخدم وآخر عمود مستخدم في ورقة البيانات.
الخطوة 10: الآن قم بتعيين النطاق المحوري باستخدام الصف الأخير والعمود الأخير.
سيؤدي ذلك إلى ضبط نطاق البيانات بشكل مثالي. سيحدد تلقائيًا نطاق البيانات حتى إذا كان هناك أي إضافة أو حذف للبيانات في ورقة البيانات.
الخطوة 11: قبل إنشاء جدول محوري ، نحتاج إلى إنشاء ذاكرة تخزين مؤقت محورية. قم بتعيين متغير ذاكرة التخزين المؤقت المحورية باستخدام رمز VBA أدناه.
الخطوة 12: الآن قم بإنشاء جدول محوري فارغ.
الخطوة 13: بعد إدخال الجدول المحوري ، نحتاج إلى إدخال حقل الصف أولاً. لذلك سأقوم بإدراج حقل الصف كعمود بلدي.
ملاحظة: قم بتنزيل المصنف لفهم أعمدة البيانات.
الخطوة 14: الآن سأدرج عنصرًا آخر في حقل الصف كعنصر الموضع الثاني. سأدرج المنتج كعنصر سطر ثانٍ في حقل الصف.
الخطوة 15: بعد إدخال الأعمدة في حقل الصف ، نحتاج إلى إدراج قيم في حقل العمود. سأقوم بإدخال "الجزء" في حقل العمود.
الخطوة 16: الآن نحن بحاجة إلى إدخال الأرقام في حقل البيانات. لذا أدخل "المبيعات" في حقل البيانات.
الخطوة 17: انتهينا من جزء ملخص الجدول المحوري ، والآن نحتاج إلى تنسيق الجدول. لتنسيق الجدول المحوري ، استخدم الكود أدناه.
لإظهار عناصر القيم المودعة في الصف في شكل جدولي ، أضف الكود أدناه في الأسفل.
حسنًا ، لقد انتهينا إذا قمنا بتشغيل هذا الرمز باستخدام مفتاح F5 أو يدويًا بعد ذلك ، يجب أن نحصل على الجدول المحوري مثل هذا.
مثل هذا باستخدام ترميز VBA ، يمكننا أتمتة عملية إنشاء جدول محوري.
للرجوع اليها ، لقد قدمت الرمز أدناه.
جدول PivotTable فرعي () خافت PTable كـ PivotTable Dim PCache مثل PivotCache Dim PRange كصفقة خافتة للنطاق مثل ورقة عمل خافتة لورقة عمل خافتة LR بصفتها طويلة خافتة LC طويلة عند الخطأ ، استئناف التطبيق التالي.DisplayAlerts = تطبيق خاطئ. Sheet "). حذف 'سيؤدي هذا إلى حذف أوراق عمل ورقة عمل الجدول المحوري الحالية. إضافة بعد: = ActiveSheet' سيؤدي هذا إلى إضافة ورقة عمل جديدة ActiveSheet.Name =" Pivot Sheet "'سيؤدي هذا إلى إعادة تسمية ورقة العمل باسم" Pivot Sheet "عند الخطأ GoTo 0 تعيين PSheet = Worksheets ("Pivot Sheet") Set DSheet = Worksheets ("Data Sheet") 'Find Last used row and Column in data sheet LR = DSheet.Cells (Rows.Count، 1) .End (xlUp) .Row LC = DSheet.Cells (1، Columns.Count). End (xlToLeft) .Column 'اضبط نطاق بيانات الجدول المحوري Set PRange = DSheet.Cells (1، 1) .Resize (LR، LC) 'Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create (xlDatabase، SourceData: = PRange)' إنشاء جدول محوري فارغ تعيين PTable = PCache.CreatePivotTable (TableDestination: = PSheet. الخلايا (1 ، 1) ، اسم الجدول: = "تقرير_المبيعات") 'أدخل البلد في الصف مع PSheet.PivotTables ("Sales_Report"). PivotFields ("Country") .Orientation = xlRowField .Position = 1 End With' Insert Product to تم حفظ الصف والموضع 2 مع PSheet.PivotTables ("Sales_Report"). PivotFields ("المنتج") .Orientation = xlRowField .Position = 2 End With 'Insert Segment to Column Filed & position 1 With PSheet.PivotTables ("Sales_Report"). PivotFields ("Segment") .Orientation = xlColumnField .Position = 1 End With 'أدخل عمود المبيعات في حقل البيانات باستخدام PSheet.PivotTables ("Sales_Report"). PivotFields ("Sales") .Orientation = xlDataField .Position = 1 End With 'Format Pivot Table PSheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True PSheet.PivotTables ("Sales_Rtyle2"). إظهار "PivotStyleMedium14" "في نموذج جدولي PSheet.PivotTables (" Sales_Report "). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub) .RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub) .RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
