第2章 Excel高级操作

本章包括

◆ 公式与函数

◆ 常用函数使用讲解

◆ 数据有效性设置

◆ Excel图表

上一章讲解了Excel的基础知识及常用操作，并提及输入数据中的公式及函数。在Excel中，函数发挥着非常重要的作用，经常能简化很多操作。本章将讲解函数及其他一些Excel中的高级操作。

2.1 公式与函数

在Excel中，公式通常是指在单元格中以“=”号开头的运算式。例如，在单元格A1中输入“=1+1”，其中，“1+1”就是单元格中的公式。公式一般包含运算符、数值、单元格引用、括号或者函数等元素，可以用来处理常见的数据运算。

2.1.1 公式和函数概述

Excel在Office家族中的特色，就是其超强的计算及分析能力。Excel的计算分析功能，从简单的加减乘除，到复杂的数学分析，范围广泛。不论用户是初学者或者是专业人员，都可在其中找到合适的应用实例。而Excel的计算分析功能，大部分是通过使用公式及函数来完成的。

公式是对工作表中的数值进行计算的等式，通常以等号“=”开始。公式的最大长度为1024个字符。简单的公式如“=16+23-4”等只包含了常量和运算符，而在Excel办公中通常还包括函数和单元格的引用，如“=SUM（F2:F8）”等，其中“:”号两边即为单元格的位置，SUM为求和函数，这表示求F2单元格到F8单元格之间所有单元格数据的和。

公式的输入本书在第1章单元格的数据输入中讲解过，此处不再讲解。

函数是一些预定义的公式，主要用来完成一些复杂的数据运算，并返回运算后的结果。Excel 2007中提供了大量的内置函数，用户使用公式时，应尽量使用这些内置函数来达到简化办公、节省时间、提高效率的目的。

函数还可以使用户在应用中得到一些其他方式无法获得的数据。例如使用INFO（）函数可以返回当前操作环境的信息，如当前工作表目录、操作系统版本号等。

每个函数都具有相同的结构形式：函数名（参数1，参数2…）。

其中，函数名即函数的名称，每个函数名都是唯一标识一个函数的。函数名总是采用大写字母，但是输入函数时无须转换大小写，因为如果输入的是小写，Excel会自动把函数名转换成大写。参数就是函数的输入值，即为函数用来处理的数据。

按照参数的数量和使用来区分，函数可以分为无参数型和有参数型两种。无参数型如NOW（）函数返回当前的日期和时间，不需要参数。常用的一般都是有参数型。函数的参数必须出现在括号内，否则会产生错误信息。

2.1.2 插入函数

在Excel 2007中，内置函数都可以在“公式”选项卡“函数库”组中找到。用户要插入函数时，进入“公式”选项卡“函数库”组，根据需要的函数的分类，单击相应分类的按钮，展开该类函数的列表进行选择插入，也可以单击函数库左侧的插入函数按钮进行插入。

下面以插入求和函数SUM函数来讲解函数插入的具体步骤。本例将插入一个求和函数SUM，来求出数字23与36的和。

step 1 选取要进行插入函数的单元格例如B3，然后进入“公式”选项卡“函数库”组，单击“自动求和”按钮右侧的小三角，弹出函数列表中选择“求和”，可以看到在工作表B3中已经插入一个SUM函数，如图2-1所示。

图2-1 插入SUM函数

step 2 在函数名后的括号里输入“23,36”，按Enter键，结果如图2-2所示。

图2-2 函数处理结果

step 3 插入函数时，也可以单击“公式”选项卡“函数库”组的“插入函数”按钮，将会弹出如图2-3所示的“插入函数”对话框，在“或选择类别”下拉列表中选择“常用函数”选项，然后在函数列表中选择“SUM”即可在所选单元格插入SUM函数。

图2-3 “插入函数”对话框

step 4 下面把SUM的参数用单元格引用表示，讲解单元格引用参数的用法。在单元格B1中输入“23”，在B2中输入“36”，在B3中的SUM函数后的括号里输入“B1, B2”，输入完成后击回车键，结果如图2-4所示。

图2-4 单元格引用函数

step 5 如果要对一个单元格区域的数据求和，只需要填入区域左上角单元格位置和右下角单元格位置，中间用“:”连接即可。

一般函数要输入的参数都会用向导的方式来提醒用户要求输入，可以在参数的输入框内看到该参数的值类型及用途。函数参数名一般都用英文提示，所以本书所有公式函数的表达式中，参数名都用Excel 2007中提示的英文名，可以在模仿实例学习插入参数的时候更方便。

2.1.3 编辑/删除函数

在Excel 2007中，如果要编辑函数，可直接选取函数所在单元格。此时，函数公式会显示在上方编辑栏中。可以直接在编辑栏中编辑函数及其参数，编辑完毕后按Enter键即可。

在Excel 2007中删除函数，同样可以在选取函数单元格后直接删除，也可以通过在所选单元格上单击鼠标右键后通过菜单来删除。

2.1.4 公式的错误值分析

在Excel 2007中，如果单元格中输入的公式有错误，Excel会在单元格的左上角呈现一个绿色的三角形，同时在单元格中也会显示出当前的错误值。选中该单元格时将自动地出现提示按钮。常见的错误及产生错误的可能原因有如下几种。

◆ 错误显示“#VALUE! ”。错误原因是当公式需要数字或逻辑值时，却输入了文本。

◆ 错误显示“DIV/0”。错误原因是输入的公式中包含明显的被零除的式子，例如“=12/0”。或者使用对空白单元格或者包含零的单元格的引用做除数。

