Rêjeya danûstendinê di Excel de nûve kirin

Min gelek caran awayên ku bi nûvekirina otomatîkî ya paşîn ve daneyan ji Înternetê têxe Excel-ê analîz kiriye. Gelek rindik:

• Di guhertoyên kevntir ên Excel 2007-2013 de, ev dikare bi daxwaznameyek malperê ya rasterast were kirin.
• Di sala 2010-an de dest pê dike, ev dikare bi pêveka Power Query re pir bi hêsanî were kirin.
  

Li van rêbazan di guhertoyên herî dawî yên Microsoft Excel-ê de, hûn niha dikarin yek din lê zêde bikin - bi karanîna fonksiyonên çêkirî ve daneyên ji Internetnternetê bi formata XML-ê veguhezînin.

XML (Zimanê Nîşandana eXtensible = Zimanê Nîşandana Berfireh) zimanek gerdûnî ye ku ji bo danasîna her cûre daneyê hatî çêkirin. Bi rastî, ew nivîsek sade ye, lê bi etîketên taybetî yên ku jê re hatine zêdekirin da ku avahiya daneyê nîşan bide. Gelek malper ji bo ku her kes dakêşîne di formata XML-ê de herikên belaş ên daneyên xwe peyda dikin. Li ser malpera Banka Navendî ya Welatê Me (www.cbr.ru), bi taybetî, bi alîkariya teknolojiyek wusa, daneyên li ser rêjeyên danûstendinê yên diravên cihêreng têne dayîn. Ji malpera Moskovayê Exchange (www.moex.com) hûn dikarin bi heman awayî quotes ji bo stokan, bond û gelek agahdariya kêrhatî dakêşînin.

Ji guhertoya 2013-an vir ve, Excel du fonksiyon hene ku rasterast daneyên XML-ê ji Înternetê di nav hucreyên kargehê de bar dike: XIZMETA WEB (WEBSERVICE) и FILTER.XML (FILTERXML). Ew bi cotan dixebitin - yekem fonksiyon XIZMETA WEB daxwazek ji malpera xwestî re pêk tîne û bersiva wê di forma XML de vedigerîne, û dûv re fonksiyonê bikar tîne FILTER.XML em vê bersivê di nav pêkhateyan de "parce" dikin, daneyên ku ji me re hewce ne jê derdixin.

Ka em bi mînakek klasîk li xebata van fonksiyonan binihêrin - ji malpera Banka Navendî ya Welatê Me nirxa danûstendinê ya her pereyê ku ji bo navberek tarîxek diyar hewce dike îtxal dikin. Em ê avakirina jêrîn wekî vala bikar bînin:

Vir:

• Di hucreyên zer de tarîxên destpêk û dawî yên heyama ku ji me re eleqedar in hene.
• Yê şîn lîsteyek daketî ya diravên ku fermanê bikar tîne heye Daneyên - Validation - Lîsteya (Daneyên - Verastkirin - Lîsteya).
• Di hucreyên kesk de, em ê fonksiyonên xwe bikar bînin da ku rêzika pirsê biafirînin û bersiva serverê bistînin.
• Tabloya li milê rastê referansek kodên diravê ye (em ê hinekî paşê jê re hewce bikin).
  

Werin em biçin!

Gav 1. Çêkirina rêzika pirsê

Ji bo ku hûn agahdariya pêwîst ji malperê bistînin, divê hûn rast bipirsin. Em diçin www.cbr.ru û lînka di binê rûpela sereke de vedikin' Çavkaniyên Teknîkî - Danîna daneyan bi karanîna XML (http://cbr.ru/development/SXML/). Em piçekî jêrîn digerînin û di mînaka duyemîn de (Mînak 2) tiştê ku ji me re lazim e dê hebe - wergirtina rêjeyên danûstendinê ji bo navberek tarîxek diyarkirî:

Wekî ku hûn ji nimûneyê dibînin, rêzika pirsê divê tarîxên destpêkê (date_req1) û dawiya (date_req2) ya heyama berjewendiya me û koda diravê (VAL_NM_RQ), rêjeya ku em dixwazin bistînin. Hûn dikarin kodên diravên sereke di tabloya jêrîn de bibînin:

Rêbernameyek bêkêmasî ya kodên diravî jî li ser malpera Banka Navendî heye - binihêrin http://cbr.ru/scripts/XML_val.asp?d=0

Naha em ê di şaneyek li ser kaxezekê de rêzika pirsê ava bikin bi:

• operatora pêwendiya nivîsê (&) ku wê li hev bicivîne;
  
• Features VPR (VLOOKUP)ji bo dîtina koda pereyê ku em di pelrêçê de hewce ne;
  
• Features NIVÎSTOK (NIVÎSTOK), ya ku tarîxê li gorî şêwaza diyarkirî roj-meh-sal bi rêkûpêk diguhezîne.

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

Gav 2. Daxwazê ​​bicîh bikin

Niha em fonksiyonê bikar tînin XIZMETA WEB (WEBSERVICE) bi rêzika pirsê ya hatî çêkirin wekî argumana yekane. Bersiv dê rêzek dirêj a koda XML be (çêtir e ku hûn wrapa peyvan vekin û mezinahiya hucreyê zêde bikin ger hûn dixwazin wê bi tevahî bibînin):

Gav 3. Parskirina bersivê

Ji bo hêsankirina fêmkirina strukturên daneya bersivê, çêtir e ku meriv yek ji parserên XML-ê yên serhêl bikar bîne (mînak, http://xpather.com/ an https://jsonformatter.org/xml-parser), ya ku dikare bi dîtbarî koda XML-ê format bike, li wê derê dakêşan zêde bike û hevoksaziyê bi reng ronî bike. Wê hingê her tişt dê pir zelaltir bibe:

Naha hûn dikarin bi zelalî bibînin ku nirxên qursê ji hêla tagên me ve têne rêve kirin ..., û tarîx taybetmendî ne Date di etîketan de .

Ji bo derxistina wan, stûnek ji deh (an jî zêdetir - heke bi marjînal were kirin) şaneyên vala yên li ser pelê hilbijêrin (ji ber ku navberek tarîxek 10-rojî hate danîn) û fonksiyonê têxe barika formula. FILTER.XML (PARZÛNXML):

Li vir, argûmana yekem girêdanek bi hucreyek bi bersivek serverê re (B8) ye, û ya duyemîn di XPath de rêzek pirsê ye, zimanek taybetî ye ku dikare were bikar anîn da ku bigihîje perçeyên koda XML-ya pêwîst û wan derxe. Ji bo nimûne, hûn dikarin li vir di derbarê zimanê XPath de bêtir bixwînin.

Girîng e ku piştî têketina formula, pêl nekin Derbasbûn, û kurteya klavyeyê Ctrl+Tarloqî+Derbasbûn, ango wê wekî formulek rêzê binivîsin (tengên ku li dora wê bixweber têne zêdekirin). Ger we guhertoya herî dawî ya Office 365-ê ya bi piştgirîya rêzikên dînamîkî yên li Excel heye, wê hingê hêsan e Derbasbûn, û hûn ne hewce ne ku hûn berê şaneyên vala hilbijêrin - fonksiyon bixwe dê bi qasî ku hewce bike şaneyan bigire.

Ji bo derxistina tarîxan, em ê heman tiştî bikin - em ê di stûna cîran de çend şaneyên vala hilbijêrin û heman fonksiyonê bikar bînin, lê bi pirsek XPath-ê cûda, da ku hemî nirxên taybetmendiyên Dîrokê ji etîketên Record bistînin:

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

Naha di pêşerojê de, dema ku tarîxên di hucreyên orîjînal B2 û B3 de biguhezînin an di navnîşa daketî ya hucreya B3 de diravek cûda hilbijêrin, pirsa me dê bixweber were nûve kirin, ji bo daneyên nû serî li servera Banka Navendî bide. Ji bo ku hûn bi destan nûvekirinek ferz bikin, hûn dikarin pêvek kurteya klavyeyê bikar bînin Ctrl+alt+F9.

• Rêjeya bitcoin-ê bi navgîniya Power Query-ê ji Excel re derxînin
• Di guhertoyên kevn ên Excel de rêjeyên danûstendinê ji Înternetê derxînin