企業(yè)項(xiàng)目管理系統(tǒng)

主要用到公式有：VLOOKUP函數(shù)、Match函數(shù)、if函數(shù)、indirect函數(shù)，iferror函數(shù)

本文教程以最近發(fā)布的《企業(yè)項(xiàng)目管理系統(tǒng)》為例進(jìn)行講解。

1、基礎(chǔ)數(shù)據(jù)

1.1 首先按下圖所示創(chuàng)建5個(gè)工作表，分別命名為index、信息查詢、信息錄入、數(shù)據(jù)源、下拉信息。

1.2 在下拉信息工作表中創(chuàng)建項(xiàng)目名的表，按上圖錄入信息后按Ctrl t。。

1.3 將項(xiàng)目名添加到名稱管理器上。選中項(xiàng)目名的表格依次點(diǎn)擊—公式—根據(jù)所選內(nèi)容創(chuàng)建名稱—首行—確定。

1.4數(shù)據(jù)源表格：按下圖所示建立數(shù)據(jù)源表格，紅色框?yàn)闃?biāo)題欄，需要輸入，紅框之外的數(shù)據(jù)可不用輸入。

1.5項(xiàng)目狀態(tài)統(tǒng)計(jì)：在數(shù)據(jù)源表格旁邊創(chuàng)建項(xiàng)目狀態(tài)的表格，在后面的數(shù)量單元格輸入下面對(duì)應(yīng)公式：=COUNTIF(表1[狀態(tài)],R2)。

2、信息錄入：在信息錄入表格中輸入下圖內(nèi)容，注意行號(hào)和列號(hào)與下圖保持一致。

2.1 通過(guò)開(kāi)發(fā)工具插入三個(gè)按鈕控件，并分別命名為“進(jìn)度維護(hù)”、“數(shù)據(jù)清除”、“錄入”。如下圖所示。

2.2 創(chuàng)建項(xiàng)目名的下拉菜單：選中創(chuàng)建下拉信息的單元格一次選擇數(shù)據(jù)—數(shù)據(jù)驗(yàn)證—數(shù)據(jù)驗(yàn)證—序列—輸入“=INDIRECT($D$6)”確定。

2.3 開(kāi)始時(shí)間公式輸入，首先選擇E10單元格，輸入公式”=IF(F9="","",F9)”,這個(gè)公式的作用就是把上一個(gè)項(xiàng)目狀態(tài)的結(jié)束時(shí)間復(fù)制到當(dāng)前項(xiàng)目狀態(tài)的開(kāi)始時(shí)間。依次類推，將公式下拉填充至E13單元格。

2.4 持續(xù)天數(shù)公式輸入：選中H9單元格輸入如下公式“=IF(AND(F9="",E9<>""),TODAY()-E9,IF(AND(F9<>"",E9<>""),F9-E9,""))”這個(gè)公式的意思是：如果當(dāng)前行所在狀態(tài)的開(kāi)始時(shí)間有數(shù)據(jù)，結(jié)束時(shí)間沒(méi)數(shù)據(jù)時(shí)，持續(xù)天數(shù)就為當(dāng)前系統(tǒng)時(shí)間減去開(kāi)始時(shí)間。如果當(dāng)前行所在狀態(tài)的開(kāi)始時(shí)間有數(shù)據(jù)，結(jié)束時(shí)間也有數(shù)據(jù)時(shí)，持續(xù)天數(shù)就為結(jié)束時(shí)間減去開(kāi)始時(shí)間。否則就返回空值。在H9輸入公式后下拉填充至H13即可。

2.5 項(xiàng)目狀態(tài)公式輸入：選中I9單元格輸入公式“=IF(E9="","",IF(F9="",D9,IF(F10="",D10,IF(F11="",D11,IF(F12="",D12,IF(F13="",D13,"已完成"))))))”這個(gè)公式的大概意思就是通過(guò)判斷每個(gè)項(xiàng)目狀態(tài)的結(jié)束時(shí)間來(lái)返回對(duì)應(yīng)的項(xiàng)目狀態(tài)。

