سری آموزش های Pivot Table (قسمت اول)

اگر شما جز اون دسته از کاربرای اکسل هستین که با حجم زیادی از داده ها کار می کنید (مثلا داده های مربوط به فروش روزانه یا تراکنش های مالی) و باید گزارش های روزانه یا دوره ای از این داده ها استخراج کنید و براساس گزارشات داده هاتون رو تحلیل کنید پس به ابزاری احتیاج دارید که بتونید در کوتاهترین زمان ممکن یه گزارش تحلیلی و انعطاف پذیر ایجاد کنید و این ابزار چیزی نیست جز Pivot Table یا همون جداول چرخشی. این ابزار قدرتمند به شما این امکان رو می ده تا با چند تا کلیک ساده گزارش های زیبا و مفهومی ایجاد کنید و به بهترین شکل دادهاتون رو ارائه و تحلیل کنید. در این سری مقاله های آموزش Pivot Table قصد داریم نحوه کار با Pivot Table رو شرح بدیم و مثال های مختلفی رو بررسی کنیم. این مقاله می تونه برای کسایی که مبتدی هستن و تازه می خوان کار با Pivot Table رو شروع کنن خیلی مفید باشه.

تعریف Pivot Table

Pivot Table یکی از ابزارهای اکسل هست که کارش بررسی و تحلیل حجم زیادی از داده ها و ایجاد گزارش‏ های خلاصه و کاربردیه در ادامه چند تا از کاربردهای Pivot Table رو ارائه شده:

• نمایش حجم زیادی از داده ها به صورت کاربر پسند ( یا همون user friendly)
• خلاصه کردن داده در دسته ها و زیر دسته ها
• فیلتر کردن، گروه بندی کردن، مرتب کردن و ایجاد فرمت های شرطی بر روی داده ها باهدف تمرکز بر روی داده هایی که بیشترین ارتباط رو با کار ما دارن
• جابه جا کردن (چرخوندن) سطر ها با ستون ها و بالعکس (برای همین اسم این جداول، جداول چرخشی هست) برای نمایش خلاصه گزارش های مختلف
• تجمیع داده های عددی در یک شیت
• کم یا زیاد کردن سطوح نمایش داده و ایجاد قابلیت Drill Down برای دسترسی به جزئیات اطلاعات

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

فرض کنید یک بانک اطلاعاتی از فروش لوازم خانگی داریم به شکل زیر:

شکل 1- بانک اطلاعاتی فروش لوازم خانگی

برای اینکه بخوایم از بانک اطلاعاتی شکل 1 گزارش های متنوعی دربیاریم (مثلا مجموع کل فروش یخچال یا تعداد جاروبرقی های فروخته شده توسط رضایی)

البته میدونیم که میشه برای این کار از توابع SUMIFs و SUMIF استفاده کرد. اما علاوه بر اینکه ویژگی ها و تفاوت های استفاده از تابع و ابزار رو میدونیم و بنا به شرایط باید روش مناسب رو انتخاب کنیم، اگر بخوایم در یک نگاه میزان فروش فروشندگان مختلف رو با هم مقایسه کنیم این ابزار خیلی کمک کننده خواهد بود و جداول مقایسه ای خیلی خوبی رو برامون ایجاد بکنه که چند نمونه از این جداول رو در شکل های 2، 3 و 4 می بینید:

شکل 2- مقایسه فروش به تفکیک فروشنده، محصول و ماه

شکل 3- مقایسه فروش به تفکیک فروشنده و محصول در یک ماه مشخص (اردیبهشت)

شکل 4- مقایسه فروش به تفکیک فروشنده و ماه برای یک محصول مشخص(مایکروفر)

حالا که با نمونه ای از کاربردهای Pivot Table آشنا شدین بهتون توصیه می کنم ادامه مقاله رو برای یادگیری نحوه کار با Pivot Table دنبال کنید.

نحوه ساخت Pivot Table در اکسل

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

گام اول: سازماندهی بانک اطلاعاتی

پایه و اساس ساخت گزارش در Pivot Table داشتن یه بانک اطلاعاتی مرتب و منظم هست. پس توصیه می کنیم حتما از ابزار Table برای ساخت بانک اطلاعاتی استفاده کنید. برای اینکار کل محدوده داده هاتون (شامل سرستون ها) رو انتخاب کنید و از تب Insert و زیر مجموعه Tables، گزینه Table رو انتخاب کنید. به این شکل شما می تونید یک بانک اطلاعاتی منظم داشته باشین.

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

