Excel高级技巧应用专题研究报告.ppt

Microsoft Excel高级技巧应用专题 微软授权MLC讲师 2 课程安排 数据的输入、编辑及格式化技巧 公式、函数应用及编辑技巧 图表的操作 数据的分析与组织 3 一 数据输入、格式化表格技巧 自动限定数据 Excel常用快捷键 处理大型工作表常用技巧 表格数据的保护 4 1.自动限定数据 Custom Number(自定义数字格式) 灵活使用自定义数字格式如：输入身份证前的几位数输入条形码前的某些重复数字在数字后面添加单位（如元、千克等）自定义各种特殊日期格式定义单元格中的不同位数小数点对齐* 操作要点：先选择基本格式，再使用自定义(Custom)功能设置相应格式 5 Conditional Formatting_条件格式 根据单元格数据(Cell Value Is)区域的大小设置预警信息 根据公式(Formula Is)自动设置格式 实现隔行变色等的特殊格式 掌握ROW和MOD函数的用法 案例简析 掌握合理使用IF函数 条件格式中的条件设置技巧 6 Validation_数据有效性 有效性检查：可以对单元格内的数据类型进行限制，并能核对输入单元格的数据定义域 设置方法：如下拉式In-cell dropdown菜单 设置输入提示(Input Message) 设置出错警告(Error Alert) 自定义有效性条件(AllowCustom) 提供级联下拉菜单(Using Indirect Function) 7 2.高效的Excel快捷键 数据区域选择技巧 使用Ctrl+* 选择连续数据区域 使用Ctrl+Shift+方向键选择系列数据 使用Alt+;选择可见单元格(忽略隐藏单元格) 其他常用Excel的快捷键 Ctrl+; Ctrl+Enter Ctrl+方向键 Shift+F11 Alt+Enter F3 F4 10 3.如何处理大型工作表 冻结窗格(Freeze Panes) 命名单元格或单元格区域，以便能够快速返回到这些工作表区域(Insert Name) 组合工作表(Group Sheet)，以便同时在多张工作表中输入公用数据 工作表间的数据链接(Insert HyperLink) 组及分级显示(Group and Outline) 11 4. Compare Side by Side With…  并排比较数据 两个工作簿将在同一个窗口中打开。当一个工作簿滚动时，Excel 将使第二个工作簿自动滚动相应数量的单元格 同时比较两个工作簿可能很困难 12 5.Protect Data_表格数据的安全 传统的数据保护模式(Add Password)缺点：不太灵活 Protect Sheet (Workbook)保护工作表 将允许用户更改的单元格区域设置为解除锁定状态( ProtectionLocked) 将需要隐藏单元格公式等进行隐藏(Hidden) 将工作表进行保护的设置(Tools-Protection)有针对性设置保护的等级(Allow Users to Edit Ranges…) 优点：非常灵活 13 6. *About Print …打印操作 使工作表不能打印 几个工作表生成一个打印文件一次打印（打印的页数可延续到下一个“Sheet”) 使工作表不能打印: Private Sub Workbook_BeforePrint(Cancel As Boolean)Cancel = TrueEnd Sub 14 二、公式与函数应用编辑技巧 基本公式应用 常用函数的应用 15 1. Base Functions_公式基本用法 双击填充柄(Fill Control)复制公式 在公式中合理使用Round函数 区别公式中各种单元格引用方式 跨工作表或工作簿的数据计算 自动求和与自动计算功能的使用 自动求和(AutoSum)功能的扩展 自动计算功能的扩展 16 2. 函数应用与高级算法处理 逻辑文本类函数应用 日期类函数应用 查询引用类函数应用 统计计数类函数应用 17 a.逻辑文本类函数 IF AND\OR LEN CONCATENATE\ MID\LEFT\RIGHT PROPER\LOWER\UPPER\TRIM 18 IF函数 判断一个条件是否满足，如果满足返回一个值，如果不满足则返回另一个值 语法：IF(logical_test,value_if_true,value_if_false) 条件为真时 条件为假时 19 AND\OR AND函数的作用是对多个逻辑值进行交集计算，返回逻辑值AND(logical1,logical2…) OR函数的作用是对多个逻辑值进行并集计算，返回逻辑值OR(logical1,logical2,…) 20 LEN\CO