excel高效数据处理PPT文档格式.pptx

1、计算结果太长,宽度不够,或者是包含无效的日期#DIV/0公式中引用了一个空单元格,或者除数为0#N/A公式中没有可用的数值,或者缺少函数参数#NAME?公式中引用了一个不可用的数据或者参数#NULL!进行了不正确的单元格引用或者区域运算#NUM使用了无效的数值.或者计算结果太大或者太小#REF应用了无效的单元格#VALUE!错误地引用了单元格或引用了错误的数值北京杰飞电脑培训中心北京杰飞电脑培训中心文本函数的使用LEFT,RIGHT:从字符串的最左端位置提取指定数量的字符;LEN返回字符串的个数LOWER将所有字母转换为小写字母UPPER将所有字母转换为大写字母PROPER:所有字母转换为首字

2、母大写FIND和SEARCH函数:文本查找函数,都是用于定位某一个字符吕在指定的字符串中的起始位置的序号示例:从编码中提取合同号=RIGHT（A21,LEN（A21）-SEARCH（A,A21,8）+1）提取账户=LEFT（B30,FIND（,B30）-1）EXACT比较两个字符串：如果它们完全相同=IF（EXACT（B39,C39）=FALSE,B39-C39,EXACT（B39,C39）北京杰飞电脑培训中心北京杰飞电脑培训中心文本函数substitute函数是替换字符串的函数，用法如下：substitute（字符串，替换的部分替换后的,从第几个开始替换）例：substitute（A-B-C

3、C,-,）就可以把-替换为空格即：A B CC“获取有多少个人员上课=LEN（B2）-LEN（SUBSTITUTE（B2,）+1合并订单号=CONCATENATE（A2,B2,C2,D2）REPT按照给定的次数重复显示文本。可以通过函数 REPT 来不断地重复显示某一文本字符串，对单元格进行填充。让身份证号码前六位隐藏显示=CONCATENATE（REPT（“*”,6）,RIGHT（C12,12）选择性粘贴数据从单元格中提取有用数据并合并起来=CONCATENATE（LEFT（C19,3）,A19,IF（B19=男,先生,女士）北京杰飞电脑培训中心北京杰飞电脑培训中心函数的使用北京杰飞电脑培训

4、中心北京杰飞电脑培训中心函数的使用MID函数返回文本字符串从指定位置开始的特定数目的字符；IF函数用于执行真假值判断。MOD函数的功能返回两个数相除的余数；DATE函数的功能返回代表特定日期的序列；1、判断性别：IF（MOD（MID（E18,17,1）,2）=0,女,男）2、出生日期=DATE（MID（E18,7,4）,MID（E18,11,2）,MID（E18,13,2）北京杰飞电脑培训中心北京杰飞电脑培训中心函数的使用TODAY函数返回当前的日期 TODAY（）NOW函数返回当前的时间YEAR函数返回某个日期对应的年份；DATEDIF返回两个日期相差的天数，月数与年数；ROUNDDOWN向

5、下舍入数字年龄：=DATEDIF（G18,TODAY（）,Y）；工龄：=ROUNDDOWN（NOW（）-K18）/365,0）合同到期：DATE（YEAR（M18）+1,MONTH（M18）,DAY（M18）-1）提前三十天提醒=IF（N18-TODAY（）=DAY（TODAY（）,还有&DAY（G18）-DAY（TODAY（）&天过生日,）北京杰飞电脑培训中心北京杰飞电脑培训中心逻辑函数使用1.IF函数是根据指定条件来判断其”真”假,而返回相应的内容.1.示例:判断数值是否在上限与下限之间=IF（D3C3,在,不在）,不在）1.示例:IF与left结合:根据代码返回部门名称=IF（LEFT（

6、A2,1）=x,销售部,IF（LEFT（A2,1）=s,生产部,IF（LEFT（A2,1）=y,研发部）2.AND函数:一般用来检验一组数据是否满足条件.1.示例:考评成绩是否合格=IF（AND（B360,C360,D360）,合格,不合格）3.OR函数:用来检验一组数据是否都不满足条件.只有一个满足条件结果为真1.示例:对员工考核成绩进行综合评定=IF（OR（B260,C260,D260）,参加培训,考核通过“）北京杰飞电脑培训中心北京杰飞电脑培训中心逻辑函数使用1、示例:快速识别产品类别；2、根据产品名称与产品颜色进行一次性调价IF（NOT（LEFT（A4,3）=洗衣机）,原价,IF（AN

7、D（LEFT（A4,3）=洗衣机,NOT（B4=白色）,C4+200,C4+50）3、解决计算结果为0错误值的问题=IF（OR（C4=,D4=）,C4/D4）4、IF函数计算个人所得税=IF（B3=1000,0,IF（B3=5000,（B3-1000）*0.1,IF（B3=10000,（B3-5000）*0.15+25,IF（B3=20000,（B3-10000）*0.2+125,（B3-20000）*0.25+375）北京杰飞电脑培训中心北京杰飞电脑培训中心数学函数统计不同时间段,不同产品类别的销售额=SUM（$A$2:$A$112012-3-15,C18:C27）统计两种或多种类别产品总销

8、售额=SUM（SUMIF（B17:B26,男士毛衣,女士毛衣,C17:C26）北京杰飞电脑培训中心北京杰飞电脑培训中心数学函数SUMIFS实现多条件统计=SUMIFS（$C$17:$C$26,$A$17:$A$26,800）统计指定店面指定类别的销售金额合计值=SUMPRODUCT（A27:A36=1）*（B27:B36=男式毛衣）*（C27:C36）INT函数对平均销售量取整=INT（AVERAGE（B2:B5）Round进行四舍五入=ROUND（AVERAGE（B13:B17）,1）北京杰飞电脑培训中心北京杰飞电脑培训中心统计函数平均值函数求平均值是忽略计算区域中的0值.=AVERAGE（

