كيفية إنشاء جدول محوري باستخدام vba؟

قبل أن أقدم لك هذا الدليل وأبدأ في استخدام VBA لإنشاء جدول محوري ، دعني أخبرك بشيء.

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

منذ ذلك الحين، تعلمت من الترميز السيئ أكثر من الأكواد التي تعمل بالفعل.

سأعرض لك اليوم طريقة بسيطة لأتمتة الجداول المحورية باستخدام كود الماكرو.

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

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

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

دون مزيد من اللغط، لنبدأ في كتابة كود الماكرو الخاص بنا لإنشاء جدول محوري.

8 خطوات بسيطة لكتابة كود الماكرو في VBA لإنشاء جدول محوري في Excel

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

تأكد من تنزيل هذا الملف من هنا للمتابعة.

1. أعلن عن المتغيرات

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

 'Declare Variables Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long

في الكود أعلاه أعلنا:

1. PSheet: لإنشاء ورقة لجدول محوري جديد.
2. DSheet: لاستخدامها كورقة فنية.
3. Pchache: يُستخدم كاسم لذاكرة التخزين المؤقت للجدول المحوري.
4. PTable: يُستخدم كاسم لجدولنا المحوري.
5. PRange: لتعيين نطاق البيانات المصدر.
6. LastRow وLastCol: للحصول على الصف والعمود الأخيرين في نطاق البيانات الخاص بنا.

2. قم بإدراج جدول بيانات جديد

قبل إنشاء جدول محوري، يقوم Excel بإدراج ورقة فارغة ثم يقوم بإنشاء جدول محوري جديد عليها.

وسوف يفعل الكود أدناه نفس الشيء بالنسبة لك.

سيقوم بإدراج ورقة عمل جديدة باسم “Pivot Table” قبل ورقة العمل النشطة وإذا كانت هناك بالفعل ورقة عمل بنفس الاسم، فسيتم حذفها أولاً.

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

 'Declare Variables On Error Resume Next Application.DisplayAlerts = False Worksheets("PivotTable").Delete Sheets.Add Before:=ActiveSheet ActiveSheet.Name = "PivotTable" Application.DisplayAlerts = True Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets("Data")

تأكد من استبدال أسماء أوراق العمل الموجودة في الكود بالأسماء الموجودة في بياناتك.

3. تعيين نطاق البيانات

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

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

 'Define Data Range LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

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

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

4. قم بإنشاء ذاكرة تخزين مؤقت محورية

في Excel 2000 والإصدارات الأحدث، قبل إنشاء جدول محوري، يجب عليك إنشاء ذاكرة تخزين مؤقت محورية لتعيين مصدر البيانات.

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

 'Define Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange). _ CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _ TableName:="SalesPivotTable")

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

يمكنك تغيير موضع الجدول المحوري عن طريق تعديل هذا الرمز.

5. قم بإدراج جدول محوري فارغ

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

هذا الكود سيفعل نفس الشيء:

 'Insert Blank Pivot Table Set PTable = PCache.CreatePivotTable _ (TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")

يقوم هذا الرمز بإنشاء جدول محوري فارغ ويسميه “SalesPivotTable”. يمكنك تغيير هذا الاسم من الكود نفسه.

6. أدخل حقول الصفوف والأعمدة

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

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

هنا هو الرمز:

 'Insert Row Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month") .Orientation = xlRowField .Position = 2 End With 'Insert Column Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone") .Orientation = xlColumnField .Position = 1 End With

لقد ذكرت في هذا الرمز السنة والشهر كحقلين. الآن إذا نظرت إلى الكود سترى أن رقم المركز موجود أيضًا. يحدد رقم الموضع هذا تسلسل الحقول.

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

7. أدخل حقل بيانات

الشيء الرئيسي هو تحديد حقل القيمة في الجدول المحوري الخاص بك.

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

 'Insert Data Field With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Amount") .Orientation = xlDataField .Function = xlSum .NumberFormat = "#,##0" .Name = "Revenue " End With

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

نستخدم xlsum لإضافة القيم، ولكن يمكنك أيضًا استخدام xlcount ووظائف أخرى.

8. قم بتنسيق الجدول المحوري

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

باستخدام VBA، يمكنك تعيين نمط التنسيق في التعليمات البرمجية.

الكود هو:

 'Format Pivot TableActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"

سيطبق الكود أعلاه شرائط الخطوط ونمط “Pivot Style Medium 9″، ولكن يمكنك أيضًا استخدام نمط آخر من هذا الرابط .

وأخيرًا، الكود الخاص بك جاهز للاستخدام.

[الكود الكامل] استخدم VBA لإنشاء PivotTable في Excel – نسخ ولصق ماكرو

 Sub InsertPivotTable() 'Macro By Exceladvisor.org 'Declare Variables Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long 'Insert a New Blank Worksheet On Error Resume Next Application.DisplayAlerts = False Worksheets("PivotTable").Delete Sheets.Add Before:=ActiveSheet ActiveSheet.Name = "PivotTable" Application.DisplayAlerts = True Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets("Data") 'Define Data Range LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol) 'Define Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange). _ CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _ TableName:="SalesPivotTable") 'Insert Blank Pivot Table Set PTable = PCache.CreatePivotTable _ (TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable") 'Insert Row Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month") .Orientation = xlRowField .Position = 2 End With 'Insert Column Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone") .Orientation = xlColumnField .Position = 1 End With 'Insert Data Field With ActiveSheet.PivotTables("SalesPivotTable").PivotFields ("Amount") .Orientation = xlDataField .Function = xlSum .NumberFormat = "#,##0" .Name = "Revenue " End With 'Format Pivot Table ActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9" End Sub

جدول محوري في ورقة العمل الموجودة

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

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

لا تقلق؛ سأريكم.

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

ولهذا يجب عليك استخدام الكود أدناه:

بدلاً من إدراج ورقة عمل جديدة، يجب عليك تحديد اسم ورقة العمل في متغير PSheet.

 Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets(“Data”)

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

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

ما أقوله هو أنك بحاجة إلى إزالة هذا المحور أولاً.

للقيام بذلك، تحتاج إلى إضافة التعليمات البرمجية التي يجب أن تزيل المحور الذي يحمل نفس الاسم من ورقة العمل (إذا كان موجودًا) قبل إدراج واحدة جديدة.

إليك الرمز الذي تحتاج إلى إضافته:

 Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets(“Data”) Worksheets("PivotTable").Activate On Error Resume Next ActiveSheet.PivotTables("SalesPivotTable").TableRange2.Clear

دعني أخبرك بما يفعله هذا الرمز.

أولاً، يقوم فقط بتعيين PSheet كورقة العمل التي تريد إدراج الجدول المحوري فيها بالفعل في المصنف الخاص بك ويقوم بتعيين أوراق عمل البيانات على أنها DSheet.

بعد ذلك، يقوم بتنشيط ورقة العمل وإزالة “Sales PivotTable” منها.

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

في نهايةالمطاف،

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

هل سبق لك استخدام كود VBA لإنشاء جدول محوري؟

يرجى مشاركة آرائكم معي في مربع التعليق؛ أود مشاركتها معك ومشاركة هذه النصيحة مع أصدقائك.

مقالات مماثلة:

• إضافة أو إزالة الإجمالي الكلي في جدول محوري في Excel
• أضف الإجمالي الجاري إلى جدول محوري في Excel
• تحديث جدول محوري في Excel تلقائيًا
• إضافة حقل وعنصر محسوب
• حذف PivotTable في Excel