Большинство работников финансовой сферы воспринимают Excel как красивый калькулятор. Мы решили помочь вам развить навыки использования этого важного инструмента, сделать из него настоящего помощника, а не просто хранителя данных. Предлагаем вам посмотреть, как можно подгрузить данные из сети Интернет непосредственно в таблицу Excel и создать функцию выбора курса валют на необходимую дату.
Немного больше 60 лет прошло с момента изобретения первого компьютера и около 50 лет с первых шагов по созданию интернета. Сейчас практически у каждого в кармане лежит мини-компьютер (смартфон) с доступом в сеть, уже начали появляться машины без водителя, некоторые и вовсе собираются колонизировать Марс. При этом большинство наших слушателей (работников финансовой сферы) до сих пор воспринимают Excel как красивый калькулятор. Мы решили помочь вам развить навыки использования этого важного инструмента, сделать из него настоящего помощника, а не просто хранителя данных.
Сегодня предлагаем вам посмотреть, как можно подгрузить данные из сети Интернет непосредственно в таблицу Excel и обработать их (создать функцию выбора курса валют на необходимую дату).
Если вам регулярно необходим курс Центрального Банка РФ, то функция «Загрузка курса с сайта ЦБ» позволит сэкономить много времени.
Разбиваем задачу на две части:
(1) Автоматизируем загрузку курсов валют за необходимый период с сайта Центрального Банка России
(2) Пишем небольшой скрипт, который создаст «Пользовательскую функцию» для выбора курса на дату и предоставит его вставку в ячейку
Хотите уметь делать так сами? Это не сложно...
Ознакомьтесь со следующими курсами по Excel от HOCK Training:
Для решения данной задачи воспользуемся стандартным малоизвестным функционалом Excel.
|
* оговорка о совместимости версий ⚠ Данный функционал приводится для версий MS Office до 2020 года выпуска. Отличие новых версий офиса состоит в том, что путь к странице с данными указывается строкой (копи-пастом из браузера), а не через встроенный браузер MS Excel. Остальные действия по разработке функции аналогичны приведённым в данной статье. Подробный разбор алгоритма действий в более новых версиях офиса приведен в дистанционном курсе «Программирование в Excel». |
(1.1) Линейка «Данные» --> «Получение внешних данных» --> «Из Интернета»:
 |
(1.2) В открывшемся внутреннем браузере Excel переходим на сайт ЦБ РФ и переходим на страницу с курсами:
 |
(1.3) Далее необходимо указать период для загрузки:
 |
(1.4) После получения данных нажмите кнопку «Импорт» и данные загрузятся в лист Excel
ВАЖНО!
Не забудьте перед этим поставить галочку в небольшом квадратике вверху страницы. На рисунке выше он зеленого цвета:
При этом в Excel будут загружены данные:
 |
(1.5) Если все данные действия записать в виде макроса, то получим следующий скрипт в Visual Basic:
 |
Обратите внимание на выделенные элементы кода. Если вносить в них изменения и запускать код на выполнение - будем получать курсы за нужный период. И не придется каждый раз повторять операции описанные выше в пунктах (1.1-1.4).
Следующий шаг - написание скрипта, который будет выбирать из подгруженных данных курс на требуемую дату и будет возвращать его при помощи функции в ячейку.
Используем возможности создания «Пользовательских функций» в редакторе Visual Basic:
(2.1) Создаем процедуру для обработки данных. Проверяем корректность работы программы на процедуре. Найденное значение курса записывается в переменную kurs (см листинг ниже).
 |
(2.2) Меняем процедуру на функцию:
 |
(2.3) Вставляем в ячейку на листе с данными нашу функцию (ищите её в категории «Определенные пользователем»):
 |
 |
(2.4) «Растягиваем» ячейку с формулой на необходимый нам диапазон стандартным образом:
 |
Работает 😉
{subscribeajaxProgram}
.png20220525182402925.png){kind=small}
