آموزش ایجاد خودکار جداول محوری در اکسل با VBA

جداول محوری ابزاری قدرتمند برای تجسم داده ها هستند، اما تنظیم آنها ممکن است دشوار باشد. با یک اسکریپت VBA، ناامیدی را از این فرآیند خارج کنید.

جداول محوری در اکسل برای آسان‌تر کردن درک و درک داده‌ها بسیار مهم هستند. یک جدول محوری می‌تواند داده‌ها را به ساختارهای معنی‌دار متراکم و خرد کند. کاربران MS Excel به طور گسترده آنها را در صنعت داده پذیرفته اند.

آیا می دانستید که می توانید جداول محوری خود را در اکسل خودکار کرده و با یک کلیک آنها را ایجاد کنید؟ MS Excel به خوبی با VBA ادغام می شود و به ابزاری عالی برای خودکارسازی کارهای تکراری تبدیل شده است.

در اینجا نحوه خودکارسازی جدول محوری با ماکرو در MS Excel VBA آورده شده است.

از مجموعه داده های تمرینی استفاده کنید

برای دنبال کردن اسکریپت VBA در این راهنما می‌توانید یک مجموعه داده ساختگی را از Tableau دانلود و استفاده کنید. کد VBA با هر مجموعه داده دیگری با چند ترفند اساسی کار می کند. قبل از شروع، مطمئن شوید که ماکروها را در کتاب کار اکسل خود فعال کرده اید.

چند ستون ضروری وجود دارد که می توانید در جدول محوری استفاده کنید. برای درک تفاوت های ظریف جدول و ساختار نهایی، می توانید یک جدول محوری پایه را به صورت دستی با عناصر زیر ایجاد کنید:

• فیلتر: منطقه
• ردیف: زیر مجموعه
• ستون ها: ایالت
• ارزش ها: فروش

محور انتهایی باید به صورت زیر باشد:

با این حال، می‌توانید به VBA اجازه دهید به‌جای آماده‌سازی دستی، این کار را به‌طور خودکار انجام دهد.

نحوه ایجاد خودکار جداول محوری در اکسل

برای خودکار کردن جداول محوری خود با VBA، یک فایل اکسل جدید باز کنید و نام برگه ها را به صورت زیر تغییر دهید:

• برگه اول: ماکرو
• برگه دوم: داده ها

صفحه ماکرو حاوی اسکریپت ماکرو است، در حالی که برگه داده حاوی داده های شما است. در صفحه ماکرو می توانید هر شکل دلخواه را وارد کنید و ماکرو را به آن اختصاص دهید. روی شکل کلیک راست کرده و روی Assign Macro کلیک کنید.

در کادر محاوره ای زیر، روی نام ماکرو خود کلیک کرده و Ok را بزنید. این مرحله ماکرو را به شکل اختصاص می دهد.

1. ویرایشگر کدنویسی Excel VBA را باز کنید

Alt + F11 را فشار دهید تا ویرایشگر کد باز شود. هنگامی که وارد ویرایشگر کد شدید، روی نام فایل کلیک راست کرده و سپس Insert و Module را انتخاب کنید. مهم است به خاطر داشته باشید که قبل از اجرای ماژول، تمام کدهای VBA را در داخل یک ماژول می نویسید.

استفاده از نام ماژول که با هدف کد همخوانی دارد، تمرین خوبی است. از آنجایی که این یک نسخه آزمایشی است، می توانید نام ماژول را به صورت زیر تعریف کنید:

sub pivot_demo()

نام ماژول با End Sub خاتمه می یابد، که دستور پایانی یک ماژول است:

End Sub

2. متغیرها را اعلام کنید

در داخل ماژول، با اعلان متغیرها شروع کنید تا مقداری از مقادیر تعریف شده توسط کاربر را که در اسکریپت استفاده می کنید ذخیره کنید. برای اعلان متغیرها می توانید از دستور Dim به صورت زیر استفاده کنید:

Dim PSheet As Worksheet, DSheet As Worksheet
Dim PvtCache As PivotCache
Dim PvtTable As PivotTable
Dim PvtRange As Range
Dim Last_Row As Long, Last_Col As Long
Dim sht1 as Variant

شما از این متغیرها برای موارد زیر استفاده خواهید کرد:

