Excel-də yenilənmiş məzənnə

Sonrakı avtomatik yeniləmə ilə məlumatları İnternetdən Excel-ə idxal etməyin yollarını dəfələrlə təhlil etmişəm. Xüsusilə:

• Excel 2007-2013-ün köhnə versiyalarında bu, birbaşa veb sorğusu ilə edilə bilər.
• 2010-cu ildən başlayaraq bu, Power Query əlavəsi ilə çox rahat şəkildə edilə bilər.
  

Microsoft Excel-in ən son versiyalarındakı bu üsullara indi başqa birini əlavə edə bilərsiniz - daxili funksiyalardan istifadə edərək XML formatında məlumatların İnternetdən idxalı.

XML (eXtensible Markup Language = Extensible Markup Language) istənilən növ məlumatı təsvir etmək üçün nəzərdə tutulmuş universal bir dildir. Əslində, bu, düz mətndir, lakin məlumat strukturunu qeyd etmək üçün ona əlavə edilmiş xüsusi etiketlərlə. Bir çox saytlar hər kəsin yükləməsi üçün öz məlumatlarının XML formatında pulsuz axınlarını təmin edir. Ölkəmizin Mərkəzi Bankının saytında (www.cbr.ru), xüsusən də oxşar texnologiyanın köməyi ilə müxtəlif valyutaların məzənnələri haqqında məlumatlar verilir. Moskva Birjasının saytından (www.moex.com) eyni şəkildə səhmlər, istiqrazlar və bir çox digər faydalı məlumatlar üçün kotirovkaları yükləyə bilərsiniz.

2013-cü ildən bəri Excel XML məlumatlarını İnternetdən iş vərəqi xanalarına birbaşa yükləmək üçün iki funksiyaya malikdir: WEB XİDMƏTİ (VEBXİDMƏT) и FILTER.XML (FILTERXML). Onlar cüt-cüt işləyirlər - ilk növbədə funksiya WEB XİDMƏTİ istədiyiniz sayta sorğunu yerinə yetirir və cavabını XML formatında qaytarır, sonra isə funksiyadan istifadə edir FILTER.XML biz bu cavabı komponentlərə ayırırıq, ondan lazım olan məlumatları çıxarırıq.

Klassik misaldan istifadə edərək bu funksiyaların işinə baxaq – müəyyən bir tarix intervalı üçün bizə lazım olan istənilən valyutanın məzənnəsini Ölkəmizin Mərkəzi Bankının saytından idxal etməklə. Boşluq kimi aşağıdakı tikintidən istifadə edəcəyik:

Burada:

• Sarı hüceyrələr bizim üçün maraqlı olan dövrün başlanğıc və bitmə tarixlərini ehtiva edir.
• Mavi olanda, əmrdən istifadə edərək valyutaların açılan siyahısı var Məlumat - Doğrulama - Siyahı (Məlumat - Doğrulama - Siyahı).
• Yaşıl xanalarda biz sorğu sətri yaratmaq və serverin cavabını almaq üçün funksiyalarımızdan istifadə edəcəyik.
• Sağdakı cədvəl valyuta kodlarına istinaddır (bir az sonra bizə lazım olacaq).
  

Gedək!

Addım 1. Sorğu sətirinin formalaşdırılması

Saytdan tələb olunan məlumatları əldə etmək üçün onu düzgün soruşmaq lazımdır. Biz www.cbr.ru saytına gedirik və əsas səhifənin altbilgisindəki linki açırıq. Texniki Resurslar'- XML istifadə edərək məlumat əldə etmək (http://cbr.ru/development/SXML/). Biz bir az aşağı sürüşürük və ikinci misalda (Nümunə 2) bizə lazım olan şey olacaq - müəyyən bir tarix intervalı üçün valyuta məzənnələrini əldə etmək:

Nümunədən göründüyü kimi, sorğu sətirində başlanğıc tarixləri olmalıdır (tarix_tələb1) və sonluqlar (tarix_tələb2) bizi maraqlandıran dövr və valyuta kodu (VAL_NM_RQ), əldə etmək istədiyimiz nisbət. Əsas valyuta kodlarını aşağıdakı cədvəldə tapa bilərsiniz:

Valyuta kodlarına dair tam bələdçi Mərkəzi Bankın internet saytında da mövcuddur – bax http://cbr.ru/scripts/XML_val.asp?d=0

İndi biz vərəqdəki hüceyrədə sorğu sətrini yaradacağıq:

• mətni birləşdirən operator (&) onu birləşdirmək üçün;
  
• Xüsusiyyətləri VPR (BAXIN)kataloqda bizə lazım olan valyutanın kodunu tapmaq;
  
• Xüsusiyyətləri TEXT (MƏTN), verilmiş nümunəyə görə tarixi gün-ay-il xəttinə çevirən.

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

Addım 2. Sorğunu yerinə yetirin

İndi funksiyadan istifadə edirik WEB XİDMƏTİ (VEBXİDMƏT) yeganə arqument kimi yaradılan sorğu sətri ilə. Cavab XML kodunun uzun sətri olacaq (bütövlükdə görmək istəyirsinizsə, söz sarğısını yandırıb xana ölçüsünü artırmaq daha yaxşıdır):

Addım 3. Cavabın təhlili

Cavab məlumatlarının strukturunu başa düşməyi asanlaşdırmaq üçün onlayn XML analizatorlarından birini istifadə etmək daha yaxşıdır (məsələn, http://xpather.com/ və ya https://jsonformatter.org/xml-parser), XML kodunu vizual olaraq formatlaşdıra, ona abzaslar əlavə edə və sintaksisi rənglə vurğulaya bilər. Sonra hər şey daha aydın olacaq:

İndi kurs dəyərlərinin etiketlərimizlə çərçivələndiyini aydın görə bilərsiniz ..., və tarixlər atributlardır tarix etiketlərdə .

Onları çıxarmaq üçün vərəqdə on (və ya daha çox - kənar ilə aparılırsa) boş xanalardan ibarət bir sütun seçin (çünki 10 günlük tarix intervalı təyin edilib) və funksiyanı düstur çubuğuna daxil edin. FILTER.XML (FİLTREXML):

Burada birinci arqument server cavabı (B8) olan xanaya keçiddir, ikincisi isə lazımi XML kod fraqmentlərinə daxil olmaq və onları çıxarmaq üçün istifadə edilə bilən xüsusi dil olan XPath-da sorğu sətridir. Məsələn, XPath dili haqqında daha çox oxuya bilərsiniz.

Düsturu daxil etdikdən sonra basmamaq vacibdir Daxil edin, və klaviatura qısa yolu Ctrl+ÜstKrkt+Daxil edin, yəni onu massiv düsturu kimi daxil edin (ətrafındakı qıvrım mötərizələr avtomatik əlavə olunacaq). Excel-də dinamik massivləri dəstəkləyən Office 365-in ən son versiyası varsa, o zaman sadə Daxil edin, və əvvəlcədən boş xanaları seçməyə ehtiyac yoxdur – funksiyanın özü lazım olduğu qədər xana götürəcək.

Tarixləri çıxarmaq üçün eyni şeyi edəcəyik - bitişik sütunda bir neçə boş xana seçəcəyik və eyni funksiyadan istifadə edəcəyik, lakin fərqli XPath sorğusu ilə Tarix atributlarının bütün dəyərlərini Qeyd etiketlərindən əldə etmək üçün:

=FILTER.XML(B8;”//Qeyd/@Tarix”)

İndi gələcəkdə B2 və B3 orijinal xanalarında tarixləri dəyişdirərkən və ya B3 xanasının açılan siyahısında fərqli valyuta seçərkən sorğumuz yeni məlumatlar üçün Mərkəzi Bankın serverinə istinad edərək avtomatik yenilənəcək. Yeniləməni əl ilə məcbur etmək üçün əlavə olaraq klaviatura qısa yolundan istifadə edə bilərsiniz Ctrl+Dayandırmaq+F9.

• Power Query vasitəsilə bitcoin dərəcəsini Excel-ə idxal edin
• Excelin köhnə versiyalarında İnternetdən valyuta məzənnələrini idxal edin