Excel数据管理应用教程第三章公式和函数第三章 公式与函数 3.1公式引用和简单函数3.2计数函数3.3判断函数3.4名字定义和应用 3.
5查找和引用函数3.6 条件求和函数3.7文本函数 3.8 日期和时间函数 3.1公式引用和简单函数应用简单公式和引用数组公式 简
单数学函数 简单统计函数 一、简单公式和引用１.实例１采用公式和引用方法计算考试成绩 【实例内容】：在员工招聘表中计算笔试成绩和总
评。笔试成绩=思想政治+专业技能;总评=笔试成绩+面试成绩。一、简单公式和引用【实例操作步骤】： 步骤1：在J4单元格输入公式：=
H4+I4。步骤2： 鼠标指向J4单元格右下方，形状呈实心十字形后按住向下拖至J18。得到笔试成绩.步骤3：在L4单元格输入公式：
=J4+K4，按步骤2方法将公式引用至L18。得到总评成绩。一、简单公式和引用２.简单公式和引用知识介绍公式是在工作表中对数值执行
计算的等式。公式有简单公式和数组公式两种。⑴简单公式概念公式以等号（=）开始，由一个或多个单元格地址、数值、函数和运算符构成。例：
2乘3 再加5的公式为：=5+23；B1到B10单元格数据求和的公式为：SUM（b1:b10）。 一、简单公式和引用运算符运算符
是公式的基本元素，它用于对公式中的元素进行特定类型的运算。Excel 包含四种类型的运算符：算术运算符、引用运算符、比较运算符和文
本运算符。算术运算符是能够完成基本的数学运算，如加法、减法和乘法等。一、简单公式和引用⑵公式的引用引用：就是在某单元格或单元格区域
的公式中用到其他单元格中的数据。引用是通过使用单元格的名称来实现的。通常单元格的名称是由列标+行号组成的。如A1，B23等。例如：
员工招聘考试信息表中，李丹的笔试成绩在J4单元格， 由于笔试成绩=思想政治+专业技能，所以J4单元格的公式为：=H4+I4，引用
了H4和I4的成绩数据。一、简单公式和引用引用运算符 引用的方式分为3种：相对引用、绝对引用及混合引用 一、简单公式和引用相对引用
相对引用也称为相对地址，它用列标和行号直接表示单元格。复制公式时，Excel根据目标单元格与源公式所在单元格的相对位置，相应地调整
引用标识。例如：单元格A1、A2、B1、B2和C2值分别为1、2、3、4、5，C1单元格公式是：=A1+B1，得到数值为4，若在D
1和D2中引用C1单元格公式，则D1中公式为：B1+C1；D2中公式为B2+C2。如图3-3，图3-4所示。
图3-3 单元格引用 图3-4 公式的相对引用一、简单公式和引用绝对引用绝
对引用是在引用单元格的列标与行号前加“$”符号，在引用公式时，不论目标单元格所在的位置如何改变，绝对引用所指向的单元格区域始终不会
改变。例如：将图3-3的C1单元格公式改为：$A$1+B1，在D1和D2中引用C1单元格公式，则D1和D2中公式为：$A$1+ C
1；D2中公式为$A$1+C2。图3-5 绝对引用一、简单公式和引用例：用绝对引用的方法重新计算总评成绩：总评=笔试成绩
30%+面试成绩70%。设B28、B29单元格分别存放笔试和面试的比例数据30%和70%。步骤1：在L4单元格输入公式：=J4
$B$28+K4$B$29。步骤2：将L4公式引用至L18，从而得到每个考试人员的总评成绩。一、简单公式和引用混合引用混合引用即
行相对、列绝对的引用，或行绝对、列相对的引用。例如：A$1，$A1等。在上例中用绝对引用的方法，由于公式“=J4$B$28+K4
$B$29”是向列方向复制，因此，列标Q在公式复制过程中既使不采用绝对引用，也是保持不变的，但行如果不采用绝对引用，却会随着目标
位置的变化而变化。所以该公式可以采用混合引用的形式：=J4B$28+K4B$29。 二、数组公式１.实例2 采用数组公式计算总
评成绩 【实例内容】：用数组公式计算员工招聘考试的总评成绩。总评=笔试成绩30%+面试成绩70%。其中：B28、B29单元格分
别存放笔试和面试的比例数据30%和70%。 【操作步骤】步骤1：选择单元格区域L4到L18。步骤2：输入公式：=J4:J18B2
8+K4:K18B29。步骤3：按下CTRL+SHIFT+ENTER。数组公式计算总评成绩，计算结果如图3-7所示。二、数组公式
图3-7 总评成绩的计算结果 二、数组公式⒉数组公式的知识介绍创建数组公式分三步进行。第一步：先选择保存计算结果的单元格或单元格
区域；第二步：输入公式的内容；第三步：按下CTRL+SHIFT+ENTER。数组公式生成后，Excel将在公式两边会自动加上大括号
｛｝。 二、数组公式使用数组公式要注意以下几点：在公式中，用数组作为参数时，一般情况下数组参数必须是同维的，但出现不匹配时，Exc
el会自动扩展。上列中J4:J18 B28，可以理解为J4B28，J5 B28，……J18 B28 ，同理K4:K18
B29理解为：K4 B29，K5 B29，……K18 B29。数组公式所涉及的单元格区域是作为一个整体进行操作，不能对其中部
分单元格内容进行编辑、清除、复制和移动等操作，也不能在数组区域中插入或删除单元格。若要删除或清除单元格，只能将整个区域删除或清除。
要修改数组公式，需要选择整个数组区域，然后在编辑栏中对公式进行修改，修改完成按下CTRL+SHIFT+ENTER.。三、简单数学函
数1.实例3： 对员工成绩进行取整和四舍五入 【实例内容】:对员工招聘考试信息表笔试成绩取整，对总评的小数部份进行四舍五入。【操作
步骤】步骤1：在J4单元格输入公式：INT(H4+I4)步骤2：向下引用至J18，如图3-8所示。步骤3：选择L4到L18单元格区
域，输入数组公式：ROUND（J4:J18B28+K4:K18B29,0）后按下CTRL+SHIFT+ENTER。笔试成绩取整
结果如图3-8所示，总评成绩小数部分四舍五入结果如图3-9所示 。三、简单数学函数图3-8 对笔试成绩取整 三、简单数学函数图3
-9 对总评成绩小数部分四舍五入处理三、简单数学函数2、简单数学函数知识介绍1) 函数简介Excel提供了许多预先定义好的特殊公式
，这些内置的公式称为函数。每个函数都有特定功能，用以解决相应的问题。Excel提供了400多个函数，共分10种类型：数学与三角函数
、统计函数、财务函数、查找与引用函数、文本函数、日期与时间函数、数据库管理函数和信息类函数等。通常函数是由函数名和参数表两部分构成
，即：函数名(参数1，参数2，……参数N) 函数名表示函数要执行什么样的运算，参数则提供运算过程中所要用到的数据来源。调
用函数的方法有2种，在编辑栏（或活动单元格中）直接输入公式和根据函数向导提示逐步完成操作。三、简单数学函数直接输入公式方法：是在编
辑栏上按公式的格式和需要的参数来输入公式，输入完毕即可得到函数运算结果。根据函数向导提示方法： 现就任务3
.3对笔试成绩取整采用函数向导，其步骤如下： 步骤1：单击J4单元格，选择菜单“插入”中的“函数(F)……”，此时出现“插入函
数”对话框。 步骤2：在“插入函数”对话框的“选择类别”下拉列表中选择“数学与三角函数”，在“选择函数”列表中
选择INT（图3-10）后单击确定按钮。 步骤3：在函数参数对话框（图3-11）的number栏上输入H4+I4
，单击确定按钮，得到笔试成绩取整结果。三、简单数学函数图3-10 插入函数对话框 图3-11 函数参
数对话框 三、简单数学函数2) 取整函数INTINT函数的功能是返回不大于number的最大整数。函数格式为：INT（number
） 其中number是数值型数据。例：INT(8.9)=8，INT(-8.9)=-93) 四舍五入函数ROUND
ROUND函数的功能是返回某个数值按指定位数进行四舍五入后的数据。函数格式为：ROUND(number,num_digits)其中
：Number：需要进行四舍五入的数字。 Num_digits：指定的位数，按此位数进行四舍五入。
例：ROUND(21.625,2)=21.63，ROUND(21.625,1)=21.6，ROUND(21.625,0)=22三
、简单数学函数4) 最大值MAX函数MAX函数的功能是返回一组值中的最大值。函数格式为：MAX(number1,number2,.
..) 其中Number1, number2, ...?? 是要从中找出最大值的 1 到 30 个数字参数。说明：如果参数为
数组或引用，则只有数组或引用中的数字将被计算。如果参数不包含数字，函数 MAX 返回 0（零）。 例：在员工招聘考试信息表中，求笔
试最高分的计算公式为：MAX（J4:J18）5）最小值MIN函数MIN函数的功能是返回一组值中的最小值。函数格式为：MIN(num
ber1,number2,...) 其中：Number1, number2,...?? 是要从中找出最小值的 1 到 30
个数字参数。说明：如果参数是数组或引用，则函数 MIN 仅使用其中的数字。如果参数中不含数字，则函数 MIN 返回 0。 例：在员
工招聘考试信息表中，求笔试最低分的计算公式为：MIX（J4:J18）四、简单统计函数1.实例4 ：对招聘考试总评成绩排名并按考试类
别统计平均分 【实例内容】：按考试类别分别统计思想政治、专业技能、笔试成绩、面试成绩以及总评的平均分，且保留一位小数；再对每个考生
的总评成绩进行排序，填入“排名”列。 【操作步骤】步骤1：在H19单元格上输入公式：=ROUND(AVERAGE(H4:H18),
1)；步骤2：向右引用至L19单元格。由此得到思想政治、专业技能、笔试成绩、面试成绩以及总评的平均分，如图3-12 所示。步骤3：
在M4中输入公式：=RANK（L4,L$4:L$18）；步骤4：向下引用至M18单元格完成排位。如图3-13所示。四、简单统计函数
图3-12 按考试类别统计平均分 四、简单统计函数图3-13 招聘考试排名结果四、简单统计函数2.简单统计函数 知识介绍1） 求和
函数SUMSUM函数的功能是返回某一单元格区域中所有数字之和。函数格式为：SUM(number1,number2, ... num
berN) 其中Number1, number2, ...??? 为 1 到 30 个需求和的参数。求和时为简化公式的输入，
可直接采用工具栏上自动求和按钮。只要选择单元格区域J4:L18（L4:L18必须为空）后直接单击按钮即可。例：计算总评成绩。若在L
4单元格上采用SUM函数，则公式为“=SUM(J4:K4)”，最后将公式向下引用至L18。 若采用数组公式，则选择L4到L1
8单元格区域并输入公式：J4:J18+K4:K18，按下CTRL+SHIFT+ENTER。四、简单统计函数2）求平均值函数AVER
AGEAVERAGE函数的功能是返回参数的算术平均值。函数格式为： AVERAGE(number1,number2,...
numberN) 其中Number1，number2,，……?? 为需要计算平均值的1 到 30 个参数。四、简单统计函数3
） 排名函数 RANKRANK函数返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值。函数格式为： RANK(
number,ref,order) 其中：Number：需要找到排位的数字。 Ref：数字列
表数组或对数字列表的引用。Ref 中的非数值型参数将被忽略。 Order：为一数字，指明排位的方式。
如果 order 为0（零）或省略， Excel 对数字的排位按照降序排列。如果 order 不为零， Excel 对数字的排位按
照升序排列。 说明：函数 RANK 对重复数的排位相同。但重复数的存在将影响后续数值的排位。 例如，一列整数分别为：1，7
，3，5，3，8，按升序排列，则数字3的排位是2，数字5的排位是4，没有排位为3 的数值。3.2计数函数1.实例5:5 参加招聘
考试人数的统计 【实例内容】在员工招聘考试信息表中，对参加笔试和面试科目的考生人数、缺考人数及笔试或面试成绩不及格人数进行统计，填
入表中20-22行对应列。【操作步骤】步骤1：在H20单元格中输入公式：=COUNT(H4:H18)后，向右引用至K20，得到各科
目考试人数。步骤2：在H21单元格中输入公式：=COUNTBLANK(H4:H18)后，向右引用至K21，得到各科目缺考人数。步骤
3：在J22单元格中输入公式：=COUNTIF(J4:J18,”<60”)后，向右引用至K22，得到笔试和面试不及格人数。 3.2
计数函数3.2计数函数【操作结果】员工招聘考试人数统计如图3-15所示。3.2计数函数2.计数函数知识介绍1）计数函数COUNT/
COUNTA/COUNTBLANK 计数函数的功能是在给定的单元格区域中统计数据个数。计数函数有COUNT、COUNTA和COUN
TBLANK三种不同的计数方式。函数格式为： COUNT(value1,value2,...)； COUN
TA(value1,value2,...)； COUNTBLANK(range) 其中： Value
1, value2, ...?为参加计算的值，个数可为 1 至 30 个； Range：需要计算其中空白单元格个数的区域。
COUNT返回参数列表中数字型数据个数。其中日期也作为数字计算在内，其他类型的数据不予统计。 3.2计数函数 COUNTA
返回参数列表中非空单元格个数。COUNTA的参数值可以是任何类型，可包括空字符串 ("")和空格，但不包括空白单元格。 COUNT
BLANK返回指定单元格区域中空白单元格的个数，单元格中含有空字符串 ("")的公式，也会计算在内，但包含零值的单元格不计算在内。
例：假设A1:A4单元格值或公式分别为12，=”123”,=””,”abc”则 COUNT(A1:A4)=1；只有数值12符合统计
要求。 COUNTA(A1:A4)=4；4个值均符合统计要求。 COUNTBLANK(A1:A4)=1；只有空串=””
符合统计要求。3.2计数函数2）条件计数函数COUNTIF COUNTIF函数的功能是计算指定的单元格区域中满足给定条件的单元格个
数。函数格式为：COUNTIF(range,criteria)其中： Range?：为需要计算其中满足条件的单元格数目的单元
格区域。 Criteria?：为确定哪些单元格将被计算在内的限定条件，其形式可以为数字、表达式或文本。 条件中所用的运
算符称为比较运算符。当用运算符比较两个值时，结果是一个逻辑值，不是 TRUE 就是 FALSE。比较运算符如表3-2所示。3.2计
数函数表3-2 比较运算符功能3.2计数函数例：在员工招聘考试信息表（如图3.15所示）中，欲统计笔试成绩大于或等于60且小于9
0的人数，可以采用上述运算符构造条件，先求出大于或等于60的人数，再从中扣除大于或等于90的人数。公式如下： COUNTIF
(J4:J18,”>=60”)- COUNTIF(J4:J18,”>=90”)3.3 判断函数 1.实例6 分析统计招聘考试录用
情况 【任务内容】对各员工笔试和面试成绩都大于或等于60分的，在考核结果栏上填上通过，否则不填写；考核结果通过且总评排名前6名的给
予录用，并在是否录用列上填上“是”，否则填“否”。3.3 判断函数【操作步骤】步骤1：在N4单元格上输入公式：=IF(J4<60,
"",IF(K4<60,"","通过"))；步骤2：将上述公式引用至N18。此时可实现两项成绩都大于或等于60的，考核结果列填上通
过，否则不填写。步骤3：在O4单元格上输入公式：=IF(AND(N4="通过",M4<=6),"是","否")步骤4：将上述公式引
用至O18，得出是否录用的结果。如图3.16所示。3.3 判断函数2.条件函数IF知识介绍1） IF函数IF函数的功能是根据逻辑计
算的真假值，返回不同结果。函数格式为：IF(logical_test,value_if_true,value_if_false)其
中：logical_test：它是计算结果为 TRUE 或 FALSE 的任意值或表达式，为IF函数的条件；
value_if_true ：判断条件结果为TRUE时的返回值； value_if_false：判
断条件结果为FALSE时的返回值。例:根据员工招聘考试表的总评填写考核结果，总评大于或等于60，考核结果为“通过”，否则为“不通过
”。操作步骤如下：步骤1：在N4单元格中给出公式：=IF(L4>=60,”通过”,” 不通过”)；步骤2：将该公式一直引用至N18
。3.3 判断函数实例6中采用函数的嵌套，根据员工笔试和面试成绩，给出考核结果，涉及到两个条件是否同时满足的问题，需要进行2次判断
。公式：=IF(J4<60,"",IF(K4<60,"","通过"))，外层IF先判断笔试是否<60，如果是，返回空串””，否则再
进行第二次判断，判断面试是否<60，如果是，返回空串””，否则，在两个条件都不满足情况下，返回“通过”。说明：IF函数中value
_if_true 及value_if_false参数可以是IF语句，用 value_if_false 及 value_if_tru
e 参数可以构造复杂些的条件。IF允许嵌套七层。3.3 判断函数2）逻辑函数AND和OR 逻辑函数AND AND函数的功能是当所有
参数的逻辑值为真时，返回 TRUE；只要一个参数的逻辑值为假，即返回 FALSE。函数格式：AND(logical1,logica
l2, ...)Logical1, logical2, ...??? 表示待检测的 1 到 30 个条件值，各条件值可为 TRUE
或 FALSE。 例：如果将例6中是否录用的条件改为：笔试和面试的条件都必须大于或等于60，并且排名要位于前6名（含第6
名）。则O4单元格公式改为： IF（AND(J4>=60,K4>=60,M4<=6)，”是”,”否”）,该公式用AND函数作为I
F函数的条件，来判断笔试、面试、排名三个要求是否都满足。三个要求都满足时，AND(J4>=60,K4>=60,M4<=6)的值为T
URE， IF函数的返回值是“是”，如果笔试、面试、排名中只要有一个不满足要求，则IF函数的返回值是“否”。
3.3 判断函数 上例也可以用IF语句嵌套完成。O4单元格的公式可以是： =IF(J4<60,”否”,IF(K4<
60,”否”,IF(M4<6,”否’,”是”)))逻辑函数OR OR函数的功能是在其参数组中，只要有一个参数逻辑值为TRUE时，返
回值为TRUE；所有参数的逻辑值都为 FALSE时，返回值为FALSE。函数格式：OR(logical1,logical2,...
)Logical1,logical2,...??? 为需要进行检验的 1 到 30 个条件，分别为 TRUE 或 FALSE。上例
用OR函数，则公式为：=IF(OR(J4<60,K4<60,M4>6),”否”,”是”)3.4名字定义和应用定义名字指定名字一、定
义名字1.实例7 利用名字进行招聘考试总评成绩的重新计算 【实例内容】将笔试成绩和面试成绩的比例数据所在的单元格B28和B29分别
定义为名字“笔试”和“面试”，修改总评公式，将公式中B28和B29分别用“笔试”和“面试”代替，查看总评成绩，再将B28和B29的
比例值改为40%和60%，观察总评结果。【操作步骤】步骤1：选择B28单元格，单击菜单栏中的“插入”，执行“名称”中的“定义”；步
骤2：在“定义名称”对话框的“在当前工作簿中的名称”文本框中输入“笔试”，如图3-17所示。步骤3：”引用位置”文本框显示$B$2
8，单击“确定”按钮；步骤4：重复上述3个步骤，将B29单元格名字定义为“面试”；一、定义名字步骤5：选择单元格区域L4:L18，
将原有数组公式修改为：=ROUND(J4:J18笔试+K4:K18面试,0)；步骤6：核对无误后按下CTRL+SHIFT+EN
TER，总评结果不变。步骤7：将B28和B29值修改为40%和60%，可以看到总评成绩按新比例自动调整。图3-17 定义名称对话框
一、定义名字图3-18 使用名字计算总评一、定义名字2.名字定义和应用知识介绍(1)定义名字的2种方法：1） 使用编辑栏左边的名称
框。其操作步骤如下；步骤1：选择需要命名的单元格或单元格区域，单击编辑栏左边的名称框；步骤2：输入名字后按回车键。这时可以看到名称
框上显示定义的名字。2）使用菜单定义名字。其操作步骤如下：步骤1：选择需要命名的单元格或单元格区域，单击菜单栏 “插入”，选择“名
称”中的“定义”命令；步骤2：在“定义名称”对话框的“在当前工作簿中的名称”文本框中输入要定义的名字；此时“引用位置”文本框中出现
选中的单元格或单元格区域地址，此处还可以修改。步骤3：单击“确定”按钮。默认状态下名字的使用范围是整个工作簿。二、指定名字1.实例
8 引用行列交叉点方式查找数据 【任务内容】将员工招聘考试信息表（如图3.16）单元格区域B2:O18的首行最左列指定为名字，并利
用名字查找李冰的总评和刘珍的笔试成绩。 二、指定名字【操作步骤】步骤1：选择需要命名的单元格区域B2:O18，单击菜单栏中的“插入
”，执行“名称”中的“指定”命令，出现“指定名称”对话框；步骤2：在“指定名称”对话框中对“名称创建于”进行设置：在“首行”和“最
左列”的复选框中打勾如图3-19所示；指定完毕单击“确定”按钮；二、指定名字步骤3：欲查找李冰的总评，只要数据清单外的空白单元格上
输入公式：=李冰 总评，如图3-20所示，输完按下ENTER键。同理，查找刘珍的笔试成绩，在空白单元格上输入公式：=刘珍 笔试成绩
图3-20 引用行列交叉点查找数据二、指定名字2.指定名字定义名字方式一次只能定义一个名字，因此它适用于给一个单元格或一个单元格区
域定义名字。在定义大量格式类似的名字时，往往需要重复操作。比如上述B28定义为“笔试”之后，还要用相同的方法定义B29为“面试”。
指定名字的方式通常为一个表的行、列指定名字。当把一个数据表的首行最左列（或末行最右列）指定为名字之后，就可以通过行列交叉点的方式来
引用单元格中的数据。引用行列交叉点单元格的公式为：=行的名字 列的名字公式中的行、列名字间必须留有空格。利用名字引用行列交叉点单元
格，不仅在本工作表中进行，也可以在本工作簿的其他工作表中引用行列交叉点单元格，也就是说在其它工作表中查找刘珍的笔试成绩与在本表中查
找，公式不变。3.5 查找和引用函数应用 引用函数INDIRECT 查找函数VLOOKUP 一、引用函数INDIRECT 1.实
例9 用名字和引用函数进行商品代码表中数据的查找 【任务内容】⑴将商品信息工作表中的商品代码表（图3-21）指定最左列为名字，查找
并填入商品销售表（图3-22）的商品代码列中；图3-21 商品代码表一、引用函数INDIRECT图3-22 商品销售表原始数据一
、引用函数INDIRECT【操作步骤】步骤1：选择商品信息工作表中商品代码表（图3-21）单元格区域G35：H63，将其最左列指定
为名字；步骤2：在商品销售表（图3-22）E3中输入公式：=INDIRECT（D3），得到润肤乳的代码rfl；步骤3：将E3中公式
引用至E113。如图3-23所示。可以看到商品销售表中商品代码列的数据由商品代码表中被引用过来。【操作结果】从商品代码表中得到商品
代码填入商品销售表中。如图3-23所示。一、引用函数INDIRECT图3-23商品销售表的商品代码查找结果一、引用函数INDIRE
CT2.引用函数INDIRECT 知识介绍引用函数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-样式的引用。一、引用函数INDIRECT例如在商品代码
表（图3-21）中，对G35：H63指定最左列为名字，此时： 函数INDIRECT(G35)的值为ds； 函数IND
IRECT(“电视机”) 的值也是ds； 假设G35另有定义的名字为“数字电视”，则函数INDIRECT(数字电视)值还是ds
。 这说明Ref_text?它可以是单元格的引用、字符串或名字。 但INDIRECT(“G35”)的值为“电视机”，即本单
元格中的内容。 公式“=INDIRECT(电视机)”将返回#REF。二、查找函数VLOOKUP 1.实例10用名字和查找函数进
行商品信息表中数据的查找 【任务内容】⑴为商品信息工作表中的商品信息表（图3-24）指定名字spxx，利用名字查找各商品销售单价填
入商品销售表（图3-23）的原单价列中；⑵完成优惠价的计算。计算方法是：原单价大于或等于2000元的，按90%优惠；原单价大于或等
于1000元且小于2000元的，按95%优惠；否则不优惠；⑶完成销售额的计算，从而完善商品销售表各列数据。如图3-25所示。销售额
=优惠价数量。⑷在商品信息表中的I2单元格任意输入某商品名称，查找出该商品的单价和产地，如图3-26-a，图3-26-b所示。二
、查找函数VLOOKUP图3-24 商品信息表二、查找函数VLOOKUP【任务操作步骤】步骤1：将商品信息表中单元格区域B2：F3
1定义名字spxx。步骤2：在商品销售表G3单元格输入公式：= VLOOKUP(D3,spxx,2,0)。步骤3：将上述公式引用至
G113。此时商品销售表的原单价全部由商品信息表中查找而来。步骤4：在H3中输入公式： =IF(G3<1000,G3,IF(G3<
2000,G395%,G390%))，并将公式引用至H113。得到商品销售表的优惠价。步骤5：在I3中输入公式：= F3H3
，并将公式引用至I113，得到商品销售表的销售额。（如图3-25所示）。步骤6：在商品信息表中单元格I3上输入公式：=VLOOKU
P(I2,spxx,2,0)，得到查找的产品单价。步骤7：在产地I4上输入公式：=VLOOKUP(I2,spxx,5,0)，得到查
找的产品产地。步骤8：在I2上输入要查找的商品名称（图3-26-a），即可看到该商品的单价和产地，如图3-26-b所示，此时I2单
元格可以多次输入要查找的商品名称，I3，I4都能显示出相应的单价和产地。在产品量大不便查找的情况下，采用这种方法是较容易的。二、查
找函数VLOOKUP【任务操作结果】从商品信息表中得到商品单价填入商品销售表的原单价中，并计算出优惠价和销售额。计算结果如图3-2
5所示。图3-25 商品销售表统计结果二、查找函数VLOOKUP在商品信息表中按商品名称查找单价和产地（图3-26-a），操作结果
如图3-26-b所示。图3-26-a 输入商品名称“空调” 图3-26-b 显示单价和产地二、查找函数VLOOKUP2.
查找函数VLOOKUP 知识介绍查找函数VLOOKUP的功能是以按列查找的方式从指定数据区域的第一列中查找给定的数据，返回在指定数
据区域中与找到的单元格同行指定列的单元格数据。函数格式如下： VLOOKUP(lookup_value,table_arra
y,col_index_num,range_lookup)Lookup_value：要查找的值。可以为数值、引用或文本字符串。Ta
ble_array：在其中查找数据的单元格区域。可以使用对区域或区域名称的引用， Col_index_num：数据区域中要返回的数
据所在的列序号。二、查找函数VLOOKUPRange_lookup：是一逻辑值，表明查找的方式是精确查找（与查找数据完全相同）还是
模糊查找（找不到完全相同数据时，则返回小于查找值的最大数值）。如果值为 TRUE（也可以是1） 或省略，表示模糊查找；如果值是 F
ALSE（或0），表示精确查找；找不到则返回错误值 #N/A。使用VLOOKUP查找时，当Range_lookup值为TRUE时，
查找区域Table_array中第一列数据要升序排列；否则可以不排序。3.6 条件求和函数应用1.实例11统计各部门及各员工的年度
销售额 【任务内容】统计各部门年度销售总额，填入部门年度销售汇总表中，如图3-27所示；统计各员工年度销售总额，填入员工销售统计表
中，如图3-28所示。图3-27 部门销售额统计表3.6 条件求和函数应用图3-28员工销售统计表3.6 条件求和函数应用【操作步
骤】步骤1：在部门年度销售汇总表B3单元格中输入如下公式：=SUMIF(商品销售表!B3:B113,部门年度销售汇总表!A3,商品
销售表!I3:I113)步骤2：采用引用方法继续算出其他部门的年度销售总额。查找区域与求和区域必须采用绝对引用。公式修改为：=SU
MIF(商品销售表!B$3:B$113,部门年度销售汇总表!A3,商品销售表!I$3:I$113)步骤3：将公式引用至B6，得到各
部门年度销售总额，步骤4：在员工销售统计表C3中输入公式：=SUMIF(商品销售表!A$3:A$113,A3,商品销售表!I$3:
I$113)步骤5：将上述公式引用至C24，由此得到每个员工的年度销售总额，如图3-28所示。3.6 条件求和函数应用2. 求和函
数SUMIF知识介绍SUMIF函数的功能是对满足指定条件的若干单元格求和。函数格式：SUMIF(range,criteria,su
m_range)Range?：用于条件判断的单元格区域；Criteria：必须满足的求和条件，其形式可以为数字、表达式或文本。例如
，条件可以表示为 32、" apples "、">=1000" 、或“电视机”。Sum_range?：为求和的数值单元格区域。上例
中，公式“=SUMIF(商品销售表!B3:B113,部门年度销售汇总表!A3,商品销售表!I3:I113)”在输入时，单元格区域可
用鼠标来选择，条件项“部门年度销售汇总表!A3”，若不采取引用时，也可用字符串“化妆部”表示，但必须注意公式中的参数采用字符串时必
须加引号。3.7文本函数应用1.实例12商品信息表中商品编号的编制 【任务内容】在商品信息表中，根据商品产地和商品名称，编制商品编
号。要求:从商品代码表和产地代码表中截取前两位代码，再加上序号（定义序号位数为3，不足三位前面置零），形成新的商品编码填入商品信息
表的商品编号列中.图3-29 编制商品信息表中的商品编号3.7文本函数应用【任务操作步骤】步骤1：分别将产地代码表单元格区域D35
：E45和商品代码表单元格区域G35：H63指定最左列为名字；步骤2：在商品信息表的第一商品对应的商品编号单元格E3中输入公式：
INDIRECT(F3)&LEFT(INDIRECT(B3),2)&TEXT(A3,"000")，步骤3：将E3公式向下引用至E
31。得到所有商品编号。3.7文本函数应用2.文本函数知识介绍:1）连接运算符“&” ：将两个文本值连接生成一个连续的文本值 .2
）截取子串函数LEFT/RIGHT/MID LEFT/RIGHT/MID函数功能： LEFT函数功能是返回文本字符串中最左边一
个或前几个字符。 RIGHT 函数的功能是返回文本字符串中最后一个或多个字符。? MID函数的功能是从文本字符串中的指定位置起返
回特定数目的字符，该数目由用户指定。函数格式： LEFT(text,num_chars) RIGHT(text,num
_chars) MID(text,start_num,num_chars)3.7文本函数应用其中： Text：是包含要提
取字符的文本字符串。 Num_chars：指定要由 LEFT或 RIGHT所提取的字符数。 Start_num?：是文本中
要提取的第一个字符的位置。 Num_chars?? 指定MID 从文本中截取的字符个数。说明：Num_chars 必须大于或等
于 0，省略 num_chars，则假定其为 1。如果 num_chars 大于文本长度，则 LEFT或RIGHT 返回所有文本。
例：设单元格A1值为文本字符串“ABCDEFGHIJKL”，公式=LEFT（A1，2）值为“AB”。 =RIGHT（A1,2）
&MID（A1,2,3）值为“KLBCD”3.7文本函数应用3）文本格式函数TEXT TEXT函数的功能是将数值转换为按指定数字格
式表示的文本。其结果将不再作为数字参与计算。函数格式：TEXT(value,format_text)其中： Value：为需要
转换为文本的数值。可以是数值、计算结果为数值的公式或含数值的单元格引用。 Format_text：为指定的数字格式。可用菜单“单
元格格式”对话框中“数字”选项卡“分类”框中的文本形式的数字格式。3.7文本函数应用例：在员工销售统计表A24中输入公式： =A3
&C2&TEXT(C3,"￥#,###.00")&"元" 则A24单元格值为：陈枫销售金额￥276,831.00元。 此
时C3单元格值276831已转换为文本并且格式为￥276,831.00。上例商品信息表的第一商品对应的商品编号单元格E3的公式为：
=INDIRECT(F3)&LEFT(INDIRECT(B3),2)&TEXT(A3,“000”) 上述公式值为：fjds001，
其含义是：福建电视001。分析：INDIRECT(F3)的值为fj。这是由于F3单元格值为福建，而福建又是名字，故取值为fj；LE
FT(INDIRECT(B3),2))是将INDIRECT(B3)运算得到的dsj截取最左边两个字符ds；TEXT(A3,"000
")是对序号A3 (值为1)转换文本，格式为001。3.8 日期和时间函数应用1.实例13根据员工的出生日期和工作日期计算年龄和工
龄 【任务内容】根据员工信息表中员工的出生日期和参加工作日期，统计出每个员工的年龄和工龄。要求： 在员工信息表中增加最后一列“
年龄”，根据出生日期计算出每个员工的年龄； 根据工作日期计算出员工工龄，填入员工工资数据来源工作表对应列中。3.8 日期和时间
函数应用【操作步骤】步骤1：在员工信息表中K2单元格中输入列标题“年龄”；步骤2：在K3中输入公式：=YEAR(TODAY())-
YEAR(H3)；步骤3：通过单元格格式设置，将该单元格格式设为“常规”。否则可能会出现日期形式数据。步骤4：将公式向下引用至K4
6；即完成每个员工的年龄计算。步骤5：在员工工资数据来源工作表中，选择员工工龄统计表的单元格I3，输入公式： =YEA
R(TODAY())-YEAR(VLOOKUP(G3,ygb,9,0))步骤6：将公式向下引用至I46。由此得到每个员工的工龄，如
图3-30所示。3.8 日期和时间函数应用图3-30 员工工龄计算结果 3.8 日期和时间函数应用分析: 公式“=YEAR(T
ODAY())-YEAR(H3)”是根据第一个员工的出生日期，计算出该员工的年龄，其中涉及到2个函数，系统日期函数和取年份函数;
YEAR(TODAY())是对系统日期所求年份，YEAR(H3)是对出生日期求年份，2个数相减得到年龄; 公式“YEA
R(TODAY())-YEAR(VLOOKUP(G3,ygb,9,0))”是求工龄，VLOOKUP(G3,ygb,9,0)是从员工
信息表中查找工龄统计表中第一个员工的工作日期，YEAR(VLOOKUP(G3,ygb,9,0))返回该员工参加工作的年份。 3.8
日期和时间函数应用2.日期和时间函数知识介绍1)显示当前系统日期和时间函数TODAY/NOW 函数功能：DOTAY函数是返回当前
日期的序列号。NOW函数是返回当前日期和时间的序列号。函数格式如下： TODAY( ) NOW()说明：如果在输入
函数前，单元格格式为“常规”，则TODAY( )和NOW()函数的结果都将设为日期格式。 假设系统日期是2008年3月22日，系统
时间是15:25。 单元格格式为“常规”： 输入公式：= TODAY( )，则显示值为：2008-3-22
输入公式：= NOW( )，则显示值为：2008-3-22 15:253.8 日期和时间函数应用2）年份/月份/日函数YEAR/M
ONTH/DAY YEAR函数的功能是返回某日期对应的年份。返回值为 1900 到 9999 之间的整数。MONTH函数的功能是返
回以序列号表示的日期中的月份。月份介于 1月到 12月之间的整数。DAY函数的功能是返回以序列号表示的某日期的天数，用整数 1 到
31 表示。其格式如下：YEAR(serial_number)MONTH(serial_number)DAY(serial_nu
mber)3.8 日期和时间函数应用其中：YEAR 函数中参数Serial_number为一个日期值，包含要查找年份的日期；MON
TH 函数中参数Serial_numbe表示一个日期值，包含要查找的月份；DAY 函数中参数Serial_number?为要查找的
那一天的日期。例：假设现在的系统日期是2008年1月22日，则：YEAR(TODAY())的值是2008; MONTH(TODAY
()) 的值是1；DAY(TODAY())的值是22。 3.8 日期和时间函数应用其中：YEAR 函数中参数Serial_numb
er为一个日期值，包含要查找年份的日期；MONTH 函数中参数Serial_numbe表示一个日期值，包含要查找的月份；DAY 函
数中参数Serial_number?为要查找的那一天的日期。例：假设现在的系统日期是2008年1月22日，则：YEAR(TODAY
())的值是2008; MONTH(TODAY()) 的值是1；DAY(TODAY())的值是22。3.8 日期和时间函数应用【实
例内容】在员工工资来源数据工作表中，含有4个数据表，即：学历档次表、职称职务档次表、员工工龄统计表和员工加班统计表。现根据员工的学
历、职务、工龄及加班时间计算工资，填入员工工资表（图3-31所示）相应列 。基本工资计算：基本工资=学历工资+职称职务工资+工龄30，加班补贴计算：加班补贴=加班时间20，应发工资计算：应发工资=基本工资+加班补贴，个人所得税计算：个人所得税=应发工资税率。应发工资大于6600按20%计税，大于3600且小于或等于6600按15%计税，大于2100且小于或等于3600按10%计税，大于1600且小于或等于2100按5%计税，否则不计税。实发工资计算：实发工资=应发工资-个人所得税-水电费-房租。3.8 日期和时间函数应用图3-31 员工工资表3.8 日期和时间函数应用任务操作步骤】基本工资计算（步骤1-步骤5）：步骤1：将学历档次表A2：B7和职务职称档次表D2：E11分别指定名字为最左列。步骤2：将员工工龄统计表单元格区域G3：I46定义为名字gl。步骤3：选择员工信息工作表，将员工信息表的单元格区域$A$3:$J$46定义名字为“ygb”。步骤4：选择员工工资表，在D3单元格输入下列公式：=INDIRECT(VLOOKUP(A3,ygb,6,0))+INDIRECT(VLOOKUP(A3,ygb,7,0))+VLOOKUP(A3,gl,3,0)30分析：此公式中(VLOOKUP(A3,ygb,6,0)是根据A3提供的工号，到员工信息表中查找该工号对应的员工的学历，再根据找到的学历采用INDIRECT函数得到该学历对应的金额；同理INDIRECT(VLOOKUP(A3,ygb,7,0))得到该员工的职称对应的金额；VLOOKUP(A3,gl,3,0)30是在工龄统计表中找到该员工的工龄，每年按30元，即得到对应于工龄部份的金额，学历工资、职称工资和工龄工资的总和即为该员工的基本工资。步骤5：向下引用至D46，如图3-32所示。3.8 日期和时间函数应用图3-32 员工基本工资计算3.8 日期和时间函数应用步骤6：计算加班补贴：在员工工资数据来源工作表的员工加班统计表图3-33中，给单元格区域K3：M46定义名字jb。步骤7：在员工工资表（图3-32）的E3单元格输入公式：=VLOOKUP(A3,jb,3)20，并将公式向下引用至E46，得到所有员工的加班补贴。3.8 日期和时间函数应用步骤8：应发工资计算：在F3单元格输入公式：=D3+E3，并向下引用至F46，得到所有员工的应发工资。步骤9：个人所得税计算：在G3单元格中输入下列公式后，将公式引用至G46：=IF(F3>6600,F320%,IF(F3>3600,F315%,IF(F3>2100,F310%,IF(F3>1600,F35%,0))))步骤10：实发工资计算：在J3单元格中输入公式：=F3-G3-H3-I3，计算出该员工的实发工资，并向下引用至J46。如图3-34所示。3.8 日期和时间函数应用图3-34 员工工资统计结果 |
|