نکات کاربردی:

هنگام ساخت بانک اطلاعاتی، باید نکات زیر رو در نظر داشته باشیم:

• برای ستون های بانک اطلاعاتی از عناوین غیر تکراری و معنادار استفاده کنید چون از همین عناوین بعدا در Pivot Table برای ساخت گزارش ها استفاده میشه.
• دقت کنید که هیچ سطر یا ستونی در بانک اطلاعاتی خالی نباشه و از Subtotal هم در بانک اطلاعاتی استفاده نشده باشه.
• برای اینکه به بانک اطلاعاتی دسترسی راحت داشته باشید و بتونید راحت تر فراخوانی کنید بهتر هست یک اسم معنادار برای بانک اطلاعاتی تخصیص بدید. برای اینکار کافیه روی یکی از سلول های جدول کلیک کنید و وارد تب Design بشین در گوشه بالای سمت چپ در فیلد Table Name می تونید هر اسمی که مدنظرتون هست برای بانک اطلاعاتی انتخاب کنید دقت کنید نام انتخابی فاصله (Space) نمیتونه داشته باشه. (برای مشاهده قواعد نامگذاری، حتما مقاله نامگذاری محدوده ها رو مطالعه کنید) مطابق شکل5.

شکل 5- تغییر نام جدول

گام دوم: ساخت Pivot Table

حالا که خیالمون از بانک اطلاعاتی راحت شد میریم سراغ ساخت PivotTable. برای اینکار اول یکی از سلول های بانک اطلاعاتی رو انتخاب میکنیم و از تب Insert در زیر گروه Tables گزینه PivotTable رو انتخاب میکنیم.

پنجره ای با نام Create PivotTable window مطابق شکل 6 نمایش داده می شه. دقت کنید که در فیلد Table/Range اسم بانک اطلاعاتی درست وارد شده باشه.

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

• مطابق شکل 6 در صورتی که گزینهNew Worksheet رو انتخاب کنیم PivotTable در یک شیت جدید ایجاد می شه.
• در صورتی که گزینه Existing Worksheet رو انتخاب کنیم PivotTable در همون شیتی که بانک اطلاعاتی هست ساخته می شه و باید از قسمت Location و با کلیک بر روی پیکان مشکی رنگ اولین سلول از محدوده ای که می خوایم Pivot Table اونجا نمایش داده بشه رو انتخاب کنیم.

شکل 6- پنجره Create PivotTable

اینجا گزینه New Worksheet رو انتخاب می کنیم و روی دکمه OK کلیک می کنیم. یک شیت جدید ایجاد میشه و پنجره ای به شکل زیر نمایش داده میشه.

شکل 7- PivotTable خالی

نکات کاربردی:

• بهتر هست که PivotTable در یک شیت جداگانه ساخته باشه.
• در صورتی که برای ساخت Pivot Table از داده های موجود در یک شیت دیگر یا یک فایل اکسل دیگر استفاده می کنید می تونید در قسمت Table/Range از سینتکس زیر استفاده کنید:

[workbook_name]sheet_name!range

برای مثال:

[Book1.xlsx]Sheet1!$A$1:$E$20

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

• میتونیم که PivotTable و PivotChart رو همزمان با هم بسازیم برای اینکار در نسخه های 2013، 2016 و 2019 اکسل از تب Insert در زیر گروه Chart بر روی PivotChart کلیک کرده و گزینهPivotChart & PivotTable را مطابق شکل 8 انتخاب میکنیم.

شکل 8- مسیر دسترسی PivotChart & PivotTable در ویرایش2019 اکسل

در ویرایش های 2007 و 2010 اکسل می توانید پس از کلیک بر رویPivotTabl  گزینه PivotChart رو انتخاب کنید.

دوره ویدئویی صفر تا صد پیوت تیبل
کامل ترین دوره آموزشی پیوت تیبل از پایه تا پیشرفته

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

گام سوم: تنظیم ساختار (Layout) گزارش

