详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

企业项目管理系统

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

主要用到公式有:VLOOKUP函数、Match函数、if函数、indirect函数iferror函数

本文教程以最近发布的《企业项目管理系统》为例进行讲解。

1、基础数据

1.1 首先按下图所示创建5个工作表,分别命名为index、信息查询、信息录入、数据源、下拉信息。

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

1.2 在下拉信息工作表中创建项目名的表,按上图录入信息后按Ctrl t。。

1.3 将项目名添加到名称管理器上。选中项目名的表格依次点击—公式—根据所选内容创建名称—首行—确定。

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

1.4数据源表格:按下图所示建立数据源表格,红色框为标题栏,需要输入,红框之外的数据可不用输入。

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

1.5项目状态统计:在数据源表格旁边创建项目状态的表格,在后面的数量单元格输入下面对应公式:=COUNTIF(表1[状态],R2)。

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

2、信息录入:在信息录入表格中输入下图内容,注意行号和列号与下图保持一致。

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

2.1 通过开发工具插入三个按钮控件,并分别命名为“进度维护”、“数据清除”、“录入”。如下图所示。

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

2.2 创建项目名的下拉菜单:选中创建下拉信息的单元格一次选择数据—数据验证—数据验证—序列—输入“=INDIRECT($D$6)”确定。

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

2.3 开始时间公式输入,首先选择E10单元格,输入公式”=IF(F9="","",F9)”,这个公式的作用就是把上一个项目状态的结束时间复制到当前项目状态的开始时间。依次类推,将公式下拉填充至E13单元格。

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

2.4 持续天数公式输入:选中H9单元格输入如下公式“=IF(AND(F9="",E9<>""),TODAY()-E9,IF(AND(F9<>"",E9<>""),F9-E9,""))”这个公式的意思是:如果当前行所在状态的开始时间有数据,结束时间没数据时,持续天数就为当前系统时间减去开始时间。如果当前行所在状态的开始时间有数据,结束时间也有数据时,持续天数就为结束时间减去开始时间。否则就返回空值。在H9输入公式后下拉填充至H13即可。

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

2.5 项目状态公式输入:选中I9单元格输入公式“=IF(E9="","",IF(F9="",D9,IF(F10="",D10,IF(F11="",D11,IF(F12="",D12,IF(F13="",D13,"已完成"))))))”这个公式的大概意思就是通过判断每个项目状态的结束时间来返回对应的项目状态。

2.6 在数据源单元格B1位置输入公式:“=IFERROR(MATCH(信息录入!$E$6,数据源!$A:$A,0),0)”这个公式的作用主要是用于判断数据录入单元格选择的项目名是否在数据源下图B区存在,方便后面代码录入。

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

2.7 选择开发工具—Visual Basic 输入录入信息的代码。如下图所示(下方有代码明细)

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

代码明细:

Sub 信息录入()

If Sheet1.Cells(6, 5) = "" Then

MsgBox "请先输入项目名!"

Exit Sub

End If

a = Sheet2.Cells(Rows.Count, 1).End(xlUp)(2, 1).Row

Dim i, k As Integer

i = Sheet2.Range("b1")

If i = 0 Then

Sheet2.Cells(a, 1) = Sheet1.Cells(6, 5) '项目名

Sheet2.Cells(a, 2) = Sheet1.Cells(9, 5) '开始日期

Sheet2.Cells(a, 3) = Sheet1.Cells(9, 7) '备注1

Sheet2.Cells(a, 4) = Sheet1.Cells(9, 6) '启动日期

Sheet2.Cells(a, 5) = Sheet1.Cells(10, 7) '备注2

Sheet2.Cells(a, 6) = Sheet1.Cells(10, 6) '计划日期

Sheet2.Cells(a, 7) = Sheet1.Cells(11, 7) '备注3

Sheet2.Cells(a, 8) = Sheet1.Cells(11, 6) '执行日期

Sheet2.Cells(a, 9) = Sheet1.Cells(12, 7) '备注4

Sheet2.Cells(a, 10) = Sheet1.Cells(12, 6) '监控日期

Sheet2.Cells(a, 11) = Sheet1.Cells(13, 7) '备注5

Sheet2.Cells(a, 12) = Sheet1.Cells(13, 6) '收尾日期

Sheet2.Cells(a, 13) = Sheet1.Cells(14, 5) '负责人

Sheet2.Cells(a, 15) = Sheet1.Cells(14, 7) '项目介绍

End If

If i > 0 Then

Sheet2.Cells(i, 1) = Sheet1.Cells(6, 5) '项目名

Sheet2.Cells(i, 2) = Sheet1.Cells(9, 5) '开始日期

Sheet2.Cells(i, 3) = Sheet1.Cells(9, 7) '备注1

Sheet2.Cells(i, 4) = Sheet1.Cells(9, 6) '启动日期

Sheet2.Cells(i, 5) = Sheet1.Cells(10, 7) '备注2

Sheet2.Cells(i, 6) = Sheet1.Cells(10, 6) '计划日期

