Excelде жаңыланган алмашуу курсу

Мен бир нече жолу Excelге маалыматтарды Интернеттен автоматтык түрдө жаңыртуу менен импорттоо жолдорун талдап чыктым. Өзгөчө:

• Excel 2007-2013 эски версияларында, бул түздөн-түз желе өтүнүч менен аткарылышы мүмкүн.
• 2010-жылдан баштап, муну Power Query кошумчасы менен абдан ыңгайлуу кылса болот.
  

Microsoft Excel программасынын акыркы версияларындагы бул ыкмаларга сиз дагы башкасын кошсоңуз болот - XML ​​форматында орнотулган функцияларды колдонуу менен Интернеттен маалыматтарды импорттоо.

XML (eXtensible Markup Language = Extensible Markup Language) – ар кандай маалыматтарды сүрөттөө үчүн иштелип чыккан универсалдуу тил. Чынында, бул жөнөкөй текст, бирок маалымат түзүмүн белгилөө үчүн ага атайын тегдер кошулган. Көптөгөн сайттар кимдир бирөө жүктөп алуу үчүн XML форматында өз маалыматтарынын акысыз агымын камсыз кылат. Биздин өлкөнүн Борбордук банкынын сайтында (www.cbr.ru), атап айтканда, окшош технологиянын жардамы менен, ар кандай валюталардын курстары боюнча маалыматтар берилген. Москва биржасынын веб-сайтынан (www.moex.com) сиз акциялардын, облигациялардын котировкаларын жана башка көптөгөн пайдалуу маалыматтарды ушул эле жол менен жүктөй аласыз.

2013-жылдын версиясынан бери Excelде XML маалыматтарын Интернеттен жумушчу барагынын клеткаларына түздөн-түз жүктөө үчүн эки функция бар: WEB КЫЗМАТЫ (ВЕБСЕРВИС) и FILTER.XML (FILTERXML). Алар жупта иштешет - биринчи функция WEB КЫЗМАТЫ каалаган сайтка суроо-талапты аткарат жана анын жообун XML форматында кайтарат, андан кийин функцияны колдонуу FILTER.XML биз бул жоопту компоненттерге бөлүп, андан керектүү маалыматтарды чыгарып алабыз.

Келгиле, бул функциялардын иштешин классикалык мисал аркылуу карап көрөлү – өлкөбүздүн Борбордук банкынын веб-сайтынан берилген даталар аралыкка керектүү каалаган валютанын курсун импорттоо. Бланка катары төмөнкү курулушту колдонобуз:

Бул жерде:

• Сары клеткалар бизди кызыктырган мезгилдин башталышы жана аяктоо даталарын камтыйт.
• Көк түстө буйрукту колдонуу менен валюталардын ачылуучу тизмеси бар Маалыматтар – Валидация – Тизме (Маалымат — Валидация — Тизме).
• Жашыл уячаларда биз суроо сапты түзүү жана сервердин жообун алуу үчүн функцияларыбызды колдонобуз.
• Оң жактагы таблица валюта коддоруна шилтеме (бул бизге бир аз кийинчерээк керек болот).
  

Кеттик!

Кадам 1. Суроо сапты түзүү

Сайттан керектүү маалыматты алуу үчүн аны туура суроо керек. Биз 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 (КӨРҮҮ)каталогдон бизге керектүү валютанын кодун табуу үчүн;
  
• Өзгөчөлүктөрү ТЕКСТ (ТЕКСТ), бул сызык аркылуу күн-ай-жыл берилген үлгү боюнча датаны айлантат.

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

2-кадам. Сурамды аткарыңыз

Азыр биз функцияны колдонобуз WEB КЫЗМАТЫ (ВЕБСЕРВИС) бир гана аргумент катары түзүлгөн суроо сап менен. Жооп XML кодунун узун сызыгы болот (эгер сиз аны толугу менен көргүңүз келсе, сөздү жабууну күйгүзүп, клетканын өлчөмүн чоңойтуңуз):

3-кадам. Жоопту талдоо

Жооп берилиштеринин структурасын түшүнүүнү жеңилдетүү үчүн, онлайн XML талдоочуларынын бирин колдонуу жакшыраак (мисалы, http://xpather.com/ же https://jsonformatter.org/xml-parser), XML кодун визуалдык түрдө форматтап, ага чегинүүлөрдү кошуп, синтаксисти түс менен бөлүп көрсөтө алат. Ошондо баары айкыныраак болот:

Эми сиз курстун баалуулуктары биздин тегдерибиз менен жабдылганын айкын көрө аласыз ..., жана даталар атрибуттар болуп саналат дата тегдерде .

Аларды чыгарып алуу үчүн баракта он (же андан көп) бош уячалардан турган тилкени тандаңыз (анткени 10 күндүк дата аралыгы коюлган) жана функцияны формула тилкесине киргизиңиз FILTER.XML (ФИЛЬТРXML):

Бул жерде, биринчи аргумент - бул сервердин жообу бар уячага шилтеме (B8), ал эми экинчиси - XPath'тагы суроо саптары, керектүү XML код фрагменттерине жетүү жана аларды чыгаруу үчүн колдонула турган атайын тил. Сиз XPath тили жөнүндө көбүрөөк окуй аласыз, мисалы, бул жерде.

Формуланы киргизгенден кийин басбоо маанилүү кирүү, жана баскычтоптун жарлыгы Ctrl+өзгөрүү+кирүү, башкача айтканда, аны массив формуласы катары киргизиңиз (анын тегерегиндеги тармал кашаалар автоматтык түрдө кошулат). Эгер сизде Excelдеги динамикалык массивдерди колдоо менен Office 365тин эң акыркы версиясы болсо, анда жөнөкөй кирүү, жана алдын ала бош уячаларды тандоонун кереги жок – функциянын өзү канча керек болсо, ошончо уячаны алат.

Даталарды чыгаруу үчүн, биз да ушундай кылабыз - биз чектеш тилкеден бир нече бош уячаларды тандап, ошол эле функцияны колдонобуз, бирок Жазуу тэгдеринен Date атрибуттарынын бардык маанилерин алуу үчүн башка XPath сурамы менен:

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

Эми келечекте, баштапкы B2 жана B3 уячаларындагы даталарды өзгөртүүдө же В3 уячасынын ылдый түшүүчү тизмесинен башка валютаны тандоодо, жаңы маалыматтар үчүн Борбордук банктын серверине шилтеме берүү менен биздин сурообуз автоматтык түрдө жаңыланат. Жаңыртууну кол менен мажбурлоо үчүн, кошумча баскычтоп жарлыгын колдонсоңуз болот Ctrl+Alt+F9.

• Power Query аркылуу Excel'ге биткоиндин курсун импорттоо
• Excelдин эски версияларында Интернеттен алмашуу курстарын импорттоо