Excel实用函数培训

内部文件，注意保密Office系列培训之——Excel2007/20102014年3月27日V11一．认识Excel二．数据处理三．公式与函数四．图表2一．认识Excel1.Excel知识结构2.Excel用户的五个层次3.Excel2007/2010界面3Excel数据处理与操作Excel函数与公式Excel图表与图形ExcelVBA与宏Excel数据分析一．认识Excel1.Excel知识结构4一．认识Excel1.Excel知识结构2.Excel用户的五个层次3.Excel2007/2010界面5刚刚开始接触新手基本掌握常用功能初级用户…+数组公式+VBA简单编程高级用户熟练使用常用功能+数据透视表+公式中级用户一．认识Excel2.Excel用户的五个层次Excel技能+专业知识+行业经验专家6一．认识Excel1.Excel知识结构2.Excel用户的五个层次3.Excel2007/2010界面7一．认识Excel3.Excel2007/2010界面8一．认识Excel二．数据处理三．公式与函数四．图表9二．数据处理1.自动填充2.定义名称3.数据有效性4.自定义格式5.条件格式6.合并计算7.分类汇总8.数据透视表10二．数据处理1.自动填充等差填充日期填充文字填充特定填充11二．数据处理1.自动填充2.定义名称3.数据有效性4.自定义格式5.条件格式6.合并计算7.分类汇总8.数据透视表12二．数据处理2.定义名称①插入名称公式→名称管理器→新建→输入参数②使用名称框命名选定单元格→点击名称框→定义名称13二．数据处理2.定义名称③命名规则全局定义与局部定义：1、默认情况下，为全局定义2、局部定义命名方法：工作表名称+半角感叹号+名称命名规则：1、可用数字与字母组合，但不能以数字开头；2、不能以R、C、r、c作为名称，如R、C在R1C1引用样式中表示行、列；3、名称中不能包含空格，可用下划线或点号代替；4、不能使用除了下换线、点号和反斜杠（\）意外的其他符号。允许使用问号，但不能作为名称的开头；5、名称字符不能超过255个；6、字母不区分大小写。14二．数据处理1.自动填充2.定义名称3.数据有效性4.自定义格式5.条件格式6.合并计算7.分类汇总8.数据透视表15二．数据处理3.数据有效性16二．数据处理3.数据有效性17二．数据处理3.数据有效性停止不能输入不符合条件的数据，点击“重试”可重新输入，点击“取消”则取消输入。警告可以选择是否继续输入数据，点击“是”可以强行输入不符合条件数据，点击“否”可以重新输入数据，点击“取消”则取消输入。信息可选择是否输入数据，点击“确定”可以直接输入数据，点击“取消”可以取消输入。18二．数据处理1.自动填充2.定义名称3.数据有效性4.自定义格式5.条件格式6.合并计算7.分类汇总8.数据透视表19二．数据处理4.自定义格式可为4种类型的数值指定不同的格式：正数、负数、零值和文本。“大于条件值”格式；“小于条件值”格式；“等于条件值”格式；文本格式注：没有特别指定条件值的时候，默认条件值为020二．数据处理4.自定义格式#,##0.00；[红色]-#,##0.00；[绿色]G/通用格式；““”@“””显示为原始数值说明1,023.401023.4正数，显示为带千分号、两位小数-1,023.00-1023负数，显示为带千分号、两位小数、红色00零值，显示为绿色“等于”等于文本，显示为文本两侧加双引号21二．数据处理4.自定义格式实战练习：编制设备编号，不足10位，编号前补0，并在10位编号前统一加“No：”。22二．数据处理1.自动填充2.定义名称3.数据有效性4.自定义格式5.条件格式6.合并计算7.分类汇总8.数据透视表23二．数据处理5.条件格式24二．数据处理1.自动填充2.定义名称3.数据有效性4.自定义格式5.条件格式6.合并计算7.分类汇总8.数据透视表25二．数据处理6.合并计算例：统计报销的金额/人次26二．数据处理1.自动填充2.定义名称3.数据有效性4.自定义格式5.条件格式6.合并计算7.分类汇总8.数据透视表27二．数据处理7.分类汇总可手动创建分级显示和自动生成分级显示。注意：1、一个工作表内在同行方向上或列方向上只能创建一个分级显示；2、在同一个分级显示中，最多允许有8个层次关系。28二．数据处理1.自动填充2.定义名称3.数据有效性4.自定义格式5.条件格式6.合并计算7.分类汇总8.数据透视表29二．数据处理8.数据透视表初步的数据统计与分析30一．认识Excel二．数据处理三．公式与函数四．图表31三．公式与函数1.什么是公式？什么是函数？2.相对引用与绝对引用3.解读函数的参数4.公式审核工具5.常见错误类型6.文本处理函数7.数字计算函数8.日期与时间函数9.常用函数与简单数组公式32三．公式与函数1.什么是公式？什么是函数？公式是以“=”为引导，通过运算符按照一定的顺序组合进行数据运算处理的等式。函数是按照特定的算法执行计算的产生一个或一组结果的预定义的特殊公式。序号公式说明1=15*3+20*2包含常量运算的公式2=A1*3+A2*2包含单元格引用的公式3=单价*数量包含名称的公式4=SUM(A1*3,A2*2)包含函数的公式33三．公式与函数1.什么是公式？什么是函数？2.相对引用与绝对引用3.解读函数的参数4.公式审核工具5.常见错误类型6.文本处理函数7.数字计算函数8.日期与时间函数9.常用函数与简单数组公式34三．公式与函数2.相对引用与绝对引用公式所在单元格与公式所引用单元格的位置关系称为单元格引用的相对性。A1样式R1C1样式特征$A$1R1C1绝对引用，向右向下复制公式不改变引用关系A$1R1C[*]行绝对列相对混合引用，向下复制公式不改变引用关系$A1R[*]C1行相对列绝对混合引用，向右复制公式不改变引用关系A1R[*]C[*]相对引用，向右向下复制公式均改变引用关系35三．公式与函数2.相对引用与绝对引用①引用其他工作表的数据=工作表名称！目标单元格例：=sheet2!A1②引用已打开的工作簿的数据=[工作簿名称]工作表名称！目标单元格例：=[Book2.xls]sheet1!$A$1③引用未打开的工作簿的数据=‘文件路径[工作簿名称]工作表名称’！目标单元格例：=‘D:\Excel案例[Book2.xls]sheet1’!$A$1练习：将多张工作表的数据汇总到一张工作表36三．公式与函数1.什么是公式？什么是函数？2.相对引用与绝对引用3.解读函数的参数4.公式审核工具5.常见错误类型6.文本处理函数7.数字计算函数8.日期与时间函数9.常用函数与简单数组公式37三．公式与函数3.解读函数的参数=IF(A10,SUM(B1:G1),””)函数的结构嵌套函数函数名称以逗号分隔的3个参数思考：哪些函数不带参数？？38三．公式与函数1.什么是公式？什么是函数？2.相对引用与绝对引用3.解读函数的参数4.公式审核工具5.常见错误类型6.文本处理函数7.数字计算函数8.日期与时间函数9.常用函数与简单数组公式39三．公式与函数4.公式审核工具使用公式审核工具分布求值以=IF(A10,SUM(B1:G1),””)为例，执行“公式求值1240三．公式与函数4.公式审核工具34541三．公式与函数1.什么是公式？什么是函数？2.相对引用与绝对引用3.解读函数的参数4.公式审核工具5.常见错误类型6.文本处理函数7.数字计算函数8.日期与时间函数9.常用函数与简单数组公式42三．公式与函数5.常见错误类型错误值类型含义#####当列宽不够显示数字，或者使用了负的日期或负的时间时，出现错误#VALUE!当使用的参数或操作数类型错误时，出现错误#DIV/0!当数字被零（0）除时，出现错误#NAME?当Excel未识别公式中的文本时，如未加载宏或定义名称，出现错误#N/A当数值对函数或公式不可用时，出现错误#REF!当单元格引用无效时，出现错误#NUM!公式或函数中使用无效数字值时，出现错误#NULL!当用空格表示两个引用单元格之间的相交运算符，但指定并不相交的两个区域的交点时，出现错误。43三．公式与函数1.什么是公式？什么是函数？2.相对引用与绝对引用3.解读函数的参数4.公式审核工具5.常见错误类型6.文本处理函数7.数字计算函数8.日期与时间函数9.常用函数与简单数组公式44三．公式与函数6.文本处理函数①连接符&用途：连接“&”前后的字符、字段A列B列C列ABX500120-E=A1&B1ABX500120-EABX500120-EA列B列ABX500120=A1&-EABX500120ABX500120-E45三．公式与函数6.文本处理函数②大小写字母转换Lower、Upper、Proper用途示例所有大写字母转为小写字母所有小写字母转为大写字母字符串首字母转为大写字母=LOWER(“ILOVEEXCEL”)=“iloveexcel”=UPPER(“iloveexcel”)=“ILOVEEXCEL”=UPPER(“I爱excel”)=“I爱Excel”46三．公式与函数6.文本处理函数③Char与CodeChar(65)=AChar(66)=BChar(67)=CChar(97)=aChar(98)=bChar(99)=cCode(“A”)=65思考：如何快速填充A-Z序号47三．公式与函数6.文本处理函数④提取字符函数Left、Right、Mid身份证号地区出生日期性别=left(a1,6)=mid(a1,7,6)=right(a1,1)3201027810014923201027810012员工代码社保号姓名=left(a1,10)=mid(a1,11,3)0002337084李明0002337084李明0000656246李小明0000656246李小明为什么是3而不是2？为什么不用Right函数？注意：MID函数有3个参数，而LEFT、RIGHT只有2个参数48三．公式与函数1.什么是公式？什么是函数？2.相对引用与绝对引用3.解读函数的参数4.公式审核工具5.常见错误类型6.文本处理函数7.数字计算函数8.日期与时间函数9.常用函数与简单数组公式49三．公式与函数7.数字计算函数①计算余数ModMod(3,2)=1Mod(15,4)=3Mod(21,7)=0……应用：身份证号判断性别？如何判断数字的奇偶性？思考：除2的余数有多少个？除3呢？50三．公式与函数7.数字计算函数②四舍五入Round数值公式显示结果123.456=round(A2,0)123123.456=round(A3,2)123.461234.56=round(A4,-2)1200区别51三．公式与函数7.数字计算函数③向上取整Roundup、向下取整Rounddown数值RoundupRounddown公式显示结果公式显示结果123.456=rounddown(A3,0)123=roundup(A3,0)124123.456=rounddown(A3,2)123.45=roundup(A3,2)123.461234.56=rounddown(A3,-2)1200=roundup(A3,-2)130052三．公式与函数1.什么是公式？什么是函数？2.相对引用与绝对引用3.解读函数的参数4.公式审核工具5.常见错误类型6.文本处理函数7.数字计算函数8.日期与时间函数9.常用函数与简单数组公式53三．公式与函数8.日期与时间函数①四个基础日期函数日期年月日=Year(a1)=Month(a1)=Day(a1)2009-6-162009616年月日日期=DATE(A1,B1,C1)20096162009-6-16特殊函数：Today()，读取计算机设置的今日日期，无参数54三．公式与函数8.日期与时间函数实战练习：快速计算退休日期（假设：男60退休、女55退休）姓名性别出生日期部门退休日期艾笑贻女1955-6-20