2.6 在數(shù)據(jù)源單元格B1位置輸入公式：“=IFERROR(MATCH(信息錄入!$E$6,數(shù)據(jù)源!$A:$A,0),0)”這個(gè)公式的作用主要是用于判斷數(shù)據(jù)錄入單元格選擇的項(xiàng)目名是否在數(shù)據(jù)源下圖B區(qū)存在，方便后面代碼錄入。

2.7 選擇開(kāi)發(fā)工具—Visual Basic 輸入錄入信息的代碼。如下圖所示（下方有代碼明細(xì)）

代碼明細(xì)：

Sub 信息錄入()

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

MsgBox "請(qǐng)先輸入項(xiàng)目名！"

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) '項(xiàng)目名

Sheet2.Cells(a, 2) = Sheet1.Cells(9, 5) '開(kāi)始日期

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

Sheet2.Cells(a, 4) = Sheet1.Cells(9, 6) '啟動(dòng)日期

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

Sheet2.Cells(a, 6) = Sheet1.Cells(10, 6) '計(jì)劃日期

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

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

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

Sheet2.Cells(a, 10) = Sheet1.Cells(12, 6) '監(jiān)控日期

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) '負(fù)責(zé)人

Sheet2.Cells(a, 15) = Sheet1.Cells(14, 7) '項(xiàng)目介紹

End If

If i > 0 Then

Sheet2.Cells(i, 1) = Sheet1.Cells(6, 5) '項(xiàng)目名

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

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

Sheet2.Cells(i, 4) = Sheet1.Cells(9, 6) '啟動(dòng)日期

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

Sheet2.Cells(i, 6) = Sheet1.Cells(10, 6) '計(jì)劃日期

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

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

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

Sheet2.Cells(i, 10) = Sheet1.Cells(12, 6) '監(jiān)控日期

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) '負(fù)責(zé)人

Sheet2.Cells(i, 15) = Sheet1.Cells(14, 7) '項(xiàng)目介紹

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 進(jìn)度維護(hù)代碼：

Sub 進(jìn)度維護(hù)()

Dim i As Integer

i = Sheet2.Range("b1")

If i = 0 Then

MsgBox "數(shù)據(jù)庫(kù)無(wú)此項(xiàng)目的數(shù)據(jù)，請(qǐng)先錄入該項(xiàng)目的信息！"

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) '開(kāi)始日期

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

Sheet1.Cells(9, 6) = Sheet2.Cells(i, 4) '啟動(dòng)日期

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

Sheet1.Cells(10, 6) = Sheet2.Cells(i, 6) '計(jì)劃日期

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

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

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

Sheet1.Cells(12, 6) = Sheet2.Cells(i, 10) '監(jiān)控日期

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) '負(fù)責(zé)人

Sheet1.Cells(14, 7) = Sheet2.Cells(i, 15) '項(xiàng)目介紹

End If

End Sub

2.9 清除數(shù)據(jù)代碼

Sub 清除數(shù)據(jù)()

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

Sheet1.Range("e9") = ""

Sheet1.Cells(6, 5) = ""

Sheet1.Cells(14, 5) = ""

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

End Sub

提示：以上所有代碼均寫(xiě)在模塊中。插入模塊的方法見(jiàn)下圖。

3 指定宏：按下圖所示順序依次點(diǎn)擊按鈕右鍵—指定宏—信息錄入—確定。將上面寫(xiě)的代碼指定到對(duì)應(yīng)按鈕下。按同樣方式將“清除數(shù)據(jù)”和“進(jìn)度維護(hù)”指定宏即可

3.1 完成以上步驟后可錄入數(shù)據(jù)測(cè)試。

3.2 統(tǒng)計(jì)圖表：全選數(shù)據(jù)源中紅框區(qū)域的數(shù)據(jù)，點(diǎn)擊插入圖表，然后再將圖表剪切至“信息錄入”界面

3.3調(diào)整圖表大小至合適位置并設(shè)置背景顏色和格式。

3.4 匯總統(tǒng)計(jì)信息：在數(shù)據(jù)源表格的K1和M1單元格分輸入“進(jìn)行中”和“已完成”

