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

در این آموزش قصد داریم با چند فیلم آموزش رایگان پیوت تیبل، شما را با این ابزار فوق العاده آشنا کنیم. اگر شما هم از کاربران متوسط و حرفه ای اکسل باشید حتما نام پیوت تیبل به گوش شما خورده است. این ویژگی فوق العاده‌ی اکسل از ابزارهای قدیمی اکسل محسوب می‌شود که در سال 1994 به عنوان تابع pivot table و از سال 1997 به عنوان ابزار پیوت تیبل به اکسل اضافه شد. این ابزار در طی 20 سال گذشته دچار تغییرات و بهینه سازی های بسیاری شده است که با آن می توانید گزارش های جامعی از دیتای خود تهیه کنید و با چند کلیک یک داشبورد پویا ایجاد کنید.

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

پیوت تیبل یکی از ابزار اکسل است که با استفاده از آن میتوانید به سرعت مجموعه داده های زیادی را با چند کلیک خلاصه سازی کنید.

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

فرض کنید شما یک مجموعه داده مطابق شکل زیر دارید:

این داده ها مربوط به فروش است که شامل 1000 ردیف می باشد.

این داده ها بر اساس منطقه، نوع خرده فروش و مشتری دسته بندی شده اند.

رئیس شما از شما میخواهد که با استفاده از این داده ها موارد زیر را گزارش کنید:

• مجموع فروش در منطقه جنوب در سال 2016 چقدر بوده است؟
• پنج خرده فروش برتر از نظر فروش کدام اند؟
• عملکرد the home depot در مقایسه با سایر خرده فروشان در جنوب چگونه بوده است؟

اولین روش این است که شما با استفاده از توابع موجود در اکسل به این سؤالات پاسخ دهید. اما اگر رئیس شما ناگهان پنج سوال دیگر ارائه کند چه؟

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

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

در زیر به آموزش این ابزار مهم میپردازیم.

قسمت اول: معرفی پیوت تیبل

خیلی از مسائل در اکسل با استفاده از فرمول نویسی قابل انجام هستند اما گاهی اوقات نوشتن این فرمول ها کاری خسته کننده و گیج کننده میشود. با استفاده از پیوت تیبل و درگ و درآپ کردن (کشیدن و رها کردن نام ستون ها) به راحتی میتوانید داده های خود را تحلیل کنید؛ آن ها را به شکل های مختلف نمایش دهید و روی آن ها فرمول نویسی کنید و در نهایت با استفاده از چارت ها یا خود پیوت تیبل گزارش تهیه کنید.

به طور خلاصه وظیفه پیوت تیبل تبدیل داده های خام با استفاده از چند کلیک به داده های قابل استفاده است.

قسمت دوم: ویژگی های پیوت تیبل

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

قسمت سوم: ایجاد اولین جدول محوری (پیوت تیبل)

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

Insert> pivot table

پنجره ای باز میشود که به توضیح این پنجره می پردازیم.

Table range:  به صورت پیش فرض محدوده داده ای مورد نظر شما را انتخاب میکند.

Use a internal data sourse: علاوه بر این شما میتوانید به منبع داده ای خارجی (کانکشن های خارجی) به عنوان مثال استفاده از فایل های اکسس، اس کیو ال سرور (SQL)، مای اس کیو ال، اوراکل یا هر دیتا بیس دیگری استفاده کنید. که در دوره آموزشی پیوت تیبل با استفاده از یک فایل اکسس این کار را به شما آموزش میدهیم.

Use this workbook’s Data Model: بعد از یادگیری پاور پیوت میتوانید از این گزینه استفاده کنید و دیتا مدل های خود را به پیوت تیبل فراخوانی کنید و آنها را خلاصه سازی کنید.

اگر بخواهید پیوت تیبل خود را در برگه (شیت) جدید ایجاد کنید باید گزینه New Worksheet را انتخاب کنید و اگر تمایل دارید که پیوت تیبل در شیت داده های شما یا شیت دلخواه شما ایجاد شود گزینه Existing worksheet را انتخاب کنید و سپس در قسمت local یک سلول از شیت دلخواه خود را معرفی کنید تا پیوت تیبل را از آن قسمت ایجاد کند.

Add this data to the Data Model: این گزینه مربوط به پاور پیوت که یک بحث تخصصی است مربوط میشود. این گزینه دیتاهای شما را به پاورپیوت و دیتا مدل شما اضافه میکند. این گزینه مربوط به پاور پیوت است. برای مشاهده دوره پاور پیوت کلیک کنید.

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

