چگونه در اکسل Pivot Table به صورت خودکار ایجاد کنیم ؟

Pivot Table در اکسل را می‌توان جدولی محوری توصیف کرد برای آسان‌سازی درک داده‌ها ساخته شده است. Pivot Table در واقع می‌تواند داده را به تکه‌های کوچک معنادار تقسیم کند. کاربران نرم‌افزار مایکروسافت، کشیدن جدول در اکسل را به کاری عادی در دنیای مدیریت داده تبدیل کرده‌اند و به صورت مداوم از آن‌ها بهره می‌برند. اما آیا می‌دانستید که امکان تولید خودکار پیوت تیبل در اکسل هم وجود دارد و می‌توانید تنها با یک کلیک آن‌ها را بسازید؟ بله، یکپارچه‌سازی معرکه اکسل با محیط برنامه‌نویسی VBA بدین معناست که ابزارهایی قدرتمند در اختیارتان قرار گرفته‌اند. در ادامه نحوه انجام این کار را می‌بینیم.

پیوت تیبل در اکسل چه کاربردی دارد؟

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

برای انجام انواع تحلیل و بررسی و همچنین آزمایش و مطالعه روی داده‌ها، به کمک جداول محوری نیاز پیدا خواهید. مثلا می‌توانید برای محاسبه درصد تغییرات در اکسل با PivotTable، چنین جدولی بسازید.

روش خودکار ساخت Pivot Table در اکسل

برای خودکارسازی ساخت Pivot Table در اکسل به کمک زبان برنامه‌نویسی VBA، ابتدا سند را باز کنید و دو برگه یا شیت در آن بسازید، سپس نام اولی را «ماکرو» و دومی را «داده» بگذارید. شیت یا برگه ماکرو حاوی اسکریپت ماکرو خواهد بود و شیت داده هم از داده نگهداری می‌کند.

در شیت ماکرو می‌توانید هر شکلی که می‌خواهید را کشیده و ماکرو را به آن متصل کنید. برای انجام این کار، روی شکل مورد نظر راست‌کلیک و گزینه Assign Macro را انتخاب کنید.

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

۱٫باز کردن محیط برنامه نویسی VBA در اکسل

درون سند اکسل، دکمه‌های Alt + F۱۱ را بزنید تا محیط کدنویسی باز شود. در این پنجره جدید، روی نام فایل راست‌کلیک و گزینه‌های Insert و سپس Module را انتخاب کنید.

مهم است به خاطر داشته باشید که قبل از اجرای ماژول، تمام کدهای VBA را داخل ماژول بنویسید.

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

sub pivot_demo()

نام ماژول با عبارت End Sub   به پایان می‌رسد که دستور اتمام ماژول است.

End Sub

۲٫ تعریف متغیرها

کدهای داخلی ماژول را با تعریف کردن متغیرها برای ذخیره مقادیر از پیش تعریف شده کاربران شروع کنید. می‌توانید دستور 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

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

• PSheet: شیت مقصد، جایی که VBA قرار است پیوت تیبل را در آن بسازد.
• DSheet: شیت داده
• PvtCache: یک حافظه کش که پیوت یا محور را در خود نگه می‌دارد.
• PvtTable: شئ Pivot Table در اکسل
• PvtRange: محدوده داده برای جدول محوری
• Last_Row و Last_Col: آخرین ستون و سطر پر شده در شیت داده (DSheet)
• Sht1: یک واریانت

مولفه‌های مختلف پیوت تیبل چیست؟

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

۳٫ برطرف کردن هشدارها و پیام‌ها

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

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

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

هنگامی که این کد اجرا شود، تمام هشدارها، اخطارها و پیام‌های دیگری که ممکن است در چنین شرایطی نشان داده شوند را سرکوب می‌کند. البته می‌توانید پارامترهای ScreenUpdating و DisplayAlerts را با قرار دادن مقدار آن‌ها روی حالت False، خاموش کنید. برای روشن کردن آن‌ها در انتهای کد نیز، حالت را روی True بگذارید.

۴٫ حذف تمام شیت‌های Pivot Table موجود در اکسل

برای ایجاد Pivot Table جدید در اکسل دو گزینه پیش روی خود دارید. اول اینکه تمام جدول‌های محوری موجود را حذف کنید و با استفاده از VBA، شیت یا برگه جدیدی برای ذخیره پیوت تیبل بسازید. در روش دوم هم می‌توانید از یک محیط کاری برای نگهداری پیوت تیبل استفاده کنید. در این راهنما می‌خواهیم شیت جدیدی بسازیم:

