پاور پیوت در اکسل (PowerPivot) – با مثال‌های کاربردی

با توجه به افزایش حجم داده‌های قابل تحلیل در مراکز کسب و کار در سال‌های اخیر، نیاز به یک ابزار مفید که بتواند علاوه بر مدیریت حجم زیاد داده، با سرعت آن‌ها را تحلیل کند، احساس می‌شود. شرکت مایکروسافت با توجه به تجربه‌ای که در نرم‌افزار کاربردی اکسل (MS- Excel) کسب کرده، افزونه‌ای برای آن به نام «پاور پیوت» (PowerPivot) معرفی کرده است که ویژگی‌های اساسی آن در مدیریت و پردازش داده‌های حجیم، کاربران را در تجزیه و تحلیل چنین داده‌های، یاری می‌رساند. به همین دلیل در این نوشتار با تکیه بر دانشی که از اکسل در اختیار داریم، با پاور پیوت در اکسل (PowerPivot) آشنا شده و با ذکر مثال‌هایی مناسب، کارایی و قابلیت‌های آن را معرفی خواهیم کرد.

به منظور آشنایی بیشتر با نحوه کار در محیط اکسل و همچنین مبانی اولیه پایگاه داده که در افزونه پاور پیوت به کار گرفته شده، پیشنهاد می‌شود مطالب آموزش اکسل — به زبان ساده و پایگاه داده و اصطلاحات کاربردی آن — به زبان ساده را مطالعه کنید. همچنین خواندن نوشتارهای رابطه در پایگاه داده | به زبان ساده و جدول محوری (Pivot Table) در اکسل ۲۰۱۹ نیز خالی از لطف نیست.

پاور پیوت در اکسل چیست و چرا مفید است؟

در حوزه و امور مربوط به تجزیه و تحلیل اطلاعات مالی، نرم‌افزار اکسل (MS-Excel) به عنوان پرطرفدارترین محصول شرکت مایکروسافت (Microsoft)، نظر بسیاری از کاربران را به خود جذب کرده است، بطوری که اغلب کسانی که در این زمینه مشغول فعالیت هستند، یا بطور حرفه‌ای یا آماتور، با آن سروکار داشته و داده‌ها را به کمک ابزارهای آن، دسته‌بندی، جمع‌بندی و براساس جداول یا نمودارها، نمایش می‌‌دهند.

با ظهور و رشد سریع و نمایی داده و اطلاعات تولیدی توسط بشر، بحث مربوط به «مِه داده» (Big Data) به علوم رایانه اضافه شده و مطابق با آن، روش و ابزارهای نگهداری و تجزیه و تحلیل آن‌ها نیز به بازار آمده است. شرکت مایکروسافت نیز با معرفی افزونه «پاور پیوت» (PowerPivot) و «پاور کوئری» (PowerQuery) به همراه «پاور ویو» (PowerView) راه‌کارهایی مناسب برای کاربرانی اکسل ارائه کرده است. در این نوشتار سعی داریم با ابزار «پاور پیوت» و نحوه اجرای دستوراتی که در اختیار کاربر قرار می‌دهد، بیشتر آشنا شویم.

محدودیت‌های زیرساختی و پردازش اکسل برای نسل قدیمی کاربران مانند محدودیت تعداد ردیف آن که در نسخه 2019 به 1،048،576 سطر می‌رسد، یکی از دلایل ظهور «پاور پیوت» است. کند شدن سرعت پردازش برای حجم زیاد در یک کارپوشه که شامل چندین کاربرگ و سطرهای زیاد باشد در اکسل اجتناب ناپذیر بوده و در مواردی که مجموعه‌های بزرگ داده، جداول داده‌ها و صفحات گسترده به هم پیوسته یا مرتبط باشند، واقعا افراد را دچار سردرگمی کرده یا اکسل را از ارائه خدمات در چنین حالتی عاجز می‌کند.