بعد از ایجاد پیوت تیبل دو تب جدید به نام های Analyze و Design ایجاد میشود.

قسمت چهارم: آشنایی با پنجره فیلدها

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

در اینجا دو روش برای به نمایش در آمدن پنجره فیلدها عبارت اند از:

روش اول بر روی سلولی که برای ایجاد پیوت تیبل معرفی کرده بودید کلیک کنید (ما سلول A2 را انتخاب کرده بودیم) با کلیک بر روی این سلول، پنجره فیلدها نمایش داده میشود.

روش دوم مسیر زیر را طی کنید:

Analyze>field list

این پنجره دارای دو بخش اصلی است: 1)بخش اسم فیلدهای شما 2)بخش ساختار پیوت تیبل شما بخش اول بر اساس سطر اول داده های شما ایجاد میشود. یعنی سطر اول داده ها را به عنوان تیتر در نظر میگیرد و آنها را به عنوان فیلد در نظر میگیرد و به عنوان اسم فیلد در پنجره فیلدها برای شما به نمایش میگذارد. بخش دوم کار اصلی پیوت تیبل را انجام میدهد. این بخش ساختار اصلی پیوت تیبل است. با درگ کردن (کلیک کردن و کشیدن) فیلدها به این بخش ساختار پیوت تیبل تشکیل میشود.

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

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

قسمت ششم: ایجاد ساختار پیوت تیبل

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

قسمت هفتم: به روز رسانی پیوت تیبل در اکسل

در این قسمت با نحوه به روز رسانی پیوت تیبل آشنا خواهیم شد. برای به روز رسانی پیوت تیبل روش های مختلفی وجود دارد که در زیر به دو مورد آن اشاره میکنیم: روش اول: بر روی پیوت تیبل خود کلیک کنید، راست کلیک کنید و بر روی گزینه Refresh کلیک کنید. روش دوم: روی پیوت تیبل خود کلیک کنید؛ به تب Analyze رفته و سپس در گروه data بر روی گزینه Refresh کلیک کنید. روش سوم:

Data>connections>refresh

قسمت هشتم: استفاده از جداول در ایجاد پیوت تیبل

راه بهتر برای استفاده از پیوت تیبل ها و ایجاد سطرهای اضافی در پیوت تیبل، استفاده از جدول ها می باشد.برای تبدیل داده های خود به جدول، کافیست بر روی داده ها کلیک کنید و کلیدهای ترکیبی Ctrl+T را فشار دهید و آنها را تبدیل به جدول کنید. یا اینکه بر روی داده های خود کلیک کنید و مسیر زیر را طی کنید:
Insert>table
حال اگر اطلاعات جدید در ادامه داده های قبلی خود اضافه کنید، به صورت اتوماتیک به جدول شما اضافه میشوند. و حالا برای به روزرسانی پیوت تیبل کافیست پیوت تیبل خود را به روز کنید.
توجه: اگر بر روی داده های که به جدول تبدیل شده اند کلیک کنید و سپس پیوت تیبل خود را ایجاد کنید در قسمت dataSource پنجره مربوط به پیوت تیبل به جای محدوده داده ها، نام جدول نشان داده میشود.

آخرین مطالب آموزشی پیوت تیبل

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

• 01- ساخت پیوت تیبل (pivot table) در اکسل
• 02- واکاوی مقادیر در پیوت تیبل
• 03- محاسبه جمع جزء (subtotal) در پیوت تیبل (Pivot table) اکسل
• طراحی بصری گزارش ها در پیوت تیبل
• نمایش Sum به جای Count در پیوت تیبل
• فرمت عددی در پیوت تیبل (pivot table)
• مقادیر خطا در پیوت تیبل (pivot table)
• سلول های خالی در پیوت تیبل (pivot table)
• حفظ عرض ستون ها پس از refresh کردن پیوت تیبل
• نمایش فیلدهای Report Filter پیوت تیبل (pivot table) در صفحات جداگانه
• نمایش میانگین در پیوت تیبل (pivot table)
• شمارش مقدارهای متمایز یا منحصر به فرد در پیوت تیبل | خاصیت distinct Count
• نمایش درصد از مجموع ستون در پیوت تیبل (pivot table)
• نمایش درصد از مجموع ردیف در پیوت تیبل (pivot table)
• قابلیت Difference from در پیوت تیبل (pivot table)
• آموزش پیوت تیبل | بدست آوردن مجموع محرک در پیوت تیبل
• جمع تجمعی یا مجموع متحرک (running total) در پیوت تیبل
• روش های گروه بندی داده ها در پیوت تیبل
• آموزش کامل استفاده از اسلایسر در پیوت تیبل
• معرفی فیلد محاسباتی(calculated field) و آیتم محاسباتی(calculated item) در پیوت تیبل
• معرفی تابع Getpivotdata اکسل |استخراج داده از پیوت تیبل

