ویژگی Get & Transform

آخرین به‌روزرسانی: 23 بهمن 1401

در سری مقاله های آموزش اکسل، در فصل گذشته به مسئله فروشنده دوره گرد پرداختیم، در این مقاله به بررسی ویژگی Get & Transform می‌پردازیم.

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

سؤالات پاسخ داده شده در این فصل:

• چگونه می‌توان آخرین قیمت‌های بیت‌کوین را دانلود کرده و اطمینان حاصل کرد که داده‌های وارد شده در اکسل هر روز به شکل خودکار به‌روز می‌شوند؟
• چگونه می‌توان آمار به‌روز جمعیت شهرهای ایالات متحده را دانلود کرد؟

در این فصل خواننده را به قابلیت شگفت‌انگیز Get & Transform موجود در اکسل 2019 که تحلیل گران را قادر می‌سازد تا به شکلی کارآمد داده‌ها را وارد کرده، تغییر شکل داده و دگرگون سازند. همان‌طور که در تصویر 1-40 نشان‌داده‌شده، ویژگی Get & Transform در اکسل 2016 آغاز به کارکرد و مستقیماً در تب Data در دسترس است.

همان‌طور که در تصویرهای 2-40 و 3-40 نشان‌داده‌شده، کلیک بر تب Data به شما اجازه می‌دهد تا فهرست با جزئیاتی از منابع داده که توسط قابلیت Get & Transform پشتیبانی می‌شود را مشاهده کنید.

به جهت محدودیت فضا، در اینجا کار خود را بر استفاده از قابلیت Get & Transform جهت واردکردن، تغییر شکل و دگرگون کردن داده‌های وارد شده از شبکه اینترنت متمرکز می‌کنیم.

پاسخ به سؤالات این فصل:

چگونه می‌تواند آخرین قیمت‌های بیت‌کوین را دانلود کرده و اطمینان حاصل کرد که داده‌های وارد شده در اکسل هر روز به شکل خودکار به‌روز می‌شوند؟

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

برای شروع به آدرس اینترنتی سایتی نیازمندیم که حاوی قیمت‌های روزانه بیت‌کوین باشد. خوشبختانه سایت اینترنتی Yahoo Finance این داده‌های موردنیاز ما را ارائه می‌کند. آدرس اینترنتی موردنظر: https://finance.yahoo.com/quote/BTCUSD/history/ است. اگر داده‌های سهام (فرض کنید سهام مایکروسافت) می‌خواهید، می‌توانید به‌سادگی متن بعد از /quote را با نماد سهام شرکت مربوطه تغییر دهید. مثلاً قیمت‌های سهام مایکروسافت را می‌توان از آدرس:

https://finance.yahoo.com/quote/MSFT/history/ وارد کرد. حالا در کاربرگی خالی از تب Data روی گزینه Get Data در بخش Get &Transform نوار منو کلیک کرده و گزینه Other Sources را انتخاب می‌کنیم. پس از انتخاب گزینه From Web کادر محاوره‌ای را همان‌طور که در تصویر 4-40 نشان‌داده‌شده پر می‌کنیم. اکنون شما یک پرس و جوی در شبکه اینترنت ایجاد کرده‌اید. پس از کلیک کردن روی دکمه Ok فهرستی از تمامی جداول حاوی آدرس اینترنتی موجود را مشاهده خواهید کرد. (تصویر 5-40 را ببینید) کلیک کردن روی جدول 2 به شما پیش نمایشی از هرآنچه قرار است وارد شود را نشان می‌دهد. در این مورد جدول 2 حاوی اطلاعات موردنیاز روزانه قیمت بیت‌کوین است.

چنانچه تمایل داشته باشید اکنون می‌توانید گزینه Load را انتخاب کرده تا دادها را فوراً در کاربرگ خود وارد کنید. در اینجا به‌جای واردکردن، می‌خواهیم داده‌ها را تغییر دهیم بنابراین گزینه Edit را انتخاب می‌کنیم که در نتیجه پنجره Query Editor را که در تصویر 6-40 نشان‌داده‌شده را ظاهر می‌کند.

فرض کنید تنها می‌خواهید ستون‌های Date و Adj Close را وارد کنید. برای این کار ابتدا از کلید Control برای مشخص و انتخاب کردن ستون‌هایی که می‌خواهیم حذف شوند استفاده می‌کنیم. پس از راست کلیک کردن گزینه Remove Columns را انتخاب می‌کنیم و می‌بینید که حالا تنها ستون‌های Date و Adj Close باقی‌مانده‌اند. بیایید فرض کنیم که می‌خواهیم ستون Week Of the Year وارد شود. برای رسیدن به این هدف ابتدا روی ستون Date راست کلیک کرده و گزینه Duplicate Column را انتخاب می‌کنیم. پس از راست کلیک کردن روی ستون ایجاد شده، گزینه Rename را انتخاب کرده و ستون Date Column دوم را به Week of year تغییر نام می‌دهیم.

حالا آماده‌ایم که داده‌ها را به اکسل وارد کنیم. برای انجام این کار به‌سادگی گزینه Close And Load را از تب Home انتخاب می‌کنیم. اکنون قیمت‌های صد روز اخیر بیت‌کوین را همان‌طور که در تصویر 8-40 و فایلی به نام Bitcoinquery.xlsx نشان‌داده‌شده را مشاهده می‌کنید.

اگر در هر زمانی بخواهیم جست‌وجوی خود را برای واردکردن داده‌های جدیدی به‌روز کنیم، می‌توانیم به‌راحتی نشانگر ماوس را درون داده‌های وارد شده قرار داده، راست کلیک کرده و گزینه Refresh را انتخاب کنیم. اگر بخواهیم که این پرس و جوی تمام داده‌ها را در فواصلی منظم و یا هروقت که فایل باز شد به‌روز کند، از تب Data گزینه Refresh All را از گروه Queries And Connections را انتخاب کرده و پس از انتخاب Refresh All گزینه Connection Properties را انتخاب می‌کنیم. حالا کادر محاوره‌ای Query Properties را برای کنترل به‌روز کردن این پرس و جوی می‌بینیم. همان‌طور که در تصویر 9-40 نشان‌داده‌شده، پرس و جوی را برای به‌روز کردن در هر 60 دقیقه تنظیم کرده‌ایم.

چگونه می‌توان آمار به‌روز جمعیت شهرهای ایالات متحده را دانلود کرد؟

فرض کنید می‌خواهید جمعیت 100 شهر بزرگ ایالات متحده را به اکسل وارد کنید. سایت اینترنتی http://worldpopulationreview.com/us-cities/ حاوی داده‌های موردنیاز است. با دنبال‌کردن همان روشی که با آن ارزش بیت‌کوین را دانلود کردیم در اینجا جدول Table 0 را انتخاب کرده و نتیجه نشان‌داده‌شده در تصویر 10-40 را به دست آوردیم.

فرض کنید که می‌خواهیم داده‌های شهر و ایالات در یک ستون واحد و جمعیت هر شهر در ستون جداگانه‌ای باشد. برای دستیابی به این هدف ابتدا از کلید Shift برای پاک‌کردن چهار ستون آخر استفاده می‌کنیم (با کلید Shift آنها را انتخاب کرده و حذف می‌کنیم) سپس روی گزینه Transform کلیک کرده و ستون‌های شهر و ایالت را انتخاب می‌کنیم. سپس گزینه Merge Columns را انتخاب کرده و بین عبارات شهر و ایالت یک علامت جداکننده کاما قرارمی دهیم. پس از راست کلیک کردن روی ستون ادغام شده آن را به ستون City And State تغییر نام می‌دهیم. حالا از منوی File می‌توانیم داده‌ها و پرس و جوی وب را در فایلی به نامUScityquery.xlsx بارگذاری کنیم. تصویر 11-40 نتیجه نهایی را نشان می‌دهد.

اگر می‌خواهید مراحل انجام شده در پرس و جوی اینترنتی خود را ببینید می‌توانید نشانگر ماوس را درون داده‌های وارد شده قرار داده و از بخش سمت چپ نوارابزار گزینه Query را انتخاب کنید. پس از کلیک کردن گزینه Edit در سمت چپ صفحه می‌توانید مراحل انجام شده توسط پرس و جوی اینترنت را مشاهده کنید (تصویر 12-40 را ببینید) البته کلیک بر روی گزینه Close and Load شما را دوباره به صفحه کاربرگ بازمی‌گرداند.

مسائل این فصل:

پرس و جوی اینترنتی تنظیم کنید که قیمت‌های روزانه اخیر فیس‌بوک و حجم معاملات روزانه آن را دانلود نماید.

با استفاده از سایت baruch.cuny.edu، جمعیت 100 شهر بزرگ دنیا را دانلود کنید. اطمینان حاصل کنید که کشورها و شهرها در یک ستون فهرست شده باشند.