افزونه Power Query معجزه دیگری در اکسل!

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

اما در این مطلب از آموزش اکسل قصد داریم به معرفی ابزار جدیدی در اکسل به نام افزونه Power Query پاور کوئری بپردازیم تا ببینید که این محدودیت را اکسل خیلی سریع با ارائه این افزونه در آفیس ورژن 2010 و افزودن آن به بدنه اکسل در آفیس 2016 برطرف کرد.

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

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

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

استفاده از این افزونه باعث صرفه جویی بسیار زیاد در وقت و زمان شما خواهد شد.

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

پاک کردن ردیف های خالی از طریق منوی فیلتر ستون

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

تبدیل مقادیر زمانی به سال

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

(اکسل مجموع تعداد روزها را بر ۳۶۵ تقسیم خواهد کرد ) برای دسترسی به این امکان کاربر باید از طریق سربرگ Transform یا Add columns  گزینه ی Total years را انتخاب کند.

نگه داشتن تکراری ها

در مسیر Home > Remove Duplicates  گزینه ی جدیدی تحت عنوان Keep duplicates در زیر گزینه ی Remove duplicates اضافه شده است .

گزینه ی جدید این امکان را به کاربر میدهد که تنها ردیف هایی را که در ستون حاضر دارای مقادیر مشترک و تکراری هستند نگه داشته و سایر ردیف های را حذف کند.

افزودن راهنما برای sample input values در پنجره ی Change Type with Locale

این قابلیت تبدیل جدید ؛ به کاربر این امکان را میدهد تا فیلد های موجود در یک ستون را بر حسب تنظیمات محلی تغییر دهد .

برای استفاده از این امکان لازم است کابر مقدار محلی را برای تمام ورک بوک تنظیم کند .

(Data >New Query > Query Options > Current Workbook > Data Load)

با کلیک راست بالای ستون در Query Editor preview و انتخاب Change Type > Using Locale  پنجره ی Change Type with Locale به نمایش در خواهد آمد که این امکان را به کاربران می دهد که در زمان انتخاب نوع خاصی از داده یا محلی سازی، به تعدادی مقادیر نمونه دسترسی داشته باشند.

پشتیبانی از فضای سفید در Query Editor preview

Query Editor preview به کاربران اجازه خواهد داد تا نمایش هر نوع کاراکتر های سفید را در سلول ها مشاهده کنند

هرچند برخورد پیشفرض برنامه با این کاراکتر ها بر روی show whitespace تنظیم شده ، اما کاربران همچنان این امکان را خواهند داشت از سربرگ View در نوار Query Editor این تنظیم را تغییر دهند

قابلیت غیرفعال سازی پیشنمایش در پنجره ی Navigator

در منوی Display options کاربر این امکان را خواهد داشت تا پیشنمایش را در پنجره ی Navigator غیرفعال کند

پشتیبانی از Technical name در پنجره ی Navigator

این قابلیت جدید به کاربر اجازه می دهد تا نحوه ی آدرس دهی را از physical name به technical name تغییر دهد .

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

تغییر نام کوئری ها از کوئری موجود در Query Editor

درون Query Editor کاربر میتواند به سرعت با کلیک راست بر روی کوئری ها یا دابل کلید و انتخاب آنها و سپس فشردن کلید F2  کوئری ها را تغییر نام دهد.

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

این قابلیت جدید که از طریق بخش Advanced options در پنجره ی data source قابل دسترسی است به کاربر اجازه میدهد مقدار مجاز وقفه در انجام یک دستور در هنگام اتصال به دیتابیس را تعین کند . این مقدار بر حسب دقیقه خواهد بود.

امکان غیرفعال سازی تنظیمات حریم خصوصی موجود در سطح ماشین (شامل کلید های رجیستری)

از آنجا که Power Query به کاربران اجازه میدهد داده ها را از منابع متعدد در یک ورک بوک ادغام کنند تنظیمی تحت عنوان Privacy Levels وجود دارد که کاربران میتوانند برای هر منبع که برای ادغام اطلاعات به آن وصل میشوند یکی از سطوح عمومی ، سازمانی و یا خصوصی را تعیین کنند .

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

• ترکیب داده های بر پایه ی سطوح Privacy برای هر منبع – این امکان جدید به کاربر اجازه می دهد تا سطوح Privacy موجود را بر روی تمام فایل های .XLSX موجود اعمال کند.
• ترکیب داده ها بر اساس سطوح Privacy هر فایل – عملکرد پیشفرض که با عملکرد موجود در نسخه های قبلی یکیست این گونه است.

• نادیده انگاشتن سطوح Privacy – این قابلیت جدید به کاربر اجازه می دهد تا همیشه بدون توجه به تنظیمات ورک بوک حاضر ، از محدودیت Privacy برای هر کدام از فایلهای .XLSX عبور کنند.

راهنمای نصب این افزونه را در فیلم آموزشی زیر می توانید مشاهده کنید.

به زودی آموزشهای مفیدی را در خصوص نحوه کار با این افزونه در سایت ارائه خواهیم داد.