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

با ورود و رشد همه جا در داده های بزرگ ، اکسل ، به عنوان یک ابزار داده و پردازش به محدوده آن فشار داده شده است-در حد مجاز 1،048،576 Row Hard و سرعت پردازش برنامه به یک خزیدن کاهش یافته است. از سال 2010 ، مایکروسافت یک تابع محوری قدرت را منتشر کرد که دامنه و سودمندی اکسل را در جایی که به استخراج ، ترکیب و تجزیه و تحلیل مجموعه داده های بزرگ تبدیل شده است ، تغییر داد. با استفاده از موارد زنده ، Ellen Su ، متخصص امور مالی ، به تفصیل نحوه تسلط بر قدرت محوری را مورد بررسی قرار داده و مزایای پنهان آن را به عنوان یک ابزار داده بزرگ بررسی می کند.

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

نسخه اسپانیایی این مقاله را ترجمه شده توسط Marisela Ordaz بخوانید

خلاصه اجرایی

• با معرفی Excel 2010 و 2013 به عنوان یک افزودنی ، اما اکنون بومی برنامه ، Power Pivot بخشی از پشته هوش تجاری مایکروسافت است که قادر به کار (اما محدود به) تجزیه و تحلیل داده های بزرگ بدون زیرساخت های ویژه یا نرم افزار است.
• به گفته مایکروسافت ، "Power Pivot شما را قادر می سازد میلیون ها ردیف داده از چندین منبع داده را به یک کتاب کار Excel وارد کنید ، بین داده های ناهمگن ایجاد کنید ، ستون ها و اقدامات محاسبه شده را با استفاده از فرمول ها ، ساخت pivottables و pivotcharts ایجاد کنید و داده ها را بیشتر تجزیه و تحلیل کنید. به طوری که شما می توانید بدون نیاز به کمک به فناوری اطلاعات ، تصمیمات تجاری به موقع بگیرید. "
• Power Pivot در پاسخ مستقیم به مطالبات داده های بزرگ نیازهای هوش تجاری معاصر ایجاد شد ، که نسل های قبلی اکسل - 1،048،576 محدودیت ردیف آنها یا کاستی های سرعت پردازش را برای مقابله با آن قرار داده اند.
• Power Pivot توسط مایکروسافت با استفاده از DAX (عبارات تجزیه و تحلیل داده ها) بیان شده است ، که مجموعه ای از توابع ، اپراتورها و ثابت های قابل استفاده در یک فرمول یا عبارت برای محاسبه/بازگشت یک یا چند مقدار است.

• Power Pivot به شما امکان می دهد صدها میلیون ردیف داده را وارد و دستکاری کنید ، در حالی که اکسل محدودیت سختی بیش از یک میلیون ردیف دارد.
• Power Pivot به شما امکان می دهد داده ها را از چندین منبع به یک کتاب کار منبع واحد وارد کنید بدون اینکه بخواهید چندین برگه منبع ایجاد کنید و با موارد احتمالی کنترل و قابلیت انتقال ، مقابله کنید.
• Power Pivot به شما امکان می دهد داده های وارداتی را دستکاری کنید ، آن را تجزیه و تحلیل کنید و بدون کند کردن سیستم رایانه خود ، مانند نمونه های اساسی اکسل ، نتیجه گیری کنید.
• Power Pivot به شما امکان می دهد مجموعه داده های بزرگ خود را با Pivotcharts و Power BI تجسم و دستکاری کنید ، جایی که اکسل اساسی فاقد این قابلیت ها است.

