Excel在財(cái)務(wù)中的應(yīng)用 第3版 課件 項(xiàng)目4、5 Excel在薪資管理中的應(yīng)用、Excel在固定資產(chǎn)管理中的應(yīng)用

Excel在薪酬管理中的應(yīng)用

原先公司也有工資計(jì)算的表格，但是一旦增加或減少了員工，或者獎(jiǎng)金等發(fā)生了調(diào)整，工資表就又要重新編制，這就失去了Excel的作用。

因此李燕決定重新編制職工薪資管理系統(tǒng)表格，通過設(shè)置多個(gè)原始表格，利用Excel表格強(qiáng)大的數(shù)據(jù)處理能力，讓薪資管理變得簡(jiǎn)單便捷。工作情境工作任務(wù)分析

李燕決定要完成以下設(shè)置：1.建立工資管理框架，包括編制員工信息表、編制員工考勤信息表、編制固定薪資信息表等；2.完成工資信息輸入、個(gè)稅的計(jì)算；3.完成工資條的查詢與打印。任務(wù)一建立工資管理系統(tǒng)表格任務(wù)二編制“職工當(dāng)月工資匯總表”任務(wù)三薪資的查詢與工資條制作建立工資管理系統(tǒng)表格o1由于職工薪酬管理需要用到很多信息，包括公司職工信息、薪資獎(jiǎng)懲規(guī)定信息、考勤信息、個(gè)稅稅率等，這些表格不能全部放在一張工作表中，必須要進(jìn)行分類，只有各個(gè)表格的內(nèi)容都相對(duì)獨(dú)立又有相互聯(lián)系，才能使表格在符合特定規(guī)范的同時(shí)，又能利用公式對(duì)Excel表格之間的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)和計(jì)算。任務(wù)一建立工資管理系統(tǒng)表格1.編制“公司職工檔案”工作表(1)創(chuàng)建超級(jí)表復(fù)制項(xiàng)目一中做過的“萬隆燈具有限公司職工信息表”表格，刪除薪資管理中不需要用到“聯(lián)系號(hào)碼”、“身份證號(hào)碼”和“出生日期”（H、I、J三列）后，創(chuàng)建超級(jí)表。任務(wù)一建立工資管理系統(tǒng)表格(2)套用表格格式2.計(jì)算職工的通信費(fèi)補(bǔ)貼通訊費(fèi)補(bǔ)貼標(biāo)準(zhǔn)：總經(jīng)理500元，學(xué)歷研究生及以上且職稱為高級(jí)或者是部門經(jīng)理都是300元，其他職工150元。=IF(G2="總經(jīng)理",500,IF(OR(AND(D2="研究生",E2="高級(jí)"),G2="部門經(jīng)理"),300,150))

在表格狀態(tài)下，公式是自動(dòng)套用的“[@職務(wù)]”代表G列“[@最高學(xué)歷]”代表D列“[@職稱]”代表E列知識(shí)技能4-1認(rèn)識(shí)表格要簡(jiǎn)化一組相關(guān)數(shù)據(jù)的管理和分析，可以將單元格范圍轉(zhuǎn)換為表格。表格功能的好處如下：

（1）表格包含一定的格式

可以通過“套用表格格式”使表格數(shù)據(jù)內(nèi)容顯示得更為清晰，打印效果更為美觀。（2）表格樣式選項(xiàng)知識(shí)技能4-1認(rèn)識(shí)表格

勾選篩選按鈕，可實(shí)現(xiàn)對(duì)標(biāo)題行進(jìn)行排序和篩選；還可勾選匯總行，實(shí)現(xiàn)在不輸入公式的情況下快速匯總表中的相關(guān)數(shù)據(jù)信息。知識(shí)技能4-1認(rèn)識(shí)表格（3）表格會(huì)使得引用結(jié)構(gòu)化在進(jìn)行引用時(shí)可以不使用“A1”之類的引用，而是在公式中引用表名或列名，實(shí)現(xiàn)數(shù)據(jù)公式的自動(dòng)擴(kuò)展，極大提高了用戶管理和分析多表數(shù)據(jù)內(nèi)容的效率。“[@職務(wù)]”代表G列“[@最高學(xué)歷]”代表D列“[@職稱]”代表E列（4）自動(dòng)生成表格名稱