Sheet2.Cells(i, 7) = Sheet1.Cells(11, 7) '备注3

Sheet2.Cells(i, 8) = Sheet1.Cells(11, 6) '执行日期

Sheet2.Cells(i, 9) = Sheet1.Cells(12, 7) '备注4

Sheet2.Cells(i, 10) = Sheet1.Cells(12, 6) '监控日期

Sheet2.Cells(i, 11) = Sheet1.Cells(13, 7) '备注5

Sheet2.Cells(i, 12) = Sheet1.Cells(13, 6) '收尾日期

Sheet2.Cells(i, 13) = Sheet1.Cells(14, 5) '负责人

Sheet2.Cells(i, 15) = Sheet1.Cells(14, 7) '项目介绍

End If

MsgBox "信息录入成功!"

Sheet1.Range("f9:f13") = ""

Sheet1.Range("e9") = ""

Sheet1.Cells(6, 5) = ""

Sheet1.Cells(14, 5) = ""

Sheet1.Range("G9:G14") = ""

End Sub

2.8 进度维护代码:

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

Sub 进度维护()

Dim i As Integer

i = Sheet2.Range("b1")

If i = 0 Then

MsgBox "数据库无此项目的数据,请先录入该项目的信息!"

Sheet1.Range("f9:f13") = ""

Sheet1.Range("e9") = ""

Sheet1.Cells(14, 5) = ""

Sheet1.Range("G9:G14") = ""

Exit Sub

End If

Sheet1.Range("f9:f13") = ""

Sheet1.Range("e9") = ""

Sheet1.Cells(14, 5) = ""

Sheet1.Range("G9:G14") = ""

If i > 0 Then

Sheet1.Cells(9, 5) = Sheet2.Cells(i, 2) '开始日期

Sheet1.Cells(9, 7) = Sheet2.Cells(i, 3) '备注1

Sheet1.Cells(9, 6) = Sheet2.Cells(i, 4) '启动日期

Sheet1.Cells(10, 7) = Sheet2.Cells(i, 5) '备注2

Sheet1.Cells(10, 6) = Sheet2.Cells(i, 6) '计划日期

Sheet1.Cells(11, 7) = Sheet2.Cells(i, 7) '备注3

Sheet1.Cells(11, 6) = Sheet2.Cells(i, 8) '执行日期

Sheet1.Cells(12, 7) = Sheet2.Cells(i, 9) '备注4

Sheet1.Cells(12, 6) = Sheet2.Cells(i, 10) '监控日期

Sheet1.Cells(13, 7) = Sheet2.Cells(i, 11) '备注5

Sheet1.Cells(13, 6) = Sheet2.Cells(i, 12) '收尾日期

Sheet1.Cells(14, 5) = Sheet2.Cells(i, 13) '负责人

Sheet1.Cells(14, 7) = Sheet2.Cells(i, 15) '项目介绍

End If

End Sub

2.9 清除数据代码

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

Sub 清除数据()

Sheet1.Range("f9:f13") = ""

Sheet1.Range("e9") = ""

Sheet1.Cells(6, 5) = ""

Sheet1.Cells(14, 5) = ""

Sheet1.Range("G9:G14") = ""

End Sub

提示:以上所有代码均写在模块中。插入模块的方法见下图。

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

3 指定宏:按下图所示顺序依次点击按钮右键—指定宏—信息录入—确定。将上面写的代码指定到对应按钮下。按同样方式将“清除数据”和“进度维护”指定宏即可

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

3.1 完成以上步骤后可录入数据测试。

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

3.2 统计图表:全选数据源中红框区域的数据,点击插入图表,然后再将图表剪切至“信息录入”界面

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

3.3调整图表大小至合适位置并设置背景颜色和格式。

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

3.4 汇总统计信息:在数据源表格的K1和M1单元格分输入“进行中”和“已完成”

在L1单元格输入公式:“=COUNTA(表1[项目名])-N1”(用于统计不是已完成项目的个数)在N1单元格输入公式:“=COUNTIF(表1[状态],M1)”(用于统计已完成项目的个数)。

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

3.5 在“信息录入”单元格的E19到E21分别输入下列公式来获取对应的值

=数据源!L1 数据源!N1

=数据源!N1

=数据源!L1

好啦,到这里“信息录入”工作表的功能就全部实现了。

4. 信息查询:这里可以直接将“信息录入”的工作表复制到“信息查询”工作表。然后删除对于的信息即可。调整页面布局如下图所示。

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

4.1 在E9单元格输入公式:=IFERROR(IF(VLOOKUP($E$6,数据源!$A:$O,2,FALSE)=0,"",VLOOKUP($E$6,数据源!$A:$O,2,FALSE)),"")

在F9单元格输入公式:=IFERROR(IF(VLOOKUP($E$6,数据源!$A:$O,4,FALSE)=0,"",VLOOKUP($E$6,数据源!$A:$O,4,FALSE)),"")

在G9单元格输入公式:=IFERROR(VLOOKUP($E$6,数据源!$A:$O,3,FALSE),"")

