Power Query / Pivot / Map / View / BI چیست و چرا به یک کاربر اکسل نیاز دارند

اصطلاحات «Power Query»، «Power Pivot»، «Power BI» و دیگر «قدرت‌ها» به طور فزاینده‌ای در مقالات و مطالبی درباره Microsoft Excel ظاهر می‌شوند. در تجربه من، همه به وضوح درک نمی کنند که در پس این مفاهیم چه چیزی نهفته است، چگونه آنها به یکدیگر متصل هستند و چگونه می توانند به یک کاربر ساده اکسل کمک کنند.

بیایید وضعیت را روشن کنیم.

پرس و جو برق

در سال 2013، یک گروه ویژه از توسعه دهندگان در مایکروسافت یک افزونه رایگان برای اکسل منتشر کردند. پرس و جو برق (نام‌های دیگر Data Explorer، Get & Transform هستند)، که می‌تواند بسیاری از کارهای مفید را برای کارهای روزمره انجام دهد:

• بارگذاری داده‌ها در اکسل از تقریباً 40 منبع مختلف، از جمله پایگاه‌های داده (SQL، Oracle، Access، Teradata…)، سیستم‌های ERP شرکتی (SAP، Microsoft Dynamics، 1C…)، خدمات اینترنتی (فیس‌بوک، گوگل آنالیتیکس، تقریباً هر وب‌سایت).
• جمع آوری داده ها از فایل ها همه انواع داده های اصلی (XLSX، TXT، CSV، JSON، HTML، XML...)، به صورت تکی و انبوه - از همه فایل های موجود در پوشه مشخص شده. از کتاب‌های کار اکسل، می‌توانید به طور خودکار داده‌ها را از همه برگه‌ها به طور همزمان دانلود کنید.
  
• پاک کردن داده های دریافتی از "آشغال": ستون ها یا ردیف های اضافی، تکرارها، اطلاعات سرویس در "هدر"، فضاهای اضافی یا کاراکترهای غیر قابل چاپ و غیره.
• داده ها را وارد کنید سفارش: حروف درست، اعداد به عنوان متن، پر کردن شکاف‌ها، اضافه کردن «کلاه» صحیح جدول، تجزیه متن «چسبنده» به ستون‌ها و چسباندن آن، تقسیم تاریخ به اجزا و غیره.
  
• به هر طریق ممکن تبدیل جداول، آوردن آنها به شکل دلخواه (فیلتر، مرتب سازی، تغییر ترتیب ستون ها، جابجایی، اضافه کردن مجموع، گسترش جداول متقاطع به تخت و جمع کردن به عقب).
• با تطبیق یک یا چند پارامتر، یعنی تابع جایگزین خوب، داده ها را از یک جدول به جدول دیگر جایگزین کنید VPR (VLOOKUP) و آنالوگ های آن

Power Query در دو نسخه یافت می شود: به عنوان یک افزونه جداگانه برای Excel 2010-2013، که می تواند از وب سایت رسمی مایکروسافت دانلود شود، و به عنوان بخشی از Excel 2016. در حالت اول، پس از نصب، یک تب جداگانه در ظاهر می شود. برتری داشتن:

در اکسل 2016، تمام عملکردهای Power Query قبلاً به طور پیش فرض ساخته شده است و در برگه است. داده ها (تاریخ) به عنوان یک گروه دریافت و تبدیل کنید (دریافت و تبدیل):

امکانات این گزینه ها کاملاً یکسان است.

ویژگی اساسی Power Query این است که تمام اقدامات برای وارد کردن و تبدیل داده ها در قالب یک پرس و جو ذخیره می شود - دنباله ای از مراحل در زبان برنامه نویسی داخلی Power Query که به اختصار "M" نامیده می شود. مراحل را همیشه می توان هر چند بار ویرایش و پخش کرد (پرس و جوی تازه کردن).

پنجره اصلی Power Query معمولاً چیزی شبیه به این است:

به نظر من، این مفیدترین افزونه لیست شده در این مقاله برای طیف گسترده ای از کاربران است. بسیاری از کارهایی که برای آنها مجبور بودید یا به طرز وحشتناکی با فرمول ها تحریف کنید یا ماکرو بنویسید، اکنون به راحتی و به زیبایی در Power Query انجام می شوند. بله، و با به روز رسانی خودکار بعدی نتایج. و با توجه به رایگان بودن آن، از نظر نسبت قیمت به کیفیت، Power Query به سادگی خارج از رقابت است و این روزها برای هر کاربر اکسل متوسط ​​​​پیشرفته ضروری است.

powerpivot

Power Pivot همچنین یک افزونه برای Microsoft Excel است، اما برای کارهای کمی متفاوت طراحی شده است. اگر Power Query بر واردات و پردازش متمرکز باشد، Power Pivot عمدتاً برای تجزیه و تحلیل پیچیده مقادیر زیاد داده مورد نیاز است. به عنوان اولین تقریب، می توانید Power Pivot را به عنوان یک جدول محوری فانتزی در نظر بگیرید.

