Excel在固定資產(chǎn)管理中的應(yīng)用知識(shí)目標(biāo)：1.掌握固定資產(chǎn)管理系統(tǒng)的業(yè)務(wù)處理流程。2.掌握固定資產(chǎn)折舊的計(jì)算方法。能力目標(biāo)：1.學(xué)會(huì)使用Excel設(shè)計(jì)固定資產(chǎn)核算系統(tǒng)。2.學(xué)會(huì)運(yùn)用篩選及數(shù)據(jù)分析工具進(jìn)行固定資產(chǎn)數(shù)據(jù)的匯總和查詢。知識(shí)目標(biāo)和能力目標(biāo)

對(duì)于固定資產(chǎn)的管理大致是這樣的流程：

①資產(chǎn)購(gòu)入后，先由各相關(guān)部門驗(yàn)收，出具意見書；

②憑此單填寫固定資產(chǎn)入庫(kù)單，送交財(cái)務(wù)部一份；

③財(cái)務(wù)部對(duì)購(gòu)入固定資產(chǎn)進(jìn)行編號(hào)，填寫固定資產(chǎn)卡片，貼上資產(chǎn)封條；

④使用人辦理資產(chǎn)使用手續(xù)。

公司日常固定資產(chǎn)管理業(yè)務(wù)有：固定資產(chǎn)增加、減少、部門間調(diào)撥，每月計(jì)提等。學(xué)習(xí)情境

使用Excel設(shè)計(jì)固定資產(chǎn)核算系統(tǒng)主要包括兩大塊內(nèi)容：

一是制作固定資產(chǎn)卡片或臺(tái)賬，作用是登記購(gòu)入固定資產(chǎn)的名稱、原值、折舊年限等，同時(shí)還要記錄因報(bào)廢或出售后固定資產(chǎn)的減少，以及部門間固定資產(chǎn)的調(diào)撥情況；

二是需要記錄每個(gè)會(huì)計(jì)期間各項(xiàng)資產(chǎn)應(yīng)計(jì)提的折舊額、累計(jì)折舊額和賬面凈值?；具^程可分為：固定資產(chǎn)臺(tái)賬的編制→固定資產(chǎn)的折舊計(jì)提→固定資產(chǎn)卡片的編制→固定資產(chǎn)的調(diào)整管理→固定資產(chǎn)分析。學(xué)習(xí)情境任務(wù)一固定資產(chǎn)臺(tái)賬的編制1.建立基礎(chǔ)信息表萬(wàn)隆燈具有限公司固定資產(chǎn)分為4類，編號(hào)及相關(guān)折舊年限、凈殘值率分別為：1.建立基礎(chǔ)信息表公司包括行政部、生產(chǎn)車間、財(cái)務(wù)部、銷售部、研發(fā)部、采購(gòu)部六個(gè)部門，固定資產(chǎn)的所屬部門使用固定資產(chǎn)并負(fù)責(zé)其日常維護(hù)。1.建立基礎(chǔ)信息表固定資產(chǎn)的集中管理在財(cái)務(wù)部，每個(gè)固定資產(chǎn)都有一張自己的卡片，記錄著其增加方式、減少方式、購(gòu)置日期、規(guī)格型號(hào)、所屬部門、原值、累計(jì)折舊、凈值、折舊方法等信息。1.建立基礎(chǔ)信息表2.建立固定資產(chǎn)臺(tái)賬表(1)設(shè)置“固定資產(chǎn)臺(tái)賬表”初始數(shù)據(jù)庫(kù)關(guān)于TODAY()函數(shù)的幾點(diǎn)說明TODAY()函數(shù)不需設(shè)置參數(shù)，即可以根據(jù)計(jì)算機(jī)系統(tǒng)設(shè)定的日期返回當(dāng)前的日期；在輸入日期函數(shù)相關(guān)公式時(shí)，部分同學(xué)可能會(huì)得到45272，其實(shí)這是單元格格式的問題。名稱引用位置范圍資產(chǎn)類別=基礎(chǔ)信息表!$B$13:$B$16工作簿使用部門=基礎(chǔ)信息表!$B$3:$B$8工作簿使用狀況=基礎(chǔ)信息表!$E$3:$E$10工作簿增加方式=基礎(chǔ)信息表!$G$3:$G$8工作簿減少方式=基礎(chǔ)信息表!$H$3:$H$7工作簿折舊方法=基礎(chǔ)信息表!$G$13:$G$15工作簿折舊費(fèi)用類別=基礎(chǔ)信息表!$H$13:$H$16工作簿基礎(chǔ)信息=基礎(chǔ)信息表!$B$13:$E$16工作簿(2)定義“固定資產(chǎn)臺(tái)賬表”名稱2.建立固定資產(chǎn)臺(tái)賬表(3)限制輸入類別2.建立固定資產(chǎn)臺(tái)賬表(4)輸入相關(guān)固定資產(chǎn)初始數(shù)據(jù)信息2.建立固定資產(chǎn)臺(tái)賬表（5）設(shè)置資產(chǎn)編號(hào)取數(shù)公式

