ورود اطلاعات از صفحه وب به اکسل + مثال

ورود اطلاعات از صفحه وب به اکسل + مثال: در این مقاله قصد داریم به طور عملی به این موضوع بپردازیم که چگونه می توان داده هایی را از صفحه وب به اکسل، درون ریزی (Import) کرد. تکنیک ها و راه حل های زیادی برای این کار مورد استفاده قرار می گیرد و بسته به تغییراتی که در داده ها ایجاد می شود و همچنین پویا بودن آنها، برخی از روشها بسیار کاربردی تر هستند.

ما در مثالی که در این مقاله عنوان خواهیم کرد، می خواهیم از ابزار Get & Transform Data که در منوی Data قرار گرفته، استفاده کنیم. این ابزارها قبلاً به عنوان افزونه های Power Query معروف بودند، اما از نسخه اکسل ۲۰۱۶ به بعد، این ابزارها به یک ویژگی استاندارد در اکسل تبدیل شدند. از آنجایی که اکثر کاربران به این عنوان عادت کرده اند، ما هنوز هم از این ابزارها به عنوان Power Query یاد می کنیم.

ویژگی Power Query در اکسل، یک ویژگی بسیار قدرتمند است که به ما امکان می دهد:

• به منابع مختلف داده متصل شویم، منابع داده ای مانند فایل های متنی (Text Files)، فایل های پایگاه داده (Databases)، فایل های اکسل (Excel Files)، وب سایت ها (Websites) و …
• که بتوانیم داده ها را براساس پیش نیازهای گزارشاتمان، تغییر دهیم.
• داده ها را به شکل جداول اکسل و مدل داده ذخیره کنیم و یا برای فراخوانی مجدد آنها بتوانیم به راحتی به آنها متصل شویم.

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

مثال: درون ریزی اطلاعاتِ قیمت لحظه‌ای نفت به درون اکسل

(ورود اطلاعات از صفحه وب به اکسل)

اولین گام، اتصال به منبع داده است. در این مثال، ما به اطلاعات جدول ثبت شده در وبسایت زیر متصل می شویم:

https://www.eia.gov/petroleum

در این وبسایت، برای راحتی کار ما تنها یک جدول داریم، اما شما می توانید به وبسایت هایی متصل شوید که چندین جدول داشته باشند.

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

پیشترها برای اینکه بتوانیم اطلاعات این جدول را در اکسل داشته باشیم، می بایست می آمدیم اطلاعات این جدول را کپی (Copy) می گرفتیم و داده ها را در محیط اکسل می چسباندیم (Paste).

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

حتی اگه بتوانید با موفقیت، اطلاعات را به محیط اکسل درون ریزی کنید، این اطلاعات و داده ها، هیچ لینکی به صفحه وبسایتِ هدف ندارد. وقتی که داده های صفحه وب تغییر کند، باید مجدداً اطلاعات جدید را از صفحه وب به محیط اکسل کپی کنید.

۱. در گام اول، آدرس وبسایت (URL) موردنظر خودتان را کپی کنید (فرض می کنیم که وبسایت را در صفحه Browser باز کرده اید. در غیر این صورت می توانید آدرس وبسایت را در محل مورد نظر در گام شماره ۲، تایپ کنید).

نکته: دقت کنید آدرسی را کپی کنید که می خواهید جدول مدنظرتان را از آن قرض بگیرید.

۲. به آدرس زیر در اکسل مراجعه کنید:

Data (tab) -> Get & Transform (group) -> From Web

۳. در پنجره From Web آدرس URL کپی شده را بچسبانید و OK کنید:

در پنجره Navigator می توانیم تمام اطلاعات و جزئیات وبسایت را در قسمت سمت چپ ببینیم:

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

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

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

اگر داده ها به تغییرات دیگری نیاز ندارند، می توانید با کلیک بر روی Load/Load To اطلاعات را به محیط اکسل ارسال کنید.

۴. در پنجره Navigator بر روی فلش رو به پایینِ کنارِ دکمه Load کلیک و … Load To را انتخاب کنید.

