Қурби мубодилаи асъор дар 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 аз Интернет ба ҳуҷайраҳои варақи корӣ мебошад: ХИЗМАТРАСОНИИ ВЕБ (ВЕБСЕРВИС) и FILTER.XML (FILTERXML). Онҳо дар ҷуфт кор мекунанд - аввал функсия ХИЗМАТРАСОНИИ ВЕБ дархостро ба сайти дилхоҳ иҷро мекунад ва ҷавоби онро дар формати XML бармегардонад ва сипас бо истифода аз функсия FILTER.XML мо ин ҷавобро ба ҷузъҳо "таҳлил мекунем" ва аз он маълумоте, ки ба мо лозим аст, мегирем.

Биёед кори ин функсияҳоро бо истифода аз як мисоли классикӣ дида бароем – ворид кардани қурби ҳар як асъори ба мо барои фосилаи муайяни санаи зарурӣ аз вебсайти Бонки марказии кишвари мо. Мо сохтмони зеринро ҳамчун варақ истифода мебарем:

Ин ҷо:

• Ҳуҷайраҳои зард санаҳои оғоз ва анҷоми давраи барои мо ҷолибро дар бар мегиранд.
• Рӯйхати кабуд бо истифода аз фармон дорои рӯйхати афтандаҳои асъор мебошад Маълумот - Тасдиқ - Рӯйхат (Маълумот - тасдиқ - рӯйхат).
• Дар ҳуҷайраҳои сабз, мо функсияҳои худро барои сохтани сатри дархост ва гирифтани посухи сервер истифода хоҳем кард.
• Ҷадвали дар тарафи рост истинод ба рамзҳои асъор аст (ба мо каме дертар лозим мешавад).
  

Рафтем!

Қадами 1. Ташаккул додани сатри дархост

Барои гирифтани маълумоти зарурӣ аз сайт, шумо бояд онро дуруст пурсед. Мо ба www.cbr.ru меравем ва истинодро дар поёни саҳифаи асосӣ мекушоем' Захираҳои техникӣ - Гирифтани маълумот бо истифода аз XML (http://cbr.ru/development/SXML/). Мо каме поинтар ҳаракат мекунем ва дар мисоли дуюм (мисоли 2) он чизест, ки ба мо лозим аст - гирифтани қурби асъор барои фосилаи санаи додашуда:

Тавре ки шумо аз мисол мебинед, сатри дархост бояд санаҳои оғозро дар бар гирад (санаи_талаб 1) ва охири (санаи_талаб 2) давраи таваҷҷӯҳи мо ва рамзи асъор (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. Иҷрои дархост

Ҳоло мо функсияро истифода мебарем ХИЗМАТРАСОНИИ ВЕБ (ВЕБСЕРВИС) бо сатри дархости тавлидшуда ҳамчун далели ягона. Ҷавоб як сатри дарози рамзи XML хоҳад буд (агар шумо хоҳед, ки онро пурра бубинед, беҳтар аст, ки калимаро фаъол созед ва андозаи чашмакро зиёд кунед):

Қадами 3. Таҳлили ҷавоб

Барои осонтар фаҳмидани сохтори маълумоти посух, беҳтар аст, ки яке аз таҳлилгари онлайни XML (масалан, http://xpather.com/ ё https://jsonformatter.org/xml-parser), ки метавонад рамзи XML-ро ба таври визуалӣ формат карда, ба он абзорҳо илова кунад ва синтаксисро бо ранг таъкид кунад. Он гоҳ ҳама чиз равшантар мешавад:

Акнун шумо ба таври возеҳ мебинед, ки арзишҳои курс аз ҷониби барчаспҳои мо чаҳорчӯба шудаанд ..., ва санаҳо атрибутҳо мебошанд таърих дар тегхо .

Барои истихроҷи онҳо, сутуни даҳ (ё бештар аз он - агар бо маржа анҷом дода шавад) чашмакҳои холиро дар варақ интихоб кунед (зеро фосилаи 10-рӯза муқаррар карда шудааст) ва функсияро дар сатри формула ворид кунед FILTER.XML (ФИЛТРXML):

Дар ин ҷо, далели аввал истинод ба ячейка бо посухи сервер (B8) ва дуввумӣ сатри дархост дар XPath, забони махсусест, ки барои дастрасӣ ба порчаҳои коди XML зарурӣ ва истихроҷи онҳо истифода мешавад. Шумо метавонед дар бораи забони XPath бештар хонед, масалан, дар ин ҷо.

Муҳим аст, ки пас аз ворид кардани формула, пахш накунед даромадан, ва миёнабури клавиатура Ctrl+Shift+даромадан, яъне онро ҳамчун формулаи массив ворид кунед (қавсҳои ҷингила дар атрофи он ба таври худкор илова карда мешаванд). Агар шумо версияи охирини Office 365-ро бо дастгирии массивҳои динамикӣ дар Excel дошта бошед, пас оддӣ даромадан, ва ба шумо лозим нест, ки чашмакҳои холиро пешакӣ интихоб кунед – худи функсия ҳамон қадар ҳуҷайраҳоро мегирад, ки лозим аст.

Барои истихроҷи санаҳо, мо ҳамин корро мекунем - мо дар сутуни ҳамсоя якчанд ячейкаҳои холӣ интихоб мекунем ва ҳамон функсияро истифода мебарем, аммо бо дархости XPath, то ҳамаи арзишҳои атрибутҳои Санаро аз барчаспҳои Сабт ба даст орем:

=FILTER.XML(B8;”//Сана/@Сана”)

Ҳоло дар оянда, ҳангоми тағир додани санаҳо дар ячейкаҳои аслии B2 ва B3 ё интихоби асъори дигар дар рӯйхати афтанда ҳуҷайраҳои B3, дархости мо ба таври худкор бо истинод ба сервери Бонки марказӣ барои маълумоти нав нав карда мешавад. Барои ба таври дастӣ маҷбур кардани навсозӣ, шумо метавонед ба таври иловагӣ миёнабури клавиатураро истифода баред Ctrl+Alt+F9.

• Меъёри биткоинҳоро ба Excel тавассути Power Query ворид кунед
• Қурбҳои асъорро аз Интернет дар версияҳои кӯҳнаи Excel ворид кунед