سبد خرید
0

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

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

چند مثال کاربردی از ابزار Power Query

کاربرد Power Query
۵/۵ - (۱ امتیاز)

چند کاربرد Power Query در اکسل

در مقاله قبلی پاور کوئری و نحوه دسترسی به این ابزار رو در نسخه های مختلف اکسل مورد بررسی قرار دادیم. در این مقاله قصد داریم چند کاربرد Power Query رو در قالب چند مثال کاربردی توضیح بدیم.

مثال ۱: پالایش داده های مربوط به کارکنان

در این مثال یک بانک اطلاعاتی حاوی اطلاعات ۱۰۰۰ نفر از پرسنل یک شرکت رو داریم. (فایل مربوطه جهت دانلود در پایان مقاله قابل دسترس هست) قصد داریم با استفاده از پاور کوئری اطلاعات کارکنان رو پالایش کنیم تا بعد از بارگذاری اطلاعات در اکسل، بتونیم تحلیل های دقیقی روی این اطلاعات انجام بدیم. برای پالایش اطلاعات کارکنان مراحل زیر رو در پاور کوئری طی می کنیم:

  1. دریافت اطلاعات از بانک اطلاعاتی کارکنان؛
  2. تعیین تکلیف افرادی که جنسیت و واحد کاریشون مشخص نیست؛
  3. حذف کارکنانی که حقوق دریافت نمی کنند؛
  4. استخراج کشور از آدرس کارکنان و حذف ستون آدرس؛
  5. استخراج سال شروع همکاری هر کارمند و نمایش آن در یک ستون جداگانه؛
  6. و در نهایت انتشار اطلاعات در فایل اکسل جهت انجام تحلیل های لازم.

بعد از اینکه مراحل ۱ تا ۶ رو انجام دادیم در صورتی که اطلاعات جدید به بانک اطلاعاتی کارکنان اضافه بشه می تونیم به راحتی با آپدیت (Refresh) پاورکوئری، داده های جدید رو بارگذاری کنیم. در واقع پاورکوئری همه مراحلی که برای پالایش داده انجام میدیم رو ذخیره میکنه و از اون به بعد هر داده ای جایگزین داده های اولیه بکنیم، با یک کلیک، همه این مراحل روی داده های جدید اجرا میشن (بدون نیاز به اینکه مجدد مراحل رو طی کنیم)

مرحله ۱: بارگذاری اطلاعات در پاورکوئری:

از تب Data و از زیر مجموعه Get & Transform روی زبانه New Query کلیک می کنیم و از زیر مجموعه From File گزینه From Workbook رو انتخاب می کنیم (مطابق شکل ۱). فایل اکسل مورد نظر رو از پنجره Import Data مطابق شکل زیر انتخاب می کنیم و روی دکمه Import کلیک می کنیم.

بارگذاری فایل اکسل در پاور کوئری

شکل ۱- بارگذاری فایل اکسل در پاور کوئری

بعد از زدن دکمه Import صفحه ای مطابق شکل ۲ نمایش داده میشه با کلیک روی data در سمت چپ صفحه می تونیم یه پیش نمایش از داده ها رو در سمت راست صفحه ببینیم.

پنجره Navigator

شکل ۲- پنجره Navigator

با دو با کلیک کردن روی data پنجره Power Query Editor مطابق شکل ۳ باز میشه. همانطور که در شکل ۳ مشخص هست محیط Power Query خیلی شبیه محیط اکسل هست.

پنجره  Power Query Editor

شکل ۳- پنجره Power Query Editor

مرحله ۲: پالایش اطلاعات

بعد از آپلود شدن اطلاعات در پاور کوئری، برای پالایش اطلاعات کارکنان در Power Query Editor اقدامات زیر رو انجام میدیم:

  1. حذف سطر های اضافی: همانطور که در شکل ۳ مشخص شده سطر بالایی جدول اضافی هست و باید حذف بشه. برای اینکار از تب Home در پنجره Power Query Editor و از زیر مجموعه Reduce Rows گزینه Remove Rows و سپس گزینه Remove Top Rows رو انتخاب می کنیم. مطابق شکل ۴ در پنجره نمایش داده شده تعداد سطرهایی که می خواهیم از بالای جدول حذف بشن رو وارد می کنیم و دکمه Ok رو می زنیم و مشاهده می کنیم که مطابق شکل ۴ سطر اضافی از بالای جدول حذف شد.