در سال 2010، مایکروسافت بُعد جدیدی به اکسل اضافه کرد و با ارائه دو افزونه «پاورپیوت و پاورکوئری» (Power Pivot, Power Pivot) بسیاری از کاستی‌های اکسل را جبران کرده و بواسطه آن، پایه‌های هوش تجاری یا همان «پاور بی ای» (Power BI) را شرکت مایکروسافت بنا نهاد.

تاریخچه و بعضی واژگان

پاور پیوت، یکی از افزونه‌هایی است که به عنوان یک ابزار خارجی برای اکسل 2010 و 2013 معرفی شد ولی برای نسخه‌های ۲۰۱۶ و ۲۰۱۹، همچنین اکسل ۳۶۵ این افزونه نصب شده ولی باید فعال سازی شود.

همانطور که در قبل نیز گفته شد، افزونه پاور پیوت، شما را قادر می‌سازد میلیون‌ها سطر یا ردیف را از منابع مختلف، مورد تجزیه و تحلیل قرار داده و برایشان فرمول نویسی کنید. برای مثال در اکسل به طور عادی امکان ورود بیش از ۱ میلیون سطر داده وجود ندارد ولی به راحتی و به کمک فرمان دریافت داده از منابع خارجی، می‌توانید بسیار بیشتر از این حد، داده و اطلاعات به پاور پیوت اضافه کنید.

زبان اصلی عبارت‌های محاسباتی که مایکروسافت در پاور پیوت مورد استفاده قرار می‌دهد، DAX یا به عبارتی (Data Analysis Expressions) به معنی «عبارت‌های تحلیل داده» است. در حقیقیت DAX، مجموعه‌ای از توابع، عملگرها و ثابت‌ها است که می‌تواند در یک فرمول یا عبارت برای محاسبه و بازگرداندن نتایج محاسبه یک یا چند مقدار استفاده شود. به بیان ساده‌تر، DAX به شما کمک می‌کند، اطلاعات جدیدی را از داده‌های موجود در مدل، طبق فرمول‌هایی نوشته شده، ایجاد کنید.

خوشبختانه برای کسانی که از قبل با اکسل آشنا بوده‌اند، فرمول‌های DAX آشنا به نظر می‌رسند، زیرا بسیاری از فرمول‌ها یا توابع قبلی (به عنوان مثال SUM, AVERAGE, TRUNC) در پاور پیوت نیز قابل استفاده بوده و با توابع به کار رفته در اکسل، پارامترهای یکسانی دارند.

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

• پاور پیوت، این به شما امکان می‌دهد صدها میلیون ردیف داده را وارد کنید.
• پاور پیوت به شما امکان می‌دهد بدون نیاز به ایجاد چندین کاربرگ یا کاربرگ مرتبط با یکدیگر (که کنترل آن‌ها بسیار مشکل نیز هست) داده‌ها را از چندین منبع به یک مدل وارد کنید.
• پاور پیوت به شما امکان می‌دهد بدون کُند شدن سرعت کامپیوتر خود، داده‌های وارد شده را تغییر داده و براساس تجزیه و تحلیلی که انجام می‌دهید، گزارش‌های متنوع تولید کنید.
• به شما امکان می‌دهد داده‌ها را با PivotCharts و Power BI به صورت تصویری نمایش دهید.

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

به منظور  تسهیل در امر یادگیری، پیشنهاد می‌کنیم که فایل اطلاعاتی مورد استفاده در این متن را از اینجا دریافت کنید. البته فایل Power Pivot Data File با قالب فشرده ذخیره شده است ولی برای بارگذاری آن روی پاور پیوت در اکسل باید آن را از حالت فشرده خارج کرده و فایل Accounting Data.csv را که یک فایل اطلاعاتی با قالب متنی است را باز یا به پاور پیوت وارد کنید. در مورد نحوه بارگذاری چنین فایل‌هایی در ادامه متن، توضیحات لازم ارائه خواهد شد. قبل از هر چیز نحوه نصب و راه‌اندازی پاور پیوت را معرفی می‌کنیم.