اصول کلی کار در Power Pivot به شرح زیر است:

1. ما اولین هستیم بارگیری داده ها در Power Pivot - 15 منبع مختلف پشتیبانی می شود: پایگاه داده های رایج (SQL، Oracle، Access ...)، فایل های Excel، فایل های متنی، فیدهای داده. علاوه بر این، می توانید از Power Query به عنوان منبع داده استفاده کنید، که تجزیه و تحلیل را تقریبا همه چیزخوار می کند.
2. سپس بین جداول بارگذاری شده اتصالات پیکربندی شده است یا همانطور که می گویند ایجاد می شود مدل داده. این امر در آینده به شما امکان می‌دهد تا در مورد هر فیلد از جداول موجود گزارش‌هایی ایجاد کنید که گویی یک جدول است. و دوباره VPR وجود ندارد.
3. در صورت لزوم، با استفاده از محاسبات اضافی به مدل داده اضافه می شود ستون های محاسبه شده (شبیه به یک ستون با فرمول در یک "جدول هوشمند") و معیارهای (آنالوگ فیلد محاسبه شده در خلاصه). همه اینها در یک زبان داخلی Power Pivot ویژه به نام DAX (Expressions تجزیه و تحلیل داده) نوشته شده است.
4. در برگه اکسل، با توجه به مدل داده، گزارش های مورد علاقه ما به شکل ساخته شده است میزهای محوری و نمودارها

پنجره اصلی Power Pivot چیزی شبیه به این است:

و مدل داده به این صورت است، یعنی تمام جداول بارگذاری شده با روابط ایجاد شده:

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

• در Power Pivot بدون محدودیت خط (مانند اکسل). می توانید جداول با هر اندازه ای را بارگذاری کنید و به راحتی با آنها کار کنید.
  
• Power Pivot در آن بسیار خوب است فشرده سازی داده ها هنگام بارگذاری آنها در مدل. یک فایل متنی اصلی 50 مگابایتی پس از دانلود به راحتی می تواند به 3-5 مگابایت تبدیل شود.
• از آنجایی که Power Pivot "زیر کاپوت" در واقع یک موتور پایگاه داده تمام عیار دارد، با حجم زیادی از اطلاعات مقابله می کند. خیلی سریع. آیا نیاز به تجزیه و تحلیل 10-15 میلیون رکورد و ایجاد یک خلاصه دارید؟ و همه اینها روی یک لپ تاپ قدیمی؟ مشکلی نیست!

متأسفانه Power Pivot هنوز در همه نسخه های اکسل گنجانده نشده است. اگر اکسل 2010 دارید، می توانید آن را به صورت رایگان از وب سایت مایکروسافت دانلود کنید. اما اگر اکسل 2013-2016 دارید، همه چیز به مجوز شما بستگی دارد، زیرا. در برخی از نسخه ها (مثلاً آفیس پرو پلاس) و در برخی دیگر گنجانده شده است (آفیس 365 خانگی، آفیس 365 شخصی و غیره).

نقشه های قدرت

این افزونه اولین بار در سال 2013 ظاهر شد و در ابتدا GeoFlow نام داشت. برای تجسم داده های جغرافیایی، یعنی اطلاعات عددی روی نقشه های جغرافیایی در نظر گرفته شده است. داده های اولیه برای نمایش از همان Power Pivot Data Model گرفته شده است (به پاراگراف قبلی مراجعه کنید).

نسخه آزمایشی Power Map (تقریباً هیچ تفاوتی با نسخه کامل ندارد) را می توان دوباره به صورت کاملاً رایگان از وب سایت مایکروسافت بارگیری کرد. نسخه کامل در برخی از بسته های مایکروسافت آفیس 2013-2016 به همراه Power Pivot – به صورت یک دکمه گنجانده شده است. نقشه 3 بعدی برگ قرار دادن (درج - نقشه سه بعدی):

ویژگی های کلیدی Power Map:

• نقشه ها می تواند هم مسطح و هم حجیم (کره) باشد.
• شما می توانید از چندین مختلف استفاده کنید انواع تجسم (هیستوگرام، نمودار حباب، نقشه حرارتی، پر کردن منطقه).
• شما می توانید اضافه کنید اندازه گیری زمان، یعنی فرآیند را متحرک کنید و پیشرفت آن را تماشا کنید.
  
• نقشه ها از سرویس بارگیری می شوند بینگ نقشهیعنی برای مشاهده به اینترنت بسیار سریع نیاز دارید. گاهی اوقات در تشخیص صحیح آدرس ها مشکلاتی وجود دارد، زیرا. نام‌های موجود در داده‌ها همیشه با نام‌های Bing Maps مطابقت ندارند.
  
