Excel魔法之奇妙函数.ppt

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