افزونه Power Pivot در اکسل

در این مطلب قصد داریم به بررسی افزونه Power Pivot در اکسل بپردازیم. افزونه ای جذاب و پرطرفدار به منظور تحلیل و گزارش گیری.

امروزه داده (Data) به عنوان یکی از مهمترین دارایی های هر سازمان و کسب و کاری به شمار می آید، اما تا زمانی که سازمان نتواند درک و بینشی از آن ها استخراج کند این دارایی بدون استفاده خواهد ماند.

اگر بستری فراهم شود تا بتوانید با داده دست و پنجه نرم کنید و آن ها را بصری سازید و درک و بینش موجود در داده را استخراج کنید، می توایند از دارایی ارزشمند خود کمال بهره برداری را انجام دهید.

امروزه بسیاری از شرکت ها در پی پیاده سازی راهکارهای Self-Service BI و سوق دادن کاربران کسب و کار به اتخاد تصمیمات داده محور بدون وابستگی به کارکنان فنی سازمان هستند.

اگر با اکسل کار می کنید و مجموعه داده ای برای تحلیل دارید راهکار شما استفاده از افزونه Power Pivot در اکسل است. به این وسیله می توانید داده های خود را import کنید، روابط را برقرار کنید و گزارش های خود را شامل PivotTable و PivotChart ایجاد کنید.

اضافه کردن افزونه Power Pivot

Power Pivot به شما قدرت تحلیل داده ها را هدیه می کند. برای کار با Power Pivot باید ابتدا آن را فعال کنید. برای فعال سازی به طریق زیر عمل کنید.

• ابتدا اکسل را باز کنید!
• از منوی File به Option بروید.
• Add-ins را انتخاب نمایید.
• منوی کشویی Manage را انتخاب کنید و از گزینه ها COM Add-ins را انتخاب نمایید.
• بر روی GO کلیک کنید.

• مطابق تصویر با پنجره ای مواجه خواهید شد که باید از آن گزینه Microsoft Power Pivot For Excel را فعال نمایید و در نهایت بر روی OK کلیک کنید.

عملیات با موفقیت به پایان رسید.

شروع کار با Power Pivot

برای شروع کار با افزونه Power Pivot در اکسل از یک مجموعه داده استاندارد به نام Adventure Works استفاده می نماییم که شامل چندین شیت می باشد. این شیت ها شامل اطلاعات فروش، کالا، زیر گروه کالاها، گروه کالا ها، مشتریان و اطلاعات تاریخی است.

بارگذاری جداول در مدل داده

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

به تب Power Pivot رفته و Manage را انتخاب کنید.

بر روی Other From Sources کلیک کنید و از Wizard باز شده Excel file را انتخاب کنید.

صفحه جدیدی باز می شود که باید مسیر فایل را در آن وارد کنید. بر روی test Connection کلیک کنید. اگر با پیغام Test connection succeeded روبرو شدید یعنی اوضاع مرتب است!

بر روی Next کلیک کنید تا به صفحه بعد هدایت شوید.

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

در نهایت بر روی Finish کلیک کنید.

در نهایت جداول در مدل بارگذاری می شوند.

همچنین می توانید به یک پایگاه داده در SQL Server متصل شوید. کافیست از From Other Sources گزینه Microsoft SQL Server را انتخاب کنید.

در این مرحله انتخاب می کنیم که از لیست جداول، جدول های مورد نظر خود را انتخاب کنیم.

در این مرحله می توانیم جداول مورد نیاز خود را انتخاب کنیم.

برقراری ارتباط بین جداول

یکی از مهمترین قابلیت های افزونه Power Pivot در اکسل امکان برقرای روابط بین جداول است.

ابتدا کمی در مورد روابط صحبت کنیم. نحوه مدل کردن ما بر اساس پایگاه داده رابطه ای است. به این نحو که شما یک جدول Master مانند مشتری در اختیار دارید. در این جدول هر مشتری یک بار تعریف شده پس ردیف تکراری ندارد. همچنین این جدول دارای یک کلید اصلی به نام Primary Key است.