حذف سطر اضافی از بالای جدول

شکل ۴- حذف سطر اضافی از بالای جدول

  1. تعیین سر ستون برای جدول اطلاعات: با توجه به حذف کردن سطر اضافی در بخش قبل حالا باید برای جدول سرستون (Header) انتخاب کنیم که در واقع همون سطر اول در شکل ۴ هست. برای اینکه سطر اول رو به سر ستون تبدیل کنیم مطابق شکل ۵ از تب Home و از زیر مجموعه Transform گزینه Use First Row as Headers رو انتخاب می کنیم.

تعیین ردیف اول به عنوان سر ستون

شکل ۵- تعیین ردیف اول به عنوان سر ستون

  1. جایگزینی مقادیر: می خواهیم مقادیر Null در ستون های Gender و Department رو با جای خالی جایگزین کنیم. برای اینکار روی هر کدوم از ستون های Gender و Department کلیک می کنیم و از تب Home و زیر مجموعه Transform روی گزینه Replace Values کلیک می کنیم مطابق شکل ۶ قسمت های مشخص شده رو تکمیل می کنیم و در قسمت Replace With چیزی نمی نویسیم و در نهایت دکمه OK رو می زنیم.

جایگزینی مقادیر NULL با جای خالی

شکل ۶- جایگزینی مقادیر NULL با جای خالی

  1. حذف پرسنل بدون حقوق: برای اینکار مطابق شکل ۷ روی زبانه ستون Salary کلیک می کنیم و Select All رو انتخاب می کنیم و بعد تیک مربوط به Null رو بر می داریم و با فیلتر کردن به راحتی پرسنلی که حقوق ندارند نمایش داده نمیشن.

انتخاب پرسنلی که حقوق دارند

شکل ۷- انتخاب پرسنلی که حقوق دارند

مرحله ۳: جدا کردن نام کشور از آدرس و نمایش آن در یک ستون جدید: برای اینکار اول ستون Location رو انتخاب می کنیم و بعد مطابق شکل ۸ از تب Add Column گزینه Extract و بعد Text After Delimiter رو انتخاب می کنیم. مطابق شکل ۸ اطلاعات رو در پنجره نمایش داده شده وارد می کنیم و دکمه OK رو می زنیم. مشاهده می کنیم که یک ستون به جدول اضافه شده که در نام کشور ها در آن نمایش داده شده.

جدا کردن نام کشور و نمایش آن در یک ستون جدید

شکل ۸- جدا کردن نام کشور و نمایش آن در یک ستون جدید

برای نمایش سال شروع همکاری هر کارمند در یک ستون جداگانه هم دقیقا به همین روش عمل می کنیم فقط در فیلد Delimiter علامت “/” رو قرار میدیم.

نکته: در سمت راست پنجره Power Query Editor قسمتی به نام Query Settings وجود داره. مطابق شکل زیر در این قسمت می تونیم تمامی مراحلی که برای ویرایش اطلاعات انجام دادیم رو به صورت گام به گام ببینیم. این قسمت می تونه برای مواقعی که فایلی داریم که می خواهیم بدونیم چه تغییراتی روی آن اعمال شده کاربردی هست. در واقع دقیقا همه مراحلی که روی فایل اعمال شده اینجا نمایش داده میشه.

 

پنجره Query Settings در Power Query Editor

شکل ۹- پنجره Query Settings در Power Query Editor

 مرحله ۴: انتشار داده ها برای تجزیه و تحلیل و گزارش در اکسل

بعد از اینکه همه ویرایش های لازم رو انجام دادیم، برای انتقال اطلاعات به اکسل، مطابق شکل ۹ در پنجره Power Query Editor  و در تب Home روی گزینه Close & Load کلیک می کنیم. با این کار اطلاعات ویرایش شده در قالب یک Table در اکسل نمایش داده میشه و می تونیم تحلیل های موردنظرمون رو انجام بدیم. (مثلا پیوت اجرا کنیم روی داده ها)

کاربرد Power Query - انتقال اطلاعات از Power Query به اکسل

شکل ۱۰- انتقال اطلاعات از Power Query به اکسل

نکته: هر زمان که اطلاعات جدیدی به بانک اطلاعاتی کارکنان اضافه بشه کافیه در فایل اکسل خروجی پاور کوئری (شکل ۱۰) و از تب Query روی Refresh کلیک کنیم. با اینکار اطلاعات جدید در قالبی که تعریف کردیم در Table اضافه می شن.

 

