Создание инструмента отслеживания обновлений проекта в Excel с использованием VBA

Создание инструмента отслеживания обновлений проекта в Excel с использованием VBA

Три простых шага для отслеживания обновлений проекта в Excel и их регистрации

Часто мы участвуем в нескольких проектах на работе. Каждый проект включает в себя несколько задач или подзадач. Хорошей практикой является отслеживание статуса этих задач и проектов для управления проектами. Эти задачи или обновления проекта могут быть использованы в наших целях, а также для обмена информацией во время проектных встреч. На рынке существуют различные бесплатные или коммерческие инструменты управления проектами, которые служат подобной цели. Однако я хотел создать простой инструмент на основе Excel для своих нужд с использованием Visual Basic Applications (VBA).

Функциональность VBA очень широка. Она может использоваться для автоматизации обработки данных, анализа данных и визуализации данных. Это делает работу с большими наборами данных в Excel очень удобной. Одним из фактов о VBA является то, что кодовая база VBA не обновляется регулярно, как различные пакеты в Python. Это может рассматриваться как преимущество и недостаток в различных контекстах. Однако одним из преимуществ является то, что после изучения VBA вы можете использовать те же знания в будущем. Вам не нужно быть в курсе новых версий или новых функций в VBA, потому что их нет (если только Microsoft не решит внедрить новые функции).

В одной из моих предыдущих публикаций я использовал VBA для ресэмплинга временных рядов.

Запуск Python через Excel VBA – пример ресэмплинга временных рядов

Комплексная оценка ресэмплинга временных рядов солнечной радиации с использованием VBA, с использованием Python и с использованием Python через…

towardsdatascience.com

В этой статье я расскажу, как я создал простой инструмент для отслеживания обновлений проекта для себя с использованием Visual Basic Applications (VBA) в Excel в трех простых шагах. Давайте начнем.

Изображение от Brands&People на Unsplash.

Цель

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

Для этой цели я создал файл Excel с двумя разными листами. Первый лист назывался ProjectTasksTracker, а второй лист назывался Logbook. Оба этих листа содержали одну и ту же строку заголовка, состоящую из шести столбцов: DateTime, Project, Tasks, Responsible Staff, Status и Updates.

Я использовал функцию =NOW() в Excel в столбце DateTime, чтобы получить реальное время. В столбце Status я разрешил три варианта в выпадающем меню: Started, In Progress и Complete. Я также создал кнопку с названием Update Logbook, чтобы автоматически регистрировать всю информацию без дублирования на листе Logbook. Лист ProjectTasksTracker выглядел как показано ниже:

Структура листа ProjectTasksTracker. Иллюстрация от автора.

Шаги кодирования

Я начал с создания подпрограммы внутри модуля VBA.

  1. Первый шаг состоял в определении объекта книги wb для файла и двух объектов листа, ws1 и ws2 для листа ProjectTasksTracker и листа Logbook соответственно. Код представлен в следующем фрагменте:
'Определение книги и двух листов.Dim wb As WorkbookDim ws1 As Worksheet 'Лист отслеживания проектовDim ws2 As Worksheet 'Лист журналаSet wb = ThisWorkbookSet ws1 = ThisWorkbook.Sheets("ProjectTasksTracker")Set ws2 = ThisWorkbook.Sheets("Logbook")

2. Второй шаг заключался в написании кода для подсчета количества строк и столбцов в двух листах. Это также можно сделать вручную. Однако, поскольку количество строк может изменяться при вводе обновлений проекта, этот процесс обновляется. Количество столбцов остается неизменным (6) для поддержания структуры двух листов согласованной. Однако кодирование также выполняется в демонстрационных целях.

В следующем кодовом фрагменте lr1 подсчитывает количество строк в рабочем листе ws1 на основе столбца A. lc1 подсчитывает количество столбцов в том же листе на основе строки 1.

'Подсчет количества строк и столбцов в листе ProjectTasksTrackerDim lr1, lc1 As Integerlr1 = ws1.Cells(Rows.Count, “A”).End(xlUp).Row lc1 = ws1.Cells(1, Columns.Count).End(xlToLeft).Column 

Примечание: Возможно использование ссылки на конкретную ячейку при работе с макросами. Это полезно при работе с наборами данных с возможностью изменений. Например, я создал именованный диапазон Updates для ссылки на ячейку F1 в листе ProjectTasksTracker. Если перед ним добавляется один столбец, то Updates будет ссылаться на ячейку G1.

