Excel实用函数培训

Office系列培训之

——Excel2007/20212021年3月27日V1认识Excel数据处置公式与函数图表1认识ExcelExcel知识构造Excel用户的五个层次Excel2007/2021界面2Excel数据处置与操作Excel函数与公式Excel图表与图形ExcelVBA与宏Excel数据分析认识ExcelExcel知识构造3认识ExcelExcel知识构造Excel用户的五个层次Excel2007/2021界面4刚刚开场接触新手根本掌握常用功能初级用户…+数组公式+VBA简单编程高级用户熟练运用常用功能+数据透视表+公式中级用户认识ExcelExcel用户的五个层次Excel技艺+专业知识+行业阅历专家5认识ExcelExcel知识构造Excel用户的五个层次Excel2007/2021界面6认识ExcelExcel2007/2021界面7认识Excel数据处置公式与函数图表8数据处置自动填充定义称号数据有效性自定义格式条件格式合并计算分类汇总数据透视表9数据处置自动填充等差填充日期填充文字填充特定填充10数据处置自动填充定义称号数据有效性自定义格式条件格式合并计算分类汇总数据透视表11数据处置定义称号①插入称号公式→称号管理器→新建→输入参数②运用称号框命名选定单元格→点击称号框→定义称号12数据处置定义称号命名规那么全局定义与部分定义：1、默许情况下，为全局定义2、部分定义命名方法：任务表称号+半角感慨号+称号命名规那么：1、可用数字与字母组合，但不能以数字开头；2、不能以R、C、r、c作为称号，如R、C在R1C1援用款式中表示行、列；3、称号中不能包含空格，可用下划线或点号替代；4、不能运用除了下换线、点号和反斜杠〔\〕不测的其他符号。允许运用问号，但不能作为称号的开头；5、称号字符不能超越255个；6、字母不区分大小写。13数据处置自动填充定义称号数据有效性自定义格式条件格式合并计算分类汇总数据透视表14数据处置数据有效性15数据处置数据有效性16数据处置数据有效性停顿不能输入不符合条件的数据，点击“重试〞可重新输入，点击“取消〞那么取消输入。警告可以选择能否继续输入数据，点击“是〞可以强行输入不符合条件数据，点击“否〞可以重新输入数据，点击“取消〞那么取消输入。信息可选择能否输入数据，点击“确定〞可以直接输入数据，点击“取消〞可以取消输入。17数据处置自动填充定义称号数据有效性自定义格式条件格式合并计算分类汇总数据透视表18数据处置自定义格式可为4种类型的数值指定不同的格式：正数、负数、零值和文本。“大于条件值〞格式；“小于条件值〞格式；“等于条件值〞格式；文本格式注：没有特别指定条件值的时候，默许条件值为019数据处置自定义格式#,##0.00；[红色]-#,##0.00；[绿色]G/通用格式；““〞@“〞〞显示为原始数值说明1,023.401023.4正数，显示为带千分号、两位小数-1,023.00-1023负数，显示为带千分号、两位小数、红色00零值，显示为绿色“等于”等于文本，显示为文本两侧加双引号20数据处置自定义格式实战练习：编制设备编号，缺乏10位，编号前补0，并在10位编号前一致加“No：〞。21数据处置自动填充定义称号数据有效性自定义格式条件格式合并计算分类汇总数据透视表22数据处置条件格式23数据处置自动填充定义称号数据有效性自定义格式条件格式合并计算分类汇总数据透视表24数据处置合并计算例：统计报销的金额/人次25数据处置自动填充定义称号数据有效性自定义格式条件格式合并计算分类汇总数据透视表26数据处置分类汇总可手动创建分级显示和自动生成分级显示。留意：1、一个任务表内在同行方向上或列方向上只能创建一个分级显示；2、在同一个分级显示中，最多允许有8个层次关系。27数据处置自动填充定义称号数据有效性自定义格式条件格式合并计算分类汇总数据透视表28数据处置数据透视表初步的数据统计与分析29认识Excel数据处置公式与函数图表30公式与函数什么是公式？什么是函数？相对援用与绝对援用解读函数的参数公式审核工具常见错误类型文本处置函数数字计算函数日期与时间函数常用函数与简单数组公式31公式与函数什么是公式？什么是函数？公式是以“=〞为引导，经过运算符按照一定的顺序组合进展数据运算处置的等式。函数是按照特定的算法执行计算的产生一个或一组结果的预定义的特殊公式。序号公式说明1=15*3+20*2包含常量运算的公式2=A1*3+A2*2包含单元格引用的公式3=单价*数量包含名称的公式4=SUM(A1*3,A2*2)包含函数的公式32公式与函数什么是公式？什么是函数？相对援用与绝对援用解读函数的参数公式审核工具常见错误类型文本处置函数数字计算函数日期与时间函数常用函数与简单数组公式33公式与函数相对援用与绝对援用公式所在单元格与公式所援用单元格的位置关系称为单元格援用的相对性。A1样式R1C1样式特征$A$1R1C1绝对引用，向右向下复制公式不改变引用关系A$1R1C[*]行绝对列相对混合引用，向下复制公式不改变引用关系$A1R[*]C1行相对列绝对混合引用，向右复制公式不改变引用关系A1R[*]C[*]相对引用，向右向下复制公式均改变引用关系34公式与函数相对援用与绝对援用①援用其他任务表的数据=任务表称号！目的单元格例：=sheet2!A1②援用已翻开的任务簿的数据=[任务簿称号]任务表称号！目的单元格例：=[Book2.xls]sheet1!$A$1③援用未翻开的任务簿的数据=‘文件途径[任务簿称号]任务表称号’！目的单元格例：=‘D:\Excel案例[Book2.xls]sheet1’!$A$1练习：将多张任务表的数据汇总到一张任务表35公式与函数什么是公式？什么是函数？相对援用与绝对援用解读函数的参数公式审核工具常见错误类型文本处置函数数字计算函数日期与时间函数常用函数与简单数组公式36公式与函数解读函数的参数=IF(A1>0,SUM(B1:G1),〞〞)函数的构造嵌套函数函数称号以逗号分隔的3个参数思索：哪些函数不带参数？？37公式与函数什么是公式？什么是函数？相对援用与绝对援用解读函数的参数公式审核工具常见错误类型文本处置函数数字计算函数日期与时间函数常用函数与简单数组公式38公式与函数公式审核工具 运用公式审核工具分布求值 以=IF(A1>0,SUM(B1:G1),〞〞)为例，执行“公式求值1239公式与函数公式审核工具34540公式与函数什么是公式？什么是函数？相对援用与绝对援用解读函数的参数公式审核工具常见错误类型文本处置函数数字计算函数日期与时间函数常用函数与简单数组公式41公式与函数常见错误类型错误值类型含义#####当列宽不够显示数字，或者使用了负的日期或负的时间时，出现错误#VALUE!当使用的参数或操作数类型错误时，出现错误#DIV/0!当数字被零（0）除时，出现错误#NAME?当Excel未识别公式中的文本时，如未加载宏或定义名称，出现错误#N/A当数值对函数或公式不可用时，出现错误#REF!当单元格引用无效时，出现错误#NUM!公式或函数中使用无效数字值时，出现错误#NULL!当用空格表示两个引用单元格之间的相交运算符，但指定并不相交的两个区域的交点时，出现错误。42公式与函数什么是公式？什么是函数？相对援用与绝对援用解读函数的参数公式审核工具常见错误类型文本处置函数数字计算函数日期与时间函数常用函数与简单数组公式43公式与函数文本处置函数衔接符&用途：衔接“&〞前后的字符、字段A列B列C列ABX500120-E=A1&B1ABX500120-EABX500120-EA列B列ABX500120=A1&"-E"ABX500120ABX500120-E44公式与函数文本处置函数大小写字母转换Lower、Upper、Proper 用途 例如一切大写字母转为小写字母一切小写字母转为大写字母字符串首字母转为大写字母=LOWER(“ILOVEEXCEL〞)=“iloveexcel〞=UPPER(“iloveexcel〞)=“ILOVEEXCEL〞=UPPER(“I爱excel〞)=“I爱Excel〞45公式与函数文本处置函数Char与CodeChar(65)=AChar(66)=BChar(67)=CChar(97)=aChar(98)=bChar(99)=cCode(“A〞)=65思索：如何快速填充A-Z序号46公式与函数文本处置函数提取字符函数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个参数47公式与函数什么是公式？什么是函数？相对援用与绝对援用解读函数的参数公式审核工具常见错误类型文本处置函数数字计算函数日期与时间函数常用函数与简单数组公式48公式与函数数字计算函数计算余数ModMod(3,2)=1Mod(15,4)=3Mod(21,7)=0……运用：身份证号判别性别？如何判别数字的奇偶性？思索：除2的余数有多少个？除3呢？49公式与函数数字计算函数四舍五入Round数值公式显示结果123.456=round(A2,0)123123.456=round(A3,2)123.461234.56=round(A4,-2)1200区别50公式与函数数字计算函数向上取整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)130051公式与函数什么是公式？什么是函数？相对援用与绝对援用解读函数的参数公式审核工具常见错误类型文本处置函数数字计算函数日期与时间函数常用函数与简单数组公式52公式与函数日期与时间函数四个根底日期函数日期年月日

=Year(a1)=Month(a1)=Day(a1)2009-6-162009616年月日日期

=DATE(A1,B1,C1)20096162009-6-16特殊函数：Today()，读取计算机设置的今日日期，无参数53公式与函数日期与时间函数实战练习：快速计算退休日期〔假设：男60退休、女55退休〕姓名性别出生日期部门退休日期艾笑贻女1955-6-20综合部

安文博男1961-7-31综合部

白露女1979-5-13综合部

谢子坚男1973-10-19财务部

卜娟芳女1978-12-29财务部

蔡星媛女1959-7-10财务部

仓晟义男1978-11-20信息部

曹昱廷男1965-2-14信息部

54公式与函数日期与时间函数Datedif函数用途：计算两个日期之间的天数、月数或年数注：此函数在Excel协助文件中没有。构造：Datedif(start_date,end_date,unit)unit代码函数返回值"y"时间段中的整年数"m"时间段中的整月数"d"时间段中的天数"md"忽略日期中的年和月，计算天数差"ym"忽略日期中的年和日，计算月数差"yd"忽略日期中的年，计算天数差55公式与函数日期与时间函数Datedif函数开始日期结束日期公式显示结果2000-6-152009-6-16=datedif(A2,B2,"y")92000-6-172009-6-16=datedif(A3,B3,"y")82008-6-152009-6-16=datedif(A4,B4,"m")122008-6-172009-6-16=datedif(A5,B5,"m")112008-6-152009-6-16=datedif(A6,B6,"d")3662008-6-172009-6-16=datedif(A7,B7,"d")3642008-3-152009-6-16=datedif(A8,B8,"md")12008-3-172009-6-16=datedif(A9,B9,"md")302008-3-152009-6-16=datedif(A10,B10,"ym")32008-6-172009-6-16=datedif(A11,B11,"ym")112008-6-152009-6-16=datedif(A12,B12,"yd")12008-6-172009-6-16=datedif(A13,B13,"yd")36456公式与函数什么是公式？什么是函数？相对援用与绝对援用解读函数的参数公式审核工具常见错误类型文本处置函数数字计算函数日期与时间函数常用函数与简单数组公式57公式与函数常用函数与简单数组公式If函数用途：按指定的条件计算满足条件选项语法构造：=If〔断定条件，假设满足条件，否那么〕例如1：对现库存小于平安库存的备注缺件隐患。A列B列C列D列=If（C列<B列,"缺件隐患","正常")件号安全库存现库存备注ABX46001010080缺件隐患AFE450030100160正常ALA5001404060正常EUH10025012082缺件隐患58公式与函数常用函数与简单数组公式If函数例如2：对月出勤超越22天的员工做“全勤〞备注,低于22天的显示实践出勤天数。A列B列C列D列=If(C列>=22,"全勤",C列)序号姓名出勤备注1张三20202李四23全勤3王五21214贾六22全勤=If〔C1>=22,"全勤",C1〕59公式与函数常用函数与简单数组公式Lookup函数用途：按列/行的方式查询目的值。构造：Vlookup、Hlookup函数=Vlookup〔需查找值，范围，相对列，0〕Lookup函数=Lookup(需查找值，对应值区域，目的值区域〕LOOKUP函数要求分级区间是升序陈列。60公式与函数常用函数与简单数组公式条件判别〔Sumif、Cou