محاسبه درصد تغییرات در اکسل با PivotTable – به زبان ساده

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

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

ما قصد داریم از برگه زیر کار را شروع کنیم:

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

قالب‌بندی بازه مقادیر به صورت جدول

اگر بازه داده‌ها از قبل به صورت یک جدول قالب‌بندی نشده باشد، باید چنین کاری را انجام دهیم. داده‌های ذخیره شده در جدول نسبت به داده‌های موجود در بازه‌های سلولی یک صفحه گسترده چند مزیت دارند. این مزیت‌ها به طور خاص هنگام استفاده از جدول‌های Pivot کاملاً نمایان خواهند بود. برای قالب‌بندی یک بازه از مقادیر به صورت جدول باید سلول‌های مورد نظر را انتخاب کرده و روی Insert > Table کلیک کنید.

ابتدا بررسی کنید که بازه انتخاب شده صحیح است و این که هدر در ردیف اول آن بازه باشد و سپس روی OK کلیک کنید. بدین ترتیب بازه انتخابی به یک جدول تبدیل می‌شود. نامگذاری جدول موجب می‌شود که اشاره به آن در ادامه زمانی که جدول‌های Pivot، نمودارها و فرمول‌های دیگری را می‌سازید، راحت‌تر باشد. روی برگه Design زیر ابزارهای Table کلیک کنید و یک نام را در کادر باز شده در آغاز ریبون وارد نمایید. ما جدول خود را Sales نامیدیم.

شما می‌توانید استایل جدول را نیز به صورت دلخواه خود تغییر دهید.

ایجاد PivotTable برای نمایش و محاسبه درصد تغییرات در اکسل

در این مرحله اقدام به ساخت جدول Pivot می‌کنیم.

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

کلیک کنید

درون جدول جدیدی که ایجاد کردید، روی Insert > PivotTable کلیک کنید. بدین ترتیب پنجره ایجاد PivotTable ظاهر می‌شود. این ابزار به صورت خودکار جدول مورد نظر شما را تشخیص می‌دهد. اما می‌توانید جدول یا بازه سلول‌هایی که می‌خواهید در جدول Pivot استفاده کنید را نیز به صورت دستی انتخاب کنید.

گروه‌بندی تاریخ‌ها برحسب بازه ماهانه

در ادامه فیلد date را که می‌خواهیم برحسب آن گروه‌بندی کنیم روی ناحیه ردیف‌های PivotTable کشیده و رها می‌کنیم. در این مثال نام فیلد Order Date است. از اکسل نسخه 2016 به بعد، مقادیر تاریخ به صورت خودکار برحسب بازه‌های سالیانه، فصلی و ماهانه گروه‌بندی می‌شود. اگر نسخه اکسل شما چنین کاری انجام نمی‌دهد یا می‌خواهید گروه‌بندی را تغییر دهید، روی سلول شامل مقدار تاریخ راست-کلیک کرده و سپس دستور Group را انتخاب کنید.

گروه‌هایی که می‌خواهید استفاده کنید را انتخاب نمایید. در این مثال تنها گزینه‌های سالانه و ماهانه انتخاب شده‌اند.

اکنون سال و ماه فیلدهایی هستند که می‌توانیم برای تحلیل خود استفاده کنیم. ماه‌ها همچنان به صورت Order Date نام‌گذاری شده‌اند.

افزودن فیلدهای مقدار به PivotTable

فیلد سال را از ردیف‌ها برداشته و به ناحیه Filter ببرید. بدین ترتیب امکان استفاده از فیلتر برای PivotTable به مدت یک سال فراهم می‌شود و از حجم شلوغی PivotTable تا حد زیادی کاسته می‌شود.

فیلدِ شامل مقادیر (مقادیر مجموع فروش در این مثال) را که می‌خواهید محاسبه کرده و درصد تغییرات را نشان دهید، گرفته و «دو بار» به ناحیه Values بکشید. ممکن است تا به اینجا چیز زیادی عایدمان نشده باشد، اما همه چیز خیلی زود تغییر خواهد یافت.

هر دو فیلد مقدار دارای مقدار پیش‌فرض مجموع (sum) هستند و فعلاً هیچ قالب‌بندی ندارند. مقادیر ستون نخست را به صورت مجموع حفظ می‌کنیم، گرچه به مقداری قالب‌بندی نیاز دارند. روی یک عدد در ستون نخست کلیک کرده و گزینه Number Formatting را از منوی میانبر انتخاب می‌کنیم.

گزینه قالب‌بندی Accounting را با 0 رقم اعشاری از کادر محاوره‌ای Format Cells انتخاب می‌کنیم. اینک PivotTable به صورت زیر در آمده است:

ایجاد ستون درصد تغییرات

روی مقداری در ستون دوم راست-کلیک کنید، روی Show Values کلیک کرده و سپس روی گزینه % Difference from کلیک نمایید.

گزینه (Previous) را به عنوان Base Item انتخاب کنید. این بدان معنی است که مقدار ماه جاری همواره با مقدار ماه‌های قبلی (فیلد تاریخ سفارش) مقایسه خواهد شد.

هم اکنون PivotTable مقادیر و درصد تغییر را نمایش می‌دهد.

در سلول شامل Row Labels کلیک کنید و عبارت «Month» را به عنوان هدر برای ستون وارد نمایید. سپس روی سلول هدر برای ستون مقادیر دوم کلیک کرده و عبارت Variance را وارد کنید.

افزودن برخی فلش‌های بصری‌سازی

برای نهایی کردن عملی این PivotTable باید درصد تغییرات را با افزودن برخی فلش‌های سبز و قرمز به نحو بهتری بصری‌سازی کنیم. این فلش‌ها یک روش محبوب برای نمایش مثبت یا منفی بودن تغییرات محسوب می‌شوند.

به این منظور، روی هریک از مقادیر در ستون دوم کلیک کرده و سپس به مسیر Home > Conditional Formatting > New Rule بروید. در پنجره Edit Formatting Rule که باز می‌شود، مراحل زیر را طی کنید:

1. گزینه All cells showing “Variance” values for Order Date را انتخاب کنید.
2. گزینه Icon Sets را از لیست استایل های قالب‌بندی انتخاب کنید.
3. مثلث‌های قرمز، زرد و سبز را از لیست Icon Style انتخاب کنید.
4. در ستون type گزینه لیست را طوری تغییر دهید که به جای درصد Number را بیان کند. بدین ترتیب ستون مقدار به 0 تبدیل می‌شود و این دقیقاً همان چیزی است که ما می‌خواهیم.

روی OK کلیک کنید تا «قالب‌بندی شرطی» (Conditional Formatting) روی PivotTable اعمال شود.

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

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

• مجموعه آموزش‌های نرم‌‌ افزار اکسل Excel
• گنجینه آموزش‌های اکسل (Microsoft Excel)
• مجموعه آموزش‌های نرم‌‌افزارهای Microsoft Office (آفیس)
• توابع جدید اکسل 2۰1۹ — راهنمای کاربردی
• تجزیه و تحلیل داده‌ها در اکسل با استفاده از جداول محوری (Pivot Tables)

==