روش ساخت جداول محوری (pivot tables) در اکسل با VBA بطور خودکار

غزاله شهسواری
روش ساخت جداول محوری (pivot tables) در اکسل با VBA بطور خودکار آموزش vba در اکسل؛ روش ساخت جداول محوری (pivot tables) در اکسل از طریق برنامه نویسی و از طریق VBA به طور خودکار ؛ آموزش داده سازی و ساخت دیتابیس در اکسل در سلام دنیا بیاموزید.

جداول محوری ابزاری قدرتمند برای تجسم داده ها هستند، اما تنظیم آنها ممکن است دشوار باشد. با یک اسکریپت VBA، ناامیدی را از این فرآیند خارج کنید.جداول محوری در اکسل برای آسان‌تر کردن و درک داده‌ها بسیار مهم هستند. یک جدول محوری می‌تواند داده‌ها را به ساختارهای معنی‌دار متراکم و خرد کند. کاربران MS Excel به طور گسترده آنها را در صنعت داده پذیرفته اند. آیا می دانستید که می توانید جداول محوری خود را در اکسل خودکار کرده و با یک کلیک آنها را ایجاد کنید؟ MS Excel به خوبی با VBA ادغام می شود و به ابزاری عالی برای خودکارسازی کارهای تکراری تبدیل شده است. در این مطلب از سلام دنیا درباره نحوه خودکار ساخت جداول محوری با ماکرو در MS Excel VBA آورده شده است. با ما همراه باشید.

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

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

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

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

حتما بخوانید: 50 کلید میانبر در اکسل 

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

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

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

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

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

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

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

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

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

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

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

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

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

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: آخرین سطر و ستون پر شده در برگه داده (DSheet).
  • Sht1: این متغیر یک نوع است.

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

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 ارجاع می دهد

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

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

جایی که:

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

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

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: برای اضافه کردن ستون.

حتما بخوانید: روش استفاده از تابع COUNT برای شمارش سلول های پر در اکسل

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

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

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

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

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

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

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

سخن آخر

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

مقالات مرتبط:

آموزش تبدیل عکس به جدول اکسل در اندروید و آیفون 

آموزش ساخت هدر و افزودن سرصفحه و پاصفحه در اکسل



از
3
رای