Excel常用函数详解

Excel常用函数详解为更好发挥计算机的作用，减少不必要的手工计算，提高工作效益，针对我们实际工作中经常使用的Excel电子表格软件，特整理一套Excel常用函数详解及举例说明，希望能给大家一点帮助：本文主要通过Excel函数的四个方面进行讲解：逻辑函数文本、日期／时间函数查找与引用函数数据库函数具体内容如下：1、逻辑函数用来判断真假值，或进行复合检验的Excel函数，称为逻辑函数。Excel提供了六种逻辑函数。即AND、OR、NOT、FALSE、IF、TRUE函数。

一、AND、OR、NOT函数

这三个函数都用来返回参数逻辑值。详细介绍见下：

（一）AND函数

所有参数的逻辑值为真时返回TRUE；只要一个参数的逻辑值为假即返回FALSE。简言之，就是当AND的参数全部满足某一条件时，返回结果为TRUE，否则为FALSE。

语法为AND(logical1,logical2,...)，其中Logical1,logical2,...表示待检测的1到30个条件值，各条件值可能为TRUE，可能为FALSE。参数必须是逻辑值，或者包含逻辑值的数组或引用。举例说明：

1、在C2单元格中输入数字50，在D2中写公式=AND(B2>30,B2<60)。由于B2等于50的确大于30、小于60。所以两个条件值（logical）均为真，则返回结果为TRUE。

图1

2、如果B1-B3单元格中的值为TRUE、FALSE、TRUE，显然三个参数并不都为真，所以在B4单元格中的公式=AND(B1:B3)等于FALSE

图2（二）OR函数

OR函数指在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE。它与AND函数的区别在于，AND函数要求所有函数逻辑值均为真，结果方为真。而OR函数仅需其中任何一个为真即可为真。比如，上面的示例2，如果在B4单元格中的公式写为=OR(B1:B3)则结果等于TRUE

图3

（三）NOT函数

NOT函数用于对参数值求反。当要确保一个值不等于某一特定值时，可以使用NOT函数。简言之，就是当参数值为TRUE时，NOT函数返回的结果恰与之相反，结果为FALSE.

比如NOT(2+2=4)，由于2+2的结果的确为4，该参数结果为TRUE，由于是NOT函数，因此返回函数结果与之相反，为FALSE。

二、TRUE、FALSE函数

TRUE、FALSE函数用来返回参数的逻辑值，由于可以直接在单元格或公式中键入值TRUE或者FALSE。因此这两个函数通常可以不使用。

三、IF函数

（一）IF函数说明

IF函数用于执行真假值判断后，根据判断结果的真假值返回不同的结果，因此If函数也称之为条件函数。它的应用很广泛，可以使用函数IF对数值和公式进行条件检测。

它的语法为IF(logical_test,value_if_true,value_if_false)。其中Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。本参数可使用任何比较运算符。

Value_if_true显示在logical_test为TRUE时返回的值，Value_if_true也可以是其他公式。Value_if_falselogical_test为FALSE时返回的值。Value_if_false也可以是其他公式。

简言之，如果第一个参数logical_test返回的结果为真的话，则执行第二个参数Value_if_true的结果，否则执行第三个参数Value_if_false的结果。IF函数可以嵌套七层，用value_if_false及value_if_true参数可以构造复杂的检测条件。

Excel还提供了可根据某一条件来分析数据的其他函数。例如，如果要计算单元格区域中某个文本串或数字出现的次数，则可使用COUNTIF工作表函数。如果要根据单元格区域中的某一文本串或数字求和，则可使用SUMIF工作表函数。

（二）IF函数应用

1、输出带有公式的空白表单

图5

以图中所示的人事状况分析表为例，由于各部门关于人员的组成情况的数据尚未填写，在总计栏（以单元格G5为例）公式为：=SUM(C5:F5)

我们看到计算为0的结果。如果这样的表格打印出来就页面的美观来看显示是不令人满意的。是否有办法去掉总计栏中的0呢？你可能会说，不写公式不就行了。当然这是一个办法，但是，如果我们利用了IF函数的话，也可以在写公式的情况下，同样不显示这些0。如何实现呢？只需将总计栏中的公式（仅以单元格G5为例）改写成：

=IF(SUM(C5:F5),SUM(C5:F5),"")

通俗的解释就是：如果SUM(C5:F5)不等于零，则在单元格中显示SUM(C5:F5)的结果，否则显示字符串。

几点说明：

（1）SUM(C5:F5)不等于零的正规写法是SUM(C5:F5)<>0，在EXCEL中可以省略<>0；

（2）""表示字符串的内容为空，因此执行的结果是在单元格中不显示任何字符。

图4

2、不同的条件返回不同的结果

如果对上述例子有了很好的理解后，我们就很容易将IF函数应用到更广泛的领域。比如，在成绩表中根据不同的成绩区分合格与不合格。现在我们就以某班级的英语成绩为例具体说明用法。

图6

