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

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

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

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



Запуск Python через Excel VBA — пример повторной выборки временных рядов
Комплексная оценка повторной выборки временных рядов солнечного излучения с использованием VBA, использования Python и использования Python через…towardsdatascience.com



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

Цель

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

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

Я использовал функцию =NOW() в Excel в столбце DateTime, чтобы получить реальное время. Я разрешил три варианта в раскрывающемся меню в столбце «Статус»: «Начато», «В процессе» и «Завершено». Я также создал кнопку под названием Обновить журнал для автоматической регистрации всей информации без дублирования на листе журнала. Лист ProjectTasksTracker выглядел так, как показано ниже:

Этапы кодирования

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

  1. Первым шагом было определение объекта рабочей книги wb для файла и двух объектов рабочей таблицы, ws1 и ws2 для листа ProjectTasksTracker и листа Журнал соответственно. Код приведен во фрагменте ниже:
'Define workbook and two worksheets.
Dim wb As Workbook
Dim ws1 As Worksheet 'Project tracker worksheet
Dim ws2 As Worksheet 'Logbook worksheet

Set wb = ThisWorkbook
Set ws1 = ThisWorkbook.Sheets("ProjectTasksTracker")
Set ws2 = ThisWorkbook.Sheets("Logbook")

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

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

'Count the number of rows and columns in ProjectTasksTracker sheet
Dim lr1, lc1 As Integer
lr1 = ws1.Cells(Rows.Count, “A”).End(xlUp).Row 
lc1 = ws1.Cells(1, Columns.Count).End(xlToLeft).Column

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

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

Dim update_cell As Range
Set update_cell = ws1.Range(“Updates”)

Dim update_column As Integer
update_column = update_cell.Column

Dim update_col As String
update_col = Chr(update_column + 64)
MsgBox "Update column belongs to: Column " & update_col

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

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

а. Для каждой строки листа ProjectTasksTracker я проверял, пуст ли столбец «Обновления» для каждой задачи. Если в определенной строке ProjectTasksTracker были обновления, я подсчитывал количество строк на листе Журнал и присваивал счетчику целое число с именем lr2. Более того, я объявил логический тип данных под названием valuesMatch и присвоил ему значение False по умолчанию.

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

в. Если бы valuesMatch было True в конце обоих циклов for, дальнейших процессов не было бы. Если valuesMatch было ложным в конце двух циклов for, то строка из листа ProjectTasksTracker (включая столбец Datetime) будет скопирована и вставлена ​​на лист Журнал. .

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

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

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

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

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

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

Заключение

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

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