۰
(۰)

Pivot Table در اکسل

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

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

ABCDEFG
۱کدنامگروه اصلیگروه فرعیقیمتتعداد فروشخریدار
۲۱۴۳۲هارد توشیباابزار دخیره سازیهارد دیسک۲۰۰۰۰۰۱۰عمده فروش
۳۱۱۳۴هارد وسترن دیجیتالابزار دخیره سازیهارد دیسک۲۳۰۰۰۰۱۵مشتری نهایی
۴۵۴۰۰هارد سامسونگابزار دخیره سازیهارد دیسک۱۸۵۰۰۰۳مشتری نهایی
۵۳۲۱۱فلش سیلیکون پاورابزار دخیره سازیفلش مموری۲۵۰۰۰۲۱مشتری نهایی
۶۱۶۱۱فلش ای دیتاابزار دخیره سازیفلش مموری۲۳۰۰۰۱۴عمده فروش
۷۱۶۱۰فلش اچ پیابزار دخیره سازیفلش مموری۲۲۵۰۰۸عمده فروش
۸۱۲۰۰گرافیک Nvidiaکارت گرافیک۶۵۰۰۰۶مشتری نهایی
۹۱۳۴۵گرافیک ATI Radeonکارت گرافیک۷۳۰۰۰۳عمده فروش
۱۰۸۱۹۰مانیتور ال جیمانیتور۳۲۰۰۰۰۷مشتری نهایی
۱۱۲۳۴۰مانیتور سامسونگمانیتور۴۱۰۰۰۰۴عمده فروش
۱۲۴۲۵۵دی وی دی رایتر پایونییردی وی دی رایتر۹۰۰۰۰۹مشتری نهایی
۱۳۴۶۲۲دی وی دی رایتر لایت اندی وی دی رایتر۸۶۰۰۰۱عمده فروش
۱۴۴۹۹۰تبلت اچ تی سیابزار همراهتبلت۹۰۰۰۰۰۱۴عمده فروش
۱۵۵۳۵۷گوشی هوواویابزار همراهتلفن همراه۶۵۰۰۰۰۲۴مشتری نهایی
۱۶۵۷۲۴گوشی ال جیابزار همراهتلفن همراه۱۱۵۰۰۰۰۱۰عمده فروش
۱۷۱۱۳۴هارد وسترن دیجیتالابزار دخیره سازیهارد دیسک۲۳۰۰۰۰۱۷مشتری نهایی
۱۸۱۶۱۱فلش ای دیتاابزار دخیره سازیفلش مموری۲۳۰۰۰۱۱عمده فروش
۱۹۱۶۱۰فلش اچ پیابزار دخیره سازیفلش مموری۲۲۵۰۰۹مشتری نهایی
۲۰۲۳۴۰مانیتور سامسونگمانیتور۴۱۰۰۰۰۶عمده فروش
۲۱۴۲۵۵دی وی دی رایتر پایونییردی وی دی رایتر۹۰۰۰۰۳عمده فروش
۲۲۴۹۹۰تبلت اچ تی سیابزار همراهتبلت۹۰۰۰۰۰۱۲مشتری نهایی
pivottable1

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

pivottable2

بعد از انتخاب گزینه 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: این بخش در اکسل ۲۰۱۳ اضافه شده و مربوط به ویژگی جدید Data Modell هست که به ما اجازه میده اطلاعات چند جدول مختلف رو به طور همزمان تحلیل کنیم که توضیح اون خارج از محدوده این مطلب هست بنابراین فعلا باهاش کاری نداریم.

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

pivottable3

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

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

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

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

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

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

pivottable4

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

pivottable5

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

pivottable6
pivottable7

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

pivottable8

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

pivottable9

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

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

فایل مثال Pivot Table در اکسل رو می تونین در پایین دانلود کنین.

چه میزان از این مطلب رضایت داشته اید؟

میانگین ۰ / ۵. از ۰

لطف می کنین اگه رای بدین

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

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