表格在生成的同時(shí)，會(huì)自動(dòng)在“公式”功能區(qū)“定義的名稱”組“名稱管理器”中生成該表的名稱，使跨工作表引用更加便捷。如果想更改該表的名稱，除了通過“名稱管理器”編輯，還能在表格的“設(shè)計(jì)”選項(xiàng)卡中修改。知識(shí)技能4-1認(rèn)識(shí)表格表的名稱在“名稱管理器”中只能編輯，不能刪除。3.編制“薪資獎(jiǎng)懲標(biāo)準(zhǔn)表”工作表3.編制“薪資獎(jiǎng)懲標(biāo)準(zhǔn)表”工作表

選擇【視圖】選項(xiàng)卡，將網(wǎng)格線前面的小方框的勾去掉，整個(gè)工作表就會(huì)呈現(xiàn)白紙狀態(tài)。4.編制“職工考勤獎(jiǎng)懲表”工作表（1）設(shè)置表格表頭單元格公式含義B2=VLOOKUP($A2,表1,2,0)根據(jù)序號(hào)從“表1”名稱范圍中找對(duì)應(yīng)的職工姓名C2=VLOOKUP($A2,表1,6,0)根據(jù)序號(hào)從“表1”名稱范圍中找對(duì)應(yīng)的職工部門D2=VLOOKUP($A2,表1,7,0)根據(jù)序號(hào)從“表1”名稱范圍中找對(duì)應(yīng)的職工職務(wù)E2=VLOOKUP($A2,表1,10,0)根據(jù)序號(hào)從“表1”名稱范圍中找對(duì)應(yīng)的職工工資（含津貼）（2）引用“公司職工檔案”工作表的數(shù)據(jù)4.編制“職工考勤獎(jiǎng)懲表”工作表（3）輸入職工的考勤情況并計(jì)算獎(jiǎng)懲工資根據(jù)職工本月的請(qǐng)假及加班天數(shù)在“職工考勤獎(jiǎng)懲表”的“病假”“事假”“曠工”（I:M列）中進(jìn)行填列，并通過這些考勤數(shù)據(jù)計(jì)算“全勤工資”“病事假/曠工扣款”“加班工資”（F:H列）。4.編制“職工考勤獎(jiǎng)懲表”工作表（3）輸入職工的考勤情況并計(jì)算獎(jiǎng)懲工資單元格公式F2=IF((I2=0)*(J2=0)*(K2=0),薪資獎(jiǎng)懲標(biāo)準(zhǔn)表!$C$4,0)