• با کار کردن در کنار شما به عنوان یک شریک فکر برای طراحی ، ساختار ، ساخت و ارائه طیف وسیعی از مدل های مالی ، بودجه ها و تجزیه و تحلیل/پروژه های بزرگ ، همه در مسیر تصمیم گیری در مورد پروژه های اسیر ، ادغام و ادغام یا سرمایه گذاری های استراتژیک قرار دارند.
• با ایجاد مدلهای سفارشی منحصر به فرد برای کسب و کار شما ، با استفاده از Power Pivot و سایر توابع ویژه اکسل.
• همچنین با ایجاد الگوهای پیش ساخته و پیش ساخته که می تواند تقریباً توسط هر کسی در سراسر سازمان شما ، تقریباً برای هر منظور ، با استفاده از محوری قدرت و سایر توابع اکسل تطبیق یابد.
• با آموزش افراد یا گروه های درون سازمان خود در مورد همه چیز از اصول اولیه اکسل ، مدل سازی و تجزیه و تحلیل گرفته تا روشهای کمی پیشرفته با استفاده از محوری قدرت ، جداول محوری برق ، نمودارهای محوری قدرت و پاورقی.
• با تحقق هر یک از این و بیشتر ، در کنار طراحی ، ایجاد و تحویل یک ارائه پاورپوینت جلا و حرفه ای ، پیش از تصمیمات استراتژیک.

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

لباس جدید امپراتور: آموزش Pivot Excel Power

Microsoft Excel پادشاه است. با ورود و رشد نمایی داده های بزرگ ، با این وجود ، ناشی از چندین دهه جمع آوری داده ها و تجمع ، ظهور ذخیره ابری ارزان و ظهور اینترنت اشیاء - یعنی ، تجارت الکترونیک ، رسانه های اجتماعی و بهم پیوستن دستگاه ها - Excel'sقابلیت ها و قابلیت های میراث به حد آنها سوق داده شده است.

به طور خاص ، زیرساخت ها و محدودیت های پردازش نسل قدیمی تر از جمله محدودیت ردیف آن از 1،048،576 ردیف ، یا پردازش اجتناب ناپذیر که در جایی که مجموعه داده های بزرگ ، جداول داده ها و صفحات گسترده به هم پیوسته مربوط می شوند ، قابلیت استفاده آن را به عنوان یک ابزار داده بزرگ بزرگ کاهش می دهد. با این حال ، در سال 2010 ، مایکروسافت ابعاد جدیدی را به اکسل به نام Power Pivot اضافه کرد. Power Pivot عملکرد هوش و تحلیلی تجاری نسل بعدی را ارائه می دهد تا در توانایی خود در استخراج ، ترکیب و تجزیه و تحلیل مجموعه داده های تقریباً بی حد و حصر بدون اختلال در سرعت پردازش ، برتری داشته باشد. با وجود انتشار آن هشت سال پیش ، با این حال ، بیشتر تحلیلگران مالی هنوز نمی دانند چگونه از Excel Power Pivot استفاده کنند ، و بسیاری نمی دانند که حتی وجود دارد.

در این مقاله ، من به شما نشان می دهم که چگونه می توانید از اصول اولیه Pivot برای غلبه بر مسائل مشترک اکسل استفاده کنید و با استفاده از برخی از نمونه ها به مزایای کلیدی اضافی این نرم افزار نگاهی بیندازید. این آموزش Power Pivot به معنای راهنمایی برای آنچه می توانید با این ابزار به دست می آورید ، خدمت می کند و در پایان ، برخی از نمونه های استفاده از نمونه را کشف می کند که در آن محور قدرت اغلب ارزشمند است.

Power Pivot چیست و چرا مفید است؟

Power Pivot یکی از ویژگی های Microsoft Excel است که به عنوان افزودنی به Excel 2010 و 2013 معرفی شده است و اکنون یک ویژگی بومی برای اکسل 2016 و 365 است. همانطور که مایکروسافت توضیح می دهد ، Power Pivot برای اکسل "شما را قادر می سازد میلیون ها ردیف وارد کنیداز داده های موجود از منابع داده های متعدد به یک کتاب کار Excel ، ایجاد روابط بین داده های ناهمگن ، ایجاد ستون ها و اقدامات محاسبه شده با استفاده از فرمول ها ، ساخت pivottables و pivotcharts ، و سپس داده ها را بیشتر تجزیه و تحلیل کنید تا بتوانید بدون نیاز به کمک به آن ، تصمیمات تجاری به موقع بگیرید."

