چگونه در اکسل درصد تغییر را با جدول محوری محاسبه نماییم؟

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

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

در زیر کاربرگی را که از آن استفاده می‌کنیم، مشاهده می‌نمایید:

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

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

قالب‌بندی محدوده داده‌ها به عنوان یک جدول

اگر از قبل محدوده داده‌های خود را به عنوان یک جدول فرمت نکرده‌اید، شما را به انجام این کار تشویق می‌کنیم. داده‌های ذخیره‌شده در جداول مزایای متعددی نسبت به داده‌های موجود در محدوده‌ سلول‌های یک کاربرگ دارند (به ویژه هنگام استفاده در جداول محوری‌).

برای فرمت یک محدوده به عنوان یک جدول، محدوده سلول‌ها را انتخاب کنید و بر روی Insert > Table کلیک کنید.

صحت محدوده داده‌ها را بررسی کنید و پس از اطمینان از وجود هدر در ردیف اول این محدوده، بر روی “OK” کلیک کنید.

محدوده در حال حاضر به عنوان یک جدول، فرمت شده است. نام‌گذاری جدول باعث می‌شود که هنگام ایجاد جداول محوری، نمودارها و فرمول‌ها در آینده راحت‌تر بتوانید به آن مراجعه کنید.

بر روی تب “Design” زیر ابزار جدول کلیک کنید و یک نام را در جعبه‌ای که در ابتدای نوار ظاهر شده است، وارد کنید. جدول زیر به نام “Sales” نام‌گذاری شده است.

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

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

اکنون می‌خواهیم بحث را با ایجاد جدول محوری ادامه دهیم. از داخل جدول جدید، بر روی Insert > PivotTable کلیک کنید.

پنجره Create PivotTable ظاهر می‌شود. این پنجره به طور خودکار جدول شما را شناسایی خواهد کرد. اما شما می‌توانید شخصا جدول یا محدوده مورد نظر را برای استفاده از جدول محوری انتخاب کنید.

تاریخ‌ها را به ماه دسته‌بندی کنید

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

از اکسل 2016 به بعد، مقادیر تاریخی به طور خودکار به سال، فصل و ماه تقسیم می‌شوند. اگر نسخه اکسل شما این کار را انجام نمی‌دهد و یا مایل هستید تا دسته‌بندی را تغییر دهید، بر روی سلول حاوی مقدار تاریخی راست‌کلیک و سپس دستور “Group” را انتخاب نمایید.

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

اکنون سال و ماه فیلد‌هایی هستند که می‌توان برای آنالیز از آن‌ها استفاده کرد. ماه‌ها هنوز هم با عنوان Order Date نام‌گذاری شده‌اند.

فیلدهای مقادیر را به جدول محوری اضافه کنید

فیلد سال را از Rows به قسمت Filter منتقل کنید. با این کار، کاربر می‌تواند به جای شلوغ کردن جدول با اطلاعات بیش از حد، جدول محوری را برای یک‌ سال فیلتر کند. فیلد حاوی مقادیر (در این مثال Total sales) را که می‌خواهید به محاسبه و ارایه تغییر در آن بپردازید به داخل ناحیه values بکشید.

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

هر دوی فیلدهایی که به تازگی اضافه شده‌اند به طور پیش فرض بر روی جمع گذاشته می‌شوند و در حال حاضر هیچ فرمتی ندارند.

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

بر روی یکی از اعداد ستون اول راست‌کلیک کنید. از شورتکاتی که ظاهر می‌شود “Number Formatting” را انتخاب نمایید.

از Format Cells، فرمت “Accounting” را با 0 عدد اعشار، انتخاب کنید.

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

بر روی یک مقدار در ستون دوم راست‌کلیک کنید. بر روی “Show Values” بروید و گزینه “% Difference from” را انتخاب نمایید.

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

حال جدول محوری هم مقادیر و هم درصد تغییرات را نشان می‌دهد.

بر روی خانه‌ای که حاوی برچسب سطرها است، کلیک کنید و “Month” را به عنوان هدر آن ستون تایپ کنید. سپس بر روی خانه هدر برای ستون‌های دومین مقادیر کلیک کنید و نوع “Variance” را انتخاب نمایید.

چند فلش واریانس اضافه کنید

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

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

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

برای اعمال تغییرات بر روی جدول محوری‌، “OK” را کلیک کنید.

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

نوشته چگونه در اکسل درصد تغییر را با جدول محوری محاسبه نماییم؟ اولین بار در وب‌سایت فناوری پدیدار شد.