749

Автоматизация Excel с помощью VBA на примере графика отпусков

(Офисной оптимизации пост (теперь уже с примерами))


В прошлом своём посте (где рассматривал, что есть VBA в Excel и зачем это может пригодиться) целых 137 человек подписалось на меня, в комментариях были призывы к каким-нибудь примерам использования VBA, да и обещал я @Tiafreed подкинуть материалов для ВКР, так что набросал за ночь простенький (в сотню строк кода без использования массивов, классов и т.д.) файлик в Excel с VBA модулем. Пост разделю условно на две части: для пользователей, кому интересно просто посмотреть как выглядит, что делает, плюс скачать, поиграться и для продвинутых пользователей, кому интересно как это работает и как настроить подобное под себя. Цель поста - показать возможности VBA (частично), предложить интересный вариант реализации достаточно распространённой задачи по расчёту отсутствия сотрудников.

Если формат поста зайдёт, то в следующий раз набросаю пример, как формировать Word документы из списка данных в Excel, используя шаблон и пользовательскую форму (и не используя ублюдскую рассылку ИМХО).

Автоматизация Excel с помощью VBA на примере графика отпусков Microsoft Excel, Программирование, Vba, Visual Basic, Офис, Оптимизация, Microsoft office, Длиннопост

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

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

Автоматизация Excel с помощью VBA на примере графика отпусков Microsoft Excel, Программирование, Vba, Visual Basic, Офис, Оптимизация, Microsoft office, Длиннопост

*Все персонажи вымышлены, совпадения случайны


Дальше идём на другой лист, нажимаем кнопку

Автоматизация Excel с помощью VBA на примере графика отпусков Microsoft Excel, Программирование, Vba, Visual Basic, Офис, Оптимизация, Microsoft office, Длиннопост

После чего идут расчёты какое-то время (у меня это где-то половина секунды)

Автоматизация Excel с помощью VBA на примере графика отпусков Microsoft Excel, Программирование, Vba, Visual Basic, Офис, Оптимизация, Microsoft office, Длиннопост

Машина рапортует нам об успешном завершении своей миссии, идём смотреть, что вышло.

Автоматизация Excel с помощью VBA на примере графика отпусков Microsoft Excel, Программирование, Vba, Visual Basic, Офис, Оптимизация, Microsoft office, Длиннопост

(Да, дизайнер я говно (как и VBA кодер в общем-то, но не об этом)) оформить самому никогда не сложно, главное, что на каждый день для каждого человека машина посчитала отпуска (можно любое другое отсутствие), вычислила цифры, разбила по отделам и отразила это графически, идём на главную

Автоматизация Excel с помощью VBA на примере графика отпусков Microsoft Excel, Программирование, Vba, Visual Basic, Офис, Оптимизация, Microsoft office, Длиннопост

Тут всё ещё проще, кнопка для запуска, табличка с примитивными расчётами (формула МАКС) и график на 366 дней который можно с лёгкостью оформить самому и с помощью которого отлично видны провалы и пики нагрузок. Нажатием на выпадающий список, мы выбираем отдел по которому выводятся данные. Вот и всё, просто и удобно. Набросал за пару ночных часов. Сразу предупреждаю, что я это не предлагаю, как готовый продукт (успешное бизнес-решение ваших кадровых проблем), просто накидал маленький пример и делюсь им с вами, потому ответственность за его использование и обслуживание не несу, но если есть желание доработать его в своих целях, готов подсказать и помочь. Да, если возникла ошибка, вероятнее всего, что формат даты/числа нарушен, защиту от дурака не ставил, ибо цели чисто демонстрационные, но если вдруг мой косяк (протестить нет возможности) перезалью и ссылку в комментарии кину. Вот сам файл (на свой страх и риск :D, никаких гарантий, что будет работать). https://yadi.sk/d/lsRdKL8wQ42FFw (и не забываем включить макросы)

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



Тэкс. Теперь вторая часть, в принципе, дальше можно не читать, так, для очистки совести её пишу. Кому интересно, как это работает или как вообще выглядит VBA на практике. Всё просто, в основе лежит вот эта строка (в ней мы будем искать колонку с датой отпуска и уже в ней работать)

Автоматизация Excel с помощью VBA на примере графика отпусков Microsoft Excel, Программирование, Vba, Visual Basic, Офис, Оптимизация, Microsoft office, Длиннопост