زبان بیان اصلی که مایکروسافت در Power Pivot از آن استفاده می کند DAX (عبارات تجزیه و تحلیل داده ها) است ، اگرچه دیگران را می توان در موقعیت های خاص استفاده کرد. مجدداً ، همانطور که مایکروسافت توضیح می دهد ، "DAX مجموعه ای از کارکردها ، اپراتورها و ثابت هایی است که می تواند در یک فرمول یا بیان برای محاسبه و بازگشت یک یا چند مقدار مورد استفاده قرار گیرد. به عبارت ساده تر ، DAX به شما کمک می کند تا اطلاعات جدیدی را از داده های موجود در مدل خود ایجاد کنید. "خوشبختانه برای کسانی که قبلاً با اکسل آشنا هستند ، فرمول های DAX آشنا به نظر می رسند ، زیرا بسیاری از فرمول ها نحو مشابهی دارند (به عنوان مثال ، جمع ، متوسط ، trunc).

برای وضوح ، مزایای اصلی استفاده از Power Pivot در مقابل Basic Excel را می توان به صورت زیر خلاصه کرد:

• این امکان را به شما می دهد که صدها میلیون ردیف داده را وارد کنید و دستکاری کنید که در آن اکسل محدودیت سختی بیش از یک میلیون ردیف دارد.
• این امکان را به شما می دهد تا داده ها را از چندین منبع به یک کتاب کار منبع واحد وارد کنید بدون اینکه بخواهید چندین برگه منبع ایجاد کنید که از کنترل نسخه و مسائل مربوط به انتقال رنج می برند.
• این امکان را به شما می دهد تا داده های وارداتی را دستکاری کنید ، آن را تجزیه و تحلیل کنید و نتیجه گیری کنید بدون اینکه کامپیوتر خود را به سرعت حلزون کاهش دهید.
• این امکان را به شما می دهد تا داده ها را با Pivotcharts و Power BI تجسم کنید.

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

نحوه استفاده از Power Pivot

1) وارد کردن مجموعه داده های بزرگ

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

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

با استفاده از برگه داده روی روبان ، من یک پرس و جو جدید از پرونده CSV ایجاد کردم (به ایجاد یک پرس و جو جدید در زیر مراجعه کنید). این قابلیت قبلاً به نام Powerquery نامیده می شد ، اما از Excel 2016 و 365 ، محکم تر در برگه داده اکسل ادغام می شد.

ایجاد یک پرس و جو جدید

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

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

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

با استفاده از عملکرد پرس و جو در نمایشگاه 1 ، می توانیم از هر یک از منابع زیر استفاده کنیم:

• مایکروسافت لاجورد
• سرور SQL
• تراداتا
• فیس بوک
• نیروی فروش
• پرونده های JSON
• کتابهای کاری اکسل
• …و خیلی بیشتر

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

3) کار با مجموعه داده های بزرگ

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

معیارهای

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

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

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

ایجاد اقدامات

منبع: الن سو ، متخصص امور مالی TOPTAL

از آنجا ، تماشا کنید که چقدر سریع می توانید یک pivottable آشنا را در یک مجموعه داده بزرگ ایجاد کنید.

ایجاد یک pivottable

میزهای ابعادی

به عنوان تحلیلگران مالی که از اکسل استفاده می کنند ، ما در استفاده از فرمولهای پیچیده برای خم شدن فناوری به خواست خود ماهر می شویم. ما استاد Vlookup ، Sumif و حتی شاخص مخوف (Match ()) هستیم. با این حال ، با استفاده از Power Pivot ، می توانیم بخش اعظم آن را از پنجره بیرون بیاوریم.

اضافه کردن یک جدول ایجاد شده توسط کاربر به یک مدل Pivot Power

برای نشان دادن این قابلیت ، من یک جدول مرجع کوچک ایجاد کردم که در آن هر دسته را به یک نوع اختصاص دادم. با انتخاب "افزودن به مدل داده" ، این جدول در Power Pivot بارگذاری می شود (به اضافه کردن یک جدول ایجاد شده توسط کاربر به یک مدل Pivot Power در بالا مراجعه کنید).

