公式与函数、数据库和图表

《公式与函数、数据库和图表》由会员分享，可在线阅读，更多相关《公式与函数、数据库和图表（91页珍藏版）》请在金锄头文库上搜索。

1、公式与函数、数据库和图表,2,5.5 公式与函数,本节要点： 公式中的运算符及优先级 单元格地址的引用方式 区域的命名与应用 函数,3,5.5 公式与函数,考试大纲要求掌握的函数： SUM、AVERAGE、MAX、MIN、COUNT、SQRT、PI、PMT、PV、DSUM、DAVERAGE、DCOUNT。,4,5.5 公式与函数,公式中的运算符及优先级 算术运算符 有：（加、正）、（减、负）、*（乘）、/（除）、%（百分号）、（乘方） 结果是数值 文本运算符 &（文本连接） 结果是文字型数据,5,5.5 公式与函数,比较运算符 有：=（等于）、（大于）、=（大于等于）、（不等于） 结果是逻辑值

2、：TRUE和FALSE,6,优先顺序是：算术运算、文本运算、比较运算。 各运算符优先顺序从高到低依次是：（ ）（括号）、（负号）、%、 、* 和 / 、和、&、比较运算符（=、=、）。,5.5 公式与函数,7,8,改变优先顺序可使用圆括号“（）” 如果公式中使用的运算符优先级相同，则按从左到右的顺序进行运算。 单元格地址的引用方式 单元格引用分为相对引用、绝对引用、混合引用和三维引用,5.5 公式与函数,9,5.5 公式与函数,相对引用 指当公式被复制到另一个单元格时，公式中引用的单元格地址或区域地址会相应发生改变。单元格地址形式是用列标和行号表示，比如A1或区域A2:B6 公式“=A1+B1

3、” 绝对引用 指公式在复制时，公式中绝对引用的单元格地址不会发生改变。单元格地址形式是在列标和行号前加“$”符号表示。公式“=$A$1+B1”,10,5.5 公式与函数,混合引用 公式中引用的单元格地址形式只在列标前或行号前加“$”符号，使得单元格地址中既有相对部分又有绝对部分，公式复制时，相对部分发生改变而绝对部分不发生改变，例如公式“=$A1+B$1+C1” 三维引用 指公式中引用的单元格在另一个工作表中，在单元格地址前加工作表名并用“！”号分隔， 例如公式“=Sheet2!A1+Sheet3!A1”,11,5.5 公式与函数,公式的移动和复制 公式移动的规律 公式引用的单元格地址不会发生

4、改变，将公式移动它所引用的单元格中时会显示出错误值#REF，其他单元格移动到公式所引用的单元格时公式也会出错#REF。 公式复制的规律 公式中相对地址和混合地址中的相对部分会改变 公式中绝对地址和混合地址中的绝对部分不会改变,12,公式中单元格引用类型的确定 具体步骤和做法是： （1）在需要输入公式的区域中选两个单元格。 单行区域选最左端相邻两个单元格，单列区域选最上边相邻两个单元格，矩形区域选左上角单元格及其相邻右下角单元格。 （2）写出两单元格中期望输入的两个公式（只考虑相对引用） （3）对比两个期望公式中引用单元格的列标和行号 改变的用相对地址表示，不变的用绝对地址表示,5.5 公式与函

5、数,13,区域的命名与应用 单元格或区域除了用单元格地址或区域地址来标识外，还可定义一个名称作为标识。名称有如下特点： （1）定义的名称不能与单元格地址相同 （2）一个工作簿的所有工作表共用一套名称 （3）每个名称都是唯一的 （4）同一单元格或区域允许有多个名称 （5）一个名称只能对应一个单元格或区域,5.5 公式与函数,14,定义名称 先选定区域 从“名称框”输入 “插入”/“名称”子菜单/“定义”命令 指定名称 选定包括要作为名称的行或列在内的区域，选择“插入”/“名称”/“指定”命令,5.5 公式与函数,15,名称的应用 选定要应用名称的单元格或区域；选择“插入”菜单/“名称”子菜单/“

