项目六 excel公式和函数使用

项目六 公式和函数使用

项目知识点：

1. 掌握的EXCEL中使用公式

2. 掌握常用函数的使用，包括SUM、AVERAGE、COUNT、MAX、MIN、COUNTIF、IF、RANK、LEFT、

RIGHT、MID

3. 熟悉使用公式、函数分析处理数据。

项目任务：

任务一：工资表及成绩表的分析； 任务二：成绩表的分析

任务三：成绩表及学生个人信息表的分析 任务四：常用函数函数综合训练。

拓展任务：

任务一：物业管理水费统计表 任务二：操行分统计表

任务三：函数“VLOOKUP”及“HLOOKUP”的使用。

知识简述 1. 公式：

1) 公式是用运算符将数据、单元格地址、函数等连接在一起的式子。 2) 向单元格输入公式时，心须以等号“=”开头。 常用的实例：

=12*78-20 常用运算

=A2+C3 对单元格A2和C3中的值相加

=AVERAGE（A2：C3） 使用Excel函数，求单元格区域中各数据的平均值。 3）“公式”跨工作表的使用。 例如：任务一

在“总评”工作表中用公式求出各位同学各科（语文、数学、英语）的总评成绩（期中占30%、期末占60%、平时占10%）。

2. 函数的使用：

1） 求和函数SUM：

主要功能：计算所有参数数值的和。

使用格式：SUM（Number1,Number2……）

参数说明：Number1、Number2……代表需要计算的值，可以是具体的数值、引用的单元格（区域）、逻辑值等。

特别提醒：如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略；

2） 求平均值函数AVERAGE：

主要功能：求出所有参数的算术平均值。 使用格式：AVERAGE(Value1, value2,……)

参数说明：Value1, value2,……：需要求平均值的数值或引用单元格（区域），参数不超过30个。

3） 求最大值函数MAX：

主要功能：求出一组数中的最大值。 使用格式：MAX(number1,number2……)

参数说明：number1,number2……代表需要求最大值的数值或引用单元格（区域），参数不超过30个

4） 求最小值函数MIN：

主要功能：求出一组 数中的最小值。 使用格式：MIN(number1,number2……)

参数说明：number1,number2……代表需要求最小值的数值或引用单元格（区域），参数不超过30个。

5） 求数值个数COUNT

主要功能：统计某个单元格区域中包含数字的单元格的个数。 使用格式：COUNTIF(Value1, value2a)

参数说明：Value1, value2, ... 为所要计算区域中包含数字的单元格个数。

6） 求字符个数：COUNTA

主要功能：计算单元格区域或数组中包含数据的单元格个数。 使用格式： COUNTA(Value1, value2)

参数说明：Value1, value2, ... 为所要计算的值，参数个数为 1 到 30 个。

7） 统计满足某个条件的个数函数COUNTIF：

主要功能：统计某个单元格区域中符合指定条件的单元格数目。 使用格式：COUNTIF(Range,Criteria)

参数说明：Range代表要统计的单元格区域；

Criteria表示指定的条件表达式。

特别提醒：允许引用的单元格区域中有空白单元格出现。

8） 排位函数RANK：

主要功能：返回某一数值在一列数值中的相对于其他数值的排位。 使用格式：RANK（Number,ref,order）

参数说明：Number代表需要排序的数值；

ref代表排序数值所处的单元格区域； order代表排序方式参数（如果为“0”或者忽略，则按降序排名，即数值越大，排名结果数值越小；如果为非“0”值，则按升序排名，即数值越大，排名结果数值越大；）。

特别提醒：

相对引用：当使用填充时，被调用单元格中的内容会随着地址的改变而改变。 绝对引用：当使用填充时，被调用单元格中的内容保持不变。 按F4键进行切换

9） 条件函数IF

主要功能：根据对指定条件的逻辑判断的真假结果，返回相对应的内容。 使用格式：=IF(Logical_test,Value_if_true,Value_if_false) 参数说明：Logical_test代表逻辑判断表达式；

Value_if_true表示当判断条件为逻辑“真（TRUE）”时的显示内容，如果忽略返回“TRUE”；

Value_if_false表示当判断条件为逻辑“假（FALSE）”时的显示内容，如果忽略返回“FALSE”。

例如：任务三 10）

