جدول چرخشی(Pivot Table)

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

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

A B C D E F G
1 کد نام گروه اصلی گروه فرعی قیمت تعداد فروش خریدار
2 1432 هارد توشیبا ابزار دخیره سازی هارد دیسک 200000 10 عمده فروش
3 1134 هارد وسترن دیجیتال ابزار دخیره سازی هارد دیسک 230000 15 مشتری نهایی
4 5400 هارد سامسونگ ابزار دخیره سازی هارد دیسک 185000 3 مشتری نهایی
5 3211 فلش سیلیکون پاور ابزار دخیره سازی فلش مموری 25000 21 مشتری نهایی
6 1611 فلش ای دیتا ابزار دخیره سازی فلش مموری 23000 14 عمده فروش
7 1610 فلش اچ پی ابزار دخیره سازی فلش مموری 22500 8 عمده فروش
8 1200 گرافیک Nvidia کارت گرافیک 65000 6 مشتری نهایی
9 1345 گرافیک ATI Radeon کارت گرافیک 73000 3 عمده فروش
10 8190 مانیتور ال جی مانیتور 320000 7 مشتری نهایی
11 2340 مانیتور سامسونگ مانیتور 410000 4 عمده فروش
12 4255 دی وی دی رایتر پایونییر دی وی دی رایتر 90000 9 مشتری نهایی
13 4622 دی وی دی رایتر لایت ان دی وی دی رایتر 86000 1 عمده فروش
14 4990 تبلت اچ تی سی ابزار همراه تبلت 900000 14 عمده فروش
15 5357 گوشی هوواوی ابزار همراه تلفن همراه 650000 24 مشتری نهایی
16 5724 گوشی ال جی ابزار همراه تلفن همراه 1150000 10 عمده فروش
17 1134 هارد وسترن دیجیتال ابزار دخیره سازی هارد دیسک 230000 17 مشتری نهایی
18 1611 فلش ای دیتا ابزار دخیره سازی فلش مموری 23000 11 عمده فروش
19 1610 فلش اچ پی ابزار دخیره سازی فلش مموری 22500 9 مشتری نهایی
20 2340 مانیتور سامسونگ مانیتور 410000 6 عمده فروش
21 4255 دی وی دی رایتر پایونییر دی وی دی رایتر 90000 3 عمده فروش
22 4990 تبلت اچ تی سی ابزار همراه تبلت 900000 12 مشتری نهایی

در این جدول ما اطلاعات مربوط به وضعیت فروش محصولات رو در اختیار داریم. حالا میخوایم گزارشی تهیه کنیم که به تفکیک گروه اصلی و فرعی محصولات، تعداد فروش رو به ما نشون بده. برای اینکار از ابزار Pivot Table اکسل استفاده میکنیم. ابتدا ستون های A تا  G رو انتخاب میکنیم و مطابق شکل زیراز تب Insert روی گزینه Pivot Table کلیک میکنیم:

آشنایی با ابزار Pivot Table اکسل - بخش اول

آشنایی با ابزار Pivot Table اکسل - بخش اول

بعد از انتخاب گزینه Pivot Table، پنجره بالا باز میشه که شامل گزینه های زیر هست:

  • Choose the data that you want to analyze: در این بخش می تونیم محدوده داده هایی که قراره تحلیل کنیم رو تعریف کنیم

Select a table or range: با انتخاب این گزینه میتونیم یک جدول یا یک محدوده رو برای تحلیل داده ها انتخاب کنیم. چون ما قبل از ایجاد Pivot Table ستون های A تا G رو انتخاب کرده بودیم محدوده A:G به طور خودکار توسط اکسل در این بخش تعریف شده که میتونیم در صورت لزوم به دلخواه اون رو تغییر بدیم.

Use an external data sourse: با انتخاب این گزینه میتونیم داده ها رو از جایی خارج از اکسل ( SQl Server، نرم افزار ACCESS , …) وارد نرم افزار کرده و اونها رو تحلیل کنیم.

  • Choose where you want the PivotTable report to be placed: در این بخش می تونیم انتخاب کنیم که آیا Pivot Table در یک شیت جدید ایجاد بشه یا در یکی از شیت هایی که در حال حاضر داریم قرار داده بشه. این بستگی به نوع نیاز ما داره که کدوم گزینه روئ انتخاب کنیم ولی اگر دلیل خاصی برای ایجاد Pivot Table در یکی از شیت های موجود وجود نداره بهتره اون رو در یکم شیت جدید ایجاد کنیم. اینطوری Workbook منظم تر و مدیریت اون راحت تر هست.

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

Existing Worksheet: در صورتی که این گزینه رو انتخاب کنیم می تونیم یک سلول رو در یکی از شیت های موجود انتخاب کنیم تا Pivot Table در اونجا ایجاد بشه.

  • Choose whether you want to analyze multiple tables: این بخش در اکسل 2013 اضافه شده و مربوط به ویژگی جدید Data Model هست که به ما اجازه میده اطلاعات چند جدول مختلف رو به طور همزمان تحلیل کنیم که توضیح اون خارج از محدوده این مطلب هست بنابراین فعلا باهاش کاری نداریم.