• PSsheet: برگه مقصد، جایی که VBA یک محور ایجاد می کند.
• DSsheet: برگه داده.
• PvtCache: یک حافظه پنهان محوری، محور را نگه می دارد.
• PvtTable: شی جدول محوری.
• PvtRange: یک محدوده داده برای pivot.
• Last Row و Last_Col: آخرین سطر و ستون پر شده در برگه داده (Sheet).
• Sht1: این متغیر یک نوع است.

3. هشدارها و پیام ها را سرکوب کنید

خطاها، اخطارها و پیام های غیر ضروری کدهای VBA شما را کند می کنند. با سرکوب چنین پیام هایی می توانید روند را به میزان قابل توجهی افزایش دهید.

از کد زیر استفاده کنید:

On Error Resume Next
 
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

جایی که:

• On Error Resume Next: این بند هرگونه خطای زمان اجرا را سرکوب می کند.
• کاربرد: برنامه به MS Excel اشاره دارد.
• DisplayAlerts: ویژگی DisplayAlerts مشخص می کند که آیا هشدارها نمایش داده شود یا خیر.
• ScreenUpdating: این ویژگی مشخص می کند که آیا تغییرات به صورت بلادرنگ به روز شوند یا فقط پس از اتمام اجرای کد.

هنگامی که این کد اجرا می شود، هر گونه هشدار، هشدار و پیامی را که اکسل در غیر این صورت نشان می دهد، سرکوب می کند. می توانید پارامترهای DisplayAlerts و ScreenUpdating را با تنظیم مقادیر آنها روی False خاموش کنید.

در انتهای کد، می‌توانید با تنظیم مقدار True، دوباره آن‌ها را روشن کنید.

4. هر برگه محوری موجود را حذف کنید

برای ایجاد یک جدول محوری جدید، دو گزینه دارید. ابتدا برگه محوری موجود را حذف کنید و از VBA برای ایجاد یک صفحه جدید برای ذخیره محور استفاده کنید. از طرف دیگر، می توانید از یک کاربرگ موجود برای نگه داشتن pivot استفاده کنید.

در این راهنما، اجازه دهید یک برگه محوری جدید برای ذخیره جدول محوری ایجاد کنیم.

حلقه for در هر برگه در کتاب کار می چرخد ​​و نام برگه را در متغیر sht1 ذخیره می کند. شما می توانید از هر نام متغیر (sht1) برای نگه داشتن نام برگه استفاده کنید. حلقه از طریق هر برگه در کتاب کار جاری می چرخد ​​و به دنبال یکی با نام خاص (Pivot) می گردد.

وقتی نام برگه مطابقت داشت، برگه را حذف می کند و به صفحه بعدی می رود. هنگامی که کد همه شیت ها را بررسی می کند، از حلقه خارج می شود و به قسمت بعدی کد منتقل می شود که یک صفحه جدید به نام Pivot اضافه می کند.

در اینجا نحوه انجام این کار آمده است:

For Each sht1 In ActiveWorkbook.Worksheets
    If sht1.Name = "Pivot" Then
        sht1.Delete
    End If
Next sht1
 
Worksheets.Add.Name = "Pivot"

5. منبع داده و برگه های محوری را تعریف کنید

ایجاد متغیرهایی برای ذخیره ارجاعات برگه های Pivot و Data ضروری است. اینها به عنوان میانبر کار می کنند، که می توانید در بقیه کدها به آنها مراجعه کنید.

Set PSheet = Worksheets("Pivot")
Set DSheet = Worksheets("Data")

6. آخرین ردیف و ستون استفاده شده را شناسایی کنید

این بخش از کد به صورت پویا کار می کند، زیرا آخرین سطر و ستون پر شده در داده ها را اندازه می دهد.

Last_Row = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
Last_Col = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PvtRange = DSheet.Cells(1, 1).Resize(Last_Row, Last_Col)

جایی که:

• Last_Row: متغیر برای ذخیره آخرین شماره ردیف پر شده، یعنی 9995
• Last_Col: متغیر برای ذخیره آخرین شماره ستون پر شده، یعنی 21
• PvtRange: PvtRange به کل محدوده داده برای pivot ارجاع می دهد

7. یک Pivot Cache و یک Pivot Table ایجاد کنید

کش محوری جدول محوری را نگه می دارد. بنابراین، قبل از ایجاد جدول محوری باید یک کش ایجاد کنید. شما باید از ارجاعات نحوی VBA برای ایجاد کش محوری در صفحه Pivot استفاده کنید.

