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

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

• در نسخه های قدیمی اکسل 2007-2013، این کار را می توان با درخواست مستقیم وب انجام داد.
• از سال 2010، این کار را می توان به راحتی با افزونه Power Query انجام داد.
  

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

XML (EXtensible Markup Language = Extensible Markup Language) یک زبان جهانی است که برای توصیف هر نوع داده ای طراحی شده است. در واقع، این متن ساده است، اما با برچسب های خاصی که برای علامت گذاری ساختار داده به آن اضافه شده است. بسیاری از سایت ها جریان های رایگان داده های خود را در قالب XML برای دانلود هر کسی ارائه می دهند. در وب سایت بانک مرکزی کشور ما (www.cbr.ru)، به ویژه، با کمک یک فناوری مشابه، داده هایی در مورد نرخ ارزهای مختلف ارائه می شود. از وب سایت بورس مسکو (www.moex.com) می توانید قیمت سهام، اوراق قرضه و بسیاری از اطلاعات مفید دیگر را به همین روش دانلود کنید.

از نسخه 2013، اکسل دو عملکرد برای بارگیری مستقیم داده های XML از اینترنت در سلول های کاربرگ دارد: سرویس وب (سرویس وب) и FILTER.XML (FILTERXML). آنها به صورت جفت کار می کنند - ابتدا تابع سرویس وب درخواستی را به سایت مورد نظر اجرا می کند و پاسخ آن را با فرمت XML برمی گرداند و سپس با استفاده از تابع FILTER.XML ما این پاسخ را به اجزاء "تجزیه" می کنیم و داده های مورد نیاز خود را از آن استخراج می کنیم.

بیایید با استفاده از یک مثال کلاسیک به عملکرد این توابع نگاه کنیم - وارد کردن نرخ ارز هر ارزی که برای یک بازه زمانی معین نیاز داریم از وب سایت بانک مرکزی کشورمان. ما از ساختار زیر به عنوان خالی استفاده خواهیم کرد:

اینجا:

• سلول های زرد حاوی تاریخ شروع و پایان دوره مورد علاقه ما هستند.
• رنگ آبی با استفاده از دستور دارای لیست کشویی ارزها است داده ها – اعتبارسنجی – فهرست (داده ها - اعتبارسنجی - فهرست).
• در سلول های سبز، از توابع خود برای ایجاد یک query string و دریافت پاسخ سرور استفاده می کنیم.
• جدول سمت راست اشاره ای به کدهای ارز است (کمی بعد به آن نیاز خواهیم داشت).
  

بیا بریم!

مرحله 1. تشکیل یک query string

برای دریافت اطلاعات مورد نیاز از سایت، باید آن را به درستی بپرسید. ما به www.cbr.ru می رویم و پیوند را در فوتر صفحه اصلی باز می کنیم. منابع فنی - دریافت داده با استفاده از XML (http://cbr.ru/development/SXML/). کمی پایین‌تر اسکرول می‌کنیم و در مثال دوم (مثال 2) چیزی که نیاز داریم وجود خواهد داشت - دریافت نرخ ارز برای یک فاصله زمانی معین:

همانطور که از مثال می بینید، رشته پرس و جو باید حاوی تاریخ های شروع باشد (date_req1) و پایان ها (date_req2) دوره مورد علاقه ما و کد ارز (VAL_NM_RQ) نرخی که می خواهیم بدست آوریم. کدهای ارز اصلی را می توانید در جدول زیر مشاهده کنید:

راهنمای کامل کدهای ارز نیز در سایت بانک مرکزی موجود است - http://cbr.ru/scripts/XML_val.asp?d=0 را ببینید.

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

• عملگر الحاق متن (&) برای کنار هم قرار دادن آن؛
  
• امکانات VPR (VLOOKUP)برای پیدا کردن کد ارز مورد نیاز ما در فهرست؛
  
• امکانات TEXT (متن)، که تاریخ را بر اساس الگوی داده شده روز-ماه-سال از طریق یک اسلش تبدیل می کند.

="http://cbr.ru/scripts/XML_dynamic.asp?date_req1="&ТЕКСТ(B2;"ДД/ММ/ГГГГ")&  "&date_req2="&ТЕКСТ(B3;"ДД/ММ/ГГГГ")&"&VAL_NM_RQ="&ВПР(B4;M:N;2;0)  

مرحله 2. درخواست را اجرا کنید

اکنون از تابع استفاده می کنیم سرویس وب (سرویس وب) با رشته پرس و جو تولید شده به عنوان تنها آرگومان. پاسخ، یک خط طولانی از کد XML خواهد بود (بهتر است در صورت تمایل کامل آن را ببینید، wrap را روشن کنید و اندازه سلول را افزایش دهید):

مرحله 3. تجزیه پاسخ

برای سهولت درک ساختار داده های پاسخ، بهتر است از یکی از تجزیه کننده های XML آنلاین (به عنوان مثال، http://xpather.com/ یا https://jsonformatter.org/xml-parser) استفاده کنید. که می تواند کد XML را به صورت بصری قالب بندی کند، به آن تورفتگی اضافه کند و نحو را با رنگ برجسته کند. سپس همه چیز بسیار واضح تر خواهد شد:

اکنون به وضوح می توانید ببینید که مقادیر دوره توسط برچسب های ما قاب بندی می شوند ...، و تاریخ ها صفات هستند تاریخ در برچسب ها .

برای استخراج آنها، یک ستون ده (یا بیشتر - اگر با حاشیه انجام شود) سلول خالی روی برگه انتخاب کنید (زیرا یک فاصله زمانی 10 روزه تعیین شده بود) و تابع را در نوار فرمول وارد کنید. FILTER.XML (فیلترXML):

در اینجا، اولین آرگومان پیوندی به یک سلول با پاسخ سرور (B8) است، و دومی یک رشته پرس و جو در XPath است، یک زبان خاص که می تواند برای دسترسی به قطعات کد XML ضروری و استخراج آنها استفاده شود. برای مثال در اینجا می توانید اطلاعات بیشتری در مورد زبان XPath بخوانید.

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

برای استخراج تاریخ‌ها، همین کار را انجام می‌دهیم - چندین سلول خالی را در ستون مجاور انتخاب می‌کنیم و از همان تابع استفاده می‌کنیم، اما با یک کوئری XPath متفاوت، تا تمام مقادیر ویژگی‌های Date را از تگ‌های Record دریافت کنیم:

=FILTER.XML(B8;”//Record/@Date”)

اکنون در آینده، هنگام تغییر تاریخ در سلول های اصلی B2 و B3 یا انتخاب ارز متفاوت در لیست کشویی سلول B3، درخواست ما به طور خودکار با مراجعه به سرور بانک مرکزی برای داده های جدید به روز می شود. برای اجبار به روز رسانی به صورت دستی، می توانید علاوه بر این، از میانبر صفحه کلید استفاده کنید کلیدهای Ctrl+دگرساز+F9.

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