پنج شنبه , ۳۰ دی ۱۳۹۵
صفحه نخست / توسعه مهارتهای اکسل / Pivot Tabel & Pivot Reporting / با Pivot Table گزارشات حرفه ای و داینامیک بسازید

با Pivot Table گزارشات حرفه ای و داینامیک بسازید

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

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

طراحی گزارشات حرفه ای با Pivot table در اکسل

نکته بسیار مهم در طراحی گزارشات حرفه ای این است که همواره سعی کنید ورودی داده هایتان را بصورت Table ایجاد نمایید. از مزیتهای ایجاد جداول در اکسل و نحوه کار با آنها قبلا مفصلا صحبت کرده ایم. در همین راستا ابتدای امر داده هایمان را به جدوا تبدیل می کنیم. برای اینکار می توانید از کلید میانبر Ctrl+T استفاده کنید.

سپس به تب کمکی DESIGN که بواسطه ایجاد جدول در منوی بالای اکسل ایجاد شده است میرویم و روی گزینه Summarize With PivotTable کلیک می نماییم.

طراحی گزارشات حرفه ای با Pivot table در اکسل-۲

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

نکته اول: می توانید اطلاعات روزانه تان را به هفته، ماه، فصل و سال گروه بندی کنید، بدون اینکه در داده های اصلی تغییری ایجاد نمایید.

برای اینکار فیلد ماه را در Rows قرار دهید و فروش را در Values می توانید سایر فیلدها را در Filters و یا Columns قرار دهید.

طراحی گزارشات حرفه ای با Pivot table در اکسل-۳

حالا روی یکی از سلولهای مرتبط با ستون Row Lables که تاریخهای روزانه در آن قرار دارد کلیک راست نمایید و گزینه Group را انتخاب کرده و هر یک از گروه های مورد نظرتان(ماه، فصل، سال) را انتخاب نمایید. می توانید همه موارد مذکور را هم انتخاب کنید.

طراحی گزارشات حرفه ای با Pivot table در اکسل-۴

اگر میخواهید گزارشات هفتگی بسازید باید گزینه Days را انتخاب کنید و قسمت Number of days را عدد هفت تعیین کنید. در نتیجه براحتی گزارشی به تفکیک سریهای زمانی مختلف ایجاد نموده اید.

طراحی گزارشات حرفه ای با Pivot table در اکسل-۵

نکته دوم: از نقش Filter در PivotTable غافل نشوید.

تا بحال به نقش فیلتر در PivotTable دقت کرده اید؟ آیا تا بحال فکر میکردید که فقط می توانید از طریق فیلد فیلتر گزارشات Customize شده بسازید؟ آیا می دانستید که می توانید بدون اینکه نیاز به ساخت PivotTableهای جدید باشد گزارشات مورد نظرتان را از یک PivotTable بسازید. منظور چیست؟ فرض کنید می خواهید همین گزارش فروش سالهای مختلف را به تفکیک برای هر محصول، هر شهر و هر ویزیتور داشته باشید. آیا از اول PivotTable میسازید؟ اینکار را نکنید اجازه دهید راهی فوق العاده را به شما نشان بدهم:

هر فیلدی را که میخواهید از آن Pivottable مشابه بسازید در فیلد فیلتر قرار دهید. در اینجا ما هم محصولات، هم شهرها و هم ویزیتورها را در فیلتر قرار داده ایم. سپس در تب Analyze روی قسمت Options کلیک کرده و دومین گزینه یعنی Show Report Filter Pages را انتخاب نمایید.

طراحی گزارشات حرفه ای با Pivot table در اکسل-۶

همانطور که از نام این گزینه پیداست صفحات مختلفی را بر اساس فیلد موجود در فیلتر برای شما ایجاد میکند. وقتی این گزینه را انتخاب کنید پنجره ای برایتان باز میشود که در آن از شما میخواهد یکی از فیلدهای موجود در فیلتر که میخواهید Pivottable آنرا برایتان ایجاد کند را انتخاب کنید. سپس Ok کنید و خواهید دید که صفحات مختلف ایجاد خواهد شد.

طراحی گزارشات حرفه ای با Pivot table در اکسل-۷

فرض کنید ما گزینه ویزیتور را انتخاب کنیم آنگاه  به ازای نام هر ویزیتور یک صفحه جدید PivotTable ایجاد خواهد شد. تمام داده های Pivot Table نیز بر اساس نام ویزیتورها فیلتر میشود.

طراحی گزارشات حرفه ای با Pivot table در اکسل-۸

نکته سوم: می توانید فیلدهای محاسباتی مورد نیازتان را خودتان بسازید.

مطمنا وقتی داده هایی از کسب و کار در اختیارتان قرار میگیرد نیاز دارید که محاسباتی بر روی آنها انجام دهید. مثلا اختلاف فروش امسال نسبت به سال قبل. یا مثلا حاشیه سود و … . شاخصهای زیادی برای محاسبه بر مبنای دیتاهای در دسترس وجود دارد اما آیا باید همه این شاخصهای را از قبل محاسبه کرد و سپس PivotTable را ساخت؟ خیر با من همراه باشید تا با هم این کارکرد فوق العاده را یاد بگیریم:

فرض کنید می خواهید حاشیه سود را طی سه سال و در فصول مختلف محاسبه کنید برای اینکار باید میزان سود در هر بازه زمانی را بر فروش همان دوره تقسیم کنید برای اینکار به تب Analyze بروید روی گزینه Fields, Items & Sets کلیک نمایید. اولین گزینه Calculated Fields را انتخاب نمایید.

طراحی گزارشات حرفه ای با Pivot table در اکسل-۹

در پنجره ای که باز میشود باید دو قسمت را تکمیل نمایید. در قسمت Name برای شاخصی که میخواهید محاسبات آنرا انجام دهید یک نام بگذارید در اینجا ما “حاشیه سود” نوشتیم و در قسمت Formula باید با استفاده از فیلدهای موجود در لیست پایین آن محاسبات را انجام دهید که ما در اینجا سود/زیان را بر فروش تقسیم کردیم. برای تقسیم از علامن / روی کیبرد استفاده کنید. بعد از اینکه ok کردید فیلد جدید خودبخود به PivotTable اضافه میشود.

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

نکته چهارم: نمودارهای داینامیک بسازید.

آیا تا بحال با خودتان فکر کرده اید که چه میشد اگر میتوانستید نمودارهایی بسازید که با اعمال فیلتر روی دیتاها نمودارتان نیز تغییر میکرد؟ بله درست است PivotTable این امکان را به شما میدهد. برای اینکار کافیست در تب Insert روی PivotChart کلیک نموده و نمودار مورد نظرتان را ایجاد نمایید. سپس در همان تب روی گزینه Slicer کلیک کرده و اسلایسرهای مورد نیازتان را بسازید.

کار اسلایسر فیلتر کردن است. در واقع اکسل برای شما منوهایی ایجاد میکند که می توانید از طریق آنها فیلترهای مورد نظرتان را خیلی راحت بر روی جدولتان اعمال کنید.

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

ساخت Pivotchart داینامیک با Slicer

 

پاسخ دهید

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

11 + 18 =

Time limit is exhausted. Please reload CAPTCHA.