Excel高级应用技巧

企业信息化基础---办公自动化Excel高级应用Excel高级应用培训目标•熟练掌握应用电子表格处理软件EXCEL进行基础数据归集、分类、筛选、统计与分析的方法；•熟练掌握表格格式设计、打印页面设置、自动表头的方法；•初步掌握利用宏、VBA命令进行数据处理的方法；•初步掌握与VFP、Access、SQLServer等数据库系统交换数据的方法；•初步掌握对自己业务领域的数据进行信息化规划设计的能力。Excel高级应用培训对象及起点要求•各类对利用计算机进行表格处理有兴趣的办公、计划、统计、财务及核算人员•具有较为熟练的计算机基本操作能力对计划、统计、财会及业务核算有一定基础Excel高级应用培训内容•1.EXCEL基础数据与汇总数据的结构设计与应用实例；•2.EXCEL基础数据与统计分析数据的结构设计与应用实例；•3.EXCEL数据的高级筛选方法与应用实例；•4.EXCEL数据计算公式的使用方法与应用实例；•5.EXCEL表格格式设计的方法与应用实例；•6.EXCEL表格打印设置的方法与应用实例；Excel高级应用培训内容(续)•7.计划数据(或定额数据)、历史数据与本期发生数据的存储结构与自动分析报表的设计方法；•8.EXCEL数据的高级图表分析方法与应用实例；•9.EXCEL宏及VBA的基本概念及应用实例；•10.EXCEL与VFP等数据库之间的导入、导出方法及应用实例；•11.EXCEL综合应用实例；•12.学员典型或疑难问题分析解答。Excel高级应用学习要点•掌握表格式、常用函数、公式的设计,掌握不同工作表、不同工作簿之间的数据引用---入门、应用、走向提高•了解宏、自定义函数、与数据库的数据导入、导出方法---高级技巧可以不会做、但应该知道找人帮助使用•了解利用计算机技术进行业务数据规划的基本原理和方法---必须清楚用算盘算帐与用计算机算帐的本质区别Excel高级应用Excel概述•Excel是什么？•Excel的版本？•Excel表与手工表格的本质区别？Excel高级应用•1.1Excel基础结构概述•工作簿•工作表•单元格第1章Excel基础工作簿是一个以XLS为扩展名的Excel文件工作表是Excel文件中用来存放表格的一页单元格是Excel工作表中行号与列号交叉点上的一格，是表格中存放数据的最小单位Excel高级应用•1.2公式基础•公式的输入•公式的切换•以结果替换公式第一章Excel基础公式编写要点：①运算符号必须是半角字符，如：等号=、括号()[]、双引号、+、-、*、/②公式在单元格内以等号(=)开始，紧跟着是运算符连接的一个关系表达式实Excel高级应用•单元格与区域的引用1.单元格的引用单元格标号(座标、地址)：列号+行号，如“D5”表示第4列第5行一个单元格2.区域的引用：区域：多个相邻的单元格组成的矩形如“B2：D5”区域标号：区域的左上角标号+“：”+右下角标号区域命名：通过定义一个名称来引用区域小技巧--区域的选择：在名称框中直接输入区域标号1.3公式的引用Excel高级应用•公式的复制1.利用单元格的填充柄拖曳复制拖曳复制时的标号相对自动调整：上下，行号减增；左右，列号减增2.对单元格的直接复制：在复制计算关系的同时，单元格标号相对自动调整小技巧--大范围的公式复制：①选定起始单元格(已有标准公式)②选定终止单元格③粘贴1.3公式的引用(续1)Excel高级应用•绝对引用与相对引用1.在公式复制时单元格标号能相对自动调整的引用称为相对引用；反之，在公式复制时单元格标号不能相对自动调整的引用称为绝对引用2.绝对引用标号的设置完全相对引用：D4完全绝对引用：$D$4行绝对引用：D$4列绝对引用：$D4小技巧—绝对引用与相对引用的切换：①选定需要改变的公式(不是只选定单元格)②按F4键,改变次序，完全绝对、行绝对、列绝对、完全相对1.3公式的引用(续2)Excel高级应用•公式的三维引用1.同一工作表内的引用(一维)，如=D2+F5；2.同一工作簿内的引用(二维)，如=D5+Sheet2!D5；3.不同工作簿内的引用(三维)，如1.3公式的引用(续3)=D5+Sheet2!D5+[1_工资管理.xls]明细表!$D$5三维引用：方括号中工作簿名称+工作表名+惊叹号+列号+行号二维引用：工作表名+惊叹号+列号+行号一维引用：列号+行号Excel高级应用•查找公式引用1.当前单元格公式引用了那些单元格：双击有公式的单元格，出现被引用单元格框选定有公式的单元格工具公式审核追踪引用单元格2.当前单元格被那些单元格引用：选定单元格工具公式审核追踪从属单元格1.3公式的引用(续4)Excel高级应用•活期储蓄利息查算表的编制•按百分比递增的销售计划的编制实例1：公式的引用Excel高级应用常见公式错误提示的说明(示例_1)1.#DIV/0：在公式中出现了除以0的错误2.#VALUE!：提供的数据类型不一致3.#REF!：单元格引用无效4.#NAME?：公式中的文字，系统不能识别5.#NUM!：参数输入值不正确6.#NULL!：指定的相交并没有发生7.#N/A：在公式中引用的一些数据不可用Excel高级应用单元格的常见方式1.单个单元格的引用，一个单元格标号，如：B3•2.一个区域的引用，相邻的多个单元组成的矩形区域(冒号)，如：B3：F5•3.多个独立单元格、或区域的引用，多个任意单元格(逗号)，如A1，B5，F6•4.两个区域的交叉引用(空格)，如：A1：E6D2：F8Excel高级应用关于公式设计的思考•循环引用问题的实际应用(见实例)•公式的计算顺序—手动重算Excel高级应用1.什么是计算机应用中函数？具有特定功能、以一个名称调用、一般需要提供一至几个参数，并返回一个处理结果如：sum(B1:B10)，计算单元格B1到B10的合计2.函数的组成名称+一对圆括号“()”和适当的参数，()不可省略3.函数的使用在公式中作为表达式的构成部分1.4函数基础Excel高级应用4.函数的输入(插入)①直接输入②利用函数粘贴工具(编辑栏工具fx、或菜单)插入fx函数要点：函数输入过程中的单元格引用“插入函数”向导的使用5.常用函数(示例_2)实例2：常用函数的应用电视歌手大奖赛评分表学生成绩计算及分析表1.4函数基础(续1)Excel高级应用补充内容1：数据有效性+条件格式•输入数据“有效性”控制的应用•“条件格式”的应用Excel高级应用第4章EXCEL函数及其实例•4.1函数概述•函数的功能：数据运算与管理•函数的嵌套：year(today())•数据的分类：数学及三角函数统计函数财务函数查找与引用函数文本函数逻辑函数信息函数Excel高级应用4.2财务函数•什么是EXCEL中的财务函数：有关货款、投资、债券或股票等的计算功能的函数•财务函数的要点：投资－－负数，收益－－正数•常见参数：p112•示例3Excel高级应用4.3日期与时间函数