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

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

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

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

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

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

فیلم آموزش جداول محوری در اکسل یا پیوت تیبل Pivot Tables در فرادرس

کلیک کنید

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

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

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

کلیک کنید

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

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

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

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

فیلم آموزش برنامه نویسی وی بی ای در اکسل – VBA در Excel – مقدماتی در فرادرس

کلیک کنید

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

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

1sub pivot_demo()

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

1End Sub

2. تعریف متغیرها

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

1Dim PSheet As Worksheet, DSheet As Worksheet
2Dim PvtCache As PivotCache
3Dim PvtTable As PivotTable
4Dim PvtRange As Range
5Dim Last_Row As Long, Last_Col As Long
6Dim sht1 as Variant

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

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

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

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

3. برطرف کردن هشدارها و پیام‌ها

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

1On Error Resume Next
2 
3With Application
4    .DisplayAlerts = False
5    .ScreenUpdating = False
6End With

قسمت‌های مختلف کد به شرح زیر هستند:

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

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

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

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

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

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

1For Each sht1 In ActiveWorkbook.Worksheets
2    If sht1.Name = "Pivot" Then
3        sht1.Delete
4    End If
5Next sht1
6 
7Worksheets.Add.Name = "Pivot"

5. تعریف منبع داده و شیت‌های پیوت

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

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

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

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

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

قسمت‌های این کد به شرح زیر هستند:

• Last_Row: متغیری برای ذخیره شماره آخرین سطر پر شده
• Last_Col: متغیری برای ذخیره شماره آخرین ستون پر شده
• PvtRange: به کل محدوده داده پیوت تیبل اشاره دارد.

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

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

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

1Set PvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PvtRange).CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), TableName:="MUODemoTable")
2Set 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: دستور پیش‌فرض برای ساخت پیوت تیبل درون حافظه کش پیوت

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

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

فیلم آموزش برنامه نویسی وی بی ای در اکسل – VBA در Excel – تکمیلی در فرادرس

کلیک کنید

برای افزودن مقادیر فیلترها، از کد زیر کمک بگیرید:

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

کد زیر را برای افزودن منابع سطرها به کار ببرید:‌

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

مقادیر ستون‌ها را می‌توانید با کد زیر اضافه کنید:

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

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

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

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

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

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

9.اجرای کد VBA برای ایجاد Pivot table خودکار در اکسل

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

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

سخن نهایی

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