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

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

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

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

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

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

نحوه ایجاد خودکار PivotTables در Excel1

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

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

  • ورق اول: ماکرو
  • ورق دوم: داده ها

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

نحوه ایجاد خودکار PivotTables در Excel 2

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

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

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

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

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

تحت pivot_demo()

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

پایان فرعی

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

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

کم نور PSSheet به عنوان کاربرگ، DSsheet به عنوان کاربرگ
کم نور PvtCache به عنوان PivotCache
مبهم کردن PvtTable به عنوان PivotTable
Dim PvtRange As Range
تیره Last_Row به عنوان طولانی، Last_Col به عنوان طولانی
کم نور sht1 به عنوان نوع

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

  • صفحه پی اس: تب مقصد که در آن VBA یک محور ایجاد می کند.
  • DSsheet: برگه داده
  • PvtCache: حافظه پنهان محوری، محور را نگه می دارد.
  • جدول Pvt: شی PivotTable.
  • PvtRange: محدوده داده برای نقطه مرجع.
  • آخرین ردیف و آخرین_سرهنگ: آخرین سطر و ستون پر شده در برگه داده (DSsheet).
  • Sht1: این متغیر نوع است.

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

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

خطاها، هشدارها و پیام های غیر ضروری کد VBA شما را کند می کند. با سرکوب چنین پیام هایی می توانید روند را تا حد زیادی تسریع کنید.

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

بر خطا رزومه بعدی
با کاربرد
DisplayAlerts = نادرست.
به روز رسانی صفحه نمایش = نادرست.
انتهای روستا

جایی که:

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

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

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

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

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

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

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

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

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

برای هر sht1 در ActiveWorkbook.کاربرگ
اگر sht1.Name = “Pivot” سپس
sht1.Delete
پایان اگر
sht1 بعدی
“Worksheets.Add.Name = “Pivot

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

برای ذخیره مراجع Pivot و Data sheets باید متغیرهایی ایجاد شوند. آنها به عنوان میانبرهایی عمل می کنند که می توانید در بقیه کد خود مشخص کنید.

تنظیم PSheet = Worksheets (“Pivot”)
تنظیم DSheet = Worksheets (“داده”)

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

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

last_row = DSsheet . سلول ها (ردیف ها. تعداد، 1). پایان (xlUp). به صف شدن
Last_Col = DSsheet.Cells(1، Columns.Count) . پایان (xlToLeft). ستون
PvtRange = DSheet.Cells (1، 1) را تنظیم کنید. تغییر اندازه (آخرین ردیف، آخرین_ستون)

جایی که:

  • آخرین ردیف: متغیر برای ذخیره آخرین شماره خط پر شده یعنی. 9995
  • آخرین_سرهنگ: متغیر برای ذخیره آخرین شماره ستون پر شده یعنی. 21
  • PvtRange: PvtRange برای کل محدوده داده اعمال می شود

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

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

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

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

PvtCache = ActiveWorkbook را تنظیم کنید. PivotCaches. ایجاد کنید (SourceType:=xlDatabase، SourceData:=PvtRange). یک جدول محوری ایجاد کنید

(TableDestination:=PSheet.Cells (2، 2)، TableName:=”MUODemoTable”)
PvtTable = PvtCache.CreatePivotTable را تنظیم کنید

جایی که:

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

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

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

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

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

با ActiveSheet. جداول خلاصه (“MUODemoTable”). PivotFields (“منطقه”)
.Orientation = xlPageField
انتهای روستا

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

با ActiveSheet. PivotTables (“MUODemoTable”) . PivotFields (“زیررده”)
.Orientation = xlRowField
انتهای روستا

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

با ActiveSheet. جداول خلاصه (“MUODemoTable”). PivotFields (“وضعیت”)
.Orientation = xlColumnField
انتهای روستا

برای اضافه کردن مقادیر تجمعی:

با ActiveSheet .PivotTables(“MUODemoTable”) .PivotFields(“Sales”)
.Orientation = xlDataField
تابع = xlSum
انتهای روستا

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

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

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

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

9. کد Excel VBA را برای ایجاد محورهای خودکار اجرا کنید

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

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

آموزش کدگذاری خودکار جداول محوری

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

آخرین کلمه

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

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

نحوه تبدیل عکس ها به صفحه گسترده اکسل در اندروید و آیفون

نحوه ساخت هدر و اضافه کردن سربرگ و پاورقی در اکسل