4.9/5 - (18 امتیاز)
در این مطلب نحوه استفاده از  ابزار پاور کوئری (power query) اکسل برای تغییر فرمت داده های خود و تبدیل آن به فرمتی که قابل استفاده در pivot table، گزارش ها و غیره باشد را ارائه خواهیم کرد. در این مطلب قصد داریم فقط شما را با ابزار پاورکوئری آشنا کنیم و در مطالب بعدی به آموزش کامل پاورکوئری در اکسل خواهیم پرداخت.

 

در این مطلب، یک مقدمه کوتاه در مورد ابزار پاور کوئری (Power Query) ارائه خواهیم کرد. همچنین دلیل اینکه این ابزار بسیار مورد علاقه من قرار گرفته و نحوه استفاده از آن برای اتومات کردن فرایندها  و صرفه جویی در زمان را فراخواهید گرفت.

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

ماشین داده ابزار  پاور کوئری (Power Query)

یک برنامه تلویزیونی در مورد نحوه ساخت اشیاء را مشاهده میکردم، در این برنامه نحوه کار یک ماشین تبدیل کننده در یک کارخانه کلوچه پزی را نشان میداد.

این موضوع چه ربطی به پاور کوئری اکسل دارد؟

اساس ماشین تبدیل کننده به صورت زیر است:

  • مواد اولیه را به آن اضافه میکنید.
  • تنظیمات آن را تغییر میدهید
  • و این ماشین به صورت شعبده آمیزی کلوچه ها (کلوچه، دونات، بیسکویت) را تحویل میدهد.

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

