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

021-47625755
info@excelpedia.net

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

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

      0

Power Query

  • خانه
  • بلاگ
  • Power Query
  • ابزار Unpivot ابزاری کاربردی در Power Query

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

  • ارسال شده توسط سامان چراغی
  • دسته بندی Power Query
  • تاریخ ۷ مرداد ۱۳۹۹
  • نظرات ۰ نظر
ابزار Unpivot در اکسل
نظر شما در مورد این آموزش

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

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

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

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

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

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

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

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

 

حتما بخوانید:  ترکیب جداول در اکسل با استفاده از Power Query

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

حتما بخوانید:  ترکیب جداول در اکسل با استفاده از Power Query

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

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

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

با عضویت در سایت به صورت مستقیم دانلود کنید

  • ارسال به ایمیل

Des

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

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

مطلب قبلی

اشتراک گذاری فرم پرسشنامه حرفه ای به همراه گزارش
۷ مرداد ۱۳۹۹

مطلب بعدی

توابع ویرایش متن انگلیسی در اکسل
۷ مرداد ۱۳۹۹

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

Scrap Data From Web Cover
انتقال داده از وبسایت به اکسل
۱۸ فروردین, ۱۳۹۹
Power Query file lists Cover-min
لیست فایل های یک پوشه در اکسل
۲۵ تیر, ۱۳۹۸
Merge Tables- Power Query-min
ترکیب جداول در اکسل با استفاده از Power Query
۱ تیر, ۱۳۹۸

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

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

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

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

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

آخرین مطالب

حل مسئله رتبه بندی داده های تکراری
۱۱آذر۱۳۹۹
ابزار Mail Merge
ترکیب Mail Merge ورد و اکسل
۲۳آبان۱۳۹۹
سورت تاریخ
مرتب سازی انواع تاریخ در اکسل
۰۲شهریور۱۳۹۹
توابع متنی: تابع Proper، تابع Lower، تابع Upper در اکسل
توابع ویرایش متن انگلیسی در اکسل
۱۷مرداد۱۳۹۹

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

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

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

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

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

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

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

021-47625755

info@excelpedia.net

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

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

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

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