سری آموزش های Pivot Table (قسمت دوم)

بهبود ظاهر پیوت تیبل با اعمال تنظیمات بیشتر

در بخش اول این مقاله با کاربردها، اصول و مفاهیم پایه ای Pivot Table آشنا شدیم و با استفاده از یک نمونه بانک اطلاعاتی فروش لوازم خانگی نحوه ساخت چند گزارش با استفاده از Pivot Table رو به صورت گام به گام توضیح دادیم. در بخش دوم این مقاله قصد داریم مطالب تکمیلی در خصوص تنظیمات Pivot Table که به بهبود ساخت گزارش ها و ظاهر پیوت تیبل کمک می کنه ارائه کنیم.

معرفی تب های Analyze و Design در ابزار Pivot Table  

در نسخه 2013، 2016 و 2019 وقتی روی یکی از فیلدهای Pivot Tab کلیک می کنیم در نوار بالایی اکسل تب های Analyze و Design (در نسخه 2007 و 2010 تب های Options و Design وجود دارد) فعال می شن. مطابق شکل 1

شکل 1- تب های Analyze و Design در Pivot Table

برای اینکه ظاهر Pivot Table رو زیباتر کنیم از امکانات موجود در تب Design استفاده می کنیم. در تب Design الگوهای (Styles) از پیش تعریف شده ای برای طراحی Pivot Table وجود داره که می تونیم از آن ها استفاده کنیم یا در صورت نیاز الگوهای دلخواه خودمون رو بسازیم. برای ساخت الگوها با فرمت جدید و دلخواه، در تب Design و از زیرمجموعه PivotTable Styles گزینه “New PivotTable Style…” رو انتخاب می کنیم. حالا کافیه از پنجره نمایش داده شده تنظیمات گرافیکی مربوط به هر قسمت از گزارش رو تعیین کنیم. مثلا میخواهیم تعیین کنیم که سلول های کل جدول به چه صورت باشه. روی گزینه Whole Table کلیک میکنیم و از قسمت Format رنگ و تنظیمات دلخواه رو برای سلول های گزارش تعیین میکنیم. همین کار رو برای اجزای مختلف گزارش مثل ستون اول، ردیف سرستون ها و … انجام میدیم.

شکل 2- Pivot Table Styles برای تغییر ظاهر پیوت تیبل

تغییر ساختار (چیدمان) گزارش

برای اینکه ساختار (چیدمان) یک گزارش رو به شکل دلخواه در بیاریم از تب Design و از قسمتReport Layout میتونیم یکی از سه ساختار Compact, Outline و Tabular رو انتخاب کنیم. حالت Compact ساختار پیشفرض Pivot Table برای گزارش های طراحی شده است. در شکل 3 این سه حالت نمایش داده شده است.

شکل 3- نحوه تغییر Layout و ظاهر پیوت تیبل

اگر بر روی فیلد مورد نظر در Pivot Table کلیک کنیم و از تب Analyze مطابق شکل 4 گزینه Field Settings رو انتخاب کنیم میتونیم ساختار زارش رو به حالت Tabular تغییر بدیم. همچنین می تونیم روی فیلد مورد نظر کلیک راست کرده و از لیست نمایش داده شده گزینه Field Settings رو انتخاب کنیم.

شکل 4- نحوه فعال کردن Field Setting از تب Analyze

همون طور که در شکل 4 نمایش داده شده با انتخاب گزینه Show item labels in tabular form هر کدوم از اسامی محصولات به شکل یک نوار در جدول اضافه شدن.

وقتی که Pivot Table می سازیم اکسل به صورت خودکار عناوین Column Label و Row Label رو به جدول اضافه می کنه مطابق شکل 5. برای حذف این عناوین اضافی دو روش وجود داره:

شکل 5- Column Label و Row Label در Pivot Table

روش اول: اگر ساختار گزارش رو به Tabular تغییر بدیم، این کلمات حذف میشن و بجای آنها نام فیلد مورد نظر نمایش داده میشه. (شکل 6)

همچنین میتونیم روی کلمه Row Labels دبل کلیک کنیم و در حالت فعال سلول با کلید Backspace عبارت Row Label و Column Label رو حذف کرده و Enter کنیم.

شکل 6- حذف کلمات Row Label و Column Label جهت بهبود ظاهر پیوت تیبل

روش دوم: از تب Analyze و از زیر مجموعه Show روی گزینه Field Headers کلیک می کنیم (مطابق شکل 7) با اینکار علاوه بر Column Label و Row Label، زبانه مربوط به فیلتر ها هم حذف می شن.

شکل 7- حذف Column Label و Row Label با استفاده از تب Analyze

