اکسل پدیا، دانشنامه پارسی اکسل

021-47625755
info@excelpedia.net

ورود یا ثبت نام

  • دانشنامه اکسل
    • اکسل چیست؟
    • اکسل مقدماتی
    • توابع اکسل
    • نمودار ها
    • وی بی – ماکرو
  • آکادمی اکسل
    • ویدئوهای آموزشی
      • رسم نمودارهای حرفه ای
      • اکسل و شروع حرفه ای
      • اکسل نینجا (اکسل پیشرفته)
      • صفر تا صد Pivot Table
      • برنامه نویسی وی بی (VBA)
    • کتاب ها
      • کتاب های فارسی
      • کتاب های انگلیسی
  • داشبورد های مدیریتی
    • آموزش ساخت داشبورد
    • دانلود داشبورد نمونه
  • افزونه های کاربردی
  • دوره های حضوری
    • اکسل نینجا
    • برنامه نویسی وی بی (VBA)
    • Cart

      0

Power Query

  • خانه
  • بلاگ
  • Power Query
  • انتقال داده از وبسایت به اکسل

انتقال داده از وبسایت به اکسل

  • ارسال شده توسط حسنا خاکزاد
  • دسته بندی Power Query
  • تاریخ ۱۸ فروردین ۱۳۹۹
  • نظرات ۱۷ دیدگاه ها
دریافت اطلاعات از سایت
3.7 / 5 ( 3 امتیاز )

دریافت اطلاعات از سایت

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

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

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

اکسل چطور میتونه داده ها رو از سایت فراخوانی کنه؟

اکسل از ورژن های قدیمی مثل 2003 این امکان رو داشته که بتونه اطلاعات سایتی رو فراخوانی و بروزرسانی کنه. خیلی از افراد حتی به این فکر نمیکنند که بتونن داده های یک سایت رو داشته باشن چه برسه به اینکه اکسل هم بتونه این کار رو انجام بده. اما انجام این کار در اکسل خیلی راحت و کاربردی انجام میشه. برای اینکه ببینیم چطور میتونیم این کار رو انجام بدیم، مراحل زیر رو انجام میدیم:

آدرس سایت مورد نظر رو پیدا کنید

سایتی که انتخاب کردم که داده ها رو از روی اون فراخوانی کنیم سایت www.tgju.org هست. این سایت اطلاعات مربوط به قیمت طلا، ارز، شاخص بورس و … رو بصورت آنلاین در اختیار ما قرار میده و ما میتونیم این اطلاعات ور در اکسل داشته باشیم.

اکسل رو باز کنید و داده ها رو فراخوانی کنید

فایل اکسل رو باز کرده و از تب Data و قسمت Get & Transform Data گزینه From Web رو انتخاب میکنیم.

حتما بخوانید:  ترکیب جداول در اکسل با استفاده از Power Query

استخراج داده از وبسایت- From web

شکل 1 – استخراج داده از وبسایت- From web

در پنجره نمایش داده شده، آدرس سایت مورد نظر رو وارد کرده و Ok رو میزنیم. در این قسمت دو گزینه basic و advance داریم که در گزینه advance به ما امکاناتی رو میده که چچطور داده ها جمع آوری بشه. اما در این تمرین، گزینه Basic جواب نیاز ما رو میده.

استخراج داده از وبسیات – آدرس سایت مورد نظر

شکل 2 – استخراج داده از وبسیات – آدرس سایت مورد نظر

نکته:
اگر آدرس صفحه مورد نظر طولانی هست، آدرس رو از نوار آدرس کپی کرده و در پنجره From web پیست کنید.

 

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

استخراج داده از وبسایت – لیست جداول موجود در صفحه وب مورد نظر

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

حالا از پنجره نمایش داده شده، جداول مورد نظر رو تیک میزنیم. مثلا د راینجا جدول مربوط به نرخ ارز، طلا و ارز مجازی رو انتخاب میکنیم.

برای اینکه بتونیم چند جدول رو انتخاب کنیم تیک گزینه Select Multiple items رو میزنیم. در اینجا با توجه به بررسی های انجام شده، میدونیم که جدول شماره 16، ارز مجازی، جدول شماره 2، نرخ طلا و جدول شماره 4، نرخ سکه رو نشون میده.

نکته:
با توجه به اینکه جداول اسم نداره، باید یکی یکی بررسی کنیم تا جداول مورد نظرمون رو پیدا کنیم و در صورت وجود ابهام داده های جداول رو با سایت (تب Web view) تطبیق بدیم که بتونیم جدول دقیق رو انتخاب کنیم.

 

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

