اکسل پدیا، دانشنامه پارسی اکسل

021-47625755
[email protected]

ورود یا ثبت نام

  • دانشنامه اکسل
    • اکسل چیست؟
    • اکسل مقدماتی
    • توابع اکسل
    • نمودار ها
    • وی بی – ماکرو
  • آکادمی اکسل
    • ویدئوهای آموزشی
      • رسم نمودارهای حرفه ای
      • اکسل و شروع حرفه ای
      • اکسل نینجا (اکسل پیشرفته)
      • صفر تا صد Pivot Table
      • برنامه نویسی وی بی (VBA)
    • کتاب ها
      • کتاب های فارسی
      • کتاب های انگلیسی
  • داشبورد های مدیریتی
    • آموزش ساخت داشبورد
    • دانلود داشبورد نمونه
  • افزونه های کاربردی
  • دوره های حضوری
    • اکسل نینجا
    • برنامه نویسی وی بی (VBA)
  • درباره ما
    • Cart

      0

ابزارها

  • خانه
  • بلاگ
  • ابزارها
  • سری آموزش های Pivot Table (قسمت دوم)

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

  • ارسال شده توسط سامان چراغی
  • دسته بندی ابزارها
  • تاریخ ۱۲ بهمن ۱۳۹۹
  • نظرات ۰ نظر
آموزش Pivot Table
نظر شما در مورد این آموزش

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

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

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

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

تب های Analyze و Design در Pivot Table برای انجام تغییرات در ظاهر پیوت تیبل

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

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

 

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

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

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

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

نحوه تغییر Layout و ظاهر پیوت تیبل

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

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

حتما بخوانید:  چهار گام ایجاد پیوت تیبل (Pivot Table)

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

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

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

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

Column Label و Row Label در Pivot Table

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

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

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

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

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

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

حذف Column Label و Row Label با استفاده از تب Analyze جهت تغییر ظاهر پیوت تیبل

شکل 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 رو به طور همزمان استفاده می کنیم.

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

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

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

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

نکته:
در صورتی که بعد از بروزرسانی، فرمت Pivot Table تغییر کرد حتما چک کنید که دو گزینهAutofit column width on update و Preserve cell formatting on update انتخاب شده باشن. برای دسترسی به این این گزینه ها از تب Analyze و از زیر مجموعه PivotTable روی گزینه Options کلیک می کنیم و در پنجره PivotTable Options مطابق شکل 9 در بخش Layout & Format دو گزینه مورد نظر رو تیک می زنیم. در واقع این دو گزینه کمک میکنن که بعد از بروزرسانی ظاهر گزارش به لحاظ کوچک و بزرگبودن آیتم ها و فرمت گزارش تغییر نکنه و بهم نریزه.

 

حتما بخوانید:  یکپارچه کردن داده ها در اکسل

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

شکل 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 رو می زنیم.

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

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

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

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

انتقال Pivot Table

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

حذف PivotTable

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

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

حذف کردن PivotTable

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

pie-chart (1)

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

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

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

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

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

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

نمونه ای از PivotTable دو بعدی

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

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

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

نمونه ای از Pivot Table سه بعدی

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

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

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

انتقال فیلدها به بخش های پیوت تیبل

نمایش فروش به دو شکل "مجموع فروش" و "درصد از فروش" با ظاهر پیوت تیبل زیبا

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

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

برچسب:پیوت

  • اشتراک گذاری:
profile avatar
سامان چراغی
من سامان چراغی هستم. دانش آموخته مقطع فوق لیسانس دانشگاه تربیت مدرس در رشته مهندسی صنایع. از سال 1388 اکسل و برنامه نویسی VBA رو به صورت حرفه ای شروع کردم.

مطلب قبلی

سری آموزش های Pivot Table (قسمت اول)
۱۲ بهمن ۱۳۹۹

مطلب بعدی

همه چیز در مورد Slicer در اکسل
۱۲ بهمن ۱۳۹۹

ممکن است همچنین دوست داشته باشید

Distinct-and-Unique-Values-Cover
تفاوت داده Distinct و Unique چیست؟ چطور میشه مشخص کرد
۱۰ فروردین, ۱۴۰۰
Broken-Links-Cover
۳ روش برای شناسایی و رفع مشکل لینک های معیوب در اکسل
۱ اسفند, ۱۳۹۹
Slicer Cover
همه چیز در مورد Slicer در اکسل
۱۶ بهمن, ۱۳۹۹

نظر بدهید لغو پاسخ

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

-- بارگیری کد امنیتی --

زودتر از دیگران با خبر بشید

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

آخرین مطالب

مقادیر متمایز در اکسل
تفاوت داده Distinct و Unique چیست؟ چطور میشه مشخص کرد
۱۰فروردین۱۴۰۰
استفاده از چندین تابع IF اکسل در هم
توابع IF تو در تو و نکات مهمی که باید بدانید
۲۷اسفند۱۳۹۹
تابع Trim
تابع Trim در اکسل
۲۱اسفند۱۳۹۹
کاربرد Power Query
چند مثال کاربردی از ابزار Power Query
۱۵اسفند۱۳۹۹

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

پاسخ سوالات شما

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

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

ورود به تالار گفتگو تلگرام

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

  • این فیلد برای اعتبار سنجی است و باید بدون تغییر باقی بماند .

021-47625755

[email protected]

آدرس: تهران، میدان دوم صادقیه، برج گلدیس، طبقه 7 واحد 721

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

تمامی حقوق برای اکسل پدیا محفوظ است.

  • اکسل از راه دور
  • شرایط و قوانین
  • درباره ما
  • تماس با ما