بلاگ

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

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

ورود اطلاعات از صفحه وب به اکسل: در این مقاله قصد داریم به طور عملی به این موضوع بپردازیم که چگونه می توان داده هایی را از صفحه وب به اکسل، درون ریزی (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 از منوی Data در اکسل

 

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

 

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

ورود داده ها از وبسایت به اکسل

 

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

 

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

ترسیم یک نمودار میله ای شناور (Up/Down bars) در اکسل + مثال

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

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

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

 

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

 

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

بارگزاری اطلاعات از website به اکسل

 

اگر موس خود را بر روی یکی از 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

 

گزینه های کنترلی Query ها برای ورود اطلاعات از وب به اکسل

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

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

 

ورود اطلاعات از وبسایت به اکسل از طریق Power Query

اشتراک گذاری:
راهنمای دانلود فایل های مقاله:
  • لینک دانلود به صورت یک فایل 126 کیلوبایتی در قالب یک فایل اکسل ارائه شده است.
  • در صورتی که به هر دلیل موفق به دانلود فایل مورد نظر نشدید، سعی کنید از یک مرورگر دیگر مانند فایرفاکس هم کمک بگیرید. در نهایت در صورتی که مشکل حل نشد با ما تماس بگیرید.
دانلود فایل

برای مشاهده لینک دانلود لطفا وارد حساب کاربری خود شوید!

پسورد فایل : ندارد

دوره های آموزشی مرتبط

6 دیدگاه

به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.

  • سلام و روز بخیر

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

    ممنونم

    • سلام به شما دوست عزیز. سپاس از اینکه با ما همراه هستید.
      این امکان وجود نداره که ما به صورت عادی بتونیم داده های Google Sheet رو وارد اکسل کنیم. برای اینکار شما داده هاتون رو توی Google Sheet تنظیم کنید و تمام کاراتون رو انجام بدید. بعد از اون برای انتقال به اکسل، به منوی File برید و گزینه Download و سپس آیتم Microsoft Excel رو انتخاب کنید. به این ترتیب، فایل شما تمام و کمال به اکسل انتقال پیدا می کند.
      موفق باشید.

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

دیدگاهتان را بنویسید

error: محتوای این سایت محافظت شده است