• در نسخه کامل (غیر دمو) Power Map، می توانید از نقشه خود استفاده کنید نقشه های قابل دانلودبه عنوان مثال، برای تجسم بازدیدکنندگان از یک مرکز خرید یا قیمت آپارتمان در یک ساختمان مسکونی درست در پلان ساختمان.
• بر اساس تجسم‌های جغرافیایی ایجاد شده، می‌توانید ویدیوها را مستقیماً در Power Map ایجاد کنید (مثال) تا بعداً آن‌ها را با کسانی که افزونه را نصب نکرده‌اند به اشتراک بگذارید یا آنها را در ارائه Power Point بگنجانید.
  

نمای قدرت

این افزونه برای اولین بار در اکسل 2013 معرفی شد، به گونه ای طراحی شده است که داده های شما را با نمودارها، نمودارها، نقشه ها و جداول تعاملی زنده کند. گاهی اوقات از اصطلاحات برای این استفاده می شود. داشبورد (داشبورد) or داشبورد (کارت امتیاز). نکته اصلی این است که می‌توانید یک برگه ویژه بدون سلول را در فایل اکسل خود وارد کنید - یک اسلاید Power View، که در آن می‌توانید متن، تصاویر و انواع مختلفی از تجسم‌ها را بر اساس داده‌های خود از Power Pivot Data Model اضافه کنید.

چیزی شبیه به این خواهد بود:

تفاوت های ظریف در اینجا عبارتند از:

• داده های اولیه از همان مکان گرفته شده است - از Power Pivot Data Model.
• برای کار با Power View، باید Silverlight را روی رایانه خود نصب کنید - آنالوگ مایکروسافت Flash (رایگان).
  

اتفاقاً در وب سایت مایکروسافت، یک دوره آموزشی بسیار مناسب در مورد Power View در .

قدرت BI

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

1. Power BI Desktop – برنامه ای برای تجزیه و تحلیل و تجسم داده ها، که از جمله شامل تمام عملکردهای افزونه های Power Query و Power Pivot + مکانیسم های تجسم بهبود یافته از Power View و Power Map است. می توانید آن را به صورت رایگان از وب سایت مایکروسافت دانلود و نصب کنید.

در Power BI Desktop می توانید:

• بارگذاری داده ها از روی 70 منبع مختلف (مانند Power Query + کانکتورهای اضافی).
• اتصال جداول برای مدل سازی (مانند Power Pivot)
• اضافه کردن محاسبات اضافی به داده ها با معیارهای и ستون های محاسبه شده در DAX (مانند Power Pivot)
• ایجاد داده های زیبا بر اساس گزارش های تعاملی با انواع مختلف تجسم (بسیار شبیه به Power View، اما حتی بهتر و قدرتمندتر).
• منتشر کردن گزارش هایی را در سایت Power BI Service ایجاد کرده (به نکته بعدی مراجعه کنید) و آنها را با همکاران به اشتراک بگذارید. علاوه بر این، امکان اعطای حقوق متفاوت (خواندن، ویرایش) به افراد مختلف وجود دارد.
  

2. سرویس آنلاین Power BI - به بیان ساده، این سایتی است که در آن شما و هر کاربر در شرکت شما "sandbox" (فضای کاری) خود را خواهید داشت که در آن می توانید گزارش های ایجاد شده در Power BI Desktop را آپلود کنید. علاوه بر مشاهده، حتی به شما امکان ویرایش آنها را می دهد و تقریباً تمام عملکردهای Power BI Desktop را به صورت آنلاین بازتولید می کند. همچنین می‌توانید تجسم‌های فردی را از گزارش‌های افراد دیگر در اینجا قرض بگیرید و داشبورد نویسنده خود را از آنها جمع‌آوری کنید.

چیزی شبیه این به نظر می رسد:

3. Power BI Mobile برنامه ای برای iOS / Android / Windows برای اتصال به Power BI Service و مشاهده راحت (نه ویرایش) گزارش ها و داشبوردهای ایجاد شده درست بر روی صفحه نمایش تلفن یا رایانه لوحی شما. می توانید آن را (کاملا رایگان) از اینجا دانلود کنید.

برای مثال، در آیفون، گزارش تولید شده در بالا به این صورت است:

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

طرح های قیمت گذاری Power BI. Power BI Desktop و Mobile رایگان هستند و اکثر ویژگی های Power BI Service نیز رایگان هستند. بنابراین برای استفاده شخصی یا استفاده در یک شرکت کوچک، نیازی به پرداخت یک پنی برای همه موارد فوق ندارید و می توانید با خیال راحت در برنامه بمانید. رایگان. اگر می‌خواهید گزارش‌ها را با همکاران به اشتراک بگذارید و حقوق دسترسی آنها را مدیریت کنید، باید به آن مراجعه کنید BESS (10 دلار در ماه برای هر کاربر). آیا مقدار بیشتری وجود دارد حق بیمه - برای شرکت های بزرگ (بیش از 500 کاربر) که به ظرفیت های ذخیره سازی و سرور جداگانه برای داده ها نیاز دارند.

• پروژه گانت نمودار در اکسل با Power Query
• نحوه ایجاد پایگاه داده در اکسل با استفاده از Power Pivot
• تجسم حرکت در طول مسیر بر روی نقشه در Power Map