من همچنین یک جدول تاریخ برای استفاده با مجموعه داده های خود ایجاد کردم (به ایجاد جدول تاریخ در زیر مراجعه کنید). Power Pivot برای Excel باعث می شود که یک جدول تاریخ را به سرعت ایجاد کنید تا ماه ها ، محله ها و روزهای هفته ادغام شود. کاربر همچنین می تواند یک جدول تاریخ سفارشی تر برای تجزیه و تحلیل هفته ها ، سالهای مالی یا هر گروه خاص سازمان ایجاد کند.

ایجاد یک جدول تاریخ

ستون های محاسبه شده

علاوه بر اقدامات ، نوع دیگری از محاسبه وجود دارد: ستون های محاسبه شده. کاربران اکسل با نوشتن این فرمول ها راحت خواهند بود ، زیرا آنها بسیار شبیه به فرمول های نوشتن در جداول داده هستند. من یک ستون محاسبه شده جدید در زیر ایجاد کرده ام (به ایجاد یک ستون محاسبه شده در زیر مراجعه کنید) که جدول داده های حسابداری را بر اساس مقدار مرتب می کند. فروش زیر 50 دلار با عنوان "کوچک" شناخته می شود و سایر افراد دارای عنوان "بزرگ" هستند. آیا فرمول احساس شهودی نمی کند؟

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

روابط

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

تعریف روابط

اکنون ، بدون هیچ گونه توابع SUMIF یا VLOOKUP مورد نیاز ، می توانیم یک pivottable ایجاد کنیم که کل فروش را تا سال محاسبه می کند و نوع آن را با یک برش برای اندازه معامله.

قابل استفاده با استفاده از روابط

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

Pivotchart با استفاده از روابط

منبع: الن سو ، متخصص امور مالی TOPTAL

در حالی که این نمودار ساده به نظر می رسد ، چشمگیر است که ایجاد یک ادغام بیش از دو میلیون ردیف داده ، بدون اضافه کردن ستون جدید به داده های فروش ، کمتر از ده ثانیه طول کشید.

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

توابع پیشرفته

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

هوش

غالباً ، وقتی نتایج مالی را بررسی می کنیم ، می خواهیم آن را با یک بازه زمانی قابل مقایسه با سال گذشته مقایسه کنیم. Power Pivot دارای برخی از کارکردهای اطلاعاتی در زمان داخلی است.

به عنوان مثال ، اضافه کردن فقط دو اقدام فوق به جدول داده های حسابداری در Power Pivot به من این امکان را می دهد تا با چند کلیک pivottable زیر را تولید کنم.

اطلاعات مربوط به اطلاعات زمان

منبع: الن سو ، متخصص امور مالی TOPTAL

دانه های ناسازگار

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

دانه های ناسازگار - جدول بودجه

منبع: الن سو ، متخصص امور مالی TOPTAL

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

دانه های ناسازگار - روابط

منبع: الن سو ، متخصص امور مالی TOPTAL

در اکسل ، pivottable زیر به سرعت جمع می شود.

دانه های ناسازگار - بودجه در مقابل نتایج واقعی

منبع: الن سو ، متخصص امور مالی TOPTAL

علاوه بر این ، ما می توانیم اقدامات جدیدی را که محاسبه واریانس بین فروش واقعی و فروش بودجه را به شرح زیر محاسبه می کنیم ، تعریف کنیم:

با استفاده از این اندازه گیری ، می توانیم واریانس را در یک pivottable نشان دهیم.

دانه های ناسازگار - نتایج واریانس

منبع: الن سو ، متخصص امور مالی TOPTAL

درصد از کل

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

این اقدامات اکنون می تواند در یک pivottable جدید مستقر شود:

درصد از کل

منبع: الن سو ، متخصص امور مالی TOPTAL

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

تراکم

فایده دیگر این است که اندازه پرونده کوچک می شود. اندازه اصلی پرونده 91 مگابایت بود و اکنون زیر 4MB است. این فشرده سازی 96 ٪ از پرونده اصلی است.

اندازه پرونده

منبع: الن سو ، متخصص امور مالی TOPTAL

