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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

代碼明細：

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

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

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 進度維護代碼：

Sub 進度維護()

Dim i As Integer

i = Sheet2.Range("b1")

If i = 0 Then

MsgBox "數(shù)據(jù)庫無此項目的數(shù)據(jù)，請先錄入該項目的信息！"

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

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

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

提示：以上所有代碼均寫在模塊中。插入模塊的方法見下圖。

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

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

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

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

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

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

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

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

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

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

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

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

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),"")

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

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

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

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

4.4 點擊添加

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

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

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

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

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

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

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

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

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

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