⭐⭐⭐

مشاهده تقویم برگزاری «دوره‌های آموزش آنلاین اکسل» فرساران در سال ۱۴۰۱

 

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

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

توجه داشته باشید که این مقاله، آموزش مقدماتی پیوت تیبل است و اگر مایلید که بر این ابزار کاملا مسلط شوید، پیشنهاد می‌کنیم تا دوره آموزشی ویدئویی ساخت گزارش با PivotTable را مشاهده کنید.

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

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

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

هر کاربر اکسلی که VLOOKUP ،PivotTable و SUMIFS را نمی داند اکسل پیشرفته را بلد نیست.

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

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

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

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

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

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

فهرست عناوین

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

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

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

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

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

داده های نمونه پیوت تیبل

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

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

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

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

نکته: هنگام استفاده از یک سری ابزارهای اکسل و همچنین پیوت تیبل نیازی به انتخاب کل جدول ندارید چرا که وقتی داده های شما پیوسته هستند و شما یک سلول از آن ناحیه را انتخاب می کنید اکسل پیوستگی را تشخیص می دهد. دلیل آن ویژگی Current region است یعنی اکسل ناحیه جاری را شناسایی می کند.

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

اکسل پنجره 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 قرار بگیرد روی آن محاسبات انجام می شود. محاسبات پیش فرض Sum است.

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

محاسبه جمع کل درپیوت تیبل

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

نکته: هر فیلدی که در قسمت 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" را انتخاب کنید.

نکته: به جای راست کلیک روی پیوت تیبل و انتخاب Refresh می توانید یک سلول دلخواه داخل پیوت را انتخاب کنید و از کلید میانبر Alt + F5 استفاده کنید.

بروز رسانی پیوت تیبل

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

نتیجه بروزرسانی پویت تیبل

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

تذکر: توجه داشته باشید که با توجه به مقیاسی که اعداد موجود دارند تغییر F5 به 2000 دلار واقع بینانه نیست، اما روش خوبی برای مشاهده بروز رسانی پیوت تیبل است. سعی کنید یک یا چند رنگ موجود را به چیزی جدید تغییر دهید، مانند "طلایی" یا "سیاه". وقتی Refresh می کنید، رنگ جدید ظاهر می شود. برای بازگشت به داده های اصلی و حالت قبلی می توانید از Undo استفاده کنید.

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

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

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

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

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

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

نکته: یکی از روش های حذف یک فیلد از پیوت تیبل این است که از پنجره Field List آنرا به سمت وسط شیت کشیده و رها کنید.

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

فرمول های آماده در پیوت تیبل

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

تذکر: توجه داشته باشید که فرمت اعداد برای percentage یا همان درصد نیز تنظیم شده است که تا دقت یک رقم  اعشار را نشان دهد.

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


فیلد لیست در حالت فیلد دوم

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

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

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

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

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

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

پنجره گروپینگ

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

نکته: ویژگی Group بر روی فیلدهایی از جنس عدد یا زمان نیز قابل اجرا هستند. 

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


فیلد لیست در حالت گروپینگ

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

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

نکته: فیلدی که در قسمت Columns قرار بگیرد گروه بندی/دسته بندی شده و داده های آن روبه‎ روی هم نشان داده می‎ شوند.

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

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

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

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

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

 

به این مطلب پاسخ دهید