شکل 4 – استخراج داده از وبسایت – انتخاب جداول داد های مورد نظر

حالا کافیه جداول مورد نظر رو به اکسل وارد کنیم. برای این کار روی گزینه Load کلیک میکنیم. با کلیک بر روی گزینه Load سه جدولی که انتخاب کردیم، در نوار Queries & Connections نمایش داده میهش که با نگه داشتن موس روی هر کدوم، محتویات جدول، آخرین زمان بروز رسانی و … نمایش داده میشه که با کلیک روی … و انتخاب گزینه Load to میتونیم جدول مورد نظر رو وارد شیت اکسل کنیم.

حتما بخوانید:  ابزار Unpivot ابزاری کاربردی در Power Query

جداول اضافه شده در قسمت Queries & Connections

شکل 5 – استخراج داده از وبسایت- جداول اضافه شده در قسمت Queries & Connections

با کلیک روی Load to پنجره ای باز میشه که میپرسه این داده ها در چه قالبی وارد اکسل بشن؟ گزینه Table رو انتخاب میکنیم و سلول مورد نظر رو برای ورود داده ها انتخاب میکنیم:

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

شکل 6 – استخراج داده از وبسایت – ورود داده ها به شیت اکسل

با زدن Ok داده های جدول انتخاب شده وارد اکسل خواهد شد. همین کار رو برای دو جدول دیگه هم انجام میدیم و هر سه جدول رو در شیت اکسل وارد میکنیم. (با توجه به جزئیات موجود، میتونیم هر جدول رو در شیت های جداگانه (New Worksheet) هم وارد کنیم)

وارد کردن داده ها در شیت اکسل

شکل 7- استخراج داده از وبسایت – وارد کردن داده ها در شیت اکسل

بروزرسانی داده ها بعد از دریافت اطلاعات از سایت

خب سوالی که پیش میاد اینه که بروز رسانی این داده به چه صورت هست؟

مثلا اگه بخوایم در بازه های زمانی مشخص، این داده ها بروز رسانی بشه باید چکار کنیم؟ بروز رسانی بصورت دستی (زمان های نامنظم) چطور انجام میشه؟

بروز رسانی کوئری ها بصورت دستی

برای انجام تنظیمات مربوط به بروزرسانی باید طبق زیر عمل کنیم:

اگر بخوایم بروز رسانی دستی و غیرخودکار انجام بشه، هر بار که نیاز به بروز رسانی بود، باید گزینه Refresh از تب Table Tools رو بزنیم:

بروزرسانی بصورت دستی

شکل 8 – استخراج داده از وبسایت – بروزرسانی بصورت دستی

با زدن refresh all همه کوئری ها بروز رسانی میشن. اگر بخوایم هر کوئری جداگانه آپدیت بشه، کوئری رو انتخاب کرده و از تب Query Tools گزینه Refresh رو انتخاب میکنیم. یا در گوشه سمت راست هر کوئری روی گزینه Refresh کلیک میکنیم. شکل 9

بروز رسانی کوئری

شکل 9 – دریافت اطلاعات از سایت – بروز رسانی کوئری

بروز رسانی کوئری ها بصورت خودکار

برای هر کوئری میتونیم تعیین کنیم که چطور و در چه فواصل زمانی بروزرسانی بشه. برای این کار کافیه روی جدول مورد نظر کلیک کرده و از تب Table Tools و گزینه Refresh روی گزینه Connection Properties کلیک کنیم و تنظیمات دلخواه رو در پنجره نمایش داده شده انجام بدیم. همونطور که در شکل 10 نمایش داده شده، فاصله زمانی بروزرسانی رو میتونیم تنظیم کنیم. با زدن تیک Refresh data when opening the file با هر بار باز کردن فایل، داده ها بروز رسانی میشه. توجه داشته باشید که همه این موارد در صورتی قابل انجام هست که اتصال به اینترنت برقرار باشه.

حتما بخوانید:  لیست فایل های یک پوشه در اکسل

تنظیم زمان های بروزرسانی خودکار

شکل 10 – استخراج داده ها از وبسایت – تنظیم زمان های بروزرسانی خودکار

این تنظیمات رو از مسیر شکل 11 نیز میتونیم انجام بدیم. با نگه داشتن روی هر کوئری و زدن … با کلیک روی گزینه Properties  پنجره تنظیم زمان بروزرسانی نمایش داده میشه.

انجام تنظیمات زمان های بروزرسانی از روی هر کوئری

شکل 11- استخراج داده از وبسایت – انجام تنظیمات زمان های بروزرسانی از روی هر کوئری

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

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

