Как в экселе сделать валюту

Обновлено: 04.07.2024

Большинство работников финансовой сферы воспринимают Excel как красивый калькулятор. Мы решили помочь вам развить навыки использования этого важного инструмента, сделать из него настоящего помощника, а не просто хранителя данных. Предлагаем вам посмотреть, как можно подгрузить данные из сети Интернет непосредственно в таблицу Excel и создать функцию выбора курса валют на необходимую дату.

Немного больше 60 лет прошло с момента изобретения первого компьютера и около 50 лет с первых шагов по созданию интернета. Сейчас практически у каждого в кармане лежит мини-компьютер (смартфон) с доступом в сеть, уже начали появляться машины без водителя, некоторые и вовсе собираются колонизировать Марс. При этом большинство наших слушателей (работников финансовой сферы) до сих пор воспринимают Excel как красивый калькулятор. Мы решили помочь вам развить навыки использования этого важного инструмента, сделать из него настоящего помощника, а не просто хранителя данных.

Сегодня предлагаем вам посмотреть, как можно подгрузить данные из сети Интернет непосредственно в таблицу Excel и обработать их (создать функцию выбора курса валют на необходимую дату).

Для чего может понадобиться эта возможность?

Если вам регулярно необходим курс Центрального Банка РФ, то функция «Загрузка курса с сайта ЦБ» позволит сэкономить много времени.

Разбиваем задачу на две части:

(1) Автоматизируем загрузку курсов валют за необходимый период с сайта Центрального Банка России

(2) Пишем небольшой скрипт, который создаст «Пользовательскую функцию» для выбора курса на дату и предоставит его вставку в ячейку

(1) Загрузка курсов

Для решения данной задачи воспользуемся стандартным малоизвестным функционалом Excel.

* оговорка о совместимости версий

Данный функционал приводится для версий MS Office до 2020 года выпуска. Разбор новой версии офиса приведен в Дистанционном курсе «Программирование в Excel».

(1.1) Линейка «Данные» --> «Получение внешних данных» --> «Из Интернета»:

(1.2) В открывшемся браузере переходим на сайт ЦБ РФ и переходим на страницу с курсами:

(1.3) Далее необходимо указать период для загрузки:

(1.4) После получения данных нажмите кнопку «Импорт» и данные загрузятся в лист Excel

ВАЖНО! Не забудьте перед этим поставить галочку в небольшом квадратике вверху страницы. На рисунке выше он зеленого цвета . При этом в Excel будут загружены данные:

(1.5) Если все данные действия записать в виде макроса, то получим следующий скрипт в Visual Basic:

Обратите внимание на выделенные элементы кода. Если вносить в них изменения и запускать код на выполнение - будем получать курсы за нужный период. И не придется каждый раз повторять операции описанные выше в пунктах (1.1-1.4).

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

(2) Создание функции «Выбор курса на дату».

Используем возможности создания «Пользовательских функций» в редакторе Visual Basic:

(2.1) Создаем процедуру для обработки данных. Проверяем корректность работы программы на процедуре. Найденное значение курса записывается в переменную kurs (см листинг ниже).

(2.2) Меняем процедуру на функцию:

(2.3) Вставляем в ячейку на листе с данными нашу функцию (ищите её в категории «Определенные пользователем»):

(2.4) «Растягиваем» ячейку с формулой на необходимый нам диапазон стандартным образом:

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

(Сравнение этих двух форматов см. в разделе Денежный и финансовый форматы ниже.)

Выделите ячейки, которые вы хотите отформатировать, а затем в группе Число на вкладке Главная щелкните стрелку вниз в поле Числовой формат.

Выберите Денежный или Финансовый.

Excel в Интернете к выбранным ячейкам применяется символ валюты по умолчанию и соответствующие десятичные знаки.

Денежный и финансовый форматы

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

десятичные знаки выровнены в столбце;

