数据库函数应用技巧

Excel 员工工资表统计实战图解

使用数据库函数可以处理和分析数据清单中的数据，以得到用户想要的资料。本节将通过一个综合实例，来说明数据库函数的使用。

打开“员工工资表.xlsx”工作簿，本例中的原始数据如图15-39所示。可以看到，该数据清单的数据字段包括了员工姓名、性别、年龄、职务及其工资额。

根据如图15-39所示的基础数据清单，公司需要了解的信息有：

销售员中工资额的最高值。
工资额在1200到1500间的员工个数。
女员工的平均年龄。
采购员的工资总和。
经理苏小北的工资额。
采购员中工资额的最小值。

下面将详细讲解怎样使用数据库函数，对上面的信息进行统计。

STEP01：根据公司的要了解的信息设置计算表格，结果如图15-40所示。

图15-39　原始数据

图15-40　计算表格

STEP02：统计销售员中工资额的最高值。在G11:G12单元格区域设置数据库查询的数据字段“职务”和条件值“销售员”，然后选中J2单元格，在编辑栏中输入公式“=DMAX(A1:E15,E1,G11:G12)”，按“Enter”键返回，即可计算出销售员中工资额的最高值，计算结果如图15-41所示。

STEP03：统计工资额在1200到1500间的员工个数。在H11:I12单元格区域设置数据库查询的数据字段“工资额”和条件值“>1200”和“<1500”，然后选中J3单元格，在编辑栏中输入公式“=DCOUNT(A1:E15,E1,H11:I12)”，按“Enter”键返回，即可计算出工资额在1200到1500间的员工个数，计算结果如图15-42所示。

图15-41　统计销售员中工资额的最高值

图15-42　统计工资额在1200到1500间的员工个数

STEP04：统计女员工的平均年龄。在J11:J12单元格区域，设置数据库查询的数据字段“性别”和条件值“女”，然后选中J4单元格，在编辑栏中输入公式“=DAVERAGE(A1:E15,C1,J11:J12)”，按“Enter”键返回，即可计算出女员工的平均年龄，计算结果如图15-43所示。

STEP05：统计采购员的工资总和。在G13:G14单元格区域设置数据库查询的数据字段“职务”和条件值“采购员”，然后选中J5单元格，在编辑栏中输入公式“=DSUM(A1:E15,E1,G13:G14)”，按“Enter”键返回，即可计算出采购员的工资总和，计算结果如图15-44所示。

图15-43　统计女员工的平均年龄

图15-44　统计采购员的工资总和

STEP06：统计经理苏小北的工资额。在H13:I14单元格区域设置数据库查询的数据字段“职务”和“姓名”，设置条件值“经理”和“苏小北”，然后选中J6单元格，在编辑栏中输入公式“=DGET(A1:E15,E1,H13:I14)”，按“Enter”键返回，即可计算出经理苏小北的工资额，计算结果如图15-45所示。

STEP07：统计采购员中工资额的最小值。在J13:J14单元格区域设置数据库查询的数据字段“职务”和条件值“采购员”，然后选中J7单元格，在编辑栏中输入公式“=DMIN(A1:E15,E1,J13:J14)”，按“Enter”键返回，即可计算出采购员中工资额的最小值，计算结果如图15-46所示。

图15-45　统计经理苏小北的工资额

图15-46　统计采购员中工资额的最小值

Excel 计算总体方差：DVARP函数

DVARP函数用于返回将列表或数据库中满足指定条件的列中数值作为样本总体，计算出样本的总体方差。其语法如下：

DVARP(database,fi eld,criteria)

下面通过实例详细讲解该函数的使用方法与技巧。根据如图15-1所示的基础数据清单，班主任想要了解：

性别为女生的英语成绩的样本总体方差。
总分>=240分的男生成绩的样本总体方差。

具体操作步骤如下。

STEP01：根据上面提出的查询条件设置计算表格和条件区域，结果如图15-36所示。

图15-36　计算表格和条件区域

STEP02：选中E22单元格，在编辑栏中输入公式“=DVARP(A1:G16,E1,A25:A26)”，然后按“Enter”键返回，即可计算出女生的英语成绩的样本总体方差，结果如图15-37所示。

STEP03：选中E23单元格，在编辑栏中输入公式“=DVARP(A1:G16,F1,B25:C26)”，然后按“Enter”键返回，即可计算出总分>=240分的男生成绩的样本总体方差，结果如图15-38所示。

