企業項目管理系統

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

本文教程以最近發布的《企業項目管理系統》為例進行講解。

1、基礎數據

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

1.2 在下拉信息工作表中創建項目名的表，按上圖錄入信息後按Ctrl t。。

1.3 将項目名添加到名稱管理器上。選中項目名的表格依次點擊—公式—根據所選内容創建名稱—首行—确定。

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

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

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

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

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

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

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

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區存在，方便後面代碼錄入。

2.7 選擇開發工具—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) '執行日期

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

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 清除數據代碼

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 統計圖表：全選數據源中紅框區域的數據，點擊插入圖表，然後再将圖表剪切至“信息錄入”界面

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

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

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

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

=數據源!L1 數據源!N1

=數據源!N1

=數據源!L1

好啦，到這裡“信息錄入”工作表的功能就全部實現了。

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

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

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

4.4 點擊添加

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

4.6 将藍色區域設置為無填充，無線條

4.7 再點擊選擇數據—編輯

4.8 線選擇軸标簽—在框選項目狀态。（如下圖所示）

4.9 在圖表類别的坐标軸選項中勾選—逆序類别

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

5.1 添加數據标簽，選擇圖表右鍵添加數據标簽即可，添加後點擊數據可修改字體和字體顔色

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

6.超鍊接：頁面切換都采用超鍊接的方式實現。連接方式見下圖。這裡不詳細說明了。

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