Pivot Table (پیوت تیبل) معجزه اکسل برای گزارش گیری
Pivot Table چیست؟
Pivot Table (پیوت تیبل) که با نام جدول پاشنه ای یا جدول محوری هم شناخته می شود به عنوان یک ابزار قدرتمند و حرفه ای، ابزاری برای آنالیز و گزارش گیری داده ها می باشد که محبوبیت خاصی برای افراد حرفه ای دارد.گزارشگیری از داده ها به بهترین و سریعترین شکل ممکن همیشه از جمله عملیات مهم و کلیدی برای آنالیز، پیشبینی و تصمیم گیری ها می باشد که گاهی پروسه زیادی را باید برای رسیدن به نتیجه موردنظر طی کرد.به جرات می توان گفت Pivot Table درحال حاضر قدرتمند ترین و انعطاف پذیرترین ابزار گزارشگیری و تهیه داشبورد می باشد که اکسل در دل خود جای داده است.متاسفانه خیلی از کاربران اکسل از وجود و قدرت این ابزار قدرتمند آگاهی ندارند.
اقدامات قبل از استفاده از پیوت تیبل :
با توجه به اینکه پیوت تیبل یک ابزار گزارشگیری می باشد، جهت استفاده از آن باید اطلاعات ما از یکسری قوانین که به آن “قوانین ذخیره سازی اطلاعات” یا “اصول ایجاد دیتابیس” گفته می شود تبعیت کند.درنتیجه ما باید برای استفاده از این ابزار پیشنیازهای آن را رعایت کنیم.
۱_ رعایت اصول ذخیره سازی اطلاعات در دیتابیس:
این موضوع که خود یک بحث مفصلی می باشد، یکی از حیاتی ترین اقدامات در ذخیره سازی اطلاعات می باشد.زیرا کیفیت عملیات گزارشگیری بشدت به نحوه ذخیره سازی اطلاعات بستگی دارد.
۲_ تبدیل محدوده داده ها به Table
Table خود یکی از ابزارهای مهم و کاربردی اکسل می باشد که آموزش مفصلی بصورت ویدئو تهیه شده که می توانید در این لینک مشاهده کنید.برای مشاهده ویدئو می بایست ابتدا در سایت عضو شده سپس بصورت کاملا رایگان در دوره آموزش مقدماتی اکسل ثبت نام کنید و به تمام ویدئوها دسترسی داشته باشید.
نکته:
برای ایجاد Pivot Table استفاده از ابزار Table ضروری نیست.اما موضوع مهمی که وجود دارد این است که بعد از ایجاد پیوت تیبل اگر به انتهای داده های خود، داده جدیدی اضافه شود دیگر توسط پیوت تیبل نشان داده نخواهد شد.اما ابزار Table این مشکل را برای ما حل می کند.
مراحل استفاده :
یک دیتابیسی همانند شکل زیر در نظر بگیرید.می خواهیم انواع گزارش ها را از این داده های خود ایجاد کنیم.
ابتدا بعد از تبدیل محدوده خود به Table، محدوده تیبل را انتخاب کرده و مسیر زیر را بروید:
Insert tab > Tables gruop > Pivot Table
با این کار پنجره Create Pivot Table باز می شود (شکل شماره ۲).
- Select a table or range : محدوده ای که میخواهید بصورت پیوت تیبل از آن گزارش بگیرید را انتخاب کنید.که اینجا محدوده من بصورت تیبل و با نام Table13 می باشد.
- Use an external data source : زمانی که بخواهید از داده های خارجی برای گزارشگیری استفاده کنید از این گزینه استفاده می کنید.
- New Worksheet : اگر میخواهید گزارشگیری در شیت دیگری ایجاد شود این گزینه را انتخاب کنید.با این کار یک شیت جدیدی ایجاد می شود و جدول گزارش گیری داخل آن ساخته می شود.
- Existing Worksheet : اگر میخواهید گزارشگیری در شیت جاری ایجاد شود این گزینه را انتخاب کنید.در قسمت Location محدوده یا سلولی را که میخواهید جدول گزارش گیری از آنجا ساخته می شود را انتخاب کنید.
نکته :
همیشه سعی کنید گزارش های خود را خارج از شیت های دیتابیس ایجاد کنید تا ناخواسته تغییراتی در اطلاعات خود ایجاد نکنید.پس توصیه می کنم تا حدامکان از گزینه ۳ یعنی New Worksheet استفاده کنید.
معرفی قسمت های مختلف این ابزار
بعد از OK کردن پنجره محیطی مثل شکل زیر ایجاد می شود:
درستون سمت راست نام ستون های جدول شما نمایش داده می شود که در زیر آن نیز ۴ عنوان مختلف دیده می شود
- Columns : از لیست نام داده هایی که هایی که می خواهید بصورت ستونی در گزارش گیری نشان داده شود در این قسمت قرار دهید
- Rows : از لیست نام داده هایی که می خواهید بصورت سطری در گزارش گیری نشان داده شود در این قسمت قرار دهید
- Values : از لیست نام داده های عددی که میخواهید بصورت جمع کل در گزارش گیری نمایش داده شود را اینجا قرار دهید.
- Filter : از لیست نام داده های عددی که میخواهید براساس آن فیلتر انجام دهید را اینجا قرار دهید.
حالا چگونه باید از این Pivot Table گزارش بگیریم؟
فرض کنید می خواهیم مجموع پرداختی های مشتریان را بدست بیاوریم.برای این کار خیلی راحت تیک “نام” و “مبلغ” را از لیست می زنیم.با این کار این دو گزینه بصورت پیشفرض در یکی از چهار گزینه پایینی قرار می گیرد و در کادر سمت چپ گزارش ما ساخته می شود.یا بجای تیک زدن می توانید عنوان داده موردنظر را با کلیک چپ بگیرید و در خانه موردنظر رها کنید.
حال می خواهم نوع پرداختی های مشتری بصورت ستونی در گزارش من آورده شود.برای اینکار نام عنوان ستون داده که “نوع” می باشد را از لیست با کلیک چپ موس میگیرم و در کادر Columns رها می کنم.
در ادامه می خواهم تاریخ پرداختی های مشتری بصورت سطری و زیرمجموعه ای از نام مشتری در گزارش من آورده شود.برای اینکار نام عنوان ستون داده که “تاریخ سررسید” می باشد را از لیست با کلیک چپ موس میگیرم و در کادر Rows رها می کنم.
حالا می خواهم یک گزینه برای فیلتر کردن براساس وضعیت پرداخت های مشتری ایجاد کنم تا بتوانم فیلتر های خودم را در پیوت تیبل اعمال کنم.برای این کار نام “وضعیت” را از لیست به کادر Filter میکشیم و رها می کنیم.
گزارش نهایی که توسط پیوت تیبل ساخته می شود به شکل زیر خواهد بود:
نکته مهم:
پیوت تیبل در گزارش گیری با داده های تکراری کاری ندارد و بصورت مجموع حاصل را نشان می دهد.یعنی اگر یک اسمی چند بار در لیست شما آمده باشد ، آن اسم را فقط یکبار در گزارش می آورد و کل مبالغ پرداختی آن شخص را با هم جمع می کند.
این گزارش به ما چه می گوید…؟
برای مثال به نام “علی جعفری” در گزارش دقت کنید.در مقابل نام آن، جمع کل انواع پرداخت هایش را نشان می دهد و در زیر آن نیز تاریخ هایی که این پرداخت ها انجام شده است بصورت زیرمجموعه ی آن نشان داده می شود.آقای “علی جعفری” در چهار تاریخ مختلف پرداختی داشته که مقابل هر تاریخ جمع کل پرداختی در آن تاریخ را برای ما نشان می دهد.
در سمت راست به کادر Rows دقت کنید… .ما ابتدا گزینه “نام” را آورده ایم سپس گزینه “تاریخ سررسید“.معنی این کار این است که در ردیف ها ابتدا نام مشتری ها را بیار سپس بعد از نام، تاریخ ها را بصورت زیر مجموعه نام بیاور.اگر خواستید ابتدا تاریخ قرار بگیرد و نام زیرمجموعه ای از آن باشد کافیست با موس بصورت درگ کردن جای آنها را باهم تغییر بدهید.قسمت جالب و جذاب پیوت تیبل اینجاست که خیلی راحت و سریع تنها با چند کلیک می توانید گزارش خود را تغییر داده و آنجور که دلتان می خواهد از نو بسازید.
نکته:
در پشت نام مشتریان یک علامت منها وجود دارد.اگر نمی خواهید زیرمجموعه های هر مشتری را ببینید کافیست روی علامت منها کلیک چپ بزنید تا زیرمجموعه بسته شود و فقط نام مشتری نشان داده شود.
نحوه بروزرسانی Pivot Table :
نکته مهمی که درمورد پیوت تیبل باید بدان توجه کرد، نحوه بروزرسانی گزارشات ساخته شده توسط پیوت تیبل می باشد.باید گفت که پیوت تیبل اتوماتیک گزارش خود را بروزرسانی نمی کند و برای اینکه بتوانید آخرین تغییرات انجام شده داده ها را در گزارش خود مشاهده کنید، باید بصورت دستی پیوت تیبل را بروزرسانی کنید.برای این کار کافیست روی گزارش پیوت تیبل کلیک راست نموده و گزینه Refresh را بزنید تا گزارش بروزرسانی شود.
یا می توانید از تب Data و گروه Connections لیست مربوط به Refresh All را باز کرده و گزینه Refresh را بزنید.اگر هم شما چندین پیوت تیبل ایجاد کرده باشید و می خواهید سریع همه این گزارش ها بروزرسانی شود، گزینه Refresh All را بزنید.
چرا گزارش اتوماتیک آپدیت نمی شود…؟
سوالی که اکثر افراد می پرسند این است که چرا پیوت تیبل هنگام تغییر در داده ها خودش اتوماتیک آپدیت نمی شود…!!؟ آیا مایکروسافت قصد اذیت کردن ما را دارد یا این یک ضعف در کدنویسی برای این ابزار قدرتمند توسط تیم مایکروسافت می باشد…!!؟
چیزی که مسلم است این است که مایکروسافت تلاش می کند تا بهترین و بهینه ترین روش ها را برای استفاده از امکانات در مجموعه آفیس اعمال کند.باید گفت که Pivot Table یک ابزار کاملا سورس پذیر با پروسه آنالیز پیشرفته داده ها می باشد.به همین دلیل زمانی که بانک اطلاعاتی شما بسیار حجیم باشد یا چندین پیوت تیبل ایجاد کرده باشید، آنالیز و بروزرسانی داده ها ممکن است چندین ثانیه طول بکشد.حالا تصور کنید آپدیت Pivot Table اتوماتیک بود و شما می خواهید یک مقداری در دیتابیس خود وارد کنید.به نظر شما چه اتفاقی می افتد…؟ آری… شما برای تغییر هر داده در سلول باید چندین ثانیه منتظر بمانید تا اول بروزرسانی پیوت تیبل ها تمام شود تا بتوانید ادامه تغییرات را در دیتابیس خود اعمال کنید.به نظر شما آیا این وضعیت مطلوبی برای شما ایجاد می کند…!!؟مسلما از این وضعیت کلافه خواهید شد.
به همین دلیل است که مایکروسافت بروزرسانی پیوت تیبل را بصورت دستی قرار داده است تا فقط زمانی عمل بروزرسانی انجام شود که شما نیاز به مشاهده آخرین تغییرات انجام شده در گزارشات خود را دارید.
منابع:
درباره جاوید
عاشق اکسل هستم و بیش از 12 ساله دارم اکسل کار می کنم. در این مسیر آموزش ها و فایل های حرفه ای ارائه دادم و همچنان در این مسیر با علاقه پیش میرم چون امروزه اکسل به یک مهارت انکار نشدنی تبدیل شده
سایر نوشته های جاویدمطالب زیر را حتما مطالعه کنید
راست چین در اکسل و نحوه راست به چپ کردن شیت اکسل
برای چاپ اطلاعات داخل فرم از قبل پرینت شده در اکسل چکار کنیم؟
فقط با ۳ سوت فرم لاگین در اکسل طراحی کن
آموزش طراحی UserForm گرافیکی و جذاب در اکسل
تبدیل عدد به حروف در اکسل + دانلود افزونه ABH
محاسبه اضافه کاری و ساعت تاخیر در اکسل+دانلود فایل
5 دیدگاه
به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.
تا pivote گرفته میشه بعد از ذخیره دوباره اکسل باز میکنم pivot از بین رفته و فقط جدول مانده
سلام. وقت بخیر
یک فایل اکسل خام جدید بسازید و روی اون تست کنید.
اگر حل نشد آفیستون رو مجدد نصب کنید.
سلام من یک ردیف داده دارم که اعداد زیر صفر و بالای صفر رو در برمیگیره میخوام بدونم چطور میتونم بفهمم که در اون ردیف چند درصد داده ها بالای صفر و چند درصد پایین صفر هستند؟
با سلام.
فرض کنید اعداد در محدوده A1:A18 قرار دارد.
برای بدست آوردن درصد سهم یک مقدار به روش زیر عمل کنید:
۱-با تابع (COUNTIF(A1:A18,”>=۰″)/Count(A1:A18= درصد اعداد صفر و بیشتر بدست می آید.
۲-با تابع (COUNTIF(A1:A18,”<0")/Count(A1:A18= درصد اعداد کمتر از صفر بدست می آید.
حال برای نمایش نتیجه با نماد درصد کافیه فرمت سلول هایی که فرمول رو داخلش نوشتین به Percentage تغییر بدین.
با خود پیوت تیبل هم میشه این کار رو انجام داد ولی این راه ساده تر بود.
سلام من پیوت تیبل درست کردم از سوابق یک فردی.
مثلا شغل نان پز با مجموع سوابق ۷۷۰ روز دوره بازه زمانی ۱۴۰۰۰۲۰۱ لعایت ۱۴۰۰۰۹۳۰
و عنوان دوم شاطر بازه زمانی ۱۴۰۰۷ لغایت ۱۴۰۰۵ مشکل اینو دارم اگر در بازه زمانی فوق عنوان شغلی بیمه شده تغییر یابد پیوت تیبل در وسط بازه زمانی نمیاره عنوان شاطر بامجموع سوابق ۷۰ در بازه زمانی ۱۴۰۰۵۰۱ لغایت ۱۴۰۰۰۷۳۰
من چطوری میتونم بگم پیوت تیبل مثل جدول زیر برام نتیجه بده.
نان پز از بازه زمانی ۱۴۰۰۰۲۰۱ لعایت ۱۴۰۰۰۴۳۲ با بازه زمانی مثلا۵۰ رور و از بازه زمانی ۱۴۰۰۰۵۰۱ لغایت ۱۴۰۰۰۷۳۰ شاطر بازه زمانی م ۸۰ روز و از بازه زمانی ۱۴۰۰۰۸۰۱ لعایت ۱۴۰۰۰۹۳۰ عنوان شغلی نان پذر
الطفا راهنمایی کنید