نصب و راه‌اندازی پاور پیوت در اکسل

فرض بر این است که از اکسل نسخه ۲۰۱۶ یا ۲۰۱۹ استفاده می‌کنید. در نسخه‌های قدیمی (اکسل ۲۰۱۰ و ۲۰۱۳) لازم است ابتدا افزونه پاور پیوت را از سایت شرکت مایکروسافت دریافت و روی رایانه شخصی خود بارگذاری کرده باشد.

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

از فهرست File گزینه Options را اجرا کرده تا پنجره Excel Options ظاهر شود. در بخش سمت چپ این پنجره، با انتخاب گزینه Add-ins، لیست افزونه‌های نصب شده «فعال» (Active) یا نصب شده ولی «غیر فعال» (Inactive) دیده می‌شود. برای نصب یا راه‌اندازی افزونه پاور پیوت در اکسل کافی است در لیست Manage که در پایین این پنجره قرار دارد، گزینه COM Add-ins را انتخاب و دکمه Go را کلیک کنید. این پنجره و تنظیمات آن در تصویر ۱، نمایش داده شده است.

با انجام این کار پنجره‌ دیگری با عنوان COM Add-ins ظاهر می‌شود که با انتخاب گزینه Microsoft Power Pivot for Excel و فشردن دکمه OK، این افزونه به برگه‌های اکسل نیز اضافه خواهد شد.

نکته: اگر از نسخه‌های ۲۰۱۰ یا ۲۰۱۳ اکسل استفاده می‌کنید باید با فشردن دکمه Add، آدرس فایل مربوط به افزونه دریافتی را مشخص کرده تا مراحل نصب و راه‌اندازی افزونه پارو پیوت در اکسل صورت گیرد.

نحوه ورود داده به پاور پیوت

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

همانطور که قبلاً اشاره شد، یکی از اصلی‌ترین محدودیت‌های اکسل مربوط به کار با مجموعه داده‌های بسیار بزرگ است. خوشبختانه اکنون اکسل می‌تواند بیش از یک میلیون ردیف را به طور مستقیم در Power Pivot بارگیری کند. برای نشان دادن این امر، یک مجموعه داده نمونه از مقادیر فروش دو ساله یک خرده فروش کالاهای ورزشی با ۹ دسته مختلف کالا و چهار منطقه ایجاد کرده‌ایم که درفایل Power Pivot Data File.csv ثبت شده است. مجموعه داده حاصل، شامل دو میلیون ردیف است. چگونگی دریافت این فایل را در ابتدای متن توضیح داده‌ایم.

برای ورود این مجموعه داده از دو شیوه استفاده می‌کنیم. ابتدا به کمک دستور Manage که در برگه Power Pivot قرار گرفته، این کار را صورت می‌دهیم، سپس نحوه اجرای آن را با کمک New Query از برگه Data و قسمت Get & Transform معرفی خواهیم کرد.

ورود داده از طریق برگه Power Pivot

ابتدا روی دستور Manage را از برگه Data در بخش Data Model، کلیک کنید. با انجام این کار پنجره جدیدی به نام Power Pivot for Excel - book1.xlsx باز می‌شود.

از برگه Home و بخش Get External Data، گزینه From Other Sources را انتخاب کنید. با حرکت به سوی پایین در این پنجره، گزینه‌های مختلف و متنوعی از «بانک‌های اطلاعاتی رابطه‌ای» (Relational  Databases)، «منابع چند بُعدی» (Multidimensional Sources)، «خوراک‌دهی داده» (Data Feeds) و «فایل‌های متنی» (Text Files) قابل مشاهده است. از آنجایی که فایل اطلاعاتی ما نیز یک فایل متنی است، گزینه Text File را انتخاب کرده و دکمه Next را کلیک می‌کنیم. این عملیات در تصویر ۳، قابل مشاهده است.

