当前位置：首页 > Excel小技巧 > 创建链接到Excel表的可变长度，动态报告-Excel学习网

创建链接到Excel表的可变长度，动态报告-Excel学习网

2019年10月18日 19:38 发布来源：Excel学习网

当您更改单元格中的条件值时，Excel可以使用公式和条件格式来自动扩展或收缩报表，而无需宏。这是如何做。

Excel Tables是Excel 2007中引入的一项强大功能。您不仅可以直接向它们报告，还可以将它们用作动态报告（包括可变长度手风琴报告）的数据源。

第一项任务是快速而轻松的；第二个更有趣。让我们看看两者。

直接从Excel表报告

假设您有一个这样的表，并且想要为每个销售人员生成一个呼叫分配报告。

最简单的方法是直接从表格中报告。例如，如果要报告Alyson，可以单击“销售人员”过滤器，然后从列表中选择Alyson，从而得到以下结果：

您可以打印她的报告，然后为Peter做同样的事情。

但是，您通常不希望直接从表格中报告。例如...

您可能需要包括公式以显示添加到表中数据的信息。（当然，您可以将计算出的列添加到表中，但是如果这些计算需要大量查找，则较长的表可能需要很长时间才能重新计算。）

您可能需要在报告中为表格中的每一行报告多行信息。

在这些情况下，您将需要创建一个动态报表，该报表从表中返回数据，这是一个非常有趣的任务。

引入动态的“手风琴”报告

下图显示了由同一工作表生成的三个报告。如您所见，报告的长度各不相同...像手风琴一样展开和收缩。因此，我称它们为手风琴报告。

通过从列表中选择“ *”，我自动显示了第一个报告。我选择“ Alyson”显示了第二个，而选择“ Peter”显示了第三个。所有这三个报告都从上面的两个Excel表中获取数据。

以下说明向您展示了如何从头开始构建此报告。

从一个电子表格动态创建三个手风琴报告。

这些报告也是动态的，因为它们会根据您在一个或多个单元格中指定的条件动态变化。

一览无余，这是报告工作表的样子，其中通配符被选为CurSeller标准：

注意...

此页面顶部的呼叫分配日志有六个分配。该报告也有六个分配，但是每个分配使用两行。

该报告在D列中显示了销售人员，当选择了特定的销售人员时，该字段将不成立...如下图所示。

第18和19行包含允许添加其他呼叫分配的公式。您可以根据需要在电子表格中向下复制这两行。

这是选择Alyson的同一报告：

选择Alyson后，报告结构将以两种方式自动更改。首先，D列的内容消失。其次，仅显示Alyson的作业，在报告底部保留空白行。

让我们看看如何创造这种魔力...

创建日志和客户端表

第一步是在新工作簿中创建日志表。为此，请输入此处显示的日期和文本。

然后，要将简单表转换为Excel 2007或更高版本中的Excel表，请选择该表，然后选择“插入”，“表”，“表”。在“创建表”对话框中，确保选中“ 我的表具有标题”，然后选择“确定”。

默认情况下，第一个表名为Table1。要将名称Log分配给该表，请选择表中的任何单元格，然后在“表工具”，“设计”，“属性”中，输入名称Log 作为表名。

同样，我们需要一个“客户详细信息”表：

将名称客户端分配给该表。

设置控制表

第二步是设置控制表。首先，在报表工作簿中创建一个新工作表，并将其命名为Control。

右图显示了控制表的全部内容。

TopRow单元格返回“呼叫分配日志”的第一行号。这是显示的单元格的公式：

B2：= ROW（Log [#All]）

为了创建这个公式，我输入了...

= row（

...然后我选择了整个日志表；输入了右括号；然后按了Enter键。Excel设置了公式中所示的Table引用，即使我指定了较大的区域，这是因为当我们指定多行范围时，Excel的 ROW函数将返回第一行。

NumRows单元格使用 ROWS函数返回日志表中当前数据的行数。这是显示的单元格的公式：

B3：= ROWS（Log [#All]）-1

将A列中的两个名称分配为B列中的范围名称。

选择范围A2：B3，

选择“公式”，“定义的名称”，“根据选择创建”。

在对话框中，确保仅选中“ 左”列，然后选择“确定”。

要设置卖方列表，请输入如图所示的文本，并根据需要设置其格式。然后选择范围A5：A8，然后再次启动“从选择中创建”对话框。但是，这一次确保仅选中“ 顶行”。

创建报告

首先向您的报表工作簿添加一个新的工作表。将工作表命名为“报告”。

现在，让我们在单元格B1中设置列表框，如此处的报告所示（并在下面重复多次）：

选中单元格B1，然后选择“数据”，“数据工具”，“数据验证”，“数据验证”。然后，在“数据验证”对话框中，在“ 允许”列表框中选择“ 列表”，然后输入 = Sellers作为“ 源”。然后选择确定。

现在，您只能在单元格B1中输入的数据是您在控制表中输入的卖方列表的内容。

使用“创建名称”，将范围名称CurSeller分配给单元格B1。

要生成实际报告，请先在F3：F4范围内输入标签。

如下图所示，要将标签居中放置在三列上，请勿合并单元格！而是选择范围F3：H4，选择用于对齐的对话框启动器（由下面的红色箭头显示）以启动显示“对齐”选项卡的“设置单元格格式”对话框。

对于对话框中的“水平文本对齐”设置，选择“跨选区居中”。然后选择确定。

输入F5：H5范围内的标签，然后为每列输入公式和数据。

F列包含对报告中的每组行重复的序列号。因为每组呼叫分配信息使用两行，所以同一序号出现两次。H列中的公式依赖于这些数字。

输入为单元格F6和F7显示的值。然后输入此公式...

F8：= F6 + 1

...并将其复制到如图所示的列中。

G列计算每组行中的每一行。也就是说，组中的第一行是1，第二行是2。条件格式公式依赖于这些数字。

要输入这些数字，请输入单元格G6和G7中显示的值，然后将范围G6：G7复制到该列中，如图所示。

H列包含报告中最复杂的公式。这些数字的目的是在日志表中标识包含CurSeller过滤器指定的信息的行。

这是单元格H6的公式。我将其分为七行，因此更易于阅读和解释，但您通常可以将其输入一行。

类别1：H6：= IF（$ F6> NumRows，“”，

类别2：IFERROR（

类别3：IF（CurSeller =“ *”，$ F6，

类别4：AGGREGATE（15,6，（1 /（Log [ SalesPerson] = CurSeller））*

第5行：ROW（Log [SalesPerson]），$ F6）

第6行：-TopRow），

第7行：“”））

第1行：如果当前行的F列中的序列号大于日志中的行数，则返回一个空字符串（“”）。除此以外...

第2行和第7行：如果以下公式中有错误，请在第7行中返回空字符串。否则，返回以下公式的内容。

第3行：如果用户在CurSeller单元格中选择了通配符“ *”，我们需要显示此行数据。因此，在F列中显示序列号。

第4和5行：使用 AGGREGATE函数返回包含Alyson数据的最小行号（假定选择了她的名字）。

当将此公式复制到单元格H8时，$ F6将更改为$ F8，其值为2。因此，在该单元格中，AGGREGATE函数将返回第二小的行编号，该行编号包含Alyson的数据。将其复制到H10后，它将返回最小的第三列，依此类推。

在第4行中，部分（Log [SalesPerson] = CurSeller）生成一个TRUE和FALSE值的临时数组。将数组划分为1时，它将返回一个值等于1和＃DIV / 0的数组！错误，这正是我们在这种情况下想要的。

然后，当我们乘以第5行返回的行号时，我们得到一个行号数组和＃DIV / 0！错误。

因为AGGREGATE的第二个参数中的6告诉函数忽略错误，并且因为其第一个参数中的15告诉函数使用SMALL函数，所以AGGREGATE返回最小的非错误行号（由单元格F6中的值指定），这是找到Alyson姓名的第一行。

（为了更好地了解AGGREGATE公式在这种情况下的工作方式，请在 AGGREGATE函数页面上研究示例8至11。）

第6行：第4行返回的行号是工作表的行号；但是我们需要日志表中的行索引号。为了获得该数字，我们减去“控制”工作表中TopRow单元返回的行号。

如图所示，将公式向下复制到H列。

创建报告的正文

这是报告，从上方再次重复：

有了三个控制列之后，我们现在可以返回报告中显示的文本。因此，输入所示的公式...

A3：=““＆IF（CurSeller =” *“，”所有卖家“，PROPER（CurSeller））的呼叫分配

此公式返回报告标题的文本。

范围A4：C5包含显示的标签。只需输入它们。

单元格D4包含以下公式：

D4：= IF（CurSeller =“ *”，“销售人员”，“”）

当CurSeller条件等于“ *”时，此公式将返回一个空字符串。

在第6行中，输入...

A6：= IF（$ H6 =“”，“”，INDEX（Log [Client]，$ H6））

B6：= IF（$ H6 =“”，“”，INDEX（Log [Date]，$ H6））

C6：= IF（$ H6 =“”，“”，INDEX（Log [CallType]，$ H6））

H列用作开关。如果此列中的指定单元格（由INDEX函数标识）包含空字符串（“”），则我们希望工作表不显示任何内容，既不显示数字，也不显示文本，也不显示格式。当单元格H6不为空时，这些公式将返回客户端的名称，日期和呼叫类型。

D6：= IF（OR（CurSeller <>“ *”，$ H6 =“”），“”，INDEX（Log [SalesPerson]，$ H6））

单元格D6添加了另一项测试。如果CurSeller是通配符或单元格H6为空，则返回空字符串。否则返回销售人员。

接下来的三个公式从“客户详细信息表”中返回数据：

A7：= IF（$ H6 =“”，“”，INDEX（客户[公司]，MATCH（$ A6，Clients [客户]，0）））

B7：= IF（$ H6 =“”，“”，INDEX （Clients [Phone]，MATCH（$ A6，Clients [Client]，0）））

C7：= IF（$ H6 =“”，“”，INDEX（Clients [PastSales]，MATCH（$ A6，Clients [Client] ，0）））

这三个公式相似。他们在单元格A6中获取客户的名称，在“客户详细信息”表中查找该客户的行号，然后在该行中分别返回“公司”，“电话”和“ PastSales”。

在第6行和第7行中输入公式后，将公式向下复制到第19行。

格式化报告

为了便于参考，下面是完整的报告：

将所需的所有填充颜色和字体分配给第3至5行。

现在，让我们分配条件格式，这使报表像魔术一样工作。在分配格式时，请记住报告中的公式正在处理文本。也就是说，如果假定行或列为空白，则受影响的单元格中的公式已经显示了空字符串。

该报告使用四种条件格式...

条件格式1

此格式控制报表正文中每两行一组的第二行。分配方法如下：

选择范围A6：D19，确保单元格A6是活动单元格。

选择“主页”，“样式”，“条件格式”，“新规则”。

在“新格式设置规则”对话框中，选择“ 使用公式来确定要格式化的单元格”。

在标有“ 格式值，其中此公式为true的编辑框：”中，输入：

= AND（$ G6 = 2，$ H6 <>“”）

（注意：该行号和下面的其他条件格式公式中的行号必须为与活动单元格相同的行号。）

在“新建格式规则”对话框中，选择“格式”以启动“格式单元格”对话框。

在“边框”选项卡中，指定底部边框。

选择确定，直到所有对话框都消失。

条件格式2

此格式控制报表正文中蓝色和白色的交替填充颜色。步骤如下：

选择范围A6：D19，确保单元格A6是活动单元格。

选择“主页”，“样式”，“条件格式”，“新规则”。

在“新格式设置规则”对话框中，选择“ 使用公式来确定要格式化的单元格”。

在标签格式为其中该公式为true的值的编辑框中：，输入：

= AND（ISODD（$ F6），$ H6 <>“”）

在“新建格式规则”对话框中，选择“格式”以启动“格式单元格”对话框。

在“填充”选项卡中，指定浅蓝色填充。

选择确定，直到所有对话框都消失。

条件格式3

此格式是隐藏D列内容的两种格式之一：

选择范围D4：D5，确保单元格D4是活动单元格。

选择“主页”，“样式”，“条件格式”，“新规则”。

在“新格式设置规则”对话框中，选择“ 使用公式来确定要格式化的单元格”。

在标有“ 格式值”（在此公式为true的位置）的编辑框中，输入：

= CurSeller <>“ *”

在“新建格式规则”对话框中，选择“格式”以启动“格式单元格”对话框。

在“填充”选项卡中，指定“无颜色”。

在“边框”选项卡中，指定黑色的左边框。

选择确定，直到所有对话框都消失。

条件格式4

这种格式是另一种隐藏D列内容的格式：

选择范围D6：D19，确保单元格D6是活动单元格。

选择“主页”，“样式”，“条件格式”，“新规则”。

在“新格式设置规则”对话框中，选择“ 使用公式来确定要格式化的单元格”。

在标有“ 格式值”（在此公式为true的位置）的编辑框中，输入：

= CurSeller <>“ *”

在“新建格式规则”对话框中，选择“格式”以启动“格式单元格”对话框。

在“填充”选项卡中，指定“无颜色”。

在边框选项卡中，指定没有边框。

选择确定，直到所有对话框都消失。

您现在应该有一份手风琴工作报告。

原创文章如转载请注明：转载自Excel学习网 [ http://www.excelxx.com/]
需要保留本文链接地址：http://www.excelxx.com/jiqiao/10718.html

分享到：

上一篇：Excel动态图表让数据动起来,老板看完亮瞎眼,还有这种操作?-Excel学习网下一篇：Excel的主要功能是什么-Excel学习网