◆ 错误显示“#####! ”。错误原因是公式计算的结果太长，超出了单元格的字符范围。

◆ 错误显示“N/A”。错误原因可能是公式中没有可用的数值或者缺少函数参数。

◆ 错误显示“#NAME? ”。错误原因是删除了公式中使用的名称，使用了不存在的名称或者名称的拼写有错误。

◆ 错误显示“#NULL! ”。错误原因是使用了不正确的区域运算符或者不正确的单元格引用。

◆ 错误显示“#NUM! ”。错误原因是使用了不能接受的参数，或者公式计算结果的数字太大、太小，无法显示。

◆ 错误显示“#REF! ”。错误原因是删除了由其他公式引用的单元格。

在Excel 2007中，单击错误按钮 即可弹出如图2-5所示菜单，通过此菜单可进行错误查看和修改。

图2-5 错误按钮菜单

还可以单击“错误检查选项”命令进入Excel选项中公式错误的设定，如图2-6所示。

图2-6 Excel选项公式设置

2.2 常用函数使用讲解

Excel 2007中提供了大量函数，大致包括数学与三角函数、日期与时间函数、财务函数、文本函数、查询与引用函数、统计函数、逻辑函数、数据库函数、信息函数、工程函数及多维数据集函数等。本节将在实例的基础上讲解每一类函数的常用方法。如果有些函数没有讲解，可以自行在插入函数对话框中选择该函数，即可在对话框下部看到该函数的简介。

2.2.1 数学与三角函数

在办公中，有时需要在Excel工作表中进行各种数学运算。Excel 2007提供了大量内置的数学和三角函数。在一些复杂的数学运算中使用这些函数可以大大提高运算效率。上一节以SUM函数讲解了函数的使用，故此处不再讲解该函数。下面介绍一些常用的数学与三角函数。

1. 取绝对值函数（ABS）

该函数返回的是函数参数的绝对值，即该数据的不带符号的数值。如果是负数，则返回它的相反数；如果为正数或零，则返回它本身。例如ABS（-5）=5、ABS（6）=6、ABS（32-45）=13。

ABS函数只有一个参数。函数参数放在函数后部的括号中。该函数在办公中一般多用于计算两个数值之间的差距。实现方式一般是将两个数值放入两个单元格中，然后在ABS函数的参数中用两个单元格的引用来进行运算。

ABS函数的相关计算如图2-7所示。

图2-7 ABS函数的相关计算

其中，A6中的ABS函数参数即为单元格的引用的运算。

2. 向上、向下舍入函数（CEILING, FLOOR）

向上、向下舍入函数（CEILING, FLOOR）的功能是将某个数值按照条件沿数值绝对值增大、减小的方向舍入，求在该条件下最近的数。其中，CEILING函数为向上舍入，FLOOR为向下舍入。其表达式分别为：

      CEILING(number, significance)
      FLOOR(number, significance)

其中，number为要进行舍入操作的参数，而significance为进行舍入的条件，表示需要舍入的乘数。当进行舍入操作时，最近数必须为该乘数的倍数。例如“CEILING（3.9,1）”表示要将3.9向上舍入到最接近1的倍数，结果为4。如果数字本身为乘数的倍数，则不进行舍入。如果乘数为非数值类型或者符号不同，函数会进行报错。具体应用实例如图2-8所示。

图2-8 舍入函数实例

3. 指数函数

常用的指数函数有两个：EXP和POWER。其表达式分别为：

      EXP（number）
      POWER（number, power）

其中，EXP函数返回的值为e的number次方；e为自然对数的底数，其值为常数2.71828182845904。POWER函数的返回值为number的power次方。具体应用实例如图2-9所示。

图2-9 指数函数实例

注意，与指数函数相对应的有自然对数函数LN、对数函数LOG，应用方法与指数函数类似，此处不再讲解。

4. 正三角函数

常用正三角函数SIN、COS和TAN，分别用来计算指定角度的正弦值、余弦值和正切值。它们都只有一个参数，即要进行计算的弧度。如果用户要输入的参数为度，可将输入的参数再乘以PI（）/180。图2-10为SIN的应用实例。

图2-10 正弦函数SlN实例

2.2.2 日期与时间函数

在日常办公中，很多时候都要制作报表。在制作这些表格时，经常要用到日期和时间并要对它们进行计算。Excel中提供了大量的日期和时间函数，它们的功能就是获得、显示和计算日期和时间，例如DATE、NOW、DATEVALUE、TODAY、YEAR、MONTH、DAY、WEEKDAY等。本节将具体介绍这些函数的功能。

在Excel中，日期是以连续的序列数来存储和计算的。默认条件下1900年1月1日的序列数为1即为起始日，而1900年2月26日的序列数则是57，因为它是起始日之后的第57天。但由于Excel中在1900年2月时，会误判1900年为闰年，所以2月29日有效。在1900年3月1日之后的序列数都比实际的序列数多1。这在计算机的计算中造成的问题不大。

在用户输入日期时，Excel会自动判断这个日期是否有效，如果无效则将这个日期作为字符串处理，如2001年2月29日。另外1900年1月1日以前的日期在Excel中也是无效的。

在Excel中，时间的序列数是用小数来表示的。从0到1。如序列数0.5表示正午12点整、0.625表示下午3点整等。

1. 显示日期及其序列数函数DATE

在Excel 2007中，如果要显示一个日期或者其序列数，需要用到DATE函数，其表达式为：

      DATE（year, month, day）

其中，year、month、day分别表示日期的年、月、日。默认条件下DATE函数显示的是参数表示的日期，如果要显示其序列数可用如下方法实现。选中函数所在的单元格，进入“开始”选项卡“单元格”组，单击“格式”按钮，在下拉菜单中选择“设置单元格格式”，然后在弹出的选项框左侧分类里选择“常规”，单击“确定”按钮即可。

在Excel 2007中，年的范围为1900年到9999年，如果年为0～1899年之间，则Excel会默认该年为加上1900年之后的年份，例如28年，则为1928年。如果年份超过了9999年，Excel会自动报错。月份和日期如果超出，会自动往下一年或者下个月递推，例如2005年13月12日，Excel会自动转换为2006年1月12日。DATE函数具体应用如图2-11所示。

图2-11 DATE应用实例

2. 获得当前日期和时间函数NOW

如果要获得计算机当前的日期和时间，可以使用NOW函数。NOW函数没有参数，其表达式如下：

      NOW（）

NOW函数操作非常简单，其具体实例如图2-12所示。

图2-12 NOW函数实例

3. 日期提取年份函数YEAR

在日期计算中，有时候只需要计算年份，例如计算某人的年龄，此时需要用到YEAR函数。YEAR函数的功能就是从一个日期中提取日期所在的年份，日期也可以是一个序列数，也可以是单元格引用。在使用该函数时，必须把单元格的格式设置为常规，否则会出现显示错误。YEAR的具体应用如图2-13所示。

图2-13 YEAR函数实例

4. 确定星期函数WEEKDAY

在Excel办公应用中，安排日程经常要用到星期的确定。函数WEEKDAY能很好地帮助用户更准确地确定星期。该函数的表达式如下：

      WEEKDAY(serial_number, [return_type])

其中，serial_number为要进行计算星期的具体日期，return_type省略默认为1，表示返回值是1时表示星期日，7表示为星期六。如果将return_type设置为2，则表示返回1表示星期一，7表示星期日；如果设置为3，则表示返回0表示星期一，6表示星期日。具体应用实例如图2-14所示。

图2-14 WEEKDAY函数实例

图2-14中，B2的结果为3，因为没有设置return_type，所以默认为1，因此表示为星期二。

5. 时间转换小时函数HOUR

HOUR函数用来从时间或者序列数中提取小时，返回一个0到23的整数。其表达式如下：

      HOUR(serial_number)

其中，serial_number表示的是要进行提取小时的时间或者时间序列数，也可以用单元格引用代替。HOUR函数应用实例如图2-15所示。

图2-15 HOUR函数实例

2.2.3 财务函数

Excel办公最重要的方面就是财务工作。Excel提供的功能能让相当烦琐、复杂的财务计算和财务分析工作变得简单、容易，能大大提高财务处理的速度和效率。这些要归功于Excel中提供了大量的财务处理函数。本节将介绍常用的财务函数，并以实例方式，让用户举一反三学习其他财务函数。

1. 借贷函数PMT

PMT函数的功能是返回固定利率投资或者贷款的等额分期偿还额。在PMT函数的参数中，规定固定的利率及分期付款的方式。其表达式如下。

      PMT(rate, nper, pv, fv, type)

其中，rate表示贷款利率，nper表示还款的期数即分多少期还完。pv表示本金，也称现值，即一些列未来付款的当前值的累积和。fv表示未来值即在最后一次付款后希望得到的现金余额，如果省略该参数则默认为0。Type表示分期付款的还款是在期末还是期初。如果设置为0或者省略，则表示在期末，如果为1，则表示在期初。下面通过实例来学习该函数的应用。

【例1】 某人目前的存款余额为200000元，他打算每个月固定存入一定金额的钱，希望在10年之后能达到1000000。银行的月利率为0.4%，则每个月他要存入多少钱？

各项参数如图2-16所示。

图2-16 PMT实例

将各项参数代入PMT函数，求得结果为4407.25元。需要注意的是，在Excel 2007中钱的正负号由钱的流通方向决定。如果是付出去的钱，则为负号；如果是收进来的钱，则为正号，如图2-16中，已经有200000元的存款可以认为是已经付出了200000元，将来要拿回1000000元则为正的。算出每个月要付出4407.25元也转换成为负数。这一点在财务函数的学习中要特别注意。

另外，在PMT函数的应用中要注意期数与利率的一致性。年利率的条件下，几年则为几期；月利率的条件下，几年即为几年＊12期，如图2-16中的nper参数。

【例2】 某人从银行贷款500000元，分4年偿还，年利率为5%，如果为等额还款，问这个人每年需要还多少钱？

分析各参数如图2-17所示。

图2-17 分期付款实例

如图2-17所示，计算出来每年应还款141005.92元。

2. 定存计算函数FV、PV

FV函数实现的功能是在已经存款即利率计算能得多少钱，而PV是已知利率及要得到的钱，计算应定存多少钱。它们的表达式分别如下：

      FV(rate, nper, pmt, pv, type)
      PV(rate, nper, pmt, Fv, type)

其参数含义与PMT函数的含义相同。下面讲解两个实例，说明这两个函数的功能及其应用方式。

【例3】 某人在银行存入50000元钱，银行的年利率为6%，问4年以后，他能得到多少钱？

分析参数如图2-18所示。

图2-18 FV函数实例

计算结果为该人能得到63123.85元。

【例4】 某人想向银行定存一笔钱，他想在5年之后获得100000元，银行的年利率为6%，问他应该存多少钱？

分析参数如图2-19所示。

图2-19 PV函数实例

计算结果，该人应存入74725.82元钱。

