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

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

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

1、基礎數據

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

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

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

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

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

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

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

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

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

2.4 持續(xù)天數公式輸入：選中H9單元格輸入如下公式“=IF(AND(F9="",E9<>""),TODAY()-E9,IF(AND(F9<>"",E9<>""),F9-E9,""))”這個公式的意思是：如果當前行所在狀態(tài)的開始時間有數據，結束時間沒數據時，持續(xù)天數就為當前系統(tǒng)時間減去開始時間。如果當前行所在狀態(tài)的開始時間有數據，結束時間也有數據時，持續(xù)天數就為結束時間減去開始時間。否則就返回空值。在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)的結束時間來返回對應的項目狀態(tài)。

2.6 在數據源單元格B1位置輸入公式：“=IFERROR(MATCH(信息錄入!$E$6,數據源!$A:$A,0),0)”這個公式的作用主要是用于判斷數據錄入單元格選擇的項目名是否在數據源下圖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) '負責人

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) '負責人

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 "數據庫無此項目的數據，請先錄入該項目的信息！"

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) '負責人

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

End If

End Sub

2.9 清除數據代碼

Sub 清除數據()

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

Sheet1.Range("e9") = ""

Sheet1.Cells(6, 5) = ""

Sheet1.Cells(14, 5) = ""

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

End Sub

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

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

3.1 完成以上步驟后可錄入數據測試。

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

3.3調整圖表大小至合適位置并設置背景顏色和格式。

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

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

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

=數據源!L1 數據源!N1

=數據源!N1

=數據源!L1

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

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

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單元格區(qū)域都通過VLOOKUP函數去獲得對應的值。這里主要是設計到函數的靈活運用，這里就不在詳細說明。如果還有疑問可直接聯(lián)系小編。

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

4.3 右鍵點擊圖表—選擇數據

4.4 點擊添加

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

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

4.7 再點擊選擇數據—編輯

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

4.9 在圖表類別的坐標軸選項中勾選—逆序類別

5.0 設置圖標的背景格式和字體顏色。如下圖所示

5.1 添加數據標簽，選擇圖表右鍵添加數據標簽即可，添加后點擊數據可修改字體和字體顏色

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

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

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