選擇A4單元格，輸入取數(shù)公式“=VLOOKUP($E4,基礎(chǔ)信息,2,0)&VLOOKUP($F4,基礎(chǔ)信息表!$B$3:$C$8,2,0)&TEXT(SUMPRODUCT(($F$4:F4=F4)*1),"00")”TEXT函數(shù)可保證結(jié)構(gòu)美觀含義：從字面上可以看出，SUMPRODUCT是由兩個(gè)英文單詞組成的，SUM是求和，PRODUCT是乘積，合起來就是乘積之和的意思。

語(yǔ)法格式：SUMPRODUCT（[array1],

[array2],

[array3],

…）。其中：[array]是數(shù)組。知識(shí)技能一：SUMPRODUCT函數(shù)SUMPRODUCT函數(shù)語(yǔ)法格式中([array1],[array2],[array3],…)逗號(hào)分隔的各個(gè)參數(shù)必須為數(shù)字型數(shù)據(jù)1.基礎(chǔ)用法：將數(shù)組間對(duì)應(yīng)的元素相乘，并返回乘積之和。具體求解過程為：1*3+2*4+2*6+4*5+3*9+6*6=106。2.擴(kuò)展應(yīng)用：多條件計(jì)數(shù)相較于SUMPRODUCT函數(shù)基礎(chǔ)用法，多條件計(jì)數(shù)中最關(guān)鍵的是確定計(jì)數(shù)的判斷條件。

因?yàn)槭桥卸l件，所以無法像基礎(chǔ)用法那樣在“插入函數(shù)”對(duì)話框中輸入，只能手工輸入，具體計(jì)算過程可執(zhí)行“公式”→“公式審核”→“公式求值”→“求值”，逐步計(jì)算相關(guān)結(jié)果。知識(shí)技能一：SUMPRODUCT函數(shù)該月部門經(jīng)理有幾人加班？=SUMPRODUCT((D2:D16="部門經(jīng)理")*1，(H2:H16<>0)*1)數(shù)據(jù)源自項(xiàng)目四“職工工資匯總表”對(duì)于SUMPRODUCT使用過程中“*1”的說明如果判斷的結(jié)果是包含TRUE或FALSE邏輯值的數(shù)組（非數(shù)值型的數(shù)據(jù)），結(jié)果將按0來處理。因此，需要將每項(xiàng)邏輯判斷值乘1，轉(zhuǎn)換為數(shù)字實(shí)現(xiàn)計(jì)數(shù)。對(duì)于SUMPRODUCT使用過程中“*1”的說明用函數(shù)實(shí)現(xiàn)數(shù)值類型的轉(zhuǎn)換。

在Excel中，有一個(gè)N函數(shù)，可以直接將不是數(shù)值形式的值轉(zhuǎn)換成數(shù)值形式，能將TRUE轉(zhuǎn)換成1，將日期轉(zhuǎn)換成序列值。利用SUMPRODUCT和N函數(shù)實(shí)現(xiàn)多條件計(jì)數(shù)對(duì)于SUMPRODUCT使用過程中“*1”的說明用函數(shù)實(shí)現(xiàn)數(shù)值類型的轉(zhuǎn)換。對(duì)于SUMPRODUCT使用過程中“*1”的說明參數(shù)前面添加兩個(gè)減號(hào)，實(shí)現(xiàn)乘兩次負(fù)1，將邏輯值轉(zhuǎn)換成數(shù)值。對(duì)于SUMPRODUCT使用過程中“*1”的說明如果不用逗號(hào)分割參數(shù)，也可以考慮直接用“*”號(hào)連接兩個(gè)以上條件作為一個(gè)參數(shù)，相當(dāng)于實(shí)現(xiàn)乘法運(yùn)算。該月加班的部門經(jīng)理們應(yīng)發(fā)工資總和數(shù)？=SUMPRODUCT((D2:D16="部門經(jīng)理")*(H2:H16<>0)*(J2:J16))增加了一個(gè)求和的區(qū)域多條件求和2.擴(kuò)展應(yīng)用：知識(shí)技能一：SUMPRODUCT函數(shù)多條件求和2.擴(kuò)展應(yīng)用：知識(shí)技能一：SUMPRODUCT函數(shù)SUMPRODUCT函數(shù)無法實(shí)現(xiàn)包含通配符的計(jì)數(shù)！=SUMPRODUCT((D2:D16=“*經(jīng)理")*(H2:H16<>0))

該月經(jīng)理級(jí)別有幾人加班？=COUNTIFS(D2:D16,"*經(jīng)理",H2:H16,"<>0")2.建立固定資產(chǎn)臺(tái)賬表任務(wù)二固定資產(chǎn)的折舊計(jì)提1.設(shè)置折舊年限公式K列“=VLOOKUP($E4,基礎(chǔ)信息,3,0)”2.計(jì)算固定資產(chǎn)殘值N列“=VLOOKUP($E4,基礎(chǔ)信息,4,0)*M4”L列“=EDATE(J4,K4*12)”3.計(jì)算固定資產(chǎn)計(jì)提折舊的到期日期知識(shí)技能二：EDATE函數(shù)含義：用于返回指定日期之前或之后幾個(gè)月的具體日期。

語(yǔ)法格式：EDATE（start_date,months）。其中：

start_date：表示起始日的日期；months：正數(shù)表示start_date之后的月份數(shù)，負(fù)數(shù)則表示之前的月份數(shù)。所以，“=EDATE("2023-12-12",1)”，返回結(jié)果應(yīng)為“2024-1-12”；如果“=EDATE("2023-12-12",-1)”，返回結(jié)果應(yīng)為“2023-11-12”。知識(shí)技能三：EOMONTH函數(shù)含義：EOMONTH函數(shù)可以計(jì)算某一特定月份中最后一天的日期。

語(yǔ)法格式：EOMONTH（start_date,months）。參數(shù)說明同EDATE函數(shù)。所以，“=EDATE("2023-12-12",1)”，返回“2024-1-31”；“=EDATE("2023-12-12",0)”，返回“2023-12-31”；如果“=EDATE("2023-12-12",-1)”，返回應(yīng)為“2023-11-30”。4.計(jì)算已計(jì)提折舊的月份數(shù)

選擇單元格P4，輸入公式“=IF(AND(MONTH($B$2)=MONTH($J4),YEAR($B$2)=YEAR($J4)),0,IF($B$2<L4,DATEDIF(EOMONTH(J4,0)+1,EOMONTH($B$2,0),”M“),K4*12))”，即可返回截止上個(gè)月已計(jì)提折舊的月份。

其中，AND(MONTH($B$2)=MONTH($J4),YEAR($B$2)=YEAR($J4)用于判定該固定資產(chǎn)是否該月購(gòu)進(jìn)，因?yàn)樵撛滦略龅墓潭ㄙY產(chǎn)不計(jì)提折舊，所以該IF函數(shù)若為真，則返回0值；第二個(gè)IF函數(shù)用于判斷折舊是否已經(jīng)超過年限，如果超過了折舊年限，返回年限值轉(zhuǎn)化成的月份數(shù)。知識(shí)技能四：?jiǎn)螀?shù)的日期函數(shù)及擴(kuò)展（1）YEAR函數(shù)：返回某個(gè)日期的年份