3. 计算利率函数RATE

【例5】 如果某人在银行中存入200000元钱，他想在5年后获得220000元，问银行的利率要多少才可以？

解决这一类问题，要用到Excel中计算利率的函数RATE。它的表达式如下：

      RATE(nper, pmt, pv, fv, type)

参数定义与PMT中参数相同。分析如图2-20所示。

图2-20 RATE函数实例

经过计算，年利率RATE应该为2%。

4. 计算存款期数函数NPER

已知存款金额、利率和要得到的钱的数额，如何计算存款的期数？这个问题可以用Excel中计算存款期数的函数NPER来完成。NPER函数的表达式如下：

      NPER(rate, pmt, pv, fv, type)

通过下面的实例来了解NPER的计算。

【例6】 某人在银行存了200000元，年利率为4%，问他多少年才可以拿到250000元？参数分析如图1-21所示。

图2-21 NPER函数实例

求得结果为5.689431256。

2.2.4 查找与引用函数

当要处理的数据非常庞大与烦琐时，想要查找到某个数据的操作显得非常麻烦。因此Excel提供了许多查找与引用函数，可以让用户在一个表格或数组中查询，并返回想要的数据。本节将介绍这些函数，如图2-22所示为插入函数时函数分类中的查找与引用函数。

图2-22 查找与引用函数

1. 垂直查表函数VLOOKUP

VLOOKUP函数实现的功能是从一个数组或者表格的最左列中查找含有某特定值的字段，再返回同一行中某一指定单元格的值。该函数的表达式如下：

      VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

其中，lookup_value为需要在表格或者数组第一列中要查找的数值，可以是数值、字符串或者单元格引用。table_array是要进行查找的数据表或数组，它可以是数据表，也可以是单元格区域。col_index_num就是要返回的单元格的列的位置，即返回值的列位于查找的数据的第几列。如果该数值大于或者小于要进行查找的列的总数，Excel会报错。range_lookup是一个逻辑值。如果为True或者省略，则表明查找的时候是近似匹配。如果查找不到look_value的精确匹配值，就匹配部分符合或者小于该数值的最大值。如果为False，则匹配与lookup_value精确相等的值。

下面用一个实例来讲解VLOOKUP函数的功能及应用。

【例7】 如图2-23所示，有一个员工工资表，需要在G列工资等级中列出各员工的工资等级。详细等级分级制度已在表中。

图2-23 员工表

在这个应用中，需要进行的操作就是根据F列中的工资，在I2:K8这个单元格区域中查找这个工资属于的等级，然后返回这个单元区域的第3列的数据，即K列的等级。

因为工资等级的划分是一个区域，所以采用近似匹配，例如一个人的工资是3600，那么它能匹配的就是数据区域中第一行小于3600的最大值，即3500等级D，如果是4200，那么匹配的就是4000等级C。

各参数分别为：look_value为F2，此处采用相对应用，操作时只需用鼠标进行下拖即可复制套用公式。数据表$I$2:$K$8，采用绝对引用。如果对这两个引用还不是很明白可翻看本书第1章中单元格的引用。返回的是数据的第3列，所以col_index_num为3。另外，本例采用近似匹配，range_lookup可以省略或者为True。

最终公式为“=VLOOKUP（F2, $I$2:$K$8,3）”。将此公式代入G列中，并对该列单元格进行套用公式，得到结果如图2-24所示。

图2-24 工资等级

运用VLOOKUP函数在本例中还可以实现的功能是，输入一个人的姓名，显示他的年龄及工资，操作如图2-25所示。

图2-25 查找应用

在B17输入姓名之后，按下回车键就会在B18单元格显示年龄，B19显示工资，结果如图2-26所示。

图2-26 查找结果

2. 查表函数LOOKUP

相比较而言，查表函数LOOKUP函数比VLOOKUP、HLOOKUP函数的应用更具有灵活性。因为VLOOKUP、HLOOKUP函数只能匹配最左列或者最上行的数据来查询。LOOKUP函数有两种语法表达式：向量和数组。一般是向量形式的比较常用。

向量形式的LOOKUP表达式如下：

      LOOKUP(lookup_value, lookup_vector, result_vector)

其中，lookup_value是进行查找匹配的值，lookup_vector是一个单行或者单列的单元格范围，这个范围的数据必须以递增的方式排列，否则不能返回正确的值。result_vector也是一个单行或者单列的数据，其范围大小应该与look_vector相同。

LOOKUP函数在lookup_vector进行查找时，如果无法找到与lookup_value完全相等的精确值，将进行近似查找，即查找仅次于lookup_value的值。

再次以图2-23所示的员工表示例应用LOOKUP函数。在评等级时，运用LOOKUP公式“=LOOKUP（F2, $I$2:$I$8, $K$2:$K$8）”，得到结果如图2-27所示。

图2-27 LOOKUP函数查找

在编辑栏上显示的即为包含LOOKUP函数的公式。同样，在图2-25所示的实例中也可以用LOOKUP函数实现，可以用公式“=LOOKUP（B17, B2:B13, F2:F13）”。具体操作可以依据VLOOKUP函数实例，此处不再赘述。

3. 比较函数MATCH

比较函数MATCH的功能是获得在指定方式下与指定数值匹配的数组中元素的相应位置，其表达式如下：

      MATCH(lookup_value, lookup_array, match_type)

