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

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

اما قبل از مطالعه این مطلب پیشنهاد میکنیم ویدئوی آموزشی زیر را با موضوع آشنایی با پنجره پاور کوئری و نحوه لود کردن داده مشاهده کنید. 

گام اول: اتصال به یک صفحه و استخراج داده از وب سایت ویکی پدیا

اکسل 2016: تب data را باز کنید، سپس New Query > From Other Sources >From Web را انتخاب کنید. اگر دکمه new query قابل مشاهده نیست،  Data > From Web را انتخاب کنید.

اکسل 2010-2013: تب power query را باز کنید، سپس  From Web را انتخاب کنید. اگر تب power query قابل مشاهده نیست، میتوانید افزونه آن را دانلود و نصب کنید. اگر با نصب پاور کوئری آشنایی ندارید می توانید این مطلب را بخوانید: دستورالعمل کامل نصب power query (پاور کوئری) در اکسل

1.  در دیالوگ باکس from web، آدرس دلخواه را وارد کنید. در اینجا آدرس یکی از صفحات ویکی پدیا را استفاده میکنیم.                                ( http://en.wikipedia.org/wiki/UEFA_European_Championship)

     2. Ok را کلیک کنید.

پس از اتصال به صفحه مورد نظر، لیستی از جدول های موجود در آدرس مشخص شده را در پنجره navigator مشاهده می کنید. شما میتوانید بر روی هرکدام از جدول ها کلیک کنید تا یک تصویر خلاصه از آن را در سمت راست پنجره مشاهده کنید.

3.  بر روی جدول [Results[edit راست کلیک کنید و edit را انتخاب کنید. Query editor به همراه داده های تورنومنت باز میشود.

 

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

حال که جدول مورد نظر در Query editor باز شده است و داده خود را از وب استخراج کرده اید، شما میتوانید داده ها را پاکسازی و شکل دهی کنید تا متناسب با نیازهای شما گردد. در این مرحله، با حذف تمام ستون ها به جز year و final winner جدول را تغییر شکل دهید.

1. در قسمت Query Preview، با استفاده از Ctrl+Click ستون های year و final winner را انتخاب کنید.
2. Remove Columns > Remove Other Columns را کلیک کنید.

گام سوم: پاکسازی داده ها

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

1. ستون year را انتخاب کنید.
2. در query editor گزینه Replace Valuesرا انتخاب کنید.
3. در دیالوگ باکس replace value، در باکس Value to Find مقدار “Details” را تایپ کنید و باکس Replace With را خالی بگذارید.
4. Ok را کلیک کنید.

گام چهارم: مقادیر یک ستون از داده های استخراج شده را فیلتر کنید.

حال با فیلتر کردن ستون year فقط ردیف هایی را نمایش میدهیم که شامل “year” نمیشود.

1. فلش مربوط به لیست کشویی ستون year را کلیک کنید.
2. در لیست filter گزینه year را غیر فعال کنید.

گام پنجم: یک query را نامگذاری کنید.

در اینجا می بایست کوئری را که ایجاد کرده اید نامگذاری کنید.

در پنجره query setting، در باکس name، عبارت Euro Cup Winners را تایپ کنید. اگر لینک all properties را کلیک کنید میتوانید توضیحاتی در مورد query در باکس Description اضافه کنید.

گام ششم: کوئری را در یک ورک شیت بارگذاری کنید.

در انتها، میتوانید کوئری Euro Cup Winners  را به یک ورک شیت بارگذاری کنید.

1. در گوشه بالا سمت چپ، گزینه close&load را کلیک کنید.

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

گام هفتم: وقایع پشت صحنه را کشف کنید.

1. در هر جایی از داده ها کلیک کنید و در پنجره view گزینه query editor را کلیک کنید.
2. در سمت راست، لیست applied steps را مشاهده کنید. همانطور که فعالیت های مربوط به Query را انجام میداده اید، query steps ایجاد شده است. هر گام از کوئری یک فرمول نوشته شده بر اساس زبان M دارد.

شما میتوانید با انتخاب  Advanced Editor در ریبون power query، کد های M  را به صورت کامل مشاهده کنید.

نکته:

query editor فقط زمانی ظاهر میشود که یک کوئری را بارگذاری یا اصلاح کنید یا یک کوئری جدید ایجاد کنید. ویدئو زیر نشان میدهد query editor زمانی ظاهر میشود که یک کوئری در ورک بوک اکسل را اصلاح میکنیم. برای مشاهده query editor بدون ایجاد یا اصلاح یک کوئری، در بخش  Get External Dataدر تب power query، دستور  From Other Sources > Blank Query را انتخاب کنید. ویدئوی زیر یک راه برای ظاهر شدن این ابزار را نشان میدهد.

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

Text.Middle([name],7,1000)