語(yǔ)法格式：YEAR(serial_number)（2）MONTH函數(shù)：返回某個(gè)日期的月份

語(yǔ)法格式：MONTH(serial_number)（3）DAY函數(shù)：返回某個(gè)日期的天數(shù)

語(yǔ)法格式：DAY(serial_number)（4）WEEKDAY函數(shù)：返回某個(gè)日期是星期幾

語(yǔ)法格式：WEEKDAY(serial_number,[return_type])關(guān)于“已計(jì)提月份”的幾點(diǎn)說明（1）從固定資產(chǎn)計(jì)提折舊的規(guī)定可知，當(dāng)月增加的固定資產(chǎn)當(dāng)月不提折舊，所以用EOMONTH函數(shù)計(jì)算出的本月最后一天加一天后，就變成了次月的第一天（EOMONTH(J4,0)+1）。再用DATEDIF計(jì)算起始月份間的差額數(shù)。（2）當(dāng)然，也可以不用隱藏函數(shù)DATEDIF，用單純的加減法計(jì)算已計(jì)提折舊的月份數(shù)。5.1計(jì)算已計(jì)提折舊金額

選擇單元格Q4，輸入公式“=IF(O4="平均年限法",SLN(M4,N4,K4)/12*P4,(IF(O4="年數(shù)總和法",SYD(M4,N4,K4,K4)*(2*K4-INT(P4/12)+1)*INT(P4/12)/2+SYD(M4,N4,K4,INT(P4/12)+1)/12*MOD(P4,12),IF(O4="雙倍余額遞減法",IF(P4<=(K4-2)*12,VDB(M4,N4,K4,0,INT(P4/12))+DDB(M4,N4,K4,INT(P4/12)+1)/12*MOD(P4,12),VDB(M4,N4,K4,0,K4-2,2,TRUE)+(M4-VDB(M4,N4,K4,0,K4-2,2,TRUE)-N4)/2/12*(P4-(K4-2)*12)),"請(qǐng)選擇折舊方法")))))”，并將其復(fù)制到Q列以下的單元格中。5.1計(jì)算已計(jì)提折舊金額知識(shí)技能五：資產(chǎn)折舊類函數(shù)用于返回一個(gè)期間內(nèi)的資產(chǎn)的直線折舊，常用于平均年限法。語(yǔ)法格式為：SLN(cost,salvage,life)Cost：原值；Salvage：殘值；Life：預(yù)期可使用年限。（1）SLN函數(shù)“累計(jì)折舊”D6=SUM(C6,D5)，并下拉公式至D9單元格?！百~面凈值”E6=$C$3-D6，并下拉至E9單元格。（1）SLN函數(shù)

用于返回在指定期間內(nèi)資產(chǎn)按年限總和法計(jì)算的折舊，即將固定資產(chǎn)原值減去殘值后的凈額，乘以逐年遞減的分?jǐn)?shù)，計(jì)算出折舊額。

計(jì)算公式為：（2）SYD函數(shù)：（2）SYD函數(shù)語(yǔ)法格式為：SLN(cost,salvage,life,

period)Life：預(yù)期可使用年限。Period：需要計(jì)提折舊值的期間，必須使用與life相同的單位。年數(shù)總和法計(jì)算的折舊年度是從開始計(jì)提折舊月的連續(xù)12個(gè)月，而會(huì)計(jì)年度一般是指1月1日至當(dāng)年12月31日；所以需要調(diào)整月份數(shù)。（2）SYD函數(shù)（3）DDB函數(shù)含義：用雙倍余額遞減法返回指定期間內(nèi)某項(xiàng)固定資產(chǎn)的折舊值。語(yǔ)法格式：DDB(cost,salvage,life,

period,[factor])。其中，參數(shù)factor是余額遞減速率（折舊因子）。外加中括號(hào)是因?yàn)閰?shù)factor可以被省略，若省略factor默認(rèn)為2。計(jì)算公式為：“Min((原值–前期累計(jì)折舊)*(factor/預(yù)期可使用年限),(原值-殘值-前期累計(jì)折舊))”（3）DDB函數(shù)按照我國(guó)會(huì)計(jì)準(zhǔn)則對(duì)雙倍余額遞減法的定義可知，在固定資產(chǎn)使用年限的最后兩年，應(yīng)改用年限平均法。（4）VDB函數(shù)含義：用雙倍余額遞減法或其他指定方法，返回給定期間內(nèi)的折舊值；通常VDB用于代表可變余額遞減法。語(yǔ)法格式：DDB(cost,salvage,life,

start_period,end_period,

[factor],[no_switch])。start_period用于計(jì)算折舊的起始時(shí)期；end_period用于計(jì)算折舊的終止時(shí)期，兩者都必須使用與life相同的單位。（4）VDB函數(shù)“start_period”的值錄入為G30單元格，“end_period”的值錄入為G31單元格；向下拖動(dòng)該公式至H39單元格?？蛇x參數(shù)no_switch，用于指定折舊值大于余額遞減計(jì)算值時(shí)，是否轉(zhuǎn)用直線折舊法。若no_switch為TRUE，即使折舊值大于余額遞減計(jì)算值，也不轉(zhuǎn)用直線折舊法；若為FALSE或被忽略，則將轉(zhuǎn)用直線折舊法。（4）VDB函數(shù)默認(rèn)用直線法計(jì)算剩余年限的折舊額大于加速折舊法計(jì)算的折舊額時(shí)，VDB函數(shù)將其轉(zhuǎn)換成直線法進(jìn)行運(yùn)算。最后4年的折舊金額相等（4）VDB函數(shù)

如果想要用VDB函數(shù)實(shí)現(xiàn)不轉(zhuǎn)直線法的效果，只需修改公式中的相關(guān)參數(shù)，將No_switch參數(shù)設(shè)置成1即可。

選擇單元格R4，輸入公式“=IF(OR(P4=K4*12,AND(MONTH($B$2)=MONTH($J4),YEAR($B$2)=YEAR($J4))),0,IF(O4="平均年限法",SLN(M4,N4,K4)/12,(IF(O4="年數(shù)總和法",SYD(M4,N4,K4,INT(P4/12)+1)/12,IF(O4="雙倍余額遞減法",(IF(P4<(K4-2)*12,DDB(M4,N4,K4,INT(P4/12)+1)/12,(M4-VDB(M4,N4,K4,0,K4-2,2,TRUE)-N4)/2/12)),"請(qǐng)選擇折舊方法")))))”5.2計(jì)算本月應(yīng)提折舊金額5.2計(jì)算本月應(yīng)提折舊金額5.3生成固定資產(chǎn)計(jì)提折舊的記賬憑證

選擇管理費(fèi)用，單元格G6，輸入“=SUMIF(固定資產(chǎn)臺(tái)賬表!$U:$U,C6,固定資產(chǎn)臺(tái)賬表!$R:$R)”，并將此公式復(fù)制到銷售費(fèi)用和制造費(fèi)用的借方金額中；選擇單元格G12，完成借方金額的匯總計(jì)算，輸入“=IFERROR(SUM(G6:H8),0)”。

選擇單元格I9，計(jì)算本月累計(jì)折舊總額，輸入“=SUM(固定資產(chǎn)臺(tái)賬表!R:R)”，也就是返回固定資產(chǎn)臺(tái)賬表中本月應(yīng)提折舊總金額。。5.3生成固定資產(chǎn)計(jì)提折舊的記賬憑證

選擇單元格I12，輸入“=IFERROR(I9,0)”，并檢查該值是否與G12相等。

選擇單元格B12，輸入“=”合計(jì)：“&SUBSTITUTE(SUBSTITUTE(TEXT(INT(I12),”[dbnum2]“)&”元“&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(TEXT(I12,”.00“),2),”[dbnum2]0角0分“),”零角“,”零“),”零分“,”整“),”零整“,”整“),”零元零“,),”零元“,)”。5.3生成固定資產(chǎn)計(jì)提折舊的記賬憑證知識(shí)技能六：替換函數(shù)含義：將文本字符串中的舊文本替換成新文本。語(yǔ)法格式：SUBSTITUTE(text,old_text,new_text,[instance_num])。其中，text為需要替換其中字符的文本或?qū)形谋镜膯卧竦囊?；[instance_num]為一數(shù)值，用來指定以new_text替換第幾次出現(xiàn)的old_text，如果缺省則將用new_text替換text中出現(xiàn)的所有old_text。1.SUBSTITUTE函數(shù)知識(shí)技能六：替換函數(shù)含義：可以將一個(gè)字符串中的部分字符串替換成另一個(gè)字符串。語(yǔ)法格式：REPLACE(old_text,start_num,num_chars,new_text)。其中，old_text是需要替換字符的文本，start_num是從第幾位開始替換，num_chars用來指定替換的字符個(gè)數(shù)，new_text則是替換的新字符串。2.REPLACE函數(shù)知識(shí)技能六：替換函數(shù)2.REPLACE函數(shù)[dbnum2]是格式函數(shù)，表示將數(shù)字轉(zhuǎn)換成中文大寫的格式。[dbnum1]也是格式函數(shù)，表示將數(shù)字轉(zhuǎn)換成中文小寫的格式；[dbnum3]表示轉(zhuǎn)換成全角數(shù)字。

