آموزش جامع Pivot Table

راهنمای جامع Pivot table

در 15 شهریور 98 بروزرسانی گردید.

Pivot table در اکسل  یکی از قوی ترین ویژگی های مایکروسافت اکسل هست.

PIVOT TABLE اجازه می دهد تا بیش از یک میلیون ردیف داده را تنها با چند کلیک ماوس تجزیه و تحلیل کنید، نتایج را در جدول نشان می دهد، تغییر نوع گزارش به راحتی کشیدن و رها کردن فیلدها می باشد.

در این نوشته به بررسی جز به جز این ابزار قدرتمند اکسل می پردازم.

۱- ساخت Table در اکسل

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

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

6 ویژگی جداول اکسل:

  1. آدرس دهی و ارجاع ساختاریافته  ( structured reference )
  2.  سبک های مختلف جدول را می توانید با رنگ بندی های متفاوت ایجاد نمایید. (table style)
  3.  استفاده از  Total Row برای محاسبه هر ستون دلخواه با یک تابع دلخواه.
  4. لیست های کشویی که به شما اجازه مرتب سازی و فیلتر کردن را می دهند.
  5. وقتی به سمت پایین جدول اسکرول می کنید عناوین جدول به عنوان نام ستون های اکسل مورد استفاده قرار می گیرد.
  6.  حذف سریع ردیف های تکراری (remove duplicates)

ساخت Table در 2 قدم

۱- بر روی یکی از سلول ها کلیک کنید.

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

۲- با استفاده از کلید ترکیبی Ctrl + T یا اینکه از تب Insert گزینه Table را کلیک کنید تا پنجره ای به این شکل باز شود.

جدول اکسل
ساخت جدول در اکسل

جدول دلخواه شما آماده هست.



۲- ساخت Pivot Table

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

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

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

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

2 روش ساخت پیوت تیبل

۱- بر روی جدول کلیک نموده سپس از تب Insert گزینه Pivot Table را انتخاب نماییم.

ساخت Pivot Table
ساخت pivot Table

۲- یا اینکه این روند را از تب Design فعال شده است پیگیری نماییم.

ساخت pivot table
ساخت Pivot Table در اکسل

بهرحال پنجره ای به این شکل باز خواهد شد.

ساخت پیوت تیبل
ساخت پیوت تیبل

Pivot Table به راحتی ساخته خواهد شد. هر Pivot table از 4 ناحیه تشکیل می شود:

  1. Columns (ستون)
  2. Filters (فیلتر)
  3. Values (مقادیر)
  4. Rows (ردیف)
4 ناحیه هر پیوت تیبل
اجزای Pivot Table

ساخت Pivot Table

3- هر سلول Pivot Tableچگونه محاسبه می شود؟

برای اینکه بدانید هر سلولی در Pivot Table از چه اجزایی تشکیل شده است کافی است بر روی آن سلول دو بار کلیک کنید، شیت جدید باز خواهد شد که به شما نشان می دهد این سلول انتخاب شده از چه داده ها و سلول هایی تشکیل شده است.

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

اگر می خواهید از این شیتی که باز شده است خلاص شوید، CTRL + Z را فشار دهید و دکمه DELETE را بزنید.

https://exceltip.ir/product/pivot-table/?utm_source=post

4- چگونه پیوت تیبل را بروزرسانی نمایم؟

وقتی در داده های اصلی تان تغییراتی اعمال می کنید برای اعمال این تغییرات در Pivot Table باید جدول را Refresh کنید که برای این کار 3 روش وجود دارد.

3 روش بروزرسانی پیوت تیبل

  1.   از تبی که در ریبون برای شما فعال شده است مسیر مقابل را طی نمایید: PivotTable Tools > Options > Refresh
  2. همچنین کافی است تا کلیدترکیبی ALT+F5 را فشار دهید.
  3. بر روی Pivot Table راست کلیک کنید و گزینه Refresh را انتخاب کنید.
بروزرسانی پیوت تیبل
بروزرسانی Pivot table

5- حالات مختلف Subtotal در پیوت تیبل اکسل

وقتی که Pivot Table تان دارای چندین فیلد در قسمت ROW هست ، اکسل به صورت خودکار جمع آن گروه را در بالای اش قرار می دهد.

حالا آیا اکسل حالت های دیگری هم برای نمایش Subtotal دارد؟

3 حالت مختلف برای نمایش جمع جزء وجود دارد که برای استفاده از این حالت ها کافی است به PivotTable Tools > Design > Subtotals بروید.

حالت 1: جمع جزء را نمایش ندهد.

do not show subtotals
subtotal  در اکسل

حالت2: Subtotal را در پایین نمایش دهد.

ترفندهای پیوت تیبل در اکسل

حالت 3: subtotal را در بالا نمایش دهد.

پیوت تیبل در اکسل
ترفند پیوت تیبل اکسل

3 دیدگاه

پاسخی بگذارید

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