F2單元格，如果沒有“病假”“事假”和“曠工”的話，那么就從“薪資獎(jiǎng)懲標(biāo)準(zhǔn)表”的C4單元格（全勤獎(jiǎng)）取數(shù)200元。4.編制“職工考勤獎(jiǎng)懲表”工作表（3）輸入職工的考勤情況并計(jì)算獎(jiǎng)懲工資單元格公式G2=ROUND($E2/30*(0.1*$I2+0.5*$J2+$K2,2)根據(jù)E2單元格先計(jì)算出日工資（假定一個(gè)月30天），然后再分別與“病假”“事假”“曠工”的獎(jiǎng)金計(jì)算標(biāo)準(zhǔn)相乘。其中，病假缺勤系數(shù)（0.1）、病假天數(shù)（I2）”；事假缺勤系數(shù)（0.5）、事假天數(shù)（J2）”；曠工缺勤工資不需要系數(shù)，直接與曠工天數(shù)（K2）相乘。最后用ROUND函數(shù)對(duì)結(jié)果取兩位小數(shù)。知識(shí)技能4-2ROUND函數(shù)含義：

將數(shù)字四舍五入到指定的位數(shù)，也就是我們常說的四舍五入。

語法格式：ROUND（number,num_digits）其中：number是要四舍五入的數(shù)字，num_digits是四舍五入的位數(shù)，即計(jì)算精度。如果num_digits大于零，則將數(shù)字四舍五入到指定的小數(shù)位數(shù)；等于零，則將數(shù)字四舍五入到最接近的整數(shù)；小于零，則將數(shù)字四舍五入到小數(shù)點(diǎn)左邊的相應(yīng)位數(shù)。單元格公式H2=300*$L2+200*$M2如果是在法定節(jié)假日加班，則用法定假日加班天數(shù)（L2）乘以法定假日加班工資（300）；如果是普通雙休日加班，則用普通假日加班天數(shù)（M2）乘以日常假日加班工資（200）。

最后將兩者相加得到總的加班工資。（3）輸入職工的考勤情況并計(jì)算獎(jiǎng)懲工資4.編制“職工考勤獎(jiǎng)懲表”工作表4.編制“職工考勤獎(jiǎng)懲表”工作表編制“職工當(dāng)月工資匯總表”o2任務(wù)二編制“職工當(dāng)月工資匯總表”1.職工工資匯總表表頭2.職工工資匯總表取數(shù)“職工當(dāng)月工資匯總表”工作表的取數(shù)來源于“職工考勤獎(jiǎng)懲表”，可將“職工考勤獎(jiǎng)懲表”的A1:M16單元格區(qū)域定義為名稱“考勤”，因?yàn)楹竺嬖诤瘮?shù)取數(shù)中需要用到這塊數(shù)據(jù)。2.職工工資匯總表取數(shù)單元格公式B2=VLOOKUP($A2,考勤,2,0)C2=VLOOKUP($A2,考勤,3,0)D2=VLOOKUP($A2,考勤,4,0)E2=VLOOKUP($A2,考勤,5,0)F2=VLOOKUP($A2,考勤,6,0)G2=VLOOKUP($A2,考勤,7,0)H2=VLOOKUP($A2,考勤,8,0)I2=VLOOKUP($A2,表1,11,0)取數(shù)方法不唯一，供參考3.“職工當(dāng)月工資匯總表”中“三險(xiǎn)一金”的計(jì)算“四險(xiǎn)一金”是指用人單位給予勞動(dòng)者的幾種保障性待遇的合稱，包括養(yǎng)老保險(xiǎn)、醫(yī)療保險(xiǎn)、失業(yè)保險(xiǎn)、工傷保險(xiǎn)、住房公積金；其中，生育保險(xiǎn)基金并入職工基本醫(yī)療保險(xiǎn)基金，統(tǒng)一征繳?！八碾U(xiǎn)一金”通常是根據(jù)應(yīng)納稅所得額乘以計(jì)提比例計(jì)算得到的，而計(jì)提比例各個(gè)省份也會(huì)有所不同。這里會(huì)涉及養(yǎng)老保險(xiǎn)、醫(yī)療保險(xiǎn)、失業(yè)保險(xiǎn)個(gè)人計(jì)提的部分，進(jìn)而會(huì)影響到職工的薪資計(jì)算，因此“三險(xiǎn)一金”的計(jì)算非常重要。3.“職工當(dāng)月工資匯總表”中“三險(xiǎn)一金”的計(jì)算(1)“應(yīng)發(fā)工資”的計(jì)算

根據(jù)應(yīng)發(fā)工資的含義，J2單元格的公式設(shè)定為“

E2

F2

G2

H2

I2”，并向下填充該列；也就是說應(yīng)發(fā)工資等于該職工的工資（含津貼）加上獎(jiǎng)金、補(bǔ)貼后扣除缺勤工資的金額。3.“職工當(dāng)月工資匯總表”中“三險(xiǎn)一金”的計(jì)算(2)“社?！钡挠?jì)算公司按10.5%的個(gè)人計(jì)提比例（即養(yǎng)老保險(xiǎn)8%、醫(yī)療保險(xiǎn)2%、失業(yè)保險(xiǎn)0.5%），通信費(fèi)不參與計(jì)提。因此，K2單元格=ROUND(($J2-$I2)*8%,2)，L2單元格=ROUND(($J2-$I2)*2%,2)，M2單元格=ROUND(($J2-$I2)*0.5%,2)，并向下填充該列。3.“職工當(dāng)月工資匯總表”中“三險(xiǎn)一金”的計(jì)算(3)“公積金”的計(jì)算單位和職工繳存比例最低不低于5%，原則上不高于12%。萬隆燈具有限公司目前一律采用10%的比例來計(jì)提，所以，公積金=（應(yīng)發(fā)工資-通信費(fèi)）*10%。L2單元格=ROUND(($J2-$I2)*10%,2)，并向下填充該列。3.“職工當(dāng)月工資匯總表”中“三險(xiǎn)一金”的計(jì)算4.編制“職工個(gè)稅計(jì)算表”工作表從2019年開始，扣繳義務(wù)人向居民個(gè)人支付工資、薪金所得時(shí)，按照累計(jì)預(yù)扣法計(jì)算預(yù)扣稅款，并按月辦理扣繳申報(bào)，采用“累計(jì)預(yù)扣法”。也就是說，先把截止當(dāng)月所有工資，相當(dāng)于“年”算個(gè)稅，然后再減去截止到上月已累計(jì)繳納金額，剩下的就是當(dāng)月需要交稅金額。因?yàn)槭穷A(yù)繳，需要到第二年3-6月，再進(jìn)行“匯算清繳”。（1）編制“職工個(gè)稅計(jì)算表”表頭（2）“職工個(gè)稅計(jì)算表”的取數(shù)