Именованный диапазон Updates ссылается на код update_cell, как показано ниже. Номер столбца, к которому он относится, указывается с помощью update_column, а номер столбца в алфавите задается update_col.

Dim update_cell As RangeSet update_cell = ws1.Range(“Updates”)Dim update_column As Integerupdate_column = update_cell.ColumnDim update_col As Stringupdate_col = Chr(update_column + 64)MsgBox "Столбец обновлений принадлежит: Столбец " & update_col
MsgBox для отображения ссылки на местоположение обновлений на основе приведенного выше кода. Иллюстрация автора.

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

3. Третий шаг является самым важным в этом процессе. На этом шаге я выполнил перебор каждой строки (за исключением строки заголовка и столбца Datetime) в листе ProjectTasksTracker и выполнил следующие операции, представленные тремя подшагами:

a. Для каждой строки в листе ProjectTasksTracker я проверил, пустой ли столбец обновлений для каждой задачи. Если у определенной строки в листе ProjectTasksTracker есть обновления, то я подсчитал количество строк в листе Logbook и присвоил это количество целочисленной переменной lr2. Кроме того, я объявил логический тип данных с именем valuesMatch и присвоил ему значение False по умолчанию.

b. Затем я создал вложенный цикл для перебора каждой строки в листе Logbook и проверки, совпадает ли содержимое каждого столбца строки в листе ProjectTasksTracker (определено как диапазон rg1) с содержимым каждого столбца любой строки в листе Logbook (определено как диапазон rg2). Если нет совпадений между rg1 и любым значением rg2, то это означало, что обновление в определенной строке в листе ProjectTasksTracker не было зарегистрировано в листе Logbook ранее. Значение valuesMatch остается False. Если содержимое строки в листе ProjectTasksTracker совпадает с любой строкой в листе Logbook, то это означает, что строка уже была зарегистрирована ранее. В этом случае значение valuesMatch будет изменено на True.

c. Если значение valuesMatch в конце обоих циклов for является True, то дополнительные процессы не требуются. Если значение valuesMatch в конце двух циклов for является False, то строка из листа ProjectTasksTracker (включая столбец Datetime) будет скопирована и вставлена в лист Logbook.

Шаги 3a, b и c были закодированы в приведенном ниже коде:

Демонстрация

На графике ниже показаны обновления в листе ProjectTasksTracker на 20.08.2023 23:32.

Начальный вид обновлений в листе ProjectTasksTracker на 29.08.2023. Иллюстрация автора.

Эти обновления уже были зарегистрированы в листе Logbook, как показано ниже, уже 20.08.2023.

Обновления в листе Logbook до 20.08.2023. Иллюстрация автора.

Затем, 29.08.2023 23:38, я внес некоторые изменения в лист ProjectTasksTracker, выделенные красным цветом (внесены изменения в первые две строки и добавлена последняя строка). Затем я нажал кнопку “Обновить журнал”, к которой привязан макрос, описанный в разделе Шаги кодирования выше.

Изменения в листе ProjectTasksTracker на 29.08.2023. Иллюстрация автора.

Эти новые изменения затем регистрируются в листе Logbook. Строки внизу, выделенные красным цветом, являются изменениями, внесенными 29.08.2023. Другие ранее зарегистрированные обновления остаются неизменными.

Новые обновления зарегистрированы в листе Logbook. Ранее зарегистрированные обновления остаются неизменными.

Заключение

В этом посте я описал несколько шагов кодирования для создания простого трекера в Excel для ввода и регистрации обновлений задач проекта. Если в листе ProjectTasksTracker внесены изменения или добавлены новые задачи, макрос будет копировать и вставлять эти обновления в лист Logbook. Однако, если в листе ProjectTasksTracker нет изменений, обновления останутся неизменными в обоих листах после нажатия кнопки.

Также возможно создание дополнительных функций, таких как сортировка строк в листе Logbook в определенном порядке в конце. Также можно создать новый файл для регистрации обновлений проекта вместо регистрации их в отдельном листе в том же файле Excel. В этом случае необходимо переопределить место назначения рабочей книги и листа в коде. Эти шаги не включены в этот пост, чтобы сделать его более простым. Код и файл Excel с включенным макросом, используемые в этом посте, доступны в этом репозитории GitHub. Спасибо за чтение!