استفاده از ابزار پیوت تیبل (PivotTable) در اکسل

اگر می خواهید داده های خود را در یک جدول اکسل جداگانه داشته باشید، می توانید داده ها را با PivotTable خلاصه کنید. PivotTable یک ابزار بسیار قدرتمند است که در آن می توانید از داده های تکی استفاده کنید. شما می توانید صدها هزار داده در یک صفحه را تجزیه و تحلیل کنید که می تواند به صورت پویا تغییر یابد. این ابزاری ساده اما قدرتمند برای استفاده است.
اکسل به شما یک روش قوی تر برای ایجاد PivotTable از جداول متعدد، منابع داده های مختلف و منابع داده های خارجی می دهد که به عنوان Power PivotTable نامیده می شود و در پایگاه داده ای که به نام Data Model شناخته می شود کار می کند. شما در مورد Power PivotTable و دیگر ابزارهای اکسل در مقالات دیگر بحث خواهیم کرد.
PivotTables، Power PivotTables، Power PivotCharts و Power View Reports، برای نمایش نتایج خلاصه شده از مجموعه داده های بزرگ، مفید هستند.

ایجاد PivotTable

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

sales data

برای ایجاد یک PivotTable از این محدوده داده، موارد زیر را انجام دهید:

  • اطمینان حاصل کنید که ردیف اول دارای هدر است. شما نیاز به هدر دارید زیرا نام فیلدها در PivotTable شماست.
  • data range به صورت SalesData_Range می باشد.
  • روی SalesData_Range کلیک کنید.
  • روی insert کلیک کنید.
  • روی جدول PivotTable در گروه جداول کلیک کنید.

کادر محاوره ای PivotTable ایجاد می شود.

pivottable

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

  • بر روی Select a table or range کلیک کنید.
  • در کادرTable/Range box، نام را تایپ کنید SalesData_Range.
  • بر روی قسمت انتخاب شده جدید که آنجا می خواهید گزارش PivotTable قرار گیرد، کلیک کنید.

همچنین می توانید analyze multiple tables را با اضافه کردن data range  به Data Model انتخاب کنید. Data Model دیتابیس Power Pivot در اکسل است.

create pivottable

  • روی دکمه OK کلیک کنید. یک شیت جدید اضافه خواهد شد. برگه جدید حاوی PivotTable خالی است.
  • آن را نامگذاری کنید: Range-PivotTable

new worksheet

همانطور که می بینید، لیست فیلد های PivotTable Fields در سمت راست برگه ظاهر می شود که حاوی نام هدر ستون ها در محدوده داده است. علاوه بر این، بر روی نوار، ابزارپ ANALYZE - PivotTable Tools و DESIGN ظاهر می شود.
شما باید فیلد PivotTable را بر اساس آنچه که می خواهید نمایش دهید انتخاب کنید. با قرار دادن فیلد در مکان های مناسب، می توانید طرح مورد نظر برای داده ها را بدست آورید. به عنوان مثال برای خلاصه کردن مقدار سفارش فروشنده، برای ماه های ژانویه، فوریه و مارس، می توانید موارد زیر را انجام دهید:

  • روی فیلد فروشنده در لیست فیلد های PivotTable کلیک کنید و آن را به ROWS درگ کنید.
  • روی فیلد ماه در لیست فیلد PivotTable کلیک کنید و آن را نیز به ROWS درگ کنید.
  • بر روی مقدار سفارش کلیک کنید و آن را به قسمت ∑ VALUES درگ کنید.

pivottable fields

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

فیلتر کردن داده ها در PivotTable

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

  • فیلتر کردن با استفاده از Report Filters
  • فیلتر کردن با استفاده از Slicers
  • فیلتر کردن داده ها به صورت manually
  • فیلتر کردن با استفاده از Label Filters
  • فیلتر کردن با استفاده از Value Filters
  • فیلتر کردن با استفاده از Date Filters
  • فیلتر کردن با استفاده از Top 10 Filter
  • فیلتر کردن با استفاده از Timeline

شما می توانید یک فیلتر را به یکی از فیلدها اختصاص دهید تا بتوانید به صورت پویا PivotTable را بر اساس مقادیر آن فیلد تغییر دهید.

  • فیلد Region را به FILTERS area درگ کنید.
  • فیلد Salesperson را به ROWS درگ کنید.
  • فیلد Month را به COLUMNS درگ کنید.
  • فیلد Order Amount را به ∑ VALUES درگ کنید.

filtering data

فیلتر با برچسب Region در بالای PivotTable نمایش داده می شود.

filter region

