Імпарт курсаў валют з Інтэрнэту

Імпарт курсу дадзенай валюты з Інтэрнэту з аўтаматычным абнаўленнем - вельмі частая задача для многіх карыстальнікаў Microsoft Excel. Уявіце, што ў вас ёсць прайс-ліст, які трэба кожную раніцу пералічваць па курсе. Або бюджэт праекта. Або кошт дамовы, якую трэба разлічваць па курсе даляра на дату заключэння дамовы.

У такіх сітуацыях вы можаце вырашыць праблему па-рознаму - усё залежыць ад таго, якая версія Excel ў вас усталяваная і якія дапаўненні знаходзяцца па-над ім.

Спосаб 1: просты вэб-запыт для бягучага абменнага курсу

Гэты спосаб падыдзе тым, у каго на кампутары яшчэ стаяць старыя версіі Microsoft Office 2003-2007. Ён не выкарыстоўвае ніякіх старонніх дапаўненняў або макрасаў і працуе толькі з убудаванымі функцыямі.

націсніце кнопку З Інтэрнэту (Інтэрнэт) таб Дата (Дата). У якое з'явілася акне ў радку Адрас (Адрас) увядзіце URL сайта, з якога будзе браць інфармацыю (напрыклад, http://www.finmarket.ru/currency/rates/) і націсніце клавішу Уводзіць.

Пры загрузцы старонкі на табліцах, якія Excel можа імпартаваць, з'явяцца чорныя і жоўтыя стрэлкі. Пстрычка па такой стрэлцы пазначае табліцу для імпарту.

Калі ўсе неабходныя табліцы адзначаны, націсніце кнопку Імпартаваць (Імпарт) у ніжняй частцы акна. Праз некаторы час, неабходны для загрузкі дадзеных, змесціва адзначаных табліц з'явіцца ў вочках на аркушы:

Для дадатковай налады вы можаце пстрыкнуць правай кнопкай мышы любую з гэтых вочак і выбраць каманду з кантэкстнага меню. Уласцівасці дыяпазону (Уласцівасці дыяпазону даных).У гэтым дыялогавым акне пры жаданні можна наладзіць частату абнаўлення і іншыя параметры:

Каціроўкі акцый, паколькі яны змяняюцца кожныя некалькі хвілін, вы можаце абнаўляць часцей (сцяжок Абнаўляць кожныя N хв.), але курсы валют, у большасці выпадкаў, дастаткова абнаўляць адзін раз у дзень (сцяжок Абнаўленне пры адкрытым файле).

Звярніце ўвагу, што ўвесь імпартаваны дыяпазон даных разглядаецца ў Excel як адзінае цэлае і яму даецца ўласнае імя, якое можна ўбачыць у дыспетчары імёнаў на ўкладцы формула (Формулы — Менеджэр імёнаў).

Спосаб 2: Параметрычны вэб-запыт, каб атрымаць абменны курс для зададзенага дыяпазону дат

Гэты метад з'яўляецца некалькі мадэрнізаваным першым варыянтам і дае карыстальніку магчымасць атрымаць курс патрэбнай валюты не толькі на бягучы дзень, але і на любую іншую цікавую дату або інтэрвал дат. Для гэтага наш вэб-запыт неабходна ператварыць у параметрычны, гэта значыць дадаць у яго два ўдакладняючых параметру (код патрэбнай нам валюты і бягучую дату). Для гэтага робім наступнае:

1. Ствараем вэб-запыт (гл. спосаб 1) на старонку сайта ЦБ Нашай краіны з архівам курсаў: http://cbr.ru/currency_base/dynamics.aspx

2. У форме злева абярыце патрэбную валюту і ўсталюйце даты пачатку і заканчэння:

3. Націсніце кнопку Каб атрымаць дадзеныя і праз пару секунд мы бачым табліцу з патрэбнымі нам значэннямі курсу для дадзенага інтэрвалу дат. Пракруціце атрыманую табліцу да канца ўніз і адзначце яе для імпарту, націснуўшы на чорна-жоўтую стрэлку ў левым ніжнім куце вэб-старонкі (толькі не пытайцеся, чаму гэтая стрэлка знаходзіцца там, а не побач з табліцай – гэта пытанне да дызайнераў сайта).

Цяпер у правым верхнім куце акна шукаем кнопку з дыскетай Захаваць запыт (Захаваць запыт) і захавайце файл з параметрамі нашага запыту ў любую прыдатную тэчку пад любым зручным імем – напрыклад, у Мае дакументы пад назвай кбр. iqy.  Пасля гэтага акно вэб-запыту і ўвесь Excel можна пакуль закрыць.