۵. در پنجره Import Data، گزینه “Existing worksheet” و سلولی را که دوست دارید اطلاعات آنجا چسبانده شوند را انتخاب یا تایپ کنید (مثلاً سلول A1 در کاربرگ Sheet1) و در نهایت OK کنید.

نتیجۀ کار، جدولی است که به یک query متصل است. در پنل Queries & Connections (که در سمت راست صفحه قابل مشاهده است)، می توان تمام query های موجودِ این فایل را دید.

اگر موس خود را بر روی یکی از query ها ببرید (Hover)، پنجره اطلاعاتی باز می شود که به شما این اطلاعات را می دهد:

• نمایشی از داده ها
• تعداد ستون های درون ریزی شده
• آخرین تاریخ/زمان به روزرسانی داده ها
• اینکه چگونه داده ها بارگزاری شده اند یا به فایل اکسل متصل شده اند.
• محل (Location) منابع داده

اگرچه داده ها صحیح هستند، اما برخی مشکلات ساختاری در نتایج وجود دارد که باعث بوجود آمدن مشکلاتی در تحلیل های آتی می شوند.

سلول های خالی در ستون Product باعث بوجود آمدن مشکلاتی هنگام مرتب کردن (Sort)، فیلتر کردن، نمودارسازی یا ساخت جداول Pivot خواهد شد.

پس لازم است قبل از هر کاری، برخی تنظیمات را روی این داده ها انجام دهیم:

۶. روی query مورد نظر کلیک راست و گزینه Edit را انتخاب کنید. 

می خواهیم محصولات ذکر شده را در سلولهای خالی و پایین ستون Product پر کنیم.

۷. در صفحه ویرایش Power Query، ستون Product را انتخاب و بروی گزینه Down در آدرس Transform (tab) –> Any Column (group) –> Fill  –> Down کلیک کنید.

هیچ اتفاقی رخ نداد. درسته؟

دلیل عدم تکرار نام محصولات در سلول های خالی این است که Power Query، آن سلول ها را به عنوان سلول خالی درنظر نگرفته است. ممکن است که چیزهایی از صفحه وبسایت در سلول مدنظر وجود دارد که ما نمی توانیم ببینیم.

ما تمام سلول های با مقادیر غیرواقعی (Fake Values) را با مقادیر تهی (Null Values) جایگزین خواهیم کرد. این کار در نهایت باعث می شود که Fill Down همانگونه که انتظار می رود، درست کار کند.

۸. از پنل Query Settings در سمت راست صفحه، گزینه Changed Type را انتخاب کنید.

۹. ستون Product را انتخاب و از آدرس Transform (tab) -> Any Column (group) -> Replace Values گزینه Replace Values را کلیک می کنیم.

۱۰. با زدن دکمه Insert به Power Query بگویید که شما می خواهید این مرحله را حتماً انجام دهید.

۱۱. در پنجره Replace Values، فیلد “Value To Find” را خالی رها کنید و در فیلد “Replace With” کلمه null را بنویسید و در پایان دکمه OK را بزنید.

حال اگر گزینه “Filled Down” ایجاد شده در مراحل قبل را از قسمت Query Settings انتخاب کنید، خواهید دید که نتایج query شما به روز خواهد شد.

۱۲. نام query را به دلخواه تغییر دهید.

۱۳. به منوی Home بروید و بر روی گزینه Close & Load کلیک کنید.

داده های ما به روز رسانی خواهند شد.

برای به روزرسانی داده ها نیز می توانیم از طریق آدرس زیر گزینه Refresh را فعال کنیم تا داده های ما در هر لحظه به روز شوند.

Data (tab) -> Queries & Connections (group) -> Refresh All

گزینه های کنترلی Query ها

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

Data (tab) -> Queries & Connections (group) -> Refresh All -> Connection Properties

برخی از مهمترین تنظیمات این پنجره شامل این موارد است:

• به روز رسانی داده ها هر N دقیقه
• به روزرسانی داده ها به صورت خودکار، هنگامی که فایل اکسل را باز می کنید.
• به روز رسانی اتصال برای همه query ها.