Excel应用讲义.docVIP

Excel讲 义 课程设计： 第1章 用Excel制作季度报表 1 1.1 案例分析 2 1.2 案例实现 5 1.2.1 柱形图的制作 5 1.2.2 饼图的制作 8 1.2.3 双轴图表的制作 12 1.2.4 带有筛选功能图表的制作 14 1.3 答疑与技巧 16 第2章 用Excel管理人力资源信息表 19 2.1 案例分析 20 2.1.1 员工信息表的建立 20 2.1.2 员工信息查询 21 2.1.3 员工数据的筛选分析 22 2.2 案例实现 22 2.2.1 利用数据有效性快速、准确输入数据 22 2.2.2 利用身份证号码计算出生年月、年龄及性别 25 2.2.3 快速输入带有固定前缀的数据 29 2.2.4 用Vlookup函数快速查询人员资料 30 2.2.5 用高级筛选查询条件满足的员工 32 2.3 答疑与技巧 34 第3章 用Excel管理客户信息表 36 3.1 案例分析 36 3.2 案例实现 38 3.2.1 用SUMPRODUCT函数计算销售总价 38 3.2.2 用COUNTA函数计算文本数量 39 3.2.3 用VLOOKUP函数提取相关数据 41 3.2.4 为客户信息排名 43 3.3 答疑与技巧 45 3.4 拓展与提高 46 3.4.1 数据透视图 46 3.4.2 用高级筛选模糊查询信息 48 第4章 用Excel管理销售数据信息 50 4.1 案例分析 51 4.1.1 产品销售统计汇总表 51 4.1.2 销售客户信息表 52 4.1.3 销售人员销售额统计表 53 4.2 案例实现 53 4.2.1 用工作组高效创建数据相似表 53 4.2.2 跨表引用汇总多表数据 56 4.2.3 为信息表的数据进行拆分及合并 61 4.2.4 用批注对数据进行人性化的提示 65 4.2.5 对重复数据的标记 67 4.2.6 用函数快速统计销售数据和奖金 69 4.2.7 用分类汇总快速统计销售数据 73 第1章 用Excel制作季度报表 Excel是一个非常好的数据管理和分析软件，它有多种数据分析方法，其中的图表分析功能以其直观、准确和便于比较等特点一直受到人们的青睐。 在Excel中，制作季度报表最常采用的方法就是将数据制作成利于观看的各种图表，然后再对数据进行分析，本章就向大家介绍如何根据数据表特点和分析需求制作出最佳的图表效果。 在“一季度销售统计报表”中，分别使用了两种“柱图”来分析“按月份统计产品销售总额”和“按产品统计各月的销售金额”，如图1.1所示。 图1.1 使用“柱图”分析两种不同数据关系 在“一季度销售统计报表”中，还可以利用“饼图”来分析“各个月份的销售份额”以及“各个产品的销售份额”情况，如图1.2所示。 图1.2 分别使用“饼图”分析“各产品”和“各月份”的份额 在另一个“销售统计报表”中，使用了双轴图表来分别表达“销售额”和“完成率”的数据关系，“销售额”数据是“柱图”看左侧坐标轴，“完成率”是“折线图”看右侧坐标轴，效果如图1.3所示。 图1.3 双轴“图表”分析数据 在“一季度各地区销售报表”中制作了带有“筛选”功能的图表，若在数据表中进行“自动筛选”，那么筛选结果会直接反映在图表当中，使利用图表分析数据更为方便和实用，如图1.4所示。 图1.4 制作的带有“筛选”功能的图表 在制作这些数据图表时，虽说存在有一定的规律，但是在数据分析时却没有固定的模式，要根据数据特点和分析需求来制作生成所需的图表，而且图表生成后，还应该根据图表的特点对图表中的各个元素进行格式美化及属性调整。 1.1 案例分析 本例介绍的3个数据表都是销售报表，分别用了“柱图”、“饼图”和“折线图”来分析季度或半年的销售数据。这3种图表是日常工作中使用最多的分析工具，利用它们可以直观地说明数量的多少、份额的大小以及数据走势等情况，甚至可以起到协助决策的关键作用。 在本例的“一季度销售统计报表”中，做了2个“柱形图”来分析数据。 $$ 第1个柱图是“三维簇状柱形图”，它很好地说明了各个产品在各个月份中的销售情况，图表以不同的产品作为分类，不同颜色的“柱”则表示各个月份的销售额。通过这样的“柱图”可以很好地比较每个产品在各月中销售量的多少，还可以通过每个分类看到这个产品的月销售走势情况，如图1.5所示。 图1.5 “三维簇状柱形图”分析数据 $$ 第2个柱图是“三维堆积柱形图”，它很好地说明了各个产品在各个月份中的销售总量，图表以不同的月份作为分类，不同颜色的“柱”则表示各个产品的销售额，而且将不同的产品销售额“堆积”在一起计算出了各个月份的销售总额情况，如图1.6所示。 图1.6 “三维堆积柱形图”分析数据 在本例的“一季度销售统计报表”中，除了制作“柱形图”来分析各个产品