ایجاد Pivot Table در اکسل به صورت خودکار – راهنمای تصویری
- یاد میگیرید چگونه در اکسل بهصورت خودکار، «Pivot Table» بسازید.
- متغیرها و ساختار کدهای «VBA» برای ایجاد جدول محوری را میآموزید.
- نحوه فعالسازی محیط «VBA» و ایجاد ماژول مناسب برای پروژه را یاد میگیرید.
- یاد میگیرید چطور هنگام ساخت «Pivot Table»، شیتها را پاکسازی و مدیریت کنید.
- میآموزید با کدنویسی، سطر، ستون، فیلتر و مقدار را به جدول محوری اضافه کنید.
- خواهید آموخت با اجرای کد، تحلیل دادهها را بهصورت خودکار انجام دهید.

مطالب مجله فرادرس را در جستجوی گوگل سریعتر ببینید
پیوت تیبل در اکسل چه کاربردی دارد؟
پیوت تیبل روشی بسیار سریع برای خلاصه کردن مقادیر حجیمی از داده است. میتوانید از این جداول برای تجزیه و تحلیل دادههای عددی با جزئیات استفاده کنید و پاسخ سوالات پیشبینینشده در مورد دادهها را به راحتی بیابید. پیوت تیبل به صورت خاص برای جستوجوی حجم زیادی از داده به روشهای مختلف و کابرپسند به درد میخورد.
«پیوت تیبل» (Pivot Table) ابزار بسیار خوبی برای گرفتن گزارشات مختلف و زنده از دادههای موجود در جداول اکسل است. روش کار با این ابزار کاربردی را در ادامه مطلب به شکل کامل توضیح دادهایم. اما در صورت تمایل به استفاده از آموزشهای ویدئویی، پیشنهاد میکنیم فیلم آموزشی و رایگان زیر را مشاهده کنید. در این فیلم نحوه ساخت و استخراج دادهها با پیوت تیبل آموزش داده شده است.
روش خودکار ساخت Pivot Table در اکسل
در شیت ماکرو میتوانید هر شکلی که میخواهید را کشیده و ماکرو را به آن متصل کنید. برای انجام این کار، روی شکل مورد نظر راستکلیک و گزینه Assign Macro را انتخاب کنید.

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

خوب است نامی را برای ماژول برگزینید که متناسب با هدف کدهای شما باشد. از آنجایی که کد ما تمرینی و دمو است، نامی مانند مورد زیر برای آن وارد میکنیم:
نام ماژول با عبارت End Sub به پایان میرسد که دستور اتمام ماژول است.
2. تعریف متغیرها
کدهای داخلی ماژول را با تعریف کردن متغیرها برای ذخیره مقادیر از پیش تعریف شده کاربران شروع کنید. میتوانید دستور Dim را برای تعریف متغیرها به کار ببرید:
از متغیرهایی که در ادامه میآیند استفاده خواهیم کرد:
- PSheet: شیت مقصد، جایی که VBA قرار است پیوت تیبل را در آن بسازد.
- DSheet: شیت داده
- PvtCache: یک حافظه کش که پیوت یا محور را در خود نگه میدارد.
- PvtTable: شئ Pivot Table در اکسل
- PvtRange: محدوده داده برای جدول محوری
- Last_Row و Last_Col: آخرین ستون و سطر پر شده در شیت داده (DSheet)
- Sht1: یک واریانت

