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

1、公式与函数、数据库和图表公式与函数、数据库和图表25.5 公式与函数公式与函数 本节要点：本节要点：公式中的运算符及优先级公式中的运算符及优先级 单元格地址的引用方式单元格地址的引用方式 区域的命名与应用区域的命名与应用 函数函数35.5 公式与函数公式与函数 考试大纲要求掌握的函数：考试大纲要求掌握的函数：SUM、AVERAGE、MAX、MIN、COUNT、SQRT、PI、PMT、PV、DSUM、DAVERAGE、DCOUNT。45.5 公式与函数公式与函数 公式中的运算符及优先级公式中的运算符及优先级 算术运算符算术运算符 有：有：（加、正）（加、正）、（减、负）、（减、负）、*（乘）、（

2、乘）、/（除）、（除）、%（百分号）、（百分号）、（乘方）（乘方）结果是数值结果是数值文本运算符文本运算符 &（文本连接）（文本连接）结果是文字型数据结果是文字型数据55.5 公式与函数公式与函数 比较运算符比较运算符 有：有：=（等于）、（等于）、（大于）、（大于）、=（大于等于）、（大于等于）、（小于）、（小于）、=（小于等于）、（小于等于）、（不等于）（不等于） 结果是逻辑值：结果是逻辑值：TRUE和和FALSE6 优先顺序是：算术运算、文本运算、比优先顺序是：算术运算、文本运算、比较运算。较运算。 各运算符优先顺序从高到低依次是：（各运算符优先顺序从高到低依次是：（ ）（括号）

3、、（括号）、（负号）、（负号）、%、 、* 和和 / 、和、和、&、比较运算符（、比较运算符（=、=、=、）。）。5.5 公式与函数公式与函数 78改变优先顺序可使用圆括号改变优先顺序可使用圆括号“（）（）”如果公式中使用的运算符优先级相同，则按如果公式中使用的运算符优先级相同，则按从左到右的顺序进行运算。从左到右的顺序进行运算。单元格地址的引用方式单元格地址的引用方式 单元格引用分为相对引用、绝对引用、混合引单元格引用分为相对引用、绝对引用、混合引用和三维引用用和三维引用 5.5 公式与函数公式与函数 95.5 公式与函数公式与函数 相对引用相对引用指当公式被复制到另一个单元格时，公

4、式中引用的指当公式被复制到另一个单元格时，公式中引用的单元格地址或区域地址会相应发生改变。单元格地单元格地址或区域地址会相应发生改变。单元格地址形式是用列标和行号表示，比如址形式是用列标和行号表示，比如A1或区域或区域A2:B6公式公式“=A1+B1”绝对引用绝对引用 指公式在复制时，公式中绝对引用的单元格地址不指公式在复制时，公式中绝对引用的单元格地址不会发生改变。单元格地址形式是在列标和行号前加会发生改变。单元格地址形式是在列标和行号前加“$”符号表示。公式符号表示。公式“=$A$1+B1” 105.5 公式与函数公式与函数 混合引用混合引用 公式中引用的单元格地址形式只在列标前或行号前公

5、式中引用的单元格地址形式只在列标前或行号前加加“$”符号，使得单元格地址中既有相对部分又有符号，使得单元格地址中既有相对部分又有绝对部分，公式复制时，相对部分发生改变而绝对绝对部分，公式复制时，相对部分发生改变而绝对部分不发生改变，例如公式部分不发生改变，例如公式“=$A1+B$1+C1” 三维引用三维引用指公式中引用的单元格在另一个工作表中，在单元指公式中引用的单元格在另一个工作表中，在单元格 地 址 前 加 工 作 表 名 并 用格 地 址 前 加 工 作 表 名 并 用 “ ！ ” 号 分 隔 ，号 分 隔 ，例如公式例如公式“=Sheet2!A1+Sheet3!A1” 115.5 公式

6、与函数公式与函数 公式的移动和复制公式的移动和复制公式移动的规律公式移动的规律公式引用的单元格地址不会发生改变，将公式公式引用的单元格地址不会发生改变，将公式移动它所引用的单元格中时会显示出错误值移动它所引用的单元格中时会显示出错误值#REF，其他单元格移动到公式所引用的单元格时公式也会其他单元格移动到公式所引用的单元格时公式也会出错出错#REF。公式复制的规律公式复制的规律公式中相对地址和混合地址中的相对部分会改变公式中相对地址和混合地址中的相对部分会改变公式中绝对地址和混合地址中的绝对部分不会改变公式中绝对地址和混合地址中的绝对部分不会改变12公式中单元格引用类型的确定公式中单元格引用类型