عبارت For Each، در هر شیت محیط کاری می‌چرخد و نام شیت را در متغیر sht۱ ذخیره می‌کند. شما می‌توانید از هر نام متغیری برای نگهداری نام شیت استفاده کنید. For Each تمام شیت‌ها را در محیط کاری بررسی می‌کند تا شیتی با نام به‌خصوص و مورد نظر ما (یعنی Pivot) را پیدا کند.

وقتی مطابقت نام شیت پیدا شد، آن را حذف می‌کند و به شیت بعدی می‌رود. وقتی تمام شیت‌ها بررسی شدند، این چرخه به پایان رسیده و قسمت بعدی کد شروع به کار می‌کند که دستوری برای افزودن شیتی جدید با نام Pivot است. در ادامه می‌بینیم که چگونه می‌توانید این کد را بنویسید:

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

۵٫ تعریف منبع داده و شیت‌های پیوت

مهم است متغیرهایی برای ذخیره منابع شیت‌های داده و پیوت داشته باشید. این متغیرها نوعی راه میانبر به حساب می‌آیند و می‌توانید در بقیه کد به آن‌ها رجوع کنید:

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

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

این بخش کد، به شکلی پویا کار می‌کند، زیرا آخرین سطر و ستون پر شده در داده‌ها را اندازه‌دهی می‌کند:

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: متغیری برای ذخیره شماره آخرین سطر پر شده
• Last_Col: متغیری برای ذخیره شماره آخرین ستون پر شده
• PvtRange: به کل محدوده داده پیوت تیبل اشاره دارد.

۷٫ ساخت حافظه کش پیوت و Pivot Table در اکسل

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

با ارجاع به کش پیوت، باید یک پیوت تیبل بسازید. به عنوان بخشی از پیوت تیبل می‌توانید شیت، مرجع سلول و نام پیوت تیبل را نیز تعریف کنید. به این منظور، کد زیر را به کار ببرید:

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: محیط کاری جاری، جایی که شیت‌های داده و پیوت را در خود دارد.
• PivotCaches.Create: سینتکس پیش‌فرض برای ساخت کش پیوت
• SourceType: از آن‌جایی که داده درون محیط کاری است، می‌توانید «نوع منبع» را به عنوان xlDatabase تعریف کنید که یعنی درون دیتابیس ما قرار دارد. برخی از موقعیت‌های دیگر شامل xlConsolidation ،xlExternal یا xlPivotTable هستند
• SourceData: می‌توانید محدوده پیوت قبلی را به جای این قسمت قرار دهید.
• CreatePivotTable: دستور پیش‌فرض برای ساخت Pivot Table در اکسل
• TableDestination: مشخص کردن شیت و مراجع سلولی برای ساخت پیوت تیبل
• TableName: نام پیوت تیبل را مشخص می‌کند.
• CreatePivotTable: دستور پیش‌فرض برای ساخت پیوت تیبل درون حافظه کش پیوت

۸٫ افزودن سطرها، ستون‌ها، فیلترها و مقادیر

از آن‌جایی که در حال حاضر Pivot Table در اکسل آماده است، باید شروع به افزودن پارامترها درون فیلترها، سطرها، ستون‌ها و مقادیر تجمیعی کنید. می‌توانید دستور 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) و به تبع آن، نام جدول پیوت و نام متغیر نیز اشاره کنید. زمانی که نیاز به افزودن فیتلرها، سطرها و ستون‌ها دارید، می‌توانید از چندین سینتکس استفاده کنید که شامل موارد زیر می‌شوند:

+ همچنین در ریسمونک بخوانید :

انواع روش جمع سلول ها در اکسل

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

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

۹٫اجرای کد VBA برای ایجاد Pivot table خودکار در اکسل

در انتها، وقتی تمام برنامه آماده بود، می‌توانید آن را با زدن کلید F۵ یا دکمه Play، اجرا کنید. سپس وقتی به شیت پیوت برگردید، خواهید دید که Pivot Table جدیدی آماده شده است.

اگر می‌خواهید خروجی قدم به قدم کد و نحوه اجرای هر قسمت از آن را ببینید، می‌توانید به ادیتور کد بروید و کلید F۸ را چندین بار فشار دهید. به این شکل خواهید توانست هر خط از کد چگونه کار می‌کند و VBA به چه شکلی جدول محوری خودکار ایجاد می‌کند.

سخن نهایی

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