某班级的成绩如图6所示，为了做出最终的综合评定，我们设定按照平均分判断该学生成绩是否合格的规则。如果各科平均分超过60分则认为是合格的，否则记作不合格。

根据这一规则，我们在综合评定中写公式（以单元格B12为例）：

=IF(B11>60,"合格","不合格")

语法解释为，如果单元格B11的值大于60，则执行第二个参数即在单元格B12中显示合格字样，否则执行第三个参数即在单元格B12中显示不合格字样。

在综合评定栏中可以看到由于C列的同学各科平均分为54分，综合评定为不合格。其余均为合格。

3、多层嵌套函数的应用

在上述的例子中，我们只是将成绩简单区分为合格与不合格，在实际应用中，成绩通常是有多个等级的，比如优、良、中、及格、不及格等。有办法一次性区分吗？可以使用多层嵌套的办法来实现。仍以上例为例，我们设定综合评定的规则为当各科平均分超过90时，评定为优秀。如图7所示。

图7

说明：为了解释起来比较方便，我们在这里仅做两重嵌套的示例，您可以按照实际情况进行更多重的嵌套，但请注意Excel的IF函数最多允许七重嵌套。

根据这一规则，我们在综合评定中写公式（以单元格F12为例）：

=IF(F11>60,IF(AND(F11>90),"优秀","合格"),"不合格")

语法解释为，如果单元格F11的值大于60，则执行第二个参数，在这里为嵌套函数，继续判断单元格F11的值是否大于90（为了让大家体会一下AND函数的应用，写成AND(F11>90)，实际上可以仅写F11>90），如果满足在单元格F12中显示优秀字样，不满足显示合格字样，如果F11的值以上条件都不满足，则执行第三个参数即在单元格F12中显示不合格字样。

在综合评定栏中可以看到由于F列的同学各科平均分为92分，综合评定为优秀。

（三）根据条件计算值

在了解了IF函数的使用方法后，我们再来看看与之类似的Excel提供的可根据某一条件来分析数据的其他函数。例如，如果要计算单元格区域中某个文本串或数字出现的次数，则可使用COUNTIF工作表函数。如果要根据单元格区域中的某一文本串或数字求和，则可使用SUMIF工作表函数。关于SUMIF函数在数学与三角函数中以做了较为详细的介绍。这里重点介绍COUNTIF的应用。

COUNTIF可以用来计算给定区域内满足特定条件的单元格的数目。比如在成绩表中计算每位学生取得优秀成绩的课程数。在工资表中求出所有基本工资在2000元以上的员工数。

语法形式为COUNTIF(range,criteria)。其中Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为32、"32"、">32"、"apples"。

1、成绩表

这里仍以上述成绩表的例子说明一些应用方法。我们需要计算的是：每位学生取得优秀成绩的课程数。规则为成绩大于90分记做优秀。如图8所示

根据这一规则，我们在优秀门数中写公式（以单元格B13为例）：

=COUNTIF(B4:B10,">90")

语法解释为，计算B4到B10这个范围，即jarry的各科成绩中有多少个数值大于90的单元格。

在优秀门数栏中可以看到jarry的优秀门数为两门。其他人也可以依次看到。

图8

2、销售业绩表

销售业绩表可能是综合运用IF、SUMIF、COUNTIF非常典型的示例。比如，可能希望计算销售人员的订单数，然后汇总每个销售人员的销售额，并且根据总发货量决定每次销售应获得的奖金。

原始数据表如图9所示（原始数据是以流水单形式列出的，即按订单号排列）

图9

按销售人员汇总表如图10所示

图10

如图10所示的表完全是利用函数计算的方法自动汇总的数据。首先建立一个按照销售人员汇总的表单样式，如图所示。然后分别计算订单数、订单总额、销售奖金。

（1）订单数--用COUNTIF计算销售人员的订单数。

以销售人员ANNIE的订单数公式为例。公式：=COUNTIF($C$2:$C$13,A17)

语法解释为计算单元格A17(即销售人员ANNIE)在"销售人员"清单$C$2:$C$13的范围内（即图9所示的原始数据表）出现的次数。

这个出现的次数即可认为是该销售人员ANNIE的订单数。

（2）订单总额--用SUMIF汇总每个销售人员的销售额。

以销售人员ANNIE的订单总额公式为例。公式：

=SUMIF($C$2:$C$13,A17,$B$2:$B$13)

此公式在"销售人员"清单$C$2:$C$13中检查单元格A17中的文本（即销售人员ANNIE），然后计算"订单金额"列（$B$2:$B$13）中相应量的和。

这个相应量的和就是销售人员ANNIE的订单总额。

（3）销售奖金--用IF根据订单总额决定每次销售应获得的奖金。

假定公司的销售奖金规则为当订单总额超过5万元时，奖励幅度为百分之十五，否则为百分之十。根据这一规则仍以销售人员ANNIE为例说明。公式为：

=IF(C17<50000,10%,15%)*C17

