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

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

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

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

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

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

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

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

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

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

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

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

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

控件名稱設置控件名稱控件名稱資產(chǎn)名稱txtname資產(chǎn)編號txtid啟用日期txtdate使用年限txtyear資產(chǎn)原值txtzcyz殘值率txtczl資產(chǎn)狀態(tài)cbxzczt增加方式cbxzjfs資產(chǎn)性質(zhì)cbxzcxz折舊方法cbxzjff增加cmdSave放棄cmdCancel2.2設計新增資產(chǎn)代碼步驟1：首先編寫窗體的初始化代碼，在窗體初始化時，可添加復合框控件中的下拉列表項目。雙擊窗體打開代碼窗口，單擊事件列表框右側(cè)的下拉箭頭，選擇“Initialize”事件。如圖步驟2：在窗體的初始化事件中，主要是為各組合框添加列表項目。其代碼如下：PrivateSubUserForm_Initialize()cbxzczt.AddItem"未使用"cbxzczt.AddItem"在用"cbxzczt.AddItem"已提足折舊"cbxzczt.AddItem"正常使用"cbxzczt.AddItem"報廢"cbxzjfs.AddItem"購入"cbxzjfs.AddItem"自建"cbxzjfs.AddItem"投入"cbxzjfs.AddItem"盤盈"cbxzjfs.AddItem"捐贈"cbxzjfs.AddItem"內(nèi)部調(diào)撥"cbxzjfs.AddItem"其他"cbxzcxz.AddItem"正常"cbxzcxz.AddItem"當月新增"cbxzcxz.AddItem"當月減少"cbxzcxz.AddItem"當月新增并減少"cbxzcxz.AddItem"其他"cbxzjff.AddItem"平均年限法"cbxzjff.AddItem"雙倍余額遞減法"cbxzjff.AddItem"固定余額遞減法"cbxzjff.AddItem"年數(shù)總和法"EndSub步驟3：因為日期類的值有特殊格式要求，為了保證輸入的正確性，需要對輸入進行驗證。雙擊“啟用日期”文本框txtdate，為其BeforeUpdate事件編寫代碼如下：PrivateSubtxtdate_BeforeUpdate(ByValCancelAsMSForms.ReturnBoolean)IfNotIsDate(txtdate.Value)ThenMsgBox"請輸入正確的啟用日期！",,"提示"txtdate.SelStart=0txtdate.SelLength=Len(txtdate.Value)Cancel=TrueEndIfEndSub步驟4：單擊“放棄”按鈕cmdCancel時，將退出窗體，事件代碼如下：PrivateSubcmdCancel_Click()Me.HideSheets("首頁").ActivateEndSub步驟5：單擊“增加”按鈕cmdSave時，首先判斷“資產(chǎn)名稱”文本框中是否輸入有內(nèi)容，然后再調(diào)用add子過程將內(nèi)容添加到“固定資產(chǎn)清單”中。具體代碼如下：PrivateSubcmdSave_Click()Iftxtname.Value=""ThenMsgBox"請輸入資產(chǎn)名稱！",,"提示"txtname.SetFocusEndIfaddEndSub步驟6：add子過程是執(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)