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

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

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

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

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

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

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

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

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

企業要求對固定資產的情況進行分析。

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

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

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

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

控件名稱設置控件名稱控件名稱資產名稱txtname資產編號txtid啟用日期txtdate使用年限txtyear資產原值txtzcyz殘值率txtczl資產狀態cbxzczt增加方式cbxzjfs資產性質cbxzcxz折舊方法cbxzjff增加cmdSave放棄cmdCancel2.2設計新增資產代碼步驟1：首先編寫窗體的初始化代碼，在窗體初始化時，可添加復合框控件中的下拉列表項目。雙擊窗體打開代碼窗口，單擊事件列表框右側的下拉箭頭，選擇“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"內部調撥"cbxzjfs.AddItem"其他"cbxzcxz.AddItem"正常"cbxzcxz.AddItem"當月新增"cbxzcxz.AddItem"當月減少"cbxzcxz.AddItem"當月新增并減少"cbxzcxz.AddItem"其他"cbxzjff.AddItem"平均年限法"cbxzjff.AddItem"雙倍余額遞減法"cbxzjff.AddItem"固定余額遞減法"cbxzjff.AddItem"年數總和法"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時，首先判斷“資產名稱”文本框中是否輸入有內容，然后再調用add子過程將內容添加到“固定資產清單”中。具體代碼如下：PrivateSubcmdSave_Click()Iftxtname.Value=""ThenMsgBox"請輸入資產名稱！",,"提示"txtname.SetFocusEndIfaddEndSub步驟6：add子過程是執行向“固定資產清單”工作表中保存窗體中輸入的內容。其代碼如下：Subadd()DimintrowAsIntegerSheets("固定資產清單").Activateintrow=Sheets("固定資產清單").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)