چگونه این اتفاق می افتد؟Power Pivot از موتور Xvelocity برای فشرده سازی داده ها استفاده می کند. به عبارت ساده ، داده ها به جای ردیف در ستون ها ذخیره می شوند. این روش ذخیره سازی به رایانه اجازه می دهد تا مقادیر تکراری را فشرده کند. در مجموعه داده های مثال ما ، فقط چهار منطقه وجود دارد که بیش از هر دو میلیون ردیف تکرار می شوند. Power Pivot برای اکسل می تواند با کارآمدتر این داده ها را ذخیره کند. نتیجه این است که برای داده هایی که مقادیر تکراری زیادی دارند ، برای ذخیره این داده ها بسیار کمتر هزینه دارد.

نکته قابل توجه این است که من از مقادیر کل دلار در این مجموعه داده نمونه استفاده کردم. اگر من دو نکته اعشاری را برای منعکس کردن سنت درج کرده بودم ، اثر فشرده سازی به 80 ٪ هنوز هم از اندازه پرونده اصلی کاهش می یابد.

جدول SSAS

مدل های Power Pivot همچنین می توانند برای کل شرکت مقیاس پذیر باشند. بیایید بگوییم که شما یک مدل محوری برق ایجاد می کنید که شروع به دستیابی به بسیاری از کاربران در سازمان می کند ، یا داده ها به ده میلیون ردیف یا هر دو افزایش می یابد. در این مرحله ، ممکن است شما بخواهید سی کاربر مختلف را که مدل را تازه می کنند یا تغییر ایجاد می کنند. این مدل را می توان یکپارچه به جداول SSA تبدیل کرد. تمام جداول و روابط حفظ می شود ، اما اکنون می توانید فرکانس تازه سازی را کنترل کنید ، نقش ها (به عنوان مثال ، فقط خواندنی ، خواندن و پردازش) را به کاربران مختلف اختصاص دهید و فقط یک قسمت جلوی اکسل کوچک را مستقر کنید که به مدل جدولی پیوند می یابد. نتیجه این است که کاربران شما می توانند با یک کتاب کار کوچک به مدل جدولی مستقر دسترسی پیدا کنند ، اما به فرمول ها و اقدامات دسترسی ندارند.

4) تجسم و تجزیه و تحلیل داده ها

فرمول های مکعب

یکی از درخواست های مداوم مشتری های من این است که من گزارش هایی را ایجاد می کنم که مطابق با یک طرح کاملاً تعریف شده باشد. من مشتریانی دارم که درخواست عرض ستون خاص ، کدهای رنگی RGB و نام و اندازه های از پیش تعریف شده را دارند. داشبورد زیر را در نظر بگیرید:

فرمول های مکعب تعبیه شده

منبع: الن سو ، متخصص امور مالی TOPTAL

اگر همه فروش های ما با Power Pivot برای اکسل قرار داشته باشد ، چگونه می توانیم شماره فروش را جمع کنیم؟با استفاده از فرمول های مکعب! ما می توانیم فرمول های مکعب را در هر سلول اکسل بنویسیم و این محاسبه را با استفاده از مدل قدرت محوری که قبلاً ساخته ایم انجام می دهد.

به عنوان مثال ، فرمول زیر در "کل فروش 2016" در سلول تایپ می شود:

قسمت اول فرمول ، که به رنگ زرد برجسته شده است ، به نام مدل Pivot Power اشاره دارد. به طور کلی ، معمولاً این کار با نسخه های جدیدتر از Power Pivot برای اکسل است. بخش در گرین تعریف می کند که ما می خواهیم از کل فروش استفاده کنیم. بخشی از رنگ آبی به اکسل می آموزد که فقط برای ردیف هایی که دارای تاریخ فروش با یک سال برابر با سال 2016 هستند ، فیلتر کند.

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

قدرت

یکی دیگر از مزیت های Power Pivot برای Excel این است که می توانید به سرعت هر کتاب کاری Pivot Power را که می سازید بگیرید و به سرعت آن را به یک مدل Power BI تبدیل کنید. با وارد کردن کتاب کار اکسل به طور مستقیم به برنامه دسک تاپ Power BI یا Power BI بصورت آنلاین ، می توانید داده های خود را با هر کسی در سازمان خود تجزیه و تحلیل ، تجسم و به اشتراک بگذارید. در اصل ، Power BI Power Pivot ، PowerQuery و SharePoint است که همه در یک حرکت می کنند. در زیر ، من با وارد کردن محوری قدرت قبلی برای کتاب کار Excel به برنامه دسک تاپ Power BI ، داشبورد ایجاد کرده ام. توجه کنید که رابط کاربری تعاملی چقدر است:

