数据库 – Excel22

Excel 数据库函数的使用实战

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

打开工资表文件。可以看到，该数据清单的数据字段包括了员工姓名、性别、年龄、职务以及员工的工资额。基础数据如图14-27所示。

图14-27　基础数据清单

根据上面的基础数据清单，公司需要了解的信息有：

·销售员中工资额的最高值。

·工资额在1200到1500间的员工个数。

·女员工的平均年龄。

·采购员的工资总和。

·经理苏小北的工资额。

·采购员中工资额的最小值。

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

步骤1：设置计算表格。根据公司的要了解的信息，设置的计算表格如图14-28所示。

图14-28　计算表格

步骤2：统计销售员中工资额的最高值。在G11：G12单元格区域设置数据库查询的数据字段“职务”和条件值“销售员”，然后在J2单元格中输入公式“=DMAX（A1：E15，E1，G11：G12）”，按Enter键，返回销售员中工资额的最高值，计算结果如图14-29所示。

步骤3：统计工资额在1200到1500间的员工个数。在H11：I12单元格区域设置数据库查询的数据字段“工资额”和条件值“>1200”和“<1500”，然后在J3单元格中输入公式“=DCOUNT（A1：E15，E1，H11：I12）”，按Enter键，返回工资额在1200~1500元之间的员工个数，计算结果如图14-30所示。

步骤4：统计女员工的平均年龄。在J11：J12单元格区域，设置数据库查询的数据字段“性别”和条件值“女”，然后在J4单元格中输入公式“=DAVERAGE（A1：E15，C1，J11：J12）”，按Enter键，返回女员工的平均年龄，计算结果如图14-31所示。

图14-29　统计销售员中工资额的最高值

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

图14-31　统计女员工的平均年龄

步骤5：统计采购员的工资总和。在G13：G14单元格区域设置数据库查询的数据字段“职务”和条件值“采购员”，然后在J5单元格中输入公式“=DSUM（A1：E15，E1，G13：G14）”，按Enter键，返回采购员的工资总和，计算结果如图14-32所示。

步骤6：统计经理苏小北的工资额。在H13：I14单元格区域设置数据库查询的数据字段“职务”和“姓名”，设置条件值“经理”和“苏小北”，然后在J6单元格中输入公式“=DGET（A1：E15，E1，H13：I14）”，按Enter键，返回经理苏小北的工资额，计算结果如图14-33所示。

图14-32　统计采购员的工资总和

图14-33　统计经理苏小北的工资额

步骤7：统计采购员中工资额的最小值。在J13：J14单元格区域设置数据库查询的数据字段“职务”和条件值“采购员”，然后在J7单元格中输入公式“=DMIN（A1：E15，E1，J13：J14）”，按Enter键，返回采购员中工资额的最小值，计算结果如图14-34所示。

图14-34　统计采购员中工资额的最小值

Excel 数据库函数的特点说明

数据库函数的共同特点

数据库函数具有以下3个共同特点：

每个函数均有3个参数：database、field和criteria，这些参数指向函数所使用的工作表区域。
除了GETPIVOTDATA函数之外，其余十二个函数都以字母D开头。
如果将字母D去掉，可以发现其实大多数数据库函数已经在Excel的其他类型函数中出现过了。例如，将DMAX函数中的D去掉的话，就是求最大值的函数MAX。

数据库函数的参数介绍

由于每个数据库函数均有3个相同参数，因此本小节先介绍这3个参数的含义，在后面再以实例的形式介绍数据库函数的具体功能。数据库函数的语法形式为：

函数名称(database,field,criteria)

对参数的说明如下：

· 参数database为构成数据清单或数据库的单元格区域。数据库是包含一组相关数据的数据清单，其中包含相关信息的行称为数据记录，而包含数据的列称为数据字段。其中，数据清单的第一行包含着每一列的标志项。

· 参数field为指定函数所使用的数据列。数据清单中的数据列必须在第一行具有标志项。参数field可以是文本，即两端带引号的标志项，如“姓名”或“性别”；参数field也可以是代表数据清单中数据列位置的数字：1表示第一列，2表示第二列，等等。