A列、B列數(shù)據(jù)可以參照前文的方法，實(shí)現(xiàn)跨表格取數(shù)。4.編制“職工個(gè)稅計(jì)算表”工作表（2）“職工個(gè)稅計(jì)算表”的取數(shù)

C列以后的數(shù)據(jù)因?yàn)樯婕暗嚼塾?jì)的概念，作為連續(xù)性，年初第一期時(shí)，就應(yīng)將“職工當(dāng)月工資匯總表”的相關(guān)信息摘錄到該表中，到第二個(gè)月時(shí)，將上一期的金額和本期金額相加，就能得到累計(jì)值了。所以，2023年12月的“職工個(gè)稅計(jì)算表”取數(shù)即為前11個(gè)月的數(shù)據(jù)加上本月計(jì)算的值。

考慮到李燕所在的工資之前沒有用Excel計(jì)算過個(gè)數(shù)，我們直接從“自然人稅收管理系統(tǒng)扣繳客戶端”中取數(shù)。（2）“職工個(gè)稅計(jì)算表”的取數(shù)（3）“累計(jì)應(yīng)納稅所得額”的計(jì)算累計(jì)預(yù)扣預(yù)繳應(yīng)納稅所得額=累計(jì)收入-累計(jì)減除費(fèi)用-累計(jì)專項(xiàng)扣除-累計(jì)專項(xiàng)附加扣除-累計(jì)依法確定的其他扣除。“累計(jì)減除費(fèi)用”該列數(shù)值即按照5000元/月乘以納稅人當(dāng)年截至本月在本單位的任職受雇月份數(shù)計(jì)算的數(shù)額?！袄塾?jì)專項(xiàng)扣除”具體包括社會(huì)保險(xiǎn)費(fèi)和住房公積金，也就是我們上一節(jié)中“五險(xiǎn)一金”計(jì)算金額的累計(jì)值。

“累計(jì)專項(xiàng)附加扣除”包括“累計(jì)子女教育支出扣除”“累計(jì)繼續(xù)教育支出扣除”“累計(jì)住房貸款利息支出扣除”“累計(jì)住房租金支出扣除““累計(jì)贍養(yǎng)老人支出扣除”“累計(jì)3歲以下嬰幼兒照護(hù)支出扣除”“累計(jì)其他扣除”。（3）“累計(jì)應(yīng)納稅所得額”的計(jì)算選擇M2單元格輸入公式“=IF(C2-SUM(D2:M2)>0,C2-SUM(D2:M2),0)”，確保應(yīng)納稅所得額均大于零。5.“個(gè)人所得稅”的計(jì)算個(gè)人所得稅=應(yīng)納稅所得額*稅率-速算扣除數(shù)。

從“自然人稅收管理系統(tǒng)扣繳客戶端”導(dǎo)出的表格可以看到有“稅率”和“速算扣除數(shù)”兩個(gè)輔助列，因?yàn)镋xcel具有強(qiáng)大的計(jì)算功能，所以我們的表格中拿掉了這兩列，直接計(jì)算“累計(jì)應(yīng)納稅額”。