取文本左边n个字符LEFT：

主要功能：从一个文本字符串的第一个字符开始，截取指定数目的字符。 使用格式：LEFT(text,num_chars)

参数说明：text代表要截字符的字符串

num_chars代表给定的截取数目。

11） 取文本右边n个字符RIGHT：

主要功能：从一个文本字符串的最后一个字符开始，截取指定数目的字符。 使用格式：RIGHT(text,num_chars)

参数说明：text代表要截字符的字符串；

num_chars代表给定的截取数目。

12） 从文本中第n个字符开始连续取m个字符MID：

主要功能：从一个文本字符串的指定位置开始，截取指定数目的字符。 使用格式：MID(text,start_num,num_chars) 参数说明：text代表一个文本字符串；

start_num表示指定的起始位置； num_chars表示要截取的数目。

特别提醒：公式中各参数间，要用英文状态下的逗号“,”隔开。

13） VLOOKUP函数

函数名称：VLOOKUP

主要功能：在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值。

使用格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 参数说明：Lookup_value代表需要查找的数值；

T able_array代表需要在其中查找数据的单元格区域；

Col_index_num为在table_array区域中待返回的匹配值的列序号（当Col_index_num为2时,返回table_array第2列中的数值，为3时，返回第3列的值??）；

ange_lookup为一逻辑值，如果为TRUE或省略，则返回近似匹配值，也就

是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果为FALSE，则返回精确匹配值，如果找不到，则返回错误值#N/A。

14）

HLOOKUP 函数

主要功能：在表格的首列查找指定的数据，并返回指定的数据所在行中的指定列处的数据

使用格式：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup 参数说明：Lookup_value为需要在数据表第一行中进行查找的数值；

Lookup_value 可以为数值、引用或文本字符串；

Table_array为需要在其中查找数据的数据表。使用对区域或区域名称的引用；

Row_index_num为table_array 中待返回的匹配值的行序号；

Range_lookup为一逻辑值，指明函数 HLOOKUP 查找时是精确匹配，还是近似匹配。如果为TURE，则返回近似匹配值。也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值。如果 range_lookup 为 FALSE或省略，函数 HLOOKUP 将查找精确匹配值，如果找不到，则返回错误值#N/A。

任务一：工资表及成绩表的分析；（使用公式计算结果） 学习目标： 1、 掌握的EXCEL中使用公式

任务实施及要求： 打开工作簿文件工资表及成绩表的分析. XLSX，完成下列操作，最终效果参考“工资表及成绩表的分析样文.docx”完成后以原文件名保存。

1.在工作表Sheet1中，在“总评成绩”列，按公式“0.7*机试成绩+0.3*笔试成绩”求出每个学生的总评成绩。

2.在工作表Sheet2中，进行下列操作： （1） （2） （3） （4） （5） （6）

计算每位员工的“应发工资”（应发工资=基本工资+奖金）。 计算每位员工的“扣款”（扣款=应发工资*20%）。

计算每位员工的“实发工资”（实发工资=应发工资-扣款）。

在 “期中”工作表中，分别用公式计算出各位学生的“总分”、“平均分”。 在 “期末”工作表中，分别用公式计算出各位学生的“总分”、“平均分” 在“总评”工作表中用公式求出各位同学各科（语文、数学、英语）的总评成

绩（期中占30%、期末占60%、平时占10%）。

效果图：

参照“工资表及成绩表的分析样文”

任务二：成绩表的分析

学习目标： 1、理解常用函数的使用，包括SUM、AVERAGE、COUNT、MAX、MIN、COUNTIF、RANK。

任务实施及要求： 打开工作簿文件成绩表的分析. XLSX，完成下列操作，完成后以原文件名保存。最终效果参考“成绩表的分析样文.docx”完成后以原文件名保存

1.在“成绩单”工作表中，使用函数进行下列计算 在G2:G21单元格求出各位学生的总分（SUM） 在F2:F21单元格求出各位学生的平均分（AVERAGE）

分别在C23、D23、E23单元格求出参加各科考试的人数（COUNT）（*代表该生没有参加考试）

在C24单元格求出全班人数（COUNTA）

分别在C26、D26、E26单元格求出参加各科考试成绩最高分（MAX） 分别在C27、D27、E27单元格求出参加各科考试成绩最低分（MIN） 分别在C25、D25、E25单元格求出参加各科不及格人数（COUNTIF）