بروزرسانی (Refresh) Pivot Table

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

بروزرسانی Pivot Table به صورت دستی

مراحل انجام کار به شکل زیر هست:

• روی یکی از فیلدهای Pivot Table کلیک می کنیم؛
• از تب Analyze و از زیر مجموعه Data، روی گزینه Refresh کلیک می کنیم (مطابق شکل 8) یا دکمه های ALT+F5 رو به طور همزمان استفاده می کنیم.

شکل 8- بروزرسانی Pivot Table با استفاده از تب Analyze

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

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

شکل 9- گزینه های انتخابی برای جلوگیری از تغییر فرمت Pivot Table بعد از بروزرسانی

در صورتی که در حین بروزرسانی Pivot Table از انجام اینکار منصرف بشیم، می تونیم با انتخاب یکی از گزینه های Refresh Status یا Cancel Refresh فرآیند بروزرسانی رو متوقف کنیم. برای انتخاب یکی از این گزینه ها از تب Analyze و از زیر مجموعه Data روی گزینه Refresh کلیک می کنیم و از لیست نمایش داده شده یکی از دو گزینه Refresh Status یا Cancel Refresh رو انتخاب می کنیم.

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

مراحل انجام کار به شکل زیر هست:

• از تب Analyze و از زیر مجموعه PivotTable روی گزینه Options کلیک می کنیم؛
• در پنجره PivotTable Options و در تب Data تیک گزینه Refresh data when opening the file رو زده (مطابق شکل10) و دکمه OK رو می زنیم.

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

انتقال PivotTable به مکانی دیگر

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

شکل 11- انتقال Pivot Table

حذف PivotTable

برای حذف کردن PivotTable می تونیم از یکی از روش های زیر استفاده کنیم:

• اگر PivotTable در یک شیت جداگانه قرار گرفته با حذف کردن شیت مربوطه، PivotTable هم حذف خواهد شد.
• اگر PivotTable به همراه داده های دیگری در یک شیت قرار گرفته، برای حذف جدول کافیه با موس محدوده PivotTable رو انتخاب کنیم و دکمه Delete رو بزنیم.
• روی یکی از فیلدهای PivotTable کلیک می کنیم و از تب Analyze و زیر مجموعه Actions روی گزینه Select کلیک می کنیم و از لیست نمایش داده شده مطابق شکل 12 گزینه Entire PivotTable رو انتخاب می کنیم و بعد دکمه Delete رو می زنیم.

شکل 12- حذف کردن PivotTable

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

مشاهده دوره صفر تا صد پیوت تیبل

مثال هایی از Pivot Table

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

مثال 1- Pivot Table دو بعدی

همان طور که در شکل 13 نشان داده شده، در سطرها (Rows) “فروشنده” و “نام محصول” قرار گرفته، در ستون ها (Columns) “ماه” قرار گرفته و در قسمت فیلتر هم فیلدی انتخاب نشده.

شکل 13- نمونه ای از PivotTable دو بعدی

مثال 2- نمونه ای از Pivot Table سه بعدی

همان طور که در شکل 14 نشان داده شده، در سطرها (Rows) “فروشنده” قرار گرفته، در ستون ها (Columns) “نام محصول” قرار گرفته و در قسمت فیلتر “ماه” انتخاب شده. تفاوت Pivot Table سه بعدی با دوبعدی در قسمت Filters هست . اگر مایل به فیلتر کردن گزارش باشیم، فیلدی رو در قسمت فیلتر قرار میدیم و با استفاده از اون گزارش رو فیلتر میکنیم و با این کار پیوت تیبل سه بعدی ایجاد کردیم. اگر مایل به فیلتر نباشیم، فیلدی در این قسمت قرار نمیگیره و گزارش ما دو بعدی خواهد بود.

شکل 14- نمونه ای از Pivot Table سه بعدی

مثال 3- نمایش داده های یک فیلد به دو صورت در Pivot Table

در این مثال می خواهیم داده های مربوط به فروش رو به دو صورت “مجموع فروش” و “درصد از فروش” نمایش بدیم برای اینکار مطابق ویدئو زیر فیلدها رو به بخش های Rows و Values اختصاص میدیم. دقت کنید برای اینکه فیلد فروش رو به دو شکل نمایش بدیم لازم هست که دوبار اون رو به قسمت Values منتقل کنیم و بعد تنظیمات مربوط به نحوه نمایش داده رو انجام بدیم. جدول نهایی در شکل 15 نمایش داده شده:

شکل 15- نمایش فروش به دو شکل “مجموع فروش” و “درصد از فروش”

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