در گام بعدی با مشخص کردن مسیر فایل متنی، در بخش File Path در پنجره Table Import Wizard و فشردن دکمه Finish، مراحل فراخوانی و قرارگیری اطلاعات در یک مدل برای پاور پیوت در اکسل انجام می‌شود. البته توجه داشته باشید که گزینه Use first row as column headers فعال بوده و در نتیجه، سطر اول مجموعه داده، به عنوان اسامی ستون‌ها قرار می‌گیرد.

به این ترتیب تمامی مراحل انجام شده و به وسیله پنجره‌ای که در تصویر ۵ دیده می‌شود، تایید مراحل کاری اعلام می‌شود. در این پنجره مشخص است که تعداد ردیف‌های مربوط به پاور پیوت در اکسل برابر با دو میلیون سطر است.

پس از انجام این کارها، ستون مربوط به Sales#Date را با قالب تاریخ (Date) و ستون Amount را با قالب عددی (Whole Number) در می‌آوریم. برای انجام این کار، کافی است پس از انتخاب هر یک از ستون‌ها، از برگه Home و قسمت Formatting، گزینه مناسب را در پارامتر Data Type انتخاب کنید. همچنین ستون شماره ردیف که در ابتدای جدول اطلاعاتی قرار گرفته را با انتخاب این ستون و اجرای دستور حذف (Delete) از برگه Design، از بین می‌بریم. به این ترتیب فقط ستون‌ها با قالب دلخواه روی پاور پیوت در اکسل قرار می‌گیرند.

ورود از طریق New Query

با استفاده از برگه Data و قسمت Get & Transform، یک Query جدید از فایل CSV ایجاد می‌کنیم (ایجاد یک پرس و جو جدید را در تصویر ۶ که به صورت پویا نمایی ایجاد شده، مشاهده کنید). این قابلیت در ابتدا بخشی از پاور کوئری بود ولی از اکسل 2016 و 365 به بعد، در برگه Data اکسل قرار گرفته است.

اگر برای دیدن این فایل تصویری با مشکل مواجه هستید، فایل پویانمایی را از اینجا دریافت و مراحل طی شده را مشاهده کنید. همانطور که در این تصویر دیده شد، قالب اعداد (Format)، انتخاب سطر اول به عنوان اسامی ستون‌ها و همچنین حذف ستون اضافی نیز صورت گرفته است.

از یک کارپوشه (Workbook) خالی در اکسل تا بارگیری هر دو میلیون ردیف در Power Pivot، حدود یک دقیقه طول می‌کشد. توجه کنید که ما توانستیم با تبدیل سطر اول به عنوان اسامی ستون‌ها و همچنین تعیین قالب بندی‌های مناسب برای داده‌ها، بسیاری از فعالیت‌ها را با سرعت بیشتر انجام دهیم.

در طول چند سال گذشته، قابلیت Power Query از یک افزونه اکسل به یک قسمت کاملاً مجزا و البته یکپارچه از کاربرگ داده بهبود یافته است. همانطور که دیدید، پاور پیوت می‌تواند داده‌های شما را از طریق مجموعه‌ای از انتخاب‌ها و تغییرات، پاکسازی کرده و با توجه به نیاز شما، قابل بهره‌برداری کند.

وارد کردن داده ها از چندین منبع

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

با استفاده از دستور New Query مطابق با تصویر 3، می‌توانیم از هر یک از منابع زیر استفاده کنیم:

• «مایکروسافت اَژِر» (Microsoft Azure)
• «سرور مایکروسافت اس کیو ال» (SQL Server)
• «ترادیتا» (Teradata)
• «فیسبوک » (FaceBook)
• «سیلزفورس» (SalesForce)
• پرونده‌های JSON
• کتاب کار یا کارپوشه‌های اکسل
• …