其中，lookup_value为要在数据表look_array中查找的数据，可以为文本、数值及单元格引用。look_array为要查找的单元格区域，应为数组或者数组引用。match_type为查找方式，它有3个值。省略match_type则默认为1，表明查找时查找小于或者等于lookup_value的最大值，此时look_array必须为升序排列。如果match_type为0，表明查找等于lookup_value的第一个数值，look_array排列顺序不限。如果match_type为-1，则表明查找大于或者等于lookup_value的最小数值，此时look_array必须为降序排列。

【例8】 在如图2-28所示数据表格中查找姓陈的员工的位置。

图2-28 MATCH函数实例

如图所示，查找数据为“陈*”, “*”代表匹配任意字符序列。由于查找姓名，所以look_array应该为B2:B13。由于使用了通配符，所以match_type应为0。输入公式“=MATCH（"陈*", B2:B13,0）”，查找结果如图所示A17为“4”，表示在查找区域的第四列。

4. 获得列标、行号函数COLUMN、ROW

COLUMN、ROW函数的功能分别是返回指定单元格引用的列标和行号。其表达式分别如下：

      COLUMN(reference)
      ROW(reference)

COLUMN、ROW函数都只有一个参数，reference为指定的单元格引用或者单元格区域。如果省略此参数，则返回函数本身所在单元格的列标或行号。如果参数为单元格区域，那么函数将返回一个包含该区域所有列标的数组。要正确显示该数组，可先选中要显示该数组的单元格，然后直接输入公式，输入完后同时按Ctrl+Shift+Enter组合键获得结果。如果只按Enter键，则只显示结果中的第一个列标。

该函数具体应用范例如图2-29所示。

图2-29 COLUMN、ROW应用范例

2.2.5 统计函数

在Excel办公中，总会遇到一些需要进行统计分析的问题，Excel 2007提供了很多统计函数。使用这些统计函数可以对数据区域进行统计分析，计算所有的标准统计值，如平均值、最大值、最小值和标准偏差等，还可以进行各种分布分析。本节主要介绍这些函数的功能及应用。

1. 求平均值函数AVERAGE、AVERAGEA

使用AVERAGE函数可以得到指定区域内包含数据的单元格的平均值。其表达式如下：

      AVERAGE(number1, number2, …)

其中，number1、number2表示需要进行计算平均值的1到30个参数。参数可以是数字，也可以是单元格引用或者单元格区域。在单元格引用或者单元格区域、数组中，如果值为文本、逻辑值或者空白单元格，这些值将被忽略不进行统计，另外包含零值的单元格将计算在内。其具体应用范例如图2-30所示。

图2-30 AVERAGE函数范例

与AVERAGE功能类似的还有函数AVERAGEA，该函数的功能是计算所有非空单元格的数据的平均值。如果数据为文本，则当作数值1；如果为逻辑值TRUE，则当作数值1；如果为逻辑值FALSE，则当作数值0。AVERAGEA计算表达式与AVERAGE相同，此处不再讲解，可以根据AVERAGE函数范例自行学习。

2. 最大值函数MAX、最小值函数MIN

最大值函数MAX、最小值函数MIN分别用来计算指定范围内数据的最大值、最小值，其表达式如下：

      MAX(number1, number2, …)
      MIN(number1, number2, …)

其中，参数number1、number2等可以是数值、文本、逻辑值、单元格引用或者单元格区域。如果值为文本或者逻辑值，那么将会被忽略，具体范例如图2-31所示。

图2-31 MAX、MlN函数范例

3. 统计单元格函数COUNT、COUNTA、COUNTBLANK

COUNT用来统计参数指定数据区域中数值的单元格的个数。其表达式如下：

      COUNT(value1, value2, …)

其中，value1、value2可以是数字、单元格引用或者单元格区域。在统计时，如果单元格的数据为文本、逻辑值、错误值或者空白，那么将忽略不进行统计。

COUNTA用来统计参数指定数据区域中所有非空单元格的个数。其表达式如下：

      COUNTA(value1, value2, …)

其中，value1、value2可以是数字、单元格引用或者单元格区域。在统计时，当单元格为空白单元格时，将忽略不进行统计。

COUNTBLANK用来统计参数指定数据区域中所有空白单元格的个数，其表达式如下：

      COUNTBLANK(range)

其中，range为要进行统计空白单元格的数据区域。

COUNT、COUNTA、COUNTBLANK经常用来统计人数，如考试、考勤等。

【例9】 设计公式统计图2-32考试数据表中应参加考试人数、实际参加考试人数及缺考人数。默认缺考时成绩为空。

图2-32 考试数据表

根据题意得知，在D2:D13单元格区域内，实际参加考试人数=有成绩的单元格数，缺考人数=空白单元格数，应参加考试人数=实际参加考试人数+缺考人数。统计公式如图2-33所示，得出正确结果。

图2-33 统计结果

4. 样本偏差函数STDEV

样本偏差函数STDEV的功能是衡量指定数据与平均数之间的差异量数。其表达式如下：

      STDEV(number1, number2, …)

其中，number1, number2等表示要进行衡量的数据，可以是数字，也可以是单元格引用。进行样本偏差计算时，如果单元格的值为文本或逻辑值时，那么这个单元格将被忽略。

函数STDEV中参数表示总体中的样本。如果参数代表整个总体，则应该使用标准偏差函数STDEVP来计算。样本偏差可以统计样本数据的稳定性，及考察数据的上下波动是否很大。

如图2-34计算员工工资样本偏差。

图2-34 工资样本偏差

2.2.6 逻辑函数

在Excel中，逻辑函数用来判断条件是否成立，通过进行多重条件的检查从而决定下一步的动作。Excel 2007提供7种逻辑函数，包含AND、FALSE、IF、IFERROR、NOT、OR、TRUE。本节将讲解这些函数的功能及具体应用。