图15-37　计算性别为女生的英语成绩的样本总体方差

图15-38　计算总分>=240分的男生成绩的样本总体方差

Excel 计算样本方差：DVAR函数

DVAR函数用于返回将列表或数据库中满足指定条件的列中数值作为一个样本，估算样本的总体方差。其语法如下：

DVAR(database,fi eld,criteria)

下面通过实例详细讲解该函数的使用方法与技巧。根据如图15-1所示的基础数据清单，班主任想要了解：

性别为女生的英语成绩的样本方差。
总分>=240分的男生成绩的样本方差。

具体操作步骤如下。

STEP01：根据上面提出的查询条件设置计算表格和条件区域，结果如图15-33所示。

图15-33　计算表格和条件区域

STEP02：选中E22单元格，在编辑栏中输入公式“=DVAR(A1:G16,E1,A25:A26)”，然后按“Enter”键返回，即可计算出性别为女生的英语成绩的样本方差，结果如图15-34所示。

STEP03：选中E23单元格，在编辑栏中输入公式“=DVAR(A1:G16,F1,B25:C26)”，然后按“Enter”键返回，即可计算出总分>=240分的男生成绩的样本方差，结果如图15-35所示。

图15-34　计算性别为女生的英语成绩的样本方差

图15-35　计算总分>=240分的男生成绩的样本方差

Excel 计算数值和：DSUM函数

DSUM函数用于返回列表或数据库中满足指定条件的列中数值之和。其语法如下：

DSUM(database,fi eld,criteria)

下面通过实例详细讲解该函数的使用方法与技巧。根据如图15-1所示的基础数据清单，班主任想要了解：

所有男生的语文成绩的总和。
数学成绩为80～90的成绩总和。

具体操作步骤如下。

STEP01：根据上面提出的查询条件设置计算表格和条件区域，结果如图15-30所示。

图15-30　计算表格和条件区域

STEP02：选中E22单元格，在编辑栏中输入公式“=DSUM(A1:G16,C1,A25:A26)”，然后按“Enter”键返回，即可计算出所有男生的语文成绩的总和，结果如图15-31所示。

STEP03：选中E23单元格，在编辑栏中输入公式“=DSUM(A1:G16,D1,B25:C26)”，然后按“Enter”键返回，即可计算出数学成绩为80～90的成绩总和，结果如图15-32所示。

图15-31　计算所有男生的语文成绩的总和

图15-32　计算数学成绩为80～90的成绩总和

Excel 计算样本标准偏差：DSTDEV函数

DSTDEV函数用于返回将列表或数据库中满足指定条件的列中数字作为一个样本，估算出的样本总体标准偏差。其语法如下：

DSTDEV(database,fi eld,criteria)

下面通过实例详细讲解该函数的使用方法与技巧。根据如图15-1所示的基础数据清单，班主任想要了解：

性别为女生的英语成绩标准偏差。
总分大于等于240分的男生成绩标准偏差。

具体操作步骤如下。

STEP01：根据上面提出的查询条件设置计算表格和条件区域，结果如图15-24所示。

STEP02：选中E22单元格，在编辑栏中输入公式“=DSTDEV(A1:G16,E1,A25:A26)”，然后按“Enter”键返回，即可计算出性别为女生的英语成绩标准偏差，结果如图15-25所示。

图15-24　计算表格和条件区域

图15-25　计算性别为女生的英语成绩标准偏差

STEP03：选中E23单元格，在编辑栏中输入公式“=DSTDEV(A1:G16,F1,B25:C26)”，然后按“Enter”键返回，即可计算出总分大于等于240分的男生成绩标准偏差，结果如图15-26所示。

DSTDEVP函数用于返回将列表或数据库中满足指定条件的列中数字作为样本总体，计算出的总体标准偏差。其语法如下：

DSTDEVP(database,fi eld,criteria)

下面通过实例详细讲解该函数的使用方法与技巧。根据如图15-1所示的基础数据清单，班主任想要了解：

性别为女生的英语成绩总体标准偏差。
总分大于等于240分的男生成绩总体标准偏差。

具体操作步骤如下。

STEP01：根据上面提出的查询条件设置计算表格和条件区域，结果如图15-27所示。

STEP02：选中E22单元格，在编辑栏中输入公式“=DSTDEVP(A1:G16,E1,A25:A26)”，然后按“Enter”键返回，即可计算出性别为女生的英语成绩总体标准偏差，结果如图15-28所示。