7、的确定 具体步骤和做法是：具体步骤和做法是：（1）在需要输入公式的区域中选两个单元格。）在需要输入公式的区域中选两个单元格。单行区域选最左端相邻两个单元格，单列区域选最上边相邻单行区域选最左端相邻两个单元格，单列区域选最上边相邻两个单元格，矩形区域选左上角单元格及其相邻右下角单元格。两个单元格，矩形区域选左上角单元格及其相邻右下角单元格。（2）写出两单元格中期望输入的两个公式（只考虑相对）写出两单元格中期望输入的两个公式（只考虑相对引用）引用）（3）对比两个期望公式中引用单元格的列标和行号）对比两个期望公式中引用单元格的列标和行号改变的用相对地址表示，不变的用绝对地址表示改变的用相对地址表示，

8、不变的用绝对地址表示5.5 公式与函数公式与函数 13区域的命名与应用区域的命名与应用单元格或区域除了用单元格地址或区域地址来标识外，还单元格或区域除了用单元格地址或区域地址来标识外，还可定义一个名称作为标识。名称有如下特点：可定义一个名称作为标识。名称有如下特点：（1）定义的名称不能与单元格地址相同）定义的名称不能与单元格地址相同（2）一个工作簿的所有工作表共用一套名称）一个工作簿的所有工作表共用一套名称（3）每个名称都是唯一的）每个名称都是唯一的（4）同一单元格或区域允许有多个名称）同一单元格或区域允许有多个名称（5）一个名称只能对应一个单元格或区域）一个名称只能对应一个单元格或区域5.5

9、 公式与函数公式与函数 14定义名称定义名称 先选定区域先选定区域从从“名称框名称框”输入输入“插入插入”/“名称名称”子菜单子菜单/“定义定义”命令命令指定名称指定名称 选定包括要作为名称的行或列在内的区域，选定包括要作为名称的行或列在内的区域，选择选择“插入插入”/“名称名称”/“指定指定”命令命令 5.5 公式与函数公式与函数 15名称的应用名称的应用选定要应用名称的单元格或区域；选择选定要应用名称的单元格或区域；选择“插入插入”菜单菜单/“名称名称”子菜单子菜单/“应用应用”命令命令 名称的删除名称的删除选择选择“插入插入”菜单菜单/“名称名称”子菜单子菜单/“定义定义”命令，出现命令

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

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

12、）参数的文本要用英文的双引号括起来。（5）参数可以是已定义的单元格名或区域名。（6）函数中使用参数可以由数值、文本和逻辑值组成。（7）可以混合使用区域名、单元格引用和数值作为函数的参数。19函数的参数类型函数的参数类型（1）数字，如）数字，如21、-7、37.25等。等。（2）文字，如）文字，如“a”、“Word”、“Excel”等。等。若在文字中使用双引号，则在每个双引号处若在文字中使用双引号，则在每个双引号处用两个双引号，如用两个双引号，如“TEXT”。（3）逻辑值，如）逻辑值，如TRUE、FALSE或者计算时产或者计算时产生逻辑值的语句（如生逻辑值的语句（如A1035）。）。（4）错误值

13、，如）错误值，如#REF!。（5）引用，如）引用，如D11、C5:C10。20公式公式运算结果运算结果说明说明=“2”+“6”8使用算术运算符时，虽然使用算术运算符时，虽然“2”和和“6”是文是文本类型的数据，但本类型的数据，但Excel认为运算是数字项认为运算是数字项，自动将其转换为数字，自动将其转换为数字=“99/09/08”-“99/09/02”6Excel将具有将具有yy/mm/dd格式的文本当作日格式的文本当作日期，将日期转换成为序列号后，再进行计期，将日期转换成为序列号后，再进行计算算=SUM（“3 * 5”,4）VALUE！Excel不能将文本不能将文本“3 * 5”转换成数字转