مثال ۲: ادغام کردن (Consolidate) فایل های اکسل موجود در یک فولدر

از دیگر کاربرد Power Query ادعام چند فایل در یک پوشه هست. فرض کنید فولدری داریم که اطلاعات مالی پروژه های مختلف رو در اون ذخیره می کنیم و تمامی فایل های موجود در فولدر ستون های یکسان دارند (فولدر CSV حاوی فایل های اکسل این مثال در انتهای مقاله قابل دانلود هست). می خواهیم اطلاعات تمام فایل ها رو در یک فایل نمایش بدیم و هر بار که اطلاعات یک پروژه جدید به فولدر اضافه میشه، فایل اکسل حاوی اطلاعات تمام پروژه ها هم به روز رسانی بشه. برای این منظور مراحل زیر رو طی می کنیم:

  1. از تب Data و از زیر مجموعه Get & Transform روی زبانه New Query کلیک می کنیم و از زیر مجموعه From File گزینه From Folder رو انتخاب می کنیم (مطابق شکل ۱۱). در پنجره نمایش داده شده مطابق شکل ۱۱ روی دکمه Brows کلیک می کنیم و مسیر فولدری که فایل های اکسل در اون قرار دارند رو انتخاب می کنیم.

انتخاب فولدر حاوی فایل های اکسل

شکل ۱۱-انتخاب فولدر حاوی فایل های اکسل

  1. بعد از زدن دکمه OK در مرحله قبل پنجره ای مطابق شکل ۱۲ نمایش داده می شه که در آن مشخصات فایل های موجود در فولدر نمایش داده شده. در این پنجره روی دکمه Combine و بعد Combine & Transform Data کلیک می کنیم.

لیست فایل های موجود در فولدر

شکل ۱۲- لیست فایل های موجود در فولدر

  1. بعد از انتخاب Combine & Transform Data پنجره Combine Files مطابق شکل ۱۳ نمایش داده میشه. در این پنجره یک نمونه از فایل هایی که می خواهیم با هم ترکیب کنیم نمایش داده شده. روی دکمه OK کلیک می کنیم و به پنجره Power Query Editor منتقل می شیم. همانطور که در پنجره Power Query Editor نشان داده شده، اطلاعات تمام فایل ها در سه ستون نمایش داده شده. در صورتی که اطلاعات نیاز به ویرایش داره (حذف سطر، اضافه کردن ستون یا تعریف سرستون و…) ویرایش های لازم رو انجام میدیم و روی دکمه Close & Load در تب Home کلیک می کنیم. اطلاعات تمامی جداول در قالب یک Table در اکسل مطابق شکل ۱۴ نمایش داده میشه.

پنجره Combine Files

شکل ۱۳- پنجره Combine Files

کاربرد Power Query- جدول نهایی حاصل از ترکیب چند جدول

شکل ۱۴- جدول نهایی حاصل از ترکیب چند جدول

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

مثال ۳: حفظ داده های تکراری

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

جدول حاوی اطلاعات تکراری

شکل ۱۵- جدول حاوی اطلاعات تکراری

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

  1. جدول اطلاعات رو به فرمت Table تغییر میدیم. برای اینکار پس از انتخاب حدوده اطلاعات، از تب Home و از زیر مجموعه Styles و از زبانه Format as Table فرمت مورد نظر رو انتخاب می کنیم؛
  2. از تب Data و از زیر مجموعه Get & Transform مطابق شکل زیر گزینه From Table رو انتخاب می کنیم.

From Table

شکل ۱۶- From Table

  1. بعد از اینکه وارد پنجره Power Query Editor شدیم مطابق شکل زیر از تب Home و از زبانه Keep Rows گزینه Keep Duplicates رو انتخاب می کنیم.

کاربرد Power Query- انتخاب Keep Duplicates

شکل ۱۷- انتخاب Keep Duplicates

  1. دکمه Close & Load رو می زنیم و اطلاعات تکراری مطابق شکل زیر در فایل اکسل نمایش داده میشه.

نمایش داده های تکراری در اکسل

شکل ۱۸- نمایش داده های تکراری در اکسل