1. 逻辑与函数AND

逻辑与函数的功能是检查是否所有参数都为TRUE，如果都为TRUE则返回TRUE，否则返回FALSE。其表达式如下：

      AND(logical1, logical2, …)

如果所有参数都为TRUE，则返回TRUE；只要有一个参数的逻辑值为FALSE，则返回FALSE。参数可以为逻辑值，也可以是包含逻辑值的数值或者单元格引用。图2-35所示为AND函数范例。

图2-35 AND函数范例

与AND函数用法类似，逻辑或函数OR表示只要有一个参数为真，返回值即为真。逻辑非函数NOT表示取指定逻辑值的相反值，如果数据为TRUE，则返回FALSE；如果数据为FALSE，则返回TRUE。此处不再多讲其用法。

2. 逻辑值函数TRUE、FALSE

Excel 2007中提供两个逻辑值函数：TRUE（）和FALSE（），分别用来取得逻辑值TRUE和FALSE。它们没有参数。

3. 逻辑判断函数IF、IFERROR

逻辑判断函数IF的功能是判断指定值的真假，然后根据判断结果返回不同的结果。其表达式如下：

      IF(logical_test, value_if_true, value_if_false

其中，参数logical_test为要进行判断的逻辑值。如果logical_test的值为TRUE，则返回第二个参数value_if_true的值；如果logical_test的值为FALSE，则返回第三个参数value_if_false的值。

如图2-36所示，根据成绩判断给出等级，这里只有两个等级A和B，如果大于90就是A，否则就是B。

图2-36 lF函数范例

逻辑判断函数IFERROR是用来判断指定单元格的数据是否错误，并给出相应操作的函数。其表达式如下：

      IFERROR(value, value_if_error)

其中，value为要进行判断的数据，如果它是一个错误值，那么将返回value_if_error，否则返回数据本身value的值。其应用范例如图2-37所示。

图2-37 lFERROR函数范例

2.2.7 文本函数

Excel办公中，除了数字问题的处理外，文本的处理也是非常重要的。文本函数是针对文本字符串进行一系列操作的函数，能对已有的信息加以处理和应用，而且还能实现不同数据类型之间的转换。Excel 2007提供了很多文本函数，可以精确地处理文本字符串。这些函数在VBA的学习过程中会应用到很多，本节将对这些函数的功能及应用进行讲解。

1. 代码转换函数CODE、CHAR

CODE函数是将文本的第一个字符转换为数字代码，其表达式如下：其中，text为要进行转换的文本。例如CODE（“B”）。码32～255所代表的字符在Excel 2007中可以在“插入”选项卡“文本”组，单击“符号”按钮即可查看。

      CODE(text)

CHAR函数是将1～255代码转换为对应的字符，例如CHAR（65）。

CHAR函数与CODE函数可以相互转换，但是CHAR函数只能转换代码1～255所代表的字符，而CODE没有限制，还可以转换汉字代表的代码。

图2-38为CODE函数和CHAR函数的应用范例。

图2-38 CODE函数及CHAR函数范例

2. 字符串函数LEFT、RIGHT、MID

字符串函数LEFT、RIGHT、MID的功能分别为从左、右、指定位置在指定字符串中取出指定字符数的文本。其表达式分别如下：

      LEFT(text, num_chars)
      RIGHT(text, num_chars)
      MID(text, start_num, num_chars)

其中，text是原始字符串，num_chars为要取出的字符串的数量，start_num为从字符串的第几个字符开始取，该数值必须为1到字符长度，否则会返回空文本。num_chars必须大于0，如果大于字符长度，则会返回所有字符。

图2-39所示为LEFT、RIGHT、MID函数范例。

图2-39 字符串函数范例

3. 文本替换函数SUBSTITUTE

在Excel中查找与替换可以使用自带的工具，不过有时需要使用公式来完成。Excel 2007中的文本替换函数一般使用SUBSTITUTE函数。SUBSTITUTE函数的功能是替换指定文本中特定字符。其表达式如下：

      SUBSTITUTE(text, old_text, new_text, instance_num)

其中，text为原始字符串，old_text为原始文本中的特定字符，new_text为要替换进文本的新字符串。如果text中含有多组old_text，可以使用instance_num来指定替换第几组old_text。如果没有指定，则替换全部old_text。

图2-40为SUBSTITUTE函数应用范例。

图2-40 SUBSTlTUTE函数范例

4. 文本重复显示函数REPT

使用REPT函数可以重复显示指定的字符串，其表达式如下：

      REPT(text, number_times)

其中，text为要重复的文本，number_times为要重复的次数。如果number_times为0，将返回空文本。图2-41为REPT函数应用范例。

图2-41 REPT函数范例

2.2.8 数据库函数

数据库是数据的一种组织形式。在数据库中能更快捷、方便、有效、安全地管理数据。数据库是现代信息世界数据的最优解决方案。关于数据库的详细信息本书将在第15章中讲解。本节将讲解在Excel中以简单工作表作为数据库应用的一些数据库函数。

在Excel 2007中建立数据库非常简单，只需输入一行标题作为数据库的字段，再输入数据，即可完成，如图2-42即为Excel中的一个简单数据库。

图2-42 简单数据库

1. 求列平均值函数DAVERAGE

DAVERAGE函数用来计算数据库中满足指定条件的指定字段数据的平均值，其表达式如下：其中，database表示构成数据库的单元格区域。field表示要进行计算的数据库中的字段，它可以是列的标志文本，也可以代表列的位置的数字。criteria表示一组包含给定条件的单元格区域。

      DAVERAGE(database, field, criteria)

【例10】 在图2-42中所示的数据库中应用DAVERAGE函数，求出性别为女的员工的平均工资。

输入条件“性别 女”如图2-43所示。

图2-43 平均值条件

然后输入公式“=DAVERAGE（A1:F13, "工资", H1:H2）”，如图2-44所示，即可求出性别为女的员工的平均工资。

图2-44 DAVERAGE函数范例

2. 求列数据和函数DSUM

DSUM函数用来计算数据库中满足指定条件的列的和，其表达式如下：

      DSUM(database, field, criteria)

其中，database为构成数据库的单元格区域，field表示要进行计算的数据库字段，criteria为包含指定条件的单元格区域。

【例11】 计算如图2-42所示员工数据库中2004年加入公司的员工的工资的总和。

先给定条件“加入年份2004”，如图2-45所示。

图2-45 求和条件

然后输入公式“=DAVERAGE（A1:F13, "工资", H1:H2）”，结果如图2-46所示。

图2-46 DSUM函数范例

3. 提取记录函数DGET

DGET函数的功能是提取数据库列中符合指定条件的记录值，其表达式如下：

      DGET(database, field, criteria)

其中，database为构成数据库的单元格区域，field为要提取值所在的列，criteria为包含给定条件的单元格区域。

【例12】 提取如图2-42所示的员工表中员工号为20041020的员工的工资。

给定条件“员工号20041020”，如图2-47所示。

图2-47 提取条件

然后输入公式“=DGET（A1:F13, "工资", H1:H2）”，如图2-48所示，即可获得员工号为20041020的员工的工资。

图2-48 DGET函数范例

2.2.9 信息函数

Excel办公中经常要对单元格的信息进行判断，为此Excel提供了内置信息函数。信息函数能确定单元格数据的数据类型及判断数据的一些必要信息。本节将介绍这些函数的功能及应用。

1. 单元格内容的判断函数

应用信息函数可以判断单元格的内容，例如是否为空单元格、数值、文本、逻辑值及错误值。

函数ISBLANK的功能是判断单元格是否为空白单元格，其表达式如下：

      ISBLANK(value)

其中，value为要进行判断的单元格。如果该单元格为空白单元格，则返回TRUE，否则返回FALSE。

单元格内容判断函数应用范例如图2-49所示。

图2-49 单元格内容判断函数范例

其中，数值判断函数ISNUMBER、文本判断函数ISTEXT、逻辑值判断函数ISLOGICAL、错误值判断函数ISERR、偶数判断函数ISEVEN，还有奇数判断函数ISODD，这些函数的用法及参数意义与ISBLANK函数相同。可以根据ISBLANK进行学习。

2. 单元格信息函数CELL

CELL函数的功能是获得参数引用区域的左上角单元格的格式、位置或内容等信息，其表达式如下：

      CELL(info_type, reference)

其中，info_type是一个文本值，给定要返回的单元格信息的类型，其值及返回类型如图2-50所示。Reference为要获取信息的单元格或区域，如果省略则表示要获取最后更改的单元格。

图2-50 参数info_type的值表

如图2-51所示，返回单元格的列宽。

图2-51 CELL函数求列宽

Info_type的其他参数可以根据自己办公需在图2-50中选择试用，此处不再一一讲解。

3. 获取操作环境信息函数INFO

INFO函数的功能是获取有关当前操作环境的信息，其表达式如下：

      INFO(type_text)

其中，type_text为该函数的唯一参数，表示要返回的操作环境信息的类型，其值和对应的返回类型，如图2-52所示。

图2-52 type_text及其对应返回类型

图2-53所示为INFO函数的应用范例。

图2-53 lNFO函数范例

2.2.10 工程函数

在Excel 2007中，工程函数一般用于工程分析、进制转换及复数的处理等方面。工程函数是一类非常专业的函数，一般非专业用不到，本节将对工程函数的一些常用功能及应用进行讲解。

1. 进制转换类函数

常用的进制有二进制、八进制、十进制及十六进制。Excel中提供了这四种进制之间相互转换的函数。此处以十进制转换为二进制函数DEC2BIN为例讲解。

十进制转换为二进制函数DEC2BIN，其表达式如下：

      DEC2BIN(number, places)

其中，参数number为要转换的十进制数，places为要使用的字符数，如果省略places参数，则表示返回结果用最少字符数表示。当要在返回的数值前置零时，要用到该参数。DEC2BIN能返回10位二进制数，其中最高位为符号位，其余9位为数值位。如果是负数，则用二进制数的补码表示。当要转换的数字小于-512或者大于511，或者不是数值型时，函数将出现错误。如果参数places为负值，参数也将出错。

DEC2BIN函数具体应用如图2-54所示。

图2-54 DEC2BlN函数范例

十进制转换为八进制函数DEC2OCT、十进制转换为十六进制函数DEC2HEX、二进制转换为八进制函数BIN2OCT、二进制转换为十进制函数BIN2DEC、二进制转换为十六进制函数BIN2HEX等用法与DEC2BIN函数相同，只是参数的范围要多加注意，此处不再一一进行讲解。

2. 复数计算函数

复数的结构分为实数部分与虚数部分。形式如a+bi，其中a、b都属于实数，i称为虚数单位。一些专业的计算中经常要用到复数的计算，如复数的和与差、求共轭复数等。

复数的和与差计算函数为IMSUM、IMSUB，其表达式分别如下：

      IMSUM(inumber1, inumber2…)
      IMSUB(inumber1, inumber2…)

其中，参数inumber1、inumber2均为要进行计算的复数，最多有29个参数。IMSUM、IMSUB函数应用范例如图2-55所示。

图2-55 lMSUM及lMSUB函数范例

求复数的共轭复数函数IMCONJUGATE，其表达式如下。

      IMCONJUGATE(inumber)

其中，inumber为要求共轭复数的原始复数。IMCONJUGATE应用范例如图2-56所示。

图2-56 lMCONJUGATE应用范例

3. 检测两个值相等函数DELTA

DELTA函数的功能是检测两数值是否相等，其表达式如下：

      DELTA(number1, number2)

其中，参数number1、number2为要进行比较的两个值。Number2省略则默认为0。如果两个参数相等则返回1，不相等则返回0。如果两个参数任意一个为非数值型，则函数出错。DELTA函数应用范例如图2-57所示。

图2-57 DELTA函数应用范例

2.3 数据有效性设置

有些时候，为了减少数据的错误，增加数据的准确性，在用户输入工作表数据之前，进行数据限制，只有满足指定条件的数据才能存放在单元格内；且在输入了错误数据时，进行错误原因的提示。这就是Excel中数据有效性的设置。

通过下面的实例来讲解设置数据有效性的详细步骤。

step 1 选定要进行有效性设置的单元格区域如本例中的工资列数据，然后进入“数据”选项卡“数据工具”组，单击“数据有效性”按钮，弹出如图2-58所示的快捷菜单。

图2-58 “数据有效性”下拉菜单

step 2 选择“数据有效性”命令，将弹出如图2-59所示的对话框。

图2-59 “数据有效性”对话框

step 3 在“数据有效性”对话框中“设置”选项卡是对数据有效性的值的范围设置，可以根据实际办公需要进行设置。本例设置数值居于1000～10000的整数，则在“允许”列表中选择“整数”，在最小值填入1000，最大值填入10000，如图2-60所示。

图2-60 数值范围设置

step 4 “输入信息”选项是输入时出现在输入单元格下方的提示信息，本例中设置如图2-61所示。

图2-61 “输入信息”设置

step 5 “出错警告”选项是设置当输入错误数据时弹出的警告提示框信息，如图2-62设置。其中样式是指提示框的形式，样式不同，警告框的按钮不同，可以根据自己喜好选择。

图2-62 “出错警告”信息设置

step 6 “输入法模式”设置是针对某些特殊的输入，如只输入英文等，此处采取默认设置“随意”。设置完毕后单击“确定”按钮，单击工资列的数据，出现如图2-63所示信息。

图2-63 输入时提示信息

step 7 输入一个500，出现如图2-64所示情况。数据有效性设置成功。

图2-64 出错提示

2.4 Excel图表

利用图表来显示数据，能更清晰、明了地表达出数据的变化、比较及数据间的关系。因此在办公应用时，图表是经常要用到的工具。

Excel 2007中图表的创建很简单。选中要转换图表的数据，进入“插入”选项卡“图表”组，然后选择相应图表的图形即可进行创建。

图2-65为某公司员工8月份的工资及奖金表，根据该数据表创建一个比较图表，其具体步骤如下。

图2-65 员工工资表

step 1 选中所有数据区域，然后进入“插入”选项卡“图表”组，单击“柱形图”按钮，弹出如图2-66所示菜单。

图2-66 柱形图菜单

step 2 在图2-66中选择需要的图形类型，此处选择第一个即可。

图2-67 生成图表

step 3 由于此处只需要“工资、奖金”系列及水平的“姓名”行，所以选中右侧的“年龄、工资、奖金”系列选项，单击右键，从快捷菜单中选择“选择数据”命令，弹出如图2-68所示对话框。

图2-68 “选择数据源”对话框

step 4 在左侧的“图例项（系列）”区域选中“年龄”，然后单击“删除”按钮，在右侧的“水平（分类）”区域单击“编辑”按钮，弹出“轴标签”对话框，如图2-69所示。

图2-69 “轴标签”对话框

step 5 将单元格区域改为“姓名”数据的单元格区域，即“=Sheet3! $B$2:$B$13”，单击“确定”按钮，返回到“选择数据源”对话框，单击“确定”按钮，返回图表，拉伸图表范围到适合大小，其结果如图2-70所示。创建图表完成。

图2-70 最终图表

在图2-70中单击某一颜色条时，将选择全部相同系列的其他类别的数据条，可清晰地对比各项数据，并能直观地阐述问题的本质，这就是图表的效果。可以根据自己需要选择合适的图表图形及数据系列，操作方法与实例步骤相同。

由于本书的讲解重点是Excel VBA编程，对于图表的更多选项和操作，如图表添加数据、编辑等，可以依据实例进行探索和学习，此处不再讲解。

2.5 小结

Excel在Office家族中的特色，就是其超强的计算及分析能力。Excel的计算分析功能，从简单的加减乘除，到复杂的数学分析，范围广泛。不论用户是初学者或者是专业人员，都可在其中找到合适的应用实例。而Excel的计算分析功能，大部分是通过使用公式及函数来完成的。

因此本章详细地讲解了Excel中应用的各类函数，及该类函数中常见的代表函数的功能和用法。在学习这些函数之后，基本上能解决办公中很多复杂运算的需要，大大提高办公的效率。需要注意的是，不要将此处Excel的函数与后续章节讲解的VBA函数混淆，两者是有区别的。本章还对Excel中其他的高级操作，如数据的有效性及图表等进行了简单的介绍。