Рядом с первой цифрой в ячейке появится символ валюты. (Это не соответствует другим символам валюты в столбце.

как десятичные знаки, так и обозначения денежной единицы выровнены в столбце;

ячейки, содержащие только нули, помечаются дефисом;

отрицательные числа отображаются в круглых скобках.

Изменение символа валюты по умолчанию

Вы пока не можете изменить символ рубля по умолчанию в Excel в Интернете. но если у вас есть настольное приложение Excel, вы можете нажать кнопку "Открыть в Excel" и следуйте инструкциям, чтобы указать нужное значение валюты по умолчанию. После применения символа валюты сохраните и снова откроете его в Excel в Интернете.

Я уже неоднократно разбирал способы импорта данных в Excel из интернета с последующим автоматическим обновлением. В частности:

В старых версиях Excel 2007-2013 это можно было сделать с помощью прямого веб-запроса.
Начиная с 2010 года это можно очень удобно делать с помощью надстройки Power Query.

К этим способам в последних версиях Microsoft Excel теперь можно добавить ещё один - импорт данных из интернета в формате XML с помощью встроенных функций.

Начиная с версии 2013 в Excel появились две функции для прямой загрузки XML-данных из интернета в ячейки листа: ВЕБСЛУЖБА (WEBSERVICE) и ФИЛЬТР.XML (FILTERXML) . Работают они в паре - сначала функция ВЕБСЛУЖБА выполняет запрос к нужному сайту и возвращает его ответ в формате XML, а затем с помощью функции ФИЛЬТР.XML мы «разбираем» этот ответ на составляющие, извлекая из него нужные нам данные.

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

В жёлтых ячейках находятся даты начала и окончания интересующего нас периода.
В синей сделан выпадающий список валют при помощи команды Данные - Проверка - Список (Data - Validation - List) .
В зеленых ячейках мы будем использовать наши функции, чтобы создать строку запроса и получить ответ сервера.
Таблица справа - справочник по кодам валют (потребуется нам чуть позже).

Шаг 1. Формируем строку запроса

Как видно из примера, строка запроса должна содержать даты начала (date_req1) и окончания (date_req2) интересующего нас периода и код валюты (VAL_NM_RQ), курс которой мы хотим получить. Коды основных валют вы можете найти в таблице ниже:

Представьте себе ситуацию, в которой вам представлен лист Excel, в котором несколько денежных значений помещены в определенный столбец. Возможно, вам потребуется преобразовать валюту для всех этих значений в столбце и отобразить их в отдельном столбце. Это может быть важно для финансовых организаций и предприятий, занимающихся импортом и экспортом. Хотя доступно множество бесплатных и платных инструментов, которые могут помочь в прямом преобразовании валют по столбцам, мы обсудим способ конвертировать валюты в Майкрософт Эксель используя базовую формулу из этой статьи.

Конвертируйте валюты в Excel

Вы можете конвертировать такие валюты, как доллар, евро, фунт, индийская рупия и т. Д., В Microsoft Excel без использования конвертера валют. Используйте эту формулу, чтобы выполнить работу.

Чтобы конвертировать одну валюту в другую, вам нужно будет использовать 3 столбца на листе Excel. Первый столбец для целевой валюты, второй для обменного курса и третий для конвертируемой валюты.

Синтаксис формулы, которая будет использоваться для конвертации валюты, выглядит следующим образом:

По сути, эта формула предназначена для простого умножения целевой валюты на обменный курс.

Как найти обменный курс между двумя валютами

Например. Предположим следующий случай. Предположим, у вас есть столбец значений в долларах со значениями, помещенными от ячейки A3 до A7. Вам нужны соответствующие значения в евро в столбце C, начиная с ячейки C3 до C7.

Сначала найдите обменный курс с помощью конвертера валют в Bing. Напишите курс обмена в ячейке B3.

Теперь напишите формулу = A3 * $ B $ 3 в ячейке C3 и нажмите Enter.

Щелкните в любом месте за пределами ячейки C3, а затем вернитесь в ячейку C3, чтобы выделить кнопку «Заливка». Потяните ячейку C3 вниз до ячейки C7, и она отобразит все значения в евро последовательно.

Одной из трудностей этого метода будет добавление денежной единицы. Однако это лучше, чем покупать платный инструмент специально для этой цели.

Microsoft Excel не включает встроенные инструменты для конвертации валюты. Тем не менее вы можете использовать внешний источник данных для получения обновляемых курсов конвертации. Затем используя обычное умножение будет выполняться автоматическая конвертация из одной валюты в другую. Далее показано, как это сделать.

Добавление внешнего источника данных в Excel

Внешний источник данных — лучший способ получить актуальные курсы валют. Вы можете использовать эти данные для преобразования из одной валюты в другую в Excel. Процесс аналогичен конвертации валюты в Google Sheets.

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

Найдите интересующую вас валюту, например я взял «U.S. Dollar (USD) XML Feed», то есть курс американского доллара к разным валютам, в том числе и к рублю.

После того, как вы найдёте тот, который хотите использовать, откройте электронную таблицу Excel. В Excel 2019 или Office 365 выберите Данные → Получить данные → Из файла → Из XML. В более старых версиях Excel вместо этого выберите Данные → Получить внешние данные → Из других источников → Из импорта данных XML.

В нашем примере мы используем фид данных FloatRates USD, поэтому мы импортируем их в Excel.

В окне «Импорт данных» вставьте URL-адрес на ваш XML-канал данных в поле «Имя файла», а затем нажмите «Открыть».

Если у вас Office 2019 или 365, вы увидите предварительный просмотр того, как данные будут импортированы. Если вы используете данные FloatRates, вам необходимо преобразовать их в редакторе Power Power Query для их использования.

Для этого нажмите «Преобразовать данные».

Откроется Редактор Power Query. Прокрутите до столбца «item» и дважды щёлкните «Table», чтобы загрузить актуальные курсы валют.

Предварительный просмотр Редактора Power Query обновляет и показывает данные валюты FloatRates. Нажмите «Закрыть и загрузить» в верхнем левом углу, чтобы добавить данные в электронную таблицу.

Импортируемые вами данные появятся в новом рабочем листе, к которому вы теперь можете обращаться, когда вам нужно конвертировать валюту.

Большинство внешних источников данных обновляются ежечасно, но FloatRates обновляется только каждые 12 часов. Если вы хотите обновить данные вручную, нажмите «Данные» → «Обновить все».

Конвертация валюты в Microsoft Excel

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

Перейдите на лист с вашими импортированными курсами валют. Если вы используете данные FloatRates, посмотрите обменные курсы в столбце «exchangeRate». Обратите внимание на ячейку, которая содержит курс валюты, в которую вы хотите конвертировать.

Используя наши данные по доллару США FloatRates, мы видим, что для конвертации долларов США в российские рубли нам необходимо использовать курс рубля в ячейке I110.

Вернитесь к основному рабочему листу и введите цену в долларах США, из которой вы хотите конвертировать. Во второй ячейке используйте формулу

и замените «C4» на ячейку, содержащую вашу цену в долларах США.

В приведённом выше примере в столбце C перечислены суммы в долларах США. В столбце D перечислены посчитаны суммы в рублях по актуальному курсу конвертации, который загрузился автоматически.

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

Вы также можете использовать ручной курс вместо внешнего источника данных для конвертации курсов валют. Просто установите обменный курс вручную в ячейке (в нашем примере - ячейка D4), а цену - в другой (ячейка C4). Та же формула умножения конвертирует вашу валюту. Однако, если вы не используете внешний источник данных, вам придется обновить курс вручную, чтобы увидеть правильную цену на текущий момент.

Читайте также: