آموزش کار با Get & Transform در اکسل

با خرید آفیس اورجینال و استفاده از ویژگی Get & Transform در اکسل ، می توانید منابع داده را جستجو کنید ، ارتباط برقرار کنید و سپس داده ها را شکل دهید (به عنوان مثال یک ستون را حذف کنید ، نوع داده را تغییر دهید یا جداول را ادغام کنید) به روش هایی که نیازهای شما را برآورده کند. هنگامی که داده های خود را شکل دادید ، می توانید یافته های خود را به اشتراک بگذارید یا از query خود برای ایجاد گزارش استفاده کنید.

مراحل بالا اغلب اینگونه اتفاق می افتد:

1. Connect : اتصال به داده های موجود در ابر (cloud) ، یک سرویس یا به صورت ذخیره شده اند.
2. Transform : داده ها را برای پاسخگویی به نیازهای خود شکل دهید. منبع اصلی بدون تغییر باقی مانده است.
3. Combine : یک مدل داده را از چندین منبع داده ایجاد کنید و یک دید منحصر به فرد به داده ها پیدا کنید.
4. Manage : پس از تکمیل درخواست شما می توانید آن را ذخیره کنید ، کپی کنید ، یا از آن برای گزارش استفاده کنید.

هر زمان که به داده متصل می شوید ، آن را تغییر شکل می دهید یا آن را با منابع داده دیگر ترکیب می کنید ، ویژگی Get & Transform به نام Query Editor هر مرحله را ضبط می کند و به شما امکان می دهد آن را مطابق میل خود اصلاح کنید. ویرایشگر Query همچنین به شما امکان می دهد مرحله را لغو کنید ، از نو انجام دهید ، ترتیب را تغییر دهید و..… با همه این ها می توانید نمای خود را از داده های متصل دقیقاً همانطور که می خواهید شکل دهید.

با Get & Transform می توانید Query هایی را ایجاد کنید که به همان سادگی و یا پیچیدگی مورد نیاز شما باشد. همانطور که مراحل را به یک Query اضافه می کنید ، Query Editor در پشت صحنه کار می کند تا مجموعه ای از دستورالعمل های گسسته را ایجاد کند که دستورات شما را اجرا می کند. این دستورالعمل ها در زبان M ایجاد شده اند. کاربرانی که از قدرت و انعطاف پذیری اسکریپت نویسی داده لذت می برند ، می توانند با استفاده از Advanced Editor ، نمایش داده های M Language را به صورت دستی ایجاد یا تغییر دهند. Query Editor و Advanced Editor بعداً در این مقاله با جزئیات بیشتری شرح داده می شوند.

می توانید query جدیدی را از مسیر Data tab > Get Data > Select a data source را انتخاب کنید. اگر دکمه دریافت داده را نمی بینید ، از سربرگ Data دکمه New Query را انتخاب کنید.

توجه: Power Query برای نسخه های قبلی اکسل به عنوان یک افزونه و همچنین در Power BI در دسترس است.

مرحله اتصال (Connect)

برای اتصال به یک منبع داده واحد ، مانند پایگاه داده Access ، می توانید از یک query استفاده کنید یا می توانید به چندین پوشه، پایگاه داده ، فیدهای OData یا وب سایت ها متصل شوید. سپس می توانید با استفاده از ترکیبات منحصر به فرد خود ، تمام آن منابع را گرد هم آورید و بینش هایی را که ممکن است ندیده باشید ، ببینید.

هنگامی که از بخش Get & Transform در برگه Data گزینه Get Data را انتخاب می کنید (یا اگر دکمه Get Data را نمی بینید New Query) ، منابع داده بسیاری را برای انتخاب خواهید دید ، از جمله فایل هایی مانند کتاب های کار اکسل یا Text / فایل های CSV ، پایگاه های داده مانند Access ، SQL Server ، Oracle و MySQL ، خدمات Azure مانند HDInsight یا Blob Storage و انواع منابع دیگر مانند وب ، لیست های SharePoint ، فایل های Hadoop ، Facebook ، Salesforce و سایر موارد.

هنگامی که به منبع داده متصل می شوید ، Get & Transform یک پنجره Navigator را نمایش می دهد که به شما امکان می دهد داده ها را از منبع آن ویرایش کنید. هنگامی که ویرایش را از پنجره Navigator انتخاب می کنید ، Get & Transform ویرایشگر Query را راه اندازی می کند که یک پنجره اختصاصی است و اتصالات داده و تحولات اعمال شده شما را تسهیل و نمایش می دهد. در بخش بعدی (Transform)، اطلاعات بیشتری در مورد Query Editor ارائه می کنیم.

مرحله تبدیل (Transform)

Get & Transform به شما امکان می دهد داده ها را از منابع داده خود به روش هایی که به شما در تحلیل آن کمک می کند ، تبدیل کنید. تبدیل داده ها به معنای تغییر دادن آن ها برای تأمین نیازهای شما است – به عنوان مثال ، می توانید یک ستون را حذف کنید ، نوع داده را تغییر دهید یا جداول را ادغام کنید – که هر یک از آن ها یک تغییر داده است. هنگامی که داده را تغییر می دهید ، در مجموع شکلی که برای تحلیل بیشتر به آن نیاز دارید را به خود می گیرد. فرآیند اعمال تحولات در یک یا چند مجموعه داده اغلب داده های شکل دهنده، نامیده می شود.

اکسل برای تسهیل و نمایش تغییر شکل داده ها، از یک ویرایشگر Query اختصاصی استفاده می کند. وقتی Data> Get Data را انتخاب کردید ، سپس منبع داده را انتخاب کنید ، مانند یک کتاب کار یا یک پایگاه داده ، پنجره Navigator ظاهر می شود تا بتوانید ازهر جدول (یا جداول) که می خواهید، در درخواست خود استفاده کنید. هنگام انتخاب جدول، پیش نمایشی از داده های آن در پنجره سمت راست پنجره Navigator نشان داده می شود.

اگر Load را انتخاب کنید ، منبع داده همانطور که هست مستقیماً وارد اکسل می شود. اگر گزینه Transform Data را انتخاب کنید ، اکسل Query Editor را راه اندازی می کند.

Query Editor تمام کارهایی را که با داده ها انجام می دهید ردیابی می کند. Query Editor هر تحول یا مرحله ای را که برای داده اعمال می کنید ، ثبت و برچسب گذاری می کند. این که آیا تغییر شکل اتصال به یک منبع داده ، از بین بردن یک ستون ، ادغام یا تغییر نوع داده است ، ویرایشگر Query هر عملیات را در بخش مراحل اعمال شده در پنجره تنظیمات Query نمایش می دهد.

تحولاتی که در اتصالات داده خود اعمال می کنید در کل Query شما را تشکیل می دهند.

مهم است که بدانید عملکردهایی که در Query Editor تعریف می کنید داده های منبع اصلی را تغییر نمی دهد. در عوض ، Excel هر مرحله را که هنگام اتصال یا تبدیل داده برمی دارید ضبط می کند و پس از پایان دادن به شکل دادن به داده ، یک عکس فوری از مجموعه داده تغییر داده شده می گیرد و آن را وارد workbook می کند.

تغییرات زیادی وجود دارد که می توانید در داده ها اعمال کنید. همچنین می توانید تحولات خود را با استفاده از زبان M با Query Editor’s Advanced Editor بنویسید. شما می توانید Advanced Editor را از زبانه های Query Editor’s Home یا View باز کنید ، جایی که می توانید مراحل M Language مرتبط با درخواست موجود را اصلاح کنید یا مورد خود را ایجاد کنید.

پس از ایجاد query، می توانید Close & Load را از تب Home انتخاب کنید و نتایج جستجو در Excel دانلود می شود و در برگه workbook جدید موجود است.

مرحله مدیریت (Manage)

هنگامی که یک workbook اکسل را که حاوی query است ذخیره می کنید ، درخواست نیز به طور خودکار ذخیره می شود. با انتخاب Queries & Connections در زبانه Data ، می توانید تمام درخواست ها را در یک workbook اکسل مشاهده کنید.

اگر روی پنجره Queries & Connections روی یک نام درخواست کلیک راست کنید ، چندین گزینه مشاهده خواهید کرد. به عنوان مثال ، می توانید یک query را کپی کنید ، که به شما امکان می دهد برخی یا همه عناصر یک query را بدون تغییرquery اصلی تغییر دهید. مانند ایجاد یک الگوی query است که می توانید آن را تغییر دهید و مجموعه داده های سفارشی ایجاد کنید – مانند یک مجموعه داده برای خرده فروشی ، دیگری برای عمده فروشی و دیگری برای موجودی ، همه آن ها براساس اتصالات داده مشابه هستند.

همچنین می توانید query ها را ادغام یا ضمیمه کنید که به شما این امکان را می دهد تا نمایش داده ها را به بلوک های قابل استفاده مجدد تبدیل کنید.

همچنین می توانید workbook خود را در Power BI منتشر کنید و گزارش های آنلاین ایجاد کنید که بتواند با گروه شما به اشتراک گذاشته شود ، به صورت خودکار تجدید و اصلاح شود. برای انتشار یک workbook  در Power BI ، مسیر File > Publish > Publish to Power BI را دنبال کنید.

اگر این مقاله برای شما مفید بود لطفاً نظرات خود را در بخش دیدگاه ها با دیگران به اشتراک بگذارید.

منبع : مایکروسافت