在L1單元格輸入公式：“=COUNTA(表1[項(xiàng)目名])-N1”（用于統(tǒng)計(jì)不是已完成項(xiàng)目的個(gè)數(shù)）在N1單元格輸入公式：“=COUNTIF(表1[狀態(tài)],M1)”（用于統(tǒng)計(jì)已完成項(xiàng)目的個(gè)數(shù)）。

3.5 在“信息錄入”單元格的E19到E21分別輸入下列公式來(lái)獲取對(duì)應(yīng)的值

=數(shù)據(jù)源!L1 數(shù)據(jù)源!N1

=數(shù)據(jù)源!N1

=數(shù)據(jù)源!L1

好啦，到這里“信息錄入”工作表的功能就全部實(shí)現(xiàn)了。

4. 信息查詢：這里可以直接將“信息錄入”的工作表復(fù)制到“信息查詢”工作表。然后刪除對(duì)于的信息即可。調(diào)整頁(yè)面布局如下圖所示。

4.1 在E9單元格輸入公式：=IFERROR(IF(VLOOKUP($E$6,數(shù)據(jù)源!$A:$O,2,FALSE)=0,"",VLOOKUP($E$6,數(shù)據(jù)源!$A:$O,2,FALSE)),"")

在F9單元格輸入公式：=IFERROR(IF(VLOOKUP($E$6,數(shù)據(jù)源!$A:$O,4,FALSE)=0,"",VLOOKUP($E$6,數(shù)據(jù)源!$A:$O,4,FALSE)),"")

在G9單元格輸入公式：=IFERROR(VLOOKUP($E$6,數(shù)據(jù)源!$A:$O,3,FALSE),"")

以上公式的意思就是通過(guò)項(xiàng)目名去數(shù)據(jù)源表格中查找對(duì)應(yīng)的值。這里如果對(duì)VLOOKUP函數(shù)不熟悉的話可以單獨(dú)在網(wǎng)上去學(xué)習(xí)一下。

然后用同樣的方式將F9到G14單元格區(qū)域都通過(guò)VLOOKUP函數(shù)去獲得對(duì)應(yīng)的值。這里主要是設(shè)計(jì)到函數(shù)的靈活運(yùn)用，這里就不在詳細(xì)說(shuō)明。如果還有疑問(wèn)可直接聯(lián)系小編。

4.2 所有公式輸入完成后選擇一個(gè)錄入了數(shù)據(jù)的項(xiàng)目名：然后依次選擇所有開(kāi)始時(shí)間—插入堆積條形圖（如下圖所示）然后調(diào)整條形圖的大小和位置。

4.3 右鍵點(diǎn)擊圖表—選擇數(shù)據(jù)

4.4 點(diǎn)擊添加

4.5先選中系列值，在框選持續(xù)天數(shù)的數(shù)據(jù)。如下圖所示

4.6 將藍(lán)色區(qū)域設(shè)置為無(wú)填充，無(wú)線條

4.7 再點(diǎn)擊選擇數(shù)據(jù)—編輯

4.8 線選擇軸標(biāo)簽—在框選項(xiàng)目狀態(tài)。（如下圖所示）

4.9 在圖表類別的坐標(biāo)軸選項(xiàng)中勾選—逆序類別

5.0 設(shè)置圖標(biāo)的背景格式和字體顏色。如下圖所示

5.1 添加數(shù)據(jù)標(biāo)簽，選擇圖表右鍵添加數(shù)據(jù)標(biāo)簽即可，添加后點(diǎn)擊數(shù)據(jù)可修改字體和字體顏色

5.2 做到這里我們的“信息查詢”工作表也做完了。

6.超鏈接：頁(yè)面切換都采用超鏈接的方式實(shí)現(xiàn)。連接方式見(jiàn)下圖。這里不詳細(xì)說(shuō)明了。

結(jié)語(yǔ)：由于篇幅限制，本次的分享就到這里，由于涉及的知識(shí)點(diǎn)較多，所以有些地方講解不是很細(xì)。如果有疑問(wèn)可以在評(píng)論區(qū)留言，小編看到了會(huì)及時(shí)回復(fù)。有興趣的可以結(jié)合作品同步學(xué)習(xí)，如有分享不到位的地方還請(qǐng)諒解！