《Excel魔法之奇妙函数.ppt》由会员分享，可在线阅读，更多相关《Excel魔法之奇妙函数.ppt（48页珍藏版）》请在三一办公上搜索。
1、培训讲师:彭亮,Excel 魔法之奇妙函数,行课网,基础应用,行课网,快速选择数据,移动技巧:Home end ctrl+方向箭 选择区域的技巧：Ctrl+shift+8:选择活动单元格周围的相邻区域。Ctrl+shift+方向键：:选择活动单元格相邻方向上的区域F5：选择特殊区域。,批量输入数据,批量输入相同的内容选择区域,输入内容,Ctrl+回车输入内容，右下角，下拉填充批量输入不同内容日期填充自定义序列=Randbetween(下限值，上限值),行课网,数据有效性,数据有效性基本应用限制数字日期序列.设置输入提示信息设置出错警告数据有效性高级应用,行课网,单元格样式,单元格样式是一组已定
2、义的格式特征几种格式一个步骤完成创建修改删除合并,行课网,表格套用格式,套用表样式创建删除自定义表样式去除表样式表样式选项,行课网,增强的条件格式,突出显示所关注区域强调异常值更直观地显示数据基于条件更改外观,行课网,公式和函数,行课网,使用函数,行课网,公式函数规律,=函数名称(参数1,参数2)参数由用户提供,最为0,最大255 号中的参数有时可省略 参数中 表示若干参数所有符号用英文半角字符用“”参数用,括号成对出现不区分大小写任何函数均有返回值函数将自动更新,行课网,引用,相对引用，复制公式时地址跟着发生变化 A3绝对引用,复制公式地址不会发生变化$A$3名称引用 为区域命名，方便存储反
3、复引用，也方便动态区域引用,常用文本函数,Left(“ABCDE”,2)=AB 左边取2字符Right(“ABCDE”,2)=DE 右边取2字符Mid(“ABCDE”,2,3)=BCD 第2字符后3Len(“ABCDE”)=5 长度REPT(“*”,10)填充字符串,行课网,常见日期函数,Today()今天Now()现在Year()提取年份Month()提取月份Day()提取日期DATE(2013,5,31)返回2013-5-31,IF函数,IF(logical_test,value_if_true,value_if_false)可结合逻辑函数用其他方法代替逻辑判断,行课网,新增函数,输入时提
4、示功能支持64层嵌套 255个参数Ctrl+Shift+u Shift+F3 Alt+=新增一些函数AverageifSumifs countifsAverageifs,行课网,VLOOKUP(精确查找),VLOOKUP(lookup_value,table_array,col_index_num,range_lookup),=VLOOKUP(M3,B2:E19,2,0),选项为0或false，为精确查找列号是需要返回的数据在查询区域中的第几列查询值必须与查询数组首列对应数字格式须保持一致,行课网,VLOOKUP精确查找,查找值与查找范围中值匹配查找范围一般绝对引用适用单条件查找查找范围中的值
5、一般不重复=Vlookup(查找值,查找范围,返回列,查找类型),行课网,vlookup(模糊查找),VLOOKUP(lookup_value,table_array,col_index_num,range_lookup),=VLOOKUP(C3,$J$3:$K$6,2,1),选项为1或ture,为模糊查找,默认模糊查找注意查询数组需按升序列号是需要返回的数据在查询区域中的第几列查询值必须与查询数组首列对应D列用IF函数也可得到类似效果,行课网,Iferror 处理错误值,=IFERROR(VLOOKUP(E3,$A$2:$C$18,2,0),)=IF(ISNA(VLOOKUP(E2,$A$2
6、:$C$18,2,0),VLOOKUP(E2,$A$2:$C$18,2,0)IFERROR(value,value_if_error),不存在的姓名将显示为空,行课网,高级图表,行课网,图表主要新功能,预置大量样式主题新增实时预览功能支持更多颜色效果其他特殊效果快速布局图表模板Alt+F1 F11,行课网,设置图表,设计:整体修改图表布局:显示不显示图表中的元素格式:设置图表格式,行课网,图表效果更美观,艺术字去除锯齿立体化颜色更多,行课网,正确选择图表类型,a.柱形图 表示大小多少的对比,b.折线图 强调项目的趋势,c.饼图 显示百分比，强调份额,行课网,双坐标轴图表的操作,利用部分数据源做
7、图表添加子图表的数据源子图表的系列更改为次坐标轴选择子表的图表类型图表选项和格式的设置,行课网,动态图表,基础图表制作窗体控件使用开发工具，窗体，组合框，按住拖向右下角INDEX函数=INDEX(array,row_num,column_num)右击，设置控件格式，数据源区域，单元格链接相对引用绝对引用=INDEX(B1:E1,$A$9),行课网,数据透视表,行课网,排序,支持64层排序支持色彩排序 单元格颜色，字体颜色，图标自定义排序文件选项高级编辑自定义列表 数据排序次序自定义序列,筛选,支持更多筛选方式颜色，文本，预置表达式高级筛选：列表区域、条件区域、结果区域如果条件写在同一行中为an
8、d如果条件写在同一列中为 or去除重复记录更简单,行课网,分类汇总,分类汇总前一定先排序支持多级别分类汇总数据，分类汇总，选择分类字段，选择汇总方式，选择计算字段，是否要分页显示,行课网,效果展示,原始数据,透视数据,行课网,数据透视表的优势,将复杂的数据快速整理报表结构灵活多变容易学习，不需要函数基础,前提条件,第一行必须有标题表格中不能有空行或者空列每一列的数据类型要一致创建之前不要有分类汇总、合并单元格,行课网,数据透视表操作步骤,规范化数据表选择数据表插入数据透视表，制作结构根据目的确定所需的字段将字段放在相应的区域中，行，列，页根据需修改统计方式格式属性的调整,行课网,使用数据透视表
9、,透视表的布局：行，列，页，数据显示指定项(强调显示字段中某一个值 页)显示多项或所有项 行，列如果行中有多项字段，可将一项放到列中形成交叉表页，行，列中可以为空，但不能放重复的内容.数据字段可以重复的，但不能为空.,行课网,数据透视表的美化与完善,字段展现形式的选择报表布局的选择字段名称的修改样式的应用,行课网,数据透视表中使用公式,计算项与计算字段数据透视表工具选项计算字段,行课网,分组显示数据,日期放在行字段,小计放在数据字段右击,创建组,按年和季度,行课网,数据透视表其他,固定数据透视表刷新数据透视表制作数据透视图分页显示数据将字段放在页字段中数据透视表工具选项下拉列表显示报表筛选页,
10、行课网,数据透视图,1,3,2,4,1.页字段 2.数据字段 3.系列字段 4.分类字段 5.项,行课网,数据管理,行课网,链接工作簿,链接工作表链接工作簿引用的表达方式=报价单.xls报价单!$C$24,行课网,选择性粘贴,仅复制格式仅复制值复制有效性转置运算,行课网,合并计算,如想按左列分类统计,请勾选最左列按第一行分类统计,请勾选首行自动更新功能 创建指向源数据的链接当源和目标区域在同一张工作表时，是不能够建立链接的,行课网,保护工作表,使区域锁定(格式单元格 保护锁定)(审阅保护工作表允许用户选择未锁定单元格)公式隐藏(格式单元格 保护隐藏勾选)(审阅保护工作表允许用户选择未锁定单元格
11、)窗口和结构锁定审阅保护工作簿,行课网,视图管理,切换窗口重排窗口冻结窗格分割窗口自定义视图,行课网,用宏提高效率,行课网,使用宏,开始录制,操作1,操作2,操作3,操作4,结束录制,执行宏,操作1,操作2,操作3,操作4,将录制好的宏指定给按钮,行课网,宏的命名,将操作步骤录制，以后简单地再次执行宏名不能用数字开头不能用#!特殊字符及空格不能超过255个不能使用关键字不区分大小写,行课网,宏的操作,录制宏快捷键工具栏/菜单项指定宏按钮 图标,图形,工作表按钮重设快捷键编辑删除宏宏的相对引用,行课网,宏的安全性,禁用所有宏，并且不通知：只有位于信任文件夹中的宏能够运行。禁用所有宏，并发出通知：当打开有宏的文件，将打开对话框，让用户选择禁用或启用。禁用无数字签署的的所有宏：该设置要求用户从verisign或其他提供商那里获取数字签名工具。如果偏写供自己使用的宏，多此一举。启用所有宏：不推荐，可能会运行有潜在危险的代码。学习测试时方便,行课网,宏的缺点,使用了很多没有必要的代码无法实现循环操作和条件语句无法实现和用户对话缺少通用性,行课网,
