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 در اکسل 2013 و 2016 ، همانند فیلم آموزشی زیر، تنظمات را انجام دهید:
نکته:
در صورتی که اکسل شما نسخه 2010 است می توانید از طریق لینک زیر، Power Pivot را توجه به 32 یا 64 بیتی بودن اکسل خود آن را دانلود ، نصب و فعال کنید:
لینک دانلود Power Pivot برای اکسل 2010
مثال: ارتباط بین دو 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 در اکسل نسخه 2016 نصب بوده در تب Data وجود دارد:
نکته:
اگر اکسل شما نسخه 2010 یا 2013 می باشد می توانید از طریق لینک زیر با توجه به 32 یا 64 بیتی بودن اکسل خود آن را دانلود ، نصب و فعال کنید:
لینک دانلود Power Query برای اکسل 2013 و 2010
مثال: ترکیب اطلاعات چندین فایل در یک فایل با استفاده ار Power Query در اکسل:
در مثال زیر، اطلاعات هزینه تجهیزات مربوط به سه پروژه در سه فایل اکسل جداگانه تهیه شده است و می خواهیم با استفاده از Append در Power Query اطلاعات موجود در سه فایل را در یک فایل ترکیب کنیم.