قدرت

یک نکته عالی در مورد قدرت BI ، پرسش و پاسخ زبان طبیعی است. برای نشان دادن ، من مدل Power BI را بر روی حساب Power BI آنلاین خود بارگذاری کردم. از وب سایت ، می توانم سؤال کنم و Power BI تجزیه و تحلیل مناسب را به عنوان نوع من ایجاد می کنم:

پرسش و پاسخ زبان طبیعی

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

یکی دیگر از مزایای Power BI این است که توسعه دهندگان در مایکروسافت دائماً به روزرسانی های آن را منتشر می کنند. ویژگی های جدید ، بسیاری از آنها با استفاده از کاربر ، ماهانه تحت فشار قرار می گیرند. از همه مهمتر ، این یک انتقال یکپارچه از Power Pivot برای اکسل است. بنابراین ، زمان سرمایه گذاری در یادگیری فرمول های DAX می تواند در Power BI مستقر شود! برای تحلیلگر که باید تجزیه و تحلیل خود را در بسیاری از کاربران در مورد دستگاه های مختلف به اشتراک بگذارد ، Power BI ممکن است ارزش کاوش داشته باشد.

بهترین روشها

پس از شروع کار ، چند روش برتر وجود دارد که باید آنها را دنبال کنید.

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

بهترین روش دیگر این است که به یاد داشته باشید که Power Pivot عالی نیست. در اکسل ، ما عادت کرده ایم که با گسترش مداوم برگه های خود به سمت راست ، محاسبات را ایجاد کنیم. محور قدرت برای اکسل در صورت محدود کردن این تمایل به سرنوشت آشکار ، داده ها را به طور مؤثر پردازش می کند. به جای ایجاد مداوم ستون های محاسبه شده در سمت راست داده های خود ، یاد بگیرید که در قسمت پایین اقدامات خود را بنویسید. این عادت اندازه پرونده های کوچکتر و محاسبات سریعتر را تضمین می کند.

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

موارد استفاده در دنیای واقعی

در این مقاله ، من فقط تعداد معدودی از راه هایی که محور قدرت برای اکسل به شما امکان می دهد یک قدم مهم فراتر از Excel Plain-Vanilla بردارید. من فکر کردم که برجسته کردن برخی موارد استفاده در دنیای واقعی مفید خواهد بود که در آن من قدرت محوری را برای اکسل پیدا کردم بسیار مفید است.

• تجزیه و تحلیل عملکرد یک نمونه کارها بزرگ دارایی ها در محدوده زمان مختلف: از آنجاو عملکرد ماهانه ماه همه با نوشتن فقط چند اقدامات به صورت نورد.
• خلاصه کردن داده های حسابداری با استفاده از سطح جمع آوری سفارشی: با شناسایی هر مورد خط عمومی به صورت نام ، دسته و صورتهای مالی ، گزارش ها به سرعت می توانند ایجاد شوند که شامل موارد خط مناسب است.
• زنجیرها می توانند فروش همان فروشگاه را شناسایی کنند: با استفاده از یک جدول که هنگام ورود فروشگاه ها به صورت آنلاین ، نتایج مالی را می توان به صورت یک فروشگاه مقایسه کرد.
• Pinpoint بیش از حد و کم عملکرد در فروش: Pivottables را می توان ایجاد کرد که پنج SKU های برتر و پنج SKU های پایین را با فروش ، حاشیه ناخالص ، بازه های زمانی تولید و غیره برجسته می کند.
• خرده فروشان می توانند جداول تقویم را که از پیکربندی 4-4-5 استفاده می کنند تعریف کنند: با استفاده از یک جدول تاریخ سفارشی ، یک خرده فروش می تواند هر روز را به راحتی 4-4-5 ماه به راحتی اختصاص دهد ، سپس نتایج فروش روزانه را می توان در ماه مربوطه قرار داد.

از صفحات گسترده clunky گرفته تا کتاب های کاری مدرن