“

[dbnum2]G/通用格式元;;;

“中的三個(gè)分號(hào)，其實(shí)是“正數(shù)；負(fù)數(shù)；零；”的格式說明，如果均空白，則表示不加任何修改。知識(shí)技能七：[dbnum2]格式函數(shù)5.3生成固定資產(chǎn)計(jì)提折舊的記賬憑證5.4計(jì)算累計(jì)折舊和賬面凈值

選擇“累計(jì)折舊”（單元格S4），輸入公式“=Q4+R4”；選擇“賬面凈值”（單元格T4），輸入公式“=IF(AND(R4=0,S4<>0),"殘值"&(M4-S4),M4-S4)”，即如果本月應(yīng)提折舊為零，則標(biāo)明已提足折舊，賬面凈值為殘值。

對(duì)于賬面凈值顯示有“殘值”的固定資產(chǎn)，將其使用狀況調(diào)整為“已提足折舊”。保護(hù)此工作表!5.4計(jì)算累計(jì)折舊和賬面凈值1.設(shè)置固定資產(chǎn)卡片樣式任務(wù)三固定資產(chǎn)卡片的編制=INDEX(固定資產(chǎn)臺(tái)賬表!$B:$B,MATCH($B$3,固定資產(chǎn)臺(tái)賬表!$A:$A,0))2.輸入固定資產(chǎn)卡片取數(shù)公式INDEX函數(shù)可以返回表格或區(qū)域中的值或值的引用。該公式用于在“固定資產(chǎn)臺(tái)賬表”的B列中查找MATCH函數(shù)返回的值。知識(shí)技能八：INDEX函數(shù)

①數(shù)組形式語(yǔ)法格式：INDEX(array,row_num,[column_num])

array：?jiǎn)卧駞^(qū)域或數(shù)組常量。

row_num：選擇數(shù)組中的某行，函數(shù)從該行返回?cái)?shù)值；column_num：選擇數(shù)組中的某列，函數(shù)從該列返回?cái)?shù)值。

如果省略row_num，則必須有column_num；如果同時(shí)使用row_num和column_num，函數(shù)返回兩者交叉處單元格的值。

②引用形式語(yǔ)法格式：INDEX(reference,row_num,[column_num],[area_num])。

reference：對(duì)一個(gè)或多個(gè)單元格區(qū)域的引用。row_num：引用中某行的行號(hào)，column_num：引用中某列的列標(biāo)，函數(shù)從該列返回一個(gè)引用。

[area_num]：選擇引用中的一個(gè)區(qū)域，從中返回row_num和column_num的交叉區(qū)域。知識(shí)技能八：INDEX函數(shù)=VLOOKUP($B3,固定資產(chǎn)臺(tái)賬表!$A:$U,2,0)不同函數(shù)實(shí)現(xiàn)單元格D3取數(shù)=INDEX(固定資產(chǎn)臺(tái)表!$B:$B,MATCH($B$3,固定資產(chǎn)臺(tái)賬表!$A:$A,0))=OFFSET(固定資產(chǎn)臺(tái)賬表!$A$3,MATCH($B$3,固定資產(chǎn)臺(tái)賬表!$A:$A,0)-3,MATCH(C3,固定資產(chǎn)臺(tái)賬表!$3:$3,0)-1)①②③知識(shí)技能八：INDEX函數(shù)任務(wù)三固定資產(chǎn)卡片的編制3.輸入固定資產(chǎn)卡片數(shù)據(jù)任務(wù)四固定資產(chǎn)的調(diào)整管理1.固定資產(chǎn)的新增

公司2023年12月12日為財(cái)務(wù)部購(gòu)入了一臺(tái)價(jià)值9000元的聯(lián)想筆記本電腦，采用直線法計(jì)提折舊。【快速訪問工具欄】——【記錄單】（1）添加“記錄單”命令按鈕（2）新增固定資產(chǎn)2.固定資產(chǎn)的減少

公司為財(cái)務(wù)部購(gòu)置新電腦的原因是該部門2022年8月18日一臺(tái)編號(hào)為GL0302的臺(tái)式電腦因主板燒毀而無法使用了，公司于當(dāng)月將其作為二手電腦出售清理。(1)執(zhí)行【數(shù)據(jù)】|【排序和篩選】|【篩選】命令2.固定資產(chǎn)的減少(2)編制“固定資產(chǎn)減損單”3.固定資產(chǎn)的變動(dòng)

公司決定自2023年12月12日起，將資產(chǎn)編號(hào)GL0105的臺(tái)式電腦從行政部調(diào)撥給銷售部使用。(1)執(zhí)行【數(shù)據(jù)】|【排序和篩選】|【篩選】命令3.固定資產(chǎn)的變動(dòng)(2)編制“固定資產(chǎn)變動(dòng)單”1.折舊數(shù)據(jù)指標(biāo)分析任務(wù)五固定資產(chǎn)分析2.計(jì)算折舊數(shù)據(jù)

