《excel办公应用教程》由会员分享，可在线阅读，更多相关《excel办公应用教程（100页珍藏版）》请在金锄头文库上搜索。
1、1,Excel在办公中的应用,微软(中国)高级培训师 张剑悦,2,Excel应用,数据管理 高效管理 准确、安全管理 专业美化效果 数据运算 公式 函数 数据分析 排序、筛选 分类汇总 数据透视表分析 单变量求解、模拟运算表、规划求解 数据图表化 数据共享与协作,3,数据管理,4,多种字段类型的数据表,5,Excel操作技巧举例,6,序列填充方法,方法1,方法2,方法3,方法4,7,日期在Excel中的应用,可更新,固定日期,可更新日期应用实例计算工龄,8,填充柄对日期的填充,以工作日填充,以月填充,9,自定义单元格类型,格式类型是显示在单元格中外在的表现形式与Excel数据本身没有必然联系,
2、10,自定义单元格格式类型应用,自定义类型 # （数值） （文本）,11,数据有效性,对单元格内容的填写进行限制 数据管理中使数据准确性加强最好的方式 整数 日期 序列 文本长度 自定义,12,数据有效性应用,13,对数据表单元格进行保护,对单元格进行保护和隐藏 设置单元格的“锁定” 设置单元格的“隐藏” 对工作表进行保护 对工作表的局部进行保护 无法选择被保护的单元格区域,14,数据保护举例,对数据表单元格进行只读权限的设置,15,保护单元格和工作表,可以添加密码保护可以对保护的工作表进行操作限制,哪个单元格是“锁定”的，哪个单元格就会被保护！,16,对工作表的隐藏,利用“VB编辑器”设置显
3、示属性,利用“隐藏”命令对工作隐藏,17,对工作表进行专业效果的美化,表头 竖排表头 斜线表头 工作表背景 背景图片 单元格底纹 框线设置 实线框 虚框设置 三维效果,18,设置表格美化的经验,不显示网格线 选用您独特的字体, 如 Tahoma, Verdana (公司另有规定除外) 表头, 标题和正文应选用不同的字号和图案以示区别 规范使用数字格式 没有必要把所有的数据都放在一张表内 尽量纵向安排数据, 以方便浏览 设置合适的显示比例, 以尽量在一屏显示全部数据 (Ctrl + 鼠标滚动轮) 固定窗格 为每一张工作表命名一个有意义的名称 删除多余的工作表,19,数据表美化前的效果,20,数据
4、表美化后的效果,设置“竖排表头”、“工作表背景”和“框线”后的数据表,21,斜线表头的制作,22,凹凸感三维效果数据表的制作,23,工作表组,为多个工作表添加相同的数据信息 为多个工作表设置相同的表格格式 组合工作组 撤销工作组 填充工作组 为分表数据在汇总表计算数据和,24,用工作表组进行数据添加,25,用三维引用获得数据结果,注意相对地址的引用= 一月! B7,26,跨表计算求分表数据在总表的和,注意： SUM函数与Shift键的配合,27,数据运算,28,Excel中的公式,所有的公式都以等号（=）开始 公式中可使用的运算符有 加（+）、减（-） 乘（*）、除（/） 乘方（ ） 括号（(
5、)） 运算规则和数学中的一样,29,公式和函数应用技巧,在公式中尽量使用引用而少输入数值 可用鼠标点击单元格来输入引用，而不必手工输入 适当使用括号或空格以易于阅读和理解 当公式较长时，可用 Alt+Enter 强行换行，以方便阅读 按F2 或双击单元格可进入公式编辑状态 可按F9 进行公式的重新计算 既可以复制公式，也可以复制公式的计算值,30,在 Excel 的工作表中，选中某些连续或不连续的单元格，那么这些单元格的一些计算（求和、平均值、最大或最小等）会自动显示在状态栏，而不用输入公式计算。如下图：,不使用公式就知道计算结果的技巧,31,单变量求解,32,常用函数,基本函数 计数相关函数
6、 Count Counta Countblank Countif Sumproduct Product Sum 数组函数,Rank Sumif CONCATENATE IF Left Right Mid Vlookup Hlookup,33,财务函数和其他函数,PMT SYD FREQUENCY INDEX MATCH OFFSET DSUM Dcount Dcounta,TEXT VALUE NA() INDIRECT MOD ROW INT TRIM REPT,34,相对地址引用和绝对地址引用,相对地址： 结果更改位置后，公式中引用的单元格也同步更改相对的位置,绝对地址： 结果更改位置后，
7、公式中引用的单元位置保持不变,35,相对地址和绝对地址引用举例,公式 =C5 / SUM ( $C$4 : $C$7) 中C5使用了相对地址，所以在计算其他部门时，可自动进行调整； 而求和区域使用了绝对地址“$C$4 : $C$7”，所以在计算时无论哪个部门都会与相同的区域和进行相除。,36,常用函数,SUM 求和,AVERAGE 求平均值,37,计数相关,Count函数是计算数值类型单元格的个数,Counta函数是计算所有类型非空单元格的个数,Countif函数是计算满足条件的单元格个数,38,计算总价的函数,Sumproduct 函数的功能是计算多列（多行）数据的乘积和，通常用来计算已知“
8、单价”和“数量”后的“总价”数据值,39,排名函数,40,条件求和计算,SUMIF 函数的作用是当满足一定的条件后，计算指定数值的和,41,条件函数,IF函数的使用规则 =IF（条件，条件成立，条件不成立）,42,IF函数的嵌套,函数的嵌套 =IF(L25000,“20%“,IF(L22000,“15%“,IF(L2500,“10%“,IF(L20,“5%“,0),43,文本函数,文本函数包括：文本的提取、文本长度计算、去除空格、文本重复、文本查找等多种运算,44,Vlookup 函数,利用Vlookup函数查找字母对应的城市： =VLOOKUP(C2,$M$2:$N$28,2,FALSE),
9、45,函数综合举例,利用身份证号码判断性别、计算出生日期、计算年龄等结果,计算性别：=IF(VALUE(RIGHT(G18,3)/2=INT(VALUE(RIGHT(G18,3)/2),“女“,“男“),计算出生日期：=IF(LEN(G18)=15,(CONCATENATE(“19“,MID(G18,7,2),“/“,MID(G18,9,2),“/“,MID(G18,11,2),(CONCATENATE(,MID(G18,7,4),“/“,MID(G18,11,2),“/“,MID(G18,13,2),计算年龄： =(TODAY()-I18)/365,46,FREQUENCY 频率函数应用,计
10、算区间段的个数可以使用 Frequency 函数 =FREQUENCY(D2:D29,G4:G8) 注意最好使用数组函数得到最终的结果,47,数据库函数,什么是数据库 ?数据库是一个内涵比较广泛的单词, 在Excel 中我们通常指数据列表Excel 中的数据列表通常满足下列条件:首行由标签组成, 每一个标签描述的是下面整列的内容. 该标签被称为字段 每一个字段都是唯一的 除第一行外，其他的行被称为记录 不包含空白行或列,48,数据库函数,49,数据库函数中条件的写法,可以放在数据列表之外的工作表的任意位置, 但通常在数据列表的上面或下面 至少包含两行, 第一行只包含某些或全部字段名 其他行包括
11、条件 同一行的条件意味”与” 不同行的条件意味”或” 条件中可包含 =, , =, , =, = 等比较符条件中也可包含公式或单元格引用,65,高级筛选,高级筛选要在空白单元格中添加筛选条件 同行条件为“与”关系；异行条件为“或”关系,筛选条件,筛选结果,66,分类汇总,对文本字段分类 再对其他字段进行汇总 求和 求平均值 求计数 多级分类汇总,67,分类汇总举例,操作方法： 先对分类字段进行排序，然后再使用“分类汇总”命令,68,用数据透视表进行数据分析,对数据表生成数据透视表 对数据字段进行分类 对数据字段进行汇总 对分类字段进行多级分类 制作二维分类表 隐藏汇总行 对多个数据进行汇总 对
12、分类字段进行分组 对数据透视表添加计算字段或计算项,69,字段数据表,70,生成数据透视表,71,数据透视表分析举例,查看“各部门”、各个“文化程度” ，平均工资的情况 二维分类表,72,数据透视表分析举例,查看“各部门”、各个“职务” ，平均工资的情况 二级分类表,73,数据透视表分析举例,查看“各部门”、各个“职务” ，平均工资和平均年龄的情况 二级分类表和多计算字段,74,按时间分组的数据透视表应用,75,单变量求解,76,模拟运算表,77,图表分析,78,图表的生成,79,图表的美化,图表分析,做了个图表,80,图表分析举例,堆积图 图表分析 计算数据分量的和，分析总量的关系,81,图
13、表分析举例,三维饼图 图表分析 分析总量份额之间的比例关系,82,图表分析举例,复合饼图 图表分析 分析总量份额之间的比例关系,83,图表分析举例,折线图 图表分析 分析数据走势情况,84,图表分析举例,雷达图 图表分析 分析数据走势和数量大小关系,85,图表分析举例,双轴图表 图表分析 既查看数据大小，又分析数据走势,86,其他图表举例,四象限图,水平线 图,直方图,瀑布图,87,创建动态图表,Offset以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。语法 OFFSET(reference,rows,cols,height,width)Refere
14、nce 作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用；否则，函数 OFFSET 返回错误值 #VALUE!。,88,Offset 函数,OFFSET(reference,rows,cols,height,width)Rows 相对于偏移量参照系的左上角单元格，上（下）偏移的行数。行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。Cols 相对于偏移量参照系的左上角单元格，左（右）偏移的列数。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。Height 高度，即所要返回的引用区域的行数。Height 必须为正数。Width 宽度，即所要返回的引用区域的列数。Width 必须为正数。如果省略 height 或 width，则假设其高度或宽度与 reference 相同。,