محیطی که در اون تنظیمات مربوط به نحوه نمایش فیلدهای اطلاعاتی در گزارش انجام میشه پنجره PivotTable Fields  هست که در سمت راست شیتی قرار گرفته که PivotTable در اون هست. مطابق شکل 9 پنجره PivotTable Fields از دو قسمت اصلی Field Section و Layout Section تشکیل شده.

قسمت Field Section شامل عناوین سرستون هایی هست که در بانک اطلاعاتی وجود داره و از این قسمت می تونیم مشخص کنیم که کدوم یک از ستون های داده ها در گزارش نمایش داده بشه.

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

پس از طریق پنجره PivotTable Fields می تونیم ساختار گزارش و نوع قرارگیری داده ها رو مشخص کنیم و نتیجه رو همونجا در گزارش مشاهده کنیم.

شکل 9- پنجره PivotTable Fields

اضافه کردن فیلد به Pivot Table

برای اضافه کردن یک فیلد اطلاعاتی به قسمت Layout Section کافیه تیک مربوط به فیلد مورد نظر رو از قسمت Field Section بزنیم.

پس از انتخاب هر فیلد، اکسل به صورت پیش فرض براساس نوع فیلد انتخابی اون فیلد رو در قسمت Layout Section اضافه می کنه:

• فیلدهای اطلاعاتی که داده های آن ها غیر عددی هستن به زیر بخش Rows اضافه می شن. (مثل فیلدهای “نام محصول” و “فروشنده” در شکل9)
• فیلدهای اطلاعاتی که داده های آن ها عددی هستن به زیر بخش Values اضافه می شن. (مثل “تعداد فروخته شده”)
• فیلدهای اطلاعاتی از نوع زمان و تاریخ هم به زیر بخش Columns اضافه می شن.

حذف کردن یک فیلد از Pivot Table

برای اینکار دو راه وجود داره:

• تیک مربوط به فیلد مورد نظر رو از قسمت Field Section برداریم.
• روی فیلد مورد نظر در Pivot Table کلیک راست کرده و مطابق شکل 10 گزینه Remove Field_Name رو انتخاب کنیم.

شکل 10- حذف فیلد از Pivot Table

مرتب کردن فیلدها

به سه روش می تونیم فیلدهای اطلاعاتی رو در قسمت Layout Section مرتب کنید:

روش اول: با Drag & Drop کردن می تونید هر فیلد رو در بین 4 بخش Layout Section جابه جا کنید برای اینکار کافیه مثل ویدئو زیر موس رو بر روی فیلد مورد نظر در قسمت Field Section نگه دارین و اون رو به سمت بخش مورد نظرتون در Layout Section بکشید.

روش دوم: روی اسم فیلد مورد نظر در قسمت Field Section کلیک راست کنید و از لیست نمایش داده شده (مطابق شکل 11) قسمتی که می خواین فیلد مورد نظر به اونجا منتقل بشه رو انتخاب کنید.

شکل 11- جابه جایی فیلدها با کلیک راست کردن روی نام فیلد در قسمت Field Section

روش سوم: اگر می خواین فیلد اطلاعاتی که قبلا به قسمت Layout Section منتقل شده رو به بخش دیگه ای منتقل کنید کافیه روی نام فیلد مورد نظرتون کلیک کنید و از بین گزینه های موجود (مطابق شکل 12) یکی رو انتخاب کنید.

شکل 12- جابه جایی فیلدها با انتخاب نام فیلد در Layout Section

گام چهارم: انتخاب تابع برای قسمت Values در Layout Section

اکسل به صورت پیش فرض برای فیلدهای اطلاعاتی از جنس عدد که در این قسمت قرار می گیرن از تابع Sum استفاده می کنه و برای فیلدهای اطلاعاتی غیر عددی (مثل متن، تاریخ) از تابع Count استفاده می کنه.

در قسمت Values این امکان وجود داره که سایر توابع هم انتخاب بشن. برای این کار بر روی فیلد مورد نظر در Pivot Table کلیک راست کرده و از لیست نمایش داده شده (مطابق شکل13) گزینه Summarize Values By رو انتخاب میکنیم و از بین توابع موجود تابع مورد نظرتون رو تعیین میکنیم. یا اینکه روی فیلد مورد نظر در قسمت Value کلیک کرده و از قسمت Value Field Setting تابع مورد نظر رو انتخاب میکنیم.