選擇“本月折舊額”單元格C3，輸入“=SUMIF(固定資產(chǎn)臺(tái)賬表!$U:$U,B3,固定資產(chǎn)臺(tái)賬表!$R:$R)”，并將其拖動(dòng)復(fù)制到C列以下的單元格。

選擇“本月末賬面凈值”單元格D3，輸入“=SUMIF(固定資產(chǎn)臺(tái)賬表!$U:$U,B3,固定資產(chǎn)臺(tái)賬表!$M:$M)-SUMIF(固定資產(chǎn)臺(tái)賬表!$U:$U,B3,固定資產(chǎn)臺(tái)賬表!$S:$S)”，同樣拖動(dòng)復(fù)制到D列以下的單元格。2.計(jì)算折舊數(shù)據(jù)

選擇“本月折舊額”單元格G3，輸入公式“=SUMIF(固定資產(chǎn)臺(tái)賬表!$F:$F,F3,固定資產(chǎn)臺(tái)賬表!$R:$R)”，并將其復(fù)制到G列以下的單元格中；

選擇本月末賬面凈值”單元格H3，輸入公式“=SUMIF(固定資產(chǎn)臺(tái)賬表!$F:$F,F3,固定資產(chǎn)臺(tái)賬表!$M:$M)-SUMIF(固定資產(chǎn)臺(tái)賬表!$F:$F,F3,固定資產(chǎn)臺(tái)賬表!$S:$S)”，并將其復(fù)制到H列以下的單元格中。2.計(jì)算折舊數(shù)據(jù)（1）生成“分析折舊費(fèi)用”組合圖3.數(shù)據(jù)可視化（2）生成“分析部門折舊額”餅圖THANKYOU!項(xiàng)目六Excel在存貨管理中的應(yīng)用項(xiàng)目一

任務(wù)一采購(gòu)與成品入庫(kù)業(yè)務(wù)處理任務(wù)二銷售與車間領(lǐng)料業(yè)務(wù)處理任務(wù)三庫(kù)存管理任務(wù)四存貨明細(xì)賬任務(wù)五制定薪金等級(jí)知識(shí)目標(biāo)和能力目標(biāo)知識(shí)目標(biāo)：1.掌握進(jìn)銷存的業(yè)務(wù)處理流程。2.了解進(jìn)銷存管理中數(shù)據(jù)之間的關(guān)系。能力目標(biāo)：1.學(xué)會(huì)使用Excel設(shè)計(jì)進(jìn)銷存管理系統(tǒng)。2.掌握工作表數(shù)據(jù)之間的操作。工作任務(wù)分解及操作1.進(jìn)貨流程：采購(gòu)員接到缺貨信息后，分析缺貨信息是否合理，再將訂單下達(dá)給供應(yīng)商；材料送達(dá)后，實(shí)物入庫(kù)，根據(jù)入庫(kù)單登記庫(kù)存賬。2.銷售流程：接收客戶訂單，簽約銷售合同，向客戶發(fā)貨并收款；每筆銷售業(yè)務(wù)發(fā)生時(shí)都要及時(shí)更新庫(kù)存。3.庫(kù)存管理流程：材料采購(gòu)入庫(kù)、產(chǎn)品完工入庫(kù)、領(lǐng)料退貨等均是涉及庫(kù)存變化的業(yè)務(wù)，均需在進(jìn)出時(shí)及時(shí)記錄并處理。o1采購(gòu)與成品入庫(kù)業(yè)務(wù)處理任務(wù)一采購(gòu)與成品入庫(kù)業(yè)務(wù)處理1.建立基礎(chǔ)信息表名稱引用位置范圍供應(yīng)商名稱=OFFSET(基礎(chǔ)信息表!$A$2,0,0,COUNTA(基礎(chǔ)信息表!$A:$A)-1)工作簿客戶名稱=OFFSET(基礎(chǔ)信息表!$B$2,0,0,COUNTA(基礎(chǔ)信息表!$B:$B)-1)工作簿原材料=OFFSET(基礎(chǔ)信息表!$D$2,0,0,COUNTA(基礎(chǔ)信息表!$D:$D)-1)工作簿庫(kù)存商品=OFFSET(基礎(chǔ)信息表!$I$2,0,0,COUNTA(基礎(chǔ)信息表!$I:$I)-1)工作簿定義名稱為了方便輸入并防止輸入錯(cuò)誤，可以進(jìn)行“數(shù)據(jù)驗(yàn)證”設(shè)置。數(shù)據(jù)驗(yàn)證2.編制“采購(gòu)業(yè)務(wù)表”

單元格E4，輸入“=VLOOKUP(D4,基礎(chǔ)信息表!$D:$G,2,0)”，則E列將返回存貨對(duì)應(yīng)的單位。

單元格F4，輸入“=VLOOKUP(D4,基礎(chǔ)信息表!$D:$G,3,0)”，則F列將返回存貨對(duì)應(yīng)的類別。