مولفههای مختلف پیوت تیبل چیست؟
در هر جدول محوری یا پیوت چهار ناحیه فیلتر، ستون، ردیف و مقدار وجود دارد. مقادیر، چیزهایی هستند که حساب میکنید. ردیفها چیزهایی هستند که بر اساس آنها محاسبه انجام میشود. ستونها، مقادیر را به قسمتهای مختلف تقسیم میکنند و از فیلتر برای جداسازی گزارشها بر اساس مواردی که در متن گزارش نیستند، استفاده میشود.
3. برطرف کردن هشدارها و پیامها
خطاها، اخطارها و پیامهای غیرضروری باعث کندی عملکرد کدهای VBA میشوند. با برطرفسازی چنین پیامهایی میتوانید روند کار را به مقدار قابل توجهی سرعت ببخشید. به این منظور، کد زیر را مورد استفاده قرار دهید:
قسمتهای مختلف کد به شرح زیر هستند:
- On Error Resume Next: هرگونه خطای زمان اجرا را برطرف میکند.
- Application: به نرمافزار اکسل اشاره دارد.
- DisplayAlerts: این ویژگی، مشخص میکند هشدارها نشان داده شوند یا نه.
- ScreenUpdating: معین میکند آیا تغییرات به صورت بلادرنگ بهروز شوند یا فقط پس از اتمام اجرای کد.
هنگامی که این کد اجرا شود، تمام هشدارها، اخطارها و پیامهای دیگری که ممکن است در چنین شرایطی نشان داده شوند را سرکوب میکند. البته میتوانید پارامترهای ScreenUpdating و DisplayAlerts را با قرار دادن مقدار آنها روی حالت False، خاموش کنید. برای روشن کردن آنها در انتهای کد نیز، حالت را روی True بگذارید.
4. حذف تمام شیتهای Pivot Table موجود در اکسل
عبارت For Each، در هر شیت محیط کاری میچرخد و نام شیت را در متغیر sht1 ذخیره میکند. شما میتوانید از هر نام متغیری برای نگهداری نام شیت استفاده کنید. For Each تمام شیتها را در محیط کاری بررسی میکند تا شیتی با نام بهخصوص و مورد نظر ما (یعنی Pivot) را پیدا کند.
وقتی مطابقت نام شیت پیدا شد، آن را حذف میکند و به شیت بعدی میرود. وقتی تمام شیتها بررسی شدند، این چرخه به پایان رسیده و قسمت بعدی کد شروع به کار میکند که دستوری برای افزودن شیتی جدید با نام Pivot است. در ادامه میبینیم که چگونه میتوانید این کد را بنویسید:
5. تعریف منبع داده و شیتهای پیوت
مهم است متغیرهایی برای ذخیره منابع شیتهای داده و پیوت داشته باشید. این متغیرها نوعی راه میانبر به حساب میآیند و میتوانید در بقیه کد به آنها رجوع کنید:
6. مشخص کردن آخرین سطر و ستون استفاده شده
این بخش کد، به شکلی پویا کار میکند، زیرا آخرین سطر و ستون پر شده در دادهها را اندازهدهی میکند:
قسمتهای این کد به شرح زیر هستند:
- Last_Row: متغیری برای ذخیره شماره آخرین سطر پر شده
- Last_Col: متغیری برای ذخیره شماره آخرین ستون پر شده
- PvtRange: به کل محدوده داده پیوت تیبل اشاره دارد.

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

8. افزودن سطرها، ستونها، فیلترها و مقادیر
از آنجایی که در حال حاضر Pivot Table در اکسل آماده است، باید شروع به افزودن پارامترها درون فیلترها، سطرها، ستونها و مقادیر تجمیعی کنید. میتوانید دستور pivotfields را برای شروع تعریف جزئیات به کار ببرید.
برای افزودن مقادیر فیلترها، از کد زیر کمک بگیرید:
کد زیر را برای افزودن منابع سطرها به کار ببرید:
مقادیر ستونها را میتوانید با کد زیر اضافه کنید:
برای افزودن مقادیر تجمیعی نیز کد زیر را بنویسید:
توجه به این نکته ضروری است که باید به صفحه فعال (شیت Pivot) و به تبع آن، نام جدول پیوت و نام متغیر نیز اشاره کنید. زمانی که نیاز به افزودن فیتلرها، سطرها و ستونها دارید، میتوانید از چندین سینتکس استفاده کنید که شامل موارد زیر میشوند:
- xlPageField: برای افزودن فیلتر
- xlRowField: برای افزودن سطر
- ٰxlRowField: برای افزودن ستون
در نهایت نیز میتوانید دستور xlDataField را برای محاسبه مجموع مقادیر به کار ببرید. البته امکان استفاده از توابع دیگر مانند xlAverage ، xlSum ،xlCount ،xlMax ،xlMin و xlProduct را نیز دارید.

9.اجرای کد VBA برای ایجاد Pivot table خودکار در اکسل
در انتها، وقتی تمام برنامه آماده بود، میتوانید آن را با زدن کلید F5 یا دکمه Play، اجرا کنید. سپس وقتی به شیت پیوت برگردید، خواهید دید که Pivot Table جدیدی آماده شده است.
سخن نهایی
مطالب مجله فرادرس را در جستجوی گوگل سریعتر ببینید