如果订单总额小于50000则奖金为10%；如果订单总额大于等于50000，则奖金为15%。

至此，我们已完全了解了EXCEL函数的逻辑函数，相信大家在实际工作中会想出更多更有用的运用。2、文本、日期/时间函数文本函数，就是在公式中处理文字串的函数。如，改变大小写或确定文字串的长度；可以替换某些字符或者去除某些字符等。日期和时间函数则可在公式中分析和处理日期值和时间值。具体介绍如下：

一、文本函数

（一）大小写转换

LOWER--将一个文字串中的所有大写字母转换为小写字母。

UPPER--将文本转换成大写形式。

PROPER--将文字串的首字母及任何非字母字符之后的首字母转换成大写。将其余的字母转换成小写。

这三种函数的基本语法形式均为“函数名（text）”。示例说明：

已有字符串为：pLeaseComEHere!这句话大小写乱用了。

通过以上三个函数可以将文本转换显示样式，使得文本变得规范。参见图1

Lower（pLeaseComEHere!）=pleasecomehere!

upper（pLeaseComEHere!）=PLEASECOMEHERE!

proper（pLeaseComEHere!）=PleaseComeHere!

图1（二）取出字符串中的部分字符

您可以使用Mid、Left、Right等函数从长字符串内获取一部分字符。具体语法格式为

LEFT函数：LEFT(text,num_chars)其中Text是包含要提取字符的文本串。Num_chars指定要由LEFT所提取的字符数。

MID函数：MID(text,start_num,num_chars)其中Text是包含要提取字符的文本串。Start_num是文本中要提取的第一个字符的位置。

RIGHT函数：RIGHT(text,num_chars)其中Text是包含要提取字符的文本串。Num_chars指定希望RIGHT提取的字符数。

如，从字符串"Thisisanapple."分别取出字符"This"、"apple"、"is"的具体函数写法为。

LEFT("Thisisanapple",4)=This

RIGHT("Thisisanapple",5)=apple

MID("Thisisanapple",6,2)=is

图2（三）去除字符串的空白

在字符串形态中，空白也是一个有效的字符，但是如果字符串中出现空白字符时，容易在判断或对比数据是发生错误，在Excel中您可以使用Trim函数清除字符串中的空白。

语法形式为：TRIM(text)其中Text为需要清除其中空格的文本。

需要注意的是，Trim函数不会清除单词之间的单个空格，如果连这部分空格都需清除的话，建议使用替换功能。比如，从字符串"MynameisMary"中清除空格的函数写法为：TRIM("MynameisMary")=MynameisMary 参见图3

图3（四）字符串的比较

在数据表中经常会对比不同的字符串，此时可以使用EXACT函数来比较两个字符串是否相同。该函数测试两个字符串是否完全相同。如果它们完全相同，则返回TRUE；否则返回FALSE。函数EXACT能区分大小写，但忽略格式上的差异。语法形式为：EXACT(text1,text2)其中Text1为比较的第一个字符串。Text2为比较的第二个字符串。举例说明：参见图4

EXACT("China","china")=False

图4二、日期与时间函数

在数据表的处理过程中，日期与时间的函数是相当重要的处理依据。Excel在这方面提供了丰富的函数供大家使用。

（一）取出当前系统时间/日期信息

用于取出当前系统时间/日期信息的函数主要有NOW、TODAY。

语法形式均为函数名（）。

（二）取得日期/时间的部分字段值

如果需要单独的年份、月份、日数或小时的数据时，可以使用HOUR、DAY、MONTH、YEAR函数直接从日期/时间中取出需要的数据。具体示例参看图5。

比如，需返回2001-5-3012:30PM的年份、月份、日数及小时数，可以分别采用相应函数实现。

YEAR(E5)=2001

MONTH(E5)=5

DAY(E5)=30

HOUR(E5)=12

图5

三、示例：辅助生成的人事资料分析表

1、示例说明

在如图6所示的某公司人事资料表中，除了编号、员工姓名、身份证号码以及参加工作时间为手工添入外，其余各项均为用函数计算所得。

图6在此例中将详细说明如何通过函数求出：

（1）自动从身份证号码中提取出生年月、性别信息。

（2）自动从参加工作时间中提取工龄信息。

2、身份证号码相关知识

在了解如何实现自动从身份证号码中提取出生年月、性别信息之前，首先需要了解身份证号码所代表的含义。我们知道，当今的身份证号码有15/18位之分。早期签发的身份证号码是15位的，现在签发的身份证由于年份的扩展（由两位变为四位）和末尾加了效验码，就成了18位。这两种身份证号码将在相当长的一段时期内共存。两种身份证号码的含义如下：

（1）15位的身份证号码：1~6位为地区代码，7~8位为出生年份(2位)，9~10位为出生月份，11~12位为出生日期，第13~15位为顺序号，并能够判断性别，奇数为男，偶数为女。

（2）18位的身份证号码：1~6位为地区代码，7~10位为出生年份(4位)，11~12位为出生月份，13~14位为出生日期，第15~17位为顺序号，并能够判断性别，奇数为男，偶数为女。18位为效验位。

3、应用函数

为了实现数据的自动提取，应用以下几个Excel函数。

（1）IF函数：根据逻辑表达式测试的结果，返回相应的值。IF函数允许嵌套。

（2）CONCATENATE：将若干个文字项合并至一个文字项中。

（3）MID：从文本字符串中指定的起始位置起，返回指定长度的字符。

（4）TODAY：返回计算机系统内部的当前日期。

（5）DATEDIF：计算两个日期之间的天数、月数或年数。

（6）VALUE：将代表数字的文字串转换成数字。

（7）RIGHT：根据所指定的字符数返回文本串中最后一个或多个字符。

（8）INT：返回实数舍入后的整数值。语法形式为：INT(number)

4、公式写法及解释（以员工Andy为例说明）

说明：为避免公式中过多的嵌套，这里的身份证号码限定为15位的。如果您看懂了公式的话，可以进行简单的修改即可适用于18位的身份证号码，甚至可适用于15、18两者并存的情况。

（1）根据身份证号码求性别

=IF(VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2),"女","男")

公式解释：a.RIGHT(E4,3)用于求出身份证号码中代表性别的数字，实际求得的为代表数字的字符串

b.VALUE(RIGHT(E4,3)用于将上一步所得的代表数字的字符串转换为数字

c.VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2用于判断这个身份证号码是奇数还是偶数，当然你也可以用Mod函数来做出判断。

d.=IF(VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2),"女","男")及如果上述公式判断出这个号码是偶数时，显示"女"，否则，这个号码是奇数的话，则返回"男"。

（2）根据身份证号码求出生日期

=CONCATENATE("19",MID(E4,7,2),"/",MID(E4,9,2),"/",MID(E4,11,2))

公式解释：a.MID(E4,7,2)为在身份证号码中获取表示年份的数字的字符串

b.MID(E4,9,2)为在身份证号码中获取表示月份的数字的字符串

c.MID(E4,11,2)为在身份证号码中获取表示日期的数字的字符串

d.CONCATENATE("19",MID(E4,7,2),"/",MID(E4,9,2),"/",MID(E4,11,2))目的就是将多个字符串合并在一起显示。

（3）根据参加工作时间求年资（即工龄）

=CONCATENATE(DATEDIF(F4,TODAY(),"y"),"年",DATEDIF(F4,TODAY(),"ym"),"个月")

公式解释：

a.TODAY()用于求出系统当前的时间

b.DATEDIF(F4,TODAY(),"y")用于计算当前系统时间与参加工作时间相差的年份

c.DATEDIF(F4,TODAY(),"ym")用于计算当前系统时间与参加工作时间相差的月份，忽略日期中的日和年。

d.=CONCATENATE(DATEDIF(F4,TODAY(),"y"),"年",DATEDIF(F4,TODAY(),"ym"),"个月")目的就是将多个字符串合并在一起显示。3、查找与引用函数有关引用的知识：

1、引用的作用

在Excel中引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一单元格的数值。还可以引用同一工作簿不同工作表的单元格、不同工作簿的单元格、甚至其它应用程序中的数据。

2、引用的含义

关于引用需要了解如下几种情况的含义：

外部引用--不同工作簿中的单元格的引用称为外部引用。

远程引用--引用其它程序中的数据称为远程引用。

相对引用--在创建公式时，单元格或单元格区域的引用通常是相对于包含公式的单元格的相对位置。

绝对引用--如果在复制公式时不希望Excel调整引用，那么请使用绝对引用。即加入美元符号，如$C$1。

3、引用的表示方法

关于引用有两种表示的方法，即A1和R1C1引用样式。

（1）引用样式一（默认）--A1

A1的引用样式是Excel的默认引用类型。这种类型引用字母标志列（从A到IV，共256列）和数字标志行（从1到65536）。这些字母和数字被称为行和列标题。如果要引用单元格，请顺序输入列字母和行数字。例如，C25引用了列C和行25交叉处的单元格。如果要引用单元格区域，请输入区域左上角单元格的引用、冒号（：）和区域右下角单元格的引用，如A20:C35。

（2）引用样式二--R1C1

在R1C1引用样式中，Excel使用"R"加行数字和"C"加列数字来指示单元格的位置。例如，单元格绝对引用R1C1与A1引用样式中的绝对引用$A$1等价。如果活动单元格是A1，则单元格相对引用R[1]C[1]将引用下面一行和右边一列的单元格，或是B2。

在了解了引用的概念后，我们来看看Excel提供的查询与引用函数。查询与引用函数可以用来在数据清单或表格中查找特定数值，或者需要查找某一单元格的引用。Excel中一共提供了ADDRESS、AREAS、CHOOSE、COLUMN、COLUMNS、HLOOKUP、HYPERLINK、INDEX、INDIRECT、LOOKUP、MATCH、OFFSET、ROW、ROWS、TRANSPOSE、VLOOKUP16个查询与引用函数。下面，笔者将分组介绍一下这些函数的使用方法及简单应用。