برای این کار باید کد VBA بنویسیم که هر بار با اجرا کردنش، داده های فعلی رو در ادامه دیتابیسی ذخیره بکنه و بعد عملیات بروزرسانی انجام بشه. که نمونه کد رو در فایل زیر می بینید.

دانلود فایل نمونه این آموزش

برای دانلود این فایل روی دکمه زیر کلیک کنید:

با عضویت در سایت به صورت مستقیم دانلود کنید

  • ارسال به ایمیل

Des

دریافت فایل این آموزشبرای دانلود فایل کلیک کنید

  • اشتراک گذاری:
profile avatar
حسنا خاکزاد
فارغ التحصیل لیسانس مهندسی صنایع، ارشد مدیریت صنعتی از دانشگاه تربیت مدرس و عاشق اکسل هستم. از سال 1388 که ترم 2 لیسانس بودم، به توصیه استاد مشاورم شروع به خوندن اکسل بصورت حرفه ای کردم و همچنان در حال مطالعه و یادگیری و البته آموزش به بقیه هستم.

مطلب قبلی

آموزش کار با تابع Replace
۱۸ فروردین ۱۳۹۹

مطلب بعدی

این عدد حاصل جمع چه اعدادی است؟
۱۸ فروردین ۱۳۹۹

ممکن است همچنین دوست داشته باشید

Unpivot Data Cover
ابزار Unpivot ابزاری کاربردی در Power Query
۷ مرداد, ۱۳۹۹
Power Query file lists Cover-min
لیست فایل های یک پوشه در اکسل
۲۵ تیر, ۱۳۹۸
Merge Tables- Power Query-min
ترکیب جداول در اکسل با استفاده از Power Query
۱ تیر, ۱۳۹۸

    17 نظر

  1. profile avatar
    mahdie
    ۱۵ آذر ۱۳۹۹
    پاسخ

    سلام
    من میخوام اطلاعات سایتی شبیه به یک سایت خبری که اطلاعاتی مثل تعداد لایک، پاورلایک، سیلور لایک و …. در هر کدوم از پست هاش هسترو داشته باشم.
    چطور میتونم؟ ضمن اینکه ممکنه بعضی پست ها یکی یا دو تا از اینا (لایک ها) رو اصلا نداشته باشه
    ممنون از شما

    • profile avatar
      حسنا خاکزاد
      ۱۷ آذر ۱۳۹۹
      پاسخ

      درود
      بستگی به ساختار سایت داره!
      باید ببینید با چه روشی میتونید اطلاعات رو جمع اوری کنید
      اگر از پاور کوئری ج نگیرید احتمالا باید کدنویسی های پیشرفته انجام بشه

  2. profile avatar
    رجا
    ۲۷ مهر ۱۳۹۹
    پاسخ

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

    • profile avatar
      حسنا خاکزاد
      ۲۸ مهر ۱۳۹۹
      پاسخ

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

  3. profile avatar
    علی
    ۱۹ مهر ۱۳۹۹
    پاسخ

    سلام خانم خاکزاد مرسی بابت آموزش خوبتون،
    میخواستم ببنیم این قابلیت در نسخه موبایل اکسل هم وجود داره چون تو گوشی یه گزینه داره بنام data validation و سپس یک گزینه میاره به اسم is valid UrL ولی من میخواستم خروجی بگیرم اتفاقی نیافتاد ..
    و سوال دیگم اینه که با استفاده از api که فایل های json هستند میشه رو سلول های اکسل خروجی گرفت?

  4. profile avatar
    رودکی
    ۱۹ مرداد ۱۳۹۹
    پاسخ

    سلام من میخوام از این صفحه سایت http://www.tsetmc.com
    از طریقی که آموزش دادید لیک آنلاین بگیرم و بیارمشون توی اکسل .
    صفحات دیگه رو میاره ولی این صفحه رو نمیتونه بیاره
    ضمن اینکه این صفحه خروجی اکسل هم میده ولی متاسفانه این خروجی رو هم نمیتونم بیارم توی اکسل و یا میشه همون خروجی اکسلی رو که این سایت میده رو انلاین کنم و مثلا هر 1 دقیقه بروز رسانیش کنیم .
    ممنون میشم راهنمایی بفرمایید .

    • profile avatar
      حسنا خاکزاد
      ۱۹ مرداد ۱۳۹۹
      پاسخ

      درود
      سایت بورس ساختارش طوری هست که با این روش های موجود قابل ورود به اکسل رو نداره
      یا باید از سایتهای مشابه مثل fipiran استفاده کنید
      یا سراغ کدنویسی های پیشرفته که میتونن داده از سایت ها جمع آوری کنن برید

  5. profile avatar
    سجاد
    ۱۵ مرداد ۱۳۹۹
    پاسخ

    سلام
    سوالی که من داشتم اینه که چرا ازسایت tsetmcیا سایت بورسی دیگه نمیتونم خروجی بگیرم؟
    ایا از صفحاتی که نیاز به لاگین شدن به سایت داره هم میشه خروجی گرفت برای اکسل؟اگر میشود چطور ؟

    • profile avatar
      حسنا خاکزاد
      ۱۵ مرداد ۱۳۹۹
      پاسخ

      درود
      ساختار سایتشون اینطور هست
      بعضی سایت ها تا حدی اطلاعات میدن مثل fipiran

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

  6. profile avatar
    موسوی‌نسل
    ۵ خرداد ۱۳۹۹
    پاسخ

    سلام
    چطور می‌شه یه جدول رو از وان‌درایو به کوئری‌ها اضافه و بروزرسانیش کرد؟

    • profile avatar
      سامان چراغی
      ۱۹ خرداد ۱۳۹۹
      پاسخ

      سلام
      روی فایل اکسل قرار داده شده در وان درایو کلیک راست کنید و گزینه Download رو بزنید. زمانیکه فایل دانلود شد، لینک فایل رو کپی کنید. در اکسل با استفاده از گزینه From Web و لینک فایلی که دانلود کردید میتونید به فایل مورد نظر دسترسی پیدا کنید.

  7. profile avatar
    امین
    ۵ اردیبهشت ۱۳۹۹
    پاسخ

    بسیار عالی بود. اصلا نمیدونستم اکسل چنین امکاناتی داره

  8. profile avatar
    zara
    ۲۶ فروردین ۱۳۹۹
    پاسخ

    با سلام
    اکسل من from web رو در قسمت get external data داره که به این صورت کار نمیکنه

    • profile avatar
      حسنا خاکزاد
      ۲۶ فروردین ۱۳۹۹
      پاسخ

      درود
      اپدیت کنید
      از 2016 به بعد به این شکل هست
      در کل این امکان در همه نسخه ها وجد داره. باید دنبال عبارت from web بگردید. در هر ورژن ی مقدار تفاوت هایی وجود داره

    • profile avatar
      سعید عظیمی
      ۲۶ فروردین ۱۳۹۹
      پاسخ

      سلام، من هم همین مشکل رو داشتم، از مسیر زیر بزنید، میاره
      Data > Get & Transform > New Query > From Other Sources > From Web

      • profile avatar
        Zara
        ۷ اردیبهشت ۱۳۹۹
        پاسخ

        ممنونم حل شد

  9. profile avatar
    سعید
    ۲۰ فروردین ۱۳۹۹
    پاسخ

    سلام، با سپاس بابت ارسال این مطلب خوب و کاربردی،