在G2:G21单元格求出各位学生总分的排名（RANK）

将各个学生各科不及格的成绩所在单元格格式设置为“浅红色填充深红色文本” 2.在“销售单”的工作表完成以下的各项操作（补充） 在C14：F14单元格求出各产品的销售的平均值 在C15：F15单元格求出各产品的销售的最大值 在C16：F16单元格求出各产品的销售的最小值 在“销售总额”列求出各分店的销售总额 在“销售排名”列求出各分店销售总额的排名

将各产品高于本产品平均值的单元格设置下图所示的格式（条件格式）

效果图：

参照“成绩表的分析样文”。

任务三：成绩表及学生个人信息表的分析 学习目标： 1、 理解常用函数的使用，包括IF、LEFT、RIGHT、MID

任务实施及要求： 打开工作簿文件成绩表及学生个人信息表的分析. XLSX，完成下列操作，最终效果参考“成绩表及学生个人信息表的分析样文.docx”完成后以原文件名保存

1.在sheet1工作表中，使用函数进行下列计算。

在I2：I21单元格区域使用IF函数，计算每个学生的语文成绩等级，如果学生语文等于大于60分显示“及格”，否则显示“不及格”。

在J2：J21单元格区域计算每个学生的成绩等级，其中总评的总分成绩在180分以下为“不及格”，在180～240分(含180分)之间为“及格”。在240～270分之间(含240分)为“优良”，270以上（含270分）为“优秀”（IF）。 2.在sheet2工作表中，使用函数进行下列计算。

（说明: 中国公民18位身份号码的规定，排列顺序从左至右依次为：第1~6位是“行政区划代码”， 第7~14位是“出生日期”， 第15~17位是“顺序码”，最后一位是“校验码”。其中“顺序码”是奇数则表示是男性，偶数则是女性）

在C2：C22单元格区域使用LEFT 函数，从身份证号中获取每个员工的“行政区划代码”，。 在D2：D22单元格区域使用MID 函数，从身份证号中获取每个员工的“出生日期”。 在E2：E22单元格区域使用MID 函数，从身份证号中获取每个员工的“顺序码”。 在F2：F22单元格区域使用RIGHT 函数，从身份证号获取每个员工的“校验码”。

效果图：

参照“成绩表及学生个人信息表的分析样文”。

任务四：常用函数综合训练 学习目标： 1、 理解常用函数的使用，包括SUM、AVERAGE、COUNT、MAX、MIN、COUNTIF、IF、RANK、LEFT、

RIGHT、MID

2、 根据所学的知识熟悉使用公式、函数分析处理数据。

任务实施及要求： 打开工作簿文件常用函数函数综合训练. XLSX，完成下列操作，最终效果参考“常用函数函数综合训练样文.docx”，完成后以原文件名保存

1.在sheet1工作表，使用公式，在E2：E20单元格区域，使用公式求出各位考生的“总评成绩”（总评成绩=0.6*机试成绩+0.4*笔试成绩）。 2. 在sheet2工作表，进行下列操作： 在F2:F21单元格求出各位学生的总分。 在G2:G21单元格求出各位学生的平均分。

分别在C23、D23、E23单元格求出参加各科考试的人数

在C24单元格求出全班人数

分别在C25、D25、E25单元格求出参加各科不及格人数 分别在C26、D26、E26单元格求出参加各科考试成绩最高分 分别在C27、D27、E27单元格求出参加各科考试成绩最低分 3. 在sheet3工作表，进行下列操作： 在F2:F21单元格求出各位学生的总分。 在G2:G21单元格求出各位学生总分的排名。

在I2：I21单元格区域计算每个学生的操行等级，其中操行成绩在60分以下为“不及格”，在60～70分(含60分)之间为“及格”。在70～80分之间(含70分)为“中等”，80～90分（含80分）为“优良”，90以上（含90分）为“优秀”。 3. 在sheet4工作表，进行下列操作（补充）

在“入学年份”列求出各个学生的入学年份（学生的入学年份是学号的前两位） 在“座位号”列求出各个学生的座位号（学生的座位号是学号的后两位） 在“所属系部编号”列求出各个学生所属的系部（学号的第三位表示所属系部）