一、ADDRESS、COLUMN、ROW

1、ADDRESS用于按照给定的行号和列标，建立文本类型的单元格地址。

其语法形式为：ADDRESS(row_num,column_num,abs_num,a1,sheet_text)

Row_num指在单元格引用中使用的行号。

Column_num指在单元格引用中使用的列标。

Abs_num指明返回的引用类型，1代表绝对引用，2代表绝对行号，相对列标，3代表相对行号，绝对列标，4为相对引用。

A1用以指明A1或R1C1引用样式的逻辑值。如果A1为TRUE或省略，函数ADDRESS返回A1样式的引用；如果A1为FALSE，函数ADDRESS返回R1C1样式的引用。

Sheet_text为一文本，指明作为外部引用的工作表的名称，如果省略sheet_text，则不使用任何工作表名。

简单说，即ADDRESS（行号，列标，引用类型，引用样式，工作表名称）

比如，ADDRESS(4,5,1,FALSE,"[Book1]Sheet1")等于"[Book1]Sheet1!R4C5"参见图1

图12、COLUMN用于返回给定引用的列标。

语法形式为：COLUMN(reference)

Reference为需要得到其列标的单元格或单元格区域。如果省略reference，则假定为是对函数COLUMN所在单元格的引用。如果reference为一个单元格区域，并且函数COLUMN作为水平数组输入，则函数COLUMN将reference中的列标以水平数组的形式返回。但是Reference不能引用多个区域。

3、ROW用于返回给定引用的行号。

语法形式为：ROW(reference)

Reference为需要得到其行号的单元格或单元格区域。如果省略reference，则假定是对函数ROW所在单元格的引用。如果reference为一个单元格区域，并且函数ROW作为垂直数组输入，则函数ROW将reference的行号以垂直数组的形式返回。但是Reference不能对多个区域进行引用。

二、AREAS、COLUMNS、INDEX、ROWS

1、AREAS用于返回引用中包含的区域个数。其中区域表示连续的单元格组或某个单元格。

其语法形式为AREAS(reference)

Reference为对某一单元格或单元格区域的引用，也可以引用多个区域。如果需要将几个引用指定为一个参数，则必须用括号括起来。

2、COLUMNS用于返回数组或引用的列数。

其语法形式为COLUMNS(array)

Array为需要得到其列数的数组、数组公式或对单元格区域的引用。

3、ROWS用于返回引用或数组的行数。

其语法形式为ROWS(array)

Array为需要得到其行数的数组、数组公式或对单元格区域的引用。

以上各函数示例见图2

图24、INDEX用于返回表格或区域中的数值或对数值的引用。

函数INDEX()有两种形式：数组和引用。数组形式通常返回数值或数值数组；引用形式通常返回引用。

（1）INDEX(array,row_num,column_num)返回数组中指定单元格或单元格数组的数值。

Array为单元格区域或数组常数。Row_num为数组中某行的行序号，函数从该行返回数值。Column_num为数组中某列的列序号，函数从该列返回数值。需注意的是Row_num和column_num必须指向array中的某一单元格，否则，函数INDEX返回错误值#REF!。

（2）INDEX(reference,row_num,column_num,area_num)返回引用中指定单元格或单元格区域的引用。

Reference为对一个或多个单元格区域的引用。

Row_num为引用中某行的行序号，函数从该行返回一个引用。

Column_num为引用中某列的列序号，函数从该列返回一个引用。

需注意的是Row_num、column_num和area_num必须指向reference中的单元格；否则，函数INDEX返回错误值#REF!。如果省略row_num和column_num，函数INDEX返回由area_num所指定的区域。

三、INDIRECT、OFFSET

1、INDIRECT用于返回由文字串指定的引用。

当需要更改公式中单元格的引用，而不更改公式本身，使用函数INDIRECT。

其语法形式为：INDIRECT(ref_text,a1)

其中Ref_text为对单元格的引用，此单元格可以包含A1-样式的引用、R1C1-样式的引用、定义为引用的名称或对文字串单元格的引用。如果ref_text不是合法的单元格的引用，函数INDIRECT返回错误值#REF!。

A1为一逻辑值，指明包含在单元格ref_text中的引用的类型。如果a1为TRUE或省略，ref_text被解释为A1-样式的引用。如果a1为FALSE，ref_text被解释为R1C1-样式的引用。

需要注意的是：如果ref_text是对另一个工作簿的引用（外部引用），则那个工作簿必须被打开。如果源工作簿没有打开，函数INDIRECT返回错误值#REF!。

2、OFFSET函数用于以指定的引用为参照系，通过给定偏移量得到新的引用。

返回的引用可以是一个单元格或者单元格区域，并可以指定返回的行数或者列数。

其基本语法形式为：OFFSET(reference,rows,cols,height,width)。