STEP03：选中E23单元格，在编辑栏中输入公式“=DSTDEVP(A1:G16,F1,B25:C26)”，然后按“Enter”键返回，即可计算出总分大于等于240分的男生成绩总体标准偏差，结果如图15-29所示。

图15-26　计算总分>=240分的男生成绩标准偏差

图15-27　计算表格和条件区域

图15-28　计算性别为女生的英语成绩总体标准偏差

图15-29　计算总分>=240分的男生成绩总体标准偏差

Excel 计算符合条件最小值：DMIN函数

DMIN函数用于返回数据清单或数据库中满足指定条件的列中的最小数值。其语法如下：

DMIN(database,fi eld,criteria)

下面通过实例详细讲解该函数的使用方法与技巧。根据如图15-1所示的基础数据清单，班主任想要了解：

英语成绩大于80分的最低成绩。
总分大于255的成绩最低的女生成绩。
平均分大于80的成绩最低的男生成绩。

具体操作步骤如下。

STEP01：根据上面提出的查询条件设置计算表格和条件区域，结果如图15-20所示。

STEP02：选中E22单元格，在编辑栏中输入公式“=DMIN(A1:G16,E1,A26:A27)”，然后按“Enter”键返回，即可计算出英语成绩大于80分的最低成绩，结果如图15-21所示。

图15-20　计算表格和条件区域

图15-21　计算英语成绩大于80分的最低成绩

STEP03：选中E23单元格，在编辑栏中输入公式“=DMIN(A1:G16,F1,B26:C27)”，然后按“Enter”键返回，即可计算出总分大于255的成绩最低的女生成绩，结果如图15-22所示。

STEP04：选中E24单元格，在编辑栏中输入公式“=DMIN(A1:G16,G1,E26:F27)”，然后按“Enter”键返回，即可计算出平均分大于80的成绩最低的男生成绩，结果如图15-23所示。

图15-22　计算总分大于255的成绩最低的女生成绩

图15-23　计算平均分大于80的成绩最低的男生成绩

Excel 计算符合条件最大值：DMAX函数

DMAX函数用于返回数据清单或数据库中满足指定条件的列中的最大数值。其语法如下：

DMAX(database,fi eld,criteria)

下面通过实例详细讲解该函数的使用方法与技巧。根据如图15-1所示的基础数据清单，班主任想要了解：

英语成绩大于90分的最高成绩。
总分大于255的成绩最高的女生成绩。
平均分大于80的成绩最高的男生成绩。

具体操作步骤如下。

STEP01：根据上面提出的查询条件设置计算表格和条件区域，结果如图15-16所示。

STEP02：选中E22单元格，在编辑栏中输入公式“=DMAX(A1:G16,E1,A26:A27)”，然后按“Enter”键返回，即可计算出英语成绩大于90分的最高成绩，结果如图15-17所示。

图15-16　计算表格和条件区域

图15-17　计算英语成绩大于90分的最高成绩

STEP03：选中E23单元格，在编辑栏中输入公式“=DMAX(A1:G16,F1,B26:C27)”，然后按“Enter”键返回，即可计算出总分大于255的成绩最高的女生成绩，结果如图15-18所示。

STEP04：选中E24单元格，在编辑栏中输入公式“=DMAX(A1:G16,G1,E26:F27)”，然后按“Enter”键返回，即可计算出平均分大于80的成绩最高的男生成绩，结果如图15-19所示。

图15-18　计算总分大于255的成绩最高的女生成绩

图15-19　计算平均分大于80的成绩最高的男生成绩

Excel 计算指定条件记录值：DGET函数

DGET函数用于从数据清单或数据库的列中提取符合指定条件的单个值。其语法如下：

DGET(database,fi eld,criteria)

下面通过实例详细讲解该函数的使用方法与技巧。根据如图15-1所示的基础数据清单，班主任想要了解：

1）姓名为“郑小梦”的平均分。

2）总分为283的学生姓名。

3）语文成绩为96的男生姓名。

具体操作步骤如下。

STEP01：根据上面提出的查询条件设置计算表格和条件区域，结果如图15-12所示。

STEP02：选中E22单元格，在编辑栏中输入公式“=DGET(A1:G16,G1,A26:A27)”，然后按“Enter”键返回，即可计算出姓名为“郑小梦”的平均分，结果如图15-13所示。

