آموزش قدم به قدم گزارش گیری با پیوت تیبل اکسل

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

 در ضمن در منابع فارسی PivotTable را جدول محوری ترجمه می کنند ولیکن در این مقاله در بیشتر موارد از همان واژه پیوت تیبل استفاده کرده ام.

چرا باید پیوت تیبل اکسل را یاد بگیریم؟

قبل از یادگیری هر موضوعی بهتر است بدانیم که دانستن آن چه ضرورتی دارد. پاسخ این سوال به دفعات مختلف در مقالات یا محتواهای آموزشی دیگر فرساران ذکر شده است:

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

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

بله! درست متوجه شدید جدول محوری یک ابزار بسیار مفید برای آنالیز حجم زیادی از داده ها و ایجاد گزارش های کاربردی و مهم ترین ابزار اکسل است. اگر تا به امروز از آن استفاده نکرده اید،‌ زمان بسیاری را بیهوده سپری کردید. پیوت تیبل ها یکی از ویژگی های بسیار قدرتمند و مفید در اکسل می باشند.در این مقاله در تلاش هستم که شما را با این معجزه‌ی اکسل آشنا کنم. 
PivotTable یا جدول محوری چیست؟

PivotTable ابزاری است که برای گزارش گیری از آن استفاده می کنیم. یعنی با ایجاد هر پیوت تیبل یک گزارش ایجاد می کنید. شاید گزارش هایی که از این طریق ایجاد می شوند را بتوانیم با دیگر امکانات اکسل بسازیم ولی باید بدانید که هیچ روشی سریعتر از پیوت تیبل برای اینکار وجود ندارد. برای مثال می توانید از فرمول هایی مثل Sumifs و خانواده اش استفاده کنید.

تفاوت پیوت تیبل با روش های دیگر ساخت گزارش در این است که شما بدون نیاز به نوشتن فرمول و در زمان بسیار کم می توانید گزارش های پویا، تعاملی و متنوعی بسازید که زوایای مختلفی از داده های کسب و کارتان را به شما نشان دهند و به راحتی با فیلتر کردن، داده هایی را که دوست دارید به شما نشان دهند. اینقدر که من از این ابزار تعریف کردم هیچکس در طول زندگی ام از من تعریف نکرده بود. با این همه تعریف نمی دانم کسی هست که نخواهد با این ابزار آشنا شود!؟

فهرست عناوین

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

آموزش گام به گام

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

داده های نمونه

فایل نمونه شامل ۴۵۲ رکورد با ۵ فیلد اطلاعاتی است. راستی بچه ها باید بدانید که فیلدها، همان ستون ها هستند و به هر ردیف نیز در اینجا رکورد می گوییم. وقتی راجع به بانک اطلاعاتی صحبت می کنیم به جای سطر و ستون از دو واژه ی رکورد و فیلد استفاده می کنیم. 

همانطور که ملاحظه می کنید ۵ فیلد تاریخ، رنگ، ناحیه، واحد و فروش وجود دارد. داده ها در یک Table(جدول) با نام «Table1» آماده شده است. پیشنهاد می کنم که برای ذخیره داده های خودتان حتما از تیبل استفاده کنید چون با اینکار امکانات خیلی خوبی به شما داده می شود که در حالت استفاده از لیست معمولی از آن ها بی بهره هستید. می پرسید چه امکاناتی؟ من به شما نخواهم گفت تا خودتان مقاله ی Table مهمترین ابزار اکسل را در سایت فرساران مطالعه کنید. 

چگونه پیوت تیبل را ایجاد کنیم

۱) برای شروع، یک سلول در داخل جدول داده را انتخاب کرده و روی Pivot Table در زبانه Insert  کلیک کنید.

اکسل پنجره Create Pivot Table را نمایش می دهد. در این پنجره دو تا چیز را باید مشخص کنید. اول انتخاب داده هایی که می خواهید از آن گزارش بگیرید و دوم انتخاب محلی که قصد دارید گزارش در آنجا نشان داده شود.
در ارتباط با اولی توجه داشته باشید که Data Range از قبل پر شده است زیرا شما در ابتدای کار یک سلول از جدول را فعال کرده بودید و در دومی مکان پیش فرض یک پیوت تیبل، شیت جدید(New worksheet) است یعنی اکسل یک شیت جدید ایجاد کرده و پیوت را در آنجا قرار می دهد.
۲) مکان پیش فرض را نادیده گرفته، گزینه شیت موجود (Existing sheet) را انتخاب کنید. در این حالت باید یک سلول را تعیین کنید. شما سلول H4 را انتخاب کنید یا در کادر موجود H4 را تایپ کنید تا پیوت تیبل را در همین شیتی که هستید قرار دهد.

۳) بر روی OK کلیک کنید، و Excel یک پیوت تیبل ایجاد می کند که از سلول H4 شروع می شود.

