فعال کردن Power Query در ورژن های مختلف اکسل

دسترسی به Power Query در نسخه های مختلف اکسل

Power Query یکی از افزونه های قدرتمند اکسل هست که از اون به جادو سبز هم نام می برن. در این مقاله قصد داریم بعد از معرفی کلی این افزونه، نصب Power Query در اکسل و نحوه دسترسی به اون رو در نسخه های مختلف اکسل (۲۰۱۰، ۲۰۱۳، ۲۰۱۶ و ۲۰۱۹) بررسی کنیم.

معرفی ابزار PQ در اکسل

وظیفه‌ی Power Query ویرایش، اصلاح، ادغام و یکپارچه‌سازی داده‌ها برای اکسل هست. به عبارت دیگه با استفاده از Power Query می‌تونیم داده‌های مختلف را ساماندهی و اصلاح کنیم و بعنوان خروجی، داده‌های اصلاح‌شده را به فایل‌ اکسل وارد کنیم. Power Query در بسیاری از موارد به کارمون سرعت عمل، دقت و کیفیت بیشتری نسبت به سایر ابزارهای اکسل میده. از نگاه متخصصین حوزه تحلیل‌داده Power Query یک افزونه‌ی داخلی اکسل هست و از جمله نرم ‌افزارهای ETL محسوب می‌شه و کاربردش در تجزیه ‌و تحلیل داده هست. ETL در واقع مخفف کلمات Extract (استخراج داده از منابع مختلف) ، Transform (تبدیل داده‌ها به نحو دلخواه و ایجاد تغییرات روی آن‌ ها) و Load (بارگذاری کردن و نمایش داده ‌های تبدیل شده و اصلاح شده) هست. در ادامه با چند مثال با کاربردهای Power Query بیشتر آشنا می شیم: مثال ۱: فرض کنید داده‌هایی از فروش محصولات داریم و اطلاعات فروش همه‌ی ماه‌های سال درون یک فایل اکسل وارد شده‌، در این صورت ویرایش داده‌ ها با استفاده از خود اکسل دردسر چندانی نداره. حالا فرض کنید داده‌ ها و اطلاعات فروش هر ماه محصولات در یک فایل اکسل مجزا ذخیره شده باشه و برای ایجاد نمودار و بررسی وضعیت کسب ‌و کار بخواهیم اطلاعات این فایل‌های مجزا رو با هم ترکیب کنیم. در صورتی که بخواهیم این کار رو با استفاده از ابزار معمولی اکسل انجام بدیم کار زمان‌ بر و پردردسری در پیش داریم، اینجاست که Power Query به کمکمون میاد! با استفاده از Power Query انجام این فرآیند با چند کلیک و در کسری از ثانیه انجام میشه و می‌تونیم دیتای ترکیب‌شده در Power Query رو به راحتی وارد فایل اکسل‌ کنیم و نمودار و گزارش بسازیم. مثال ۲: یک فایل اکسل داریم که شامل ۵۰ شیت هست و در هر شیت اطلاعات درآمد یکی از شعبه‌‌های شرکت وارد شده. این اطلاعات به گونه ای وارد شدن که ساختار ورود اطلاعات یکسان نیست (یعنی مثلا در شیت‌های ۱ تا ۲۰ ستون اول جدول “میزان درآمد” هست و ستون دوم “میزان سود”. در حالی که در سایر شیت ‌ها ستون اول جدول “میزان سود” هست و ستون دوم “میزان درآمد”)، و در انتهای سال مالی می‌خواهیم این داده ها رو با هم ادغام کنیم و از آنها نمودار یا Pivot Table تهیه کنیم، در این صورت باید قبل از ادغام، ساختار این داده‌ها رو یکسان و یکپارچه کنیم. انجام این کار با Power Query بسیار آسان‌تر و سریع‌تر از دیگر ابزار اکسل است. البته فراموش نکنید با وجود اینکه Power Query پردازش‌های خود را کاملاً جدا از اکسل انجام می‌ده، در نهایت باید خروجی خودش رو به اکسل عرضه کنه (دیتای خروجی Power Query فقط در اکسل قابل نمایش هست).

  حالا که با کلیات Power Query آشنا شدیم، نحوه دسترسی به این ابزار قدرتمند رو در نسخه های مختلف اکسل بررسی می کنیم. Power Query در ابتدا به عنوان یک Add-in در اکسل ۲۰۱۳ معرفی شد و بعدها به عنوان Add-in برای نسخه ۲۰۱۰ اکسل هم در دسترس قرار گرفت. این افزونه، از آفیس نسخه ۲۰۱۶ به هسته‌ی نرم‌افزار اکسل اضافه شده و بدون نیاز به هیچ‌گونه دانلودی در اکسل ۲۰۱۶ و ۲۰۱۹ قابل استفاده هست. در ادامه نحوه دسترسی به Power Query رو نسخه های مختلف اکسل بررسی می کنیم.

PQ در نسخه های ۲۰۱۰ و ۲۰۱۳ اکسل

باتوجه به اینکه در نسخه های ۲۰۱۰ و ۲۰۱۳ اکسل Power Query به عنوان یک Add-in نصب میشه باید مراحل زیر رو طی کنیم:

