excel函数公式volkup使用例题

项目六 公式和函数使用

项目知识点：

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. 掌握的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. 函数

有用的excel函数公式

项目检验重复公式

公式IF(MATCH($A2,$A$2:$A$18,)=ROW()-1,"","重复")

生日7日提醒公式

IF(DATEDIF(U13-7,TODAY(),"YD")<=7,"提醒","")

对大于100的产量进行求和

SUMPRODUCT((B2:B11>100)*B2:B11)

对大于100或者小于110的数据 SUMPRODUCT((B12:B27>100)*(B12:B27<110)*B12:B27) 求和 对一车间男性职工的工资求和 SUMPRODUCT((B2:B10="一车间")*(C2:C10="男")*D2:D10) 对姓赵的女职工工资求和 求前三名产量之和 偏移3位求和 SUMPRODUCT((LEFT(A2:A10)="赵")*(C2:C10="女")*D2:D10) SUM(LARGE(B2:B10,{1,2,3})) SUM(OFFSET($B$1,MATCH(A15,A2:A11,0),,,3))

1

1，对三组生产数据求和:=SUM(B2:B7,D2:D7,F2:F7)：

2，对生产表中大于100的产量进行求和:{=SUM((B2:B11>100)*B2:B11)}：

3，对生产表大于110或者小于100的数据求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)}： 4，对一车间男性职工的工资求和:{=SUM((B2:B10=\一车间\男\： 5，对姓赵的女职工工资求和:{=SUM((LEFT(A2:A10)=\赵\女\6，求前三名产量之和:=SUM(LARGE(B2:B10,{1,2,3}))： 7，求所有工作表相同区域数据之和:=SUM(A组:E组!B2:B9)

8，求图书订购价格总和:{=SUM((B2:E2=参考价格!A$2:A$7)*参考价格!B$2:B$7)} 9，求当前表以外的所有工作表相同区域的总和:=SUM(一月:五月!B2), 10，用SUM函数计数:{=SUM((B2:B9=\男\ 11，求1累加到100之和:{=SUM(ROW(1:100))}

12，多个工作表不同区域求前三名产量和:{=SUM(LARGE(CHOOSE({1,2,3,4,5},A组!B2:B9,B组!B2:B9,C组!B2:

EXCEL函数大全

1.数据库和清单管理函数

DAVERAGE 返回选定数据库项的平均值

DCOUNT 计算数据库中包含数字的单元格的个数

DCOUNTA 计算数据库中非空单元格的个数

DGET 从数据库中提取满足指定条件的单个记录

DMAX 返回选定数据库项中的最大值

DMIN 返回选定数据库项中的最小值

DPRODUCT 乘以特定字段（此字段中的记录为数据库中满足指定条件的记录）中的值

DSTDEV 根据数据库中选定项的示例估算标准偏差

DSTDEVP 根据数据库中选定项的样本总体计算标准偏差

DSUM 对数据库中满足条件的记录的字段列中的数字求和

DVAR 根据数据库中选定项的示例估算方差

DVARP 根据数据库中选定项的样本总体计算方差

GETPIVOTDATA 返回存储在数据透视表中的数据

2.日期和时间函数

DATE 返回特定时间的系列数

DATEDIF 计算两个日期之间的年、月、日数

DATEVALUE 将文本格式的日期转换为系列数

DAY 将系列数转换为月份中的日

DAYS360 按每年360天计算两个日期之间的天数

EDATE 返回在开始日期之前或之后指定月数的某个日期的系列数

EOMONTH 返回指定月份数之前或之后某月的最后一天的系列数

HOUR 将系列数转换为小时

一、员工信息表公式

1、计算性别(F列)

=IF(MOD(MID(E3,17,1),2),'男','女')

2、出生年月(G列)

=TEXT(MID(E3,7,8),'0-00-00')

3、年龄公式(H列)

=DATEDIF(G3,TODAY(),'y')

4、退休日期(I列)

=TEXT(EDATE(G3,12*(5*(F3='男') 55)),'yyyy/mm/dd aaaa')

5、籍贯(M列)

=VLOOKUP(LEFT(E3,6)*1,地址库!E:F,2,)

注：附带示例中有地址库代码表

6、社会工龄(T列)

=DATEDIF(S3,NOW(),'y')

7、公司工龄(W列)

=DATEDIF(V3,NOW(),'y')&'年'&DATEDIF(V3,NOW(),'ym')&'月'&DATEDIF(V3,NOW(),'md')&'天'

8、合同续签日期(Y列)

=DATE(YEAR(V3) LEFTB(X3,2),MONTH(V3),DAY(V3))-1

9、合

公式与函数

电子表格与数据库系统

公式与函数----主讲 沈静 主讲

19:28

公式与函数

电子表格与数据库系统

本节课的内容提要： 公式的基本操作 运算符的类型和优先级 单元格引用 数组公式 函数的基本操作

19:28

公式与函数

电子表格与数据库系统

概述: 概述:Excel不仅具有基本的制表功能, Excel不仅具有基本的制表功能,还有 不仅具有基本的制表功能 强大的计算功能,计算离不开公式和函数. 强大的计算功能,计算离不开公式和函数. 公式是单元格中的一系列值 是单元格中的一系列值、 公式是单元格中的一系列值、单元格 引用、名称或运算符的组合， 引用、名称或运算符的组合，可以生成新 的值。 的值。 数组公式可以简化计算 可以简化计算, 数组公式可以简化计算,可以同时进行 多个计算并返回一种或多种结果， 多个计算并返回一种或多种结果，每个结 果显示在一个单元格. 果显示在一个单元格.19:28 3

公式与函数

电子表格与数据库系统

概述: 概述:函数是预定义的内置公式， 函数是预定义的内置公式，可以进行 是预定义的内置公式 数学、 数学、文本及逻辑的运算或者查找工作表 的信息。 的信息。

19:28

公式与函数

认识公式

电子表格与数据库系统

公式是

Excel练习2-公式与函数

拓展练习

请将“Excel练习2素材”文件夹改为你的中文姓名。 1. 打开“成绩表.xls”，按以下要求操作，完成后以原名保存。

1）把工作表“学生成绩表”复制为新工作表并改名为“公式与函数”，调整各列宽为最适合列宽。 2）在表格右侧增加两列，列标题为“总分”和“平均分”，用函数或公式计算出所有记录的总分和平均分。

3）用函数计算各列合计，结果放在表格最下面一行。在该行的第一个单元格中输入“合计”，作为该行的标题。

4）在表格右侧增加一列“总分率”，并计算每个学生的总分率（总分率=个人总分/合计总分）。 5）计算出各科成绩及总分的最大值和最小值，结果放在表格下方，并设行标题“最大值”和“最小值”在结果行的第一个单元。

6）在表格右侧增加一列“序号”，并用函数计算出每个学生学号的最后两位编码。

7）在表格右侧增加两列“出生日期”和“年龄”，在“出生日期”列随意输入一些日期，在“年龄”列用函数和公式计算出每个学生的年龄。

2．打开“练习.xls”， 按以下要求操作，完成后以原名保存。

1）在“语文成绩”和“数学成绩”工作表中，利用公式计算总评成绩列数据，其中平时成绩占30%，考试成绩占70%。然后在“总成绩”工作表中

公式与函数

电子表格与数据库系统

公式与函数----主讲 沈静 主讲

19:28

公式与函数

电子表格与数据库系统

本节课的内容提要： 公式的基本操作 运算符的类型和优先级 单元格引用 数组公式 函数的基本操作

19:28

公式与函数

电子表格与数据库系统

概述: 概述:Excel不仅具有基本的制表功能, Excel不仅具有基本的制表功能,还有 不仅具有基本的制表功能 强大的计算功能,计算离不开公式和函数. 强大的计算功能,计算离不开公式和函数. 公式是单元格中的一系列值 是单元格中的一系列值、 公式是单元格中的一系列值、单元格 引用、名称或运算符的组合， 引用、名称或运算符的组合，可以生成新 的值。 的值。 数组公式可以简化计算 可以简化计算, 数组公式可以简化计算,可以同时进行 多个计算并返回一种或多种结果， 多个计算并返回一种或多种结果，每个结 果显示在一个单元格. 果显示在一个单元格.19:28 3

公式与函数

电子表格与数据库系统

概述: 概述:函数是预定义的内置公式， 函数是预定义的内置公式，可以进行 是预定义的内置公式 数学、 数学、文本及逻辑的运算或者查找工作表 的信息。 的信息。

19:28

公式与函数

认识公式

电子表格与数据库系统

公式是

介绍了EXCEL中的常用函数

数据库和清单管理函数 AVERAGE 返回选定数据库项的平均值 COUNT 计算数据库中包含数字的单元格的个数 COUNTA 计算数据库中非空单元格的个数 DGET 从数据库中提取满足指定条件的单个记录 MAX 返回选定数据库项中的最大值 MIN 返回选定数据库项中的最小值 PRODUCT 乘以特定字段（此字段中的记录为数据库中满足指定条件的记录）中的值

STDEV 根据数据库中选定项的示例估算标准偏差

STDEVP 根据数据库中选定项的样本总体计算标准偏差

SUM 对数据库中满足条件的记录的字段列中的数字求和

VAR 根据数据库中选定项的示例估算方差

VARP 根据数据库中选定项的样本总体计算方差

GETPIVOTDATA 返回存储在数据透视表中的数据

日期和时间函数

DATE 返回特定时间的系列数

DATEDIF 计算两个日期之间的年、月、日数

DATEVALUE 将文本格式的日期转换为系列数 DAY 将系列数转换为月份中的日 DAYS360 按每年360天计算两个日期之间的天数 EDATE 返回在开始日期之前或之后指定月数的某个日期的系列数 EOMONTH 返回指定月份数之前或之后某月的最后一天的系列数