آشنائی با Power Query و Power Pivot در اکسل

Power Pivot چیست ؟

همانگونه که می دانید Pivot Table ، به کاربر اجازه استفاده فقط از یک منبع اطلاعاتی را می دهد که برای یک مجوعه داده های بسیار ساده مناسب می باشد ولی این موضوع می تواند تبدیل به یک محدودیت برای Pivot Table شود چرا که در خیلی از مواقع ، اطلاعات در جداول و منابع متعدد تقسیم می شوند که در این حالت کاربر باید با استفاده از توابع VLOOKUP و یا مشابه آن، داده ها را به یک جدول واحد تبدیل کرده تا بتواند Pivot Table مورد نظر خود را ایجاد کند که انجام این کار می تواند باعث بروز مشکلاتی شده و یا شما را به نتیجه مطلوب نرساند.

Power Pivot یک افزونه (Add in) در اکسل است که شما را قادر می سازد میلیون ها داده از چندین بانک یا منبع اطلاعاتی را در یک فایل اکسل Import کرده و سپس از طریق ایجاد ارتباط (Relationships) بین اطلاعات و جداول، تجزیه و تحلیل داده ها را با سرعت بیشتری انجام دهید.

با Power Pivot دیگر نیازی نیست فرمول ها را در Sheet های مختلف بازنویسی کنید بلکه می توانید فرمول را یک بار در مدل داده (Data Model) نوشته و سپس با استفاده از چندین Pivot Table به هر میزان که می خواهید آنها را فراخوانی کنید.

از دیگر ویژگی های Power Pivot که می توان برای تجزیه و تحلیل داده ها از آن استفاده کرد عبارت است از:

  • ایجاد ارتباط بین داده های مختلف ار منابع اطلاعاتی متفاوت
  • ایجاد ستون های محاسباتی
  • ذخیره سازی نتیج تحلیل در قالب فایل اکسل
  • ایجاد perspectives
  • ایجاد hierarchies  (سلسه مراتب)
  • ایجاد شاخص های اندازه گیری (KPI)

*فعال سازی Power Pivot در اکسل:

جهت فعال کردن Power Pivot در اکسل ۲۰۱۳ و ۲۰۱۶ ، همانند فیلم آموزشی زیر، تنظمات را انجام دهید:

نکته:

در صورتی که اکسل شما نسخه ۲۰۱۰ است می توانید از طریق لینک زیر، Power Pivot را توجه به ۳۲ یا ۶۴ بیتی بودن اکسل خود آن را دانلود ، نصب و فعال کنید:

لینک دانلود Power Pivot برای اکسل ۲۰۱۰

مثال: ارتباط بین دو Pivot Table با استفاده از Power Pivot در اکسل

در مثال زیر دو جدول در دو Sheet مجزا وجود دارد:

جدول شماره یک : مشخصات پروژه های شرکت :

جدول شماره دو :اطلاعات مربوط به صورت وضعیت پروژه ها:

می خواهیم با استفاده از Power Pivot ، این دو جدول را به یکدیگر طوری ارتباط بدهیم که مبلغ تجمعی صورت وضعیت هر یک از پروژه ها را بدون فرمول نویسی محاسبه کنیم:

 

Power Query چیست ؟

Power Query  همانند Power Pivot یک افزونه (Add in )  در اکسل است که می تواند جهت جستجو ، استخراج ، اصلاح و ترکیب داده ها از منابع مختلف استفاده شود.

Query از یک یا چند مرحله تشکیل شده است که می تواند به منبع داده ها متصل شده و یا تغییراتی را در آنها اعمال کند.

با استفاده از این ابزار می توان داده ها را ترکیب ، حذف و جمع آوری کرد و سپس نتایج نهائی را در قالب یک جدول در Sheet و یا در Excel Data Model نمایش داد، همچنین اگر از ماکرو جهت تبدیل داده های* خود در اکسل استفاده می کنید ، Power Query می تواند جایگزین مناسبی برای این کار باشد.

*تبدیل داده با استفاده از Power Query عبارت است از :

Remove columns, rows, blanks

Convert data types – text, numbers, dates

Split or merge columns

Sort & filter columns

Add calculated columns

Aggregate or summarize data

Find & replace text

Unpivot data to use for pivot table

*فعال سازی Power Query در اکسل:

 بطور پیش فرض Power Query در اکسل نسخه ۲۰۱۶ نصب بوده در تب Data وجود دارد:

نکته:

اگر اکسل شما نسخه ۲۰۱۰ یا ۲۰۱۳ می باشد می توانید از طریق لینک زیر با توجه به ۳۲ یا ۶۴ بیتی بودن اکسل خود آن را دانلود ، نصب و فعال کنید:

لینک دانلود Power Query برای اکسل ۲۰۱۳ و ۲۰۱۰

 

مثال: ترکیب اطلاعات چندین فایل در یک فایل با استفاده ار Power Query در اکسل:

در مثال زیر، اطلاعات هزینه تجهیزات مربوط به سه پروژه در سه فایل اکسل جداگانه تهیه شده است و می خواهیم با استفاده از Append در Power Query اطلاعات موجود در سه فایل را در یک فایل ترکیب کنیم.

**مخاطبین محترم که علاقه مند به یادگیری Power Query & Power Pivot در اکسل هستند می توانند از طریق “محصولات آموزشی” در منوی فروشگاه سایت نسبت به تهیه آموزش تصویری Power Pivot و Power Query اقدام نمایند.

 

 

 

دیدگاهتان را بنویسید