第3篇-综合应用篇-综合案例3-Excel综合案例

1 计算机基础计算机基础 第三篇第三篇 综合案例篇综合案例篇 综合案例综合案例 3 Excel2010 2 综合案例综合案例 3 Excel综合案例综合案例 职工信息表职工信息表 l 案例简介 l Excel的基本操作设置 l Excel函数基础应用 l Excel数据图表应用 l 实例思考题 职工信息表职工信息表 案例简介案例简介 l 简介简介 利用 EXCEL2010软件 来建立一个 企业职工信息表，进行 EXCEL高级操作 练习，对信息进行有效处理，让工作达 到事半功倍的效果。 3 职工信息表职工信息表 案例简介案例简介 l 总体分析总体分析 企业 每月工资的发放是再正常不过的一件事情，那么在 每月工资发放前夕，财务人员必须要完成对本月工资的核算 。工资的最终结算金额来自多项数据，如员工的基本工资、 涨工资额、各项补贴、个人所得税等等。如果用手工方式计 算这些数据。工作效率低下，并容易出错。有了 EXCEL软件 ，就可以在学习 EXCEL中创建表格并利用函数计算来管理这 些数据，从而达到一些自动化管理的功能。 4 职工信息表职工信息表 案例简介案例简介 l 设计规划设计规划 通过具体实例，我们要学习和掌握 EXCEL2010软件如下 部分 EXCEL的 基本 操作 设置 Excel函数 的运用 Excel数据库 管理 和 数据 进行分析 Excel综合 运用 5 职工信息表职工信息表 案例简介案例简介 l 解决方案解决方案 制作 本案例时，首先需要搜索信息建立职工信息表，并 进行单元格格式设置，除了用公式统计每位员工的工资额外 ，还要进行排序、高级筛选、制作图表和数据透视表等操作 。 此外 ，因为企业实际业务的需要，还要把工资表制作成 工资条发给每位员工，我们利用 EXCEL2010的高级操作完 成这个功能，制作出来的工资条既美观又简便。 6 7 综合案例综合案例 3 Excel综合案例综合案例 职工信息表职工信息表 l 案例简介 l Excel的基本操作设置 l Excel函数基础应用 l Excel数据图表应用 l 实例思考题 职工信息表职工信息表 Excel的基本操作设置的基本操作设置 l 建立 工作表并 重命名 用户可以将使用的工作表根据工作表的具体内容进行重 命名，方便以后查找工作表。可以使用下了方式进行操作。 右击要更改名称的工作表标签，然后单击快捷菜单的 “ 重命名 ” 命令 ，这时工作表的名称高亮显示，然后输入新的工作表名称，按 Enter 键，即可完成工作表重命名操作。 l 工作表标签颜色更改 为了便于查找显示不同的工作表，可以改变工作表标签 的颜色，也可以将有重点内容的工作表做特殊标记。 右击工作表标签，然后单击快捷菜单中的 “ 工作表标签颜色 ” 项，即 可更改所需的标签颜色。 8 职工信息表职工信息表 Excel的基本操作设置的基本操作设置 9 如图，为 “职工信息表 ”创建蓝色标签 职工信息表职工信息表 Excel的基本操作设置的基本操作设置 l 工作表标题 操作 实例实例 ： 给给 工作表插入标题工作表插入标题 “ 职工信息职工信息 ”, 合并居中合并居中 ,字体为黑色字体为黑色 24号楷体号楷体 。 选中 第 1行 右键点击插入，并合并 A1:AD1单元格，输入文字：职工 信息，设置字体为 黑色 24号 楷体 。 10 职工信息表职工信息表 Excel的基本操作设置的基本操作设置 l 冻结窗口 操作 窗格是文档窗口的一部分，以垂直或水平条为界限，并 由此与其他部分隔开。可以通过冻结窗格来查看工作表的两 个区域和锁定一个区域中的行或者列。 实例：实例： 冻结列标题和姓名列冻结列标题和姓名列 先切换到 “视图 ”选项卡，然后选中 C3单元格，点击 “冻结窗格 ”，冻结 拆分窗格 。 11 需选择正确的单元格 再进行冻结窗口操作 职工信息表职工信息表 Excel的基本操作设置的基本操作设置 l 单元格格式 操作 右 击 选中的单元格，选择 “设置单元格格式 ”，在对话框中 选择不同要求的单元格格式。包括 “数字 ”、 “对齐 ”、 “字体 ”、 “ 边框 ”、 “填充 ”、 “保护 ”选项栏。 12 职工信息表职工信息表 工作薄与工作表基础应用工作薄与工作表基础应用 l 条件格式的 应用 通过应用不同的颜色刻度、数据条或图表集样式，可以 更直观显示工作表中的数据。 实例：实例： 对基本工资数据区域使用条件格式为蓝色数据条对基本工资数据区域使用条件格式为蓝色数据条 。 选中 “样式 ”选项卡，选择 “条件格式 ”，采用 “数据条 ”类型，选中使用 “蓝 色数据条 ” 13 如图，对基本工资使 用到了蓝色数据条 职工信息表职工信息表 工作薄与工作表基础应用工作薄与工作表基础应用 l 条件格式的 应用 实例：实例： 并并 将职工信息表的自动套用格式设置为将职工信息表的自动套用格式设置为 “ 浅色浅色 16” 。 先 将职工信息表全选后，选中 “开始 ”选项卡，选择 “套用表格格式 ”，采 用 “浅色 ”中 “表格样式浅色 16”类型 。 14 选择浅色 16样式 15 综合案例综合案例 3 Excel综合案例综合案例 职工信息表职工信息表 l 案例简介 l Excel基本操作设置 l Excel函数基础应用 l Excel数据图表应用 l 实例思考题 职工信息表职工信息表 Excel函数应用基础函数应用基础 l 日期与 时间 函数 应用 对工作表中的包含的日期与时间信息按规定进行处理的 一类函数。日期与时间函数主要应用于两个时间点之间的天 数、制定月份的最后一天、将时间与日期转换成序列号、返 回指定时间、计算周次等。日期函数包括 DATE函数、 DAY 函数、 YEAR函数等，时间函数通常包括 HOUR函数、 MINUTE函数、 SECOND函数等。 实例：实例： 计算每位职工的工龄计算每位职工的工龄 函数思路：工龄 =当前的年份数 -参加工作时的年份数 当前年份 =YEAR(TODAY() 语法： YEAR(serial_number) serial_number 为一个 日期值 16 当前时间点可以用 TODAY函数获取 职工信息表职工信息表 Excel函数应用基础函数应用基础 选中 J3单元格，在编辑栏中输入 “=YEAR(TODAY()-YEAR(I3)” 如 图所示：计算工龄 17 注意单元格格式应为数字格式 职工信息表职工信息表 Excel函数应用基础函数应用基础 l 数组频率统计函数 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函数应用基础函数应用基础 l 逻辑函数中 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(K325),“资深党员 “,“)” 如 图所示 23 职工信息表职工信息表 Excel函数应用基础函数应用基础 l 统计函数中 COUNTIF函数的 应用 COUNTIF 函数对区域中满足单个指定条件的单元格进 行计数 。 语法： COUNTIF(range, criteria) range 必需。要对其进行计数的一个或多个单元格， 其中包括数字或名称、数组或包含数字的引用 。 criteria 必需。用于定义将对哪些单元格进行计数的数 字、表达式、单元格引用或文本字符串。 24 职工信息表职工信息表 Excel函数应用基础函数应用基础 l 实例实例 :计算少数民族职工的总数计算少数民族职工的总数 ,填入填入 AE9单元格单元格 要 计算少数民族职工数，可以条件设定为非汉族人数的 统计 在 AE9单元格中编辑函数 “=COUNTIF(K3:K67,“汉 “)” 如图所示 25 在条件选择上，少数民族应定义为除汉族以外的民族 职工信息表职工信息表 Excel函数应用基础函数应用基础 l 函数 的综合应用 实例实例 : 用函数查出姓名为用函数查出姓名为 “林超林超 “的职工的的职工的 学历学历 通过 工作表中某一单元格信息来搜索与其相关的其他单元格信息， 我们可以考虑一些相关功能的函数来实现查找数据工作 。 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 综合案例综合案例 3 Excel综合案例综合案例 职工信息表职工信息表 l 案例简介 l Excel的基本操作设置 l Excel函数基础应用 l Excel数据图表应用 l 实例思考题 职工信息表职工信息表 Excel数据图表应用数据图表应用 l 数据的 排序 数据排序是使用 Excel2010自带的排序功能，对工作表 中的文本、数字以及日期或时间数据按照一定的条件进行排 列，并且可以进行自定义或格式排序。 实例实例 :对职称按高工，工程师，其他技术职称的顺序进行排序对职称按高工，工程师，其他技术职称的顺序进行排序 。 选中整张工作表单 A2:AD76，点击 “开始 ”菜单中的 “排序和筛选 ”按钮 ，选择自定义排序。主要关键字定义为 “职称 ” 次序定义为 “自定义序 列 ”如 图所 示 30 职工信息表职工信息表 Excel数据图表应用数据图表应用 在自定义序列中填入序列为高工 ,工程师 ,其他 如 图所示 完成后，职称排序完成 效果如图所示 31 职工信息表职工信息表 Excel数据图表应用数据图表应用 l 数据图表的 制作 Excel数据图表是数据的一种可视的表示形式，可以按照图形格式显 示系列数值数据。图表可以直观显示工作表数据，从而形象地反映数据 的差异、发展趋势及预测走向。 Excel2010提供了 11种图表类型，分别为柱形图、折线图、饼图、条 形图、面积图、 XY散点图、股价图、曲面图、圆环图、气泡图和雷达图 。不同的图表类型适用于各类数据的形象表达。如图所示 32 职工信息表职工信息表 Excel数据图表应用数据图表应用 实例实例 :使用前四个人的使用前四个人的 “ 姓名姓名 ” 和和 “ 基本工资基本工资 ” 两列数据建立簇状圆柱图两列数据建立簇状圆柱图 ，要求：标题名为，要求：标题名为 “ 基本工资分布情况图基本工资分布情况图 ” ，分类轴为名为，分类轴为名为 “ 姓名姓名 ” ， 数值轴名为数值轴名为 “ 基本工资基本工资 ” ，初始值为，初始值为 1000元元 , 图例显示在底部并显示值图例显示在底部并显示值 ，插入，插入 到到 “ Sheet2” ,Sheet2命名为命名为 :“ 基本工资分布基本工资分布 ” 点开 “Sheet2”工作表，选择 “插入 ”选项卡，点击 “图表 ”分类选中 “柱 形图 ”里面的 “簇状圆柱图 ”，并选择图表数据区域为 “职工信息表 !$B$2:$B$6,职工信息表 !$P$2:$P$6” 如图所示 33 职工信息表职工信息表 Excel数据图表应用数据图表应用 修改图表背景填充颜色为渐变填充（默认），坐标轴选项中，最小值 设为 1000，主要刻度和次要刻度均设置为 500，如 图所 示 。 同时在数 据图右侧中选择 “插入 ”选项卡，点击 “艺术字 ”，编写艺术字内容 “基本 工资分布情况图 ”，调整艺术字大小和位置，使之整体效果美观 34 基基 本本 工工 资资 分分 布布 情情 况况 图图 职工信息表职工信息表 Excel数据图表应用数据图表应用 l 高级 筛选 案例案例 ： 高级高级 筛选筛选 :少数民族党员的基本情况少数民族党员的基本情况 ,将筛选结果存入将筛选结果存入 Sheet3,并更名并更名 为为 “ 少数民族党员基本情况少数民族党员基本情况 ” 先把整张数据表复制到 Sheet3表，编写满足题目要求的筛选条件 政治 面貌为党员，民族为非汉。选择 “数据 ”选项卡，在 “筛选 ”分 栏中 点击 “高级 ”按钮 。 如图所示 35 筛选结果：均为少数民族党员 筛选条件 职工信息表职工信息表 Excel数据图表应用数据图表应用 l 数据透视 表 案例案例 ： 新建新建 数据透视表数据透视表 ,分析不同部门各种职称不同学历的员工的个数分析不同部门各种职称不同学历的员工的个数 , 命名为部门职称学历命名为部门职称学历 情况情况 ， 并将数据透视表的最左上格添加斜线表头，并将数据透视表的最左上格添加斜线表头， 表头名为表头名为 “ 部门部门 职称职称 ” 新建 工作表命名为 “部门职称学历情况 ”，选择 “插入 ”选项卡下 “表格 ” 组中 “数据透视表 ”，在创建对话框中表区域选择职工信息表 !$A$2:$AD$67。如图所示 36 职工信息表职工信息表 Excel数据图表应用数据图表应用 根据题目要求选择添加到报表的字段分别为 “部门名称 ”、 “学历 ”、 “ 职称 ”三项。如图所 示 37 添加 部门名称、学历、 职称 三项字段 职工信息表职工信息表 Excel数据图表应用数据图表应用 拖动 “职称 ”字段到列标签， “部门名称 ”字段到行标签，数值计数项为 “学历 ”字段，如图所 示 38 列标签、行标签、数值统计项 职工信息表职工信息表 Excel数据图表应用数据图表应用 完成 数据透视表后，选择最左上角单元格 “计数项：学历 ”，删除其内 容，重新输入内容 “部门 职称 ”，然后右键点击本单元格，选择设置 单元格格式中 “边框 ”选项卡中，绘制斜线表 头 。 39 输入部门职称时，通过 空格符号，使之两个项目 产生 一定间距，可以使斜 线表头设计美观 选择单元格斜线 职工信息表职工信息表 Excel数据图表应用数据图表应用 案例：案例： 将将 职工信息表中的部门名称和实发工资两列复制到职工信息表中的部门名称和实发工资两列复制到 Sheet4，并更，并更 名为名为 “ 部门工资发放情况表部门工资发放情况表 ” ，对各部门的实发工资用合并计算进行统，对各部门的实发工资用合并计算进行统 计计 。 同时按合并计算的部分进行制作同时按合并计算的部分进行制作 SmartArt中的基本列表图形。中的基本列表图形。 新建一张工作表改名为 “部门工资发放情况表 ”，并把职工信息表中的 部门名称和实发工资两列复制到本工作表内。指定一个空白单元格， 选择 “数据 ”选项卡里的 “合并计算 ”功能，引用位置确定为选取的两列数 据，并将其添加到所有引用位置，并在标签位置处 选择 “首行 ”和 “最左 列 ”，最后确定 完成 。 40 职工信息表职工信息表 Excel数据图表应用数据图表应用 按合并计算的部分进行制作 SmartArt中的基本列表图形 。 在 本工作表空白处点击 “插入 ”选项卡，在插图列表中选择 “SmartArt” 菜单，选择基本列表样式，在左侧的项目列表中填