9、IF（B3:B80,B3:B8）指定条件求平均值=AVERAGE（IF（$A$3:$A$12=F8,$C$3:$C$12）同时满足多个条件的数据求平均值,一班,值不为0的=AVERAGE（IF（A2:A11=1）*（C2:C110）,C2:C11）北京杰飞电脑培训中心北京杰飞电脑培训中心统计函数统计指定月份的平均值销售金额,想看每个销售人员2,4,6月销售额（隔行求）=AVERAGE（IF（MOD（COLUMN（A3:G3）,2）=0,B3:G3）包含文本的平均值AVERAGEA=AVERAGEA（B2:D2）指定班级的平均成绩=AVERAGEIF（$A$3:$A$12,E7,$C$3:$C$

10、12）Averageif使用通配符=AVERAGEIF（A2:A7,*西部,B2:B7）=AVERAGEIF（A2:A7,“*（新售点）”,B2:B7）要注意新售点在什么输入法状态下输入的。北京杰飞电脑培训中心北京杰飞电脑培训中心统计函数AVERAGEIFS：排除无效的，测试。并计算平均值。=AVERAGEIFS（B6:B13,B6:B13,=1.8,B6:B13,=3.1）求指定班级的平均并忽略0值=AVERAGEIFS（$C$2:$C$11,$A$2:$A$11,E6,$C$2:$C$11,0）AVERAGEIFS使用通配符=AVERAGEIFS（B2:B7,A2:A7,*西部,A2:A7

11、,*（新售点）北京杰飞电脑培训中心北京杰飞电脑培训中心统计函数计算选手的最后得分=TRIMMEAN（B2:B11,0.2）计算销量的几何平均值=GEOMEAN（B2:B7）统计销售记录条数=COUNT（C:C）使用COUNT函数与COUNTBLANK来统计空白单元格数目，示例为：缺席与应到人数返回参数列表中非空值的单元格个数=COUNTA（A3:A13）统计非空单元格个数=COUNTBLANK（B3:B13）北京杰飞电脑培训中心北京杰飞电脑培训中心统计函数COUNTIF计算区域中满足给定条件的单元格的个数。统计各产品的销售记录=COUNTIF（$B$2:$B$11,G9）统计出大于指定数值的记

12、录条数：=COUNTIF（B2:B11,=50000）统计出大于某个单元格数值的记录条数=COUNTIF（$B$2:$B$11,=&E8）返回大于平均值的记录数=COUNTIF（B2:B11,&AVERAGE（B2:B11）北京杰飞电脑培训中心北京杰飞电脑培训中心统计函数 countifs统计指定区域中满足多个条件的记录数查看上半个月男士毛衣的销售个数=COUNTIFS（B2:B11,F8,A2:A11,=F4,0,C2:C11）求最小值时忽略0值=MIN（IF（B2:B110,B2:B11）自己动手做包含文本的最大与最小值，函数分别为MAXA,MINA排位统计函数：对销售业绩进行排名：=RA

13、NK（B10,$B$2:$B$11）对不连续单元格进行排名次:=RANK（B20,（B20,B24,B28,B32）返回数值在一个数据集中的百分比排位=PERCENTRANK（$B$2:$B$11,B2,3）PERCENTILE返回数值区域的K百分比数值点=PERCENTILE（B2:B11,0.9）QUARTILE求取四分位数=QUARTILE（B2:B11,0）北京杰飞电脑培训中心北京杰飞电脑培训中心查找引用函数LOOKUP函数进行查询:在销售管理表等数据表中,通常都需要进行大量的数据查询操作.实现输入编号即可查询相应的信息查询指定编号人员信息第一种公式:向量:指在单行区域或单列区域（向量

14、）中查找值.然后返回第二个单行区域或单列区域中相同位置的值:=LOOKUP（$A$12,$A$2:$A$7,B$2:B$7）第二种是数组型,第一行或第一列中查找指定的数值.然后返回最后一行或最后一列中相同位置处的数值.=LOOKUP（$A$13,$A2:B7）北京杰飞电脑培训中心北京杰飞电脑培训中心查找引用函数HLOOKUP函数:用于在表格或数值数组的首行查找指定的数值,并返回表格或数组当前列中指定行处的数值.如下示例:根据不同的值班类别建立工资标准表.=HLOOKUP（E11,$A$3:$F$4,2,0）HLOOKUP实现查询=HLOOKUP（$J$2,$C$1:$F$13,ROW（A2）,

15、FALSE）VLOOKUP函数:用于在表格或数值数组的首列查找指定的数值,并返回表格或数组当前行中指定列处的数值=VLOOKUP（$A$12,$A$2:$D$7,COLUMN（B1）,0）=VLOOKUP（H2,$D$2:$E$10,2,0）实现合并两张表北京杰飞电脑培训中心北京杰飞电脑培训中心查找引用函数MATCH返回在指定方式下与指定数值匹配的数组;使用MATCH函数返回指定元素所在位置.=MATCH（A11,B1:B7,0）;=MATCH（A12,A1:D1,0）INDEX函数实现查找:返回数据清单或数组中的元素值,此元素由行序号和列序号索引值给定.如成绩表中指定条件查找学生某项成绩.=INDEX（B2:F13,6,4）;自己