Личный опыт: зелёный стажер-экспериментатор осваивает VBA в Excel и откапывает час за часом...

Это история 17-летней давности с моего первого места работы. Она про то, как удалять рутину экспериментальным путем. Еще в мире нет никаких метрик и продактов, которые дашбордяд на лавинах данных, а есть мое первое место работы, тысяча табличек в Excel и ежедневная рутина по жонглированию этими табличками...

В этом посте вы вряд ли найдете какие-то секреты или пример решения конкретного кейса на цифрах. Это, скорее, личный опыт с личным растянутым во времени кейсом, решение которого далось мне экспериментально, но по итогу которого я и Excel освоил, и алгоритмы подтянул, и с VBA познакомился.

Моя первая работа после университета (не считая каких-то подработок). Я устраиваюсь в розничную компанию, занимающуюся одеждой, которая держит около 30 бутиков в торговых центрах Москвы, растет и расширяется в регионы. Еще не жахнул кризис 2008 года, который потопит такие бизнесы, как этот. В компании есть 1С, в которой собирается информация со счетчиков посетителей в бутиках, данные с касс, и все вроде ок. Весь управленческий учет ведется в Excel. На управленческий учет и планирование работает отдел из 8 человек, которые в ручном режиме реализуют бизнес-процессы планирования, бюджетирования, создания оперативной отечности и т.п. фактически каждый день. Ввиду человеческого фактора, при переносе данных из таблицы в таблицу, агрегации и составлении отчетов, возникают ошибки, которые регулярно приходится исправлять, перепроверяя все таблицы. В общем типовые болячки учета в Excel, про которые все давно всё знают.

Я еще зелёный выпускник хорошего ВУЗа с неплохо варящим котелком, но без опыта и без навыков ни в 1C, ни в Excel, ни в чем либо офисном.

Я прихожу на работу каждый день к 9:00 без опозданий, график жёсткий, ухожу ровно в 18:00, но иногда задерживаюсь, хоть это и не принято в компании. Да, и корпоративный автобус не ждет задерживающихся: опоздал — добирайся сам до ближайшего метро за много км от офиса.

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

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

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

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

Так проходит день за днем в течение пары месяцев.

Поработав в таком режиме пару месяцев, делая одинаковую рутину изо дня в день, меня достаточно регулярно начинает посещать мысль: «А, что если я запишу макрос по копированию инфо из одного листа в другой?». Дело даже не в том, что делать рутину изо дня в день было сложно. Скорее, дело в том, что это было скучно и однообразно, а мозг требовал нагрузки.

У меня было время для экспериментов.

Надо отметить, что у меня не было ни знаний, ни YouTube, ни курсов по работе с Excel, которые сейчас только ленивый не найдет даже в бесплатном формате. А еще в компании был закрыт доступ в интернет, и из внешнего электронного мира была только аська и кнопочный телефон. Лучшее, что я мог себе позволить — это узнавать Excel методом тыка и через «Помощь» по нажатию F1.

Спросите, откуда у меня такие мысли про макросы, если я ничего этого не знал? А, вон там кнопочка на панели инструментов Excel — «Записать макрос». Очень любопытно, что это такое, и я как-то уже пробовал тыкать. И получалось интересно. Отсутствие интернета и связи с внешним миром, надо сказать, прекрасно делают свое дело, запуская любопытство и исследовательский настрой. И дальше это любопытство толкает к экспериментам.

Совет подготовительный: хотите поэкспериментировать или раскачать любопытство, отключитесь от интернета и смартфона, хотя бы на пол дня, и зависните в Excel.

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

Пилот или, как модно сейчас выражаться, тестирование гипотезы. Наверное, если я сейчас скажу что-то в духе «если я переведу консолидацию конкретных табличек на макросы, то это (а) позволит мне сократить свои собственные трудозатраты на 50% и (б) уменьшит количество человеческих ошибок при переносе данных до нуля», то это будет нормальная гипотеза, которую можно замечательно проверить и протестировать. Тогда все эти модные слова и конструкции были мне не знакомы, и мне просто хотелось поэкспериментировать, чтобы получить какой-то классный результат, ачивку, достижение.

Мои первые макросы и автоматизация были отвратительно и в то же время банально простыми: я открывал два файла, нажимал кнопку и макрос копировал фиксированный диапазон из файла-источника в файл-приемник (мастер-файл).

Дело в том, что чтобы автоматизировать что-то с помощью макросов в Excel, вам совершенно не нужно знать, как эти макросы устроены, или быть программистом. Ключ к успешному эксперименту здесь, скорее, в том, что вам нужно выбрать то самое ручное элементарное действие, которое вы регулярно делаете, и которое может быть легко автоматизировано.

В моем случае элементарным действием для автоматизации было простое копирование (копипейст — выделил диапазон ячеек, нажал Ctrl+C, переключился на другую книгу, поставил указатель на нужную ячейку, нажал Ctrl+V, и так по кругу). Дальше вы делаете пару экспериментов с записью макроса и его воспроизведением. Благо, что это не rocket science, и записать макрос может любой пользователь, буквально нажав одну кнопку на панели инструментов. Немного терпения, и у вас уже готов первый элемент автоматизации.

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

Первая проблема, с которой я столкнулся: если в источнике кто-то переписал исходную формулу в ячейке, то и в приемник скопируется переписанная ячейка, а это, увы, означало неизбежность ошибок при дальнейших расчетах. Первое время я даже исправлял их вручную. Само собой, потом надоело. Пришлось усовершенствовать макрос: теперь вместо копирования диапазона (таблицы) макрос «хватал» только те области, которые были предназначены для ручного заполнения, и переносил данные оттуда в мастер-файл. После чего файл-источник успешно закрывался. Те области, которые в мастер-файле содержали формулы для расчетов, оставались нетронутыми.

Это был первый успех, который (а) сократил мое время на эту рутину и (б) уменьшил кол-во человеческих ошибок. Но рутина оставалась рутиной, и все равно мне приходилось открывать все файлы по-очереди и запускать макросы на перенос.

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

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

Я докрутил этот макрос до той степени автоматизации, что мне нужно было только сохранить все присланные файлы-источники (30 штук) из почты в специальную директорию, и потом по клику одной кнопки все эти файлы по-очереди открывались, данные из них переносились в мастер-файл, после чего они успешно закрывались.

И сохранение файлов из Outlook, при желании, можно автоматизировать, но тогда мне это было не нужно, да и лишних знаний к тому моменту не было.

Файлы я впоследствии настроил так, чтобы в них нельзя было изменить структуру строк и столбов, то есть фактически зафиксировал разметку. Люди, которые получают такой «заблокированный» файл для работы и заполнения данных, обычно не очень довольны по-началу просто потому, что это ограничивает их возможности (кто-то даже может подумать, что ему не доверяют), но потом на удивление выясняется, что это в общем-то не мешает работе, и на этом все недовольство улетучивается.

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

Конечно, такая доработка открытия-закрытия файлов не случилась по щелчку пальцев. Здесь есть важный нюанс. Дело в том, что, как вы, может быть, знаете, Excel позволяет записать макрос, а потом посмотреть на код этого макроса, который записывается на языке программирования Visual Basic (точнее, Visual Basic for Applications, или VBA). Посмотреть такой код можно, если у вас открыта панель «Разработчик» (это можно сделать в настройках Excel), по клику на иконку «Visual Basic».

На тот момент я не был знаком с VBA. Но элементарные действия (как копипейст) записывались в режиме записи макросов очень простыми 1-2-строчными кодами, и на примере этих кодов было несложно разобраться, как работает команда копирования диапазона ячеек, например. Поскольку у меня было время экспериментировать, я мог в эти команды вносить изменения и смотреть, а что будет. Так, постепенно, я научился базовым вещам посредством кода на VBA, таким как: поставить курсор на нужную ячейку, найти ячейку с определенным значением, удалить, вставить, скопировать и т.п.

Если у вас есть время для экспериментов и хватает любопытства, и при этом вы много работаете с Excel, то обязательно поэкспериментируйте с макросами, записью кода, внесением изменений и т.п. Такие опыты расширяют понимание реальных возможностей Excel, которые обычно используются не больше, чем на 20%. В действительности, Excel представляет собой очень функциональный и мощный инструмент.

Вернемся к рутине и работе...

