04x01 数据库函数统一的语法与参数说明
1、统一的语法
数据库函数有统一的语法格式如下:
数据库函数名称(Database,Field,Criteria)
2、统一的参数
参数名称 | 说明 |
---|---|
Database 构成列表或数据库的单元格区域 | 数据库是包含一组相关数据的列表,其中包含相关信息的行为记录,而包含数据的列为字段。 列表的第一行包含每一列的标签。 |
Field 指定函数所使用的列 | 输入两端带双引号的列标签,如 “使用年数” 或 “产量”;或是代表列表中列位置的数字(不带引号):1 表示第一列,2 表示第二列,依此类推。 |
Criteria 为包含指定条件的单元格区域 | 可以为参数指定 criteria 任意区域,只要此区域包含至少一个列标签,并且列标签下至少有一个在其中为列指定条件的单元格。 |
注意:
- 虽然条件区域可以位于工作表的任意位置,但不要将条件区域置于列表的下方。 如果向列表中添加更多信息,新的信息将会添加在列表下方的第一行上。 如果列表下方的行不是空的,Excel 将无法添加新的信息。
- 确定条件区域没有与列表相重叠。
- 若要对数据库中的一个完整列执行操作,请在条件区域中的列标签下方加入一个空行。
04x02 数据库函数用到的Criteria条件详细说明
Criteria条件使用总结
- 一行就是一个条件集。
- 同一行之间是"AND且"的关系。
- 不同行之间是"OR或"的关系。
- 留空的单元格表示"ALL全部"。
在单元格中键入一个等号表示要输入公式。 要显示包括等号的文本,将文本和等号用双引号括起,如下所示:
在单元格中键入的内容 | Excel 计算和显示的内容 |
---|---|
="=彭德威" | =彭德威 |
="=3000" | =3000 |
Excel 在筛选文本数据时不区分大小写字符。 但是可以使用EXACT()公式来执行区分大小写的搜索。 有关示例,请参阅本文后面的使用区分大小写的搜索筛选文本
以下各节提供了复杂条件的示例。
Criteria条件使用实例
1. 一列中有多个条件
布尔逻辑:(销售人员 = “彭德威” OR 销售人员 = “郑建杰”)
要查找满足“一列中有多个条件”的行,请直接在条件区域的单独行中依次键入条件。
在下面的数据区域 (A6:C10) 中,条件区域 (B1:B3) 显示“销售人员”列 (A8:C10) 中包含“彭德威”或“郑建杰”的行。
A | B | C | |
---|---|---|---|
1 | 类型 | 销售人员 | 销售额 |
2 | =彭德威 | ||
3 | =郑建杰 | ||
4 | |||
5 | |||
6 | 类型 | 销售人员 | 销售额 |
7 | 饮料 | 蔡耀明 | ¥51,220 |
8 | 肉类 | 彭德威 | ¥4,500 |
9 | 农产品 | 郑建杰 | ¥63,280 |
10 | 农产品 | 彭德威 | ¥65,440 |
2. 多列中有多个条件,其中所有条件都必须为真
布尔逻辑:(类型 = “农产品” AND 销售额 > 1000)
要查找满足“多列中有多个条件”的行,请在条件区域的同一行中键入所有条件。
在下面的数据区域 (A6:C10) 中,条件区域 (A1:C2) 显示“类型”列中包含“农产品”并且“销售额”列 (A9:C10) 中值大于 ¥1,000 的所有行。
A | B | C | |
---|---|---|---|
1 | 类型 | 销售人员 | 销售额 |
2 | =农产品 | >1000 | |
3 | |||
4 | |||
5 | |||
6 | 类型 | 销售人员 | 销售额 |
7 | 饮料 | 蔡耀明 | ¥51,220 |
8 | 肉类 | 彭德威 | ¥4,500 |
9 | 农产品 | 郑建杰 | ¥63,280 |
10 | 农产品 | 彭德威 | ¥65,440 |
3. 多列中有多个条件,其中任意条件为真即可
布尔逻辑:(类型 = “农产品” OR 销售人员 = “彭德威”)
要查找满足“多列中有多个条件,其中所有条件都必须为真”的行,请在条件区域的不同行中键入条件。
在下面的数据区域 (A6:C10) 中,条件区域 (A1:B3) 显示“类型”列中包含“农产品”或“销售人员”列 (A8:C10) 中包含“彭德威”的所有行。
A | B | C | |
---|---|---|---|
1 | 类型 | 销售人员 | 销售额 |
2 | =农产品 | ||
3 | =彭德威 | ||
4 | |||
5 | |||
6 | 类型 | 销售人员 | 销售额 |
7 | 饮料 | 蔡耀明 | ¥51,220 |
8 | 肉类 | 彭德威 | ¥4,500 |
9 | 农产品 | 郑建杰 | ¥63,280 |
10 | 农产品 | 彭德威 | ¥65,440 |
4. 多个条件集,其中每个集包括用于多个列的条件
布尔逻辑:( (销售人员 = “彭德威” AND 销售额 >3000) OR (销售人员 = “郑建杰” AND 销售额 > 1500) )
要查找满足“多个条件集,其中每个集包括用于多个列的条件”的行,请在单独的行中键入每个条件集。
在下面的数据区域 (A6:C10) 中,条件区域 (B1:C3) 显示“销售人员”列中包含“彭德威”并且“销售额”列中值大于 ¥3,000 的行,或者显示“销售人员”列中包含“郑建杰”并且“销售额”列 (A9:C10) 中值大于 ¥1,500 的行。
A | B | C | |
---|---|---|---|
1 | 类型 | 销售人员 | 销售额 |
2 | =彭德威 | >3000 | |
3 | =郑建杰 | >1500 | |
4 | |||
5 | |||
6 | 类型 | 销售人员 | 销售额 |
7 | 饮料 | 蔡耀明 | ¥51,220 |
8 | 肉类 | 彭德威 | ¥4,500 |
9 | 农产品 | 郑建杰 | ¥63,280 |
10 | 农产品 | 彭德威 | ¥65,440 |
5. 多个条件集,其中每个集包括用于一个列的条件
布尔逻辑:( (销售额 > 6000 AND 销售额 < 6500 ) OR (销售额 < 500) )
要查找满足“多个条件集,其中每个集包括用于一个列的条件”的行,请在多个列中包括同一个列标题。
在下面的数据区域 (A6:C10) 中,条件区域 (C1:D3) 显示“销售额”列 (A8:C10) 中值在 6,000 和 6,500 之间以及值小于 500 的行。
A | B | C | D | |
---|---|---|---|---|
1 | 类型 | 销售人员 | 销售额 | 销售额 |
2 | >6000 | <6500 | ||
3 | <500 | |||
4 | ||||
5 | ||||
6 | 类型 | 销售人员 | 销售额 | |
7 | 饮料 | 蔡耀明 | ¥51,220 | |
8 | 肉类 | 彭德威 | ¥4,500 | |
9 | 农产品 | 郑建杰 | ¥63,280 | |
10 | 农产品 | 彭德威 | ¥6544 |
6. 查找共享某些字符而非其他字符的文本值的条件
要查找共享某些字符而非其他字符的文本值,请执行下面一项或多项操作:
- 键入一个或多个不带等号 (=) 的字符,以查找列中文本值以这些字符开头的行。 例如,如果键入文本“ Dav”作为条件,则 Excel将找到“Davolio”、“David”和“Davis”。
- 使用通配符。可以使用下表的通配符作为比较条件。
用途 | 若要查找 |
---|---|
? (问号) | 任意单个字符 例如,sm?th 可找到“smith”和“smyth” |
*(星号) | 任意数量的字符 例如,*east 可找到“Northeast”和“Southeast” |
~(波形符转意)后跟 ?、* 或 ~ | 问号、星号或波形符 例如,fy91~? 可找到“fy91?” |
在以下数据区域 (A6:C10) 中,条件区域 (A1:B3) 显示“Type”列中以“Me”开头的行或“Salesperson”列 (A7:C9) 中第二个字符为“u”的行。
A | B | C | |
---|---|---|---|
1 | Type | Salesperson | Sales |
2 | Me | ||
3 | =?u* | ||
4 | |||
5 | |||
6 | Type | Salesperson | Sales |
7 | Beverages | Suyama | 5122 |
8 | Meat | Davolio | 450 |
9 | produce | Buchanan | 6328 |
10 | Produce | Davolio | 6544 |
7. 将公式结果用作条件
可以将公式的计算结果作为条件使用。 记住下列要点:
- 公式必须计算为 TRUE 或 FALSE。
- 因为您正在使用公式,请像您平常那样输入公式,而不要以下列方式键入表达式:=”=条目"
- 不要将列标签用作条件标签;请将条件标签保留为空,或者使用区域中并非列标签的标签(在以下示例中,是“计算的平均值”和“精确匹配”)。
如果在公式中使用列标签,而不是相对单元格引用或区域名称,则 Excel 会显示错误值(如 #NAME?) 或含有条件单元格中的 #VALUE!。 您可以忽略此错误,因为它不影响区域的筛选。 - 用作条件的公式必须使用相对引用来引用第一行中相应的单元格(在下面的示例中,是 C7 和 A7)。
- 公式中的所有其他引用必须是绝对单元格引用。
例7-1. 筛选大于数据区域中所有值的平均值的值
在以下数据区域 (A6:D10) 中,条件区域 (D1:D2) 显示“销售额”列 (C7:C10) 中值大于所有“销售额”值的平均值的行。 在公式中,“C7”引用数据区域 (7) 的第一行的筛选列 ( C ),而不是使用列标签"销售额"。
A | B | C | D | |
---|---|---|---|---|
1 | 类型 | 销售人员 | 销售额 | 计算的平均值 |
2 | =C7>AVERAGE($C 7 : 7: 7:C$10) | |||
3 | ||||
4 | ||||
5 | ||||
6 | 类型 | 销售人员 | 销售额 | |
7 | 饮料 | 蔡耀明 | ¥51,220 | |
8 | 肉类 | 彭德威 | ¥4,500 | |
9 | 农产品 | 郑建杰 | ¥63,280 | |
10 | 农产品 | 彭德威 | ¥65,440 |
例7-2. 使用区分大小写的搜索筛选文本
在数据区域 (A6:D10) 中,通过使用 EXACT 函数执行区分大小写的搜索,条件区域 (D1:D2) 显示“Type”列 (A10:C10) 中包含“Produce”的行。 在公式中,“A7”引用数据区域 (7) 中首行的筛选列 (A),而不是使用列标签Type。
A | B | C | D | |
---|---|---|---|---|
1 | Type | Salesperson | Sales | Exact Match |
2 | =EXACT(A7, “Produce”) | |||
3 | ||||
4 | ||||
5 | ||||
6 | Type | Salesperson | Sales | |
7 | Beverages | Suyama | 5122 | |
8 | Meat | Davolio | 450 | |
9 | produce | Buchanan | 6328 | |
10 | Produce | Davolio | 6544 |
04x03 数据库函数及其实例
01 DAVERAGE 函数
对列表或数据库中满足指定条件的记录字段(列)中的数值求平均值。
语法
DAVERAGE(database, field, criteria)
例子:
树种 | 高度 | 年数 | 产量 | 利润 | 高度 |
---|---|---|---|---|---|
=苹果树 | >10 | <16 | |||
=梨树 | |||||
树种 | 高度 | 年数 | 产量 | 利润 | |
苹果树 | 18 | 20 | 14 | 105 | |
梨树 | 12 | 12 | 10 | 96 | |
樱桃树 | 13 | 14 | 9 | 105 | |
苹果树 | 14 | 15 | 10 | 75 | |
梨树 | 9 | 8 | 8 | 76.8 | |
苹果树 | 8 | 9 | 6 | 45 | |
公式 | 说明 | 结果 | |||
=DAVERAGE(A4:E10,“产量”,A1:B2) | 此函数计算高度在 10 英尺以上的苹果树的平均产量。 | 12 | |||
=DAVERAGE(A4:E10, 3, A4:E10) | 此函数计算数据库中所有树种的平均使用年数。 | 13 |
02 DCOUNT 函数
返回列表或数据库中满足指定条件的记录字段(列)中包含数字的单元格的个数。
字段参数为可选项。 如果省略字段,DCOUNT 计算数据库中符合条件的所有记录数。
语法
DCOUNT(database, field, criteria)
例子:
树种 | 高度 | 年数 | 产量 | 利润 | 高度 |
---|---|---|---|---|---|
=苹果树 | >10 | <16 | |||
=梨树 | |||||
树种 | 高度 | 年数 | 产量 | 利润 | |
苹果树 | 18 | 20 | 14 | ¥1,050 | |
梨树 | 12 | 12 | 10 | ¥960 | |
樱桃树 | 13 | 14 | 9 | ¥1,050 | |
苹果树 | 14 | 无 | 10 | ¥750 | |
梨树 | 9 | 8 | 8 | ¥770 | |
苹果树 | 12 | 11 | 6 | ¥450 | |
公式 | 说明 | 结果 | |||
=DCOUNT(A5:E11, “使用年数”,A1:F2) | 查找高度在 10 到 16 米之间的苹果树,并且计算这些记录中“使用年数”字段包含数字的单元格数目。 | 1 |
03 DCOUNTA 函数
返回列表或数据库中满足指定条件的记录字段(列)中的非空单元格的个数。
字段参数为可选项。 如果省略字段,DCOUNTA 计算数据库中符合条件的所有记录数。
语法
DCOUNTA(database, field, criteria)
例子:
树种 | 高度 | 年数 | 产量 | 利润 | 高度 |
---|---|---|---|---|---|
=苹果树 | >10 | <16 | |||
=梨树 | |||||
树种 | 高度 | 使用年数 | 产量 | 利润 | |
苹果树 | 18 | 20 | 14 | 105 | |
梨树 | 12 | 12 | 10 | 96 | |
樱桃树 | 13 | 14 | 9 | 105 | |
苹果树 | 14 | 15 | 10 | 75 | |
梨树 | 9 | 8 | 8 | 76.8 | |
苹果树 | 8 | 9 | 6 | 45 | |
公式 | 说明 | 结果 | |||
=DCOUNTA(A4:E10, “利润”, A1:F2) | 对 A 列中包含“苹果树”且高度大于 10 英尺且小于 16 英尺的的非空”利润“行进行计数。 仅第 8 行满足上述三个条件。 | 1 |
04 DGET 函数
从列表或数据库的列中提取符合指定条件的单个值。
语法
DGET(database, field, criteria)
例子:
树种 | 高度 | 年数 | 产量 | 利润 | 高度 |
---|---|---|---|---|---|
=苹果树 | >10 | <16 | |||
梨树 | >12 | ||||
树种 | 高度 | 年数 | 产量 | 利润 | |
苹果树 | 18 | 20 | 14 | ¥1,050 | |
梨树 | 12 | 12 | 10 | ¥960 | |
樱桃树 | 13 | 14 | 9 | ¥1,050 | |
苹果树 | 14 | 15 | 10 | ¥750 | |
梨树 | 9 | 8 | 8 | ¥770 | |
苹果树 | 8 | 9 | 6 | ¥450 | |
公式 | 说明 | 结果 | |||
=DGET(A5:E11, “产量”, A1:A3) | 返回 #NUM! 错误值,因为有多个记录满足条件(任何 apple 或梨树)。 | #NUM! | |||
=DGET(A5:E11, “产量”, A1:F3) | 返回 10(第 9 行中的苹果树产量),因为这是满足 A1:F3 中条件的唯一记录。 | 10 |
05 DMAX 函数
返回列表或数据库中满足指定条件的记录字段(列)中的最大数字。
语法
DMAX(database, field, criteria)
例子:
树种 | 高度 | 年数 | 产量 | 利润 | 高度 |
---|---|---|---|---|---|
=苹果树 | >10 | <16 | |||
=梨树 | |||||
树种 | 高度 | 使用年数 | 产量 | 利润 | |
苹果树 | 18 | 20 | 14 | ¥1,050 | |
梨树 | 12 | 12 | 10 | ¥960 | |
樱桃树 | 13 | 14 | 9 | ¥1,050 | |
苹果树 | 14 | 15 | 10 | ¥750 | |
梨树 | 9 | 8 | 8 | ¥770 | |
苹果树 | 8 | 9 | 6 | ¥450 | |
公式 | 说明 | 结果 | |||
=DMAX(A5:E11,“利润”,A1:F3) | 此函数查找任何高度在 10 到 16 英尺之间的苹果树或任何梨树的最大利润。 第 7 行中的梨树满足上述条件。 | ¥960 |
06 DMIN 函数
返回列表或数据库中满足指定条件的记录字段(列)中的最小数字。
语法
DMIN(Database, field, criteria)
例子:
树种 | 高度 | 年数 | 产量 | 利润 | 高度 |
---|---|---|---|---|---|
=苹果树 | >10 | <16 | |||
=梨树 | |||||
树种 | 高度 | 年数 | 产量 | 利润 | |
苹果树 | 18 | 20 | 14 | ¥1,050 | |
梨树 | 12 | 12 | 10 | ¥960 | |
樱桃树 | 13 | 14 | 9 | ¥1,050 | |
苹果树 | 14 | 15 | 10 | ¥750 | |
梨树 | 9 | 8 | 8 | ¥770 | |
苹果树 | 8 | 9 | 6 | ¥450 | |
公式 | 说明 | 结果 | |||
=DMIN(A5:E11, “利润”, A1:F3) | 此函数查找任何高度在 10 到 16 英尺之间的苹果树或任何梨树的最小利润。 第 9 行中的苹果树满足上述条件。 | ¥750 |
07 DPRODUCT 函数
返回列表或数据库中满足指定条件的记录字段(列)中的数值的乘积。
语法
DPRODUCT(database, field, criteria)
例子:
Tree | Height | Age | Yield | Profit | Height |
---|---|---|---|---|---|
=Apple | >10 | <16 | |||
=Pear | |||||
Tree | Height | Age | Yield | Profit | |
Apple | 18 | 20 | 14 | 105 | |
Pear | 12 | 12 | 10 | 96 | |
Cherry | 13 | 14 | 9 | 105 | |
Apple | 14 | 15 | 10 | 75 | |
Pear | 9 | 8 | 8 | 77 | |
Apple | 8 | 9 | 6 | 45 | |
Formula | Description | Result | |||
=DPRODUCT(A4:E10, “Yield”, A1:F3) | The product of the yields from apple trees with a height between 10 and 16 feet and any pear trees. | 800 |
08 DSTDEV 函数
返回利用列表或数据库中满足指定条件的记录字段(列)中的数字作为一个样本估算出的总体标准偏差。
语法
DSTDEV(database, field, criteria)
例子:
Tree | Height | Age | Yield | Profit | Height |
---|---|---|---|---|---|
=Apple | >10 | <16 | |||
=Pear | |||||
Tree | Height | Age | Yield | Profit | |
Apple | 18 | 20 | 14 | 105 | |
Pear | 12 | 12 | 10 | 96 | |
Cherry | 13 | 14 | 9 | 105 | |
Apple | 14 | 15 | 10 | 75 | |
Pear | 9 | 8 | 8 | 77 | |
Apple | 8 | 9 | 6 | 45 | |
Formula | Description | Result | |||
=DSTDEV(A4:E10,“Yield”,A1:A3) | The estimated standard deviation in the yield of apple and pear trees if the data in A5:E11 is only a sample of the total orchard population. | 2.96648 |
09 DSTDEVP 函数
返回利用列表或数据库中满足指定条件的记录字段(列)中的数字作为样本总体计算出的总体标准偏差。
语法
DSTDEVP(database, field, criteria)
例子:
Tree | Height | Age | Yield | Profit | Height |
---|---|---|---|---|---|
=Apple | >10 | <16 | |||
=Pear | |||||
Tree | Height | Age | Yield | Profit | |
Apple | 18 | 20 | 14 | 105 | |
Pear | 12 | 12 | 10 | 96 | |
Cherry | 13 | 14 | 9 | 105 | |
Apple | 14 | 15 | 10 | 75 | |
Pear | 9 | 8 | 8 | 77 | |
Apple | 8 | 9 | 6 | 45 | |
Formula | Description | Result | |||
=DSTDEVP(A4:E10,“Yield”,A1:A3) | The true standard deviation in the yield of apple and pear trees if the data in the database is the entire population. | 2.6532998 |
10 DSUM 函数
返回列表或数据库中满足指定条件的记录字段(列)中的数字之和。
语法
DSUM(database, field, criteria)
例子:
Tree | Height | Age | Yield | Profit | Height |
---|---|---|---|---|---|
=Apple | >10 | <16 | |||
=Pear | |||||
Tree | Height | Age | Yield | Profit | |
Apple | 18 | 20 | 14 | 105 | |
Pear | 12 | 12 | 10 | 96 | |
Cherry | 13 | 14 | 9 | 105 | |
Apple | 14 | 15 | 10 | 75 | |
Pear | 9 | 8 | 8 | 77 | |
Apple | 8 | 9 | 6 | 45 | |
Formula | Description | Result | |||
=DSUM(A4:E10,“Profit”,A1:A2) | The total profit from apple trees (rows 5, 8, and 10). | 225 | |||
=DSUM(A4:E10,“Profit”, A1:F3) | The total profit from apple trees with a height between 10 and 16 feet, and all pear trees (rows 6, 8, and 9). | 248 |
11 DVAR 函数
返回利用列表或数据库中满足指定条件的记录字段(列)中的数字作为一个样本估算出的总体方差。
语法
DVAR(database, field, criteria)
例子:
Tree | Height | Age | Yield | Profit | Height |
---|---|---|---|---|---|
=Apple | >10 | <16 | |||
=Pear | |||||
Tree | Height | Age | Yield | Profit | |
Apple | 18 | 20 | 14 | 105 | |
Pear | 12 | 12 | 10 | 96 | |
Cherry | 13 | 14 | 9 | 105 | |
Apple | 14 | 15 | 10 | 75 | |
Pear | 9 | 8 | 8 | 77 | |
Apple | 8 | 9 | 6 | 45 | |
Formula | Description | Result | |||
=DVAR(A4:E10, “Yield”, A1:A3) | The estimated variance in the yield of apple and pear trees if the data in the database is only a sample of the total orchard population. | 8.8 |
12 DVARP 函数
通过使用列表或数据库中满足指定条件的记录字段(列)中的数字作为样本总体计算总体方差。
语法
DVARP(database, field, criteria)
例子:
Tree | Height | Age | Yield | Profit | Height |
---|---|---|---|---|---|
=Apple | >10 | <16 | |||
=Pear | |||||
Tree | Height | Age | Yield | Profit | |
Apple | 18 | 20 | 14 | 105 | |
Pear | 12 | 12 | 10 | 96 | |
Cherry | 13 | 14 | 9 | 105 | |
Apple | 14 | 15 | 10 | 75 | |
Pear | 9 | 8 | 8 | 77 | |
Apple | 8 | 9 | 6 | 45 | |
Formula | Description | Result | |||
=DVARP(A4:E10, “Yield”, A1:A3) | The true variance in the yield of apple and pear trees if the data in the database is the entire orchard population. | 7.04 |