使用交叉表查询让汇总数据更容易阅读

重要:  本文是由机器翻译的,请参阅免责声明。请在 此处 中查找本文的英文版本以便参考。

当您想要重组汇总数据,以使其更易于阅读和理解,请考虑使用交叉表查询。

交叉表查询计算总和、 平均值或其他聚合函数,然后再按两个值对结果进行分组 — 一个侧面数据表,另一个的顶部。

本文内容

概述

创建交叉表查询

对标题使用范围或间隔

提示输入用于限制行标题的参数

将空值替换为零

交叉表查询提示

概述

交叉表查询是一种选择查询。交叉表查询运行时,具有不同的结构,从其他类型的数据表的数据表中显示的结果。

交叉表查询的结构可以更加轻松地阅读简单的选择比显示相同的数据,如下图所示的查询。

显示相同数据的选择查询和交叉表查询

1. 此选择查询按员工和类别在垂直方向对汇总数据进行分组。

2.交叉表查询可以显示相同的数据,但水平和垂直对数据进行分组,以便在数据表可以更紧凑、 易于阅读。

创建交叉表查询

在创建交叉表查询时,您可以指定哪些字段包含行标题,哪些字段包含列标题,以及哪些字段包含要汇总的值。每次指定列标题和要汇总的值时,您只能使用一个字段。当您指定行标题时,最多可使用三个字段。

您可以使用表达式生成行标题、列标题或值以进行汇总。

交叉表查询的关系图

1. 该侧的一列、两列或三列包含行标题。用作行标题的字段名称显示在这些列的首行。

2. 行标题在此处显示。由于显示所有行标题组合,因此在使用多个行标题字段时,交叉表数据表中的行数会迅速增加。

3. 位于此侧的列包含列标题和汇总值。请注意,列标题字段的名称不在数据表中显示。

4. 此处显示汇总值。

创建交叉表查询的方法

使用交叉表查询向导   交叉表查询向导通常是创建交叉表查询的最快和最简单方法。它大部分工作,但是也存在向导中不提供的几个选项。

向导具有以下优点 ︰

  • 非常简单易用。   若要使用它,您启动向导,然后回答一系列的指导性问题。

  • 它可以自动将日期分组为时间间隔。   如果您使用的字段包含日期/时间数据向导中的列标题也有助于您将日期分组为时间间隔,如月份或季度。

    提示: 如果您想要使用来自包含日期/时间字段的值,为列标题,但想要将日期分组为时间间隔向导中不提供,如财政年度或 biennium,不要使用向导来创建您的查询。相反,在设计视图中创建交叉表查询,并使用表达式创建间隔。

  • 用作起始点。   您可以使用向导创建所需的基本交叉表查询,并且然后通过使用设计视图微调查询的设计。

但是,通过使用向导中,您无法 ︰

  • 多个表或查询用作记录源。

  • 使用表达式创建字段。

  • 添加参数提示。

  • 指定要用作列标题的固定值列表。

    在向导的最后一步中,您可以选择修改查询设计视图中。这使您可以添加向导不支持,如更多记录源的查询设计元素。

在设计视图中工作    设计视图可让您更好地控制您的查询设计。它支持在向导中不可用的功能。

请考虑使用设计视图创建交叉表查询,如果需要 ︰

  • 具有更好地控制该过程。该向导可以为您的一些决策。

  • 作为记录源中使用多个表或查询。

  • 向查询添加参数提示。

  • 使用表达式作为查询中的字段。

  • 指定要用作列标题的固定值列表。

  • 练习使用设计网格。

编写 SQL 视图中的查询   如果您愿意,您可以在 SQL 视图中编写交叉表查询。但是,不能使用 SQL 视图中指定参数数据类型。如果您想要在交叉表查询中使用参数,您必须通过修改您的查询设计视图中指定参数数据类型。

提示: 请记住,您不是局限于用于创建交叉表查询中使用仅一种方法。您可以使用向导创建的查询,然后使用设计视图修改查询设计。

返回页首

创建交叉表查询

通过使用交叉表查询向导创建交叉表查询

在设计视图中创建交叉表查询

在 SQL 视图中创建交叉表查询

通过使用交叉表查询向导创建交叉表查询

使用交叉表查询向导要求您使用一个表或查询用作记录源交叉表查询。如果一个表中没有您想要包括在交叉表查询中的所有数据,首先创建选择查询返回所需的数据。有关创建选择查询的详细信息,请参阅另请参阅部分。

  1. 创建选项卡中的其他组中,单击查询向导

  2. 在“新建查询”对话框中,单击“交叉表查询向导”,然后单击“确定”。

    将启动交叉表查询向导。

  3. 在向导的第一页,选择要用于创建交叉表查询的表或查询。

  4. 在下一页上,选择包含要用作行标题的值的字段。

    最多可选择三个字段用作行标题源,但使用的行标题越少,交叉表查询数据表就越容易阅读。

    如果您选择多个字段来提供行标题,则您选择字段的顺序确定了对结果进行排序的默认顺序。

  5. 在下一页上,选择包含要用作列标题的值的字段。

    通常,您应选择包含少量值的字段,以帮助保持您的结果易于阅读。例如,使用只包含少量可能值(如性别)的字段可能优于使用包含许多不同值(如年龄)的字段。

    如果选择用于列标题的字段具有“日期/时间”数据类型,则向导会增加一个步骤,使您能够指定将日期组合为间隔(如月份或季度)的方式。

  6. 如果您为列标题选择“日期/时间”字段,向导的下一页将要求您指定用于对日期进行分组的时间间隔。您可以指定季度日期日期/时间。如果您没有为列标题选择“日期/时间”字段,则该向导将跳过此页。

  7. 在下一页,选择用于计算汇总值的字段和函数。您选择的字段的数据类型将决定可用的函数。

  8. 在同一页上,选择或清除“是,包括各行小计”复选框以包括或排除行小计。

    如果包括各行小计,交叉表查询拥有一个与字段值一样,使用相同字段和函数的附加行标题。包括一个插入到附加列的行总和,该附加列汇总了剩余的列。例如,如果交叉表查询通过位置和性别(性别列标题)计算平均年龄,附加列将按位置计算所有性别的平均年龄。

    您可以通过在设计视图中编辑交叉表查询来更改用于生成行总和的函数。

  9. 在向导的下一页上,键入查询的名称,然后指定是查看结果还是修改查询设计。

返回页首

在设计视图中创建交叉表查询

通过使用设计视图来创建交叉表查询,您可以使用任意多个记录源 (表和查询) 所需。但是,您可以将保留设计简单先创建返回的所有数据所需的选择查询,然后将该查询用作仅记录源交叉表查询。有关创建选择查询的详细信息,请参阅另请参阅部分。

当在设计视图中生成交叉表查询时,使用设计网格中的“总计”和“交叉表”行指定成为列标题的字段值、成为行标题的字段值,以及用于计算和、平均值、计数或其他计算的字段值。

显示在设计视图中的部分交叉表查询

1. 这些行中的设置决定字段是行标题、列标题还是汇总值。

2. 此设置将字段值显示为行标题。

3. 此设置将字段值显示为列标题。

4. 这些设置生成汇总值。

创建查询

  1. 在“创建”选项卡上的“其他”组中,单击“查询设计”。

  2. 在“显示表”对话框中,双击要用作记录源的各个表或查询。

    如果使用多个记录源,请确保这些表或查询连接到他们共有的字段上。有关联接表和查询的详细信息,请参阅“另请参阅”部分。

  3. 关闭“显示表”对话框。

  4. 在“设计”选项卡的“查询类型”组中,单击“交叉表”。

  5. 在查询设计窗口中,双击要用作行标题源的每个字段。您可以为行标题选择三个字段。

  6. 在查询设计网格中的各个行标题字段的“交叉表”行中,选择“行标题”。

    您可以在“条件”行中输入一个表达式来限制该字段的结果。您还可以使用“排序”行指定字段的排序顺序。

  7. 在查询设计窗口中,双击要用作行标题源的字段。您只可以为列标题选择一个字段。

  8. 在查询设计网格中的列标题字段的“交叉表”行中,选择“列标题”。

    您可以在“条件”行中输入一个表达式以限制该列标题字段的结果。但是,使用带有列标题字段的条件表达式不限制交叉表查询返回的列数。相反,它限制哪些列包含数据。例如,假设列标题字段有三个可能值:红色、绿色和蓝色。如果将条件 =‘蓝色’应用于列标题字段,交叉表仍会显示为红色列和绿色列,但是只有蓝色列包含数据。

    如果您希望限制显示为列标题的值,可以通过使用查询的“列标题”属性指定固定值列表。有关详细信息,请参阅为列标题指定固定值

  9. 在查询设计窗口中,双击要用于计算汇总值的字段。您只可以选择一个字段用于汇总值。

  10. 在查询设计网格中的汇总值字段的“总计”行中,选择一个用于计算这些值的聚合函数。

  11. 在汇总值字段的“交叉表”行中,选择“”。

    不能为汇总值字段指定条件,也不能在该字段上进行排序。

  12. “设计”选项卡上的“结果”组中,单击“运行”

为列标题指定固定值

如果要指定用于列标题的固定值,您可以设置查询的“列标题”属性。

  1. 在设计视图中打开交叉表查询。

  2. 如果属性表不可见,请按 F4 显示该表。

  3. 在“常规”选项卡上方的属性表中,确保“所选内容的类型”是“查询属性”。如果不是,请单击查询设计网格上方的空间中的空白处。

  4. 在属性表的“常规”选项卡上,在“列标题”属性中输入要用作列标题的值列表,以逗号分隔。

    列标题中不允许使用某些字符(如大多数标点符号)。如果在值列表中使用这些字符,Access 用下划线(_)代替所有此类字符。

返回页首

在 SQL 视图中创建交叉表查询

交叉表查询的 SQL 语法

交叉表查询在 SQL 中表示为 TRANSFORM 语句。TRANSFORM 语句的语法如下 ︰

转换aggfunction
selectstatement
PIVOT透视[IN (value1[, value2[,...]])]

TRANSFORM 语句包含以下部分 ︰

部分

说明

aggfunction

对所选的数据进行操作 SQL 聚合函数。

selectstatement

SELECT 语句。

透视字段

设置字段或表达式您想要用于创建查询的结果中的列标题。

value1 value2

用于创建列标题的固定的值。

SQL 视图不限制表或查询,您可以用作记录源交叉表查询的数。但是,您可以帮助保持简单通过创建选择查询返回的所有要在交叉表查询中使用数据的设计,然后使用该选择查询作为记录源。有关创建选择查询的详细信息,请参阅另请参阅部分。

  1. 在“创建”选项卡上的“其他”组中,单击“查询设计”。

  2. 关闭“显示表”对话框。

  3. 设计选项卡的视图组中,单击视图,然后单击SQL 视图

  4. SQL 对象选项卡上,键入或粘贴以下 SQL:

    TRANSFORM 
    SELECT
    FROM
    GROUP BY
    PIVOT
    ;
  5. 在第一行中之后转换,请, 键入表达式用于计算汇总值;例如, Sum([Amount])

    如果您使用的多个表或查询用作记录源,包括为每个字段的名称; 的一部分的表或查询名称例如, Sum ([费用]。 [金额])

  6. 在第二行中,选择之后, 键入字段或您想要使用的行标题的字段表达式的列表。使用逗号; 分隔的列表项例如, [预算]。 [Dept_ID],[费用]。[类型]

  7. 第三行中,之后, 键入列表的表或查询,要用作记录源;例如,预算、 费用

  8. 在第四行之后GROUP BY中,,键入在 SELECT 子句中,在步骤 6 中相同您使用的字段的列表。

  9. 在第五个行中,数据透视表之后键入某个字段名称或您想要对列标题; 使用的表达式例如, PIVOT [预算]。 [Year].

添加到行标题字段的排序顺序

若要添加到 SQL 视图中的交叉表查询的排序顺序,请使用 ORDER BY 子句。

  1. 插入 GROUP BY 子句和数据透视子句之间的线条。

  2. 在新行中,键入ORDER BY后面跟一个空格。

  3. 键入您想要作为排序依据; 的表达式的字段名称例如, ORDER BY [费用]。 [Expense_Class]

    默认情况下,ORDER BY 子句对值按升序顺序进行排序。如果您想要按降序排序,字段名或表达式之后键入降序

  4. 如果您想要作为排序依据的其他字段或表达式,键入一个逗号,然后键入其他字段名或表达式。排序时,会出现字段或表达式 ORDER BY 子句中所显示的顺序。

限制用于行或列标题的值

您可以使用以下过程以指定要用作列标题和添加到您的行标题字段的条件的值列表。这些步骤假定您已在 SQL 视图中打开交叉表查询。

指定要用作列标题的固定的值

  • 在数据透视子句的末尾,键入后, 跟逗号分隔值 (括在括号中) 的列表以用作列标题。例如,在 2007年、 2008年、 2009年 (2010年)生成四个列标题 ︰ 2007年、 2008年、 2009年、 2010年。

如果您指定的固定的值的不对应字段值从数据透视表字段,该固定值按钮将变为一个空列的列标题。

添加用于限制行标题的查询条件

  1. FROM 子句之后插入新行。

  2. 位置字段条件后跟类型。

    如果您想要使用其他条件,您可以使用 AND 和 OR 运算符来扩展 WHERE 子句。您还可以为逻辑集使用括号对组条件。

返回页首

对标题使用范围或间隔

有时,而不是使用的行或列标题字段的每个值,您想要分组为区域的字段的值,然后对行或列标题中使用这些区域。例如,假设一个"年龄"字段用于列标题。而不是使用的每个年龄的一列,您可能希望使用表示时间范围的列。

可以在表达式中使用 IIf 函数来创建用于行或列标题的范围。

提示: 如果要创建带有“日期/时间”字段的时间间隔,请考虑使用交叉表查询向导。该向导可让您将日期分组为“”、“季度”、“”、“日期”或“日期/时间”间隔。如果所有这些间隔是都不是您想要的,您应该在设计视图中创建交叉表查询,然后使用本节所述的方法来创建所需的间隔。

IIf 的工作方式

IIf函数的工作原理是计算表达式,然后返回一个值,如果该表达式为 true 或另一个值,如果该表达式为 false。您可以嵌套IIf语句以创建逻辑顺序进行比较。因此,您可以使用IIf分隔成范围的数值字段值。

IIf 语法

IIf ( expr truepart falsepart )

IIf 函数语法具有下列参数:

参数

说明

expr

必需。要计算其值的表达式。

truepart

必需。exprTrue 时返回的值或表达式。

falsepart

必需。exprFalse 时返回的值或表达式。

使用表达式创建范围

  1. 在设计视图中打开交叉表查询。

  2. 在查询设计网格的“字段”行中,右键单击一个空列,然后在快捷菜单上单击“缩放”。

  3. 在“缩放”框中,键入字段别名,并且后跟一个冒号 (:)。

  4. 键入 IIf()

  5. IIf 后面的括号中,键入一个定义字段值第一个范围的比较表达式。

    例如,假定正在创建“年龄”字段的范围,且您希望每个范围是 20 年。第一个范围的比较表达式是 [年龄]< 21

  6. 在比较表达式后键入一个逗号,然后键入范围的名称,并用引号括起来。您提供的名称为该范围之内值的交叉表标题。

    例如,在 [年龄]< 21后面键入一个逗号,然后键入“0 — 20 岁”

  7. 在范围名称后面键入一个逗号(在双引号外面),然后执行下列操作之一:

    • 若要创建其他范围,键入 IIf(),然后重复步骤 5、6 和 7。

    • 对于最后一个范围,只需键入该范围的名称即可。

      例如,一个将“年龄”字段分隔为二十年的范围的完整嵌套 IIf 表达式如下所示(为易于阅读,添加了换行符):

      IIf([Age]<21,"0-20 years",
      IIf([Age]<41,"21-40 years",
      IIf([Age]<61,"41-60 years",
      IIf([Age]<81,"61-80 years", "80+ years"))))

      注意: 当 Access 计算表达式时,某一 IIf 语句的值为 True 时则立即停止计算。您无需指定每个范围的下限,因为任何低于给定范围下限的值都为 True。

  8. 在查询设计网格的“总计”行中,选择“分组依据”。

  9. 在“交叉表”行中,指定是将范围用作行标题还是列标题。请记住,您可以指定一个到三个行标题,和一个列标题。

