شروع جشنواره نوروزی و تخفیف های ویژه 1402 (جهت مشاهده تخفیف ها اینجا کلیک کنید)
سبد خرید
0

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

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

ابزار Unpivot ابزاری کاربردی در Power Query

ابزار Unpivot در اکسل
نظر شما در مورد این آموزش

Unpivot کردن داده ها با ابزار Unpivot

همونطور که میدونیم یکی از بهترین ابزارها برای گزارشگیری ابزار پیوت تیبل هست. این ابزار بدون نیاز به فرمول نویسی، میتونوه گزارش های خیلی متنوع و کاربردی رو در اختیارمون قرار بده. اما همونطور که در مورد این ابزار میدونیم، دیتابیس مورد نیاز این ابزار یک دیتابیس جدولی (Tabular) هست. در واقع اگر دیتابیس ماتریسی داشته باشیم، نمیتونیم از ابزار پیوت تیبل برای گزارشگیری استفاده کنیم. پس باید راهی پیدا کنیم که دیتابیس ماتریسی رو به دیتابیس جدولی (Tabular) تبدیل کنیم (اصطلاحا Unpivot کنیم) تا بتونیم از پیوت تیبل برای گزارشگیری از این داده ها استفاده کنیم. تفاوت این دو نوع دیتابیس در شکل ۱ نمایش داده شده است.

تفاوت ساختار دیتابیس ماتریسی و جدولی

شکل ۱ – تفاوت ساختار دیتابیس ماتریسی و جدولی

همونطور که در شکل ۱ نمایش داده شده دیتابیس ماتریسی یک ماتریس هست که سطرهای آن نام محصول و ستونهای آن ماه های سال هست و در هر تقاطع سطر و ستون، میزان فروش محصول مربوطه در ماه مشخص شده ثبت شده است. اما در دیتابیس جدولی، سه ستون داریم، یکی نام محصول، ماه و میزان فروش. پس باید نام هر محصول دوازده بار (به تعداد ماه های سال) تکرار بشه و برای هر ردیف ،فروش محصول در همون ماه ثبت بشه.

برای تبدیل دیتابیس ماتریسی به دیتابیس مسطح چکار باید انجام بدیم؟

پاورکوئری امکانی رو در اختیار ما قرار داده که میتونه این کار رو انجام بده. اسم این ابزار Unpivot هست. که در واقع داده ها رو از حالت ماتریسی یا (پیوت شده) به حالت جدولی (Tabular) تبدیل میکنه. حالا ببینیم که این ابزار به چه صورت کار میکنه.

نکته:
برای انجام این کار میتونیم از کدنویسی VBA هم استفاده کنیم و با استفاده از حلقه های For دیتابیس ماتریسی رو به جدولی (Tabular) تبدیل کنیم.

 

برای استفاده از این ابزار مراحل زیر رو طی میکنیم:

گام اول: تبدیل داده ها به Table

  1. روی یکی از سلول های جدول ماتریسی کلیک میکنیم
  2. از تب Insert روی گزینه Table کلیک میکنیم
  3. از پنجره باز شده، محدوده مشخص شده رو در صورت نیاز اصلاح میکنیم.
  4. با زدن Ok محدوده مورد نظر به Table تبدیل میشه

اگر با Table آشنا نیستید مقاله مربوط به Table رو مطالعه کنید.

گام دوم: وارد کردن داده ها در پاور کوئری

  1. روی یک سلول از تیبل کلیک میکنیم و از تب Data و قسمت Get & Transform Data روی گزینه From Table/Range کلیک میکنیم.
  2. در پنجره Power Query Editor (شکل ۲) ستون نام محصول که قراره بقیه داده ها بر اساس اون جابجا بشن رو انتخاب میکنیم.

ورود به پنجره Query Editor جهت استفاده از ابزار Unpivot

شکل ۲ – ورود به پنجره Query Editor

  1. حالا روی ستون محصول کلیک راست کرده و روی گزینه Unpivot other columns کلیک میکنیم.

انتخاب ستون مورد نظر برای Unpivot

