模塊四：Excel在固定資產(chǎn)管理中的應(yīng)用

六EXCEL在固定資產(chǎn)管理中的應(yīng)用一固定資產(chǎn)清單（一）固定資產(chǎn)清單是用來(lái)存放所有固定資產(chǎn)數(shù)據(jù)的工作表，后續(xù)折舊費(fèi)用的分配、相關(guān)記賬憑證的生成、資產(chǎn)的分析都是建立在固定資產(chǎn)清單的基礎(chǔ)上。利用Excel建立固定資產(chǎn)清單是固定資產(chǎn)管理的前提。我們先來(lái)看一下如何建立固定資產(chǎn)清單。

固定資產(chǎn)清單通過(guò)存放與該固定資產(chǎn)相關(guān)的所有數(shù)據(jù)，實(shí)現(xiàn)對(duì)企業(yè)的固定資產(chǎn)詳細(xì)、全面的管理。清單中一般要包括如下的項(xiàng)目：資產(chǎn)名稱、資產(chǎn)編號(hào)、類別編號(hào)、類別名稱、使用部門、費(fèi)用科目、起始日期、使用年限、終止日期、資產(chǎn)狀態(tài)、增加方式、資產(chǎn)性質(zhì)、資產(chǎn)原值、資產(chǎn)凈殘值率、資產(chǎn)殘值、已計(jì)提月份、本月折舊額、本年計(jì)提月數(shù)、本年折舊額等基本項(xiàng)目。項(xiàng)目設(shè)置的多少可以根據(jù)實(shí)際情況靈活掌握，如果企業(yè)管理需要，還可以包括資產(chǎn)設(shè)備的規(guī)格型號(hào)、制造單位等一些輔助項(xiàng)目。1.查找和引用函數(shù)1.1MATCH（lookup_value,lookup_array,match_type）功能：返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置。（1）參數(shù)說(shuō)明：lookup_value：需要在數(shù)據(jù)表中查找的數(shù)值，可以是數(shù)值（數(shù)字、文本或邏輯值）或?qū)?shù)字、文本或邏輯值的單元格引用。lookup_array：可能包含所要查找的數(shù)值的連續(xù)單元格區(qū)域，可以是數(shù)組或數(shù)組引用。match_type：可能的值是數(shù)字-1、0、1，它指明EXCEL如何在第二個(gè)參數(shù)中查找第一個(gè)參數(shù)。（2）查找方式說(shuō)明：當(dāng)match_type為-1時(shí)，lookup_array必須按降序排列，函數(shù)MATCH查找大于或等于lookup_value的最小數(shù)值；當(dāng)match_type為0時(shí)，lookup_array可以按任何順序排列，函數(shù)MATCH查找等于lookup_value的第一個(gè)數(shù)值；當(dāng)match_type為1或省略時(shí)，lookup_array必須按升序排列，函數(shù)MATCH查找小于或等于lookup_value的最大數(shù)值。（3）舉例：MATCH（16，{97，52，36，15}，-1）=3MATCH（15，{20，15，12，15}，0）=2MATCH（68，{31，52，66，79}）=31.2OFFSET（reference，rows，cols，height，width）功能：以指定的引用為參照系，通過(guò)給定的偏移量得到新的引用。（1）參數(shù)說(shuō)明：reference：作為偏移量參照系的引用區(qū)域；rows：表示相對(duì)偏移量參照系左上角的單元格上（下）偏移的行數(shù)；為正數(shù)時(shí)表示向下偏移，為負(fù)數(shù)時(shí)表示向上偏移；cols：表示相對(duì)偏移量參照系左上角的單元格左（右）偏移的列數(shù)；為正數(shù)時(shí)表示向右偏移，為負(fù)數(shù)時(shí)表示向左偏移；height：表示返回的引用區(qū)域的行數(shù)；width：表示返回的引用區(qū)域的列數(shù)。（2）注意：①如果行數(shù)和列數(shù)偏移量超出工作表邊緣，函數(shù)OFFSET返回錯(cuò)誤值#REF!。②如果省略height或width，則假設(shè)其高度或?qū)挾扰creference區(qū)域相同。③函數(shù)OFFSET實(shí)際上并不移動(dòng)任何單元格或更改選定區(qū)域，它只是返回一個(gè)引用。函數(shù)OFFSET可用于任何需要將引用作為參數(shù)的函數(shù)。④height，width：必須為正數(shù)，也可以省略，如果省略，則假設(shè)其高度或?qū)挾扰c第一個(gè)參數(shù)引用的區(qū)域相同。（3）舉例：公式SUM(OFFSET(C2,1,2,3,1))將計(jì)算比單元格C2靠下1行并靠右2列的3行1列的區(qū)域的總值。1.3INDEX功能是返回表格或區(qū)域中的數(shù)值或?qū)?shù)值的引用。（1）數(shù)組形式INDEX（array,row_num,column_num）功能：返回?cái)?shù)組中指定單元格或單元格數(shù)組的數(shù)值。①參數(shù)說(shuō)明：array：?jiǎn)卧駞^(qū)域或數(shù)組常數(shù)；row_num：數(shù)組中某行的行序號(hào)，函數(shù)從該行返回?cái)?shù)值。如果省略row_num，則必須有column_num；column_num：數(shù)組中某列的列序號(hào)，函數(shù)從該列返回?cái)?shù)值。如果省略column_num，則必須有row_num。②注意：如果同時(shí)使用row_num和column_num，函數(shù)INDEX返回row_num和column_num交叉處的單元格的數(shù)值。如果數(shù)組只包含一行或一列，則相對(duì)應(yīng)的參數(shù)row_num或column-_num為可選。如果數(shù)組有多行和多列，但只使用row_num或column_num，函數(shù)INDEX返回?cái)?shù)組中的整行或整列，且返回值也為數(shù)組。如果將row_num或column_num設(shè)置為0，函數(shù)INDEX分別返回整個(gè)列或行的數(shù)組數(shù)值。如果需要使用以數(shù)組形式返回的數(shù)值時(shí)，必須指向array中的某一單元格，否則，函數(shù)INDEX返回錯(cuò)誤值#REF！。③舉例：

INDEX（{3，4，5；6，7，8}，2，3）=8。如果作為數(shù)組公式輸入，則INDEX（{3，4，5；6，7，8}，2，0）={6，7，8}

（2）引用形式INDEX（reference,row_num,column_num,area_num）功能：返回引用中的指定單元格。①參數(shù)說(shuō)明：reference：一個(gè)或多個(gè)單元格區(qū)域的引用。如果為引用輸入一個(gè)不連續(xù)的選定區(qū)域，必須用括號(hào)括起來(lái)。如果引用中的每個(gè)區(qū)域只包含一行或一列，則相應(yīng)的參數(shù)row_num或column-_num分別為可選項(xiàng)。例如，對(duì)單行的引用，可以使用函數(shù)INDEX（reference,column_num）。row_num：引用中某行的行序號(hào)，函數(shù)從該行返回一個(gè)引用。column_num：引用中某列的列序號(hào)，函數(shù)從該列返回一個(gè)引用。area_num:選擇引用中的一個(gè)區(qū)域，并返回該區(qū)域中row_num和column_num的交叉區(qū)域。選取或輸入的第一個(gè)區(qū)域序號(hào)為1，第二個(gè)為2，以此類推。如果省略area_num，函數(shù)INDEX使用區(qū)域1。②注意：