通?？梢钥紤]使用IF系列函數(shù)、VLOOKUP函數(shù)、MAX函數(shù)或數(shù)組實(shí)現(xiàn)“個(gè)人所得稅”的計(jì)算。（1）利用IF函數(shù)計(jì)算“個(gè)人所得稅”單元格公式O2=IF(N2<36000,N2*3%,IF(N2<144000,N2*10%-2520,IF(N2<300000,N2*20%-16920,IF(N2<420000,N2*25%-31920,IF(N2<660000,N2*30%-52920,IF(N2<960000,N2*35%-85920,N2*45%-181920))))))IFS函數(shù)該如何實(shí)現(xiàn)呢

如果覺得IF嵌套函數(shù)掌握得不好，也可以用IF結(jié)合AND實(shí)現(xiàn)“個(gè)人所得稅”的計(jì)算。單元格公式O2=IF(AND(N2>=0,N2<=36000),N2*3%,"")&IF(AND(N2>36000,N2<=144000),N2*10%-2520,"")&IF(AND(N2>144000,N2<=300000),N2*20%-16920,"")&IF(AND(N2>300000,N2<=420000),N2*25%-31920,"")&IF(AND(N2>420000,N2<=660000),N2*30%-52920,"")&IF(AND(N2>660000,N2<=960000),N2*35%85920,"")&IF(N2>960000,N2*45%-181920,"")連接區(qū)間段時(shí)，一定要確保連續(xù)性（2）利用VLOOKUP函數(shù)計(jì)算“個(gè)人所得稅”單元格公式O2=N2*(VLOOKUP(N2,薪資獎(jiǎng)懲標(biāo)準(zhǔn)表!$J$3:$L$9,2,1))-VLOOKUP(N2,薪資獎(jiǎng)懲標(biāo)準(zhǔn)表!$J$3:$L$9,3,1)（3）利用MAX函數(shù)計(jì)算“個(gè)人所得稅”單元格公式O2=MAX(N2*0.03,N2*0.1-2520,N2*0.2-16920,N2*0.25-31920,N2*0.3-52920,N2*0.35-85920,N2*0.45-181920)只有在符合級(jí)次的范圍內(nèi)計(jì)算出的“個(gè)人所得稅”才是最大的。知識(shí)技能4-3極值統(tǒng)計(jì)1．MAX函數(shù)和MIN函數(shù)含義：MAX是maximum的簡(jiǎn)寫，通常用于返回一組值中的最大值。

語法格式：MAX(number1,[number2],...)。其中，參數(shù)number1是必需的，后續(xù)參數(shù)是可選的，最多可包含255個(gè)參數(shù)。參數(shù)可以是數(shù)字或者是包含數(shù)字的名稱、數(shù)組或引用。

有最大值就會(huì)有最小值，MIN是minimum的簡(jiǎn)寫，通常用于返回一組值中的最小值，語法格式和參數(shù)設(shè)定同MAX函數(shù)。知識(shí)技能4-3極值統(tǒng)計(jì)2．LARGE函數(shù)和SMALL函數(shù)其中，array表示需要確定最大值的數(shù)組或數(shù)據(jù)區(qū)域；k表示返回的數(shù)據(jù)在array區(qū)域中從大到小排列的位置。如果區(qū)域中數(shù)據(jù)點(diǎn)的個(gè)數(shù)為n，則函數(shù)LARGE(array,1)返回最大值，函數(shù)LARGE(array,n)返回最小值?？梢?，LARGE函數(shù)比MAX函數(shù)的應(yīng)用范圍更廣。含義：LARGE函數(shù)用于返回?cái)?shù)據(jù)集中第k個(gè)最大值。

語法格式：LARGE(array,k,...)。知識(shí)技能4-3極值統(tǒng)計(jì)2．LARGE函數(shù)和SMALL函數(shù)其中，k表示返回的數(shù)據(jù)在array區(qū)域中從小到大排列的位置。同樣，如果區(qū)域中數(shù)據(jù)點(diǎn)的個(gè)數(shù)為n，則函數(shù)SMALL（array，1）返回最小值，函數(shù)SMALL（array，n）返回最大值。含義：SMALL函數(shù)和LARGE函數(shù)相對(duì)應(yīng)，用于返回?cái)?shù)據(jù)集中第k個(gè)最小值。。

