განახლებულია გაცვლითი კურსი 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 მონაცემების უშუალოდ ჩატვირთვისთვის სამუშაო ფურცელ უჯრედებში: ვებ სერვისი (WEBSERVICE) и FILTER.XML (FILTERXML). ისინი მუშაობენ წყვილებში - ჯერ ფუნქცია ვებ სერვისი ასრულებს მოთხოვნას სასურველ საიტზე და აბრუნებს მის პასუხს XML ფორმატში, შემდეგ კი ფუნქციის გამოყენებით FILTER.XML ჩვენ ამ პასუხს კომპონენტებად „ვანაწილებთ“, მისგან საჭირო მონაცემებს ვიღებთ.

მოდით შევხედოთ ამ ფუნქციების ფუნქციონირებას კლასიკური მაგალითის გამოყენებით - ჩვენი ქვეყნის ცენტრალური ბანკის ვებგვერდიდან ნებისმიერი ვალუტის კურსის იმპორტირება, რომელიც ჩვენ გვჭირდება მოცემული თარიღის ინტერვალისთვის. ჩვენ გამოვიყენებთ შემდეგ კონსტრუქციას, როგორც ცარიელი:

აქ:

• ყვითელი უჯრედები შეიცავს ჩვენთვის საინტერესო პერიოდის დაწყების და დასრულების თარიღებს.
• ლურჯს აქვს ვალუტების ჩამოსაშლელი სია ბრძანების გამოყენებით მონაცემები – ვალიდაცია – სია (მონაცემები — ვალიდაცია — სია).
• მწვანე უჯრედებში ჩვენ გამოვიყენებთ ჩვენს ფუნქციებს შეკითხვის სტრიქონის შესაქმნელად და სერვერის პასუხის მისაღებად.
• ცხრილი მარჯვნივ არის მინიშნება ვალუტის კოდების შესახებ (ეს ცოტა მოგვიანებით დაგვჭირდება).
  

წავედით!

ნაბიჯი 1. შეკითხვის სტრიქონის ფორმირება

საიტიდან საჭირო ინფორმაციის მისაღებად საჭიროა სწორად იკითხოთ. ჩვენ მივდივართ www.cbr.ru-ზე და ვხსნით ბმულს მთავარი გვერდის ქვედაბოლოში' ტექნიკური რესურსები - მონაცემთა მიღება XML-ის გამოყენებით (http://cbr.ru/development/SXML/). ჩვენ გადავდივართ ოდნავ ქვემოთ და მეორე მაგალითში (მაგალითი 2) იქნება ის, რაც გვჭირდება - ვალუტის კურსების მიღება მოცემული თარიღის ინტერვალისთვის:

როგორც მაგალითიდან ხედავთ, შეკითხვის სტრიქონი უნდა შეიცავდეს დაწყების თარიღებს (თარიღი_req1) და დაბოლოებები (თარიღი_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 კოდის ვიზუალური ფორმატირება, მასში ჩაღრმავების დამატება და სინტაქსის ფერით ხაზგასმა. მაშინ ყველაფერი გაცილებით ნათელი გახდება:

ახლა თქვენ ნათლად ხედავთ, რომ კურსის მნიშვნელობები ჩარჩოშია ჩვენი ტეგებით ...და თარიღები ატრიბუტებია თარიღი ტეგებში .

მათი ამოსაღებად, ფურცელზე აირჩიეთ ათი (ან მეტი – თუ ეს კეთდება ზღვრით) ცარიელი უჯრედებისგან შემდგარი სვეტი (რადგან დაყენებული იყო თარიღის 10-დღიანი ინტერვალი) და შეიყვანეთ ფუნქცია ფორმულების ზოლში. FILTER.XML (ფილტრიXML):

აქ, პირველი არგუმენტი არის უჯრედის ბმული სერვერის პასუხით (B8), ხოლო მეორე არის შეკითხვის სტრიქონი XPath-ში, სპეციალურ ენაზე, რომელიც შეიძლება გამოყენებულ იქნას XML კოდის აუცილებელ ფრაგმენტებზე წვდომისთვის და მათი ამოსაღებად. შეგიძლიათ მეტი წაიკითხოთ XPath ენის შესახებ, მაგალითად, აქ.

მნიშვნელოვანია, რომ ფორმულის შეყვანის შემდეგ არ დააჭიროთ შეიყვანეთდა კლავიატურის მალსახმობი Ctrl+გადაიტანოს+შეიყვანეთ, ანუ შეიყვანეთ მასივის ფორმულის სახით (მის გარშემო ხვეული ბრეკეტები ავტომატურად დაემატება). თუ თქვენ გაქვთ Office 365-ის უახლესი ვერსია Excel-ში დინამიური მასივების მხარდაჭერით, მაშინ მარტივი შეიყვანეთდა თქვენ არ გჭირდებათ წინასწარ ცარიელი უჯრედების არჩევა – თავად ფუნქცია მიიღებს იმდენ უჯრედს, რამდენიც სჭირდება.

თარიღების ამოსაღებად ჩვენ იგივეს გავაკეთებთ - ავარჩევთ რამდენიმე ცარიელ უჯრედს მიმდებარე სვეტში და გამოვიყენებთ იგივე ფუნქციას, მაგრამ განსხვავებული XPath მოთხოვნით, რომ მივიღოთ თარიღის ატრიბუტების ყველა მნიშვნელობა ჩანაწერის ტეგებიდან:

=FILTER.XML(B8;”//ჩანაწერი/@თარიღი”)

ახლა მომავალში, ორიგინალური B2 და B3 უჯრედების თარიღების შეცვლისას ან B3 უჯრედის ჩამოსაშლელ სიაში სხვა ვალუტის არჩევისას, ჩვენი მოთხოვნა ავტომატურად განახლდება, ახალი მონაცემებისთვის ცენტრალური ბანკის სერვერზე მითითებით. ხელით განახლების იძულებით, შეგიძლიათ დამატებით გამოიყენოთ კლავიატურის მალსახმობი Ctrl+Alt+F9.

• ბიტკოინის კურსის იმპორტი Excel-ში Power Query-ის საშუალებით
• გაცვლითი კურსის იმპორტი ინტერნეტიდან Excel-ის ძველ ვერსიებში