نظر بدهید لغو پاسخ

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

-- بارگیری کد امنیتی --

زودتر از دیگران با خبر بشید

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

آخرین مطالب

حل مسئله رتبه بندی داده های تکراری
۱۱آذر۱۳۹۹
ابزار Mail Merge
ترکیب Mail Merge ورد و اکسل
۲۳آبان۱۳۹۹
سورت تاریخ
مرتب سازی انواع تاریخ در اکسل
۰۲شهریور۱۳۹۹
توابع متنی: تابع Proper، تابع Lower، تابع Upper در اکسل
توابع ویرایش متن انگلیسی در اکسل
۱۷مرداد۱۳۹۹

وجود یک منبع جامع و به روز که بدونی همیشه پشتیبانته برای یادگیری ضروریه. اکسل پدیا رو برای همین به وجود آوردیم.

پاسخ سوالات شما

اگر سوالات اکسلی دارید تو تالار گفتمان اکسل پدیا میتونید مطرح کنید. اساتید بسیاری هستند که به شما کمک میکنند.

کانال و تالار گفتمان اکسل پدیا

ورود به تالار گفتگو تلگرام

عضویت در خبرنامه
با عضویت در خبرنامه، ایمیل‌های آموزشی درباره اکسل و برنامه نویسی وی بی را دریافت خواهید کرد.

  • این فیلد برای اعتبار سنجی است و باید بدون تغییر باقی بماند .

021-47625755

info@excelpedia.net

آدرس: تهران، میدان دوم صادقیه، برج گلدیس، طبقه 7 واحد 721

دانلود اپ اکسل پدیادانلود از کافه بازار

تمامی حقوق برای اکسل پدیا محفوظ است.

  • اکسل از راه دور
  • شرایط و قوانین
  • درباره ما
  • تماس با ما