ابزار پاور کوئری (power query) نیز به همین شیوه عمل میکند!

  1. ابتدا منابع داده خود را اضافه میکنید (جدول های اکسل، فایل های csv، جدول های پایگاه داده، صفحات وب و …)
  2. دکمه موجود در پنجره پاور کوئری (Power Query) را فشار دهید تا داده های شما انتقال یابد.
  3. این داده ها را وارد ورک شیت یا مدل خود کنید و حال آماده به قرار گیری در پیوت تیبل (pivot (table و گزارش هاست.

ابزار پاور کوئری (Power Query) شبیه به یک ماشین است زیرا، زمانی که تنظیمات پاور کوئری (Power Query) خود را مشخص کردید، فرایند میتواند هر تعداد بار تکرار شود.

اگر از ماکروها برای تبدیل داده های خود استفاده میکنید، در نظر داشته باشد که استفاده از پاور کوئری (Power Query) میتواند بعنوان جایگزینی بسیار آسانتر برای کد های vba مطرح شود که نیاز به هیچ گونه کد نویسی نیز ندارد.

چه کسانی و در کجا از پاور کوئری و پاور پیوت استفاده می کنند

شاید برای شما هم سوال باشد که چه کسانی و در کجا از پاور کوئری و پاور پیوت استفاده می کنند؟ پاور کوئری و پاور پیوت چه قابلیت هایی دارند و در کجا ها به ما کمک می کنند؟! در این فیلم ویدئویی ببینید.

تسهیل انجام کارهای مربوط به داده

آیا شما نیز با داده هایی کار میکنید که از یک سیستم ثبت اطلاعات بدست می آید؟ این سیستم میتواند یک سیستم دفتر مرکزی، حسابداری، ERP، سیستم مدیریت ارتباط با مشتری، Salesforce، یا هر سیستم گزارشگری دیگری باشد که شامل داده میشود.

اگر با این سیستم ها کار میکنید، احتمالاً وقت بسیار زیادی از کار خود را صرف تغییر شکل و یا انتقال داده ها برای ایجاد pivot table ها و گزارش ها یا نمودارها میکنید.

این تبدیل های داده، میتواند شامل کارهای زیر باشد:

  • حذف ستون ها، ردیفها یا سلول های خالی
  • تغییر نوع داده – متن، عدد، تاریخ
  • ترکیب یا ادغام ستون ها
  • دسته بندی و فیلتر ستون ها
  • اضافه کردن ستون های محاسبه شده
  • تجمیع و خلاصه سازی داده ها
  • جستجو و جایگزین کردن متن
  • Unpivot کردن داده ها برای استفاده در pivot table

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

خوشبختانه، pivot table دکمه هایی دارد که این کارها را به صورت اتوماتیک انجام میدهد.

بررسی منو های داخلی در ابزار پاور کوئری (Power Query)

زمانی که این ابزار را نصب کردید میتوانید پنجره پاور کوئری (Power Query) را در ریبون اکسل خود مشاهده کنید. یا اگر از ورژن 2016 استفاده می کنید در ریبون Data به گروه Get & Transform دسترسی دارید.

با استفاده از دکمه های موجود در پنجره پاور کوئری (Power Query) داده های منبع خود را بدست می آورید. دوباره، داده های شما میتواند در فایل های اکسل، فایل های csv، Access، پایگاه داده های SQL، SharePiont، Salesforce.com، سیستم های پویای ارتباط با مشتری ها، Facebook، ویکی پدیا، و بسایت ها و غیره دسته بندی شود.

برای آشنایی با نحوه استراج داده ها از وب سایت ها این مطلب را مطالعه کنید: فراخوانی داده از وبسایت با استفاده از power query (پاور کوئری)

زمانی که مشخص کردید داده های شما ازکجا بدست می آید، میتوانید با استفاده از پنجره پاور کوئری (Power Query) تغییرات مورد نظر را بر روی داده های خود انجام دهید.

دکمه های موجود در پنجره ویرایشگر پاور کوئری (Power Query) به شما امکان میدهد داده های خود را تبدیل و تغییر دهید.

همانطور که دکمه های مورد نظر خود را در این پنجره جستجو میکنید، در مورد کارهایی فکر کنید که به صورت تکراری انجام میدادید. هر بار که یک دکمه را فشار میدهید، عمل شما (گام های شما) ثبت میشود و زمانی که داده های جدیدی دارید میتوانید این کارهای را بسیار سریعتر انجام دهید.

بعد از اینکه گام های مورد نظر را انجام دادید، میتوانید با زدن دکمه close & load داده های بدست آمده را وارد جدولی در ورک شیت خود کنید.

همچنین میتوانید Query های فعلی خود را اصلاح کنید یا جدول خروجی خود را با استفاده از داده های به روز رسانی شده تازه سازی کنید.

در اینجا تعدادی مثال از اینکه پاور کوئری (Power Query) چه کاری میتواند با داده های شما انجام دهد آورده ایم.

خارج کردن داده ها از حالت پیوت (Unpivot Data) برای استفاده در پیوت تیبل

ویژگی مورد علاقه من از پاور کوئری (Power Query) توانایی Unpivote Data است.  این یک تکنیک برای دریافت داده های خود از منبع Pivote Table است. این کار نیز به عنوان نرمال کردن داده های شما به منظور دریافت آن در فرمت جدولی به کار می رود.

داده ها ممکن است هنگام وارد شدن از منبع داده یا سیستم گزارش دهی به  شکل زیر باشند.

و شما میخواهی نتیجه نهایی شبیه شکل زیر باشد.

پاور کوئری (Power Query) میتواند این کار را با کلیک چند دکمه انجام دهد؛ و  شما میتوانید داده های خود را برای استفاده در یک Pivot table آماده کنید.

الحاق (اتصال) جداول با ابزار پاور کوئری (Power Query)

ویژگی Append (الحاق) از پاور کوئری (Power Query) به شما امکان می دهد تا چندین جدول را ترکیب کنید تا یک جدول بزرگ ایجاد کنید.پاور کوئری (Power Query) میتواند این کار را با چندین جدول در یک فایل انجام دهد یا می تواند داده ها را از یک دسته از فایل ها/ منابع مختلف فراخوانی کند.

مثلاً شما یک پوشه دارید که شامل فایل های CSV یا اکسل با داده های گزارش شده برای هر ماه است. تمام این فایل ها را در ماشین پاور کوئری (Power Query) بریزید و یک جدول خوب را که میتوانید آن را برای ایجاد Pivot table و نمودارها استفاده کنید؛ جدا کنید.

اگر داده ها در آن گزارش ها نیز باید تغییر داده شوند (حذف ردیف ها ، تقسیم ستون ها، ناحیه یابی و غیره) ،پاور کوئری (Power Query) اکسل میتواند آن را در یک فرآیند مشابه اداره کند.

پس از تنظیم، تمام کاری که شما باید انجام دهید این است که هر ماه یکبار دکمه Refresh را بزنید تا یک فایل جدید به پوشه اضافه شود و ردیف ها به جدول خروجی اضافه شود.

چقدر عالی!!!!!!

ترکیب جداول – جایگزینی برای تابع vlookup

پاور کوئری (Power Query) اکسل از این قابلیت برخوردار است که جدول ها را با هم ترکیب کرده یا متصل کند. این ویژگی میتواند بعنوان جایگزینی برای توابع vlookup و index/match قرار گیرد

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

استفاده از فرمول vlookup بسیار عالی است ولی استفاده از این تابع ممکن است به معنی اضافه شدن صدها فرمول به ورک شیت شما باشد. که این موضوع باعث بالا رفتن سایز فایل در زمان محاسبات شما خواهد شد.

پاور کوئری اکسل (Power Query) انجام این کارها را بسیار سریع میکند و میتواند با انجام چند کلیک جدول ها را با هم ترکیب کند.

طراحی توابع

پاور کوئری اکسل (Power Query) به صورتی طراحی شده است که شما نیاز به نوشتن کد برای استفاده از آن ندارید. استفاده از آن بسیار آسان است زیرا شما فقط باید دکمه ها را فشار دهید و به همان صورتی که در دیگر محدوده های اکسل استفاده می کرده اید، فیلتر های مورد نظر خود را انجام دهید.

با این حال، از پاور کوئری اکسل (Power Query) میتوان برای ساخت توابع نیز استفاده کرد. این ویژگی پتانسیل نامحدودی را به شما میدهد که میتوانید به هر صورت ممکن داده های خود را تغییر دهید.

این کار بر اساس زبان M صورت میگیرد، نوشتن تابع در این زبان  بسیار شبیه به نوشتن یک تابع در اکسل است. این ویژگی آن را بسیار کاربر پسند میکند و یادگیری کد ها را برای کاربران بسیار آسانتر می نماید.

اگر می خواهید با زبان فرمول نویسی m بیشتر آشنا شوید این مطلب را مطالعه کنید: مقدمه ای در مورد زبان برنامه نویسی M

این زبان جدید و مجموعه توابع به این معنی است که مطالب بسیاری برای یادگیری وجود دارد، ولی من سرگرم کننده ترین و چالشی ترین قسمت را انتخاب کرده ام. به علاوه اینکه کارفرمایان آینده به دنبال کارکنانی هستند که دارای مهارت استفاده از پاور کوئری اکسل (Power Query) باشند.

ابزار پاور کوئری اکسل (Power Query) گام های شما را ثبت و فرایند ها را اتومات میکند.

پاور کوئری (Power Query) نه تنها انجام این گام ها را آسانتر میکند بلکه گام های شما را نیز ثبت میکند و به همین خاطر نیازی نیست که یک کار را چندین بار تکرار کنید.  به همین دلیل اگر یک کار را به صورت روزانه، هفتگی یا ماهانه انجام میدهید، این کار وقت شما را هدر نمی دهد .

این ابزار در کنترل خطاها نیز بسیار خوب عمل میکند. اگر ساختار داده های شما تغییر کند، پاور کوئری اکسل (Power Query) برای شما مشخص میکند که کدام مرحله تغییر یافته و آن را اصلاح میکند. این موضوع نگهداری فایل را آسان میکند و نیازی نیست شما در صورت ایجاد تغییر در فایل آن را به صورت کامل دوباره سازی کنید.

شما میتوانید با استفاده از پاور کوئری (Power Query) داده های خود را برای استفاده در pivot table، نمودارها و گزارش های داشبورد آماده سازی کنید. این، گامی مهم در فرایند خلاصه سازی و تحلیل داده است.

ماشین پاور کوئری (Power Query) و power bi

این ابزار نمیتواند دقیقا کلوچه تولید کند ولی پاور کوئری (Power Query) بسیار قدرمند است. این ابزار در زمان تغییر داده های وقت زیادی را برای شما ذخیره میکند.

پاور کوئری (Power Query) فقط یکی از اجزای Power BI (هوش تجاری)، یکی از محصولات شرکت مایکروسافت است.

اگر به موضوع ساخت کلوچه در کارخانه برگردیم، پاور کوئری (Power Query) مثل جزء اول خط تولید است. زمانی که کلوچه ها شکل گرفتند، می بایست پخت شوند (Pivot Tables, PowerPivot) و بسته بندی شوند و برای ارائه به مشتری آماده شوند     (Power View, Power Map, Charts, Dashboards, …)

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

[/vc_column_text][/vc_column][/vc_row]

فیلم و دوره رایگان آموزش پاور کوئری در اکسل و پاور بی آی

مشترک شدن
Notify of
guest

6 نظرات
نظردهی درون متنی
مشاهده همه نظرات
S.M.J

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

امیر دایی

خواهش میکنم. موفق باشید.

محمد

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

امیر دایی

سلام
روی جدول کلیک کنید. از تب design گزینه properties رو انتخاب کنید. تیک گزینه adjust column width بردارید.

احمد

سلام یه سوال دارم
من میخوام از یه سایتی اطلاعات رو با کویری بگیرم منتها پیغام خطا میده access to the resource is forbidden
این توی تب anonymous تو تب های دیگه هم یوزر پسوورد میخواد باید چیکار کنم؟

امیر دایی

سلام
اطلاعاتی که دادید خیلی ناقص هست. از طریق تلگرام @amirdaee یا ایمیل amir_daee@yahoo.com به صورت کامل مراحلی که کار رو انجام میدید و عکس اتفاقاتی که میفته رو ارسال کنید. قطعا اینطوری سریع تر به جواب میرسید.