Дело двух утренних часов превратилось в дело 5-15 минут: сохранить 30 файлов из 30 писем в 1 директорию и 1 раз запустить макрос. Вот так просто. Самое приятное — я освободил время, которое проинвестировал в развитие тех же идей.

Если помните, последующие 2 утренних часа я тратил на сверку данных в Excel c данными в 1С. Такая проверка корректности проводилась вручную: я открывал 1С, выгружал специальный внешний отчет, который выдавал мне табличку с набором данных, которые нужно было сравнить с данными из Excel.

К сожалению, моих знаний на тот момент хватило только на то, чтобы написать автопроверку данных из Excel с данными из внешнего отчета 1С, который мне все равно приходилось выгружать вручную. Но вместо выискивания ошибок глазами вся работа превратилась в… запусти макрос и смотри в месседж-боксах, в каких местах твоя программа нашла разночтения, а дальше поднимай трубку и звони в бутик выясняй, где корректные данные: в 1С или в Excel.

Anyway, подытоживая абзацы выше, мои 4 часа ежедневной рутины превратились в 30 минут сохранения файлов, запуска макросов и обзвона бутиков для выяснения причин расхождения данных. По-моему, уже неплохая оптимизация.

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

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

Как процесс бюджетирования выстроен в торговой компании, которая живет от копейки до копейки? Каждый месяц все ответственные подразделения заполняют таблички (шаблоны) с расходами на следующий месяц. Кто-то эти таблички потом сводит в единый бюджет, и дальше происходит management decision этих бюджетов. Есть здесь рутина? Конечно, да. Эта рутина не ежедневная, как в моем случае, но ежемесячно специальный человек рассылает шаблоны, получает обратно заполненные таблички, консолидирует их и готовит сводный бюджет (табличку, как вы понимаете).

Зачем вручную сводить бюджет из табличек, если можно все сделать автоматически?

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

Решение оказалось простым и очевидным, но уже больше из области алгоритмики, нежели из области записи макросов в Excel. Нужно было поставить метки по типу: «начало» блока с данными / «конец» блока с данными. Все, что между началом и концом, итеративно перебиралось и по-строчно консолидировалось в общую бюджетную табличку. No magic, но просто чуть сложнее, чем на предыдущем этапе.

Метками служили некоторые ключевые значения в ячейках шаблонов, например, ячейка с текстом «№ п/п» или «Наименование статьи» отлично индикативно подсвечивают, что ниже будут идти те самые записи по статьям расходов. Достаточно найти поиском такое значение в табличке и поставить курсор на нужную ячейку. Как вы понимаете, записать макрос с поиском значения так же просто, как и записать макрос с копированием. Таким образом вы гарантированно ставите курсор на то место, с которого дальше стартует алгоритм перебора статей. Перебор статей продолжается до тех пор, пока курсор не наткнется на конец блока, который можно, например, узнать по натыканию на ячейку со значением «Итого» (благо это в бюджетах всегда есть).

Сам перебор статей — это цикл действий, который на каждой итерации проверяет, а не наткнулись ли мы на «Итого», и не пора ли остановиться, а если нет, то просто копируем определенные значения из текущей строки, находим в другой (консолидированной) табличке соответствующие строки (статьи расходов) и прибавляем значение исходной ячейки к консолидированному. К сожалению, для написания цикла уже понадобится либо чуть-чуть знать алгоритмы, либо чуть-чуть знать программирование. Циклы — это базовый инструмент алгоритмов. Такие вещи обычно рассказывают на 1 или 2 уроке любого курса по программированию, будь то Python или что-то другое. Поэтому познакомиться с этим можно очень быстро и просто.

Совет третий: если вам кажется, что автоматизация какая-то сложная, то разбейте её на элементарные действия. Подумайте через призму циклов действий и условий. Поверьте, если вы делаете одно и то же изо дня в день, то это набор элементарных действий, которые можно автоматизировать. Более того, скорее всего, это простой алгоритм, который вы можете описать, чтобы лучше понять, где и что вы можете автоматизировать.

Дальше была табличка, которая называлась «Кассовый план». Суть её была в том, что на ежемесячной (или еженедельной) основе сотрудник отдела заносил план по платежам, а потом ежедневно — факт по платежам. А табличка показывала данные и % исполнения бюджета по платежам. Табличка была структурирована по статьям оплат и т.п. В общем, красивое, детальное полотнище — всё как мы любим.

Перед подготовкой самого отчета-таблички тот же сотрудник отдела делал достаточно простую ручную работу, занося платежи в реестр. Обычный такой реестр, список, простая табличка в стиле: 1 платеж – 1 строка – 1 запись. В реестре уже была вся нужная информация о том, к какой статье относился платеж, и что он из себя представлял. В общем, дублируем 1С, но зато в табличках для руководства. Это важно. Потому что руководству принимать решения, а чтобы их принимать, информацию нужно видеть. Так вот, в 1С её видеть было сложно, долго, непонятно, не в том виде и т.д.

Дело с автоматизацией «Кассового плана» оказалось не сильно сложнее, чем с бюджетами. Очередной апгрейд, но уже не макроса, а уровня осознания в моей голове.

Фактически нужно было реализовать считывание инфо из строки (из реестра) и запись этой инфо в огромную матрицу в нужную ячейку. В принципе ничего сложного: прочитал запись, выделил признаки, нашел нужные столбец и строку, записал значение.

Так, стоп. Еще недавно этот человек радовался, когда ему удалось просто заавтоматизировать копипейст, а теперь надо вот это.

Набитая рука недолго мучалась, и на всё со всем ушло, по-моему, два дня времени между рабочими задачами. Повторюсь, здесь нет магии, и вся суть этой автоматизации заключается в супер-простом алгоритме: прочитал запись, выделил признаки, нашел нужные столбец и строку, записал значение на пересечении этих столбца и строки. Размер таблички и размер реестра не имеют значения. Будь они хоть миллиард на миллиард. Конечно, Excel терпит достаточно ограниченного размера таблички, но все же подход супер-простой.

Совет четвертый: если подступиться к задаче с нулевой базы, то она может показаться трудной для любого человека, кто не знаком с VBA или автоматизацией. В действительности, большинство подобных задач достаточно алгоритмичны, и всё, что вам нужно сделать — это расписать алгоритм, то есть порядок действий, как выше: прочитал, нашел, проверил, записал и так по кругу. А дальше заавтоматизировать первый (любой) элемент из этого перечня действий. Так вы перестанете быть с нулевой базой и поймете, что дело не хитрое.

Дальше по той же схеме пошла автоматизация сборки P&L, CF, планы и факты. Там были свои шаблоны, свои признаки, но действовал все тот же алгоритм: прочитал запись, ну, дальше вы поняли...В общем масштабировать подход удалось на все 100%.

Ручная консолидация табличек осталась в истории. Как показала практика, с постепенным уходом трех или четырех сотрудников в общем ничего не пострадало, все таблички консолидировались замечательно и исправно. Только когда появлялись новые бутики, приходилось делать некоторые поправки на работу алгоритма, который будучи написанным просто в экспериментальном режиме «для себя», все же оперировал предустановленным количеством файлов для консолидации. Но поправить константу в коде было сильно проще, чем тратить часы на ручное копирование ячеек.

Мне «повезло», что конкретно в той компании большой объем рутины делался в Excel, и у меня было время и возможность потестировать автоматизацию. Когда она заработала, дальше было делом нехитрым её раскрутить на полную катушку.

Мне также повезло, что у меня еще были какие-то скиллы в программировании: базовый C в университете все же позволял понимать, как построить простые циклы for / while и ветвления if … else.

Но больше всего мне повезло с тем, что такие эксперименты действительно приносили удовольствие и были продиктованы любопытством — пробовать автоматизировать повторяющиеся изо дня в день действия.

Как итог, наверное, уже можно зафиксировать, что удалось где-то в 8 раз (банально по времени) сократить рутину основной собственной задачи, убрать человеческий фактор, а впоследствии автоматизировать все так, что почти в 4 раза трудозатраты общие порезать. Те, что на консолидации табличек были задействованы, так точно.

И как итог, я точно советую вам, если вы сталкиваетесь с ежедневной рутиной в Excel, просто начать. Начать с малого. Автоматизируйте копипейст или что-то подобное простое. Увидите, как дальше всё закрутится.