《Excel在財(cái)務(wù)中的應(yīng)用》課件項(xiàng)目三

1、EXCEL在財(cái)務(wù)中的應(yīng)用于凌云項(xiàng)目三 EXCEL在薪資管理中的應(yīng)用學(xué)習(xí)目的：分析薪資管理中運(yùn)用的數(shù)據(jù)；掌握數(shù)據(jù)的數(shù)據(jù)引用、計(jì)算、排序、篩選、分類匯總、數(shù)據(jù)透視表以及數(shù)據(jù)透視圖等內(nèi)容操作；以薪資管理表為引例，拓展應(yīng)用，觸類旁通。3.1 薪資表的建立3.1.1 相關(guān)數(shù)據(jù)分析計(jì)算1.薪資明細(xì)表一般包括單位人員的基本工資信息，為了便于分析和理解，我們把薪資明細(xì)表簡化為以下內(nèi)容，并根據(jù)計(jì)算，得出結(jié)果，如圖3-1所示。保險(xiǎn)扣款：假定保險(xiǎn)扣款按基本工資和崗位津貼的15%扣除。（1）應(yīng)發(fā)工資：用SUM函數(shù)求得。（2）保險(xiǎn)扣款：用公式“應(yīng)發(fā)工資*15%”求得。（3）考勤扣款：引用職工考勤表中的“扣款合計(jì)”。（4

