EXECL會計與財務(wù)管理-情境-3

學(xué)習(xí)情境三EXCEL函數(shù)與薪酬管理實務(wù)2情境三學(xué)習(xí)目標(biāo)【知識目標(biāo)】理解Excel函數(shù)類型與作用、函數(shù)構(gòu)成；理解Excel引用符號的含義；理解Excel函數(shù)在銷售分析、個人所得稅計算、職工薪酬計算與統(tǒng)計、會計分錄統(tǒng)計中的應(yīng)用。

【技能目標(biāo)】

掌握修改系統(tǒng)日期、顯示Excel公式的方法；掌握嵌套函數(shù)、單元格式設(shè)置與格式復(fù)制、工作表的設(shè)置與打印技術(shù)；掌握以下Excel函數(shù)的使用：自動求和SUM函數(shù)、算術(shù)平均數(shù)Average函數(shù)、計數(shù)Count函數(shù)、最大值Max函數(shù)、最小值Min函數(shù)、排位Rank函數(shù)、年Year函數(shù)、月Month函數(shù)、日Day函數(shù)、現(xiàn)在Now函數(shù)、今天Today函數(shù)、小時Hour函數(shù)、四舍五入Round函數(shù)、取整Int函數(shù)、截尾取整Trunc函數(shù)、條件IF函數(shù)、且And函數(shù)、條件計數(shù)Countif函數(shù)、條件求和Sumif函數(shù)、條件平均數(shù)Averageif函數(shù)等。學(xué)習(xí)任務(wù)1使用EXCEL函數(shù)【技能與理論精析】1.Excel引用符號2.Excel函數(shù)類型與作用Excel提供了財務(wù)函數(shù)、日期與時間函數(shù)、數(shù)學(xué)與三角函數(shù)、統(tǒng)計函數(shù)、查找與引用函數(shù)、數(shù)據(jù)庫函數(shù)、文本函數(shù)、邏輯函數(shù)、信息函數(shù)、工程函數(shù)等十余大類，幾百個函數(shù)。使用這些函數(shù)不僅可以完成許多復(fù)雜的計算，而且還可以簡化公式的繁雜程度。3【技能與理論精析】3.Excel函數(shù)構(gòu)成Excel函數(shù)由等號、函數(shù)名、小括號及參數(shù)構(gòu)成，函數(shù)名說明函數(shù)所要進行的運算類別，參數(shù)用于指定函數(shù)使用的數(shù)值或單元格引用。函數(shù)公式為：=函數(shù)名(參數(shù)1,參數(shù)2,參數(shù)3,…)4.自動求和SUM函數(shù)Excel自動求和函數(shù)的公式構(gòu)成為：=SUM(Number1,Number2,…)其中，Number1,Number2,……為需要求和的參數(shù)（求和的總體）。Excel2003為1-30個參數(shù)，Excel2007-Excel2016為1-255個參數(shù)。4【工作任務(wù)3-1】某公司生產(chǎn)經(jīng)營4-6種產(chǎn)品，2015年各季度銷售收入如表3-2所示。要求用公式與函數(shù)計算各季度、各產(chǎn)品的合計收入。5【工作成果】Excel中用函數(shù)、公式完成工作任務(wù)后，如圖所示?！竟ぷ髁鞒獭浚?）設(shè)計表格.（2）計算合計數(shù)，使用工具按鈕求和。（3）計算合計數(shù)，可使用函數(shù)向?qū)Хā＃?）直接鍵入函數(shù)。7學(xué)習(xí)任務(wù)2常用EXCEL統(tǒng)計函數(shù)【技能與理論精析】1.算術(shù)平均數(shù)Average函數(shù)公式為：=Average(Number1,Number2,…)其中，Number1,Number2,…為需要求平均數(shù)的參數(shù)（統(tǒng)計總體）。Excel2003為1-30個參數(shù)，Excel2007-Excel2016為1-255個參數(shù)。2.計數(shù)Count函數(shù)計數(shù)Count函數(shù)用于計算包含數(shù)字的單元格或數(shù)據(jù)區(qū)域中數(shù)字的個數(shù)（只有數(shù)字類型的數(shù)據(jù)才被計算）。函數(shù)公式為：=Count(Value1,Value2,…)式中：Value1,Value2,…表示包含或引用各種類型數(shù)據(jù)的參數(shù)（統(tǒng)計總體）。Excel2003為1-30個參數(shù)，Excel2007-Excel2016為1-255個參數(shù)。83.最大值Max函數(shù)最大值Max函數(shù)的功能是返回一組數(shù)值中的最大值。函數(shù)公式為：=Max(Number1,Number2,…)其中，Number1,Number2,…表示要從中找出最大值的數(shù)字參數(shù)（統(tǒng)計總體）。Excel2003為1-30個參數(shù)，Excel2007-Excel2016為1-255個參數(shù)。4.最小值Min函數(shù)最小值Min函數(shù)的功能是返回一組數(shù)值中的最小值。函數(shù)公式為：=Min(Number1,Number2,…)其中，Number1,Number2,…表示要從中找出最小值的數(shù)字參數(shù)（統(tǒng)計總體）。Excel2003為1-30個參數(shù)，Excel2007-Excel2016為1-255個參數(shù)。95.排位Rank函數(shù)排位Rank函數(shù)的功能是確定一個數(shù)值在一組數(shù)值中的位次（名次）。數(shù)值的排位是此值與指定區(qū)域中其他數(shù)值的相對大小。其函數(shù)公式為：=Rank(Number,Ref,Order)式中：Number表示需要排位的數(shù)字；Ref表示包含一組數(shù)字的數(shù)組或引用，即需排位的全部總體，非數(shù)值型參數(shù)將被忽略；Order表示排位方式，如果Order為0或省略則按降序排列，否則按升序排列。10【工作任務(wù)3-2】承前表，計算各季度的品種數(shù)、平均銷售額、各季最大銷售額、最小銷售額等?！竟ぷ鞒晒縀xcel中用函數(shù)完成銷售分析后，工作成果如圖所示。11【工作流程】（1）計算平均數(shù)。（2）計算各季最大銷售額。（3）計算最小銷售額公式：K3=Min(B3:G3)，自動填充K4至K6公式。（4）計算各季經(jīng)營品種。（5）排位計算。12學(xué)習(xí)任務(wù)3 Excel日期與時間函數(shù)【技能與理論精析】1.日期函數(shù)概述Excel提供的日期函數(shù)很多。如年月日函數(shù)、星期函數(shù)、當(dāng)前日期函數(shù)、特定日期函數(shù)等。2.年Year函數(shù)年Year函數(shù)是取函數(shù)參數(shù)中的年份值，取值范圍為1900-9999之間的數(shù)字。函數(shù)公式為：=Year(Serial_number)式中：參數(shù)Serial_number為一個日期值（包含年份、月份、天數(shù)）。13【技能與理論精析】3.月Month函數(shù)月Month函數(shù)是取函數(shù)參數(shù)中的月份值，取值范圍為1-12之間的數(shù)字。函數(shù)公式為：=Month(Serial_number)4.日Day函數(shù)日Day函數(shù)是取函數(shù)參數(shù)中的天數(shù)值，取值范圍為1-31之間的數(shù)字。函數(shù)公式為：=Day(Serial_number)5.現(xiàn)在Now函數(shù)現(xiàn)在Now函數(shù)用于返回當(dāng)前的日期和時間。該函數(shù)沒有參數(shù)，公式如下：=Now()6.今天Today函數(shù)今天Today函數(shù)用于返回當(dāng)前日期。該函數(shù)沒有參數(shù)，公式如下：=Today()7.小時Hour函數(shù)小時Hour函數(shù)用于返回時間值的小時數(shù)。函數(shù)公式為：=Hour(Serial_number)1415【工作任務(wù)3-3】我國會計從業(yè)資格考證的《考綱》中規(guī)定，會計人員必須掌握Windows操作系統(tǒng)日期的修改方法，單元格日期格式的設(shè)置方法；掌握現(xiàn)在Now函數(shù)、今天Today函數(shù)、年Year函數(shù)、月Month函數(shù)、日Day函數(shù)等，以及日期的加減運算。在Windows操作系統(tǒng)中，修改系統(tǒng)日期為2016年4月18日。然后在Excel中分別使用日期、時間函數(shù)進行顯示與運算。【工作成果】Excel中完成工作任務(wù)后，工作成果如圖所示。16【工作過程】（1）修改系統(tǒng)日期。（2）在A1至C1單元格中分別錄入“2009-2-7、=Today()、=Now()”。（3）在D1至D4單元區(qū)域中，分別錄入日期或時間函數(shù)公式“=Year(A1)、=Month(A1)、=Day(A1)、=Hour(A1)”。然后自動填充E1至F4單元格區(qū)域公式。（4）在A2、A3兩個單元格均錄入公式“=A1”。再自動填充B2至C3單元格公式。（5）在A4至C4單元格中分別錄入“=Year(B1)-Year(A1)、=Month(B1)-Month(A1)、=Day(B1)-Day(A1)”。（6）右擊C1單元格選擇“設(shè)置單元格格式”命令，進入“設(shè)置單元格格式”對話框。在“數(shù)字”卡片左部選擇“自定義”分類，在右部類型編輯框中鍵入“yyyy-mm-ddhh:mm:ss”。（7）選定A2至C2單元區(qū)域，右擊并進入“設(shè)置單元格格式”對話框，在“數(shù)字”卡左部選擇“日期”分類，選擇右部的“年月日”類型，單擊“確定”按鈕。（8）選定A3至C4單元區(qū)域，右擊并進入“設(shè)置單元格格式”對話框，在“數(shù)字”卡左部選擇“常規(guī)”分類，單擊“確定”按鈕。【思考與討論】（1）A3至C3單元區(qū)域顯示的數(shù)字是什么含義？如何將其修改為A1單元格的格式？（2）A4至C4、D1至F4單元區(qū)域顯示的數(shù)字是什么含義？學(xué)習(xí)任務(wù)4 舍入函數(shù)與顯示公式【技能與理論精析】1.四舍五入Round函數(shù)四舍五入Round函數(shù)是對某個數(shù)字按指定位數(shù)進行四舍五入取數(shù)。函數(shù)公式為：=Round(Number,Num_digits)2.取整Int函數(shù)取整Int函數(shù)是將數(shù)字向下舍入到最接近的整數(shù)。函數(shù)公式為：=Int(Number)3.截尾取整Trunc函數(shù)截尾取整Trunc函數(shù)是將數(shù)字的小數(shù)部分截去，返回整數(shù)。函數(shù)公式為：=Trunc(Number,Num_digits)19【技能與理論精析】4.顯示公式Excel單元格中，顯示的值與輸入（保存）的值，很多時候都是有區(qū)別的。一般而言，在Excel的單元格中顯示的是運算結(jié)果，保存的值是輸入該單元格的值。在單元格被引用時，參與運算的是保存值而不是顯示值。查看單元格中輸入（保存）值的方法主要有：（1）選定該單元格后從編輯框中查看。（2）雙擊該單元格后從該單元格中查看。（3）發(fā)出“顯示公式”命令后從單元格中查看。20【工作任務(wù)3-4】在Excel中，進行以下四舍五入Round、取整Int、截尾取整Trunc函數(shù)的運算，并在單元格中顯示公式。【工作成果】Excel中完成舍入函數(shù)運算的工作任務(wù)后，如圖所示。21【工作流程】（1）四舍五入Round函數(shù)。在A1至C1單元格分別錄入公式“=Round(8049.454545,3)，=Round(8049.454545,0)，=Round(8049.454545,-1)”。（2）取整Int函數(shù)。在A2至C2單元格分別錄入公式“=Int(56.983)，=Int(-56.823)，=Int(-56.083)”。（3）截尾取整Trunc函數(shù)。在A3至C3單元格中分別錄入公式“=Trunc(36.98)，=Trunc(-36.22)，=Trunc(-36.72)”。（4）在A4至C4單元格分別錄入分?jǐn)?shù)或公式“02/3，02/3，=ROUND(2/3,2)”。再選定B4單元格，單擊千位分隔符按鈕（Excel2003在格式工具欄，Excel2007-Excel2016在“開始/數(shù)字”組）。可見：B4、C4單元格顯示的都是0.67。（5）在A5至C5單元格分別錄入公式“=A4*200，=B4*200，=C4*200”。（6）顯示公式。22學(xué)習(xí)任務(wù)5 個人所得稅計算與條件函數(shù)【技能與理論精析】1.條件IF函數(shù)條件IF函數(shù)也稱如果函數(shù)，它的功能是判斷一個條件是否滿足，如果滿足返回一個值，如果不滿足則返回另一個值。函數(shù)公式為：=IF(Logical_test,Value_if_true,Value_if_false)式中：Logical_test表示判斷的條件或表達式；Value_if_true表示為True（真）時返回的值；Value_if_false表示為False（假）時返回的值。2.IF的嵌套函數(shù)嵌套函數(shù)是指使用一個函數(shù)作為另一個函數(shù)的參數(shù)。IF可多次判斷，Excel2003最多嵌套7層，Excel2007-Excel2016可嵌套64層。23【工作任務(wù)3-5】渝森公司的薪資政策及2016年1月部分職工的薪資情況如表3-3所示。要求計算這些職工的應(yīng)交個人所得稅與實發(fā)薪資。24【個稅計算解析】我國2011年9月起執(zhí)行的個人所得稅法規(guī)定，工資與薪金所得的扣稅基數(shù)為3500元，附加費為1400元（外國人），個稅適用稅率如表所示。計算公式是：應(yīng)納個人所得稅稅額=計稅薪資額×適用稅率-速算扣除數(shù)25【工作成果】Excel中完成渝森公司薪資計算表，如圖所示。26【工作流程】（1）輸入已知數(shù)據(jù)。（2）計算工齡工資。（3）計算五險一金。（4）計算計稅工資。（5）計算個人所得稅。（6）計算實發(fā)薪資。（7）在編輯框全部顯示公式。27學(xué)習(xí)任務(wù)6 職工薪酬Excel計算【技能與理論精析】1.且And函數(shù)且And函數(shù)也稱與函數(shù)，是在判斷時：當(dāng)所有參數(shù)的邏輯值為真時，返回True；只要有一個參數(shù)的邏輯值為假，即返回False。函數(shù)公式為：=And(Logical1,Logical2,…)2.嵌套函數(shù)嵌套函數(shù)是指使用一個函數(shù)作為另一個函數(shù)的參數(shù)，如將求和函數(shù)、平均數(shù)函數(shù)、且函數(shù)等作為條件IF函數(shù)的參數(shù)等，以進行較為復(fù)雜的數(shù)據(jù)運算。28【工作任務(wù)3-6】Excel中進行如果IF函數(shù)、且And函數(shù)與嵌套函數(shù)的運算?！竟ぷ鞒晒抗ぷ魅蝿?wù)完成后的工作成果，如圖所示。29【工作流程】（1）在A7單元格錄入“8”，在B7單元格錄入“25”。（2）在A8單元格錄入“經(jīng)理”，在B8單元格錄入“90”。（3）將條件IF函數(shù)與且And函數(shù)進行嵌套運算，可實現(xiàn)多種運算功能。（4）在A10至C10單元格分別錄入“49、76、92”。30【工作任務(wù)3-7】宏興公司的薪資政策、部分職員的基本信息如表所示。31【工作成果】Excel中完成工作任務(wù)后，將顯示如圖所示的宏興公司職工月薪資計算表。32【工作流程】（1）新建工作表，將其表標(biāo)簽名稱修改為“宏興薪資”。（2）錄入編號。（3）錄入其他已知數(shù)據(jù)。（4）計算職務(wù)工資。（5）計算崗位工資。（6）計算應(yīng)發(fā)薪資與保險。（7）計算計稅薪資。（8）計算個稅。（9）計算實發(fā)薪資。（10）自動填充公式。（11）顯示兩位小數(shù)并千位分隔。33學(xué)習(xí)任務(wù)7 職工薪酬Excel條件統(tǒng)計【技能與理論精析】1.條件計數(shù)Countif函數(shù)條件計數(shù)Countif函數(shù)的功能是計算某個區(qū)域中滿足給定條件的單元格數(shù)目。函數(shù)公式為：=Countif(Range,Criteria)式中：Range表示統(tǒng)計的總體或單元格區(qū)域，Criteria表示確定哪些單元格將被計算在內(nèi)的條件，其形式可以為數(shù)字、表達式或文本。2.條件求和Sumif函數(shù)條件求和Sumif函數(shù)的功能是對滿足條件的單元格求和。函數(shù)公式為：=Sumif(Range,Criteria,Sum_range)式中：Range表示用于條件判斷的單元格區(qū)域（查找總體）；Criteria表示相加求和的條件，其形式可以為數(shù)字、表達式或文本；Sum_range表示需要求和的實際單元格。34【技能與理論精析】3.條件平均數(shù)Averageif函數(shù)條件平均數(shù)Averageif函數(shù)用于運算某個區(qū)域內(nèi)滿足給定條件的所有單元格的平均值（算術(shù)平均值）。其函數(shù)公式為（注意：Excel2003無法使用此函數(shù)）：=Averageif(Range,Criteria,Average_range)式中：Range為統(tǒng)計總體，即計算平均值的單元格或單元格區(qū)域，它可以是數(shù)字或包含數(shù)字的名稱、數(shù)組或引用；Criteria用于定義統(tǒng)計條件，要對哪些單元格計算平均值，它可以是數(shù)字、表達式、單元格引用或文本形式的條件；Average_range為要計算平均值的實際單元格集，若忽略，則使用Range。35【工作任務(wù)3-8】用函數(shù)統(tǒng)計宏興公司銷售部人數(shù)、職員人數(shù)、扣個稅10元以上人數(shù)、經(jīng)理實發(fā)薪資合計、經(jīng)理人均實發(fā)薪資、職員人均實發(fā)薪資?！竟ぷ鞒晒縀xcel中用Countif、Sumif、Averageif函數(shù)，完成任務(wù)后的工作成果如圖所示。36【工作過程】（1）統(tǒng)計銷售部人數(shù)。（2）其他人數(shù)統(tǒng)計公式：P2=Countif(D2:D13,"職員")，Q2=Countif(L2:L13,">10")。（3）統(tǒng)計經(jīng)理人均實發(fā)薪資。（4）職員人均實發(fā)薪資公式：T2=Averageif(D2: