عمومی , کاربردی 53 بازدید

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

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

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

PivotTable

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

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

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

PivotTable

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

PivotTable

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

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

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

PivotTable

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

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

PivotTable

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

PivotTable

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

PivotTable

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

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

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

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

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

PivotTable

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

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

PivotTable

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

PivotTable

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

PivotTable

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

PivotTable

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

برای نهایی کردن عملی این 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 تبدیل می‌شود و این دقیقاً همان چیزی است که ما می‌خواهیم.

PivotTable

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

PivotTable

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

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

==

آیا این مطلب برای شما مفید بود؟

نظر شما چیست؟

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *