एक्सेल मा अद्यावधिक विनिमय दर

मैले बारम्बार स्वचालित अद्यावधिकको साथ इन्टरनेटबाट एक्सेलमा डाटा आयात गर्ने तरिकाहरू विश्लेषण गरेको छु। खासमा:

• Excel 2007-2013 को पुरानो संस्करणहरूमा, यो प्रत्यक्ष वेब अनुरोधको साथ गर्न सकिन्छ।
• 2010 मा सुरु गर्दै, यो पावर क्वेरी एड-इनको साथ धेरै सहज रूपमा गर्न सकिन्छ।
  

माइक्रोसफ्ट एक्सेलको नवीनतम संस्करणहरूमा यी विधिहरूमा, तपाइँ अब अर्को थप्न सक्नुहुन्छ - बिल्ट-इन प्रकार्यहरू प्रयोग गरेर XML ढाँचामा इन्टरनेटबाट डाटा आयात गर्दै।

XML (एक्सटेन्सिबल मार्कअप भाषा = एक्सटेन्सिबल मार्कअप भाषा) कुनै पनि प्रकारको डाटा वर्णन गर्न डिजाइन गरिएको विश्वव्यापी भाषा हो। वास्तवमा, यो सादा पाठ हो, तर डेटा संरचना मार्कअप गर्न विशेष ट्यागहरू थपिएको छ। धेरै साइटहरूले डाउनलोड गर्नका लागि XML ढाँचामा उनीहरूको डेटाको निःशुल्क स्ट्रिमहरू प्रदान गर्दछ। हाम्रो देशको केन्द्रीय बैंक (www.cbr.ru) को वेबसाइटमा, विशेष गरी, समान प्रविधिको सहयोगमा, विभिन्न मुद्राहरूको विनिमय दरहरूमा डेटा दिइएको छ। मस्को एक्सचेन्ज वेबसाइट (www.moex.com) बाट तपाईले स्टक, बन्ड र अन्य धेरै उपयोगी जानकारीको लागि उद्धरणहरू डाउनलोड गर्न सक्नुहुन्छ।

संस्करण 2013 देखि, एक्सेलले कार्यपत्र कक्षहरूमा इन्टरनेटबाट सीधा XML डाटा लोड गर्नका लागि दुई प्रकार्यहरू छन्: वेब सेवा (WEBSERVICE) и FILTER.XML (FILTERXML)। तिनीहरू जोडीमा काम गर्छन् - पहिलो प्रकार्य वेब सेवा इच्छित साइटमा अनुरोध कार्यान्वयन गर्दछ र XML ढाँचामा यसको प्रतिक्रिया फर्काउँछ, र त्यसपछि प्रकार्य प्रयोग गरेर FILTER.XML हामी यस उत्तरलाई कम्पोनेन्टमा "पार्स" गर्छौं, हामीलाई आवश्यक डेटा निकाल्दै।

क्लासिक उदाहरण प्रयोग गरेर यी कार्यहरूको सञ्चालनलाई हेरौं - हाम्रो देशको केन्द्रीय बैंकको वेबसाइटबाट दिइएको मिति अन्तरालको लागि हामीलाई आवश्यक पर्ने कुनै पनि मुद्राको विनिमय दर आयात गर्ने। हामी निम्न निर्माणलाई खाली रूपमा प्रयोग गर्नेछौं:

यहाँ:

• पहेंलो कक्षहरूले हामीलाई चासोको अवधिको सुरु र अन्त्य मितिहरू समावेश गर्दछ।
• निलोमा आदेश प्रयोग गरी मुद्राहरूको ड्रप-डाउन सूची छ डाटा - प्रमाणीकरण - सूची (डेटा - प्रमाणीकरण - सूची).
• हरियो कक्षहरूमा, हामी क्वेरी स्ट्रिङ सिर्जना गर्न र सर्भरको प्रतिक्रिया प्राप्त गर्न हाम्रो प्रकार्यहरू प्रयोग गर्नेछौं।
• दायाँ तिरको तालिका मुद्रा कोडहरूको सन्दर्भ हो (हामीलाई केहि पछि आवश्यक हुनेछ)।
  

चलो जानुहोस्!

चरण 1. क्वेरी स्ट्रिङ बनाउँदै