其中，reference变量作为偏移量参照系的引用区域(reference必须为对单元格或相连单元格区域的引用，否则，OFFSET函数返回错误值＃VALUE!)。

rows变量表示相对于偏移量参照系的左上角单元格向上(向下)偏移的行数(例如rows使用2作为参数，表示目标引用区域的左上角单元格比reference低2行)，行数可为正数(代表在起始引用单元格的下方)或者负数(代表在起始引用单元格的上方)或者0(代表起始引用单元格)。

cols表示相对于偏移量参照系的左上角单元格向左(向右)偏移的列数(例如cols使用4作为参数，表示目标引用区域的左上角单元格比reference右移4列)，列数可为正数(代表在起始引用单元格的右边)或者负数(代表在起始引用单元格的左边)。

如果行数或者列数偏移量超出工作表边缘，OFFSET函数将返回错误值＃REF！。height变量表示高度，即所要返回的引用区域的行数(height必须为正数)。width变量表示宽度，即所要返回的引用区域的列数(width必须为正数)。如果省略height或者width，则假设其高度或者宽度与reference相同。例如，公式OFFSET(A1,2,3,4,5)表示比单元格A1靠下2行并靠右3列的4行5列的区域(即D3:H7区域)。

由此可见，OFFSET函数实际上并不移动任何单元格或者更改选定区域，它只是返回一个引用。

四、HLOOKUP、LOOKUP、MATCH、VLOOKUP

1、LOOKUP函数与MATCH函数

LOOKUP函数可以返回向量（单行区域或单列区域）或数组中的数值。此系列函数用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。当比较值位于数据表的首行，并且要查找下面给定行中的数据时，使用函数HLOOKUP。当比较值位于要进行数据查找的左边一列时，使用函数VLOOKUP。

如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用函数MATCH而不是函数LOOKUP。MATCH函数用来返回在指定方式下与指定数值匹配的数组中元素的相应位置。从以上分析可知，查找函数的功能，一是按搜索条件，返回被搜索区域内数据的一个数据值；二是按搜索条件，返回被搜索区域内某一数据所在的位置值。利用这两大功能，不仅能实现数据的查询，而且也能解决如"定级"之类的实际问题。

2、LOOKUP用于返回向量（单行区域或单列区域）或数组中的数值。

函数LOOKUP有两种语法形式：向量和数组。

（1）向量形式

函数LOOKUP的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值。

其基本语法形式为LOOKUP(lookup_value,lookup_vector,result_vector)

Lookup_value为函数LOOKUP在第一个向量中所要查找的数值。Lookup_value可以为数字、文本、逻辑值或包含数值的名称或引用。

Lookup_vector为只包含一行或一列的区域。Lookup_vector的数值可以为文本、数字或逻辑值。

需要注意的是Lookup_vector的数值必须按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否则，函数LOOKUP不能返回正确的结果。文本不区分大小写。

Result_vector只包含一行或一列的区域，其大小必须与lookup_vector相同。

如果函数LOOKUP找不到lookup_value，则查找lookup_vector中小于或等于lookup_value的最大数值。

如果lookup_value小于lookup_vector中的最小值，函数LOOKUP返回错误值#N/A。

示例详见图3

图3（2）数组形式

函数LOOKUP的数组形式在数组的第一行或第一列查找指定的数值，然后返回数组的最后一行或最后一列中相同位置的数值。通常情况下，最好使用函数HLOOKUP或函数VLOOKUP来替代函数LOOKUP的数组形式。函数LOOKUP的这种形式主要用于与其他电子表格兼容。关于LOOKUP的数组形式的用法在此不再赘述，感兴趣的可以参看Excel的帮助。

3、HLOOKUP与VLOOKUP

HLOOKUP用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。

VLOOKUP用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。

当比较值位于数据表的首行，并且要查找下面给定行中的数据时，请使用函数HLOOKUP。

当比较值位于要进行数据查找的左边一列时，请使用函数VLOOKUP。

语法形式为：

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

其中，Lookup_value表示要查找的值，它必须位于自定义查找区域的最左列。Lookup_value可以为数值、引用或文字串。

Table_array查找的区域，用于查找数据的区域，上面的查找值必须位于这个区域的最左列。可以使用对区域或区域名称的引用。

Row_index_num为table_array中待返回的匹配值的行序号。Row_index_num为1时，返回table_array第一行的数值，row_index_num为2时，返回table_array第二行的数值，以此类推。

Col_index_num为相对列号。最左列为1，其右边一列为2，依此类推.

Range_lookup为一逻辑值，指明函数HLOOKUP查找时是精确匹配，还是近似匹配。

下面详细介绍一下VLOOKUP函数的应用。

简言之，VLOOKUP函数可以根据搜索区域内最左列的值，去查找区域内其它列的数据，并返回该列的数据，对于字母来说，搜索时不分大小写。所以，函数VLOOKUP的查找可以达到两种目的：一是精确的查找。二是近似的查找。下面分别说明。

