Криптонит для чайника.
Знакомьтесь, я чайник! Вообще-то я и правда не особо силен в программировании, но особенно не комфортно себя чувствую пытаясь «что-то» создать на незнакомом мне языке. В моем рассказе «Visual Basic for Applications» и есть тот непривычный для меня язык. На самом деле, все это лирическое отступление, не более чем попытка занизить ваши ожидания.
Моя повесть начинается как типовой рассказ офисного работника. Единственная отличительная черта в том, что офиса у меня нет и работаю я на дому (черт, никого уже этим не удивишь). Но к всеобщему счастью, я такой же брюзга, как и большинство.
Утро понедельника как всегда было отвратительным. Рассеивающаяся дымка и еле заметно срывающийся дождь — малоприятное зрелище. Желание работать отпало еще в воскресенье. Работы как всегда много, ничего не клеится, а тут еще и экселевская база на 300к строк свалилась мне в руки. Ну не то что-бы свалилась, закончила парсится. И как вы понимаете, что структурированной, форматированной и однородной базой там и не пахло, а иначе откуда статье взяться. Так что на выходе я получил громадное множество неоднородной информации, из которой необходимо было выделить то нужное, что требовалось мне для работы, отсекая все остальное.
Экспозиция чайника
Посмотрев на тот ужас и хаос, я еще некоторое время провел в состоянии прокрастинации, бездумно множа энтропию. К слову, excel я знаю достаточно плохо, ну, в смысле, заполнить таблицу значениями могу — и отформатировать лист тоже. Даже удалить дубликаты способен (еще бы в 2007), но не более того. Но, немного придя в себя и осознав, что само собой ничего не образуется, начал искать решение.
Как и любой другой здравомыслящий человек я обратился за помощью на спец форум с тусовкой бухгалтеров и excel’евских гиков. Рекламировать форумы, на которых я просил помощи, не стану, замечу только, что из 5 популярных помочь мне согласились лишь на двух. Ребята если вы это читаете — огромное спасибо!
В общем, оставив свой вопрос, я принялся искать решение самостоятельно.
Завязка чайника
Мои задачи были весьма типичны и просты. В таблице содержалась самая необходимая информация: Название (вместе с типом регистрации компании), Телефон (мобильные и городские вперемешку), Адрес (редкостная муть), Сайт компании (тут на удивление все в порядке), email (маловажная инфа, но все же для галочки — грабанул) и Отрасль/Рубрика. Все это мне необходимо было привести в надлежащий вид, для последующего экспортирования в crm.
Дождавшись долгожданного ответа, принялся применять его на практике. Итог — стало еще хуже. Безусловно, я нефигово утрирую, но все же та формула (кстати, ад адский, а не формула), которую мне предоставил один из отзывчивых форумчанинов, оказалась не совсем что бы жизнеспособной. Свою функцию она, конечно, выполняла, но все это было очень муторно, долго и ужасно. Осознав, что формулы — это не вариант, я решил писать макрос.
И… Создал еще одну слезливую тему на вышеобозначеном форуме (черный пояс по форум-до). По старинке я дождался ответа и в этот раз какой-то небезразличный юзер написал мне достаточно сносный макрос, подходящий под мои нужды. Он умел разделять телефоны, а так же отделять тип предприятия от названия (просто разделить столбцы по последней запятой).
Принцип его работы до нельзя прост. На основе словаря из колонки с номерами при помощи Replace удалялись все лишние знаки: тире, скобоки и запятые. После чего программка перебирала значения и отделяла те, в которых есть обозначенный код оператора. В итоге Номера *конечно* отделялись, но ломалось форматирование и без того не особо эстетичное.
И фиг бы с ним, но была какая-то недосказанность, незавершенность.
Бритва чайника
Сидя вечером в кресле и допивая стакан бурбона… Ага, сидел и пилил очередную халтуру, взятую на фрилансе. К моему стыду я и не вспомню в чем там была суть («Нужно больше монотонной работы» (С) War3). Но самое главное, что, применяя RegExp в поле верификации e-mail’a, меня вдруг осенило, а что если… Да-да, спустя столько бесполезных слов, я наконец-то подошел к самому главному. А что если применить регулярные выражения для жонглирования значениями внутри таблицы?
Пролистав несколько зарубежных сайтов, ничего толкового найдено не было. Конечно, были макросы, добавляющие функциональность формнул на regeex, но это не то. Взяв за основу код макроса формулы, я доработал исходный скрипт и, на удивление, оно заработало. Эврика, прокричал я и принялся допивать бурбон.
Пишем свой VLOOKUP для того, чтобы не зависеть от стандартного (Excel functions)
Триумф чайника
Победный танец прервала ошибка в выполнении моего макроса версии 2.0. К слову сказать, мажорные обновления я выпускал так же часто, как оборачиваясь, проверить, нет ли кого за спиной. Паранойя, знаете ли.
О чем это я? Ах да, в последствии выяснилось, что суть ошибки была в случайно занесенной переменной в кавычки и неправильное количество сабматчей. Исправив сей недочет, принялся допиливать функционал конкретно под свою задачу. Cпустя 2 часа я-за-кончил. Конечный макрос успешно форматировал и отделял телефону по коду оператора (дифференцируя по коду как мобильные, а остальные как городские), разделял название предприятия и тип регистрации, а так же выделял из адреса город компании и заносил его в отдельный столбец.
Ничего особенного, скажете вы? И будете абсолютно правы. Большинство людей, умеющих работать с excel на уровне «продвинутого пользователя», сделали бы ту же операцию за 15 минут при помощи формул и базовых функций форматирования. Но я был счастлив и решил поделится этим счастьем с другими.
Итог, просто итог!
Внизу статьи я разместил подробный код формы, которая позволяет другим менее продвинутым пользователям, как я, применять собственные регулярные выражения к данным содержащимся в таблице. По сути, мой твик (не совсем конечно мой) умеет следующее:
Отделить — отделяет значения по заданной маске в новый столбец, оставляя исходный столбец нетронутым.
Важное замечание: из-за недостатка опыта и знаний мне так и не удалось заставить макрос создать новый пустой столбец справа от редактируемого. Это значит, что соседний справа столбец должен быть пуст, иначе выходные\конечные данные заменят исходное содержимое.
Удалить — удаляет значения по заданой маске. Примечательно, что мне удалось заставить работать макрос таким образом, что при условии количества групп захвата более 0 (от 1 до 3) удаляется только окружающая информация, а не значение, совпадающее с маской в скобках.
Разделить — разделяет значение на две группы. В новый столбец выносятся значения подходящие по RegExp, в старом остается все, что не совпало. Это так же значит, что соседний справа столбец должен быть пуст.
ИСТИНА/ЛОЖЬ — дополнительный инструмент для взаимодействия с формулами. При совпадении маски заносит в соседний столбик значения ИСТИНА, в противном случае — ЛОЖЬ.
Хотелки или дополнительные опции
Количество групп захвата — количество масок занесенных в скобки. Пример: \d{3}\s?(.*?),\s(.*?)\. — тут их две.
Выбрать столбец — название говорит само за себя. Выбор редактируемого столбца.
Заменить на\добавить — В случае удаления по маске, заменяет удаленные значения на введенный пользователем текст. В случае отделения/разделения — добавляет в конец каждого значения или группы, введенные символы.
Результат на новом листе — результат на новом листе.
Эпилог победителя
Простите меня за некую претенциозность и непривычный хабрастиль. Из-за моего врожденного неумения красиво обличать свои мысли в текст, а так же создавать экшн, объясняя код, я решил, что в самой статье будет лучше описать свой кейс, а хорошо комментированный код разместить в ссылке внизу статьи.