Сначала мы подготавливаемся, что-то где-то очищаем, что-то добавляем (всё в общем-то закомментил) и сортируем строки по отделам

Автоматизация Excel с помощью VBA на примере графика отпусков Microsoft Excel, Программирование, Vba, Visual Basic, Офис, Оптимизация, Microsoft office, Длиннопост

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

Автоматизация Excel с помощью VBA на примере графика отпусков Microsoft Excel, Программирование, Vba, Visual Basic, Офис, Оптимизация, Microsoft office, Длиннопост
Автоматизация Excel с помощью VBA на примере графика отпусков Microsoft Excel, Программирование, Vba, Visual Basic, Офис, Оптимизация, Microsoft office, Длиннопост

И простейший обработчик для выпадающего списка - просто вставляем в строку из которой берёт данные график данные из нужной нам строки. Рассчитываем её как номер строки начала (у нас 22) + номер элемента выпадающего списка (нумерация идёт с нуля у listindex)

Автоматизация Excel с помощью VBA на примере графика отпусков Microsoft Excel, Программирование, Vba, Visual Basic, Офис, Оптимизация, Microsoft office, Длиннопост

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

MS, Libreoffice & Google docs

481 пост12.9K подписчика

Добавить пост

Правила сообщества

1. Не нарушать правила Пикабу

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях


Важно - сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

Утверждения вроде "пост - отстой", это оскорбление автора и будет наказываться баном.

Подробнее
Лучшие посты за сегодня
8321

Действительно?

Действительно? Политика, Пенсия, Идиотизм, Twitter, Россия, Пенсионная реформа, Наследство
4927

Вы не понимаете...

Вы не понимаете... Курение, Соседи
4854

Нуу... логично

Нуу... логично Скриншот, Отношения, Мужчины, Женщины, Мужчины и женщины, Измена, Повтор, Фейк
Показать полностью 1
4618

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


https://www.roi.ru/72401/
4506

Вот как

Вот как
4442

Настоящий умный дом

Настоящий умный дом
4282

Надо все успеть

4236

Вождь краснокожих

3895

Ответ на пост «Я тоже видел короткое собеседование» 

3892

Здравствуйте я на пересдачу

Здравствуйте я на пересдачу Криминальное чтиво, Юмор, Раскадровка, Повтор
Показать полностью 1
3427

Спалил хозяйку

3141

Взрослая уже...

Взрослая уже...
3030

Я сделал это к ..40 годам)

2918

Упс...

2823

«Панк» – мой диплом

«Панк» – мой диплом Скульптура, Современное искусство, Диплом, Дипломная работа, Панки, Панк-рок, Искусство, Лепка, Бронза, Субкультуры, Видео, Длиннопост
«Панк» – мой диплом Скульптура, Современное искусство, Диплом, Дипломная работа, Панки, Панк-рок, Искусство, Лепка, Бронза, Субкультуры, Видео, Длиннопост
«Панк» – мой диплом Скульптура, Современное искусство, Диплом, Дипломная работа, Панки, Панк-рок, Искусство, Лепка, Бронза, Субкультуры, Видео, Длиннопост
«Панк» – мой диплом Скульптура, Современное искусство, Диплом, Дипломная работа, Панки, Панк-рок, Искусство, Лепка, Бронза, Субкультуры, Видео, Длиннопост
«Панк» – мой диплом Скульптура, Современное искусство, Диплом, Дипломная работа, Панки, Панк-рок, Искусство, Лепка, Бронза, Субкультуры, Видео, Длиннопост
Показать полностью 5 3
2811

Ветер и панорамные окна

2749

ФСБ накрыла в Дагестане подпольную типографию. Изъяты 12 млн поддельных рублей

ФСБ накрыла в Дагестане подпольную типографию. Изъяты 12 млн поддельных рублей Скриншот, Юмор, Дагестан, Комментарии на Пикабу
2578

Переиграл и уничтожил

Переиграл и уничтожил
2421

Ответ на пост «У челябинки арестовали дом за долги 400 человек»

Ответ на пост «У челябинки арестовали дом за долги 400 человек» Долг, ФССП, СНИЛС, Продажа авто, Судебные приставы, Произвол, Длиннопост
Показать полностью 1
2357

15 лет совместной жизни

15 лет совместной жизни
Похожие посты закончились. Возможно, вас заинтересуют другие посты по тегам: