سبد خرید
0

سبد خرید شما خالی است.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

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

نصب 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 می‌تونیم داده‌ها رو از فایل ‌های متنی، وب، مایکروسافت اکسس و سایر دیتابیس‌ ها هم وارد و ویرایش کنیم. مجدداً تاکید می‌کنیم که دیتای ویرایش‌شده توسطPower Query رو فقط در اکسل می تونیم Load کنیم. پس تا اینجا متوجه شدیم که Power Query دیتا و اطلاعات رو از منابع مختلف (اعم از فایل xlsx، txt،  csv و فایل‌های انواع دیتابیس‌ ها) می‌گیره، پردازش ‌های مورد نظر کاربر رو بر روی اطلاعات انجام میده و در نهایت دیتای اصلاح‌شده رو در اکسل نمایش می‌ده. این پردازش ‌ها می‌تونن شامل: ادغام داده ‌ها، حذف داده‌ های زائد و اضافه، نرمال ‌سازی داده‌ ها و… باشن.

 

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

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

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

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

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

مشخص کردن نسخه ویندوز در اکسل 2010

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

مشخص کردن نسخه ویندوز در اکسل 2013

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

  • برنامه اکسل رو می بندیم و برای دانلود 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 براساس نسخه ویندوز

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

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

Microsoft Power Query for Excel Setup

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

License Terms

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

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

تعیین مسیر نصب Add-in

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

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

پیغام شروع فرآیند نصب

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

پیغام پایان نصب Add-in

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

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

اضافه شدن تب Power Query بعد از نصب Add-in در نسخه 2010

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

اضافه شدن تب Power Query بعد از نصب Add-in در نسخه 2013

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

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

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

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

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

همچنین از تب Developer و گزینه COM Add-Ins این گزینه در دسترس هست.

پنجره COM Add-Ins

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

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

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

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

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

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

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

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

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

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

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

126

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

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد.

توسط
تومان