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

त्यानंतरच्या स्वयंचलित अद्ययावतीकरणासह मी इंटरनेटवरून Excel मध्ये डेटा आयात करण्याच्या मार्गांचे वारंवार विश्लेषण केले आहे. विशेषतः:

• एक्सेल 2007-2013 च्या जुन्या आवृत्त्यांमध्ये, हे थेट वेब विनंतीसह केले जाऊ शकते.
• 2010 पासून, हे पॉवर क्वेरी अॅड-इनसह अतिशय सोयीस्करपणे केले जाऊ शकते.
  

मायक्रोसॉफ्ट एक्सेलच्या नवीनतम आवृत्त्यांमधील या पद्धतींमध्ये, तुम्ही आता आणखी एक जोडू शकता - अंगभूत कार्ये वापरून XML स्वरूपात इंटरनेटवरून डेटा आयात करणे.

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

आवृत्ती 2013 पासून, वर्कशीट सेलमध्ये इंटरनेटवरून थेट XML डेटा लोड करण्यासाठी Excel मध्ये दोन कार्ये आहेत: वेब सेवा (वेबसेवा) и 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 पहा

आता आपण शीटवरील सेलमध्ये क्वेरी स्ट्रिंग तयार करू:

• मजकूर संयोजन ऑपरेटर (&) ते एकत्र ठेवण्यासाठी;
  
• वैशिष्ट्ये व्हीपीआर (VLOOKUP)निर्देशिकेत आवश्यक असलेल्या चलनाचा कोड शोधण्यासाठी;
  
• वैशिष्ट्ये TEXT (पाठ), जे स्लॅशद्वारे दिलेल्या पॅटर्नच्या दिवस-महिना-वर्षानुसार तारखेला रूपांतरित करते.

="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+शिफ्ट+प्रविष्ट करा, म्हणजे ते अॅरे फॉर्म्युला म्हणून एंटर करा (त्याभोवती कुरळे ब्रेसेस आपोआप जोडले जातील). तुमच्याकडे Excel मधील डायनॅमिक अॅरेसाठी समर्थन असलेली Office 365 ची नवीनतम आवृत्ती असल्यास, एक साधी प्रविष्ट करा, आणि तुम्हाला रिकाम्या सेलची आगाऊ निवड करण्याची गरज नाही – फंक्शन स्वतःच आवश्यक तितक्या सेल घेईल.

तारखा काढण्यासाठी, आम्ही तेच करू - आम्ही जवळच्या स्तंभातील अनेक रिक्त सेल निवडू आणि तेच फंक्शन वापरू, परंतु वेगळ्या XPath क्वेरीसह, रेकॉर्ड टॅगमधून तारीख गुणधर्मांची सर्व मूल्ये मिळवण्यासाठी:

=FILTER.XML(B8;”//रेकॉर्ड/@तारीख”)

आता भविष्यात, मूळ सेल B2 आणि B3 मधील तारखा बदलताना किंवा सेल B3 च्या ड्रॉप-डाउन सूचीमध्ये भिन्न चलन निवडताना, नवीन डेटासाठी सेंट्रल बँकेच्या सर्व्हरचा संदर्भ देऊन आमची क्वेरी स्वयंचलितपणे अद्यतनित केली जाईल. व्यक्तिचलितपणे अद्यतनाची सक्ती करण्यासाठी, तुम्ही कीबोर्ड शॉर्टकट देखील वापरू शकता Ctrl+alt+F9.

• पॉवर क्वेरीद्वारे एक्सेलमध्ये बिटकॉइन दर आयात करा
• एक्सेलच्या जुन्या आवृत्त्यांमध्ये इंटरनेटवरून विनिमय दर आयात करा