آموزش پیوت در طلا

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

05 نوامبر آشنایی با ابزار 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، پنجره بالا باز میشه که شامل گزینه های زیر هست:

• 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 شما چیزی شبیه تصویر زیر خواهید داشت:

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

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

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

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

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

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

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

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

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

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

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

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

آموزش نحوه استفاده از پیوت تیبل Pivot Tables در مایکروسافت اکسل

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

آموزش نحوه استفاده Pivot Table در مایکروسافت اکسل

مشکلی که اکثراً با آن مواجه هستیم این است که کوهی از داده‌ ها را در اختیار داریم و به دنبال راهی هستیم تا آن را خلاصه کنیم. همه ما در جست و جوی یک راه برای درک صحیح از مجموعه‌ ای از داده‌ ها و پیدا کردن اطلاعاتی مفید در مورد وضعیت نشان داده شده از هر دسته هستیم.

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

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

آموزش نحوه استفاده Pivot Table در مایکروسافت اکسل

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

نحوه ایجاد پیوت تیبل Pivot Table در مایکروسافت اکسل

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

ابتدا تمام ستون‌ داده‌ ها را در اکسل انتخاب کنید سپس بر روی تب Insert کلیک کرده و گزینه Pivot Table را انتخاب کنید تا پنجره Create Pivot Table برای آموزش پیوت در طلا شما باز شود که شامل گزینه‌ های زیر است:

Select a table or range: با انتخاب این گزینه می توانید یک محدوده را برای تجزیه و تحلیل داده ها انتخاب کنید.

Use an external data source: با استفاده از این گزینه می‌ توانید داده هایی را که از قبل در نرم افزار دیگری آماده کرده‌اید به مایکروسافت اکسل وارد کنید.

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

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

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

نحوه ایجاد Pivot Table در مایکروسافت اکسل

خب اکنون شما یک صفحه جدید در اکسل دارید که Pivot Table را در آن ایجاد کرده‌ اید. در سمت راست صفحه اکسل شما یک کادر report builder وجود دارد که ابزاری برای ایجاد ساخت سفارشی Pivot Table می ‌باشد.

نحوه ایجاد Pivot Table در مایکروسافت اکسل

نحوه استفاده از ساختار پیوت تیبل Pivot Table در اکسل

مهمترین نکته استفاده از Pivot Table درک report builder می‌باشد. اجازه دهید اول یاد بگیریم که این منو چگونه کار می ‌کند.

کار با Pivot Table Fields

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

در سمت راست قسمت بالا Pivot Table، نام فیلدهای جدول داده‌های صفحه اکسل شما قرار گرفته است. هر ستون از داده‌های اصلی آموزش پیوت در طلا شما به عنوان یک فیلد با نام همان عنوان نشان داده می ‌شود. شما می ‌توانید هر فیلد را به داخل چهار جعبه Filters، Columns، Rows و Values بکشید. در ادامه این چهار بخش را به شما معرفی می‌ کنیم.

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

۱-‌ Filters

وقتی که شما یک فیلد را به جعبه Filters می کشید، می ‌توانید از آن فیلد برای فیلتر کردن گزارش یا اطلاعات خود استفاده کنید. برای درک بیشتر این گزینه به مثال زیر توجه کنید:

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

در تصویر زیر ما فیلد Client را به درون جعبه Filters می ‌کشیم. در قسمت بالای Pivot Table یک ستون با نام Client ایجاد می‌شود. وقتی که شما بر روی منوی کشویی کلیک می ‌کنید، لیست Client را مشاهده خواهید کرد و می ‌توانید گزینه مورد نظر خود را که می‌ خواهید آن را فیلتر کنید، انتخاب کنید. حال بر روی Client دلخواه خود کلیک کرده و سپس گزینه Ok را انتخاب کنید.

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

شما می ‌توانید ببینید که Pivot Table فیلتر و در حال حاضر فقط یک Client برای ما آموزش پیوت در طلا انتخاب شده است. همچنین شما می ‌توانید گزینه checking the Select Multiple را انتخاب کنید تا گزینه‌ های دیگری را که قصد دارید فیلتر کنید را به آن اضافه کنید.