4. Адкрыйце папку, у якой вы захавалі запыт, і знайдзіце файл запыту кбр. iqy, затым пстрыкніце па ім правай кнопкай мышы – Адкрыць з дапамогай – Нататнік (або выберыце яго са спісу - звычайна гэта файл Notepad.exe з папкі C: Windows). Пасля адкрыцця файла запыту ў нататніку вы павінны ўбачыць нешта накшталт гэтага:

Самае каштоўнае тут - радок з адрасам і параметрамі запыту ў ёй, якія мы падставім - код патрэбнай нам валюты (выдзелена чырвоным) і дата заканчэння, якую заменім на сённяшнюю (выдзелена сіні). Уважліва адрэдагуйце радок, каб атрымаць наступнае:

http://cbr.ru/currency_base/dynamics.aspx?VAL_NM_RQ=[«Код валюты»]&date_req1=01.01.2000&r1=1&date_req2=[«Дата»]&rt=1&рэжым=1

Усё астатняе пакіньце як ёсць, захавайце і зачыніце файл.

5. Ствараем новую кнігу ў Excel, адкрываем ліст, куды хочам імпартаваць архіў курсаў ЦБ. У любую прыдатную вочка ўвядзіце формулу, якая выдасць нам бягучую дату у тэкставым фармаце для падмены запыту:

= ТЭКСТ(СЁННЯ();”ДД.ММ.ГГГГ”)

або ў англійскай версіі

=ТЭКСТ(СЁННЯ(),»дд.мм.гггг»)

Дзесьці побач ўпісваем код патрэбнай нам валюты з табліцы:

Неабходны код таксама можна паглядзець у радку запыту прама на сайце ЦБ.

6. Загружаем дадзеныя на ліст, выкарыстоўваючы за аснову створаныя вочкі і файл cbr.iqy, г.зн. Дадзеныя – Сувязі – Знайсці іншыя (Дадзеныя — Існуючыя злучэнні). У якое адкрылася акне выбару крыніцы дадзеных знайдзіце і адкрыйце файл кбр. iqy. Перад імпартам Excel удакладніць у нас тры рэчы.

Па-першае, куды імпартаваць табліцу даных:

Па-другое, адкуль атрымаць код валюты (вы можаце паставіць галачку Выкарыстоўвайце гэта значэнне па змаўчанні (Выкарыстоўвайце гэта значэнне/спасылку для будучых абнаўленняў), каб потым кожны раз гэтая ячэйка не ўказвалася пры абнаўленнях і сцяжок Аўтаматычнае абнаўленне пры змене значэння ячэйкі (Абнаўляць аўтаматычна пры змене значэння ячэйкі):

Па-трэцяе, з якой ячэйкі браць канчатковую дату (тут таксама можна паставіць абодва галачкі, каб заўтра пры абнаўленні не ўсталёўваць гэтыя параметры ўручную):

націсніце OK, пачакайце пару секунд і атрымаеце поўны архіў курсу патрэбнай валюты на аркушы:

Як і ў першым спосабе, пстрыкнуўшы правай кнопкай мышы на імпартаваных дадзеных і выбраўшы каманду Уласцівасці дыяпазону (Уласцівасці дыяпазону даных), вы можаце наладзіць частату абнаўлення Пры адкрыцці файла (Абнавіць пры адкрыцці файла). Затым, калі ў вас ёсць доступ да Інтэрнэту, дадзеныя будуць аўтаматычна абнаўляцца кожны дзень, т. Е. Табліца будзе аўтаматычна абнаўляцца новымі дадзенымі.

Прасцей за ўсё атрымаць курс на патрэбную дату з нашай табліцы з дапамогай функцыі ВПР (ВПР) – калі вы з гэтым не знаёмыя, то я настойліва раю вам гэта зрабіць. Па такой формуле, напрыклад, можна выбраць з нашай табліцы курс даляра на 10 студзеня 2000 года:

або на англійскай мове =VLOOKUP(E5,cbr,3,1)

дзе

• E5 – ячэйка, якая змяшчае дадзеную дату
  
• cbr – імя дыяпазону даных (аўтаматычна генеруецца падчас імпарту і звычайна супадае з імем файла запыту)
• 3 – парадкавы нумар слупка ў нашай табліцы, адкуль мы бярэм дадзеныя
• 1 – аргумент, які ўключае прыблізны пошук для функцыі VLOOKUP, каб вы маглі знайсці курсы для тых прамежкавых дат, якія фактычна не прысутнічаюць у слупку A (будзе ўзятая бліжэйшая папярэдняя дата і яе курс). Вы можаце прачытаць больш пра прыблізны пошук з дапамогай функцыі VLOOKUP тут.

• Макрас, каб атрымаць курс даляра на дадзеную дату ў бягучай ячэйцы
• Дадатковая функцыя PLEX для атрымання абменнага курсу долара, еўра, грыўні, фунта стэрлінгаў і г.д. на любую дату
• Увядзіце любы курс валюты на любую дату ў надбудове PLEX