بعد از اینکه تنظیمات دلخواهمون رو انجام دادیم اوکی رو کلیک میکنیم تا Pivot Table  ایجاد بشه. بعد از ایجاد Pivot Table  شما چیزی شبیه تصویر زیر خواهید داشت:

آشنایی با ابزار Pivot Table اکسل - بخش اول

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

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

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

Columns: گاهی ممکنه پیش بیاد که بخوایم داده هامون رو همزمان بر اساس بیش از یک فیلد بررسی کنیم. به عنوان مثال علاوه بر بررسی تعداد فروش بر اساس گروه اصلی و گروه فرعی محصولات، بخوایم ببینیم از تعداد فروش هر گروه محصول چه مقدار مربوط به عمده فروش و چه مقدار مربوط به مشتری نهایی هست. برای این موارد میتونیم از بخش Columns استفاده کنیم. فیلد خریدار رو با ماوس به قسمت Columns میکشیم.

Values:  در این بخش مقادیری که قصد بررسی اونها رو داریم قرار میدیم. در مثال بالا ما به دنبال تعداد فروش هستیم پس فیلد تعداد فروش رو با ماوس به این بخش میکشیم.

بعد از اینکه فیلدها رو در مکان های مناسبشون قرار دادیم چیزی شبیه شکل زیر خواهیم داشت:

آشنایی با ابزار Pivot Table اکسل - بخش اول

خب هنوز Pivot Table ما کامل نشده و نیاز به چند تا اصلاح داره. اولین نکته ای که به چشم میخوره اینه که تعداد فروش ها درست محاسبه نشده. به عنوان مثال تعدا فروش گروه محصول ابزارهای دخیره سازی برابر با عدد 9 نشون داده شده در حالی که در جدولی که داده های ما قرار دارند در مجموع تعداد 108 عدد از ابزارهای ذخیره سازی فروخته شده. این به این دلیل هست که به طور پیش فرض ابزار Pivot Table اکسل برای تجمیع فیلدهایی که در بخش Values قرار داده میشن از تابع Count استفاده میکنه (تعداد اونها رو میشماره) در حالی که مدنظر ما تابع Sum هست(به دنبال این هستیم که مقادیر تعداد فروش با هم جمع بشن). به عکس زیر دقت کنید:

آشنایی با ابزار Pivot Table اکسل - بخش اول

برای اینکه تابع تجمیع فیلد رو به Sum تغییر بدیم مثل عکس زیر روی فیلد مدنظرمون کلیک میکنیم و از منوی باز شده گزینه Value Field Settings  رو انتخاب میکنیم. با اینکار پنجره زیر باز میشه.

آشنایی با ابزار Pivot Table اکسل - بخش اول

آشنایی با ابزار Pivot Table اکسل - بخش اول

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

آشنایی با ابزار Pivot Table اکسل - بخش اول

تنها چیزی که باقی میمونه فیلتر کردن فلش اچ پی با کد 1610 هست. برا اینکار در قسمت بالای Pivot Table جایی که فیلد کد قرار داره کلیک میکنیم و پس از باز شدن منو، ابتدا گزینه Select Multiple Items رو کلیک میکنیم و بعد تیک مربوط به کد 1610 رو برمیداریم.

آشنایی با ابزار Pivot Table اکسل - بخش اول

با اینکار تعداد فروش مربوط به فلش اچ پی با کد 1610 در گزارش Pivot Table ما لحاظ نمیشه.

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

اگر به خاطر داشته باشید در بخش قبل PivotTable اکسل ما به شکل زیر در اومد:

آشنایی با ابزار PivotTable اکسل - بخش دوم

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

آشنایی با ابزار PivotTable اکسل - بخش دوم

  • تب Analyze: که میتونیم تنظیمات رو درباره بخش های مختلف pivotTable در اون انجام بدیم، تغییراتی رو روی فیلدهای جدول اعمال کنیم و …
  • تب Design: که مربوط به انجام تغییرات ظاهری PivotTable مثل رنگ ردیف‌ها، رنگ سرتیترها و … هست.

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

آشنایی با ابزار PivotTable اکسل - بخش دوم

1. PivotTable Name: در این بخش میتونیم یک نام برای PivotTable خودمون انتخاب کنیم. این نام در بخش های مختلف مثل کدنویسی VBA استفاده میشه.

2. Options: با انتخاب این گزینه منوی زیر باز میشه که سه گزینه داره.

آشنایی با ابزار PivotTable اکسل - بخش دوم

  • Options: با انتخاب این گزینه پنجره تنظیمات PivotTable اکسل باز میشه که در ادامه آموزش درباره این پنجرهصحبت خواهیم کرد
  • Show Report Filter Pages: این گزینه از فیلدهایی که در بخش فیلتر  وجود دارند
  • Generate GetPivotData: که مربوط به تنظیمات نوع رفرنس دادن به سلول های PivotTable هست.