همانطور که می بینید:

  • مقادیر Salesperson در سطرها ظاهر می شود.
  • مقادیر Month در ستون ها ظاهر می شود.
  • فیلتر Region در بالا ظاهر می شود و به طور پیش فرض به صورت ALL انتخاب می شود.
  • خلاصه مقادیر را در جمع Order Amount وجود دارد.
  1. مجموع مقدار سفارش فروشندگان در ستون Grand Total ظاهر می شود.
  2. مجموع مقدار سفارش ماهانه در سطر Grand Total ظاهر می شود.
  • روی فلش در Region Filter کلیک کنید.

لیست کشویی با مقادیر فیلد ظاهر می شود.

region values

  • Select Multiple Items را انتخاب کنید. موارد انتخاب شده برای تمام مقادیر ظاهر می شود. به طور پیش فرض تمام موارد در حالت انتخاب قرار دادند.
  • کادر (ALL) را از حالت انتخاب خارج کنید تا تمام موارد از حالت انتخاب خارج شوند.
  • روی South و West کلیک کنید.

multiple items

  • روی دکمه OK کلیک کنید. داده های مربوط به مناطق جنوب و غرب فقط خلاصه می شود.

selected items

همانطور که مشاهده می کنید، در سلول در کنار فیلتر منطقه (Multiple Items) نمایش داده می شود، که شما بیش از یک مقدار را انتخاب کرده اید. اما چه تعداد مقادیر و چه مقادیری که انتخاب کرده اید را نمایش نمی دهد. در چنین مواردی، استفاده از Slicers یک گزینه بهتر برای فیلتر کردن است.

استفاده از Slicers در PivotTable

فیلتر کردن با استفاده از Slicers مزایای بسیاری دارد :

  • شما می توانید چندین فیلتر را با انتخاب فیلدها در Slicers داشته باشید.
  • شما می توانید فیلدهایی را که ادر فیلتر استفاده می شود تجسم کنید (یک Slicer در هر فیلد).
  • یک Slicer دکمه هایی خواهد داشت که مقادیر فیلد را نشان می دهد. شما می توانید بر روی دکمه های Slicer کلیک کنید تا مقادیر موجود در فیلد را انتخاب کنید.
  • شما می توانید مقادیر یک فیلد را در فیلتر استفاده کنید (دکمه های انتخاب شده در Slicer هایلایت می شوند).
  • شما می توانید از Slicer رایج برای چند PivotTables و یا PivotCharts استفاده کنید.
  • شما می توانید Slicer را پنهان یا نمایش دهید.

برای آشنایی با استفاده از Slicers، PivotTable زیر را در نظر بگیرید.

usage of slicers

فرض کنید شما می خواهید این PivotTable را بر اساس فیلدهای Region و Month فیلتر کنید.

  • روی تب ANALYZE در PIVOTTABLE TOOLS روی نوار کلیک کنید.
  • بر روی Insert Slicer در Filter group کلیک کنید.

کادر محاوره ای Insert Slicers ظاهر می شود که شامل تمام فیلدهای داده شما است.

  • Region و Month را تیک بزنید.

analyse pivottable

  • روی دکمه OK کلیک کنید. برای هر یک از فیلدهای انتخاب شده، Slicer با تمام مقادیر انتخاب شده به طور پیش فرض ظاهر می شوند. ابزار slicerدر Ribbon ظاهر می شود تا روی تنظیمات Slicer کار کند.

slicer tools

همانطور که می بینید، هر Slicer دارای تمام مقادیر فیلد است که نشان می دهد و مقادیر به صورت دکمه نمایش داده می شوند. به طور پیش فرض، تمام مقادیر یک فیلد انتخاب می شوند و از این رو تمام دکمه ها هایلایت می شوند.
فرض کنید شما می خواهید PivotTable را فقط برای مناطق جنوب و غرب و ماه های فوریه و مارس نمایش دهید.

  • روی جنوب در Region Slicer کلیک کنید. فقط جنوب در Slicer - Region هایلایت خواهد شد.
  • کلید Ctrl را نگه دارید و روی West در Region Slicer کلیک کنید.
  • روی فوریه در Month Slicer کلیک کنید.
  • کلید Ctrl را نگه داشته و بر روی ماه مارس در Month Slicer کلیک کنید. مقادیر انتخاب شده در Slicers هایلایت می شوند. PivotTable برای مقادیر انتخاب شده خلاصه می شود.

selected values

برای اضافه کردن و یا حذف مقادیر یک فیلد از فیلتر، کلید Ctrl را نگه دارید و بر روی آن دکمه ها در Slicer مربوطه کلیک کنید.

 

نظرات کاربران

نظر خودتان را بنویسید