excel senior training.pptxVIP

MS Excel高级用户实用技巧培训 --Wang Ji From OPS/PP Excel 用户的五个层次 2 目录 深入基本功能 函数 函数综合应用 公式、宏和VBA 3 深入基本功能 4 自定义数字格式 Demo004 自定义的格式代码具有以下的结构 正数；负数；零值；文本 大于条件值；小于条件值；等于条件值；文本（可用“比较运算符+数值”的方式来表示条件值） 小技巧001 5 保留自定义格式的显示值 Ctrl+C -打开剪贴板对话框-目标单元格-点击剪贴板中所需复制的项目 隔行插入空行的技巧 借助辅助列和排序的方法 标记重复项目 利用条件枝格式-仅对唯一值或重复值设置格式 深入基本功能 6 数据透视表 多表动态汇总 制作复杂结构的计划完成状况分析表 Demo009 select 销售员,开票日期,数量,销售 as 类别 from [销售明细$] union all select 销售员,0 as 开票日期,计划销量,计划 as 类别 from [计划$] 目录 深入基本功能 函数 函数综合应用 公式、宏和VBA 7 公式、宏和VBA 8 宏的定义 宏与VBA 宏是VBA语言编程的一段程序，是一系列命令和函数，存储于Visual Basic模块中，在需要执行该任务时可随时运行。 Excel宏就是一组指令集，通过执行类似批处理的一组命令，来实现任务的自动化执行。 VBA是Visual Basic For Application的简称，是Office中的一种应用程序开发工具。VBA可以有效地自定义和扩展Excel的功能。 VBA功能很强大，但不是所有工作用VBA都会很简单，需要依据实际情况来定。通常能用公式就不用宏或VBA。 录制绝对宏（合并PICKLIST，工作中的实例） Demo058 修改录制宏,使其适用于更广的范围 宏练习01 公式、宏和VBA 9 录制相对宏（数据整理）工作中的实例 Demo059 修改录制宏,使其在指定的区域范围,运行适当的次数,得到结果. 公式、宏和VBA 10 修改录制宏,使其准确运行. 用VBA编程替代录制宏. 宏、VBA和公式（去掉尾随半角与全角空格，工作中的实例） Demo060 小技巧003 隐藏Excel中的名称 Demo 小技巧003 1.在VBA编辑器窗口选“插入”-“模块” 2.在模块1的代码窗口输入以下代码。 Sub HideNames() Dim nm As Name 声明 Name 变量 For Each nm In ThisWorkbook.Names 循环所有定义的名称 nm.Visible = False 设置为false-隐藏,true-显示 Next 下一循环 End Sub 3.在代码窗口运行这段代码后，能隐藏所有定义的名称。 4.如果将”nm.Visible = False” 改为”nm.Visible = true” 后能恢复名称显示 11 公式、宏和VBA 目录 深入基本功能 函数 函数综合应用 公式、宏和VBA 12 函数 15 15 15 15 15 15 15 15 13 请用Excel自动完成九宫图数字1-9的填充 使用规划求解,配合函数完成九宫图求解 Demo061 15 8 1 6 15 3 5 7 15 4 9 2 15 15 15 15 15 14 请用Excel自动填充数字1-9完成九宫图 函数综合应用 函数 15 Excel函数之条条道路通罗马 具有相似功能的函数或组合 Rand vs Randbetween Ceiling vs Roundup; Floor vs Rounddown Sumproduct vs Sumifs/Countifs Date vs Edate/Emonth IF vs Choose …… Demo010 达到同一目标的函数公式组合 计算个税 Demo011 函数 16 函数一般分类 日期与时间函数 文本和数据函数 信息函数 数字与三角函数 统计与求和函数 查找与引用函数 财务函数 工程函数 数据库函数及外部函数 常用参数 17 数值，如=A1*10 日期，如=（“2008-8-22”-“2008-8-8”） 文本，如=MID(“1234567890”,5,5) 逻辑值，如｛=match(true,(A1:A10=”1001”)，0)｝ 错误值，如=countif(A1:A100,#num!) 数值与逻辑值 在数值运算中，True=1,False=0 在逻辑判断中，0=false,所有非0数值=True 函数 文本型数值与数值转换 18 假设A1为文本型数值“1”，有以下几种常用方法实现转换 =A1*1 =1 =A1