第二篇 使用公式和函数,第二篇 使用公式和函数 7、公式和函数的基础 8、使用命名公式-名称 9、文本处理技术 10、数据计算 11、日期和时间计算 12、查找与引用 13、统计与求和,7、公式和函数的基础,运算符：,7、公式和函数的基础,相对引用、绝对引用、混合引用 相对引用：复制公式时，EXCEL自动调整目标单元格的引用。 绝对引用：复制公式时，源单元格的引用不变。 混合引用：以上两种引用同时出现。,7、公式和函数的基础,函数和公式定义 函数：EXCEL内部预先定义并按照特定的顺序、结构来执行计算、分析等数据处理任务的功能模块。 公式：用户自行设计并结合常量数据、单元格引用、运算符号等元素进行的数据处理和计算的算式。 例如：公式：=（A1+A2+A3+A4+A5）/ 5 函数：=AVERAGE（A1：A5） 函数的易失性： 每次打开工作表，都需要重新计算 随机函数 ：RAND、RANDBETWEEN 日期函数：TODAY、NOW 引用函数：INDIRECT、OFFSET 部分信息类函数：CELL、INFO,8、使用公式命名名称,名称的概念：用户自行设计并能够进行数据处理的算式。名称可在函数和公式中调中。 名称的优势：增强公式的可读性和便于公式修改； 有利于简化公式； 代替辅助工作表区域； 突破函数嵌套限制； 名称的分类：工作簿级名称（同一工作簿任意工作表中使用）； 名称格式：工作簿名+ ！+名称 工作表级名称（只在同一工作表中使用）； 名称格式：工作表名+ ！+名称 使用名称框定义：,9、文本处理技术,文本是EXCEL的主要数据类型之一，也是用户在工作中经常接触到的内容，本节主要介绍如何利用EXCEL提供的文本函数来对此类数据进行处理。 9.1、文本的合并 将多个文本连接生成新的文本字符串，可以利用CONCATENATE函数进行处理 = CONCATENATE（AAA，BBB）=AAABBB CONCATENATE的参数不能多于30个，这些内容帮助上可以查询到。 可以用&来处理 =AAA&BBB=AAABBB,9、文本处理技术,9.2 文本比较 文本数据也是可以比较的 如：“” exact() 示例：,9、文本处理技术,9.3 字符转换应用 大小写转换：LOWER() 转换成小写 UPPER()转换成大写 PROPER()转换成首字母大写 全角半角转换: ASC() 将所有字符转换为半角字符 WIDCHAR()将所有字符转换为全角字符 可换行的文: CHAR(10) 如:”welcome to”& char(10) &”china” 显示为welcome to china,9、文本处理技术,9.4常用提取字符的函数 LEFT() 左侧提取 MID() 从字符串中间任意位置提取字符串 RIGHT()右侧提取指定长度的字符串 LEN()取字符串的长度 例:利用MID 函数提取身份证号码中的8 位生日数字,9、文本处理技术,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。,9、文本处理技术,9.6字符串的分离 很多时候,用户需要进行拆分的数据都具有一定的共性,对于这样的数据进行拆分,可以使用查找函数结合提取字符串函数来处理. 例:使用查找函数拆分空格分隔的数据,9、文本处理技术,9.7统计字符串中指定字符的个数 单元格文本为“Microsoft Office System” 那么如何统计单元格文本中字母 “o”出现的次数 = LEN (“Microsoft Office System”) LEN(SUBSTITUTE(“Microsoft Office System”, “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。,10、数学计算,学习要点: 要点1:了解取余函数及应用 要点2:常用舍入函数介绍 要点3:随机函数的应用 要点4:认识求和类函数,10、数学计算,10.1取余函数 在数学概念中,被除数与除数进行整除运算后余的数值被称为余数,其特征是余数必定小于除数.例如10除以3余数为1.EXCEL的取余函数MOD函数计算两数相除后的余数非常方便. 示例:利用MOD函数取余 如果求19除以7的余数,可以用下面的公式: = MOD(19,7) 结果为5 如果正好整除则余数为0 -2除以7的余数是? 2除以-7的余数是? 余数的符号与除数一致.,10、数学计算,10.1.1判断奇偶性 利用余数小于除数的原理，当用一个数值对2进行取余操作时，结果就只能得到0或1。在实际工作中，可以利用此原理来判断数值的奇偶性 示例：得用函数计算数值的奇偶性 IF(MOD(23,2)=1,”奇数”,”偶数”),10、数学计算,10.2 数据取舍函数 在对数值的处理中,经常会碰到进位和舍去的情况,EXCEL提供了如下取舍函数,注:显示精度,10、数学计算,10.2 .1 INT和TRUNC函数 INT函数用于取得不大于目标值的最大整数,TRUNC是对目标值进行截取 INT函数只能取到整数位,TRUNC则可以取得小数位数 例子: 1.对于数值15.38进行取整 =INT(15.38) = 15 =TRUNC(15.38)=15 2.对于负数8.72进行取整 =INT(-8.72)= -9 =TRUNC(-8.72)= -8,10、数学计算,10.2 .2 ROUNDUP和ROUNDDOWN 函数 UP 向上 DWON 向下 例子: 对于数值123.456若保留两位小数,两个函数处理结果不同 =ROUNDUP(123.456,2) = 123.46 =ROUNDDOWN(123.456) = 123.45 ROUNDDOWN 向下取舍同于TRUNC,10、数学计算,10.2.3 ROUND 函数 ROUND函数用于返回指定小数位数的四舍五入运算. 例: 对于数值12.456进行四舍五入保留两位小数为123.46 =ROUND(12.456)=12.46 对于数值1234.56,若要四舍五入到1200 =ROUND(1234.56,-2)=1200,10、数学计算,10.3 随机 函数 在许多应用中用户需要得到一些不确定的数,这种情况下会使用到随机函数. EXCEL提供的随机函数为RAND(),它返回0-1之间的数值,这个函数具有易失性. 例:,10、数学计算,10.4 SUM求和 函数 SUM求和函数用于数值求和. 例: 介绍一下SUMPRODUCT 在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。 ,11、日期和时间计算,11.1年月日函数 TODAY()和NOW() 用于生成当前的日期和时间 DATE函数:返回具体的日期 YEAR,MONTH,DAY:从日期数据中提取年份,月份,日期; 介绍一下DATEDIF(S_DATE,E_DATE,YMD),12、查找与引用,12.1查找与引用函数主要讲一下 VLOOKUP():主要的查询函数 MATCH():查找定位函数 ROW():行号 COLUMN():列号,13、统计与求和,统计函数是EXCEL中使用比较多的一类函数. 13.1基本的统计函数 COUNT:针对数据表中的数值进行计数 COUNTA:主要用于统计非空单元格的个数 COUNTBLANK:统计数据表中空单元格的个数,也包括有空字符的单元格,13、统计与求和,13.2 COUNTIF条件统计函数 语法 COUNTIF(range,criteria) Range 为需要计算其中满足条件的单元格数目的单元格区域。 Criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、“32“、“32“ 或 “apples“。,13、统计与求和,13.3 SUMIF条件求和函数 语法 SUMIF(range,criteria,sum_range) Range 为用于条件判断的单元格区域。 Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、“32“、“32“ 或 “apples“。 Sum_range 是需要求和的实际单元格。,13、统计与求和,13.4 极值应用函数 MAX:取得数据最大值 MIN:取得数据最小值 LARGE:返回指定第N个最大值 SMALL:取得指定第N个最小值 Rank排名,13、统计与求和,13. 5 SUBTOTAL函数 返回列表或数据库中的分类汇总。通常，使用“数据”菜单中的“分类汇总”命令可以容易地创建带有分类汇总的列表。一旦创建了分类汇总，就可以通过编辑 SUBTOTAL 函数对该列表进行修改。 SUBTOTAL(function_num, ref1, ref2, .) Function_num 为 1 到 11（包含隐藏值）或 101 到 111（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算。,13、统计与求和,13. 6 offset函数 以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。,