在“专业名称”列求出各学生的专业（系部编号为2专业名称为“工商部”，系部编号为3专业名称为“电子部”，系部编号为4专业名称为“计算机部”）

效果图：

参照“常用函数函数综合训练样文”。

任务拓展

任务一：物业管理水费统计表

一、情境描述：假如你是小区物业管理处的管理员，每月要将水电工抄录的各住户的用水情况进行统计计费，以便派单收取水费。

二、实验内容及要求

? 打开“水费计费表-A.xlsx”工作簿，参考样文“水费计费表-A样文”,各个工作表功

能作用说明如下：

A．“抄表详细表”是记录1-12月份的用水量（按吨作单位）

B．“实交费登记表”是各住户每月交的水费金额，可以多交作为预交款

C．“应交费计算表”用来计算每月应交水费金额，计算方法：本月抄表度数，减去上月抄表度数，得到本月实际用水量，乘以每吨水单价，再加上月累计欠费金额。

D．“欠费登记表”，用来计算每月各住户欠费情况

1． 在“应交费计算表”，计算出1月份各住户应交的水费金额（1月份实际用水量*水费单价）

2． 在“实交费登记表”中，将P列数据复制到B列，模拟用户实交1月份水费金额。 3． 在“欠费登记表”，计算出1月份各住户欠费（预交费）金额。（1月份应交费金额-1月份实交费）

4． 在“抄表详细表”，将P列数据复制到D列，模拟2月份抄表录入数据。 5． 在“应交费计算表”，计算出2月份各住户应交的水费金额（含1月份欠费金额） 6． 在“实交费登记表”中，将Q列数据复制到C列，模拟用户实交2月份水费金额。 7． 在“欠费登记表”，计算出2月份各住户欠费（预交费）金额。（2月份应交费金额-2月份实交费）

? 打开“水费计费表-B.xlsx”工作簿，参考样文“水费计费表-B样文”。

(1) 计算各住户2月份应交水费金额：要求如果2月份抄表数还没有数据，则显示空白；否则计算出2月份应交的水费金额。并将此公式函数复制到3-12月份，方便以后，当每月录入“抄表数”及上月用户“实交水费金额”，能自动求出用户当月应交水费金额。

? 提示，使用IF函数及公式，自行想出验证数据是否正确方法

(2) 计算各住户2月份水费欠费情况，要求如果2月份应交费没有计算出来，则2月份欠交费显示空白；否则计算出2月份欠费金额。并将此公式函数复制到3-12月份，方便以后，只要计算出“本月应交水费”及录入本月用户“实交水费金额”后，能自动求出当月各用户欠费情况。

? 提示，使用IF函数及公式，自行想出验证数据是否正确方法 ?

打开“奖牌积分计算表.xlsx”工作簿，参考样文“奖牌积分计算表样文”，理解工

作表数据含义，使用公式计算各个国家奖牌总数（金、银、铜牌数相加）、奖牌总分，使用统计排名函数，求出积分排名（依据总分）。

任务二：操行分统计表

1．打开“操行分统计表”，计算各周的每位同学的“减分小计、加分小计”；在“16”工作表后面，添加一个工作表，作为汇总操行分表，用来求出各位同学各个项目加分总和、减分总和及各位同学最终操行得分（加分总计-减分总计）

2．打开“外聘教师酬金计算表.xslx”工作簿, (1) 按照下表，使用IF函数求出各位教师的职称系数

职称 教授 副教授 讲师 助教 职称系数 1.8 1.5 1.2 1 (2) 按照下表，使用IF函数求出各位教师的奖金

学生评价 优 良 中 差 奖金 1000 600 300 0 (1) 酬金标准计算方法：“酬金基数*职称系数” (2) 应发课酬计算方法：课酬+奖金 (3) 理解数据表，求出其它各项数值

任务三：函数“VLOOKUP”及“HLOOKUP”的使用。

打开工作簿“卡号表查找.XLSX”，参考“卡号表查找-样文”完成下列操作，完成后以原文件名保存。

（1）在sheet1工作表中，利用“表1”的数据，查找出员工相对应的卡号。（VLOOKUP） （2）在sheet2工作表中，求出项目的单价。(HLOOKUP)

本文来源：https://www.bwwdw.com/article/5pop.html