（1）精确查找--根据区域最左列的值，对其它列的数据进行精确的查找

示例：创建工资表与工资条

首先建立员工工资表

图4然后，根据工资表创建各个员工的工资条，此工资条为应用Vlookup函数建立。以员工Sandy（编号A001）的工资条创建为例说明。

第一步，拷贝标题栏

第二步，在编号处（A21）写入A001

第三步，在姓名（B21）创建公式

=VLOOKUP($A21,$A$3:$H$12,2,FALSE)

语法解释：在$A$3:$H$12范围内（即工资表中）精确找出与A21单元格相符的行，并将该行中第二列的内容计入单元格中。

第四步，以此类推，在随后的单元格中写入相应的公式。

图5（2）近似的查找--根据定义区域最左列的值，对其它列数据进行不精确值的查找

示例：按照项目总额不同提取相应比例的奖金

第一步，建立一个项目总额与奖金比例的对照表，如图6所示。项目总额的数字均为大于情况。即项目总额在0~5000元时，奖金比例为1%，以此类推。

图6

图7第二步假定某项目的项目总额为13000元，在B11格中输入公式

=VLOOKUP(A11,$A$4:$B$9,2,TRUE)

即可求得具体的奖金比例为5%，如图7。4、MATCH函数

MATCH函数有两方面的功能，两种操作都返回一个位置值。

一是确定区域中的一个值在一列中的准确位置，这种精确的查询与列表是否排序无关。

二是确定一个给定值位于已排序列表中的位置，这不需要准确的匹配.

语法结构为：MATCH(lookup_value,lookup_array,match_type)

lookup_value为要搜索的值。

lookup_array：要查找的区域(必须是一行或一列)。

match_type：匹配形式，有0、1和－1三种选择："0"表示一个准确的搜索。"1"表示搜索小于或等于查换值的最大值，查找区域必须为升序排列。"－1"表示搜索大于或等于查找值的最小值，查找区域必须降序排开。以上的搜索，如果没有匹配值，则返回＃N/A。五、其他（CHOOSE、TRANSPOSE）

1、CHOOSE函数

函数CHOOSE可以使用index_num返回数值参数清单中的数值。使用函数CHOOSE可以基于索引号返回多达29个待选数值中的任一数值。

语法形式为：CHOOSE(index_num,value1,value2,...)

Index_num用以指明待选参数序号的参数值。Index_num必须为1到29之间的数字、或者是包含数字1到29的公式或单元格引用。

Value1,value2,...为1到29个数值参数，函数CHOOSE基于index_num，从中选择一个数值或执行相应的操作。参数可以为数字、单元格引用，已定义的名称、公式、函数或文本。

2、TRANSPOSE函数

TRANSPOSE用于返回区域的转置。函数TRANSPOSE必须在某个区域中以数组公式的形式输入，该区域的行数和列数分别与array的列数和行数相同。使用函数TRANSPOSE可以改变工作表或宏表中数组的垂直或水平走向。

语法形式为TRANSPOSE(array)

Array为需要进行转置的数组或工作表中的单元格区域。所谓数组的转置就是，将数组的第一行作为新数组的第一列，数组的第二行作为新数组的第二列，以此类推。

示例，将原来为横向排列的业绩表转置为纵向排列。

图8

图9第一步，由于需要转置的为多个单元格形式，因此需要以数组公式的方法输入公式。故首先选定需转置的范围。此处我们设定转置后存放的范围为A9.B14.

第二步，单击常用工具栏中的"粘贴函数"图标，将出现"粘贴函数"对话框，在"函数分类"框中选择"查找与引用函数"框中选择TRANSPOSE，此时在对话框的底部将出现该函数的简短解释。单击"确定"后将弹出TRANSPOSE函数参数设置对话框。第三步，选择数组的范围即A2.F3

第四步，由于此处是以数组公式输入，因此需要按CRTL+SHIFT+ENTER组合键来确定为数组公式，此时会在公式中显示"{}"。随即转置成功，如图10所示。

图10以上我们介绍了Excel的查找与引用函数，此类函数的灵活应用对于减少重复数据的录入是大有裨益的。此处只做了些抛砖引玉的示例，相信大家会在实际运用中想出更具实用性的应用方法。4、数据库函数Dvarp基于选定的数据库项的样本总体估算方差Getpivotda返回存储于数据透视表中的数据Dstdevp基于选定数据库项中的样本总体估算标准偏差Daverage返回选定数据项的平均值Dcount计算数据库中包含数字的单元格个数Dcounta计算数据库中非空单元格的个数Dget从数据库中提取满足指定条件的单个记录Dmax返回选定数据库项中的最大值Dmin返回选定数据库项中的最小值Dproduct将数据库中满足条件的记录的特定字段中的数值相乘Dstdev基于选定数据库项中的单个样本估算标准偏差Dsum对数据库中满足条件的记录的字段列中的数字求和Dvar基于选定的数据库项的单个样本估算方差