row_num、column_num和area_num必須指向reference中的單元格，否則，函數(shù)INDEX返回錯(cuò)誤值#REF！。如果省略row_num和column_num，函數(shù)INDEX返回由area_num所指定的區(qū)域。函數(shù)INDEX的結(jié)果為一個(gè)引用，且在其他公式中也被解釋為引用。根據(jù)公式的需要，函數(shù)INDEX的返回值可以作為引用或數(shù)值。③舉例：公式“CELL（“width”，INDEX（A1：B2，1，2））”等價(jià)于公式“CELL（“width”，B1）”。CELL函數(shù)的功能是返回某一引用區(qū)域的指定單元格的格式、位置或內(nèi)容等信息，此處CELL函數(shù)的功能是返回A1：B2區(qū)域的右上角單元格的寬度。這里，CELL函數(shù)將INDEX函數(shù)的返回值作為單元格引用。而公式“2*INDEX（A1：B2，1，2）”將INDEX函數(shù)的返回值解釋為B1單元格中的數(shù)字。說(shuō)明：如果要突出指標(biāo)體系之間聯(lián)系的視覺(jué)效果，可以不取消表頁(yè)中的網(wǎng)格線。選擇【工具】|【選項(xiàng)】，在【視圖】|【窗口】下取消“網(wǎng)格線”選項(xiàng)，單擊【確定】。結(jié)果如圖所示。1.固定資產(chǎn)基礎(chǔ)參數(shù)設(shè)置固定資產(chǎn)清單中涉及到的有些數(shù)據(jù)相對(duì)固定，有一定規(guī)律，為了提高輸入效率，可以將這些數(shù)據(jù)組成一個(gè)系統(tǒng)參數(shù)表，這些參數(shù)如圖所示。如果將這些數(shù)據(jù)設(shè)置為供用戶選擇的下拉列表，還需要將這些數(shù)據(jù)區(qū)域分別進(jìn)行命名，具體步驟如下：步驟1：首先建立一個(gè)Excel工作簿，保存工作簿，命名為“固定資產(chǎn)系統(tǒng)”。將該工作簿sheet1工作表標(biāo)簽改為“系統(tǒng)參數(shù)”。在該工作表中輸入上圖所示項(xiàng)目。步驟2：進(jìn)行數(shù)據(jù)區(qū)域命名。選定B3：B7區(qū)域，執(zhí)行【公式】︱【名稱管理器】命令，打開(kāi)【名稱管理器】對(duì)話框，單擊【新建】按鈕，打開(kāi)【編輯名稱】對(duì)話框，如圖所示。在【名稱】文本框中輸入“類別編號(hào)”。通過(guò)在【引用位置】文本框中輸入公式：=OFFSET(系統(tǒng)參數(shù)!$B$2,1,,COUNTA(系統(tǒng)參數(shù)!$B:$B)-1)，可以實(shí)現(xiàn)動(dòng)態(tài)調(diào)整命名區(qū)域。單擊【確定】按鈕。步驟3：用同樣的方法，可以將其他系統(tǒng)參數(shù)分別進(jìn)行數(shù)據(jù)區(qū)域命名，公式如下：類別編號(hào)=OFFSET(系統(tǒng)參數(shù)!$B$2,1,,COUNTA(系統(tǒng)參數(shù)!$B:$B)-1)類別名稱=OFFSET(系統(tǒng)參數(shù)!$C$2,1,,COUNTA(系統(tǒng)參數(shù)!$C:$C)-1)部門編號(hào)=OFFSET(系統(tǒng)參數(shù)!$D$2,1,,COUNTA(系統(tǒng)參數(shù)!$D:$D)-1)部門名稱=OFFSET(系統(tǒng)參數(shù)!$E$2,1,,COUNTA(系統(tǒng)參數(shù)!$E:$E)-1)費(fèi)用科目=OFFSET(系統(tǒng)參數(shù)!$F$2,1,,COUNTA(系統(tǒng)參數(shù)!$F:$F)-1)增加方式=OFFSET(系統(tǒng)參數(shù)!$G$2,1,,COUNTA(系統(tǒng)參數(shù)!$G:$G)-1)減少方式=OFFSET(系統(tǒng)參數(shù)!$H$2,1,,COUNTA(系統(tǒng)參數(shù)!$H:$H)-1)資產(chǎn)狀態(tài)=OFFSET(系統(tǒng)參數(shù)!$I$2,1,,COUNTA(系統(tǒng)參數(shù)!$I:$I)-1)資產(chǎn)性質(zhì)=OFFSET(系統(tǒng)參數(shù)!$J$2,1,,COUNTA(系統(tǒng)參數(shù)!$J:$J)-1)折舊方法=OFFSET(系統(tǒng)參數(shù)!$K$2,1,,COUNTA(系統(tǒng)參數(shù)!$K:$K)-1)

在具體的操作細(xì)節(jié)上，每一個(gè)系統(tǒng)參數(shù)的設(shè)置只需要修改數(shù)據(jù)區(qū)域名稱以及函數(shù)OFFSET中的第一、第四個(gè)參數(shù)，單擊【確定】按鈕，就可以很快地完成以上的區(qū)域命名。結(jié)果如圖2.固定資產(chǎn)清單格式的設(shè)置步驟1：打開(kāi)“固定資產(chǎn)系統(tǒng)”工作簿，將sheet2工作表標(biāo)簽改為“固定資產(chǎn)清單”。在該工作表中輸入如圖所示項(xiàng)目。其中A3：U3區(qū)域的項(xiàng)目包括：“資產(chǎn)名稱”、“資產(chǎn)編號(hào)”、“類別編號(hào)”、“類別名稱”、“使用部門”、“費(fèi)用科目”、“起始日期”、“使用年限”、“終止日期”、“資產(chǎn)狀態(tài)”、“增加方式”、“資產(chǎn)性質(zhì)”、“折舊方法”、“原值”、“殘值率”、“殘值”、“已計(jì)提月份”、“本月折舊額”、“本年計(jì)提月數(shù)”、“本年折舊額”、“累計(jì)折舊額”。注意：1.在本體系中取數(shù)時(shí)，“平均資產(chǎn)總額”一般應(yīng)取期初數(shù)和期末數(shù)的平均值，由于本例資產(chǎn)負(fù)債表中缺少2011年度的年初數(shù)，所以分別使用了期末數(shù)作為計(jì)算依據(jù)。2.為了保證計(jì)算結(jié)果的有效性及合理性，本例對(duì)原資產(chǎn)負(fù)債表和利潤(rùn)表的個(gè)別數(shù)據(jù)進(jìn)行了調(diào)整。步驟2：?jiǎn)卧馢2中應(yīng)該填入的是固定資產(chǎn)清單當(dāng)前日期。在單元格J2中輸入公式：=NOW()或=TODAY()，使用這兩個(gè)函數(shù)的好處是可以使折舊計(jì)算清單中的相關(guān)數(shù)據(jù)隨系統(tǒng)日期的更新而自動(dòng)更新計(jì)算。如圖所示。步驟3：如上圖所示，J2單元格顯示為日期所對(duì)應(yīng)的序列號(hào)，還需要進(jìn)行如下設(shè)置：選定J2單元格，執(zhí)行【開(kāi)始】|【數(shù)字】|【設(shè)置單元格格式】命令，彈出【設(shè)置單元格格式】對(duì)話框，選擇【數(shù)字】選項(xiàng)卡，【分類】設(shè)置為“日期”，【類型】選擇為“2001年3月”。設(shè)置好后J2單元格如圖所示。3.錄入基礎(chǔ)數(shù)據(jù)固定資產(chǎn)清單表格中需要輸入的基礎(chǔ)數(shù)據(jù)包括：資產(chǎn)名稱、資產(chǎn)編號(hào)、起始日期、使用年限、資產(chǎn)狀態(tài)、增加方式、資產(chǎn)性質(zhì)、原值、殘值率等列數(shù)據(jù)。以下內(nèi)容均在“固定資產(chǎn)清單”工作表中完成。1.“資產(chǎn)名稱”列數(shù)據(jù)直接通過(guò)鍵盤錄入。如圖2.“資產(chǎn)編號(hào)”列數(shù)據(jù)設(shè)置為文本格式，通過(guò)鍵盤輸入。本企業(yè)固定資產(chǎn)編碼方式為：部門編號(hào)+類別編號(hào)+序號(hào)；編碼方案為133。步驟：選定B4：B20，打開(kāi)【設(shè)置單元格格式】對(duì)話框。選擇【數(shù)字】選項(xiàng)卡，設(shè)置該區(qū)域【分類】為“文本”格式。通過(guò)鍵盤輸入相關(guān)資產(chǎn)編號(hào)，如圖3.“起始日期”列數(shù)據(jù)設(shè)置為日期格式，通過(guò)鍵盤輸入，顯示為“××××年×月×日”。步驟：選定G4：G20，打開(kāi)【設(shè)置單元格格式】對(duì)話框。選擇【數(shù)字】選項(xiàng)卡，設(shè)置該區(qū)域【分類】為“日期”格式?！绢愋汀窟x擇“2001年3月14日”。通過(guò)鍵盤輸入相關(guān)資產(chǎn)啟用日期，如圖4.“使用年限”列數(shù)據(jù)設(shè)置為數(shù)值格式，通過(guò)鍵盤輸入數(shù)字，顯示為“×年”。步驟：選定H4：H20，打開(kāi)【設(shè)置單元格格式】對(duì)話框。選擇【數(shù)字】選項(xiàng)卡，設(shè)置該區(qū)域【分類】為“自定義”格式?！绢愋汀窟x擇“#"年"”，如圖4-1-12所示。通過(guò)鍵盤輸入相關(guān)資產(chǎn)使用年限，如圖5.“原值”列數(shù)據(jù)設(shè)置為會(huì)計(jì)專用格式，保留兩位小數(shù)，無(wú)貨幣符號(hào)，通過(guò)鍵盤輸入。步驟：選定N4：N20，打開(kāi)【設(shè)置單元格格式】對(duì)話框。選擇【數(shù)字】選項(xiàng)卡，設(shè)置該區(qū)域【分類】為“會(huì)計(jì)專用”格式，保留兩位小數(shù)，沒(méi)有貨幣符號(hào)。通過(guò)鍵盤輸入相關(guān)資產(chǎn)原值，如圖6.“殘值率”列數(shù)據(jù)設(shè)置為百分比格式，保留兩位小數(shù)，通過(guò)鍵盤輸入。步驟：選定O4：O20，打開(kāi)【設(shè)置單元格格式】對(duì)話框。選擇【數(shù)字】選項(xiàng)卡，設(shè)置該區(qū)域【分類】為“百分比”格式，保留兩位小數(shù)。通過(guò)鍵盤輸入相關(guān)資產(chǎn)殘值率，如圖所示。7.“資產(chǎn)狀態(tài)”、“增加方式”、“資產(chǎn)性質(zhì)”、“折舊方法”等列數(shù)據(jù)通過(guò)設(shè)置數(shù)據(jù)有效性選擇輸入?！百Y產(chǎn)狀態(tài)”、“增加方式”、“資產(chǎn)性質(zhì)”、“折舊方法”等列數(shù)據(jù)都具有一定規(guī)律性，且數(shù)據(jù)相對(duì)固定。這些列中需要填入的數(shù)據(jù)在“系統(tǒng)參數(shù)”表中已經(jīng)定義，可以將這些列設(shè)置為可供用戶選擇的下拉列表，以提高數(shù)據(jù)輸入效率。步驟：選定“資產(chǎn)狀態(tài)”所在的單元格區(qū)域J4：J19，執(zhí)行【數(shù)據(jù)】|【數(shù)據(jù)工具】|【數(shù)據(jù)有效性】|【數(shù)據(jù)有效性】命令，彈出【數(shù)據(jù)有效性】對(duì)話框，在【設(shè)置】選項(xiàng)卡中的【有效性條件】區(qū)域中的【允許】下拉列表中選擇“序列”，然后在【來(lái)源】文本框中鍵入“=資產(chǎn)狀態(tài)”，單擊【確定】按鈕。如圖“增加方式”、“資產(chǎn)性質(zhì)”、“折舊方法”項(xiàng)目都可以采用數(shù)據(jù)有效性設(shè)置，形成下拉列表，以方便數(shù)據(jù)的輸入。利用設(shè)置好的下拉列表，完成“資產(chǎn)狀態(tài)”、“增加方式”、“資產(chǎn)性質(zhì)”、“折舊方法”列數(shù)據(jù)輸入，如圖

所示。熟練固定資產(chǎn)清單的設(shè)計(jì)及快速錄入數(shù)據(jù)。四EXCEL在固定資產(chǎn)管理中的應(yīng)用二固定資產(chǎn)清單（二）在任務(wù)一的基礎(chǔ)上完成“固定資產(chǎn)清單”工作表其他列公式的設(shè)置。固定資產(chǎn)清單中除了基礎(chǔ)數(shù)據(jù)列需要輸入外，其他各列均可通過(guò)設(shè)置公式計(jì)算得到。需要設(shè)置公式的列包括類別編號(hào)、類別名稱、使用部門、費(fèi)用科目、終止日期、殘值、已計(jì)提月份、本月折舊額、本年計(jì)提月數(shù)、本年折舊額、累計(jì)折舊額等。本任務(wù)在“固定資產(chǎn)清單”工作表中完成。1.折舊函數(shù)1.1SLN(cost,salvage,life)功能：用平均年限法計(jì)算某項(xiàng)資產(chǎn)折舊額。（1）參數(shù)說(shuō)明：cost：資產(chǎn)原值；salvage：資產(chǎn)在折舊期末的價(jià)值（也稱為資產(chǎn)殘值）；life：折舊期限（有時(shí)也稱作資產(chǎn)的使用壽命）。若為年，計(jì)算的是年折舊額；若為月，則計(jì)算的是月折舊額。（2）注意：如果采用工作量法計(jì)算折舊，應(yīng)在SLN函數(shù)的life參數(shù)中輸入預(yù)計(jì)的總工作量，這樣可以得到每單位工作量的折舊額，然后根據(jù)每期的工作量和每單位工作量的折舊額計(jì)算各期的折舊額。1.2DB(cost,salvage,life,period,month)功能：用定率余額遞減法計(jì)算某項(xiàng)資產(chǎn)折舊額。（1）參數(shù)說(shuō)明：cost：資產(chǎn)原值；salvage：資產(chǎn)在折舊期末的價(jià)值（也稱為資產(chǎn)殘值）；life：折舊期限（有時(shí)也稱作資產(chǎn)的使用壽命）；period：需要計(jì)算折舊值的期間。（2）注意：period必須使用與life相同的單位；month為第一年的月份數(shù)，如省略，則假設(shè)為12。1.3DDB(cost,salvage,life,period,factor)功能：用雙倍余額遞減法或其他指定方法，計(jì)算某項(xiàng)資產(chǎn)在指定期間內(nèi)的折舊額。參數(shù)說(shuō)明：cost：資產(chǎn)原值；salvage：資產(chǎn)在折舊期末的價(jià)值（也稱為資產(chǎn)殘值）；life：折舊期限（有時(shí)也稱作資產(chǎn)的使用壽命）；period：需要計(jì)算折舊值的期間，period必須使用與life相同的單位；factor：余額遞減速率。如果factor被省略，則假設(shè)為2（雙倍余額遞減法）；這五個(gè)參數(shù)都必須為正數(shù)。1.4VDB(cost,salvage,life,start_period,end_period,factor,no_switch)功能：用雙倍余額遞減法或其他指定的方法，計(jì)算指定的任何期間內(nèi)的資產(chǎn)折舊額。（1）參數(shù)說(shuō)明：cost、salvage、life、factor同DDB說(shuō)明；start_period：進(jìn)行折舊計(jì)算的起始期間，它必須與life的單位相同；end-_period：進(jìn)行折舊計(jì)算的截止期間，它必須與life的單位相同；no-_switch：邏輯值，指定當(dāng)按直線法計(jì)算的折舊額大于按余額遞減計(jì)算的折舊額時(shí)，是否轉(zhuǎn)用直線折舊法。如果no_switch為TRUE，即使按直線法計(jì)算的折舊額大于按余額遞減計(jì)算值，Excel也不轉(zhuǎn)用直線折舊法計(jì)算折舊。如果no_switch為FALSE或被忽略，且按直線法計(jì)算的折舊額大于余額遞減計(jì)算值時(shí)，Excel將轉(zhuǎn)用直線折舊法計(jì)算折舊。（2）注意：以上各參數(shù)除no_switch外必須都為正數(shù)。1.5SYD(cost,salvage,life,per)功能：用年數(shù)總和法計(jì)算某項(xiàng)資產(chǎn)的折舊額。參數(shù)說(shuō)明：cost：資產(chǎn)原值；salvage：資產(chǎn)在折舊期末的價(jià)值（也稱為資產(chǎn)殘值）；life：折舊期限（有時(shí)也稱作資產(chǎn)的使用壽命）；per：期間，其單位與life相同。1.“類別編號(hào)”、“類別名稱”、“使用部門”列公式1.1“類別編號(hào)”列公式設(shè)置步驟1：由于“資產(chǎn)編號(hào)”列已經(jīng)通過(guò)手工輸入，編碼方式為：部門編號(hào)+類別編號(hào)+序號(hào)，編碼方案為133。因此可以利用Excel中的MID函數(shù)從資產(chǎn)編號(hào)中取得“類別編號(hào)”。例如“資產(chǎn)編號(hào)”是“1011001”，在編號(hào)的左邊第一位“1”是“使用部門”辦公室的編號(hào)，左邊第二到第四位“011”是“類別編號(hào)”，表示房屋。因此，C4單元格公式為：=MID（B4,2,3），如圖所示。步驟2：由于每項(xiàng)固定資產(chǎn)“類別編號(hào)”的計(jì)算方法相同，利用填充柄將該公式復(fù)制到C5：C20中所有填寫(xiě)固定資產(chǎn)“類別編號(hào)”的單元格中。1.2“類別名稱”列公式設(shè)置步驟1：“類別名稱”與“類別編號(hào)”具有對(duì)應(yīng)關(guān)系，可以采用查詢函數(shù)，根據(jù)“類別編號(hào)”查詢“類別名稱”。D4單元格公式為：=INDEX(類別名稱,MATCH(MID(B4,2,3),類別編號(hào),0))或者=LOOKUP(C4,類別編號(hào),類別名稱)，如圖

所示。步驟2：由于每項(xiàng)固定資產(chǎn)“類別名稱”的計(jì)算方法相同，利用填充柄將該公式復(fù)制到D5：D20中所有填寫(xiě)固定資產(chǎn)“類別名稱”的單元格中。1.3“使用部門”列公式設(shè)置步驟1：結(jié)合“類別編號(hào)”、“類別名稱”列公式所用知識(shí)點(diǎn)，設(shè)計(jì)“使用部門”列公式。E4單元格公式為：=INDEX(部門名稱,MATCH(MID(B4,1,1),部門編號(hào),0))或者=LOOKUP(MID(B4,1,1),部門編號(hào),部門名稱)，如圖所示步驟2：由于每項(xiàng)固定資產(chǎn)“使用部門”的計(jì)算方法相同，利用填充柄將該公式復(fù)制到E5：E20中所有填寫(xiě)固定資產(chǎn)“使用部門”的單元格中。2.“費(fèi)用科目”列公式每月計(jì)提的固定資產(chǎn)折舊費(fèi)，應(yīng)根據(jù)用途計(jì)入相關(guān)資產(chǎn)的成本或者當(dāng)前損益，借記“制造費(fèi)用”、“管理費(fèi)用”等科目，在固定資產(chǎn)清單中設(shè)置“費(fèi)用科目”列，便于日后費(fèi)用的歸集?！百M(fèi)用科目”列的設(shè)置可以利用【數(shù)據(jù)有效性】的下拉列表功能來(lái)輸入，前面已經(jīng)多次講解，此處不再贅述。步驟1：“費(fèi)用科目”列還可以利用VLOOKUP函數(shù)從“系統(tǒng)參數(shù)”表中取得。F4單元格公式為：=VLOOKUP(E4,系統(tǒng)參數(shù)!$E$3:$F$7,2,FALSE)。步驟2：利用填充柄復(fù)制到其他固定資產(chǎn)“費(fèi)用科目”單元格中。3.“終止日期”列公式固定資產(chǎn)應(yīng)當(dāng)按月計(jì)提折舊，通常對(duì)當(dāng)月增加的固定資產(chǎn)，當(dāng)月不提折舊，從下月起計(jì)提；對(duì)當(dāng)月減少的固定資產(chǎn)，當(dāng)月照提，從下月起不提折舊。因此終止日期的設(shè)置主要是起到提醒該項(xiàng)固定資產(chǎn)是否已經(jīng)到了停止計(jì)提折舊的作用。步驟1：終止日期的計(jì)算與起始日期和使用年限有關(guān)。I4單元格公式為：=DATE(YEAR(G4)+H4,MONTH(G4),DAY(G4))。步驟2：由于每個(gè)固定資產(chǎn)終止日期的計(jì)算都是相同的方法，利用填充柄將該公式復(fù)制到其他固定資產(chǎn)“終止日期”的單元格中，如圖4.“殘值”列公式步驟1：固定資產(chǎn)凈殘值決定于其原值和殘值率，利用公式“原值”、“殘值率”兩列中取得數(shù)據(jù)計(jì)算得到。P4單元格公式為：=N4*O4。步驟2：利用填充柄將該公式復(fù)制到其他固定資產(chǎn)“殘值”單元格中，如圖5.“已計(jì)提月份”列公式已計(jì)提月份是指從起始日期開(kāi)始到當(dāng)前日期，該項(xiàng)固定資產(chǎn)已經(jīng)計(jì)提折舊的月份。我國(guó)會(huì)計(jì)制度規(guī)定當(dāng)月新增的固定資產(chǎn)從下月開(kāi)始計(jì)算折舊，因此，固定資產(chǎn)如果是該月新增，則已計(jì)提月份為0；如果不是該月新增，則計(jì)算出來(lái)的月份數(shù)的差額還應(yīng)減去1，才能得出正確的已計(jì)提折舊的月份數(shù)。Q4單元格公式為：方法一：=(12-MONTH(G4))+((YEAR($J$2)-YEAR(G4)-1)*12+MONTH($J$2)-1)公式解析：把已計(jì)提月份劃分為三個(gè)部分：一是起始當(dāng)年計(jì)提的月份“12-MONTH(G4)”；二是中間的整年份已計(jì)提的月份“((YEAR($J$2)-YEAR(G4)-1)*12”；三是計(jì)提當(dāng)年已計(jì)提的月份“MONTH($J$2)”，三部分相加后再減去1所得的就是該項(xiàng)固定資產(chǎn)總共已計(jì)提的月份。方法二：=(YEAR($J$2)-YEAR(G4))*12+MONTH($J$2)-MONTH(G4)-1公式解析：根據(jù)當(dāng)前日期和起始日期之間的月份差來(lái)計(jì)算已計(jì)提折舊的月份數(shù)，計(jì)算出來(lái)的月份數(shù)的差額再減去1；方法三：=INT(DAYS360(G4,$J$2)/30)-1公式解析：根據(jù)函數(shù)DAYS360按照一年360天的算法（每個(gè)月以30天計(jì)，一年共計(jì)12個(gè)月），返回兩日期間相差的天數(shù)，由相差的天數(shù)轉(zhuǎn)換為月份取整后減去1，可以得到該項(xiàng)固定資產(chǎn)總共已計(jì)提的月份。以上三種方法都可以用來(lái)計(jì)算已計(jì)提月份，但有時(shí)計(jì)算出來(lái)的結(jié)果會(huì)有誤差，一個(gè)企業(yè)選擇其中一種不會(huì)影響其折舊總額，介紹給大家是為了拓展解決問(wèn)題的思路。利用填充柄將該公式復(fù)制到其他固定資產(chǎn)“已計(jì)提月份”單元格中。6.設(shè)置到期提醒當(dāng)月減少的固定資產(chǎn)，當(dāng)月計(jì)提折舊，下月起不提折舊。通過(guò)在固定資產(chǎn)清單中提供到期提醒設(shè)置，避免使用者忽略到期時(shí)間而計(jì)提折舊帶來(lái)不必要的麻煩。可以利用Excel中的條件格式來(lái)設(shè)置到期提醒。為說(shuō)明問(wèn)題，將固定資產(chǎn)清單做如下臨時(shí)更改：G8單元格數(shù)值改為“2009年9月1日”，G13單元格數(shù)值改為“2007年12月1日”。步驟1：選定A4：U20單元格區(qū)域，執(zhí)行【開(kāi)始】|【樣式】|【條件格式】|【新建規(guī)則】命令，打開(kāi)【新建格式規(guī)則】對(duì)話框，選擇規(guī)則類型為“使用公式確定要設(shè)置格式的單元格”，在“為符合此公式的值設(shè)置格式”文本框中設(shè)置公式：=$Q4>=$H4*12，單擊【格式】按鈕，設(shè)置符合公式要求的單元格為紅色背景，如圖步驟2：效果如圖7.“本月折舊額”列公式我國(guó)大部分企業(yè)的固定資產(chǎn)折舊一般都采用直線折舊法，假設(shè)我們先考慮所有固定資產(chǎn)均采用直線折舊法，則在計(jì)算折舊額時(shí)只需要知道三個(gè)基本因素（原值、預(yù)計(jì)凈殘值、使用年限），就可以計(jì)算出當(dāng)期應(yīng)提折舊。方法一：采用Excel的內(nèi)置折舊函數(shù)SLN來(lái)計(jì)算固定資產(chǎn)折舊額。R4公式單元格公式為：=SLN(N4,P4,H4*12)利用填充柄將該公式復(fù)制到其他固定資產(chǎn)“本月折舊額”單元格中，如圖方法二：直接用基本運(yùn)算功能來(lái)計(jì)算折舊，這種方法比較簡(jiǎn)單，在此就不再顯示圖表。公式設(shè)置：=(N4-P4)/(12*H4)公式解析：=（原值-預(yù)計(jì)凈殘值）/使用期限通過(guò)以上的計(jì)算，有些固定資產(chǎn)的折舊計(jì)算出來(lái)后可能會(huì)出現(xiàn)很多小數(shù)位數(shù)，可以通過(guò)四舍五入函數(shù)的將其保留為兩位小數(shù)，進(jìn)一步完善的公式為。公式設(shè)置：=ROUND(SLN(N4,P4,H4*12),2)如果R5、R6單元格分別采用“雙倍余額遞減法”和“年數(shù)總和法”，則單元格R5、R6中輸入公式分別為：R5=DDB(N5,P5,H5,INT(Q5/12)+1)/12R6=SYD(N6,P6,H6,INT(Q6/12)+1)/128.“本年計(jì)提月數(shù)”列公式我國(guó)會(huì)計(jì)制度對(duì)固定資產(chǎn)的折舊有如下規(guī)定：（1）當(dāng)月啟用的設(shè)備從下月開(kāi)始計(jì)算折舊；（2）設(shè)備非本年開(kāi)始啟用，且本年全年一直使用，則本年每月均應(yīng)進(jìn)行折舊；（3）設(shè)備本年開(kāi)始啟用，且一直用到年末，則本年折舊月數(shù)從啟用的下一個(gè)月起至年末。由于固定資產(chǎn)的使用壽命一般都在幾年以上，所以本年啟用的設(shè)備本年就停用的情況可以不予考慮。在判斷本年折舊月份時(shí)，可以分為兩種情況：一是如果起始的年份小于當(dāng)前年份，并且當(dāng)前年份小于起始年份和使用年限之和，則表示資產(chǎn)全年處于正常使用狀態(tài)；二是本年內(nèi)到期，則本年應(yīng)折舊的月份數(shù)應(yīng)為起始月份數(shù)?；谝陨系姆治?，S4單元格公式為：=IF(J4="報(bào)廢",0,IF(AND(YEAR(G4)<YEAR($J$2),YEAR($J$2)<(YEAR(G4)+H4)),12,MONTH(G4)))利用填充柄將該公式復(fù)制到其他固定資產(chǎn)“本年計(jì)提月數(shù)”單元格中，如圖9.“本年折舊額”列公式本年應(yīng)計(jì)提折舊總額即為月折舊額乘以本年折舊月份數(shù)。因此T4單元格公式為：=R4*S4利用填充柄將該公式復(fù)制到其他固定資產(chǎn)“本年折舊額”單元格中，如圖10.“累計(jì)折舊額”列公式因?yàn)橹本€折舊法是按照固定資產(chǎn)使用年限平均計(jì)算年折舊額的計(jì)算方法，每期的折舊額是相同的。所以累計(jì)折舊只需要通過(guò)已計(jì)提月份和本期計(jì)提折舊額就可以直接計(jì)算出來(lái)了。U4單元格公式為：=(Q4+1)*R4利用填充柄將該公式復(fù)制到其他固定資產(chǎn)“累計(jì)折舊”單元格中，如圖熟練掌握各類折舊函數(shù)的靈活使用。四EXCEL在固定資產(chǎn)管理中的應(yīng)用三固定資產(chǎn)卡片固定資產(chǎn)清單中包含了所有的固定資產(chǎn)信息，但當(dāng)只需要查看某一項(xiàng)固定資產(chǎn)時(shí)，該表格就顯得比較繁雜，不易于查找。固定資產(chǎn)卡片是按照固定資產(chǎn)項(xiàng)目開(kāi)設(shè)，用以進(jìn)行固定資產(chǎn)明細(xì)核算的賬簿。在一些管理比較正規(guī)的企業(yè)，大型的機(jī)器設(shè)備類固定資產(chǎn)通常會(huì)通過(guò)固定資產(chǎn)卡片進(jìn)行管理。固定資產(chǎn)卡片中的數(shù)據(jù)，可以利用Excel中的公式從固定資產(chǎn)清單中取得。以固定資產(chǎn)卡片中的數(shù)據(jù)為基礎(chǔ)，還可以利用公式對(duì)折舊額的相關(guān)數(shù)據(jù)進(jìn)行計(jì)算。本任務(wù)的基本流程包括：設(shè)置固定資產(chǎn)卡片格式；錄入相關(guān)單元格公式；輸入固定資產(chǎn)編號(hào)，生成固定資產(chǎn)卡片?？ㄆ谱?/p>

步驟1：打開(kāi)“固定資產(chǎn)系統(tǒng)”工作簿，將sheet3工作表標(biāo)簽改為“固定資產(chǎn)卡片”步驟2：在“固定資產(chǎn)卡片”工作表中輸入如圖所示表格項(xiàng)目。步驟3：合并及居中單元格區(qū)域C2：I2，設(shè)置文字的字體為“華文隸書(shū)”、字號(hào)為“26”，顏色為“藍(lán)色”，下劃線為“會(huì)計(jì)用雙下劃線”，調(diào)整行高至合適的數(shù)值。如圖。步驟4：將表格項(xiàng)目所在單元格填充為灰色，并設(shè)置對(duì)齊方式和邊框，適當(dāng)調(diào)整行高和列寬。設(shè)置后的效果如圖步驟5：設(shè)置卡片編號(hào)的長(zhǎng)度。假設(shè)卡片編號(hào)的長(zhǎng)度是固定的，為了輸入方便，我們可以提前設(shè)置好單元格D3的格式，例如卡片編號(hào)的長(zhǎng)度是4位，不夠4位的前面補(bǔ)“0”，具體操作步驟為：選擇單元格D3，打開(kāi)【設(shè)置單元格格式】對(duì)話框【數(shù)字】選項(xiàng)卡，在【自定義】分類中，【類型】文本框中輸入“0000”即可。步驟6：設(shè)置固定資產(chǎn)卡片的日期?？ㄆ娜掌诳梢灾苯尤∽杂凇肮潭ㄙY產(chǎn)清單”中的日期，在單元格I3中輸入公式：=固定資產(chǎn)清單!J2，并且要將單元格I3的格式設(shè)置為日期型格式，否則將顯示為日期所對(duì)應(yīng)的序列號(hào)。如圖所示，計(jì)算當(dāng)前日期。步驟7：固定資產(chǎn)編號(hào)的提示設(shè)置。每一項(xiàng)固定資產(chǎn)都有唯一的編號(hào)，編號(hào)的信息直接會(huì)影響到其余固定資產(chǎn)信息的取得，因此我們首先設(shè)置單元格D4的輸入提示。具體步驟為：選定單元格D4，執(zhí)行【數(shù)據(jù)】|【數(shù)據(jù)工具】|【數(shù)據(jù)有效性】下拉菜單中的【數(shù)據(jù)有效性】命令，彈出【數(shù)據(jù)有效性】對(duì)話框，單擊【輸入信息】選項(xiàng)卡，選擇“選定單元格時(shí)顯示輸入信息”復(fù)選框，然后在【輸入信息】文本框中鍵入“請(qǐng)輸入要查詢制作卡片的固定資產(chǎn)編號(hào)”，如圖所示。單擊【確定】按鈕后，關(guān)閉【數(shù)據(jù)有效性】對(duì)話框，選定工作表中的單元格D4，此時(shí)屏幕上將顯示如圖所示的提示信息。步驟8：固定資產(chǎn)編號(hào)的下拉列表設(shè)置。步驟6中我們?cè)O(shè)置了輸入的提示信息，為了提高輸入的準(zhǔn)確度和效率，還可以進(jìn)一步將該單元格設(shè)置成下拉列表式輸入。關(guān)于數(shù)據(jù)下拉列表的設(shè)置方法我們?cè)谇懊嬉呀?jīng)多次講解，在此需要強(qiáng)調(diào)的是作為數(shù)據(jù)源的區(qū)域必須提前進(jìn)行區(qū)域命名。具體步驟為：將“固定資產(chǎn)清單”中的“資產(chǎn)編號(hào)”區(qū)域進(jìn)行命名，如圖所示。公式設(shè)置為：=OFFSET(固定資產(chǎn)清單!$B$3,1,,COUNTA(固定資產(chǎn)清單!$B:$B)-1)然后再選擇單元格D4，打開(kāi)【數(shù)據(jù)有效性】對(duì)話框，單擊【設(shè)置】選項(xiàng)卡，在【允許】文本框中選擇“序列”，【來(lái)源】文本框中輸入公式：=資產(chǎn)編號(hào)。如圖。步驟9：固定資產(chǎn)卡片中的其他信息，如“固定資產(chǎn)名稱”、“類別編號(hào)”、“類別名稱”、“增加方式”、“部門名稱”、“使用狀況”、“原值”、“殘值”、“折舊方法”、“已計(jì)提月份”、“開(kāi)始使用日期”、“費(fèi)用科目”等都可以通過(guò)公式的設(shè)置，取自于“固定資產(chǎn)清單”。公式設(shè)置為：類別編號(hào)：D5=INDEX(固定資產(chǎn)清單!C$4:C$500,MATCH($D$4,固定資產(chǎn)清單!B$4:B$500,0))增加方式：D6=INDEX(固定資產(chǎn)清單!K$4:K$500,MATCH($D$4,固定資產(chǎn)清單!B$4:B$500,0))使用狀況：D7=INDEX(固定資產(chǎn)清單!J$4:J$500,MATCH($D$4,固定資產(chǎn)清單!B$4:B$500,0))原值：D8=INDEX(固定資產(chǎn)清單!N$4:N$500,MATCH($D$4,固定資產(chǎn)清單!B$4:B$500,0))折舊方法：D9=INDEX(固定資產(chǎn)清單!M$4:M$500,MATCH($D$4,固定資產(chǎn)清單!B$4:B$500,0))固定資產(chǎn)名稱：F4=INDEX(固定資產(chǎn)清單!A$4:A$500,MATCH($D$4,固定資產(chǎn)清單!B$4:B$500,0))類別名稱：F5=INDEX(固定資產(chǎn)清單!D$4:D$500,MATCH($D$4,固定資產(chǎn)清單!B$4:B$500,0))部門名稱：F6=INDEX(固定資產(chǎn)清單!E$4:E$500,MATCH($D$4,固定資產(chǎn)清單!B$4:B$500,0))使用年限：F7=INDEX(固定資產(chǎn)清單!H$4:H$500,MATCH($D$4,固定資產(chǎn)清單!B$4:B$500,0))凈殘值率：F8=INDEX(固定資產(chǎn)清單!O$4:O$500,MATCH($D$4,固定資產(chǎn)清單!B$4:B$500,0))已提折舊月數(shù)：F9=INDEX(固定資產(chǎn)清單!Q$4:Q$500,MATCH($D$4,固定資產(chǎn)清單!B$4:B$500,0))開(kāi)始使用日期：H7=INDEX(固定資產(chǎn)清單!G$4:G$500,MATCH($D$4,固定資產(chǎn)清單!B$4:B$500,0))凈殘值：H8=D8*F8費(fèi)用科目：H9=INDEX(固定資產(chǎn)清單!F$4:F$500,MATCH($D$4,固定資產(chǎn)清單!B$4:B$500,0))公式的運(yùn)算結(jié)果如圖熟練掌握固定資產(chǎn)卡片的設(shè)計(jì)。四EXCEL在固定資產(chǎn)管理中的應(yīng)用四固定資產(chǎn)分析固定資產(chǎn)是企業(yè)用來(lái)改變勞動(dòng)對(duì)象的勞動(dòng)資料，與其他資產(chǎn)相比，具有兩個(gè)主要特點(diǎn)：一是使用年限長(zhǎng)，能多次加入生產(chǎn)過(guò)程并保持其原有實(shí)物形態(tài)；二是單位價(jià)值較高，其價(jià)值隨著使用的磨損逐漸部分地通過(guò)折舊形式轉(zhuǎn)移到新產(chǎn)品中去。

企業(yè)要求對(duì)固定資產(chǎn)的情況進(jìn)行分析。

固定資產(chǎn)分析的主要目的是及時(shí)掌握固定資產(chǎn)的使用狀況和資金占用情況，為固定資產(chǎn)管理提供依據(jù)。固定資產(chǎn)分析中折舊分析是固定資產(chǎn)管理的重要內(nèi)容。折舊是固定資產(chǎn)在使用過(guò)程中逐漸損耗而消失的那部分價(jià)值，這部分價(jià)值應(yīng)該以折舊費(fèi)用的形式計(jì)入各期成本費(fèi)用，并從企業(yè)的營(yíng)業(yè)收入中得到補(bǔ)償，轉(zhuǎn)化為貨幣資金，從而為固定資產(chǎn)的更新提供可能。本任務(wù)以第一節(jié)建立的固定資產(chǎn)清單為基礎(chǔ)，基本流程包括：利用數(shù)據(jù)透視表編制固定資產(chǎn)折舊費(fèi)用分配表，并生成本期憑證；利用數(shù)據(jù)透視表對(duì)固定資產(chǎn)新舊程度進(jìn)行分析；利用數(shù)據(jù)透視表和數(shù)據(jù)透視圖對(duì)固定資產(chǎn)構(gòu)成進(jìn)行分析。1.使用數(shù)據(jù)透視表編制固定資產(chǎn)折舊費(fèi)用分配表數(shù)據(jù)透視表是一種交互式報(bào)表，可以各種不同方式靈活地展示數(shù)據(jù)的特征。固定資產(chǎn)管理中，由于受益對(duì)象（使用部門）不同，折舊費(fèi)的借記科目也不同，因此需要對(duì)固定資產(chǎn)的折舊按受益對(duì)象（使用部門）進(jìn)行分配匯總。利用數(shù)據(jù)透視表來(lái)編制折舊費(fèi)用分配表，具體步驟為：步驟1：打開(kāi)“固定資產(chǎn)系統(tǒng)”工作簿，新建“折舊費(fèi)用分配”工作表，選定C5單元格。執(zhí)行【插入】|【表】|【數(shù)據(jù)透視表】下拉菜單中的【數(shù)據(jù)透視表】命令，在彈出的【創(chuàng)建數(shù)據(jù)透視表】對(duì)話框中，單擊“選擇一個(gè)表或區(qū)域”單選按鈕。

在【表/區(qū)域】文本框輸入建立數(shù)據(jù)透視表的數(shù)據(jù)源區(qū)域?yàn)椤肮潭ㄙY產(chǎn)清單！$A$3:$U$20”，或通過(guò)單擊右側(cè)的折疊按鈕直接選擇數(shù)據(jù)源區(qū)域?！具x擇放置數(shù)據(jù)透視表的位置】為“現(xiàn)有工作表”單選按鈕。如圖。步驟2：?jiǎn)螕簟敬_定】按鈕，在彈出的【數(shù)據(jù)透視表字段列表】對(duì)話框中，選擇“費(fèi)用科目”、“使用部門”字段添加到【行標(biāo)簽】中、選擇“原值”、“本月折舊額”字段添加到【數(shù)值】中。數(shù)據(jù)透視表制作完成，如圖。步驟3：格式化數(shù)據(jù)透視表，使其美觀、易懂。合并及居中單元格區(qū)域C3：F3，設(shè)置文字的字體為“華文隸書(shū)”、字號(hào)為“26”，顏色為“藍(lán)色”，下劃線為“會(huì)計(jì)專用雙下劃線”，合并及居中單元格區(qū)域D4：E4，設(shè)置公式為=NOW()，日期顯示格式如圖所示。隱藏第五行。選擇【設(shè)計(jì)】|【布局】|【分類匯總】|【在組的頂部顯示所有分類匯總】選項(xiàng)。選擇【設(shè)計(jì)】|【布局】|【報(bào)表布局】|【以大綱形式顯示】選項(xiàng)。選擇【設(shè)計(jì)】|【布局】|【空行】|【在每個(gè)項(xiàng)目后插入空行】選項(xiàng)。將E列、F列字段名分別改為“原值”、“本月折舊額”。畫(huà)表格線，設(shè)置E列、F列數(shù)字為“會(huì)計(jì)專用”格式，保留兩位小數(shù)，無(wú)貨幣符號(hào)。設(shè)置對(duì)齊方式，調(diào)整行高列寬至合適的數(shù)值。2.記賬憑證的生成折舊費(fèi)用分配表將計(jì)提折舊額分配到有關(guān)成本和費(fèi)用中，它是制作計(jì)提折舊記賬憑證的依據(jù)。下面介紹計(jì)提折舊記賬憑證的生成。步驟1：新建“記賬憑證”工作表，制作如下圖所示格式，并輸入所示項(xiàng)目。步驟2：本工作表中借貸方金額通過(guò)公式從“折舊費(fèi)用分配”表中取得。相關(guān)單元格公式分別為：D3=NOW()E5=INDEX(折舊費(fèi)用分配!$E:$E,MATCH(B4,折舊費(fèi)用分配!$B:$B,0))E6=INDEX(折舊費(fèi)用分配!$E:$E,MATCH(B5,折舊費(fèi)用分配!$B:$B,0))F7=INDEX(折舊費(fèi)用分配!$E:$E,MATCH("總計(jì)",折舊費(fèi)用分配!$B:$B,0))E9=SUM(D4:D7)F9=SUM(E4:E7)3.固定資產(chǎn)新舊程度分析利用數(shù)據(jù)透視表對(duì)固定資產(chǎn)新舊程度進(jìn)行分析。步驟1：新建“固定資產(chǎn)新舊程度分析”工作表，選定C4單元格。打開(kāi)【創(chuàng)建數(shù)據(jù)透視表】對(duì)話框，單擊“選擇一個(gè)表或區(qū)域”單選按鈕。在【表/區(qū)域】中輸入建立數(shù)據(jù)透視表的數(shù)據(jù)源區(qū)域?yàn)椤肮潭ㄙY產(chǎn)清單！$A$3:$U$20”，或通過(guò)單擊右側(cè)的折疊按鈕直接選擇數(shù)據(jù)源區(qū)域?！具x擇放置數(shù)據(jù)透視表的位置】為“現(xiàn)有工作表”單選按鈕。如圖。步驟2：?jiǎn)螕簟敬_定】按鈕，在彈出的【數(shù)據(jù)透視表字段列表】對(duì)話框中，選擇“使用部門”、“資產(chǎn)名稱”字段添加到【行標(biāo)簽】、選擇“原值”、“累計(jì)折舊額”字段添加到【數(shù)值】。如圖。步驟3：選定數(shù)據(jù)透視表中任一個(gè)單元格，執(zhí)行【選項(xiàng)】|【工具】|【公式】|【計(jì)算字段】命令，打開(kāi)【插入計(jì)算字段】對(duì)話框。在【名稱：】復(fù)合框中輸入“折余價(jià)值”，在【公式】文本框中輸入“=原值-累計(jì)折舊額”。如圖

所示。單擊【確定】按鈕，數(shù)據(jù)透視表中增加了一列：“求和項(xiàng)：折余價(jià)值”。步驟4：選定數(shù)據(jù)透視表中任一個(gè)單元格，打開(kāi)【插入計(jì)算字段】對(duì)話框。在【名稱：】復(fù)合框中輸入“折余價(jià)值占原值比重”，在【公式】文本框中輸入“=折余價(jià)值/原值”。單擊【確定】按鈕，在數(shù)據(jù)透視表中增加了一列：“求和項(xiàng)：折余價(jià)值占原值比重”。步驟3、步驟4結(jié)果如圖。步驟5：格式化數(shù)據(jù)透視表，使其美觀、易懂。合并及居中單元格區(qū)域C2：H2，設(shè)置文字的字體為“華文隸書(shū)”、字號(hào)為“26”，顏色為“藍(lán)色”，下劃線為“會(huì)計(jì)專用雙下劃線”，合并及居中單元格區(qū)域E3：F3，設(shè)置公式為=NOW()，調(diào)整日期顯示格式，隱藏第四行。選擇【設(shè)計(jì)】|【布局】|【報(bào)表布局】|【以大綱形式顯示】選項(xiàng)。將E列、F列、G列、H列字段名分別改為“原值”、“累計(jì)折舊額”、“折余價(jià)值”、“折余價(jià)值占原值的比重”。畫(huà)表格線，設(shè)置E列、F列、G列數(shù)字為“會(huì)計(jì)專用”格式，保留兩位小數(shù)，無(wú)貨幣符號(hào)，H列數(shù)字為“百分比”格式，保留兩位小數(shù)。設(shè)置對(duì)齊方式，調(diào)整行高列寬至合適的數(shù)值。結(jié)果如圖所示。4.固定資產(chǎn)構(gòu)成分析利用數(shù)據(jù)透視表和數(shù)據(jù)透視圖對(duì)固定資產(chǎn)構(gòu)成進(jìn)行分析。步驟1：新建“固定資產(chǎn)構(gòu)成分析”工作表，選定C5單元格。打開(kāi)【創(chuàng)建數(shù)據(jù)透視表】對(duì)話框中，單擊“選擇一個(gè)表或區(qū)域”單選按鈕。在【表/區(qū)域】文本框輸入建立數(shù)據(jù)透視表的數(shù)據(jù)源區(qū)域?yàn)椤肮潭ㄙY產(chǎn)清單！$A$3:$U$20”，或通過(guò)單擊右側(cè)的折疊按鈕直接選擇數(shù)據(jù)源區(qū)域?！具x擇放置數(shù)據(jù)透視表的位置】為“現(xiàn)有工作表”單選按鈕。步驟2：?jiǎn)螕簟敬_定】按鈕，在彈出的【數(shù)據(jù)透視表字段列表】對(duì)話框中，選擇“使用部門”字段添加到【報(bào)表篩選】中、選擇“類別名稱”字段添加到【行標(biāo)簽】中、選擇“折余價(jià)值”字段添加到【數(shù)值】中。如圖。步驟3：選定數(shù)據(jù)透視表中任一個(gè)單元格，打開(kāi)【插入計(jì)算字段】對(duì)話框。在【名稱：】復(fù)合框中輸入“比重”，在【公式】文本框中輸入“=原值-累計(jì)折舊額”。單擊【確定】按鈕，數(shù)據(jù)透視表中增加了一列：“求和項(xiàng)：比重”。步驟4：雙擊“求和項(xiàng)：比重”所在單元格E6，打開(kāi)【值字段設(shè)置】對(duì)話框。選擇【值顯示方式】，在【值顯示方式】列表框中選擇“占同列數(shù)據(jù)總和的百分比”。如圖4-4-11所示。單擊【確定】按鈕，如圖。步驟5：格式化數(shù)據(jù)透視表，使其美觀、易懂。合并及居中單元格區(qū)域C2：E2，設(shè)置文字的字體為“華文隸書(shū)”、字號(hào)為“26”，顏色為“藍(lán)色”，下劃線為“會(huì)計(jì)專用雙下劃線”。設(shè)置E6單元格公式為=NOW()，調(diào)整日期顯示格式。隱藏第四、第五行。設(shè)置D列數(shù)字為“會(huì)計(jì)專用”格式，保留兩位小數(shù)，無(wú)貨幣符號(hào)，E列數(shù)字為“百分比”格式，保留兩位小數(shù)。設(shè)置對(duì)齊方式，調(diào)整行高列寬至合適的數(shù)值。結(jié)果如圖

所示。步驟6：選定數(shù)據(jù)透視表任一單元格，執(zhí)行【插入】|【圖表】|【餅圖】|【分離型三維餅圖】命令，即可生成圖表。步驟7：格式化圖表，使其美觀、易懂。設(shè)置數(shù)據(jù)標(biāo)簽：選定圖表，執(zhí)行【布局】|【標(biāo)簽】|【數(shù)據(jù)標(biāo)簽】|【數(shù)據(jù)標(biāo)簽外】命令。設(shè)置數(shù)據(jù)標(biāo)簽格式：選定數(shù)據(jù)標(biāo)簽，單擊【格式】|【當(dāng)前所選內(nèi)容】|【設(shè)置所選內(nèi)容格式】”按鈕，打開(kāi)【設(shè)置數(shù)據(jù)標(biāo)簽格式】對(duì)話框，在【標(biāo)簽選項(xiàng)】|【標(biāo)簽包括】中選擇“類別名稱”、“百分比”復(fù)選框；在【數(shù)字】中“類別”選擇“百分比”，保留兩位小數(shù)。刪除圖例。將圖表標(biāo)題改為“固定資產(chǎn)構(gòu)成分析”。選定圖表區(qū)，執(zhí)行【格式】|【當(dāng)前所選內(nèi)容】|【設(shè)置所選內(nèi)容格式】命令，打開(kāi)【設(shè)置圖表格式】對(duì)話框，在【填充】中選擇“圖片或紋理填充”單選項(xiàng)，單擊【紋理】按鈕，選擇第三行第五列紋理。調(diào)整字符格式、繪圖區(qū)格式到合適狀態(tài)。結(jié)果如圖所示。步驟7：通過(guò)篩選不同使用部門，可以得到不同部門的構(gòu)成數(shù)據(jù)和相應(yīng)圖表。如圖所示。熟練掌握固定資產(chǎn)分析表和分析圖的設(shè)計(jì)制作。四EXCEL在固定資產(chǎn)管理中的應(yīng)用五固定資產(chǎn)系統(tǒng)的建立通過(guò)前面的任務(wù)，我們已經(jīng)建立了七個(gè)工作表。利用這些工作表，可以實(shí)現(xiàn)以下功能：建立了固定資產(chǎn)清單；以固定資產(chǎn)清單為基礎(chǔ)生成固定資產(chǎn)卡片；依托固定資產(chǎn)清單，形成折舊分配表，生成記賬憑證；利用固定資產(chǎn)清單，進(jìn)行固定資產(chǎn)新舊程度分析和構(gòu)成分析。現(xiàn)在，我們希望將各個(gè)工作表整合成一個(gè)完整的系統(tǒng)，密切相互之間的聯(lián)系。我們可以通過(guò)為“固定資產(chǎn)系統(tǒng)”工作簿設(shè)計(jì)一個(gè)用戶界面，使財(cái)務(wù)管理人員能夠一目了然了解到本工作簿的主要功能，并實(shí)現(xiàn)利用按鈕進(jìn)入各個(gè)工作表，利用宏改進(jìn)固定資產(chǎn)系統(tǒng)，高效完成各種管理、分析。本任務(wù)的基本流程包括：利用繪圖工具，建立用戶界面；通過(guò)超鏈接功能，實(shí)現(xiàn)進(jìn)入相應(yīng)工作表；當(dāng)完成管理和分析工作后，單擊“返回首頁(yè)”自動(dòng)返回到用戶界面；利用控件工具和宏代碼改進(jìn)完善本系統(tǒng)。1.固定資產(chǎn)系統(tǒng)用戶界面的設(shè)計(jì)1.1用戶界面的設(shè)計(jì)步驟1：打開(kāi)“固定資產(chǎn)系統(tǒng)”工作簿，新建“首頁(yè)”工作表，執(zhí)行【開(kāi)發(fā)工具】|【控件】|【插入】|【分組框】命令，鼠標(biāo)顯示為十字光標(biāo)，拖動(dòng)鼠標(biāo)，在“首頁(yè)”工作表中繪制出一個(gè)分組框。單擊新建分組框的文字，將其改為“資產(chǎn)信息”。。如圖步驟2：繪制啟動(dòng)各功能的按鈕。Excel的【開(kāi)發(fā)工具】|【控件】|【插入】下拉列表中提供有按鈕，但不能控制其樣式，本任務(wù)中使用Excel提供的自選圖形來(lái)制作漂亮的按鈕。選擇【插入】|【插圖】|【形狀】|【矩形】|【圓角矩形】，然后設(shè)置其填充效果，再設(shè)置其陰影效果，輸入文字“新增資產(chǎn)”。按同樣方式設(shè)置“資產(chǎn)卡片”按鈕。結(jié)果如圖步驟3：向工作表中再添加兩個(gè)“分組框”，復(fù)制剛繪制的按鈕，修改分組框的文字，給按鈕添加上相應(yīng)的文字，調(diào)整其擺放位置，如圖步驟4：選擇【插入】|【插圖】|【形狀】|【矩形】|【矩形】，向工作表中添加一個(gè)矩形自選圖形。選擇【格式】|【形狀樣式】|【形狀填充】|【白色】；選擇【格式】|【形狀樣式】|【形狀輪廓】|【主題顏色】|【黑色】；選擇【格式】|【形狀樣式】|【形狀輪廓】|【粗細(xì)】|【0.5磅】；選擇【格式】|【形狀樣式】|【形狀效果】|【陰影】|【外部】|【右下斜偏移】，，為其添加陰影；設(shè)置【疊放次序】|【置于底層】，然后輸入標(biāo)題文字“固定資產(chǎn)系統(tǒng)”，并設(shè)置為“華文隸書(shū)”“32”號(hào)，藍(lán)色。得到如圖所示的主界面。步驟5：?jiǎn)螕簟綩ffice按鈕】|【Excel選項(xiàng)】，打開(kāi)【Excel選項(xiàng)】，選【高級(jí)】,取消“顯示水平滾動(dòng)條”、“顯示垂直滾動(dòng)條”、“顯示行和列標(biāo)題”、“顯示網(wǎng)格線”各項(xiàng)前面的選中符號(hào)，如圖步驟6：“首頁(yè)”工作表最終顯示效果，如圖1.2超鏈接的創(chuàng)建步驟1：打開(kāi)“首頁(yè)”工作表，選中“資產(chǎn)卡片”按鈕，執(zhí)行【插入】|【鏈接】|【超鏈接】命令，打開(kāi)【編輯超鏈接】對(duì)話框，在【鏈接到：】中選擇“本文檔中的位置”，【請(qǐng)鍵入單元格引用】中輸入A1，【或在這篇文檔中選擇位置】中選擇“固定資產(chǎn)卡片”，單擊【確定】按鈕。如圖所示步驟2：將前面設(shè)置好的圓角矩形按鈕復(fù)制到“固定資產(chǎn)卡片”工作表中，放在如圖所示位置，修改按鈕上的文字為“<<返回首頁(yè)”，并為其創(chuàng)建超鏈接，位置為“首頁(yè)”工作表。如圖步驟3：打開(kāi)“首頁(yè)”工作表，為其他按鈕創(chuàng)建超鏈接，“或在這篇文檔中選擇位置”列表框中選擇位置分別為：“新增資產(chǎn)”-“固定資產(chǎn)清單”“折舊分配”-“折舊費(fèi)用分配”“憑證生成”-“記賬憑證”“新舊程度”-“固定資產(chǎn)新舊程度分析”“構(gòu)成分析”-“固定資產(chǎn)構(gòu)成分析”創(chuàng)建過(guò)程與步驟1相同步驟4：為其他工作表創(chuàng)建“<<返回首頁(yè)”按鈕，創(chuàng)建過(guò)程與步驟2相同。步驟5：為了防止用戶操作時(shí)拖動(dòng)“首頁(yè)”工作表中的按鈕，打開(kāi)“首頁(yè)”工作表，執(zhí)行【審閱】|【更改】|【保護(hù)工作表】命令，可以將“首頁(yè)”工作表保護(hù)起來(lái)。如圖2.利用宏改進(jìn)系統(tǒng)2.1基礎(chǔ)數(shù)據(jù)的輸入任務(wù)二中資產(chǎn)名稱、資產(chǎn)編號(hào)、起始日期、使用年限、資產(chǎn)狀態(tài)、增加方式、資產(chǎn)性質(zhì)、原值、殘值率等列數(shù)據(jù)是在“固定資產(chǎn)清單”工作表中通過(guò)鍵盤直接輸入或通過(guò)數(shù)據(jù)有效性選擇錄入。這種方法將使所有數(shù)據(jù)處于可修改范圍，用戶的誤操作容易破壞原來(lái)數(shù)據(jù)。我們還可以利用窗體錄入以上數(shù)據(jù)，然后再填充到工作表中，這種方法的優(yōu)點(diǎn)是將用戶和工作表進(jìn)行隔離，保證數(shù)據(jù)的安全。通過(guò)窗體錄入數(shù)據(jù)，首先要做的工作是設(shè)計(jì)窗體，并在窗體中放置相應(yīng)的控件。步驟1：執(zhí)行【開(kāi)發(fā)工具】|【代碼】|【VisualBasic】命令（或按快捷鍵Alt+F11）進(jìn)入VisualBasic編輯器。執(zhí)行【插入】|【用戶窗體】命令，插入一個(gè)用戶窗體。如圖步驟2：?jiǎn)螕羧鐖D所示的屬性窗口，將窗體的“Caption”屬性設(shè)為“新增資產(chǎn)”。如圖步驟3：?jiǎn)螕艨丶ぞ呦渲械臉?biāo)簽按鈕“A”，在窗體中繪制一個(gè)標(biāo)簽，設(shè)置其“AutoSize”屬性為“True”，使標(biāo)簽控件的大小與輸入的文字自動(dòng)適應(yīng)；再設(shè)置其“Caption”屬性為“資產(chǎn)名稱”；單擊控件工具箱中的文本框按鈕，緊接著“資產(chǎn)名稱”標(biāo)簽放置一個(gè)文本框控件。如圖步驟4：用類似的方法創(chuàng)建其他控件，然后再進(jìn)行對(duì)齊操作，最后得到如圖所示的用戶窗體。如圖。步驟5：如表4-5-1所示，列出了在編寫(xiě)代碼時(shí)要用到的控件名稱（標(biāo)簽控件另命名）表

控件名稱設(shè)置控件名稱控件名稱資產(chǎn)名稱txtname資產(chǎn)編號(hào)txtid啟用日期txtdate使用年限txtyear資產(chǎn)原值txtzcyz殘值率txtczl資產(chǎn)狀態(tài)cbxzczt增加方式cbxzjfs資產(chǎn)性質(zhì)cbxzcxz折舊方法cbxzjff增加cmdSave放棄cmdCancel2.2設(shè)計(jì)新增資產(chǎn)代碼步驟1：首先編寫(xiě)窗體的初始化代碼，在窗體初始化時(shí)，可添加復(fù)合框控件中的下拉列表項(xiàng)目。雙擊窗體打開(kāi)代碼窗口，單擊事件列表框右側(cè)的下拉箭頭，選擇“Initialize”事件。如圖步驟2：在窗體的初始化事件中，主要是為各組合框添加列表項(xiàng)目。其代碼如下：PrivateSubUserForm_Initialize()cbxzczt.AddItem"未使用"cbxzczt.AddItem"在用"cbxzczt.AddItem"已提足折舊"cbxzczt.AddItem"正常使用"cbxzczt.AddItem"報(bào)廢"cbxzjfs.AddItem"購(gòu)入"cbxzjfs.AddItem"自建"cbxzjfs.AddItem"投入"cbxzjfs.AddItem"盤盈"cbxzjfs.AddItem"捐贈(zèng)"cbxzjfs.AddItem"內(nèi)部調(diào)撥"cbxzjfs.AddItem"其他"cbxzcxz.AddItem"正常"cbxzcxz.AddItem"當(dāng)月新增"cbxzcxz.AddItem"當(dāng)月減少"cbxzcxz.AddItem"當(dāng)月新增并減少"cbxzcxz.AddItem"其他"cbxzjff.AddItem"平均年限法"cbxzjff.AddItem"雙倍余額遞減法"cbxzjff.AddItem"固定余額遞減法"cbxzjff.AddItem"年數(shù)總和法"EndSub步驟3：因?yàn)槿掌陬惖闹涤刑厥飧袷揭?，為了保證輸入的正確性，需要對(duì)輸入進(jìn)行驗(yàn)證。雙擊“啟用日期”文本框txtdate，為其BeforeUpdate事件編寫(xiě)代碼如下：PrivateSubtxtdate_BeforeUpdate(ByValCancelAsMSForms.ReturnBoolean)IfNotIsDate(txtdate.Value)ThenMsgBox"請(qǐng)輸入正確的啟用日期！",,"提示"txtdate.SelStart=0txtdate.SelLength=Len(txtdate.Value)Cancel=TrueEndIfEndSub步驟4：?jiǎn)螕簟胺艞墶卑粹ocmdCancel時(shí)，將退出窗體，事件代碼如下：PrivateSubcmdCancel_Click()Me.HideSheets("首頁(yè)").ActivateEndSub步驟5：?jiǎn)螕簟霸黾印卑粹ocmdSave時(shí)，首先判斷“資產(chǎn)名稱”文本框中是否輸入有內(nèi)容，然后再調(diào)用add子過(guò)程將內(nèi)容添加到“固定資產(chǎn)清單”中。具體代碼如下：PrivateSubcmdSave_Click()Iftxtname.Value=""ThenMsgBox"請(qǐng)輸入資產(chǎn)名稱！",,"提示"txtname.SetFocusEndIfaddEndSub步驟6：add子過(guò)程是執(zhí)行向“固定資產(chǎn)清單”工作表中保存窗體中輸入的內(nèi)容。其代碼如下：Subadd()DimintrowAsIntegerSheets("固定資產(chǎn)清單").Activateintrow=Sheets("固定資產(chǎn)清單").Range("a65536").End(xlUp).Row+1Cells(introw,1)=txtname.ValueCells(introw,2).NumberFormatLocal="@"Cells(introw,2)=txtid.ValueCells(introw,7)=txtdate.ValueCells(introw,8)=txtyear.ValueCells(introw,10)=cbxzczt.ValueCells(introw,11)=cbxzjfs.ValueCells(introw,12)=cbxzcxz.ValueCells(introw,13)=cbxzjff.ValueCells(introw,14)=txtzcyz.ValueCells(introw,15)