创建链接到Excel表的可变长度，动态报告--英雄云拓展知识分享

当您更改单元格中的条件值时，Excel可使用公式和条件格式来自动扩大或收缩报表，而无需宏。这是如何做。Excel Tables是Excel 2007中引入的一项强大功能。您不但可以直接向它们报告，还可以将它们用作动态报告（包括可变长度手风琴报告）的数据源。第一项任务是快捷而轻松的；第2个更有趣。让我们看看二者。直接从Excel表报告假定您有一个这样的表，并且想要为每一个销售人员生成一个呼唤分配报告。

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

您可以打印她的报告，然后为Peter做一样的事情。但是，您通常不希望直接从表格中报告。例如…您可能需要包括公式以显示添加到表中数据的信息。（固然，您可以将计算出的列添加到表中，但是如果这些计算需要大量查找，则较长的表可能需要很长时间才能重新计算。）您可能需要在报告中为表格中的每行报告多行信息。在这些情况下，您将需要创建一个动态报表，该报表从表中返回数据，这是一个非常有趣的任务。引入动态的“手风琴”报告下图显示了由同一工作表生成的3个报告。如您所见，报告的长度各不相同…像手风琴一样展开和收缩。因此，这种情况下，我称它们为手风琴报告。经过从列表当选择“ *”，我自动显示了第一个报告。我选择“ Alyson”显示了第2个，而选择“ Peter”显示了第3个。所有这3个报告都从上面的两个Excel表中获得数据。以下说明向您展现了如何从头开始构造此报告。

从一个电子表格动态创建3个手风琴报告。这些报告也是动态的，由于它们会针对您在一个或多个单元格中指定的条件动态变化。一览无余，这是报告工作表的模样，其中通配符被选为CurSeller标准：

注意…此页面顶部的呼唤分配日志有6个分配。该报告也有6个分配，但是每一个分配使用两行。该报告在D列中显示了销售人员，当选择了特定的销售人员时，该字段将不成立…以下图所示。第18和19行包括允许添加其他呼唤分配的公式。您可以针对需要在电子表格中向下复制这两行。这是选择Alyson的同一报告：

选择Alyson后，报告结构将以两类方式自动更改。首先，D列的内容消失。其次，仅显示Alyson的作业，在报告底部保存空白行。让我们看看如何制造这类魔力…创建日志和客户端表第一步是在新工作簿中创建日志表。为此，就某些情况而言，请输入此处显示的日期和文本。

然后，要将简单表转换为Excel 2007或更高版本中的Excel表，请选择该表，然后选择“插入”，“表”，“表”。在“创建表”对话框中，确保选中“ 我的表具有标题”，然后选择“肯定”。默许情况下，第一个表名为Table1。要将名称Log分配给该表，请选择表中的任何单元格，然后在“表工具”，“设计”，“属性”中，输入名称Log 作为表名。一样，我们需要一个“客户详细信息”表：

将名称客户端分配给该表。设置控制表第2步是设置控制表。首先，在报表工作簿中创建一个新工作表，并将其命名为Control。