به عنوان تحلیلگران مالی ، ما موظفیم محاسبات پیچیده ای را در مورد مجموعه داده های در حال گسترش انجام دهیم. از آنجا که اکسل در حال حاضر ابزار تحلیلی پیش فرض است ، منحنی یادگیری محوری قدرت آسان است ، و بسیاری از توابع عملکردهای بومی Mirror Excel.

با استفاده از توابع مکعب ، Power Pivot برای اکسل یکپارچه در کتابهای کار اکسل موجود شما مخلوط می شود. از افزایش بهره وری محاسباتی نمی توان غافل شد. با فرض سرعت پردازش 20 ٪ سریعتر ، که محافظه کار است ، تحلیلگر مالی که روزانه شش ساعت را در اکسل می گذراند می تواند 300 ساعت در سال صرفه جویی کند!

علاوه بر این، اکنون می توانیم مجموعه های داده ای را که بسیار بزرگ تر از آنچه قبلاً با Excel سنتی خود می توانستیم، تجزیه و تحلیل کنیم. با مدل هایی که به طور کارآمد طراحی شده اند، می توانیم به راحتی 10 برابر مقدار داده ای داشته باشیم که قبلاً در اکسل سنتی مجاز بودیم، در حالی که چابکی تحلیلی سریع را حفظ می کنیم. با قابلیت تبدیل مدل ها از Power Pivot به SSAS Tabular، میزان داده ای که می توان پردازش کرد، 100 تا 1000 برابر آن چیزی است که می توانیم در اکسل به دست آوریم.

توانایی Power Pivot برای اکسل برای انجام محاسبات سریع بر روی مقادیر زیادی از داده ها و همچنان حفظ توانایی فرو رفتن در جزئیات، می تواند تجزیه و تحلیل مالی را از صفحات گسترده بی نظم به کتاب های کاری مدرن تبدیل کند.

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

منابع و راهنماهای مفید

Collie, R., & Singh, A. (2016). Power Pivot و Power BI: راهنمای کاربر اکسل برای DAX، Power Query، Power BI و Power Pivot در Excel 2010-2016. ایالات متحده: ماکرو مقدس! کتاب ها

فراری، آ.، و روسو، ام. (2015). راهنمای قطعی DAX: هوش تجاری با Microsoft Excel، SQL Server Analysis Services و Power BI. ایالات متحده: مایکروسافت پرس، ایالات متحده آمریکا.

درک اصول اولیه

پاور پیوت میز چیست؟

وقتی به جداول Power Pivot در مقابل Pivot نگاه می کنید، Power Pivot یک ویژگی اکسل است که واردات، دستکاری و تجزیه و تحلیل داده های بزرگ را بدون از دست دادن سرعت/عملکرد امکان پذیر می کند. جداول پاور پیوت جداول محوری هستند که به کاربر اجازه می دهند داده های جداول مختلف را با هم ترکیب کنند و در هنگام کار بر روی چندین جداول، زنجیره سازی فیلتر قدرتمندی را برای آن ها فراهم می کنند.

چگونه می توانم یک جدول Power Pivot ایجاد کنم؟

ابتدا مجموعه داده خود را در کتاب کار Power Pivot خود وارد کنید. سپس در پنجره Power Pivot در تب Power Pivot Home، روی PivotTable کلیک کنید. در مرحله بعد، "کاربرگ جدید" را انتخاب کنید (اکسل یک PivotTable خالی اضافه می کند). سپس، PivotTable خالی را انتخاب کنید و دستورالعمل های موجود در آن را دنبال کنید.

چگونه نمودار Power Pivot ایجاد کنم؟

ابتدا مجموعه داده خود را در کتاب کار Power Pivot خود وارد کنید. سپس یک جدول Power Pivot ایجاد کنید. سپس در تب Power Pivot، روی فلش زیر PivotTable کلیک کرده و PivotChart را انتخاب کنید.«کاربرگ موجود» را انتخاب کرده و روی «OK» کلیک کنید. اکسل یک PivotChart خالی به همان کاربرگ اضافه می کند. دستورالعمل های آنجا را دنبال کنید.