Check-moscow.ru

Финансы и учет
4 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как вставить текущий курс обмена в Excel?

Импорт курсов валюты в Excel

Запуск формулы курс валюты

Один из наиболее эффективных способов вставить обменный курс с сайта Центрального банка в ячейку Excel — использовать макрос-надстройку. После установки VBA-Excel у вас появится новая вкладка в ленте с командой вызова функции Exchange Rate.

Вы можете использовать эту команду для импорта ставок на заданную дату с помощью удобной формы.

Импорт курса валюты в Excel

Выбор даты импорта

В форму встроен календарь для выбора времени импорта курса валюты. По умолчанию выбирается текущая дата.

Вы также можете установить текущую дату, нажав на кнопку Today на календаре

Выбор валюты

Следующий шаг — указать, какой курс валюты вы хотите импортировать. Выберите нужную строку из списка.

Можно выбрать все валюты, перечисленные на сайте Центрального банка. Для удобства наиболее популярные курсы обмена доллара и евро размещены в верхней части списка. Остальные перечислены в алфавитном порядке.

Выбор единицы расчета

Обратите внимание, что не все обменные курсы берутся за единицу. Например, для армянских драм расчет ведется на 100 единиц. Если вы хотите, чтобы курс обмена вставлялся за единицу, установите флажок Включить расчет за 1 валютную единицу в левом нижнем углу.

Тип вставки

Если вы не планируете обновлять курс валюты в дальнейшем, то советую вставлять курс валюты "текстом" тогда Excel не будет обращаться к сайту ЦБ для обновления данных. Для этого нажмите кнопку Вставка текстом.

Если вы хотите периодически обновлять ставку (например, на текущую дату), используйте кнопку Вставить формулу. В этом случае функция ставки будет вставлена в выбранную ячейку с заданными параметрами.

Последовательность шагов

Все снимки экрана сделаны в Excel 2016.

Шаг 1

Поэтому, когда мы заходим на ww.cbr.ru, мы сразу же видим ссылки на курсы валют.

Читайте так же:
Как вставить несколько гиперссылок в ячейку / много ячеек в Excel?

step1

Шаг 2

Попадаем на страницу баз данных валют. Затем переходим к разделу, посвященному динамике данной валюты.

step2

Шаг 3

Введите параметры поиска: тип валюты, даты и вид таблицы:

step3

Шаг 4

В результатах нас интересует только адресная строка, копируем ее в буфер обмена (CTRL-С):

step4

Шаг 5

Открываем новый документ в EXCEL. Переходим на закладку Данные. Далее – Получение внешних данных / Из интернета:

step5

Шаг 6

Загрузился встроенный браузер. В шаге 4 (мы нажали Control-V) мы скопировали ссылку, которая у нас была с предыдущего шага, и снова увидели таблицу с историей курсов валют. Можно перейти от начала работы к таблице без использования браузера, но браузер EXCEL основан на IE, что не очень удобно.

Единственное, что нам нужно здесь сделать, это нажать на кнопку «Сохранить запрос»:

step6

Шаг 7

Параметры WEB-запроса (поэтому он и называется WEB-запросом) сохраняются в файле с расширением .iqy.

step7

Шаг 8

Откройте записанный файл в текстовом редакторе, например, в Блокноте. Найдите даты и код валюты (R01235 для доллара США) в строке запроса:

step8

Шаг 9

Меняем эти значения на переменные и сохраняем изменения. Название переменных произвольное. Синтаксис переменной – [“Name”]:

step9

Шаг 10

Вернитесь в EXCEL и подготовьте значения переменных, которые будут считываться при обращении к сайту Центрального банка. Значение каждой переменной должно быть помещено в отдельную ячейку листа. Популярные коды валют: Доллар США — R01235, Евро — R01239, Юань — R01375. Любой другой код валюты можно узнать, повторно подав заявку на сайте Центрального банка.

Формат даты необходимо поменять на текстовый, т.к. по умолчанию EXCEL дата представляет собой пятизначное число. Делается это при помощи формулы =ТЕКСТ(B3;»ДД.ММ.ГГГГ»). В дальнейшем колонку «Переменные» можно скрыть.

step10

Шаг 11

Снова обращаемся к закладке «Данные», раздел «Существующие подключения». Выбираем «Найти другие …» и загружаем записанный файл в формате .iqy

Читайте так же:
Как вставить пропуск скрытых / отфильтрованных ячеек и строк в Excel?

step11a

step11b

Шаг 12

Вывести данные на новый лист.

step12

Шаг 13

Указываем ячейки, в которых находятся значения переменных. Выбираем «Использовать данное значение по умолчанию» и «Автоматически обновлять при изменении значения ячейки», если есть желание подгружать данные в автоматическом режиме:

step13

Шаг 14

