1、第6章 工资表分析与统计第6章 工资表分析与统计6.1 任务任务书书6.2 任务示任务示范范6.3 知识拓展知识拓展第6章 工资表分析与统计6.1任务书任务书1.插入标题及新员工信息插入标题及新员工信息通过本任务熟练掌握信息的插入、删除和修改操作。2.公式计算公式计算通过本任务熟练掌握Excel公式函数的运用。3.设置有效性和条件格式设置有效性和条件格式通过本任务熟练掌握Excel有效性设置及条件格式的运用。第6章 工资表分析与统计4.统计相关信息统计相关信息通过本任务熟练掌握Excel数据统计函数。5.排序、分类汇总排序、分类汇总通过本任务熟练掌握Excel对数据的排序和分类汇总方法技巧。6
2、.筛选筛选通过本任务熟练掌握Excel筛选的方法和技巧。第6章 工资表分析与统计6.2任任 务务 示示 范范 1.插入标题及新员工信息插入标题及新员工信息【任务实施】【任务实施】(1)打开“工资表”，插入标题。操作步骤操作步骤1：双击文件“工资表.xlsx”，打开Sheet1工作表。操作步骤操作步骤2：选中第1行行标，右键选择插入，在顶部插入一行。操作步骤操作步骤3：选中A1单元格，输入“现代公司10月工资表”，设置字体为“华文彩云”，字号为“20”。第6章 工资表分析与统计(2)插入某个新人信息，在32行前插入一行“姓名：林淡泊，身份证 330103197803120037，1999/8/1
3、0，上海，财会部，科级，奖金800”。工号自动填充，从0001开始到0066结束。操作步骤：操作步骤：选择3行行标，单击右键选择插入。插入一空行，从姓名开始输入，身份证号需在前加单引号，日期格式需要输入日期分隔符“-”或“/”，每个信息输入完按Tab键，光标会向右移动。输入完成，对工号进行自动填充，工号要转变为文本格式，在数字0001前加“”号，或者设置工号列为文本格式。第6章 工资表分析与统计 2.公式计算公式计算【任务实施】【任务实施】(1)根据身份证号计算出生年月日。操作步骤：操作步骤：在D列输入公式“=MID(C3,7,4)&年&MID(C3,11,2)&月&MID(C3,13,2)&
4、日”，向下自动填充。MID函数提取身份证当中的年月日信息，通过&连接符进行连接，最后拼接成某人的出生年月信息。第6章 工资表分析与统计(2)根据工作日期，推算工龄。操作步骤：操作步骤：在F列输入公式“=DATEDIF(E3,TODAY(),Y)”，向下填充。DATEDIF函数可以求得两个日期之间的差值，结果可以是返回年数、月数和日数。计算出的结果要以常规格式显示。第6章 工资表分析与统计(3)根据职务等级计算出基本工资和岗位工资。基本工资和岗位工资都是根据职务等级规定好的，以等级工资表信息为依据进行查找。操作步骤操作步骤1：定义参考表为“等级工资表”，在基本工资列输入公式“=VLOOKUP(I
5、3，等级工资表，2，0)”，参数1是对应的等级，参数2是参考表，参数3返回信息是参考表第几列，参数4、0代表精确匹配。如果需要模糊匹配可以设置为1。操作步骤操作步骤2：岗位工资的计算类似，同样使用函数“=VLOOKUP(I3，等级工资表，3，0)，”计算出岗位工资。第6章 工资表分析与统计(4)计算三金。住房公积金=基本工资0.01；养老金=基本工资0.02；失业保险金=基本工资0.014。操作步骤操作步骤1：住房公积金列输入公式“=工资表!J3*0.01”。操作步骤操作步骤2：养老金列输入公式“=工资表!J3*0.02”。操作步骤操作步骤3：失业保险金列输入公式“=工资表!J3*0.014”
6、。第6章 工资表分析与统计 5)计算水费和电费。引用水度数和电度数表中的数据，每度电1.5元，每度水2元。操作步骤操作步骤1：在电费这列输入公式“=10月电度数!B2*1.5”，向下填充。操作步骤操作步骤2：在水费这列输入公式“=10月水度数!B2*2”，向下填充。(6)计算应税金额。应税金额为实际收入超过3000部分。操作步骤：操作步骤：如果应税金额小于0，则显示为0，采用IF函数，在应税金额列输入公式“=IF(SUM(K3:M3)-SUM(N3:Q3)-30000，SUM(K3:M3)-SUM(N3:Q3)-3000，0)”，向下填充。第6章 工资表分析与统计(7)计算个人所得税。操作步骤
7、：操作步骤：根据个人所得税表(如表6-1所示)，计算个人所得税。第6章 工资表分析与统计(8)计算扣款合计为三金、水电费和个人所得税。扣款合计单元格输入公式“=SUM(M3:Q3，S3)”，向下自动填充。(9)计算实发金额。实发金额为基本工资+岗位工资+奖金-扣款合计。在实发金额单元格输入公式“=SUM(J3:L3)-T3”，向下自动填充。第6章 工资表分析与统计 3.设置有效性和条件格式设置有效性和条件格式【任务实施】【任务实施】(1)设置数据有效性。要求设置基本工资，最低1500，最高4500。当输入数值超过基本工资时，弹出警告提示“基本工资必须在1500-4500范围内，请重填”。操作步
8、骤操作步骤1：选中基本工资列数据部分，单击“数据”选项卡中的“有效性”按钮，弹出有效性设置对话框，设置允许整数，介于最小值1500和最大值4500之间，如图6-1所示。第6章 工资表分析与统计图6-1有效性设置对话框第6章 工资表分析与统计操作步骤操作步骤2：设置出错警告。选择“停止”样式，输入标题为“超出范围”，输入错误信息为“基本工资必须在1500-4500范围内，请重填”，如图6-2所示。图6-2出错警告设置第6章 工资表分析与统计(2)设置条件格式。要求奖金大于等于2000的设置字体为“红色”“加粗”；奖金小于800的填充为“蓝色”。操作步骤：操作步骤：选中奖金列的数据部分，单击“开始
9、”选项卡中的“条件格式”按钮，在突出显示的下级菜单中选择“其他规则”，设置单元格大于或等于2000，相应的格式为字体“红色”“加粗”，分别如图6-3和图6-4所示。第6章 工资表分析与统计图6-3选择“其他规则”第6章 工资表分析与统计图6-4新建格式规则1第6章 工资表分析与统计用同样的方法，再设置一个奖金小于800的填充为蓝色，分别如图6-5和图6-6所示。图6-5新建格式规则2第6章 工资表分析与统计图6-6条件格式规则管理器第6章 工资表分析与统计4.统计相关信息统计相关信息【任务实施】【任务实施】(1)统计各职务等级的人数。选中整张工资表，在“公式”选项卡的“定义名称”命令组中单击根
10、据所选内容创建相应的名称，如图6-7所示。选择“首行”，将每列都定义为列标题名称。操作步骤：操作步骤：在“统计”工作表中使用COUNTIF函数统计。在“人数”列的B3单元格中输入公式“=COUNTIF(职务等级，A3)”，向下填充。第6章 工资表分析与统计图6-7以选定区域创建名称 第6章 工资表分析与统计(2)统计各职务等级的实发金额合计。操作步骤：操作步骤：使用SUMIF函数，统计符合特定条件的单元格的和，如图6-8所示。图6-8 SUMIF函数第6章 工资表分析与统计(3)查找实发工资最高的员工姓名。操作步骤：操作步骤：先求出最高实发金额数。在“统计”表的A11单元格中输入公式“=MAX
11、(实发)”，这个数必须是唯一的，然后使用DGET函数查找符合条件的员工姓名。在姓名列下方单元格中输入公式“=DGET(工资表!A2:U68，2，统计!A10:A11)”，如图6-9所示。第6章 工资表分析与统计图6-9DGET函数第6章 工资表分析与统计(4)统计销售部科级的员工人数，结果写在A14单元格中。操作步骤：操作步骤：这里涉及多个条件的计数，可使用COUNTIFS函数来设置多个条件。在A14单元格中输入公式“=COUNTIFS(部门，销售部，职务等级，科级)”，如图6-10所示。第6章 工资表分析与统计图6-10COUNTIFS函数对话框第6章 工资表分析与统计(5)统计工龄在354
12、0之间的办事员人数。操作步骤：操作步骤：这里，统计人数要涉及3个条件：工龄在某个区间(工龄35和工龄40)、职务等级又是办事员，仍然使用COUNTIFS函数，如图6-11所示。在A17单元格中输入公式“=COUNTIFS(工龄，=35，工龄，=40，职务等级，办事员)”。第6章 工资表分析与统计图6-11COUNTIFS函数对话框第6章 工资表分析与统计(6)统计办事员平均年龄。操作步骤：操作步骤：统计平均年龄，又有特定条件，可采用数据库函数DAVERAGE来实现。在C19单元格中输入“职务等级”，在C20单元格中输入“办事员”，在A21单元格中输入公式“=DAVERAGE(工资表!A2:U6
13、8，6，C20:C21)”，如图6-12所示。第6章 工资表分析与统计图6-12DAVERAGE函数对话框第6章 工资表分析与统计5.排序、分类汇总排序、分类汇总【任务实施】【任务实施】(1)对工资表排序。操作步骤：操作步骤：新建“排序”工作表，复制工资表到“排序”工作表中，根据部门进行升序排序，部门相同的按照职务等级降序排序(厅级、处级、科级、办事员)。第6章 工资表分析与统计此处需要设置两个条件：条件1为主要关键字选择“部门”，升序；条件2为次要关键字选择“职务等级”，设置次序为自定义序列(按照要求的顺序排序，在自定义序列中添加相应文字，每个级别用回车隔开)，如图6-13所示。自定义排序设
14、置如图6-14所示。第6章 工资表分析与统计图6-13自定义序列第6章 工资表分析与统计图6-14自定义排序设置第6章 工资表分析与统计(2)分类汇总不同部门的员工数和平均奖金。操作步骤操作步骤1：新建“分类汇总”工作表，将“排序”表中的内容复制到“分类汇总”表中，对部门进行排序；然后选择“数据”选项卡的“分类汇总”，进行不同部门员工数的统计。在“分类汇总”对话框中，设置分类字段为“部门”，汇总方式为“计数”，选定汇总项为“部门”，如图6-15所示。第6章 工资表分析与统计 图6-15分类汇总员工数第6章 工资表分析与统计操作步骤操作步骤2：再次单击“分类汇总”，进行不同部门平均奖金的统计。在
15、“分类汇总”对话框中设置分类字段为“部门”，选定汇总方式为“平均值”，选定汇总项为“奖金”，取消“替换当前分类汇总”，如图6-16所示。第6章 工资表分析与统计图6-16分类汇总平均奖金第6章 工资表分析与统计最终分级显示效果如图6-17所示。统计的员工一共66人，总计数求和使用SUBTOTAL函数，要移动标题位置。图6-17分级显示效果第6章 工资表分析与统计6.筛选筛选【任务实施】【任务实施】(1)筛选出工龄大于40的办事员。操作步骤：操作步骤：使用自动筛选，选中整张表，在“数据”选项卡中单击“筛选”按钮，此时标题栏右侧会出现下拉框，用于选择筛选条件。此处需要筛选出工龄大于40，选择数字筛
16、选下级菜单中的“大于”，在弹出的对话框中进行设置，如图6-18所示。第6章 工资表分析与统计图6-18自定义筛选第6章 工资表分析与统计(2)筛选出姓李的员工。操作步骤：操作步骤：要筛选出姓李的，可在自动筛选姓名下拉框中选择文本筛选“开头是”，然后在对话框中输入“李”，如图6-19所示。图6-19姓“李”的筛选第6章 工资表分析与统计(3)筛选出奖金大于等于1500的销售人员或奖金大于等于4000的厅级人员。操作步骤：操作步骤：设置筛选条件如表6-2所示。第6章 工资表分析与统计进行高级筛选，需将结果复制到表下。高级筛选设置如图6-20所示。图6-20高级筛选设置第6章 工资表分析与统计(4)
17、筛选出工作日期是1988年的员工信息。操作步骤：操作步骤：设置日期的筛选条件，设置工作日期在1988-1-1之后或与之相同，并且到1988-12-31之前或与之相同。设置如图6-21所示，即可筛选出1988年参加工作的员工信息。图6-21筛选工作日期是1988年的员工第6章 工资表分析与统计(5)筛选出出生年月是5月的员工的信息。操作步骤：操作步骤：这里要使用通配符筛选，*代表任意多个字符，？代表单个字符。将筛选条件设置为文本条件的自定义，然后如图6-22所示进行设置，即可筛选出5月出生的员工信息。图6-22筛选5月出生的员工第6章 工资表分析与统计6.3知知 识识 拓拓 展展1.会计科目拆分
18、会计科目拆分会计工作中我们经常会用到会计科目，其中科目编码和科目名称是连在一起的。为了方便对会计科目的分类管理，有时需要将两者分开，把科目编码和科目名称单独放在一列中。通过本任务，将科目编码和科目名称拆开，并熟练掌握Excel中函数LEFT、RIGHT、LEN、LENB、MID的应用。最终效果如图6-23所示。第6章 工资表分析与统计图6-23拆分会计科目第6章 工资表分析与统计2.利用函数或者数组公式统计利用函数或者数组公式统计会计工作中，我们经常会有一张巨大的、根据科目编制的财务报表。现在要计算净利润，净利润等于将所有科目编码等于4的科目项求和，最终效果如图6-24所示。这里需要找到科目编码长度为4的项目，并对它们求和。如果用累加这样的公式，一旦删掉了某个科目，公式就会出错。为了解决这个问题，有两种方法：一是采用数组公式，二是采用SUMPRODUCT函数。第6章 工资表分析与统计图6-24净利润统计
