Image Image Image Image Image Image Image Image Image Image

Simplenomics | Март 28, 2017

Scroll to top

Top

No Comments

Макрос Excel для преобразования таблиц в вид, пригодный для баз данных

Одной из неприятных проблем при обработке количественных данных является преобразование их в нужной формат и нужный вид. Как правило, данные содержатся не в том виде, в котором удобно их анализировать. Для небольшого объема это можно делать вручную. Причем часто лучше так и поступить — разработка какого-то специального инструмента автоматизации для данных, которые могут легко измениться или их не так много, возможно, не имеет смысл. И проще все «перебить руками».
Но если речь идет о сотнях или тысячах показателей или подобная задача встает с определенной регулярностью, стоит подумать над автоматизацией процесса. 
Приведу простой пример. Исходный файл в формате Excel выглядит подобным образом: 



                                             

Данные (это цены на различные корпоративные облигации, скачанные из Bloomberg) сгруппированы на одном листе в группы по три строки:
  1. идентификатор облигации;
  2. дата;
  3. цена. 
Для каждой бумаги (то есть  каждой группы из трех столбцов), ее длина отличается произвольным образом. Это происходит потому, что разные бумаги начали торговаться в разные периоды времени, для некоторых периодов есть пропуски и так далее.  
Для того, чтобы использовать их в собственной базе данных для дальнейшей обработки, необходимо свести их в «нормальный» вид — в одну таблицу по три столбца, в которой бумаги с соответствующими им идентификаторами идут «сверху вниз». 
В принципе, это достаточно часто встречающая задача, когда из обычного экселелевского вида таблицы нужно преобразовать данные для анализа в формате сводных таблиц или обработки в базе данных. В данном случае ее можно довольно легко решить и средствами СУБД, но мы воспользовались средствами Excel, так как для наших целей мы используем загрузку данных из Bloomberg в Excel, а не сразу в СУБД. 
Для того, чтобы решить эту задачу я написал небольшой макрос, который состоит из процедуры и функции. Процедура «проходит» по всему листу с данными, до тех пор пока находит что-то в первой строчке (в нашем случае это формула в каждом третьем столбце) и копирует их на новый лист. Функция же находит последнюю ненулевую строку в данном столбце и возвращает ее номер. 
Исходные коды, если кому-то пригодятся для своих целей: