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

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

فرمول‌نویسی و محاسبات ساده‌ای مثل درصد رشد یا درصد سقوط در اکسل بسیار ساده است. برای اطلاعات بیشتر به مقاله‌ای که قبلاً در این رابطه تهیه کردیم، مراجعه فرمایید:

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

در این مقاله جدولی به صورت زیر در اختیار داریم که می‌خواهیم آن را به جدول محوری تبدیل کرده و اختلاف قیمت کالاها را بررسی کنیم:

ترسیم جدول در اکسل

قبل از هر چیز می‌بایست محدوده‌ای از سلول‌ها را به جدول تبدیل کنید. برای این مهم ابتدا محدوده‌ی موردنظر را با روش کلیک و درگ یا با استفاده از کلید میانبر Ctrl + A انتخاب کنید و سپس در منوی Insert از نوار ابزار بالای صفحه، روی Table کلیک کنید. 

آموزش محاسبه درصد رشد و سقوط در جدول محوری یا PivotTable در اکسل

در پنجره‌ی Create Table روی OK کلیک کنید تا رنج انتخاب شده به جدول تبدیل شود. البته اگر ردیف اول، ردیف عنوان است، تیک گزینه‌ی My table has headers را بزنید و سپس روی OK کلیک کنید. برای نام‌گذاری جدول، روی تب Design کلیک کنید و در بخش Table Tools نام جدول را به صورت دلخواه تایپ کنید. در این مثال از نام Sales که به معنی فروش‌ها است، استفاده می‌کنیم.

آموزش محاسبه درصد رشد و سقوط در جدول محوری یا PivotTable در اکسل

در تب Design گزینه‌هایی برای بهبود ظاهر جدول نیز وجود دارد. به عنوان مثال می‌توانید ردیف‌ها را یکی در میان با دو رنگ متفاوت، رنگی کنید.

نحوه‌ی ترسیم جدول محوری در Excel

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

آموزش محاسبه درصد رشد و سقوط در جدول محوری یا PivotTable در اکسل

به عنوان مثال اگر طبق روش گفته شده در مرحله‌ی قبلی، جدولی نساخته باشید، می‌توانید روی فیلد Table/Range کلیک کنید و سپس با کلیک روی اولین سلول رنج موردنظر و درگ کردن به سمت آخرین سلول، محدوده‌ای از سلول‌ها را انتخاب کنید تا به جدول محوری تبدیل شود.

گروه‌بندی داده‌های جدول محوری

در این مثال پس از ایجاد جدول محوری، ردیف‌ها را گروه‌بندی می‌کنیم. ستون تاریخ سفارش یا Order Date، حاوی تاریخ فروش محصولات است. برای گروه‌بندی کردن فروش‌ها بر حسب تاریخ فروش، می‌توانید روی یکی از سلول‌های حاوی تاریخ راست‌کلیک کرده و گزینه‌ی Group را انتخاب کنید.

در اکسل ۲۰۱۶ و نسخه‌های بعدی، داده‌هایی از نوع تاریخ به صورت خودکار در گروه‌های سال و فصل و ماه، گروه‌بندی می‌شوند.

آموزش محاسبه درصد رشد و سقوط در جدول محوری یا PivotTable در اکسل

نوع گروه‌بندی را انتخاب کنید. در این مثال از Months و Years یا گروه‌بندی بر اساس سال و ماه استفاده می‌کنیم. Days یا روزها و Quarters یا فصل‌ها و گزینه‌های دیگر نیز موجود است و به تناسب نیاز می‌توانید تقسیم‌بندی را به شکل متفاوتی انجام بدهید.

آموزش محاسبه درصد رشد و سقوط در جدول محوری یا PivotTable در اکسل

به این ترتیب فیلدهای ماه و سال برای آنالیز داده، اضافه می‌شود.

آموزش محاسبه درصد رشد و سقوط در جدول محوری یا PivotTable در اکسل

اضافه کردن فیلد اعداد و مقادیر به PivotTable

برای فراهم کردن قابلیت فیلتر کردن اطلاعات جدول بر اساس سال و در واقع تاریخ فروش، فیلد Year را از بخش Rows‌ به ناحیه‌ی Filter جابجا کنید. از طرفی برای محاسبه کردن اختلاف میزان فروش در ماه‌های مختلف، به اعداد نیاز داریم. لذا با روش درگ و دراپ، فیلد مقدار فروش یا Total sales را به بخش Values اضافه کنید.

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