6、应用”命令 名称的删除 选择“插入”菜单/“名称”子菜单/“定义”命令，出现“定义名称”对话框，从名称列表中选中要删除的名称，单击“删除”按钮便完成删除操作。,5.5 公式与函数,16,函数 函数的构成是以函数名开始，后面跟着的是左圆括号、以逗号分隔的参数和右圆括号，一般形式： （参数1,参数2,） 函数名 表示该函数功能的英文单词或者是缩写 参数：可以是常量、单元格地址、区域地址、表达式、或函数；参数的个数、类型、所代表的含义各不相同，参数最多不能超过30个。有些函数不需要参数，但括号仍须保留，有些函数参数分必选与可选两种，一个函数的参数又是一个函数时，称之为函数嵌套，最多允许包含有7级嵌套

7、函数。 返回值 是函数的运算结果,5.5 公式与函数,5.5 公式与函数,17,Excel函数共九大类，300多个。 什么是函数？函数即预先定义好的公式。 类型：数学与三角函数、统计函数、数据库函数、逻辑函数等9大类。 每个函数至少返回一个计算得到的值（绝大多数函数只返回一个值，部分函数可返回多个值），要注意函数返回的值的类型。 函数的引用格式： 函数名(参数1，参数2，) 没有参数的函数括号不能省略,5.5 公式与函数,18,函数的语法,（1）公式必须以“=”开头。 （2）函数的参数用圆括号“（ ）”括起来。 （3）函数的参数多于一个时，要用“，”号分隔。 （4）参数的文本要用英文的双引号括

8、起来。 （5）参数可以是已定义的单元格名或区域名。 （6）函数中使用参数可以由数值、文本和逻辑值组成。 （7）可以混合使用区域名、单元格引用和数值作为函数的参数。,19,函数的参数类型,（1）数字，如21、-7、37.25等。 （2）文字，如“a”、“Word”、“Excel”等。若在文字中使用双引号，则在每个双引号处用两个双引号，如“TEXT”。 （3）逻辑值，如TRUE、FALSE或者计算时产生逻辑值的语句（如A1035）。 （4）错误值，如#REF!。 （5）引用，如D11、C5:C10。,20,表-数值转换示例,21,函数 函数的输入方法 1、直接输入 先输入“=” 2、用粘贴函数输入

9、 “粘贴函数”按钮，或选择“插入”菜单/“函数”命令，或单击编辑栏上的“编辑公式”按钮 3、利用自动求和按钮输入SUM函数,5.5 公式与函数,22,函数 查找函数的帮助信息 “粘贴函数”按钮 “帮助”菜单/“Microsoft Excel 帮助”命令 按F1键,5.5 公式与函数,23,常用函数功能简介 1、数学函数 （1）绝对值函数ABS 语法：ABS(number) 参数number是需要计算绝对值的数值或单元格地址。 功能：返回参数number的绝对值。 （2）取整函数INT 语法：INT(number) 参数number是需要取整处理的数值或单元格地址。 功能：返回参数number向

10、下取整为最接近的整数。,5.5 公式与函数,24,（3）余数函数MOD 语法：MOD(number,divisor) 参数number为被除数，divisor 为除数，两者均为数值或单元格地址。如果 divisor为零，函数 MOD 返回错误值 #DIV/0!。 功能：返回参数number除以参数divisor的余数，余数的正负号与divisor一致。用INT函数表示为：MOD(n, d) = n - d*INT(n/d),5.5 公式与函数,25,（4）符号函数SIGN 语法：SIGN(number)参数number为数值或单元格地址。 功能：返回参数number的符号。 当number为正