همزمان با ایجاد پیوت تیبل اکسل همچنین پنجره PivotTable Fields را سمت راست پنجره اکسل نمایش می دهد که در ابتدا خالی است. توجه داشته باشید که هر پنج فیلد موجود در جدول داده ها در اینجا ذکر شده است، اما از این فیلدها هنوز استفاده نشده است. گزارش دلخواه خودتان را باید با کمک این پنجره بسازید. 

 برای ساخت گزارش مورد نظر، باید فیلدهای مورد نیاز را به یکی از قسمت های Columns، Rows یا Values بکشید. همچنین از ناحیه Filters نیز برای اعمال فیلترهای عمومی بر روی پیوت تیبل استفاده می شود. 

اضافه کردن فیلدها به پیوت تیبل

۱) فیلد Sales را به پنجره Field list و قسمت Values بکشید.
اکسل جمع کل 26356 را محاسبه می کند. این مجموع تمام مقادیر فروش در کل جدول داده است.

سوال: آیا می شود یک فیلد متنی را در قسمت Values قرار دهیم؟ چه محاسباتی روی آن انجام  می شود؟

۲) فیلد Color را به همین ترتیب در قسمت Rows بکشید.

اکسل داده های فروش را بر اساس رنگ دسته بندی کرده و جمع فروش هر دسته (که در اینجا رنگ ها هستند) را روبروی آن نشان می دهد.

توجه داشته باشید که جمع کل 26356 در ردیف Grand total دست نخورده باقی مانده است. این منطقی است، زیرا ما هنوز در حال تهیه گزارش از مجموعه کامل داده ها هستیم.
بیایید در این مرحله به پنجره Field List نگاهی بیندازیم. می بینید که Color یک فیلد Row است و Sales یک Values است.

قالب بندی اعداد

همانطور که در حالت عادی می توانیم قالب بندی اعداد را تغییر دهیم، بر روی اعداد پیوت تیبل نیز می توانیم قالب بندی اعداد را اجرا کنیم. با انجام این کار، هنگام بروز رسانی گزارش های فرمت های اعمال شده حفظ خواهند شد. برای مثال قصد داریم واحد پول $ را روی اعداد اجرا کنیم.
۱) بر روی یکی از اعداد  فروش مثلا عدد 6414 کلیک راست کرده و Number Format را انتخاب کنید.

۲) قالب بندی Currency را با صفر رقم اعشار انتخاب کنید، Ok را فشار دهید .

در پیوت تیبل حاصل، کلیه مقادیر فروش دارای فرمت currency هستند:

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

مرتب سازی داده ها بر اساس مقادیر

۱) بر روی یکی از مقادیر فروش کلیک راست کرده و از قسمت Sort گزینه Largest to smallest را انتخاب کنید.

در ابتدا اکسل رنگ هایی با بیشترین فروش را لیست می کند. هنگام تغییر داده ها یا پیکربندی مجدد پیوت تیبل، این ترتیب مرتب سازی حفظ می شود.

بروز رسانی داده ها

برای به روزرسانی داده های پیوت تیبل باید از Refresh استفاده کنیم.  برای مشاهده نحوه عملکرد آن، ابتدا در جدول اصلی داده ها تغییری ایجاد خواهیم کرد و بعد از آن تغییرات حاصل را در پیوت تیبل مشاهده می کنیم.
۱) سلول F5 را انتخاب کرده و 11.00 دلار را به 2000 دلار تغییر دهید.
۲) بر روی یکی از خانه های پیوت تیبل کلیک راست کرده و "Refresh" را انتخاب کنید.

توجه کنید که رنگ "قرمز" اکنون پر فروش ترین رنگ است و به طور خودکار در بالای پیوت تیبل نمایش داده می شود.

۳) دوباره F5 را به 11.00 دلار تغییر دهید و پیوت تیبل را دوباره Refresh کنید.

سوال: اگر چندین جدول داشته باشید بروز رسانی پیوت تیبل ها چگونه انجام می شود؟ اگر در انتهای جدول رکورد جدید اضافه کنیم آیا با رفرش کردن گزارش به روزرسانی می شود؟ 

اضافه کردن دومین فیلد در ناحیه Values

در این بخش قصد داریم بگوییم که بیش از یک فیلد را نیز می توانیم بیش از یک فیلد را به ناحیه Value اضافه کنیم.
۱) فیلد Units را به قسمت Value بکشید تا Sales و Units را با هم ببینید.

سوال: اگر قصد داشته باشیم میانگین فروش رنگ ها را در کنار جمع فروش داشته باشیم، چه کاری انجام دهیم؟

درصد از کل در اکسل با پیوت تیبل

مقادیر موجود در ستون های ناحیه Values را می توان به شکل های مختلف نمایش داد. برای مثال بدون انجام محاسبات خاص و نوشتن فرمول می توانیم مقادیر به عنوان یک درصد از مقدار کل نشان دهیم.  برای اینکار دوبار فیلد فروش را به ناحیه Values اضافه کنید که در یکی جمع فروش و در دیگری درصد از کل را مشاهئه کنیم.
۱) فیلد Units را از قسمت Values بردارید.