بعلاوه، چندین منبع داده می‌توانند یا در تابع Query یا در پنجره Power Pivot برای ادغام داده‌ها ترکیب شوند. به عنوان مثال، می‌توانید داده‌های هزینه تولید را از یک کاربرگ اکسل و نتایج واقعی فروش را از سرور SQL به کمک Query into Power Pivot در کنار هم قرار دهید. به این ترتیب، می‌توانید دو مجموعه داده را با تطبیق اعداد موجود در طبقه‌های مختلف محصول تولید برای درآمد در هر واحد ترکیب کنید.

محاسبات روی مجموعه داده بزرگ در Power Pivot

یکی دیگر از مزایای اصلی پاور پیوت در اکسل که قدرت آن را نشان می‌دهد، توانایی در انجام محاسبات و تغییر یا ایجاد متغیرهای جدید در مجموعه داده‌های بزرگ است. اغلب این کار را برای جمع‌بندی و تجزیه و تحلیل داده‌ها، انجام می‌دهیم. در ادامه به ذکر چند مثال، نحوه انجام این کارها، آورده شده است.

فیلم آموزش اکسل – ابزارهای کاربردی Excel + گواهینامه در فرادرس

کلیک کنید

در تصویر ۷، که تحت عنوان «ایجاد اندازه‌ها» (Creating Measures) نمایش داده شده است، به بخش تفکیک شده پنجره Power Pivot توجه کنید. قسمت بالایی این پنجره، شامل مقادیر است که سطرها نشانگر هر مشاهده و ستون‌ها نیز متغیرها را نشان می‌دهد. در بخش انتهایی این پنجره (که رنگ متفاوتی نسبت به قسمت بالا دارد) فضایی برای محاسبات و به اصطلاح، ایجاد «اندازه‌ها» (Measures) در نظر گرفته شده است.

نکته: اگر قسمت پایینی که برای محاسبات یا اندازه‌ها در نظر گرفته شده، در صفحه کاری شما، مشاهده نمی‌شود، از برگه Home و در قسمت View گزینه Calculation Area را فعال کنید. البته توجه داشته باشید که «نمای داده‌ها» (Data View) نیز فعال باشد.

حال به نوشتن فرمول‌های محاسباتی خواهیم پرداخت. برای مثال اگر بخواهیم مجموع ستون Amount را محاسبه کنیم، با توجه به قالب DAX، به شکل زیر فرمول را می‌نویسیم.

1Measure 1:=SUM([Amount])

یا برای محاسبه مقداری به عنوان میانگین فروش (Average Sales) فرمول را در سطر بعدی به شکل زیر وارد می‌کنیم.

1Average Sales:=AVERAGE('Accounting Data'[Amount])

همانطور که می‌بینید، برای هر یک از اندازه‌ها، به طور جداگانه می‌توانید نام خاصی در نظر بگیرید. در فرمول اول، نام پیش‌فرض به صورت Measure 1 ثبت شده است ولی در فرمول دوم، به طور دلخواه برای این اندازه، نام‌‌گذاری توسط کاربر انجام شده و عبارت Average Sales در نظر گرفته شده. توجه داشته باشید که بین فرمول و نام اندازه، باید از علامت «:» استفاده کرده باشید.

پیشنهاد می‌شود از نام‌های ساده انگلیسی برای اندازه‌ها استفاده کنید. زیرا جدول پاور پیوت در اکسل همچنین نوشتن فرمول‌ها و اجرای محاسبات، ساده‌تر و البته خواناتر خواهد بود. به اضافه اینکه اگر احتیاج به کد نویسی به زبان VBA داشته باشیم، اسامی انگلیسی یا لاتین برای متغیرها،‌ در دسترس‌تر و راحت‌تر در کد‌ها قابل مشاهده یا ویرایش هستند.

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

خلاصه و جمع‌بندی

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