如何为筛选的Excel报表设置级联列表框-Excel教程

Excel教程 2022-03-12 22:44:07

当主列表中的每个项目与一组辅助列表中的项目的不同集合相关联时，您可以使用级联列表框来管理那些辅助列表。

例如，如图的第一张图片显示，帽子在三个州出售，并且从该列表中选择了佛罗里达州。

在第二个图像中，即将从“产品”列表中选择“外套”项目。

在第三个图像中，选择了Coats后，状态单元变为红色，警告您Coats不在佛罗里达出售。

最后，在第四个图像中，“状态”列表框显示了销售Coats的五个州，并且即将选择新泽西州。您可以通过多种方式使用主次列表结构。例如…主要列表可以是部门的名称，次要列表可以是在每个部门工作的人员的名称。主要列表可以是竞争对手的名称，次要列表可以是每个竞争对手活跃的地区。主要列表可以是供应商的名称，次要列表可以是您从每个供应商购买的产品。等等。然后，一旦选择了主要和次要项目，报表就可以使用SUMIFS，COUNTIFS，AVERAGEIFS，SUMPRODUCT，数组公式或其他聚合方法从工作簿中的表中返回有关选择内容的信息。以下说明说明了如何使用动态范围名称和条件格式设置级联列表框。您也可以在此处下载工作簿。开始列表维护表此图像显示此级联列表示例的完整布局：

级联列表设置该表无需与列表位于同一工作表上。为了方便起见，我将其显示在列表附近。出于明显的原因，我将此表称为Graycell表。狭窄的灰色行和列标记了表使用的范围的边界。您将看到。首先，设置表格的文本和格式，如下所示。然后定义三个名称，这些名称引用它们如图的范围。为此，选择范围B6：H8。选择“公式”，“定义的名称”，“从选择中创建”，或按Ctrl + Shift + F3。在“创建名称”对话框中，确保仅选择“ 左”列。然后选择确定。D6单元格中的项目号指定D列中列表中的项目数。这是在显示的单元格中返回该数字的公式：D6： = COUNTA（D $ 8：D $ 15）（您可能会注意到，COUNTA函数同时计算数字和文本，但不计算空单元格。）输入公式后，将其复制到右侧，如图所示。开始列表框在第二行和第三行中输入文本并设置格式，然后设置格式。然后使用“创建名称”对话框将“产品”和“状态”分配为如图两个黄色单元格的名称。（顺便说一下，这些单元格是黄色的，以提供视觉提示，这些单元格包含可以更改的设置。）稍后，您将在这两个单元格中添加下拉列表框。但是首先，您需要设置两个动态范围名称。创建第一个动态范围名称列表框依赖于两个相当长的动态范围名称。为了解释名字，我将其分成两部分，然后将这些部分组合成一个长公式。对于第一部分，我们需要创建一个OFFSET公式，该公式返回对表中包含黄色产品单元格中输入的标签的单元格的引用。为此，我们使用INDEX-MATCH公式。这两个函数的语法公式为：= INDEX（参考，行数，列数，区域数）
= MATCH（lookup_value，lookup_array，match_type）因此，在任何空单元格中，输入以下公式：= INDEX（项目，1，MATCH（产品，产品，0））这是此公式告诉Excel的操作：从整个Items范围开始，返回对该范围第1行以及MATCH公式指定的列号中找到的单元格的引用。要查找该列号，请使用MATCH在“产品”列表中查找指定的产品。由于MATCH的第三个参数为零，因此可以按任何顺序列出产品，并且需要完全匹配。再次在此示例中，您可以在此处下载 …

级联列表设置..INDEX-MATCH公式返回对单元格D6的引用，该引用包含值3。现在，我们使用OFFSET-MAT??CH公式，其中OFFSET的语法为：=偏移（参考，行，列，高度，宽度）初步公式为：= OFFSET（TopRow，1，MATCH（Product，Products，0）-1，（3），1）（这里，该公式末尾的“（3）”是我们刚刚

标签：公式单元格名称选择 excel教程

上一篇：Excel数据表创建蒙特卡洛-汇总数据表的结果- 下一篇：绘制变化率以显示隐藏的业务绩效-

如何为筛选的Excel报表设置级联列表框-Excel教程

热门文章

最新文章

标签列表