۲) فیلد Sales دوباره را به قسمت Values اضافه کنید.
۳) روی یکی از سلول های ستون دوم sales که اضافه کردید، کلیک راست کرده و « of grand total%» را انتخاب کنید.

نتیجه دسته بندی بر اساس رنگ همراه با فروش کل و درصدی از کل است.

اینجا پنجره Field list به شکل زیر است:

سوال: آیا می توانیم در پیوت تیبل فرمول دلخواهی بنویسیم که محاسباتی را بر روی مقادیر موجود انجام دهد؟

گروه بندی براساس تاریخ

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

۱) دومین فیلد فروش (Sales2 را که در بخش قبلی اضافه کرده بودید)، حذف کنید.
۲) فیلد Date را به قسمت Columns بکشید.
در ابتدا اکسل تاریخ ها را به صورت پیش فرض گروه بندی شده به شما می دهد. با اجرای مرحله بعد می توانید آنرا به سلیقه خود تغییر دهید.
۳) روی یکی از تاریخ ها مثلا 2017 در قسمت عناوین راست کلیک کرده و "Group" را انتخاب کنید.

۴) هنگامی که پنجره Group ظاهر شد، Group را فقط براساس Year گروه بندی کنید (فعلن انتخاب Month و Quarter را لغو کنید، سپس در مراحل بعدی برای تمرین هر کدام را به تنهایی یا با هم انتخاب کنید).

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

اینجا پنجره Field list به شکل زیر است:

پیوت تیبل های دو بعدی

می توانیم همزمان از دو بخش Rows و Columns همزمان استفاده کنیم.
۱) فیلد Date را از قسمت Columns بیرون بکشید(حذف کنید).
۲) فیلد Region را به قسمت Columns بکشید. 

۳) می توانید جای فیلد Region و Color را تغییر دهید.(Region را به ناحیه Rows و Color را به ناحیه Columns بکشید)
اکسل یک پیوت تیبل دو بعدی دیگر ایجاد می کند.

باز هم توجه کنید که فروش کل (26356 دلار) در تمام پیوت تیبل های بالا یکسان است. هر جدول نمای متفاوتی از داده های یکسان را نشان می دهد، بنابراین همه آنها به شکلی یک جمع کل را نشان می دهند.
مثال فوق نشان می دهد که با چه سرعتی می توانید پیوت تیبل های مختلفی از داده های مشابه ایجاد کنید. شما می توانید با استفاده از انواع داده ها، انواع مختلف دیگری از پیوت تیبل ایجاد کنید.

مزایای کلیدی پیوت تیبل

• سادگی: شما به سادگی و بدون استفاده از فرمول می توانید گزارش های دلخواه خود را ایجاد کنید و تنظیمات مورد نظر خود را اعمال کنید.
• سرعت: شما می توانید در عرض چند دقیقه یک گزارش زیبا و مفید با یک پیوت تیبل ایجاد کنید. حتی اگر در فرمول نویسی مهارت زیادی داشته باشید، تنظیم پیوت تیبل سریعتر است و به تلاش بسیار کمتری نیاز دارد.
• انعطاف پذیری: برخلاف فرمول ها، پیوت تیبل شما را در نمای خاصی از داده های قفل نمی کند. می توانید جدول محوری را به سرعت متناسب با نیازهای خود دوباره تنظیم کنید. حتی می توانید یک پیوت تیبل را کپی کرده و یک نمای جداگانه ایجاد کنید.
• دقت: تا زمانی که پیوت تیبل به درستی تنظیم شود، می توانید مطمئن باشید که نتایج دقیق هستند. در واقع، یک پیوت تیبل اغلب مشکلات موجود در داده ها را سریعتر از هر ابزار دیگری نشان می دهد.
• قالب بندی: یک پیوت تیبل می تواند به طور خودکار قالب بندی و سبک ثابتی را برای اعداد اعمال می کند، حتی با تغییر داده ها.
• به روز رسانی ها: جداول محوری برای به روزرسانی های مداوم طراحی شده اند. اگر یک پیوت تیبل را از روی جدول اکسل ایجاد کرده باشید و جدول با توجه به داده های جدید در صورت لزوم تغییر اندازه دهد، تمام کاری که شما باید انجام دهید کلیک کردن روی Refresh است، و پیوت تیبل شما جدیدترین موارد را به شما نشان می دهد.
• فیلتر کردن: جداول محوری شامل چندین ابزار برای فیلتر کردن داده ها هستند. برای مثال شما می توانید با قراردادن فیلد ناحیه در بخش Filters تعیین کنید که داده های شرق و شمال را نشان دهد.
•  نمودار: هنگامی که یک PivotTable دارید، می توانید به راحتی از آن یک نمودار بسازید. به نموداری از که پیوت تیبل ساخته شود PivotChart می گویند. پیوت چارت ها نیز مشابه پیوت تیبل پویا و تعاملی هستند.