在Excel中提供了工作表函数，用于对数据清单或数据库中的数据进行分析，就是数据库函数（Dfunctions）。

一、函数的共同特点

（1）每个函数都有3个参数：database（数据源）、field（分析/统计字段）和criteria（条件）。

（2）除了Getpivotdata函数之外，其余12个函数都以字母D开头。

（3）可以看出若将字母D去掉，则这些函数大多是其他类型函数。如，Dsum去掉D，就是求和函数SUM。

二、数据库函数列表

在Excel包含的数据库函数及其应用如表所示：

表1

三、数据库函数的参数含义

函数的语法形式为：函数名称(database,field,criteria)。

Database即构成数据清单或数据库的单元格区域。其中数据清单的第一行包含着每一列的标志项，即字段名称。

Field即指定函数所使用的数据列/数据字段。数据清单中的数据列必须在第一行具有标志项。Field可以是文本，即两端带引号的标志项，如“使用年数”或“产量”；此外，Field也可以是代表数据清单中数据列位置的数字：1表示第一列，2表示第二列，等等。

Criteria为一组包含给定条件的单元格区域。可以为参数criteria指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。

四、举例说明

1、例：某果园的果树的高度、使用年数、产量与利润的统计数据表如表所示，树种高度使用年数产量利润苹果树182014105梨树112121096樱桃树13149105苹果树14151075梨树98876．8苹果树89645

表2要求求出

（1）有多少种苹果树的树高在10~16米之间？

（2）苹果树与梨树的最大利润值是多少？

（3）高度大于10米的苹果树的最小利润是多少？

（4）苹果树的总利润？

（5）高度大于10米的苹果树的平均产量？

（6）果园中所有树种的平均使用年数？

（7）求苹果树和梨树产量的估算标准偏差、真实标准偏差、估算方差、真实方差？

2、具体求解步骤如下：

（1）创建新工作表，将数据录入Excel中，如图所示；

图1

（2）由于第一问需要求解有多少种苹果树的树高在10~16米之间，因此建立查询条件高度在10与16之间如图所示；

图2

（3）利用函数DCOUNT求解满足条件的单元格数来计算高度在10~16米的苹果树的种类。公式为：DCOUNT(A4:E10,"使用年数",B1:G2)，求出有一种苹果树满足条件。

（4）由于第二问为求苹果树与梨树的最大利润值，因此再建立一个查询条件——梨树，如图。

图3（5）利用函数DMAX求出苹果树与梨树的最大利润，同理可以利用函数DMIN求出高度大于10英尺苹果树的最小利润。具体公式为：

=DMAX(A4:E10,"利润",A1:A3)求出苹果树与梨树的最大利润为105

=DMIN(A4:E10,"利润",A1:B2)求出高度大于10英尺苹果树的最小利润为75

（6）利用函数DSUM可以求出所有苹果树的总利润。

公式为：=DSUM(A4:E10,"利润",A1:A2)总利润为225

（7）其他各问均可采用类似的函数求解，详细的公式如表所示。公式说明（结果）Dcount（A4：E10，“使用年数”，A1：F2）此函数查找高度在10到16米之间的苹果树的记录，并且计算这些记录中“使用年数”字段包含数字的单元格个数。（1）Dcounta（A4：E10，“利润”，A1：F2）些函数查找高度在10到16米之间的苹果树的记录，并且计算这些记录中“利润”字段为非空的单元格个数。（1）Dmax（A4：E10，“利润”，A1：A3）些函数查找果树和梨树最大利润。（105）Dmin（A4：E10，“利润”，A1：B2）些函数查找高度大于10米的苹果树的最小利润。（75）Dsum（A4：E10，“利润”，A1：A2）些函数计算苹果树的总利润。（225）Dsum（A4：E10，“利润”，A1：F2）些函数计算高度在10到16米之间的苹果树的总利润。（75）Dproduct（A4：E10，“产量”，A1：B2）些函数计算高度大于10米的苹果树的产量。（140）Daverage（A4：E10，“产量”，A1：B2）些函数计算高度大于10米的苹果树的平均产量。（12）Daverage（A4：E10，3，A1：E10）些函数计算数据库中所有树的使用年数。（13）Dstdev（A4：E10，“产量”，A1：A3）如果数据库中的数据只是整个果园的一个样本，则此值是苹果树和梨树产量的估算标准偏差。（2.97）Dstdevp（A4：E10，“产量”，A1：A3）如果数据库中的数据为整个果园样本总体，则此值是苹果树和梨树产量的真实标准偏差。（2.65）Dvar（A4：E10，“产量”，A1：A3）如果数据库中的数据只是整个果园的一个样本，那么此值是苹果树和梨树产量的估算方差。（8.8）Dvarp（A4：E10，“产量”，A1：A3）如果数据库中的数据为整个果园的样本总体，则么此值是苹果树和梨树产量的真实方差。（7.04）Dget（A4：E10，“产量”，A1：A3）