شکل 13- نحوه انتخاب سایر توابع برای بخش Values در Layout Section

گام پنجم: نمایش فیلد های حاوی داده های عددی با روش های مختلف محاسباتی

امکان دیگری که برای داده های عددی در Pivot Table وجود داره نمایش اعداد به شیوه های مختلف هست مثلا به صورت درصدی از کل داده ها یا نمایش داده از کوچک به بزرگ یا بالعکس. برای استفاده از این امکان در نسخه 2013، 2016 و 2019 بر روی یکی از فیلدهای عددی در PivotTable کلیک راست کرده و از لیست نمایش داده شده (مطابق شکل 14) گزینه Show Values As رو انتخاب میکنیم و از لیست نمایش داده گزینه مورد نظر رو انتخاب میکنیم. در ویرایش 2010 و پایین تر برای دسترسی به این قسمت روی گزینه Option در زیر مجموعه Calculations کلیک میکنیم.

شکل 14- انتخاب روش نمایش داده های عددی در PivotTable

در شکل 15 گزینه %Grand Total برای نمایش داده ها انتخاب شده. همانطور که می بینید بعد از انتخاب این گزینه مشخص شده که سهم فروش هر فروشنده از کل فروش چقدر بوده و برای هر فروشنده هم مشخص شده که سهم هر محصول از فروش چند درصد بوده. در واقع این شیوه نمایش داده ها می تونه در کوتاهترین زمان ممکن بیشترین اطلاعات رو از داده ها در اختیار ما بذاره.

تنظیمات مربوط به نمایش PivotTable Field List

در صورتی که بخوایم نحوه نمایش Layout Section و Field Section رو در پنجره PivotTable Fields تغییر بدیم، (نحوه نمایش رو Customize کنید) مطابق شکل 15 روی علامت چرخ دنده کلیک کنید و به این ترتیب لیستی از انواع چیدمان ‏ها رو نمایش داده میشه و می تونیم چیدمان مورد نظر رو انتخاب کنیم.

شکل 15- تغییر نحوه نمایش در PivotTable Fields

برای تغییر اندازه پنجره PivotTable Fields موس رو بر روی نواری که پنجره PivotTable Fields رو از شیت اکسل جدا می کنه نگه داریم (مطابق ویدئو زیر) تا نشان گر موس به شکل یک پیکان دو طرفه دربیاد و بعد موس رو به سمت راست یا چپ بکشیم تا پنجره به اندازه مورد نظر دربیاد.

بازکردن و بستن پنجره PivotTable Fields

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

برای باز کردن دوباره PivotTable Fields بر روی یکی از سلولهای گزارش ایجاد شده کلیک راست کرده و از لیست نمایش داده شده گزینه Show Field List رو انتخاب کنیم. همچنین می تونیم روی یکی از سلولهای Pivot Table کلیک کنیم تا تب Analyze در نوار بالای اکسل نمایش داده بشه (مطابق شکل16) و بعد در تب Analyze در زیر مجموعه Show گزینه Field List رو انتخاب کنیم.

شکل 16- بازکردن Field List با استفاده از تب Analyze

استفاده از Pivot Tables پیشنهادی اکسل

همان طور که دیدین ساختن Pivot Table کار سختی نیست اما اکسل در نسخه های جدیدش یه امکان خوب اضافه کرده و اون هم اینکه Pivot Table هایی که بیشترین همخوانی رو با داده ها دارن پیشنهاد می کنه. برای استفاده از این جداول می تونید مراحل زیر رو طی کنید:

• روی یکی از سلول های موجود در محدوده داده ها کلیک میکنیم؛
• در تب Insert از زیر مجموعه Tables گزینه Recommended PivotTables رو انتخاب میکنیم. با اینکار اکسل مجموعه ای از جداول پیشنهادی رو نمایش می ده؛ (مطابق شکل17)
• در پنجره Recommended PivotTables روی هر کدوم از جداول کلیک میکنیم تا پیش نمایش گزارش مورد نظر رو ببینیم.
• اگر جدولی مورد تایید بود با زدن دکمه OK در یک شیت جداگانه PivotTable نمایش داده میشه.

شکل 17- استفاده از Recommended PivotTables

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

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