На новом листе формируется таблица с котировками валюты. В принципе эти данные уже можно использовать, но на листе кроме таблицы присутствует много лишнего «мусора».

step14

Шаг 15

Для очистки данных нам поможет Power Query. В EXCEL 2016 раздел Power Query (Скачать и преобразовать) находится по соседству с «Получением данных» (вкладка данные). Нас интересует создание нового запроса из таблицы. При нажатии на «Из таблицы» должна быть активной страница с полученными данными.

step15

Шаг 16

Открывается диалог интерфейса Power Query. Для начала удаляем лишнюю колону. Для этого надо ее выделить и нажать «Удалить столбцы».

step16

Шаг 17

Далее убираем ненужную информацию, расположенную в верхних строках: «Удаление верхних строк». И указываем номер последней строки с мусором (26).

step17

Шаг 18

Теперь вы можете использовать верхнюю строку как заголовок таблицы:

step18

Шаг 19

Выберите столбец Дата и отсортируйте его по убыванию (от новой даты к старой):

step19

Шаг 20

После сортировки становится понятно, что внизу таблицы тоже был «мусор». Чистим его по аналогии с шагом 17. Таблица начинает приобретать рабочий вариант.

Остается только выделить колонку Дата и придать ей правильный формат:

step20

Шаг 21

Последний шаг – указать где должны отображаться очищенные данные:

step21

step21a

Различные форматы новых данных будут показаны на новом листе. Теперь вы можете работать с этой информацией по своему усмотрению.

image98

Еще одним преимуществом Power Query является его обратимость. Вы можете в любой момент вернуться в редактор запросов (Show Queries). В правой части интерфейса находится история редактирования запросов, где вы всегда можете внести изменения и удалить действия.

Читайте так же:
Как закрыть и снова открыть активную книгу?

image99

Для обновления данных в итоговой таблице нужно нажать «Обновить все» в закладке Данные:

image97

С помощью встроенных функций EXCEL (без истории курсов) теперь можно автоматически загружать выбранный курс валют. Ниже приведены некоторые советы по загрузке курсов валют с помощью EXCEL:

Настройки импорта курса валют, валютных пар из интернета в Excel 2019

Excel вообще продвинулся очень хорошо в последние несколько лет. Мои любимые спарклайны, работа с данными — это вообще шедевр. Но наш любимый Эксель стал напоминать Фотошоп, без инструкции уже никуда. Поэтому продолжу. Сейчас получим пару EUR-USD из интернета. А в следующем посте покажу как рассылать автоматические обновления.

Вы можете искать исходные курсы валют или другую информацию в поисковой системе, набрав что-то вроде xml eur usd или Free XML Currency Exchange Rate (Бесплатный XML-курс обмена валют).

Запустите наш Excel (у меня версия 2019 года, мне не нравится версия 365).

Данные -> Из интернета -> Вставляем адрес нашей найденной таблицы в поле URL. Например, http://www.floatrates.com/daily/eur.json

Что касается другой основной валюты, то евро заменяется другой валютой. Например, с долларом США.

Фотографии можно кликать.

Настройки импорта курса валют, валютных пар из интернета в Excel 2019

Мы находимся в Power Query и видим таблицу значений. Я рекомендую вам прочитать руководство по Power Query. На этой же странице представлено множество шаблонов на все случаи жизни.

Настройки импорта курса валют, валютных пар из интернета в Excel 2019

Вы выбрали валюту пары. С правой стороны вы можете увидеть дерево торговли. Вы всегда можете вернуться назад, независимо от того, сколько шагов вы сделали.

Настройки импорта курса валют, валютных пар из интернета в Excel 2019

Я удалю лишние строчки.

Настройки импорта курса валют, валютных пар из интернета в Excel 2019Настройки импорта курса валют, валютных пар из интернета в Excel 2019

Сортировка в соответствии с выбранной ячейкой. Да, это влияет на сортировку.

Настройки импорта курса валют, валютных пар из интернета в Excel 2019

Все сработало как надо: валюта, дата, обменный курс, обратный курс.

Настройки импорта курса валют, валютных пар из интернета в Excel 2019

Но это еще не все. Вам необходимо настроить автоматизацию.

Читайте так же:
Как добавить горизонтальные границы только в определенном диапазоне в Excel?

Настройки импорта курса валют, валютных пар из интернета в Excel 2019

Здесь вы можете выбрать любую конфигурацию для приема данных.

Настройки импорта курса валют, валютных пар из интернета в Excel 2019

Вот как все закончилось.

Настройки импорта курса валют, валютных пар из интернета в Excel 2019

Данные можно копировать из ячеек в любую электронную таблицу или документ. Да, я чуть не забыл, что в Word это работает точно так же. Это очень удобно!

получили пару EUR-USD из интернета

Итак, мы получили из интернета пару EUR-USD в наш документ Excel.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector