محصولات ویدئویی به مدت یک هفته تخفیف خورده ------------------------> مشاهده دوره ها
سبد خرید
0

هیچ محصولی در سبد خرید نیست.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

تکنیک خیلی کاربردی از ابزار گزارشگیری Pivot Table

گزارش گیری در اکسل با پیوت تیبل
۱/۵ - (۱ امتیاز)

پیوت تیبل و گزارش های جذاب

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

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

در این مقاله میخواهیم یک گزارش خاص با استفاده از یک امکان خیلی کاربردی از ابزار پیوت تیبل بسازیم. فرض کنید داده هایی رو مشابه شکل ۱ داریم. قیمت چند قلم کالا در سال های متفاوت ثبت شده است.

ایجاد گزارش با پیوت تیبل

دیتابیس ذخیره شده از قیمت کالا در سالهای مختلف برای استفاده در گزارش

شکل ۱- دیتابیس ذخیره شده از قیمت کالا در سالهای مختلف برای استفاده در گزارش

حالا میخواهیم گزارشی رو بسازیم که قیمت ها رو نسبت به سال ۱۳۹۷ مقایسه کنه. مثلا اینکه در سال ۱۳۹۵، قیمت کالای ۱ نسبت به سال ۱۳۹۷ چقدر تغییرات (کاهش، افزایش) داشته.

برای این کار ابتدا پیوت تیبل رو ایجاد میکنیم، پس کافیه از تب Insert روی گزینه Pivot Table کلیک کنیم و بعد از انتخاب محدوده داده ها و زدن OK گزارش رو ایجاد کنیم.

حالا گزارش رو مطابق شکل ۲ میسازیم، فیلد سال در Columns، فیلد کالا در قسمت Rows و فیلد قیمت هم در قسمت Values قرار میگیره و گزارش ساخته میشه.

ساخت گزارش با استفاده از پیوت تیبل

شکل ۲- ساخت گزارش با استفاده از پیوت تیبل

تغییر نحوه نمایش مقادیر

حالا باید نوع محاسبات رو تغییر بدیم. در پیوت تیبل امکانی داریم که میتونیم محاسبات تحلیلی و مقایسه ای رو بدون نیاز به فرمول نویسی ایجاد کنیم. برای این کار کافیه روی قسمت value کلیک کرده و Value Field Settings رو انتخاب کنیم.

از پنجره باز شده روی تب Show Value as کلیک میکنیم.

از پنجره باز شده، نوع محاسبات رو تعیین میکنیم. در اینجا میخواهیم تغییرات قیمت رو نسبت به سال ۱۳۹۷ بصورت درصد حساب کنیم. پس از لیست بازشو، روی گزینه %Difference From کلیک میکنیم و مطابق شکل ۳ تنظیمات رو انجام میدیم:

همونطور که در شکل ۳ نمایش داده شده، معیار محاسبه اختلاف رو فیلد سال و آیتم مورد نظر رو سال ۱۳۹۷ تعیین میکنیم.

تنظیمات محاسبات اختلاف به درصد

شکل ۳- تنظیمات محاسبات اختلاف به درصد

حالا با زدن OK محاسبات به صورت اختلاف به درصد نمایش داده میشه.

در واقع برای هر کالا، اختلاف قیمت در هر سال نسبت به سال ۱۳۹۷ بصورت درصد محاسبه میشه.

نمایش اختلاف بصورت درصد

شکل ۴- نمایش اختلاف بصورت درصد

مثلا قیمت کالای ۱ در سال ۱۳۹۵، ۴۱ درصد بیشتر از قیمت همین کالا در سال ۱۳۹۷ بوده و قیمت کالای ۲ در سال ۱۳۹۵، به میزان ۶۵ درصد کمتر از سال ۱۳۹۷ بوده.

تغییر رنگ پیوت تیبل بر اساس مقادیر سلول ها

حالا میتونیم مقادیر مثبت و منفی رو با استفاده از Conditional Formatting تغییر رنگ بدیم و یک طیف رنگ رو نمایش بدیم.

برای این کار محدوده گزارش رو انتخاب میکنیم و از تب Home/ Conditional Formatting روی Color Scale کلیک کرده و طیف مورد نظر رو انتخاب میکنیم. همونطور که در شکل ۵ نمایش داده میشه هر چقدر رنگ سول پررنگ تر باشه، میزان اختلاف بیشتری رو نشون میده، اختلاف مثبت (افزایش) به سمت رنگ سبز و اختلاف منفی (کاهش) به سمت رنگ قرمز نمایش داده میشه.

اعمال طیف رنگ روی نتیجه محاسبات گزارش

شکل ۵- اعمال طیف رنگ روی نتیجه محاسبات گزارش

دانلود فایل اکسل ایجاد گزارش با پیوت

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

توجه: جهت دانلود فایل حتما در حساب کاربری خود وارد شوید.

مشاهده ویدئو تکنیک خاص در ایجاد گزارش با پیوت

مطالب این آموزش رو میتونید در ویدئو زیر نیز مشاهده کنید.

در حال بارگذاری...
آواتار
131

فارغ التحصیل لیسانس مهندسی صنایع، ارشد مدیریت صنعتی از دانشگاه تربیت مدرس و عاشق اکسل هستم. از سال 1388 که ترم 2 لیسانس بودم، به توصیه استاد مشاورم شروع به خوندن اکسل بصورت حرفه ای کردم و همچنان در حال مطالعه و یادگیری و البته آموزش به بقیه هستم.

ارسال دیدگاه

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

توسط
تومان