سری آموزش های Pivot Table (قسمت دوم)
بهبود ظاهر پیوت تیبل با اعمال تنظیمات بیشتر
در بخش اول این مقاله با کاربردها، اصول و مفاهیم پایه ای Pivot Table آشنا شدیم و با استفاده از یک نمونه بانک اطلاعاتی فروش لوازم خانگی نحوه ساخت چند گزارش با استفاده از Pivot Table رو به صورت گام به گام توضیح دادیم. در بخش دوم این مقاله قصد داریم مطالب تکمیلی در خصوص تنظیمات Pivot Table که به بهبود ساخت گزارش ها و ظاهر پیوت تیبل کمک می کنه ارائه کنیم.
معرفی تب های Analyze و Design در ابزار Pivot Table
در نسخه ۲۰۱۳، ۲۰۱۶ و ۲۰۱۹ وقتی روی یکی از فیلدهای Pivot Tab کلیک می کنیم در نوار بالایی اکسل تب های Analyze و Design (در نسخه ۲۰۰۷ و ۲۰۱۰ تب های Options و Design وجود دارد) فعال می شن. مطابق شکل ۱
شکل ۱- تب های Analyze و Design در Pivot Table
برای اینکه ظاهر Pivot Table رو زیباتر کنیم از امکانات موجود در تب Design استفاده می کنیم. در تب Design الگوهای (Styles) از پیش تعریف شده ای برای طراحی Pivot Table وجود داره که می تونیم از آن ها استفاده کنیم یا در صورت نیاز الگوهای دلخواه خودمون رو بسازیم. برای ساخت الگوها با فرمت جدید و دلخواه، در تب Design و از زیرمجموعه PivotTable Styles گزینه “New PivotTable Style…” رو انتخاب می کنیم. حالا کافیه از پنجره نمایش داده شده تنظیمات گرافیکی مربوط به هر قسمت از گزارش رو تعیین کنیم. مثلا میخواهیم تعیین کنیم که سلول های کل جدول به چه صورت باشه. روی گزینه Whole Table کلیک میکنیم و از قسمت Format رنگ و تنظیمات دلخواه رو برای سلول های گزارش تعیین میکنیم. همین کار رو برای اجزای مختلف گزارش مثل ستون اول، ردیف سرستون ها و … انجام میدیم.
شکل ۲- Pivot Table Styles برای تغییر ظاهر پیوت تیبل
تغییر ساختار (چیدمان) گزارش
برای اینکه ساختار (چیدمان) یک گزارش رو به شکل دلخواه در بیاریم از تب Design و از قسمتReport Layout میتونیم یکی از سه ساختار Compact, Outline و Tabular رو انتخاب کنیم. حالت Compact ساختار پیشفرض Pivot Table برای گزارش های طراحی شده است. در شکل ۳ این سه حالت نمایش داده شده است.
شکل ۳- نحوه تغییر Layout و ظاهر پیوت تیبل
اگر بر روی فیلد مورد نظر در Pivot Table کلیک کنیم و از تب Analyze مطابق شکل ۴ گزینه Field Settings رو انتخاب کنیم میتونیم ساختار زارش رو به حالت Tabular تغییر بدیم. همچنین می تونیم روی فیلد مورد نظر کلیک راست کرده و از لیست نمایش داده شده گزینه Field Settings رو انتخاب کنیم.
شکل ۴- نحوه فعال کردن Field Setting از تب Analyze
همون طور که در شکل ۴ نمایش داده شده با انتخاب گزینه Show item labels in tabular form هر کدوم از اسامی محصولات به شکل یک نوار در جدول اضافه شدن.
وقتی که Pivot Table می سازیم اکسل به صورت خودکار عناوین Column Label و Row Label رو به جدول اضافه می کنه مطابق شکل ۵. برای حذف این عناوین اضافی دو روش وجود داره:
شکل ۵- Column Label و Row Label در Pivot Table
روش اول: اگر ساختار گزارش رو به Tabular تغییر بدیم، این کلمات حذف میشن و بجای آنها نام فیلد مورد نظر نمایش داده میشه. (شکل ۶)
همچنین میتونیم روی کلمه Row Labels دبل کلیک کنیم و در حالت فعال سلول با کلید Backspace عبارت Row Label و Column Label رو حذف کرده و Enter کنیم.
شکل ۶- حذف کلمات Row Label و Column Label جهت بهبود ظاهر پیوت تیبل
روش دوم: از تب Analyze و از زیر مجموعه Show روی گزینه Field Headers کلیک می کنیم (مطابق شکل ۷) با اینکار علاوه بر Column Label و Row Label، زبانه مربوط به فیلتر ها هم حذف می شن.
شکل ۷- حذف Column Label و Row Label با استفاده از تب Analyze
بروزرسانی (Refresh) Pivot Table
با وجود اینکه Pivot Table به بانک اطلاعاتی متصل هست در صورت تغییر بانک اطلاعاتی، اکسل به طور خودکار Pivot Table رو بروزرسانی نمی کنه. برای بروز رسانی Pivot Table هم می تونیم این کار رو به صورت دستی انجام بدیم و هم تنظیمات جدول رو به گونه ای انجام بدیم که وقتی فایل اکسل رو باز می کنیم Pivot Table به صورت خودکار بروزرسانی بشه. در ادامه هر دو روش رو توضیح میدیم.
بروزرسانی Pivot Table به صورت دستی
مراحل انجام کار به شکل زیر هست:
- روی یکی از فیلدهای Pivot Table کلیک می کنیم؛
- از تب Analyze و از زیر مجموعه Data، روی گزینه Refresh کلیک می کنیم (مطابق شکل ۸) یا دکمه های ALT+F5 رو به طور همزمان استفاده می کنیم.
شکل ۸- بروزرسانی 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 مطابق شکل ۹ در بخش Layout & Format دو گزینه مورد نظر رو تیک می زنیم. در واقع این دو گزینه کمک میکنن که بعد از بروزرسانی ظاهر گزارش به لحاظ کوچک و بزرگبودن آیتم ها و فرمت گزارش تغییر نکنه و بهم نریزه.
شکل ۹- گزینه های انتخابی برای جلوگیری از تغییر فرمت 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 رو زده (مطابق شکل۱۰) و دکمه OK رو می زنیم.
شکل ۱۰- تنظیم بروزرسانی PivotTable به صورت خودکار پس از باز شدن فایل اکسل
انتقال PivotTable به مکانی دیگر
در صورتی که بخواهیم PivotTable رو به مکانی دیگر مثلا به یک فایل اکسل جدید، یک شیت دیگر منتقل کنیم یا حتی در شیت فعلی جابه جا کنیم از تب Analyze و از زیر مجموعه Actions گزینهMove PivotTable رو انتخاب می کنیم. پس از انتخاب این گزینه پنجره ای مطابق شکل ۱۱ باز می شه. در پنجره مربوطه یکی از دو گزینه رو به دلخواه انتخاب می کنیم.
شکل ۱۱- انتقال Pivot Table
حذف PivotTable
برای حذف کردن PivotTable می تونیم از یکی از روش های زیر استفاده کنیم:
- اگر PivotTable در یک شیت جداگانه قرار گرفته با حذف کردن شیت مربوطه، PivotTable هم حذف خواهد شد.
- اگر PivotTable به همراه داده های دیگری در یک شیت قرار گرفته، برای حذف جدول کافیه با موس محدوده PivotTable رو انتخاب کنیم و دکمه Delete رو بزنیم.
- روی یکی از فیلدهای PivotTable کلیک می کنیم و از تب Analyze و زیر مجموعه Actions روی گزینه Select کلیک می کنیم و از لیست نمایش داده شده مطابق شکل ۱۲ گزینه Entire PivotTable رو انتخاب می کنیم و بعد دکمه Delete رو می زنیم.
شکل ۱۲- حذف کردن PivotTable
دوره ویدئویی صفر تا صد پیوت تیبل
کامل ترین دوره آموزشی پیوت تیبل از پایه تا پیشرفته
مثال هایی از Pivot Table
در ادامه چند نمونه از ساختارهای PivotTable رو در قالب مثال توضیح میدیم که می تونه برای کسانی که می خوان ساخت گزارش هاشون رو با PivotTable شروع کنن مفید باشه.
مثال ۱- Pivot Table دو بعدی
همان طور که در شکل ۱۳ نشان داده شده، در سطرها (Rows) “فروشنده” و “نام محصول” قرار گرفته، در ستون ها (Columns) “ماه” قرار گرفته و در قسمت فیلتر هم فیلدی انتخاب نشده.
شکل ۱۳- نمونه ای از PivotTable دو بعدی
مثال ۲- نمونه ای از Pivot Table سه بعدی
همان طور که در شکل ۱۴ نشان داده شده، در سطرها (Rows) “فروشنده” قرار گرفته، در ستون ها (Columns) “نام محصول” قرار گرفته و در قسمت فیلتر “ماه” انتخاب شده. تفاوت Pivot Table سه بعدی با دوبعدی در قسمت Filters هست . اگر مایل به فیلتر کردن گزارش باشیم، فیلدی رو در قسمت فیلتر قرار میدیم و با استفاده از اون گزارش رو فیلتر میکنیم و با این کار پیوت تیبل سه بعدی ایجاد کردیم. اگر مایل به فیلتر نباشیم، فیلدی در این قسمت قرار نمیگیره و گزارش ما دو بعدی خواهد بود.
شکل ۱۴- نمونه ای از Pivot Table سه بعدی
مثال ۳- نمایش داده های یک فیلد به دو صورت در Pivot Table
در این مثال می خواهیم داده های مربوط به فروش رو به دو صورت “مجموع فروش” و “درصد از فروش” نمایش بدیم برای اینکار مطابق ویدئو زیر فیلدها رو به بخش های Rows و Values اختصاص میدیم. دقت کنید برای اینکه فیلد فروش رو به دو شکل نمایش بدیم لازم هست که دوبار اون رو به قسمت Values منتقل کنیم و بعد تنظیمات مربوط به نحوه نمایش داده رو انجام بدیم. جدول نهایی در شکل ۱۵ نمایش داده شده:
شکل ۱۵- نمایش فروش به دو شکل “مجموع فروش” و “درصد از فروش”
در این مقاله سعی کردیم مطالبی تکمیلی در خصوص تنظیمات ظاهر پیوت تیبل و ساختار آن ارائه کنیم تا با یادگیری آن ها بتونید جداول تحلیلی کارآمدتری رو بسازید. همچنین مثال های از اشکال مختلف Pivot Table ارائه کردیم تا به فهم بهتر مطالب کمک کنه. بهتون پیشنهاد میکنم مقاله تابع GetPivotData و نحوه کار با آن رو مطالعه کنید.
سلام و وقت بخیر
من دوتا ستون در پیوت تیبل دارم که میخوام تفاضلشون رو در ستون جدیدی داشته باشم چکار باید کرد؟
درود بر شما
هم میتونید برید توی calculated field فرمول بنویسید
فرمول تفاضل.. مثلا دو فیلد مبلغ پرداختی و درخواستی رو میخواید از هم کم کنید. فرمولش میشه این:
=’مبلغ درخواستی’ -‘مبلغ پرداختی’
یا از show value as گزینه difference from استفاده کنید
سلام آقای چراغی
من در پاور کوئری در قسمت سمت راست ان در پنجره Applied Steps روی آخرین مرحله خودم که به اسم ماه بوده کلیک راست می کنم ولی گزینه Insert Step After وجود نداره
که در قسمت Formula Bar دستور شما رو بنویسم. در تب بعد از Add Column آن یک گزینه به اسم Formula هست که تیک رو میزنم کدها رو اونجا نمایش میده من کد ها رو همه نوشتم ولی وقتی به جای عبارت Changed Type نام مرحله قبلی که گذاشته بودم ” mah” رو می نویسم ارور میده و میگه این رو قبلا نوشتی
من دقیقا به جای عبارت Changed Type چی رو بنویسم؟
به جای کلمه Name نام ستون خودم که شماره ماه داره رو جایگزین می کنم اون ارور نمیده
ممنون میشم اگه راهنمایی کنید
یا یک نمونه فایل برام ایمیل کنید.
na.dehnavi@gmail.com
درود
ببینید در Power Query باید در هر Step مشخص کنید که میخواهید روی خروجی کدام Step کار کنید. اینجا هم باید در ابتدای فرمول به جای “Changed Type” نام یک Step قبل تر رو بذارید.
بقیه موارد رو درست انجام دادید.
سلام خانم خاکزاد
یه لطف می کنید مقاله مربوط به گروه بندی Time که گفتین رو روی سایت قراربدین ممنون میشم.
خدمت شما
https://excelpedia.net/4-ways-to-group-time-in-excel/
سلام آقای چراغی، پیرو پاسخ قبلی شما ، به سوالی که پرسیدم
من در پاورکوئری به تب Add Column رفتم گزینه Conditional Column وجود نداره. این گزینه Add CustomColumn هست
در پنجره باز شده فیلد Column Name وجود نداره
این گزینه هست Custom Column FORMULA که باید کد رو بنویسم یه = هست من هر کدی نوشتم ولی چیزی نشون نمیده
میشه بیشتر راهنمایی کنید که شرط رو چطوری بنویسم ممنون میشم.
سلام
ممکن هست به دلیل تفاوت ورژن این مسئله پیش آمده. میتونید از روش زیر این کار رو انجام بدید:
به جای عبارت Changed Type نام مرحله قبلی گذاشته شود.
به جای کلمه Name نام ستونی که شماره ماه داره رو جایگزین کنید.
سلام ببخشید خانم پاکزاد، در مورد سوال Time که پرسیدم
من اون ستون رو در جدول پایوت تیبل بسازم؟
بعد اون ستون که میسازم به ستون Time من وصل میشه؟
من هر دفعه اطلاعات داخل جدول پایوت تیبل من عوض میشه و ثابت نیست آیا اطلاعات جدید ستون Time روی ستون جدید به صورت اتومات میشینه؟
من مبتدی هستم اگه بیشتر راهنمایی کنید و یا یک نمونه فایل آموزشی آپلود کنید ممنون میشم
مقالشو تا فردا انشاله میذارم توی سایت
سلام من در فایل اکسل یک فیلد به اسم Time دارم که با توجه به فیلدهای دیگر جدول این فیلد Time ساعت هایی رو تایپ کردم
(مثلا ۰۹۲۰، ۰۸۴۰، ۱۴۳۰، ۱۹۳۳، ۲۳۲۰ و…….. )
من وقتی از جدول فوق پایوت تیبل میسازم و نمودار را میسازم
کل ساعت ها رو روی نمودار نشون میده
من میخوام روی نمودار برای فیلد Time محدوده یا رنج تعیین کنم
به این شکل که از ساعت( ۱۲-۰۶) ، (۱۸-۱۲) ، (۲۴-۱۸)، (۰۶-۲۴)
ساعت هایی که از ساعت ۶ صبح تا ۱۲ ظهر وارد شدن رو تو رنج (۱۲-۰۶) قرار بگیره و ساعت هایی که از ساعت ۱۲ ظهر تا ۱۸ بعد ازظهر وارد شدن رو تو رنج (۱۸-۱۲) وبه همین شکل الی اخر
ممنون میشم اگه راهنمایی کنید.
درود
ببینید برای دسته بندی کاستومایز شده، خود سیستم grouping جوابگو نیست
یک راه ساده اینه که یک ستون به دیتا اضافه کنید و مثلا با فرمول زیر، دسته بندی ۴ ساعتی انجام بدید:
=FLOOR(A1,4/24)
بعد اون فیلد رو به عنوان سرگروه قرار بدید
سلام پیرو پاسخ قبلی، به سوالی که پرسیدم
من در پاورکوئری به تب Add Column رفتم گزینه Conditional Column وجود نداره. این گزینه Add CustomColumn هست
در پنجره باز شده فیلد Column Name وجود نداره
این گزینه هست Custom Column FORMULA که باید کد رو بنویسم یه = هست من هر کدی نوشتم ولی چیزی نشون نمیده
میشه بیشتر راهنمایی کنید که شرط رو چطوری بنویسم ممنون میشم.
درود بر شما
سوالتون و کجا مطرح کردید؟
اینجا که چیزی نیس
سلام ببخشید من یک سوال پرسیدم خیلی برام واجبه جوابش اگه پاسخ بدین خیلی خیلی ممنون میشم
سلام ببخشید ممنون بابت پاسخ به سوال قبلی
پیرو سوال قبلی من داخل اکسل یک ستون دارم به اسم زمان که ساعت هایی رو به صورت چهار رقمی وارد شده (مثلا ۰۸۵۰ و۰۹۳۰،…… ) . وقتی من پایوت تیبل از اطلاعاتم میسازم نمودار رسم می کنم کل زمان ها را یکجا میاره من میخوام به این زمان ها رنج اختصاص بدم مثلا ۶ صبح تا ۱۲ ظهر بعد ۱۲ تا ۶ و ۶ تا ۲۴ بعد ساعت هایی که داخل جدول هست رو توی این رنج ها قرار بگیره
با تشکر فراوان
درود
مقاله ۴ روش برای دسته بندی ساعت در اکسلرو مطالعه کنید.
سلام
ببخشید من یک بانک اطلاعاتی دارم که اطلاعات اون رو با زدن یک دکمه به اکسل انتقال میده که حاوی سال، ماه و روز هست که به صورت عددی وارد شده وقتی انتقال پیدا کرد من داخل اکسل از اون اطلاعات یک پایوت تیبل ساختم ویک داشبورد ساختم. که به صورت نمودار به من نشون میده. من میخوام اطلاعات ماه که به صورت عدد هست رو توی پایوت تیبل به صورت حروف نشون بده ( مثلا فروردین، اردیبهشت و… )
درود
برای این کار یک راه کار به این صورت هست:
در پاورکوئری به تب Add Column برید و گزینه Conditional Column رو انتخاب کنید.
در پنجره باز شده اسم ستون رو تعیین کنید و در فیلد Column Name ستون ماه عددی رو انتخاب کنید و شرط رو برای عدد یک تعیین کنید که کلمه فروردین نمایش داده شود. سپس دکمه Add Clause رو بزنید که ردیف جدید اضافه بشه و همین کار رو تا عدد ۱۲ انجام بدید.
سلام خسته نباشین
من توی گزارش پیوت ۲ تا rows دارم که یکی به عنوان گروه و دیگری بعنوان زیر گروه اون هستش که من اون گروه رو میخوام بیارم یه ستون جدا کنار زیر گروهش دیده بشه و به صورت سطری رو زیر گروهش نمایش داده نشه
درود
از تب design چیدمان یا layout رو بذارید روی حالت tabular
سلام وقت بخیر
امکان گزارشگری از دو ردیف به عنوان title و یک ستون در pivot وجود داره؟
درود
سوال اصلا واضح نیست
سلام و وقت بخیر
در پیوت تیبل من دو ستون تاریخ براساس ماه و تاریخ شمسی دارم اطلاعات وارد شده من بر این فرمت وارد می شود اما هر بار که رفرش می کنم این دو ستون تبدیل به میلادی می شود .ایراد کارم کجاست ؟ سپاسگزارم
سلام، وقت بخیر
از قسمت Vlaue Field format دکمه Number رو بزنید و فرمت درست رو انتخاب کنید.
همچنین در Pivot Table Options گزینه Preserve Cell Formatting on Update رو انتخاب کنید.
با سلام من یک شیت دارم شامل بیشتر از ۲۰۰۰ رکورد وقتی با pivote table گزارش گیری میکنم مثلا در قسمت row ۱۰۰ رکورد ایجاد شده با کلیک بر روی یکی از انها یک شیت جدا باز میشه و اطلاعات اون سلول رو نشون میده که چند رکورد داشته دستوری فرمولی یا کد وجود داره که با زدن اون به صورت خودکار اکسل همه این ۱۰۰ رکورد در pivote table رو به ۱۰۰تا شیت تبدیل کنه
درود
تنها دستوری که اینکار و میکنه report filter page هست
باید گزارشتون مطالق با خروجی که میخواید طراحی کنید که بتونید از این امکان در این راستا استفاده کنید
سلام من پیوت درست میکنم بعد از اینکه فایل رو باز و بسته میکنم فایل به حالت نوشتاری میشه واز خالت پیوت خارج میشه علتش چیه
درود
تنظیمات اپشن رو چک کنید شاید ایتمی تیک داره
با تشکر از سایت و مطالب ارزشمند شما
من با اکسل ۲۰۱۰ کار می کنم،
مسئله: اسامی ۱۰ شهرستان در شیتی با حدود ۱۰۰۰ ردیف تایپ شده و اپراتور هنگام تایپ اسامی ، قبل یا بعد آنها کلیپ space را زده ,
در پنجره فیلتر ، اسامی یونیک دیده میشن و کاراکتر space نادیده گرفته میشه که منطقی هم هست.
مشکل: وقتی ستون شهرستان را در pivote table به کادر row labels می آوریم اسامی مشابه (دارای کاراکتر space) در سطر های مجزا دسته بندی میشن که سبب گزارشی نامطلوب میشه. البته میشه با trim ستون شهرستان قضیه را حل کرداما آیا این عملکرد اکسل توجیهی داره؟
درود بر شما
بله توجیه داره
از نظر ما (انسان) اون عبارات با هم برابر هستن
اما از نظر اکسل، دو عبارت مجزا هستن. چون تعداد و جنس کاراکتر متفاوت دارند
پس داده باید برای گزارش گیری validate باشه. در واقع از زمان ورود داده باید کنترل بشه این قضیه