單元格G4，輸入“=VLOOKUP(D4,基礎(chǔ)信息表!$D:$G,4,0)”，則G列將返回存貨對(duì)應(yīng)的規(guī)格型號(hào)。完成采購(gòu)業(yè)務(wù)明細(xì)表應(yīng)付總額：N4=L4+M4其中：存貨成本：L4=I4*J4+K4分析采購(gòu)數(shù)據(jù)1）按供貨商查看知識(shí)技能一：分類匯總1.顯示或隱藏明細(xì)數(shù)據(jù)（1）運(yùn)用任務(wù)窗格的加減號(hào)和級(jí)別號(hào)知識(shí)技能一：分類匯總1.顯示或隱藏明細(xì)數(shù)據(jù)（1）運(yùn)用任務(wù)窗格的加減號(hào)和級(jí)別號(hào)知識(shí)技能一：分類匯總1.顯示或隱藏明細(xì)數(shù)據(jù)（2）運(yùn)用功能區(qū)選項(xiàng)卡中的按鈕知識(shí)技能一：分類匯總2.編輯或清除分類匯總

一個(gè)分類匯總雖然可以對(duì)多列進(jìn)行計(jì)算，但同時(shí)只能按一個(gè)字段匯總并執(zhí)行一種計(jì)算。知識(shí)技能一：分類匯總

在【匯總方式】下除了常規(guī)求和外，還可以求平均值、最大/最小值等；現(xiàn)以“計(jì)數(shù)”為例。知識(shí)技能一：分類匯總2.編輯或清除分類匯總

如果不再需要此分類匯總，可將其刪除。

需要說明的是，分類匯總一旦執(zhí)行全部刪除，Excel無法執(zhí)行“撤銷鍵入”操作，也就意味著無法回到分類匯總前的某項(xiàng)操作，實(shí)際工作中請(qǐng)注意數(shù)據(jù)的保存和備份。知識(shí)技能二：SUBTOTAL函數(shù)

通常，使用“分類匯總”命令創(chuàng)建的列表，可自動(dòng)生成SUBTOTAL函數(shù)。當(dāng)然，也可直接使用SUBTOTAL函數(shù)完成相關(guān)數(shù)據(jù)的匯總。

語(yǔ)法格式：SUBTOTAL(function_num,ref1,[ref2],...])。

function_num：可以指定1到11（包含隱藏值）或101到111（忽略隱藏值）之間的數(shù)字，用于指定使用何種函數(shù)在列表中進(jìn)行分類匯總計(jì)算。

ref：要進(jìn)行分類匯總計(jì)算的命名區(qū)域或引用。function_num（包含隱藏值）function_num（忽略隱藏值）對(duì)應(yīng)函數(shù)1101AVERAGE（算數(shù)平均值）2102COUNT（計(jì)算包含數(shù)字的單元格個(gè)數(shù)）3103COUNTA（計(jì)算不為空單元格的個(gè)數(shù)）4104MAX（一組值中的最大值）5105MIN（一組值中的最小值）6106PRODUCT（乘積）7107STDEV（估計(jì)基于樣本的標(biāo)準(zhǔn)偏差）8108STDEVP（整個(gè)樣本總體的標(biāo)準(zhǔn)偏差）9109SUM（求和）10110VAR(基于給定樣本的方差)11111VARP(基于整個(gè)樣本總體的方差)知識(shí)技能二：SUBTOTAL函數(shù)關(guān)于隱藏值的說明

實(shí)際運(yùn)用SUBTOTAL函數(shù)時(shí)，并不需要死記硬背每個(gè)數(shù)字具體對(duì)應(yīng)什么函數(shù)，當(dāng)單元格輸入“=SUBTOTAL(”后，Excel會(huì)給出參數(shù)供選擇關(guān)于隱藏值的說明2）按存貨名稱查看分析采購(gòu)數(shù)據(jù)先手動(dòng)將主分類字段進(jìn)行排序，Excel才會(huì)按此分類執(zhí)行匯總。2）按存貨名稱查看分析采購(gòu)數(shù)據(jù)知識(shí)技能三：排序

排序，是指按照指定的順序?qū)?shù)據(jù)重新排列組織，是數(shù)據(jù)整理的一種重要手段。通常，數(shù)據(jù)排序要求每列中的數(shù)據(jù)類型相同，而且不允許有空行或空列，也不能有合并的單元格。

1.排序操作（1）單列排序與多列排序（2）按顏色排序（3）自定義排序

1.排序操作2.SORT函數(shù)含義：SORT函數(shù)可對(duì)某個(gè)區(qū)域范圍或數(shù)組的內(nèi)容進(jìn)行排序。語(yǔ)法格式為：SORT(array,[sort_index],[sort_order],[by_col])其中：array是要排序的區(qū)域或數(shù)組；sort_index表示排序字段在所在array中的數(shù)字；sort_order表示所需排序順序的數(shù)字，默認(rèn)1表示升序，-1表示降序；by_col表示所需排序方向的邏輯值，默認(rèn)FALSE是按行排序，TRUE是按列排序。

