جدول محوری یا Pivot Table یکی از ابزارهای اصلی و مفید نرم‌افزار Excel است که برای تحلیل داده و بررسی گزارش خلاصه از اعداد و ارقام به کار می‌رود. محاسبه درصد رشد یا نزول در اکسل ساده است و در جدول محوری نیز می‌توان اختلاف اعداد و در واقع رشد یا سقوط اعداد را به صورت درصد محاسبه کرد.
در این مقاله به روش محاسبه کردن درصد اختلاف بین اعداد در جدول محوری و در نرم‌افزار Excel مجموعه‌ی آفیس مایکروسافت می‌پردازیم.
فرمول‌نویسی و محاسبات ساده‌ای مثل درصد رشد یا درصد سقوط در اکسل بسیار ساده است. برای اطلاعات بیشتر به مقاله‌ای که قبلاً در این رابطه تهیه کردیم، مراجعه فرمایید:
اگر در اکسل جدولی از اعداد و ارقام تهیه کرده‌اید و برای تحلیل بهتر داده‌ها، جدول را به جدول محوری یا Pivot Table تبدیل کرده‌اید هم محاسبه کردن درصد اختلاف امکان‌پذیر و بسیار ساده است. البته در نرم‌افزارهای مشابه اکسل، قابلیت ترسیم جدول محوری وجود دارد که نمونه‌ی محبوب آن، گوگل شیتس است. برای آشنایی بیشتر با جدول محوری و کاربرد آن، به مقاله‌ی زیر مراجعه فرمایید:
در این مقاله جدولی به صورت زیر در اختیار داریم که می‌خواهیم آن را به جدول محوری تبدیل کرده و اختلاف قیمت کالاها را بررسی کنیم:
ترسیم جدول در اکسل
قبل از هر چیز می‌بایست محدوده‌ای از سلول‌ها را به جدول تبدیل کنید. برای این مهم ابتدا محدوده‌ی موردنظر را با روش کلیک و درگ یا با استفاده از کلید میانبر Ctrl + A انتخاب کنید و سپس در منوی Insert از نوار ابزار بالای صفحه، روی Table کلیک کنید.
در پنجره‌ی Create Table روی OK کلیک کنید تا رنج انتخاب شده به جدول تبدیل شود. البته اگر ردیف اول، ردیف عنوان است، تیک گزینه‌ی My table has headers را بزنید و سپس روی OK کلیک کنید. برای نام‌گذاری جدول، روی تب Design کلیک کنید و در بخش Table Tools نام جدول را به صورت دلخواه تایپ کنید. در این مثال از نام Sales که به معنی فروش‌ها است، استفاده می‌کنیم.
در تب Design گزینه‌هایی برای بهبود ظاهر جدول نیز وجود دارد. به عنوان مثال می‌توانید ردیف‌ها را یکی در میان با دو رنگ متفاوت، رنگی کنید.
نحوه‌ی ترسیم جدول محوری در Excel
گام بعدی ایجاد جدول محوری یا Pivot Table است. برای این کار در تب Insert بالای صفحه روی PivotTable کلیک کنید تا پنجره‌ی Create PivotTable نمایان شود. جدولی که در مرحله‌ی قبلی ساخته‌اید به صورت خودکار شناسایی می‌شود اما اگر چند جدول داشته باشید، می‌توانید جدول موردنظر را به صورت دستی انتخاب کنید.
به عنوان مثال اگر طبق روش گفته شده در مرحله‌ی قبلی، جدولی نساخته باشید، می‌توانید روی فیلد Table/Range کلیک کنید و سپس با کلیک روی اولین سلول رنج موردنظر و درگ کردن به سمت آخرین سلول، محدوده‌ای از سلول‌ها را انتخاب کنید تا به جدول محوری تبدیل شود.
گروه‌بندی داده‌های جدول محوری
در این مثال پس از ایجاد جدول محوری، ردیف‌ها را گروه‌بندی می‌کنیم. ستون تاریخ سفارش یا Order Date، حاوی تاریخ فروش محصولات است. برای گروه‌بندی کردن فروش‌ها بر حسب تاریخ فروش، می‌توانید روی یکی از سلول‌های حاوی تاریخ راست‌کلیک کرده و گزینه‌ی Group را انتخاب کنید.
در اکسل ۲۰۱۶ و نسخه‌های بعدی، داده‌هایی از نوع تاریخ به صورت خودکار در گروه‌های سال و فصل و ماه، گروه‌بندی می‌شوند.
نوع گروه‌بندی را انتخاب کنید. در این مثال از Months و Years یا گروه‌بندی بر اساس سال و ماه استفاده می‌کنیم. Days یا روزها و Quarters یا فصل‌ها و گزینه‌های دیگر نیز موجود است و به تناسب نیاز می‌توانید تقسیم‌بندی را به شکل متفاوتی انجام بدهید.
به این ترتیب فیلدهای ماه و سال برای آنالیز داده، اضافه می‌شود.
اضافه کردن فیلد اعداد و مقادیر به PivotTable
برای فراهم کردن قابلیت فیلتر کردن اطلاعات جدول بر اساس سال و در واقع تاریخ فروش، فیلد Year را از بخش Rows به ناحیه‌ی Filter جابجا کنید. از طرفی برای محاسبه کردن اختلاف میزان فروش در ماه‌های مختلف، به اعداد نیاز داریم. لذا با روش درگ و دراپ، فیلد مقدار فروش یا Total sales را به بخش Values اضافه کنید.
این تغییرات همگی در ستون کنار صفحه انجام می‌شود و نتیجه به صورت زیر خواهد بود:
همان‌طور که در تصویر فوق مشاهده می‌کنید، هر دو فیلد اضافه شده در جدول محوری، خلاصه وضعیت را به صورت حاصل‌جمع اعداد و در واقع میزان فروش کلی نشان می‌دهند. به جای جمع یا Sum می‌توانید عمل دیگری را انتخاب کنید و درصد رشد فروش را محاسبه کنید.
اما قبل از این کار، بهتر است استایل سلول‌ها را تغییر بدهید. لذا روی یکی از سلول‌های اولین ستون راست‌کلیک کنید و گزینه‌ی Number Formatting را انتخاب کنید. سپس در پنجره‌ی Format Cells فرمت Accounting یا محاسبات مالی را انتخاب کرده و تعداد ارقام پس از اعشار را 0 وارد کنید تا بخش اعشاری حذف شود. در نتیجه نمایش اعداد به شکل زیر تغییر می‌کند:
افزودن ستون درصد تغییرات در جدول محوری
برای افزودن ستونی حاوی درصد تغییر اعداد، روی یکی از سلول‌ها ستون آخر راست‌کلیک کنید و گزینه‌ی Show Values As و سپس % Difference from را انتخاب کنید.
در پنجره‌ی باز شده می‌بایست مشخص کنید که درصد اختلاف با کدام سلول محاسبه شود. در این مثال می‌خواهیم میزان فروش هر ماه را با ماه قبلی مقایسه کنیم لذا از منوی Base Item، گزینه‌ی (Previous) انتخاب می‌شود و از منوی کرکره‌ای Base Filed نیز فیلد تاریخ فروش یا Order Date انتخاب می‌شود.
با کلیک روی OK، شیوه‌ی نمایش اعداد در ستون دوم تغییر می‌کند و درصد رشد فروش نسبت به ماه قبلی، ذکر می‌شود:
بهتر است در این مرحله عنوان ستون آخر را تغییر بدهید و از واژه‌ای مثل Variance یا اختلاف، استفاده کنید. کافی است روی سلول‌ها در ردیف عنوان ستون‌ها کلیک کنید و نام هر ستون را به صورت دلخواه تایپ کنید.
اضافه کردن فلش‌های نمایش تغییرات در اکسل
نمایش درصد رشد بسیار مناسب است اما برای بهبود ظاهر می‌توانید در سلول‌های درصد رشد، فلش‌های رنگی به سمت بالا یا پایین یا حالت صاف اضافه کنید تا با یک نگاه مشخص شود که کدام اعداد وضعیت صعودی داشته‌اند و کدام موارد وضعیت نزولی داشته‌اند.
برای اضافه کردن فلش‌های رنگی، روی یکی از سلول‌ها کلیک کنید و سپس از تب 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 را انتخاب کنید.
در نهایت روی OK کلیک کنید. نتیجه به صورت زیر خواهد بود:
howtogeekسیاره‌ی آی‌تی