• نسخه ویندوز نصب شده رو مشخص می کنیم (bit32/64 bit). برای این کار در صورتی که نسخه اکسل ۲۰۱۰ هست از تب File گزینه Help رو انتخاب می کنیم. مطابق شکل ۱ در سمت راست صفحه نسخه اکسل و ویندوز مشخص شده. در نسخه ۲۰۱۳ از تب File گزینه Account و سپس About Excel رو انتخاب می کنیم و مطابق شکل ۲ نسخه ویندوز نمایش داده میشه.

شکل ۱- مشخص کردن نسخه ویندوز در اکسل ۲۰۱۰

شکل ۲- مشخص کردن نسخه ویندوز در اکسل ۲۰۱۳

• برنامه اکسل رو می بندیم و برای دانلود Power Query Add-in وارد لینک زیر می شیم؛

https://www.microsoft.com/en-us/download/details.aspx?id=39379 بعد از کلیک بر روی دکمه Download صفحه ای مطابق شکل ۳ نمایش داده میشه که براساس نسخه ویندوز (bit32/64 bit ) که در مرحله قبل مشخص شد Power Query Add-in رو دانلود می کنیم و سپس روی دکمه Next کلیک می کنیم.

شکل ۳- دانلود Power Query add-in براساس نسخه ویندوز

• بعد از پایان دانلود فایلی با پسوند .msi نمایش داده می شه. روی فایل کلیک می کنیم و پنجره ای مطابق شکل زیر نمایش داده میشه. روی دکمه Next کلیک می کنیم و پنجره ای مطابق شکل ۵ نمایش داده میشه که در اون شرایطی برای استفاده از برنامه درج شده. در این صفحه هم بعد از انتخاب ” accept the terms of the License Agreement” دکمه Next رو می زنیم؛

شکل ۴- نصب Power Query در اکسل

شکل ۵- قبول توافقنامه استفاده از برنامه

بعد از زدن دکمه Next در پنجره بعدی مطابق شکل ۶ محل نصب Add-in رو مشخص می کنیم و دوباره دکمه Next رو می زنیم.

شکل ۶- تعیین مسیر نصب Add-in

بعد از زدن دکمه Next پنجره ای مطابق شکل ۷ نمایش داده می شه. در این پنجره دکمه Install رو می زنیم. بعد از نصب Add-in پیغامی مطابق شکل ۸ نمایش داده می شه که به معنی پایان نصب Add-in هست. روی دکمه Finish کلیک می کنیم.

شکل ۷- پیغام شروع فرآیند نصب

شکل ۸- پیغام پایان نصب Add-in

• یک فایل اکسل باز می کنیم و مشاهده می کنیم که مطابق شکل ۹ (در نسخه ۲۰۱۰) و شکل ۱۰ (در نسخه ۲۰۱۳) یک تب جدید با نام Power Query ایجاد شده.

شکل ۹- اضافه شدن تب Power Query بعد از نصب Add-in در نسخه ۲۰۱۰

شکل ۱۰- اضافه شدن تب Power Query بعد از نصب Add-in در نسخه ۲۰۱۳

• در صورتی که تب Power Query اضافه نشده باشه باید Add-in رو فعال کنیم. برای این منظور از تب File گزینه Options و بعد Add- Ins رو انتخاب می کنیم. مطابق شکل ۱۱ از زبانه Manage گزینه COM Add-ins رو انتخاب می کنیم و دکمه Goکلیک می کنیم.

شکل ۱۱- فعال کردن Power Query Add-in

بعد از زدن دکمه Go پنجره ای مطابق شکل زیر نمایش داده می شه. در پنجره COM Add-Ins گزینه Microsoft Power Query for Excel رو انتخاب می کنیم و دکمه OK رو می زنیم. حالا می تونیم تب Power Query رو مطابق شکل ۹ در نسخه ۲۰۱۰ اکسل و شکل ۱۰ در نسخه ۲۰۱۳ مشاهده کنیم. همچنین از تب Developer و گزینه COM Add-Ins این گزینه در دسترس هست.

شکل ۱۲- پنجره COM Add-Ins

Power Query در نسخه ۲۰۱۶ اکسل

همانطور که قبلا هم گفتیم، Power Query از نسخه ۲۰۱۶ به بعد به هسته‌ی نرم‌افزار اکسل اضافه شده و برای استفاده از آن به دانلود Add-in نیاز نداریم. برای دسترسی به Power Query در اکسل ۲۰۱۶ کافیه اکسل رو باز کنیم و وارد تب Data بشیم، Power Query همینجاست! در اکسل ۲۰۱۶ مطابق شکل ۱۳ دسترسی به Power Query از طریق گروه Get & Transform امکان‌پذیر هست.

شکل ۱۳- دسترسی به Power Query در نسخه ۲۰۱۶ اکسل

برای رفتن به محیط Power Query روی New Query کلیک می کنیم و یک فایل حاوی داده را انتخاب می کنیم.

Power Query در نسخه ۲۰۱۹ اکسل

با کمی تفاوت نسبت به اکسل ۲۰۱۶، دسترسی به Power Query در اکسل ۲۰۱۹، مطابق شکل ۱۴ از طریق تب Data و گروه‌ های Get & Transform Data و Queries & Connections امکان‌پذیر هست.

شکل ۱۴- دسترسی به Power Query در نسخه ۲۰۱۹ اکسل

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