返回页首

提示输入用于限制行标题的参数

您可能希望当运行交叉表查询时提示输入。例如,假设您正在使用多个行标题,其中一个为国家/地区。您可能希望查询提示一个名称,然后根据用户的输入显示数据,而不是始终显示每个国家或地区的数据。

可以向任意行标题字段添加参数提示。

注意: 您也可以向列标题字段添加参数提示,但这不会限制所显示的列。有关限制所显示列的详细信息,请参阅为列标题指定固定值部分。

  1. 在设计视图中打开交叉表查询。

  2. 在您要为其提示用户输入的行标题字段的“条件”行中,在方括号中键入问题文本。在运行查询时,该问题文本将显示为提示。

    例如,如果在“条件”行中键入 [哪个国家或地区?],在运行查询时,将会出现一个对话框,其中包含问题“哪个国家或地区?”、一个输入框和一个“确定”按钮。

    提示: 如果您希望您的参数非常灵活,通过使用Like运算符串联使用通配符,表达式。例如,而不是使用[哪些国家/地区或 region?]作为您的条件,您可以使用如 [哪些国家/地区或 region?] &"*"进行匹配更大范围的输入参数。使用不会更改参数提示的外观。

  3. 在“设计”选项卡的“显示/隐藏”组中,单击“参数”。

  4. 在“查询参数”对话框中的“参数”列中,输入您在“条件”行中使用的相同参数提示。包括方括号,但不包含任何连接的通配符或 Like 运算符。

  5. “数据类型”列中,选择参数的数据类型。数据类型应与行标题字段的数据类型相匹配。

返回页首

将空值替换为零

如果您用于在交叉表查询中计算汇总值的字段包含空值,您所使用的任何聚合函数将忽略这些值。对于某些聚合函数,结果可能受到影响。例如,若要计算平均值,请您添加所有值并用结果除以值的个数。但如果字段包含有任何空值,这些空值不计为值个数的一部分。

在某些情况下,您可能希望将所有空值替换为零,以便这些值将在聚合计算期间被计算在内。您可以使用 Nz 函数将空值替换为零。

Nz 语法

Nz ( variant [valueifnull ] )

Nz 函数的语法有以下参数:

参数

说明

Variant

必需。可变的数据类型变量

valueifnull

可选(除非用于查询中)。如果变量参数为,提供值的变量将被返回。此参数允许您返回非零值或零长度的字符串。

注意: 如果在查询表达式中使用 Nz 函数而不使用 valueifnull 参数,那么在包含空值的字段中结果将是零长度字符串。

  1. 在设计视图中打开查询后,在查询设计网格中右键单击“”字段。

  2. 在快捷菜单中,单击“缩放”。

  3. 在“缩放”框中,将字段名或表达式放在括号中,然后在括号前面键入 Nz

  4. 在右侧括号内直接键入, 0

    例如,如果将 Nz 用于名为“Hours Lost”的字段,以将 null 值转换为零,那么最终的表达式将如下所示:

Nz([Hours Lost], 0)

返回页首

交叉表查询提示

  • 使其保持简单    随着行组合数量的增加,交叉表查询可能变得难以阅读。请勿使用超出所需数量的行标题。

  • 考虑将交叉表建造在步骤中    请不要局限于仅使用表。您通常可以先建造总计查询,然后将该查询用作交叉表查询的记录源。

  • 仔细选择自己的列标题字段    当列标题数保持相对较小时,交叉表数据表将更易于阅读。一旦确定用作标题的字段,请考虑使用具有最少不同值的字段以生成列标题。例如,如果查询计算按年龄和性别计算值,请考虑将性别用作列标题,而不是年龄,因为性别通常比年龄具有更少的可能值。

  • 使用子查询中 WHERE 子句   您可以使用子查询用作 WHERE 子句的交叉表查询中的一部分。

返回页首

注意: 机器翻译免责声明:本文是由无人工介入的计算机系统翻译的。Microsoft 提供机器翻译是为了帮助非英语国家/地区用户方便阅读有关 Microsoft 产品、服务和技术的内容。由于机器翻译的原因,本文可能包含词汇、语法或文法方面的错误。

扩展你的技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×