ساخت پیوت تیبل در اکسل

تشخیص حالت پیوت داده ها

برای تشخیص حالت پیوت داده-ها از روش زیر استفاده میکنیم:

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

اگر همه سطرها از یک نوع داده و همه سطرها هم از یک نوع داده بودند با حالت پیوت مواجه هستیم.

این ابزار به اعتقاد من قدرتمند ترین ابزار اکسل است. و در این مطلب نحوه ساخت پیوت تیبل را با هم بررسی خواهیم کرد .

اگر از مجموعه داده یا جدول برای تحلیل اطلاعات خود استفاده میکنید. می بایست برای ارتقای توانایی های تحلیل خود و صرفه جویی در زمان از پیوت تیبل (pivot table) استفاده کنید.

پیوت تیبل (pivot table) توسط مدیران پروژه، تحلیلگران مالی، حسابرسان، کنترل کنندگان هزینه، تحلیلگران فروش و حتی پزشکان و … مورد استفاده قرار می گیرد. کار با پیوت تیبل بسیار ساده است. هر ستون دیتای شما به عنوان یک فیلد شناخته می شود. پس از ساختن پیوت تیبل کافی است با کشیدن فیلدها در ساختار پیوت تیبل، گزارش مورد نظر را تهیه کنید.

مراحل ساخت پیوت تیبل

برای ساخت پیوت تیبل گام های زیر را طی میکنیم:

گام 1: در یکی از سلول های مجموعه داده خود کلیک کنید.

گام 2: به قسمت  Insert > Pivot Table بروید.

گام3: دو گزینه پیش رو خواهید داشت. گزینه اول: میتوانید پیوت تیبل خود را در شیتی که اطلاعات در آن قرار گرفته شده ایجاد کنید. گزینه دوم: یا آن را در یک شیت جدید ایجاد کنید. چنانچه قصد دارید پیوت تیبل خود را در شیت جدید ایجاد کنید. گزینه new worksheet را انتخاب نمایید. و در انتها بر روی OK کلیک کنید.

اگر میخواهید پیوت تیبل خود را در شیت کنونی ایجاد کنید. گزینه Existing worksheet را انتخاب نمایید. در قسمت location آدرس سلولی که میخواهید از آنجا پیوت تیبل ایجاد شود وارد کنید. (دقت داشته باشید باید سلولی را انتخاب کنید که خالی است.). در انتها بر روی OK کلیک کنید.

بخش های مختلف پیوت تیبل

برای استفاده موثر از پیوت تیبل، لازم است که با بخش های مختلف آن آشنا شوید.

در ادامه با بخش های زیر از پیوت تیبل آشنا خواهیم شد:

حافظه پنهان محوری Pivot Cache

بخش ارزش ها Values Area

بخش ردیف ها Rows Area

بخش ستون ها Columns Area

بخش فیلترها Filters Area

حافظه پنهان پیوت

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

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

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

بخش ارزش ها (Values Area)

این بخش مربوط به محاسبات یا مقادیر است.

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

قسمتی که با رنگ نارنجی مشخص شده است مربوط به بخش ارزش ها (values Area) است.

در این مثال، داده ها مربوط به کل فروش هر ماه برای چهار منطقه است.

بخش ردیف ها (Rows Area)

عناوینی که در سمت چپ ناحیه Values است، بخش Rows است.

در مثال زیر، ناحیه Rows با کادر قرمز مشخص شده است.

بخش ستون ها (Columns Area)

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

در مثال زیر، بخش ستون ها شامل ماه ها است که با کادر قرمز مشخص شده اند.

ناحیه فیلترها (Filters Area)

بخش فیلترها یک فیلتر اختیاری است که میتوانید آن را برای بررسی بیشتر مجموعه داده ها استفاده کنید.

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

 در تصویر زیر به طور خلاصه چهار بخش پیوت تیبل نشان داده شده است:

گام4: حالا کافی است فیلدها را درگ کنید.

Pivot table شما آماده است.