آموزش محاسبه درصد رشد و سقوط در جدول محوری یا PivotTable در اکسل

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

اما قبل از این کار، بهتر است استایل سلول‌ها را تغییر بدهید. لذا روی یکی از سلول‌های اولین ستون راست‌کلیک کنید و گزینه‌ی Number Formatting را انتخاب کنید. سپس در پنجره‌ی Format Cells فرمت Accounting یا محاسبات مالی را انتخاب کرده و تعداد ارقام پس از اعشار را 0 وارد کنید تا بخش اعشاری حذف شود. در نتیجه نمایش اعداد به شکل زیر تغییر می‌کند:

آموزش محاسبه درصد رشد و سقوط در جدول محوری یا PivotTable در اکسل

افزودن ستون درصد تغییرات در جدول محوری

برای افزودن ستونی حاوی درصد تغییر اعداد، روی یکی از سلول‌ها ستون آخر راست‌کلیک کنید و گزینه‌ی Show Values As و سپس % Difference from را انتخاب کنید.

آموزش محاسبه درصد رشد و سقوط در جدول محوری یا PivotTable در اکسل

در پنجره‌ی باز شده می‌بایست مشخص کنید که درصد اختلاف با کدام سلول محاسبه شود. در این مثال می‌خواهیم میزان فروش هر ماه را با ماه قبلی مقایسه کنیم لذا از منوی Base Item، گزینه‌ی (Previous) انتخاب می‌شود و از منوی کرکره‌ای Base Filed نیز فیلد تاریخ فروش یا Order Date انتخاب می‌شود.

آموزش محاسبه درصد رشد و سقوط در جدول محوری یا PivotTable در اکسل

با کلیک روی OK، شیوه‌ی نمایش اعداد در ستون دوم تغییر می‌کند و درصد رشد فروش نسبت به ماه قبلی، ذکر می‌شود:

آموزش محاسبه درصد رشد و سقوط در جدول محوری یا PivotTable در اکسل

بهتر است در این مرحله عنوان ستون آخر را تغییر بدهید و از واژه‌ای مثل Variance یا اختلاف، استفاده کنید. کافی است روی سلول‌ها در ردیف عنوان ستون‌ها کلیک کنید و نام هر ستون را به صورت دلخواه تایپ کنید.

آموزش محاسبه درصد رشد و سقوط در جدول محوری یا PivotTable در اکسل

اضافه کردن فلش‌های نمایش تغییرات در اکسل

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

برای اضافه کردن فلش‌های رنگی، روی یکی از سلول‌ها کلیک کنید و سپس از تب Home روی Conditional Formatting کلیک کرده و سپس روی New Rule کلیک کنید. در بخش ویرایش قاعده یا Edit Formatting Rule، مراحل زیر را طی کنید:

  • ابتدا گزینه‌ی All cells showing “Variance” values for Order Date را انتخاب کنید.
  • از منوی کرکره‌ای Format Style گزینه‌ی Icon Sets را انتخاب کنید.
  • از منوی کرکره‌ای Icon Style یکی از گروه‌های آیکون‌ها را به تناسب نیاز و سلیقه انتخاب کنید.
  • در بخش پایین پنجره برای هر یک از فلش‌های رنگی، شرط لازم را تعریف کنید. به عنوان مثال برای فلش سبز رو به بالا، از منوی کرکره‌ای Type حالت Number را انتخاب کنید و در فیلد Value عدد 0 را تایپ کنید و از منوی کرکره‌ای مجاور این فیلد، حالت بزرگ‌تر از را انتخاب کنید. به این ترتیب اعداد بزرگ‌تر از صفر با فلش سبز رو به بالا و در واقع فلش رشد فروش نمایش داده می‌شوند.
  • برای فلش قرمز رو به پایین، مشابه مرحله‌ی قبلی عمل کنید با این تفاوت که حالت کوچک‌تر از 0 را انتخاب کنید.

آموزش محاسبه درصد رشد و سقوط در جدول محوری یا PivotTable در اکسل

در نهایت روی OK کلیک کنید. نتیجه به صورت زیر خواهد بود:

آموزش محاسبه درصد رشد و سقوط در جدول محوری یا PivotTable در اکسل