با ارجاع به کش محوری، باید یک جدول محوری ایجاد کنید. به عنوان بخشی از جدول محوری، می توانید صفحه، مرجع سلول و نام جدول محوری را تعریف کنید.

Set PvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PvtRange).CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), TableName:="MUODemoTable")
Set PvtTable = PvtCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="MUODemoTable")

جایی که:

• ActiveWorkbook: کتاب کار فعلی که در آن برگه Data و Pivot را دارید.
• PivotCaches.Create: نحو پیش فرض برای ایجاد حافظه پنهان محوری.
• SourceType: از آنجایی که داده ها را در کتاب کار دارید، می توانید آن را به عنوان xlDatabase تعریف کنید. برخی از گزینه های دیگر عبارتند از xlConsolidation، xlExternal یا xlPivotTable.
• SourceData: می توانید محدوده محوری قبلی را به عنوان داده منبع ارجاع دهید.
• CreatePivotTable: دستور پیش فرض برای ایجاد جدول محوری.
• TableDestination: باید برگه و مراجع سلولی را که می خواهید pivot ایجاد کنید، مشخص کنید.
• TableName: نام جدول محوری را مشخص کنید.
• CreatePivotTable: دستور پیش فرض برای ایجاد جدول محوری در حافظه نهان محوری.

8. ردیف ها، ستون ها، فیلترها و مقادیر را وارد کنید

از آنجایی که جدول محوری آماده است، باید شروع به اضافه کردن پارامترها در فیلترها، ردیف‌ها، ستون‌ها و مقادیر تجمیع کنید. می توانید از دستور VBA pivotfields برای شروع به اعلام جزئیات استفاده کنید.

برای افزودن مقادیر فیلتر:

With ActiveSheet.PivotTables("MUODemoTable").PivotFields("Region")
.Orientation = xlPageField
End With 

برای افزودن مقادیر ردیف:

With ActiveSheet.PivotTables("MUODemoTable").PivotFields("Sub-Category")
.Orientation = xlRowField
End With

برای افزودن مقادیر ستون:

With ActiveSheet.PivotTables("MUODemoTable").PivotFields("State")
.Orientation = xlColumnField
End With

برای افزودن مقادیر تجمعی:

With ActiveSheet.PivotTables("MUODemoTable").PivotFields("Sales")
.Orientation = xlDataField
.Function = xlSum
End With

توجه به این نکته ضروری است که باید به صفحه فعال (Pivot sheet) و به دنبال آن نام جدول محوری و نام متغیر اشاره کنید. هنگامی که نیاز به افزودن فیلتر(ها)، سطر(ها و ستون ها) دارید، می توانید بین دستورهای مختلفی جابجا شوید که شامل موارد زیر است:

• xlPageField: برای افزودن فیلترها.
• xlRowField: برای افزودن ردیف.
• xlRowField: برای اضافه کردن ستون.

در نهایت، می‌توانید از دستور xlDataField برای محاسبه تجمیع مقادیر استفاده کنید. می توانید از توابع جمع دیگری مانند xlSum، xlAverage، xlCount، xlMax، xlMin و xlProduct استفاده کنید.

9. اجرای کد VBA اکسل برای ایجاد محورهای خودکار

در نهایت، زمانی که کل برنامه آماده شد، می توانید آن را با فشردن F5 یا کلیک بر روی دکمه پخش اجرا کنید. وقتی به برگه Pivot در کتاب کار خود برگردید، خواهید دید که یک جدول محوری جدید برای بررسی آماده است.

اگر می خواهید اجرای گام به گام نحوه اجرای دستور کد را خط به خط مشاهده کنید، می توانید به ویرایشگر کد بروید و F8 را چندین بار فشار دهید. به این ترتیب، می توانید ببینید که هر خط کد چگونه کار می کند و VBA چگونه محورهای شما را به طور خودکار ایجاد می کند.

آموزش کدنویسی خودکار جداول محوری

Pivot ها فقط به MS Excel محدود نمی شوند. زبان های برنامه نویسی مانند پایتون به شما اجازه می دهند که محورهای بهینه سازی شده را تنها با چند خط کد ایجاد کنید.

بهینه سازی داده ها نمی تواند ساده تر از این باشد. شما می توانید به طور موثر دستورات خود را در پایتون انتخاب و انتخاب کنید و ساختار محوری مشابه اکسل را به راحتی انجام دهید.

منبع مقاله