搜索
12综合案例3Excel综合案例职工信息表案例简介Excel的基本操作设置Excel函数基础应用Excel数据图表应用实例思考题职工信息表案例简介简介利用EXCEL2010软件来建立一个企业职工信息表，进行EXCEL高级操作练习，对信息进行有效处理，让工作达到事半功倍的效果。3职工信息表案例简介总体分析企业每月工资的发放是再正常不过的一件事情，那么在每月工资发放前夕，财务人员必须要完成对本月工资的核算。工资的最终结算金额来自多项数据，如员工的基本工资、涨工资额、各项补贴、个人所得税等等。如果用手工方式计算这些数据。工作效率低下，并容易出错。有了EXCEL软件，就可以在学习EXCEL中创建表格并利用函数计算来管理这些数据，从而达到一些自动化管理的功能。4职工信息表案例简介设计规划通过具体实例，我们要学习和掌握EXCEL2010软件如下部分EXCEL的基本操作设置Excel函数的运用Excel数据库管理和数据进行分析Excel综合运用5职工信息表案例简介解决方案制作本案例时，首先需要搜索信息建立职工信息表，并进行单元格格式设置，除了用公式统计每位员工的工资额外，还要进行排序、高级筛选、制作图表和数据透视表等操作。此外，因为企业实际业务的需要，还要把工资表制作成工资条发给每位员工，我们利用EXCEL2010的高级操作完成这个功能，制作出来的工资条既美观又简便。67综合案例3Excel综合案例职工信息表案例简介Excel的基本操作设置Excel函数基础应用Excel数据图表应用实例思考题职工信息表Excel的基本操作设置建立工作表并重命名用户可以将使用的工作表根据工作表的具体内容进行重命名，方便以后查找工作表。可以使用下了方式进行操作。右击要更改名称的工作表标签，然后单击快捷菜单的“重命名”命令，这时工作表的名称高亮显示，然后输入新的工作表名称，按Enter键，即可完成工作表重命名操作。工作表标签颜色更改为了便于查找显示不同的工作表，可以改变工作表标签的颜色，也可以将有重点内容的工作表做特殊标记。右击工作表标签，然后单击快捷菜单中的“工作表标签颜色”项，即可更改所需的标签颜色。8职工信息表Excel的基本操作设置9如图，为“职工信息表”创建蓝色标签职工信息表Excel的基本操作设置工作表标题操作实例：给工作表插入标题“职工信息”,合并居中,字体为黑色24号楷体。选中第1行右键点击插入，并合并A1:AD1单元格，输入文字：职工信息，设置字体为黑色24号楷体。10职工信息表Excel的基本操作设置冻结窗口操作窗格是文档窗口的一部分，以垂直或水平条为界限，并由此与其他部分隔开。可以通过冻结窗格来查看工作表的两个区域和锁定一个区域中的行或者列。实例：冻结列标题和姓名列先切换到“视图”选项卡，然后选中C3单元格，点击“冻结窗格”，冻结拆分窗格。11需选择正确的单元格再进行冻结窗口操作职工信息表Excel的基本操作设置单元格格式操作右击选中的单元格，选择“设置单元格格式”，在对话框中选择不同要求的单元格格式。包括“数字”、“对齐”、“字体”、“边框”、“填充”、“保护”选项栏。12职工信息表工作薄与工作表基础应用条件格式的应用通过应用不同的颜色刻度、数据条或图表集样式，可以更直观显示工作表中的数据。实例：对基本工资数据区域使用条件格式为蓝色数据条。选中“样式”选项卡，选择“条件格式”，采用“数据条”类型，选中使用“蓝色数据条”13如图，对基本工资使用到了蓝色数据条职工信息表工作薄与工作表基础应用条件格式的应用实例：并将职工信息表的自动套用格式设置为“浅色16”。先将职工信息表全选后，选中“开始”选项卡，选择“套用表格格式”，采用“浅色”中“表格样式浅色16”类型。14选择浅色16样式15综合案例3Excel综合案例职工信息表案例简介Excel基本操作设置Excel函数基础应用Excel数据图表应用实例思考题职工信息表Excel函数应用基础日期与时间函数应用对工作表中的包含的日期与时间信息按规定进行处理的一类函数。日期与时间函数主要应用于两个时间点之间的天数、制定月份的最后一天、将时间与日期转换成序列号、返回指定时间、计算周次等。日期函数包括DATE函数、DAY函数、YEAR函数等，时间函数通常包括HOUR函数、MINUTE函数、SECOND函数等。实例：计算每位职工的工龄函数思路：工龄=当前的年份数-参加工作时的年份数当前年份=YEAR(TODAY())语法：YEAR(serial_number)serial_number为一个日期值16当前时间点可以用TODAY函数获取职工信息表Excel函数应用基础选中J3单元格，在编辑栏中输入“=YEAR(TODAY())-YEAR(I3)”如图所示：计算工龄17注意单元格格式应为数字格式职工信息表Excel函数应用基础数组频率统计函数FREQUENCY函数的应用计算数值在某个区域内的出现频率，然后返回一个垂直数组。例如，使用函数FREQUENCY可以在分数区域内计算测验分数的个数。由于函数FREQUENCY返回一个数组，所以它必须以数组公式的形式输入。语法：FREQUENCY(data_array,bins_array)Data_array必需。一个值数组或对一组数值的引用，您要为它计算频率。Bins_array必需。一个区间数组或对区间的引用，该区间用于对data_array中的数值进行分组。18职工信息表Excel函数应用基础实例：用FREQUENCY函数计算工龄在5年以内,6-10年,11-20年,20年以上的员工人数，填入AF3：AF6单元格。选中AF3：AF6单元格，输入“=FREQUENCY(J3:J67,AG3:AG5)”，在编辑栏状态下，按下Ctrl+Shift+Enter三键，完成数组公式，如图所示。19右侧数据为区间分割点职工信息表Excel函数应用基础逻辑函数中IF函数的综合应用如果指定条件的计算结果为TRUE，IF函数将返回某个值；如果该条件的计算结果为FALSE，则返回另一个值。语法：IF(logical_test,[value_if_true],[value_if_false])logical_test必需。计算结果可能为TRUE或FALSE的任意值或表达式。value_if_true可选。logical_test参数的计算结果为TRUE时所要返回的值。value_if_false可选。logical_test参数的计算结果为FALSE时所要返回的值。20职工信息表Excel函数应用基础实例：计算涨工资额(助教涨幅为5%,讲师涨幅为8%,高工涨幅为15%,副教授\工程师涨幅为18%,主治医师\教授涨幅为25%,其余职称涨幅为2%)选中R3单元格，编辑栏中输入“=IF(N3=助教,P3*5%,IF(N3=讲师,P3*8%,IF(N3=高工,P3*15%,IF(OR(N3=副教授,N3=工程师),P3*18%,IF(OR(N3=主治医师,N3=教授),P3*25%,P3*2%)))))”如图所示21使用逻辑函数IF来完成多层嵌套使用，同时也应用到了OR函数进行选择匹配条件选项。职工信息表Excel函数应用基础实例:少数民族额外补贴基本工资的5%,填入S列，博士额外补贴基本工资的5%,填入T列。选择S3单元格，输入函数“=IF(K3汉,P3*5%,0)”，填充本列数据单元格。选择T3单元格，输入函数“=IF(M3=博士,P3*5%,0)”，填充本列数据单元格。如图所示22职工信息表Excel函数应用基础实例:党龄在25年以上的为资深党员,用函数在G列列出是否为资深党员.需要同时满足政治面貌为“党员”，而且党龄需要大于25年两个条件，才可以作为“资深党员”.选中G3单元格，输入函数“=IF(AND(E3=党员,(YEAR(TODAY())-YEAR(F3))25),资深党员,)”如图所示23职工信息表Excel函数应用基础统计函数中COUNTIF函数的应用COUNTIF函数对区域中满足单个指定条件的单元格进行计数。语法：COUNTIF(range,criteria)range必需。要对其进行计数的一个或多个单元格，其中包括数字或名称、数组或包含数字的引用。criteria必需。用于定义将对哪些单元格进行计数的数字、表达式、单元格引用或文本字符串。24职工信息表Excel函数应用基础实例:计算少数民族职工的总数,填入AE9单元格要计算少数民族职工数，可以条件设定为非汉族人数的统计在AE9单元格中编辑函数“=COUNTIF(K3:K67,汉)”如图所示25在条件选择上，少数民族应定义为除汉族以外的民族职工信息表Excel函数应用基础函数的综合应用实例:用函数查出姓名为林超的职工的学历通过工作表中某一单元格信息来搜索与其相关的其他单元格信息，我们可以考虑一些相关功能的函数来实现查找数据工作。AF13单元格中输入函数（方法一）“=INDEX(B3:M67,MATCH(AE13,B3:B67,0),MATCH(AF12,B2:M2,0))”如图所示：26职工信息表Excel函数应用基础实例:用函数查出姓名为林超的职工的学历AF13单元格中输入函数（方法二）“=VLOOKUP(AE13,B2:M67,12,FALSE)”如图所示：27思考：比较两种函数的特点职工信息表Excel函数应用基础实例:如果某个职工在本月过生日，在备注一栏里，写明“祝你生日快乐”要获取个人的生日月份才能判定是否是当月出生的，需要从个人身份证号码中提取，提取过程中要考虑是否存在空字符以及字符串转换成数字格式等因素。选中AD3单元格，输入编辑函数“=IF(VALUE(TRIM(MID(D3,11,2)))=MONTH(NOW()),”祝你生日快乐“,”“)”如图所示28选择了VALUE函数（将代表数字的文本字符串转换成数字）、TRIM函数（清除文本中所有的空格）、MID函数（返回文本字符串中从指定位置开始的特定数目的字符）29综合案例3Excel综合案例职工信息表案例简介Excel的基本操作设置Excel函数基础应用Excel数据图表应用实例思考题职工信息表Excel数据图表应用数据的排序数据排序是使用Excel2010自带的排序功能，对工作表中的文本、数字以及日期或时间数据按照一定的条件进行排列，并且可以进行自定义或格式排序。实例:对职称按高工，工程师，其他技术职称的顺序进行排序。选中整张工作表单A2:AD76，点击“开始”菜单中的“排序和筛选”按钮，选择自定义排序。主要关键字定义为“职称”次序定义为“自定义序列”如图所示30职工信息表Excel数据图表应用在自定义序列中填入序列为高工,工程师,其他如图所示完成后，职称排序完成效果如图所示31职工信息表Excel数据图表应用数据图表的制作Excel数据图表是数据的一种可视的表示形式，可以按照图形格式显示系列数值数据。图表可以直观显示工作表数据，从而形象地反映数据的差异、发展趋势及预测走向。Excel2010提供了11种图表类型，分别为柱形图、折线图、饼图、条形图、面积图、XY散点图、股价图、曲面图、圆环图、气泡图和雷达图。不同的图表类型适用于各类数据的形象表达。如图所示32职工信息表Excel数据图表应用实例:使用前四个人的“姓名”和“基本工资”两列数据建立簇状圆柱图，要求：标题名为“基本工资分布情况图”，分类轴为名为“姓名”，数值轴名为“基本工资”，初始值为1000元,图例显示在底部并显示值，插入到“Sheet2”,Sheet2命名为:“基本工资分布”点开“Sheet2”工作表，选择“插入”选项卡，点击“图表”分类选中“柱形图”里面的“簇状圆柱图”，并选择图表数据区域为“职工信息表!$B$2:$B$6,职工信息表!$P$2:$P$6”如图所示33职工信息表Excel数据图表应用修改图表背景填充颜色为渐变填充（默认），坐标轴选项中