14、换成数字3“abcdef”3abcdef当公式需要文本型数值时，当公式需要文本型数值时，Execl自动将数自动将数字转换为文本字转换为文本表表-数值转换示例数值转换示例21函数函数函数的输入方法函数的输入方法 1、直接输入、直接输入 先输入先输入“=”2、用粘贴函数输入、用粘贴函数输入 “粘贴函数粘贴函数”按钮，或选择按钮，或选择“插入插入”菜单菜单/“函数函数”命令，或单击编辑栏上的命令，或单击编辑栏上的“编辑公编辑公式式”按钮按钮 3、利用自动求和按钮输入、利用自动求和按钮输入SUM函数函数 5.5 公式与函数公式与函数 22函数函数查找函数的帮助信息查找函数的帮助信息 “粘贴函数粘贴函数

15、”按钮按钮“帮助帮助”菜单菜单/“Microsoft Excel 帮助帮助”命命令令按按F1键键5.5 公式与函数公式与函数 23常用函数功能简介常用函数功能简介1、数学函数、数学函数 （1）绝对值函数）绝对值函数ABS语法：语法：ABS(number)参数参数number是需要计算绝对值的数值或单元格地址。是需要计算绝对值的数值或单元格地址。功能：返回参数功能：返回参数number的绝对值。的绝对值。（2）取整函数）取整函数INT语法：语法：INT(number)参数参数number是需要取整处理的数值或单元格地址。是需要取整处理的数值或单元格地址。功能：返回参数功能：返回参数number向

16、下取整向下取整为最接近的整数。为最接近的整数。 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/

17、d)5.5 公式与函数公式与函数 25（4）符号函数）符号函数SIGN 语法：语法：SIGN(number)参数参数number为数值为数值或单元格地址。或单元格地址。 功能：返回参数功能：返回参数number的符号。的符号。当当number为正数时返回为正数时返回1，为零时返回，为零时返回0，为负，为负数时返回数时返回-1。5.5 公式与函数公式与函数 26（5）圆周率函数）圆周率函数PI 语法：语法：PI( )无参数，但不能省略括号。无参数，但不能省略括号。 功 能 ： 返 回 圆 周 率功 能 ： 返 回 圆 周 率 的 值 ， 即的 值 ， 即3.14159265358979，精确到小

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

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

20、r的平方根。的平方根。 eg:= SQRT(4)的值为的值为2 = SQRT(4)的值为的值为#NUM!5.5 公式与函数公式与函数 30（9）截尾函数）截尾函数TRUNC语法：语法：TRUNC(number,num_digits)参数参数number需要截尾的数值或单元格地址；需要截尾的数值或单元格地址；num_digits用于指定精度的位数，省略时的默用于指定精度的位数，省略时的默认值为认值为0。功能：返回参数功能：返回参数number按指定精度位数按指定精度位数num_digits截尾后的数值。截尾后的数值。Eg: = TRUNC(55.21,2)的值为的值为55.21 = TRUNC(

21、55.21,1)的值为的值为55.2 = TRUNC(55.21)的值为的值为55 = TRUNC(55.21,1)的值为的值为505.5 公式与函数公式与函数 31（10）求和函数）求和函数SUM语法：语法：SUM(number1,number2, .)参数参数number1, number2, . 为为 1 到到 30 个需要求和个需要求和的参数。直接键入到参数表中的数值常量、逻辑值及的参数。直接键入到参数表中的数值常量、逻辑值及数字的文本表达式将求和，如果参数为单元格地址或数字的文本表达式将求和，如果参数为单元格地址或区域地址，只有其中的数值被求和，区域地址，只有其中的数值被求和，而空白

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

23、求和的条件，其形式可以为数值、表达式、文本或单元格地址；文本或单元格地址；sum_range为需要求和的实际区域为需要求和的实际区域地址，如果省略该参数，求和的实际区域是地址，如果省略该参数，求和的实际区域是range。功能：返回根据指定条件对若干单元格求和的值。该值功能：返回根据指定条件对若干单元格求和的值。该值是当区域是当区域range中的单元格满足条件中的单元格满足条件criteria时，将其对时，将其对应区域应区域sum_range的相应单元格的数值求和的相应单元格的数值求和 Eg：SUMIF(B3:B20,“男男”,D3:D20) 是求男生的数学成绩总和是求男生的数学成绩总和5.5