右图显示了控制表的全部内容。TopRow单元格返回“呼唤分配日志”的第一行号。这是显示的单元格的公式：B2：= ROW（Log [#All]）为了创建这个公式，我输入了…= row（…然后我选择了全部日志表；输入了右括号；然后按了Enter键。Excel设置了公式中所示的Table援用，即便我指定了较大的区域，这是由于 当我们指定多行范围时，Excel的 ROW函数将返回第一行。NumRows单元格使用 ROWS函数返回日志表中当前数据的行数。这是显示的单元格的公式：B3：= ROWS（Log [#All]）⑴将A列中的两个名称分配为B列中的范围名称。选择范围A2：B3，选择“公式”，“定义内涵的名称”，“针对选择创建”。在对话框中，确保仅选中“ 左”列，然后选择“肯定”。要设置卖方列表，请输入如图所示的文本，并针对需要设置其格式。然后选择范围A5：A8，然后再次启动“从选择中创建”对话框。但是，这一次确保仅选中“ 顶行”。创建报告首先向您的报表工作簿添加一个新的工作表。将工作表命名为“报告”。现在，让我们在单元格B1中设置列表框，如此处的报告所示（并在下面重复屡次）：

选中单元格B1，然后选择“数据”，“数据工具”，“数据验证”，“数据验证”。然后，在“数据验证”对话框中，在“ 允许”列表框当选择“ 列表”，然后输入 = Sellers作为“ 源”。然后选择肯定。现在，您只能在单元格B1中输入的数据是您在控制表中输入的卖方列表的内容。使用“创建名称”，将范围名称CurSeller分配 给单元格B1。要生成实际报告，请先在F3：F4范围内输入标签。以下图所示，要将标签居中放置在3列上，请勿合并单元格！而是选择范围F3：H4，选择用于对齐的对话框启动器（由下面的红色箭头显示）以启动显示“对齐”选项卡的“设置单元格格式”对话框。

对对话框中的“水平文本对齐”设置，选择“跨选区居中”。然后选择肯定。输入F5：H5范围内的标签，然后为每列输入公式和数据。F列包括对报告中的每组行重复的序列号。由于每组呼唤分配信息使用两行，所以同一序号出现两次。H列中的公式依赖于这些数字。输入为单元格F6和F7显示的值。然后输入此公式…F8：= F6 + 1…并将其复制到如图所示的列中。G列计算每组行中的每行。也就是说，组中的第一行是1，第2行是2。条件格式公式依赖于这些数字。要输入这些数字，请输入单元格G6和G7中显示的值，然后将范围G6：G7复制到该列中，如图所示。

H列包括报告中最复杂的公式。这些数字的目标是在日志表中标识包括CurSeller过滤器指定的信息的行。这是单元格H6的公式。我将其分为7行，因此更容易于浏览和解释，但您通常可以将其输入一行。
种别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函数将返回第2小的行编号，该行编号包括Alyson的数据。将其复制到H10后，它将返回最小的第3列，依此类推。在第4行中，部份（Log [SalesPerson] = CurSeller）生成一个TRUE和FALSE值的临时数组。将数组划分为1时，它将返回一个值等于1和＃DIV / 0的数组！毛病，这正是我们在这类情况下想要的。然后，当我们乘以第5行返回的行号时，我们得到一个行号数组和＃DIV / 0！毛病。由于AGGREGATE的第2个参数中的6告知函数疏忽毛病，并且由于其第一个参数中的15告知函数使用SMALL函数，所以AGGREGATE返回最小的非毛病行号（由单元格F6中的值指定） ，这是找到Alyson姓名的第一行。（为了更好地了解AGGREGATE公式在这类情况下的工作方式，请在 AGGREGATE函数页面上研究示例8至11。 ）第6行：第4行返回的行号是工作表的行号；但是我们需要日志表中的行索引号。为了取得该数字，我们减去“控制”工作表中TopRow单元返回的行号。如图所示，将公式向下复制到H列。创建报告的正文这是报告，从上方再次重复：

有了3个控制列以后，我们现在可以返回报告中显示的文本。因此，这种情况下，输入所示的公式…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为空，则返回空字符串。否则返回销售人员。接下来的3个公式从“客户详细信息表”中返回数据：

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）））这3个公式类似。他们在单元格A6中获得客户的名称，在“客户详细信息”表中查找该客户的行号，然后在该行中分别返回“公司”，“电话”和“ PastSales”。在第6行和第7行中输入公式后，将公式向下复制到第19行。格式化报告为了便于参考，下面是完全的报告：

将所需的所有填充色彩和字体分配给第3至5行。现在，让我们分配条件格式，这使报表像魔术一样工作。在分配格式时，请记住报告中的公式正在处理文本。也就是说，如果假定行或列为空白，则受作用的单元格中的公式已显示了空字符串。该报告使用4种条件格式…条件格式1此格式控制报表正文中每两行一组的第2行。分配方法以下：选择范围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 <>“ *”在“新建格式规则”对话框中，选择“格式”以启动“格式单元格”对话框。在“填充”选项卡中，指定“无色彩”。在边框选项卡中，指定没有边框。选择肯定，直到所有对话框都消失。您现在应当有一份手风琴工作报告。