語法格式：SMALL(array,k,...)。（4）利用數(shù)組計(jì)算“個(gè)人所得稅”單元格公式O2=ROUND(MAX(N2*{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-{0,2520,16920,31920,52920,85920,181920}),2)如果想用已有的“薪資獎(jiǎng)懲標(biāo)準(zhǔn)表”中的數(shù)據(jù)，需要注意絕對(duì)引用該區(qū)域數(shù)值，同時(shí)按住<Ctrl+Shift+Enter>組合鍵，才能實(shí)現(xiàn)向下拖動(dòng)。知識(shí)技能4-4數(shù)組

數(shù)組是指按規(guī)則排列的一組數(shù)據(jù)的集合，包括常量數(shù)組和內(nèi)存數(shù)組。其中，常量數(shù)組是常量的集合，用花括號(hào)括起來；內(nèi)存數(shù)組是指通過計(jì)算得到的數(shù)組，存在內(nèi)存中，并可作為函數(shù)的參數(shù)參與下一步運(yùn)算。按維度分，Excel公式涉及一維數(shù)組和二維數(shù)組。一維數(shù)組按呈現(xiàn)狀態(tài)不同，又可進(jìn)一步分為橫向一維數(shù)組和縱向一維數(shù)組。橫向一維數(shù)組在Excel中只占用一行，并用逗號(hào)作為分隔符；縱向一維數(shù)組則是只占用一列，并用分號(hào)作為分隔符。知識(shí)技能4-4數(shù)組橫向一維數(shù)組縱向一維數(shù)組知識(shí)技能4-4數(shù)組常量與一維數(shù)組的乘法計(jì)算應(yīng)納稅所得額*稅率需要同時(shí)按住<Ctrl+Shift+Enter>組合鍵，才能實(shí)現(xiàn)數(shù)組的乘法計(jì)算。知識(shí)技能4-4數(shù)組一維數(shù)組的減法計(jì)算應(yīng)納稅所得額*稅率-速算扣除數(shù)（5）“職工個(gè)稅計(jì)算表”中其它項(xiàng)目計(jì)算（6）“職工當(dāng)月工資匯總表”中“實(shí)發(fā)工資”的計(jì)算薪資的查詢與工資條制作o31.薪資信息的查詢（1）自動(dòng)篩選如果是表格的創(chuàng)建中，會(huì)在首行自動(dòng)創(chuàng)建帶有“篩選”按扭。如果是普通單元格區(qū)域，可以選擇需要篩選的標(biāo)題行，執(zhí)行【數(shù)據(jù)】|【篩選】，也可以直接在【開始】選項(xiàng)卡中執(zhí)行【排序和篩選】|【篩選】。任務(wù)三薪資的查詢與工資條制作1.薪資信息的查詢（1）自動(dòng)篩選查詢?cè)撛隆吧a(chǎn)車間”的薪資水平若想還原成初始狀態(tài)，可單擊“部門”列標(biāo)簽后的“篩選”按鈕，并在彈出的對(duì)話框中勾選“全選”；也可按<Ctrl+Shift+L>的組合鍵，退出篩選狀態(tài)。1.薪資信息的查詢（2）自定義篩選篩選該公司實(shí)發(fā)工資前五名的員工通配符1.薪資信息的查詢（2）自定義篩選（3）高級(jí)篩選在篩選的源數(shù)據(jù)區(qū)域外手動(dòng)設(shè)定篩選條件區(qū)域：

1.條件區(qū)域的第一行為條件的列標(biāo)簽行，需要與篩選的源數(shù)據(jù)區(qū)域的篩選條件列標(biāo)簽相同；在該列標(biāo)簽下方，至少應(yīng)包含一行具體的篩選條件。2.如果字段具有兩個(gè)或以上篩選條件時(shí)，可以在對(duì)應(yīng)的列標(biāo)簽下方單元格依次列出各個(gè)條件，其邏輯關(guān)系為“或”；但若在同一行對(duì)應(yīng)的列標(biāo)簽下輸入各個(gè)條件，則各條件間的邏輯關(guān)系為“與”。