شکل ۳ – انتخاب ستون مورد نظر برای Unpivot

  1. با زدن این گزینه داده ها بصورت جدولی چیده میشوند.

تبدیل داده های ماتریسی به جدول

شکل ۴ – تبدیل داده های ماتریسی به جدول

  1. حالا کافیه نام سرستون ها رو به عباراتی با معنی تغییر بدیم. برای این کار کافیه روی سرستون دبل کلیک کنیم و نام دلخواه رو تایپ کنیم.

تغییر نام سرستون داده ها

شکل ۵ – تغییر نام سرستون داده ها

  1. حالا باید داده های تغییر ساختار یافته رو به شیت اکسل منتقل کنیم. برای این کار کافیه از تب Home روی گزینه Load to Close & کلیک میکنیم.

انتقال داده ها به شیت اکسل

شکل ۶ – انتقال داده ها به شیت اکسل

  1. از پنجره نمایش داده شده، گزینه مورد نظر (در اینجا، Table) رو انتخاب میکنیم تا داده ها در قالب یک Table در یک شیت جدید نمایش داده بشه.

انتخاب حالت مورد نظر برای نگهداری داده

شکل ۷ – انتخاب حالت مورد نظر برای نگهداری داده

حالا دیگه داده ها رو در قالب یک دیتابیس جدولی و در یک شیت مجزا داریم و آماده برای گزارشگیری با پیوت تیبل.

بروزرسانی کوئری ایجاد شده

سوال خیلی مهمی که پیش میاد این هست که اگر داده ها تغییر کرد، یا اضافه/کم شد، همه این مراحل رو باید تکرار کرد؟

پاسخ این سوال خیر هست!

یکی از ویژگی های خیلی مهم و کاربردی پاورکوئری اینه که قابلیت آپدیت و بروزرسانی داره. در ادامه نحوه بروزرسانی کوئری مورد نظر رو شرح میدهیم:

  1. داده مورد نظر رو در ادامه ماتریس اولیه (سطر یا ستون) وارد میکنیم.
  2. چون محدوده داده های ماتریسی خاصیت Table داره، در صورتی که داده در (ستون یا ردیف) به داده ها اضافه بشه، این ویژگی بسط داده میشه و داده های جدید هم در قالب Table اولیه در نظر گرفته خواهند شد. اگر این اتفاق بصورت خودکار نیافتاد، بعد از اضافه کردن داده ها، از تب Table Tools و گزینه Resize Table محدوده مورد نظر رو اصلاح کنید.
  3. از تب Data روی گزینه Queries & Connections کلیک میکنیم.
  4. از پنجره باز شده در سمت راست شیت اکسل، روی کوئری مورد نظر کلیک راست کرده و گزینه Refresh رو میزنیم. یا اینکه روی علامت  کلیک میکنیم.

آپدیت کردن کوئری ایجاد شده

شکل ۸- آپدیت کردن کوئری ایجاد شده

با طی کردن این مراحل، داده های جدید به دیتابیس جدولی (Tabular) در شیت مورد نظر اضافه میشوند و کافیه که گزارش ایجاد شده توسط پیوت تیبل رو Refresh کنیم. همه گزار شها بروز رسانی میشه و براحتی تغییرات جدید روی داده ها و در نتیجه گزارش اعمال میشوند.

نکته:
اگر فقط مقدار داده ها تغییر پیدا کنه، کافیه که فقط گام ۳ و ۴ انجام بشه تا جدول داده های Tabular آپدیت شوند.

 

ابزار پاورکوئری ابزار بسیار قوی برای کار کردن با داده ها و آماده سازی آنهاست. حتما مقالات مرتبط با این ابزار فوق العاده کاربردی رو مطالعه کنید.

دانلود فایل آموزش ابزار Unpivot در اکسل

فایل زیر رو میتونید دانلود کنید و مراحل موجود در آموزش رو روی داده ها اجرا کنید.

133

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

دیدگاه کاربران
  • یوسف ۳۰ بهمن ۱۴۰۰ / ۴:۳۹ ب٫ظ

    مطلبه عالی بود
    دمتون گرم واقعا کارمو راه انداخت

ارسال دیدگاه

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

توسط
تومان