۲-‌ Rows

ردیف‌ هایی هستند که در سمت چپ یک Pivot Table ظاهر می ‌شوند. هنگامی که شما یک فیلد را به درون جعبه Rows می ‌کشید، هر یک از مقادیر آن فیلد آموزش پیوت در طلا به صورت یک فیلد نمایش داده می ‌شود. در شکل زیر می‌توانید دو پیکربندی متفاوت را مشاهده کنید. برای مثال وقتی که ما Client را به جعبه Rows وارد می‌کنیم نام هر کدام از آن‌ها به صورت ردیفی در Pivot Table نشان داده خواهد شد.

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

در تصویر بالا، ما Client را وارد جعبه Rows کردیم و Pivot Table هر کدام از نام ‌ها را در یک ردیف قرار داده است. در تصویر پایین Client بر داشته شده است و به جای آن Project type وارد شده است.

۳-‌ Columns

باکشیدن هر فیلد به داخل جعبه Columns، یک ستون جداگانه برای هر کدام از داده های شما ایجاد خواهد شد. در مثال زیر، ما فیلد Client را به عنوان ردیفی در my Pivot Table نگه داشته ایم. برای به دست آوردن جزئیات بیشتر از داده ها و اطلاعات خود ما Project Type را به Columns اضافه می ‌کنیم تا بتوانیم به طور همزمان داده ها و اطلاعات را بررسی کنیم.

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

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

۴-‌ Values

شما می‌ توانید ترکیب پیچیده ای از ردیف آموزش پیوت در طلا ها و ستون ها را بسازید اما Pivot Table وقتی معنا پیدا می ‌کند که شما یک فیلد را درون جعبه Values بکشید.

کار با فیلد Values متفاوت ‌تر از بقیه فیلد ها است. شما نمی ‌توانید هر فیلدی را درون جعبه values بکشید و نتایج خوبی را به دست آورید. این جعبه برای مقادیر عددی داده های شما مانند مقدار دلار یا میزان ساعت صرف شده و … ایجاد شده است.

Pivot Table به شما اجازه می‌دهد تا با راه‌ های مختلف با مقادیر کار کنید. وقتی شما یک فیلد را به درون جعبه Values می‌کشید، اغلب پیش فرض موجود در اکسل بر روی Count تنظیم شده است. برای مثال، هر بار به تعداد هر آیتم را در Pivot Table چاپ می ‌کند.

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

اما اگر شما بخواهید تعدا شمارش ها نشان داده نشود چه؟ یا بهتر است بگوییم که اگر ما بخواهیم مجموع مقدارها یا حساب های هر ردیف را نشان داده شود باید چه کار کنیم؟ انجام این کار بسیار آسان است، بر روی Pivot Table راست کلیک کرده و سپس گزینه Summarize Values By را انتخاب و پس از آن بر روی Sum کلیک کنید. در تصویر بالا مشاهده خواهید کرد که Pivot Table مجموع کامل مقادیر را برای هر مشتری نشان می‌دهد.

در حال حاضر ما یک Pivot Table را ایجاد کردیم و با گزینه‌ ها موجود در آن آشنا شدیم. اما اکنون چگونه می توانیم این داده ها را به اطلاعات مفید و قابل درک تبدیل کنیم؟

نگهداری اطلاعات موجود در Pivot Table برای تجزیه و تحلیل داده ها امری بسیار ضروری است. قبل از انجام این کار اجازه دهید دو ویژگی مهم را در Pivot Table بررسی کنیم.

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

ابتدا Pivot Table را انتخاب و به تب Analyze بروید و بر روی گزینه Change Data Source کلیک کنید حال شما می‌توانید یک ستون جدید را انتخاب و در آن تایپ کنید یا بر روی ستون ها یا ردیف ها کلیک کنید و اطلاعات جدید خود را وارد کنید.

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

برای بازسازی یا رفرش Pivot Table، ابتدا Pivot Table را انتخاب کنید سپس در راست کلیک کرده و Refresh را انتخاب کنید و یا می ‌توانید بر روی تب Analyze کلیک کنید و گزینه Refresh را انتخاب کنید.

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

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