24、公式与函数公式与函数 33（12）条件计数函数）条件计数函数COUNTIF语法：语法：COUNTIF(range,criteria)参数参数range为意区域地址；为意区域地址；criteria为确定为确定range内哪些单元格将计数的条件。内哪些单元格将计数的条件。功能：返回区域功能：返回区域range中的满足条件中的满足条件criteria的单的单元格个数。元格个数。 Eg:= COUNTIF(B2:B20,”女女”) 是求女生的人数是求女生的人数5.5 公式与函数公式与函数 342、文本函数、文本函数 （1）左提取子串函数）左提取子串函数LEFT语法：语法：LEFT(text,num_c

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

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

27、串的字符；是用于提取子串的字符；start_num为为提取字符的开始位置；提取字符的开始位置；num_chars用于指定提取用于指定提取子串的字符数。子串的字符数。功能：返回字符串功能：返回字符串text从从start_num起起num_chars个字符的子串。个字符的子串。Eg:=MID(“chinaren”,3,4)的值是的值是inar5.5 公式与函数公式与函数 37（4）字符串长度函数）字符串长度函数LEN语法：语法：LEN(text)参数参数text是要计算其长度的文本。空格将作是要计算其长度的文本。空格将作为字符进行计数。为字符进行计数。功能：返回字符串功能：返回字符串text中的

28、字符数，结果为整中的字符数，结果为整数。数。 Eg：=LEN(“beijing chinaren”)5.5 公式与函数公式与函数 38（5）数字代码转字符函数）数字代码转字符函数CHAR语法：语法：CHAR(number)参数参数number是用于转换字符的数字代码（介是用于转换字符的数字代码（介于于 1255 之间）。之间）。功能：返回参数功能：返回参数number对应的字符。对应的字符。 Eg:=CHAR(114)5.5 公式与函数公式与函数 39（6）字符转数字代码函数）字符转数字代码函数CODE语法：语法：CODE(text)参数参数text为字符或字符串。为字符或字符串。功能：返回字

29、符串功能：返回字符串text中第一个字符的数字代码。中第一个字符的数字代码。 Eg:=CHAR(“r”)5.5 公式与函数公式与函数 （7）查找子串函数）查找子串函数FIND、SEARCH语法：语法：FIND(find_text,within_text,start_num)SEARCH(find_text,within_text,start_num)参数参数find_text是要查找的子串；是要查找的子串；Within_text是包含要查是包含要查找子串的字符串；找子串的字符串；Start_num指定开始查找的位置，省略时指定开始查找的位置，省略时默认值为默认值为1。功能：返回功能：返回fin

30、d_text在在Within_text中从第中从第Start_num个字符个字符开始第一次出现时的位置。开始第一次出现时的位置。FIND函数和函数和SEARCH函数的区函数的区别：前者区分字符串大小写，后者则不然；前者要查找的子别：前者区分字符串大小写，后者则不然；前者要查找的子串不能用通配符，而后者可以。通配符是指串不能用通配符，而后者可以。通配符是指“？”表示任意表示任意一个字符、一个字符、“*”表示任意一串字符。表示任意一串字符。Eg：=FIND(“i”,“chinaren”,2)的值是的值是3 =SEARCH(“?i”,“chinaren”)的值是的值是25.5 公式与函数公式与函数

31、41（8）特定位数的数值转字符函数）特定位数的数值转字符函数FIXED语法：语法：FIXED(number,decimals,no_commas)参数参数number是要进行四舍五入并转换成字符串是要进行四舍五入并转换成字符串的数值；的数值；decimals指定小数位数；指定小数位数；no_commas为一为一逻辑值。逻辑值。功能：返回功能：返回number四舍五入后保留四舍五入后保留decimals位小数位小数所转换成的字符串，所转换成的字符串，no_commas的值为的值为TRUE或非或非0时返回的字符串时返回的字符串不带逗号不带逗号，为，为FALSE、或省略、或省略、或为或为0时，结果带

32、逗号。时，结果带逗号。 Eg:= FIXED(2523.645,2,2)的值为的值为2523.65 = FIXED(2523.645,2,0)的值为的值为2,523.655.5 公式与函数公式与函数 42（9）数字的字符串转数值函数）数字的字符串转数值函数VALUE语法：语法：VALUE(text)参数参数text为带引号的文本，也可以是单元格地为带引号的文本，也可以是单元格地址。址。功能：返回数值型数据，将数字组成的文字串转功能：返回数值型数据，将数字组成的文字串转换成数值。换成数值。 Eg：= VALUE(“1，968”)的值为的值为19685.5 公式与函数公式与函数 433、日期与时间

33、函数、日期与时间函数 （1）指定日期函数）指定日期函数DATE语法：语法：DATE(year,month,day)参数参数year ,month ,day代表年份、月份、日。代表年份、月份、日。year是是在在1900至至9999之间中的一个整数，如果在之间中的一个整数，如果在0到到1899之间之间中取值，会自动加中取值，会自动加1900再作为年份；再作为年份；month在在1至至12间间取值，如果大于取值，如果大于12，将从下一年一月开始往上累加，将从下一年一月开始往上累加，例如，例如，DATE(1998,14,2) 返回代表返回代表 1999 年年 2 月月 2 日的日的序列数；如果序列数

34、；如果 day 大于该月份的最大天数时，将从下大于该月份的最大天数时，将从下个月的第一天开始往上累加。个月的第一天开始往上累加。功能：返回指定日期的序列数。功能：返回指定日期的序列数。Eg:= DATE(0,14,32) 的值是1901-3-45.5 公式与函数公式与函数 44（2）系统日期函数）系统日期函数TODAY语法：语法：TODAY( )无参数无参数功能：返回计算机系统内部时钟的日期序列数。功能：返回计算机系统内部时钟的日期序列数。得到一个整数。得到一个整数。 5.5 公式与函数公式与函数 45（3）系统日期和时间函数）系统日期和时间函数NOW语法：语法：NOW( )无参数无参数功能：

35、返回计算机系统内部时钟的日期和时间功能：返回计算机系统内部时钟的日期和时间的序列数。结果的整数部分代表日期，小数部分的序列数。结果的整数部分代表日期，小数部分代表时间。代表时间。INT(NOW( )=TODAY( )。5.5 公式与函数公式与函数 46（4）年份函数）年份函数YEAR语法：语法：YEAR(serial_number)参数参数serial_number为一个日期值或序列数。为一个日期值或序列数。功能：返回功能：返回serial_number相应的年份值。为相应的年份值。为1900到到9999之间的整数。之间的整数。 Eg:=YEAR(113212)的值为的值为2209 =YEAR

36、(1980/12/25)的值为的值为19805.5 公式与函数公式与函数 47（5）月份函数）月份函数MONTH语法：语法：MONTH(serial_number)参数参数serial_number为一个日期值或序列数。为一个日期值或序列数。功能：返回功能：返回serial_number相应的月份值。为相应的月份值。为1到到12之间的整数。之间的整数。Eg:=MONTH(252)的值是的值是9 =MONTH(2003/4/5)的值是的值是45.5 公式与函数公式与函数 48（6）日函数）日函数DAY语法：语法：DAY(serial_number)参数参数serial_number为一个日期值或

37、序列数。为一个日期值或序列数。功能：返回功能：返回serial_number相应的日数值。为相应的日数值。为1到到31之间的整数。之间的整数。 Eg:=DAY(“2005/10/15”)的值是的值是155.5 公式与函数公式与函数 49（7）指定时间函数）指定时间函数TIME语法：语法：TIME(hour,minute,second)参数参数hour代表代表“时时”，取，取0到到23之间的数；之间的数；minute代表代表“分分”，取，取0到到59之间的数；之间的数；second代代表表“秒秒”，取，取0到到59之间的数。之间的数。功能：返回指定时间的序列数。功能：返回指定时间的序列数。Eg:

38、=TIME(5,10,25)的值是的值是5:10 AM5.5 公式与函数公式与函数 50（8）时函数）时函数HOUR语法：语法：HOUR(serial_number)功能：返回功能：返回serial_number的相应小时数。的相应小时数。Eg:=HOUR(“5:25 PM”)的值为的值为17 HOUR(0.5)的值是的值是125.5 公式与函数公式与函数 51（9）分函数）分函数MINUTE语法：语法：MINUTE(serial_number)功能：返回功能：返回serial_number的相应分钟数。的相应分钟数。Eg:=MINUTE(“3:56:45 PM”)的值是的值是56（10）秒函

39、数）秒函数SECOND语法：语法：SECOND(serial_number)功能：返回功能：返回serial_number的相应秒数。的相应秒数。Eg:=SECOND(“3:56:45 PM”)的值是的值是455.5 公式与函数公式与函数 52（1）逻辑）逻辑“与与”函数函数AND语法：语法：AND(logical1,logical2, .)参数参数logical1, logical2, . 表示待检测的表示待检测的1到到30个条件值，各条件值为逻辑值个条件值，各条件值为逻辑值 TRUE、或、或FALSE。 功能：所有参数的逻辑值为真时返回功能：所有参数的逻辑值为真时返回TRUE；只；只要有一

40、个参数的逻辑值为假即返回要有一个参数的逻辑值为假即返回 FALSE。 Eg:=AND(1,3,9,-21)的值为的值为FALSE5.5 公式与函数公式与函数 53（2）逻辑）逻辑“或或”函数函数OR语法：语法：OR(logical1,logical2, .)参数参数logical1, logical2, . 表示待检测的表示待检测的1到到30个条件值，各条件值为逻辑值个条件值，各条件值为逻辑值 TRUE、或、或FALSE。 功能：所有参数中，任何一个参数逻辑值为功能：所有参数中，任何一个参数逻辑值为 TRUE，即返回，即返回 TRUE。否则返回。否则返回 FALSE。Eg:=OR(1,3,9,

41、-21) 的值是的值是TRUE =OR(51) 的值是的值是FALSE5.5 公式与函数公式与函数 54（3）逻辑）逻辑“非非”函数函数NOT语法：语法：NOT(logical)参数参数logical为一个逻辑值或逻辑表达式。为一个逻辑值或逻辑表达式。功能：参数功能：参数logical为为 FALSE时，返回时，返回TRUE；参数为参数为TRUE时，返回时，返回FALSE。 Eg:=NOT(23)的值是的值是TRUE5.5 公式与函数公式与函数 55（4）条件选择函数）条件选择函数IF语法：语法：IF(logical_test,value_if_true,value_if_false)参数参数

42、logical_test是检测条件，可以是结果为是检测条件，可以是结果为TRUE或或FALSE的任意值或表达式；的任意值或表达式；Value_if_true是是l当当ogical_test为为TRUE时的返回值；时的返回值；Value_if_false是当是当logical_test为为FALSE时的返回值。时的返回值。功能：当检测条件功能：当检测条件logical_test为为TRUE时返回值时返回值Value_if_true；否则返回值；否则返回值Value_if_false。Eg:= IF(A2100,”超出预算超出预算”,”预算内预算内”) 5.5 公式与函数公式与函数 56=IF(T

43、ODAY()-DATE(YEAR(TODAY(),MONTH(E3),DAY(E3)=0,YEAR(T ODA Y()-YEAR(E3),YEAR(TODAY()-YEAR(E3)-1)=IF(F1620,30,IF(F16=80、=60，电子班（等同于：，电子班（等同于：=电子班），可使用电子班），可使用“*”表示任意一串字符、表示任意一串字符、“？”表示任表示任意一个字符。意一个字符。 755.6 数据库管理与数据分析数据库管理与数据分析 使用使用计算条件计算条件时，只要在一列上的两个相邻空白时，只要在一列上的两个相邻空白单元格来组成条件区域，第一个单元格为条件标志，单元格来组成条件区域，

44、第一个单元格为条件标志，条件标志可以是空白或任意，但不能与数据清单中任条件标志可以是空白或任意，但不能与数据清单中任何一个字段名相同（否则会被系统当作比较条件）；何一个字段名相同（否则会被系统当作比较条件）；第二个单元格为计算条件，要求输入一个结果为逻辑第二个单元格为计算条件，要求输入一个结果为逻辑值值TRUE或或FALSE的公式，且必须引用数据清单中的的公式，且必须引用数据清单中的一个字段，引用的方法有两种：一个字段，引用的方法有两种：方法一：相对引用与条件有关的字段中第一条记录的方法一：相对引用与条件有关的字段中第一条记录的单元格。单元格。方法二：直接使用与条件有关的字段名（不加双引方法二

45、：直接使用与条件有关的字段名（不加双引号）。号）。765.6 数据库管理与数据分析数据库管理与数据分析 条件区域建立完毕，选定数据清单区域中任一单条件区域建立完毕，选定数据清单区域中任一单元格，再选择元格，再选择“数据数据”菜单菜单/“筛选筛选”子菜单子菜单/“高级筛高级筛选选”命令命令 进行高级筛选进行高级筛选“高级筛选高级筛选”对话框中各选项的含义是：对话框中各选项的含义是：选中选中“将筛选结果复制到其他位置将筛选结果复制到其他位置”，筛选结果不，筛选结果不在数据清单中显示，而是在另一个区域，用户要在在数据清单中显示，而是在另一个区域，用户要在“复制到复制到”处处输入目标区域的左上角单元格

46、输入目标区域的左上角单元格；“数据区域数据区域”是填入整个数据清单区域。是填入整个数据清单区域。775.6 数据库管理与数据分析数据库管理与数据分析 “条件区域条件区域”是填入前面建立的筛选条件所在的区域是填入前面建立的筛选条件所在的区域“选择不重复的记录选择不重复的记录”被选中时，如果有多条相同被选中时，如果有多条相同的记录符合条件，则只选出其中的一条（其他重复的记录符合条件，则只选出其中的一条（其他重复的记录不都选出）。的记录不都选出）。 数据库统计函数数据库统计函数 专门用于对数据清单中的数据进行统计分析，可以专门用于对数据清单中的数据进行统计分析，可以对符合给定条件的记录进行统计，不符

47、合的则不参对符合给定条件的记录进行统计，不符合的则不参与运算。与运算。数据库统计函数的语法形式为：数据库统计函数的语法形式为：（database，field，criteria） 785.6 数据库管理与数据分析数据库管理与数据分析 参数参数database是构成数据清单的区域；是构成数据清单的区域；field是统计的是统计的字段，即对字段，即对database中的哪一列进行统计运算；中的哪一列进行统计运算；criteria是条件区域，区域中是给定的条件。是条件区域，区域中是给定的条件。函数基本功能是根据函数基本功能是根据criteria给定的条件，对给定的条件，对database区域中符合条件

48、的记录的区域中符合条件的记录的field字段值进行统计运算。字段值进行统计运算。其中，统计字段其中，统计字段field有三种表示方式：有三种表示方式：（1）字段名所在的单元格地址。）字段名所在的单元格地址。（2）用双引号括起来的字段名。）用双引号括起来的字段名。（3）统计字段在数据清单区域中的序号，区域中最）统计字段在数据清单区域中的序号，区域中最左列序号为左列序号为1，向右逐列递增，向右逐列递增1。795.6 数据库管理与数据分析数据库管理与数据分析 建立建立criteria条件区域的要求与方法同高级筛选所使用的条件区域的要求与方法同高级筛选所使用的条件区域完全一致。条件区域完全一致。dat

49、abase一般取整个数据清单区域。一般取整个数据清单区域。 常用的数据库统计函数有：常用的数据库统计函数有：数据库计数函数：数据库计数函数： DCOUNT(database,field,criteria) DCOUNTA(database,field,criteria)DCOUNT只对数值进行计数只对数值进行计数DCOUNTA对各类型数据都进行计数对各类型数据都进行计数80数据库求和函数：数据库求和函数：DSUM(database,field,criteria)数据库平均值函数：数据库平均值函数： DAVERAGE(database,field,criteria)数据库最大值函数：数据库最大值

50、函数：DMAX(database,field,criteria)数据库最小值函数：数据库最小值函数：DMIN(database,field,criteria)5.6 数据库管理与数据分析数据库管理与数据分析 815.6 数据库管理与数据分析数据库管理与数据分析 分类汇总分类汇总必须先分类后汇总；必须先分类后汇总；分类是指对分类字段排序，使同类记录集中；分类是指对分类字段排序，使同类记录集中；再执行再执行“数据数据”/“分类汇总分类汇总”命令对排序字段汇总命令对排序字段汇总 移去分类汇总的结果时，可以在移去分类汇总的结果时，可以在“分类汇总分类汇总”对话对话框中单击框中单击“全部删除全部删除”按钮；按钮； 825.6 数据库管理与数据分析数据库管理与数据分析 数据透视表数据透视表 也是对数据清单中的数据进行汇总和分析。也是对数据清单中的数据进行汇总和分析。分类汇总分类汇总适用于按一个字段分类、对一个适用于按一个字段分类、对一个或多个字段进行汇总或多个字段进行汇总数据透视表数据透视表可按一个也可按多个字段进行可按