Excel2003高级培训-公式和函数

1第二篇使用公式和函数第二篇使用公式和函数7、公式和函数的基础8、使用命名公式-名称9、文本处理技术10、数据计算11、日期和时间计算12、查找与引用13、统计与求和27、公式和函数的基础运算符：符号优先级说明实例-2算术运算符：负号=8*-5=-40%3算术运算符：百分号=60*%5=3^4算术运算符：乘幂=3^2=916^(1/2)*和/5算术运算符：乘和除=3*2/4=1.5+和-6算术运算符：加和减=、、、、=、=8比较运算符：等于、不等于、大于、小于、大于等于、小于等于=(A1=A2)=(B1ABC)=(C1=5)&7文本运算符：连接文本=AB&BC=ABBC:1区域运算符：冒号=SUM(A1:A10)空格1交叉运算符：单个空格=SUM(A1:B5A4:D9)相当于=SUM(A4:B5)，1联合运算符：逗号=RANK(A1,(A1:A10,C1:C10)返回大小排位37、公式和函数的基础相对引用、绝对引用、混合引用相对引用：复制公式时，EXCEL自动调整目标单元格的引用。绝对引用：复制公式时，源单元格的引用不变。混合引用：以上两种引用同时出现。47、公式和函数的基础函数和公式定义函数：EXCEL内部预先定义并按照特定的顺序、结构来执行计算、分析等数据处理任务的功能模块。公式：用户自行设计并结合常量数据、单元格引用、运算符号等元素进行的数据处理和计算的算式。例如：公式：=（A1+A2+A3+A4+A5）/5函数：=AVERAGE（A1：A5）函数的易失性：每次打开工作表，都需要重新计算随机函数：RAND、RANDBETWEEN日期函数：TODAY、NOW引用函数：INDIRECT、OFFSET部分信息类函数：CELL、INFO58、使用公式命名—名称名称的概念：用户自行设计并能够进行数据处理的算式。名称可在函数和公式中调中。名称的优势：增强公式的可读性和便于公式修改；有利于简化公式；代替辅助工作表区域；突破函数嵌套限制；名称的分类：工作簿级名称（同一工作簿任意工作表中使用）；名称格式：工作簿名+！+名称工作表级名称（只在同一工作表中使用）；名称格式：工作表名+！+名称使用名称框定义：69、文本处理技术文本是EXCEL的主要数据类型之一，也是用户在工作中经常接触到的内容，本节主要介绍如何利用EXCEL提供的文本函数来对此类数据进行处理。9.1、文本的合并将多个文本连接生成新的文本字符串，可以利用CONCATENATE函数进行处理=CONCATENATE（‘AAA’，‘BBB’）=‘AAABBB’CONCATENATE的参数不能多于30个，这些内容帮助上可以查询到。可以用&来处理=‘AAA’&’BBB’=‘AAABBB’79、文本处理技术9.2文本比较文本数据也是可以比较的如：“”exact()示例：89、文本处理技术9.3字符转换应用大小写转换：◆LOWER()转换成小写◆UPPER()转换成大写◆PROPER()转换成首字母大写全角半角转换:◆ASC()将所有字符转换为半角字符◆WIDCHAR()将所有字符转换为全角字符可换行的文:CHAR(10)如:”welcometo”&char(10)&”china”显示为welcometochina99、文本处理技术9.4常用提取字符的函数LEFT()左侧提取MID()从字符串中间任意位置提取字符串RIGHT()右侧提取指定长度的字符串LEN()取字符串的长度例:利用MID函数提取身份证号码中的8位生日数字109、文本处理技术9.5文本查找:◆FIND()用于定位某一字符串在指定的字符串中的起始位置◆SEARCH()用于定位某一字符串在指定的字符串中的起始位置不同:FIND()区分大小写;SEARCH()支持通配符例:利用文本查找函数进行模糊查找FIND(find_text,within_text,start_num)FINDB(find_text,within_text,start_num)Find_text是要查找的文本。Within_text是包含要查找文本的文本。Start_num指定开始进行查找的字符。within_text中的首字符是编号为1的字符。如果忽略start_num，则假设其为1。119、文本处理技术9.6字符串的分离很多时候,用户需要进行拆分的数据都具有一定的共性,对于这样的数据进行拆分,可以使用查找函数结合提取字符串函数来处理.例:使用查找函数拆分空格分隔的数据129、文本处理技术9.7统计字符串中指定字符的个数单元格文本为“MicrosoftOfficeSystem”那么如何统计单元格文本中字母“o”出现的次数=LEN(“MicrosoftOfficeSystem”)–LEN(SUBSTITUTE(“MicrosoftOfficeSystem”,“o”,“”))由于SUBSTITUTE函数区分大小写,因此,以上公式只会返回小写字母“o”的出现的次数2语法:SUBSTITUTE(text,old_text,new_text,instance_num)Text为需要替换其中字符的文本，或对含有文本的单元格的引用。Old_text为需要替换的旧文本。New_text用于替换old_text的文本。Instance_num为一数值，用来指定以new_text替换第几次出现的old_text。如果指定了instance_num，则只有满足要求的old_text被替换；否则将用new_text替换Text中出现的所有old_text。1310、数学计算学习要点:要点1:了解取余函数及应用要点2:常用舍入函数介绍要点3:随机函数的应用要点4:认识求和类函数1410、数学计算10.1取余函数在数学概念中,被除数与除数进行整除运算后余的数值被称为余数,其特征是余数必定小于除数.例如10除以3余数为1.EXCEL的取余函数MOD函数计算两数相除后的余数非常方便.示例:利用MOD函数取余如果求19除以7的余数,可以用下面的公式:=MOD(19,7)结果为5如果正好整除则余数为0-2除以7的余数是?2除以-7的余数是?余数的符号与除数一致.1510、数学计算10.1.1判断奇偶性利用余数小于除数的原理，当用一个数值对2进行取余操作时，结果就只能得到0或1。在实际工作中，可以利用此原理来判断数值的奇偶性示例：得用ＭＯＤ函数计算数值的奇偶性．＝IF(MOD(23,2)=1,”奇数”,”偶数”)1610、数学计算10.2数据取舍函数在对数值的处理中,经常会碰到进位和舍去的情况,EXCEL提供了如下取舍函数函数名称功能描述INT取整函数,将数字向下取舍为最接近的数值TRUNC将数字截尾取整ROUNDDOWN将数字朝零的方向舍入ROUNDUP将数字朝远离零的方向舍入CEILING将数字沿绝对值增大的方向舍入为最接近的SINGIFICANCE的倍数FLOOR将数字沿绝对值减小的方向舍入为最接近的SINGIFICANCE的倍数EVEN将数字向上舍入为最接近的偶数ODD将数字向上舍入为最接近的奇数ROUND将数字四舍五入到指定的位数注:显示精度1710、数学计算10.2.1INT和TRUNC函数◆INT函数用于取得不大于目标值的最大整数,TRUNC是对目标值进行截取◆INT函数只能取到整数位,TRUNC则可以取得小数位数例子:1.对于数值15.38进行取整=INT(15.38)=15=TRUNC(15.38)=152.对于负数8.72进行取整=INT(-8.72)=-9=TRUNC(-8.72)=-81810、数学计算10.2.2ROUNDUP和ROUNDDOWN函数UP向上DWON向下例子:对于数值123.456若保留两位小数,两个函数处理结果不同=ROUNDUP(123.456,2)=123.46=ROUNDDOWN(123.456)=123.45ROUNDDOWN向下取舍同于TRUNC1910、数学计算10.2.3ROUND函数ROUND函数用于返回指定小数位数的四舍五入运算.例:对于数值12.456进行四舍五入保留两位小数为123.46=ROUND(12.456)=12.46对于数值1234.56,若要四舍五入到1200=ROUND(1234.56,-2)=12002010、数学计算10.3随机函数在许多应用中用户需要得到一些不确定的数,这种情况下会使用到随机函数.EXCEL提供的随机函数为RAND(),它返回0-1之间的数值,这个函数具有易失性.例:2110、数学计算10.4SUM求和函数SUM求和函数用于数值求和.例:2211、日期和时间计算11.1年月日函数TODAY()和NOW()用于生成当前的日期和时间DATE函数:返回具体的日期YEAR,MONTH,DAY:从日期数据中提取年份,月份,日期;2312、查找与引用12.1查找与引用函数主要讲一下VLOOKUP():主要的查询函数MATCH():查找定位函数ROW():行号COLUMN():列号2413、统计与求和统计函数是EXCEL中使用比较多的一类函数.13.1基本的统计函数COUNT:针对数据表中的数值进行计数COUNTA:主要用于统计非空单元格的个数COUNTBLANK:统计数据表中空单元格的个数,也包括有空字符的单元格2513、统计与求和13.2COUNTIF条件统计函数语法COUNTIF(range,criteria)Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为32、32、32或apples。2613、统计与求和13.3SUMIF条件求和函数语法SUMIF(range,criteria,sum_range)Range为用于条件判断的单元格区域。Criteria为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为32、32、32或apples。Sum_range是需要求和的实际单元格。AB属性值佣金100,0007,000200,00014,000300,00021,000400,00028,000公式说明（结果）=SUMIF(A2:A5,160000,B2:B5)属性值超过160,000的佣金的和(63,000)2713、统计与求和13.4极值应用函数MAX:取得数据最大值MIN:取得数据最小值LARGE:返回指定第N个最大值SMALL:取得指定第N个最小值Rank排名2813、统计与求和13.5SUBTOTAL函数返回列表或数据库中的分类汇总。通常，使用“数据”菜单中的“分类汇总”命令可以容易地创建带有分类汇总的列表。一旦创建了分类汇总，就可以通过编辑SUBTOTAL函数对该列表进行修改。SUBTOTAL(function_num,ref1,ref2,...)Function_num为1到11（包含隐藏值）或101到111（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算。Function_num（包含隐藏值）（Function_num忽略隐藏值）函数1101AVERAGE2102COUNT3103COUNTA4104MAX5105MIN6106PRODUCT7107STDEV8108STDEVP9109SUM10110VAR11111VARP2913、统计与求和13.6offset函数以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。