ساختار Pivot Table یا گزارشات حرفه ای در اکسل

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

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

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

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

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

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

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

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

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

نکته دوم:  نقش Filter در PivotTable در اکسل

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

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

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

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

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

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

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

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

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

نکته چهارم: ساختار نمودارهای داینامیک در اکسل

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

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