· 参数criteria为一组包含给定条件的单元格区域。

对数据库函数的几点说明

可以为参数criteria指定任意区域，但是至少要包含一个列标志和列标志下方用于设定条件的单元格。
虽然条件区域可以在工作表的任意位置，但不要将条件区域置于数据清单的下方。
确定条件区域没有与数据清单相重叠。
如果要对数据库的整个列进行操作，需要在条件区域中的列标志下方输入一个空白行。

对条件区域的几点说明

每一个数据库函数都有条件区域，条件是指所指定的限制查询或筛选的结果集中包含哪些记录的条件；清单是指包含相关数据的一系列工作表行。建立条件区域要满足下面的条件。

在可用作条件区域的数据清单上插入至少三个空白行。
条件区域必须具有列标志。
请确保在条件值与数据清单之间至少留了一个空白行。

Excel利用已经含有数据的表格，制作新的资料

另一方面，如果是以下载的数据或累积的数据为材料进行加工、整理，并根据要求制作资料，就不只是输入函数这么简单了，我们必须要思考“利用何种材料，制作出何种资料”。

用 Excel 制作数据分析资料的基础为以下3点。

➊ 从数据库形式的表格，转换成由纵轴和横轴组成的倒 L 字形矩阵表

➋ 再次设定项目

“将按日计算的数字改为按月计算”

“将按都道府县计算的数字改为按地域计算”

像这样，大多数情况下会根据不同目的，将细分单位的项目转换为较大单位的项目。准备多重变换模式后，可以通过 VLOOKUP 函数处理。

还可以添加与前年的对比、预测&实际对比、结构比率等各种分析现状时需要的项目。至于应该添加怎样的项目，我会在第8章中详细说明。

➌ 需要定期制作、更新的资料，应预先在表格内输入函数

需要定期制作、更新的资料，不仅要花费大量时间进行复制粘贴，还容易发生粘贴错误数据等失误。这时，我们需要事先在表格中设计这样的结构：预先确定表格的格式，然后在表格中输入函数，使其能够自动统计数据并填充表格。如此一来，我们只要把材料数据粘贴到固定位置就能立刻完成表格更新。具体来说，这需要用到 SUMIF 函数和 COUNTIF 函数。

关于具体的操作步骤我会在第8章中详细解说，请大家先记住一个原则：关于使用数据透视表或自动填充功能来制作表格这项操作，还有很大余地能够对其进行改善。多数情况下，改为用函数来处理，就能够大幅度提高操作效率，节省工作时间。

接下来，我将以上述注意事项为基础，给大家介绍一些能够提高制作表格效率的功能与技巧。

在单元格中输入数据，制作表格（制作数据库）

例如，在 Excel 中输入名片信息，或者销售额和交易信息等操作就是这种形式。

这时，工作表的形式必须是“数据库形式”（也被称作“清单形式”）。例如，含有名片信息的数据库就是下表的形式。

输有名片信息的数据库

在第1行中输入项目名称，第2行之后逐行输入数据。

第1行设定的项目要尽量细致

第1行设定的项目要尽可能的详细，这样在进行后续工作时才会更加方便。例如，在地址栏中分开输入都道府县和下级行政区，那么之后在计算在各都道府县的客户的分布状况等工作就会变得简单。因为，我们可以使用&或 CONCATENATE 函数将不同单元格的数据连接到一起，但拆分单元格中的内容会花费大量的时间。有时甚至无法使用函数拆分单元格内容，只能手动完成。

每列数据中设定输入有效性

接着，为了能够提高将信息输入数据库表格的效率，最重要的就是预先在每列数据（每列内容叫作“Field”）中按照各个项目，设定“输入规则”（之后会具体解释）。

另外，像下面这样灵活运用各种不同类型的函数，就能提高工作效率。

在年龄栏中输入 DATEDIF 函数后，只要输入出生日期，就会自动显示年龄

这样的表格被广泛应用于客户、销售额的管理中，而在日常工作中也经常利用这种表格制作各种数据分析的资料。