图15-12　计算表格和条件区域

图15-13　计算姓名为“郑小梦”的平均分

STEP03：选中E23单元格，在编辑栏中输入公式“=DGET(A1:G16,A1,B26:B27)”，然后按“Enter”键返回，即可计算出总分为283的学生姓名，结果如图15-14所示。

STEP04：选中E24单元格，在编辑栏中输入公式“=DGET(A1:G16,A1,C26:D27)”，然后按“Enter”键返回，即可计算出语文成绩为96的男生姓名，结果如图15-15所示。

图15-14　计算总分为283的学生姓名

图15-15　计算语文成绩为96的男生姓名

Excel 计算单元格数量：DCOUNT函数图解

DCOUNT函数用于返回数据清单或数据库中满足指定条件的列中包含数字的单元格个数。其语法如下：

DCOUNT(database,fi eld,criteria)

其中，field参数为可选项，如果省略，DCOUNT函数将返回数据库中满足条件criteria的所有记录数。

下面通过实例详细讲解该函数的使用方法与技巧。根据图15-1所示的基础数据清单，班主任想要了解：

1）语文成绩大于80分的女生个数。

2）数学成绩大于等于80分小于90分的学生个数。

具体操作步骤如下。

STEP01：根据上面提出的查询条件设置计算表格和条件区域，结果如图15-6所示。

STEP02：选中E22单元格，在编辑栏中输入公式“=DCOUNT(A1:G16,C1,A25:B26)”，然后按“Enter”键返回，即可计算出语文成绩大于80分的女生个数，结果如图15-7所示。

图15-6　计算表格和条件区域

图15-7　语文成绩大于80分的女生个数

STEP03：选中E23单元格，在编辑栏中输入公式“=DCOUNT(A1:G16,D1,C25:D26)”，然后按“Enter”键返回，即可计算出数学成绩大于等于80小于90分的学生个数，结果如图15-8所示。

DCOUNTA函数用于返回数据清单或数据库中满足指定条件的列中非空单元格的个数。参数field为可选项，如果省略，则DCOUNTA函数将返回数据库中满足条件的所有记录数。其语法如下：

DCOUNTA(database,fi eld,criteria)

下面通过实例详细讲解该函数的使用方法与技巧。根据如图15-1所示的基础数据清单，班主任想要了解：

1）英语成绩大于80分的男生个数。

2）总分大于等于255分的学生个数。

具体操作步骤如下。

STEP01：根据上面提出的查询条件设置计算表格和条件区域，结果如图15-9所示。

图15-8　数学成绩大于等于80小于90分的学生个数

图15-9　计算表格和条件区域

STEP02：选中E22单元格，在编辑栏中输入公式“=DCOUNTA(A1:G16,E1,A25:B26)”，然后按“Enter”键返回，即可计算出英语成绩大于80分的男生个数，结果如图15-10所示。

STEP03：选中E23单元格，在编辑栏中输入公式“=DCOUNTA(A1:G16,F1,C25:C26)”，然后按“Enter”键返回，即可计算出总分大于等于255分的学生个数，结果如图15-11所示。

图15-10　英语成绩大于80分的男生个数

图15-11　计算总分大于等于255分的学生个数

Excel 计算条目平均值：DAVERAGE函数图解

DAVERAGE函数用于返回列表或数据库中满足指定条件的列中数值的平均值。其语法如下：

DAVERAGE(database,fi eld,criteria)

下面通过实例详细讲解该函数的使用方法与技巧。根据图15-1所示的基础数据清单，班主任想要了解：

所有女生总分的平均分。
英语成绩大于80分的平均分。

具体操作步骤如下所示。

STEP01：根据上面提出的查询条件设置计算表格和条件区域，设置结果如图15-3所示。

图15-3　计算表格和条件区域

STEP02：选中E22单元格，在编辑栏中输入公式“=DAVERAGE(A1:G16,F1,A25:A26)”，然后按“Enter”键返回，即可计算出所有女生总分的平均分，结果如图15-4所示。

STEP03：选中E23单元格，在编辑栏中输入公式“=DAVERAGE(A1:G16,E1,B25:B26)”，然后按“Enter”键返回，即可计算出英语成绩大于80分的平均分，结果如图15-5所示。

图15-4　计算所有女生总分的平均分

图15-5　计算英语成绩大于80分的平均分