以上公式的意思就是通过项目名去数据源表格中查找对应的值。这里如果对VLOOKUP函数不熟悉的话可以单独在网上去学习一下。

然后用同样的方式将F9到G14单元格区域都通过VLOOKUP函数去获得对应的值。这里主要是设计到函数的灵活运用,这里就不在详细说明。如果还有疑问可直接联系小编。

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

4.2 所有公式输入完成后选择一个录入了数据的项目名:然后依次选择所有开始时间—插入堆积条形图(如下图所示)然后调整条形图的大小和位置。

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

4.3 右键点击图表—选择数据

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

4.4 点击添加

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

4.5先选中系列值,在框选持续天数的数据。如下图所示

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

4.6 将蓝色区域设置为无填充,无线条

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

4.7 再点击选择数据—编辑

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

4.8 线选择轴标签—在框选项目状态。(如下图所示)

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

4.9 在图表类别的坐标轴选项中勾选—逆序类别

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

5.0 设置图标的背景格式和字体颜色。如下图所示

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

5.1 添加数据标签,选择图表右键添加数据标签即可,添加后点击数据可修改字体和字体颜色

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

5.2 做到这里我们的“信息查询”工作表也做完了。

6.超链接:页面切换都采用超链接的方式实现。连接方式见下图。这里不详细说明了。

详细介绍!如何用Excel制作企业项目管理系统(excel怎么做项目管理)

结语:由于篇幅限制,本次的分享就到这里,由于涉及的知识点较多,所以有些地方讲解不是很细。如果有疑问可以在评论区留言,小编看到了会及时回复。有兴趣的可以结合作品同步学习,如有分享不到位的地方还请谅解!

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

(1)
上一篇 2022年9月9日 上午8:16
下一篇 2022年9月9日 上午8:18

相关推荐

  • 商业合同中的合同策略有哪些(商业合同中的合同策略有哪些内容)

    欢迎关注秦羽思维头条号,每天免费获取更多管理知识及行业动态干货方法论。 合同关系 合同策略形成 合同策略的形成是一个迭代的过程,因为在每个决策中都需要考虑风险评估和缓解。 制订合同…

    科研百科 2022年11月22日
    175
  • 2025科研项目题目

    2025科研项目题目: 探索宇宙中是否存在智慧生命 随着人类对宇宙的不断探索,我们越来越意识到宇宙中存在的可能性。在宇宙中存在着数以亿计的星系和行星,其中许多都有可能支持生命的存在…

    科研百科 2025年3月30日
    1
  • 协同办公平台手机app(协同办公平台哪个好)

    协同办公平台哪个好?这是一个非常主观的问题,因为每个人对于协同办公平台的需求和偏好都有所不同。但是,以下是一些常见的协同办公平台,以及它们的特点,可以帮助你做出选择: 1. Mic…

    科研百科 2024年6月3日
    52
  • 重大科研项目公关 重大科研项目的标准是什么

    重大科研项目公关的标准是什么 随着科技的不断进步,重大科研项目已经成为现代社会中不可或缺的一部分。重大科研项目的公关也成为了一项必不可少的任务。那么,重大科研项目公关的标准是什么?…

    科研百科 2024年3月29日
    124
  • 研发项目调度实施过程存在的问题

    研发项目调度实施过程存在的问题 在软件开发和工程领域中,研发项目调度实施过程是确保项目按时交付的关键步骤。然而,在这个过程中存在一些问题,这些问题可能会导致项目延误,降低项目质量,…

    科研百科 2024年10月25日
    0
  • 科研项目前的

    科研项目前的 科研项目前的等待是令人焦虑和不安的。在这个时期,我们需要等待研究人员和工程师们完成他们的工作,准备进行实验。等待的过程可能长达数月甚至数年,这对于一些人来说可能是一个…

    科研百科 2025年2月15日
    0
  • 环保麻地膜应运而生

      随着塑料地膜在农业生产上的利用不断扩大,“白色”污染也日趋严重。在环保意识不断增强的今天,已引起国家有关部门的高度关注。  为保护生态环境,促进农业的可持续发展,我院麻类所科研…

    科研百科 2022年5月20日
    293
  • 东华办公协同

    东华办公协同: 让工作更加高效 随着现代企业规模不断扩大和员工数量的不断增加,传统的办公方式已经无法满足高效的工作任务和团队协作的需求。因此,东华办公协同成为了现代企业必须面对的问…

    科研百科 2024年8月22日
    58
  • 织密组织体系,广州南沙实施“三网融合”畅通平战转换机制(广州三网整治)

    7月25日下午,“改革强动力 创新激活力”——“南沙改革创新榜”发布活动在广州南沙成功举办。 本次活动由南方都市报社、南都高质量发展研究院主办,获得省、市委改革办指导,南沙区委改革…

    科研百科 2023年10月20日
    113
  • 酵母多糖微量元素多功能生物制剂研究达国际先进水平

        近日,中国农业科学院兰州畜牧与兽药研究所完成的“酵母多糖微量元素多功能生物制剂研究”项目通过农业部科技教育司组织专家的…

    科研百科 2022年5月17日
    280