3.SORTBY函數(shù)含義：如果想要對(duì)網(wǎng)格中的數(shù)據(jù)排序，最好用SORTBY函數(shù)。SORTBY函數(shù)是基于某范圍或數(shù)組中的值對(duì)一些列范圍或數(shù)組的內(nèi)容進(jìn)行排序。語(yǔ)法格式為：SORTBY(array,by_array1,[sort_order1])其中：array是要排序的區(qū)域或數(shù)組；by_array1是要進(jìn)行排序的范圍或數(shù)組的依據(jù)；sort_order表示所需排序順序的數(shù)字，默認(rèn)1表示升序，-1表示降序；因?yàn)榭梢詫?shí)現(xiàn)多字段排序，所以可根據(jù)需求添加by_array2和其對(duì)應(yīng)的sort_order2。3.編制“成品入庫(kù)表”單元格E4，輸入公式“=VLOOKUP(D4,基礎(chǔ)信息表!$I:$L,2,FALSE)”，則E列將返回存貨對(duì)應(yīng)的單位。單元格F4，輸入公式“=VLOOKUP(D4,基礎(chǔ)信息表!$I:$L,3,FALSE)”，則F列將返回存貨對(duì)應(yīng)的類別。單元格G4，輸入公式“=VLOOKUP(D4,基礎(chǔ)信息表!$I:$L,4,FALSE)”

則G列將返回存貨對(duì)應(yīng)的規(guī)格型號(hào)。3.編制“成品入庫(kù)表”

手工輸入H列“結(jié)轉(zhuǎn)數(shù)量”和I列“結(jié)轉(zhuǎn)單價(jià)”后，選擇單元格J4，輸入公式“=H4*I4”，即“結(jié)轉(zhuǎn)金額=結(jié)轉(zhuǎn)數(shù)量*結(jié)轉(zhuǎn)單價(jià)”o2銷售與車間領(lǐng)料業(yè)務(wù)處理

單元格F4，輸入“=IF(D4="材料",VLOOKUP(E4,基礎(chǔ)信息表!$D:$G,2,FALSE),IF(D4="商品",VLOOKUP(E4,基礎(chǔ)信息表!$I:$L,2,FALSE),“請(qǐng)檢查存貨名稱輸入是否正確”))”，返回存貨對(duì)應(yīng)的單位。同理返回G列存貨對(duì)應(yīng)的類別、H列存貨對(duì)應(yīng)的規(guī)格型號(hào)。1.編制“銷售業(yè)務(wù)表”任務(wù)二銷售與車間領(lǐng)料業(yè)務(wù)處理用IF函數(shù)區(qū)分不同的銷售業(yè)務(wù)

銷售業(yè)務(wù)，若實(shí)現(xiàn)主營(yíng)業(yè)務(wù)收入，減少的是庫(kù)存商品，若實(shí)現(xiàn)其他業(yè)務(wù)收入，減少的是原材料，故要增加“摘要”來區(qū)分不同的屬性。1.編制“銷售業(yè)務(wù)表”2.編制”車間領(lǐng)料表”同“采購(gòu)業(yè)務(wù)表”，設(shè)置E列、F列和G列相關(guān)公式選擇J4單元格，輸入“=H4*I4”知識(shí)技能三：多條件求和間的區(qū)別o3庫(kù)存管理任務(wù)三庫(kù)存管理1.編制“庫(kù)存管理表”

材料是采購(gòu)入庫(kù)的，數(shù)據(jù)來源為“采購(gòu)業(yè)務(wù)表”；產(chǎn)成品是車間生產(chǎn)完工后結(jié)轉(zhuǎn)入庫(kù)的，數(shù)據(jù)來源為“成品入庫(kù)表”。

材料的減少可能是車間領(lǐng)料，也可能是出售多余材料，所以數(shù)據(jù)來源包含“銷售業(yè)務(wù)表”和“車間領(lǐng)料表”；產(chǎn)品的減少一般來說是因?yàn)殇N售實(shí)現(xiàn)了主營(yíng)業(yè)務(wù)收入，所以數(shù)據(jù)來源為“銷售業(yè)務(wù)表”。2.編輯相關(guān)單元格公式（1）原材料的“入庫(kù)數(shù)量”和“入庫(kù)金額”（2）產(chǎn)成品的“入庫(kù)數(shù)量”和“入庫(kù)金額”（3）原材料的“出庫(kù)數(shù)量”和“出庫(kù)金額”（4）產(chǎn)成品的“出庫(kù)數(shù)量”和“出庫(kù)金額”（5）計(jì)算“期末數(shù)量”和“期末金額”3.錄入業(yè)務(wù)，美化單元格格式設(shè)置“預(yù)警”列【條件格式】

選擇單元格N4，輸入公式“=IF(K4<=M4,"*","")”3.錄入業(yè)務(wù)，美化單元格格式設(shè)置“預(yù)警”列【條件格式】

條件格式可以根據(jù)特定條件對(duì)數(shù)據(jù)進(jìn)行格式標(biāo)識(shí)，以更加直觀地獲取特定問題的視覺提示，常被應(yīng)用在單元格、表格和數(shù)據(jù)透視表中。“條件格式”的菜單欄由3塊內(nèi)容構(gòu)成：1．僅對(duì)部分單元格設(shè)置格式2.使用數(shù)據(jù)條/色階/圖標(biāo)集設(shè)置所有單元格格式3.新建/管理/清除規(guī)則

知識(shí)技能四：條件格式1．僅對(duì)部分單元格設(shè)置格式知識(shí)技能四：條件格式2．使用數(shù)據(jù)條/色階/圖標(biāo)集設(shè)置所有單元格格式知識(shí)技能四：條件格式3．新建/管理/清除規(guī)則知識(shí)技能四：條件格式

增設(shè)“安全”列，執(zhí)行【新