چند مثال کاربردی از ابزار 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

شکل ۱۶- From Table

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ثبت ایمیل جهت دانلود فایل

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

آدرس ایمیل

گروه تخصصی اکسل پدیا | همراه شما در مسیر یادگیری

بارگذاری...