企業(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
提示：以上所有代碼均寫在模塊中。插入模塊的方法見(jiàn)下圖。
3 指定宏：按下圖所示順序依次點(diǎn)擊按鈕右鍵—指定宏—信息錄入—確定。將上面寫的代碼指定到對(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ō)明了。
聯(lián)系客服