因此，若要篩選滿足多組條件之一的情況，可將各組條件輸入在條件區(qū)域的不同行上。1.薪資信息的查詢（3）高級(jí)篩選1.薪資信息的查詢（3）高級(jí)篩選2.職工工資條查詢（1）利用VLOOKUP函數(shù)進(jìn)行查詢2.職工工資條查詢（2）利用OFFSET和MATCH函數(shù)進(jìn)行查詢3.工資條的制作（1）利用函數(shù)制作全員工資條=IF(MOD(ROW(A1),3)>0,OFFSET($A$1,IF(MOD(ROW(A1)-1,3),ROUNDUP(ROW(A1)/3,0),0),COLUMN(A1)-1),"")知識(shí)技能4-4取余函數(shù)與行列引用函數(shù)（1）MOD函數(shù)，用于返回兩數(shù)相除的余數(shù)。語法格式為：MOD（number，divisor）其中，number是被除數(shù)，divisor是除數(shù)，所以如果divisor為0，函數(shù)會(huì)返回錯(cuò)誤值#DIV/0!。（2）ROW函數(shù)和COLUMN函數(shù)，用于返回引用的行號(hào)和列號(hào)。語法格式為：ROW（[reference]）COLUMN函數(shù)參數(shù)及語法格式同ROW函數(shù)。知識(shí)技能4-4取余函數(shù)與行列引用函數(shù)（3）ROUNDUP函數(shù)，用于將數(shù)字以遠(yuǎn)離零值的方向進(jìn)行向上舍入。語法格式為：ROUNDUP（number，num_digits）其中，number是要進(jìn)行向上舍入的數(shù)字，num_digits是要將數(shù)字舍入到的位數(shù)。有UP就會(huì)有DOWN，ROUNDDOWN函數(shù)用于將數(shù)字以朝向零值的方向進(jìn)行向下舍入。修改薪資序號(hào)為偶數(shù)序列，修改表頭序號(hào)為奇數(shù)序列，執(zhí)行“篩選——升序”操作。3.工資條的制作（2）利用排序功能制作全員工資條（3）利用定位功能制作全員工資條交叉輸入任意數(shù)字，并使用拖動(dòng)填充柄的方法填充至工資明細(xì)表的結(jié)束行。3.工資條的制作（3）利用定位功能制作全員工資條3.工資條的制作（3）利用定位功能制作全員工資條3.工資條的制作THANKYOU!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)購入后，先由各相關(guān)部門驗(yàn)收，出具意見書；

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

③財(cái)務(wù)部對(duì)購入固定資產(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)賬，作用是登記購入固定資產(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ǔ)信息表萬隆燈具有限公司固定資產(chǎn)分為4類，編號(hào)及相關(guān)折舊年限、凈殘值率分別為：1.建立基礎(chǔ)信息表公司包括行政部、生產(chǎn)車間、財(cái)務(wù)部、銷售部、研發(fā)部、采購部六個(gè)部門，固定資產(chǎn)的所屬部門使用固定資產(chǎn)并負(fù)責(zé)其日常維護(hù)。1.建立基礎(chǔ)信息表固定資產(chǎn)的集中管理在財(cái)務(wù)部，每個(gè)固定資產(chǎn)都有一張自己的卡片，記錄著其增加方式、減少方式、購置日期、規(guī)格型號(hào)、所屬部門、原值、累計(jì)折舊、凈值、折舊方法等信息。1.建立基礎(chǔ)信息表2.建立固定資產(chǎn)臺(tái)賬表(1)設(shè)置“固定資產(chǎn)臺(tái)賬表”初始數(shù)據(jù)庫關(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是乘積，合起來就是乘積之和的意思。

語法格式：SUMPRODUCT（[array1],

[array2],

[array3],

…）。其中：[array]是數(shù)組。知識(shí)技能一：SUMPRODUCT函數(shù)SUMPRODUCT函數(shù)語法格式中([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è)月的具體日期。

語法格式：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ì)算某一特定月份中最后一天的日期。

語法格式：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)是否該月購進(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è)日期的年份

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

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

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

語法格式：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)的直線折舊，常用于平均年限法。語法格式為：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ù)語法格式為：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)的折舊值。語法格式：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ù)按照我國會(huì)計(jì)準(zhǔn)則對(duì)雙倍余額遞減法的定義可知，在固定資產(chǎn)使用年限的最后兩年，應(yīng)改用年限平均法。（4）VDB函數(shù)含義：用雙倍余額遞減法或其他指定方法，返回給定期間內(nèi)的折舊值；通常VDB用于代表可變余額遞減法。語法格式：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ù)含義：將文本字符串中的舊文本替換成新文本。語法格式：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è)字符串。語法格式：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,MATC