Primary Key فیلدی یکتاست که برای هر مشتری منحصر به فرد است و مشتریان مختلف را از هم تفکیک می کند. این فیلد در جدول مشتری Customer Key می باشد. در عین حال جدول فروش که می تواند هر مشتری در آن بارها و بارها تکرار شده باشد. این 2 جدول باید از طریق Customer Key با هم در ارتباط باشند.

همان طور که مشاهده می کنید ارتباط یک به چند (*) است. به این معنی که هر ردیف از جدول مشتری با چندین ردیف از جدول فروش در ارتباط است.

از طریق این روابط است که شما می توانید گزارش های خود را به بهترین نحو ایجاد کنید.

برای آگاهی از چیستی و چرایی ایجاد روابط مطالب زیر را مطالعه کنید.

از تب Power Pivot بر روی manage کلیک کنید.

در صفحه جدید و از تب Home بر روی Diagram View کلیک کنید.

کافیست کلید جداول را به عنوان پل ارتباطی با هم ارتباط دهیم. به طور مثال با Drag & Drop فیلد ProductKey از 2 جدول DimProduct و FactInternetSales.

در نهایت پس از اتمام برقراری روابط مدل به شکل زیر حاصل می شود.

نحوه ساخت Pivot Table

تا به اینجا مهمترین گام که ساخت مدل داده بود را به اتمام رساندید. حال باید به ساخت Pivot Table بپردازیم. پس از تب Home گزینه Pivot Table را انتخاب نمایید.

باکسی پیش روی شما باز می شود که باید بر روی OK کلیک کنید.

در نهایت با محیطی مانند تصویر زیر مواجه خواهید شد.

در این محیط می توانید گزارش های مورد نیاز خود را بسازید. فرض کنید می خواهیم تعداد فروش هر گروه محصول را مشاهده کنیم. کافی است ستون گروه محصول را در Row و فیلد تعداد را در Values قرار دهیم. گزارش برای ما ساخته خواهد شد.

با رها کردن فیلد تعداد در قسمت Values، به طور پیش فرض اعداد با یکدیگر جمع می شوند. می توانید بر روی زبانه کنار فیلد کلیک کرده و Value Field Setting را انتخاب نمایید.

مطابق تصویر زیر لیستی از توابع پیش روی شما نمایش داده می شوندکه می توانید هر یک از آن ها را انتخاب کنید.

نحوه ایجاد Pivot Chart

برای ایجاد Pivot Chart کافی است از تب Analyze بر روی Pivot Chart کلیک کرده و چارت مورد نیاز خود را انتخاب کنید. ما برای این مثال Pie Chart یا همان نمودار دایره ای را انتخاب کرده ایم.

می خواهیم میزان فروش در سال های مختلف را در این چارت نمایش دهیم. کافیست به طریق تصویر زیر عمل کنیم.

ایجاد Measure

برای ایجاد مژر کافیست از تب Power Pivot گزینه Measures را انتخاب کنیم.

حال در پنجره باز شده نام مژر را تایپ نموده و فرمول خود را بنویسید. به نمونه زیر توجه کنید. قابل ذکر است برای فرمول نویسی در این محیط نیاز به فراگیری زبان DAX خواهید داشت.

می خواهیم میزان فروش را که از ضرب تعداد در قیمت محاسبه می شود برای هر گروه کالا و در هر سال نمایش دهیم. کافیست این مژر را نوشته و در Pivot Table مورد استفاده قرار دهیم.

امکان ایجاد KPI

KPI یا شاخص های کلیدی عملکرد از مهمترین اجزای هر گزارشی به شمار می روند. امکان ایجاد KPI در Power Pivot یکی از مهمترین قابلیت های این افزونه است.

برای این منظور مژری ایجاد کردیم که درصد فروش هر گروه کالا را محاسبه کند. بر اساس این مژر می خواهیم یک KPI ایجاد کنیم. برای این منظور از Power Pivot بر روی New KPI کلیک کنید.

در پنجره باز شده می توانید تنظیمات مقدار واقعی و هدف را انجام دهید.

 در نهایت گزارش به شکل زیر قابل مشاهده خواهد بود که وضعیت فروش هر گروه کالا را نشان می دهد.

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