11、数时返回1，为零时返回0，为负数时返回-1。,5.5 公式与函数,26,（5）圆周率函数PI 语法：PI( ) 无参数，但不能省略括号。 功能：返回圆周率的值，即3.14159265358979，精确到小数点后15位。,5.5 公式与函数,27,（6）随机数函数RAND 语法：RAND( ) 无参数，但不能省略括号。 功能：返回大于等于0小于1的均匀分布随机数，工作表每次计算时都将返回一个新的数值。,5.5 公式与函数,28,（7）四舍五入函数ROUND 语法：ROUND(number,num_digits) 参数number为需要进行舍入的数值或单元格地址，num_digits为指定的位数，

12、按此位数进行舍入（为0时只保留整数，为-1时个位要四舍五入，依此类推）。 功能：返回参数number按指定位数num_digits四舍五入后的数值。,5.5 公式与函数,29,（8）平方根函数SQRT 语法：SQRT(number) 参数number为需要求平方根的数值或单元格地址，如果该数值为负，则函数 SQRT 返回错误值 #NUM!。 功能：返回参数number的平方根。 eg:= SQRT(4)的值为2 = SQRT(4)的值为#NUM!,5.5 公式与函数,30,（9）截尾函数TRUNC 语法：TRUNC(number,num_digits) 参数number需要截尾的数值或单元格地

13、址；num_digits用于指定精度的位数，省略时的默认值为0。 功能：返回参数number按指定精度位数num_digits截尾后的数值。 Eg: = TRUNC(55.21,2)的值为55.21 = TRUNC(55.21,1)的值为55.2 = TRUNC(55.21)的值为55 = TRUNC(55.21,1)的值为50,5.5 公式与函数,31,（10）求和函数SUM 语法：SUM(number1,number2, .) 参数number1, number2, . 为 1 到 30 个需要求和的参数。直接键入到参数表中的数值常量、逻辑值及数字的文本表达式将求和，如果参数为单元格地址或

14、区域地址，只有其中的数值被求和，而空白单元格、逻辑值、文本或错误值将被忽略。如果参数为错误值或者是不能转换成数字的文本，将会导致错误得到错误值#VALUE!。 功能：返回所有参数数值之和。,5.5 公式与函数,32,（11）条件求和函数SUMIF 语法：SUMIF(range,criteria,sum_range) 参数range为用于条件判断的区域地址；criteria为确定哪些单元格将求和的条件，其形式可以为数值、表达式、文本或单元格地址；sum_range为需要求和的实际区域地址，如果省略该参数，求和的实际区域是range。 功能：返回根据指定条件对若干单元格求和的值。该值是当区域ran

15、ge中的单元格满足条件criteria时，将其对应区域sum_range的相应单元格的数值求和 Eg：SUMIF(B3:B20,“男”,D3:D20) 是求男生的数学成绩总和,5.5 公式与函数,33,（12）条件计数函数COUNTIF 语法：COUNTIF(range,criteria) 参数range为意区域地址；criteria为确定range内哪些单元格将计数的条件。 功能：返回区域range中的满足条件criteria的单元格个数。 Eg:= COUNTIF(B2:B20,”女”) 是求女生的人数,5.5 公式与函数,34,2、文本函数 （1）左提取子串函数LEFT 语法：LEFT(

16、text,num_chars) 参数text是用于提取子串的字符；num_chars为非负整数，用于指定提取子串的字符数，省略时默认值为1。 功能：返回字符串text从左起num_chars个字符的子串。若num_chars 大于text的长度，则返回所有字符。 Eg: =LEFT(“chinaren”,4)的值为chin,5.5 公式与函数,35,（2）右提取子串函数RIGHT 语法：RIGHT(text,num_chars) 参数text是用于提取子串的字符；num_chars为非负整数，用于指定提取子串的字符数，省略时默认值为1。 功能：返回字符串text从右起num_chars个字符的子串。若num_chars 大于text的长度，则返回所有字符。 Eg: =RIGHT(“chinaren”,4)的值为aren,5.5 公式与函数,36,（3）中间提取子串函数MID 语法：MID(text,start_num,num_chars) 参数text是用于提取子串的字符；star