साइटबाट आवश्यक जानकारी प्राप्त गर्न, तपाईंले यसलाई सही रूपमा सोध्न आवश्यक छ। हामी www.cbr.ru मा जान्छौं र मुख्य पृष्ठको फुटरमा लिङ्क खोल्छौं' प्राविधिक स्रोत'- XML प्रयोग गरेर डाटा प्राप्त गर्दै (http://cbr.ru/development/SXML/)। हामी अलि तल स्क्रोल गर्छौं र दोस्रो उदाहरणमा (उदाहरण २) हामीलाई चाहिने कुरा हुनेछ - दिइएको मिति अन्तरालको लागि विनिमय दरहरू प्राप्त गर्दै:

तपाईले उदाहरणबाट देख्न सक्नुहुन्छ, क्वेरी स्ट्रिङमा सुरु मितिहरू समावेश हुनुपर्छ (date_req1) र अन्त्य (date_req2हामीलाई ब्याजको अवधि र मुद्रा कोड (VAL_NM_RQ), हामीले प्राप्त गर्न चाहेको दर। तपाईंले तलको तालिकामा मुख्य मुद्रा कोडहरू फेला पार्न सक्नुहुन्छ:

मुद्रा कोडहरूको लागि एक पूर्ण गाइड केन्द्रीय बैंक वेबसाइटमा पनि उपलब्ध छ - हेर्नुहोस् http://cbr.ru/scripts/XML_val.asp?d=0

अब हामी पानामा सेलमा क्वेरी स्ट्रिङ बनाउँछौं:

• टेक्स्ट कन्केटनेसन अपरेटर (&) यसलाई सँगै राख्नको लागि;
  
• विशेषताहरु VPR (VLOOKUP)हामीलाई निर्देशिकामा आवश्यक मुद्राको कोड फेला पार्न;
  
• विशेषताहरु पाठ (पाठ), जसले स्ल्याश मार्फत दिन-महिना-वर्ष दिइएको ढाँचा अनुसार मिति रूपान्तरण गर्दछ।

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

चरण 2. अनुरोध कार्यान्वयन गर्नुहोस्

अब हामी प्रकार्य प्रयोग गर्छौं वेब सेवा (WEBSERVICE) उत्पन्न क्वेरी स्ट्रिङलाई मात्र तर्कको रूपमा। जवाफ XML कोडको लामो लाइन हुनेछ (शब्द र्‍याप खोलेर सेल साइज बढाउनु राम्रो हुन्छ यदि तपाईं यसलाई पूर्ण रूपमा हेर्न चाहनुहुन्छ भने):

चरण 3. जवाफ पार्स गर्दै

प्रतिक्रिया डेटाको संरचना बुझ्न सजिलो बनाउनको लागि, अनलाइन XML पार्सरहरू मध्ये एक प्रयोग गर्नु राम्रो हुन्छ (उदाहरणका लागि, http://xpather.com/ वा https://jsonformatter.org/xml-parser), जसले XML कोडलाई भिजुअल रूपमा ढाँचा गर्न सक्छ, यसमा इन्डेन्टहरू थपेर र रङसँग सिन्ट्याक्स हाइलाइट गर्न सक्छ। त्यसपछि सबै कुरा धेरै स्पष्ट हुनेछ:

अब तपाइँ स्पष्ट रूपमा देख्न सक्नुहुन्छ कि पाठ्यक्रम मानहरू हाम्रो ट्यागहरूद्वारा फ्रेम गरिएका छन् ..., र मितिहरू विशेषताहरू हुन् मिति ट्यागहरूमा .

तिनीहरूलाई निकाल्नको लागि, पानामा खाली कक्षहरू (वा धेरै - मार्जिनसँग गरियो भने) को स्तम्भ चयन गर्नुहोस् (किनकि १०-दिनको मिति अन्तराल सेट गरिएको थियो) र सूत्र पट्टीमा प्रकार्य प्रविष्ट गर्नुहोस्। FILTER.XML (फिल्टरXML):

यहाँ, पहिलो तर्क सर्भर प्रतिक्रिया (B8) को साथ सेलको लिङ्क हो, र दोस्रो XPath मा क्वेरी स्ट्रिङ हो, एक विशेष भाषा जुन आवश्यक XML कोड टुक्राहरू पहुँच गर्न र तिनीहरूलाई निकाल्न प्रयोग गर्न सकिन्छ। तपाईले XPath भाषाको बारेमा थप पढ्न सक्नुहुन्छ, उदाहरणका लागि, यहाँ।

यो महत्त्वपूर्ण छ कि सूत्र प्रविष्ट गरेपछि, थिच्नुहोस् प्रविष्ट गर्नुहोस्, र किबोर्ड सर्टकट Ctrl+सिफ्ट+प्रविष्ट गर्नुहोस्, अर्थात् यसलाई एरे सूत्रको रूपमा प्रविष्ट गर्नुहोस् (यसको वरिपरि घुमाउरो कोष्ठकहरू स्वचालित रूपमा थपिनेछन्)। यदि तपाइँसँग Excel मा गतिशील arrays को लागि समर्थन संग Office 365 को नवीनतम संस्करण छ भने, एक सरल प्रविष्ट गर्नुहोस्, र तपाईंले पहिले नै खाली कक्षहरू चयन गर्न आवश्यक पर्दैन - प्रकार्यले आफैंले जति धेरै कक्षहरू लिनेछ।

मितिहरू निकाल्नको लागि, हामी त्यसै गर्नेछौं - हामी छेउछाउको स्तम्भमा धेरै खाली कक्षहरू चयन गर्नेछौं र एउटै प्रकार्य प्रयोग गर्नेछौं, तर फरक XPath क्वेरीको साथ, रेकर्ड ट्यागहरूबाट मिति विशेषताहरूको सबै मानहरू प्राप्त गर्न:

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

अब भविष्यमा, मूल कक्ष B2 र B3 मा मितिहरू परिवर्तन गर्दा वा सेल B3 को ड्रप-डाउन सूचीमा फरक मुद्रा छनोट गर्दा, नयाँ डेटाको लागि केन्द्रीय बैंक सर्भरलाई सन्दर्भ गर्दै, हाम्रो क्वेरी स्वचालित रूपमा अद्यावधिक हुनेछ। म्यानुअल रूपमा अद्यावधिक गर्न बलियो बनाउन, तपाइँ थप रूपमा किबोर्ड सर्टकट प्रयोग गर्न सक्नुहुन्छ Ctrl+alt+F9.

• पावर क्वेरी मार्फत एक्सेलमा बिटकोइन दर आयात गर्नुहोस्
• एक्सेलको पुरानो संस्करणहरूमा इन्टरनेटबाट विनिमय दरहरू आयात गर्नुहोस्