حالا روی گزینه Options کلیک کنید. پنجره زیر باز میشه:

آشنایی با ابزار PivotTable اکسل - بخش دوم

در ادامه با هم مهم ترین و کاربردی ترین تنظیمات این پنجره رو مرور میکنیم.

1. تب Layout & Format:

آشنایی با ابزار PivotTable اکسل - بخش دوم

  • For error values show: با انتخاب این گزینه میتونیم عبارتی رو تعیین کنیم که در صورت وجود خطا در مقادیر موجود در PvotTable به جای اون مقدار نمایش داده بشه.
  • For empty cells show: با انتخاب این گزینه میتونیم عبارتی رو تعیین کنیم که در صورت وجود مقدار خالی در PvotTable به جای سلول خالی نمایش داده بشه.
  • Autofit column widths on update: اگر این گزینه فعال باشه، با هر بار Refresh کردن PivotTable ، عرض ستون ها با توجه به مقادیر جدید به صورت خودکار تنظیم میشه.
  • Preserve cell formatting on update: اگر این گزینه فعال باشه فرمت بندی هایی که رو رنگ و … سلول ها انجام دادیم با Refresh کردن Pivottable از بین نمیره.

 2. تب Total & Filters:

آشنایی با ابزار PivotTable اکسل - بخش دوم

  • Show grand totals for rows: در صورتی که این گزینه فعال باشه جمع مقادیر موجود در PivotTable در انتهای هر سطر نمایش داده میشه.
  • Show grand totals for columns: در صورتی که این گزینه فعال باشه جمع مقادیر موجود در PivotTable در انتهای هر ستون نمایش داده میشه.

3. تب Display:

آشنایی با ابزار PivotTable اکسل - بخش دوم

  • Show expand/collapse buttons: اگر این گزینه رو غیر فعال کنیم علامت های مثبت و منفی در کنار سطرهای PivotTable نمایش داده نمیشه.
  • Display field cptions and filter drop downs: اگر این گزینه رو غیرفعال کنیم نام فیلد ها و همینطور دکمه فیلتر کردن مخفی میشه.
  • Field List: در این بخش میتونیم نحوه مرتب شدن لیست فیلدها رو مشخص کنیم. منظور از لیست فیلدها همون بخش بالا و سمت راست PivotTable هست که نام فیلدها در اون نمایش داده میشه و میتونیم اونها رو به بخش های مختلف اختصاص بدیم. اگر گزینه Sort A to Z رو انتخاب کنیم فیلدها به ترتیب نام مرتب میشن و در صورتی که گزینه Sort in datasource order رو انتخاب کنیم فیلدها به همون ترتیبی که در شیت مرجع ما قرار دارند نماش داده میشن.این گزینه زمانی که فیلدهای زیادی داریم میتونه خیلی مفید باشه و به ما در پیدا کردن فیلد موردنظرمون کمک کنه.

4. تب Printing:

آشنایی با ابزار PivotTable اکسل - بخش دوم

  • Print expand/collapse buttons when displayed on PivotTable: با انتخاب این گزینه علامتهای مثبت و منی کنار هر سطر هنگام پرینت PivotTable چاپ میشن.
  • Repeat row labels on each printed page و Set print titles: در صورتی که این دو گزینه رو فعال کنیم و PivotTable ما هنگام چاپ بیش از یک صفحه باشه، عنوان فیلدها در هر صفحه بالای سطرها چاپ میشه.

5. تب Data:

آشنایی با ابزار PivotTable اکسل - بخش دوم

  • Save source data with file: اگر این گزینه فعال باشه و ما برای ایجاد PivotTable خودمون از منبع خارجی (مثل access و SQL Server) اسفاده کرده باشیم، اطلاعات اتصال به اون مبع در درون فایل اکسل ما ذخیره میشه.
  • Refresh data when opening file: با انتخاب این گزینه زمانی که ما فایل اکسمون رو باز میکنیم PivotTable ما به صورت خودکار Refresh میشه.
  • Enable show details: در صورتی که این گزینه فعال باشه کاربر میتونه با راست کلیک کردن روی هر بخش از اطلاعات PivotTable و انتخاب گزینه Show details، اطلاعات مربوط به اون سلول رو در شیت اصلی که ما PivotTable خودمون رو بر اساس اون ساختیم ببینه. اگر بخوایم این امکان رو غیر فعال کنیم کافیه تیک مربوط به این گزینه رو برداریم.

6. تب Alt Text:

آشنایی با ابزار PivotTable اکسل - بخش دوم

در این بخش میتونیم توضیحات و اطلاعاتی راجع به PivotTable خودمون ارائه بدیم تا کسانی که نمیتونند به دلیل معلولیت و … PivotTable رو ببینند بتونند از این توضیحات استفاده کنند.

همچنین ببینید

ثابت کردن(FREEZE) سطرها و ستون ها در اکسل

در این مطلب میخوایم نحوه ثابت کردن سطرها و ستون ها رو در اکسل یاد …

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

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