مثال ۴: تقسیم کردن ستون براساس تعداد کاراکترها

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

  1. جدول اطلاعات رو در قالب Table تعریف می کنیم (در مثال قبل روش تعریف Table توضیح داده شده).
  2. از تب Data و از زیر مجموعه Get & Transform مطابق شکل زیر گزینه From Table رو انتخاب می کنیم؛
  3. بعد از اینکه وارد پنجره Power Query Editor شدیم مطابق شکل زیر از تب Home و از زبانه Split Columns گزینه By Number of Characters رو انتخاب می کنیم و در پنجره بعدی تعداد کاراکتر هایی که می خواهیم در ستون ها نمایش داده بشن رو وارد می کنیم.

کاربرد Power Query- استفاده از قابلیت Split Columns

شکل ۱۹- استفاده از قابلیت Split Columns

  1. روی دکمه Close & Load می زنیم و فایل اکسل مطابق شکل زیر نمایش داده میشه. همانطور که در شکل ۲۰ مشخص شده ستون شماره کارت ۱۶ رقمی به ۴ چهار ستون تقسیم شده که در هر ستون هم حاوی ۴ هست.

کاربرد Power Query- نمایش شماره کارت 16 رقمی در چهار ستون

شکل ۲۰- نمایش شماره کارت ۱۶ رقمی در چهار ستون

مثال ۵- کاربرد Power Query :تکمیل مقادیر خالی در جدول

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

جدول حاوی اطلاعات فروش ماهانه

شکل ۲۱- جدول حاوی اطلاعات فروش ماهانه

برای انجام اینکار مراحل زیر رو طی می کنیم:

  1. جدول اطلاعات رو در قالب Table تعریف می کنیم؛
  2. از تب Data و از زیر مجموعه Get & Transform مطابق شکل زیر گزینه From Table رو انتخاب می کنیم؛
  3. بعد از اینکه وارد پنجره Power Query Editor شدیم مطابق شکل زیر، روی زبانه کنار عنوان Sales Month کلیک می کنیم و اطلاعات ستون ماه رو به صورت صعودی (Ascending) مرتب می کنیم؛

مرتب کردی صعودی اطلاعات ستون Sales Month

شکل ۲۲- مرتب کردی صعودی اطلاعات ستون Sales Month

در مرحله بعد اطلاعات ستون Sales QRT رو به صورت نزولی (Descending) مرتب می کنیم؛

مرتب کردی نزولی اطلاعات ستون Sales QRT

شکل ۲۳- مرتب کردی نزولی اطلاعات ستون Sales QRT

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

وضعیت ستون های Sales QRT و Sales Month بعد از مرتب کردن

شکل ۲۴- وضعیت ستون های Sales QRT و Sales Month بعد از مرتب کردن

  1. حالا ستون Sales QRT رو انتخاب می کنیم و مطابق شکل زیر از تب Transform و از زبانه Fill گزینه Down را انتخاب می کنیم. مشاهده می کنیم که تمام قسمت های خالی ستون Sales QRT تکمیل شدن.

کاربرد Power Query- انتخاب Fill Down از تب Transform

شکل ۲۵- انتخاب Fill Down از تب Transform

  1. روی دکمه Close & Load کلیک می کنیم و جدول اطلاعات فروش در شیت جدید مطابق شکل زیر نمایش داده می شه.

جدول نهایی بعد از تکمیل اطلاعات با استفاده از Fill Down

شکل ۲۶- جدول نهایی بعد از تکمیل اطلاعات با استفاده از Fill Down

دراین مقاله  چند کاربرد Power Query رو با مثال های مختلف بررسی کردیم. میتونید با دانلود دیتابیس های مورد استفاده در این مقاله، خودتون مراحل رو یک به یک اعمال کنید تا بهتر یاد بگیرید.

لازمه بدونید که کاربردهای Power Query فقط به این موارد خلاصه نمیشه. پیشنهاد می کنیم برای آشنایی با کاربردهای دیگه پاور کوئری حتما مقاله های زیر رو مطالعه کنید:

  1. ابزار Unpivot ابزاری کاربردی در Power Query
  2. ترکیب جداول در اکسل با استفاده از Power Query
  3. انتقال داده از وبسایت به اکسل
  4. لیست فایل های یک پوشه در اکسل

دانلود فایل ها و داده های مثال های استفاده شده در معرفی چند کاربرد Power Query

برای دانلود داده های استفاده شده در معرفی چند کاربرد Power Query روی دکمه زیر کلیک کنید:

126

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

ارسال دیدگاه

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

توسط
تومان