2、）個(gè)人所得稅扣款：引用職工納稅表的“應(yīng)納個(gè)人所得稅”。（5）實(shí)發(fā)工資：用公式“應(yīng)發(fā)工資-考勤扣款-保險(xiǎn)扣款-個(gè)人所得稅扣款”求得。3.1 薪資表的建立2.職工考勤表假定企業(yè)相關(guān)制度規(guī)定：病假扣款：病假大于等于10天，銷售部扣1000元，非銷售部扣900元，病假小于10天，銷售部扣500元，非銷售部扣400元。事假扣款：事假大于等于7天,扣基本工資的50%,小于7天,按實(shí)際天數(shù)扣款(每月按22天計(jì)算)。（1）病假扣款：根據(jù)要求，用公式“=IF(E3=0,0,IF(E3=10,IF(C3=銷售部,1000,900),IF(C3=銷售部,500,400)”求得。（2）事假扣款：根據(jù)要求，用公式“=I

3、F(F3=0,0,IF(F3=7,D3*0.5,F3*D3/22)”（3）扣款合計(jì)：用SUM函數(shù)求病假扣款、事假扣款之和。計(jì)算結(jié)果如圖3-2所示。3.1 薪資表的建立3.職工納稅表個(gè)人所得稅按新個(gè)稅起征點(diǎn)3500稅率參考表中扣交，見表3-1。注意：該表僅用于EXCEL中IF語句實(shí)踐，工作中涉及的個(gè)稅還應(yīng)以個(gè)人所得稅法為準(zhǔn)。（1）應(yīng)納稅工資額：根據(jù)公式“=C3+D3-3500”求得。（2）應(yīng)納個(gè)人所得稅：根據(jù)以下公式求得：“=IF(E3=1500,E3*0.03,IF(E3=4500,E3*0.1-105,IF(E3=9000,E3*0.2-555,IF(E3=35000,E3*0.25-100

4、5,IF(E3=55000,E3*0.3-2755,IF(E3=80000,E3*0.35-5505,E3*0.45-13505)”計(jì)算結(jié)果如圖3-3所示。3.1 薪資表的建立3.1.2 制作職工工資條職工工資條便于職工了解工資項(xiàng)目及工資明細(xì)，這里我們介紹一種利用VLOOKUP函數(shù)制作工資條的方法。1.VLOOKUP函數(shù)，功能：是一個(gè)查找和引用函數(shù)，可以在表格或區(qū)域中按行查找內(nèi)容。3.1 薪資表的建立3.1.2 制作職工工資條2.制作工資條的步驟(1)建立“職工工資條”表，復(fù)制“薪資明細(xì)表”表頭部分。(2)在“薪資明細(xì)表”中，選A2:J22,定義名稱為“工資條”。(3)在“職工工資條”中，在A

5、3單元格中輸入編號(hào)“001”，在B3單元格中輸入公式“=VLOOKUP(A3,工資條,2,0)”。(4)選中B3填充柄向右復(fù)制公式，并依次修改首列號(hào)和所在列號(hào)。(5)選中A1:J3區(qū)域，按住填充柄，向下復(fù)制，就得出職工工資條。3.2數(shù)據(jù)排序3.2.1數(shù)據(jù)排序概述1.對(duì)文本、數(shù)字、日期類型的數(shù)據(jù)進(jìn)行排序2.按多列或多行進(jìn)行排序3.2數(shù)據(jù)排序在工作表中，為了醒目顯示，一些數(shù)據(jù)可能設(shè)置了單元格顏色、字體顏色或單元格圖標(biāo)等格式，同樣我們也可以用這些格式來進(jìn)行排序。3.2.2按特殊格式排序在工作表中，為了醒目顯示，一些數(shù)據(jù)可能設(shè)置了單元格顏色、字體顏色或單元格圖標(biāo)等格式，同樣我們也可以用這些格式來進(jìn)行排

6、序。下面我們以“職工考勤表”為例，假如，表中“”事假（天數(shù)）大于或等天5天的已經(jīng)設(shè)置了“淺紅填充深紅文本”，要求按這種格式進(jìn)行排序，步驟如下：3.2數(shù)據(jù)排序3.2.3按自定義序列排序除上述排序外，用戶可以使用自定義列表的順序進(jìn)行排序。例如，“職稱”列可能包含要用來排序依據(jù)的值（如“高級(jí)”、“中級(jí)”和“初級(jí)”）。如何排序才能使包含“初級(jí)”的行先顯示，然后是“中級(jí)”，最后是“無”？如果按字母順序排序，“升序”排序會(huì)“初級(jí)、高級(jí)、無、中級(jí)”排列，顯然不符合要求。通過創(chuàng)建“自定義序列”，可以避免這一問題。3.3 篩選篩選就是根據(jù)用戶要求將滿足條件的數(shù)據(jù)記錄通過篩選過濾出來，提高工作效率。而且還不容易發(fā)

7、生遺漏，減少差錯(cuò)。篩選一般分為自動(dòng)篩選和高級(jí)篩選。3.3.1自動(dòng)篩選1.自動(dòng)篩選的類型2.以“職工考勤表”為例，“自動(dòng)篩選”的操作步驟3.3.2高級(jí)篩選如果篩選的數(shù)據(jù)需要復(fù)雜條件 (例如，類型 =“農(nóng)產(chǎn)品”O(jiān)R 銷售人員 =“李小明”)，可以使用“高級(jí)篩選”對(duì)話框。下面以“薪資明細(xì)表”為例，詳細(xì)說明高級(jí)篩選的操作及條件區(qū)域的定義規(guī)則。3.4分類匯總分類匯總是Excel的一項(xiàng)重要功能，它能快速以某個(gè)字段為分類項(xiàng)，對(duì)數(shù)據(jù)列表中其他字段的數(shù)值進(jìn)行統(tǒng)計(jì)計(jì)算。3.4.1單個(gè)字段分類匯總以“薪資管理表”為例介紹Excel表格中按“部門”進(jìn)行分類匯總的操作方法。3.4.1多個(gè)字段分類匯總Excel也可以對(duì)數(shù)

8、據(jù)進(jìn)行多字段的分類匯總。例如，我們可以按“部門”和“職稱”同時(shí)對(duì)實(shí)發(fā)工資求和，這要分兩步，首先按“部門”進(jìn)行分類匯總，在此基礎(chǔ)上再按“職稱”進(jìn)行分類匯總。3.5數(shù)據(jù)透視表和數(shù)據(jù)透視圖3.5.1數(shù)據(jù)透視表數(shù)據(jù)透視表是一種交互式的表，可以進(jìn)行求和、計(jì)數(shù)等一系列的計(jì)算，所進(jìn)行的計(jì)算與數(shù)據(jù)在數(shù)據(jù)透視表中的排列有關(guān)。數(shù)據(jù)透視表，可以動(dòng)態(tài)地改變它們的版面布置，以便按照不同方式分析數(shù)據(jù)，也可以重新安排行號(hào)、列標(biāo)和頁字段。每一次改變版面布置時(shí)，數(shù)據(jù)透視表會(huì)立即按照新的布置重新計(jì)算數(shù)據(jù)。另外，如果原始數(shù)據(jù)發(fā)生更改，則可以更新數(shù)據(jù)透視表。3.5數(shù)據(jù)透視表和數(shù)據(jù)透視圖3.5.2數(shù)據(jù)透視圖數(shù)據(jù)透視圖是與之相關(guān)聯(lián)的數(shù)據(jù)透視表數(shù)據(jù)的圖形表示形式，與數(shù)據(jù)透視表一樣，數(shù)據(jù)透視圖報(bào)告