项目工作任务 excel_在Excel中跟踪项目任务

项目工作任务 excel

If you're working on an Excel project with a client, or other employees in your company, how do you keep track of everything that needs to be done? If emails are flying back and forth, with questions, and comments, and specification changes, it's easy for things to slip through the cracks. Then, 5 minutes before the deadline, someone realizes that a key piece is missing!

如果您正在与客户或公司中的其他员工一起处理Excel项目，那么如何跟踪需要完成的所有工作？ 如果电子邮件来回飞来飞去，有问题，评论和规格更改，事情就很容易溜走。 然后，在截止日期前5分钟，有人意识到钥匙丢失了！

Nobody wants that to happen, so it helps to have a system for keeping track of everything that has to be done.

没有人希望这样的事情发生，因此拥有一个跟踪必须完成的事情的系统会有所帮助。

Excel中的任务列表 (Task List in Excel)

For projects that require more than a couple of simple changes, I usually create a task list in Excel. It's nothing too fancy – just a list of what needs to be done, broken into steps.

对于需要进行多个简单更改的项目，通常会在Excel中创建任务列表。 没什么太花哨的-只是需要完成的工作的清单，分为几步。

For example,

例如，

• if the project requires changes to several files, there are columns to record the file and sheet names.
  如果项目需要更改几个文件，则有几列记录文件和图纸名称。
• task types can be entered, so the time estimates can be grouped by those categories
  可以输入任务类型，因此可以将时间估算值按这些类别分组
• task description – as detailed as necessary – break large tasks into separate steps
  任务描述–根据需要进行详细说明–将大型任务分解为单独的步骤
• time estimate, in minutes, to get a rough idea of how long the project will take
  时间估算(以分钟为单位)，以大致了解该项目将花费多长时间

You can see a few more columns, in the next screen shot.

在下一个屏幕截图中，您将看到更多的列。

• person the task is assigned to
  任务分配给的人
• date the task was completed
  任务完成的日期
• actual time to complete the task
  完成任务的实际时间
• notes or questions about the task
  有关任务的注释或问题
• person who should answer the question
  应该回答这个问题的人
• replies to the questions
  回答问题

For other projects, you might need additional information, such as:

对于其他项目，您可能需要其他信息，例如：

• task due date
  任务截止日期
• reference number
  参考编号
• department
  部门

What else would you need to record for each task?

您还需要为每个任务记录什么？

下拉列表 (Drop Down Lists)

To make it easier to enter the tasks, I use data validation to create drop down lists in some columns.

为了使输入任务更容易，我使用数据验证在某些列中创建下拉列表 。

The source lists are stored on a separate sheet, in named Excel Tables.

源列表存储在名为Excel Tables的单独工作表中 。

总任务时间 (Total the Task Times)

At the top of the task sheet, there are cells that summarize the estimated and actual task times. That gives you a quick overview, while you enter the tasks, or update them with actual times.

在任务表的顶部，有一些单元格总结了估计和实际的任务时间。 在您输入任务或以实际时间更新任务时，可以快速概览。

The times are entered as minutes, so the formula sums all the times, then divides by 60

时间以分钟为单位输入，因此公式将所有时间相加，然后除以60

=SUM(tblTasks[Time Est Minutes])/60

= SUM(tblTask​​s [时间估计分钟])/ 60

项目任务摘要 (Project Task Summaries)

After the tasks are entered, you can create a pivot table or two, to show the estimated and actual times. The times can be summarized per person, or per task category, or any other grouping that you'd like.

输入任务后，您可以创建一个或两个数据透视表，以显示估计时间和实际时间。 时间可以按人员，任务类别或您想要的任何其他分组进行汇总。

共享任务列表 (Share the Task List)

I don't like setting up shared Excel workbooks, but I've found that it works well to upload a file to Microsoft's OneDrive site, and everyone can edit the task list there, using Excel Online. The only strange thing that I've noticed is that after you select from a drop down list, the cell below is selected, instead of staying in the same cell.

我不喜欢设置共享的Excel工作簿 ，但是我发现将文件上传到Microsoft的OneDrive网站非常有效，每个人都可以使用Excel Online在此编辑任务列表。 我注意到的唯一奇怪的事情是，从下拉列表中选择后，下面的单元格被选中，而不是停留在同一单元格中。

After you upload the file, use the Share options, to send a link to all the interested parties. I save the link as a bookmark in my browser, so it's easy to get back to the file again.

上传文件后，使用“共享”选项将链接发送给所有感兴趣的各方。 我在浏览器中将链接另存为书签，因此很容易再次返回文件。

You can download a copy of your task file, after you make changes, to store as a local backup. Or, go back to look at previous versions of the file.

进行更改后，您可以下载任务文件的副本，以存储为本地备份。 或者，返回以查看文件的早期版本。

下载项目任务跟踪器样本 (Download the Project Task Tracker Sample)

You can download a copy of my project task tracker file, from my Contextures website. On the Sample Files page, go to the Data Validation section, and look for DV0069 – Excel Project Task Tracker.

您可以从Contextures网站下载我的项目任务跟踪器文件的副本。 在“样本文件”页面上，转到“数据验证”部分，然后查找DV0069 – Excel Project Task Tracker 。

The zipped file is in xlsx format, and does not contain macros.

压缩文件为xlsx格式，不包含宏。

翻译自: https://contexturesblog.com/archives/2015/04/09/track-project-tasks-in-excel/

项目工作任务 excel